@@ -, +, @@ 1 - Create a report that takes multiple parameters, e.g.: <> as four, <> as five 2 - Run this report, note you are asked for five parameters 3 - Apply patch 4 - Update database 5 - Run report, note you are asked for three params 6 - Verify the results reflect the supplied parameters 7 - Export the report and verify results 8 - Alter the URL and provide 5 params as sql_params and no param_name variables i.e. http://localhost:8081/cgi-bin/koha/reports/guided_reports.pl?reports=1&phase=Run+this+report&sql_params=Banana&sql_params=CPL&sql_params=0&sql_params=Orange&sql_params=LPL 9 - Verify results come out as expected --- .../prog/en/includes/reports-toolbar.inc | 1 + .../en/modules/reports/guided_reports_start.tt | 1 + reports/guided_reports.pl | 23 +++++++++++++++++----- 3 files changed, 20 insertions(+), 5 deletions(-) --- a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-toolbar.inc +++ a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-toolbar.inc @@ -50,6 +50,7 @@ [% IF ( execute ) %] [% BLOCK params %] [%- FOREACH param IN sql_params %]&sql_params=[% param %][% END %] + [%- FOREACH param_name IN param_names %]&param_name=[% param_name %][% END %] [%- END %]
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt @@ -662,6 +662,7 @@ canned reports and writing custom SQL reports.

    [% FOREACH sql_param IN sql_params %] + [% IF sql_param.input == 'date' %]
  1. --- a/reports/guided_reports.pl +++ a/reports/guided_reports.pl @@ -673,6 +673,8 @@ elsif ($phase eq 'Run this report'){ my $offset = 0; my $report_id = $input->param('reports'); my @sql_params = $input->multi_param('sql_params'); + my @param_names = $input->multi_param('param_name'); + # offset algorithm if ($input->param('page')) { $offset = ($input->param('page') - 1) * $limit; @@ -696,8 +698,13 @@ elsif ($phase eq 'Run this report'){ my @split = split /<<|>>/,$sql; my @tmpl_parameters; my @authval_errors; + my %uniq_params; for(my $i=0;$i<($#split/2);$i++) { my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; + my $sep = $authorised_value ? "|" : ""; + if( defined $uniq_params{$text.$sep.$authorised_value} ){ + next; + } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; } my $input; my $labelid; if ( not defined $authorised_value ) { @@ -789,7 +796,7 @@ elsif ($phase eq 'Run this report'){ }; } - push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid }; + push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value }; } $template->param('sql' => $sql, 'name' => $name, @@ -797,9 +804,10 @@ elsif ($phase eq 'Run this report'){ 'auth_val_errors' => \@authval_errors, 'enter_params' => 1, 'reports' => $report_id, + 'uniq_params' => \%uniq_params, ); } else { - my $sql = get_prepped_report( $sql, @sql_params ); + my $sql = get_prepped_report( $sql, \@param_names, \@sql_params); my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id ); my $total = nb_rows($sql) || 0; unless ($sth) { @@ -830,6 +838,7 @@ elsif ($phase eq 'Run this report'){ 'pagination_bar' => pagination_bar($url, $totpages, scalar $input->param('page')), 'unlimited_total' => $total, 'sql_params' => \@sql_params, + 'param_names' => \@param_names, ); } } @@ -844,12 +853,13 @@ elsif ($phase eq 'Export'){ my $report_id = $input->param('report_id'); my $report = get_saved_report($report_id); my $sql = $report->{savedsql}; + my @param_names = $input->multi_param('param_name'); my @sql_params = $input->multi_param('sql_params'); my $format = $input->param('format'); my $reportname = $input->param('reportname'); my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ; - $sql = get_prepped_report( $sql, @sql_params ); + $sql = get_prepped_report( $sql, \@param_names, \@sql_params ); my ($sth, $q_errors) = execute_query($sql); unless ($q_errors and @$q_errors) { my ( $type, $content ); @@ -1061,10 +1071,13 @@ sub create_non_existing_group_and_subgroup { # pass $sth and sql_params, get back an executable query sub get_prepped_report { - my ($sql, @sql_params ) = @_; + my ($sql, $param_names, $sql_params ) = @_; + my %lookup; + @lookup{@$param_names} = @$sql_params; my @split = split /<<|>>/,$sql; + my @tmpl_parameters; for(my $i=0;$i<$#split/2;$i++) { - my $quoted = $sql_params[$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*$/) { --