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;
# set the version for version checking
-$VERSION = 0.01;
+$VERSION = 0.11;
@ISA = qw(Exporter);
@EXPORT =
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_definition delete_report format_results get_sql );
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',
'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
[ '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";
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];
=cut
sub build_query {
- my ( $columns, $criteria, $orderby, $area, $totals ) = @_;
+ my ( $columns, $criteria, $orderby, $area, $totals, $definition ) = @_;
### $orderby
my $keys = $keys{$area};
my $tables = $table_areas{$area};
my $sql =
- _build_query( $tables, $columns, $criteria, $keys, $orderby, $totals );
+ _build_query( $tables, $columns, $criteria, $keys, $orderby, $totals, $definition );
return ($sql);
}
sub _build_query {
- my ( $tables, $columns, $criteria, $keys, $orderby, $totals ) = @_;
+ my ( $tables, $columns, $criteria, $keys, $orderby, $totals, $definition) = @_;
### $orderby
# $keys is an array of joining constraints
my $dbh = C4::Context->dbh();
$criteria =~ s/AND/WHERE/;
$query .= " $criteria";
}
+ if ($definition){
+ my @definitions = split(',',$definition);
+ my $deftext;
+ foreach my $def (@definitions){
+ my $defin=get_from_dictionary('',$def);
+ $deftext .=" ".$defin->[0]->{'saved_sql'};
+ }
+ if ($query =~ /WHERE/i){
+ $query .= $deftext;
+ }
+ else {
+ $deftext =~ s/AND/WHERE/;
+ $query .= $deftext;
+ }
+ }
if ($totals) {
my $groupby;
my @totcolumns = split( ',', $totals );
}
sub execute_query {
- my ( $sql, $type, $format ) = @_;
+ my ( $sql, $type, $format, $id ) = @_;
my $dbh = C4::Context->dbh();
# take this line out when in production
- $sql .= " LIMIT 10";
+ if ($format eq 'url'){
+ }
+ else {
+ $sql .= " LIMIT 10";
+ }
my $sth = $dbh->prepare($sql);
$sth->execute();
my $colnames=$sth->{'NAME'};
my @results;
- my $row = join ('</th><th>',@$colnames);
- $row = "<tr><th>$row</th></tr>";
+ 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
$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') {
+ 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 );
}
}
+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 * 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;
$sth->execute($id);
my $data = $sth->fetchrow_hashref();
$sth->finish();
- return ( $data->{'savedsql'}, $data->{'type'} );
+ return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'} );
}
=item create_compound($masterID,$subreportID)
while (my $info = $sth->fetchrow_hashref()){
if ($info->{'COLUMN_NAME'} eq $column){
#column we want
- if ($info->{'TYPE_NAME'} eq 'CHAR'){
+ if ($info->{'TYPE_NAME'} eq 'CHAR' || $info->{'TYPE_NAME'} eq 'VARCHAR'){
$info->{'TYPE_NAME'} = 'distinct';
}
return $info->{'TYPE_NAME'};
}
sub get_from_dictionary {
+ my ($area,$id) = @_;
my $dbh = C4::Context->dbh();
my $query = "SELECT * FROM reports_dictionary";
+ if ($area){
+ $query.= " WHERE area = ?";
+ }
+ elsif ($id){
+ $query.= " WHERE id = ?"
+ }
my $sth = $dbh->prepare($query);
- $sth->execute;
+ if ($id){
+ $sth->execute($id);
+ }
+ elsif ($area) {
+ $sth->execute($area);
+ }
+ else {
+ $sth->execute();
+ }
my @loop;
while (my $data = $sth->fetchrow_hashref()){
push @loop,$data;
my $sth = $dbh->prepare($query);
$sth->execute($id);
$sth->finish();
- }
+}
+
+sub get_sql {
+ 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'};
+}
+
=head1 AUTHOR
Chris Cormack <crc@liblime.com>
projects / koha.git / blobdiff