Lines 20-27
Link Here
|
20 |
|
20 |
|
21 |
# test comment |
21 |
# test comment |
22 |
|
22 |
|
23 |
use strict; |
23 |
use Modern::Perl; |
24 |
#use warnings; FIXME - Bug 2505 |
24 |
|
25 |
use C4::Auth; |
25 |
use C4::Auth; |
26 |
use CGI; |
26 |
use CGI; |
27 |
use C4::Context; |
27 |
use C4::Context; |
Lines 30-35
use C4::Output;
Link Here
|
30 |
use C4::Koha; |
30 |
use C4::Koha; |
31 |
use C4::Circulation; |
31 |
use C4::Circulation; |
32 |
use C4::Dates qw/format_date format_date_in_iso/; |
32 |
use C4::Dates qw/format_date format_date_in_iso/; |
|
|
33 |
use C4::Branch; |
34 |
use C4::Biblio; |
33 |
|
35 |
|
34 |
=head1 NAME |
36 |
=head1 NAME |
35 |
|
37 |
|
Lines 53-79
$filters[2] = format_date_in_iso( $filters[2] );
Link Here
|
53 |
$filters[3] = format_date_in_iso( $filters[3] ); |
55 |
$filters[3] = format_date_in_iso( $filters[3] ); |
54 |
my $podsp = $input->param("PlacedOnDisplay"); |
56 |
my $podsp = $input->param("PlacedOnDisplay"); |
55 |
my $rodsp = $input->param("ReceivedOnDisplay"); |
57 |
my $rodsp = $input->param("ReceivedOnDisplay"); |
56 |
my $aodsp = $input->param("AcquiredOnDisplay"); ##added by mason. |
|
|
57 |
my $calc = $input->param("Cellvalue"); |
58 |
my $calc = $input->param("Cellvalue"); |
58 |
my $output = $input->param("output"); |
59 |
my $output = $input->param("output"); |
59 |
my $basename = $input->param("basename"); |
60 |
my $basename = $input->param("basename"); |
60 |
|
61 |
|
61 |
my ($template, $borrowernumber, $cookie) |
62 |
my ( $template, $borrowernumber, $cookie ) = get_template_and_user( |
62 |
= get_template_and_user({template_name => $fullreportname, |
63 |
{ |
63 |
query => $input, |
64 |
template_name => $fullreportname, |
64 |
type => "intranet", |
65 |
query => $input, |
65 |
authnotrequired => 0, |
66 |
type => "intranet", |
66 |
flagsrequired => {reports => '*'}, |
67 |
authnotrequired => 0, |
67 |
debug => 1, |
68 |
flagsrequired => { reports => '*' }, |
68 |
}); |
69 |
debug => 1, |
69 |
our $sep = $input->param("sep"); |
70 |
} |
|
|
71 |
); |
72 |
|
73 |
our $sep = $input->param("sep") // ''; |
70 |
$sep = "\t" if ($sep eq 'tabulation'); |
74 |
$sep = "\t" if ($sep eq 'tabulation'); |
71 |
$template->param(do_it => $do_it, |
75 |
$template->param( |
72 |
DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), |
76 |
do_it => $do_it, |
73 |
); |
77 |
DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), |
|
|
78 |
); |
74 |
if ($do_it) { |
79 |
if ($do_it) { |
75 |
my $results = |
80 |
my $results = |
76 |
calculate( $line, $column, $podsp, $rodsp, $aodsp, $calc, \@filters ); |
81 |
calculate( $line, $column, $podsp, $rodsp, $calc, \@filters ); |
77 |
if ( $output eq "screen" ) { |
82 |
if ( $output eq "screen" ) { |
78 |
$template->param( mainloop => $results ); |
83 |
$template->param( mainloop => $results ); |
79 |
output_html_with_http_headers $input, $cookie, $template->output; |
84 |
output_html_with_http_headers $input, $cookie, $template->output; |
Lines 117-125
else {
Link Here
|
117 |
my $req; |
122 |
my $req; |
118 |
$req = $dbh->prepare("SELECT distinctrow id,name FROM aqbooksellers ORDER BY name"); |
123 |
$req = $dbh->prepare("SELECT distinctrow id,name FROM aqbooksellers ORDER BY name"); |
119 |
$req->execute; |
124 |
$req->execute; |
120 |
my @select; |
|
|
121 |
push @select, ""; |
125 |
push @select, ""; |
122 |
$select{''} = "All Suppliers"; |
126 |
$select{''} = "All Suppliers"; |
123 |
while ( my ( $value, $desc ) = $req->fetchrow ) { |
127 |
while ( my ( $value, $desc ) = $req->fetchrow ) { |
124 |
push @select, $desc; |
128 |
push @select, $desc; |
125 |
$select{$value}=$desc; |
129 |
$select{$value}=$desc; |
Lines 183-193
else {
Link Here
|
183 |
$select{''} = "All"; |
187 |
$select{''} = "All"; |
184 |
my $hassort1; |
188 |
my $hassort1; |
185 |
while ( my ($value) = $req->fetchrow ) { |
189 |
while ( my ($value) = $req->fetchrow ) { |
186 |
if ($value) { |
190 |
if ($value) { |
187 |
$hassort1 = 1; |
191 |
$hassort1 = 1; |
188 |
push @select, $value; |
192 |
push @select, $value; |
189 |
$select{$value} = $value; |
193 |
$select{$value} = $value; |
190 |
} |
194 |
} |
191 |
} |
195 |
} |
192 |
my $CGISort1 = CGI::scrolling_list( |
196 |
my $CGISort1 = CGI::scrolling_list( |
193 |
-name => 'Filter', |
197 |
-name => 'Filter', |
Lines 211-222
else {
Link Here
|
211 |
my $hglghtsort2; |
215 |
my $hglghtsort2; |
212 |
|
216 |
|
213 |
while ( my ($value) = $req->fetchrow ) { |
217 |
while ( my ($value) = $req->fetchrow ) { |
214 |
if ($value) { |
218 |
if ($value) { |
215 |
$hassort2 = 1; |
219 |
$hassort2 = 1; |
216 |
$hglghtsort2 = !($hassort1); |
220 |
$hglghtsort2 = !($hassort1); |
217 |
push @select, $value; |
221 |
push @select, $value; |
218 |
$select{$value} = $value; |
222 |
$select{$value} = $value; |
219 |
} |
223 |
} |
220 |
} |
224 |
} |
221 |
my $CGISort2 = CGI::scrolling_list( |
225 |
my $CGISort2 = CGI::scrolling_list( |
222 |
-name => 'Filter', |
226 |
-name => 'Filter', |
Lines 237-261
else {
Link Here
|
237 |
|
241 |
|
238 |
my $CGIsepChoice = GetDelimiterChoices; |
242 |
my $CGIsepChoice = GetDelimiterChoices; |
239 |
|
243 |
|
|
|
244 |
my $branches = GetBranches; |
245 |
my @branches; |
246 |
foreach ( sort keys %$branches ) { |
247 |
push @branches, $branches->{$_}; |
248 |
} |
249 |
|
250 |
my $ccode_subfield_structure = GetMarcSubfieldStructureFromKohaField('items.ccode', ''); |
251 |
my $ccode_label; |
252 |
my $ccode_avlist; |
253 |
if($ccode_subfield_structure) { |
254 |
$ccode_label = $ccode_subfield_structure->{liblibrarian}; |
255 |
$ccode_avlist = GetAuthorisedValues($ccode_subfield_structure->{authorised_value}); |
256 |
} |
257 |
|
240 |
$template->param( |
258 |
$template->param( |
241 |
CGIBookSeller => $CGIBookSellers, |
259 |
CGIBookSeller => $CGIBookSellers, |
242 |
CGIItemType => $CGIItemTypes, |
260 |
CGIItemType => $CGIItemTypes, |
243 |
CGIBudget => $CGIBudget, |
261 |
CGIBudget => $CGIBudget, |
244 |
hassort1 => $hassort1, |
262 |
hassort1 => $hassort1, |
245 |
hassort2 => $hassort2, |
263 |
hassort2 => $hassort2, |
246 |
HlghtSort2 => $hglghtsort2, |
|
|
247 |
CGISort1 => $CGISort1, |
264 |
CGISort1 => $CGISort1, |
248 |
CGISort2 => $CGISort2, |
265 |
CGISort2 => $CGISort2, |
249 |
CGIextChoice => $CGIextChoice, |
266 |
CGIextChoice => $CGIextChoice, |
250 |
CGIsepChoice => $CGIsepChoice, |
267 |
CGIsepChoice => $CGIsepChoice, |
251 |
date_today => C4::Dates->new()->output() |
268 |
branches => \@branches, |
|
|
269 |
ccode_label => $ccode_label, |
270 |
ccode_avlist => $ccode_avlist, |
252 |
); |
271 |
); |
253 |
|
272 |
|
254 |
} |
273 |
} |
255 |
output_html_with_http_headers $input, $cookie, $template->output; |
274 |
output_html_with_http_headers $input, $cookie, $template->output; |
256 |
|
275 |
|
257 |
sub calculate { |
276 |
sub calculate { |
258 |
my ( $line, $column, $podsp, $rodsp, $aodsp, $process, $filters ) = @_; |
277 |
my ( $line, $column, $podsp, $rodsp, $process, $filters ) = @_; |
259 |
my @mainloop; |
278 |
my @mainloop; |
260 |
my @loopfooter; |
279 |
my @loopfooter; |
261 |
my @loopcol; |
280 |
my @loopcol; |
Lines 264-269
sub calculate {
Link Here
|
264 |
my %globalline; |
283 |
my %globalline; |
265 |
my $grantotal = 0; |
284 |
my $grantotal = 0; |
266 |
|
285 |
|
|
|
286 |
$podsp ||= 0; |
287 |
$rodsp ||= 0; |
288 |
|
267 |
# extract parameters |
289 |
# extract parameters |
268 |
my $dbh = C4::Context->dbh; |
290 |
my $dbh = C4::Context->dbh; |
269 |
|
291 |
|
Lines 271-379
sub calculate {
Link Here
|
271 |
# Checking filters |
293 |
# Checking filters |
272 |
# |
294 |
# |
273 |
my @loopfilter; |
295 |
my @loopfilter; |
274 |
for ( my $i = 0 ; $i <= 8 ; $i++ ) { |
296 |
for ( my $i = 0 ; $i <= @$filters ; $i++ ) { |
275 |
my %cell; |
297 |
if( defined @$filters[$i] and @$filters[$i] ne '' ) { |
276 |
if ( @$filters[$i] ) { |
298 |
my %cell; |
277 |
if ( ( ( $i == 1 ) or ( $i == 3 ) ) and ( @$filters[ $i - 1 ] ) ) { |
299 |
if ( ( ( $i == 1 ) or ( $i == 3 ) ) and ( @$filters[ $i - 1 ] ) ) { |
278 |
$cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] ); |
300 |
$cell{err} = 1 if ( @$filters[$i] lt @$filters[ $i - 1 ] ); |
279 |
} |
301 |
} |
280 |
# format the dates filters, otherwise just fill as is |
302 |
# format the dates filters, otherwise just fill as is |
281 |
if ($i>=4) { |
303 |
if ($i >= 4) { |
282 |
$cell{filter} .= @$filters[$i]; |
304 |
$cell{filter} = @$filters[$i]; |
283 |
} else { |
305 |
} else { |
284 |
$cell{filter} .= format_date(@$filters[$i]); |
306 |
$cell{filter} = format_date(@$filters[$i]); |
|
|
307 |
} |
308 |
given ($i) { |
309 |
when (0) { $cell{crit} = "Placed On From" } |
310 |
when (1) { $cell{crit} = "Placed On To" } |
311 |
when (2) { $cell{crit} = "Received On From" } |
312 |
when (3) { $cell{crit} = "Received On To" } |
313 |
when (4) { $cell{crit} = "Bookseller" } |
314 |
when (5) { $cell{crit} = "Home branch" } |
315 |
when (6) { $cell{crit} = "Collection" } |
316 |
when (7) { $cell{crit} = "Doc Type" } |
317 |
when (8) { $cell{crit} = "Budget" } |
318 |
when (9) { $cell{crit} = "Sort1" } |
319 |
when (10) { $cell{crit} = "Sort2" } |
320 |
default { $cell{crit} = "" } |
285 |
} |
321 |
} |
286 |
$cell{crit} .= "Placed On From" if ( $i == 0 ); |
|
|
287 |
$cell{crit} .= "Placed On To" if ( $i == 1 ); |
288 |
$cell{crit} .= "Received On From" if ( $i == 2 ); |
289 |
$cell{crit} .= "Received On To" if ( $i == 3 ); |
290 |
|
291 |
# $cell{crit} .= "Acquired On From" if ( $i == 4 ); |
292 |
# $cell{crit} .= "Acquired On To" if ( $i == 5 ); |
293 |
|
294 |
$cell{crit} .= "BookSeller" if ( $i == 4 ); |
295 |
$cell{crit} .= "Doc Type" if ( $i == 5 ); |
296 |
$cell{crit} .= "Budget" if ( $i == 6 ); |
297 |
$cell{crit} .= "Sort1" if ( $i == 7 ); |
298 |
$cell{crit} .= "Sort2" if ( $i == 8 ); |
299 |
push @loopfilter, \%cell; |
322 |
push @loopfilter, \%cell; |
300 |
} |
323 |
} |
301 |
} |
324 |
} |
302 |
|
325 |
|
303 |
my @linefilter; |
326 |
my %filter; |
304 |
|
327 |
my %field; |
305 |
$linefilter[0] = @$filters[0] if ( $line =~ /closedate/ ); |
328 |
foreach ($line, $column) { |
306 |
$linefilter[1] = @$filters[1] if ( $line =~ /closedate/ ); |
329 |
$filter{$_} = []; |
307 |
$linefilter[0] = @$filters[2] if ( $line =~ /received/ ); |
330 |
$field{$_} = $_; |
308 |
$linefilter[1] = @$filters[3] if ( $line =~ /received/ ); |
331 |
given ($_) { |
309 |
|
332 |
when (/closedate/) { |
310 |
$linefilter[0] = @$filters[4] if ( $line =~ /bookseller/ ); |
333 |
$filter{$_}->[0] = @$filters[0]; |
311 |
$linefilter[0] = @$filters[5] if ( $line =~ /itemtype/ ); |
334 |
$filter{$_}->[1] = @$filters[1]; |
312 |
$linefilter[0] = @$filters[6] if ( $line =~ /budget/ ); |
335 |
my $a = $_; |
313 |
$linefilter[0] = @$filters[7] if ( $line =~ /sort1/ ); |
336 |
given ($podsp) { |
314 |
$linefilter[0] = @$filters[8] if ( $line =~ /sort2/ ); |
337 |
when (1) { $field{$a} = "concat(hex(weekday($a)+1),'-',dayname($a))" } |
315 |
|
338 |
when (2) { $field{$a} = "concat(hex(month($a)),'-',monthname($a))" } |
316 |
my @colfilter; |
339 |
when (3) { $field{$a} = "Year($a)" } |
317 |
$colfilter[0] = @$filters[0] if ( $column =~ /closedate/ ); |
340 |
default { $field{$a} = $a } |
318 |
$colfilter[1] = @$filters[1] if ( $column =~ /closedate/ ); |
341 |
} |
319 |
$colfilter[0] = @$filters[2] if ( $column =~ /received/ ); |
342 |
} |
320 |
$colfilter[1] = @$filters[3] if ( $column =~ /received/ ); |
343 |
when (/received/) { |
321 |
|
344 |
$filter{$_}->[0] = @$filters[2]; |
322 |
$colfilter[0] = @$filters[4] if ( $column =~ /bookseller/ ); |
345 |
$filter{$_}->[1] = @$filters[3]; |
323 |
$colfilter[0] = @$filters[5] if ( $column =~ /itemtype/ ); |
346 |
my $a = $_; |
324 |
$colfilter[0] = @$filters[6] if ( $column =~ /budget/ ); |
347 |
given ($rodsp) { |
325 |
$colfilter[0] = @$filters[7] if ( $column =~ /sort1/ ); |
348 |
when (1) { $field{$a} = "concat(hex(weekday($a)+1),'-',dayname($a))" } |
326 |
$colfilter[0] = @$filters[8] if ( $column =~ /sort2/ ); |
349 |
when (2) { $field{$a} = "concat(hex(month($a)),'-',monthname($a))" } |
327 |
|
350 |
when (3) { $field{$a} = "Year($a)" } |
328 |
# 1st, loop rows. |
351 |
default { $field{$a} = $a } |
329 |
my $linefield; |
352 |
} |
330 |
if ( ( $line =~ /closedate/ ) and ( $podsp == 1 ) ) { |
353 |
} |
331 |
|
354 |
when (/bookseller/) { |
332 |
#Display by day |
355 |
$filter{$_}->[0] = @$filters[4]; |
333 |
$linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))"; |
356 |
} |
334 |
} |
357 |
when (/homebranch/) { |
335 |
elsif ( ( $line =~ /closedate/ ) and ( $podsp == 2 ) ) { |
358 |
$filter{$_}->[0] = @$filters[5]; |
336 |
|
359 |
} |
337 |
#Display by Month |
360 |
when (/ccode/) { |
338 |
$linefield .= "concat(hex(month($line)),'-',monthname($line))"; |
361 |
$filter{$_}->[0] = @$filters[6]; |
339 |
} |
362 |
} |
340 |
elsif ( ( $line =~ /closedate/ ) and ( $podsp == 3 ) ) { |
363 |
when (/itemtype/) { |
341 |
|
364 |
$filter{$_}->[0] = @$filters[7]; |
342 |
#Display by Year |
365 |
} |
343 |
$linefield .= "Year($line)"; |
366 |
when (/budget/) { |
344 |
|
367 |
$filter{$_}->[0] = @$filters[8]; |
345 |
} |
368 |
} |
346 |
elsif ( ( $line =~ /received/ ) and ( $rodsp == 1 ) ) { |
369 |
when (/sort1/) { |
347 |
|
370 |
$filter{$_}->[0] = @$filters[9]; |
348 |
#Display by day |
371 |
} |
349 |
$linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))"; |
372 |
when (/sort2/) { |
350 |
} |
373 |
$filter{$_}->[0] = @$filters[10]; |
351 |
elsif ( ( $line =~ /received/ ) and ( $rodsp == 2 ) ) { |
374 |
} |
352 |
|
375 |
} |
353 |
#Display by Month |
|
|
354 |
$linefield .= "concat(hex(month($line)),'-',monthname($line))"; |
355 |
} |
376 |
} |
356 |
elsif ( ( $line =~ /received/ ) and ( $rodsp == 3 ) ) { |
|
|
357 |
|
377 |
|
358 |
#Display by Year |
378 |
my @linefilter = @{ $filter{$line} }; |
359 |
$linefield .= "Year($line)"; |
379 |
my $linefield = $field{$line}; |
|
|
380 |
my @colfilter = @{ $filter{$column} }; |
381 |
my $colfield = $field{$column}; |
360 |
|
382 |
|
361 |
} |
383 |
# 1st, loop rows. |
362 |
else { |
384 |
my $strsth = " |
363 |
$linefield .= $line; |
385 |
SELECT DISTINCTROW $linefield |
364 |
} |
386 |
FROM aqorders |
365 |
|
387 |
LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) |
366 |
my $strsth; |
388 |
LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber) |
367 |
$strsth .= |
389 |
LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber) |
368 |
"SELECT DISTINCTROW $linefield FROM (aqorders, aqbasket ) |
390 |
LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber) |
369 |
LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber) |
391 |
LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) |
370 |
LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber) |
392 |
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id) |
371 |
LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) |
393 |
WHERE $line IS NOT NULL AND $line <> '' "; |
372 |
|
394 |
|
373 |
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno) |
395 |
if (@linefilter) { |
374 |
AND $line IS NOT NULL AND $line <> '' "; |
|
|
375 |
|
376 |
if (@linefilter) { |
377 |
if ( $linefilter[1] ) { |
396 |
if ( $linefilter[1] ) { |
378 |
if ( $linefilter[0] ) { |
397 |
if ( $linefilter[0] ) { |
379 |
$strsth .= " AND $line BETWEEN ? AND ? "; |
398 |
$strsth .= " AND $line BETWEEN ? AND ? "; |
Lines 408-469
sub calculate {
Link Here
|
408 |
else { |
427 |
else { |
409 |
$sth->execute; |
428 |
$sth->execute; |
410 |
} |
429 |
} |
411 |
while ( my ($celvalue) = $sth->fetchrow ) { |
430 |
while ( my ($celvalue) = $sth->fetchrow ) { |
412 |
my %cell; |
431 |
my %cell; |
413 |
if ($celvalue) { |
432 |
if ($celvalue) { |
414 |
$cell{rowtitle} = $celvalue; |
433 |
$cell{rowtitle} = $celvalue; |
415 |
push @loopline, \%cell; |
434 |
push @loopline, \%cell; |
416 |
} |
435 |
} |
417 |
$cell{totalrow} = 0; |
436 |
$cell{totalrow} = 0; |
418 |
} |
|
|
419 |
# 2nd, loop cols. |
420 |
my $colfield; |
421 |
if ( ( $column =~ /closedate/ ) and ( $podsp == 1 ) ) { |
422 |
|
423 |
#Display by day |
424 |
$colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))"; |
425 |
} |
426 |
elsif ( ( $column =~ /closedate/ ) and ( $podsp == 2 ) ) { |
427 |
|
428 |
#Display by Month |
429 |
$colfield .= "concat(hex(month($column)),'-',monthname($column))"; |
430 |
} |
431 |
elsif ( ( $column =~ /closedate/ ) and ( $podsp == 3 ) ) { |
432 |
|
433 |
#Display by Year |
434 |
$colfield .= "Year($column)"; |
435 |
|
436 |
} |
437 |
elsif ( ( $column =~ /received/ ) and ( $rodsp == 1 ) ) { |
438 |
|
439 |
#Display by day |
440 |
$colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))"; |
441 |
} |
442 |
elsif ( ( $column =~ /received/ ) and ( $rodsp == 2 ) ) { |
443 |
|
444 |
#Display by Month |
445 |
$colfield .= "concat(hex(month($column)),'-',monthname($column))"; |
446 |
} |
447 |
elsif ( ( $column =~ /received/ ) and ( $rodsp == 3 ) ) { |
448 |
|
449 |
#Display by Year |
450 |
$colfield .= "Year($column)"; |
451 |
|
452 |
} |
453 |
else { |
454 |
$colfield .= $column; |
455 |
} |
437 |
} |
456 |
|
438 |
|
457 |
my $strsth2; |
439 |
# 2nd, loop cols. |
458 |
$strsth2 .= |
440 |
my $strsth2 = " |
459 |
"SELECT distinctrow $colfield FROM (aqorders, aqbasket ) |
441 |
SELECT DISTINCTROW $colfield |
460 |
LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber) |
442 |
FROM aqorders |
461 |
LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber) |
443 |
LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) |
462 |
LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) |
444 |
LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber) |
463 |
|
445 |
LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber) |
464 |
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) |
446 |
LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber) |
465 |
WHERE (aqorders.basketno=aqbasket.basketno) AND |
447 |
LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) |
466 |
$column IS NOT NULL AND $column <> '' "; |
448 |
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id) |
|
|
449 |
WHERE $column IS NOT NULL AND $column <> '' |
450 |
"; |
467 |
|
451 |
|
468 |
if (@colfilter) { |
452 |
if (@colfilter) { |
469 |
if ( $colfilter[1] ) { |
453 |
if ( $colfilter[1] ) { |
Lines 488-494
sub calculate {
Link Here
|
488 |
} |
472 |
} |
489 |
} |
473 |
} |
490 |
|
474 |
|
491 |
|
|
|
492 |
$strsth2 .= " GROUP BY $colfield"; |
475 |
$strsth2 .= " GROUP BY $colfield"; |
493 |
$strsth2 .= " ORDER BY $colfield"; |
476 |
$strsth2 .= " ORDER BY $colfield"; |
494 |
|
477 |
|
Lines 503-515
sub calculate {
Link Here
|
503 |
else { |
486 |
else { |
504 |
$sth2->execute; |
487 |
$sth2->execute; |
505 |
} |
488 |
} |
506 |
while ( my $celvalue = $sth2->fetchrow ) { |
489 |
while ( my $celvalue = $sth2->fetchrow ) { |
507 |
my %cell; |
490 |
my %cell; |
508 |
if ($celvalue) { |
491 |
if ($celvalue) { |
509 |
$cell{coltitle} = $celvalue; |
492 |
$cell{coltitle} = $celvalue; |
510 |
push @loopcol, \%cell; |
493 |
push @loopcol, \%cell; |
511 |
} |
494 |
} |
512 |
} |
495 |
} |
513 |
|
496 |
|
514 |
my $i = 0; |
497 |
my $i = 0; |
515 |
my @totalcol; |
498 |
my @totalcol; |
Lines 528-545
sub calculate {
Link Here
|
528 |
# preparing calculation |
511 |
# preparing calculation |
529 |
my $strcalc; |
512 |
my $strcalc; |
530 |
$strcalc .= "SELECT $linefield, $colfield, "; |
513 |
$strcalc .= "SELECT $linefield, $colfield, "; |
531 |
$strcalc .= "SUM( aqorders.quantity ) " if ( $process == 1 ); |
514 |
given ($process) { |
532 |
$strcalc .= "SUM( aqorders.quantity * aqorders.listprice ) " |
515 |
when (1) { $strcalc .= "COUNT(*) " } |
533 |
if ( $process == 2 ); |
516 |
when (2) { $strcalc .= "COUNT(DISTINCT(aqorders.biblionumber)) " } |
534 |
$strcalc .= "FROM (aqorders, aqbasket ) |
517 |
when ([3,4,5]) { $strcalc .= "SUM(aqorders.listprice) " } |
535 |
LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber) |
518 |
default { $strcalc .= "NULL " } |
536 |
LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber) |
519 |
} |
537 |
LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) |
520 |
$strcalc .= " |
538 |
|
521 |
FROM aqorders |
539 |
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) |
522 |
LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) |
540 |
WHERE (aqorders.basketno=aqbasket.basketno) "; |
523 |
LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber) |
541 |
|
524 |
LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber) |
542 |
@$filters[0] =~ s/\*/%/g if ( @$filters[0] ); |
525 |
LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber) |
|
|
526 |
LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) |
527 |
LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id) |
528 |
WHERE aqorders.datecancellationprinted IS NULL "; |
529 |
$strcalc .= " AND (aqorders.datereceived IS NULL OR aqorders.datereceived = '') " |
530 |
if ( $process == 4 ); |
531 |
$strcalc .= " AND aqorders.datereceived IS NOT NULL AND aqorders.datereceived <> '' " |
532 |
if ( $process == 5 ); |
533 |
@$filters[0] =~ s/\*/%/g if ( @$filters[0] ); |
543 |
$strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'" |
534 |
$strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'" |
544 |
if ( @$filters[0] ); |
535 |
if ( @$filters[0] ); |
545 |
@$filters[1] =~ s/\*/%/g if ( @$filters[1] ); |
536 |
@$filters[1] =~ s/\*/%/g if ( @$filters[1] ); |
Lines 554-573
sub calculate {
Link Here
|
554 |
@$filters[4] =~ s/\*/%/g if ( @$filters[4] ); |
545 |
@$filters[4] =~ s/\*/%/g if ( @$filters[4] ); |
555 |
$strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'" |
546 |
$strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'" |
556 |
if ( @$filters[4] ); |
547 |
if ( @$filters[4] ); |
557 |
@$filters[5] =~ s/\*/%/g if ( @$filters[5] ); |
548 |
$strcalc .= " AND items.homebranch = '" . @$filters[5] . "'" |
558 |
$strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[5] . "'" |
|
|
559 |
if ( @$filters[5] ); |
549 |
if ( @$filters[5] ); |
560 |
@$filters[6] =~ s/\*/%/g if ( @$filters[6] ); |
550 |
@$filters[6] =~ s/\*/%/g if ( @$filters[6] ); |
561 |
$strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[6] . "'" |
551 |
$strcalc .= " AND items.ccode = '" . @$filters[6] . "'" |
562 |
if ( @$filters[6] ); |
552 |
if ( @$filters[6] ); |
563 |
@$filters[7] =~ s/\*/%/g if ( @$filters[7] ); |
553 |
@$filters[7] =~ s/\*/%/g if ( @$filters[7] ); |
564 |
$strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[7] . "'" |
554 |
$strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[7] . "'" |
565 |
if ( @$filters[7] ); |
555 |
if ( @$filters[7] ); |
566 |
@$filters[8] =~ s/\*/%/g if ( @$filters[8] ); |
556 |
@$filters[8] =~ s/\*/%/g if ( @$filters[8] ); |
567 |
$strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[8] . "'" |
557 |
$strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[8] . "'" |
568 |
if ( @$filters[8] ); |
558 |
if ( @$filters[8] ); |
569 |
|
559 |
@$filters[9] =~ s/\*/%/g if ( @$filters[9] ); |
570 |
$strcalc .= " AND aqorders.datecancellationprinted is NULL "; |
560 |
$strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[9] . "'" |
|
|
561 |
if ( @$filters[9] ); |
562 |
@$filters[10] =~ s/\*/%/g if ( @$filters[10] ); |
563 |
$strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[10] . "'" |
564 |
if ( @$filters[10] ); |
571 |
|
565 |
|
572 |
$strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield"; |
566 |
$strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield"; |
573 |
my $dbcalc = $dbh->prepare($strcalc); |
567 |
my $dbcalc = $dbh->prepare($strcalc); |
Lines 575-582
sub calculate {
Link Here
|
575 |
|
569 |
|
576 |
my $emptycol; |
570 |
my $emptycol; |
577 |
while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) { |
571 |
while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) { |
578 |
next if ($row eq undef || $col eq undef); |
|
|
579 |
|
580 |
$emptycol = 1 if ( !defined($col) ); |
572 |
$emptycol = 1 if ( !defined($col) ); |
581 |
$col = "zzEMPTY" if ( !defined($col) ); |
573 |
$col = "zzEMPTY" if ( !defined($col) ); |
582 |
$row = "zzEMPTY" if ( !defined($row) ); |
574 |
$row = "zzEMPTY" if ( !defined($row) ); |
Lines 594-608
sub calculate {
Link Here
|
594 |
# and the number matches the number of columns |
586 |
# and the number matches the number of columns |
595 |
foreach my $col (@loopcol) { |
587 |
foreach my $col (@loopcol) { |
596 |
my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} }; |
588 |
my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} }; |
|
|
589 |
$value = sprintf("%.2f", $value) if($value and grep /$process/, (3,4,5)); |
597 |
push @loopcell, { value => $value }; |
590 |
push @loopcell, { value => $value }; |
598 |
} |
591 |
} |
599 |
push @looprow, |
592 |
my $r = { |
600 |
{ |
593 |
rowtitle => ( $row eq "zzEMPTY" ) ? "NULL" : $row, |
601 |
'rowtitle' => ( $row eq "zzEMPTY" ) ? "NULL" : $row, |
594 |
loopcell => \@loopcell, |
602 |
'loopcell' => \@loopcell, |
595 |
hilighted => ( $hilighted > 0 ), |
603 |
'hilighted' => ( $hilighted > 0 ), |
596 |
totalrow => $table{$row}->{totalrow} |
604 |
'totalrow' => $table{$row}->{totalrow} |
597 |
}; |
605 |
}; |
598 |
$r->{totalrow} = sprintf("%.2f", $r->{totalrow}) if($r->{totalrow} and grep /$process/, (3,4,5)); |
|
|
599 |
push @looprow, $r; |
606 |
$hilighted = -$hilighted; |
600 |
$hilighted = -$hilighted; |
607 |
} |
601 |
} |
608 |
|
602 |
|
Lines 617-633
sub calculate {
Link Here
|
617 |
: $col->{coltitle} |
611 |
: $col->{coltitle} |
618 |
}; |
612 |
}; |
619 |
} |
613 |
} |
|
|
614 |
$total = sprintf("%.2f", $total) if($total and grep /$process/, (3,4,5)); |
620 |
|
615 |
|
621 |
push @loopfooter, { 'totalcol' => $total }; |
616 |
push @loopfooter, { 'totalcol' => $total }; |
622 |
} |
617 |
} |
623 |
|
618 |
|
624 |
# the header of the table |
619 |
# the header of the table |
625 |
$globalline{loopfilter}=\@loopfilter; |
620 |
$globalline{loopfilter} = \@loopfilter; |
626 |
# the core of the table |
621 |
# the core of the table |
627 |
$globalline{looprow} = \@looprow; |
622 |
$globalline{looprow} = \@looprow; |
628 |
$globalline{loopcol} = \@loopcol; |
623 |
$globalline{loopcol} = \@loopcol; |
629 |
|
624 |
|
630 |
# # the foot (totals by borrower type) |
625 |
# # the foot (totals by borrower type) |
|
|
626 |
$grantotal = sprintf("%.2f", $grantotal) if ($grantotal and grep /$process/, (3,4,5)); |
631 |
$globalline{loopfooter} = \@loopfooter; |
627 |
$globalline{loopfooter} = \@loopfooter; |
632 |
$globalline{total} = $grantotal; |
628 |
$globalline{total} = $grantotal; |
633 |
$globalline{line} = $line; |
629 |
$globalline{line} = $line; |
634 |
- |
|
|