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