- my $colnames=$sth->{'NAME'};
- my @results;
- my $row;
- my %temphash;
- $row = join ('</th><th>',@$colnames);
- $row = "<tr><th>$row</th></tr>";
- $temphash{'row'} = $row;
- push @results, \%temphash;
- my $string;
- my @xmlarray;
- while ( my @data = $sth->fetchrow_array() ) {
-
- # tabular
- my %temphash;
- my $row = join( '</td><td>', @data );
- $row = "<tr><td>$row</td></tr>";
- $temphash{'row'} = $row;
- if ( $format eq 'text' ) {
- $string .= "\n" . $row;
- }
- if ($format eq 'tab' ){
- $row = join("\t",@data);
- $string .="\n" . $row;
- }
- if ($format eq 'csv' ){
- $row = join(",",@data);
- $string .="\n" . $row;
- }
- if ($format eq 'url'){
- my $temphash;
- @$temphash{@$colnames}=@data;
- push @xmlarray,$temphash;
- }
- push @results, \%temphash;
-# }
- }
- $sth->finish();
- if ( $format eq 'text' || $format eq 'tab' || $format eq 'csv' ) {
- return $string;
- }
- elsif ($format eq 'url') {
- 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;
- }
- else {
- return ( \@results );
- }
-}
-
-=item save_report($sql,$name,$type,$notes)
-
-Given some sql and a name this will saved it so that it can resued
-
-=cut
-
-sub save_report {
- my ( $sql, $name, $type, $notes ) = @_;
- my $dbh = C4::Context->dbh();
- my $query =
-"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes) VALUES (?,now(),now(),?,?,?,?)";
- my $sth = $dbh->prepare($query);
- $sth->execute( 0, $sql, $name, $type, $notes );
- $sth->finish();
-
-}
-
-sub store_results {
- my ($id,$xml)=@_;
- my $dbh = C4::Context->dbh();
- my $query = "SELECT * FROM saved_reports WHERE report_id=?";
- my $sth = $dbh->prepare($query);
- $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="<tr>";
- foreach my $key (keys %$row){
- $htmlrow .= "<td>$row->{$key}</td>";
- }
- $htmlrow .= "</tr>";
- $row->{'row'} = $htmlrow;
- }
- $sth->finish;
- $query = "SELECT * FROM saved_sql WHERE id = ?";
- $sth = $dbh->prepare($query);
- $sth->execute($id);
- $data = $sth->fetchrow_hashref();
- $sth->finish();
- return ($perl,$data->{'report_name'},$data->{'notes'});
-}
-
-sub delete_report {
- my ( $id ) = @_;
- my $dbh = C4::Context->dbh();
- my $query = "DELETE FROM saved_sql WHERE id = ?";
- my $sth = $dbh->prepare($query);
- $sth->execute($id);
- $sth->finish();
-}
-
-sub get_saved_reports {
- my $dbh = C4::Context->dbh();
- 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;
- while ( my $data = $sth->fetchrow_hashref() ) {
- push @reports, $data;
- }
- $sth->finish();
- return ( \@reports );
-}
-
-sub get_saved_report {
- my ($id) = @_;
- my $dbh = C4::Context->dbh();
- my $query = " SELECT * FROM saved_sql WHERE id = ?";
- my $sth = $dbh->prepare($query);
- $sth->execute($id);
- my $data = $sth->fetchrow_hashref();
- $sth->finish();
- return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'} );
-}
-
-=item create_compound($masterID,$subreportID)