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
You can’t perform that action at this time.