by Andrew Fuerste-Henry on Oct 24, 2019
If you’ve spent any time writing reports to look at fines and fees in Koha, you’ve likely had occasion to scratch your head over some mysterious coded values. Koha version 19.05 is going to alleviate some of that confusion, though we’ll have to put in a little work to reap the benefits of this change. These changes are detailed in Bug 22521, but I’ll offer some simpler explanation here.
A little background
Information about patrons’ fines and fees is stored in the accountlines table, along with any payments, credits, and writeoffs. Within the accountlines table, the accounttype field marks what type of account action each line represents. The 18.11 manual contains a helpful list of the potential values for this field. For this blog post, we’re particularly interested in these three:
- FU = Overdue, still acccruing
- F = Overdue fine
- FFOR = Forgiven fine
These three types combined cover all possible variations for overdue fines. While an item is checked out and overdue, the fines cronjob applies a fine of type FU (for “fine updating”). If the item is checked in normally, that FU fine gets replaced with an F fine. If the item is checked in with the Forgive Fines mode checkbox checked, the FU fine is replaced with an FFOR fine.
That means that if we want to write a report to find all fines we’d say “WHERE accountlines.accounttype IN (“F”,”FFOR,”FU”).” Here’s an example:
Changes in 19.05
The data structure described above confusingly conflates the type of entry (overdue fine) with the status of that entry (accruing, set, or forgiven). Bug 22521 was filed to split type and status into two distinct values, both to clarify the existing data and logic and to enable future development.
In the 19.05 schema, the accountlines table contains a new column for status. All overdue fines are recorded with the type “OVERDUE” and a status of “UNRETURNED”, “RETURNED”, or “FORGIVEN.” That means that if we want to write a report to find all fines we’d say “WHERE accounttype=”OVERDUE.” Here’s an example:
Changes to existing data and reports
When a site is upgraded to 19.05, the upgrade process does back through all existing accountlines data and translates the old fines data to the new values. However, it’s not able to automatically change reports to use these new values. Here’s my 18.11 example report run on a 19.05 site without changes to the accounttype logic:
The report comes back empty because we no longer have anything in our database with accounttype F, FFOR, or FU. It does not give an error because our report is still using valid syntax, it’s just looking for something that’s not there anymore.
Broadly speaking, the changes that reports will need to accommodate these new values are fairly simple:
- “WHERE accounttype IN (‘F’,’FFOR’,’FU’)” becomes “WHERE accounttype=’OVERDUE’”
- “WHERE accounttype=’FU’” becomes “WHERE accounttype=’OVERDUE’ AND status=’UNRETURNED’”
- “WHERE accounttype=’F’” becomes “WHERE accounttype=’OVERDUE’ AND status=’RETURNED’”
- “WHERE accounttype=’FFOR’” becomes “WHERE accounttype=’OVERDUE’ AND status=’FORGIVEN’”
There will surely be reports that require more complicated edits, but these basics will cover most cases.
Here at ByWater, we’re working to identify reports across our partner libraries that will be impacted by the change. Once libraries are upgraded to 19.05, we’ll proactively address those reports to minimize downtime. While one could duplicate an existing accounts report and edit it for these new values while still on 18.11, that new report will not successfully give data until after the upgrade.
Read more by Andrew Fuerste-Henry