From 9fe88868de570ecc730ea010d67e6b1c0f2e0b2b Mon Sep 17 00:00:00 2001 From: Pasi Kallinen Date: Fri, 10 Aug 2018 08:56:08 +0300 Subject: [PATCH] Bug 21200: Allow default values for saved SQL report "date" parameters Let the saved SQL report define default date values, relative to the time the report is being run, for any "date" fields. Often such date fields require entering a date eg. 6 months past or similar, and having the default value in would make it much less likely for the user to choose the wrong date. The default value is put after the "date" text, separated by a pipe. Format for the default date value is either "now", or " ", where value is a positive or negative integer value, and unit is one of "hour", "day", "week", "month", "year". Not putting in the default value would behave the same as before, leaving the date input field empty. For example: SELECT count(*) FROM items WHERE DATE(dateaccessioned) BETWEEN <> AND <> Would default to the last 6 months. Test plan: 1) Apply patch. 2) Create a new save SQL report that has one or more date fields. 3) Test the report without default date values. The date input fields should have no default values. 4) Test the report with allowed default date values, eg. "now", "-6 month", "-5 year", "1 day", etc. The date input fields should have the proper default values. 5) Test the report with disallowed default date values, eg. "NOT", "-X", "10 asdasd", etc. These are silently ignored, and the date input fields should have no default values. 6) Change your dateformat system preference and repeat 3-5 7) Test that other input fields (normal text fields, authorised value pulldowns) still work as expected. Signed-off-by: Pasi Kallinen --- .../intranet-tmpl/prog/en/includes/calendar.inc | 24 ++++++++++++++++------ .../en/modules/reports/guided_reports_start.tt | 2 +- reports/guided_reports.pl | 13 ++++++++++-- 3 files changed, 30 insertions(+), 9 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/calendar.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/calendar.inc index f13a1fa4a5..fb43e7156d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/calendar.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/calendar.inc @@ -56,19 +56,22 @@ function validate_date (dateText, inst) { } } -function Date_from_syspref(dstring) { +function Date_from_syspref(dstring, tmpdformat) { var dateX = dstring.split(/[-/.]/); + if (typeof tmpdformat === 'undefined') { + tmpdformat = dformat; + } if (debug > 1 && sentmsg < 1) {sentmsg++; alert("Date_from_syspref(" + dstring + ") splits to:\n" + dateX.join("\n"));} - if (dformat === "iso") { + if (tmpdformat === "iso") { return new Date(dateX[0], (dateX[1] - 1), dateX[2]); // YYYY-MM-DD to (YYYY,m(0-11),d) - } else if (dformat === "us") { + } else if (tmpdformat === "us") { return new Date(dateX[2], (dateX[0] - 1), dateX[1]); // MM/DD/YYYY to (YYYY,m(0-11),d) - } else if (dformat === "metric") { + } else if (tmpdformat === "metric") { return new Date(dateX[2], (dateX[1] - 1), dateX[0]); // DD/MM/YYYY to (YYYY,m(0-11),d) - } else if (dformat === "dmydot") { + } else if (tmpdformat === "dmydot") { return new Date(dateX[2], (dateX[1] - 1), dateX[0]); // DD.MM.YYYY to (YYYY,m(0-11),d) } else { - if (debug > 0) {alert("KOHA ERROR - Unrecognized date format: " +dformat);} + if (debug > 0) {alert("KOHA ERROR - Unrecognized date format: " +tmpdformat);} return 0; } } @@ -150,6 +153,15 @@ $("#dateofbirth").datepicker({ }).on("change", function(e, value) { if ( ! is_valid_date( $(this).val() ) ) {$(this).val("");} }); + + $( ".datepicker" ).each(function () { + var defval = $(this).data("defvalue"); + if (defval !== "") { + var tmpd = Date_from_syspref(defval, "iso"); + $(this).datepicker('setDate', tmpd); + } + }); + // http://jqueryui.com/demos/datepicker/#date-range var dates = $( ".datepickerfrom, .datepickerto" ).datepicker({ changeMonth: true, 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 5d0546ba98..2010d1a921 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 @@ -672,7 +672,7 @@ canned reports and writing custom SQL reports.

[% IF sql_param.input == 'date' %]
  • - +
  • [% ELSIF ( sql_param.input == 'text' ) %]
  • diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 0989dfdbd1..e365b8dfc9 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -701,19 +701,28 @@ elsif ($phase eq 'Run this report'){ my @authval_errors; my %uniq_params; for(my $i=0;$i<($#split/2);$i++) { - my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; + my ($text,$authorised_value,$def_value) = split /\|/,$split[$i*2+1]; my $sep = $authorised_value ? "|" : ""; if( defined $uniq_params{$text.$sep.$authorised_value} ){ next; } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; } my $input; my $labelid; + my $inputvalue; if ( not defined $authorised_value ) { # no authorised value input, provide a text box $input = "text"; } elsif ( $authorised_value eq "date" ) { # require a date, provide a date picker $input = 'date'; + if ( defined $def_value && ($def_value eq "now" || $def_value =~ /^([+-]?\d+) +(hour|day|week|month|year)$/) ) { # careful with the regex, we are doing explicit sql query + my ($ivalue, $interval) = ($1, $2); + ($ivalue, $interval) = (0, "hour") if ($def_value eq "now"); + my $dbh=C4::Context->dbh; + my $sth = $dbh->prepare("SELECT DATE(DATE_ADD(NOW(), INTERVAL ? ".$interval." ))"); + $sth->execute( $ivalue ); + $inputvalue = $sth->fetchrow_array(); + } } else { # defined $authorised_value, and not 'date' my $dbh=C4::Context->dbh; @@ -797,7 +806,7 @@ elsif ($phase eq 'Run this report'){ }; } - push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value }; + push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'inputvalue' => $inputvalue, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value }; } $template->param('sql' => $sql, 'name' => $name, -- 2.11.0