[Koha] SQL reports - Fine generating with itemtype filter

Suresh Kumar Tejomurtula tejoskumar at gmail.com
Wed Jun 8 01:26:50 NZST 2016

Dear all,

I need help in generating a report related to fines.

Using the below report iam able to generate the fines. I would like to add
a filter to this report "itemtype" for fine report as our circulation rules
are slightly different for books and journals and the complete fine reports
sometimes not exactly what iam looking for.

I tried adding this (SELECT itype FROM items t WHERE t.itype = 'BK') AS
'ITEMTYPE') and added items t at "from statement", but not working. As iam
not much aware of sql reports, requesting help in generating report with
itemtype as a filter, I would like to generate fine report on a monthly
basis with itemtypes.

    (SELECT CONCAT('<a href=\"/cgi-bin/koha/members/
boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ',
    FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron,
    format(sum(amountoutstanding),2) AS 'Outstanding',
    (SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber =
i.borrowernumber) AS 'Checkouts'
    accountlines a, borrowers b
    (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE
a2.borrowernumber = a.borrowernumber)  > '0.00'
    AND a.borrowernumber = b.borrowernumber
AND a.timestamp BETWEEN <<FINE Between (yyyy-mm-dd)|date>> AND <<and
    a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC

T. Suresh Kumar

More information about the Koha mailing list