Lines 385-420
SELECT biblio.*, items.*, issues.*, biblioitems.itemtype, TO_DAYS(NOW())-TO_DAYS
Link Here
|
385 |
AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN ? and ? |
385 |
AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN ? and ? |
386 |
END_SQL |
386 |
END_SQL |
387 |
|
387 |
|
388 |
my $query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ? "; |
388 |
my $query = "SELECT * FROM overduerules WHERE delay IS NOT NULL AND typecode = ? AND branchcode = ? "; |
389 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
389 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
390 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
390 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
391 |
|
391 |
|
392 |
my $rqoverduerules = $dbh->prepare($query); |
392 |
my $rqoverduerules = $dbh->prepare($query); |
393 |
$rqoverduerules->execute($branchcode, @myborcat, @myborcatout); |
393 |
$rqoverduerules->execute(1, $branchcode, @myborcat, @myborcatout); |
|
|
394 |
|
395 |
my $trqoverduerules = $dbh->prepare($query); |
396 |
$trqoverduerules->execute(2, $branchcode, @myborcat, @myborcatout); |
397 |
|
398 |
my $ttrqoverduerules = $dbh->prepare($query); |
399 |
$ttrqoverduerules->execute(3, $branchcode, @myborcat, @myborcatout); |
394 |
|
400 |
|
395 |
# We get default rules is there is no rule for this branch |
401 |
# We get default rules is there is no rule for this branch |
396 |
if($rqoverduerules->rows == 0){ |
402 |
if($rqoverduerules->rows == 0){ |
397 |
$query = "SELECT * FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = '' "; |
403 |
$query = "SELECT * FROM overduerules WHERE delay IS NOT NULL AND typecode = ? AND branchcode = '' "; |
398 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
404 |
$query .= " AND categorycode IN (".join( ',' , ('?') x @myborcat ).") " if (@myborcat); |
399 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
405 |
$query .= " AND categorycode NOT IN (".join( ',' , ('?') x @myborcatout ).") " if (@myborcatout); |
400 |
|
406 |
|
401 |
$rqoverduerules = $dbh->prepare($query); |
407 |
$rqoverduerules = $dbh->prepare($query); |
402 |
$rqoverduerules->execute(@myborcat, @myborcatout); |
408 |
$rqoverduerules->execute(1, @myborcat, @myborcatout); |
|
|
409 |
|
410 |
$trqoverduerules = $dbh->prepare($query); |
411 |
$trqoverduerules->execute(2, @myborcat, @myborcatout); |
412 |
|
413 |
$ttrqoverduerules = $dbh->prepare($query); |
414 |
$ttrqoverduerules->execute(3, @myborcat, @myborcatout); |
403 |
} |
415 |
} |
404 |
|
416 |
|
405 |
# my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' ); |
417 |
# my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' ); |
406 |
while ( my $overdue_rules = $rqoverduerules->fetchrow_hashref ) { |
418 |
while ( my $overdue_rules[1] = $rqoverduerules->fetchrow_hashref ) { |
|
|
419 |
$overdue_rules[2] = $trqoverduerules->fetchrow_hashref; |
420 |
$overdue_rules[3] = $ttrqoverduerules->fetchrow_hashref; |
421 |
|
407 |
PERIOD: foreach my $i ( 1 .. 3 ) { |
422 |
PERIOD: foreach my $i ( 1 .. 3 ) { |
408 |
|
423 |
|
409 |
$verbose and warn "branch '$branchcode', pass $i\n"; |
424 |
$verbose and warn "branch '$branchcode', pass $i\n"; |
410 |
my $mindays = $overdue_rules->{"delay$i"}; # the notice will be sent after mindays days (grace period) |
425 |
my $mindays = $overdue_rules[$i]->{"delay"}; # the notice will be sent after mindays days (grace period) |
411 |
my $maxdays = ( |
426 |
my $maxdays = ( |
412 |
$overdue_rules->{ "delay" . ( $i + 1 ) } |
427 |
$overdue_rules[$i+1]->{ "delay" } |
413 |
? $overdue_rules->{ "delay" . ( $i + 1 ) } - 1 |
428 |
? $overdue_rules[$i+1]->{ "delay" } - 1 |
414 |
: ($MAX) |
429 |
: ($MAX) |
415 |
); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended) |
430 |
); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended) |
416 |
|
431 |
|
417 |
if ( !$overdue_rules->{"letter$i"} ) { |
432 |
if ( !$overdue_rules[$i]->{"letter"} ) { |
418 |
$verbose and warn "No letter$i code for branch '$branchcode'"; |
433 |
$verbose and warn "No letter$i code for branch '$branchcode'"; |
419 |
next PERIOD; |
434 |
next PERIOD; |
420 |
} |
435 |
} |
Lines 438-444
END_SQL
Link Here
|
438 |
} |
453 |
} |
439 |
if ( $overdue_rules->{categorycode} ) { |
454 |
if ( $overdue_rules->{categorycode} ) { |
440 |
$borrower_sql .= ' AND borrowers.categorycode=? '; |
455 |
$borrower_sql .= ' AND borrowers.categorycode=? '; |
441 |
push @borrower_parameters, $overdue_rules->{categorycode}; |
456 |
push @borrower_parameters, $overdue_rules[$i]->{categorycode}; |
442 |
} |
457 |
} |
443 |
$borrower_sql .= ' AND categories.overduenoticerequired=1 '; |
458 |
$borrower_sql .= ' AND categories.overduenoticerequired=1 '; |
444 |
if($triggered) { |
459 |
if($triggered) { |
Lines 452-458
END_SQL
Link Here
|
452 |
# $sth gets borrower info iff at least one overdue item has triggered the overdue action. |
467 |
# $sth gets borrower info iff at least one overdue item has triggered the overdue action. |
453 |
my $sth = $dbh->prepare($borrower_sql); |
468 |
my $sth = $dbh->prepare($borrower_sql); |
454 |
$sth->execute(@borrower_parameters); |
469 |
$sth->execute(@borrower_parameters); |
455 |
$verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows"; |
470 |
$verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules[$i]->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows"; |
456 |
|
471 |
|
457 |
while ( my ( $borrowernumber, $firstname, $lastname, |
472 |
while ( my ( $borrowernumber, $firstname, $lastname, |
458 |
$address1, $address2, $city, $postcode, $country, $email |
473 |
$address1, $address2, $city, $postcode, $country, $email |
Lines 460-466
END_SQL
Link Here
|
460 |
{ |
475 |
{ |
461 |
$verbose and warn "borrower $firstname, $lastname ($borrowernumber) has items triggering level $i."; |
476 |
$verbose and warn "borrower $firstname, $lastname ($borrowernumber) has items triggering level $i."; |
462 |
|
477 |
|
463 |
if ( $overdue_rules->{"debarred$i"} ) { |
478 |
if ( $overdue_rules[$i]->{"debarred"} ) { |
464 |
|
479 |
|
465 |
#action taken is debarring |
480 |
#action taken is debarring |
466 |
C4::Members::DebarMember($borrowernumber, '9999-12-31'); |
481 |
C4::Members::DebarMember($borrowernumber, '9999-12-31'); |
Lines 489-495
END_SQL
Link Here
|
489 |
$sth2->finish; |
504 |
$sth2->finish; |
490 |
|
505 |
|
491 |
my $letter = parse_letter( |
506 |
my $letter = parse_letter( |
492 |
{ letter_code => $overdue_rules->{"letter$i"}, |
507 |
{ letter_code => $overdue_rules[$i]->{"letter"}, |
493 |
borrowernumber => $borrowernumber, |
508 |
borrowernumber => $borrowernumber, |
494 |
branchcode => $branchcode, |
509 |
branchcode => $branchcode, |
495 |
items => \@items, |
510 |
items => \@items, |