4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
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.
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.
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>.
30 use C4::Branch; # GetBranches
32 use Koha::Patron::Categories;
36 plugin that shows a table with issues for categories and borrower
40 this result is quite complex to build...
41 the 2D array contains :
43 * borrowers types on rows
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 :
54 ->{borrowercategorycode} => the total of issues for each cell of the table.
55 ->{total} => the total for the itemtype
56 ->{description} => the itemtype description
58 the borrowertype hash contains description and total for each borrowercategory.
60 the hashes are then translated to hash / arrays to be returned to manager.pl & send to the template
69 my $patron_categories = Koha::Patron::Categories->search_limited({}, {order_by => ['categorycode']});
70 $template->param( patron_categories => $patron_categories );
75 my ($parameters) = @_;
78 my $borrower_category = @$parameters[0];
79 my $branch = @$parameters[1];
80 my $dbh = C4::Context->dbh;
81 # build the SQL query & execute it
83 # 1st, loop every itemtypes.
84 my $sth = $dbh->prepare("select itemtype,description from itemtypes");
87 while (my ($itemtype,$description) = $sth->fetchrow) {
88 $itemtypes{$itemtype}->{description} = $description;
89 $itemtypes{$itemtype}->{total} = 0;
91 # now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
92 my $strsth="SELECT itemtype, count( * )
93 FROM issues, borrowers, biblioitems, items
94 WHERE issues.borrowernumber = borrowers.borrowernumber
95 AND items.itemnumber = issues.itemnumber
96 AND biblioitems.biblionumber = items.biblionumber
97 AND borrowers.categorycode = ?";
98 $strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
99 $strsth .= " GROUP BY biblioitems.itemtype";
100 my $sth = $dbh->prepare($strsth);
101 my $sthcategories = $dbh->prepare("select categorycode,description from categories");
102 $sthcategories->execute;
104 my @categorycodeloop;
107 my $borrower_categorycode =0;
110 my @loopborrowertype;
111 my @loopborrowertotal;
115 #If no Borrower-category selected....
117 if (!$borrower_category) {
118 while ( ($categorycode,$description) = $sthcategories->fetchrow) {
119 $borrowertype{$categorycode}->{description} = $description;
120 $borrowertype{$categorycode}->{total} = 0;
122 $categorycode{categorycode} = $description;
123 push @categorycodeloop,\%categorycode;
124 foreach my $itemtype (keys %itemtypes) {
125 $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
127 $sth->execute($categorycode);
128 while (my ($itemtype, $total) = $sth->fetchrow) {
129 $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
130 $borrowertype{$categorycode}->{total} += $total;
131 $itemtypes{$itemtype}->{total} += $total;
132 $grantotal += $total;
136 foreach my $itemtype (keys %itemtypes) {
138 $sthcategories->execute;
139 while (($categorycode,$description) = $sthcategories->fetchrow ) {
141 $cell{issues} = $itemtypes{$itemtype}->{results}->{$categorycode};
142 #printf stderr "%s ",$categorycode;
143 push @loopitemtype,\%cell;
147 $line{loopitemtype} = \@loopitemtype;
148 if ($itemtypes{$itemtype}->{description}) {
149 $line{itemtype} = $itemtypes{$itemtype}->{description};
151 $line{itemtype} = "$itemtype (no entry in itemtype table)";
153 $line{hilighted} = 1 if $hilighted eq 1;
154 $line{totalitemtype} = $itemtypes{$itemtype}->{total};
155 $hilighted = -$hilighted;
156 push @loopborrowertype, \%line;
158 $sthcategories->execute;
159 while (($categorycode,$description) = $sthcategories->fetchrow ) {
161 $line{issues} = $borrowertype{$categorycode}->{total};
162 push @loopborrowertotal, \%line;
165 # A Borrower_category has been selected
166 # extracting corresponding data
167 $borrowertype{$categorycode}->{description} = $borrower_category;
168 $borrowertype{$categorycode}->{total} = 0;
169 while (($categorycode,$description) = $sthcategories->fetchrow) {
170 if ($description =~ /$borrower_category/ ) {
171 $borrower_categorycode = $categorycode;
173 $cc{categorycode} = $description;
174 push @categorycodeloop,\%cc;
175 foreach my $itemtype (keys %itemtypes) {
176 $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
178 $sth->execute($categorycode);
179 while (my ($itemtype, $total) = $sth->fetchrow) {
180 $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
181 $borrowertype{$categorycode}->{total} += $total;
182 $itemtypes{$itemtype}->{total} += $total;
188 foreach my $itemtype (keys %itemtypes) {
191 $cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
192 push @loopitemtype, \%cell;
194 $line{loopitemtype} = \@loopitemtype;
195 if ($itemtypes{$itemtype}->{description}) {
196 $line{itemtype} = $itemtypes{$itemtype}->{description};
198 $line{itemtype} = "$itemtype (no entry in itemtype table)";
200 $line{hilighted} = 1 if $hilighted eq 1;
201 $line{totalitemtype} = $itemtypes{$itemtype}->{total};
202 $hilighted = -$hilighted;
203 push @loopborrowertype, \%line;
206 $cell{issues} = $borrowertype{$borrower_categorycode}->{total};
207 push @loopborrowertotal, \%cell;
209 # the header of the table
210 $globalline{loopborrowertype} = \@loopborrowertype;
211 # the core of the table
212 $globalline{categorycodeloop} = \@categorycodeloop;
213 # the foot (totals by borrower type)
214 $globalline{loopborrowertotal} = \@loopborrowertotal;
215 $globalline{grantotal}= $grantotal;
216 push @mainloop,\%globalline;