From 01ec6cad9fe2cb58f18337a99d649d0841ed16c2 Mon Sep 17 00:00:00 2001 From: Mark Tompsett Date: Wed, 24 Jun 2015 12:13:46 -0400 Subject: [PATCH] Bug 14382: Non-ISO Date parameters generate empty reports. Content-Type: text/plain; charset=utf-8 The issue is that SQL expects ISO dates, but the user may wish to view dates according to the dateformat system preference. By detecting a date preference, the non-ISO dates can be converted to ISO dates before being stuffed back into the SQL query to be executed. TEST PLAN --------- 1) Add a report with date parameters. -- I used 'Holds placed in date range' from http://wiki.koha-community.org/wiki/SQL_Reports_Library 2) Set your dateformat to YYYY-MM-DD 3) Run the report -- Note the SQL reads "... BETWEEN '{date formatted in YYYY-MM-DD}'..." -- If there is supposed to be data, there is some. 4) Set your dateformat to MM/DD/YYYY 5) Run the report -- Note the SQL reads "... BETWEEN '{date formatted in MM/DD/YYYY}'..." -- If there is supposed to be data, there is none. 6) Apply patch 7) Repeat steps 2-5 -- The SQL will always read YYYY-MM-DD (ISO) format. -- The report will have data, if there is some. 8) koha qa test tools. Signed-off-by: Amit Gupta Signed-off-by: Marcel de Rooy Adding a QA follow-up. --- reports/guided_reports.pl | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 9cfa8ce..25d9929 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -33,6 +33,7 @@ use C4::Branch; # XXX subfield_is_koha_internal_p use C4::Koha qw/IsAuthorisedValueCategory GetFrameworksLoop/; use C4::Context; use C4::Log; +use Koha::DateUtils qw/dt_from_string output_pref/; =head1 NAME @@ -750,9 +751,13 @@ elsif ($phase eq 'Run this report'){ my @split = split /<<|>>/,$sql; my @tmpl_parameters; for(my $i=0;$i<$#split/2;$i++) { - my $quoted = C4::Context->dbh->quote($sql_params[$i]); + my $quoted = $sql_params[$i]; # if there are special regexp chars, we must \ them $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; + if ($split[$i*2+1] =~ /|date>>/) { + $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted; + } + $quoted = C4::Context->dbh->quote($quoted); $sql =~ s/<<$split[$i*2+1]>>/$quoted/; } my ($sth, $errors) = execute_query($sql, $offset, $limit); -- 1.7.10.4