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.
+ [% 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 %]
[% END %]
+ [% FOREACH key IN uniq_params.keys %]
+
+
+ [% END %]
@@ -737,6 +754,11 @@ $(document).ready(function() {
[% ELSE %]
[% END %]
+[% IF (combine_params) %]
+
+[% ELSE %]
+
+[% END %]
[% IF (usecache) %]