|
Lines 311-333
sub calculate {
Link Here
|
| 311 |
if($line_attribute_type) { |
311 |
if($line_attribute_type) { |
| 312 |
$strsth = "SELECT attribute FROM borrower_attributes WHERE code = '$line_attribute_type' "; |
312 |
$strsth = "SELECT attribute FROM borrower_attributes WHERE code = '$line_attribute_type' "; |
| 313 |
} else { |
313 |
} else { |
| 314 |
$strsth = "SELECT distinctrow $linefield FROM statistics, "; |
314 |
$strsth = "SELECT distinctrow $linefield FROM statistics "; |
| 315 |
|
315 |
|
| 316 |
# get stats on items if ccode or location, otherwise borrowers. |
316 |
# get stats on items if ccode or location, otherwise borrowers. |
| 317 |
$strsth .= |
317 |
$strsth .= |
| 318 |
( $linesource eq 'items' ) |
318 |
( $linesource eq 'items' ) |
| 319 |
? " items WHERE (statistics.itemnumber=items.itemnumber) " |
319 |
? " LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " |
| 320 |
: " borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) "; |
320 |
: " LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; |
| 321 |
} |
321 |
} |
| 322 |
$strsth .= " AND $line is not null "; |
322 |
$strsth .= " WHERE $line is not null "; |
| 323 |
|
323 |
|
| 324 |
if ( $line =~ /datetime/ ) { |
324 |
if ( $line =~ /datetime/ ) { |
| 325 |
if ( $linefilter[1] and ( $linefilter[0] ) ) { |
325 |
if ( $linefilter[1] and ( $linefilter[0] ) ) { |
| 326 |
$strsth .= " AND $line between ? AND ? "; |
326 |
$strsth .= " AND $line between ? AND ? "; |
| 327 |
} elsif ( $linefilter[1] ) { |
327 |
} elsif ( $linefilter[1] ) { |
| 328 |
$strsth .= " AND $line < ? "; |
328 |
$strsth .= " AND $line <= ? "; |
| 329 |
} elsif ( $linefilter[0] ) { |
329 |
} elsif ( $linefilter[0] ) { |
| 330 |
$strsth .= " AND $line > ? "; |
330 |
$strsth .= " AND $line >= ? "; |
| 331 |
} |
331 |
} |
| 332 |
$strsth .= " AND type ='" . $type . "' " if $type; |
332 |
$strsth .= " AND type ='" . $type . "' " if $type; |
| 333 |
$strsth .= " AND dayname(datetime) ='" . $daysel . "' " if $daysel; |
333 |
$strsth .= " AND dayname(datetime) ='" . $daysel . "' " if $daysel; |
|
Lines 340-347
sub calculate {
Link Here
|
| 340 |
$debug and warn $strsth; |
340 |
$debug and warn $strsth; |
| 341 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth }; |
341 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth }; |
| 342 |
my $sth = $dbh->prepare($strsth); |
342 |
my $sth = $dbh->prepare($strsth); |
| 343 |
if ( (@linefilter) and ( $linefilter[1] ) ) { |
343 |
if ( (@linefilter) and ($linefilter[0]) and ($linefilter[1]) ) { |
| 344 |
$sth->execute( $linefilter[0], $linefilter[1] ); |
344 |
$sth->execute( $linefilter[0], $linefilter[1] . " 23:59:59" ); |
|
|
345 |
} elsif ( $linefilter[1] ) { |
| 346 |
$sth->execute( $linefilter[1] . " 23:59:59" ); |
| 345 |
} elsif ( $linefilter[0] ) { |
347 |
} elsif ( $linefilter[0] ) { |
| 346 |
$sth->execute( $linefilter[0] ); |
348 |
$sth->execute( $linefilter[0] ); |
| 347 |
} else { |
349 |
} else { |
|
Lines 397-419
sub calculate {
Link Here
|
| 397 |
if($column_attribute_type) { |
399 |
if($column_attribute_type) { |
| 398 |
$strsth2 = "SELECT attribute FROM borrower_attributes WHERE code = '$column_attribute_type' "; |
400 |
$strsth2 = "SELECT attribute FROM borrower_attributes WHERE code = '$column_attribute_type' "; |
| 399 |
} else { |
401 |
} else { |
| 400 |
$strsth2 = "SELECT distinctrow $colfield FROM statistics, "; |
402 |
$strsth2 = "SELECT distinctrow $colfield FROM statistics "; |
| 401 |
|
403 |
|
| 402 |
# get stats on items if ccode or location, otherwise borrowers. |
404 |
# get stats on items if ccode or location, otherwise borrowers. |
| 403 |
$strsth2 .= |
405 |
$strsth2 .= |
| 404 |
( $colsource eq 'items' ) |
406 |
( $colsource eq 'items' ) |
| 405 |
? "items WHERE (statistics.itemnumber=items.itemnumber) " |
407 |
? "LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " |
| 406 |
: "borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) "; |
408 |
: "LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; |
| 407 |
} |
409 |
} |
| 408 |
$strsth2 .= " AND $column IS NOT NULL "; |
410 |
$strsth2 .= " WHERE $column IS NOT NULL "; |
| 409 |
|
411 |
|
| 410 |
if ( $column =~ /datetime/ ) { |
412 |
if ( $column =~ /datetime/ ) { |
| 411 |
if ( ( $colfilter[1] ) and ( $colfilter[0] ) ) { |
413 |
if ( ( $colfilter[1] ) and ( $colfilter[0] ) ) { |
| 412 |
$strsth2 .= " AND $column BETWEEN ? AND ? "; |
414 |
$strsth2 .= " AND $column BETWEEN ? AND ? "; |
| 413 |
} elsif ( $colfilter[1] ) { |
415 |
} elsif ( $colfilter[1] ) { |
| 414 |
$strsth2 .= " AND $column < ? "; |
416 |
$strsth2 .= " AND $column <= ? "; |
| 415 |
} elsif ( $colfilter[0] ) { |
417 |
} elsif ( $colfilter[0] ) { |
| 416 |
$strsth2 .= " AND $column > ? "; |
418 |
$strsth2 .= " AND $column >= ? "; |
| 417 |
} |
419 |
} |
| 418 |
$strsth2 .= " AND type ='". $type ."' " if $type; |
420 |
$strsth2 .= " AND type ='". $type ."' " if $type; |
| 419 |
$strsth2 .= " AND dayname(datetime) ='". $daysel ."' " if $daysel; |
421 |
$strsth2 .= " AND dayname(datetime) ='". $daysel ."' " if $daysel; |
|
Lines 427-434
sub calculate {
Link Here
|
| 427 |
$debug and warn $strsth2; |
429 |
$debug and warn $strsth2; |
| 428 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth2 }; |
430 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth2 }; |
| 429 |
my $sth2 = $dbh->prepare($strsth2); |
431 |
my $sth2 = $dbh->prepare($strsth2); |
| 430 |
if ( (@colfilter) and ( $colfilter[1] ) ) { |
432 |
if ( (@colfilter) and ($colfilter[0]) and ($colfilter[1]) ) { |
| 431 |
$sth2->execute( $colfilter[0], $colfilter[1] ); |
433 |
$sth2->execute( $colfilter[0], $colfilter[1] . " 23:59:59" ); |
|
|
434 |
} elsif ( $colfilter[1] ) { |
| 435 |
$sth2->execute( $colfilter[1] . " 23:59:59" ); |
| 432 |
} elsif ( $colfilter[0] ) { |
436 |
} elsif ( $colfilter[0] ) { |
| 433 |
$sth2->execute( $colfilter[0] ); |
437 |
$sth2->execute( $colfilter[0] ); |
| 434 |
} else { |
438 |
} else { |
|
Lines 592-603
sub calculate {
Link Here
|
| 592 |
my $value = table_get(\%table, $row->{rowtitle}, $col->{coltitle}); |
596 |
my $value = table_get(\%table, $row->{rowtitle}, $col->{coltitle}); |
| 593 |
push @loopcell, { value => $value }; |
597 |
push @loopcell, { value => $value }; |
| 594 |
} |
598 |
} |
| 595 |
my $rowtitle = ( $row->{rowtitle} eq "NULL" ) ? "zzEMPTY" : $row->{rowtitle}; |
|
|
| 596 |
push @looprow, |
599 |
push @looprow, |
| 597 |
{ 'rowtitle_display' => $row->{rowtitle_display}, |
600 |
{ 'rowtitle_display' => $row->{rowtitle_display}, |
| 598 |
'rowtitle' => $rowtitle, |
601 |
'rowtitle' => $row->{rowtitle}, |
| 599 |
'loopcell' => \@loopcell, |
602 |
'loopcell' => \@loopcell, |
| 600 |
'totalrow' => table_get(\%table, $rowtitle, 'totalrow'), |
603 |
'totalrow' => table_get(\%table, $row->{rowtitle}, 'totalrow'), |
| 601 |
}; |
604 |
}; |
| 602 |
} |
605 |
} |
| 603 |
for my $col (@loopcol) { |
606 |
for my $col (@loopcol) { |
|
Lines 624-634
sub calculate {
Link Here
|
| 624 |
return [ ( \%globalline ) ]; |
627 |
return [ ( \%globalline ) ]; |
| 625 |
} |
628 |
} |
| 626 |
|
629 |
|
| 627 |
sub null_to_zzempty ($) { |
630 |
sub null_to_zzempty { |
| 628 |
my $string = shift; |
631 |
my $string = shift; |
| 629 |
defined($string) or return 'zzEMPTY'; |
632 |
|
| 630 |
($string eq "NULL") and return 'zzEMPTY'; |
633 |
if (!defined($string) or $string eq '' or uc($string) eq 'NULL') { |
| 631 |
return $string; # else return the valid value |
634 |
return 'zzEMPTY'; |
|
|
635 |
} |
| 636 |
|
| 637 |
return $string; |
| 632 |
} |
638 |
} |
| 633 |
|
639 |
|
| 634 |
sub table_set { |
640 |
sub table_set { |
| 635 |
- |
|
|