From 7bc6d2fa54e98d259d8566b8b353a4bed654b3e5 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 set adds a new colum to saved_sql 'combine_params' If set, parameters that have the same name and data type will only be asked for once from the user 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 still asked for five parameters 6 - Edit report, note the 'Combine params' options, say YES!!!!! 7 - Re-run the report, you shoudl be asked for three parameters 8 - Verify the results reflect the supplied parameters 9 - Create a new report (perhaps something more substantive) 10 - Save with 'combine params' checke 11 - View in saved reports and see the new 'combine params' column 12 - Verify it is correctly set 13 - Run the report and verify correct results 14 - prove -v t/db_dependent/Reports/Guided.t --- C4/Reports/Guided.pm | 10 +++--- ...g9634_add_combine_params_option_to_reports.perl | 7 +++++ .../en/modules/reports/guided_reports_start.tt | 27 ++++++++++++++++ reports/guided_reports.pl | 36 ++++++++++++++++++++-- t/db_dependent/Reports/Guided.t | 32 ++++++++++++++++++- 5 files changed, 105 insertions(+), 7 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug9634_add_combine_params_option_to_reports.perl diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index d61b282..51d6795 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -561,11 +561,12 @@ sub save_report { my $subgroup = $fields->{subgroup}; my $cache_expiry = $fields->{cache_expiry} || 300; my $public = $fields->{public}; + my $combine_params = $fields->{combine_params} || 0; my $dbh = C4::Context->dbh(); $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/ - my $query = "INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,report_area,report_group,report_subgroup,type,notes,cache_expiry,public) VALUES (?,now(),now(),?,?,?,?,?,?,?,?,?)"; - $dbh->do($query, undef, $borrowernumber, $sql, $name, $area, $group, $subgroup, $type, $notes, $cache_expiry, $public); + my $query = "INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,report_area,report_group,report_subgroup,type,notes,cache_expiry,public,combine_params) VALUES (?,now(),now(),?,?,?,?,?,?,?,?,?,?)"; + $dbh->do($query, undef, $borrowernumber, $sql, $name, $area, $group, $subgroup, $type, $notes, $cache_expiry, $public,$combine_params); my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef, $borrowernumber, $name); @@ -582,6 +583,7 @@ sub update_sql { my $subgroup = $fields->{subgroup}; my $cache_expiry = $fields->{cache_expiry}; my $public = $fields->{public}; + my $combine_params = $fields->{combine_params}; if( $cache_expiry >= 2592000 ){ die "Please specify a cache expiry less than 30 days\n"; @@ -589,8 +591,8 @@ sub update_sql { my $dbh = C4::Context->dbh(); $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/ - my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, report_group = ?, report_subgroup = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? "; - $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id ); + my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, report_group = ?, report_subgroup = ?, notes = ?, cache_expiry = ?, public = ?, combine_params = ? WHERE id = ? "; + $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $combine_params, $id ); } sub store_results { diff --git a/installer/data/mysql/atomicupdate/bug9634_add_combine_params_option_to_reports.perl b/installer/data/mysql/atomicupdate/bug9634_add_combine_params_option_to_reports.perl new file mode 100644 index 0000000..ac56c79 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug9634_add_combine_params_option_to_reports.perl @@ -0,0 +1,7 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + $dbh->do( "ALTER TABLE saved_sql ADD COLUMN combine_params tinyint(1) NOT NULL DEFAULT 0" ); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug XXXXX - add combine params option to reports)\n"; +} 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 1d5e9f4..84a4efb 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 @@ -165,6 +165,7 @@ canned reports and writing custom SQL reports.

Last run Public JSON URL + Combine parameters [% IF (usecache) %] Cache expiry (seconds) [% END %] Saved results [% IF has_obsolete_reports %]Update[% END %] @@ -209,6 +210,13 @@ canned reports and writing custom SQL reports.

[% Koha.Preference('staffClientBaseURL') %]/cgi-bin/koha/svc/report?id=[% savedreport.id | html %] [% END %] + + [% IF (savedreport.combine_params) %] + Yes + [% ELSE %] + No + [% END %] + [% IF (usecache) %] [% savedreport.cache_expiry %] [% END %] [% FOR result IN savedreport.results %] @@ -316,6 +324,11 @@ canned reports and writing custom SQL reports.

[% ELSE %]
  • [% END %] +[% IF (combine_params) %] +
  • +[% ELSE %] +
  • +[% END %] [% IF (usecache) %]
  • + + [% END %]
    @@ -737,6 +754,11 @@ $(document).ready(function() { [% ELSE %]
  • [% END %] +[% IF (combine_params) %] +
  • +[% ELSE %] +
  • +[% END %] [% IF (usecache) %]
  • [% ELSE %]
  • [% END %] +[% IF (combine_params) %] +
  • +[% ELSE %] +
  • +[% END %] [% IF (usecache) %]