X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=reports%2Fguided_reports.pl;h=8a2aada943d9e80b5c010c24f0c91e8652870b2a;hb=bf79b889f05937d5039c3246913c46c99ea3c928;hp=4be4e79859a04a4e87c9b47c0d0f37b857002ca7;hpb=89cda847a1852a0b42b79f245af57ec4ae429bd3;p=koha.git

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 4be4e79859..8a2aada943 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -21,8 +21,9 @@ use strict;
 #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,11 +37,10 @@ guided_reports.pl
 
 Script to control the guided report creation
 
-=over2
-
 =cut
 
 my $input = new CGI;
+my $usecache = C4::Context->ismemcached;
 
 my $phase = $input->param('phase');
 my $flagsrequired;
@@ -63,8 +63,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
@@ -72,13 +85,21 @@ if ( !$phase ) {
 elsif ( $phase eq 'Build new' ) {
     # build a new report
     $template->param( 'build1' => 1 );
-    $template->param( 'areas' => get_report_areas() );
+    $template->param( 'areas' => get_report_areas(), 'usecache' => $usecache, 'cache_expiry' => 300, 'public' => '0' );
 }
 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),
+        'usecache' => $usecache,
+    );
+    if ($filter) {
+        while ( my ($k, $v) = each %$filter ) {
+            $template->param( "filter_$k" => $v ) if $v;
+        }
+    }
 }
 
 elsif ( $phase eq 'Delete Saved') {
@@ -93,9 +114,12 @@ elsif ( $phase eq 'Delete Saved') {
 elsif ( $phase eq 'Show SQL'){
 	
 	my $id = $input->param('reports');
-	my $sql = get_sql($id);
+    my ($sql,$type,$reportname,$notes) = get_saved_report($id);
 	$template->param(
-		'sql' => $sql,
+        'id'      => $id,
+        'reportname' => $reportname,
+        'notes'      => $notes,
+		'sql'     => $sql,
 		'showsql' => 1,
     );
 }
@@ -103,12 +127,15 @@ elsif ( $phase eq 'Show SQL'){
 elsif ( $phase eq 'Edit SQL'){
 	
     my $id = $input->param('reports');
-    my ($sql,$type,$reportname,$notes) = get_saved_report($id);
+    my ($sql,$type,$reportname,$notes, $cache_expiry, $public) = get_saved_report($id);
     $template->param(
 	    'sql'        => $sql,
 	    'reportname' => $reportname,
         'notes'      => $notes,
         'id'         => $id,
+        'cache_expiry' => $cache_expiry,
+        'public' => $public,
+        'usecache' => $usecache,
 	    'editsql'    => 1,
     );
 }
@@ -118,7 +145,27 @@ elsif ( $phase eq 'Update SQL'){
     my $sql        = $input->param('sql');
     my $reportname = $input->param('reportname');
     my $notes      = $input->param('notes');
+    my $cache_expiry = $input->param('cache_expiry');
+    my $cache_expiry_units = $input->param('cache_expiry_units');
+    my $public = $input->param('public');
+
     my @errors;
+
+    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
+    if( $cache_expiry_units ){
+      if( $cache_expiry_units eq "minutes" ){
+        $cache_expiry *= 60;
+      } elsif( $cache_expiry_units eq "hours" ){
+        $cache_expiry *= 3600; # 60 * 60
+      } elsif( $cache_expiry_units eq "days" ){
+        $cache_expiry *= 86400; # 60 * 60 * 24
+      }
+    }
+    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
+    if( $cache_expiry >= 2592000 ){
+      push @errors, {cache_expiry => $cache_expiry};
+    }
+
     if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
         push @errors, {sqlerr => $1};
     }
@@ -132,9 +179,11 @@ elsif ( $phase eq 'Update SQL'){
         );
     }
     else {
-        update_sql( $id, $sql, $reportname, $notes );
+        update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public );
         $template->param(
             'save_successful'       => 1,
+            'reportname'            => $reportname,
+            'id'                    => $id,
         );
     }
     
@@ -153,17 +202,41 @@ elsif ($phase eq 'retrieve results') {
 }
 
 elsif ( $phase eq 'Report on this Area' ) {
-
-    # they have choosen a new report and the area to report on
-    $template->param(
-        'build2' => 1,
-        'area'   => $input->param('areas'),
-        'types'  => get_report_types(),
-    );
+    my $cache_expiry_units = $input->param('cache_expiry_units'),
+    my $cache_expiry = $input->param('cache_expiry');
+
+    # we need to handle converting units
+    if( $cache_expiry_units eq "minutes" ){
+      $cache_expiry *= 60;
+    } elsif( $cache_expiry_units eq "hours" ){
+      $cache_expiry *= 3600; # 60 * 60
+    } elsif( $cache_expiry_units eq "days" ){
+      $cache_expiry *= 86400; # 60 * 60 * 24
+    }
+    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
+    if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days
+      # report error to user
+      $template->param(
+        'cache_error' => 1,
+        'build1' => 1,
+        'areas'   => get_report_areas(),
+        'cache_expiry' => $cache_expiry,
+        'usecache' => $usecache,
+        'public' => $input->param('public'),
+      );
+    } else {
+      # they have choosen a new report and the area to report on
+      $template->param(
+          'build2' => 1,
+          'area'   => $input->param('areas'),
+          'types'  => get_report_types(),
+          'cache_expiry' => $cache_expiry,
+          'public' => $input->param('public'),
+      );
+    }
 }
 
 elsif ( $phase eq 'Choose this type' ) {
-
     # they have chosen type and area
     # get area and type and pass them to the template
     my $area = $input->param('area');
@@ -173,11 +246,12 @@ elsif ( $phase eq 'Choose this type' ) {
         'area'   => $area,
         'type'   => $type,
         columns  => get_columns($area,$input),
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
     );
 }
 
 elsif ( $phase eq 'Choose these columns' ) {
-
     # we now know type, area, and columns
     # next step is the constraints
     my $area    = $input->param('area');
@@ -191,6 +265,9 @@ elsif ( $phase eq 'Choose these columns' ) {
         'column' => $column,
         definitions => get_from_dictionary($area),
         criteria    => get_criteria($area,$input),
+        'cache_expiry' => $input->param('cache_expiry'),
+        'cache_expiry_units' => $input->param('cache_expiry_units'),
+        'public' => $input->param('public'),
     );
 }
 
@@ -235,7 +312,6 @@ elsif ( $phase eq 'Choose these criteria' ) {
         }
 	}
     }
-
     $template->param(
         'build5'         => 1,
         'area'           => $area,
@@ -243,6 +319,9 @@ elsif ( $phase eq 'Choose these criteria' ) {
         'column'         => $column,
         'definition'     => $definition,
         'criteriastring' => $query_criteria,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'cache_expiry_units' => $input->param('cache_expiry_units'),
+        'public' => $input->param('public'),
     );
 
     # get columns
@@ -262,7 +341,7 @@ elsif ( $phase eq 'Choose these criteria' ) {
     $template->param( 'total_by' => \@total_by );
 }
 
-elsif ( $phase eq 'Choose These Operations' ) {
+elsif ( $phase eq 'Choose these operations' ) {
     my $area     = $input->param('area');
     my $type     = $input->param('type');
     my $column   = $input->param('column');
@@ -283,6 +362,8 @@ elsif ( $phase eq 'Choose These Operations' ) {
         'criteriastring' => $criteria,
         'totals'         => $totals,
         'definition'     => $definition,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
     );
 
     # get columns
@@ -301,7 +382,7 @@ elsif ( $phase eq 'Choose These Operations' ) {
     $template->param( 'order_by' => \@order_by );
 }
 
-elsif ( $phase eq 'Build Report' ) {
+elsif ( $phase eq 'Build report' ) {
 
     # now we have all the info we need and can build the sql
     my $area     = $input->param('area');
@@ -332,7 +413,9 @@ elsif ( $phase eq 'Build Report' ) {
     $template->param(
         'showreport' => 1,
         'sql'        => $sql,
-        'type'       => $type
+        'type'       => $type,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
     );
 }
 
@@ -343,7 +426,9 @@ elsif ( $phase eq 'Save' ) {
     $template->param(
         'save' => 1,
         'sql'  => $sql,
-        'type' => $type
+        'type' => $type,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
     );
 }
 
@@ -353,6 +438,26 @@ elsif ( $phase eq 'Save Report' ) {
     my $name = $input->param('reportname');
     my $type = $input->param('types');
     my $notes = $input->param('notes');
+    my $cache_expiry = $input->param('cache_expiry');
+    my $cache_expiry_units = $input->param('cache_expiry_units');
+    my $public = $input->param('public');
+
+
+    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
+    if( $cache_expiry_units ){
+      if( $cache_expiry_units eq "minutes" ){
+        $cache_expiry *= 60;
+      } elsif( $cache_expiry_units eq "hours" ){
+        $cache_expiry *= 3600; # 60 * 60
+      } elsif( $cache_expiry_units eq "days" ){
+        $cache_expiry *= 86400; # 60 * 60 * 24
+      }
+    }
+    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
+    if( $cache_expiry >= 2592000 ){
+      push @errors, {cache_expiry => $cache_expiry};
+    }
+    ## FIXME this is AFTER entering a name to save the report under
     if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
         push @errors, {sqlerr => $1};
     }
@@ -366,12 +471,16 @@ elsif ( $phase eq 'Save Report' ) {
             'reportname'=> $name,
             'type'      => $type,
             'notes'     => $notes,
+            'cache_expiry' => $cache_expiry,
+            'public'    => $public,
         );
     }
     else {
-        save_report( $borrowernumber, $sql, $name, $type, $notes );
+        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
         $template->param(
             'save_successful'       => 1,
+            'reportname'            => $name,
+            'id'                    => $id,
         );
     }
 }
@@ -399,7 +508,11 @@ 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) {
+            my $labelid;
+            if ($authorised_value eq "date") {
+               $input = 'date';
+            }
+            elsif ($authorised_value) {
                 my $dbh=C4::Context->dbh;
                 my @authorised_values;
                 my %authorised_lib;
@@ -451,8 +564,11 @@ elsif ($phase eq 'Run this report'){
                         $authorised_lib{$value} = $lib;
                     }
                 }
+                $labelid = $text;
+                $labelid =~ s/\W//g;
                 $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
                     -name     => "sql_params",
+                    -id       => "sql_params_".$labelid,
                     -values   => \@authorised_values,
 #                     -default  => $value,
                     -labels   => \%authorised_lib,
@@ -463,9 +579,9 @@ elsif ($phase eq 'Run this report'){
                 );
 
             } else {
-                $input = "<input type='text' name='sql_params'/>";
+                $input = "text";
             }
-            push @tmpl_parameters, {'entry' => $text, 'input' => $input };
+            push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
         }
         $template->param('sql'         => $sql,
                         'name'         => $name,
@@ -486,7 +602,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 {
@@ -500,10 +616,14 @@ 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,
+            'id'      => $report,
             'execute' => 1,
             'name'    => $name,
             'notes'   => $notes,
@@ -515,7 +635,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!
@@ -569,7 +689,7 @@ elsif ($phase eq 'Create report from SQL') {
             'notes'         => $input->param('notes'),
         );
     }
-	$template->param('create' => 1);
+        $template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache);
 }
 
 elsif ($phase eq 'Create Compound Report'){
@@ -601,7 +721,7 @@ sub header_cell_loop {
 }
 
 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(),