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

(-)a/C4/Reports/Guided.pm (-50 / +35 lines)
Lines 20-25 package C4::Reports::Guided; Link Here
20
use Modern::Perl;
20
use Modern::Perl;
21
use CGI qw ( -utf8 );
21
use CGI qw ( -utf8 );
22
use Carp;
22
use Carp;
23
use JSON qw( from_json );
23
24
24
use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
25
use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
25
use C4::Context;
26
use C4::Context;
Lines 27-34 use C4::Templates qw/themelanguage/; Link Here
27
use C4::Koha;
28
use C4::Koha;
28
use Koha::DateUtils;
29
use Koha::DateUtils;
29
use C4::Output;
30
use C4::Output;
30
use XML::Simple;
31
use XML::Dumper;
32
use C4::Debug;
31
use C4::Debug;
33
use C4::Log;
32
use C4::Log;
34
33
Lines 536-545 sub execute_query { Link Here
536
    $sth->execute(@$sql_params, $offset, $limit);
535
    $sth->execute(@$sql_params, $offset, $limit);
537
    return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err);
536
    return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err);
538
    return ( $sth );
537
    return ( $sth );
539
    # my @xmlarray = ... ;
540
    # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
541
    # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
542
    # store_results($id,$xml);
543
}
538
}
544
539
545
=head2 save_report($sql,$name,$type,$notes)
540
=head2 save_report($sql,$name,$type,$notes)
Lines 594-640 sub update_sql { Link Here
594
}
589
}
595
590
596
sub store_results {
591
sub store_results {
597
	my ($id,$xml)=@_;
592
    my ( $id, $json ) = @_;
598
	my $dbh = C4::Context->dbh();
593
    my $dbh = C4::Context->dbh();
599
	my $query = "SELECT * FROM saved_reports WHERE report_id=?";
594
    $dbh->do(q|
600
	my $sth = $dbh->prepare($query);
595
        INSERT INTO saved_reports ( report_id, report, date_run ) VALUES ( ?, ?, NOW() );
601
	$sth->execute($id);
596
    |, undef, $id, $json );
602
	if (my $data=$sth->fetchrow_hashref()){
603
		my $query2 = "UPDATE saved_reports SET report=?,date_run=now() WHERE report_id=?";
604
		my $sth2 = $dbh->prepare($query2);
605
	    $sth2->execute($xml,$id);
606
	}
607
	else {
608
		my $query2 = "INSERT INTO saved_reports (report_id,report,date_run) VALUES (?,?,now())";
609
		my $sth2 = $dbh->prepare($query2);
610
		$sth2->execute($id,$xml);
611
	}
612
}
597
}
613
598
614
sub format_results {
599
sub format_results {
615
	my ($id) = @_;
600
    my ( $id ) = @_;
616
	my $dbh = C4::Context->dbh();
601
    my $dbh = C4::Context->dbh();
617
	my $query = "SELECT * FROM saved_reports WHERE report_id = ?";
602
    my ( $report_name, $notes, $json, $date_run ) = $dbh->selectrow_array(q|
618
	my $sth = $dbh->prepare($query);
603
       SELECT ss.report_name, ss.notes, sr.report, sr.date_run
619
	$sth->execute($id);
604
       FROM saved_sql ss
620
	my $data = $sth->fetchrow_hashref();
605
       LEFT JOIN saved_reports sr ON sr.report_id = ss.id
621
	my $dump = new XML::Dumper;
606
       WHERE sr.id = ?
622
	my $perl = $dump->xml2pl( $data->{'report'} );
607
    |, undef, $id);
623
	foreach my $row (@$perl) {
608
    return {
624
		my $htmlrow="<tr>";
609
        report_name => $report_name,
625
		foreach my $key (keys %$row){
610
        notes => $notes,
626
			$htmlrow .= "<td>$row->{$key}</td>";
611
        results => from_json( $json ),
627
		}
612
        date_run => $date_run,
628
		$htmlrow .= "</tr>";
613
    };
629
		$row->{'row'} = $htmlrow;
614
}
630
	}
631
	$sth->finish;
632
	$query = "SELECT * FROM saved_sql WHERE id = ?";
633
	$sth = $dbh->prepare($query);
634
	$sth->execute($id);
635
	$data = $sth->fetchrow_hashref();
636
	return ($perl,$data->{'report_name'},$data->{'notes'});	
637
}	
638
615
639
sub delete_report {
616
sub delete_report {
640
    my (@ids) = @_;
617
    my (@ids) = @_;
Lines 652-661 sub delete_report { Link Here
652
sub get_saved_reports_base_query {
629
sub get_saved_reports_base_query {
653
    my $area_name_sql_snippet = get_area_name_sql_snippet;
630
    my $area_name_sql_snippet = get_area_name_sql_snippet;
654
    return <<EOQ;
631
    return <<EOQ;
655
SELECT s.*, r.report, r.date_run, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname,
632
SELECT s.*, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname,
656
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
633
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
657
FROM saved_sql s
634
FROM saved_sql s
658
LEFT JOIN saved_reports r ON r.report_id = s.id
659
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
635
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
660
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup)
636
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup)
661
LEFT OUTER JOIN borrowers b USING (borrowernumber)
637
LEFT OUTER JOIN borrowers b USING (borrowernumber)
Lines 675-685 sub get_saved_reports { Link Here
675
    if ($filter) {
651
    if ($filter) {
676
        if (my $date = $filter->{date}) {
652
        if (my $date = $filter->{date}) {
677
            $date = eval { output_pref( { dt => dt_from_string( $date ), dateonly => 1, dateformat => 'iso' }); };
653
            $date = eval { output_pref( { dt => dt_from_string( $date ), dateonly => 1, dateformat => 'iso' }); };
678
            push @cond, "DATE(date_run) = ? OR
654
            push @cond, "DATE(last_modified) = ? OR
679
                         DATE(date_created) = ? OR
680
                         DATE(last_modified) = ? OR
681
                         DATE(last_run) = ?";
655
                         DATE(last_run) = ?";
682
            push @args, $date, $date, $date, $date;
656
            push @args, $date, $date, $date;
683
        }
657
        }
684
        if (my $author = $filter->{author}) {
658
        if (my $author = $filter->{author}) {
685
            $author = "%$author%";
659
            $author = "%$author%";
Lines 871-876 sub get_sql { Link Here
871
	return $data->{'savedsql'};
845
	return $data->{'savedsql'};
872
}
846
}
873
847
848
sub get_results {
849
    my ( $report_id ) = @_;
850
    my $dbh = C4::Context->dbh;
851
    warn $report_id;
852
    return $dbh->selectall_arrayref(q|
853
        SELECT id, report, date_run
854
        FROM saved_reports
855
        WHERE report_id = ?
856
    |, { Slice => {} }, $report_id);
857
}
858
874
sub _get_column_defs {
859
sub _get_column_defs {
875
    my ($cgi) = @_;
860
    my ($cgi) = @_;
876
    my %columns;
861
    my %columns;
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-4 / +12 lines)
Lines 360-366 canned reports and writing custom SQL reports.</p> Link Here
360
                        <td>No</td>
360
                        <td>No</td>
361
                        [% END %]
361
                        [% END %]
362
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
362
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
363
                        <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 %]
363
                        <td>
364
                            [% FOR result IN savedreport.results %]
365
                                <a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&amp;id=[% result.id %]">[% result.date_run %]</a>
366
                                <br/>
367
                            [% END %]
364
                        </td>
368
                        </td>
365
                        <td>
369
                        <td>
366
                            <div class="dropup">
370
                            <div class="dropup">
Lines 920-932 Sub report:<select name="subreport"> Link Here
920
[% END %]
924
[% END %]
921
925
922
926
923
[% IF ( retresults ) %]
927
[% IF saved_results %]
924
<h1>Saved report results</h1>
928
<h1>Saved report results</h1>
925
<h2>[% name %]</h2>
929
<h2>[% name %]</h2>
926
<p>[% notes %]</p>
930
<p>[% notes %]</p>
927
<table>
931
<table>
928
[% FOREACH result IN results %]
932
[% FOREACH rows IN saved_results %]
929
[% result.row %]
933
<tr>
934
[% FOREACH col IN rows %]
935
<td>[% col %]</td>
936
[% END %]
937
<tr>
930
[% END %]
938
[% END %]
931
</table>
939
</table>
932
[% END %]
940
[% END %]
(-)a/misc/cronjobs/runreport.pl (-12 / +20 lines)
Lines 18-25 Link Here
18
# You should have received a copy of the GNU General Public License
18
# You should have received a copy of the GNU General Public License
19
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
# along with Koha; if not, see <http://www.gnu.org/licenses>.
20
20
21
use strict;
21
use Modern::Perl;
22
use warnings;
23
22
24
use C4::Reports::Guided; # 0.12
23
use C4::Reports::Guided; # 0.12
25
use C4::Context;
24
use C4::Context;
Lines 34-39 use Text::CSV_XS; Link Here
34
use CGI qw ( -utf8 );
33
use CGI qw ( -utf8 );
35
use Carp;
34
use Carp;
36
use Encode;
35
use Encode;
36
use JSON qw( to_json );
37
37
38
BEGIN {
38
BEGIN {
39
    # find Koha's Perl modules
39
    # find Koha's Perl modules
Lines 55-61 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ] Link Here
55
   -m --man        full documentation, same as --help --verbose
55
   -m --man        full documentation, same as --help --verbose
56
   -v --verbose    verbose output
56
   -v --verbose    verbose output
57
57
58
   --format=s      selects format. Choice of text, html, csv, or tsv
58
   --format=s      selects format. Choice of text, html, csv or tsv
59
59
60
   -e --email      whether to use e-mail (implied by --to or --from)
60
   -e --email      whether to use e-mail (implied by --to or --from)
61
   -a --attachment additionally attach the report as a file. cannot be used with html format
61
   -a --attachment additionally attach the report as a file. cannot be used with html format
Lines 65-70 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ] Link Here
65
   --to=s          e-mail address to send report to
65
   --to=s          e-mail address to send report to
66
   --from=s        e-mail address to send report from
66
   --from=s        e-mail address to send report from
67
   --subject=s     subject for the e-mail
67
   --subject=s     subject for the e-mail
68
   --store-results store the result of the report
68
69
69
70
70
 Arguments:
71
 Arguments:
Lines 118-123 E-mail address to send report from. Defaults to KohaAdminEmailAddress. Link Here
118
119
119
Subject for the e-mail message. Defaults to "Koha Saved Report"
120
Subject for the e-mail message. Defaults to "Koha Saved Report"
120
121
122
=item B<--store-results>
123
124
Store the result of the report into the saved_reports DB table.
125
126
To access the results, go on Reports > Guided reports > Saved report.
127
121
=back
128
=back
122
129
123
=head1 DESCRIPTION
130
=head1 DESCRIPTION
Lines 167-172 my $from = ""; Link Here
167
my $subject = "";
174
my $subject = "";
168
my $separator = ',';
175
my $separator = ',';
169
my $quote = '"';
176
my $quote = '"';
177
my $store_results = 0;
170
178
171
my $username = undef;
179
my $username = undef;
172
my $password = undef;
180
my $password = undef;
Lines 185-190 GetOptions( Link Here
185
    'username:s'        => \$username,
193
    'username:s'        => \$username,
186
    'password:s'        => \$password,
194
    'password:s'        => \$password,
187
    'method:s'          => \$method,
195
    'method:s'          => \$method,
196
    'store-results'     => \$store_results,
188
197
189
) or pod2usage(2);
198
) or pod2usage(2);
190
pod2usage( -verbose => 2 ) if ($man);
199
pod2usage( -verbose => 2 ) if ($man);
Lines 242-248 foreach my $report_id (@ARGV) { Link Here
242
    }
251
    }
243
    # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
252
    # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
244
    my ($sth) = execute_query($sql);
253
    my ($sth) = execute_query($sql);
245
    # execute_query(sql, , 0, 20, , )
246
    my $count = scalar($sth->rows);
254
    my $count = scalar($sth->rows);
247
    unless ($count) {
255
    unless ($count) {
248
        print "NO OUTPUT: 0 results from execute_query\n";
256
        print "NO OUTPUT: 0 results from execute_query\n";
Lines 251-262 foreach my $report_id (@ARGV) { Link Here
251
    $verbose and print "$count results from execute_query\n";
259
    $verbose and print "$count results from execute_query\n";
252
260
253
    my $message;
261
    my $message;
262
    my @rows_to_store;
254
    if ($format eq 'html') {
263
    if ($format eq 'html') {
255
        my $cgi = CGI->new();
264
        my $cgi = CGI->new();
256
        my @rows = ();
265
        my @rows;
257
        while (my $line = $sth->fetchrow_arrayref) {
266
        while (my $line = $sth->fetchrow_arrayref) {
258
            foreach (@$line) { defined($_) or $_ = ''; }    # catch undef values, replace w/ ''
267
            foreach (@$line) { defined($_) or $_ = ''; }    # catch undef values, replace w/ ''
259
            push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
268
            push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
269
            push @rows_to_store, [@$line] if $store_results;
260
        }
270
        }
261
        $message = $cgi->table(join "", @rows);
271
        $message = $cgi->table(join "", @rows);
262
    } elsif ($format eq 'csv') {
272
    } elsif ($format eq 'csv') {
Lines 266-281 foreach my $report_id (@ARGV) { Link Here
266
            });
276
            });
267
        while (my $line = $sth->fetchrow_arrayref) {
277
        while (my $line = $sth->fetchrow_arrayref) {
268
            $csv->combine(@$line);
278
            $csv->combine(@$line);
269
#            foreach (@$line) {
270
#                defined($_) or $_ = '';
271
#                $_ =~ s/$quote/\\$quote/g;
272
#                $_ = "$quote$_$quote";
273
#            }    # catch undef values, replace w/ ''
274
#            $message .= join ($separator, @$line) . "\n";
275
            $message .= $csv->string() . "\n";
279
            $message .= $csv->string() . "\n";
280
            push @rows_to_store, [@$line] if $store_results;
276
        }
281
        }
277
    }
282
    }
278
283
    if ( $store_results ) {
284
        my $json = to_json( \@rows_to_store );
285
        C4::Reports::Guided::store_results( $report_id, $json );
286
    }
279
    if ($email) {
287
    if ($email) {
280
        my $args = { to => $to, from => $from, subject => $subject };
288
        my $args = { to => $to, from => $from, subject => $subject };
281
        if ( $format eq 'html' ) {
289
        if ( $format eq 'html' ) {
(-)a/reports/guided_reports.pl (-10 / +12 lines)
Lines 112-120 elsif ( $phase eq 'Build new' ) { Link Here
112
    my $subgroup = $input->param('subgroup');
112
    my $subgroup = $input->param('subgroup');
113
    $filter->{group} = $group;
113
    $filter->{group} = $group;
114
    $filter->{subgroup} = $subgroup;
114
    $filter->{subgroup} = $subgroup;
115
    my $reports = get_saved_reports($filter);
116
    for my $report ( @$reports ) {
117
        $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
118
    }
115
    $template->param(
119
    $template->param(
116
        'saved1' => 1,
120
        'saved1' => 1,
117
        'savedreports' => get_saved_reports($filter),
121
        'savedreports' => $reports,
118
        'usecache' => $usecache,
122
        'usecache' => $usecache,
119
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
123
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
120
        filters => $filter,
124
        filters => $filter,
Lines 260-273 elsif ( $phase eq 'Update SQL'){ Link Here
260
}
264
}
261
265
262
elsif ($phase eq 'retrieve results') {
266
elsif ($phase eq 'retrieve results') {
263
	my $id = $input->param('id');
267
    my $id = $input->param('id');
264
	my ($results,$name,$notes) = format_results($id);
268
    my $result = format_results( $id );
265
	# do something
269
    $template->param(
266
	$template->param(
270
        report_name   => $result->{report_name},
267
		'retresults' => 1,
271
        notes         => $result->{notes},
268
		'results' => $results,
272
        saved_results => $result->{results},
269
		'name' => $name,
273
        date_run      => $result->{date_run},
270
		'notes' => $notes,
271
    );
274
    );
272
}
275
}
273
276
274
- 

Return to bug 14435