use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
use C4::Context;
-use C4::Dates qw/format_date/;
-use C4::Output;
+use C4::Dates qw/format_date format_date_in_iso/;
+use C4::Templates qw/themelanguage/;
use C4::Dates;
use XML::Simple;
use XML::Dumper;
BEGIN {
# set the version for version checking
- $VERSION = 0.12;
+ $VERSION = 3.07.00.049;
require Exporter;
@ISA = qw(Exporter);
@EXPORT = qw(
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 format_results get_sql
- select_2_select_count_value update_sql
+ nb_rows update_sql
);
}
}
=head1 NAME
-
+
C4::Reports::Guided - Module for generating guided reports
=head1 SYNOPSIS
=head1 DESCRIPTION
+=cut
=head1 METHODS
return ( \@criteria_array );
}
+sub nb_rows($) {
+ my $sql = shift or return;
+ my $sth = C4::Context->dbh->prepare($sql);
+ $sth->execute();
+ my $rows = $sth->fetchall_arrayref();
+ return scalar (@$rows);
+}
+
=item execute_query
-=over
+ ($results, $total, $error) = execute_query($sql, $offset, $limit)
-($results, $total, $error) = execute_query($sql, $offset, $limit)
-=back
+When passed C<$sql>, this function returns an array ref containing a result set
+suitably formatted for display in html or for output as a flat file when passed in
+C<$format> and C<$id>. It also returns the C<$total> records available for the
+supplied query. If passed any query other than a SELECT, or if there is a db error,
+C<$errors> an array ref is returned containing the error after this manner:
- When passed C<$sql>, this function returns an array ref containing a result set
- suitably formatted for display in html or for output as a flat file when passed in
- C<$format> and C<$id>. It also returns the C<$total> records available for the
- supplied query. If passed any query other than a SELECT, or if there is a db error,
- C<$errors> an array ref is returned containing the error after this manner:
+C<$error->{'sqlerr'}> contains the offending SQL keyword.
+C<$error->{'queryerr'}> contains the native db engine error returned for the query.
- C<$error->{'sqlerr'}> contains the offending SQL keyword.
- C<$error->{'queryerr'}> contains the native db engine error returned for the query.
-
- Valid values for C<$format> are 'text,' 'tab,' 'csv,' or 'url. C<$sql>, C<$type>,
- C<$offset>, and C<$limit> are required parameters. If a valid C<$format> is passed
- in, C<$offset> and C<$limit> are ignored for obvious reasons. A LIMIT specified by
- the user in a user-supplied SQL query WILL apply in any case.
+Valid values for C<$format> are 'text,' 'tab,' 'csv,' or 'url. C<$sql>, C<$type>,
+C<$offset>, and C<$limit> are required parameters. If a valid C<$format> is passed
+in, C<$offset> and C<$limit> are ignored for obvious reasons. A LIMIT specified by
+the user in a user-supplied SQL query WILL apply in any case.
=cut
# ~ remove any LIMIT clause
# ~ repace SELECT clause w/ SELECT count(*)
-sub select_2_select_count_value ($) {
- my $sql = shift or return;
- my $countsql = select_2_select_count($sql);
- $debug and warn "original query: $sql\ncount query: $countsql\n";
- my $sth1 = C4::Context->dbh->prepare($countsql);
- $sth1->execute();
- my $total = $sth1->fetchrow();
- $debug and warn "total records for this query: $total\n";
- return $total;
-}
sub select_2_select_count ($) {
# Modify the query passed in to create a count query... (I think this covers all cases -crn)
my ($sql) = strip_limit(shift) or return;
return;
}
$offset = 0 unless $offset;
- $limit = 9999 unless $limit;
+ $limit = 999999 unless $limit;
$debug and print STDERR "execute_query($sql, $offset, $limit)\n";
if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
return (undef, { sqlerr => $1} );
=item save_report($sql,$name,$type,$notes)
-Given some sql and a name this will saved it so that it can resued
+Given some sql and a name this will saved it so that it can reused
+Returns id of the newly created report
=cut
sub save_report {
- my ( $borrowernumber, $sql, $name, $type, $notes ) = @_;
+ my ( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ) = @_;
+ $cache_expiry ||= 300;
my $dbh = C4::Context->dbh();
$sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
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( $borrowernumber, $sql, $name, $type, $notes );
+"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes,cache_expiry, public) VALUES (?,now(),now(),?,?,?,?,?,?)";
+ $dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
+ my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
+ $borrowernumber, $name);
+ return $id;
}
sub update_sql {
my $sql = shift;
my $reportname = shift;
my $notes = shift;
+ my $cache_expiry = shift;
+ my $public = shift;
+
+ # not entirely a magic number, Cache::Memcached::Set assumed any expiry >= (60*60*24*30) is an absolute unix timestamp (rather than relative seconds)
+ if( $cache_expiry >= 2592000 ){
+ die "Please specify a cache expiry less than 30 days\n";
+ }
+
my $dbh = C4::Context->dbh();
$sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
- my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ? WHERE id = ? ";
+ my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
my $sth = $dbh->prepare($query);
- $sth->execute( $sql, $reportname, $notes, $id );
+ $sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id );
$sth->finish();
}
$sth->execute($id);
}
+# $filter is either { date => $d, author => $a, keyword => $kw }
+# or $keyword. Optional.
+my $DATE_FORMAT = "%d/%m/%Y";
sub get_saved_reports {
+ my ($filter) = @_;
+ $filter = { keyword => $filter } if $filter && !ref( $filter );
+
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 $result = $sth->fetchall_arrayref({});
- foreach (@$result){
- $_->{date_created} = format_date($_->{date_created});
-
- my $member = C4::Members::GetMember(borrowernumber=>$_->{borrowernumber});
- $_->{borrowerfirstname} = $member->{firstname};
- $_->{borrowersurname} = $member->{surname};
+ my (@cond,@args);
+ my $query = "SELECT saved_sql.id, report_id, report,
+ date_run, date_created, last_modified, savedsql, last_run,
+ report_name, type, notes,
+ borrowernumber, surname as borrowersurname, firstname as borrowerfirstname,
+ cache_expiry, public
+ FROM saved_sql
+ LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
+ LEFT OUTER JOIN borrowers USING (borrowernumber)";
+ if ($filter) {
+ if (my $date = $filter->{date}) {
+ $date = format_date_in_iso($date);
+ push @cond, "DATE(date_run) = ? OR
+ DATE(date_created) = ? OR
+ DATE(last_modified) = ? OR
+ DATE(last_run) = ?";
+ push @args, $date, $date, $date, $date;
+ }
+ if (my $author = $filter->{author}) {
+ $author = "%$author%";
+ push @cond, "surname LIKE ? OR
+ firstname LIKE ?";
+ push @args, $author, $author;
+ }
+ if (my $keyword = $filter->{keyword}) {
+ $keyword = "%$keyword%";
+ push @cond, "report LIKE ? OR
+ report_name LIKE ? OR
+ notes LIKE ? OR
+ savedsql LIKE ?";
+ push @args, $keyword, $keyword, $keyword, $keyword;
+ }
}
+ $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
+ $query .= " ORDER by date_created";
+
+ my $result = $dbh->selectall_arrayref($query, {Slice => {}}, @args);
+ $_->{date_created} = format_date($_->{date_created}) foreach @$result;
+
return $result;
}
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 $query;
+ my $sth;
+ my $report_arg;
+ if ($#_ == 0 && ref $_[0] ne 'HASH') {
+ ($report_arg) = @_;
+ $query = " SELECT * FROM saved_sql WHERE id = ?";
+ } elsif (ref $_[0] eq 'HASH') {
+ my ($selector) = @_;
+ if ($selector->{name}) {
+ $query = " SELECT * FROM saved_sql WHERE report_name = ?";
+ $report_arg = $selector->{name};
+ } elsif ($selector->{id} || $selector->{id} eq '0') {
+ $query = " SELECT * FROM saved_sql WHERE id = ?";
+ $report_arg = $selector->{id};
+ } else {
+ return;
+ }
+ } else {
+ return;
+ }
+ $sth = $dbh->prepare($query);
+ $sth->execute($report_arg);
my $data = $sth->fetchrow_hashref();
- return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'} );
+ return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'}, $data->{'id'} );
}
=item create_compound($masterID,$subreportID)
my $columns_def_file = "columns.def";
my $htdocs = C4::Context->config('intrahtdocs');
my $section='intranet';
- my ($theme, $lang) = themelanguage($htdocs, $columns_def_file, $section,$cgi);
+ my ($theme, $lang) = C4::Templates::themelanguage($htdocs, $columns_def_file, $section,$cgi);
my $full_path_to_columns_def_file="$htdocs/$theme/$lang/$columns_def_file";
open (COLUMNS,$full_path_to_columns_def_file);
while (my $input = <COLUMNS>){
+ chomp $input;
my @row =split(/\t/,$input);
- $columns{$row[0]}=$row[1];
+ $columns{$row[0]}= $row[1];
}
close COLUMNS;
projects / koha.git / blobdiff