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

(-)a/opac/svc/report (+38 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
print $query->header;
32
my ($sql,$type,$name,$notes) = get_saved_report($report);
33
my $offset=0;
34
my $limit=10;
35
my ($sth, $errors) = execute_query($sql, $offset, $limit);
36
my $lines = $sth->fetchall_arrayref;
37
my $json_text   = to_json($lines);
38
print $json_text;
(-)a/svc/report (-2 / +50 lines)
Line 0 Link Here
0
- 
1
#!/usr/bin/perl
1
--
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 ( $template, $loggedinuser, $cookie ) = get_template_and_user(
33
    {
34
        template_name   => "intranet-main.tmpl",
35
        query           => $query,
36
        type            => "intranet",
37
        authnotrequired => 0,
38
        flagsrequired   => {
39
            catalogue => 1,
40
        },
41
    }
42
);
43
print $query->header;
44
my ($sql,$type,$name,$notes) = get_saved_report($report);
45
my $offset=0;
46
my $limit=10;
47
my ($sth, $errors) = execute_query($sql, $offset, $limit);
48
my $lines = $sth->fetchall_arrayref;
49
my $json_text   = to_json($lines);
50
print $json_text;
2
opac/svc/report |   26 ++++++++++++++++++++++++++
51
opac/svc/report |   26 ++++++++++++++++++++++++++
3
1 files changed, 26 insertions(+), 0 deletions(-)
52
1 files changed, 26 insertions(+), 0 deletions(-)
(-)a/opac/svc/report (-2 / +26 lines)
Lines 28-33 use CGI; Link Here
28
my $query = CGI->new();
28
my $query = CGI->new();
29
my $report=$query->param('id');
29
my $report=$query->param('id');
30
30
31
my $cache;
32
my $usecache=C4::Context->preference('usecache');
33
34
if ($usecache){
35
    require Koha::Cache;
36
    Koha::Cache->import();
37
    $cache = Koha::Cache->new ( {'cache_type' => 'memcached',
38
				 'cache_servers' => C4::Context->config("memcached_servers")
39
				});
40
    my $namespace = C4::Context->config("memcached_namespace");
41
    my $page = $cache->get_from_cache("$namespace:opac:report:$report");
42
    if ($page){
43
	print $query->header;
44
	print $page;
45
	exit;
46
    }
47
}    
48
49
50
51
31
print $query->header;
52
print $query->header;
32
my ($sql,$type,$name,$notes) = get_saved_report($report);
53
my ($sql,$type,$name,$notes) = get_saved_report($report);
33
my $offset=0;
54
my $offset=0;
Lines 36-38 my ($sth, $errors) = execute_query($sql, $offset, $limit); Link Here
36
my $lines = $sth->fetchall_arrayref;
57
my $lines = $sth->fetchall_arrayref;
37
my $json_text   = to_json($lines);
58
my $json_text   = to_json($lines);
38
print $json_text;
59
print $json_text;
39
- 
60
40
--
61
if ($usecache){
62
    my $namespace = C4::Context->config("memcached_namespace");
63
    $cache->set_in_cache("$namespace:opac:report:$report", $json_text, 300);
64
}
41
C4/Reports/Guided.pm                               |   23 +++++++---
65
C4/Reports/Guided.pm                               |   23 +++++++---
42
installer/data/mysql/updatedatabase.pl             |    9 +++-
66
installer/data/mysql/updatedatabase.pl             |    9 +++-
43
.../en/modules/reports/guided_reports_start.tt     |   25 ++++++++++-
67
.../en/modules/reports/guided_reports_start.tt     |   25 ++++++++++-
44
opac/svc/report                                    |    9 ++--
68
opac/svc/report                                    |    9 ++--
45
reports/guided_reports.pl                          |   20 +++++++--
69
reports/guided_reports.pl                          |   20 +++++++--
46
svc/report                                         |   45 +++++++++++++++----
70
svc/report                                         |   45 +++++++++++++++----
47
6 files changed, 101 insertions(+), 30 deletions(-)
71
6 files changed, 101 insertions(+), 30 deletions(-)
(-)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 (-2 / +23 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 201-212 canned reports and writing custom SQL reports.</p> Link Here
201
<h1>Build A Report</h1>
209
<h1>Build A Report</h1>
202
<form action="/cgi-bin/koha/reports/guided_reports.pl">
210
<form action="/cgi-bin/koha/reports/guided_reports.pl">
203
<fieldset class="rows">
211
<fieldset class="rows">
204
<legend>Step 1 of 6: Choose a Module to Report on</legend>
212
<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">
213
<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">
206
[% FOREACH area IN areas %]
214
[% FOREACH area IN areas %]
207
<option value="[% area.id %]">[% area.name %]</option>
215
<option value="[% area.id %]">[% area.name %]</option>
208
[% END %]
216
[% END %]
209
</select></li></ol>
217
</select></li>
218
[% IF (public) %]
219
  <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>
220
[% ELSE %]
221
  <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>
222
[% END %]
223
[% IF (usecache) %] <li><label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>seconds</li> [% END %]
224
</ol>
210
</fieldset>
225
</fieldset>
211
<fieldset class="action">
226
<fieldset class="action">
212
<input type="hidden" name="phase" value="Report on this Area" />
227
<input type="hidden" name="phase" value="Report on this Area" />
Lines 600-605 Sub report:<select name="subreport"> Link Here
600
<legend>Edit SQL</legend>
615
<legend>Edit SQL</legend>
601
<ol>
616
<ol>
602
<li><label for="reportname">Report Name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" /></li>
617
<li><label for="reportname">Report Name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" /></li>
618
[% IF (public) %]
619
  <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>
620
[% ELSE %]
621
  <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>
622
[% END %]
623
[% IF (usecache) %] <li><label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>seconds</li> [% END %]
603
<li><label for="notes">Notes:</label><textarea id="notes" name="notes" cols="50" rows="2">[% notes %]</textarea></li>
624
<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>
625
<li><textarea id="sql" name="sql" rows="10" cols="60">[% sql %]</textarea></li>
605
</ol>
626
</ol>
(-)a/opac/svc/report (-5 / +4 lines)
Lines 31-36 my $report=$query->param('id'); Link Here
31
my $cache;
31
my $cache;
32
my $usecache=C4::Context->preference('usecache');
32
my $usecache=C4::Context->preference('usecache');
33
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
34
if ($usecache){
37
if ($usecache){
35
    require Koha::Cache;
38
    require Koha::Cache;
36
    Koha::Cache->import();
39
    Koha::Cache->import();
Lines 46-56 if ($usecache){ Link Here
46
    }
49
    }
47
}    
50
}    
48
51
49
50
51
52
print $query->header;
52
print $query->header;
53
my ($sql,$type,$name,$notes) = get_saved_report($report);
54
my $offset=0;
53
my $offset=0;
55
my $limit=10;
54
my $limit=10;
56
my ($sth, $errors) = execute_query($sql, $offset, $limit);
55
my ($sth, $errors) = execute_query($sql, $offset, $limit);
Lines 60-64 print $json_text; Link Here
60
59
61
if ($usecache){
60
if ($usecache){
62
    my $namespace = C4::Context->config("memcached_namespace");
61
    my $namespace = C4::Context->config("memcached_namespace");
63
    $cache->set_in_cache("$namespace:opac:report:$report", $json_text, 300);
62
    $cache->set_in_cache("$namespace:opac:report:$report", $json_text, $cache_expiry);
64
}
63
}
(-)a/reports/guided_reports.pl (-4 / +16 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-142 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 $public = $input->param('public');
147
140
    my @errors;
148
    my @errors;
141
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
149
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
142
        push @errors, {sqlerr => $1};
150
        push @errors, {sqlerr => $1};
Lines 151-157 elsif ( $phase eq 'Update SQL'){ Link Here
151
        );
159
        );
152
    }
160
    }
153
    else {
161
    else {
154
        update_sql( $id, $sql, $reportname, $notes );
162
        update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public );
155
        $template->param(
163
        $template->param(
156
            'save_successful'       => 1,
164
            'save_successful'       => 1,
157
            'id'                    => $id,
165
            'id'                    => $id,
Lines 373-378 elsif ( $phase eq 'Save Report' ) { Link Here
373
    my $name = $input->param('reportname');
381
    my $name = $input->param('reportname');
374
    my $type = $input->param('types');
382
    my $type = $input->param('types');
375
    my $notes = $input->param('notes');
383
    my $notes = $input->param('notes');
384
    my $cache_expiry = $input->param('cache_expiry');
385
    my $public = $input->param('public');
376
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
386
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
377
        push @errors, {sqlerr => $1};
387
        push @errors, {sqlerr => $1};
378
    }
388
    }
Lines 386-395 elsif ( $phase eq 'Save Report' ) { Link Here
386
            'reportname'=> $name,
396
            'reportname'=> $name,
387
            'type'      => $type,
397
            'type'      => $type,
388
            'notes'     => $notes,
398
            'notes'     => $notes,
399
            'cache_expiry' => $cache_expiry,
400
            'public'    => $public,
389
        );
401
        );
390
    }
402
    }
391
    else {
403
    else {
392
        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes );
404
        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
393
        $template->param(
405
        $template->param(
394
            'save_successful'       => 1,
406
            'save_successful'       => 1,
395
            'id'                    => $id,
407
            'id'                    => $id,
(-)a/svc/report (-12 / +35 lines)
Lines 29-50 use CGI; Link Here
29
my $query = CGI->new();
29
my $query = CGI->new();
30
my $report=$query->param('id');
30
my $report=$query->param('id');
31
31
32
my $cache;
33
my $usecache = C4::Context->preference('usecache');
34
32
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
35
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
33
    {
36
  {
34
        template_name   => "intranet-main.tmpl",
37
    template_name   => "intranet-main.tmpl",
35
        query           => $query,
38
    query           => $query,
36
        type            => "intranet",
39
    type            => "intranet",
37
        authnotrequired => 0,
40
    authnotrequired => 0,
38
        flagsrequired   => {
41
    flagsrequired   => {
39
            catalogue => 1,
42
      catalogue => 1,
40
        },
43
    },
41
    }
44
  }
42
);
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
43
print $query->header;
62
print $query->header;
44
my ($sql,$type,$name,$notes) = get_saved_report($report);
63
# $public isnt used for intranet
64
my ($sql, $type, $name, $notes, $cache_expiry, $public) = get_saved_report($report);
45
my $offset=0;
65
my $offset=0;
46
my $limit=10;
66
my $limit=10;
47
my ($sth, $errors) = execute_query($sql, $offset, $limit);
67
my ($sth, $errors) = execute_query($sql, $offset, $limit);
48
my $lines = $sth->fetchall_arrayref;
68
my $lines = $sth->fetchall_arrayref;
49
my $json_text   = to_json($lines);
69
my $json_text   = to_json($lines);
50
print $json_text;
70
print $json_text;
51
- 
71
52
--
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
}
53
installer/data/mysql/sysprefs.sql |    2 +-
76
installer/data/mysql/sysprefs.sql |    2 +-
54
1 files changed, 1 insertions(+), 1 deletions(-)
77
1 files changed, 1 insertions(+), 1 deletions(-)
(-)a/installer/data/mysql/sysprefs.sql (-3 / +1 lines)
Lines 328-331 INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES(' Link Here
328
INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OpacKohaUrl','1',"Show 'Powered by Koha' text on OPAC footer.",NULL,NULL);
328
INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OpacKohaUrl','1',"Show 'Powered by Koha' text on OPAC footer.",NULL,NULL);
329
INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('EasyAnalyticalRecords','0','If on, display in the catalogue screens tools to easily setup analytical record relationships','','YesNo');
329
INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('EasyAnalyticalRecords','0','If on, display in the catalogue screens tools to easily setup analytical record relationships','','YesNo');
330
INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacShowRecentComments',0,'If ON a link to recent comments will appear in the OPAC masthead',NULL,'YesNo');
330
INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacShowRecentComments',0,'If ON a link to recent comments will appear in the OPAC masthead',NULL,'YesNo');
331
331
INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('usecache',0,'If on pages with caching enabled will use caching',NULL,'YesNo');
332
- 
333
--
334
.../en/modules/reports/guided_reports_start.tt     |   60 +++++++++++-
332
.../en/modules/reports/guided_reports_start.tt     |   60 +++++++++++-
335
reports/guided_reports.pl                          |   95 +++++++++++++++++---
333
reports/guided_reports.pl                          |   95 +++++++++++++++++---
336
2 files changed, 138 insertions(+), 17 deletions(-)
334
2 files changed, 138 insertions(+), 17 deletions(-)
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-5 / +55 lines)
Lines 206-211 canned reports and writing custom SQL reports.</p> Link Here
206
206
207
207
208
[% 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 %]
209
<h1>Build A Report</h1>
214
<h1>Build A Report</h1>
210
<form action="/cgi-bin/koha/reports/guided_reports.pl">
215
<form action="/cgi-bin/koha/reports/guided_reports.pl">
211
<fieldset class="rows">
216
<fieldset class="rows">
Lines 218-228 canned reports and writing custom SQL reports.</p> Link Here
218
[% IF (public) %]
223
[% IF (public) %]
219
  <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>
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>
220
[% ELSE %]
225
[% ELSE %]
221
  <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>
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>
222
[% END %]
227
[% END %]
223
[% IF (usecache) %] <li><label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>seconds</li> [% 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 %]
224
</ol>
237
</ol>
225
</fieldset>
238
<ofieldset>
226
<fieldset class="action">
239
<fieldset class="action">
227
<input type="hidden" name="phase" value="Report on this Area" />
240
<input type="hidden" name="phase" value="Report on this Area" />
228
<input type="submit" name="submit" value="Next &gt;&gt;" />
241
<input type="submit" name="submit" value="Next &gt;&gt;" />
Lines 236-241 canned reports and writing custom SQL reports.</p> Link Here
236
<h1>Build A Report</h1>
249
<h1>Build A Report</h1>
237
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
250
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
238
<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 %]" />
239
<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>
240
<ol><li><label for="types">Choose: </label>
255
<ol><li><label for="types">Choose: </label>
241
    <select id="types" name="types">
256
    <select id="types" name="types">
Lines 269-274 canned reports and writing custom SQL reports.</p> Link Here
269
<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">
270
    <input type="hidden" name="area" value="[% area %]" />
285
    <input type="hidden" name="area" value="[% area %]" />
271
    <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 %]" />
272
    <fieldset>
289
    <fieldset>
273
<div class="yui-g">
290
<div class="yui-g">
274
<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 317-322 canned reports and writing custom SQL reports.</p> Link Here
317
    <input type="hidden" name="area" value="[% area %]" />
334
    <input type="hidden" name="area" value="[% area %]" />
318
    <input type="hidden" name="type" value="[% type %]" />
335
    <input type="hidden" name="type" value="[% type %]" />
319
    <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 %]" />
320
    <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>
321
    <table>
340
    <table>
322
        [% FOREACH criteri IN criteria %]
341
        [% FOREACH criteri IN criteria %]
Lines 413-418 canned reports and writing custom SQL reports.</p> Link Here
413
<input type="hidden" name="column" value="[% column %]" />
432
<input type="hidden" name="column" value="[% column %]" />
414
<input type="hidden" name="definition" value="[% definition %]" />
433
<input type="hidden" name="definition" value="[% definition %]" />
415
<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 %]" />
416
<fieldset><table>
437
<fieldset><table>
417
[% FOREACH total_b IN total_by %]
438
[% FOREACH total_b IN total_by %]
418
<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 443-448 canned reports and writing custom SQL reports.</p> Link Here
443
<input type="hidden" name="criteria" value="[% criteriastring %]" />
464
<input type="hidden" name="criteria" value="[% criteriastring %]" />
444
<input type="hidden" name="definition" value="[% definition %]" />
465
<input type="hidden" name="definition" value="[% definition %]" />
445
<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 %]" />
446
<fieldset><table>[% FOREACH order_b IN order_by %]
469
<fieldset><table>[% FOREACH order_b IN order_by %]
447
<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>
448
<select name="[% order_b.name %]_ovalue">
471
<select name="[% order_b.name %]_ovalue">
Lines 473-478 canned reports and writing custom SQL reports.</p> Link Here
473
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
496
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
474
<input type="hidden" name="sql" value="[% sql %]" />
497
<input type="hidden" name="sql" value="[% sql %]" />
475
<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 %]" />
476
<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>
477
<fieldset class="action"><input type="hidden" name="phase" value="Save" />  
502
<fieldset class="action"><input type="hidden" name="phase" value="Save" />  
478
<input type="submit" name="submit" value="Save" />  </fieldset>
503
<input type="submit" name="submit" value="Save" />  </fieldset>
Lines 483-488 canned reports and writing custom SQL reports.</p> Link Here
483
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
508
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
484
<input type="hidden" name="sql" value="[% sql |html %]" />
509
<input type="hidden" name="sql" value="[% sql |html %]" />
485
<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 %]" />
486
<fieldset class="rows">
513
<fieldset class="rows">
487
<legend>Save Your Custom Report</legend>
514
<legend>Save Your Custom Report</legend>
488
<ol>
515
<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 %]" />
572
        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
546
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
573
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
547
    </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 %]
548
    <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>
549
    <li><label for="types">Type:</label>
590
    <li><label for="types">Type:</label>
550
        <select id="types" name="types">
591
        <select id="types" name="types">
Lines 620-626 Sub report:<select name="subreport"> Link Here
620
[% ELSE %]
661
[% ELSE %]
621
  <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>
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>
622
[% END %]
663
[% END %]
623
[% IF (usecache) %] <li><label for="cache_expiry">Cache expiry:</label><input type="text" id="cache_expiry" name="cache_expiry" value="[% cache_expiry %]"></input>seconds</li> [% 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 %]
624
<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>
625
<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>
626
</ol>
675
</ol>
Lines 657-662 Sub report:<select name="subreport"> Link Here
657
    <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.
658
    <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.
659
    [% 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.
660
    [% ELSE %]
710
    [% ELSE %]
661
    [% END %]
711
    [% END %]
662
[% END %]
712
[% END %]
(-)a/reports/guided_reports.pl (-13 / +83 lines)
Lines 143-151 elsif ( $phase eq 'Update SQL'){ Link Here
143
    my $reportname = $input->param('reportname');
143
    my $reportname = $input->param('reportname');
144
    my $notes      = $input->param('notes');
144
    my $notes      = $input->param('notes');
145
    my $cache_expiry = $input->param('cache_expiry');
145
    my $cache_expiry = $input->param('cache_expiry');
146
    my $cache_expiry_units = $input->param('cache_expiry_units');
146
    my $public = $input->param('public');
147
    my $public = $input->param('public');
147
148
148
    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
149
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
166
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
150
        push @errors, {sqlerr => $1};
167
        push @errors, {sqlerr => $1};
151
    }
168
    }
Lines 181-197 elsif ($phase eq 'retrieve results') { Link Here
181
}
198
}
182
199
183
elsif ( $phase eq 'Report on this Area' ) {
200
elsif ( $phase eq 'Report on this Area' ) {
201
    my $cache_expiry_units = $input->param('cache_expiry_units'),
202
    my $cache_expiry = $input->param('cache_expiry');
184
203
185
    # they have choosen a new report and the area to report on
204
    # we need to handle converting units
186
    $template->param(
205
    if( $cache_expiry_units eq "minutes" ){
187
        'build2' => 1,
206
      $cache_expiry *= 60;
188
        'area'   => $input->param('areas'),
207
    } elsif( $cache_expiry_units eq "hours" ){
189
        'types'  => get_report_types(),
208
      $cache_expiry *= 3600; # 60 * 60
190
    );
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
    }
191
}
233
}
192
234
193
elsif ( $phase eq 'Choose this type' ) {
235
elsif ( $phase eq 'Choose this type' ) {
194
195
    # they have chosen type and area
236
    # they have chosen type and area
196
    # get area and type and pass them to the template
237
    # get area and type and pass them to the template
197
    my $area = $input->param('area');
238
    my $area = $input->param('area');
Lines 201-211 elsif ( $phase eq 'Choose this type' ) { Link Here
201
        'area'   => $area,
242
        'area'   => $area,
202
        'type'   => $type,
243
        'type'   => $type,
203
        columns  => get_columns($area,$input),
244
        columns  => get_columns($area,$input),
245
        'cache_expiry' => $input->param('cache_expiry'),
246
        'public' => $input->param('public'),
204
    );
247
    );
205
}
248
}
206
249
207
elsif ( $phase eq 'Choose these columns' ) {
250
elsif ( $phase eq 'Choose these columns' ) {
208
209
    # we now know type, area, and columns
251
    # we now know type, area, and columns
210
    # next step is the constraints
252
    # next step is the constraints
211
    my $area    = $input->param('area');
253
    my $area    = $input->param('area');
Lines 219-224 elsif ( $phase eq 'Choose these columns' ) { Link Here
219
        'column' => $column,
261
        'column' => $column,
220
        definitions => get_from_dictionary($area),
262
        definitions => get_from_dictionary($area),
221
        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'),
222
    );
267
    );
223
}
268
}
224
269
Lines 263-269 elsif ( $phase eq 'Choose these criteria' ) { Link Here
263
        }
308
        }
264
	}
309
	}
265
    }
310
    }
266
267
    $template->param(
311
    $template->param(
268
        'build5'         => 1,
312
        'build5'         => 1,
269
        'area'           => $area,
313
        'area'           => $area,
Lines 271-276 elsif ( $phase eq 'Choose these criteria' ) { Link Here
271
        'column'         => $column,
315
        'column'         => $column,
272
        'definition'     => $definition,
316
        'definition'     => $definition,
273
        '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'),
274
    );
321
    );
275
322
276
    # get columns
323
    # get columns
Lines 311-316 elsif ( $phase eq 'Choose These Operations' ) { Link Here
311
        'criteriastring' => $criteria,
358
        'criteriastring' => $criteria,
312
        'totals'         => $totals,
359
        'totals'         => $totals,
313
        'definition'     => $definition,
360
        'definition'     => $definition,
361
        'cache_expiry' => $input->param('cache_expiry'),
362
        'public' => $input->param('public'),
314
    );
363
    );
315
364
316
    # get columns
365
    # get columns
Lines 360-366 elsif ( $phase eq 'Build Report' ) { Link Here
360
    $template->param(
409
    $template->param(
361
        'showreport' => 1,
410
        'showreport' => 1,
362
        'sql'        => $sql,
411
        'sql'        => $sql,
363
        'type'       => $type
412
        'type'       => $type,
413
        'cache_expiry' => $input->param('cache_expiry'),
414
        'public' => $input->param('public'),
364
    );
415
    );
365
}
416
}
366
417
Lines 371-377 elsif ( $phase eq 'Save' ) { Link Here
371
    $template->param(
422
    $template->param(
372
        'save' => 1,
423
        'save' => 1,
373
        'sql'  => $sql,
424
        'sql'  => $sql,
374
        'type' => $type
425
        'type' => $type,
426
        'cache_expiry' => $input->param('cache_expiry'),
427
        'public' => $input->param('public'),
375
    );
428
    );
376
}
429
}
377
430
Lines 382-388 elsif ( $phase eq 'Save Report' ) { Link Here
382
    my $type = $input->param('types');
435
    my $type = $input->param('types');
383
    my $notes = $input->param('notes');
436
    my $notes = $input->param('notes');
384
    my $cache_expiry = $input->param('cache_expiry');
437
    my $cache_expiry = $input->param('cache_expiry');
438
    my $cache_expiry_units = $input->param('cache_expiry_units');
385
    my $public = $input->param('public');
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
386
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
457
    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
387
        push @errors, {sqlerr => $1};
458
        push @errors, {sqlerr => $1};
388
    }
459
    }
Lines 605-611 elsif ($phase eq 'Create report from SQL') { Link Here
605
            'notes'         => $input->param('notes'),
676
            'notes'         => $input->param('notes'),
606
        );
677
        );
607
    }
678
    }
608
	$template->param('create' => 1);
679
	$template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache);
609
}
680
}
610
681
611
elsif ($phase eq 'Create Compound Report'){
682
elsif ($phase eq 'Create Compound Report'){
612
- 

Return to bug 7249