Lines 410-445
SELECT biblio.*, items.*, issues.*, biblioitems.itemtype, TO_DAYS($date)-TO_DAYS
Link Here
|
410 |
AND TO_DAYS($date)-TO_DAYS(date_due) BETWEEN ? and ? |
410 |
AND TO_DAYS($date)-TO_DAYS(date_due) BETWEEN ? and ? |
411 |
END_SQL |
411 |
END_SQL |
412 |
|
412 |
|
413 |
my $query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ? "; |
413 |
my $query = "SELECT * FROM overduerules WHERE delay IS NOT NULL AND typecode = ? AND branchcode = ? "; |
414 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
414 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
415 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
415 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
416 |
|
416 |
|
417 |
my $rqoverduerules = $dbh->prepare($query); |
417 |
my $rqoverduerules = $dbh->prepare($query); |
418 |
$rqoverduerules->execute($branchcode, @myborcat, @myborcatout); |
418 |
$rqoverduerules->execute(1, $branchcode, @myborcat, @myborcatout); |
|
|
419 |
|
420 |
my $trqoverduerules = $dbh->prepare($query); |
421 |
$trqoverduerules->execute(2, $branchcode, @myborcat, @myborcatout); |
422 |
|
423 |
my $ttrqoverduerules = $dbh->prepare($query); |
424 |
$ttrqoverduerules->execute(3, $branchcode, @myborcat, @myborcatout); |
419 |
|
425 |
|
420 |
# We get default rules is there is no rule for this branch |
426 |
# We get default rules is there is no rule for this branch |
421 |
if($rqoverduerules->rows == 0){ |
427 |
if($rqoverduerules->rows == 0){ |
422 |
$query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = '' "; |
428 |
$query = "SELECT * FROM overduerules WHERE delay IS NOT NULL AND typecode = ? AND branchcode = '' "; |
423 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
429 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
424 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
430 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
425 |
|
431 |
|
426 |
$rqoverduerules = $dbh->prepare($query); |
432 |
$rqoverduerules = $dbh->prepare($query); |
427 |
$rqoverduerules->execute(@myborcat, @myborcatout); |
433 |
$rqoverduerules->execute(1, @myborcat, @myborcatout); |
|
|
434 |
|
435 |
$trqoverduerules = $dbh->prepare($query); |
436 |
$trqoverduerules->execute(2, @myborcat, @myborcatout); |
437 |
|
438 |
$ttrqoverduerules = $dbh->prepare($query); |
439 |
$ttrqoverduerules->execute(3, @myborcat, @myborcatout); |
428 |
} |
440 |
} |
429 |
|
441 |
|
430 |
# my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' ); |
442 |
# my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' ); |
431 |
while ( my $overdue_rules = $rqoverduerules->fetchrow_hashref ) { |
443 |
my @overdue_rules; |
|
|
444 |
while ( $overdue_rules[1] = $rqoverduerules->fetchrow_hashref ) { |
445 |
$overdue_rules[2] = $trqoverduerules->fetchrow_hashref; |
446 |
$overdue_rules[3] = $ttrqoverduerules->fetchrow_hashref; |
447 |
|
432 |
PERIOD: foreach my $i ( 1 .. 3 ) { |
448 |
PERIOD: foreach my $i ( 1 .. 3 ) { |
433 |
|
449 |
|
434 |
$verbose and warn "branch '$branchcode', pass $i\n"; |
450 |
$verbose and warn "branch '$branchcode', pass $i\n"; |
435 |
my $mindays = $overdue_rules->{"delay$i"}; # the notice will be sent after mindays days (grace period) |
451 |
my $mindays = $overdue_rules[$i]->{"delay"}; # the notice will be sent after mindays days (grace period) |
436 |
my $maxdays = ( |
452 |
my $maxdays = ( |
437 |
$overdue_rules->{ "delay" . ( $i + 1 ) } |
453 |
$overdue_rules[$i+1]->{ "delay" } |
438 |
? $overdue_rules->{ "delay" . ( $i + 1 ) } - 1 |
454 |
? $overdue_rules[$i+1]->{ "delay" } - 1 |
439 |
: ($MAX) |
455 |
: ($MAX) |
440 |
); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended) |
456 |
); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended) |
441 |
|
457 |
|
442 |
if ( !$overdue_rules->{"letter$i"} ) { |
458 |
if ( !$overdue_rules[$i]->{"letter"} ) { |
443 |
$verbose and warn "No letter$i code for branch '$branchcode'"; |
459 |
$verbose and warn "No letter$i code for branch '$branchcode'"; |
444 |
next PERIOD; |
460 |
next PERIOD; |
445 |
} |
461 |
} |
Lines 461-469
END_SQL
Link Here
|
461 |
$borrower_sql .= ' AND issues.branchcode=? '; |
477 |
$borrower_sql .= ' AND issues.branchcode=? '; |
462 |
push @borrower_parameters, $branchcode; |
478 |
push @borrower_parameters, $branchcode; |
463 |
} |
479 |
} |
464 |
if ( $overdue_rules->{categorycode} ) { |
480 |
if ( $overdue_rules[$i]->{categorycode} ) { |
465 |
$borrower_sql .= ' AND borrowers.categorycode=? '; |
481 |
$borrower_sql .= ' AND borrowers.categorycode=? '; |
466 |
push @borrower_parameters, $overdue_rules->{categorycode}; |
482 |
push @borrower_parameters, $overdue_rules[$i]->{categorycode}; |
467 |
} |
483 |
} |
468 |
$borrower_sql .= ' AND categories.overduenoticerequired=1 '; |
484 |
$borrower_sql .= ' AND categories.overduenoticerequired=1 '; |
469 |
if($triggered) { |
485 |
if($triggered) { |
Lines 477-483
END_SQL
Link Here
|
477 |
# $sth gets borrower info iff at least one overdue item has triggered the overdue action. |
493 |
# $sth gets borrower info iff at least one overdue item has triggered the overdue action. |
478 |
my $sth = $dbh->prepare($borrower_sql); |
494 |
my $sth = $dbh->prepare($borrower_sql); |
479 |
$sth->execute(@borrower_parameters); |
495 |
$sth->execute(@borrower_parameters); |
480 |
$verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows"; |
496 |
$verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules[$i]->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows"; |
481 |
|
497 |
|
482 |
while ( my $data = $sth->fetchrow_hashref ) { |
498 |
while ( my $data = $sth->fetchrow_hashref ) { |
483 |
my $borrowernumber = $data->{'borrowernumber'}; |
499 |
my $borrowernumber = $data->{'borrowernumber'}; |
Lines 502-518
END_SQL
Link Here
|
502 |
} |
518 |
} |
503 |
} |
519 |
} |
504 |
|
520 |
|
505 |
my $letter = C4::Letters::getletter( 'circulation', $overdue_rules->{"letter$i"}, $branchcode ); |
521 |
my $letter = C4::Letters::getletter( 'circulation', $overdue_rules[$i]->{"letter"}, $branchcode ); |
506 |
|
522 |
|
507 |
unless ($letter) { |
523 |
unless ($letter) { |
508 |
$verbose and warn "Message '$overdue_rules->{letter$i}' content not found"; |
524 |
$verbose and warn "Message '$overdue_rules[$i]->{letter}' content not found"; |
509 |
|
525 |
|
510 |
# might as well skip while PERIOD, no other borrowers are going to work. |
526 |
# might as well skip while PERIOD, no other borrowers are going to work. |
511 |
# FIXME : Does this mean a letter must be defined in order to trigger a debar ? |
527 |
# FIXME : Does this mean a letter must be defined in order to trigger a debar ? |
512 |
next PERIOD; |
528 |
next PERIOD; |
513 |
} |
529 |
} |
514 |
|
530 |
|
515 |
if ( $overdue_rules->{"debarred$i"} ) { |
531 |
if ( $overdue_rules[$i]->{"debarred"} ) { |
516 |
|
532 |
|
517 |
#action taken is debarring |
533 |
#action taken is debarring |
518 |
C4::Members::DebarMember($borrowernumber, '9999-12-31'); |
534 |
C4::Members::DebarMember($borrowernumber, '9999-12-31'); |
Lines 541-547
END_SQL
Link Here
|
541 |
$sth2->finish; |
557 |
$sth2->finish; |
542 |
|
558 |
|
543 |
$letter = parse_letter( |
559 |
$letter = parse_letter( |
544 |
{ letter_code => $overdue_rules->{"letter$i"}, |
560 |
{ letter_code => $overdue_rules[$i]->{"letter"}, |
545 |
borrowernumber => $borrowernumber, |
561 |
borrowernumber => $borrowernumber, |
546 |
branchcode => $branchcode, |
562 |
branchcode => $branchcode, |
547 |
items => \@items, |
563 |
items => \@items, |
Lines 553-559
END_SQL
Link Here
|
553 |
} |
569 |
} |
554 |
); |
570 |
); |
555 |
unless ($letter) { |
571 |
unless ($letter) { |
556 |
$verbose and warn "Message '$overdue_rules->{letter$i}' content not found"; |
572 |
$verbose and warn "Message '$overdue_rules[$i]->{letter}' content not found"; |
557 |
|
573 |
|
558 |
# might as well skip while PERIOD, no other borrowers are going to work. |
574 |
# might as well skip while PERIOD, no other borrowers are going to work. |
559 |
# FIXME : Does this mean a letter must be defined in order to trigger a debar ? |
575 |
# FIXME : Does this mean a letter must be defined in order to trigger a debar ? |