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

(-)a/C4/Reports/Guided.pm (-52 / +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-37 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 Smart::Comments;
34
# use Data::Dumper;
35
use C4::Log;
32
use C4::Log;
36
33
37
use Koha::AuthorisedValues;
34
use Koha::AuthorisedValues;
Lines 537-546 sub execute_query { Link Here
537
    $sth->execute(@$sql_params, $offset, $limit);
534
    $sth->execute(@$sql_params, $offset, $limit);
538
    return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err);
535
    return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err);
539
    return ( $sth );
536
    return ( $sth );
540
    # my @xmlarray = ... ;
541
    # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
542
    # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
543
    # store_results($id,$xml);
544
}
537
}
545
538
546
=head2 save_report($sql,$name,$type,$notes)
539
=head2 save_report($sql,$name,$type,$notes)
Lines 595-641 sub update_sql { Link Here
595
}
588
}
596
589
597
sub store_results {
590
sub store_results {
598
	my ($id,$xml)=@_;
591
    my ( $id, $json ) = @_;
599
	my $dbh = C4::Context->dbh();
592
    my $dbh = C4::Context->dbh();
600
	my $query = "SELECT * FROM saved_reports WHERE report_id=?";
593
    $dbh->do(q|
601
	my $sth = $dbh->prepare($query);
594
        INSERT INTO saved_reports ( report_id, report, date_run ) VALUES ( ?, ?, NOW() );
602
	$sth->execute($id);
595
    |, undef, $id, $json );
603
	if (my $data=$sth->fetchrow_hashref()){
604
		my $query2 = "UPDATE saved_reports SET report=?,date_run=now() WHERE report_id=?";
605
		my $sth2 = $dbh->prepare($query2);
606
	    $sth2->execute($xml,$id);
607
	}
608
	else {
609
		my $query2 = "INSERT INTO saved_reports (report_id,report,date_run) VALUES (?,?,now())";
610
		my $sth2 = $dbh->prepare($query2);
611
		$sth2->execute($id,$xml);
612
	}
613
}
596
}
614
597
615
sub format_results {
598
sub format_results {
616
	my ($id) = @_;
599
    my ( $id ) = @_;
617
	my $dbh = C4::Context->dbh();
600
    my $dbh = C4::Context->dbh();
618
	my $query = "SELECT * FROM saved_reports WHERE report_id = ?";
601
    my ( $report_name, $notes, $json, $date_run ) = $dbh->selectrow_array(q|
619
	my $sth = $dbh->prepare($query);
602
       SELECT ss.report_name, ss.notes, sr.report, sr.date_run
620
	$sth->execute($id);
603
       FROM saved_sql ss
621
	my $data = $sth->fetchrow_hashref();
604
       LEFT JOIN saved_reports sr ON sr.report_id = ss.id
622
	my $dump = new XML::Dumper;
605
       WHERE sr.id = ?
623
	my $perl = $dump->xml2pl( $data->{'report'} );
606
    |, undef, $id);
624
	foreach my $row (@$perl) {
607
    return {
625
		my $htmlrow="<tr>";
608
        report_name => $report_name,
626
		foreach my $key (keys %$row){
609
        notes => $notes,
627
			$htmlrow .= "<td>$row->{$key}</td>";
610
        results => from_json( $json ),
628
		}
611
        date_run => $date_run,
629
		$htmlrow .= "</tr>";
612
    };
630
		$row->{'row'} = $htmlrow;
613
}
631
	}
632
	$sth->finish;
633
	$query = "SELECT * FROM saved_sql WHERE id = ?";
634
	$sth = $dbh->prepare($query);
635
	$sth->execute($id);
636
	$data = $sth->fetchrow_hashref();
637
	return ($perl,$data->{'report_name'},$data->{'notes'});	
638
}	
639
614
640
sub delete_report {
615
sub delete_report {
641
    my (@ids) = @_;
616
    my (@ids) = @_;
Lines 653-662 sub delete_report { Link Here
653
sub get_saved_reports_base_query {
628
sub get_saved_reports_base_query {
654
    my $area_name_sql_snippet = get_area_name_sql_snippet;
629
    my $area_name_sql_snippet = get_area_name_sql_snippet;
655
    return <<EOQ;
630
    return <<EOQ;
656
SELECT s.*, r.report, r.date_run, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname,
631
SELECT s.*, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname,
657
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
632
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
658
FROM saved_sql s
633
FROM saved_sql s
659
LEFT JOIN saved_reports r ON r.report_id = s.id
660
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
634
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
661
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)
635
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)
662
LEFT OUTER JOIN borrowers b USING (borrowernumber)
636
LEFT OUTER JOIN borrowers b USING (borrowernumber)
Lines 676-686 sub get_saved_reports { Link Here
676
    if ($filter) {
650
    if ($filter) {
677
        if (my $date = $filter->{date}) {
651
        if (my $date = $filter->{date}) {
678
            $date = eval { output_pref( { dt => dt_from_string( $date ), dateonly => 1, dateformat => 'iso' }); };
652
            $date = eval { output_pref( { dt => dt_from_string( $date ), dateonly => 1, dateformat => 'iso' }); };
679
            push @cond, "DATE(date_run) = ? OR
653
            push @cond, "DATE(last_modified) = ? OR
680
                         DATE(date_created) = ? OR
681
                         DATE(last_modified) = ? OR
682
                         DATE(last_run) = ?";
654
                         DATE(last_run) = ?";
683
            push @args, $date, $date, $date, $date;
655
            push @args, $date, $date, $date;
684
        }
656
        }
685
        if (my $author = $filter->{author}) {
657
        if (my $author = $filter->{author}) {
686
            $author = "%$author%";
658
            $author = "%$author%";
Lines 872-877 sub get_sql { Link Here
872
	return $data->{'savedsql'};
844
	return $data->{'savedsql'};
873
}
845
}
874
846
847
sub get_results {
848
    my ( $report_id ) = @_;
849
    my $dbh = C4::Context->dbh;
850
    warn $report_id;
851
    return $dbh->selectall_arrayref(q|
852
        SELECT id, report, date_run
853
        FROM saved_reports
854
        WHERE report_id = ?
855
    |, { Slice => {} }, $report_id);
856
}
857
875
sub _get_column_defs {
858
sub _get_column_defs {
876
    my ($cgi) = @_;
859
    my ($cgi) = @_;
877
    my %columns;
860
    my %columns;
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-4 / +12 lines)
Lines 357-363 canned reports and writing custom SQL reports.</p> Link Here
357
                        <td>No</td>
357
                        <td>No</td>
358
                        [% END %]
358
                        [% END %]
359
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
359
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
360
                        <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 %]
360
                        <td>
361
                            [% FOR result IN savedreport.results %]
362
                                <a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&amp;id=[% result.id %]">[% result.date_run %]</a>
363
                                <br/>
364
                            [% END %]
361
                        </td>
365
                        </td>
362
                        <td>
366
                        <td>
363
                            <div class="dropup">
367
                            <div class="dropup">
Lines 915-927 Sub report:<select name="subreport"> Link Here
915
[% END %]
919
[% END %]
916
920
917
921
918
[% IF ( retresults ) %]
922
[% IF saved_results %]
919
<h1>Saved report results</h1>
923
<h1>Saved report results</h1>
920
<h2>[% name %]</h2>
924
<h2>[% name %]</h2>
921
<p>[% notes %]</p>
925
<p>[% notes %]</p>
922
<table>
926
<table>
923
[% FOREACH result IN results %]
927
[% FOREACH rows IN saved_results %]
924
[% result.row %]
928
<tr>
929
[% FOREACH col IN rows %]
930
<td>[% col %]</td>
931
[% END %]
932
<tr>
925
[% END %]
933
[% END %]
926
</table>
934
</table>
927
[% END %]
935
[% 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 106-114 elsif ( $phase eq 'Build new' ) { Link Here
106
    my $subgroup = $input->param('subgroup');
106
    my $subgroup = $input->param('subgroup');
107
    $filter->{group} = $group;
107
    $filter->{group} = $group;
108
    $filter->{subgroup} = $subgroup;
108
    $filter->{subgroup} = $subgroup;
109
    my $reports = get_saved_reports($filter);
110
    for my $report ( @$reports ) {
111
        $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
112
    }
109
    $template->param(
113
    $template->param(
110
        'saved1' => 1,
114
        'saved1' => 1,
111
        'savedreports' => get_saved_reports($filter),
115
        'savedreports' => $reports,
112
        'usecache' => $usecache,
116
        'usecache' => $usecache,
113
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
117
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
114
        filters => $filter,
118
        filters => $filter,
Lines 254-267 elsif ( $phase eq 'Update SQL'){ Link Here
254
}
258
}
255
259
256
elsif ($phase eq 'retrieve results') {
260
elsif ($phase eq 'retrieve results') {
257
	my $id = $input->param('id');
261
    my $id = $input->param('id');
258
	my ($results,$name,$notes) = format_results($id);
262
    my $result = format_results( $id );
259
	# do something
263
    $template->param(
260
	$template->param(
264
        report_name   => $result->{report_name},
261
		'retresults' => 1,
265
        notes         => $result->{notes},
262
		'results' => $results,
266
        saved_results => $result->{results},
263
		'name' => $name,
267
        date_run      => $result->{date_run},
264
		'notes' => $notes,
265
    );
268
    );
266
}
269
}
267
270
268
- 

Return to bug 14435