From 8acf5912b3fd213b8bcb3e7ee9b47f7cb086002b Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Mon, 15 Jan 2018 14:11:29 +0000 Subject: [PATCH] Bug 9634: Allow for combining same paraneters in SQL reports This patch combines report paramters with the same name and data type (authorised value) This presereves the past ability to provide all parameters as 'sql_params' in order to preserve existing public report links To test: 1 - Create a report that takes multiple parameters, e.g.: SELECT <> AS one, <> AS two, <> as three, <> 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. i.e. 5 different values that are those of the URL. Signed-off-by: Victor Grousset Signed-off-by: Charles Farmer Signed-off-by: Katrin Fischer Signed-off-by: Jonathan Druart --- .../prog/en/includes/reports-toolbar.inc | 1 + .../modules/reports/guided_reports_start.tt | 1 + reports/guided_reports.pl | 22 ++++++++++++++----- 3 files changed, 19 insertions(+), 5 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-toolbar.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/reports-toolbar.inc index 9b3a562c08..99ce946084 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/reports-toolbar.inc +++ b/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 %] 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 7f5d368eeb..20064b2e6c 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 @@ -663,6 +663,7 @@ canned reports and writing custom SQL reports.

    [% FOREACH sql_param IN sql_params %] + [% IF sql_param.input == 'date' %]
  1. diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 8809b81710..b5aa1d3d77 100755 --- a/reports/guided_reports.pl +++ b/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, @@ -799,7 +806,7 @@ elsif ($phase eq 'Run this report'){ 'reports' => $report_id, ); } 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 +837,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 +852,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 +1070,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*$/) { -- 2.39.5