@@ -, +, @@ interface Fixed bugs when creating reports, added more user friendly input and feedback Added caching to intranet report webservices, added cache expiry and public options to reports Working on caching for services Starting work on webservices --- 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 --- a/C4/Reports/Guided.pm +++ a/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) --- a/installer/data/mysql/updatedatabase.pl +++ a/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); } --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ a/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: