From ce0b4276f67f6591f54253bb7ad89899377e032c Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Mon, 29 Oct 2007 13:22:39 -0500 Subject: [PATCH] Getting stored results working Signed-off-by: Chris Cormack Signed-off-by: Joshua Ferraro --- C4/Reports.pm | 69 ++++++++++++++----- .../modules/reports/guided_reports_start.tmpl | 20 ++++-- reports/guided_reports.pl | 11 +++ tools/runreport.pl | 4 +- 4 files changed, 82 insertions(+), 22 deletions(-) diff --git a/C4/Reports.pm b/C4/Reports.pm index 38cd7ea0fd..0944c5264e 100644 --- a/C4/Reports.pm +++ b/C4/Reports.pm @@ -24,6 +24,7 @@ use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); use C4::Context; use C4::Output; use XML::Simple; +use XML::Dumper; # use Smart::Comments; # use Data::Dumper; @@ -35,15 +36,15 @@ $VERSION = 0.01; qw(get_report_types get_report_areas get_columns build_query get_criteria save_report get_saved_reports execute_query get_saved_report create_compound run_compound get_column_type get_distinct_values save_dictionary get_from_dictionary - delete_definition delete_report); + delete_definition delete_report format_results); our %table_areas; $table_areas{'1'} = [ 'borrowers', 'statistics','items', 'biblioitems' ]; # circulation $table_areas{'2'} = [ 'items', 'biblioitems', 'biblio' ]; # catalogue -$table_areas{'3'} = [ 'borrowers', 'accountlines' ]; # patrons +$table_areas{'3'} = [ 'borrowers' ]; # patrons $table_areas{'4'} = ['aqorders', 'biblio', 'items']; # acquisitions - +$table_areas{'5'} = [ 'borrowers', 'accountlines' ]; # accounts our %keys; $keys{'1'} = [ 'statistics.borrowernumber=borrowers.borrowernumber', @@ -54,11 +55,12 @@ $keys{'2'} = [ 'items.biblioitemnumber=biblioitems.biblioitemnumber', 'biblioitems.biblionumber=biblio.biblionumber' ]; -$keys{'3'} = ['borrowers.borrowernumber=accountlines.borrowernumber']; +$keys{'3'} = [ ]; $keys{'4'} = [ 'aqorders.biblionumber=biblio.biblionumber', 'biblio.biblionumber=items.biblionumber' ]; +$keys{'5'} = ['borrowers.borrowernumber=accountlines.borrowernumber']; # have to do someting here to know if its dropdown, free text, date etc @@ -73,7 +75,7 @@ $criteria{'2'} = [ 'biblioitems.itemtype', 'items.holdingbranch', 'items.homebranch' ,'items.itemlost']; $criteria{'3'} = ['borrowers.branchcode']; $criteria{'4'} = ['aqorders.datereceived|date']; - +$criteria{'5'} = ['borrowers.branchcode']; our %columns; my $columns_def_file = "columns.def"; @@ -139,9 +141,9 @@ sub get_report_areas { my $dbh = C4::Context->dbh(); # FIXME these should be in the database - my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions' ); + my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts'); my @reports2; - for ( my $i = 0 ; $i < 4 ; $i++ ) { + for ( my $i = 0 ; $i < 5 ; $i++ ) { my %hashrep; $hashrep{id} = $i + 1; $hashrep{name} = $reports[$i]; @@ -346,8 +348,7 @@ sub execute_query { $temphash{'row'} = $row; push @results, \%temphash; my $string; - my %xmlhash; - my $i=1; + my @xmlarray; while ( my @data = $sth->fetchrow_array() ) { # tabular @@ -369,8 +370,7 @@ sub execute_query { if ($format eq 'url'){ my $temphash; @$temphash{@$colnames}=@data; - $xmlhash{$i}=$temphash; - $i++; + push @xmlarray,$temphash; } push @results, \%temphash; # } @@ -380,8 +380,9 @@ sub execute_query { return $string; } elsif ($format eq 'url') { - my $url; - my $xml = XMLout(\%xmlhash); + my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id"; + my $dump = new XML::Dumper; + my $xml = $dump->pl2xml( \@xmlarray ); store_results($id,$xml); return $url; } @@ -410,12 +411,46 @@ sub save_report { sub store_results { my ($id,$xml)=@_; my $dbh = C4::Context->dbh(); - my $query = "INSERT INTO saved_reports (report_id,report,date_run) VALUES (?,?,now())"; + my $query = "SELECT * FROM saved_reports WHERE report_id=?"; my $sth = $dbh->prepare($query); - $sth->execute($id,$xml); + $sth->execute($id); + if (my $data=$sth->fetchrow_hashref()){ + my $query2 = "UPDATE saved_reports SET report=?,date_run=now() WHERE report_id=?"; + my $sth2 = $dbh->prepare($query2); + $sth2->execute($xml,$id); + $sth2->finish(); + } + else { + my $query2 = "INSERT INTO saved_reports (report_id,report,date_run) VALUES (?,?,now())"; + my $sth2 = $dbh->prepare($query2); + $sth2->execute($id,$xml); + $sth2->finish(); + } $sth->finish(); } +sub format_results { + my ($id) = @_; + my $dbh = C4::Context->dbh(); + my $query = "SELECT * FROM saved_reports WHERE report_id = ?"; + my $sth = $dbh->prepare($query); + $sth->execute($id); + my $data = $sth->fetchrow_hashref(); + my $dump = new XML::Dumper; + my $perl = $dump->xml2pl( $data->{'report'} ); + foreach my $row (@$perl) { + my $htmlrow=""; + foreach my $key (keys %$row){ + $htmlrow .= "$row->{$key}"; + } + $htmlrow .= ""; + $row->{'row'} = $htmlrow; + } + $sth->finish; + return $perl; + +} + sub delete_report { my ( $id ) = @_; my $dbh = C4::Context->dbh(); @@ -427,7 +462,9 @@ sub delete_report { sub get_saved_reports { my $dbh = C4::Context->dbh(); - my $query = "SELECT * FROM saved_sql ORDER by date_created"; + my $query = "SELECT *,saved_sql.id AS id FROM saved_sql + LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id + ORDER by date_created"; my $sth = $dbh->prepare($query); $sth->execute(); my @reports; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl index 2faaffeeab..9a833cf6bb 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl @@ -44,11 +44,13 @@ reports

Choose the report to run from the list

- + + @@ -88,12 +90,12 @@ reports


Tabular: - + +--> @@ -321,6 +323,16 @@ Sub report:
Report NameTypeNotesDeleteReport NameTypeNotesSaved ResultsDelete
&phase=Run%20this%20report"> "> + &phase=Delete%20Saved">Delete
+ + + +
+ + diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 62f3198e30..4857ecaa26 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -86,6 +86,17 @@ elsif ( $phase eq 'Delete Saved') { } +elsif ($phase eq 'retrieve results') { + my $id = $input->param('id'); + my $results = format_results($id); + # do something + $template->param( + 'retresults' => 1, + 'results' => $results, + ); + +} + elsif ( $phase eq 'Report on this Area' ) { # they have choosen a new report and the area to report on diff --git a/tools/runreport.pl b/tools/runreport.pl index 45d278c160..412d806f99 100755 --- a/tools/runreport.pl +++ b/tools/runreport.pl @@ -9,13 +9,13 @@ use Mail::Sendmail; my ($report,$format,$email) = @ARGV; my ($sql,$type) = get_saved_report($report); -my $results = execute_query($sql,$type,$format); +my $results = execute_query($sql,$type,$format,$report); my $message; if ($format eq 'text'){ $message="$results
"; } if ($format eq 'url'){ - $message="something"; + $message="$results"; } if ($email){ -- 2.39.5