Lines 317-339
sub calculate {
Link Here
|
317 |
if($line_attribute_type) { |
317 |
if($line_attribute_type) { |
318 |
$strsth = "SELECT attribute FROM borrower_attributes WHERE code = '$line_attribute_type' "; |
318 |
$strsth = "SELECT attribute FROM borrower_attributes WHERE code = '$line_attribute_type' "; |
319 |
} else { |
319 |
} else { |
320 |
$strsth = "SELECT distinctrow $linefield FROM statistics, "; |
320 |
$strsth = "SELECT distinctrow $linefield FROM statistics "; |
321 |
|
321 |
|
322 |
# get stats on items if ccode or location, otherwise borrowers. |
322 |
# get stats on items if ccode or location, otherwise borrowers. |
323 |
$strsth .= |
323 |
$strsth .= |
324 |
( $linesource eq 'items' ) |
324 |
( $linesource eq 'items' ) |
325 |
? " items WHERE (statistics.itemnumber=items.itemnumber) " |
325 |
? " LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " |
326 |
: " borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) "; |
326 |
: " LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; |
327 |
} |
327 |
} |
328 |
$strsth .= " AND $line is not null "; |
328 |
$strsth .= " WHERE $line is not null "; |
329 |
|
329 |
|
330 |
if ( $line =~ /datetime/ ) { |
330 |
if ( $line =~ /datetime/ ) { |
331 |
if ( $linefilter[1] and ( $linefilter[0] ) ) { |
331 |
if ( $linefilter[1] and ( $linefilter[0] ) ) { |
332 |
$strsth .= " AND $line between ? AND ? "; |
332 |
$strsth .= " AND $line between ? AND ? "; |
333 |
} elsif ( $linefilter[1] ) { |
333 |
} elsif ( $linefilter[1] ) { |
334 |
$strsth .= " AND $line < ? "; |
334 |
$strsth .= " AND $line <= ? "; |
335 |
} elsif ( $linefilter[0] ) { |
335 |
} elsif ( $linefilter[0] ) { |
336 |
$strsth .= " AND $line > ? "; |
336 |
$strsth .= " AND $line >= ? "; |
337 |
} |
337 |
} |
338 |
$strsth .= " AND type ='" . $type . "' " if $type; |
338 |
$strsth .= " AND type ='" . $type . "' " if $type; |
339 |
$strsth .= " AND dayname(datetime) ='" . $daysel . "' " if $daysel; |
339 |
$strsth .= " AND dayname(datetime) ='" . $daysel . "' " if $daysel; |
Lines 346-353
sub calculate {
Link Here
|
346 |
$debug and warn $strsth; |
346 |
$debug and warn $strsth; |
347 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth }; |
347 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth }; |
348 |
my $sth = $dbh->prepare($strsth); |
348 |
my $sth = $dbh->prepare($strsth); |
349 |
if ( (@linefilter) and ( $linefilter[1] ) ) { |
349 |
if ( (@linefilter) and ($linefilter[0]) and ($linefilter[1]) ) { |
350 |
$sth->execute( $linefilter[0], $linefilter[1] ); |
350 |
$sth->execute( $linefilter[0], $linefilter[1] . " 23:59:59" ); |
|
|
351 |
} elsif ( $linefilter[1] ) { |
352 |
$sth->execute( $linefilter[1] . " 23:59:59" ); |
351 |
} elsif ( $linefilter[0] ) { |
353 |
} elsif ( $linefilter[0] ) { |
352 |
$sth->execute( $linefilter[0] ); |
354 |
$sth->execute( $linefilter[0] ); |
353 |
} else { |
355 |
} else { |
Lines 403-425
sub calculate {
Link Here
|
403 |
if($column_attribute_type) { |
405 |
if($column_attribute_type) { |
404 |
$strsth2 = "SELECT attribute FROM borrower_attributes WHERE code = '$column_attribute_type' "; |
406 |
$strsth2 = "SELECT attribute FROM borrower_attributes WHERE code = '$column_attribute_type' "; |
405 |
} else { |
407 |
} else { |
406 |
$strsth2 = "SELECT distinctrow $colfield FROM statistics, "; |
408 |
$strsth2 = "SELECT distinctrow $colfield FROM statistics "; |
407 |
|
409 |
|
408 |
# get stats on items if ccode or location, otherwise borrowers. |
410 |
# get stats on items if ccode or location, otherwise borrowers. |
409 |
$strsth2 .= |
411 |
$strsth2 .= |
410 |
( $colsource eq 'items' ) |
412 |
( $colsource eq 'items' ) |
411 |
? "items WHERE (statistics.itemnumber=items.itemnumber) " |
413 |
? "LEFT JOIN items ON (statistics.itemnumber = items.itemnumber) " |
412 |
: "borrowers WHERE (statistics.borrowernumber=borrowers.borrowernumber) "; |
414 |
: "LEFT JOIN borrowers ON (statistics.borrowernumber = borrowers.borrowernumber) "; |
413 |
} |
415 |
} |
414 |
$strsth2 .= " AND $column IS NOT NULL "; |
416 |
$strsth2 .= " WHERE $column IS NOT NULL "; |
415 |
|
417 |
|
416 |
if ( $column =~ /datetime/ ) { |
418 |
if ( $column =~ /datetime/ ) { |
417 |
if ( ( $colfilter[1] ) and ( $colfilter[0] ) ) { |
419 |
if ( ( $colfilter[1] ) and ( $colfilter[0] ) ) { |
418 |
$strsth2 .= " AND $column BETWEEN ? AND ? "; |
420 |
$strsth2 .= " AND $column BETWEEN ? AND ? "; |
419 |
} elsif ( $colfilter[1] ) { |
421 |
} elsif ( $colfilter[1] ) { |
420 |
$strsth2 .= " AND $column < ? "; |
422 |
$strsth2 .= " AND $column <= ? "; |
421 |
} elsif ( $colfilter[0] ) { |
423 |
} elsif ( $colfilter[0] ) { |
422 |
$strsth2 .= " AND $column > ? "; |
424 |
$strsth2 .= " AND $column >= ? "; |
423 |
} |
425 |
} |
424 |
$strsth2 .= " AND type ='". $type ."' " if $type; |
426 |
$strsth2 .= " AND type ='". $type ."' " if $type; |
425 |
$strsth2 .= " AND dayname(datetime) ='". $daysel ."' " if $daysel; |
427 |
$strsth2 .= " AND dayname(datetime) ='". $daysel ."' " if $daysel; |
Lines 433-440
sub calculate {
Link Here
|
433 |
$debug and warn $strsth2; |
435 |
$debug and warn $strsth2; |
434 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth2 }; |
436 |
push @loopfilter, { crit => 'SQL =', sql => 1, filter => $strsth2 }; |
435 |
my $sth2 = $dbh->prepare($strsth2); |
437 |
my $sth2 = $dbh->prepare($strsth2); |
436 |
if ( (@colfilter) and ( $colfilter[1] ) ) { |
438 |
if ( (@colfilter) and ($colfilter[0]) and ($colfilter[1]) ) { |
437 |
$sth2->execute( $colfilter[0], $colfilter[1] ); |
439 |
$sth2->execute( $colfilter[0], $colfilter[1] . " 23:59:59" ); |
|
|
440 |
} elsif ( $colfilter[1] ) { |
441 |
$sth2->execute( $colfilter[1] . " 23:59:59" ); |
438 |
} elsif ( $colfilter[0] ) { |
442 |
} elsif ( $colfilter[0] ) { |
439 |
$sth2->execute( $colfilter[0] ); |
443 |
$sth2->execute( $colfilter[0] ); |
440 |
} else { |
444 |
} else { |
Lines 598-609
sub calculate {
Link Here
|
598 |
my $value = table_get(\%table, $row->{rowtitle}, $col->{coltitle}); |
602 |
my $value = table_get(\%table, $row->{rowtitle}, $col->{coltitle}); |
599 |
push @loopcell, { value => $value }; |
603 |
push @loopcell, { value => $value }; |
600 |
} |
604 |
} |
601 |
my $rowtitle = ( $row->{rowtitle} eq "NULL" ) ? "zzEMPTY" : $row->{rowtitle}; |
|
|
602 |
push @looprow, |
605 |
push @looprow, |
603 |
{ 'rowtitle_display' => $row->{rowtitle_display}, |
606 |
{ 'rowtitle_display' => $row->{rowtitle_display}, |
604 |
'rowtitle' => $rowtitle, |
607 |
'rowtitle' => $row->{rowtitle}, |
605 |
'loopcell' => \@loopcell, |
608 |
'loopcell' => \@loopcell, |
606 |
'totalrow' => table_get(\%table, $rowtitle, 'totalrow'), |
609 |
'totalrow' => table_get(\%table, $row->{rowtitle}, 'totalrow'), |
607 |
}; |
610 |
}; |
608 |
} |
611 |
} |
609 |
for my $col (@loopcol) { |
612 |
for my $col (@loopcol) { |
Lines 630-640
sub calculate {
Link Here
|
630 |
return [ ( \%globalline ) ]; |
633 |
return [ ( \%globalline ) ]; |
631 |
} |
634 |
} |
632 |
|
635 |
|
633 |
sub null_to_zzempty ($) { |
636 |
sub null_to_zzempty { |
634 |
my $string = shift; |
637 |
my $string = shift; |
635 |
defined($string) or return 'zzEMPTY'; |
638 |
|
636 |
($string eq "NULL") and return 'zzEMPTY'; |
639 |
if (!defined($string) or $string eq '' or uc($string) eq 'NULL') { |
637 |
return $string; # else return the valid value |
640 |
return 'zzEMPTY'; |
|
|
641 |
} |
642 |
|
643 |
return $string; |
638 |
} |
644 |
} |
639 |
|
645 |
|
640 |
sub table_set { |
646 |
sub table_set { |
641 |
- |
|
|