Lines 1-297
Link Here
|
1 |
#!/usr/bin/perl |
|
|
2 |
|
3 |
# Copyright 2000-2002 Katipo Communications |
4 |
# |
5 |
# This file is part of Koha. |
6 |
# |
7 |
# Koha is free software; you can redistribute it and/or modify it |
8 |
# under the terms of the GNU General Public License as published by |
9 |
# the Free Software Foundation; either version 3 of the License, or |
10 |
# (at your option) any later version. |
11 |
# |
12 |
# Koha is distributed in the hope that it will be useful, but |
13 |
# WITHOUT ANY WARRANTY; without even the implied warranty of |
14 |
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
15 |
# GNU General Public License for more details. |
16 |
# |
17 |
# You should have received a copy of the GNU General Public License |
18 |
# along with Koha; if not, see <http://www.gnu.org/licenses>. |
19 |
|
20 |
use strict; |
21 |
use warnings; |
22 |
|
23 |
use CGI qw ( -utf8 ); |
24 |
use C4::Auth; |
25 |
use C4::Context; |
26 |
use C4::Koha; |
27 |
use C4::Output; |
28 |
use C4::Circulation; |
29 |
use C4::Reports; |
30 |
use C4::Members; |
31 |
use C4::Dates qw/format_date_in_iso/; |
32 |
|
33 |
=head1 NAME |
34 |
|
35 |
reports/borrowers_out.pl |
36 |
|
37 |
=head1 DESCRIPTION |
38 |
|
39 |
Plugin that shows a stats on borrowers |
40 |
|
41 |
=cut |
42 |
|
43 |
my $input = new CGI; |
44 |
my $do_it=$input->param('do_it'); |
45 |
my $fullreportname = "reports/borrowers_out.tt"; |
46 |
my $limit = $input->param("Limit"); |
47 |
my $column = $input->param("Criteria"); |
48 |
my @filters = $input->param("Filter"); |
49 |
$filters[1] = format_date_in_iso($filters[1]) if $filters[1]; |
50 |
my $output = $input->param("output"); |
51 |
my $basename = $input->param("basename"); |
52 |
our $sep = $input->param("sep") || ''; |
53 |
$sep = "\t" if ($sep eq 'tabulation'); |
54 |
my ($template, $borrowernumber, $cookie) |
55 |
= get_template_and_user({template_name => $fullreportname, |
56 |
query => $input, |
57 |
type => "intranet", |
58 |
authnotrequired => 0, |
59 |
flagsrequired => {reports => '*'}, |
60 |
debug => 1, |
61 |
}); |
62 |
$template->param(do_it => $do_it, |
63 |
); |
64 |
if ($do_it) { |
65 |
# Displaying results |
66 |
my $results = calculate($limit, $column, \@filters); |
67 |
if ($output eq "screen"){ |
68 |
# Printing results to screen |
69 |
$template->param(mainloop => $results); |
70 |
output_html_with_http_headers $input, $cookie, $template->output; |
71 |
exit; |
72 |
} else { |
73 |
# Printing to a csv file |
74 |
print $input->header(-type => 'application/vnd.sun.xml.calc', |
75 |
-encoding => 'utf-8', |
76 |
-attachment=>"$basename.csv", |
77 |
-filename=>"$basename.csv" ); |
78 |
my $cols = @$results[0]->{loopcol}; |
79 |
my $lines = @$results[0]->{looprow}; |
80 |
# header top-right |
81 |
print "num /". @$results[0]->{column} .$sep; |
82 |
# Other header |
83 |
foreach my $col ( @$cols ) { |
84 |
print $col->{coltitle}.$sep; |
85 |
} |
86 |
print "Total\n"; |
87 |
# Table |
88 |
foreach my $line ( @$lines ) { |
89 |
my $x = $line->{loopcell}; |
90 |
print $line->{rowtitle}.$sep; |
91 |
foreach my $cell (@$x) { |
92 |
my $cellvalue = defined $cell->{value} ? $cell->{value}.$sep : ''.$sep; |
93 |
print $cellvalue; |
94 |
} |
95 |
# print $line->{totalrow}; |
96 |
print "\n"; |
97 |
} |
98 |
# footer |
99 |
print "TOTAL"; |
100 |
$cols = @$results[0]->{loopfooter}; |
101 |
foreach my $col ( @$cols ) { |
102 |
print $sep.$col->{totalcol}; |
103 |
} |
104 |
print $sep.@$results[0]->{total}; |
105 |
exit; |
106 |
} |
107 |
# Displaying choices |
108 |
} else { |
109 |
my $dbh = C4::Context->dbh; |
110 |
my @values; |
111 |
my %labels; |
112 |
my %select; |
113 |
my $req; |
114 |
|
115 |
my $CGIextChoice = ( 'CSV' ); # FIXME translation |
116 |
my $CGIsepChoice = GetDelimiterChoices; |
117 |
|
118 |
my ($codes,$labels) = GetborCatFromCatType(undef,undef); |
119 |
my @borcatloop; |
120 |
foreach my $thisborcat (sort keys %$labels) { |
121 |
my %row =(value => $thisborcat, |
122 |
description => $labels->{$thisborcat}, |
123 |
); |
124 |
push @borcatloop, \%row; |
125 |
} |
126 |
$template->param( |
127 |
CGIextChoice => $CGIextChoice, |
128 |
CGIsepChoice => $CGIsepChoice, |
129 |
borcatloop =>\@borcatloop, |
130 |
); |
131 |
output_html_with_http_headers $input, $cookie, $template->output; |
132 |
} |
133 |
|
134 |
|
135 |
sub calculate { |
136 |
my ($line, $column, $filters) = @_; |
137 |
my @mainloop; |
138 |
my @loopfooter; |
139 |
my @loopcol; |
140 |
my @loopline; |
141 |
my @looprow; |
142 |
my %globalline; |
143 |
my $grantotal =0; |
144 |
# extract parameters |
145 |
my $dbh = C4::Context->dbh; |
146 |
|
147 |
# Filters |
148 |
# Checking filters |
149 |
# |
150 |
my @loopfilter; |
151 |
for (my $i=0;$i<=2;$i++) { |
152 |
my %cell; |
153 |
if ( @$filters[$i] ) { |
154 |
if (($i==1) and (@$filters[$i-1])) { |
155 |
$cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ; |
156 |
} |
157 |
$cell{filter} .= @$filters[$i]; |
158 |
$cell{crit} .="Bor Cat" if ($i==0); |
159 |
$cell{crit} .="Without issues since" if ($i==1); |
160 |
push @loopfilter, \%cell; |
161 |
} |
162 |
} |
163 |
my $colfield; |
164 |
my $colorder; |
165 |
if ($column){ |
166 |
$column = "borrowers.".$column if $column=~/categorycode/ || $column=~/branchcode/; |
167 |
my @colfilter ; |
168 |
$colfilter[0] = @$filters[0] if ($column =~ /category/ ) ; |
169 |
# $colfilter[0] = @$filters[11] if ($column =~ /sort2/ ) ; |
170 |
#warn "filtre col ".$colfilter[0]." ".$colfilter[1]; |
171 |
|
172 |
# loop cols. |
173 |
$colfield .= $column; |
174 |
$colorder .= $column; |
175 |
|
176 |
my $strsth2; |
177 |
$strsth2 .= "select distinct $colfield FROM borrowers WHERE 1"; |
178 |
if ($colfilter[0]) { |
179 |
$colfilter[0] =~ s/\*/%/g; |
180 |
$strsth2 .= " and $column LIKE '$colfilter[0]' " ; |
181 |
} |
182 |
$strsth2 .=" group by $colfield"; |
183 |
$strsth2 .=" order by $colorder"; |
184 |
# warn "". $strsth2; |
185 |
|
186 |
my $sth2 = $dbh->prepare( $strsth2 ); |
187 |
$sth2->execute; |
188 |
while (my ($celvalue) = $sth2->fetchrow) { |
189 |
my %cell; |
190 |
# my %ft; |
191 |
# warn "coltitle :".$celvalue; |
192 |
$cell{coltitle} = $celvalue; |
193 |
# $ft{totalcol} = 0; |
194 |
push @loopcol, \%cell; |
195 |
} |
196 |
# warn "fin des titres colonnes"; |
197 |
} |
198 |
|
199 |
my $i=0; |
200 |
# my @totalcol; |
201 |
|
202 |
#Initialization of cell values..... |
203 |
my @table; |
204 |
|
205 |
# warn "init table"; |
206 |
if($line) { |
207 |
for (my $i=1;$i<=$line;$i++) { |
208 |
foreach my $col ( @loopcol ) { |
209 |
$table[$i]->{($col->{coltitle})?$col->{coltitle}:"Global"}=0; |
210 |
} |
211 |
} |
212 |
} |
213 |
|
214 |
|
215 |
# preparing calculation |
216 |
my $strcalc ; |
217 |
|
218 |
# Processing calculation |
219 |
$strcalc .= "SELECT CONCAT( borrowers.surname , \"\\t\",borrowers.firstname, \"\\t\", borrowers.cardnumber)"; |
220 |
$strcalc .= " , $colfield " if ($colfield); |
221 |
$strcalc .= " FROM borrowers "; |
222 |
$strcalc .= "WHERE 1 "; |
223 |
@$filters[0]=~ s/\*/%/g if (@$filters[0]); |
224 |
$strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] ); |
225 |
|
226 |
$strcalc .= " AND NOT EXISTS (SELECT * FROM issues WHERE issues.borrowernumber=borrowers.borrowernumber "; |
227 |
$strcalc .= " AND issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]); |
228 |
$strcalc .= ") "; |
229 |
$strcalc .= " AND NOT EXISTS (SELECT * FROM old_issues WHERE old_issues.borrowernumber=borrowers.borrowernumber "; |
230 |
$strcalc .= " AND old_issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]); |
231 |
$strcalc .= ") "; |
232 |
$strcalc .= " group by borrowers.borrowernumber"; |
233 |
$strcalc .= ", $colfield" if ($column); |
234 |
$strcalc .= " order by $colfield " if ($colfield); |
235 |
my $max; |
236 |
if ($line) { |
237 |
if (@loopcol) { |
238 |
$max = $line*@loopcol; |
239 |
} else { $max=$line;} |
240 |
$strcalc .= " LIMIT 0,$max"; |
241 |
} |
242 |
|
243 |
my $dbcalc = $dbh->prepare($strcalc); |
244 |
$dbcalc->execute; |
245 |
# warn "filling table"; |
246 |
my $previous_col; |
247 |
$i=1; |
248 |
while (my @data = $dbcalc->fetchrow) { |
249 |
my ($row, $col )=@data; |
250 |
$col = "zzEMPTY" if (!defined($col)); |
251 |
$i=1 if (($previous_col) and not($col eq $previous_col)); |
252 |
$table[$i]->{$col}=$row; |
253 |
# warn " $i $col $row"; |
254 |
$i++; |
255 |
$previous_col=$col; |
256 |
} |
257 |
|
258 |
push @loopcol,{coltitle => "Global"} if not($column); |
259 |
|
260 |
$max =(($line)?$line:@table -1); |
261 |
for ($i=1; $i<=$max;$i++) { |
262 |
my @loopcell; |
263 |
#@loopcol ensures the order for columns is common with column titles |
264 |
# and the number matches the number of columns |
265 |
my $colcount=0; |
266 |
foreach my $col ( @loopcol ) { |
267 |
my $value; |
268 |
if (@loopcol){ |
269 |
$value =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}; |
270 |
} else { |
271 |
$value =$table[$i]->{"zzEMPTY"}; |
272 |
} |
273 |
push @loopcell, {value => $value} ; |
274 |
} |
275 |
push @looprow,{ 'rowtitle' => $i , |
276 |
'loopcell' => \@loopcell, |
277 |
}; |
278 |
} |
279 |
|
280 |
|
281 |
|
282 |
# the header of the table |
283 |
$globalline{loopfilter}=\@loopfilter; |
284 |
# the core of the table |
285 |
$globalline{looprow} = \@looprow; |
286 |
$globalline{loopcol} = \@loopcol; |
287 |
# # the foot (totals by borrower type) |
288 |
$globalline{loopfooter} = \@loopfooter; |
289 |
$globalline{total}= $grantotal; |
290 |
$globalline{line} = $line; |
291 |
$globalline{column} = $column; |
292 |
push @mainloop,\%globalline; |
293 |
return \@mainloop; |
294 |
} |
295 |
|
296 |
1; |
297 |
__END__ |
298 |
- |