Fine Paid for Library Patrons (Koha SQL Reports Queries)

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)