5 # Copyright 2000-2002 Katipo Communications
7 # This file is part of Koha.
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
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.
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
26 use HTML
::Template
::Pro
;
30 use C4
::Branch
; # GetBranches
34 plugin that shows a table with issues for categories and borrower
38 this result is quite complex to build...
39 the 2D array contains :
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 :
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
66 my $dbh = C4
::Context
->dbh;
67 my $branches=GetBranches
();
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'};
77 my $CGIbranch=CGI
::scrolling_list
( -name
=> 'value',
79 -values => \
@select_branch,
80 -labels
=> \
%select_branches,
83 $template->param(CGIbranch
=> $CGIbranch);
87 my ($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
95 # 1st, loop every itemtypes.
96 my $sth = $dbh->prepare("select itemtype,description from itemtypes");
99 while (my ($itemtype,$description) = $sth->fetchrow) {
100 $itemtypes{$itemtype}->{description
} = $description;
101 $itemtypes{$itemtype}->{total
} = 0;
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;
116 my @categorycodeloop;
119 my $borrower_categorycode =0;
122 my @loopborrowertype;
123 my @loopborrowertotal;
127 #If no Borrower-category selected....
129 if (!$borrower_category) {
130 while ( ($categorycode,$description) = $sthcategories->fetchrow) {
131 $borrowertype{$categorycode}->{description
} = $description;
132 $borrowertype{$categorycode}->{total
} = 0;
134 $categorycode{categorycode
} = $description;
135 push @categorycodeloop,\
%categorycode;
136 foreach my $itemtype (keys %itemtypes) {
137 $itemtypes{$itemtype}->{results
}->{$categorycode} = 0;
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;
148 foreach my $itemtype (keys %itemtypes) {
150 $sthcategories->execute;
151 while (($categorycode,$description) = $sthcategories->fetchrow ) {
153 $cell{issues
} = $itemtypes{$itemtype}->{results
}->{$categorycode};
154 #printf stderr "%s ",$categorycode;
155 push @loopitemtype,\
%cell;
159 $line{loopitemtype
} = \
@loopitemtype;
160 if ($itemtypes{$itemtype}->{description
}) {
161 $line{itemtype
} = $itemtypes{$itemtype}->{description
};
163 $line{itemtype
} = "$itemtype (no entry in itemtype table)";
165 $line{hilighted
} = 1 if $hilighted eq 1;
166 $line{totalitemtype
} = $itemtypes{$itemtype}->{total
};
167 $hilighted = -$hilighted;
168 push @loopborrowertype, \
%line;
170 $sthcategories->execute;
171 while (($categorycode,$description) = $sthcategories->fetchrow ) {
173 $line{issues
} = $borrowertype{$categorycode}->{total
};
174 push @loopborrowertotal, \
%line;
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;
185 $cc{categorycode
} = $description;
186 push @categorycodeloop,\
%cc;
187 foreach my $itemtype (keys %itemtypes) {
188 $itemtypes{$itemtype}->{results
}->{$categorycode} = 0;
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;
200 foreach my $itemtype (keys %itemtypes) {
203 $cell{issues
}=$itemtypes{$itemtype}->{results
}->{$borrower_categorycode};
204 push @loopitemtype, \
%cell;
206 $line{loopitemtype
} = \
@loopitemtype;
207 if ($itemtypes{$itemtype}->{description
}) {
208 $line{itemtype
} = $itemtypes{$itemtype}->{description
};
210 $line{itemtype
} = "$itemtype (no entry in itemtype table)";
212 $line{hilighted
} = 1 if $hilighted eq 1;
213 $line{totalitemtype
} = $itemtypes{$itemtype}->{total
};
214 $hilighted = -$hilighted;
215 push @loopborrowertype, \
%line;
218 $cell{issues
} = $borrowertype{$borrower_categorycode}->{total
};
219 push @loopborrowertotal, \
%cell;
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;