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($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
25
use vars qw($VERSION @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
BEGIN {
34
BEGIN {
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 343-349 canned reports and writing custom SQL reports.</p> Link Here
343
                        <td>No</td>
343
                        <td>No</td>
344
                        [% END %]
344
                        [% END %]
345
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
345
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
346
                        <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 %]
346
                        <td>
347
                            [% FOR result IN savedreport.results %]
348
                                <a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&amp;id=[% result.id %]">[% result.date_run %]</a>
349
                                <br/>
350
                            [% END %]
347
                        </td>
351
                        </td>
348
                        <td>
352
                        <td>
349
                            <div class="dropdown">
353
                            <div class="dropdown">
Lines 925-937 Sub report:<select name="subreport"> Link Here
925
[% END %]
929
[% END %]
926
930
927
931
928
[% IF ( retresults ) %]
932
[% IF saved_results %]
929
<h1>Saved report results</h1>
933
<h1>Saved report results</h1>
930
<h2>[% name %]</h2>
934
<h2>[% name %]</h2>
931
<p>[% notes %]</p>
935
<p>[% notes %]</p>
932
<table>
936
<table>
933
[% FOREACH result IN results %]
937
[% FOREACH rows IN saved_results %]
934
[% result.row %]
938
<tr>
939
[% FOREACH col IN rows %]
940
<td>[% col %]</td>
941
[% END %]
942
<tr>
935
[% END %]
943
[% END %]
936
</table>
944
</table>
937
[% END %]
945
[% END %]
(-)a/misc/cronjobs/runreport.pl (-11 / +22 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 33-38 use Text::CSV_XS; Link Here
33
use CGI qw ( -utf8 );
32
use CGI qw ( -utf8 );
34
use Carp;
33
use Carp;
35
use Encode;
34
use Encode;
35
use JSON qw( to_json );
36
36
37
use vars qw($VERSION);
37
use vars qw($VERSION);
38
38
Lines 57-63 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ] Link Here
57
   -m --man        full documentation, same as --help --verbose
57
   -m --man        full documentation, same as --help --verbose
58
   -v --verbose    verbose output
58
   -v --verbose    verbose output
59
59
60
   --format=s      selects format. Choice of text, html, csv, or tsv
60
   --format=s      selects format. Choice of text, html, csv or tsv
61
61
62
   -e --email      whether to use e-mail (implied by --to or --from)
62
   -e --email      whether to use e-mail (implied by --to or --from)
63
   --username      username to pass to the SMTP server for authentication
63
   --username      username to pass to the SMTP server for authentication
Lines 66-71 runreport.pl [ -h | -m ] [ -v ] reportID [ reportID ... ] Link Here
66
   --to=s          e-mail address to send report to
66
   --to=s          e-mail address to send report to
67
   --from=s        e-mail address to send report from
67
   --from=s        e-mail address to send report from
68
   --subject=s     subject for the e-mail
68
   --subject=s     subject for the e-mail
69
   --store-results store the result of the report
69
70
70
71
71
 Arguments:
72
 Arguments:
Lines 119-124 E-mail address to send report from. Defaults to KohaAdminEmailAddress. Link Here
119
120
120
Subject for the e-mail message. Defaults to "Koha Saved Report"
121
Subject for the e-mail message. Defaults to "Koha Saved Report"
121
122
123
=item B<--store-results>
124
125
Store the result of the report into the saved_reports DB table.
126
127
To access the results, go on Reports > Guided reports > Saved report.
128
122
=back
129
=back
123
130
124
=head1 DESCRIPTION
131
=head1 DESCRIPTION
Lines 167-172 my $from = ""; Link Here
167
my $subject = 'Koha Saved Report';
174
my $subject = 'Koha Saved Report';
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 184-189 GetOptions( Link Here
184
    'username:s'        => \$username,
192
    'username:s'        => \$username,
185
    'password:s'        => \$password,
193
    'password:s'        => \$password,
186
    'method:s'          => \$method,
194
    'method:s'          => \$method,
195
    'store-results'     => \$store_results,
187
196
188
) or pod2usage(2);
197
) or pod2usage(2);
189
pod2usage( -verbose => 2 ) if ($man);
198
pod2usage( -verbose => 2 ) if ($man);
Lines 236-242 foreach my $report_id (@ARGV) { Link Here
236
    }
245
    }
237
    # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
246
    # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
238
    my ($sth) = execute_query($sql);
247
    my ($sth) = execute_query($sql);
239
    # execute_query(sql, , 0, 20, , )
240
    my $count = scalar($sth->rows);
248
    my $count = scalar($sth->rows);
241
    unless ($count) {
249
    unless ($count) {
242
        print "NO OUTPUT: 0 results from execute_query\n";
250
        print "NO OUTPUT: 0 results from execute_query\n";
Lines 245-256 foreach my $report_id (@ARGV) { Link Here
245
    $verbose and print "$count results from execute_query\n";
253
    $verbose and print "$count results from execute_query\n";
246
254
247
    my $message;
255
    my $message;
256
    my @rows_to_store;
248
    if ($format eq 'html') {
257
    if ($format eq 'html') {
249
        my $cgi = CGI->new();
258
        my $cgi = CGI->new();
250
        my @rows = ();
259
        my @rows;
251
        while (my $line = $sth->fetchrow_arrayref) {
260
        while (my $line = $sth->fetchrow_arrayref) {
252
            foreach (@$line) { defined($_) or $_ = ''; }    # catch undef values, replace w/ ''
261
            foreach (@$line) { defined($_) or $_ = ''; }    # catch undef values, replace w/ ''
253
            push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
262
            push @rows, $cgi->TR( join('', $cgi->td($line)) ) . "\n";
263
            push @rows_to_store, [@$line] if $store_results;
254
        }
264
        }
255
        $message = $cgi->table(join "", @rows);
265
        $message = $cgi->table(join "", @rows);
256
    } elsif ($format eq 'csv') {
266
    } elsif ($format eq 'csv') {
Lines 260-274 foreach my $report_id (@ARGV) { Link Here
260
            });
270
            });
261
        while (my $line = $sth->fetchrow_arrayref) {
271
        while (my $line = $sth->fetchrow_arrayref) {
262
            $csv->combine(@$line);
272
            $csv->combine(@$line);
263
#            foreach (@$line) {
264
#                defined($_) or $_ = '';
265
#                $_ =~ s/$quote/\\$quote/g;
266
#                $_ = "$quote$_$quote";
267
#            }    # catch undef values, replace w/ ''
268
#            $message .= join ($separator, @$line) . "\n";
269
            $message .= $csv->string() . "\n";
273
            $message .= $csv->string() . "\n";
274
            push @rows_to_store, [@$line] if $store_results;
270
        }
275
        }
271
    }
276
    }
277
278
    if ( $store_results ) {
279
        my $json = to_json( \@rows_to_store );
280
        C4::Reports::Guided::store_results( $report_id, $json );
281
    }
282
272
    if ($email){
283
    if ($email){
273
        my $email = Koha::Email->new();
284
        my $email = Koha::Email->new();
274
        my %mail;
285
        my %mail;
(-)a/reports/guided_reports.pl (-10 / +12 lines)
Lines 104-112 elsif ( $phase eq 'Build new' ) { Link Here
104
    my $subgroup = $input->param('subgroup');
104
    my $subgroup = $input->param('subgroup');
105
    $filter->{group} = $group;
105
    $filter->{group} = $group;
106
    $filter->{subgroup} = $subgroup;
106
    $filter->{subgroup} = $subgroup;
107
    my $reports = get_saved_reports($filter);
108
    for my $report ( @$reports ) {
109
        $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
110
    }
107
    $template->param(
111
    $template->param(
108
        'saved1' => 1,
112
        'saved1' => 1,
109
        'savedreports' => get_saved_reports($filter),
113
        'savedreports' => $reports,
110
        'usecache' => $usecache,
114
        'usecache' => $usecache,
111
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
115
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
112
        filters => $filter,
116
        filters => $filter,
Lines 252-265 elsif ( $phase eq 'Update SQL'){ Link Here
252
}
256
}
253
257
254
elsif ($phase eq 'retrieve results') {
258
elsif ($phase eq 'retrieve results') {
255
	my $id = $input->param('id');
259
    my $id = $input->param('id');
256
	my ($results,$name,$notes) = format_results($id);
260
    my $result = format_results( $id );
257
	# do something
261
    $template->param(
258
	$template->param(
262
        report_name   => $result->{report_name},
259
		'retresults' => 1,
263
        notes         => $result->{notes},
260
		'results' => $results,
264
        saved_results => $result->{results},
261
		'name' => $name,
265
        date_run      => $result->{date_run},
262
		'notes' => $notes,
263
    );
266
    );
264
}
267
}
265
268
266
- 

Return to bug 14435