SQL Reports

Detailed Accession Register 

SELECT 
  oo.dateaccessioned AS 'Date',
  oo.barcode AS 'Acc. No.',
  ooo.title AS 'Title',
  ExtractValue(bm.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') AS 'Subtitle',
  ooo.author AS 'Author/Editor',
  CONCAT_WS(' , ', o.editionstatement, oo.enumchron) AS 'Ed./Vol.',
  CONCAT_WS(' ', o.place, o.publishercode) AS 'Place & Publisher',
  ooo.copyrightdate AS 'Year',
  o.pages AS 'Page(s)',
  ooooooo.name AS 'Source',
  oo.itemcallnumber AS 'Class No./Book No.',
  CONCAT_WS(', ₹', CONCAT(' ', ooooo.symbol, oooo.listprice), oooo.rrp_tax_included) AS 'Cost',
  CONCAT_WS(' , ', oooooo.invoicenumber, oooooo.shipmentdate) AS 'Bill No. & Date',
  '' AS 'Withdrawn Date',
  '' AS 'Remarks'
FROM 
  biblioitems o
  LEFT JOIN items oo ON oo.biblioitemnumber = o.biblioitemnumber
  LEFT JOIN biblio ooo ON ooo.biblionumber = o.biblionumber
  LEFT JOIN biblio_metadata bm ON bm.biblionumber = ooo.biblionumber
  LEFT JOIN aqorders oooo ON oooo.biblionumber = o.biblionumber
  LEFT JOIN currency ooooo ON ooooo.currency = oooo.currency
  LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid = oo.booksellerid
  LEFT JOIN aqbooksellers ooooooo ON ooooooo.id = oo.booksellerid
WHERE 
  CAST(oo.barcode AS UNSIGNED) BETWEEN <<Accession Number>> AND <<To Accession Number>>
GROUP BY 
  oo.barcode
ORDER BY 
  oo.barcode ASC
 

--------------------------

Patrons with most checkouts in date range

select concat(b.surname,', ',b.firstname) as name, 
       count(s.borrowernumber) as checkouts 
from statistics s 
left join borrowers b 
using (borrowernumber) 
where s.datetime between <<Top checkouts between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
group by s.borrowernumber 
order by count(s.borrowernumber) desc 
limit 20

--------------------------

All Checked Out Books

SELECT c.date_due, p.surname, p.firstname,
       p.phone, p.email, b.title, b.author,
       i.itemcallnumber, i.barcode, i.location 
FROM issues c
LEFT JOIN items i ON (c.itemnumber=i.itemnumber) 
LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber) 
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) 
ORDER BY c.date_due ASC 

--------------------------

 New Books (Date wise List of Books)

SELECT 
items.dateaccessioned 'Accessioned Date',biblio.biblionumber 'Record No.',items.stocknumber 'Accession No.',items.barcode 'Barcode',items.itemcallnumber 'Call Number',biblio.author 'Author',biblio.title 'Title',biblio.subtitle    'subtitle',biblioitems.publishercode 'Publication Detail',biblio.copyrightdate 'Year',biblioitems.place 'Place',items.ccode 'Collection Code',items.itype 'Type',items.location'Location',biblioitems.collectiontitle'Subject' FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN
<<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
ORDER BY items.dateaccessioned DESC 

--------------------------

All Patron List

SELECT borrowerNumber, cardNumber, firstname, initials, surname, othernames, phone, phonepro, mobile, fax, emailpro, categorycode, sort2
FROM borrowers

--------------------------

Who Modify Patron Record

SELECT 
    action_logs.timestamp AS "Date/Time",
    staff.firstname AS "Staff First Name",
    borrowers.cardnumber AS "Patron Card Number",
    borrowers.firstname AS "Patron First Name",
    action_logs.action AS "Action",
    action_logs.info AS "Details"
FROM action_logs
JOIN borrowers ON borrowers.borrowernumber = action_logs.object
LEFT JOIN borrowers AS staff ON staff.borrowernumber = action_logs.user
WHERE action_logs.module = 'MEMBERS'
  AND borrowers.cardnumber = <<Enter Patron Card Number>>
ORDER BY action_logs.timestamp DESC  

--------------------------

Check in History

SELECT 
    items.barcode AS "Barcode",
    biblio.title AS "Title",
    borrowers.cardnumber AS "Card Number",
    borrowers.firstname AS "Patron Name",
    old_issues.issuedate AS "Date of Issue",
    old_issues.returndate AS "Date of Return"
FROM 
    old_issues
LEFT JOIN items ON old_issues.itemnumber = items.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
LEFT JOIN borrowers ON old_issues.borrowernumber = borrowers.borrowernumber
WHERE 
    DATE(old_issues.returndate) BETWEEN <<Start Date|date>> AND <<End Date|date>>
ORDER BY 
    old_issues.returndate
 

--------------------------

Pending Books

SELECT
    issues.borrowernumber AS "Patron Number",
    biblio.title AS "Title",
    items.barcode AS "Barcode",
    items.itemcallnumber AS "Call Number",
    issues.issuedate AS "Issue Date",
    issues.date_due AS "Due Date"
FROM
    issues
LEFT JOIN items ON issues.itemnumber = items.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
ORDER BY issues.issuedate DESC  

--------------------------