Yesterday was the last day of the year, when Sri Ashish Kumar Barik the young and enthusiastic librarian at Midnapore City College requested for a new custom SQL report that would allow him:
To see every payment made by every user in a given date range, sorted by MM/YY. It should show every type of payment made e.g. overdue fines, lost book fees, library card replacement. Basically everything that was charged to and paid by a patron
In this blog post we share it with all, so that others looking for something similar can have a ready template for their use. It’s our way to saying thank you and Happy New Year 2019 to all our readers, clients and well-wishers.
Putting it together
The report uses three tables in the Koha database – (a) accountlines; (b) borrowers and (c) categories.
The report adds a quick transaction view link next to each and every payment. For library staff wanting to find out the details of a particular payment, with this report that information is just a single click away.
The report metadata
Report name | List all payments made by patrons between two dates |
Report group | Accounts |
Notes | List all payments made by patrons between two dates, sorted by date and patron name. |
The SQL statement
SELECT CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=", T1.borrowernumber, "\" target=\"_blank\">View Transaction</a>") AS 'Click to view' , T2.cardnumber AS 'Card No.' , CONCAT(T2.firstname, " ", T2.surname) AS 'Name' , T3.description AS 'Category' , CONCAT(SUBSTRING(MONTHNAME(T1.timestamp), 1,3), " ", YEAR(T1.timestamp)) AS 'Billing Period' , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date' , CONCAT("₹", LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid' FROM `accountlines` T1 LEFT JOIN borrowers T2 USING (borrowernumber) LEFT JOIN categories T3 USING (categorycode) WHERE T1.accounttype="PAY" AND DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>> ORDER BY DATE(T1.timestamp), CONCAT(T2.firstname, " ", T2.surname)
See it in action
Here is a short 2 minute video of it for those who want to see it action.
Wishing everyone a Happy New Year 2019.