Koha How-To

Sending Notices for Long Overdue Items

Most of our partner libraries use Koha's long overdue functionality -- when an item becomes sufficiently overdue, the system charges the patron the item's replacement price. However, the long overdue process doesn't include any function to notify the patron that their item has become lost and they now owe the library for its replacement. We've been using Koha's overdue notices for this purpose, but it's not an ideal solution. Because overdue notices are not intrinsically linked to the long overdue process, what we're really doing here is sending an overdue notice for things are are going to be marked long overdue soon, which is messily imprecise. There are a handful of ways in which the two processes can get out of sync. This creates some headaches and have left us with a variety of work-arounds to keep notices sending as desired.

A new option

But then came Koha 19.05 and the new patron_emailer cron! Jessie and Kelly already made a great blog post and video about how this cron works. In short, it lets us write a report that gives a list of patrons, write a notice that pulls data from the report, and then tell Koha to send that notice via email on a recurring schedule. In this specific case, this means we can write a report listing items that were marked long overdue in the most recent run of the long overdue cronjob and then use that report to send them all a notice about those items.

Because we're no longer trying to use the overdue notices to guess at what's about to become long overdue, we no longer need to worry about trying to get the two processes to line up. And, as an added bonus, this frees up the third overdue that we had been using as a billing notice. We end up with both better notices and more notices!

The report

The core of the report we need here isn't complicated. We know the long overdue cronjob gets run once a day and is the only way for a lost charge to get generated by a cronjob, so we want a list of all of the lost item charges applied by a cronjob today. Most simply, that means:

SELECT *

FROM accountlines

WHERE accounttype='l' and interface='cron' and date=curdate()

However, the patron emailer cron needs our report to contain all of the information that we want to push into our notice. If we want the patron's name and the title of the lost item and all those other important details, we need to get that into the report in a way that we can then pass into the notice in a way that looks nice. That complicates things a bit, but here's one way to do it:

SELECT borrowernumber, cardnumber, email, firstname, surname, GROUP_CONCAT(items_content separator '|') AS items_content_list
FROM borrowers LEFT JOIN
(SELECT issues.borrowernumber, CONCAT('"',b.title,'" by ',author,', Due: ',date(date_due),', Barcode: ',Barcode,', Replacement charge: $',accountlines.amount) AS items_content
FROM accountlines LEFT JOIN items USING (itemnumber) LEFT JOIN biblio b USING (biblionumber) LEFT JOIN borrowers USING (borrowernumber) LEFT JOIN issues USING (issue_id)
WHERE accountlines.date=curdate() AND accounttype='l' AND interface='cron'
GROUP BY issue_id) ic
USING (borrowernumber)
WHERE items_content IS NOT NULL
GROUP BY borrowernumber

Note that this report looks at the issues table. That means it will only work if the lost item still shows as checked out to the patron, which is going to depend on how your library has set the MarkLostItemAsReturned system preference. If you're marking items as returned when the cron sets them to long overdue, you just need to adjust that report to look at the old_issues table instead:

SELECT borrowernumber, cardnumber, email, firstname, surname, GROUP_CONCAT(items_content separator '|') AS items_content_list
FROM borrowers LEFT JOIN
(SELECT old_issues.borrowernumber, CONCAT('"',b.title,'" by ',author,', Due: ',date(date_due),', Barcode: ',Barcode,', Replacement charge: $',accountlines.amount) AS items_content
FROM accountlines LEFT JOIN items USING (itemnumber) LEFT JOIN biblio b USING (biblionumber) LEFT JOIN borrowers USING (borrowernumber) LEFT JOIN old_issues USING (issue_id)
WHERE accountlines.date=curdate() AND accounttype='l' AND interface='cron'
GROUP BY issue_id) ic
USING (borrowernumber)
WHERE items_content IS NOT NULL
GROUP BY borrowernumber

Here's an example of what that report looks like when you run it in Koha:

Note that the items_content_list field has details for two separate long overdue items, separated by a pipe ( | ). Koha's going to make one email for each line in our report, so when a patron has multiple long overdue items we need to either group them together in the same line or send multiple emails. Assuming libraries would rather send one email per patron, we've grouped them up and will break them out more readably in the notice.

The notice

Next we need to create the notice. We don't often create completely new notices in Koha, so this may be unfamiliar, but it's pretty simple and something we're happy to help with. On the Notices & Slips page, click the New Notice button and select the module you'd like to put this notice in. Our process will work whichever you pick, but Circulation makes sense organizationally. Give your notice a unique code and a descriptive name. I used the code LONGOVERDUE and the name "Long Overdue Lost Notice":

Then you want to define the content of the email version of your notice. This is going to use Template Toolkit markup language rather than the "hungry alligator" (<< >>) syntax we use in most other notices. Our Template Toolkit tags are going to name the report columns we want to pull data from. So if your report has a column named "surname," you put "[% surname %]" in your notice where you want that name to show up. Here's what I wrote:

Dear [% firstname %] [% surname %],

You are being billed for:

[% FOREACH item IN items_content_list.split('\\|') %]
[% item %]
[% END %]

A processing fee of $10 will be assessed for each item listed above.

Thank you,
Andrew's Pretend Library Staff

The line in there that says "[% items_content_list.replace('\\|','<br>') %]" takes the items_content_list I mentioned above and replaces the pipe with a line break.

Note the line of text about processing fees. I chose not to work processing fees into the report just to spare complexity. Instead, I just put it in the notice as plain text. If your processing fees differ by item type the report could be made to include them and reflect those differences.

The cron

The next step is to define your cronjob. I'm not going to get into it because it's both pretty simple and something most of our partner libraries would need us to do for them. You can see the patron email cron and its options here if you're interested.

What about print notices?

Some of you are probably thinking about your patrons who don't have email addresses and realizing I haven't talked about notices to print and mail. This cron does not generate print notices. There's room for future development within Koha there if the community is interested, but we have options with existing tools. My suggestion would be to create a version of the report above that only lists patrons without an email address, ask us to schedule that to email to you as a CSV attachment each day, and use that file to perform a mail merge in Office.

UPDATE

On further consideration, I decided it wasn't a great idea to make the report need to change should a library adjust their MarkLostItemsAsReturned behavior. Here's a new report that will work regardless of whether or not you're keeping lost items on the patron's account:

SELECT borrowernumber, cardnumber, email, firstname, surname, group_concat(items_content separator '|') as items_content_list

FROM borrowers LEFT JOIN

(SELECT coalesce(issues.borrowernumber,old_issues.borrowernumber) as borrowernumber, concat('"',b.title,'" by ',author,', Due: ',date(coalesce(issues.date_due,old_issues.date_due)),', Barcode: ',Barcode,', Replacement charge: $',accountlines.amount) as items_content

FROM accountlines left join items using (itemnumber) left join biblio b using (biblionumber) left join borrowers using (borrowernumber) left join issues using (issue_id) left join old_issues using (issue_id)

WHERE accountlines.date=curdate() AND accounttype='l' and interface='cron'

GROUP BY issues.issue_id, old_issues.issue_id) ic

USING (borrowernumber)

WHERE items_content is not null

GROUP BY borrowernumber

Read more by Andrew Fuerste-Henry

Tags