reports/borrowers_out.pl

   1 #!/usr/bin/perl
   2
   3 # Copyright 2000-2002 Katipo Communications
   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 use CGI;
  22 use C4::Auth;
  23 use C4::Context;
  24 use C4::Koha;
  25 use C4::Output;
  26 use C4::Circulation;
  27 use C4::Members;
  28 use C4::Dates qw/format_date_in_iso/;
  29
  30 =head1 NAME
  31
  32 plugin that shows a stats on borrowers
  33
  34 =head1 DESCRIPTION
  35
  36 =over 2
  37
  38 =cut
  39
  40 my $input = new CGI;
  41 my $do_it=$input->param('do_it');
  42 my $fullreportname = "reports/borrowers_out.tmpl";
  43 my $limit = $input->param("Limit");
  44 my $column = $input->param("Criteria");
  45 my @filters = $input->param("Filter");
  46 my $output = $input->param("output");
  47 my $basename = $input->param("basename");
  48 my $mime = $input->param("MIME");
  49 my $del = $input->param("sep");
  50 my ($template, $borrowernumber, $cookie)
  51     = get_template_and_user({template_name => $fullreportname,
  52                 query => $input,
  53                 type => "intranet",
  54                 authnotrequired => 0,
  55                 flagsrequired => {reports => 1},
  56                 debug => 1,
  57                 });
  58 $template->param(do_it => $do_it,
  59         DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
  60         );
  61 if ($do_it) {
  62 # Displaying results
  63     my $results = calculate($limit, $column, \@filters);
  64     if ($output eq "screen"){
  65 # Printing results to screen
  66         $template->param(mainloop => $results);
  67         output_html_with_http_headers $input, $cookie, $template->output;
  68         exit(1);
  69     } else {
  70 # Printing to a csv file
  71         print $input->header(-type => 'application/vnd.sun.xml.calc',
  72                             -encoding    => 'utf-8',
  73                             -attachment=>"$basename.csv",
  74                             -filename=>"$basename.csv" );
  75         my $cols = @$results[0]->{loopcol};
  76         my $lines = @$results[0]->{looprow};
  77         my $sep;
  78         $sep =C4::Context->preference("delimiter");
  79 # header top-right
  80         print "num /". @$results[0]->{column} .$sep;
  81 # Other header
  82         foreach my $col ( @$cols ) {
  83             print $col->{coltitle}.$sep;
  84         }
  85         print "Total\n";
  86 # Table
  87         foreach my $line ( @$lines ) {
  88             my $x = $line->{loopcell};
  89             print $line->{rowtitle}.$sep;
  90             foreach my $cell (@$x) {
  91                 print $cell->{value}.$sep;
  92             }
  93             print $line->{totalrow};
  94             print "\n";
  95         }
  96 # footer
  97         print "TOTAL";
  98         $cols = @$results[0]->{loopfooter};
  99         foreach my $col ( @$cols ) {
 100             print $sep.$col->{totalcol};
 101         }
 102         print $sep.@$results[0]->{total};
 103         exit(1);
 104     }
 105 # Displaying choices
 106 } else {
 107     my $dbh = C4::Context->dbh;
 108     my @values;
 109     my %labels;
 110     my %select;
 111     my $req;
 112
 113     my @mime = ( C4::Context->preference("MIME") );
 114 #       foreach my $mime (@mime){
 115 #               warn "".$mime;
 116 #       }
 117
 118     my $CGIextChoice=CGI::scrolling_list(
 119                 -name     => 'MIME',
 120                 -id       => 'MIME',
 121                 -values   => \@mime,
 122                 -size     => 1,
 123                 -multiple => 0 );
 124
 125     my @dels = ( C4::Context->preference("delimiter") );
 126     my $CGIsepChoice=CGI::scrolling_list(
 127                 -name     => 'sep',
 128                 -id       => 'sep',
 129                 -values   => \@dels,
 130                 -size     => 1,
 131                 -multiple => 0 );
 132
 133     my ($codes,$labels) = GetborCatFromCatType(undef,undef);
 134     my @borcatloop;
 135     foreach my $thisborcat (sort keys %$labels) {
 136             my %row =(value => $thisborcat,
 137                                     description => $labels->{$thisborcat},
 138                             );
 139             push @borcatloop, \%row;
 140     }
 141     $template->param(
 142                     CGIextChoice => $CGIextChoice,
 143                     CGIsepChoice => $CGIsepChoice,
 144                     borcatloop =>\@borcatloop,
 145                     );
 146 output_html_with_http_headers $input, $cookie, $template->output;
 147 }
 148
 149
 150 sub calculate {
 151     my ($line, $column, $filters) = @_;
 152     my @mainloop;
 153     my @loopfooter;
 154     my @loopcol;
 155     my @loopline;
 156     my @looprow;
 157     my %globalline;
 158     my $grantotal =0;
 159 # extract parameters
 160     my $dbh = C4::Context->dbh;
 161
 162 # Filters
 163 # Checking filters
 164 #
 165     my @loopfilter;
 166     for (my $i=0;$i<=2;$i++) {
 167         my %cell;
 168         if ( @$filters[$i] ) {
 169             if (($i==1) and (@$filters[$i-1])) {
 170                 $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
 171             }
 172             $cell{filter} .= @$filters[$i];
 173             $cell{crit} .="Bor Cat" if ($i==0);
 174             $cell{crit} .="Without issues since" if ($i==1);
 175             push @loopfilter, \%cell;
 176         }
 177     }
 178     my $colfield;
 179     my $colorder;
 180     if ($column){
 181         $column = "borrowers.".$column if $column=~/categorycode/;
 182         my @colfilter ;
 183         $colfilter[0] = @$filters[0] if ($column =~ /category/ )  ;
 184     #   $colfilter[0] = @$filters[11] if ($column =~ /sort2/ ) ;
 185     #warn "filtre col ".$colfilter[0]." ".$colfilter[1];
 186
 187     # loop cols.
 188         $colfield .= $column;
 189         $colorder .= $column;
 190
 191         my $strsth2;
 192         $strsth2 .= "select distinctrow $colfield FROM borrowers LEFT JOIN `old_issues` ON issues.borrowernumber=borrowers.borrowernumber";
 193         if ($colfilter[0]) {
 194             $colfilter[0] =~ s/\*/%/g;
 195             $strsth2 .= " and $column LIKE '$colfilter[0]' " ;
 196         }
 197         $strsth2 .=" group by $colfield";
 198         $strsth2 .=" order by $colorder";
 199         warn "". $strsth2;
 200
 201         my $sth2 = $dbh->prepare( $strsth2 );
 202         $sth2->execute;
 203
 204         while (my ($celvalue) = $sth2->fetchrow) {
 205             my %cell;
 206     #           my %ft;
 207     #           warn "coltitle :".$celvalue;
 208             $cell{coltitle} = $celvalue;
 209     #           $ft{totalcol} = 0;
 210             push @loopcol, \%cell;
 211         }
 212     #   warn "fin des titres colonnes";
 213     }
 214
 215     my $i=0;
 216 #       my @totalcol;
 217     my $hilighted=-1;
 218
 219     #Initialization of cell values.....
 220     my @table;
 221
 222 #       warn "init table";
 223     for (my $i=1;$i<=$line;$i++) {
 224         foreach my $col ( @loopcol ) {
 225 #                       warn " init table : $row->{rowtitle} / $col->{coltitle} ";
 226             $table[$i]->{($col->{coltitle})?$col->{coltitle}:"Global"}=0;
 227         }
 228     }
 229
 230
 231 # preparing calculation
 232     my $strcalc ;
 233
 234 # Processing calculation
 235     $strcalc .= "SELECT CONCAT( borrowers.surname , \"\\t\",borrowers.firstname, \"\\t\", borrowers.cardnumber)";
 236     $strcalc .= " , $colfield " if ($colfield);
 237     $strcalc .= " FROM borrowers ";
 238     $strcalc .= "WHERE 1 ";
 239     @$filters[0]=~ s/\*/%/g if (@$filters[0]);
 240     $strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] );
 241     if (@$filters[1]){
 242         my $strqueryfilter="SELECT DISTINCT borrowernumber FROM old_issues where old_issues.timestamp> @$filters[1] ";
 243         my $queryfilter = $dbh->prepare("SELECT DISTINCT borrowernumber FROM old_issues where old_issues.timestamp> ".format_date_in_iso(@$filters[1]));
 244         $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)";
 245
 246 #               $queryfilter->execute(@$filters[1]);
 247 #               while (my ($borrowernumber)=$queryfilter->fetchrow){
 248 #                       $strcalc .= " AND borrowers.borrowernumber <> $borrowernumber ";
 249 #               }
 250     } else {
 251         my $strqueryfilter="SELECT DISTINCT borrowernumber FROM old_issues ";
 252         my $queryfilter = $dbh->prepare("SELECT DISTINCT borrowernumber FROM old_issues ");
 253         $queryfilter->execute;
 254         $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)";
 255 #               while (my ($borrowernumber)=$queryfilter->fetchrow){
 256 #                       $strcalc .= " AND borrowers.borrowernumber <> $borrowernumber ";
 257 #               }
 258     }
 259     $strcalc .= " group by borrowers.borrowernumber";
 260     $strcalc .= ", $colfield" if ($column);
 261     $strcalc .= " order by $colfield " if ($colfield);
 262     my $max;
 263     if (@loopcol) {
 264         $max = $line*@loopcol;
 265     } else { $max=$line;}
 266     $strcalc .= " LIMIT 0,$max" if ($line);
 267     warn "SQL :". $strcalc;
 268
 269     my $dbcalc = $dbh->prepare($strcalc);
 270     $dbcalc->execute;
 271 #       warn "filling table";
 272     my $previous_col;
 273     $i=1;
 274     while (my  @data = $dbcalc->fetchrow) {
 275         my ($row, $col )=@data;
 276         $col = "zzEMPTY" if ($col eq undef);
 277         $i=1 if (($previous_col) and not($col eq $previous_col));
 278         $table[$i]->{$col}=$row;
 279 #               warn " $i $col $row";
 280         $i++;
 281         $previous_col=$col;
 282     }
 283
 284     push @loopcol,{coltitle => "Global"} if not($column);
 285
 286     $max =(($line)?$line:@table -1);
 287     for ($i=1; $i<=$max;$i++) {
 288         my @loopcell;
 289         #@loopcol ensures the order for columns is common with column titles
 290         # and the number matches the number of columns
 291         my $colcount=0;
 292         foreach my $col ( @loopcol ) {
 293             my $value;
 294             if (@loopcol){
 295                 $value =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}};
 296             } else {
 297                 $value =$table[$i]->{"zzEMPTY"};
 298             }
 299             push @loopcell, {value => $value} ;
 300         }
 301         push @looprow,{ 'rowtitle' => $i ,
 302                         'loopcell' => \@loopcell,
 303                         'hilighted' => ($hilighted >0),
 304                     };
 305         $hilighted = -$hilighted;
 306     }
 307
 308
 309
 310     # the header of the table
 311     $globalline{loopfilter}=\@loopfilter;
 312     # the core of the table
 313     $globalline{looprow} = \@looprow;
 314     $globalline{loopcol} = \@loopcol;
 315 #       # the foot (totals by borrower type)
 316     $globalline{loopfooter} = \@loopfooter;
 317     $globalline{total}= $grantotal;
 318     $globalline{line} = $line;
 319     $globalline{column} = $column;
 320     push @mainloop,\%globalline;
 321     return \@mainloop;
 322 }
 323
 324 1;