Bug 5698: Add date picker option to SQL Runtime Parameters

[koha.git] / reports / guided_reports.pl

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 24b7878..ce9370a 100755 (executable)
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -13,16 +13,17 @@
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #
-# You should have received a copy of the GNU General Public License along with
-# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
-# Suite 330, Boston, MA  02111-1307 USA
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
 use strict;
-# use warnings;  # FIXME
+#use warnings; FIXME - Bug 2505
 use CGI;
 use Text::CSV;
+use URI::Escape;
 use C4::Reports::Guided;
-use C4::Auth;
+use C4::Auth qw/:DEFAULT get_session/;
 use C4::Output;
 use C4::Dates;
 use C4::Debug;
@@ -36,19 +37,17 @@ guided_reports.pl
 
 Script to control the guided report creation
 
-=over2
-
 =cut
 
 my $input = new CGI;
 
 my $phase = $input->param('phase');
 my $flagsrequired;
-if ( $phase eq 'Build new' ) {
-    $flagsrequired = 'create_report';
+if ( $phase eq 'Build new' or $phase eq 'Delete Saved' ) {
+    $flagsrequired = 'create_reports';
 }
 elsif ( $phase eq 'Use saved' ) {
-    $flagsrequired = 'execute_report';
+    $flagsrequired = 'execute_reports';
 } else {
     $flagsrequired = '*';
 }
@@ -63,8 +62,21 @@ my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
         debug           => 1,
     }
 );
+my $session = $cookie ? get_session($cookie->value) : undef;
+
+my $filter;
+if ( $input->param("filter_set") ) {
+    $filter = {};
+    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/;
+    $session->param('report_filter', $filter) if $session;
+    $template->param( 'filter_set' => 1 );
+}
+elsif ($session) {
+    $filter = $session->param('report_filter');
+}
 
-    my @errors = ();
+
+my @errors = ();
 if ( !$phase ) {
     $template->param( 'start' => 1 );
     # show welcome page
@@ -77,8 +89,15 @@ elsif ( $phase eq 'Build new' ) {
 elsif ( $phase eq 'Use saved' ) {
     # use a saved report
     # get list of reports and display them
-    $template->param( 'saved1' => 1 );
-    $template->param( 'savedreports' => get_saved_reports() ); 
+    $template->param(
+        'saved1' => 1,
+        'savedreports' => get_saved_reports($filter),
+    );
+    if ($filter) {
+        while ( my ($k, $v) = each %$filter ) {
+            $template->param( "filter_$k" => $v ) if $v;
+        }
+    }
 }
 
 elsif ( $phase eq 'Delete Saved') {
@@ -135,6 +154,7 @@ elsif ( $phase eq 'Update SQL'){
         update_sql( $id, $sql, $reportname, $notes );
         $template->param(
             'save_successful'       => 1,
+            'id'                    => $id,
         );
     }
     
@@ -234,7 +254,6 @@ elsif ( $phase eq 'Choose these criteria' ) {
             $query_criteria .= " AND $crit='$value'";
         }
        }
-       warn $query_criteria;
     }
 
     $template->param(
@@ -370,9 +389,10 @@ elsif ( $phase eq 'Save Report' ) {
         );
     }
     else {
-        save_report( $borrowernumber, $sql, $name, $type, $notes );
+        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes );
         $template->param(
             'save_successful'       => 1,
+            'id'                    => $id,
         );
     }
 }
@@ -400,7 +420,21 @@ elsif ($phase eq 'Run this report'){
         for(my $i=0;$i<($#split/2);$i++) {
             my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
             my $input;
-            if ($authorised_value) {
+            if ($authorised_value eq "date") {
+               $input = qq(<input id="date_$text" type="text" value="" size="10" name="sql_params">
+                         <img id="date_picker_$text" alt="Show Calendar" src="/intranet-tmpl/prog/en/lib/calendar/cal.gif">
+                         <script type="text/javascript">
+                              Calendar.setup(
+                              {
+                                 inputField : "date_$text",
+                                 ifFormat : "%Y-%m-%d",
+                                 button : "date_picker_$text"
+                              }
+                              );
+                         </script>
+                         );
+            }
+            elsif ($authorised_value) {
                 my $dbh=C4::Context->dbh;
                 my @authorised_values;
                 my %authorised_lib;
@@ -487,7 +521,7 @@ elsif ($phase eq 'Run this report'){
             $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
         }
         my ($sth, $errors) = execute_query($sql, $offset, $limit);
-        my $total = select_2_select_count_value($sql) || 0;
+        my $total = nb_rows($sql) || 0;
         unless ($sth) {
             die "execute_query failed to return sth for report $report: $sql";
         } else {
@@ -501,7 +535,10 @@ elsif ($phase eq 'Run this report'){
         }
 
         my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
-        my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report";
+        my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&amp;phase=Run%20this%20report";
+        if (@sql_params) {
+            $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
+        }
         $template->param(
             'results' => \@rows,
             'sql'     => $sql,
@@ -516,7 +553,7 @@ elsif ($phase eq 'Run this report'){
 }
 
 elsif ($phase eq 'Export'){
-    binmode STDOUT, ':utf8';
+    binmode STDOUT, ':encoding(UTF-8)';
 
        # export results to tab separated text or CSV
        my $sql    = $input->param('sql');  # FIXME: use sql from saved report ID#, not new user-supplied SQL!
@@ -601,20 +638,8 @@ sub header_cell_loop {
     return \@headers;
 }
 
-# pass $sth, get back an array of names for the column headers
-sub header_cell_values {
-    my $sth = shift or return ();
-    return @{$sth->{NAME}};
-}
-
-# pass $sth, get back a TMPL_LOOP-able set of names for the column headers
-sub header_cell_loop {
-    my @headers = map { +{ cell => $_ } } header_cell_values (shift);
-    return \@headers;
-}
-
 foreach (1..6) {
-    $template->param('build' . $_) and $template->param(buildx => $_) and last;
+     $template->{VARS}->{'build' . $_} and $template->{VARS}->{'buildx' . $_} and last;
 }
 $template->param(   'referer' => $input->referer(),
                     'DHTMLcalendar_dateformat' => C4::Dates->DHTMLcalendar(),