fixing buggy url in cancel button
[koha.git] / reports / issues_by_borrower_category.plugin
1 #!/usr/bin/perl
2
3 # $Id$
4
5 # Copyright 2000-2002 Katipo Communications
6 #
7 # This file is part of Koha.
8 #
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
12 # version.
13 #
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
17 #
18 # You should have received a copy of the GNU General Public License along with
19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
20 # Suite 330, Boston, MA  02111-1307 USA
21
22 use strict;
23 use C4::Auth;
24 use CGI;
25 use C4::Context;
26 use HTML::Template::Pro;
27 use C4::Search;
28 use C4::Output;
29 use C4::Koha;
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         my $dbh = C4::Context->dbh;
67         my $branches=GetBranches();
68         my @branches;
69         my @select_branch;
70         my %select_branches;
71         push @select_branch,"";
72         $select_branches{""} = "";
73         foreach my $branch (keys %$branches) {
74                 push @select_branch, $branch;
75                 $select_branches{$branch} = $branches->{$branch}->{'branchname'};
76         }
77         my $CGIbranch=CGI::scrolling_list( -name     => 'value',
78                                 -id => 'value',
79                                 -values   => \@select_branch,
80                                 -labels   => \%select_branches,
81                                 -size     => 1,
82                                 -multiple => 0 );
83         $template->param(CGIbranch => $CGIbranch);
84         return $template;
85 }
86 sub calculate {
87         my ($parameters) = @_;
88         my @results =();
89 # extract parameters
90         my $borrower_category = @$parameters[0];
91         my $branch = @$parameters[1];
92         my $dbh = C4::Context->dbh;
93 # build the SQL query & execute it
94
95 # 1st, loop every itemtypes.
96         my $sth = $dbh->prepare("select itemtype,description from itemtypes");
97         $sth->execute;
98         my %itemtypes;
99         while (my ($itemtype,$description) = $sth->fetchrow) {
100                 $itemtypes{$itemtype}->{description} = $description;
101                 $itemtypes{$itemtype}->{total} = 0;
102         }
103 # now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
104         my $strsth="SELECT itemtype, count( * )
105                                 FROM issues, borrowers, biblioitems, items
106                                 WHERE issues.borrowernumber = borrowers.borrowernumber 
107                                         AND items.itemnumber = issues.itemnumber 
108                                         AND biblioitems.biblionumber = items.biblionumber 
109                                         AND borrowers.categorycode = ?";
110         $strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
111         $strsth .= " GROUP BY biblioitems.itemtype";
112         my $sth = $dbh->prepare($strsth);
113         my $sthcategories = $dbh->prepare("select categorycode,description from categories");
114         $sthcategories->execute;
115         my %borrowertype;
116         my @categorycodeloop;
117         my $categorycode;
118         my $description;
119         my $borrower_categorycode =0;
120         my @mainloop;
121         my @itemtypeloop;
122         my @loopborrowertype;
123         my @loopborrowertotal;
124         my %globalline;
125         my $hilighted=-1;
126         my $grantotal =0;
127         #If no Borrower-category selected....
128         # Print all 
129         if (!$borrower_category) {
130                 while ( ($categorycode,$description) = $sthcategories->fetchrow) {
131                         $borrowertype{$categorycode}->{description} = $description;
132                         $borrowertype{$categorycode}->{total} = 0;
133                         my %categorycode;
134                         $categorycode{categorycode} = $description;
135                         push @categorycodeloop,\%categorycode;
136                         foreach my $itemtype (keys %itemtypes) {
137                                 $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
138                         }
139                         $sth->execute($categorycode);
140                         while (my ($itemtype, $total) = $sth->fetchrow) {
141                                 $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
142                                 $borrowertype{$categorycode}->{total} += $total;
143                                 $itemtypes{$itemtype}->{total} += $total;
144                                 $grantotal += $total;
145                         }
146                 }
147                 # build the result
148                 foreach my $itemtype (keys %itemtypes) {
149                         my @loopitemtype;
150                         $sthcategories->execute;
151                         while (($categorycode,$description) =  $sthcategories->fetchrow ) {
152                                 my %cell;
153                                 $cell{issues} = $itemtypes{$itemtype}->{results}->{$categorycode};
154                                 #printf stderr "%s      ",$categorycode;
155                                 push @loopitemtype,\%cell;
156                         }
157                         #printf stderr "\n";
158                         my %line;
159                         $line{loopitemtype} = \@loopitemtype;
160                         if ($itemtypes{$itemtype}->{description}) {
161                                 $line{itemtype} = $itemtypes{$itemtype}->{description};
162                         } else {
163                                 $line{itemtype} = "$itemtype (no entry in itemtype table)";
164                         }
165                         $line{hilighted} = 1 if $hilighted eq 1;
166                         $line{totalitemtype} = $itemtypes{$itemtype}->{total};
167                         $hilighted = -$hilighted;
168                         push @loopborrowertype, \%line;
169                 }
170                 $sthcategories->execute;
171                 while (($categorycode,$description) =  $sthcategories->fetchrow ) {
172                         my %line;
173                         $line{issues} = $borrowertype{$categorycode}->{total};
174                         push @loopborrowertotal, \%line;
175                 }
176         } else {
177                 # A Borrower_category has been selected
178                 # extracting corresponding data
179                 $borrowertype{$categorycode}->{description} = $borrower_category;
180                 $borrowertype{$categorycode}->{total} = 0;
181                 while (($categorycode,$description) = $sthcategories->fetchrow) {
182                         if ($description =~ /$borrower_category/ ) {
183                                 $borrower_categorycode = $categorycode;
184                                 my %cc;
185                                 $cc{categorycode} = $description;
186                                 push @categorycodeloop,\%cc;
187                                 foreach my $itemtype (keys %itemtypes) {
188                                         $itemtypes{$itemtype}->{results}->{$categorycode} = 0;
189                                 }
190                                 $sth->execute($categorycode);
191                                 while (my ($itemtype, $total) = $sth->fetchrow) {
192                                         $itemtypes{$itemtype}->{results}->{$categorycode} = $total;
193                                         $borrowertype{$categorycode}->{total} += $total;
194                                         $itemtypes{$itemtype}->{total} += $total;
195                                         $grantotal +=$total;
196                                 }
197                         }
198                 }
199                 # build the result
200                 foreach my $itemtype (keys %itemtypes) {
201                         my @loopitemtype;
202                         my %cell;
203                         $cell{issues}=$itemtypes{$itemtype}->{results}->{$borrower_categorycode};
204                         push @loopitemtype, \%cell;
205                         my %line;
206                         $line{loopitemtype} = \@loopitemtype;
207                         if ($itemtypes{$itemtype}->{description}) {
208                                 $line{itemtype} = $itemtypes{$itemtype}->{description};
209                         } else {
210                                 $line{itemtype} = "$itemtype (no entry in itemtype table)";
211                         }
212                         $line{hilighted} = 1 if $hilighted eq 1;
213                         $line{totalitemtype} = $itemtypes{$itemtype}->{total};
214                         $hilighted = -$hilighted;
215                         push @loopborrowertype, \%line;
216                 }
217                 my %cell;
218                 $cell{issues} = $borrowertype{$borrower_categorycode}->{total};
219                 push @loopborrowertotal, \%cell;
220         }
221         # the header of the table
222         $globalline{loopborrowertype} = \@loopborrowertype;
223         # the core of the table
224         $globalline{categorycodeloop} = \@categorycodeloop;
225         # the foot (totals by borrower type)
226         $globalline{loopborrowertotal} = \@loopborrowertotal;
227         $globalline{grantotal}= $grantotal;
228         push @mainloop,\%globalline;
229         return \@mainloop;
230 }
231
232 1;