C4/Reports.pm

   1 package C4::Reports;
   2
   3 # Copyright 2007 Liblime Ltd
   4 #
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it under the
   8 # terms of the GNU General Public License as published by the Free Software
   9 # Foundation; either version 2 of the License, or (at your option) any later
  10 # version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  14 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License along with
  17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  18 # Suite 330, Boston, MA  02111-1307 USA
  19
  20 use strict;
  21 require Exporter;
  22
  23 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  24 use C4::Context;
  25 use C4::Output;
  26 # use Smart::Comments;
  27 # use Data::Dumper;
  28
  29 # set the version for version checking
  30 $VERSION = 0.01;
  31
  32 @ISA = qw(Exporter);
  33 @EXPORT =
  34   qw(get_report_types get_report_areas get_columns build_query get_criteria
  35   save_report get_saved_reports execute_query get_saved_report create_compound run_compound
  36   get_column_type get_distinct_values save_dictionary get_from_dictionary
  37   delete_definition);
  38
  39 our %table_areas;
  40 $table_areas{'1'} =
  41   [ 'borrowers', 'statistics','items', 'biblioitems' ];    # circulation
  42 $table_areas{'2'} = [ 'items', 'biblioitems', 'biblio' ];   # catalogue
  43 $table_areas{'3'} = [ 'borrowers', 'accountlines' ];        # patrons
  44 $table_areas{'4'} = ['aqorders', 'biblio', 'items'];        # acquisitions
  45
  46 our %keys;
  47 $keys{'1'} = [
  48     'statistics.borrowernumber=borrowers.borrowernumber',
  49     'items.itemnumber = statistics.itemnumber',
  50     'biblioitems.biblioitemnumber = items.biblioitemnumber'
  51 ];
  52 $keys{'2'} = [
  53     'items.biblioitemnumber=biblioitems.biblioitemnumber',
  54     'biblioitems.biblionumber=biblio.biblionumber'
  55 ];
  56 $keys{'3'} = ['borrowers.borrowernumber=accountlines.borrowernumber'];
  57 $keys{'4'} = [
  58         'aqorders.biblionumber=biblio.biblionumber',
  59         'biblio.biblionumber=items.biblionumber'
  60 ];
  61
  62 # have to do someting here to know if its dropdown, free text, date etc
  63
  64 our %criteria;
  65 $criteria{'1'} = [
  66     'statistics.type',   'borrowers.categorycode',
  67     'statistics.branch', 'biblioitems.itemtype',
  68     'biblioitems.publicationyear|date',
  69     'items.dateaccessioned|date'
  70 ];
  71 $criteria{'2'} =
  72   [ 'biblioitems.itemtype', 'items.holdingbranch', 'items.homebranch' ,'items.itemlost'];
  73 $criteria{'3'} = ['borrowers.branchcode'];
  74 $criteria{'4'} = ['aqorders.datereceived|date'];
  75
  76
  77 our %columns;
  78 my $columns_def_file = "columns.def";
  79 my $htdocs = C4::Context->config('intrahtdocs');
  80 my $section='intranet';
  81 my ($theme, $lang) = themelanguage($htdocs, $columns_def_file, $section);
  82
  83 my $columns_def_file="$htdocs/$theme/$lang/$columns_def_file";
  84 open (COLUMNS,$columns_def_file);
  85 while (my $input = <COLUMNS>){
  86         my @row =split(/\t/,$input);
  87         $columns{$row[0]}=$row[1];
  88 }
  89
  90 close COLUMNS;
  91
  92 =head1 NAME
  93
  94 C4::Reports - Module for generating reports
  95
  96 =head1 SYNOPSIS
  97
  98   use C4::Reports;
  99
 100 =head1 DESCRIPTION
 101
 102
 103 =head1 METHODS
 104
 105 =over 2
 106
 107 =cut
 108
 109 =item get_report_types()
 110
 111 This will return a list of all the available report types
 112
 113 =cut
 114
 115 sub get_report_types {
 116     my $dbh = C4::Context->dbh();
 117
 118     # FIXME these should be in the database perhaps
 119     my @reports = ( 'Tabular', 'Summary', 'Matrix' );
 120     my @reports2;
 121     for ( my $i = 0 ; $i < 3 ; $i++ ) {
 122         my %hashrep;
 123         $hashrep{id}   = $i + 1;
 124         $hashrep{name} = $reports[$i];
 125         push @reports2, \%hashrep;
 126     }
 127     return ( \@reports2 );
 128
 129 }
 130
 131 =item get_report_areas()
 132
 133 This will return a list of all the available report areas
 134
 135 =cut
 136
 137 sub get_report_areas {
 138     my $dbh = C4::Context->dbh();
 139
 140     # FIXME these should be in the database
 141     my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions' );
 142     my @reports2;
 143     for ( my $i = 0 ; $i < 4 ; $i++ ) {
 144         my %hashrep;
 145         $hashrep{id}   = $i + 1;
 146         $hashrep{name} = $reports[$i];
 147         push @reports2, \%hashrep;
 148     }
 149     return ( \@reports2 );
 150
 151 }
 152
 153 =item get_all_tables()
 154
 155 This will return a list of all tables in the database
 156
 157 =cut
 158
 159 sub get_all_tables {
 160     my $dbh   = C4::Context->dbh();
 161     my $query = "SHOW TABLES";
 162     my $sth   = $dbh->prepare($query);
 163     $sth->execute();
 164     my @tables;
 165     while ( my $data = $sth->fetchrow_arrayref() ) {
 166         push @tables, $data->[0];
 167     }
 168     $sth->finish();
 169     return ( \@tables );
 170
 171 }
 172
 173 =item get_columns($area)
 174
 175 This will return a list of all columns for a report area
 176
 177 =cut
 178
 179 sub get_columns {
 180
 181     # this calls the internal fucntion _get_columns
 182     my ($area) = @_;
 183     my $tables = $table_areas{$area};
 184     my @allcolumns;
 185     foreach my $table (@$tables) {
 186         my @columns = _get_columns($table);
 187         push @allcolumns, @columns;
 188     }
 189     return ( \@allcolumns );
 190 }
 191
 192 sub _get_columns {
 193     my ($tablename) = @_;
 194     my $dbh         = C4::Context->dbh();
 195     my $sth         = $dbh->prepare("show columns from $tablename");
 196     $sth->execute();
 197     my @columns;
 198         my %tablehash;
 199         $tablehash{'table'}=$tablename;
 200         push @columns, \%tablehash;
 201     while ( my $data = $sth->fetchrow_arrayref() ) {
 202         my %temphash;
 203         $temphash{'name'}        = "$tablename.$data->[0]";
 204         $temphash{'description'} = $columns{"$tablename.$data->[0]"};
 205         push @columns, \%temphash;
 206     }
 207     $sth->finish();
 208     return (@columns);
 209 }
 210
 211 =item build_query($columns,$criteria,$orderby,$area)
 212
 213 This will build the sql needed to return the results asked for,
 214 $columns is expected to be of the format tablename.columnname.
 215 This is what get_columns returns.
 216
 217 =cut
 218
 219 sub build_query {
 220     my ( $columns, $criteria, $orderby, $area, $totals ) = @_;
 221 ### $orderby
 222     my $keys   = $keys{$area};
 223     my $tables = $table_areas{$area};
 224
 225     my $sql =
 226       _build_query( $tables, $columns, $criteria, $keys, $orderby, $totals );
 227     return ($sql);
 228 }
 229
 230 sub _build_query {
 231     my ( $tables, $columns, $criteria, $keys, $orderby, $totals ) = @_;
 232 ### $orderby
 233     # $keys is an array of joining constraints
 234     my $dbh           = C4::Context->dbh();
 235     my $joinedtables  = join( ',', @$tables );
 236     my $joinedcolumns = join( ',', @$columns );
 237     my $joinedkeys    = join( ' AND ', @$keys );
 238     my $query =
 239       "SELECT $totals $joinedcolumns FROM $tables->[0] ";
 240         for (my $i=1;$i<@$tables;$i++){
 241                 $query .= "LEFT JOIN $tables->[$i] on ($keys->[$i-1]) ";
 242         }
 243
 244     if ($criteria) {
 245                 $criteria =~ s/AND/WHERE/;
 246         $query .= " $criteria";
 247     }
 248     if ($totals) {
 249         my $groupby;
 250         my @totcolumns = split( ',', $totals );
 251         foreach my $total (@totcolumns) {
 252             if ( $total =~ /\((.*)\)/ ) {
 253                 if ( $groupby eq '' ) {
 254                     $groupby = " GROUP BY $1";
 255                 }
 256                 else {
 257                     $groupby .= ",$1";
 258                 }
 259             }
 260         }
 261         $query .= $groupby;
 262     }
 263     if ($orderby) {
 264         $query .= $orderby;
 265     }
 266     return ($query);
 267 }
 268
 269 =item get_criteria($area);
 270
 271 Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values.
 272
 273 =cut
 274
 275 sub get_criteria {
 276     my ($area) = @_;
 277     my $dbh    = C4::Context->dbh();
 278     my $crit   = $criteria{$area};
 279     my @criteria_array;
 280     foreach my $localcrit (@$crit) {
 281         my ( $value, $type )   = split( /\|/, $localcrit );
 282         my ( $table, $column ) = split( /\./, $value );
 283         if ( $type eq 'date' ) {
 284                         my %temp;
 285             $temp{'name'}   = $value;
 286             $temp{'date'}   = 1;
 287                         $temp{'description'} = $columns{$value};
 288             push @criteria_array, \%temp;
 289         }
 290         else {
 291
 292             my $query =
 293               "SELECT distinct($column) as availablevalues FROM $table";
 294             my $sth = $dbh->prepare($query);
 295             $sth->execute();
 296             my @values;
 297             while ( my $row = $sth->fetchrow_hashref() ) {
 298                 push @values, $row;
 299                 ### $row;
 300             }
 301             $sth->finish();
 302             my %temp;
 303             $temp{'name'}   = $value;
 304                         $temp{'description'} = $columns{$value};
 305             $temp{'values'} = \@values;
 306             push @criteria_array, \%temp;
 307         }
 308     }
 309     return ( \@criteria_array );
 310 }
 311
 312 sub execute_query {
 313     my ( $sql, $type, $format ) = @_;
 314     my $dbh = C4::Context->dbh();
 315
 316     # take this line out when in production
 317     $sql .= " LIMIT 10";
 318     my $sth = $dbh->prepare($sql);
 319     $sth->execute();
 320         my $colnames=$sth->{'NAME'};
 321         my @results;
 322         my $row = join ('</th><th>',@$colnames);
 323         $row = "<tr><th>$row</th></tr>";
 324         my %temphash;
 325         $temphash{'row'} = $row;
 326         push @results, \%temphash;
 327
 328     my $string;
 329     while ( my @data = $sth->fetchrow_array() ) {
 330
 331             # tabular
 332             my %temphash;
 333             my $row = join( '</td><td>', @data );
 334             $row = "<tr><td>$row</td></tr>";
 335             $temphash{'row'} = $row;
 336             if ( $format eq 'text' ) {
 337                 $string .= "\n" . $row;
 338             }
 339                         if ($format eq 'tab' ){
 340                                 $row = join("\t",@data);
 341                                 $string .="\n" . $row;
 342                         }
 343                         if ($format eq 'csv' ){
 344                                 $row = join(",",@data);
 345                                 $string .="\n" . $row;
 346                         }
 347
 348             push @results, \%temphash;
 349 #        }
 350     }
 351     $sth->finish();
 352     if ( $format eq 'text' || $format eq 'tab' || $format eq 'csv') {
 353         return $string;
 354     }
 355     else {
 356         return ( \@results );
 357     }
 358 }
 359
 360 =item save_report($sql,$name,$type,$notes)
 361
 362 Given some sql and a name this will saved it so that it can resued
 363
 364 =cut
 365
 366 sub save_report {
 367     my ( $sql, $name, $type, $notes ) = @_;
 368     my $dbh = C4::Context->dbh();
 369     my $query =
 370 "INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes)  VALUES (?,now(),now(),?,?,?,?)";
 371     my $sth = $dbh->prepare($query);
 372     $sth->execute( 0, $sql, $name, $type, $notes );
 373     $sth->finish();
 374
 375 }
 376
 377 sub get_saved_reports {
 378     my $dbh   = C4::Context->dbh();
 379     my $query = "SELECT * FROM saved_sql ORDER by date_created";
 380     my $sth   = $dbh->prepare($query);
 381     $sth->execute();
 382     my @reports;
 383     while ( my $data = $sth->fetchrow_hashref() ) {
 384         push @reports, $data;
 385     }
 386     $sth->finish();
 387     return ( \@reports );
 388 }
 389
 390 sub get_saved_report {
 391     my ($id)  = @_;
 392     my $dbh   = C4::Context->dbh();
 393     my $query = " SELECT * FROM saved_sql WHERE id = ?";
 394     my $sth   = $dbh->prepare($query);
 395     $sth->execute($id);
 396     my $data = $sth->fetchrow_hashref();
 397     $sth->finish();
 398     return ( $data->{'savedsql'}, $data->{'type'} );
 399 }
 400
 401 =item create_compound($masterID,$subreportID)
 402
 403 This will take 2 reports and create a compound report using both of them
 404
 405 =cut
 406
 407 sub create_compound {
 408         my ($masterID,$subreportID) = @_;
 409         my $dbh = C4::Context->dbh();
 410         # get the reports
 411         my ($mastersql,$mastertype) = get_saved_report($masterID);
 412         my ($subsql,$subtype) = get_saved_report($subreportID);
 413
 414         # now we have to do some checking to see how these two will fit together
 415         # or if they will
 416         my ($mastertables,$subtables);
 417         if ($mastersql =~ / from (.*) where /i){
 418                 $mastertables = $1;
 419         }
 420         if ($subsql =~ / from (.*) where /i){
 421                 $subtables = $1;
 422         }
 423         return ($mastertables,$subtables);
 424 }
 425
 426 =item get_column_type($column)
 427
 428 This takes a column name of the format table.column and will return what type it is
 429 (free text, set values, date)
 430
 431 =cut
 432
 433 sub get_column_type {
 434         my ($tablecolumn) = @_;
 435         my ($table,$column) = split(/\./,$tablecolumn);
 436         my $dbh = C4::Context->dbh();
 437         my $catalog;
 438         my $schema;
 439
 440         # mysql doesnt support a column selection, set column to %
 441         my $tempcolumn='%';
 442         my $sth = $dbh->column_info( $catalog, $schema, $table, $tempcolumn ) || die $dbh->errstr;
 443         while (my $info = $sth->fetchrow_hashref()){
 444                 if ($info->{'COLUMN_NAME'} eq $column){
 445                         #column we want
 446                         if ($info->{'TYPE_NAME'} eq 'CHAR'){
 447                                 $info->{'TYPE_NAME'} = 'distinct';
 448                         }
 449                         return $info->{'TYPE_NAME'};
 450                 }
 451         }
 452         $sth->finish();
 453 }
 454
 455 =item get_distinct_values($column)
 456
 457 Given a column name, return an arrary ref of hashrefs suitable for use as a tmpl_loop
 458 with the distinct values of the column
 459
 460 =cut
 461
 462 sub get_distinct_values {
 463         my ($tablecolumn) = @_;
 464         my ($table,$column) = split(/\./,$tablecolumn);
 465         my $dbh = C4::Context->dbh();
 466         my $query =
 467           "SELECT distinct($column) as availablevalues FROM $table";
 468         my $sth = $dbh->prepare($query);
 469         $sth->execute();
 470         my @values;
 471         while ( my $row = $sth->fetchrow_hashref() ) {
 472                 push @values, $row;
 473         }
 474         $sth->finish();
 475         return \@values;
 476 }
 477
 478 sub save_dictionary {
 479         my ($name,$description,$sql,$area) = @_;
 480         my $dbh = C4::Context->dbh();
 481         my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,area,date_created,date_modified)
 482   VALUES (?,?,?,?,now(),now())";
 483     my $sth = $dbh->prepare($query);
 484     $sth->execute($name,$description,$sql,$area) || return 0;
 485     $sth->finish();
 486     return 1;
 487 }
 488
 489 sub get_from_dictionary {
 490         my $dbh = C4::Context->dbh();
 491         my $query = "SELECT * FROM reports_dictionary";
 492         my $sth = $dbh->prepare($query);
 493         $sth->execute;
 494         my @loop;
 495         while (my $data = $sth->fetchrow_hashref()){
 496                 push @loop,$data;
 497
 498                 }
 499         $sth->finish();
 500         return (\@loop);
 501 }
 502
 503 sub delete_definition {
 504         my ($id) = @_;
 505         my $dbh = C4::Context->dbh();
 506         my $query = "DELETE FROM reports_dictionary WHERE id = ?";
 507         my $sth = $dbh->prepare($query);
 508         $sth->execute($id);
 509         $sth->finish();
 510         }
 511 =head1 AUTHOR
 512
 513 Chris Cormack <crc@liblime.com>
 514
 515 =cut
 516
 517 1;