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