Bugzilla – Attachment 73549 Details for
Bug 9634
Allow for parameters re-use on SQL reports
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 9634: Allow for combining same paraneters in SQL reports
Bug-9634-Allow-for-combining-same-paraneters-in-SQ.patch (text/plain), 6.81 KB, created by
Nick Clemens (kidclamp)
on 2018-04-03 10:43:42 UTC
(
hide
)
Description:
Bug 9634: Allow for combining same paraneters in SQL reports
Filename:
MIME Type:
Creator:
Nick Clemens (kidclamp)
Created:
2018-04-03 10:43:42 UTC
Size:
6.81 KB
patch
obsolete
>From bb59da4b1c2c870bd204f8cf37e53625ca267d21 Mon Sep 17 00:00:00 2001 >From: Nick Clemens <nick@bywatersolutions.com> >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 <<cat>> AS one, <<dog|branches>> AS two, <<dog|YES_NO>> as three, > <<cat>> as four, <<dog|branches>> 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 | 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 649fbe1..4475010 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 %] > > <div class="btn-group"> >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 28e0c17..d0008ef 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 >@@ -662,6 +662,7 @@ canned reports and writing custom SQL reports.</p> > <fieldset class="rows"> > <ol> > [% FOREACH sql_param IN sql_params %] >+ <input name="param_name" value="[% sql_param.name %]" type="hidden" /> > [% IF sql_param.input == 'date' %] > <li> > <label for="date_[% sql_param_entry %][% loop.count %]">[% sql_param.entry %]:</label> <input id="date_[% sql_param_entry %][% loop.count %]" type="text" value="" size="10" name="sql_params" class="datepicker" /> >diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl >index 8809b81..b5aa1d3 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.1.4
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 9634
:
70592
|
70685
|
70735
|
70736
|
73548
|
73549
|
73691
|
73802
|
73807