Attachment 115084 Details for Bug 27380 – Bug 27380: Add 'list' option to Koha report parameters

[patch] Bug 27380: Add 'list' option to Koha report parameters

Bug-27380-Add-list-option-to-Koha-report-parameter.patch (text/plain), 3.17 KB, created by Andrew Fuerste-Henry on 2021-01-12 12:37:39 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Andrew Fuerste-Henry

Created: 2021-01-12 12:37:39 UTC

Size: 3.17 KB

patch

obsolete

>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

Actions: View | Diff | Splinter Review

Attachments on bug 27380: 115044 | 115047 | 115048 | 115084 | 115085 | 116160 | 116161 | 116162 | 116163 | 116464 | 116465 | 116466 | 116818 | 116830 | 116908