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;
  24 use C4::Context;
  25 use HTML::Template::Pro;
  26 use C4::Search;
  27 use C4::Output;
  28 use C4::Koha;
  29 use C4::Members;
  30
  31 use C4::Branch; # GetBranches
  32
  33 =head1 NAME
  34
  35 plugin that shows a table with issues for categories and borrower
  36
  37 =head1 DESCRIPTION
  38
  39 this result is quite complex to build...
  40 the 2D array contains :
  41 * item types on lines
  42 * borrowers types on rows
  43
  44 If no issues are done, the array must be filled by 0 anyway.
  45 So, the script works as this :
  46 1- parse the itemtype table to get itemtype descriptions and set itemtype total to 0
  47 2- for each borrower category :
  48 ** create an array with total = 0 for each itemtype defined in 1
  49 ** calculate the total for each itemtype (SQL request)
  50 The big hash has the following structure :
  51 $itemtypes{itemtype}
  52         ->{results}
  53                 ->{borrowercategorycode} => the total of issues for each cell of the table.
  54         ->{total} => the total for the itemtype
  55         ->{description} => the itemtype description
  56
  57 the borrowertype hash contains description and total for each borrowercategory.
  58
  59 the hashes are then translated to hash / arrays to be returned to manager.pl & send to the template
  60
  61 =over2
  62
  63 =cut
  64
  65 sub set_parameters {
  66         my ($template) = @_;
  67         my $dbh = C4::Context->dbh;
  68         my $branches=GetBranches();
  69         my @branches;
  70         my @select_branch;
  71         my %select_branches;
  72         push @select_branch,"";
  73         $select_branches{""} = "";
  74         foreach my $branch (keys %$branches) {
  75                 push @select_branch, $branch;
  76                 $select_branches{$branch} = $branches->{$branch}->{'branchname'};
  77         }
  78         my $CGIbranch=CGI::scrolling_list( -name     => 'value',
  79                                 -id => 'branch',
  80                                 -values   => \@select_branch,
  81                                 -labels   => \%select_branches,
  82                                 -size     => 1,
  83                                 -multiple => 0 );
  84         $template->param(CGIbranch => $CGIbranch);
  85
  86   my ($codes,$labels)=GetborCatFromCatType(undef,undef);
  87   my @borcatloop;
  88   foreach my $thisborcat (sort keys %$labels) {
  89             push @borcatloop, {value => $thisborcat,
  90                                description => $labels->{$thisborcat},
  91                             };
  92   }
  93   $template->param(loopcategories => \@borcatloop);
  94         return $template;
  95 }
  96 sub calculate {
  97         my ($parameters) = @_;
  98         my @results =();
  99 # extract parameters
 100         my $borrower_category = @$parameters[0];
 101         my $branch = @$parameters[1];
 102         my $dbh = C4::Context->dbh;
 103 # build the SQL query & execute it
 104
 105 # 1st, loop every itemtypes.
 106         my $sth = $dbh->prepare("select itemtype,description from itemtypes");
 107         $sth->execute;
 108         my %itemtypes;
 109         while (my ($itemtype,$description) = $sth->fetchrow) {
 110                 $itemtypes{$itemtype}->{description} = $description;
 111                 $itemtypes{$itemtype}->{total} = 0;
 112         }
 113 # now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
 114         my $strsth="SELECT itemtype, count( * )
 115                                 FROM issues, borrowers, biblioitems, items
 116                                 WHERE issues.borrowernumber = borrowers.borrowernumber
 117                                         AND items.itemnumber = issues.itemnumber
 118                                         AND biblioitems.biblionumber = items.biblionumber
 119                                         AND borrowers.categorycode = ?";
 120         $strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
 121         $strsth .= " GROUP BY biblioitems.itemtype";
 122         my $sth = $dbh->prepare($strsth);
 123         my $sthcategories = $dbh->prepare("select categorycode,description from categories");
 124         $sthcategories->execute;
 125         my %borrowertype;
 126         my @categorycodeloop;
 127         my $categorycode;
 128         my $description;
 129         my $borrower_categorycode =0;
 130         my @mainloop;
 131         my @itemtypeloop;
 132         my @loopborrowertype;
 133         my @loopborrowertotal;
 134         my %globalline;
 135         my $hilighted=-1;
 136         my $grantotal =0;
 137         #If no Borrower-category selected....
 138         # Print all
 139         if (!$borrower_category) {
 140                 while ( ($categorycode,$description) = $sthcategories->fetchrow) {
 141                         $borrowertype{$categorycode}->{description} = $description;
 142                         $borrowertype{$categorycode}->{total} = 0;
 143                         my %categorycode;
 144                         $categorycode{categorycode} = $description;
 145                         push @categorycodeloop,\%categorycode;
 146                         foreach my $itemtype (keys %itemtypes) {
 147                                 $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
 148                         }
 149                         $sth->execute($categorycode);
 150                         while (my ($itemtype, $total) = $sth->fetchrow) {
 151                                 $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
 152                                 $borrowertype{$categorycode}->{total} += $total;
 153                                 $itemtypes{$itemtype}->{total} += $total;
 154                                 $grantotal += $total;
 155                         }
 156                 }
 157                 # build the result
 158                 foreach my $itemtype (keys %itemtypes) {
 159                         my @loopitemtype;
 160                         $sthcategories->execute;
 161                         while (($categorycode,$description) =  $sthcategories->fetchrow ) {
 162                                 my %cell;
 163                                 $cell{issues} = $itemtypes{$itemtype}->{results}->{$categorycode};
 164                                 #printf stderr "%s      ",$categorycode;
 165                                 push @loopitemtype,\%cell;
 166                         }
 167                         #printf stderr "\n";
 168                         my %line;
 169                         $line{loopitemtype} = \@loopitemtype;
 170                         if ($itemtypes{$itemtype}->{description}) {
 171                                 $line{itemtype} = $itemtypes{$itemtype}->{description};
 172                         } else {
 173                                 $line{itemtype} = "$itemtype (no entry in itemtype table)";
 174                         }
 175                         $line{hilighted} = 1 if $hilighted eq 1;
 176                         $line{totalitemtype} = $itemtypes{$itemtype}->{total};
 177                         $hilighted = -$hilighted;
 178                         push @loopborrowertype, \%line;
 179                 }
 180                 $sthcategories->execute;
 181                 while (($categorycode,$description) =  $sthcategories->fetchrow ) {
 182                         my %line;
 183                         $line{issues} = $borrowertype{$categorycode}->{total};
 184                         push @loopborrowertotal, \%line;
 185                 }
 186         } else {
 187                 # A Borrower_category has been selected
 188                 # extracting corresponding data
 189                 $borrowertype{$categorycode}->{description} = $borrower_category;
 190                 $borrowertype{$categorycode}->{total} = 0;
 191                 while (($categorycode,$description) = $sthcategories->fetchrow) {
 192                         if ($description =~ /$borrower_category/ ) {
 193                                 $borrower_categorycode = $categorycode;
 194                                 my %cc;
 195                                 $cc{categorycode} = $description;
 196                                 push @categorycodeloop,\%cc;
 197                                 foreach my $itemtype (keys %itemtypes) {
 198                                         $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
 199                                 }
 200                                 $sth->execute($categorycode);
 201                                 while (my ($itemtype, $total) = $sth->fetchrow) {
 202                                         $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
 203                                         $borrowertype{$categorycode}->{total} += $total;
 204                                         $itemtypes{$itemtype}->{total} += $total;
 205                                         $grantotal +=$total;
 206                                 }
 207                         }
 208                 }
 209                 # build the result
 210                 foreach my $itemtype (keys %itemtypes) {
 211                         my @loopitemtype;
 212                         my %cell;
 213                         $cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
 214                         push @loopitemtype, \%cell;
 215                         my %line;
 216                         $line{loopitemtype} = \@loopitemtype;
 217                         if ($itemtypes{$itemtype}->{description}) {
 218                                 $line{itemtype} = $itemtypes{$itemtype}->{description};
 219                         } else {
 220                                 $line{itemtype} = "$itemtype (no entry in itemtype table)";
 221                         }
 222                         $line{hilighted} = 1 if $hilighted eq 1;
 223                         $line{totalitemtype} = $itemtypes{$itemtype}->{total};
 224                         $hilighted = -$hilighted;
 225                         push @loopborrowertype, \%line;
 226                 }
 227                 my %cell;
 228                 $cell{issues} = $borrowertype{$borrower_categorycode}->{total};
 229                 push @loopborrowertotal, \%cell;
 230         }
 231         # the header of the table
 232         $globalline{loopborrowertype} = \@loopborrowertype;
 233         # the core of the table
 234         $globalline{categorycodeloop} = \@categorycodeloop;
 235         # the foot (totals by borrower type)
 236         $globalline{loopborrowertotal} = \@loopborrowertotal;
 237         $globalline{grantotal}= $grantotal;
 238         push @mainloop,\%globalline;
 239         return \@mainloop;
 240 }
 241
 242 1;