[Koha] Printing overdue letters

[Shannon Taheny]

Thanks Mark for sharing this

We also had difficulty with this process, as our clients respond better to letters rather than emails.

Regards

Shannon Taheny

Information and Resources Coordinator
Multicultural Aged Care Library
library at mac.org.au




-----Original Message-----
From: Koha [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Mark Alexander
Sent: Sunday, 5 January 2020 10:23 PM
To: koha
Subject: Re: [Koha] Printing overdue letters

Excerpts from Mark Alexander's message of 2020-01-04 20:27:31 -0500:
> I think this might work, except that I can't figure out how to get a
> patron's list of overdue books into the generated letter.  The SQL
> would need to produce a CSV file with a single row for each patron,
> containing the patron name, address, and an arbitrarily long list of
> overdue books.

A kind person replied off-list and gave me the magic I needed:
GROUP_CONCAT.  Thanks!

In case anyone is interested, here is the SQL I ended up using:

SELECT p.cardnumber,
  CONCAT(p.firstname, ' ', p.surname) as name,
  p.address,
  CONCAT(p.city, ', ', p.state, ' ', p.zipcode) as city,
  GROUP_CONCAT(CONCAT_WS(' ', REPLACE(b.title, ' /', ''),
                         CONCAT('$', i.replacementprice))
               SEPARATOR '; ') as overdues
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
INNER JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= <<Days overdue>>
GROUP BY cardnumber
_______________________________________________
Koha mailing list  http://koha-community.org
Koha at lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

________________________
This email was scanned by Bitdefender