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.html?m=1
On Sun, 26 Jun 2022, 7:36 am Shafrizan Affendi, <shaffe...@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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha