|
Lines 30-35
use C4::Reports;
Link Here
|
| 30 |
use C4::Circulation; |
30 |
use C4::Circulation; |
| 31 |
use C4::Biblio qw/GetMarcSubfieldStructureFromKohaField/; |
31 |
use C4::Biblio qw/GetMarcSubfieldStructureFromKohaField/; |
| 32 |
|
32 |
|
|
|
33 |
use Koha::DateUtils; |
| 34 |
|
| 33 |
=head1 NAME |
35 |
=head1 NAME |
| 34 |
|
36 |
|
| 35 |
plugin that shows a stats on borrowers |
37 |
plugin that shows a stats on borrowers |
|
Lines 81-87
if ($do_it) {
Link Here
|
| 81 |
exit; |
83 |
exit; |
| 82 |
} else { |
84 |
} else { |
| 83 |
print $input->header( |
85 |
print $input->header( |
| 84 |
-type => 'application/vnd.sun.xml.calc', |
86 |
-type => 'text/csv', |
| 85 |
-encoding => 'utf-8', |
87 |
-encoding => 'utf-8', |
| 86 |
-attachment => "$basename.csv", |
88 |
-attachment => "$basename.csv", |
| 87 |
-name => "$basename.csv" |
89 |
-name => "$basename.csv" |
|
Lines 233-242
sub calculate {
Link Here
|
| 233 |
} |
235 |
} |
| 234 |
} |
236 |
} |
| 235 |
|
237 |
|
| 236 |
@$filters[13] = C4::Dates->new(@$filters[13])->output('iso') if @$filters[13]; |
238 |
@$filters[13] = dt_from_string(@$filters[13])->date() if @$filters[13]; |
| 237 |
@$filters[14] = C4::Dates->new(@$filters[14])->output('iso') if @$filters[14]; |
239 |
@$filters[14] = dt_from_string(@$filters[14])->date() if @$filters[14]; |
| 238 |
@$filters[15] = C4::Dates->new(@$filters[15])->output('iso') if @$filters[15]; |
240 |
@$filters[15] = dt_from_string(@$filters[15])->date() if @$filters[15]; |
| 239 |
@$filters[16] = C4::Dates->new(@$filters[16])->output('iso') if @$filters[16]; |
241 |
@$filters[16] = dt_from_string(@$filters[16])->date() if @$filters[16]; |
| 240 |
|
242 |
|
| 241 |
my @linefilter; |
243 |
my @linefilter; |
| 242 |
$linefilter[0] = @$filters[0] if ( $line =~ /items\.itemcallnumber/ ); |
244 |
$linefilter[0] = @$filters[0] if ( $line =~ /items\.itemcallnumber/ ); |
|
Lines 429-512
sub calculate {
Link Here
|
| 429 |
LEFT JOIN biblioitems ON ($itemstable.biblioitemnumber = biblioitems.biblioitemnumber) |
431 |
LEFT JOIN biblioitems ON ($itemstable.biblioitemnumber = biblioitems.biblioitemnumber) |
| 430 |
LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) |
432 |
LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) |
| 431 |
WHERE 1 "; |
433 |
WHERE 1 "; |
| 432 |
$strcalc .= "AND barcode $not like ? " if ($barcodefilter); |
434 |
|
|
|
435 |
my @sqlargs; |
| 436 |
|
| 437 |
if ($barcodefilter) { |
| 438 |
$strcalc .= "AND barcode $not like ? "; |
| 439 |
push @sqlargs, $barcodefilter; |
| 440 |
} |
| 433 |
|
441 |
|
| 434 |
if ( @$filters[0] ) { |
442 |
if ( @$filters[0] ) { |
|
|
443 |
$strcalc .= " AND $itemstable.itemcallnumber >= ? "; |
| 435 |
@$filters[0] =~ s/\*/%/g; |
444 |
@$filters[0] =~ s/\*/%/g; |
| 436 |
$strcalc .= " AND $itemstable.itemcallnumber >=" . $dbh->quote( @$filters[0] ); |
445 |
push @sqlargs, @$filters[0]; |
| 437 |
} |
446 |
} |
| 438 |
|
447 |
|
| 439 |
if ( @$filters[1] ) { |
448 |
if ( @$filters[1] ) { |
|
|
449 |
$strcalc .= " AND $itemstable.itemcallnumber <= ? "; |
| 440 |
@$filters[1] =~ s/\*/%/g; |
450 |
@$filters[1] =~ s/\*/%/g; |
| 441 |
$strcalc .= " AND $itemstable.itemcallnumber <=" . $dbh->quote( @$filters[1] ); |
451 |
push @sqlargs, @$filters[1]; |
| 442 |
} |
452 |
} |
| 443 |
|
453 |
|
| 444 |
if ( @$filters[2] ) { |
454 |
if ( @$filters[2] ) { |
|
|
455 |
$strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE ? "; |
| 445 |
@$filters[2] =~ s/\*/%/g; |
456 |
@$filters[2] =~ s/\*/%/g; |
| 446 |
$strcalc .= " AND " . ( C4::Context->preference('item-level_itypes') ? "$itemstable.itype" : 'biblioitems.itemtype' ) . " LIKE '" . @$filters[2] . "'"; |
457 |
push @sqlargs, @$filters[2]; |
| 447 |
} |
458 |
} |
| 448 |
|
459 |
|
| 449 |
if ( @$filters[3] ) { |
460 |
if ( @$filters[3] ) { |
|
|
461 |
$strcalc .= " AND biblioitems.publishercode LIKE ? "; |
| 450 |
@$filters[3] =~ s/\*/%/g; |
462 |
@$filters[3] =~ s/\*/%/g; |
| 451 |
@$filters[3] .= "%" unless @$filters[3] =~ /%/; |
463 |
@$filters[3] .= "%" unless @$filters[3] =~ /%/; |
| 452 |
$strcalc .= " AND biblioitems.publishercode LIKE \"" . @$filters[3] . "\""; |
464 |
push @sqlargs, @$filters[3]; |
| 453 |
} |
465 |
} |
| 454 |
if ( @$filters[4] ) { |
466 |
if ( @$filters[4] ) { |
| 455 |
@$filters[4] =~ s/\*/%/g; |
|
|
| 456 |
$strcalc .= " AND " . |
467 |
$strcalc .= " AND " . |
| 457 |
(C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate') |
468 |
(C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate') |
| 458 |
. ">" . @$filters[4]; |
469 |
. "> ? "; |
|
|
470 |
@$filters[4] =~ s/\*/%/g; |
| 471 |
push @sqlargs, @$filters[4]; |
| 459 |
} |
472 |
} |
| 460 |
if ( @$filters[5] ) { |
473 |
if ( @$filters[5] ) { |
| 461 |
@$filters[5] =~ s/\*/%/g; |
474 |
@$filters[5] =~ s/\*/%/g; |
| 462 |
$strcalc .= " AND " . |
475 |
$strcalc .= " AND " . |
| 463 |
(C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate') |
476 |
(C4::Context->preference('marcflavour') eq 'UNIMARC' ? 'publicationyear' : 'copyrightdate') |
| 464 |
. "<" . @$filters[5]; |
477 |
. "< ? "; |
|
|
478 |
push @sqlargs, @$filters[5]; |
| 465 |
} |
479 |
} |
| 466 |
if ( @$filters[6] ) { |
480 |
if ( @$filters[6] ) { |
|
|
481 |
$strcalc .= " AND $itemstable.homebranch LIKE ? "; |
| 467 |
@$filters[6] =~ s/\*/%/g; |
482 |
@$filters[6] =~ s/\*/%/g; |
| 468 |
$strcalc .= " AND $itemstable.homebranch LIKE '" . @$filters[6] . "'"; |
483 |
push @sqlargs, @$filters[6]; |
| 469 |
} |
484 |
} |
| 470 |
if ( @$filters[7] ) { |
485 |
if ( @$filters[7] ) { |
|
|
486 |
$strcalc .= " AND $itemstable.location LIKE ? "; |
| 471 |
@$filters[7] =~ s/\*/%/g; |
487 |
@$filters[7] =~ s/\*/%/g; |
| 472 |
$strcalc .= " AND $itemstable.location LIKE '" . @$filters[7] . "'"; |
488 |
push @sqlargs, @$filters[7]; |
| 473 |
} |
489 |
} |
| 474 |
if ( @$filters[8] ) { |
490 |
if ( @$filters[8] ) { |
|
|
491 |
$strcalc .= " AND $itemstable.ccode LIKE ? "; |
| 475 |
@$filters[8] =~ s/\*/%/g; |
492 |
@$filters[8] =~ s/\*/%/g; |
| 476 |
$strcalc .= " AND $itemstable.ccode LIKE '" . @$filters[8] . "'"; |
493 |
push @sqlargs, @$filters[8]; |
| 477 |
} |
494 |
} |
| 478 |
if ( defined @$filters[9] and @$filters[9] ne '' ) { |
495 |
if ( defined @$filters[9] and @$filters[9] ne '' ) { |
|
|
496 |
$strcalc .= " AND $itemstable.notforloan LIKE ? "; |
| 479 |
@$filters[9] =~ s/\*/%/g; |
497 |
@$filters[9] =~ s/\*/%/g; |
| 480 |
$strcalc .= " AND $itemstable.notforloan LIKE '" . @$filters[9] . "'"; |
498 |
push @sqlargs, @$filters[9]; |
| 481 |
} |
499 |
} |
| 482 |
if ( defined @$filters[10] and @$filters[10] ne '' ) { |
500 |
if ( defined @$filters[10] and @$filters[10] ne '' ) { |
|
|
501 |
$strcalc .= " AND $itemstable.materials LIKE ? "; |
| 483 |
@$filters[10] =~ s/\*/%/g; |
502 |
@$filters[10] =~ s/\*/%/g; |
| 484 |
$strcalc .= " AND $itemstable.materials LIKE '" . @$filters[10] . "'"; |
503 |
push @sqlargs, @$filters[10]; |
| 485 |
} |
504 |
} |
| 486 |
if ( @$filters[13] ) { |
505 |
if ( @$filters[13] ) { |
|
|
506 |
$strcalc .= " AND $itemstable.dateaccessioned >= ? "; |
| 487 |
@$filters[13] =~ s/\*/%/g; |
507 |
@$filters[13] =~ s/\*/%/g; |
| 488 |
$strcalc .= " AND $itemstable.dateaccessioned >= '@$filters[13]' "; |
508 |
push @sqlargs, @$filters[13]; |
| 489 |
} |
509 |
} |
| 490 |
if ( @$filters[14] ) { |
510 |
if ( @$filters[14] ) { |
|
|
511 |
$strcalc .= " AND $itemstable.dateaccessioned <= ? "; |
| 491 |
@$filters[14] =~ s/\*/%/g; |
512 |
@$filters[14] =~ s/\*/%/g; |
| 492 |
$strcalc .= " AND $itemstable.dateaccessioned <= '@$filters[14]' "; |
513 |
push @sqlargs, @$filters[14]; |
| 493 |
} |
514 |
} |
| 494 |
if ( $cellvalue eq 'deleteditems' and @$filters[15] ) { |
515 |
if ( $cellvalue eq 'deleteditems' and @$filters[15] ) { |
|
|
516 |
$strcalc .= " AND DATE(deleteditems.timestamp) >= ? "; |
| 495 |
@$filters[15] =~ s/\*/%/g; |
517 |
@$filters[15] =~ s/\*/%/g; |
| 496 |
$strcalc .= " AND DATE(deleteditems.timestamp) >= '@$filters[15]' "; |
518 |
push @sqlargs, @$filters[15]; |
| 497 |
} |
519 |
} |
| 498 |
if ( $cellvalue eq 'deleteditems' and @$filters[16] ) { |
520 |
if ( $cellvalue eq 'deleteditems' and @$filters[16] ) { |
| 499 |
@$filters[16] =~ s/\*/%/g; |
521 |
@$filters[16] =~ s/\*/%/g; |
| 500 |
$strcalc .= " AND DATE(deleteditems.timestamp) <= '@$filters[16]' "; |
522 |
$strcalc .= " AND DATE(deleteditems.timestamp) <= ?"; |
|
|
523 |
push @sqlargs, @$filters[16]; |
| 501 |
} |
524 |
} |
| 502 |
$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield"; |
525 |
$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield"; |
| 503 |
$debug and warn "SQL: $strcalc"; |
526 |
$debug and warn "SQL: $strcalc"; |
| 504 |
my $dbcalc = $dbh->prepare($strcalc); |
527 |
my $dbcalc = $dbh->prepare($strcalc); |
| 505 |
if ($barcodefilter) { |
528 |
$dbcalc->execute(@sqlargs); |
| 506 |
$dbcalc->execute($barcodefilter); |
|
|
| 507 |
} else { |
| 508 |
$dbcalc->execute(); |
| 509 |
} |
| 510 |
|
529 |
|
| 511 |
while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) { |
530 |
while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) { |
| 512 |
|
531 |
|
| 513 |
- |
|
|