reports/issues_by_borrower_category.plugin

   1 #!/usr/bin/perl
   2
   3 # $Id$
   4
   5 # Copyright 2000-2002 Katipo Communications
   6 #
   7 # This file is part of Koha.
   8 #
   9 # Koha is free software; you can redistribute it and/or modify it under the
  10 # terms of the GNU General Public License as published by the Free Software
  11 # Foundation; either version 2 of the License, or (at your option) any later
  12 # version.
  13 #
  14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  16 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  17 #
  18 # You should have received a copy of the GNU General Public License along with
  19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  20 # Suite 330, Boston, MA  02111-1307 USA
  21
  22 use strict;
  23 use C4::Auth;
  24 use CGI;
  25 use C4::Context;
  26 use C4::Search;
  27 use C4::Output;
  28 use C4::Koha;
  29
  30 =head1 NAME
  31
  32 plugin that shows a table with issues for categories and borrower
  33
  34 =head1 DESCRIPTION
  35
  36 this result is quite complex to build...
  37 the 2D array contains :
  38 * item types on lines
  39 * borrowers types on rows
  40
  41 If no issues are done, the array must be filled by 0 anyway.
  42 So, the script works as this :
  43 1- parse the itemtype table to get itemtype descriptions and set itemtype total to 0
  44 2- for each borrower category :
  45 ** create an array with total = 0 for each itemtype defined in 1
  46 ** calculate the total for each itemtype (SQL request)
  47 The big hash has the following structure :
  48 $itemtypes{itemtype}
  49         ->{results}
  50                 ->{borrowercategorycode} => the total of issues for each cell of the table.
  51         ->{total} => the total for the itemtype
  52         ->{description} => the itemtype description
  53
  54 the borrowertype hash contains description and total for each borrowercategory.
  55
  56 the hashes are then translated to hash / arrays to be returned to manager.pl & send to the template
  57
  58 =over2
  59
  60 =cut
  61
  62 sub set_parameters {
  63         my ($template) = @_;
  64         my $dbh = C4::Context->dbh;
  65         my $branches=GetBranches();
  66         my @branches;
  67         my @select_branch;
  68         my %select_branches;
  69         push @select_branch,"";
  70         $select_branches{""} = "";
  71         foreach my $branch (keys %$branches) {
  72                 push @select_branch, $branch;
  73                 $select_branches{$branch} = $branches->{$branch}->{'branchname'};
  74         }
  75         my $CGIbranch=CGI::scrolling_list( -name     => 'value',
  76                                 -id => 'value',
  77                                 -values   => \@select_branch,
  78                                 -labels   => \%select_branches,
  79                                 -size     => 1,
  80                                 -multiple => 0 );
  81         $template->param(CGIbranch => $CGIbranch);
  82         return $template;
  83 }
  84 sub calculate {
  85         my ($parameters) = @_;
  86         my @results =();
  87 # extract parameters
  88         my $borrower_category = @$parameters[0];
  89         my $branch = @$parameters[1];
  90         my $dbh = C4::Context->dbh;
  91 # build the SQL query & execute it
  92
  93 # 1st, loop every itemtypes.
  94         my $sth = $dbh->prepare("select itemtype,description from itemtypes");
  95         $sth->execute;
  96         my %itemtypes;
  97         while (my ($itemtype,$description) = $sth->fetchrow) {
  98                 $itemtypes{$itemtype}->{description} = $description;
  99                 $itemtypes{$itemtype}->{total} = 0;
 100         }
 101 # now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
 102         my $strsth="SELECT itemtype, count( * )
 103                                 FROM issues, borrowers, biblio, items
 104                                 WHERE issues.borrowernumber = borrowers.borrowernumber
 105                                         AND items.itemnumber = issues.itemnumber
 106                                         AND biblio.biblionumber = items.biblionumber
 107                                         AND borrowers.categorycode = ?";
 108         $strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
 109         $strsth .= " GROUP BY biblio.itemtype";
 110         my $sth = $dbh->prepare($strsth);
 111         my $sthcategories = $dbh->prepare("select categorycode,description from categories");
 112         $sthcategories->execute;
 113         my %borrowertype;
 114         my @categorycodeloop;
 115         my $categorycode;
 116         my $description;
 117         my $borrower_categorycode =0;
 118         my @mainloop;
 119         my @itemtypeloop;
 120         my @loopborrowertype;
 121         my @loopborrowertotal;
 122         my %globalline;
 123         my $hilighted=-1;
 124         my $grantotal =0;
 125         #If no Borrower-category selected....
 126         # Print all
 127         if (!$borrower_category) {
 128                 while ( ($categorycode,$description) = $sthcategories->fetchrow) {
 129                         $borrowertype{$categorycode}->{description} = $description;
 130                         $borrowertype{$categorycode}->{total} = 0;
 131                         my %categorycode;
 132                         $categorycode{categorycode} = $description;
 133                         push @categorycodeloop,\%categorycode;
 134                         foreach my $itemtype (keys %itemtypes) {
 135                                 $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
 136                         }
 137                         $sth->execute($categorycode);
 138                         while (my ($itemtype, $total) = $sth->fetchrow) {
 139                                 $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
 140                                 $borrowertype{$categorycode}->{total} += $total;
 141                                 $itemtypes{$itemtype}->{total} += $total;
 142                                 $grantotal += $total;
 143                         }
 144                 }
 145                 # build the result
 146                 foreach my $itemtype (keys %itemtypes) {
 147                         my @loopitemtype;
 148                         $sthcategories->execute;
 149                         while (($categorycode,$description) =  $sthcategories->fetchrow ) {
 150                                 my %cell;
 151                                 $cell{issues} = $itemtypes{$itemtype}->{results}->{$categorycode};
 152                                 #printf stderr "%s      ",$categorycode;
 153                                 push @loopitemtype,\%cell;
 154                         }
 155                         #printf stderr "\n";
 156                         my %line;
 157                         $line{loopitemtype} = \@loopitemtype;
 158                         if ($itemtypes{$itemtype}->{description}) {
 159                                 $line{itemtype} = $itemtypes{$itemtype}->{description};
 160                         } else {
 161                                 $line{itemtype} = "$itemtype (no entry in itemtype table)";
 162                         }
 163                         $line{hilighted} = 1 if $hilighted eq 1;
 164                         $line{totalitemtype} = $itemtypes{$itemtype}->{total};
 165                         $hilighted = -$hilighted;
 166                         push @loopborrowertype, \%line;
 167                 }
 168                 $sthcategories->execute;
 169                 while (($categorycode,$description) =  $sthcategories->fetchrow ) {
 170                         my %line;
 171                         $line{issues} = $borrowertype{$categorycode}->{total};
 172                         push @loopborrowertotal, \%line;
 173                 }
 174         } else {
 175                 # A Borrower_category has been selected
 176                 # extracting corresponding data
 177                 $borrowertype{$categorycode}->{description} = $borrower_category;
 178                 $borrowertype{$categorycode}->{total} = 0;
 179                 while (($categorycode,$description) = $sthcategories->fetchrow) {
 180                         if ($description =~ /$borrower_category/ ) {
 181                                 $borrower_categorycode = $categorycode;
 182                                 my %cc;
 183                                 $cc{categorycode} = $description;
 184                                 push @categorycodeloop,\%cc;
 185                                 foreach my $itemtype (keys %itemtypes) {
 186                                         $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
 187                                 }
 188                                 $sth->execute($categorycode);
 189                                 while (my ($itemtype, $total) = $sth->fetchrow) {
 190                                         $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
 191                                         $borrowertype{$categorycode}->{total} += $total;
 192                                         $itemtypes{$itemtype}->{total} += $total;
 193                                         $grantotal +=$total;
 194                                 }
 195                         }
 196                 }
 197                 # build the result
 198                 foreach my $itemtype (keys %itemtypes) {
 199                         my @loopitemtype;
 200                         my %cell;
 201                         $cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
 202                         push @loopitemtype, \%cell;
 203                         my %line;
 204                         $line{loopitemtype} = \@loopitemtype;
 205                         if ($itemtypes{$itemtype}->{description}) {
 206                                 $line{itemtype} = $itemtypes{$itemtype}->{description};
 207                         } else {
 208                                 $line{itemtype} = "$itemtype (no entry in itemtype table)";
 209                         }
 210                         $line{hilighted} = 1 if $hilighted eq 1;
 211                         $line{totalitemtype} = $itemtypes{$itemtype}->{total};
 212                         $hilighted = -$hilighted;
 213                         push @loopborrowertype, \%line;
 214                 }
 215                 my %cell;
 216                 $cell{issues} = $borrowertype{$borrower_categorycode}->{total};
 217                 push @loopborrowertotal, \%cell;
 218         }
 219         # the header of the table
 220         $globalline{loopborrowertype} = \@loopborrowertype;
 221         # the core of the table
 222         $globalline{categorycodeloop} = \@categorycodeloop;
 223         # the foot (totals by borrower type)
 224         $globalline{loopborrowertotal} = \@loopborrowertotal;
 225         $globalline{grantotal}= $grantotal;
 226         push @mainloop,\%globalline;
 227         return \@mainloop;
 228 }
 229
 230 1;