SQL Reports Accounting

Reports about anything to do with accounting, including fines, fees, credits etc.
SQL Reports Main Library 
== Fines with Patron & Item Info ==
* Developer: Kyle M Hall 
* Module: Accounting
* Purpose: List of unpaid fines with patron and item information
* Status: Complete - Updated 6/4/19
SELECT 
 b.surname, b.firstname, b.email, bib.title, i.barcode,
 a.amountoutstanding, ni.issuedate, ni.date_due, 
 IF ( ni.returndate IS NULL , " ", ni.returndate ) AS returndate 
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 
 a.amountoutstanding > 0 
GROUP BY a.accountlines_id
ORDER BY b.surname, b.firstname, ni.timestamp DESC
== List patrons detailed information of fine ( including title info for each item )==
* Developer: Pankaj Kumar Sharma 
* Module: Accounting
* Purpose: Useful for library professionals who want to fetch fine against each line item in detail.
* Status: Completed
* Works/Tested with: Koha Version-18.05.04.000, MySQL Version-14.14
SELECT FORMAT(accountlines.amountoutstanding,2) as fine, borrowers.surname, borrowers.firstname, borrowers.cardnumber, 
issues.date_due, (TO_DAYS( date_due)-TO_DAYS(curdate())) AS 'days overdue', items.itype, items.itemcallnumber, items.barcode,
 items.homebranch, biblio.title, biblio.author 
FROM 
borrowers left JOIN accountlines ON (borrowers.borrowernumber=accountlines.borrowernumber) 
JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber) 
LEFT JOIN items ON (issues.itemnumber=items.itemnumber) LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) 
where
(TO_DAYS( date_due)-TO_DAYS(curdate())) <=0 
order by borrowers.borrowernumber
== Patrons with Fines ==
* Developer: Katrin Fischer 
* Module: Accounting
* Purpose: List patrons with their fine amounts
* Status: Complete
SELECT 
 (SELECT CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ', b.firstname,'</a>') 
 FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron, 
 format(sum(amountoutstanding),2) AS 'Outstanding',
 (SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'
FROM 
 accountlines a, borrowers b
WHERE 
 (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber) > '0.00'
 AND a.borrowernumber = b.borrowernumber
GROUP BY 
 a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC
== Patrons with Fines at Branch ==
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Accounting
* Purpose: List patrons with their fine amounts limited by branch
* Status: Complete
select p.surname, p.firstname, p.borrowernumber, p.cardnumber,
format(sum(a.amountoutstanding),2) as owes
from borrowers p
left join accountlines a using (borrowernumber)
where a.amountoutstanding > 0 and p.branchcode=<<Branch|branches>>
group by a.borrowernumber
== Patrons with More Than an Amount in Fines ==
* Developer: Nicole C. Baratta, ByWater Solutions 
* Module: Accounting
* Purpose: List patrons with who owe greater than or equal to an amount entered when the report is run.
* Status: Complete
SELECT p.cardnumber, p.surname, p.firstname, p.address, p.address2, p.city, p.state, p.phone, p.branchcode as 'patron branch',
 p.debarred , p.debarredcomment, p.dateexpiry, format(sum(a.amountoutstanding),2) as 'amount owed'
FROM borrowers p
left join accountlines a using (borrowernumber)
GROUP BY a.borrowernumber
HAVING sum(a.amountoutstanding) >= <<Owe more than>>
ORDER BY p.surname, p.firstname 
== Patrons with credits ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: 
* Status: Complete 
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, address, city, zipcode, round(Sum(accountlines.amountoutstanding),2) AS 'total owed' 
FROM accountlines LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) WHERE amountoutstanding != 0 
GROUP BY accountlines.borrowernumber having sum(accountlines.amountoutstanding) < 0 
ORDER BY borrowers.surname, borrowers.firstname
== Collections Report for Unique Management==
* Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS
* Module: Accounting
* Purpose: Outputs patrons with fines in certain categories, with more than $X in fines, and no fine payments in the last 60 days. 
* Status: Completed
* Note: Updated by Jared Camins-Esakov, C & P Bibliography Services on 16 May 2012. If you were using a previous version of this report, please update the report and contact Unique immediately to tell them to disregard previous reports. The logic was reversed.
* Note: This report, with the NOT IN line, eliminates any patron who has *ever* paid on their account - it is too broad and we've removed it from our reports we use at NEKLS now (see the next report for an updated specific version).
 SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, 
 borrowers.address, borrowers.city, borrowers.zipcode, borrowers.email, borrowers.phone,
 borrowers.dateofbirth, borrowers.debarred, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
 FROM borrowers, accountlines 
 WHERE borrowers.categorycode IN ('BONN-CITY', 'OTT-CITY') 
 AND borrowers.borrowernumber 
 NOT IN (SELECT distinct borrowernumber FROM accountlines WHERE accountlines.date < DATE_SUB(CURDATE(),INTERVAL 60 DAY) AND (accountlines.accounttype IN ('PAY', 'C') ) )
 AND borrowers.borrowernumber = accountlines.borrowernumber 
 GROUP BY borrowers.borrowernumber 
 HAVING SUM(accountlines.amountoutstanding) >= 25.00 
 ORDER BY borrowers.surname ASC;
* Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
* Module: Accounting
* Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
* Status: Completed
* Note: New Delinquent Report - first report sent to Unique, sent weekly after that
* Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT borrowers.cardnumber,
 borrowers.borrowernumber,
 borrowers.surname,
 borrowers.firstname,
 borrowers.address,
 borrowers.city,
 borrowers.zipcode,
 borrowers.phone,
 borrowers.mobile,
 borrowers.phonepro AS "Alt Ph 1",
 borrowers.B_phone AS "Alt Ph 2",
 borrowers.branchcode,
 categories.category_type AS "Adult or Child",
 borrowers.dateofbirth, MAX(accountlines.date) AS "Most recent charge",
 FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
 LEFT JOIN borrowers USING(borrowernumber) 
 LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') AND
 ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) AND
 accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) AND
 accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day) 
GROUP BY borrowers.borrowernumber 
HAVING Due >=25.00 
ORDER BY borrowers.surname ASC
* Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
* Module: Accounting
* Purpose: Produces report of patrons in particular branch codes with their sort1 field set to "yes" and fines of more than $25.
* Status: Completed
* Note: Update report - weekly report sent to Unique
SELECT borrowers.borrowernumber, borrowers.surname, borrowers.firstname, FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
LEFT JOIN borrowers USING(borrowernumber) 
LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN (BRANCHCODES SEPARATED BY COMMAS) 
AND borrowers.sort1 = 'yes'
GROUP BY borrowers.borrowernumber 
ORDER BY borrowers.surname ASC
* Developer: A team effort: Thatcher Rea - ByWater Solutions, Nicole Engard - ByWater Solutions, Katrin Fischer - BSZ, Liz Rea - NEKLS and Robin Hastings - NEKLS
* Module: Accounting
* Purpose: Produces report of patrons in particular branch codes with more than $25 in fines that were incurred more than 60 but less than 365 days ago
* Status: Completed
* Note: New Delinquent Report Linked - used to easily add the $10 fee and set the sort1 field to "yes" for new delinquent accounts
* Note: Changed WHERE clause: borrowers.sort1 != 'yes' => ( borrowers.sort1 != 'yes' OR borrower.sort1 IS NULL ) -- Barton Chittenden - Bywater
SELECT CONCAT('<a href=\"/cgi-bin/koha/members/maninvoice.pl?borrowernumber=', borrowers.borrowernumber, '\" target="_blank">', borrowers.cardnumber, '</a>') AS "Link to Fines",
 borrowers.borrowernumber,
 borrowers.surname,
 borrowers.firstname,
 borrowers.address,
 borrowers.city,
 borrowers.zipcode,
 borrowers.phone,
 borrowers.mobile,
 borrowers.phonepro AS "Alt Ph 1",
 borrowers.B_phone AS "Alt Ph 2",
 borrowers.branchcode,
 categories.category_type AS "Adult or Child",
 borrowers.dateofbirth,
 MAX(accountlines.date) AS "Most recent charge",
 FORMAT(SUM(accountlines.amountoutstanding),2) AS Due 
FROM accountlines 
 LEFT JOIN borrowers USING(borrowernumber) 
 LEFT JOIN categories USING(categorycode) 
WHERE borrowers.categorycode IN ('OTT-CITY','OTT-CITYJ','OTT-FRCO','OTT-FRCOJ','OTT-OTHR','OTT-OTHRJ') AND
 ( borrowers.sort1 != 'yes' OR borrowers.sort1 IS NULL ) AND
 accountlines.date > DATE_SUB(CURDATE(), INTERVAL 1 year) AND
 accountlines.date < DATE_SUB(CURDATE(), INTERVAL 60 day) 
GROUP BY borrowers.borrowernumber 
HAVING Due >=25.00 
ORDER BY borrowers.surname ASC
== No checkouts since accruing a fine ==
* Developer: Ian Bays (PTFS Europe) on behalf of BASE Library
* Module: Accounting
* Purpose: This report can help to show what effect fines have and whether readers return after accruing. 
* Status: One Example
SELECT
 CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',borrowers.borrowernumber,'\" target="_blank">', borrowers.borrowernumber, '</a>') AS borrowernumber,
 (SELECT
 MAX(issuedate)
 FROM issues
 WHERE borrowernumber = borrowers.borrowernumber)
 'latest issue',
 (SELECT
 MAX(issuedate)
 FROM old_issues
 WHERE borrowernumber = borrowers.borrowernumber)
 'latest old iss',
 (SELECT
 MAX(date)
 FROM accountlines
 WHERE (accounttype = 'F'
 OR accounttype = 'FU'
 OR accounttype = 'O')
 AND borrowernumber = borrowers.borrowernumber)
 'latest fine'
 FROM borrowers
 WHERE (SELECT
 MAX(issuedate)
 FROM issues
 WHERE borrowernumber = borrowers.borrowernumber)
 < (SELECT
 MAX(date)
 FROM accountlines
 WHERE (accounttype = 'F'
 OR accounttype = 'FU'
 OR accounttype = 'O')
 AND borrowernumber = borrowers.borrowernumber)
 AND (SELECT
 MAX(issuedate)
 FROM old_issues
 WHERE borrowernumber = borrowers.borrowernumber)
 < (SELECT
 MAX(date)
 FROM accountlines
 WHERE (accounttype = 'F'
 OR accounttype = 'FU'
 OR accounttype = 'O')
 AND borrowernumber = borrowers.borrowernumber)
== Incremental Fines with Patron & Item Info ==
* Developer: Ramprasad Joshi
* Module: Accounting
* Purpose: List of unpaid fines with patron and item information, with an incremental charge: $1 per day the first fortnight overdue, $2 for the next, $5 daily after that; it can be tailored by patron category.
* Status: One Example
SELECT
 borrowers.cardnumber,borrowers.categorycode,borrowers.surname,issues.date_due,
 (TO_DAYS(curdate())-TO_DAYS( date_due)) AS daysoverdue,
 items.barcode AS 'Accession Number',
 biblio.title,biblio.author,
 IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=15,(TO_DAYS(curdate())-TO_DAYS( date_due)),
 IF((TO_DAYS(curdate())-TO_DAYS( date_due))<=30,2*(TO_DAYS(curdate())-TO_DAYS( date_due))-15,5*(TO_DAYS(curdate())-TO_DAYS( date_due))-105))
 AS fine
 FROM borrowers
 LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
 LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
 LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 WHERE (borrowers.categorycode=<<Patron Category|categorycode>>) AND (TO_DAYS(curdate())-TO_DAYS(date_due)) > '0'
 ORDER BY borrowers.cardnumber ASC
== Total Forgiven Fines Today ==
* Developer: Nicole C. Baratta, ByWater Solutions 
* Module: Accounting 
* Purpose: Total amount forgiven in fines today 
* Status: Complete 
SELECT SUM(amount) 
 FROM accountlines 
 WHERE DATE(timestamp)=CURDATE() AND (accounttype='FOR' OR accounttype='W')
== Total Fines Paid (Date Range) ==
* Developer: Dr Vimal Kumar V., Mahatma Gandhi University Library 
* Module: Accounting 
* Purpose: Generate list of overdue paid in a period. Applicable to Koha version since 19.11. 
* Status: Complete 
SELECT b.cardnumber AS 'Card Number',b.surname AS 'Name',FORMAT(ABS(a.amount), 2) AS 'Amount'
FROM borrowers b
JOIN accountlines a
WHERE b.borrowernumber = a.borrowernumber AND a.credit_type_code = 'payment' AND a.date 
BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD |date>> 
AND b.branchcode=<<Enter patrons library|branches>> AND categorycode LIKE <<Enter Category borrowers|categorycode>>
== Total Fines Paid Today ==
* Developer: Nicole C. Baratta, ByWater Solutions 
* Module: Accounting 
* Purpose: Total amount paid in fines today 
* Status: Complete 
SELECT SUM(amount) 
 FROM accountlines 
 WHERE DATE(timestamp)=CURDATE() AND (accounttype='PAY' OR accounttype='C')
== Yesterday's Fines by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: Fines charged yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' or accounttype = 'FU' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's Fines ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: Fines charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines WHERE (accounttype = 'F' or accounttype = 'FU' ) and date = (now() - interval 1 day)
== Yesterday's Lost Item Charges by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: lost items charged yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's Lost Item Charges ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: lost items charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
== Yesterday's Account Management Fees by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: acct mgt charged yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's Account Management Fees ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: acct mgt fees charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
== Yesterday's Forgiven Charges by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: forgiven charges yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's Forgiven Charges (entire system) ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: forgiven charges yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) and date = (now() - interval 1 day)
== Yesterday's Sundry Fees by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: sundry fees yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'M') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's Sundry Fees (entire system) ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: sundry fees charged yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines WHERE (accounttype = 'M') and date = (now() - interval 1 day) 
== Yesterday's Credits by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: credits yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'C') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's Credits (entire system) ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: credits yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines WHERE (accounttype = 'C') and date = (now() - interval 1 day)
== Yesterday's New Card Fees by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: new card fees yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'N') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's New Card Fees (entire system) ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: new card fees yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines WHERE (accounttype = 'N') and date = (now() - interval 1 day)
== Yesterday's Payments by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: payments yesterday for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'PAY') and date = (now() - interval 1 day) and borrowers.branchcode = 'LIB'
== Yesterday's Payments (entire system) ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: payments yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines WHERE (accounttype = 'PAY') and date = (now() - interval 1 day)
== Year to Date Fines by branch ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: year to date fines charged for a particular branch (edit branchcode as needed) 
* Status: Complete 
SELECT 
 round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' 
FROM accountlines 
LEFT JOIN borrowers on (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' or accounttype = 'FU' ) and YEAR(date) = YEAR(NOW()) and borrowers.branchcode = 'LIB'
== Year to Date Fines (entire system) ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: year to date fines charged (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' 
FROM accountlines 
WHERE (accounttype = 'F' or accounttype = 'FU' ) and YEAR(date) = YEAR(NOW())
== Total Fines Owed ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: total amount of fines owed (entire system) 
* Status: Complete 
SELECT FORMAT(Sum(accountlines.amountoutstanding),2) FROM accountlines
==Writeoff fine (Date Range wise) ==
* Developer: Nikunj Tyagi, DPL 
* Module: Accounting 
* Purpose: writeoff Amount (Date range wise) with patron details (entire system) 
* Status: Complete 
SELECT borrowers.borrowernumber,
 borrowers.cardnumber,
 accountlines.amount,
 accountlines.date
FROM accountlines, borrowers
WHERE borrowers.borrowernumber = accountlines.borrowernumber AND
 accounttype = 'W'
 AND date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>
== Write-off Fines in Date Range (with Circulation Note, Payment Notes, Timestamps, and Manager Name) ==
* Developer: Alex Chen, Butte County Library
* Module: Accounting
* Purpose: Provide a list of write-off fines in a date range with Circulation Note, Timestamps, Payment Notes, and staff who performed the actions for Accounting and Auditing purposes.
* Status: Complete
SELECT
	CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=', patrons.borrowernumber, '\" target=/"_blank\">', patrons.cardnumber, '</a>' ) AS 'Card Number',
	CONCAT(patrons.firstname, ' ', patrons.surname) AS 'Patron Name',
	patrons.borrowernotes AS 'Circulation Note',
	patrons.opacnote AS 'OPAC Note',
	patrons.categorycode AS 'Patron Category',
	accountlines.amount AS 'Amount',
	accountlines.timestamp AS 'Transaction Timestamp',
	accountlines.description AS 'Description',
	accountlines.note AS 'Payment Notes',
	CONCAT(managers.firstname, ' ', managers.surname) AS 'Manager Name'
FROM accountlines
LEFT JOIN borrowers patrons ON (accountlines.borrowernumber = patrons.borrowernumber)
LEFT JOIN borrowers managers ON (accountlines.manager_id = managers.borrowernumber)
WHERE
	(accountlines.date BETWEEN <<Fine Waived Between |date>> AND <<and |date>>)
	AND accountlines.accounttype = 'W'
	AND patrons.categorycode = <<Patron Category |categorycode>>
== Sum of Total Write-off Amount by Patron Category in a Date Range ==
* Developer: Alex Chen, Butte County Library
* Module: Accounting
* Purpose: Provide a sum of total write-off amount by patron categorycode in a date range
* Status: Complete
SELECT
	patrons.categorycode AS 'Patron Category',
	SUM(ABS(accountlines.amount)) AS 'Total Write-Off Amount'
FROM accountlines
LEFT JOIN borrowers patrons ON (accountlines.borrowernumber = patrons.borrowernumber)
WHERE
	(accountlines.date BETWEEN <<Fine Waived Between |date>> AND <<and |date>>)
	AND accountlines.accounttype = 'W'
GROUP BY patrons.categorycode
== Payment (fine) detail (Date Range) ==
* Developer: Nikunj Tyagi, DPL 
* Module: Accounting 
* Purpose: Payment (Date range wise) with patron details (entire system) 
* Status: Complete 
SELECT borrowers.borrowernumber,
 borrowers.cardnumber,
 accountlines.amount,
 accountlines.date
FROM accountlines, borrowers
WHERE borrowers.borrowernumber = accountlines.borrowernumber AND
 accounttype = 'pay' AND
 date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-DD>>
== Yesterday's Amount Collected (entire system) ==
* Developer: Jane Wagner, PTFS 
* Module: Accounting 
* Purpose: amount actually collected yesterday (entire system) 
* Status: Complete 
SELECT round(Sum(accountlines.amount),2) AS 'Paid Yesterday' 
FROM accountlines
WHERE (accounttype = 'PAY' ) and date = (now() - interval 1 day)
== Amount Collected in specific Date Range (entire system) ==
* Developer: Nicole C. Baratta, ByWater Solutions 
* Module: Accounting 
* Purpose: Asks you to enter the date range for which you would like to see all of the money collected at all branches.
* Status: Complete 
select FORMAT(abs(sum(amount)),2) as 'Total Collected' 
from accountlines 
where (accounttype='C' or accounttype='PAY') and 
 timestamp between <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
== Accounting for date range ==
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Accounting
* Purpose: List of all accounting details in date range
* Status: Complete
SELECT 
CASE accounttype 
 WHEN 'A' THEN 'Account management fee'
 WHEN 'C' THEN 'Credit'
 WHEN 'F' THEN 'Overdue Fine'
 WHEN 'FOR' THEN 'Forgiven'
 WHEN 'FU' THEN 'Overdue Fine Still Accruing'
 WHEN 'L' THEN 'Lost Item'
 WHEN 'LR' THEN 'Lost and Returned'
 WHEN 'M' THEN 'Sundry'
 WHEN 'N' THEN 'New Card'
 WHEN 'PAY' THEN 'Payment'
 WHEN 'W' THEN 'Writeoff'
 ELSE accounttype END 
 AS transaction, SUM(amount)
 FROM accountlines
 WHERE DATE(timestamp) between <<Collected between (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>
 GROUP BY accounttype
== Payments collected at a branch in a date range ==
* Developer: Nicole C. Baratta, ByWater Solutions
* Module: Accounting
* Purpose: Fines collected at a branch in a date range (uses the logged in user's branch)
* Status: Complete
SELECT FORMAT(abs(sum(a.amount)),2) AS 'Total Collected' 
FROM accountlines a
LEFT JOIN borrowers p on (a.manager_id=p.borrowernumber)
WHERE a.accounttype in ('C','PAY') AND 
 a.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> and
p.branchcode=<<Branch|branches>>
== Amount due on lost items (deleted and current) ==
* Developer: Barton Chittenden, ByWater Solutions
* Module: Accounting
* Purpose: Amount due on lost items (deleted and current)
* Status: Complete
SELECT
 FORMAT( SUM( amountoutstanding ), 2) as 'Amount Due'
FROM
 items i
 left join deleteditems di using ( itemnumber )
 left join accountlines a on ( a.itemnumber = COALESCE (i.itemnumber, di.itemnumber) )
WHERE
 COALESCE ( i.itemlost, di.itemlost ) != 0
== Forgiven fines for items checked in during a date range (Fines amnesty week check) ==
* Developer: Nick Clemens, ByWater Solutions
* Module: Accounting
* Purpose: Fines forgiven on books checked in during a date range
* Status: Complete
SELECT surname, firstname, cardnumber, description, amountoutstanding, itemcallnumber, holdingbranch,barcode, datetime AS CheckInDate, a.timestamp AS FineDate FROM statistics 
LEFT JOIN accountlines a USING (borrowernumber,itemnumber)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
WHERE type='return' AND accounttype='FFOR' AND datetime BETWEEN <<Start date|date>> AND <<End date|date>>
== Fines outstanding for items checked in during a date range (Fines amnesty week check) ==
* Developer: Nick Clemens, ByWater Solutions
* Module: Accounting
* Purpose: Fines outstanding on books checked in during a date range
* Status: Complete
SELECT surname, firstname, cardnumber, description, amountoutstanding, itemcallnumber, holdingbranch,barcode, datetime AS CheckInDate, a.timestamp AS FineDate FROM statistics 
LEFT JOIN accountlines a USING (borrowernumber,itemnumber)
LEFT JOIN borrowers USING (borrowernumber)
LEFT JOIN items USING (itemnumber)
WHERE type='return' AND amountoutstanding > 0 AND datetime BETWEEN <<Start date|date>> AND <<End date|date>>
== All accounts offsets for a given borrrower ==
* Developer: Andrew Fuerste-Henry, ByWater Solutions
* Module: Accounting
* Purpose: Show history of credits and debits for a borrower using the account_offsets table added in 17.11
* Status: Complete
select o.id, o.credit_id, o.debit_id, o.type, o.amount, o.created_on, a.borrowernumber, a.description 
from account_offsets o left join accountlines a on (o.debit_id=a.accountlines_id)
where a.borrowernumber=<<Borrower Number>>
order by o.created_on desc
== PayPal payments collected in a date range ==
* Developer: Myka Kennedy Stephens, Lancaster Theological Seminary
* Module: Accounting
* Purpose: For Koha libraries utilizing the PayPal integration; Displays fine/fee transactions for a date range with note = "PayPal"
* Status: Complete
SELECT
 CONCAT(
 '<a target="_blank" href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',
 borrowers.borrowernumber,
 '\">',
 borrowers.borrowernumber,
 '</a>'
 ) AS Link,
 borrowers.surname,
 borrowers.firstname,
 accountlines.date,
 accountlines.amount,
 accountlines.note
FROM
 accountlines,
 borrowers
WHERE
 borrowers.borrowernumber = accountlines.borrowernumber AND
 accounttype = 'pay' AND
 accountlines.note = 'PayPal' AND
 date BETWEEN <<Between (YYYY-MM-dd)|date>> AND <<and (YYYY-MM-dd)|date>>
ORDER BY
 date,
 borrowers.surname
== Duplicate Fines ==
* Developer: Ian Bays, PTFS Europe wrote this report for BASE Library 
* Module: Accounting 
* Purpose: Shows patrons that have been erroneously fined more than once on the same day for the same issue id
* Status: Complete 
select concat(date,', ',issue_id) iss_dt, 
 (select branchcode from borrowers where borrowernumber = accountlines.borrowernumber) branch, 
 CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',accountlines.borrowernumber,'\" target="_blank">', accountlines.borrowernumber, '</a>') AS borrowernumber, count(*) cnt, 
group_concat(timestamp separator ', ') stamps from accountlines 
where accounttype = 'F' and issue_id is not NULL group by iss_dt having cnt >1
order by branch, stamps DESC
== PayPal Transactions Sorted by Branch ==
* Developer: Lisette Scheer, Latah County Library District/Valnet Consortium 
* Module: Accounting 
* Purpose: Shows paypal payments in the last month, sorted by owning library. 
* Status: Complete 
SELECT
 Format(ABS(credits.amount), 2) AS AMT_COLLECTED,
 Format(ABS(account_offsets.amount), 2) AS FEE_AMOUNT,
 If(
 Format((debits.amount + account_offsets.amount), 2) = 0,
 "-", Format((debits.amount + account_offsets.amount), 2)
 ) AS PREVIOUS_PAYMENTS,
 Format(debits.amountoutstanding, 2) AS FEE_STILL_OWED,
 borrowerinfo.cardnumber AS FROM_PATRON,
 credits.note AS PAYMENT_NOTES,
 credits.date AS PAYMENT_DATE,
 If(items.barcode IS NULL, "DELETED", Upper(items.barcode)) AS FOR_ITEM,
 If(items.homebranch IS NOT NULL, items.homebranch, manualinvoiceinfo.branchcode) AS OWNED_BY,
 debits.date AS FEE_DATE,
 debits.description AS FEE_DESCRIPTION,
 debits.note AS FEE_NOTE
FROM
 accountlines credits
 JOIN account_offsets ON account_offsets.credit_id = credits.accountlines_id
 JOIN accountlines debits ON account_offsets.debit_id = debits.accountlines_id
 LEFT JOIN old_issues ON debits.issue_id = old_issues.issue_id
 LEFT JOIN items ON debits.itemnumber = items.itemnumber
 JOIN borrowers borrowerinfo ON borrowerinfo.borrowernumber = credits.borrowernumber
 LEFT JOIN borrowers manualinvoiceinfo ON manualinvoiceinfo.borrowernumber = debits.manager_id
WHERE
 Month(credits.date) = Month(Now() - INTERVAL 1 MONTH) AND
 Year(credits.date) = Year(Now() - INTERVAL 1 MONTH) AND
 credits.note='Paypal'
GROUP BY
 account_offsets.id
ORDER BY OWNED_BY