package C4::Utils::DataTables::Members;

use Modern::Perl;

use C4::Context;

use C4::Utils::DataTables;

use Koha::DateUtils;

sub search {

    my ( $params ) = @_;

    my $searchmember = $params->{searchmember};

    my $firstletter = $params->{firstletter};

    my $categorycode = $params->{categorycode};

    my $branchcode = $params->{branchcode};

    my $searchtype = $params->{searchtype} || 'contain';

    my $searchfieldstype = $params->{searchfieldstype} || 'standard';

    my $has_permission = $params->{has_permission};

    my $dt_params = $params->{dt_params};

    unless ( $searchmember ) {

        $searchmember = $dt_params->{sSearch} // '';

    }

    # If branches are independent and user is not superlibrarian

    # The search has to be only on the user branch

    my $userenv = C4::Context->userenv;

    my $logged_in_user = Koha::Patrons->find( $userenv->{number} );

    my @restricted_branchcodes = $logged_in_user->libraries_where_can_see_patrons;

    my ($sth, $query, $iTotalQuery, $iTotalRecords, $iTotalDisplayRecords);

    my $dbh = C4::Context->dbh;

    # Get the module_bit from a given permission code

    if ( $has_permission ) {

        ($has_permission->{module_bit}) = $dbh->selectrow_array(q|

            SELECT bit FROM userflags WHERE flag=?

        |, undef, $has_permission->{permission});

    }

    my (@where, @conditions);

    # Get the iTotalRecords DataTable variable

    $iTotalQuery = "SELECT COUNT(borrowers.borrowernumber) FROM borrowers";

    if ( $has_permission ) {

        $iTotalQuery .= ' LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber';

        $iTotalQuery .= ' AND module_bit=? AND code=?';

        push @conditions, $has_permission->{module_bit}, $has_permission->{subpermission};

    }

    if ( @restricted_branchcodes ) {

        push @where, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";

        push @conditions, @restricted_branchcodes;

    }

    if ( $has_permission ) {

        push @where, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';

        push @conditions, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};

    }

    $iTotalQuery .= ' WHERE ' . join ' AND ', @where if @where;

    ($iTotalRecords) = $dbh->selectrow_array( $iTotalQuery, undef, @conditions );

    # Do that after iTotalQuery!

    if ( defined $branchcode and $branchcode ) {

        @restricted_branchcodes = @restricted_branchcodes

            ? grep ({ $_ eq $branchcode } @restricted_branchcodes)

                ? ($branchcode)

                : (undef) # Do not return any results

            : ($branchcode);

    }

    if ( $searchfieldstype eq 'dateofbirth' ) {

        # Return an empty list if the date of birth is not correctly formatted

        $searchmember = eval { output_pref( { str => $searchmember, dateformat => 'iso', dateonly => 1 } ); };

        if ( $@ or not $searchmember ) {

            return {

                iTotalRecords        => $iTotalRecords,

                iTotalDisplayRecords => 0,

                patrons              => [],

            };

        }

    }

    my @columns = qw( borrowernumber surname firstname othernames flags streetnumber streettype address address2 city state zipcode country cardnumber dateexpiry borrowernotes branchcode email userid dateofbirth categorycode phone phonepro mobile fax email emailpro);

    my $prefillguarantorfields = C4::Context->preference("PrefillGuaranteeField");

    my @prefill_fields = split(/\,/,$prefillguarantorfields);

    if ( @prefill_fields ) {

        foreach my $field (@prefill_fields) {

            if (! grep {$_ eq $field} @columns) {

                push @columns, $field;

            }

        }

    };

    my $borrowers_columns = "";

    foreach my $item (@columns) {

        $borrowers_columns .= "borrowers." . $item . ", ";

    }

    my $select = "SELECT " . $borrowers_columns . "

        categories.description AS category_description, categories.category_type,

        branches.branchname, borrowers.phone";

    my $from = "FROM borrowers

                LEFT JOIN branches ON borrowers.branchcode = branches.branchcode

                LEFT JOIN categories ON borrowers.categorycode = categories.categorycode";

    my @where_args;

    if ( $has_permission ) {

        $from .= '

                LEFT JOIN user_permissions ON borrowers.borrowernumber=user_permissions.borrowernumber

                AND module_bit=? AND code=?';

        push @where_args, $has_permission->{module_bit}, $has_permission->{subpermission};

    }

    my @where_strs;

    if(defined $firstletter and $firstletter ne '') {

        push @where_strs, "borrowers.surname LIKE ?";

        push @where_args, "$firstletter%";

    }

    if(defined $categorycode and $categorycode ne '') {

        push @where_strs, "borrowers.categorycode = ?";

        push @where_args, $categorycode;

    }

    if(@restricted_branchcodes ) {

        push @where_strs, "borrowers.branchcode IN (" . join( ',', ('?') x @restricted_branchcodes ) . ")";

        push @where_args, @restricted_branchcodes;

    }

    my $searchfields = {

        standard => C4::Context->preference('DefaultPatronSearchFields') || 'surname,firstname,othernames,cardnumber,userid',

        email => 'email,emailpro,B_email',

        borrowernumber => 'borrowernumber',

        phone => 'phone,phonepro,B_phone,altcontactphone,mobile',

        address => 'streetnumber,streettype,address,address2,city,state,zipcode,country',

    };

    # * is replaced with % for sql

    $searchmember =~ s/\*/%/g;

    # split into search terms

    my @terms;

    # consider coma as space

    $searchmember =~ s/,/ /g;

    if ( $searchtype eq 'contain' ) {

       @terms = split / /, $searchmember;

    } else {

       @terms = ($searchmember);

    }

    foreach my $term (@terms) {

        next unless $term;

        my $term_dt = eval { local $SIG{__WARN__} = {}; output_pref( { str => $term, dateonly => 1, dateformat => 'sql' } ); };

        if ($term_dt) {

            $term = $term_dt;

        } else {

            $term .= '%'    # end with anything

              if $term !~ /%$/;

            $term = "%$term"    # begin with anythin unless start_with

              if $searchtype eq 'contain' && $term !~ /^%/;

        }

        my @where_strs_or;

        if ( defined $searchfields->{$searchfieldstype} ) {

            for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {

                push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";

                push @where_args, $term;

            }

        } else {

            push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfieldstype) . " LIKE ?";

            push @where_args, $term;

        }

        if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) {

            my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber');

            for my $borrowernumber ( @matching_borrowernumbers ) {

                push @where_strs_or, "borrowers.borrowernumber = ?";

                push @where_args, $borrowernumber;

            }

        }

        push @where_strs, '('. join (' OR ', @where_strs_or) . ')'

            if @where_strs_or;

    }

    if ( $has_permission ) {

        push @where_strs, '( borrowers.flags = 1 OR borrowers.flags & (1 << ?) OR module_bit=? AND code=? )';

        push @where_args, ($has_permission->{module_bit}) x 2, $has_permission->{subpermission};

    }

    my $where = @where_strs ? " WHERE " . join (" AND ", @where_strs) : undef;

    my $orderby = dt_build_orderby($dt_params);

    my $limit;

    # If iDisplayLength == -1, we want to display all patrons

    if ( !$dt_params->{iDisplayLength} || $dt_params->{iDisplayLength} > -1 ) {

        # In order to avoid sql injection

        $dt_params->{iDisplayStart} =~ s/\D//g if defined($dt_params->{iDisplayStart});

        $dt_params->{iDisplayLength} =~ s/\D//g if defined($dt_params->{iDisplayLength});

        $dt_params->{iDisplayStart} //= 0;

        $dt_params->{iDisplayLength} //= 20;

        $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";

    }

    $query = join(

        " ",

        ($select ? $select : ""),

        ($from ? $from : ""),

        ($where ? $where : ""),

        ($orderby ? $orderby : ""),

        ($limit ? $limit : "")

    );

    $sth = $dbh->prepare($query);

    $sth->execute(@where_args);

    my $patrons = $sth->fetchall_arrayref({});

    # Get the iTotalDisplayRecords DataTable variable

    $query = "SELECT COUNT(borrowers.borrowernumber) " . $from . ($where ? $where : "");

    $sth = $dbh->prepare($query);

    $sth->execute(@where_args);

    ($iTotalDisplayRecords) = $sth->fetchrow_array;

    # Get some information on patrons

    foreach my $patron (@$patrons) {

        my $patron_object = Koha::Patrons->find( $patron->{borrowernumber} );

        $patron->{overdues} = $patron_object->get_overdues->count;

        $patron->{issues} = $patron_object->checkouts->count;

        $patron->{age} = $patron_object->get_age;

        my $balance = $patron_object->account->balance;

        # FIXME Should be formatted from the template

        $patron->{fines} = sprintf("%.2f", $balance);

        if( $patron->{dateexpiry} ) {

            # FIXME We should not format the date here, do it in template-side instead

            $patron->{dateexpiry} = output_pref( { dt => scalar dt_from_string( $patron->{dateexpiry}, 'iso'), dateonly => 1} );

        } else {

            $patron->{dateexpiry} = '';

        }

    }

    return {

        iTotalRecords => $iTotalRecords,

        iTotalDisplayRecords => $iTotalDisplayRecords,

        patrons => $patrons

    }

}

1;

__END__

=head1 NAME

C4::Utils::DataTables::Members - module for using DataTables with patrons

=head1 SYNOPSIS

This module provides (one for the moment) routines used by the patrons search

=head2 FUNCTIONS

=head3 search

    my $dt_infos = C4::Utils::DataTables::Members->search($params);

$params is a hashref with some keys:

=over 4

=item searchmember

  String to search in the borrowers sql table

=item firstletter

  Introduced to contain 1 letter but can contain more.

  The search will done on the borrowers.surname field

=item categorycode

  Search patrons with this categorycode

=item branchcode

  Search patrons with this branchcode

=item searchtype

  Can be 'start_with' or 'contain' (default value). Used for the searchmember parameter.

=item searchfieldstype

  Can be 'standard' (default value), 'email', 'borrowernumber', 'phone', 'address' or 'dateofbirth', 'sort1', 'sort2'

=item dt_params

  Is the reference of C4::Utils::DataTables::dt_get_params($input);

=cut

=back

=head1 LICENSE

This file is part of Koha.

Copyright 2013 BibLibre

Koha is free software; you can redistribute it and/or modify it

under the terms of the GNU General Public License as published by

the Free Software Foundation; either version 3 of the License, or

(at your option) any later version.

Koha is distributed in the hope that it will be useful, but

WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU General Public License for more details.

You should have received a copy of the GNU General Public License

along with Koha; if not, see <http://www.gnu.org/licenses>.

[% USE raw %]

[% USE Asset %]

[% USE Koha %]

[% USE Branches %]

[% SET footerjs = 1 %]

[% INCLUDE 'doc-head-open.inc' %]

<title>Koha &rsaquo; Patron search</title>

[% INCLUDE 'doc-head-close.inc' %]

<style> .modal-body .close { display: none; } </style>

</head>

<body id="common_patron_search" class="common">

<div id="patron_search" class="yui-t7">

    <div class="container-fluid">

        <form id="searchform">

            <fieldset class="brief">

                <h3>Search for patron</h3>

                <ol>

                    <li>

                        <label for="searchmember_filter">Search:</label>

                        <input type="text" id="searchmember_filter" value="[% searchmember | html %]"/>

                    </li>

                    <li>

                        <label for="categorycode_filter">Category:</label>

                        <select id="categorycode_filter">

                            <option value="">Any</option>

                            [% FOREACH category IN categories %]

                                <option value="[% category.categorycode | html %]">[% category.description | html %]</option>

                            [% END %]

                        </select>

                    </li>

                    <li>

                        <label for="branchcode_filter">Library:</label>

                        <select id="branchcode_filter">

                            [% SET libraries = Branches.all( only_from_group => 1 ) %]

                            [% IF libraries.size != 1 %]

                                <option value="">Any</option>

                            [% END %]

                            [% FOREACH l IN libraries %]

                                <option value="[% l.branchcode | html %]">[% l.branchname | html %]</option>

                            [% END %]

                        </select>

                    </li>

                </ol>

                <fieldset class="action">

                    <input type="submit" value="Search" />

                </fieldset>

            </fieldset>

        </form>

        [% IF patrons_with_acq_perm_only %]

            <div class="hint">Only staff with superlibrarian or acquisitions permissions (or order_manage permission if granular permissions are enabled) are returned in the search results</div>

        [% END %]

        [% IF patrons_with_suggestion_perm_only %]

            <div class="hint">Only staff with superlibrarian or suggestions_manage permissions are returned in the search results</div>

        [% END %]

        <div class="browse">

            Browse by last name:

            [% FOREACH letter IN alphabet.split(' ') %]

                <a href="#" class="filterByLetter">[% letter | html %]</a>

            [% END %]

        </div>

        <div id="info" class="dialog message"></div>

        <div id="error" class="dialog alert"></div>

        <input type="hidden" id="firstletter_filter" value="" />

        <div id="searchresults">

            <table id="memberresultst">

                <thead>

                    <tr>

                        [% FOR column IN columns %]

                            [% SWITCH column %]

                                [% CASE 'cardnumber' %]<th>Card</th>

                                [% CASE 'dateofbirth' %]<th>Date of birth</th>

                                [% CASE 'address' %]<th>Address</th>

                                [% CASE 'name' %]<th>Name</th>

                                [% CASE 'branch' %]<th>Library</th>

                                [% CASE 'category' %]<th>Category</th>

                                [% CASE 'dateexpiry' %]<th>Expires on</td>

                                [% CASE 'borrowernotes' %]<th>Notes</th>

                                [% CASE 'action' %]<th>&nbsp;</th>

                            [% END %]

                        [% END %]

                    </tr>

                  </thead>

                <tbody></tbody>

            </table>

        </div>

<div id="closewindow"><a href="#" class="btn btn-default btn-default close">Close</a></div>

<!-- Patron preview modal -->

<div class="modal" id="patronPreview" tabindex="-1" role="dialog" aria-labelledby="patronPreviewLabel">

    <div class="modal-dialog" role="document">

        <div class="modal-content">

            <div class="modal-header">

                <button type="button" class="closebtn" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>

                <h4 class="modal-title" id="patronPreviewLabel"></h4>

            </div>

            <div class="modal-body">

                <div id="loading">

                    <img src="[% interface | html %]/[% theme | html %]/img/spinner-small.gif" alt="" /> Loading

                </div>

            </div>

            <div class="modal-footer">

                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>

            </div>

        </div>

    </div>

</div>

</div>

</div>

[% MACRO jsinclude BLOCK %]

    [% INCLUDE 'datatables.inc' %]

    <script type="text/javascript">

        var search = 1;

        $(document).ready(function(){

            $("#info").hide();

            $("#error").hide();

            [% IF view != "show_results" %]

                $("#searchresults").hide();

                search = 0;

            [% END %]

            // Apply DataTables on the results table

            dtMemberResults = $("#memberresultst").dataTable($.extend(true, {}, dataTablesDefaults, {

                'bServerSide': true,

                'sAjaxSource': "/cgi-bin/koha/svc/members/search",

                'fnServerData': function(sSource, aoData, fnCallback) {

                    if ( ! search ) {

                        return;

                    }

                    aoData.push({

                        'name': 'searchmember',

                        'value': $("#searchmember_filter").val()

                    },{

                        'name': 'firstletter',

                        'value': $("#firstletter_filter").val()

                    },{

                        'name': 'categorycode',

                        'value': $("#categorycode_filter").val()

                    },{

                        'name': 'branchcode',

                        'value': $("#branchcode_filter").val()

                    },{

                        'name': 'name_sorton',

                        'value': 'borrowers.surname borrowers.firstname'

                    },{

                        'name': 'category_sorton',

                        'value': 'categories.description',

                    },{

                        'name': 'branch_sorton',

                        'value': 'branches.branchname'

                    },{

                        'name': 'template_path',

                        'value': '[% json_template | html %]',

                    },{

                        'name': 'selection_type',

                        'value': '[% selection_type | html %]',

                    }

                    [% IF patrons_with_acq_perm_only %]

                    ,{

                        'name': 'has_permission',

                        'value': 'acquisition.order_manage',

                    }

                    [% ELSIF patrons_with_suggestion_perm_only %]

                    ,{

                        'name': 'has_permission',

                        'value': 'suggestions.suggestions_manage',

                    }

                    [% END %]

                    );

                    $.ajax({

                        'dataType': 'json',

                        'type': 'POST',

                        'url': sSource,

                        'data': aoData,

                        'success': function(json){

                            fnCallback(json);

                        }

                    });

                },

                'aoColumns':[

                    [% FOR column IN columns %]

                        [% IF column == 'action' %]

                            { 'mDataProp': 'dt_action', 'bSortable': false, 'sClass': 'actions' }

                        [% ELSIF column == 'address' %]

                            { 'mDataProp': 'dt_address', 'bSortable': false }

                        [% ELSE %]

                            { 'mDataProp': 'dt_[% column | html %]' }

                        [% END %]

                        [% UNLESS loop.last %],[% END %]

                    [% END %]

                ],

                'bAutoWidth': false,

                'sPaginationType': 'full_numbers',

                "iDisplayLength": [% Koha.Preference('PatronsPerPage') | html %],

                'aaSorting': [[[% aaSorting || 0 | html %], 'asc']],

                'bFilter': false,

                'bProcessing': true,

            }));

            $("#searchform").on('submit', filter);

            $(".filterByLetter").on("click",function(e){

                e.preventDefault();

                filterByFirstLetterSurname($(this).text());

            });

            $("body").on("click",".add_user",function(e){

                e.preventDefault();

                var borrowernumber = $(this).data("borrowernumber");

                var firstname = $(this).data("firstname");

                var surname = $(this).data("surname");

                add_user( borrowernumber, firstname + " " + surname );

            });

            $("body").on("click",".select_user",function(e){

                e.preventDefault();

                var borrowernumber = $(this).data("borrowernumber");

                var borrower_data = $("#borrower_data"+borrowernumber).val();

                var guarantor_attributes = $("#guarantor_attributes"+borrowernumber).val();

                if ( !guarantor_attributes ) {

                    guarantor_attributes = "{}";

                }

                select_user( borrowernumber, JSON.parse(borrower_data), JSON.parse(guarantor_attributes) );

            });

            $("body").on("click",".patron_preview", function( e ){

                e.preventDefault();

                var borrowernumber = $(this).data("borrowernumber");

                var page = "/cgi-bin/koha/members/moremember.pl?print=brief&borrowernumber=" + borrowernumber;

                $("#patronPreview .modal-body").load( page + " div.container-fluid" );

                $('#patronPreview').modal({show:true});

            });

            $("#patronPreview").on('hidden.bs.modal', function (e) {

                $("#patronPreview .modal-body").html("<img src=\"[% interface | html %]/[% theme | html %]/img/spinner-small.gif\" alt=\"\" /> Loading");

            });

        });

        function filter() {

            search = 1;

            $("#firstletter_filter").val('');

            $("#searchresults").show();

            dtMemberResults.fnDraw();

            return false;

        }

        // User has clicked on a letter

        function filterByFirstLetterSurname(letter) {

            $("#firstletter_filter").val(letter);

            search = 1;

            $("#searchresults").show();

            dtMemberResults.fnDraw();

        }

        // modify parent window owner element

        [% IF selection_type == 'add' %]

            function add_user(borrowernumber, borrowername) {

                var p = window.opener;

                // In one place (serials/routing.tt), the page is reload on every add

                // We have to wait for the page to be there

                function wait_for_opener () {

                    if ( ! $(opener.document).find('body').size() ) {

                        setTimeout(wait_for_opener, 500);

                    } else {

                        [%# Note that add_user could sent data instead of borrowername too %]

                        $("#info").hide();

                        $("#error").hide();

                        if ( p.add_user(borrowernumber, borrowername) < 0 ) {

                            $("#error").html(_("Patron '%s' is already in the list.").format(borrowername));

                            $("#error").show();

                        } else {

                            $("#info").html(_("Patron '%s' added.").format(borrowername));

                            $("#info").show();

                        }

                    }

                }

                wait_for_opener();

            }

        [% ELSIF selection_type == 'select' %]

            function select_user(borrowernumber, data, attributes) {

                var p = window.opener;

                p.select_user(borrowernumber, data, null, attributes);

                window.close();

            }

        [% END %]

    </script>

[% END %]

[% SET popup_window = 1 %]

[% INCLUDE 'intranet-bottom.inc' %]

[% USE raw %]

[% USE To %]

[% USE Branches %]

[% USE KohaDates %]

{

    "sEcho": [% sEcho | html %],

    "iTotalRecords": [% iTotalRecords | html %],

    "iTotalDisplayRecords": [% iTotalDisplayRecords | html %],

    "aaData": [

        [% FOREACH data IN aaData %]

            {

                "dt_cardnumber":

                    "[% data.cardnumber | html %]",

                "dt_name":

                    "[% INCLUDE 'patron-title.inc' borrowernumber = data.borrowernumber category_type = data.category_type firstname = data.firstname surname = data.surname othernames = data.othernames cardnumber = data.cardnumber invert_name = 1%]",

                "dt_dateofbirth":

                    "[% INCLUDE 'patron-age.inc' patron = data %]",

                "dt_address":

                    "[% INCLUDE escape_address data=data %]",

                "dt_action":

                    "<a href=\"#\" class=\"btn btn-default btn-xs select_user\" data-borrowernumber=\"[% data.borrowernumber | html %]\">Select</a><input type=\"hidden\" id=\"borrower_data[% data.borrowernumber | html %]\" name=\"borrower_data[% data.borrowernumber | html %]\" value=\"[% To.json(data) | html %]\" /><input type=\"hidden\" id=\"guarantor_attributes[% data.borrowernumber | html %]\" name=\"guarantor_attributes[% data.borrowernumber | html %]\" value=\"[% To.json(guarantor_attributes) | html %]\" />"

            }[% UNLESS loop.last %],[% END %]

        [% END %]

    ]

}

[% BLOCK escape_address %]

[%~ SET address = data.streetnumber _ ' ' %]

[%~ IF data.address %][% SET address = address _ data.address _ ' ' %][% END %]

[%~ IF data.address2 %][% SET address = address _ data.address2 _ ' ' %][% END %]

[%~ IF data.city %][% SET address = address _ data.city _ ' ' %][% END %]

[%~ IF data.state %][% SET address = address _ data.state _ ' ' %][% END %]

[%~ IF data.zipcode %][% SET address = address _ data.zipcode _ ' ' %][% END %]

[%~ IF data.country %][% SET address = address _ data.country _ ' ' %][% END %]

[%~ SET address = address _ Branches.GetName( data.branchcode ) %]

[%~ To.json( address ) | $raw ~%]

[% END %]

        if ( attributes ) {

            for (var i = 0; i < parseInt(attributes.length, 10); i++) {

                var attribute = attributes[i];

                if ( borrower[attribute] != null && document.forms.entryform[attribute].value == "" ) {

                    document.forms.entryform[attribute].value = borrower[attribute];

                }

            }

        }