Lisa, It sounds like you're getting a good response for partners, but I thought I'd send along the report we use for overdue notices in case it helps you in the interim. It reflects the quirks of our little school library (we sort patrons by class/grade; we only list titles on overdue reports), but the basic setup has been working for us. It concatenates overdue materials and lost materials into two lists; in each, the title are separated with a ] (a character that doesn't otherwise appear in our records). We do use a mail merge, but our assistant can run a quick find-and-replace to replace the ] with a line break and create a nice list for each patron. The SQL query for the report is below; let me know if you'd like to see the mail merge template we use. Our report is set to run for classes (cities), grades (zipcodes), and/or campuses (categorycode), but you could easily use it by cardnumber or another field, or to pull patrons' full addresses for a mailing. I hope it can help with this problem while the feature is being developed! --Katelyn. SELECT p.surname, p.firstname, p.zipcode, p.city, overdue.title, fines.finelist FROM borrowers p LEFT JOIN (SELECT oc.borrowernumber, GROUP_CONCAT(ob.title, ']') AS title FROM issues oc LEFT JOIN items oi USING (itemnumber) LEFT JOIN biblio ob USING (biblionumber) WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > 0 AND oi.homebranch = 'CCPCS' GROUP BY borrowernumber) overdue USING (borrowernumber) LEFT JOIN (SELECT borrowernumber, GROUP_CONCAT(single_fine, "]") AS finelist FROM (SELECT borrowernumber, (SELECT CONCAT(title, ': $', FORMAT(sum(amountoutstanding),2)) FROM accountlines suma WHERE suma.itemnumber=fa.itemnumber AND suma.borrowernumber=fa.borrowernumber) AS single_fine FROM accountlines fa LEFT JOIN items fi USING (itemnumber) LEFT JOIN biblio fb USING (biblionumber) WHERE (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = fa.borrowernumber AND a2.itemnumber = fa.itemnumber) > '0.00' GROUP BY itemnumber ORDER BY borrowernumber) faa GROUP BY borrowernumber) fines USING (borrowernumber) WHERE p.city LIKE <<Class/HP/Advisory (% if all)>> AND p.zipcode LIKE <<Grade (% if all)>> AND p.categorycode LIKE <<Level (PK, 12, ES, MS, HS, GONE--must choose one!)>> AND ((overdue.title IS NOT NULL) OR (fines.finelist IS NOT NULL)) ORDER BY p.city, p.zipcode, p.surname Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 kbrowne@ccpcs.org http://www.ccpcs.org/library/ On Tue, Mar 25, 2014 at 12:10 PM, Lisa Gugliotti <lisa@hchlibrary.org>wrote:
Hi Koha Community,
We are a small public library that is very interested in having the ability to print out overdue notices on demand for patrons who don't have email, or who don't want to share their email accounts with us. We'd like them in letter format so we can snail mail them out.
We've tried Mail Merge from a Koha report, but it's difficult and tedious to get patrons with more than one item overdue to work correctly on a single bill.
Would any other entities be interested in funding a development that would allow Koha to print individual overdue bills on demand?
Thank you,
Lisa Gugliotti Reference Librarian The Henry Carter Hull Library 10 Killingworth Turnpike Clinton, CT 06413 860-669-2342 lisa@hchlibrary.org www.hchlibrary.org
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha