From f5fd919fd33b095afda9f86f619b83ff6ed78184 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

Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
---
 reports/guided_reports.pl | 45 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 45 insertions(+)

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 963e428004..b8cf9ce6f3 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -1090,3 +1090,48 @@ sub create_non_existing_group_and_subgroup {
     }
 }
 
+# pass $sth and sql_params, get back an executable query
+sub get_prepped_report {
+    my ($sql, $param_names, $sql_params ) = @_;
+
+    # First we split out the placeholders
+    # This part of the code supports using [[ table.field | alias ]] in the
+    # query and replaces it by table.field AS alias. Not sure why we would
+    # need it if we can type the latter (which is simpler)?
+    my @split = split /\[\[|\]\]/,$sql;
+    my $headers;
+    for(my $i=0;$i<$#split/2;$i++){ #The placeholders are always the odd elements of the array
+        my ($type,$name) = split /\|/,$split[$i*2+1]; # We split them on '|'
+        $headers->{$name} = $type; # Store as a lookup for the template
+        $headers->{$name} =~ s/^\w*\.//; # strip the table name just as in $sth->{NAME} array
+        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; #Quote any special characters so we can replace the placeholders
+        $name = C4::Context->dbh->quote($name);
+        $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/; # Remove placeholders from SQL
+    }
+
+    my %lookup;
+    @lookup{@$param_names} = @$sql_params;
+    @split = split /<<|>>/,$sql;
+    my @tmpl_parameters;
+    for(my $i=0;$i<$#split/2;$i++) {
+        my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$sql_params[$i];
+        # if there are special regexp chars, we must \ them
+        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
+        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
+            $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $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