From 17db2ef079fa01599715a8b629b9dbcea40e9fa9 Mon Sep 17 00:00:00 2001
From: Nick Clemens <nick@bywatersolutions.com>
Date: Mon, 11 Jan 2021 17:13:38 +0000
Subject: [PATCH] Bug 27380: Add 'list' option to Koha report parameters

TODO: Need to address the svc endpoints

To test:
1 - Create a 'New SQL report' like:
    SELECT * FROM items WHERE itemnumber IN <<Itemnumbers|list>>
2 - Run the report
3 - You should have a text area where you can enter various itemnumbers
4 - Enter some valid and invalid itemnumbers
5 - You get the info for the valid itemnumbers, no error for the others
6 - Test adding other params to the report and ensure things still work as expected
7 - You should be able to use the svc api to all the report, separating items with %0D in the url:
---
 C4/Reports/Guided.pm                                     |  1 +
 .../prog/en/modules/reports/guided_reports_start.tt      |  5 +++++
 reports/guided_reports.pl                                | 16 ++++++++++++++--
 3 files changed, 20 insertions(+), 2 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 8c89165e2a..7bc1504c53 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -916,6 +916,7 @@ Returns a hash containig all reserved words
 sub GetReservedAuthorisedValues {
     my %reserved_authorised_values =
             map { $_ => 1 } ( 'date',
+                              'list',
                               'branches',
                               'itemtypes',
                               'cn_source',
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
index 27859b9b5c..f9eab1453c 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
@@ -814,6 +814,11 @@
                                                 <label for="sql_params[% loop.count | html %]">[% sql_param.entry | html %]: </label>
                                                 <input id="sql_params[% loop.count | html %]" type="text" name="sql_params" />
                                             </li>
+                                        [% ELSIF ( sql_param.input == 'textarea' ) %]
+                                            <li>
+                                                <label for="sql_params[% loop.count | html %]">[% sql_param.entry | html %]: </label>
+                                                <textarea id="sql_params[% loop.count | html %]" name="sql_params" rows="5"></textarea>
+                                            </li>
                                         [% ELSE %]
                                             <li>
                                                 <label for="sql_params_[% sql_param.labelid | html %]">[% sql_param.entry | html %]:</label>
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index aab868ed19..12cc70220b 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -220,7 +220,6 @@ elsif ( $phase eq 'Update SQL'){
     my $cache_expiry_units = $input->param('cache_expiry_units');
     my $public = $input->param('public');
     my $save_anyway = $input->param('save_anyway');
-
     my @errors;
 
     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
@@ -734,6 +733,9 @@ elsif ($phase eq 'Run this report'){
                 } elsif ( $authorised_value eq "date" ) {
                     # require a date, provide a date picker
                     $input = 'date';
+                } elsif ( $authorised_value eq "list" ) {
+                    # require a list, provide a textarea
+                    $input = 'textarea';
                 } else {
                     # defined $authorised_value, and not 'date'
                     my $dbh=C4::Context->dbh;
@@ -1118,7 +1120,17 @@ sub get_prepped_report {
         if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
             $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
         }
-        $quoted = C4::Context->dbh->quote($quoted);
+        unless( $split[$i*2+1] =~ /\|\s*list\s*$/ && $quoted ){
+            $quoted = C4::Context->dbh->quote($quoted);
+        } else {
+            my @list = split /\n/, $quoted;
+            my @quoted_list;
+            foreach my $item ( @list ){
+                $item =~ s/\r//;
+              push @quoted_list, C4::Context->dbh->quote($item);
+            }
+            $quoted="(".join(",",@quoted_list).")";
+        }
         $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
     }
     return $sql,$headers;
-- 
2.11.0