koha-utilization-certificate-report

utlization_certificate

SELECT 
    GROUP_CONCAT(DISTINCT(f.ifund) ORDER BY f.ifund separator ", ") AS "Fund Source", 
    f.bibno AS "Bib. No.", 
    f.btitle AS "Item Title", 
    f.bauthor AS Author, 
    f.ted AS Edition, 
    f.tpub AS Publisher, 
    f.tisbn AS ISBN, 
    GROUP_CONCAT(f.idate separator ", ") AS "Date Acquired", 
    GROUP_CONCAT(f.ibarcode separator ", ") AS Barcodes, 
    GROUP_CONCAT(f.depcnt separator ", ") AS "Department/Copies", 
    SUM(f.iprice) AS "Total Cost", 
    SUM(f.icopies) AS Copies  
FROM 
    (SELECT  
        b.biblionumber AS bibno, 
        b.title As btitle, 
        b.author AS bauthor, 
        t.editionstatement AS ted, 
        t.publishercode AS tpub, 
        t.isbn AS tisbn,  
        GROUP_CONCAT(
            i.dateaccessioned separator ", "
        ) AS idate, 
        GROUP_CONCAT(
            i.barcode  separator ", "
        ) ibarcode, 
        CONCAT(IFNULL(h.lib, "N/A"), " (", COUNT(IFNULL(h.lib, "N/A")),  ")") AS depcnt, 
        sum(price) AS iprice, 
        count(i.biblionumber) AS icopies, 
        fnd.lib AS ifund
    FROM biblio b 
        LEFT JOIN biblioitems t USING(biblionumber)
        LEFT JOIN items i USING(biblionumber)
        LEFT JOIN (SELECT authorised_value,lib FROM `authorised_values` WHERE category="HOLDCAT") h ON (h.authorised_value=i.department)
        LEFT JOIN (SELECT authorised_value,lib FROM `authorised_values` WHERE category="FUNDSRC") fnd ON (fnd.authorised_value=i.fundsource)
    WHERE (i.fundsource LIKE  <<Funds|UC_FUNDSRC>>)
        AND (i.department LIKE <<Department|UC_HOLDCAT>>)
        AND i.dateaccessioned BETWEEN <<From (yyyy-mm-dd)|date>> AND <<To (yyyy-mm-dd)|date>>
    GROUP BY  b.biblionumber, h.lib) f
GROUP BY bibno