From d28223b5197eba5ca8cff96d42793f9f1860e58e Mon Sep 17 00:00:00 2001
From: Andrew Isherwood <andrew.isherwood@ptfs-europe.com>
Date: Fri, 20 Apr 2018 14:25:49 +0100
Subject: [PATCH] Bug 20600: Add filtering of ILL requests in list

This patch adds the ability for the ILL request list in the staff
interface to be filtered by a number of criteria:

- Status
- Date placed
- Date modified
- Pickup branch
- Borrower card number

To test:
1) Apply patch
2) Ensure you have a reasonable range of ILL requests created
3) Navigate to the "View ILL requests" page
4) Choose one or more filtering criteria
5) Click "Search"
6) Observe the results are filtered to match the selected criteria
7) Click "Clear"
8) Observe your results are returned to their initial state
9) Repeat steps 4 -> 6 until you are happy.

Dates supplied by the /api/v1/illrequests API route were not conforming
to the preference specified by the dateformat syspref. This patch
addresses that.

It has been addressed as part of this bug since we are adding filtering
of requests by some date fields and, therefore, needed dates in a
predictable format.

To test:
1) Apply the patch
2) Ensure you have at least one ILL request created
3) Make a request to the /api/v1/illrequests endpoint
4) Observe that dates supplied for "placed_formatted" & "updated_formatted" conform to your
dateformat syspref.
5) Change your date format syspref, repeat steps 3 & 4

Signed-off-by: Magnus Enger <magnus@libriotech.no>
Works as advertised. Nice enh!
---
 Koha/REST/V1/Illrequests.pm                        |  27 +-
 koha-tmpl/intranet-tmpl/prog/css/staff-global.css  |   5 +
 .../prog/en/modules/ill/ill-requests.tt            | 329 +++++++++++++++++++--
 3 files changed, 325 insertions(+), 36 deletions(-)

diff --git a/Koha/REST/V1/Illrequests.pm b/Koha/REST/V1/Illrequests.pm
index 6e3eace..2a41987 100644
--- a/Koha/REST/V1/Illrequests.pm
+++ b/Koha/REST/V1/Illrequests.pm
@@ -21,6 +21,7 @@ use Mojo::Base 'Mojolicious::Controller';
 
 use Koha::Illrequests;
 use Koha::Libraries;
+use Koha::DateUtils qw( format_sqldatetime );
 
 =head1 NAME
 
@@ -40,6 +41,7 @@ sub list {
     my $args = $c->req->params->to_hash // {};
     my $filter;
     my $output = [];
+    my @format_dates = ( 'placed', 'updated' );
 
     # Create a hash where all keys are embedded values
     # Enables easy checking
@@ -54,18 +56,31 @@ sub list {
         $filter->{$filter_param} = \@values;
     }
 
-    my $requests = Koha::Illrequests->search($filter);
+    my @req_list = Koha::Illrequests->search($filter)->as_list;
 
     if ( scalar (keys %embed) )
     {
         # Need to embed stuff
-        my @results = map { $_->TO_JSON(\%embed) } $requests->as_list;
-        return $c->render( status => 200, openapi => \@results );
+        @req_list = map { $_->TO_JSON(\%embed) } @req_list;
     }
-    else
-    {
-        return $c->render( status => 200, openapi => $requests );
+
+    # Create new "formatted" columns for each date column
+    # that needs formatting
+    foreach(@req_list) {
+        foreach my $field(@format_dates) {
+            if (defined $_->{$field}) {
+                $_->{$field . "_formatted"} = format_sqldatetime(
+                    $_->{$field},
+                    undef,
+                    undef,
+                    1
+                );
+            }
+        }
+
     }
+
+    return $c->render( status => 200, openapi => \@req_list );
 }
 
 1;
diff --git a/koha-tmpl/intranet-tmpl/prog/css/staff-global.css b/koha-tmpl/intranet-tmpl/prog/css/staff-global.css
index c68bff6..3000f74 100644
--- a/koha-tmpl/intranet-tmpl/prog/css/staff-global.css
+++ b/koha-tmpl/intranet-tmpl/prog/css/staff-global.css
@@ -3164,6 +3164,11 @@ table#ill-requests {
     width: 100% !important;
 }
 
+#illfilter_dateplaced,
+#illfilter_datemodified {
+    width: 80%;
+}
+
 #helper span {
     display: none;
 }
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt
index 70f31ca..c34a451 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt
@@ -9,12 +9,30 @@
 [% Asset.js("lib/jquery/plugins/jquery.checkboxes.min.js") %]
 [% Asset.css("css/datatables.css") %]
 [% INCLUDE 'datatables.inc' %]
+[% INCLUDE 'calendar.inc' %]
 <script type="text/javascript">
     //<![CDATA[
     $(document).ready(function() {
 
         // Illview Datatable setup
 
+        var table;
+
+        // Filters that are active
+        var activeFilters = {};
+
+        // Date format for datepickers
+        var dateMap = {
+            dmydot: 'dd.mm.yy',
+            iso: 'yy-mm-dd',
+            metric: 'dd/mm/yy',
+            us: 'mm/dd/yy'
+        };
+        var dateFormat = dateMap['[% Koha.Preference('dateformat') %]'];
+        $('#illfilter_dateplaced, #illfilter_datemodified').datepicker(
+            'option', 'dateFormat', dateFormat
+        );
+
         // Fields we don't want to display
         var ignore = [
             'accessurl',
@@ -26,7 +44,6 @@
             'medium',
             'notesopac',
             'notesstaff',
-            'placed',
             'replied'
         ];
 
@@ -45,14 +62,149 @@
             'metadata_Author',
             'metadata_Title',
             'borrowername',
+            'patron_cardnumber',
             'biblio_id',
             'library',
             'status',
+            'placed',
+            'placed_formatted',
             'updated',
+            'updated_formatted',
             'illrequest_id',
             'action'
         ];
 
+        // Filterable columns
+        var filterable = {
+            status: {
+                prep: function(tableData, oData) {
+                    var uniques = {};
+                    tableData.forEach(function(row) {
+                        var resolvedName = getStatusName(
+                            oData[0].capabilities[row.status].name
+                        );
+                        uniques[resolvedName] = 1
+                    });
+                    Object.keys(uniques).sort().forEach(function(unique) {
+                        $('#illfilter_status').append(
+                            '<option value="' + unique  +
+                            '">' + unique +  '</option>'
+                        );
+                    });
+                },
+                listener: function() {
+                    var me = 'status';
+                    $('#illfilter_status').change(function() {
+                        var sel = $('#illfilter_status option:selected').val();
+                        if (sel && sel.length > 0) {
+                            activeFilters[me] = function() {
+                                table.column(6).search(sel);
+                            }
+                        } else {
+                            if (activeFilters.hasOwnProperty(me)) {
+                                delete activeFilters[me];
+                            }
+                        }
+                    });
+                },
+                clear: function() {
+                    $('#illfilter_status').val('');
+                }
+            },
+            pickupBranch: {
+                prep: function(tableData, oData) {
+                    var uniques = {};
+                    tableData.forEach(function(row) {
+                        uniques[row.library.branchname] = 1
+                    });
+                    Object.keys(uniques).sort().forEach(function(unique) {
+                        $('#illfilter_branchname').append(
+                            '<option value="' + unique  +
+                            '">' + unique +  '</option>'
+                        );
+                    });
+                },
+                listener: function() {
+                    var me = 'pickupBranch';
+                    $('#illfilter_branchname').change(function() {
+                        var sel = $('#illfilter_branchname option:selected').val();
+                        if (sel && sel.length > 0) {
+                            activeFilters[me] = function() {
+                                table.column(5).search(sel);
+                            }
+                        } else {
+                            if (activeFilters.hasOwnProperty(me)) {
+                                delete activeFilters[me];
+                            }
+                        }
+                    });
+                },
+                clear: function() {
+                    $('#illfilter_branchname').val('');
+                }
+            },
+            barcode: {
+                listener: function() {
+                    var me = 'barcode';
+                    $('#illfilter_barcode').change(function() {
+                        var val = $('#illfilter_barcode').val();
+                        if (val && val.length > 0) {
+                            activeFilters[me] = function() {
+                                table.column(3).search(val);
+                            }
+                        } else {
+                            if (activeFilters.hasOwnProperty(me)) {
+                                delete activeFilters[me];
+                            }
+                        }
+                    });
+                },
+                clear: function() {
+                    $('#illfilter_barcode').val('');
+                }
+            },
+            dateModified: {
+                listener: function() {
+                    var me = 'dateModified';
+                    $('#illfilter_datemodified').change(function() {
+                        var val = $('#illfilter_datemodified').val();
+                        if (val && val.length > 0) {
+                            activeFilters[me] = function() {
+                                table.column(10).search(val);
+                            }
+                        } else {
+                            if (activeFilters.hasOwnProperty(me)) {
+                                delete activeFilters[me];
+                            }
+                        }
+                    });
+                },
+                clear: function() {
+                    $('#illfilter_datemodified').val('');
+                }
+            },
+            datePlaced: {
+                listener: function() {
+                    var me = 'datePlaced';
+                    $('#illfilter_dateplaced').change(function() {
+                        var val = $('#illfilter_dateplaced').val();
+                        if (val && val.length > 0) {
+                            activeFilters[me] = function() {
+                                table.column(8).search(val);
+                            }
+                        } else {
+                            if (activeFilters.hasOwnProperty(me)) {
+                                delete activeFilters[me];
+                            }
+                        }
+                    });
+                },
+                clear: function() {
+                    $('#illfilter_dateplaced').val('');
+                }
+            }
+        };
+
         // Remove any fields we're ignoring
         var removeIgnore = function(dataObj) {
             dataObj.forEach(function(thisRow) {
@@ -132,31 +284,35 @@
                 var status_name = meta.settings.oInit.originalData[0].capabilities[
                     row.status
                 ].name;
-                switch( status_name ) {
-                    case "New request":
-                        return _("New request");
-                    case "Requested":
-                        return _("Requested");
-                    case "Requested from partners":
-                        return _("Requested from partners");
-                    case "Request reverted":
-                        return _("Request reverted");
-                    case "Queued request":
-                        return _("Queued request");
-                    case "Cancellation requested":
-                        return _("Cancellation requested");
-                    case "Completed":
-                        return _("Completed");
-                    case "Delete request":
-                        return _("Delete request");
-                    default:
-                        return status_name;
-                }
+                return getStatusName(status_name);
             } else {
                 return '';
             }
         };
 
+        var getStatusName = function(origName) {
+            switch( origName ) {
+                case "New request":
+                    return _("New request");
+                case "Requested":
+                    return _("Requested");
+                case "Requested from partners":
+                    return _("Requested from partners");
+                case "Request reverted":
+                    return _("Request reverted");
+                case "Queued request":
+                    return _("Queued request");
+                case "Cancellation requested":
+                    return _("Cancellation requested");
+                case "Completed":
+                    return _("Completed");
+                case "Delete request":
+                    return _("Delete request");
+                default:
+                    return origName;
+            }
+        };
+
         // Render function for creating a row's action link
         var createActionLink = function(data, type, row) {
             return '<a class="btn btn-default btn-sm" ' +
@@ -190,6 +346,12 @@
             library: {
                 name: _("Library"),
                 func: createLibrary
+            },
+            updated: {
+                name: _("Updated on"),
+            },
+            patron_cardnumber: {
+                name: _("Patron barcode")
             }
         };
 
@@ -240,7 +402,8 @@
                     // Create the base column object
                     var colObj = {
                         name: thisCol,
-                        className: thisCol
+                        className: thisCol,
+                        defaultContent: ''
                     };
                     // We may need to process the data going in this
                     // column, so do it if necessary
@@ -256,27 +419,90 @@
                 });
 
                 // Initialise the datatable
-                $('#ill-requests').DataTable($.extend(true, {}, dataTablesDefaults, {
-                    'aoColumnDefs': [  // Last column shouldn't be sortable or searchable
-                        {
+                table = $('#ill-requests').DataTable($.extend(true, {}, dataTablesDefaults, {
+                    'aoColumnDefs': [
+                        { // Last column shouldn't be sortable or searchable
                             'aTargets': [ 'actions' ],
                             'bSortable': false,
                             'bSearchable': false
                         },
+                        { // Hide the two date columns we use just for sorting
+                            'aTargets': [ 'placed', 'updated' ],
+                            'bVisible': false,
+                            'bSearchable': false
+                        },
+                        { // When sorting 'placed', we want to use the
+                          // unformatted column
+                          'aTargets': [ 'placed_formatted'],
+                          'iDataSort': 7
+                        },
+                        { // When sorting 'updated', we want to use the
+                          // unformatted column
+                          'aTargets': [ 'updated_formatted'],
+                          'iDataSort': 9
+                        }
                     ],
-                    'aaSorting': [[ 6, 'desc' ]], // Default sort, updated descending
+                    'aaSorting': [[ 9, 'desc' ]], // Default sort, updated descending
                     'processing': true, // Display a message when manipulating
                     'iDisplayLength': 10, // 10 results per page
                     'sPaginationType': "full_numbers", // Pagination display
                     'deferRender': true, // Improve performance on big datasets
                     'data': dataCopy,
                     'columns': colData,
-                    'originalData': data // Enable render functions to access
-                                       // our original data
+                    'originalData': data, // Enable render functions to access
+                                          // our original data
+                    'initComplete': function() {
+
+                        // Prepare any filter elements that need it
+                        for (var el in filterable) {
+                            if (filterable.hasOwnProperty(el)) {
+                                if (filterable[el].hasOwnProperty('prep')) {
+                                    filterable[el].prep(dataCopy, data);
+                                }
+                                if (filterable[el].hasOwnProperty('listener')) {
+                                    filterable[el].listener();
+                                }
+                            }
+                        }
+
+                    }
                 }));
+
             }
         );
 
+        var clearSearch = function() {
+            table.search('').columns().search('');
+            activeFilters = {};
+            for (var filter in filterable) {
+                if (
+                    filterable.hasOwnProperty(filter) &&
+                    filterable[filter].hasOwnProperty('clear')
+                ) {
+                    filterable[filter].clear();
+                }
+            }
+            table.draw();
+        };
+
+        // Apply any search filters, or clear any previous
+        // ones
+        $('#illfilter_form').submit(function(event) {
+            event.preventDefault();
+            table.search('').columns().search('');
+            for (var active in activeFilters) {
+                if (activeFilters.hasOwnProperty(active)) {
+                    activeFilters[active]();
+                }
+            }
+            table.draw();
+        });
+
+        // Clear all filters
+        $('#clear_search').click(function() {
+            clearSearch();
+        });
+
     });
     //]]>
 </script>
@@ -298,6 +524,45 @@
 
 <div id="doc3" class="yui-t2">
     <div id="bd">
+        [% IF query_type == 'illlist' %]
+        <div id="illfilter_yui_column" class="yui-b">
+            <form method="get" id="illfilter_form">
+                <fieldset class="brief">
+                    <h3>Filters</h3>
+                    <ol>
+                        <li>
+                            <label for="illfilter_status">Status:</label>
+                            <select name="illfilter_status" id="illfilter_status">
+                                <option value=""></option>
+                            </select>
+                        </li>
+                        <li>
+                            <label for="illfilter_dateplaced">Date placed:</label>
+                            <input type="text" name="illfilter_dateplaced" id="illfilter_dateplaced" class="datepicker">
+                        </li>
+                        <li>
+                            <label for="illfilter_datemodified">Updated on:</label>
+                            <input type="text" name="illfilter_datemodified" id="illfilter_datemodified" class="datepicker">
+                        </li>
+                        <li>
+                            <label for="illfilter_branchname">Library:</label>
+                            <select name="illfilter_branchname" id="illfilter_branchname">
+                                <option value=""></option>
+                            </select>
+                        </li>
+                        <li>
+                            <label for="illfilter_barcode">Patron barcode:</label>
+                            <input type="text" name="illfilter_barcode" id="illfilter_barcode">
+                        </li>
+                    </ol>
+                    <fieldset class="action">
+                        <input type="submit" value="Search" />
+                        <input type="button" value="Clear" id="clear_search" />
+                    </fieldset>
+                </fieldset>
+            </form>
+        </div>
+        [% END %]
         <div id="yui-main">
             <div id="interlibraryloans" class="yui-b">
         [% IF !backends_available %]
@@ -588,11 +853,15 @@
                                 <tr id="illview-header">
                                     <th>Author</th>
                                     <th>Title</th>
-                                    <th>Patron</th>
+                                    <th>Patron name</th>
+                                    <th>Patron barcode</th>
                                     <th>Biblio ID</th>
                                     <th>Library</th>
                                     <th>Status</th>
-                                    <th>Updated on</th>
+                                    <th class="placed">&nbsp;</th>
+                                    <th class="placed_formatted">Date placed</th>
+                                    <th class="updated">&nbsp;</th>
+                                    <th class="updated_formatted">Updated on</th>
                                     <th>Request number</th>
                                     <th class="actions"></th>
                                 </tr>
-- 
2.1.4