Printing overdue letters
My librarian has asked me to make Koha do something that doesn't sound easy to me. She'd like to be able to generate letters to each patron having overdues more than N days (where N is a variable). The letter would be then be printed, stuffed into an envelope, and mailed with the postal service. I know that sounds old-fashioned, but she seems to really want to do this instead of using Koha's ability to email notices and messages. I can understand this, knowing that in this part of rural New England, many of our patrons are elderly and either don't have email, or have it but don't want to give us their email addresses. I am not sure I can do this in a simple SQL report, which generates a table, not a form letter. So my next idea was to write a report that outputs the needed information (patron name, address, list of overdue books and their replacement price). Then I'd export the results of the report to a CSV file. Then I'd do a mail merge in Libre Office Writer (or Word, which the librarian uses) using the data in the CSV file. The generated letter might look like this: To: PATRON_ADDRESS Dear PATRON_NAME, You have the following overdue books: BOOK1_TITLE, BOOK1_PRICE BOOK2_TITLE, BOOK1_PRICE ... Thanks, Your library 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. I am not sure that is possible, but then, my SQL skills are not so great. Any help with this would be appreciated greatly.
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
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@mac.org.au -----Original Message----- From: Koha [mailto:koha-bounces@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha ________________________ This email was scanned by Bitdefender
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@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@mac.org.au
-----Original Message----- From: Koha [mailto:koha-bounces@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Mark Alexander, I am not sure of using Koha for generating printed letters, but we developed our own method for handling overdue issues. In our college environment, even though emails are sent as a part of overdue notifications, only few students will have the habit of checking emails and respond accordingly. But some portion of students won't check their emails or submit non-working email ids, and will not respond to the library. To overcome this problem, I have complied separate Library Dues Management System using PHP and MySQL. This system can be accessed within the campus by all the other departments, namely, Library, Respective Departments, Admission Section, Head of the Institution, Head of the Department, with only VIEW Options (No data can be modified) and can be accessed via Login and Password. This helps respective departments to follow up with over due issue at their convenience. Overdue list is prepared at the end of each semester with Koha Reporting , and same data is fed into this Library Dues Management System. (Because, administration department keeps asking details frequently, and koha library system access can not be shared, to balance this, I set up separate application) This system has printing letter option, which uses a pre-filled template (body of the letter), and inserts respective barcode, title, issue data, due date, etc. Once the letter is generated with auto -filling, it will be printed and only signature is collected by the Librarian and transferred to the postal dispatch section of the College. Two copies will be printed each time, one copy is kept as proof in the Library. Screenshot: https://ibb.co/y46f8rX https://ibb.co/FnM3LwV (the content marked in red color is auto filled data from php script) This idea may help you in developing your own system to get this work done. -- *Satish MV* Librarian - Govt. Engineering College Opp. Dairy Circle, B.M.Road, Hassan 573 201 Karnataka, INDIA
Excerpts from SATISH's message of 2020-01-06 17:34:26 +0530:
To overcome this problem, I have complied separate Library Dues Management System using PHP and MySQL.
Interesting. I was thinking of doing something along these lines using a Koha plugin. I find plugins really difficult to debug (based on my experience with writing a couple of simple ones), but it might be a little easier for the staff at our little library to deal with.
participants (4)
-
Mark Alexander -
Pattabhi RK -
SATISH -
Shannon Taheny