Koha (Library system) User Paid Fine Report

by

currently I’m using KOHA v23.05, in the Report module, I want to create a report which will appear the users/borrowers who paid fine (daily/monthly). Eventually I managed to pull the data of borrower number, but not their name. As per below image. The manager id and manager surname is needed to acknowledge which staff that handle the fine payment.

My question is, how can I appear the borrower (who paid fine) in this report? I’ll provide the SQL codes together.

Thank you in advance, really need a help for this.

SELECT accountlines.accountlines_id,accountlines.borrowernumber,CAST(accountlines.amount AS DECIMAL(10, 2)) AS formatted_amount,accountlines.date,accountlines.manager_id,borrowers.surname AS manager_surname
FROM accountlines AS accountlines
JOIN borrowers AS borrowers ON accountlines.manager_id = borrowers.borrowernumber
WHERE accountlines.credit_type_code = "Payment" AND DATE(accountlines.timestamp) BETWEEN <<From date|date>> AND <<To date|date>>

Result/report

accountlines_id borrowernumber formatted_amount date                           manager_id manager_surname
353645 58395 -0.60 2023-10-10 10:46:30 21 WAN AZURA RADZUAN
353646 58428 -0.70 2023-10-10 10:56:09 820 SUZYLA BINTI ABDUL AZIZ
353647 58427 -2.90 2023-10-10 10:57:38 820 SUZYLA BINTI ABDUL AZIZ
353648 58624 -2.40 2023-10-10 10:58:57 820 SUZYLA BINTI ABDUL AZIZ
353649 58599 -0.20 2023-10-10 11:00:53 820 SUZYLA BINTI ABDUL AZIZ
353660 1036 -1.00 2023-10-10 11:32:31 187 MOHD. JAMIL AHMAD MOKHTOR

  • 1

    Don’t post code, data or error messages as images. Post them as text. See How to Ask

    – 

  • Thank you for the comments. Already edited.

    – 

You need to add a second join to the borrowers table to retrieve the borrower’s details:

SELECT
    a.accountlines_id,
    a.borrowernumber,
    b.surname AS borrower_surname,
    CAST(a.amount AS DECIMAL(10, 2)) AS formatted_amount,
    a.date,
    a.manager_id,
    m.surname AS manager_surname
FROM accountlines AS a
JOIN borrowers AS m ON a.manager_id = m.borrowernumber
JOIN borrowers AS b ON a.borrowernumber = b.borrowernumber
WHERE a.credit_type_code="Payment"
AND DATE(a.timestamp) BETWEEN <<From date|date>> AND <<To date|date>>

Leave a Comment