[Koha] Help regarding fine calculation report

[Cunningham, Keah M]

Hi T. Suresh Kumar,

Does this get what you need?

*****
SELECT surname AS 'Last Name', firstname AS 'First Name', cardnumber AS 'Card Number', description AS Description, amountoutstanding AS 'Amount Outstanding', itemcallnumber AS 'Item Call Number', items.itype AS 'Item-Level Item Item', holdingbranch AS 'Holding Branch', barcode AS Barcode, datetime AS 'Check In Date', a.timestamp AS 'Fine Date', type AS Type
FROM statistics
LEFT JOIN accountlines a USING (borrowernumber,itemnumber)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
WHERE (type='return' OR type ='renew') AND amountoutstanding > 0 AND datetime BETWEEN <<Start Date|date>> AND <<End Date|date>>
ORDER BY type ASC, surname ASC, firstname ASC
***

Modified from https://wiki.koha-community.org/wiki/SQL_Reports_Library#Fines_outstanding_for_items_checked_in_during_a_date_range_.28Fines_amnesty_week_check.29

Best,
Keah

-----Original Message-----
From: Koha [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Suresh Kumar Tejomurtula
Sent: Thursday, July 07, 2016 6:49 AM
To: koha <koha at lists.katipo.co.nz>
Subject: [Koha] Help regarding fine calculation report

Hi,

Need help in refining the below query which is based on the query from SQL reports library. I need to add the following also to the query:

1. Using the below report Iam able to generate fine report for only returned items. I need to generate fine even for the items that are renewed and fine is generated.

2. Also in some cases the below query is not including the start date and end date. As far as I read about mysql help regarding BETWEEN function, it includes both start date and END date.


*SELECT *
*   b.cardnumber as Emp_NO,TRIM(CONCAT( b.surname, b.firstname)) as Name,
b.address as DEPT,bib.title, i.barcode as Acc_NO,DATE_FORMAT( a.timestamp, "%d %b %Y" ) as FineDate,*
*    a.amountoutstanding, DATE_FORMAT(ni.issuedate, "%d %b %Y" ) as
Issue_date, DATE_FORMAT(ni.date_due, "%d %b %Y" ) as Due_date,*
*    IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate, IF (
ni.lastreneweddate IS NULL , " ", ni.lastreneweddate ) AS lastreneweddate * *FROM accountlines a *
*  LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber ) *
*  LEFT JOIN items i ON ( a.itemnumber = i.itemnumber ) *
*  LEFT JOIN biblio bib ON ( i.biblionumber = bib.biblionumber ) *
*  LEFT JOIN ( SELECT * FROM issues UNION SELECT * FROM old_issues ) ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber ) * *WHERE * *DATE_FORMAT( ni.returndate,  "%m" )=<<Enter Month MM>> AND DATE_FORMAT( ni.returndate,  "%Y" )=<<Enter Year YYYY>>*
*and*
*((itype='BK'  OR itype='REP' ) AND amountoutstanding >'5.00'  OR  (itype='JNL' OR itype='STD') AND amountoutstanding >'1.50')  and returndate is not null* *ORDER BY returndate asc*

If there is any other report in the reports library which helps me to generate fines report between two dates for the items that are returned/renewed, please let me know.
--
Regards
T. Suresh Kumar
_______________________________________________
Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha