Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Koha-raporttikyselyitä
/*=============================================================
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment