[Koha] Printing overdue letters

Mark Alexander marka at pobox.com
Mon Jan 6 00:53:17 NZDT 2020


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


More information about the Koha mailing list