From 01fe4aea32829dccb6bb71b4d69e1a603a9fdbb1 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. 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. Signed-off-by: Tomas Cohen Arazi (cherry picked from commit c3fea53039a6c53c766b0403eedd57f644c6f772) Signed-off-by: Chris Cormack Conflicts: reports/guided_reports.pl --- reports/guided_reports.pl | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 5d8b30242e..0fbd8c532f 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -31,6 +31,9 @@ use C4::Dates qw/format_date/; use C4::Debug; 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 @@ -746,9 +749,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); -- 2.39.5