[Koha] Koha Sql Report Help

Shafrizan Affendi shaffendi at gmail.com
Sun Jun 26 14:05:10 NZST 2022


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


More information about the Koha mailing list