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 under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
25 use HTML
::Template
::Pro
;
29 use C4
::Branch
; # GetBranches
33 plugin that shows a table with issues for categories and borrower
37 this result is quite complex to build...
38 the 2D array contains :
40 * borrowers types on rows
42 If no issues are done, the array must be filled by 0 anyway.
43 So, the script works as this :
44 1- parse the itemtype table to get itemtype descriptions and set itemtype total to 0
45 2- for each borrower category :
46 ** create an array with total = 0 for each itemtype defined in 1
47 ** calculate the total for each itemtype (SQL request)
48 The big hash has the following structure :
51 ->{borrowercategorycode} => the total of issues for each cell of the table.
52 ->{total} => the total for the itemtype
53 ->{description} => the itemtype description
55 the borrowertype hash contains description and total for each borrowercategory.
57 the hashes are then translated to hash / arrays to be returned to manager.pl & send to the template
65 my $dbh = C4
::Context
->dbh;
66 my $branches=GetBranches
();
70 push @select_branch,"";
71 $select_branches{""} = "";
72 foreach my $branch (keys %$branches) {
73 push @select_branch, $branch;
74 $select_branches{$branch} = $branches->{$branch}->{'branchname'};
76 my $CGIbranch=CGI
::scrolling_list
( -name
=> 'value',
78 -values => \
@select_branch,
79 -labels
=> \
%select_branches,
82 $template->param(CGIbranch
=> $CGIbranch);
86 my ($parameters) = @_;
89 my $borrower_category = @
$parameters[0];
90 my $branch = @
$parameters[1];
91 my $dbh = C4
::Context
->dbh;
92 # build the SQL query & execute it
94 # 1st, loop every itemtypes.
95 my $sth = $dbh->prepare("select itemtype,description from itemtypes");
98 while (my ($itemtype,$description) = $sth->fetchrow) {
99 $itemtypes{$itemtype}->{description
} = $description;
100 $itemtypes{$itemtype}->{total
} = 0;
102 # now, parse each category. Before filling the result array, fill it with 0 to have every itemtype column.
103 my $strsth="SELECT itemtype, count( * )
104 FROM issues, borrowers, biblioitems, items
105 WHERE issues.borrowernumber = borrowers.borrowernumber
106 AND items.itemnumber = issues.itemnumber
107 AND biblioitems.biblionumber = items.biblionumber
108 AND borrowers.categorycode = ?";
109 $strsth.= " AND borrowers.branchcode = ".$dbh->quote($branch) if ($branch);
110 $strsth .= " GROUP BY biblioitems.itemtype";
111 my $sth = $dbh->prepare($strsth);
112 my $sthcategories = $dbh->prepare("select categorycode,description from categories");
113 $sthcategories->execute;
115 my @categorycodeloop;
118 my $borrower_categorycode =0;
121 my @loopborrowertype;
122 my @loopborrowertotal;
126 #If no Borrower-category selected....
128 if (!$borrower_category) {
129 while ( ($categorycode,$description) = $sthcategories->fetchrow) {
130 $borrowertype{$categorycode}->{description
} = $description;
131 $borrowertype{$categorycode}->{total
} = 0;
133 $categorycode{categorycode
} = $description;
134 push @categorycodeloop,\
%categorycode;
135 foreach my $itemtype (keys %itemtypes) {
136 $itemtypes{$itemtype}->{results
}->{$categorycode} = 0;
138 $sth->execute($categorycode);
139 while (my ($itemtype, $total) = $sth->fetchrow) {
140 $itemtypes{$itemtype}->{results
}->{$categorycode} = $total;
141 $borrowertype{$categorycode}->{total
} += $total;
142 $itemtypes{$itemtype}->{total
} += $total;
143 $grantotal += $total;
147 foreach my $itemtype (keys %itemtypes) {
149 $sthcategories->execute;
150 while (($categorycode,$description) = $sthcategories->fetchrow ) {
152 $cell{issues
} = $itemtypes{$itemtype}->{results
}->{$categorycode};
153 #printf stderr "%s ",$categorycode;
154 push @loopitemtype,\
%cell;
158 $line{loopitemtype
} = \
@loopitemtype;
159 if ($itemtypes{$itemtype}->{description
}) {
160 $line{itemtype
} = $itemtypes{$itemtype}->{description
};
162 $line{itemtype
} = "$itemtype (no entry in itemtype table)";
164 $line{hilighted
} = 1 if $hilighted eq 1;
165 $line{totalitemtype
} = $itemtypes{$itemtype}->{total
};
166 $hilighted = -$hilighted;
167 push @loopborrowertype, \
%line;
169 $sthcategories->execute;
170 while (($categorycode,$description) = $sthcategories->fetchrow ) {
172 $line{issues
} = $borrowertype{$categorycode}->{total
};
173 push @loopborrowertotal, \
%line;
176 # A Borrower_category has been selected
177 # extracting corresponding data
178 $borrowertype{$categorycode}->{description
} = $borrower_category;
179 $borrowertype{$categorycode}->{total
} = 0;
180 while (($categorycode,$description) = $sthcategories->fetchrow) {
181 if ($description =~ /$borrower_category/ ) {
182 $borrower_categorycode = $categorycode;
184 $cc{categorycode
} = $description;
185 push @categorycodeloop,\
%cc;
186 foreach my $itemtype (keys %itemtypes) {
187 $itemtypes{$itemtype}->{results
}->{$categorycode} = 0;
189 $sth->execute($categorycode);
190 while (my ($itemtype, $total) = $sth->fetchrow) {
191 $itemtypes{$itemtype}->{results
}->{$categorycode} = $total;
192 $borrowertype{$categorycode}->{total
} += $total;
193 $itemtypes{$itemtype}->{total
} += $total;
199 foreach my $itemtype (keys %itemtypes) {
202 $cell{issues
}=$itemtypes{$itemtype}->{results
}->{$borrower_categorycode};
203 push @loopitemtype, \
%cell;
205 $line{loopitemtype
} = \
@loopitemtype;
206 if ($itemtypes{$itemtype}->{description
}) {
207 $line{itemtype
} = $itemtypes{$itemtype}->{description
};
209 $line{itemtype
} = "$itemtype (no entry in itemtype table)";
211 $line{hilighted
} = 1 if $hilighted eq 1;
212 $line{totalitemtype
} = $itemtypes{$itemtype}->{total
};
213 $hilighted = -$hilighted;
214 push @loopborrowertype, \
%line;
217 $cell{issues
} = $borrowertype{$borrower_categorycode}->{total
};
218 push @loopborrowertotal, \
%cell;
220 # the header of the table
221 $globalline{loopborrowertype
} = \
@loopborrowertype;
222 # the core of the table
223 $globalline{categorycodeloop
} = \
@categorycodeloop;
224 # the foot (totals by borrower type)
225 $globalline{loopborrowertotal
} = \
@loopborrowertotal;
226 $globalline{grantotal
}= $grantotal;
227 push @mainloop,\
%globalline;