SQL Reports Accounting

From Koha Wiki
Jump to navigation Jump to search

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
Retrieved from "https://wiki.koha-community.org/w/index.php?title=SQL_Reports_Accounting&oldid=32538"