|
Lines 425-431
END_SQL
Link Here
|
| 425 |
# <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode> |
425 |
# <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode> |
| 426 |
|
426 |
|
| 427 |
my $borrower_sql = <<'END_SQL'; |
427 |
my $borrower_sql = <<'END_SQL'; |
| 428 |
SELECT COUNT(*), issues.borrowernumber, firstname, surname, address, address2, city, zipcode, country, email, MIN(date_due) as longest_issue |
428 |
SELECT distinct(issues.borrowernumber), firstname, surname, address, address2, city, zipcode, country, email, date_due |
| 429 |
FROM issues,borrowers,categories |
429 |
FROM issues,borrowers,categories |
| 430 |
WHERE issues.borrowernumber=borrowers.borrowernumber |
430 |
WHERE issues.borrowernumber=borrowers.borrowernumber |
| 431 |
AND borrowers.categorycode=categories.categorycode |
431 |
AND borrowers.categorycode=categories.categorycode |
|
Lines 439-451
END_SQL
Link Here
|
| 439 |
$borrower_sql .= ' AND borrowers.categorycode=? '; |
439 |
$borrower_sql .= ' AND borrowers.categorycode=? '; |
| 440 |
push @borrower_parameters, $overdue_rules->{categorycode}; |
440 |
push @borrower_parameters, $overdue_rules->{categorycode}; |
| 441 |
} |
441 |
} |
| 442 |
$borrower_sql .= ' AND categories.overduenoticerequired=1 |
442 |
$borrower_sql .= ' AND categories.overduenoticerequired=1 '; |
| 443 |
GROUP BY issues.borrowernumber '; |
|
|
| 444 |
if($triggered) { |
443 |
if($triggered) { |
| 445 |
$borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) = ?'; |
444 |
$borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(date_due) = ?'; |
| 446 |
push @borrower_parameters, $mindays; |
445 |
push @borrower_parameters, $mindays; |
| 447 |
} else { |
446 |
} else { |
| 448 |
$borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN ? and ? ' ; |
447 |
$borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN ? and ? ' ; |
| 449 |
push @borrower_parameters, $mindays, $maxdays; |
448 |
push @borrower_parameters, $mindays, $maxdays; |
| 450 |
} |
449 |
} |
| 451 |
|
450 |
|
|
Lines 454-464
END_SQL
Link Here
|
| 454 |
$sth->execute(@borrower_parameters); |
453 |
$sth->execute(@borrower_parameters); |
| 455 |
$verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows"; |
454 |
$verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows"; |
| 456 |
|
455 |
|
| 457 |
while ( my ($itemcount, $borrowernumber, $firstname, $lastname, |
456 |
while ( my ( $borrowernumber, $firstname, $lastname, |
| 458 |
$address1, $address2, $city, $postcode, $country, $email, |
457 |
$address1, $address2, $city, $postcode, $country, $email, |
| 459 |
$longest_issue ) = $sth->fetchrow ) |
458 |
$date_due ) = $sth->fetchrow ) |
| 460 |
{ |
459 |
{ |
| 461 |
$verbose and warn "borrower $firstname, $lastname ($borrowernumber) has $itemcount items triggering level $i."; |
460 |
$verbose and warn "borrower $firstname, $lastname ($borrowernumber) has items triggering level $i."; |
| 462 |
|
461 |
|
| 463 |
my $letter = C4::Letters::getletter( 'circulation', $overdue_rules->{"letter$i"} ); |
462 |
my $letter = C4::Letters::getletter( 'circulation', $overdue_rules->{"letter$i"} ); |
| 464 |
|
463 |
|
| 465 |
- |
|
|