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