Uitleencijfers
Er is een rapport waarmee de uitleencijfers per maand kunnen worden uitgelezen. Per maand worden de cijfers verzameld van uitleningen, verlengingen en het aantal ingeleverde boeken (issue, renew, return).
SELECT DISTINCT
CONCAT('<strong>',s.type,'</strong>') AS ' ',
CONCAT('<em>',b.branchname,'</em>') AS 'Bibliotheek',
SUM(IF(MONTHNAME(s.datetime) = 'January', 1, 0)) AS 'Januari',
SUM(IF(MONTHNAME(s.datetime) = 'February', 1, 0)) AS 'Februari',
SUM(IF(MONTHNAME(s.datetime) = 'March', 1, 0)) AS 'Maart',
SUM(IF(MONTHNAME(s.datetime) = 'April', 1, 0)) AS 'April',
SUM(IF(MONTHNAME(s.datetime) = 'May', 1, 0)) AS 'Mei',
SUM(IF(MONTHNAME(s.datetime) = 'June', 1, 0)) AS 'Juni',
SUM(IF(MONTHNAME(s.datetime) = 'July', 1, 0)) AS 'Juli',
SUM(IF(MONTHNAME(s.datetime) = 'August', 1, 0)) AS 'Augustus',
SUM(IF(MONTHNAME(s.datetime) = 'September', 1, 0)) AS 'September',
SUM(IF(MONTHNAME(s.datetime) = 'October', 1, 0)) AS 'Oktober',
SUM(IF(MONTHNAME(s.datetime) = 'November', 1, 0)) AS 'November',
SUM(IF(MONTHNAME(s.datetime) = 'December', 1, 0)) AS 'December',
COUNT(*) AS 'Totaal',
CONCAT('<strong>',s.type,'</strong>') AS ' '
FROM statistics s, branches b
WHERE s.branch = b.branchcode
AND s.type IN ('renew', 'return', 'issue')
AND YEAR(s.datetime)=@CurrentYear:=<<Jaar>>
GROUP BY b.branchname, s.type
UNION
SELECT
NULL AS ' ',
NULL AS 'BRANCH',
NULL AS 'January',
NULL AS 'February',
NULL AS 'March',
NULL AS 'April',
NULL AS 'May',
NULL AS 'June',
NULL AS 'July',
NULL AS 'August',
NULL AS 'September',
NULL AS 'October',
NULL AS 'November',
NULL AS 'December',
NULL AS 'Totaal',
NULL AS ' '
UNION
SELECT DISTINCT
CONCAT('<strong>',c.type,'</strong>') AS ' ',
'<em>ALL BRANCHES</em>' AS 'BRANCH',
SUM(IF(MONTHNAME(c.datetime) = 'January', 1, 0)) AS 'Januari',
SUM(IF(MONTHNAME(c.datetime) = 'February', 1, 0)) AS 'Februari',
SUM(IF(MONTHNAME(c.datetime) = 'March', 1, 0)) AS 'Maart',
SUM(IF(MONTHNAME(c.datetime) = 'April', 1, 0)) AS 'April',
SUM(IF(MONTHNAME(c.datetime) = 'May', 1, 0)) AS 'Mei',
SUM(IF(MONTHNAME(c.datetime) = 'June', 1, 0)) AS 'Juni',
SUM(IF(MONTHNAME(c.datetime) = 'July', 1, 0)) AS 'Juli',
SUM(IF(MONTHNAME(c.datetime) = 'August', 1, 0)) AS 'Augustus',
SUM(IF(MONTHNAME(c.datetime) = 'September', 1, 0)) AS 'September',
SUM(IF(MONTHNAME(c.datetime) = 'October', 1, 0)) AS 'Oktober',
SUM(IF(MONTHNAME(c.datetime) = 'November', 1, 0)) AS 'November',
SUM(IF(MONTHNAME(c.datetime) = 'December', 1, 0)) AS 'December',
CONCAT('<strong>',COUNT(*),'</strong>') AS 'Totaal',
CONCAT('<strong>',c.type,'</strong>') AS ' '
FROM statistics c
WHERE c.type IN ('renew', 'return', 'issue')
AND YEAR(c.datetime) = @CurrentYear
GROUP BY c.type