Bug 18350 (QA Followup) Add classes to elements
[koha.git] / reports / issues_by_borrower_category.plugin
blob1c2d1d1ab7f277e8c8c6b152ca70448ebf18097c
1 #!/usr/bin/perl
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>.
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;
30 use Koha::Patron::Categories;
32 =head1 NAME
34 plugin that shows a table with issues for categories and borrower
36 =head1 DESCRIPTION
38 this result is quite complex to build...
39 the 2D array contains :
40 * item types on lines
41 * borrowers types on rows
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
56 the borrowertype hash contains description and total for each borrowercategory.
58 the hashes are then translated to hash / arrays to be returned to manager.pl & send to the template
60 =over2
62 =cut
64 sub set_parameters {
65 my ($template) = @_;
67 my $patron_categories = Koha::Patron::Categories->search_limited({}, {order_by => ['categorycode']});
68 $template->param( patron_categories => $patron_categories );
69 return $template;
72 sub calculate {
73 my ($parameters) = @_;
74 my @results =();
75 # extract parameters
76 my $borrower_category = @$parameters[0];
77 my $branch = @$parameters[1];
78 my $dbh = C4::Context->dbh;
79 # build the SQL query & execute it
81 # 1st, loop every itemtypes.
82 my $sth = $dbh->prepare("select itemtype,description from itemtypes");
83 $sth->execute;
84 my %itemtypes;
85 while (my ($itemtype,$description) = $sth->fetchrow) {
86 $itemtypes{$itemtype}->{description} = $description;
87 $itemtypes{$itemtype}->{total} = 0;
89 # now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
90 my $strsth="SELECT itemtype, count( * )
91 FROM issues, borrowers, biblioitems, items
92 WHERE issues.borrowernumber = borrowers.borrowernumber
93 AND items.itemnumber = issues.itemnumber
94 AND biblioitems.biblionumber = items.biblionumber
95 AND borrowers.categorycode = ?";
96 $strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
97 $strsth .= " GROUP BY biblioitems.itemtype";
98 my $sth = $dbh->prepare($strsth);
99 my $sthcategories = $dbh->prepare("select categorycode,description from categories");
100 $sthcategories->execute;
101 my %borrowertype;
102 my @categorycodeloop;
103 my $categorycode;
104 my $description;
105 my $borrower_categorycode =0;
106 my @mainloop;
107 my @itemtypeloop;
108 my @loopborrowertype;
109 my @loopborrowertotal;
110 my %globalline;
111 my $hilighted=-1;
112 my $grantotal =0;
113 #If no Borrower-category selected....
114 # Print all
115 if (!$borrower_category) {
116 while ( ($categorycode,$description) = $sthcategories->fetchrow) {
117 $borrowertype{$categorycode}->{description} = $description;
118 $borrowertype{$categorycode}->{total} = 0;
119 my %categorycode;
120 $categorycode{categorycode} = $description;
121 push @categorycodeloop,\%categorycode;
122 foreach my $itemtype (keys %itemtypes) {
123 $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
125 $sth->execute($categorycode);
126 while (my ($itemtype, $total) = $sth->fetchrow) {
127 $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
128 $borrowertype{$categorycode}->{total} += $total;
129 $itemtypes{$itemtype}->{total} += $total;
130 $grantotal += $total;
133 # build the result
134 foreach my $itemtype (keys %itemtypes) {
135 my @loopitemtype;
136 $sthcategories->execute;
137 while (($categorycode,$description) = $sthcategories->fetchrow ) {
138 my %cell;
139 $cell{issues} = $itemtypes{$itemtype}->{results}->{$categorycode};
140 #printf stderr "%s ",$categorycode;
141 push @loopitemtype,\%cell;
143 #printf stderr "\n";
144 my %line;
145 $line{loopitemtype} = \@loopitemtype;
146 if ($itemtypes{$itemtype}->{description}) {
147 $line{itemtype} = $itemtypes{$itemtype}->{description};
148 } else {
149 $line{itemtype} = "$itemtype (no entry in itemtype table)";
151 $line{hilighted} = 1 if $hilighted eq 1;
152 $line{totalitemtype} = $itemtypes{$itemtype}->{total};
153 $hilighted = -$hilighted;
154 push @loopborrowertype, \%line;
156 $sthcategories->execute;
157 while (($categorycode,$description) = $sthcategories->fetchrow ) {
158 my %line;
159 $line{issues} = $borrowertype{$categorycode}->{total};
160 push @loopborrowertotal, \%line;
162 } else {
163 # A Borrower_category has been selected
164 # extracting corresponding data
165 $borrowertype{$categorycode}->{description} = $borrower_category;
166 $borrowertype{$categorycode}->{total} = 0;
167 while (($categorycode,$description) = $sthcategories->fetchrow) {
168 if ($description =~ /$borrower_category/ ) {
169 $borrower_categorycode = $categorycode;
170 my %cc;
171 $cc{categorycode} = $description;
172 push @categorycodeloop,\%cc;
173 foreach my $itemtype (keys %itemtypes) {
174 $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
176 $sth->execute($categorycode);
177 while (my ($itemtype, $total) = $sth->fetchrow) {
178 $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
179 $borrowertype{$categorycode}->{total} += $total;
180 $itemtypes{$itemtype}->{total} += $total;
181 $grantotal +=$total;
185 # build the result
186 foreach my $itemtype (keys %itemtypes) {
187 my @loopitemtype;
188 my %cell;
189 $cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
190 push @loopitemtype, \%cell;
191 my %line;
192 $line{loopitemtype} = \@loopitemtype;
193 if ($itemtypes{$itemtype}->{description}) {
194 $line{itemtype} = $itemtypes{$itemtype}->{description};
195 } else {
196 $line{itemtype} = "$itemtype (no entry in itemtype table)";
198 $line{hilighted} = 1 if $hilighted eq 1;
199 $line{totalitemtype} = $itemtypes{$itemtype}->{total};
200 $hilighted = -$hilighted;
201 push @loopborrowertype, \%line;
203 my %cell;
204 $cell{issues} = $borrowertype{$borrower_categorycode}->{total};
205 push @loopborrowertotal, \%cell;
207 # the header of the table
208 $globalline{loopborrowertype} = \@loopborrowertype;
209 # the core of the table
210 $globalline{categorycodeloop} = \@categorycodeloop;
211 # the foot (totals by borrower type)
212 $globalline{loopborrowertotal} = \@loopborrowertotal;
213 $globalline{grantotal}= $grantotal;
214 push @mainloop,\%globalline;
215 return \@mainloop;