Fine Paid for Library Patrons (Students Fine Amount Report)
  • Create the New SQL Report
  • Copy and Paste the following lines into the SQL report. Save and close.

Koha 18.11 below Version
 
SELECT accountlines.Date,borrowers.Cardnumber,borrowers.Surname AS "Students Name", borrowers.borrowernotes AS Department, accountlines.Amount As "Paid Amount", accountlines.Amountoutstanding
FROM accountlines, borrowers WHERE borrowers.borrowernumber = accountlines.borrowernumber AND accounttype = 'pay' AND date  BETWEEN <<Between Date (YYYY-DD-MM)|date>> AND <<and (yyyy-dd-mm)|date>>  ORDER BY  accountlines.Date

 
 Koha 19.5 above Version
 
SELECT 
    CONCAT("<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=", T1.borrowernumber, "\" target=\"_blank\">View Transaction</a>") AS 'Click to view'
    , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date'
    , T2.cardnumber AS 'Lib Card No.'
    , CONCAT(T2.surname) AS 'Name'
    , T3.description AS 'Category'
    , T2.borrowernotes AS 'Dapartment'
    , CONCAT("₹", LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid Amount' 
FROM 
    `accountlines` T1 
     LEFT JOIN borrowers T2 USING (borrowernumber) 
    LEFT JOIN categories T3 USING (categorycode) 
WHERE 
    T1.credit_type_code="payment" 
    AND
   DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>> 
ORDER BY DATE(T1.timestamp), CONCAT(T2.firstname, " ", T2.surname)


Koha 22.11 above Version with Invoice Number
 
SELECT ROW_NUMBER() OVER (ORDER BY DATE(T1.credit_number) , CONCAT( T1.timestamp, " ",T2.surname ))AS 'S.No'
    , DATE_FORMAT(DATE(T1.timestamp), "%d/%m/%Y") AS 'Txn Date'
    , T1.credit_number AS 'Bill No'
    , T2.cardnumber AS 'Roll No.'
    , T2.surname AS 'Name of the Student'
    , T3.description AS 'Designation'
    , T2.borrowernotes AS 'Dapartment'
    , CONCAT( LPAD(REPLACE(ROUND(T1.amount, 2),"-", ""), 8, " ")) AS 'Paid Amount' 
  
FROM 
    `accountlines` T1 
     LEFT JOIN borrowers T2 USING (borrowernumber) 
    LEFT JOIN categories T3 USING (categorycode) 
WHERE 
T1.credit_type_code="payment" 
    AND
   DATE(T1.timestamp) BETWEEN <<From date|date>> AND <<To date|date>> 


Report Model (Run Report)