From 1f7b231c7071e89f1b679d11aeb8bf45d0c5f9ea Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Wed, 9 Nov 2011 08:28:26 +1300 Subject: [PATCH 1/5] Starting work on webservices --- opac/svc/report | 38 ++++++++++++++++++++++++++++++++++++++ svc/report | 50 ++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 88 insertions(+), 0 deletions(-) create mode 100755 opac/svc/report create mode 100755 svc/report diff --git a/opac/svc/report b/opac/svc/report new file mode 100755 index 0000000..428657f --- /dev/null +++ b/opac/svc/report @@ -0,0 +1,38 @@ +#!/usr/bin/perl + +# Copyright 2011 Chris Cormack +# +# 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'); + +print $query->header; +my ($sql,$type,$name,$notes) = 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; diff --git a/svc/report b/svc/report new file mode 100755 index 0000000..e7c4925 --- /dev/null +++ b/svc/report @@ -0,0 +1,50 @@ +#!/usr/bin/perl + +# Copyright 2011 Chris Cormack +# +# 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 ( $template, $loggedinuser, $cookie ) = get_template_and_user( + { + template_name => "intranet-main.tmpl", + query => $query, + type => "intranet", + authnotrequired => 0, + flagsrequired => { + catalogue => 1, + }, + } +); +print $query->header; +my ($sql,$type,$name,$notes) = 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; -- 1.7.4.1 From c00c01a04f692976433e64fd74672aa3cd9c88c0 Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Wed, 9 Nov 2011 20:29:26 +1300 Subject: [PATCH 2/5] Working on caching for services --- opac/svc/report | 26 ++++++++++++++++++++++++++ 1 files changed, 26 insertions(+), 0 deletions(-) diff --git a/opac/svc/report b/opac/svc/report index 428657f..18215b1 100755 --- a/opac/svc/report +++ b/opac/svc/report @@ -28,6 +28,27 @@ use CGI; my $query = CGI->new(); my $report=$query->param('id'); +my $cache; +my $usecache=C4::Context->preference('usecache'); + +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 ($sql,$type,$name,$notes) = get_saved_report($report); my $offset=0; @@ -36,3 +57,8 @@ 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, 300); +} -- 1.7.4.1 From f17c2df3024ffacc4f638d817ec6f61114e96f57 Mon Sep 17 00:00:00 2001 From: Chris Hall Date: Mon, 21 Nov 2011 12:00:27 +1300 Subject: [PATCH 3/5] Added caching to intranet report webservices, added cache expiry and public options to reports --- C4/Reports/Guided.pm | 23 +++++++--- installer/data/mysql/updatedatabase.pl | 9 +++- .../en/modules/reports/guided_reports_start.tt | 25 ++++++++++- opac/svc/report | 9 ++-- reports/guided_reports.pl | 20 +++++++-- svc/report | 45 +++++++++++++++---- 6 files changed, 101 insertions(+), 30 deletions(-) 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 49ef378..5a178bb 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4551,8 +4551,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..b00443c 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.

Notes Author Creation Date + Public + [% IF (usecache) %] Cache expiry (seconds) [% END %] Saved Results Saved SQL   @@ -152,6 +154,12 @@ canned reports and writing custom SQL reports.

[% savedreport.notes %] [% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %]) [% savedreport.date_created %] +[% IF (savedreport.public) %] +True +[% ELSE %] +False +[% END %] +[% IF (usecache) %] [% savedreport.cache_expiry %] [% END %] [% IF ( savedreport.date_run ) %][% savedreport.date_run %][% END %] @@ -201,12 +209,19 @@ canned reports and writing custom SQL reports.

Build A Report

-Step 1 of 6: Choose a Module to Report on +Step 1 of 6: Choose a Module to Report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility
+ +[% IF (public) %] +
  • +[% ELSE %] +
  • +[% END %] +[% IF (usecache) %]
  • seconds
  • [% END %] +
    @@ -600,6 +615,12 @@ Sub report: +[% IF (public) %] +
  • +[% ELSE %] +
  • +[% END %] +[% IF (usecache) %]
  • seconds
  • [% END %]
  • diff --git a/opac/svc/report b/opac/svc/report index 18215b1..d51e72d 100755 --- a/opac/svc/report +++ b/opac/svc/report @@ -31,6 +31,9 @@ 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(); @@ -46,11 +49,7 @@ if ($usecache){ } } - - - print $query->header; -my ($sql,$type,$name,$notes) = get_saved_report($report); my $offset=0; my $limit=10; my ($sth, $errors) = execute_query($sql, $offset, $limit); @@ -60,5 +59,5 @@ print $json_text; if ($usecache){ my $namespace = C4::Context->config("memcached_namespace"); - $cache->set_in_cache("$namespace:opac:report:$report", $json_text, 300); + $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..d552566 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,6 +142,9 @@ 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 $public = $input->param('public'); + my @errors; if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) { push @errors, {sqlerr => $1}; @@ -151,7 +159,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, @@ -373,6 +381,8 @@ 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 $public = $input->param('public'); if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) { push @errors, {sqlerr => $1}; } @@ -386,10 +396,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, diff --git a/svc/report b/svc/report index e7c4925..b36fe52 100755 --- a/svc/report +++ b/svc/report @@ -29,22 +29,47 @@ 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, - }, - } + { + 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; -my ($sql,$type,$name,$notes) = get_saved_report($report); +# $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.4.1 From cd54c3aa2845f8e32e00d00701579d1dd308c683 Mon Sep 17 00:00:00 2001 From: Chris Hall Date: Mon, 21 Nov 2011 13:33:53 +1300 Subject: [PATCH 4/5] added missing sysprefs.sql entry for usecache setting --- installer/data/mysql/sysprefs.sql | 2 +- 1 files changed, 1 insertions(+), 1 deletions(-) diff --git a/installer/data/mysql/sysprefs.sql b/installer/data/mysql/sysprefs.sql index ae2c1cb..df46ded 100755 --- a/installer/data/mysql/sysprefs.sql +++ b/installer/data/mysql/sysprefs.sql @@ -328,4 +328,4 @@ INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES(' INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OpacKohaUrl','1',"Show 'Powered by Koha' text on OPAC footer.",NULL,NULL); INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('EasyAnalyticalRecords','0','If on, display in the catalogue screens tools to easily setup analytical record relationships','','YesNo'); INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacShowRecentComments',0,'If ON a link to recent comments will appear in the OPAC masthead',NULL,'YesNo'); - +INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('usecache',0,'If on pages with caching enabled will use caching',NULL,'YesNo'); -- 1.7.4.1 From ccb8113287faab799c6411cce1f7b8865e1d52c7 Mon Sep 17 00:00:00 2001 From: Chris Hall Date: Mon, 21 Nov 2011 15:41:14 +1300 Subject: [PATCH 5/5] Fixed bugs when creating reports, added more user friendly input and feedback --- .../en/modules/reports/guided_reports_start.tt | 60 +++++++++++- reports/guided_reports.pl | 95 +++++++++++++++++--- 2 files changed, 138 insertions(+), 17 deletions(-) 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 b00443c..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 @@ -206,6 +206,11 @@ canned reports and writing custom SQL reports.

    [% IF ( build1 ) %] +[% IF ( cache_error) %] +
    + Please choose a cache_expiry less than 30 days +
    +[% END %]

    Build A Report

    @@ -218,11 +223,19 @@ canned reports and writing custom SQL reports.

    [% IF (public) %]
  • [% ELSE %] -
  • -[% END %] -[% IF (usecache) %]
  • seconds
  • [% END %] +
  • +[% END %] +[% IF (usecache) %]
  • + + +
  • [% END %] -
    +
    @@ -236,6 +249,8 @@ canned reports and writing custom SQL reports.

    Build A Report

    + +
    Step 2 of 6: Pick a Report Type
    1. + +
      + +
      Step 4 of 6: Select Criteria to Limit on [% FOREACH criteri IN criteria %] @@ -413,6 +432,8 @@ canned reports and writing custom SQL reports.

      + +
      [% FOREACH total_b IN total_by %] @@ -443,6 +464,8 @@ canned reports and writing custom SQL reports.

      + +
      [% FOREACH order_b IN order_by %]
      + +

      You will need to save the report before you can execute it

      @@ -483,6 +508,8 @@ canned reports and writing custom SQL reports.

      + +
      Save Your Custom Report
        @@ -545,6 +572,20 @@ canned reports and writing custom SQL reports.

        [% IF ( reportname ) %] [% ELSE %][% END %] +[% IF (public) %] +
      1. +[% ELSE %] +
      2. +[% END %] +[% IF (usecache) %]
      3. + + +
      4. [% END %]
      5. [% ELSE %]
      6. [% END %] -[% IF (usecache) %]
      7. seconds
      8. [% END %] +[% IF (usecache) %]
      9. + + +
      10. [% END %]
      @@ -657,6 +706,7 @@ Sub report: