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::Dates qw/format_date format_date_in_iso/;
Link Here
|
27 |
use C4::Templates qw/themelanguage/; |
28 |
use C4::Templates qw/themelanguage/; |
28 |
use C4::Koha; |
29 |
use C4::Koha; |
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 = format_date_in_iso($date); |
652 |
$date = format_date_in_iso($date); |
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; |