Fine Paid for Library Patrons (Students Fine Amount Report)
Koha 18.11 below Version
- 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)
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>>
4 Comments
Just tried the SQL report query and indeed it's worked perfectly. This is what I have been looking for. Thanks for sharing...
ReplyDeleteThanks you 👍
Deletewriteoff fine error so please solve...
ReplyDeleteThe following error was encountered:
The database returned the following error:
Unknown column 'accountlines.accounttype' in 'where clause'
Please check the log for further details.
Return to previous page
Go to Tools Notice and check the Write off notice "accountlines.accounttype"
Delete