From e7c14876e1727b17b9de379c9134996a421a6733 Mon Sep 17 00:00:00 2001 From: Chris Hall 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 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 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 Date: Wed Nov 9 20:29:26 2011 +1300 Working on caching for services commit e0511f180cebd81747858ad776433fe3a1cf6854 Author: Chris Cormack Date: Wed Nov 9 08:28:26 2011 +1300 Starting work on webservices Signed-off-by: Chris Cormack Signed-off-by: Katrin Fischer 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.

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 %] @@ -198,16 +206,36 @@ 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

-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) %]
  • + + +
  • [% END %] + +
    @@ -221,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 %] @@ -398,6 +432,8 @@ canned reports and writing custom SQL reports.

      + +
      [% FOREACH total_b IN total_by %] @@ -428,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

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

      + +
      Save Your Custom Report
        @@ -530,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. Edit SQL
        1. +[% IF (public) %] +
        2. +[% ELSE %] +
        3. +[% END %] +[% IF (usecache) %]
        4. + + +
        5. [% END %]
        @@ -636,6 +706,7 @@ Sub report: