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/kohastructure.sql (+2 lines)
Lines 1714-1719 CREATE TABLE saved_sql ( Link Here
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;
(-)a/installer/data/mysql/updatedatabase.pl (+13 lines)
Lines 5212-5217 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
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)
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-3 / +74 lines)
Lines 138-143 canned reports and writing custom SQL reports.</p> Link Here
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>&nbsp;</th>
145
  <th>&nbsp;</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&amp;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&amp;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 &gt;&gt;" />
242
<input type="submit" name="submit" value="Next &gt;&gt;" />
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 &quot;Saved Reports&quot; screen and delete this report or retry creating a new one.
730
    <br />Please return to the &quot;Saved Reports&quot; 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 %]
(-)a/opac/svc/report (+68 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->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
}
(-)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->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'){
(-)a/svc/report (-1 / +79 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   => { 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
}

Return to bug 7249