[Koha] Printing overdue letters

[Pattabhi RK]

Hi Shannon and Mark,


  Our clients had the same requirement of generating reminder letters and
to be sent to the members. We had modified the guided_reports.pl program,
such that it has added a button which upon clicked will generate a letter
in PDF format. Rather than writing a separate PHP solution, modifying the
guided_reports.pl will be a easier solution.
In India most of the government public libraries use postal mail as most
members still prefer snail mail along with SMS reminders.

Best regards,
Pattabhi RK



On Fri, Jan 24, 2020 at 1:10 PM Shannon Taheny <library at mac.org.au> wrote:

> 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
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>