@@ -, +, @@ 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 - if report is not public http://localhost/cgi-bin/koha/svc/report?id=2 fails, - if report is public, both links work - URL doesn't show new borrower immediately - correct - Turning off the usecache system preference updates data - correct - Restarting memcached updates data '/etc/init.d/memcached restart' - correct - Waiting until cache time runs out updates data - correct --- C4/Reports/Guided.pm | 23 +++- installer/data/mysql/kohastructure.sql | 2 + installer/data/mysql/updatedatabase.pl | 13 ++ .../en/modules/reports/guided_reports_start.tt | 77 ++++++++++++- opac/svc/report | 68 +++++++++++ reports/guided_reports.pl | 117 +++++++++++++++++--- svc/report | 79 +++++++++++++ 7 files changed, 352 insertions(+), 27 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/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -1714,6 +1714,8 @@ CREATE TABLE saved_sql ( `report_name` varchar(255) default NULL, `type` varchar(255) default NULL, `notes` text, + `cache_expiry` int NOT NULL default 300, + `public` boolean NOT NULL default FALSE, PRIMARY KEY (`id`), KEY boridx (`borrowernumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -5212,6 +5212,19 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE saved_sql + ADD ( + cache_expiry INT NOT NULL DEFAULT 300, + public BOOLEAN NOT NULL DEFAULT FALSE + ); + "); + print "Upgrade to $DBversion done (Added cache_expiry and public fields in +saved_reports table.)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) --- 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 @@ -138,6 +138,8 @@ canned reports and writing custom SQL reports.

Notes Author Creation date + Public + [% IF (usecache) %] Cache expiry (seconds) [% END %] Saved results Saved SQL   @@ -153,6 +155,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) %] +Yes +[% ELSE %] +No +[% END %] +[% IF (usecache) %] [% savedreport.cache_expiry %] [% END %] [% IF ( savedreport.date_run ) %][% savedreport.date_run %][% END %] @@ -199,16 +207,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 %] + +
    @@ -222,6 +250,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 %] @@ -399,6 +433,8 @@ canned reports and writing custom SQL reports.

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

      @@ -469,6 +509,8 @@ canned reports and writing custom SQL reports.

      + +
      Save your custom report
        @@ -545,6 +587,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 report
        1. +[% IF (public) %] +
        2. +[% ELSE %] +
        3. +[% END %] +[% IF (usecache) %]
        4. + + +
        5. [% END %]
        @@ -659,6 +729,7 @@ Sub report: