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