Koha Reports : Patrons with outstanding fines with books

This report will list patrons who have outstanding fines and also still have items belonging to the library. These reports have been tested on Koha 3.16 to koha 3.18


SELECT
borrowers.cardnumber AS Cardnumber,
CONCAT(
borrowers.surname,
', ',
borrowers.firstname) AS Name,
FORMAT(SUM(accountlines.amountoutstanding),2) AS "Amount outstanding"
FROM borrowers
JOIN accountlines
USING(borrowernumber)
WHERE
accountlines.amountoutstanding > 0
AND
borrowers.borrowernumber IN (SELECT borrowernumber FROM issues)
GROUP BY borrowers.cardnumber
ORDER BY borrowers.surname ASC

If you would like to have the option of selecting only patrons belonging to a particular branch, then the following variation of the same report will help you.


SELECT
borrowers.cardnumber AS Cardnumber,
CONCAT(
borrowers.surname,
', ',
borrowers.firstname) AS Name,
FORMAT(SUM(accountlines.amountoutstanding),2) AS "Amount outstanding"
FROM borrowers
JOIN accountlines
USING(borrowernumber)
WHERE
accountlines.amountoutstanding > 0
AND 
branchcode = <<Branch|branches>>
AND
borrowers.borrowernumber IN (SELECT borrowernumber FROM issues)
GROUP BY borrowers.cardnumber
ORDER BY borrowers.surname ASC

The above report will include an extra step where you will be required to select the branch you want from a drop down list consisting of all branches in your library.

Otuoma Sanya

Otuoma Sanya is a full-time systems librarian, tech enthusiast and writer. His areas of interest are data mining, institutional repositories, library automation and web development using python Django.

Leave a Reply