All payments in a date range with quick transaction view

A SQL report for Koha ILS

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.

2 thoughts on “All payments in a date range with quick transaction view”

  1. I posted this comment on YouTube, but no one has responded, so I’ll ask here too:

    Is this a test database with false data? Or are these the card numbers and names of actual students and faculty that use your library? I’m just curious because, on the one hand you’re blurring the photographs on the accounts, but on the other hand I can see their names, their card numbers and the amounts they have paid in fees which would be considered a breach of the library user’s right to privacy in the USA.

Leave a Reply

Your email address will not be published. Required fields are marked *