reports/issues_by_borrower_category.plugin

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