Attachment #9351 for bug #7249

View | Details | Raw Unified | Return to bug 7249
Collapse All | Expand All




=cut

sub save_report {
    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,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;

    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 = ?, cache_expiry = ?, public = ? WHERE id = ? ";
    my $sth = $dbh->prepare($query);
    $sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id );
    $sth->finish();
}


    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,
                        cache_expiry, public
                 FROM saved_sql 
                 LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
                 LEFT OUTER JOIN borrowers USING (borrowernumber)";

    my $sth   = $dbh->prepare($query);
    $sth->execute($id);
    my $data = $sth->fetchrow_hashref();
    return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'} );
}

=item create_compound($masterID,$subreportID)

Lines 1714-1719 CREATE TABLE saved_sql ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+2 lines)
1714	`report_name` varchar(255) default NULL,	1714	`report_name` varchar(255) default NULL,
1715	`type` varchar(255) default NULL,	1715	`type` varchar(255) default NULL,
1716	`notes` text,	1716	`notes` text,
		1717	`cache_expiry` int NOT NULL default 300,
		1718	`public` boolean NOT NULL default FALSE,
1717	PRIMARY KEY (`id`),	1719	PRIMARY KEY (`id`),
1718	KEY boridx (`borrowernumber`)	1720	KEY boridx (`borrowernumber`)
1719	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	1721	) ENGINE=InnoDB DEFAULT CHARSET=utf8;




    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)

Lines 138-143 canned reports and writing custom SQL reports.</p> Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-3 / +74 lines)
138	<th>Notes</th>	138	<th>Notes</th>
139	<th>Author</th>	139	<th>Author</th>
140	<th>Creation date</th>	140	<th>Creation date</th>
		141	<th>Public</th>
		142	[% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
141	<th>Saved results</th>	143	<th>Saved results</th>
142	<th>Saved SQL</th>	144	<th>Saved SQL</th>
143	<th> </th>	145	<th> </th>
Lines 153-158 canned reports and writing custom SQL reports.</p> Link Here
153	<td>[% savedreport.notes %]</td>	155	<td>[% savedreport.notes %]</td>
154	<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>	156	<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
155	<td>[% savedreport.date_created %]</td>	157	<td>[% savedreport.date_created %]</td>
		158	[% IF (savedreport.public) %]
		159	<td>Yes</td>
		160	[% ELSE %]
		161	<td>No</td>
		162	[% END %]
		163	[% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
156	<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 %]	164	<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 %]
157	</td>	165	</td>
158	<td>	166	<td>
Lines 199-214 canned reports and writing custom SQL reports.</p> Link Here
199		207
200		208
201	[% IF ( build1 ) %]	209	[% IF ( build1 ) %]
		210	[% IF ( cache_error) %]
		211	<div class="dialog alert">
		212	<b> Please choose a cache_expiry less than 30 days </b>
		213	</div>
		214	[% END %]
202	<h1>Build a report</h1>	215	<h1>Build a report</h1>
203	<form action="/cgi-bin/koha/reports/guided_reports.pl">	216	<form action="/cgi-bin/koha/reports/guided_reports.pl">
204	<fieldset class="rows">	217	<fieldset class="rows">
205	<legend>Step 1 of 6: Choose a module to report on</legend>	218	<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
206	<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">	219	<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">
207	[% FOREACH area IN areas %]	220	[% FOREACH area IN areas %]
208	<option value="[% area.id %]">[% area.name %]</option>	221	<option value="[% area.id %]">[% area.name %]</option>
209	[% END %]	222	[% END %]
210	</select></li></ol>	223	</select></li>
211	</fieldset>	224	[% IF (public) %]
		225	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
		226	[% ELSE %]
		227	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selected">No (default)</option> <option value="1">Yes</public> </select></li>
		228	[% END %]
		229	[% IF (usecache) %] <li>
		230	<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
		231	<select id="cache_expiry_units" name="cache_expiry_units">
		232	<option value="seconds">Seconds (default)</option>
		233	<option value="minutes">Minutes</option>
		234	<option value="hours">Hours</option>
		235	<option value="days">Days</option>
		236	</select>
		237	</li>[% END %]
		238	</ol>
		239	<ofieldset>
212	<fieldset class="action">	240	<fieldset class="action">
213	<input type="hidden" name="phase" value="Report on this Area" />	241	<input type="hidden" name="phase" value="Report on this Area" />
214	<input type="submit" name="submit" value="Next >>" />	242	<input type="submit" name="submit" value="Next >>" />
Lines 222-227 canned reports and writing custom SQL reports.</p> Link Here
222	<h1>Build A Report</h1>	250	<h1>Build A Report</h1>
223	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">	251	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
224	<input type="hidden" name="area" value="[% area %]" />	252	<input type="hidden" name="area" value="[% area %]" />
		253	<input type="hidden" name="public" value="[% public %]" />
		254	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
225	<fieldset class="rows"><legend>Step 2 of 6: Pick a report type</legend>	255	<fieldset class="rows"><legend>Step 2 of 6: Pick a report type</legend>
226	<ol><li><label for="types">Choose: </label>	256	<ol><li><label for="types">Choose: </label>
227	<select id="types" name="types">	257	<select id="types" name="types">
Lines 255-260 canned reports and writing custom SQL reports.</p> Link Here
255	<form id="column_submit" action="/cgi-bin/koha/reports/guided_reports.pl" method="post">	285	<form id="column_submit" action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
256	<input type="hidden" name="area" value="[% area %]" />	286	<input type="hidden" name="area" value="[% area %]" />
257	<input type="hidden" name="type" value="[% type %]" />	287	<input type="hidden" name="type" value="[% type %]" />
		288	<input type="hidden" name="public" value="[% public %]" />
		289	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
258	<fieldset>	290	<fieldset>
259	<div class="yui-g">	291	<div class="yui-g">
260	<div class="yui-u first"> <div style="float: left;"><select id="availableColumns" name="oldcolumns2" multiple="multiple" size="25" style="min-width: 200px;height:300px;">	292	<div class="yui-u first"> <div style="float: left;"><select id="availableColumns" name="oldcolumns2" multiple="multiple" size="25" style="min-width: 200px;height:300px;">
Lines 303-308 canned reports and writing custom SQL reports.</p> Link Here
303	<input type="hidden" name="area" value="[% area %]" />	335	<input type="hidden" name="area" value="[% area %]" />
304	<input type="hidden" name="type" value="[% type %]" />	336	<input type="hidden" name="type" value="[% type %]" />
305	<input type="hidden" name="column" value="[% column %]" />	337	<input type="hidden" name="column" value="[% column %]" />
		338	<input type="hidden" name="public" value="[% public %]" />
		339	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
306	<fieldset><legend>Step 4 of 6: Select criteria to limit on</legend>	340	<fieldset><legend>Step 4 of 6: Select criteria to limit on</legend>
307	<table>	341	<table>
308	[% FOREACH criteri IN criteria %]	342	[% FOREACH criteri IN criteria %]
Lines 399-404 canned reports and writing custom SQL reports.</p> Link Here
399	<input type="hidden" name="column" value="[% column %]" />	433	<input type="hidden" name="column" value="[% column %]" />
400	<input type="hidden" name="definition" value="[% definition %]" />	434	<input type="hidden" name="definition" value="[% definition %]" />
401	<input type="hidden" name="criteria" value="[% criteriastring %]" />	435	<input type="hidden" name="criteria" value="[% criteriastring %]" />
		436	<input type="hidden" name="public" value="[% public %]" />
		437	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
402	<fieldset><table>	438	<fieldset><table>
403	[% FOREACH total_b IN total_by %]	439	[% FOREACH total_b IN total_by %]
404	<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>	440	<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>
Lines 429-434 canned reports and writing custom SQL reports.</p> Link Here
429	<input type="hidden" name="criteria" value="[% criteriastring %]" />	465	<input type="hidden" name="criteria" value="[% criteriastring %]" />
430	<input type="hidden" name="definition" value="[% definition %]" />	466	<input type="hidden" name="definition" value="[% definition %]" />
431	<input type="hidden" name="totals" value="[% totals %]" />	467	<input type="hidden" name="totals" value="[% totals %]" />
		468	<input type="hidden" name="public" value="[% public %]" />
		469	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
432	<fieldset><table>[% FOREACH order_b IN order_by %]	470	<fieldset><table>[% FOREACH order_b IN order_by %]
433	<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>	471	<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>
434	<select name="[% order_b.name %]_ovalue">	472	<select name="[% order_b.name %]_ovalue">
Lines 459-464 canned reports and writing custom SQL reports.</p> Link Here
459	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">	497	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
460	<input type="hidden" name="sql" value="[% sql %]" />	498	<input type="hidden" name="sql" value="[% sql %]" />
461	<input type="hidden" name="type" value="[% type %]" />	499	<input type="hidden" name="type" value="[% type %]" />
		500	<input type="hidden" name="public" value="[% public %]" />
		501	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
462	<p>You will need to save the report before you can execute it</p>	502	<p>You will need to save the report before you can execute it</p>
463	<fieldset class="action"><input type="hidden" name="phase" value="Save" />	503	<fieldset class="action"><input type="hidden" name="phase" value="Save" />
464	<input type="submit" name="submit" value="Save" /> </fieldset>	504	<input type="submit" name="submit" value="Save" /> </fieldset>
Lines 469-474 canned reports and writing custom SQL reports.</p> Link Here
469	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">	509	<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
470	<input type="hidden" name="sql" value="[% sql \|html %]" />	510	<input type="hidden" name="sql" value="[% sql \|html %]" />
471	<input type="hidden" name="type" value="[% type %]" />	511	<input type="hidden" name="type" value="[% type %]" />
		512	<input type="hidden" name="public" value="[% public %]" />
		513	<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
472	<fieldset class="rows">	514	<fieldset class="rows">
473	<legend>Save your custom report</legend>	515	<legend>Save your custom report</legend>
474	<ol>	516	<ol>
Lines 545-550 canned reports and writing custom SQL reports.</p> Link Here
545	[% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />	587	[% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
546	[% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %]	588	[% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %]
547	</li>	589	</li>
		590	[% IF (public) %]
		591	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
		592	[% ELSE %]
		593	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selected">No (default)</option> <option value="1">Yes</public> </select></li>
		594	[% END %]
		595	[% IF (usecache) %] <li>
		596	<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
		597	<select id="cache_expiry_units" name="cache_expiry_units">
		598	<option value="seconds" selected="selected">Seconds (default)</option>
		599	<option value="minutes">Minutes</option>
		600	<option value="hours">Hours</option>
		601	<option value="days">Days</option>
		602	</select>
		603	</li>[% END %]
548	<li><label for="notes">Notes:</label> <textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>	604	<li><label for="notes">Notes:</label> <textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
549	<li><label for="types">Type:</label>	605	<li><label for="types">Type:</label>
550	<select id="types" name="types">	606	<select id="types" name="types">
Lines 622-627 Sub report:<select name="subreport"> Link Here
622	<legend>Edit SQL report</legend>	678	<legend>Edit SQL report</legend>
623	<ol>	679	<ol>
624	<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>	680	<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>
		681	[% IF (public) %]
		682	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
		683	[% ELSE %]
		684	<li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selelcted">No (default)</option> <option value="1">Yes</public> </select></li>
		685	[% END %]
		686	[% IF (usecache) %] <li>
		687	<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
		688	<select id="cache_expiry_units" name="cache_expiry_units">
		689	<option value="seconds">Seconds (default)</option>
		690	<option value="minutes">Minutes</option>
		691	<option value="hours">Hours</option>
		692	<option value="days">Days</option>
		693	</select>
		694	</li>[% END %]
625	<li><label for="notes">Notes:</label><textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>	695	<li><label for="notes">Notes:</label><textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
626	<li><textarea id="sql" name="sql" rows="10" cols="60">[% sql %]</textarea></li>	696	<li><textarea id="sql" name="sql" rows="10" cols="60">[% sql %]</textarea></li>
627	</ol>	697	</ol>
Lines 659-664 Sub report:<select name="subreport"> Link Here
659	<br />Use of this keyword is not allowed in Koha reports due to security and data integrity risks. Only SELECT queries are allowed.	729	<br />Use of this keyword is not allowed in Koha reports due to security and data integrity risks. Only SELECT queries are allowed.
660	<br />Please return to the "Saved Reports" screen and delete this report or retry creating a new one.	730	<br />Please return to the "Saved Reports" screen and delete this report or retry creating a new one.
661	[% ELSIF ( error.queryerr ) %]The database returned the following error: <br />[% error.queryerr %]<br />Please check the log for further details.	731	[% ELSIF ( error.queryerr ) %]The database returned the following error: <br />[% error.queryerr %]<br />Please check the log for further details.
		732	[% ELSIF ( error.cache_expiry ) %]Please select a cache expiry less than 30 days.
662	[% ELSE %]	733	[% ELSE %]
663	[% END %]	734	[% END %]
664	[% END %]	735	[% END %]




#!/usr/bin/perl

# Copyright 2011 Chris Cormack <chris@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->ismemcached;

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' => $ENV{'MEMCACHED_SERVERS'}
        }
    );
    my $namespace = $ENV{'MEMCACHED_NAMESPACE'} || 'koha';
    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 = $ENV{'MEMCACHED_NAMESPACE'} || 'koha';
    $cache->set_in_cache( "$namespace:opac:report:$report",
        $json_text, $cache_expiry );
}




=cut

my $input = new CGI;
my $usecache = C4::Context->ismemcached;

my $phase = $input->param('phase');
my $flagsrequired;

elsif ( $phase eq 'Build new' ) {
    # build a new report
    $template->param( 'build1' => 1 );
    $template->param( 'areas' => get_report_areas(), 'usecache' => $usecache, 'cache_expiry' => 300, 'public' => '0' );
}
elsif ( $phase eq 'Use saved' ) {
    # use a saved report

    $template->param(
        'saved1' => 1,
        'savedreports' => get_saved_reports($filter),
        'usecache' => $usecache,
    );
    if ($filter) {
        while ( my ($k, $v) = each %$filter ) {

elsif ( $phase eq 'Edit SQL'){
	
    my $id = $input->param('reports');
    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,
    );
}

    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};
    }

        );
    }
    else {
        update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public );
        $template->param(
            'save_successful'       => 1,
            'reportname'            => $reportname,

}

elsif ( $phase eq 'Report on this Area' ) {
    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');

        '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');

        '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'),
    );
}


        }
	}
    }

    $template->param(
        'build5'         => 1,
        'area'           => $area,

        '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

        'criteriastring' => $criteria,
        'totals'         => $totals,
        'definition'     => $definition,
        'cache_expiry' => $input->param('cache_expiry'),
        'public' => $input->param('public'),
    );

    # get columns

    $template->param(
        'showreport' => 1,
        'sql'        => $sql,
        'type'       => $type,
        'cache_expiry' => $input->param('cache_expiry'),
        'public' => $input->param('public'),
    );
}


    $template->param(
        'save' => 1,
        'sql'  => $sql,
        'type' => $type,
        'cache_expiry' => $input->param('cache_expiry'),
        'public' => $input->param('public'),
    );
}


    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};
    }

            'reportname'=> $name,
            'type'      => $type,
            'notes'     => $notes,
            'cache_expiry' => $cache_expiry,
            'public'    => $public,
        );
    }
    else {
        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
        $template->param(
            'save_successful'       => 1,
            'reportname'            => $name,

            'notes'         => $input->param('notes'),
        );
    }
        $template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache);
}

elsif ($phase eq 'Create Compound Report'){




#!/usr/bin/perl

# Copyright 2011 Chris Cormack <chris@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' => $ENV{'MEMCACHED_SERVERS'}
        }
    );
    my $namespace = $ENV{'MEMCACHED_NAMESPACE'} || 'koha';
    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 = $ENV{'MEMCACHED_NAMESPACE'} || 'koha';
    $cache->set_in_cache( "$namespace:intranet:report:$report",
        $json_text, $cache_expiry );
}

Return to bug 7249

Lines 5212-5217 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+13 lines)
5212	SetVersion($DBversion);	5212	SetVersion($DBversion);
5213	}	5213	}
5214		5214
		5215	$DBversion = "3.09.00.XXX";
		5216	if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
		5217	$dbh->do("ALTER TABLE saved_sql
		5218	ADD (
		5219	cache_expiry INT NOT NULL DEFAULT 300,
		5220	public BOOLEAN NOT NULL DEFAULT FALSE
		5221	);
		5222	");
		5223	print "Upgrade to $DBversion done (Added cache_expiry and public fields in
		5224	saved_reports table.)\n";
		5225	SetVersion($DBversion);
		5226	}
		5227
5215	=head1 FUNCTIONS	5228	=head1 FUNCTIONS
5216		5229
5217	=head2 TableExists($table)	5230	=head2 TableExists($table)

Lines 472-483 Returns id of the newly created report Link Here

(-)a/C4/Reports/Guided.pm (-7 / +16 lines)
472	=cut	472	=cut
473		473
474	sub save_report {	474	sub save_report {
475	my ( $borrowernumber, $sql, $name, $type, $notes ) = @_;	475	my ( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ) = @_;
476	my $dbh = C4::Context->dbh();	476	my $dbh = C4::Context->dbh();
477	$sql =~ s/(\s\;\s)$//; # removes trailing whitespace and /;/	477	$sql =~ s/(\s\;\s)$//; # removes trailing whitespace and /;/
478	my $query =	478	my $query =
479	"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes) VALUES (?,now(),now(),?,?,?,?)";	479	"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes,cache_expiry, public) VALUES (?,now(),now(),?,?,?,?,?,?)";
480	$dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes );	480	$dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
481	my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,	481	my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
482	$borrowernumber, $name);	482	$borrowernumber, $name);
483	return $id;	483	return $id;
Lines 488-498 sub update_sql { Link Here
488	my $sql = shift;	488	my $sql = shift;
489	my $reportname = shift;	489	my $reportname = shift;
490	my $notes = shift;	490	my $notes = shift;
		491	my $cache_expiry = shift;
		492	my $public = shift;
		493
		494	# not entirely a magic number, Cache::Memcached::Set assumed any expiry >= (606024*30) is an absolute unix timestamp (rather than relative seconds)
		495	if( $cache_expiry >= 2592000 ){
		496	die "Please specify a cache expiry less than 30 days\n";
		497	}
		498
491	my $dbh = C4::Context->dbh();	499	my $dbh = C4::Context->dbh();
492	$sql =~ s/(\s\;\s)$//; # removes trailing whitespace and /;/	500	$sql =~ s/(\s\;\s)$//; # removes trailing whitespace and /;/
493	my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ? WHERE id = ? ";	501	my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
494	my $sth = $dbh->prepare($query);	502	my $sth = $dbh->prepare($query);
495	$sth->execute( $sql, $reportname, $notes, $id );	503	$sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id );
496	$sth->finish();	504	$sth->finish();
497	}	505	}
498		506
Lines 559-565 sub get_saved_reports { Link Here
559	my $query = "SELECT saved_sql.id, report_id, report,	567	my $query = "SELECT saved_sql.id, report_id, report,
560	date_run, date_created, last_modified, savedsql, last_run,	568	date_run, date_created, last_modified, savedsql, last_run,
561	report_name, type, notes,	569	report_name, type, notes,
562	borrowernumber, surname as borrowersurname, firstname as borrowerfirstname	570	borrowernumber, surname as borrowersurname, firstname as borrowerfirstname,
		571	cache_expiry, public
563	FROM saved_sql	572	FROM saved_sql
564	LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id	573	LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
565	LEFT OUTER JOIN borrowers USING (borrowernumber)";	574	LEFT OUTER JOIN borrowers USING (borrowernumber)";
Lines 603-609 sub get_saved_report { Link Here
603	my $sth = $dbh->prepare($query);	612	my $sth = $dbh->prepare($query);
604	$sth->execute($id);	613	$sth->execute($id);
605	my $data = $sth->fetchrow_hashref();	614	my $data = $sth->fetchrow_hashref();
606	return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'} );	615	return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'} );
607	}	616	}
608		617
609	=item create_compound($masterID,$subreportID)	618	=item create_compound($masterID,$subreportID)

Line 0 Link Here

(-)a/opac/svc/report (+68 lines)
		1	#!/usr/bin/perl
		2
		3	# Copyright 2011 Chris Cormack <chris@bigballofwax.co.nz>
		4	#
		5	# This file is part of Koha.
		6	#
		7	# Koha is free software; you can redistribute it and/or modify it under the
		8	# terms of the GNU General Public License as published by the Free Software
		9	# Foundation; either version 2 of the License, or (at your option) any later
		10	# version.
		11	#
		12	# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
		13	# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
		14	# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
		15	#
		16	# You should have received a copy of the GNU General Public License along with
		17	# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
		18	# Suite 330, Boston, MA 02111-1307 USA
		19	#
		20
		21	use strict;
		22	use warnings;
		23
		24	use C4::Reports::Guided;
		25	use JSON;
		26	use CGI;
		27
		28	my $query = CGI->new();
		29	my $report = $query->param('id');
		30
		31	my $cache;
		32	my $usecache = C4::Context->ismemcached;
		33
		34	my ( $sql, $type, $name, $notes, $cache_expiry, $public ) =
		35	get_saved_report($report);
		36	die "Sorry this report is not public\n" unless $public;
		37
		38	if ($usecache) {
		39	require Koha::Cache;
		40	Koha::Cache->import();
		41	$cache = Koha::Cache->new(
		42	{
		43	'cache_type' => 'memcached',
		44	'cache_servers' => $ENV{'MEMCACHED_SERVERS'}
		45	}
		46	);
		47	my $namespace = $ENV{'MEMCACHED_NAMESPACE'} \|\| 'koha';
		48	my $page = $cache->get_from_cache("$namespace:opac:report:$report");
		49	if ($page) {
		50	print $query->header;
		51	print $page;
		52	exit;
		53	}
		54	}
		55
		56	print $query->header;
		57	my $offset = 0;
		58	my $limit = 10;
		59	my ( $sth, $errors ) = execute_query( $sql, $offset, $limit );
		60	my $lines = $sth->fetchall_arrayref;
		61	my $json_text = to_json($lines);
		62	print $json_text;
		63
		64	if ($usecache) {
65	my $namespace = $ENV{'MEMCACHED_NAMESPACE'} \|\| 'koha';
66	$cache->set_in_cache( "$namespace:opac:report:$report",
67	$json_text, $cache_expiry );
68	}

Lines 40-45 Script to control the guided report creation Link Here

(-)a/reports/guided_reports.pl (-17 / +100 lines)
40	=cut	40	=cut
41		41
42	my $input = new CGI;	42	my $input = new CGI;
		43	my $usecache = C4::Context->ismemcached;
43		44
44	my $phase = $input->param('phase');	45	my $phase = $input->param('phase');
45	my $flagsrequired;	46	my $flagsrequired;
Lines 84-90 if ( !$phase ) { Link Here
84	elsif ( $phase eq 'Build new' ) {	85	elsif ( $phase eq 'Build new' ) {
85	# build a new report	86	# build a new report
86	$template->param( 'build1' => 1 );	87	$template->param( 'build1' => 1 );
87	$template->param( 'areas' => get_report_areas() );	88	$template->param( 'areas' => get_report_areas(), 'usecache' => $usecache, 'cache_expiry' => 300, 'public' => '0' );
88	}	89	}
89	elsif ( $phase eq 'Use saved' ) {	90	elsif ( $phase eq 'Use saved' ) {
90	# use a saved report	91	# use a saved report
Lines 92-97 elsif ( $phase eq 'Use saved' ) { Link Here
92	$template->param(	93	$template->param(
93	'saved1' => 1,	94	'saved1' => 1,
94	'savedreports' => get_saved_reports($filter),	95	'savedreports' => get_saved_reports($filter),
		96	'usecache' => $usecache,
95	);	97	);
96	if ($filter) {	98	if ($filter) {
97	while ( my ($k, $v) = each %$filter ) {	99	while ( my ($k, $v) = each %$filter ) {
Lines 125-136 elsif ( $phase eq 'Show SQL'){ Link Here
125	elsif ( $phase eq 'Edit SQL'){	127	elsif ( $phase eq 'Edit SQL'){
126		128
127	my $id = $input->param('reports');	129	my $id = $input->param('reports');
128	my ($sql,$type,$reportname,$notes) = get_saved_report($id);	130	my ($sql,$type,$reportname,$notes, $cache_expiry, $public) = get_saved_report($id);
129	$template->param(	131	$template->param(
130	'sql' => $sql,	132	'sql' => $sql,
131	'reportname' => $reportname,	133	'reportname' => $reportname,
132	'notes' => $notes,	134	'notes' => $notes,
133	'id' => $id,	135	'id' => $id,
		136	'cache_expiry' => $cache_expiry,
		137	'public' => $public,
		138	'usecache' => $usecache,
134	'editsql' => 1,	139	'editsql' => 1,
135	);	140	);
136	}	141	}
Lines 140-146 elsif ( $phase eq 'Update SQL'){ Link Here
140	my $sql = $input->param('sql');	145	my $sql = $input->param('sql');
141	my $reportname = $input->param('reportname');	146	my $reportname = $input->param('reportname');
142	my $notes = $input->param('notes');	147	my $notes = $input->param('notes');
		148	my $cache_expiry = $input->param('cache_expiry');
		149	my $cache_expiry_units = $input->param('cache_expiry_units');
		150	my $public = $input->param('public');
		151
143	my @errors;	152	my @errors;
		153
		154	# if we have the units, then we came from creating a report from SQL and thus need to handle converting units
		155	if( $cache_expiry_units ){
		156	if( $cache_expiry_units eq "minutes" ){
		157	$cache_expiry *= 60;
		158	} elsif( $cache_expiry_units eq "hours" ){
		159	$cache_expiry = 3600; # 60 60
		160	} elsif( $cache_expiry_units eq "days" ){
		161	$cache_expiry = 86400; # 60 60 * 24
		162	}
		163	}
		164	# 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
		165	if( $cache_expiry >= 2592000 ){
		166	push @errors, {cache_expiry => $cache_expiry};
		167	}
		168
144	if ($sql =~ /;?\W?(UPDATE\|DELETE\|DROP\|INSERT\|SHOW\|CREATE)\W/i) {	169	if ($sql =~ /;?\W?(UPDATE\|DELETE\|DROP\|INSERT\|SHOW\|CREATE)\W/i) {
145	push @errors, {sqlerr => $1};	170	push @errors, {sqlerr => $1};
146	}	171	}
Lines 154-160 elsif ( $phase eq 'Update SQL'){ Link Here
154	);	179	);
155	}	180	}
156	else {	181	else {
157	update_sql( $id, $sql, $reportname, $notes );	182	update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public );
158	$template->param(	183	$template->param(
159	'save_successful' => 1,	184	'save_successful' => 1,
160	'reportname' => $reportname,	185	'reportname' => $reportname,
Lines 177-193 elsif ($phase eq 'retrieve results') { Link Here
177	}	202	}
178		203
179	elsif ( $phase eq 'Report on this Area' ) {	204	elsif ( $phase eq 'Report on this Area' ) {
180		205	my $cache_expiry_units = $input->param('cache_expiry_units'),
181	# they have choosen a new report and the area to report on	206	my $cache_expiry = $input->param('cache_expiry');
182	$template->param(	207
183	'build2' => 1,	208	# we need to handle converting units
184	'area' => $input->param('areas'),	209	if( $cache_expiry_units eq "minutes" ){
185	'types' => get_report_types(),	210	$cache_expiry *= 60;
186	);	211	} elsif( $cache_expiry_units eq "hours" ){
		212	$cache_expiry = 3600; # 60 60
		213	} elsif( $cache_expiry_units eq "days" ){
		214	$cache_expiry = 86400; # 60 60 * 24
		215	}
		216	# 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
		217	if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days
		218	# report error to user
		219	$template->param(
		220	'cache_error' => 1,
		221	'build1' => 1,
		222	'areas' => get_report_areas(),
		223	'cache_expiry' => $cache_expiry,
		224	'usecache' => $usecache,
		225	'public' => $input->param('public'),
		226	);
		227	} else {
		228	# they have choosen a new report and the area to report on
		229	$template->param(
		230	'build2' => 1,
		231	'area' => $input->param('areas'),
		232	'types' => get_report_types(),
		233	'cache_expiry' => $cache_expiry,
		234	'public' => $input->param('public'),
		235	);
		236	}
187	}	237	}
188		238
189	elsif ( $phase eq 'Choose this type' ) {	239	elsif ( $phase eq 'Choose this type' ) {
190
191	# they have chosen type and area	240	# they have chosen type and area
192	# get area and type and pass them to the template	241	# get area and type and pass them to the template
193	my $area = $input->param('area');	242	my $area = $input->param('area');
Lines 197-207 elsif ( $phase eq 'Choose this type' ) { Link Here
197	'area' => $area,	246	'area' => $area,
198	'type' => $type,	247	'type' => $type,
199	columns => get_columns($area,$input),	248	columns => get_columns($area,$input),
		249	'cache_expiry' => $input->param('cache_expiry'),
		250	'public' => $input->param('public'),
200	);	251	);
201	}	252	}
202		253
203	elsif ( $phase eq 'Choose these columns' ) {	254	elsif ( $phase eq 'Choose these columns' ) {
204
205	# we now know type, area, and columns	255	# we now know type, area, and columns
206	# next step is the constraints	256	# next step is the constraints
207	my $area = $input->param('area');	257	my $area = $input->param('area');
Lines 215-220 elsif ( $phase eq 'Choose these columns' ) { Link Here
215	'column' => $column,	265	'column' => $column,
216	definitions => get_from_dictionary($area),	266	definitions => get_from_dictionary($area),
217	criteria => get_criteria($area,$input),	267	criteria => get_criteria($area,$input),
		268	'cache_expiry' => $input->param('cache_expiry'),
		269	'cache_expiry_units' => $input->param('cache_expiry_units'),
		270	'public' => $input->param('public'),
218	);	271	);
219	}	272	}
220		273
Lines 259-265 elsif ( $phase eq 'Choose these criteria' ) { Link Here
259	}	312	}
260	}	313	}
261	}	314	}
262
263	$template->param(	315	$template->param(
264	'build5' => 1,	316	'build5' => 1,
265	'area' => $area,	317	'area' => $area,
Lines 267-272 elsif ( $phase eq 'Choose these criteria' ) { Link Here
267	'column' => $column,	319	'column' => $column,
268	'definition' => $definition,	320	'definition' => $definition,
269	'criteriastring' => $query_criteria,	321	'criteriastring' => $query_criteria,
		322	'cache_expiry' => $input->param('cache_expiry'),
		323	'cache_expiry_units' => $input->param('cache_expiry_units'),
		324	'public' => $input->param('public'),
270	);	325	);
271		326
272	# get columns	327	# get columns
Lines 307-312 elsif ( $phase eq 'Choose These Operations' ) { Link Here
307	'criteriastring' => $criteria,	362	'criteriastring' => $criteria,
308	'totals' => $totals,	363	'totals' => $totals,
309	'definition' => $definition,	364	'definition' => $definition,
		365	'cache_expiry' => $input->param('cache_expiry'),
		366	'public' => $input->param('public'),
310	);	367	);
311		368
312	# get columns	369	# get columns
Lines 356-362 elsif ( $phase eq 'Build Report' ) { Link Here
356	$template->param(	413	$template->param(
357	'showreport' => 1,	414	'showreport' => 1,
358	'sql' => $sql,	415	'sql' => $sql,
359	'type' => $type	416	'type' => $type,
		417	'cache_expiry' => $input->param('cache_expiry'),
		418	'public' => $input->param('public'),
360	);	419	);
361	}	420	}
362		421
Lines 367-373 elsif ( $phase eq 'Save' ) { Link Here
367	$template->param(	426	$template->param(
368	'save' => 1,	427	'save' => 1,
369	'sql' => $sql,	428	'sql' => $sql,
370	'type' => $type	429	'type' => $type,
		430	'cache_expiry' => $input->param('cache_expiry'),
		431	'public' => $input->param('public'),
371	);	432	);
372	}	433	}
373		434
Lines 377-382 elsif ( $phase eq 'Save Report' ) { Link Here
377	my $name = $input->param('reportname');	438	my $name = $input->param('reportname');
378	my $type = $input->param('types');	439	my $type = $input->param('types');
379	my $notes = $input->param('notes');	440	my $notes = $input->param('notes');
		441	my $cache_expiry = $input->param('cache_expiry');
		442	my $cache_expiry_units = $input->param('cache_expiry_units');
		443	my $public = $input->param('public');
		444
		445
		446	# if we have the units, then we came from creating a report from SQL and thus need to handle converting units
		447	if( $cache_expiry_units ){
		448	if( $cache_expiry_units eq "minutes" ){
		449	$cache_expiry *= 60;
		450	} elsif( $cache_expiry_units eq "hours" ){
		451	$cache_expiry = 3600; # 60 60
		452	} elsif( $cache_expiry_units eq "days" ){
		453	$cache_expiry = 86400; # 60 60 * 24
		454	}
		455	}
		456	# 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
		457	if( $cache_expiry >= 2592000 ){
		458	push @errors, {cache_expiry => $cache_expiry};
		459	}
		460	## FIXME this is AFTER entering a name to save the report under
380	if ($sql =~ /;?\W?(UPDATE\|DELETE\|DROP\|INSERT\|SHOW\|CREATE)\W/i) {	461	if ($sql =~ /;?\W?(UPDATE\|DELETE\|DROP\|INSERT\|SHOW\|CREATE)\W/i) {
381	push @errors, {sqlerr => $1};	462	push @errors, {sqlerr => $1};
382	}	463	}
Lines 390-399 elsif ( $phase eq 'Save Report' ) { Link Here
390	'reportname'=> $name,	471	'reportname'=> $name,
391	'type' => $type,	472	'type' => $type,
392	'notes' => $notes,	473	'notes' => $notes,
		474	'cache_expiry' => $cache_expiry,
		475	'public' => $public,
393	);	476	);
394	}	477	}
395	else {	478	else {
396	my $id = save_report( $borrowernumber, $sql, $name, $type, $notes );	479	my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
397	$template->param(	480	$template->param(
398	'save_successful' => 1,	481	'save_successful' => 1,
399	'reportname' => $name,	482	'reportname' => $name,
Lines 602-608 elsif ($phase eq 'Create report from SQL') { Link Here
602	'notes' => $input->param('notes'),	685	'notes' => $input->param('notes'),
603	);	686	);
604	}	687	}
605	$template->param('create' => 1);	688	$template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache);
606	}	689	}
607		690
608	elsif ($phase eq 'Create Compound Report'){	691	elsif ($phase eq 'Create Compound Report'){

Line 0 Link Here

(-)a/svc/report (-1 / +79 lines)
0	-	1	#!/usr/bin/perl
		2
		3	# Copyright 2011 Chris Cormack <chris@bigballofwax.co.nz>
		4	#
		5	# This file is part of Koha.
		6	#
		7	# Koha is free software; you can redistribute it and/or modify it under the
		8	# terms of the GNU General Public License as published by the Free Software
		9	# Foundation; either version 2 of the License, or (at your option) any later
		10	# version.
		11	#
		12	# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
		13	# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
		14	# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
		15	#
		16	# You should have received a copy of the GNU General Public License along with
		17	# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
		18	# Suite 330, Boston, MA 02111-1307 USA
		19	#
		20
		21	use strict;
		22	use warnings;
		23
		24	use C4::Auth;
		25	use C4::Reports::Guided;
		26	use JSON;
		27	use CGI;
		28
		29	my $query = CGI->new();
		30	my $report = $query->param('id');
		31
		32	my $cache;
		33	my $usecache = C4::Context->preference('usecache');
		34
		35	my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
		36	{
		37	template_name => "intranet-main.tmpl",
		38	query => $query,
		39	type => "intranet",
		40	authnotrequired => 0,
		41	flagsrequired => { catalogue => 1, },
		42	}
		43	);
		44
		45	if ($usecache) {
		46	require Koha::Cache;
		47	Koha::Cache->import();
		48	$cache = Koha::Cache->new(
		49	{
		50	'cache_type' => 'memcached',
		51	'cache_servers' => $ENV{'MEMCACHED_SERVERS'}
		52	}
		53	);
		54	my $namespace = $ENV{'MEMCACHED_NAMESPACE'} \|\| 'koha';
		55	my $page = $cache->get_from_cache("$namespace:intranet:report:$report");
		56	if ($page) {
		57	print $query->header;
		58	print $page;
		59	exit;
		60	}
		61	}
		62
		63	print $query->header;
		64
		65	# $public isnt used for intranet
66	my ( $sql, $type, $name, $notes, $cache_expiry, $public ) =
67	get_saved_report($report);
68	my $offset = 0;
69	my $limit = 10;
70	my ( $sth, $errors ) = execute_query( $sql, $offset, $limit );
71	my $lines = $sth->fetchall_arrayref;
72	my $json_text = to_json($lines);
73	print $json_text;
74
75	if ($usecache) {
76	my $namespace = $ENV{'MEMCACHED_NAMESPACE'} \|\| 'koha';
77	$cache->set_in_cache( "$namespace:intranet:report:$report",
78	$json_text, $cache_expiry );
79	}