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.