Koha-raporttikyselyitä

jyu-koha-queries.sql

/*=============================================================
    Koha-raporttikyselyitä
    Matti Lassila, Avoimen tiedon keskus, Jyväskylän yliopisto.

    Huomaa kyselyitä käyttäessäsi, että esimerkiksi URL-
    osoitteet sisältävät parametreina JYU:n Kohan raporttien ID:t
    joten kyselyitä ei voi useimmissa tapauksissa käyttää suoraan
    ilman muokkausta.
==============================================================*/



/*Kaikkien asiakkaiden yli kaksi kuukautta myöhässä olevat lainat. Ei sisällä myöhässä olevia kaukolainoja. */

SELECT
   DATE_FORMAT(issues.date_due,"%Y-%m-%d") as "Eräpäivä",
   borrowers.surname as 'Sukunimi',
   borrowers.borrowernotes AS 'Huomautus asiakastiedoissa',
   CONCAT(messages.message) AS 'Viestit (ent. Voyager popup)',
   CONCAT(
  '<a target="_blank" href=\"mailto:',
borrowers.email,'?Subject=Myöhässä%20oleva%20laina\">',
borrowers.email,
'</a>') as 'Sähköposti',
   CONCAT(
        '<a target="_blank" href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
        borrowers.borrowernumber,
        '\">',
        cardnumber,
        '</a>'
    ) AS Lainaaja,
borrowers.categorycode as 'Asiakasryhmä',
CONCAT(
        '<a target="_blank" href=\"/cgi-bin/koha/reports/guided_reports.pl?reports=24&phase=Run+this+report&sql_params=',
        borrowers.cardnumber,'&sql_params=',
        '\">',
        'Lainat',
        '</a>'
    ) AS ' '
 
FROM
    issues
INNER JOIN borrowers USING (borrowernumber)
LEFT JOIN (
SELECT
   borrowers.borrowernumber,
   GROUP_CONCAT(messages.message) as message
FROM
    messages
LEFT JOIN borrowers USING(borrowernumber)
GROUP BY borrowernumber

) AS messages ON messages.borrowernumber = borrowers.borrowernumber
WHERE datediff(CURRENT_DATE, date_due) > 60 AND datediff(CURRENT_DATE, date_due) < 180 AND borrowers.categorycode <> 'LIBRARY'
GROUP BY borrowers.borrowernumber
ORDER BY borrowers.surname ASC ,issues.date_due

/* Tietyn asiakkaan yli kaksi kuukautta myöhässä olevat lainat */

SELECT
       RTRIM(REGEXP_REPLACE(biblio.author,'[\.,]$','')) as 'Tekijä',
       CONCAT('<a target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblio.biblionumber, '&itemnumber=', issues.itemnumber, '#edititem', '\">', RTRIM(REGEXP_REPLACE(biblio.title,'[/,:]$','')), '</a>' ) AS 'Nimeke'
FROM borrowers 
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber) 
LEFT JOIN items ON (issues.itemnumber=items.itemnumber) 
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
LEFT JOIN authorised_values ON (authorised_values.authorised_value = items.permanent_location AND authorised_values.category = "LOC") 
WHERE DATEDIFF(CURRENT_DATE,issues.date_due) > '60' 
AND borrowers.cardnumber LIKE CONCAT( '%', <<Kortin viivakoodi>>, '%')
AND issues.date_due >= <<Lainojen eräpäivä alkaen|date>> 
ORDER BY borrowers.surname ASC, issues.date_due ASC

/* Signum-haku */

SELECT 
items.itype as "Aineistotyyppi",
items.itemcallnumber as "Signum",
CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?', 
       'biblionumber=', biblio.biblionumber, '&itemnumber=', items.itemnumber, 
       '\">', items.barcode, '</a>' ) AS 'Viivakoodi',
biblio.title as "Nimeke",
biblio.copyrightdate as "Julkaisuvuosi" 
  FROM items 
  LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) 
  LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) 
  WHERE items.itemcallnumber LIKE concat(<<Signum (katkaisee automaattisesti oikealta)>>, '%')
  ORDER BY items.itemcallnumber ASC

/* Varastotietueiden (holding-tietojen) haku */

SELECT 
holdings.callnumber as "Signum",
RTRIM(REGEXP_REPLACE(biblio.title,'[/,:]$','')) as 'Nimeke',
CONCAT('<a target="_blank" href=\"/cgi-bin/koha/catalogue/showmarc.pl?', 
       'holding_id=', holdings.holding_id, '&viewas=html', 
       '\">', 'Varastotietue', '</a>' ) AS ' ',
CONCAT('<a target="_blank" href=\" /cgi-bin/koha/cataloguing/addholding.pl?op=edit&biblionumber=', 
       biblio.biblionumber,'&holding_id=', holdings.holding_id, '#editholding', 
       '\">', 'Muokkaa', '</a>' ) AS ' ',
CONCAT('<a target="_blank" href=\" /cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
       biblio.biblionumber, 
       '\">', 'BIB', '</a>' ) AS '  '
  FROM holdings 
  LEFT JOIN biblio ON (holdings.biblionumber=biblio.biblionumber) 
  WHERE REPLACE(holdings.callnumber, ' ', '') LIKE concat(REPLACE(<<Signum (katkaisee automaattisesti oikealta)>>, ' ', ''), '%') AND holdings.deleted_on IS NULL
  ORDER BY holdings.callnumber ASC

/* Laskutetut mutta hyllyssä saatavana olevat niteet.*/

SELECT 
barcode as '',
biblio.title as 'Nimeke',
items.datelastseen as 'Käsitelty viimeksi',
homebranch as 'Sijainti',
CONCAT('<a target="_blank" href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem', 
       '&biblionumber=',items.biblionumber, '&itemnumber=',items.itemnumber,'&searchid=#edititem',
       '\">', "Niteen tiedot", '</a>' ) AS 'Nide',
CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',reserves.borrowernumber,'\">', borrowers.cardnumber, '</a>') AS 'Varattu asiakkaalle'
FROM items
LEFT JOIN biblio USING(biblionumber)
LEFT JOIN reserves USING(itemnumber)
LEFT JOIN borrowers USING(borrowernumber)
WHERE items.notforloan = "5" AND items.itemlost = "0" AND itemnumber NOT IN (
SELECT itemnumber FROM issues)

/* Yli kaksi viikkoa myöhässä olevat kaukolainat */

SELECT
    MAX(datediff(CURRENT_DATE, date_due)) AS 'Lainat myöhässä enimmillään',
    count(*) AS 'Lainoja myöhässä yht',
   borrowers.borrowernotes AS 'Huomautus asiakastiedoissa',
   CONCAT(messages.message) AS 'Viestit (ent. Voyager popup)',
   CONCAT(
        '<a target="_top" href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
        borrowers.borrowernumber,
        '\">',
        cardnumber,
        '</a>'
    ) AS Lainaaja,
  CONCAT(
  '<a target="_blank" href=\"mailto:',
borrowers.email,'?Subject=Myöhässä%20oleva%20laina\">',
borrowers.email,
'</a>') as 'Sähköposti'
FROM
    issues
INNER JOIN borrowers USING (borrowernumber)
LEFT JOIN (
SELECT
   borrowers.borrowernumber,
   GROUP_CONCAT(messages.message) as message
FROM
    messages
LEFT JOIN borrowers USING(borrowernumber)
GROUP BY borrowernumber

) AS messages ON messages.borrowernumber = borrowers.borrowernumber
WHERE datediff(CURRENT_DATE, date_due) > 14 AND borrowers.categorycode = 'LIBRARY'
GROUP BY borrowers.borrowernumber
ORDER BY MAX(datediff(CURRENT_DATE, date_due)) DESC, borrowers.borrowernumber, count(*)

/* Kaukopalveluasiakkaat. JYKDOKissa kaukopalveluakiakkaiden sukunimenä on määrämuotoinen hakuavain*/

SELECT 
	surname as 'Hakuavain',
	address as 'Kirjasto',
	firstname as 'Tarkenne',
	CONCAT(
        '<a target="_top" href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
        borrowers.borrowernumber,
        '\">',
        cardnumber,
        '</a>'
    ) AS Kortti
FROM borrowers 
WHERE categorycode = "LIBRARY" AND surname LIKE concat(<<Hakuavain (katkaisee automaattisesti oikealta)>>, '%')
ORDER BY surname

/* Voimassa oleva varaustilanne*/

SELECT DISTINCT
	biblio.title as 'Nimeke',
	COUNT(reserves.waitingdate) as 'Odottaa noutoa',
	COUNT(reserves.biblionumber) AS 'Yht',
	CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', reserves.biblionumber, '\">Tietue</a>' ) AS ''
FROM
	reserves
LEFT JOIN biblio USING(biblionumber)
GROUP BY
	reserves.biblionumber,
	biblio.title
ORDER BY Yht DESC

/* Varausten odotusaikaan liittyviä tunnuslukuja */

SELECT
	biblio.title as 'Nimeke',
	biblio.copyrightdate as 'Vuosi',
	itemcount.items_in_collection as 'Niteitä kokoelmassa',
	EXTRACT(YEAR FROM old_reserves.reservedate) as 'Varausvuosi',
	ROUND(AVG(DATEDIFF(old_reserves.waitingdate,old_reserves.reservedate)),0)  as 'Keskimääräinen odotusaika päiviä ennen saapumista',
	ROUND(AVG(DATEDIFF(old_reserves.cancellationdate,old_reserves.reservedate)),0)  as 'Keskimääräinen odotusaika päiviä ennen perumista',
	cancelled_count.cancelled_reserves AS 'Peruttuja varauksia',
	CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', old_reserves.biblionumber, '\">Tietue</a>' ) AS ''
	FROM
	old_reserves
INNER JOIN biblio USING(biblionumber)
LEFT JOIN items USING(biblionumber)
LEFT JOIN (
	SELECT old_reserves.biblionumber,
	COUNT(old_reserves.biblionumber) AS cancelled_reserves 
	FROM old_reserves 
	WHERE old_reserves.cancellationdate IS NOT NULL
	GROUP BY old_reserves.biblionumber) AS cancelled_count ON cancelled_count.biblionumber = old_reserves.biblionumber
LEFT JOIN (
SELECT biblionumber, COUNT(items.biblionumber) AS items_in_collection FROM items GROUP BY items.biblionumber
) as itemcount ON itemcount.biblionumber = old_reserves.biblionumber

/* Nidehistoria. Näyttää niteen lainaushistorian Kohan käyttöönotosta alkaen */

SELECT
    CONCAT('<a href=\"/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowers.borrowernumber,'\">', borrowers.cardnumber, '</a>') AS 'Kortti',
    borrowers.surname as 'Sukunimi',
    borrowers.firstname as 'Etunimi',
	CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?', 
       'biblionumber=', biblio.biblionumber, '&itemnumber=', items.itemnumber, 
       '\">', items.barcode, '</a>' ) AS 'Nide',
	old_issues.issuedate as 'Lainattu',
	DATE_FORMAT(old_issues.date_due,'%Y-%m-%d') as 'Eräpäivä',
	old_issues.returndate as 'Palautettu'
FROM old_issues
RIGHT JOIN items USING(itemnumber)
RIGHT JOIN biblio ON items.biblionumber = biblio.biblionumber
LEFT JOIN borrowers USING(borrowernumber)
WHERE items.barcode = <<Viivakoodi>>

/* Tänään palautetut*/

SELECT
	RTRIM(REGEXP_REPLACE(biblio.title,'[/,:]$','')) as 'Nimeke',
	CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?', 
       'biblionumber=', biblio.biblionumber, '&itemnumber=', items.itemnumber, 
       '\">', items.barcode, '</a>' ) AS 'Viivakoodi',
	items.datelastseen as 'Käsitelty viimeksi',
	  CONCAT(
        '<a target="_blank" href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
        reserver.borrowernumber,
        '\">',
        reserver.cardnumber,
        '</a>'
    ) AS 'Varattu asiakkaalle',
	items.holdingbranch as 'Oletussijainti',
	items.itype as 'Tyyppi',
	old_issues.returndate as 'Palautusaika'
FROM
	items
LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
LEFT JOIN issues ON items.itemnumber = issues.itemnumber
LEFT JOIN reserves ON reserves.itemnumber = items.itemnumber
LEFT JOIN borrowers AS borrower ON borrower.borrowernumber = issues.borrowernumber
LEFT JOIN borrowers AS reserver ON reserver.borrowernumber = reserves.borrowernumber
LEFT JOIN old_issues ON old_issues.itemnumber = items.itemnumber AND DATEDIFF(old_issues.returndate,DATE(items.datelastseen)) = 0 
WHERE DATEDIFF(old_issues.timestamp, CURRENT_DATE) = 0 AND items.itype != 'SP' AND items.itype !='VARASTO180' AND items.holdingbranch !="CHYDENIUS"
ORDER BY old_issues.timestamp DESC;

/* Niteiden tilan pikalistaus*/
SELECT
	biblio.title as 'Nimeke',
	CONCAT('<a href=\"/cgi-bin/koha/catalogue/moredetail.pl?', 
       'biblionumber=', biblio.biblionumber, '&itemnumber=', items.itemnumber, 
       '\">', items.barcode, '</a>' ) AS 'Viivakoodi',
	items.datelastborrowed as 'Lainattu viimeksi',
	items.datelastseen as 'Käsitelty viimeksi',
	  CONCAT(
        '<a target="_blank" href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
        borrower.borrowernumber,
        '\">',
        borrower.cardnumber,
        '</a>'
    ) AS 'Lainassa asiakkaalla',
	  CONCAT(
        '<a target="_blank" href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
        reserver.borrowernumber,
        '\">',
        reserver.cardnumber,
        '</a>'
    ) AS 'Varattu asiakkaalle',
	items.holdingbranch as 'Oletussijainti',
	items.itype as 'Tyyppi',
	items.itemlost_on as 'Merkitty kadonneeksi',
	old_issues.returndate as 'Palautettu viimeksi'
FROM
	items
LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
LEFT JOIN issues ON items.itemnumber = issues.itemnumber
LEFT JOIN reserves ON reserves.itemnumber = items.itemnumber
LEFT JOIN borrowers AS borrower ON borrower.borrowernumber = issues.borrowernumber
LEFT JOIN borrowers AS reserver ON reserver.borrowernumber = reserves.borrowernumber
LEFT JOIN old_issues ON old_issues.itemnumber = items.itemnumber AND DATEDIFF(old_issues.returndate,DATE(items.datelastseen)) = 0 
WHERE biblio.biblionumber = <<BIBID>>
ORDER BY items.itype, items.datelastseen DESC