Bug 15407: Koha::Patron::Categories - replace GetborCatFromCatType
[koha.git] / 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;