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; |