[Koha] Development for 'On Demand' Printouts of Overdue Bills

[Katelyn Browne]

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 at ccpcs.org
http://www.ccpcs.org/library/


On Tue, Mar 25, 2014 at 12:10 PM, Lisa Gugliotti <lisa at 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 at hchlibrary.org
>     www.hchlibrary.org
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>