[Patches] [PATCH] [SIGNED-OFF] Bug 7249 : Allow reports to be called through a

koha-patchbot at kohaaloha.com koha-patchbot at kohaaloha.com
Wed Dec 21 03:10:53 NZDT 2011


From: Chris Hall <chrish at catalyst.net.nz>
Date: Mon, 21 Nov 2011 16:09:19 +1300
Subject: [PATCH] [SIGNED-OFF] Bug 7249 : Allow reports to be called through a
 restful interface

Hands back JSON, and supports caching

Squashed commit of the following:

commit 5c93506079738b9a13139417d3a0734289a4b007
Author: Chris Hall <chrish at catalyst.net.nz>
Date:   Mon Nov 21 15:41:14 2011 +1300

    Fixed bugs when creating reports, added more user friendly input and feedback

commit c62430bb358ee4af6ee5331b4d3a2ed67f723032
Author: Chris Hall <chrish at catalyst.net.nz>
Date:   Mon Nov 21 12:00:27 2011 +1300

    Added caching to intranet report webservices, added cache expiry and public options to reports

commit 2d89f0777d95b26bf08635782070b6367d0698f3
Author: Chris Cormack <chris at bigballofwax.co.nz>
Date:   Wed Nov 9 20:29:26 2011 +1300

    Working on caching for services

commit e0511f180cebd81747858ad776433fe3a1cf6854
Author: Chris Cormack <chris at bigballofwax.co.nz>
Date:   Wed Nov 9 08:28:26 2011 +1300

    Starting work on webservices

Signed-off-by: Chris Cormack <chrisc at catalyst.net.nz>
Signed-off-by: Katrin Fischer <Katrin.Fischer.83 at web.de>
Comments on follow-up.
---
 C4/Reports/Guided.pm                               |   23 +++-
 installer/data/mysql/updatedatabase.pl             |    9 +-
 .../en/modules/reports/guided_reports_start.tt     |   77 ++++++++++++-
 opac/svc/report                                    |   63 +++++++++++
 reports/guided_reports.pl                          |  117 +++++++++++++++++---
 svc/report                                         |   75 +++++++++++++
 6 files changed, 335 insertions(+), 29 deletions(-)
 create mode 100755 opac/svc/report
 create mode 100755 svc/report

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 75c6c37..8081b32 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -472,12 +472,12 @@ Returns id of the newly created report
 =cut
 
 sub save_report {
-    my ( $borrowernumber, $sql, $name, $type, $notes ) = @_;
+    my ( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ) = @_;
     my $dbh = C4::Context->dbh();
     $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
     my $query =
-"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes)  VALUES (?,now(),now(),?,?,?,?)";
-    $dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes );
+"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes,cache_expiry, public)  VALUES (?,now(),now(),?,?,?,?,?,?)";
+    $dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
     my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
                                    $borrowernumber, $name);
     return $id;
@@ -488,11 +488,19 @@ sub update_sql {
     my $sql = shift;
     my $reportname = shift;
     my $notes = shift;
+    my $cache_expiry = shift;
+    my $public = shift;
+
+    # not entirely a magic number, Cache::Memcached::Set assumed any expiry >= (60*60*24*30) is an absolute unix timestamp (rather than relative seconds)
+    if( $cache_expiry >= 2592000 ){
+      die "Please specify a cache expiry less than 30 days\n";
+    }
+
     my $dbh = C4::Context->dbh();
     $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
-    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ? WHERE id = ? ";
+    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
     my $sth = $dbh->prepare($query);
-    $sth->execute( $sql, $reportname, $notes, $id );
+    $sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id );
     $sth->finish();
 }
 
@@ -559,7 +567,8 @@ sub get_saved_reports {
     my $query = "SELECT saved_sql.id, report_id, report,
                         date_run, date_created, last_modified, savedsql, last_run,
                         report_name, type, notes,
-                        borrowernumber, surname as borrowersurname, firstname as borrowerfirstname
+                        borrowernumber, surname as borrowersurname, firstname as borrowerfirstname,
+                        cache_expiry, public
                  FROM saved_sql 
                  LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
                  LEFT OUTER JOIN borrowers USING (borrowernumber)";
@@ -603,7 +612,7 @@ sub get_saved_report {
     my $sth   = $dbh->prepare($query);
     $sth->execute($id);
     my $data = $sth->fetchrow_hashref();
-    return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'} );
+    return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'} );
 }
 
 =item create_compound($masterID,$subreportID)
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index d2b4225..67c8f47 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -4552,8 +4552,13 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
 }
 $DBversion = "3.06.00.xxx";
 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
-    $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('usecache',0,'If on pages with caching enabled will use caching',NULL,'YesNo')");
-    print "Upgradet to $DBversion done (Added syspref usecache, When this preference is turned on pages with with caching support will use caching) \n";
+    $dbh->do("ALTER TABLE saved_sql
+          ADD (
+            cache_expiry INT NOT NULL DEFAULT 300,
+            public BOOLEAN NOT NULL DEFAULT FALSE
+          );
+    ");
+    print "Upgradet to $DBversion done (Added cache_expiry and pulic fields in saved_reports table.) \n";
     SetVersion ($DBversion);
 }
 
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 3836a02..1a351ee 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
@@ -137,6 +137,8 @@ canned reports and writing custom SQL reports.</p>
   <th>Notes</th>
   <th>Author</th>
   <th>Creation Date</th>
+  <th>Public</th>
+  [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
   <th>Saved Results</th>
   <th>Saved SQL</th>
   <th> </th>
@@ -152,6 +154,12 @@ canned reports and writing custom SQL reports.</p>
 <td>[% savedreport.notes %]</td>
 <td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
 <td>[% savedreport.date_created %]</td>
+[% IF (savedreport.public) %]
+<td>True</td>
+[% ELSE %]
+<td>False</td>
+[% END %]
+[% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
 <td>[% IF ( savedreport.date_run ) %]<a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=[% savedreport.id %]">[% savedreport.date_run %]</a>[% END %]
 </td>
     <td>
@@ -198,16 +206,36 @@ canned reports and writing custom SQL reports.</p>
 
 
 [% IF ( build1 ) %]
+[% IF ( cache_error) %]
+<div class="dialog alert">
+<b> Please choose a cache_expiry less than 30 days </b>
+</div>
+[% END %]
 <h1>Build A Report</h1>
 <form action="/cgi-bin/koha/reports/guided_reports.pl">
 <fieldset class="rows">
-<legend>Step 1 of 6: Choose a Module to Report on</legend>
+<legend>Step 1 of 6: Choose a Module to Report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
 <ol><li><label for="areas">Choose: </label><select name="areas" id="areas">
 [% FOREACH area IN areas %]
 <option value="[% area.id %]">[% area.name %]</option>
 [% END %]
-</select></li></ol>
-</fieldset>
+</select></li>
+[% IF (public) %]
+  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">False (default)</option> <option value="1" selected="selected">True</public> </select></li>
+[% ELSE %]
+  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selected">False (default)</option> <option value="1">True</public> </select></li>
+[% END %]
+[% IF (usecache) %] <li>
+<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
+<select id="cache_expiry_units" name="cache_expiry_units">
+<option value="seconds">Seconds (default)</option>
+<option value="minutes">Minutes</option>
+<option value="hours">Hours</option>
+<option value="days">Days</option>
+</select>
+</li>[% END %]
+</ol>
+<ofieldset>
 <fieldset class="action">
 <input type="hidden" name="phase" value="Report on this Area" />
 <input type="submit" name="submit" value="Next >>" />
@@ -221,6 +249,8 @@ canned reports and writing custom SQL reports.</p>
 <h1>Build A Report</h1>
 <form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
 <input type="hidden" name="area" value="[% area %]" />
+<input type="hidden" name="public" value="[% public %]" />
+<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
 <fieldset class="rows"><legend>Step 2 of 6: Pick a Report Type</legend>
 <ol><li><label for="types">Choose: </label>
     <select id="types" name="types">
@@ -254,6 +284,8 @@ canned reports and writing custom SQL reports.</p>
 <form id="column_submit" action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
     <input type="hidden" name="area" value="[% area %]" />
     <input type="hidden" name="type" value="[% type %]" />
+    <input type="hidden" name="public" value="[% public %]" />
+    <input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
     <fieldset>
 <div class="yui-g">
 <div class="yui-u first">	<div style="float: left;"><select id="availableColumns" name="oldcolumns2" multiple="multiple" size="25" style="min-width: 200px;height:300px;">
@@ -302,6 +334,8 @@ canned reports and writing custom SQL reports.</p>
     <input type="hidden" name="area" value="[% area %]" />
     <input type="hidden" name="type" value="[% type %]" />
     <input type="hidden" name="column" value="[% column %]" />
+    <input type="hidden" name="public" value="[% public %]" />
+    <input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
     <fieldset><legend>Step 4 of 6: Select Criteria to Limit on</legend>
     <table>
         [% FOREACH criteri IN criteria %]
@@ -398,6 +432,8 @@ canned reports and writing custom SQL reports.</p>
 <input type="hidden" name="column" value="[% column %]" />
 <input type="hidden" name="definition" value="[% definition %]" />
 <input type="hidden" name="criteria" value="[% criteriastring %]" />
+<input type="hidden" name="public" value="[% public %]" />
+<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
 <fieldset><table>
 [% FOREACH total_b IN total_by %]
 <tr><td><input type="checkbox" name="total_by" id="[% total_b.name %]" value="[% total_b.name %]" /> <label for="[% total_b.name %]">[% total_b.name %]</label></td>
@@ -428,6 +464,8 @@ canned reports and writing custom SQL reports.</p>
 <input type="hidden" name="criteria" value="[% criteriastring %]" />
 <input type="hidden" name="definition" value="[% definition %]" />
 <input type="hidden" name="totals" value="[% totals %]" />
+<input type="hidden" name="public" value="[% public %]" />
+<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
 <fieldset><table>[% FOREACH order_b IN order_by %]
 <tr><td><input type="checkbox" id="[% order_b.name %]" name="order_by" value="[% order_b.name %]" /> <label for="[% order_b.name %]">[% order_b.name %]</label></td><td>
 <select name="[% order_b.name %]_ovalue">
@@ -458,6 +496,8 @@ canned reports and writing custom SQL reports.</p>
 <form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
 <input type="hidden" name="sql" value="[% sql %]" />
 <input type="hidden" name="type" value="[% type %]" />
+<input type="hidden" name="public" value="[% public %]" />
+<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
 <p>You will need to save the report before you can execute it</p>
 <fieldset class="action"><input type="hidden" name="phase" value="Save" />  
 <input type="submit" name="submit" value="Save" />  </fieldset>
@@ -468,6 +508,8 @@ canned reports and writing custom SQL reports.</p>
 <form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
 <input type="hidden" name="sql" value="[% sql |html %]" />
 <input type="hidden" name="type" value="[% type %]" />
+<input type="hidden" name="public" value="[% public %]" />
+<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
 <fieldset class="rows">
 <legend>Save Your Custom Report</legend>
 <ol>
@@ -530,6 +572,20 @@ canned reports and writing custom SQL reports.</p>
         [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
         [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
     </li>
+[% IF (public) %]
+  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">False (default)</option> <option value="1" selected="selected">True</public> </select></li>
+[% ELSE %]
+  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selected">False (default)</option> <option value="1">True</public> </select></li>
+[% END %]
+[% IF (usecache) %] <li>
+<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
+<select id="cache_expiry_units" name="cache_expiry_units">
+<option value="seconds" selected="selected">Seconds (default)</option>
+<option value="minutes">Minutes</option>
+<option value="hours">Hours</option>
+<option value="days">Days</option>
+</select>
+</li>[% END %]
     <li><label for="notes">Notes:</label> <textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
     <li><label for="types">Type:</label>
         <select id="types" name="types">
@@ -600,6 +656,20 @@ Sub report:<select name="subreport">
 <legend>Edit SQL</legend>
 <ol>
 <li><label for="reportname">Report Name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" /></li>
+[% IF (public) %]
+  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">False (default)</option> <option value="1" selected="selected">True</public> </select></li>
+[% ELSE %]
+  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selelcted">False (default)</option> <option value="1">True</public> </select></li>
+[% END %]
+[% IF (usecache) %] <li>
+<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
+<select id="cache_expiry_units" name="cache_expiry_units">
+<option value="seconds">Seconds (default)</option>
+<option value="minutes">Minutes</option>
+<option value="hours">Hours</option>
+<option value="days">Days</option>
+</select>
+</li>[% END %]
 <li><label for="notes">Notes:</label><textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
 <li><textarea id="sql" name="sql" rows="10" cols="60">[% sql %]</textarea></li>
 </ol>
@@ -636,6 +706,7 @@ Sub report:<select name="subreport">
     <br />Use of this keyword is not allowed in Koha reports due to security and data integrity risks. Only SELECT queries are allowed.
     <br />Please return to the "Saved Reports" screen and delete this report or retry creating a new one.
     [% ELSIF ( error.queryerr ) %]The database returned the following error: <br />[% error.queryerr %]<br />Please check the log for further details.
+    [% ELSIF ( error.cache_expiry ) %]Please select a cache expiry less than 30 days.
     [% ELSE %]
     [% END %]
 [% END %]
diff --git a/opac/svc/report b/opac/svc/report
new file mode 100755
index 0000000..d51e72d
--- /dev/null
+++ b/opac/svc/report
@@ -0,0 +1,63 @@
+#!/usr/bin/perl
+
+# Copyright 2011 Chris Cormack <chris at bigballofwax.co.nz>
+#
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# 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
+#
+
+use strict;
+use warnings;
+
+use C4::Reports::Guided;
+use JSON;
+use CGI;
+
+my $query = CGI->new();
+my $report=$query->param('id');
+
+my $cache;
+my $usecache=C4::Context->preference('usecache');
+
+my ($sql, $type, $name, $notes, $cache_expiry, $public) = get_saved_report($report);
+die "Sorry this report is not public\n" unless $public;
+
+if ($usecache){
+    require Koha::Cache;
+    Koha::Cache->import();
+    $cache = Koha::Cache->new ( {'cache_type' => 'memcached',
+				 'cache_servers' => C4::Context->config("memcached_servers")
+				});
+    my $namespace = C4::Context->config("memcached_namespace");
+    my $page = $cache->get_from_cache("$namespace:opac:report:$report");
+    if ($page){
+	print $query->header;
+	print $page;
+	exit;
+    }
+}    
+
+print $query->header;
+my $offset=0;
+my $limit=10;
+my ($sth, $errors) = execute_query($sql, $offset, $limit);
+my $lines = $sth->fetchall_arrayref;
+my $json_text   = to_json($lines);
+print $json_text;
+
+if ($usecache){
+    my $namespace = C4::Context->config("memcached_namespace");
+    $cache->set_in_cache("$namespace:opac:report:$report", $json_text, $cache_expiry);
+}
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index f5667a2..cb02a30 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -40,6 +40,7 @@ Script to control the guided report creation
 =cut
 
 my $input = new CGI;
+my $usecache = C4::Context->preference('usecache');
 
 my $phase = $input->param('phase');
 my $flagsrequired;
@@ -84,7 +85,7 @@ 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
@@ -92,6 +93,7 @@ elsif ( $phase eq 'Use saved' ) {
     $template->param(
         'saved1' => 1,
         'savedreports' => get_saved_reports($filter),
+        'usecache' => $usecache,
     );
     if ($filter) {
         while ( my ($k, $v) = each %$filter ) {
@@ -122,12 +124,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,
     );
 }
@@ -137,7 +142,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};
     }
@@ -151,7 +176,7 @@ 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,
             'id'                    => $id,
@@ -173,17 +198,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');
@@ -193,11 +242,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');
@@ -211,6 +261,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'),
     );
 }
 
@@ -255,7 +308,6 @@ elsif ( $phase eq 'Choose these criteria' ) {
         }
 	}
     }
-
     $template->param(
         'build5'         => 1,
         'area'           => $area,
@@ -263,6 +315,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
@@ -303,6 +358,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
@@ -352,7 +409,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'),
     );
 }
 
@@ -363,7 +422,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'),
     );
 }
 
@@ -373,6 +434,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};
     }
@@ -386,10 +467,12 @@ elsif ( $phase eq 'Save Report' ) {
             'reportname'=> $name,
             'type'      => $type,
             'notes'     => $notes,
+            'cache_expiry' => $cache_expiry,
+            'public'    => $public,
         );
     }
     else {
-        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes );
+        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
         $template->param(
             'save_successful'       => 1,
             'id'                    => $id,
@@ -593,7 +676,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'){
diff --git a/svc/report b/svc/report
new file mode 100755
index 0000000..b36fe52
--- /dev/null
+++ b/svc/report
@@ -0,0 +1,75 @@
+#!/usr/bin/perl
+
+# Copyright 2011 Chris Cormack <chris at bigballofwax.co.nz>
+#
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# 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
+#
+
+use strict;
+use warnings;
+
+use C4::Auth;
+use C4::Reports::Guided;
+use JSON;
+use CGI;
+
+my $query = CGI->new();
+my $report=$query->param('id');
+
+my $cache;
+my $usecache = C4::Context->preference('usecache');
+
+my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
+  {
+    template_name   => "intranet-main.tmpl",
+    query           => $query,
+    type            => "intranet",
+    authnotrequired => 0,
+    flagsrequired   => {
+      catalogue => 1,
+    },
+  }
+);
+
+if ($usecache){
+  require Koha::Cache;
+  Koha::Cache->import();
+  $cache = Koha::Cache->new ( {'cache_type' => 'memcached',
+      'cache_servers' => C4::Context->config("memcached_servers")
+    }); 
+  my $namespace = C4::Context->config("memcached_namespace");
+  my $page = $cache->get_from_cache("$namespace:intranet:report:$report");
+  if ($page){
+    print $query->header;
+    print $page;
+    exit;
+  }   
+}
+
+print $query->header;
+# $public isnt used for intranet
+my ($sql, $type, $name, $notes, $cache_expiry, $public) = get_saved_report($report);
+my $offset=0;
+my $limit=10;
+my ($sth, $errors) = execute_query($sql, $offset, $limit);
+my $lines = $sth->fetchall_arrayref;
+my $json_text   = to_json($lines);
+print $json_text;
+
+if ($usecache){
+  my $namespace = C4::Context->config("memcached_namespace");
+  $cache->set_in_cache("$namespace:intranet:report:$report", $json_text, $cache_expiry);
+}
-- 
1.7.5.4


More information about the Patches mailing list