Acquisition Reports

From Koha Wiki
Jump to navigation Jump to search

Reports about anything to do with acquisitions

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Return to the SQL Reports Library

Orders in Date Range

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show order information for a specific date period.
  • Status: Complete. Broken in Koha 3.16. aqorders.notes field was replaced with aqorders.order_internalnote and aqorders.order_vendornote
SELECT v.name AS vendor, b.title AS 'book title', 
       format(o.listprice,2) AS 'list price', 
       format(o.unitprice,2) AS 'actual price',
       ba.basketname, o.notes 
FROM aqorders o 
LEFT JOIN aqbasket ba USING (basketno) 
LEFT JOIN aqbooksellers v ON (v.id = ba.booksellerid) 
LEFT JOIN biblio b USING (biblionumber) 
WHERE o.entrydate BETWEEN <<Ordered BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

All Orders

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show the information for every order in the system.
  • Status: Complete
SELECT v.name AS Vendor, concat(c.basketname, ' (', c.basketno, ')') AS Basket,
       c.creationdate AS Ordered,  concat(p.firstname, ' ', 
       p.surname) AS 'Managed by', b.title, 
       ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS subtitle, 
       b.author, bi.isbn, bi.publishercode, 
       o.rrp AS RRP, o.ecost AS EST, o.quantity AS Qty,
       format(o.listprice*o.quantity,2) AS Total, f.budget_name AS Fund 
FROM aqorders o 
LEFT JOIN aqbudgets f USING (budget_id) 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN biblioitems bi USING (biblionumber) 
LEFT JOIN aqbasket c USING (basketno) 
LEFT JOIN aqbooksellers v ON (c.booksellerid=v.id) 
LEFT JOIN borrowers p ON (c.authorisedby=p.borrowernumber) 
ORDER BY v.name, c.basketno ASC

Items ordered vs. received

  • Developer: Barton Chittenden, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show the number of items ordered vs. received for a given vendor.
  • Status: Complete
SELECT
    aqbasket.basketno, 
    aqbasket.basketname, 
    aqbasket.creationdate, 
    aqbasket.closedate, 
    sum(aqorders.quantity) AS 'Items ordered', 
    sum(aqorders.quantityreceived) AS 'Items received',
    sum(aqorders.quantity) - sum(aqorders.quantityreceived) AS difference
FROM
    aqbooksellers
    INNER JOIN aqbasket ON (aqbooksellers.id = aqbasket.booksellerid) 
    INNER JOIN aqorders USING (basketno) 
WHERE
    aqbooksellers.id = 91
    AND aqbasket.creationdate BETWEEN <<Ordered BETWEEN|date>> AND <<and|date>>
GROUP BY basketno
ORDER BY aqbasket.creationdate

Ledger

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Show's a ledger of all items ordered
  • Status: Complete. Works on Koha 3.16. Changed biblioitemnumber to biblionumber in the join on items.
SELECT b.name AS vendor, i.itype, p.budget_branchcode AS branch, k.basketno,  
       o.entrydate AS 'order date', format(o.listprice,2) AS 'list price', 
       format(o.unitprice,2) AS 'unit price', o.quantity, 
       format(o.totalamount,2) AS 'total amount', o.datereceived AS 'date received' 
FROM aqbasket k 
LEFT JOIN aqbooksellers b ON (k.booksellerid=b.id) 
LEFT JOIN aqorders o USING (basketno)
LEFT JOIN items i USING (biblionumber)
LEFT JOIN aqbudgets p USING (budget_id)

Titles ordered in a Fund

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Titles ordered in a specific fund (will search for any part of the fund code)
  • Status: Complete
SELECT b.title, b.author 
FROM biblio b 
LEFT JOIN aqorders a 
USING (biblionumber) 
LEFT JOIN aqbudgets aq 
USING (budget_id) 
WHERE a.datereceived BETWEEN <<Date received BETWEEN (yyyy-mm-dd)|date>> 
          AND <<and (yyyy-mm-dd)|date>> AND
          aq.budget_code LIKE concat(<<Budget code>>,'%')

Amount Encumbered

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Total encumbered against each budget
  • Status: Complete
SELECT b.budget_name, format(sum(b.budget_amount),2) AS 'amount budgeted',
          format(sum(o.listprice*o.quantity),2) AS 'amount encumbered' 
FROM aqorders o 
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NULL 
GROUP BY b.budget_name

Amount Spent

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Total spent against each budget
  • Status: Complete
SELECT b.budget_name, format(sum(b.budget_amount),2) AS 'amount budgeted',
          format(sum(o.listprice*o.quantity),2) AS 'amount spent' 
FROM aqorders o 
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NOT NULL 
GROUP BY b.budget_name

Tax Receipt

  • Developer: Paul A., Naval Marine Archive
  • Module: Acquisitions
  • Purpose: To allow a Charity to produce an "inventory" for Tax Receipting purposes; selection by Donor and Accession date.
  • Status: Complete
SELECT
items.barcode AS Barcode,items.dateaccessioned AS 'Acc Date', 
biblio.title AS Title, biblio.author AS Author,biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Year, 
biblioitems.editionstatement AS Edition,
items.price AS FMV,
ExtractValue(more_subfields_xml,
'/collection/record/datafield[\@tag=\"999\"]/subfield[\@code=\"x\"]') AS 'Condition'
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE
items.booksellerid = <<Pick your donor>>
AND DATE(items.dateaccessioned) BETWEEN <<Accessioned BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
 
ORDER BY biblio.author ASC

List of Biblio Numbers on an Invoice

  • Developer: Joseph Alway
  • Module: Acquisitions
  • Purpose: This report will give a list of the biblionumbers on an invoice. Which we can then use to export the records from Koha using the export data tool.
  • Status: Complete
SELECT biblionumber AS 'Biblio Number'
FROM aqorders
LEFT JOIN aqinvoices USING (invoiceid)
WHERE invoicenumber=<<Invoice Number>>

Orders with like data in a subfield of a field

  • Developer: Joseph Alway
  • Module: Acquisitions
  • Purpose: Displays the Biblionumber, Title, Author, Basket Name, List Price, Budget Code, and Vendor of All Orders with a like value in a subfield of a field.
  • Status: Complete
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumber, biblio.title,
    biblio.author, aqbasket.basketname AS PO, format(aqorders.listprice,2) AS 'List Price', aqbudgets.budget_code AS Fund, aqbooksellers.name AS Vendor
FROM
    biblioitems
    JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
    JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
    LEFT JOIN aqorders ON (biblioitems.biblionumber = aqorders.biblionumber)
    LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
    LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id)
    LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
WHERE
    ExtractValue(metadata, "//datafield[@tag=<< Field (XXX)>>]/subfield[@code=<<Subfield(Y)>>]")
        LIKE <<Search Term (USE % AS wildcard)>>
    AND biblio.biblionumber = aqorders.biblionumber

Orders received in a date range

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Orders received in a date range
  • Status: Complete
SELECT t.title, b.name AS vendor, p.budget_name AS fund, format(o.listprice,2) AS 'list price',
format(o.unitprice,2) AS 'unit price', o.quantity,
format((o.totalamount*o.quantity),2) AS 'total amount', o.datereceived AS 'date received', o.sort1
FROM aqbasket k
LEFT JOIN aqbooksellers b ON (k.booksellerid=b.id)
LEFT JOIN aqorders o USING (basketno)
LEFT JOIN items i USING (biblionumber)
LEFT JOIN aqbudgets p USING (budget_id)
LEFT JOIN biblio t USING (biblionumber)
WHERE o.datereceived BETWEEN <<Received BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>

Orders not received

  • Developer: Joseph Alway
  • Module: Acquisitions
  • Purpose: List orders that haven't been received.
  • Status: Complete
SELECT
    aqbs.name AS 'Vendor',
    CONCAT('<a href=\"/cgi-bin/koha/acqui/basket.pl?basketno=',basketno,'\">',basketno,'</a>') AS Basket,
    CONCAT('<a href=\"/cgi-bin/koha/acqui/neworderempty.pl?ordernumber=',a.ordernumber,'\">',a.ordernumber,'</a>') AS 'Order',
    CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',a.biblionumber,'\">',a.biblionumber,'</a>') AS Biblio,
    b.title AS Title, b.author AS Author, format(a.listprice,2) AS 'List Price', format(a.discount,2) AS 'Discount', format(a.ecost,2) AS 'Estimated Cost',
    u.budget_code AS 'Fund Code', u.budget_name AS 'Fund Name', a.order_internalnote AS 'Order Notes', a.entrydate AS 'Date Ordered'
FROM
    aqorders a
    LEFT JOIN biblio b
        USING (biblionumber) 
    LEFT JOIN aqinvoices i
        USING (invoiceid)
    LEFT JOIN aqbudgets u
        USING (budget_id)
    LEFT JOIN aqbasket aqb
        USING (basketno)
    LEFT JOIN aqbooksellers aqbs
        ON aqbs.id=aqb.booksellerid
WHERE
    a.ordernumber IS NOT NULL
    AND a.datecancellationprinted IS NULL
    AND a.datereceived IS NULL
ORDER BY u.budget_name ASC

List of Order given to specific Vendor during current active budget

  • Developer: Vinod Kumar Mishra
  • Module: Acquisitions
  • Purpose: It gives detail about all the titles and order given to specific vendor during current active financial year.
  • Status: Complete tested on Koha 16.11.07
SELECT bb.name AS 'Vendor Name',b.basketname AS 'Basket Name', b.creationdate AS 'Basket Creation Date',
r.currency AS 'Currency', r.listprice AS 'Price', r.quantity AS 'Quantity', r.rrp AS 'Unit Price',
r.ecost AS 'Discounted Price',  format(r.ecost*r.quantity,2) AS 'Total Price',
r.orderstatus AS 'Status', bbb.budget_code AS 'Fund Code'
 
FROM aqorders r
 
LEFT JOIN aqbasket b ON b.basketno=r.basketno
LEFT JOIN aqbooksellers bb ON bb.id=b.booksellerid
LEFT JOIN aqbudgets bbb ON bbb.budget_id=r.budget_id
LEFT JOIN aqbudgetperiods bbbb ON bbbb.budget_period_id=bbb.budget_period_id
 
WHERE bb.id = <<Koha Vendor Code>> AND bbbb.budget_period_active LIKE '1'

List of Pending Books Requested/Suggested for purchase

  • Developer: Vinod Kumar Mishra
  • Module: Acquisitions
  • Purpose: This will provide list of books which is suggested by various users and not yet approved. It contains information about suggester name, His ID, Status, Author, Title, Year, Publisher, ISBN, Quantity, Currency, Price, Total price and Budget head.
  • Status: Complete tested on Koha 16.11.07
  • Note: You may replace 'ASKED' with Available, Ordered etc. status from last line (WHERE STATUS LIKE 'ASKED') of this query to get the report accordingly.
SELECT bb.surname 'Suggested By', bb.cardnumber 'Member ID', r.STATUS, r.author 'Author',
r.title 'Title', r.copyrightdate 'Year', r.publishercode 'Publisher', r.isbn 'ISBN',
r.quantity 'Quantity',r.currency 'Currency', r.price 'Price', r.total 'Total Price',
b.budget_code 'Department'
 
FROM suggestions r
 
LEFT JOIN aqbudgets b ON r.budgetid=b.budget_id
LEFT JOIN borrowers bb ON r.suggestedby=bb.borrowernumber
 
WHERE STATUS LIKE 'ASKED'

Single Basket Order List

  • Developer: Vinod Kumar Mishra Got help from Mr. Monirul Purkait
  • Module: Acquisitions
  • Purpose: Provide much details of the title entered in a particular basket like requester, fund, vendor etc.
  • Status: Complete tested on Koha 16.11.07
SELECT 
    v.name AS Vendor,
    concat(c.basketname, ' (', c.basketno, ')') AS Basket,
    c.creationdate AS Ordered,
    concat(pp.firstname, ' ',pp.surname, ' (', pp.cardnumber, ')') AS 'Requested By',
    concat(p.firstname, ' ',  p.surname) AS 'Managed by', 
    b.title AS 'Title', 
    ExtractValue(bi.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS 'Subtitle', 
    b.author AS 'Author/Editor', bi.isbn AS 'ISBN', bi.publishercode AS 'Publisher', 
    o.currency AS 'Currency',o.listprice AS 'Original Price', o.rrp AS 'Price (INR)',o.discount AS 'Discount %', o.ecost AS 'Discounted Price', o.quantity AS 'Copies',
    format(o.ecost*o.quantity,2) AS Total, f.budget_name AS 'Fund/Dept. Name' 
FROM aqorders o
LEFT JOIN aqbudgets f USING (budget_id) 
LEFT JOIN biblio b USING (biblionumber) 
LEFT JOIN biblioitems bi USING (biblionumber) 
LEFT JOIN aqbasket c USING (basketno) 
LEFT JOIN aqbooksellers v ON (c.booksellerid=v.id) 
LEFT JOIN borrowers p ON (c.authorisedby=p.borrowernumber) 
LEFT JOIN suggestions s ON (b.biblionumber = s.biblionumber)
LEFT JOIN borrowers pp ON (s.suggestedby=pp.borrowernumber)
 
WHERE
 
    b.biblionumber IS NOT NULL                
    AND c.basketname LIKE CONCAT( '%', <<Basketname>>, '%')
 
ORDER BY v.name, c.basketno ASC

List of all Active Vendors

  • Developer: Vinod Kumar Mishra , Andrew Fuerste-Henry, ByWater Solutions
  • Module: Acquisitions
  • Purpose: Provide list of all active Vendors in Koha along with contact details and Koha ID.
  • Status: Updated to work on 20.05
SELECT r.id 'Koha ID', r.name'Firm Name',i.name 'Contact Person',i.phone 'Phone/Mobile', i.email 'Email',r.postal 'Postal Address',r.fax 'Fax'
 
FROM aqbooksellers  r
LEFT JOIN aqcontacts i ON (i.booksellerid=r.id)
 
WHERE r.active=1

Items list by accession number range and sorted by call number

  • Developer: Vinod Kumar Mishra
  • Module: Acquisitions
  • Purpose: Display/Print of New Arrivals list Sorted by call number (Subject wise) may be useful for cover flow and public reports.
  • Status: Complete tested on Koha 16.11.07
  • Note: Replace 'XXXX' with your range of barcode
SELECT biblio.author,biblio.title,items.itemcallnumber,items.barcode,isbn
 
FROM items
 
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
 
WHERE cast(items.barcode AS UNSIGNED) BETWEEN 'XXXX' AND 'XXXX' ORDER BY items.itemcallnumber ASC

Total amount of order given to each Vendor in active budget year

  • Developer: Vinod Kumar Mishra
  • Module: Acquisitions
  • Purpose: This report gives you data about the total amount of order given to a particular vendor in active budget year. It will help in proper distribution of orders among the vendors.
  • Status: Complete tested on Koha 16.11.07
SELECT  b.booksellerid 'Vendor ID', bb.name AS 'Vendor Name', SUM(r.ecost * r.quantity) AS 'Total Amount'
 
FROM aqorders r
 
LEFT JOIN aqbasket b ON b.basketno=r.basketno
LEFT JOIN aqbooksellers bb ON bb.id=b.booksellerid
LEFT JOIN aqbudgets bbb ON bbb.budget_id=r.budget_id
LEFT JOIN aqbudgetperiods bbbb ON bbbb.budget_period_id=bbb.budget_period_id
 
WHERE NOT r.orderstatus='cancelled' AND bbbb.budget_period_active LIKE '1'
 
GROUP BY bb.id

Detail Accession Register Report/Format

  • Developer: Vinod Kumar Mishra
  • Module: Acquisitions
  • Purpose: This report will help all Libraries to print the accession record of a book and maintain the register especially as per Indian GFR2017 prescribed accession register format.
  • Status: Complete tested on Koha 16.11.11
SELECT oo.dateaccessioned AS 'Date',
  oo.barcode AS 'Acc. No.',
  ooo.title AS 'Title',
  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 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

All Invoice details received in particular financial/budget year

  • Developer: Vinod Kumar Mishra
  • Module: Acquisitions
  • Purpose: This report will help Libraries to print all Invoice details received in a particular financial/budget year. It provides number of unique titles as well as total items received along with other invoice details.
  • Status: Complete tested on Koha 16.11.07
SELECT rrr.name 'Vendor/Firm Name', r.basketname 'Basket/Order Number',rrrr.invoicenumber 'Invoice Number',rrrr.billingdate 'Bill Date',
 round(SUM(case when rrrr.shipmentcost IS NOT NULL THEN rr.unitprice_tax_included*rr.quantityreceived+rrrr.shipmentcost
 ELSE rr.unitprice_tax_included*rr.quantityreceived END),2)'Total Invoice Amount',rrrr.closedate 'Invoice Close Date',
 COUNT(DISTINCT rr.biblionumber)'Unique Title',SUM(rr.quantityreceived) 'Quantity Received'

FROM aqbasket r

left join aqorders rr on rr.basketno=r.basketno
left join aqbooksellers rrr on rrr.id=r.booksellerid
left join aqinvoices rrrr on rrrr.invoiceid=rr.invoiceid
left join aqbudgets rrrrr on rrrrr.budget_id=rr.budget_id


where rr.orderstatus LIKE 'complete' AND rrrrr.budget_period_id=<<Koha Budget Period ID>>

group by rr.basketno

order by rrr.name ASC

Summary of invoices paid on [date] to [vendor]

  • Developer: Sarah Cornell and Bywater staff
  • Module: Acquisitions
  • Purpose: Summarizes invoice activity on a date. Enter % for all vendors.
  • Status: Completed
  • Works with: 17.11
SELECT 
CONCAT('<a href="/cgi-bin/koha/acqui/invoice.pl?invoiceid=',invoiceid,'">view</a>') AS 'view invoice',
aqinvoices.invoicenumber, 
sub2.*,
CAST((sub2.items_total+sub2.shipment_cost) AS DECIMAL(18,2)) AS 'invoice total'

FROM
(
SELECT  invoiceid,
     sub1.vendor, 
     sub1.invoice_date,
     sub1.date_paid,
     CAST(SUM(sub1.ordertotal_hide) AS DECIMAL(18,2)) AS 'items_total',
     sub1.shipment_cost

FROM 
(
SELECT  invoiceid, 
     aqbooksellers.name AS 'vendor',  
     aqinvoices.billingdate AS 'invoice_date',
     aqinvoices.closedate AS 'date_paid',
     (unitprice*quantityreceived) AS 'ordertotal_hide',
     CAST(IFNULL(aqinvoices.shipmentcost,0) AS DECIMAL(18,2)) AS 'shipment_cost'
FROM aqorders
LEFT JOIN aqinvoices USING (invoiceid)
LEFT JOIN aqbooksellers ON booksellerid=aqbooksellers.id
) AS sub1

GROUP BY invoiceid
) AS sub2

LEFT JOIN aqinvoices USING (invoiceid)
WHERE DATE(aqinvoices.closedate)=<<Date closed|date>>
AND sub2.vendor LIKE CONCAT( '%', <<Enter part of vendor name>>, '%')

List of invoices in date range in a fund

  • Developer: Caroline Cyr La Rose, inLibro
  • Module: Acquisitions
  • Purpose: Gives a list of all invoices with total for a particular fund
  • Status: Completed
  • Works with: developped on 19.05
SELECT
    budget_name AS "Fund",
    invoicenumber AS "Invoice number",
    aqbooksellers.name AS "Vendor",
    ROUND(SUM((unitprice*quantityreceived)), 2) AS "Total"
FROM
    aqinvoices
    LEFT JOIN aqbooksellers ON (aqinvoices.booksellerid = aqbooksellers.id)
    LEFT JOIN aqorders USING (invoiceid)
    LEFT JOIN aqbudgets USING (budget_id)
WHERE budget_code = <<Fund code>>
    AND datereceived IS NOT NULL
    AND billingdate BETWEEN <<Between|date>> AND <<and|date>>
GROUP BY invoiceid

Avg days from receipt to available

  • Developer: Bywater Solutions
  • Module: Acquisitions
  • Purpose: Specify the time period the item was first available.Accession date variant
  • Status: Completed
  • Works with: 19.05
SELECT 'Average',SUM(days_from_receipt)/COUNT(*) AS Average_days,"" AS "First activity","" AS "Accessioned"
FROM (
SELECT DATEDIFF(date(MIN(datetime)),dateaccessioned)  AS days_from_receipt
FROM items 
JOIN statistics USING (itemnumber) 
WHERE dateaccessioned IS NOT NULL AND statistics.type IN ( 'return','localuse','issue')   AND dateaccessioned > '2016-04-04'
GROUP BY itemnumber
HAVING date(min(datetime)) BETWEEN <<Start date|date>> AND <<End date|date>>
) foo
UNION ALL
SELECT itemnumber,DATEDIFF(date(MIN(datetime)),dateaccessioned), min(date(datetime)), dateaccessioned AS days_from_receipt
FROM items 
JOIN statistics USING (itemnumber) 
WHERE dateaccessioned IS NOT NULL AND statistics.type IN ( 'return','localuse','issue')   AND dateaccessioned > '2016-04-04'
GROUP BY itemnumber
HAVING date(min(datetime)) BETWEEN <<Start date|date>> AND <<End date|date>>

Suggestion Report with selection by Status

  • Developer: Joseph Alway
  • Module: Acquisitions
  • Purpose: Displays the Title, Author, ©/Pub Date, Fund, Suggested By, Date Requested, and Status of suggestions based on the custom authorized values in CUSTOM_REPORT_SUGGESTIONS_STATUS.
  • Status: Complete
  • Works with: 22.11
  • Note: Must Create Custom Authorized Values in Custom Authorized Values Category to function properly.
==================
START OF SQL CODE:
==================
SELECT sug.title AS 'Title',
       sug.author AS 'Author',
       sug.copyrightdate AS '©/Pub Date',
       aqb.budget_code AS 'Fund',
       CONCAT(bo.firstname,' ',bo.surname) AS 'Suggested By',
       sug.suggesteddate AS 'Date Requested',
       REPLACE(CONCAT(UCASE(LEFT(sug.STATUS, 1)), LCASE(SUBSTRING(sug.STATUS, 2))), 'Asked', 'Pending') AS 'Status'
FROM suggestions AS sug
LEFT JOIN borrowers AS bo ON (sug.suggestedby=bo.borrowernumber)
LEFT JOIN aqbudgets AS aqb ON (sug.budgetid=aqb.budget_id)
WHERE sug.STATUS LIKE <<Status|CUSTOM_REPORT_SUGGESTIONS_STATUS>>
==================
END OF SQL CODE
==================

===============================
Authorized Value Category Name:
===============================
CUSTOM_REPORT_SUGGESTIONS_STATUS

==========================
Authorized Values Details: (These are entered under the CUSTOM_REPORT_SUGGESTIONS_STATUS authorized value category.)
==============================================
Authorized Value,Description,Description(OPAC)
==============================================
%,1| All,1| All
Accepted,3| Accepted,3| Accepted
ASKED,2| Pending,2| Pending
Checked,4| Checked,4| Checked
Ordered,5| Ordered,5| Ordered
Rejected,6| Rejected,6| Rejected
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Acquisition_Reports&oldid=38504"