Fine Paid for Library Patrons (Koha SQL Reports Queries) Koha v 18 and above https://libpowertech.blogspot.com/2019/05/fine-paid-for-library-patrons-sql.... On Sun, 26 Jun 2022, 7:36 am Shafrizan Affendi, <shaffendi@gmail.com> wrote:

Hi,

Recently I upgraded my Koha from ver 18 to 22.05. Some of my sql report are not working anymore. For example to show a fine collection by staff id. Here is my previous sql.

SELECT @rownum:=@rownum+1 No, student.borrowernumber, student.cardnumber AS Student_ID, student.surname AS Student_Name, format(db.amount,2) As Amount, accounttype, date(db.timestamp) as Date, time(db.timestamp) As Time, db.manager_id AS Staff_ID, staff.surname AS Staff_Name, db.note As Nota FROM (SELECT @rownum:=0) r, action_logs a, accountlines db INNER JOIN borrowers student ON db.borrowernumber = student.borrowernumber INNER JOIN borrowers staff ON db.manager_id = staff.borrowernumber WHERE date(db.timestamp) BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD|date>> AND module='fines' AND accounttype='pay' GROUP BY db.timestamp ORDER BY db.timestamp

I did read about the changes regarding table accountype but still not quite understand. Anyone can help me to correct this sql?

Really appreciate your help

Thanks in advance _______________________________________________

Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha