[Koha] Koha Sql Report Help

[MASTeR Library]

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, <shaffendi at 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>