[Koha-cvs] CVS: koha/reports borrowers_stats.pl,NONE,1.1 issues_by_borrower_category.plugin,1.2,1.3

[Henri-Damien LAURENT]

Update of /cvsroot/koha/koha/reports
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv7891/reports

Modified Files:
        issues_by_borrower_category.plugin 
Added Files:
        borrowers_stats.pl 
Log Message:
Adding reports

--- NEW FILE ---
#!/usr/bin/perl

# $Id: borrowers_stats.pl,v 1.1 2005/02/19 17:31:49 hdl Exp $

# Copyright 2000-2002 Katipo Communications
#
# This file is part of Koha.
#
# Koha is free software; you can redistribute it and/or modify it under the
# terms of the GNU General Public License as published by the Free Software
# Foundation; either version 2 of the License, or (at your option) any later
# version.
#
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
# 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

use strict;
use C4::Auth;
use CGI;
use C4::Context;
use HTML::Template;
use C4::Search;
use C4::Output;
use C4::Koha;
use C4::Interface::CGI::Output;
use C4::Circulation::Circ2;

=head1 NAME

plugin that shows a stats on borrowers

=head1 DESCRIPTION


=over2

=cut

my $input = new CGI;
my $do_it=$input->param('do_it');
my $fullreportname = "reports/borrowers_stats.tmpl";
my $line = $input->param("Line");
my $column = $input->param("Column");
my @filters = $input->param("Filter");
my $digits = $input->param("digits");
my $borstat = $input->param("status");
my ($template, $borrowernumber, $cookie)
        = get_template_and_user({template_name => $fullreportname,
                                query => $input,
                                type => "intranet",
                                authnotrequired => 0,
                                flagsrequired => {editcatalogue => 1},
                                debug => 1,
                                });
$template->param(do_it => $do_it);
if ($do_it) {
        my $results = calculate($line, $column, $digits, $borstat, 
address@hidden);
        $template->param(mainloop => $results);
#       print $input->header(-type => 'application/vnd.ms-excel', 
-name=>"export.csv");
#       my $lines = @$results[0]->{looprow};
#       foreach my $line (@$lines) {
#               my $x = $line->{loopcell};
#               foreach my $cell (@$x) {
#                       print $cell->{value}.";";
#               }
#               print "\n";
#       }
} else {
        my $dbh = C4::Context->dbh;
        my @values;
        my %labels;
        my $req;
        $req = $dbh->prepare( "select categorycode, description from 
categories");
        $req->execute;
        my %select_catcode;
        my @select_catcode;
        push @select_catcode,"";
        $select_catcode{""} = "";
        while (my ($catcode, $description) =$req->fetchrow) {
                push @select_catcode, $catcode;
                $select_catcode{$catcode} = $description
        }
        my $CGICatCode=CGI::scrolling_list( -name     => 'Filter',
                                -id => 'Filter',
                                -values   => address@hidden,
                                -labels   => \%select_catcode,
                                -size     => 1,
                                -multiple => 0 );
        
        $req = $dbh->prepare( "select distinctrow sort1 from borrowers");
        $req->execute;
        my @select_sort1;
        push @select_sort1,"";
        while (my ($value) =$req->fetchrow) {
                push @select_sort1, $value;
        }
        my $CGIsort1=CGI::scrolling_list( -name     => 'Filter',
                                -id => 'Filter',
                                -values   => address@hidden,
                                -size     => 1,
                                -multiple => 0 );
        
        $req = $dbh->prepare( "select distinctrow sort2 from borrowers");
        $req->execute;
        my @select_sort2;
        push @select_sort2,"";
        while (my ($value) =$req->fetchrow) {
                push @select_sort2, $value;
        }
        my $CGIsort2=CGI::scrolling_list( -name     => 'Filter',
                                -id => 'Filter',
                                -values   => address@hidden,
                                -size     => 1,
                                -multiple => 0 );
        $template->param(CGICatcode => $CGICatCode,
                                        CGISort1 => $CGIsort1,
                                        CGISort2 => $CGIsort2
                                        );

}
output_html_with_http_headers $input, $cookie, $template->output;



sub calculate {
        my ($line, $column, $digits, $status, $filters) = @_;
        my @mainloop;
        my @loopfooter;
        my @loopcol;
        my @looprow;
        my %globalline;
        my $grantotal =0;
# extract parameters
        my $dbh = C4::Context->dbh;

# Filters
        my $linefilter = "";
        $linefilter = @$filters[0] if ($line =~ /categorycode/ )  ;
        $linefilter = @$filters[1] if ($line =~ /zipcode/ )  ;
        $linefilter = @$filters[2] if ($line =~ /sort1/ ) ;
        $linefilter = @$filters[3] if ($line =~ /sort2/ ) ;

        my $colfilter = "";
        $colfilter = @$filters[0] if ($column =~ /categorycode/);
        $colfilter = @$filters[1] if ($column =~ /zipcode/);
        $colfilter = @$filters[2] if ($column =~ /sort1/);
        $colfilter = @$filters[3] if ($column =~ /sort2/);

        my @loopfilter;
        for (my $i=0;$i<=3;$i++) {
                my %cell;
                if ( @$filters[$i] ) {
                        $cell{filter} .= @$filters[$i];
                        $cell{crit} .="Category Code " if ($i==0);
                        $cell{crit} .="Zip Code" if ($i==1);
                        $cell{crit} .="Sort1" if ($i==2);
                        $cell{crit} .="Sort2" if ($i==3);
                        push @loopfilter, \%cell;
                }
        }
        if ($status) {
                push @loopfilter,{crit=>"Status",filter=>$status}
        }
# 1st, loop rows.
#problem with NULL Values.
        my $strsth;
        $strsth .= "select distinctrow $line from borrowers where $line is not 
null ";
        $linefilter =~ s/\*/%/g;
        if ( $linefilter ) {
                $strsth .= " and $line LIKE ? " ;
        }
        $strsth .= " and $status='1' " if ($status);
        $strsth .=" order by $line";
        warn "". $strsth;
        
        my $sth = $dbh->prepare( $strsth );
        if ( $linefilter ) {
                $sth->execute($linefilter);
        } else {
                $sth->execute;
        }
        while ( my ($celvalue) = $sth->fetchrow) {
                my %cell;
                if ($celvalue) {
                        $cell{rowtitle} = $celvalue;
                } else {
                        $cell{rowtitle} = "";
                }
                $cell{totalrow} = 0;
                push @looprow, \%cell;
        }

# 2nd, loop cols.
        my $strsth2;
        $colfilter =~ s/\*/%/g;
        $strsth2 .= "select distinctrow $column from borrowers where $column is 
not null";
        if ( $colfilter ) {
                $strsth2 .= " and $column LIKE ? ";
        } 
        $strsth2 .= " and $status='1' " if ($status);
        $strsth2 .= " order by $column";
        warn "". $strsth2;
        my $sth2 = $dbh->prepare( $strsth2 );
        if ($colfilter) {
                $sth2->execute($colfilter);
        } else {
                $sth2->execute;
        }
        while (my ($celvalue) = $sth2->fetchrow) {
                my %cell;
                my %ft;
                $cell{coltitle} = $celvalue;
                $ft{totalcol} = 0;
                push @loopcol, \%cell;
                push @loopfooter, \%ft;
        }
# now, parse each category. Before filling the result array, fill it with 0 to 
have every itemtype column.
        my $strcalc .= "SELECT  count( * ) FROM borrowers WHERE $line = ? and 
$column= ? ";
        $strcalc .= " AND categorycode like '" . @$filters[1] ."%' " if ( 
@$filters[1] );
        $strcalc .= " AND sort1 like ' " . @$filters[2] ."%'" if ( @$filters[2] 
);
        $strcalc .= " AND sort2 like ' " . @$filters[3] ."%'" if ( @$filters[3] 
);
        $strcalc .= " AND zipcode like ' " . @$filters[4] ."%'" if ( 
@$filters[4] );
        $strcalc .= " and $status='1' " if ($status);
        warn "". $strcalc;
        my $dbcalc = $dbh->prepare($strcalc);
        my $i=0;
        my @totalcol;
        my $hilighted=-1;
        # for each line
        for (my $i=0; $i<=$#looprow; $i++) {
                my $row = $looprow[$i]->{'rowtitle'};
                my @loopcell;
                my $totalrow=0;
                # for each column
                for (my $j=0;$j<=$#loopcol;$j++) {
                        my $col = $loopcol[$j]->{'coltitle'};
                        $dbcalc->execute($row,$col);
                        my ($value) = $dbcalc->fetchrow;
#                       warn "$row / $col / $value";
                        $totalrow += $value;
                        $grantotal += $value;
                        $loopfooter[$j]->{'totalcol'} +=$value;
                        push @loopcell,{value => $value};
                }
                $looprow[$i]->{'totalrow'}=$totalrow;
                $looprow[$i]->{'loopcell'address@hidden;
                $looprow[$i]->{'hilighted'} = 1 if $hilighted eq 1;
                $hilighted = -$hilighted;
        }

#       # the header of the table
        address@hidden;
        $globalline{looprow} = address@hidden;
#       # the core of the table
        $globalline{loopcol} = address@hidden;
#       # the foot (totals by borrower type)
        $globalline{loopfooter} = address@hidden;
        $globalline{total}= $grantotal;
        $globalline{line} = $line;
        $globalline{column} = $column;
        push @mainloop,\%globalline;
        return address@hidden;
}

1;
Index: issues_by_borrower_category.plugin
===================================================================
RCS file: /cvsroot/koha/koha/reports/issues_by_borrower_category.plugin,v
retrieving revision 1.2
retrieving revision 1.3
diff -C2 -r1.2 -r1.3
*** issues_by_borrower_category.plugin  6 May 2004 14:54:44 -0000       1.2
--- issues_by_borrower_category.plugin  19 Feb 2005 17:31:42 -0000      1.3
***************
*** 112,156 ****
        my %borrowertype;
        my @categorycodeloop;
!       while (my ($categorycode,$description) = $sthcategories->fetchrow) {
!               $borrowertype{$categorycode}->{description} = $description;
!               $borrowertype{$categorycode}->{total} = 0;
!               my %categorycode;
!               $categorycode{categorycode} = $description;
!               push @categorycodeloop,\%categorycode;
!               foreach my $itemtype (keys %itemtypes) {
!                       $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
!               }
!               $sth->execute($categorycode);
!               while (my ($itemtype, $total) = $sth->fetchrow) {
!                       $itemtypes{$itemtype}->{results}->{$categorycode} = 
$total;
!                       $borrowertype{$categorycode}->{total} += $total;
!                       $itemtypes{$itemtype}->{total} += $total;
!               }
!       }
!       my $grantotal = 0;
! # build the result
        my @mainloop;
        my @itemtypeloop;
        my @loopborrowertype;
        my %globalline;
        my $hilighted=-1;
!       foreach my $itemtype (keys %itemtypes) {
!               my @loopitemtype;
!               foreach my $categorycode (keys 
%{$itemtypes{$itemtype}->{results}}) {
!                       my %cell;
!                       $cell{issues} = 
$itemtypes{$itemtype}->{results}->{$categorycode};
!                       push @loopitemtype,\%cell;
                }
!               my %line;
!               $line{loopitemtype} = address@hidden;
!               if ($itemtypes{$itemtype}->{description}) {
!                       $line{itemtype} = $itemtypes{$itemtype}->{description};
!               } else {
!                       $line{itemtype} = "$itemtype (no entry in itemtype 
table)";
                }
!               $line{hilighted} = 1 if $hilighted eq 1;
!               $line{totalitemtype} = $itemtypes{$itemtype}->{total};
!               $hilighted = -$hilighted;
!               push @loopborrowertype, \%line;
        }
        # the header of the table
--- 112,218 ----
        my %borrowertype;
        my @categorycodeloop;
!       my $categorycode;
!       my $description;
!       my $borrower_categorycode =0;
        my @mainloop;
        my @itemtypeloop;
        my @loopborrowertype;
+       my @loopborrowertotal;
        my %globalline;
        my $hilighted=-1;
!       my $grantotal =0;
!       #If no Borrower-category selected....
!       # Print all 
!       if (!$borrower_category) {
!               while ( ($categorycode,$description) = 
$sthcategories->fetchrow) {
!                       $borrowertype{$categorycode}->{description} = 
$description;
!                       $borrowertype{$categorycode}->{total} = 0;
!                       my %categorycode;
!                       $categorycode{categorycode} = $description;
!                       push @categorycodeloop,\%categorycode;
!                       foreach my $itemtype (keys %itemtypes) {
!                               
$itemtypes{$itemtype}->{results}->{$categorycode} = 0;
!                       }
!                       $sth->execute($categorycode);
!                       while (my ($itemtype, $total) = $sth->fetchrow) {
!                               
$itemtypes{$itemtype}->{results}->{$categorycode} = $total;
!                               $borrowertype{$categorycode}->{total} += $total;
!                               $itemtypes{$itemtype}->{total} += $total;
!                               $grantotal += $total;
!                       }
!               }
!               # build the result
!               foreach my $itemtype (keys %itemtypes) {
!                       my @loopitemtype;
!                       $sthcategories->execute;
!                       while (($categorycode,$description) =  
$sthcategories->fetchrow ) {
!                               my %cell;
!                               $cell{issues} = 
$itemtypes{$itemtype}->{results}->{$categorycode};
!                               #printf stderr "%s      ",$categorycode;
!                               push @loopitemtype,\%cell;
!                       }
!                       #printf stderr "\n";
!                       my %line;
!                       $line{loopitemtype} = address@hidden;
!                       if ($itemtypes{$itemtype}->{description}) {
!                               $line{itemtype} = 
$itemtypes{$itemtype}->{description};
!                       } else {
!                               $line{itemtype} = "$itemtype (no entry in 
itemtype table)";
!                       }
!                       $line{hilighted} = 1 if $hilighted eq 1;
!                       $line{totalitemtype} = $itemtypes{$itemtype}->{total};
!                       $hilighted = -$hilighted;
!                       push @loopborrowertype, \%line;
!               }
!               $sthcategories->execute;
!               while (($categorycode,$description) =  $sthcategories->fetchrow 
) {
!                       my %line;
!                       $line{issues} = $borrowertype{$categorycode}->{total};
!                       push @loopborrowertotal, \%line;
!               }
!       } else {
!               # A Borrower_category has been selected
!               # extracting corresponding data
!               $borrowertype{$categorycode}->{description} = 
$borrower_category;
!               $borrowertype{$categorycode}->{total} = 0;
!               while (($categorycode,$description) = $sthcategories->fetchrow) 
{
!                       if ($description =~ /$borrower_category/ ) {
!                               $borrower_categorycode = $categorycode;
!                               my %cc;
!                               $cc{categorycode} = $description;
!                               push @categorycodeloop,\%cc;
!                               foreach my $itemtype (keys %itemtypes) {
!                                       
$itemtypes{$itemtype}->{results}->{$categorycode} = 0;
!                               }
!                               $sth->execute($categorycode);
!                               while (my ($itemtype, $total) = $sth->fetchrow) 
{
!                                       
$itemtypes{$itemtype}->{results}->{$categorycode} = $total;
!                                       $borrowertype{$categorycode}->{total} 
+= $total;
!                                       $itemtypes{$itemtype}->{total} += 
$total;
!                                       $grantotal +=$total;
!                               }
!                       }
                }
!               # build the result
!               foreach my $itemtype (keys %itemtypes) {
!                       my @loopitemtype;
!                       my %cell;
!                       
$cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
!                       push @loopitemtype, \%cell;
!                       my %line;
!                       $line{loopitemtype} = address@hidden;
!                       if ($itemtypes{$itemtype}->{description}) {
!                               $line{itemtype} = 
$itemtypes{$itemtype}->{description};
!                       } else {
!                               $line{itemtype} = "$itemtype (no entry in 
itemtype table)";
!                       }
!                       $line{hilighted} = 1 if $hilighted eq 1;
!                       $line{totalitemtype} = $itemtypes{$itemtype}->{total};
!                       $hilighted = -$hilighted;
!                       push @loopborrowertype, \%line;
                }
!               my %cell;
!               $cell{issues} = $borrowertype{$borrower_categorycode}->{total};
!               push @loopborrowertotal, \%cell;
        }
        # the header of the table
***************
*** 159,171 ****
        $globalline{categorycodeloop} = address@hidden;
        # the foot (totals by borrower type)
-       my @loopborrowertotal;
-       foreach my $categorycode (keys %borrowertype) {
-               my %line;
-               $line{issues} = $borrowertype{$categorycode}->{total};
-               push @loopborrowertotal, \%line;
-       }
        $globalline{loopborrowertotal} = address@hidden;
! #     $globalline{total} = $grantotal;
! #     $globalline{borrower_category} = $borrower_category;
        push @mainloop,\%globalline;
        return address@hidden;
--- 221,226 ----
        $globalline{categorycodeloop} = address@hidden;
        # the foot (totals by borrower type)
        $globalline{loopborrowertotal} = address@hidden;
!       $globalline{grantotal}= $grantotal;
        push @mainloop,\%globalline;
        return address@hidden;