Report Name: All Invoice details received in particular financial/budget year

    Developer: Vinod Kumar Mishra (http://mishravk.com/)
    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(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