Lines 43-49
BEGIN {
Link Here
|
43 |
get_report_types get_report_areas get_report_groups get_columns build_query get_criteria |
43 |
get_report_types get_report_areas get_report_groups get_columns build_query get_criteria |
44 |
save_report get_saved_reports execute_query get_saved_report create_compound run_compound |
44 |
save_report get_saved_reports execute_query get_saved_report create_compound run_compound |
45 |
get_column_type get_distinct_values save_dictionary get_from_dictionary |
45 |
get_column_type get_distinct_values save_dictionary get_from_dictionary |
46 |
delete_definition delete_report format_results get_sql |
46 |
delete_definition delete_report get_sql |
47 |
nb_rows update_sql build_authorised_value_list |
47 |
nb_rows update_sql build_authorised_value_list |
48 |
GetReservedAuthorisedValues |
48 |
GetReservedAuthorisedValues |
49 |
GetParametersFromSQL |
49 |
GetParametersFromSQL |
Lines 537-546
sub execute_query {
Link Here
|
537 |
$sth->execute(@$sql_params, $offset, $limit); |
537 |
$sth->execute(@$sql_params, $offset, $limit); |
538 |
return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err); |
538 |
return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err); |
539 |
return ( $sth ); |
539 |
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 |
} |
540 |
} |
545 |
|
541 |
|
546 |
=head2 save_report($sql,$name,$type,$notes) |
542 |
=head2 save_report($sql,$name,$type,$notes) |
Lines 594-642
sub update_sql {
Link Here
|
594 |
$dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id ); |
590 |
$dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id ); |
595 |
} |
591 |
} |
596 |
|
592 |
|
597 |
sub store_results { |
|
|
598 |
my ($id,$xml)=@_; |
599 |
my $dbh = C4::Context->dbh(); |
600 |
my $query = "SELECT * FROM saved_reports WHERE report_id=?"; |
601 |
my $sth = $dbh->prepare($query); |
602 |
$sth->execute($id); |
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 |
} |
614 |
|
615 |
sub format_results { |
616 |
my ($id) = @_; |
617 |
my $dbh = C4::Context->dbh(); |
618 |
my $query = "SELECT * FROM saved_reports WHERE report_id = ?"; |
619 |
my $sth = $dbh->prepare($query); |
620 |
$sth->execute($id); |
621 |
my $data = $sth->fetchrow_hashref(); |
622 |
my $dump = new XML::Dumper; |
623 |
my $perl = $dump->xml2pl( $data->{'report'} ); |
624 |
foreach my $row (@$perl) { |
625 |
my $htmlrow="<tr>"; |
626 |
foreach my $key (keys %$row){ |
627 |
$htmlrow .= "<td>$row->{$key}</td>"; |
628 |
} |
629 |
$htmlrow .= "</tr>"; |
630 |
$row->{'row'} = $htmlrow; |
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 |
|
640 |
sub delete_report { |
593 |
sub delete_report { |
641 |
my (@ids) = @_; |
594 |
my (@ids) = @_; |
642 |
return unless @ids; |
595 |
return unless @ids; |
Lines 653-662
sub delete_report {
Link Here
|
653 |
sub get_saved_reports_base_query { |
606 |
sub get_saved_reports_base_query { |
654 |
my $area_name_sql_snippet = get_area_name_sql_snippet; |
607 |
my $area_name_sql_snippet = get_area_name_sql_snippet; |
655 |
return <<EOQ; |
608 |
return <<EOQ; |
656 |
SELECT s.*, r.report, r.date_run, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname, |
609 |
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 |
610 |
b.firstname AS borrowerfirstname, b.surname AS borrowersurname |
658 |
FROM saved_sql s |
611 |
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) |
612 |
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) |
613 |
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) |
614 |
LEFT OUTER JOIN borrowers b USING (borrowernumber) |
Lines 676-686
sub get_saved_reports {
Link Here
|
676 |
if ($filter) { |
628 |
if ($filter) { |
677 |
if (my $date = $filter->{date}) { |
629 |
if (my $date = $filter->{date}) { |
678 |
$date = format_date_in_iso($date); |
630 |
$date = format_date_in_iso($date); |
679 |
push @cond, "DATE(date_run) = ? OR |
631 |
push @cond, "DATE(date_created) = ? OR |
680 |
DATE(date_created) = ? OR |
|
|
681 |
DATE(last_modified) = ? OR |
632 |
DATE(last_modified) = ? OR |
682 |
DATE(last_run) = ?"; |
633 |
DATE(last_run) = ?"; |
683 |
push @args, $date, $date, $date, $date; |
634 |
push @args, $date, $date, $date; |
684 |
} |
635 |
} |
685 |
if (my $author = $filter->{author}) { |
636 |
if (my $author = $filter->{author}) { |
686 |
$author = "%$author%"; |
637 |
$author = "%$author%"; |