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

(-)a/C4/Reports/Guided.pm (-7 / +16 lines)
Lines 472-483 Returns id of the newly created report Link Here
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 >= (60*60*24*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)
(-)a/installer/data/mysql/updatedatabase.pl (-2 / +7 lines)
Lines 4551-4558 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
4551
}
4551
}
4552
$DBversion = "3.06.00.xxx";
4552
$DBversion = "3.06.00.xxx";
4553
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4553
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4554
    $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('usecache',0,'If on pages with caching enabled will use caching',NULL,'YesNo')");
4554
    $dbh->do("ALTER TABLE saved_sql
4555
    print "Upgradet to $DBversion done (Added syspref usecache, When this preference is turned on pages with with caching support will use caching) \n";
4555
          ADD (
4556
            cache_expiry INT NOT NULL DEFAULT 300,
4557
            public BOOLEAN NOT NULL DEFAULT FALSE
4558
          );
4559
    ");
4560
    print "Upgradet to $DBversion done (Added cache_expiry and pulic fields in saved_reports table.) \n";
4556
    SetVersion ($DBversion);
4561
    SetVersion ($DBversion);
4557
}
4562
}
4558
4563
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-3 / +74 lines)
Lines 137-142 canned reports and writing custom SQL reports.</p> Link Here
137
  <th>Notes</th>
137
  <th>Notes</th>
138
  <th>Author</th>
138
  <th>Author</th>
139
  <th>Creation Date</th>
139
  <th>Creation Date</th>
140
  <th>Public</th>
141
  [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
140
  <th>Saved Results</th>
142
  <th>Saved Results</th>
141
  <th>Saved SQL</th>
143
  <th>Saved SQL</th>
142
  <th>&nbsp;</th>
144
  <th>&nbsp;</th>
Lines 152-157 canned reports and writing custom SQL reports.</p> Link Here
152
<td>[% savedreport.notes %]</td>
154
<td>[% savedreport.notes %]</td>
153
<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
155
<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
154
<td>[% savedreport.date_created %]</td>
156
<td>[% savedreport.date_created %]</td>
157
[% IF (savedreport.public) %]
158
<td>True</td>
159
[% ELSE %]
160
<td>False</td>
161
[% END %]
162
[% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
155
<td>[% IF ( savedreport.date_run ) %]<a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&amp;id=[% savedreport.id %]">[% savedreport.date_run %]</a>[% END %]
163
<td>[% IF ( savedreport.date_run ) %]<a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&amp;id=[% savedreport.id %]">[% savedreport.date_run %]</a>[% END %]
156
</td>
164
</td>
157
    <td>
165
    <td>
Lines 198-213 canned reports and writing custom SQL reports.</p> Link Here
198
206
199
207
200
[% IF ( build1 ) %]
208
[% IF ( build1 ) %]
209
[% IF ( cache_error) %]
210
<div class="dialog alert">
211
<b> Please choose a cache_expiry less than 30 days </b>
212
</div>
213
[% END %]
201
<h1>Build A Report</h1>
214
<h1>Build A Report</h1>
202
<form action="/cgi-bin/koha/reports/guided_reports.pl">
215
<form action="/cgi-bin/koha/reports/guided_reports.pl">
203
<fieldset class="rows">
216
<fieldset class="rows">
204
<legend>Step 1 of 6: Choose a Module to Report on</legend>
217
<legend>Step 1 of 6: Choose a Module to Report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
205
<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">
218
<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">
206
[% FOREACH area IN areas %]
219
[% FOREACH area IN areas %]
207
<option value="[% area.id %]">[% area.name %]</option>
220
<option value="[% area.id %]">[% area.name %]</option>
208
[% END %]
221
[% END %]
209
</select></li></ol>
222
</select></li>
210
</fieldset>
223
[% IF (public) %]
224
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">False (default)</option> <option value="1" selected="selected">True</public> </select></li>
225
[% ELSE %]
226
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selected">False (default)</option> <option value="1">True</public> </select></li>
227
[% END %]
228
[% IF (usecache) %] <li>
229
<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
230
<select id="cache_expiry_units" name="cache_expiry_units">
231
<option value="seconds">Seconds (default)</option>
232
<option value="minutes">Minutes</option>
233
<option value="hours">Hours</option>
234
<option value="days">Days</option>
235
</select>
236
</li>[% END %]
237
</ol>
238
<ofieldset>
211
<fieldset class="action">
239
<fieldset class="action">
212
<input type="hidden" name="phase" value="Report on this Area" />
240
<input type="hidden" name="phase" value="Report on this Area" />
213
<input type="submit" name="submit" value="Next &gt;&gt;" />
241
<input type="submit" name="submit" value="Next &gt;&gt;" />
Lines 221-226 canned reports and writing custom SQL reports.</p> Link Here
221
<h1>Build A Report</h1>
249
<h1>Build A Report</h1>
222
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
250
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
223
<input type="hidden" name="area" value="[% area %]" />
251
<input type="hidden" name="area" value="[% area %]" />
252
<input type="hidden" name="public" value="[% public %]" />
253
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
224
<fieldset class="rows"><legend>Step 2 of 6: Pick a Report Type</legend>
254
<fieldset class="rows"><legend>Step 2 of 6: Pick a Report Type</legend>
225
<ol><li><label for="types">Choose: </label>
255
<ol><li><label for="types">Choose: </label>
226
    <select id="types" name="types">
256
    <select id="types" name="types">
Lines 254-259 canned reports and writing custom SQL reports.</p> Link Here
254
<form id="column_submit" action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
284
<form id="column_submit" action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
255
    <input type="hidden" name="area" value="[% area %]" />
285
    <input type="hidden" name="area" value="[% area %]" />
256
    <input type="hidden" name="type" value="[% type %]" />
286
    <input type="hidden" name="type" value="[% type %]" />
287
    <input type="hidden" name="public" value="[% public %]" />
288
    <input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
257
    <fieldset>
289
    <fieldset>
258
<div class="yui-g">
290
<div class="yui-g">
259
<div class="yui-u first">	<div style="float: left;"><select id="availableColumns" name="oldcolumns2" multiple="multiple" size="25" style="min-width: 200px;height:300px;">
291
<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 302-307 canned reports and writing custom SQL reports.</p> Link Here
302
    <input type="hidden" name="area" value="[% area %]" />
334
    <input type="hidden" name="area" value="[% area %]" />
303
    <input type="hidden" name="type" value="[% type %]" />
335
    <input type="hidden" name="type" value="[% type %]" />
304
    <input type="hidden" name="column" value="[% column %]" />
336
    <input type="hidden" name="column" value="[% column %]" />
337
    <input type="hidden" name="public" value="[% public %]" />
338
    <input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
305
    <fieldset><legend>Step 4 of 6: Select Criteria to Limit on</legend>
339
    <fieldset><legend>Step 4 of 6: Select Criteria to Limit on</legend>
306
    <table>
340
    <table>
307
        [% FOREACH criteri IN criteria %]
341
        [% FOREACH criteri IN criteria %]
Lines 398-403 canned reports and writing custom SQL reports.</p> Link Here
398
<input type="hidden" name="column" value="[% column %]" />
432
<input type="hidden" name="column" value="[% column %]" />
399
<input type="hidden" name="definition" value="[% definition %]" />
433
<input type="hidden" name="definition" value="[% definition %]" />
400
<input type="hidden" name="criteria" value="[% criteriastring %]" />
434
<input type="hidden" name="criteria" value="[% criteriastring %]" />
435
<input type="hidden" name="public" value="[% public %]" />
436
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
401
<fieldset><table>
437
<fieldset><table>
402
[% FOREACH total_b IN total_by %]
438
[% FOREACH total_b IN total_by %]
403
<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>
439
<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 428-433 canned reports and writing custom SQL reports.</p> Link Here
428
<input type="hidden" name="criteria" value="[% criteriastring %]" />
464
<input type="hidden" name="criteria" value="[% criteriastring %]" />
429
<input type="hidden" name="definition" value="[% definition %]" />
465
<input type="hidden" name="definition" value="[% definition %]" />
430
<input type="hidden" name="totals" value="[% totals %]" />
466
<input type="hidden" name="totals" value="[% totals %]" />
467
<input type="hidden" name="public" value="[% public %]" />
468
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
431
<fieldset><table>[% FOREACH order_b IN order_by %]
469
<fieldset><table>[% FOREACH order_b IN order_by %]
432
<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>
470
<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>
433
<select name="[% order_b.name %]_ovalue">
471
<select name="[% order_b.name %]_ovalue">
Lines 458-463 canned reports and writing custom SQL reports.</p> Link Here
458
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
496
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
459
<input type="hidden" name="sql" value="[% sql %]" />
497
<input type="hidden" name="sql" value="[% sql %]" />
460
<input type="hidden" name="type" value="[% type %]" />
498
<input type="hidden" name="type" value="[% type %]" />
499
<input type="hidden" name="public" value="[% public %]" />
500
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
461
<p>You will need to save the report before you can execute it</p>
501
<p>You will need to save the report before you can execute it</p>
462
<fieldset class="action"><input type="hidden" name="phase" value="Save" />  
502
<fieldset class="action"><input type="hidden" name="phase" value="Save" />  
463
<input type="submit" name="submit" value="Save" />  </fieldset>
503
<input type="submit" name="submit" value="Save" />  </fieldset>
Lines 468-473 canned reports and writing custom SQL reports.</p> Link Here
468
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
508
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
469
<input type="hidden" name="sql" value="[% sql |html %]" />
509
<input type="hidden" name="sql" value="[% sql |html %]" />
470
<input type="hidden" name="type" value="[% type %]" />
510
<input type="hidden" name="type" value="[% type %]" />
511
<input type="hidden" name="public" value="[% public %]" />
512
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
471
<fieldset class="rows">
513
<fieldset class="rows">
472
<legend>Save Your Custom Report</legend>
514
<legend>Save Your Custom Report</legend>
473
<ol>
515
<ol>
Lines 530-535 canned reports and writing custom SQL reports.</p> Link Here
530
        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
572
        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
531
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
573
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
532
    </li>
574
    </li>
575
[% IF (public) %]
576
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">False (default)</option> <option value="1" selected="selected">True</public> </select></li>
577
[% ELSE %]
578
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selected">False (default)</option> <option value="1">True</public> </select></li>
579
[% END %]
580
[% IF (usecache) %] <li>
581
<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
582
<select id="cache_expiry_units" name="cache_expiry_units">
583
<option value="seconds" selected="selected">Seconds (default)</option>
584
<option value="minutes">Minutes</option>
585
<option value="hours">Hours</option>
586
<option value="days">Days</option>
587
</select>
588
</li>[% END %]
533
    <li><label for="notes">Notes:</label> <textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
589
    <li><label for="notes">Notes:</label> <textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
534
    <li><label for="types">Type:</label>
590
    <li><label for="types">Type:</label>
535
        <select id="types" name="types">
591
        <select id="types" name="types">
Lines 600-605 Sub report:<select name="subreport"> Link Here
600
<legend>Edit SQL</legend>
656
<legend>Edit SQL</legend>
601
<ol>
657
<ol>
602
<li><label for="reportname">Report Name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" /></li>
658
<li><label for="reportname">Report Name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" /></li>
659
[% IF (public) %]
660
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">False (default)</option> <option value="1" selected="selected">True</public> </select></li>
661
[% ELSE %]
662
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0" selected="selelcted">False (default)</option> <option value="1">True</public> </select></li>
663
[% END %]
664
[% IF (usecache) %] <li>
665
<label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>
666
<select id="cache_expiry_units" name="cache_expiry_units">
667
<option value="seconds">Seconds (default)</option>
668
<option value="minutes">Minutes</option>
669
<option value="hours">Hours</option>
670
<option value="days">Days</option>
671
</select>
672
</li>[% END %]
603
<li><label for="notes">Notes:</label><textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
673
<li><label for="notes">Notes:</label><textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
604
<li><textarea id="sql" name="sql" rows="10" cols="60">[% sql %]</textarea></li>
674
<li><textarea id="sql" name="sql" rows="10" cols="60">[% sql %]</textarea></li>
605
</ol>
675
</ol>
Lines 636-641 Sub report:<select name="subreport"> Link Here
636
    <br />Use of this keyword is not allowed in Koha reports due to security and data integrity risks. Only SELECT queries are allowed.
706
    <br />Use of this keyword is not allowed in Koha reports due to security and data integrity risks. Only SELECT queries are allowed.
637
    <br />Please return to the &quot;Saved Reports&quot; screen and delete this report or retry creating a new one.
707
    <br />Please return to the &quot;Saved Reports&quot; screen and delete this report or retry creating a new one.
638
    [% ELSIF ( error.queryerr ) %]The database returned the following error: <br />[% error.queryerr %]<br />Please check the log for further details.
708
    [% ELSIF ( error.queryerr ) %]The database returned the following error: <br />[% error.queryerr %]<br />Please check the log for further details.
709
    [% ELSIF ( error.cache_expiry ) %]Please select a cache expiry less than 30 days.
639
    [% ELSE %]
710
    [% ELSE %]
640
    [% END %]
711
    [% END %]
641
[% END %]
712
[% END %]
(-)a/opac/svc/report (+63 lines)
Line 0 Link Here
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->preference('usecache');
33
34
my ($sql, $type, $name, $notes, $cache_expiry, $public) = get_saved_report($report);
35
die "Sorry this report is not public\n" unless $public;
36
37
if ($usecache){
38
    require Koha::Cache;
39
    Koha::Cache->import();
40
    $cache = Koha::Cache->new ( {'cache_type' => 'memcached',
41
				 'cache_servers' => C4::Context->config("memcached_servers")
42
				});
43
    my $namespace = C4::Context->config("memcached_namespace");
44
    my $page = $cache->get_from_cache("$namespace:opac:report:$report");
45
    if ($page){
46
	print $query->header;
47
	print $page;
48
	exit;
49
    }
50
}    
51
52
print $query->header;
53
my $offset=0;
54
my $limit=10;
55
my ($sth, $errors) = execute_query($sql, $offset, $limit);
56
my $lines = $sth->fetchall_arrayref;
57
my $json_text   = to_json($lines);
58
print $json_text;
59
60
if ($usecache){
61
    my $namespace = C4::Context->config("memcached_namespace");
62
    $cache->set_in_cache("$namespace:opac:report:$report", $json_text, $cache_expiry);
63
}
(-)a/reports/guided_reports.pl (-17 / +100 lines)
Lines 40-45 Script to control the guided report creation Link Here
40
=cut
40
=cut
41
41
42
my $input = new CGI;
42
my $input = new CGI;
43
my $usecache = C4::Context->preference('usecache');
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 122-133 elsif ( $phase eq 'Show SQL'){ Link Here
122
elsif ( $phase eq 'Edit SQL'){
124
elsif ( $phase eq 'Edit SQL'){
123
	
125
	
124
    my $id = $input->param('reports');
126
    my $id = $input->param('reports');
125
    my ($sql,$type,$reportname,$notes) = get_saved_report($id);
127
    my ($sql,$type,$reportname,$notes, $cache_expiry, $public) = get_saved_report($id);
126
    $template->param(
128
    $template->param(
127
	    'sql'        => $sql,
129
	    'sql'        => $sql,
128
	    'reportname' => $reportname,
130
	    'reportname' => $reportname,
129
        'notes'      => $notes,
131
        'notes'      => $notes,
130
        'id'         => $id,
132
        'id'         => $id,
133
        'cache_expiry' => $cache_expiry,
134
        'public' => $public,
135
        'usecache' => $usecache,
131
	    'editsql'    => 1,
136
	    'editsql'    => 1,
132
    );
137
    );
133
}
138
}
Lines 137-143 elsif ( $phase eq 'Update SQL'){ Link Here
137
    my $sql        = $input->param('sql');
142
    my $sql        = $input->param('sql');
138
    my $reportname = $input->param('reportname');
143
    my $reportname = $input->param('reportname');
139
    my $notes      = $input->param('notes');
144
    my $notes      = $input->param('notes');
145
    my $cache_expiry = $input->param('cache_expiry');
146
    my $cache_expiry_units = $input->param('cache_expiry_units');
147
    my $public = $input->param('public');
148
140
    my @errors;
149
    my @errors;
150
151
    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
152
    if( $cache_expiry_units ){
153
      if( $cache_expiry_units eq "minutes" ){
154
        $cache_expiry *= 60;
155
      } elsif( $cache_expiry_units eq "hours" ){
156
        $cache_expiry *= 3600; # 60 * 60
157
      } elsif( $cache_expiry_units eq "days" ){
158
        $cache_expiry *= 86400; # 60 * 60 * 24
159
      }
160
    }
161
    # 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
162
    if( $cache_expiry >= 2592000 ){
163
      push @errors, {cache_expiry => $cache_expiry};
164
    }
165
141
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
166
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
142
        push @errors, {sqlerr => $1};
167
        push @errors, {sqlerr => $1};
143
    }
168
    }
Lines 151-157 elsif ( $phase eq 'Update SQL'){ Link Here
151
        );
176
        );
152
    }
177
    }
153
    else {
178
    else {
154
        update_sql( $id, $sql, $reportname, $notes );
179
        update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public );
155
        $template->param(
180
        $template->param(
156
            'save_successful'       => 1,
181
            'save_successful'       => 1,
157
            'id'                    => $id,
182
            'id'                    => $id,
Lines 173-189 elsif ($phase eq 'retrieve results') { Link Here
173
}
198
}
174
199
175
elsif ( $phase eq 'Report on this Area' ) {
200
elsif ( $phase eq 'Report on this Area' ) {
176
201
    my $cache_expiry_units = $input->param('cache_expiry_units'),
177
    # they have choosen a new report and the area to report on
202
    my $cache_expiry = $input->param('cache_expiry');
178
    $template->param(
203
179
        'build2' => 1,
204
    # we need to handle converting units
180
        'area'   => $input->param('areas'),
205
    if( $cache_expiry_units eq "minutes" ){
181
        'types'  => get_report_types(),
206
      $cache_expiry *= 60;
182
    );
207
    } elsif( $cache_expiry_units eq "hours" ){
208
      $cache_expiry *= 3600; # 60 * 60
209
    } elsif( $cache_expiry_units eq "days" ){
210
      $cache_expiry *= 86400; # 60 * 60 * 24
211
    }
212
    # 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
213
    if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days
214
      # report error to user
215
      $template->param(
216
        'cache_error' => 1,
217
        'build1' => 1,
218
        'areas'   => get_report_areas(),
219
        'cache_expiry' => $cache_expiry,
220
        'usecache' => $usecache,
221
        'public' => $input->param('public'),
222
      );
223
    } else {
224
      # they have choosen a new report and the area to report on
225
      $template->param(
226
          'build2' => 1,
227
          'area'   => $input->param('areas'),
228
          'types'  => get_report_types(),
229
          'cache_expiry' => $cache_expiry,
230
          'public' => $input->param('public'),
231
      );
232
    }
183
}
233
}
184
234
185
elsif ( $phase eq 'Choose this type' ) {
235
elsif ( $phase eq 'Choose this type' ) {
186
187
    # they have chosen type and area
236
    # they have chosen type and area
188
    # get area and type and pass them to the template
237
    # get area and type and pass them to the template
189
    my $area = $input->param('area');
238
    my $area = $input->param('area');
Lines 193-203 elsif ( $phase eq 'Choose this type' ) { Link Here
193
        'area'   => $area,
242
        'area'   => $area,
194
        'type'   => $type,
243
        'type'   => $type,
195
        columns  => get_columns($area,$input),
244
        columns  => get_columns($area,$input),
245
        'cache_expiry' => $input->param('cache_expiry'),
246
        'public' => $input->param('public'),
196
    );
247
    );
197
}
248
}
198
249
199
elsif ( $phase eq 'Choose these columns' ) {
250
elsif ( $phase eq 'Choose these columns' ) {
200
201
    # we now know type, area, and columns
251
    # we now know type, area, and columns
202
    # next step is the constraints
252
    # next step is the constraints
203
    my $area    = $input->param('area');
253
    my $area    = $input->param('area');
Lines 211-216 elsif ( $phase eq 'Choose these columns' ) { Link Here
211
        'column' => $column,
261
        'column' => $column,
212
        definitions => get_from_dictionary($area),
262
        definitions => get_from_dictionary($area),
213
        criteria    => get_criteria($area,$input),
263
        criteria    => get_criteria($area,$input),
264
        'cache_expiry' => $input->param('cache_expiry'),
265
        'cache_expiry_units' => $input->param('cache_expiry_units'),
266
        'public' => $input->param('public'),
214
    );
267
    );
215
}
268
}
216
269
Lines 255-261 elsif ( $phase eq 'Choose these criteria' ) { Link Here
255
        }
308
        }
256
	}
309
	}
257
    }
310
    }
258
259
    $template->param(
311
    $template->param(
260
        'build5'         => 1,
312
        'build5'         => 1,
261
        'area'           => $area,
313
        'area'           => $area,
Lines 263-268 elsif ( $phase eq 'Choose these criteria' ) { Link Here
263
        'column'         => $column,
315
        'column'         => $column,
264
        'definition'     => $definition,
316
        'definition'     => $definition,
265
        'criteriastring' => $query_criteria,
317
        'criteriastring' => $query_criteria,
318
        'cache_expiry' => $input->param('cache_expiry'),
319
        'cache_expiry_units' => $input->param('cache_expiry_units'),
320
        'public' => $input->param('public'),
266
    );
321
    );
267
322
268
    # get columns
323
    # get columns
Lines 303-308 elsif ( $phase eq 'Choose These Operations' ) { Link Here
303
        'criteriastring' => $criteria,
358
        'criteriastring' => $criteria,
304
        'totals'         => $totals,
359
        'totals'         => $totals,
305
        'definition'     => $definition,
360
        'definition'     => $definition,
361
        'cache_expiry' => $input->param('cache_expiry'),
362
        'public' => $input->param('public'),
306
    );
363
    );
307
364
308
    # get columns
365
    # get columns
Lines 352-358 elsif ( $phase eq 'Build Report' ) { Link Here
352
    $template->param(
409
    $template->param(
353
        'showreport' => 1,
410
        'showreport' => 1,
354
        'sql'        => $sql,
411
        'sql'        => $sql,
355
        'type'       => $type
412
        'type'       => $type,
413
        'cache_expiry' => $input->param('cache_expiry'),
414
        'public' => $input->param('public'),
356
    );
415
    );
357
}
416
}
358
417
Lines 363-369 elsif ( $phase eq 'Save' ) { Link Here
363
    $template->param(
422
    $template->param(
364
        'save' => 1,
423
        'save' => 1,
365
        'sql'  => $sql,
424
        'sql'  => $sql,
366
        'type' => $type
425
        'type' => $type,
426
        'cache_expiry' => $input->param('cache_expiry'),
427
        'public' => $input->param('public'),
367
    );
428
    );
368
}
429
}
369
430
Lines 373-378 elsif ( $phase eq 'Save Report' ) { Link Here
373
    my $name = $input->param('reportname');
434
    my $name = $input->param('reportname');
374
    my $type = $input->param('types');
435
    my $type = $input->param('types');
375
    my $notes = $input->param('notes');
436
    my $notes = $input->param('notes');
437
    my $cache_expiry = $input->param('cache_expiry');
438
    my $cache_expiry_units = $input->param('cache_expiry_units');
439
    my $public = $input->param('public');
440
    
441
442
    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
443
    if( $cache_expiry_units ){
444
      if( $cache_expiry_units eq "minutes" ){
445
        $cache_expiry *= 60;
446
      } elsif( $cache_expiry_units eq "hours" ){
447
        $cache_expiry *= 3600; # 60 * 60
448
      } elsif( $cache_expiry_units eq "days" ){
449
        $cache_expiry *= 86400; # 60 * 60 * 24
450
      }
451
    }
452
    # 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
453
    if( $cache_expiry >= 2592000 ){
454
      push @errors, {cache_expiry => $cache_expiry};
455
    }
456
    ## FIXME this is AFTER entering a name to save the report under
376
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
457
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
377
        push @errors, {sqlerr => $1};
458
        push @errors, {sqlerr => $1};
378
    }
459
    }
Lines 386-395 elsif ( $phase eq 'Save Report' ) { Link Here
386
            'reportname'=> $name,
467
            'reportname'=> $name,
387
            'type'      => $type,
468
            'type'      => $type,
388
            'notes'     => $notes,
469
            'notes'     => $notes,
470
            'cache_expiry' => $cache_expiry,
471
            'public'    => $public,
389
        );
472
        );
390
    }
473
    }
391
    else {
474
    else {
392
        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes );
475
        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
393
        $template->param(
476
        $template->param(
394
            'save_successful'       => 1,
477
            'save_successful'       => 1,
395
            'id'                    => $id,
478
            'id'                    => $id,
Lines 593-599 elsif ($phase eq 'Create report from SQL') { Link Here
593
            'notes'         => $input->param('notes'),
676
            'notes'         => $input->param('notes'),
594
        );
677
        );
595
    }
678
    }
596
	$template->param('create' => 1);
679
	$template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache);
597
}
680
}
598
681
599
elsif ($phase eq 'Create Compound Report'){
682
elsif ($phase eq 'Create Compound Report'){
(-)a/svc/report (-1 / +75 lines)
Line 0 Link Here
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   => {
42
      catalogue => 1,
43
    },
44
  }
45
);
46
47
if ($usecache){
48
  require Koha::Cache;
49
  Koha::Cache->import();
50
  $cache = Koha::Cache->new ( {'cache_type' => 'memcached',
51
      'cache_servers' => C4::Context->config("memcached_servers")
52
    }); 
53
  my $namespace = C4::Context->config("memcached_namespace");
54
  my $page = $cache->get_from_cache("$namespace:intranet:report:$report");
55
  if ($page){
56
    print $query->header;
57
    print $page;
58
    exit;
59
  }   
60
}
61
62
print $query->header;
63
# $public isnt used for intranet
64
my ($sql, $type, $name, $notes, $cache_expiry, $public) = get_saved_report($report);
65
my $offset=0;
66
my $limit=10;
67
my ($sth, $errors) = execute_query($sql, $offset, $limit);
68
my $lines = $sth->fetchall_arrayref;
69
my $json_text   = to_json($lines);
70
print $json_text;
71
72
if ($usecache){
73
  my $namespace = C4::Context->config("memcached_namespace");
74
  $cache->set_in_cache("$namespace:intranet:report:$report", $json_text, $cache_expiry);
75
}

Return to bug 7249