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 <amit.gupta@informaticsglobal.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Adding a QA follow-up.
Signed-off-by: Mason James <mtj@kohaaloha.com>

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 77c8996..dd0386f 100755 (executable)
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -30,6 +30,7 @@ use C4::Dates qw/format_date/;
 use C4::Debug;
 use C4::Branch; # XXX subfield_is_koha_internal_p
 use C4::Koha qw/IsAuthorisedValueCategory/;
+use Koha::DateUtils qw/dt_from_string output_pref/;
 
 =head1 NAME
 
@@ -735,9 +736,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);