X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FReports.pm;h=3e2f1959bea0f3046146a1061541990a8de705d8;hb=39d1b7e61b4e11d76e443f7755aba96e3cf13ab6;hp=045e78f75a35f96c32e59895ab2e1f5689a45fdf;hpb=ad5161d2dccfb3a43c7a2049298703c27f8fa34d;p=koha_gimpoz diff --git a/C4/Reports.pm b/C4/Reports.pm index 045e78f75a..3e2f1959be 100644 --- a/C4/Reports.pm +++ b/C4/Reports.pm @@ -13,639 +13,75 @@ package C4::Reports; # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR # A PARTICULAR PURPOSE. See the GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along with -# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, -# Suite 330, Boston, MA 02111-1307 USA +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. use strict; +#use warnings; FIXME - Bug 2505 use CGI; 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; +use C4::Debug; BEGIN { - # set the version for version checking - $VERSION = 0.12; - require Exporter; - @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_report format_results get_sql - ); + # set the version for version checking + $VERSION = 0.13; + require Exporter; + @ISA = qw(Exporter); + @EXPORT = qw( + GetDelimiterChoices + ); } -our %table_areas; -$table_areas{'1'} = - [ 'borrowers', 'statistics','items', 'biblioitems' ]; # circulation -$table_areas{'2'} = [ 'items', 'biblioitems', 'biblio' ]; # catalogue -$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.itemnumber = statistics.itemnumber', - 'biblioitems.biblioitemnumber = items.biblioitemnumber' -]; -$keys{'2'} = [ - 'items.biblioitemnumber=biblioitems.biblioitemnumber', - 'biblioitems.biblionumber=biblio.biblionumber' -]; -$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 - -our %criteria; -$criteria{'1'} = [ - 'statistics.type', 'borrowers.categorycode', - 'statistics.branch', 'biblioitems.itemtype', - 'biblioitems.publicationyear|date', - 'items.dateaccessioned|date' -]; -$criteria{'2'} = - [ 'biblioitems.itemtype', 'items.holdingbranch', 'items.homebranch' ,'items.itemlost']; -$criteria{'3'} = ['borrowers.branchcode']; -$criteria{'4'} = ['aqorders.datereceived|date']; -$criteria{'5'} = ['borrowers.branchcode']; - - =head1 NAME - -C4::Reports - Module for generating reports - -=head1 SYNOPSIS - use C4::Reports; +C4::Reports - Module for generating reports =head1 DESCRIPTION +This module contains functions common to reports. -=head1 METHODS - -=over 2 - -=cut - -=item get_report_types() - -This will return a list of all the available report types - -=cut - -sub get_report_types { - my $dbh = C4::Context->dbh(); - - # FIXME these should be in the database perhaps - my @reports = ( 'Tabular', 'Summary', 'Matrix' ); - my @reports2; - for ( my $i = 0 ; $i < 3 ; $i++ ) { - my %hashrep; - $hashrep{id} = $i + 1; - $hashrep{name} = $reports[$i]; - push @reports2, \%hashrep; - } - return ( \@reports2 ); - -} - -=item get_report_areas() - -This will return a list of all the available report areas - -=cut - -sub get_report_areas { - my $dbh = C4::Context->dbh(); - - # FIXME these should be in the database - my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts'); - my @reports2; - for ( my $i = 0 ; $i < 5 ; $i++ ) { - my %hashrep; - $hashrep{id} = $i + 1; - $hashrep{name} = $reports[$i]; - push @reports2, \%hashrep; - } - return ( \@reports2 ); - -} - -=item get_all_tables() - -This will return a list of all tables in the database - -=cut - -sub get_all_tables { - my $dbh = C4::Context->dbh(); - my $query = "SHOW TABLES"; - my $sth = $dbh->prepare($query); - $sth->execute(); - my @tables; - while ( my $data = $sth->fetchrow_arrayref() ) { - push @tables, $data->[0]; - } - $sth->finish(); - return ( \@tables ); - -} - -=item get_columns($area) - -This will return a list of all columns for a report area - -=cut - -sub get_columns { - - # this calls the internal fucntion _get_columns - my ($area,$cgi) = @_; - my $tables = $table_areas{$area}; - my @allcolumns; - my $first = 1; - foreach my $table (@$tables) { - my @columns = _get_columns($table,$cgi, $first); - $first = 0; - push @allcolumns, @columns; - } - return ( \@allcolumns ); -} - -sub _get_columns { - my ($tablename,$cgi, $first) = @_; - my $dbh = C4::Context->dbh(); - my $sth = $dbh->prepare("show columns from $tablename"); - $sth->execute(); - my @columns; - my $column_defs = _get_column_defs($cgi); - my %tablehash; - $tablehash{'table'}=$tablename; - $tablehash{'__first__'} = $first; - push @columns, \%tablehash; - while ( my $data = $sth->fetchrow_arrayref() ) { - my %temphash; - $temphash{'name'} = "$tablename.$data->[0]"; - $temphash{'description'} = $column_defs->{"$tablename.$data->[0]"}; - push @columns, \%temphash; - } - $sth->finish(); - return (@columns); -} - -=item build_query($columns,$criteria,$orderby,$area) - -This will build the sql needed to return the results asked for, -$columns is expected to be of the format tablename.columnname. -This is what get_columns returns. - -=cut - -sub build_query { - 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, $definition ); - return ($sql); -} - -sub _build_query { - my ( $tables, $columns, $criteria, $keys, $orderby, $totals, $definition) = @_; -### $orderby - # $keys is an array of joining constraints - my $dbh = C4::Context->dbh(); - my $joinedtables = join( ',', @$tables ); - my $joinedcolumns = join( ',', @$columns ); - my $joinedkeys = join( ' AND ', @$keys ); - my $query = - "SELECT $totals $joinedcolumns FROM $tables->[0] "; - for (my $i=1;$i<@$tables;$i++){ - $query .= "LEFT JOIN $tables->[$i] on ($keys->[$i-1]) "; - } +=head1 EXPORTED FUNCTIONS - if ($criteria) { - $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 ); - foreach my $total (@totcolumns) { - if ( $total =~ /\((.*)\)/ ) { - if ( $groupby eq '' ) { - $groupby = " GROUP BY $1"; - } - else { - $groupby .= ",$1"; - } - } - } - $query .= $groupby; - } - if ($orderby) { - $query .= $orderby; - } - return ($query); -} +=head2 GetDelimiterChoices -=item get_criteria($area,$cgi); + my $delims = GetDelimiterChoices; -Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values. +This will return a list of all the available delimiters. =cut -sub get_criteria { - my ($area,$cgi) = @_; - my $dbh = C4::Context->dbh(); - my $crit = $criteria{$area}; - my $column_defs = _get_column_defs($cgi); - my @criteria_array; - foreach my $localcrit (@$crit) { - my ( $value, $type ) = split( /\|/, $localcrit ); - my ( $table, $column ) = split( /\./, $value ); - if ( $type eq 'date' ) { - my %temp; - $temp{'name'} = $value; - $temp{'date'} = 1; - $temp{'description'} = $column_defs->{$value}; - push @criteria_array, \%temp; - } - else { +sub GetDelimiterChoices { + my $dbh = C4::Context->dbh; - my $query = - "SELECT distinct($column) as availablevalues FROM $table"; - my $sth = $dbh->prepare($query); - $sth->execute(); - my @values; - while ( my $row = $sth->fetchrow_hashref() ) { - push @values, $row; - ### $row; - } - $sth->finish(); - my %temp; - $temp{'name'} = $value; - $temp{'description'} = $column_defs->{$value}; - $temp{'values'} = \@values; - push @criteria_array, \%temp; - } - } - return ( \@criteria_array ); -} + my $sth = $dbh->prepare(" + SELECT options, value + FROM systempreferences + WHERE variable = 'delimiter' + "); -sub execute_query { - my ( $sql, $type, $format, $id ) = @_; - my $dbh = C4::Context->dbh(); - - # take this line out when in production - if ($format eq 'url'){ - } - else { - $sql .= " LIMIT 10"; - } - my $sth = $dbh->prepare($sql); $sth->execute(); - my $colnames=$sth->{'NAME'}; - my @results; - my $row; - my %temphash; - $row = join ('