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 |
- |
|
|