From f5fd919fd33b095afda9f86f619b83ff6ed78184 Mon Sep 17 00:00:00 2001 From: Nick Clemens 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 <> 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 --- 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