Skillnad mellan versioner av "Gallringslista Bibliotek Mellansjö"
Hoppa till navigering
Hoppa till sök
(Skapade sidan med '<pre> SELECT biblio.author AS Författare, CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\" target="_blank">', biblio.title, '</a...') |
Klas (diskussion | bidrag) |
||
Rad 1: | Rad 1: | ||
<pre> | <pre> | ||
− | SELECT biblio.author AS Författare, | + | SELECT biblio.copyrightdate As 'Utgivning', |
+ | biblio.author AS Författare, | ||
CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\" target="_blank">', biblio.title, '</a>' ) AS Titel, | CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\" target="_blank">', biblio.title, '</a>' ) AS Titel, | ||
− | items.itemcallnumber AS | + | items.itemcallnumber AS Signum, |
− | items.ccode AS | + | items.ccode AS Avd, |
− | itemtypes.description AS | + | itemtypes.description AS Extyp, |
− | avplac.lib AS | + | avplac.lib AS Plac, |
items.barcode AS Streckkod, | items.barcode AS Streckkod, | ||
− | items.datelastborrowed AS 'Senast lånad', | + | CASE |
+ | WHEN items.datelastborrowed IS NULL | ||
+ | THEN 'Aldrig' | ||
+ | ELSE items.datelastborrowed | ||
+ | END | ||
+ | AS 'Senast lånad', | ||
items.issues AS 'Antal lån', | items.issues AS 'Antal lån', | ||
− | (SELECT GROUP_CONCAT( ItemInnerDets.ItemDetails ORDER BY ItemInnerDets.ItemDetails ASC SEPARATOR '</br>') | + | (SELECT GROUP_CONCAT( DISTINCT ItemInnerDets.ItemDetails ORDER BY ItemInnerDets.ItemDetails ASC SEPARATOR '</br>') |
FROM | FROM | ||
(SELECT itemsInner.biblionumber, branches.branchname, itemsInner.barcode, | (SELECT itemsInner.biblionumber, branches.branchname, itemsInner.barcode, | ||
− | CONCAT(branches.branchname) AS ItemDetails | + | CONCAT(branches.branchname,', ',itemsInner.issues,' Utlån, senaste ', |
+ | CASE | ||
+ | WHEN itemsInner.datelastborrowed IS NULL | ||
+ | THEN '---' | ||
+ | ELSE | ||
+ | itemsInner.datelastborrowed | ||
+ | END | ||
+ | ) AS ItemDetails | ||
FROM items itemsInner | FROM items itemsInner | ||
LEFT JOIN branches ON (itemsInner.homebranch=branches.branchcode) | LEFT JOIN branches ON (itemsInner.homebranch=branches.branchcode) | ||
+ | WHERE itemsInner.homebranch IN ('8BXQ','8BXZ','8BYA','8BYB','8BYC','8BYI','8BYL','8BYM','8BYQ','8BYR','8BYS','GULL','HJO','HOVA','KABO','MARI','SKSB','TIDA','TIKF','TORE') AND itemsInner.itemlost='0' | ||
) ItemInnerDets | ) ItemInnerDets | ||
WHERE ItemInnerDets.biblionumber = items.biblionumber AND ItemInnerDets.barcode <> items.barcode ORDER BY ItemInnerDets.ItemDetails | WHERE ItemInnerDets.biblionumber = items.biblionumber AND ItemInnerDets.barcode <> items.barcode ORDER BY ItemInnerDets.ItemDetails | ||
− | ) AS 'Finns även vid' | + | ) AS 'Finns även vid', |
+ | (SELECT GROUP_CONCAT( DISTINCT HistoryInnerDets.HistoryDetails ORDER BY HistoryInnerDets.HistoryDetails ASC SEPARATOR '</br>') | ||
+ | FROM | ||
+ | (SELECT items.biblionumber, branches.branchname, HistoryInner.itemnumber, | ||
+ | CONCAT(branches.branchname) AS HistoryDetails | ||
+ | FROM old_issues HistoryInner | ||
+ | LEFT JOIN branches ON (HistoryInner.branchcode=branches.branchcode) | ||
+ | LEFT JOIN items ON (items.itemnumber=HistoryInner.itemnumber) | ||
+ | WHERE HistoryInner.branchcode IN ('8BXQ','8BXZ','8BYA','8BYB','8BYC','8BYI','8BYL','8BYM','8BYQ','8BYR','8BYS','GULL','HJO','HOVA','KABO','MARI','SKSB','TIDA','TIKF','TORE') | ||
+ | ) HistoryInnerDets | ||
+ | WHERE HistoryInnerDets.biblionumber = items.biblionumber ORDER BY HistoryInnerDets.HistoryDetails | ||
+ | ) AS 'Titeln tidigare lånad vid (lån i Koha, eller gamla sparade lån)', | ||
+ | |||
+ | CASE | ||
+ | WHEN ExtractValue(bm.metadata, '//controlfield[@tag="003"]') = 'SE-LIBR' | ||
+ | |||
+ | THEN | ||
+ | CONCAT( | ||
+ | |||
+ | CONCAT( | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Umdp','%'),'Depåbiblioteket </br></br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Gull','%'),'Gullspång </br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Hjo','%'),'Hjo </br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Hova','%'),'Hova </br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Kabo','%'),'Karlsborg</br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Mari','%'),'Mariestad </br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Sksb','%'),'Skövde </br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Tikf','%'),'Tibro </br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Tida','%'),'Tidaholm </br>',''), | ||
+ | IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Tore','%'),'Töreboda </br>','') | ||
+ | ) | ||
+ | , | ||
+ | |||
+ | CONCAT('<a href=\"https://libris.kb.se/bib/', ExtractValue( bm.metadata, '//controlfield[@tag="001"]'), '\" target="_blank">', 'Libris webbsök', '</a></br>' ) | ||
+ | , | ||
+ | |||
+ | CASE | ||
+ | WHEN | ||
+ | ExtractValue (bm.metadata,'//datafield[@tag="887"]/subfield[@code="a"]') ='' | ||
+ | THEN | ||
+ | 'Marcfält 887 saknas' | ||
+ | ELSE | ||
+ | CONCAT('<a href=\"https://libris.kb.se/katalogisering/', SUBSTRING(ExtractValue (bm.metadata,'//datafield[@tag="887"][1]/subfield[@code="a"]'),9,15), '\" target="_blank">', 'LibrisXL', '</a>' ) | ||
+ | END | ||
+ | |||
+ | |||
+ | ) | ||
+ | |||
+ | |||
+ | ELSE 'Ej Libris-post' | ||
+ | END | ||
+ | AS 'Bestånd i Libris' | ||
FROM items | FROM items | ||
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) | LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) | ||
LEFT JOIN authorised_values avplac ON (items.location=avplac.authorised_value) | LEFT JOIN authorised_values avplac ON (items.location=avplac.authorised_value) | ||
− | LEFT JOIN itemtypes ON (items.itype=itemtypes.itemtype) | + | LEFT JOIN itemtypes ON (items.itype=itemtypes.itemtype) |
+ | INNER JOIN biblio_metadata bm ON bm.biblionumber = biblio.biblionumber AND bm.format = 'marcxml' | ||
+ | |||
WHERE items.homebranch = <<Bibliotek|branches>> | WHERE items.homebranch = <<Bibliotek|branches>> | ||
− | AND items.location | + | AND items.location LIKE <<Placering|LOC:all>> |
− | AND items.ccode | + | AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>> |
− | AND items.itemcallnumber LIKE <<Hyllsignum. | + | AND items.itype LIKE <<Exemplartyp|itemtypes:all>> |
+ | AND items.itemcallnumber LIKE <<Hyllsignum. | ||
+ | Trunkera med %. | ||
+ | För att få med alla hyllsignum, skriv bara %>> | ||
AND (YEAR(NOW())-YEAR(items.datelastborrowed) > <<Antal år sedan senaste utlån>> OR items.datelastborrowed IS NULL) | AND (YEAR(NOW())-YEAR(items.datelastborrowed) > <<Antal år sedan senaste utlån>> OR items.datelastborrowed IS NULL) | ||
− | AND YEAR(NOW())-YEAR(items.dateaccessioned) > <<Antal år sedan | + | AND (YEAR(NOW())-YEAR(items.dateaccessioned) > <<Antal år sedan senaste utlån>>) |
AND items.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) | AND items.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) | ||
+ | AND items.itemlost='0' | ||
− | ORDER BY itemcallnumber, author, title | + | ORDER BY ccode,avplac.lib,itemcallnumber, author, title</pre> |
− | </pre> |
Nuvarande version från 15 februari 2023 kl. 13.54
SELECT biblio.copyrightdate As 'Utgivning', biblio.author AS Författare, CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\" target="_blank">', biblio.title, '</a>' ) AS Titel, items.itemcallnumber AS Signum, items.ccode AS Avd, itemtypes.description AS Extyp, avplac.lib AS Plac, items.barcode AS Streckkod, CASE WHEN items.datelastborrowed IS NULL THEN 'Aldrig' ELSE items.datelastborrowed END AS 'Senast lånad', items.issues AS 'Antal lån', (SELECT GROUP_CONCAT( DISTINCT ItemInnerDets.ItemDetails ORDER BY ItemInnerDets.ItemDetails ASC SEPARATOR '</br>') FROM (SELECT itemsInner.biblionumber, branches.branchname, itemsInner.barcode, CONCAT(branches.branchname,', ',itemsInner.issues,' Utlån, senaste ', CASE WHEN itemsInner.datelastborrowed IS NULL THEN '---' ELSE itemsInner.datelastborrowed END ) AS ItemDetails FROM items itemsInner LEFT JOIN branches ON (itemsInner.homebranch=branches.branchcode) WHERE itemsInner.homebranch IN ('8BXQ','8BXZ','8BYA','8BYB','8BYC','8BYI','8BYL','8BYM','8BYQ','8BYR','8BYS','GULL','HJO','HOVA','KABO','MARI','SKSB','TIDA','TIKF','TORE') AND itemsInner.itemlost='0' ) ItemInnerDets WHERE ItemInnerDets.biblionumber = items.biblionumber AND ItemInnerDets.barcode <> items.barcode ORDER BY ItemInnerDets.ItemDetails ) AS 'Finns även vid', (SELECT GROUP_CONCAT( DISTINCT HistoryInnerDets.HistoryDetails ORDER BY HistoryInnerDets.HistoryDetails ASC SEPARATOR '</br>') FROM (SELECT items.biblionumber, branches.branchname, HistoryInner.itemnumber, CONCAT(branches.branchname) AS HistoryDetails FROM old_issues HistoryInner LEFT JOIN branches ON (HistoryInner.branchcode=branches.branchcode) LEFT JOIN items ON (items.itemnumber=HistoryInner.itemnumber) WHERE HistoryInner.branchcode IN ('8BXQ','8BXZ','8BYA','8BYB','8BYC','8BYI','8BYL','8BYM','8BYQ','8BYR','8BYS','GULL','HJO','HOVA','KABO','MARI','SKSB','TIDA','TIKF','TORE') ) HistoryInnerDets WHERE HistoryInnerDets.biblionumber = items.biblionumber ORDER BY HistoryInnerDets.HistoryDetails ) AS 'Titeln tidigare lånad vid (lån i Koha, eller gamla sparade lån)', CASE WHEN ExtractValue(bm.metadata, '//controlfield[@tag="003"]') = 'SE-LIBR' THEN CONCAT( CONCAT( IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Umdp','%'),'Depåbiblioteket </br></br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Gull','%'),'Gullspång </br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Hjo','%'),'Hjo </br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Hova','%'),'Hova </br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Kabo','%'),'Karlsborg</br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Mari','%'),'Mariestad </br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Sksb','%'),'Skövde </br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Tikf','%'),'Tibro </br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Tida','%'),'Tidaholm </br>',''), IF (ExtractValue (bm.metadata,'//datafield[@tag="852"]/subfield[@code="b"]') LIKE CONCAT('%','Tore','%'),'Töreboda </br>','') ) , CONCAT('<a href=\"https://libris.kb.se/bib/', ExtractValue( bm.metadata, '//controlfield[@tag="001"]'), '\" target="_blank">', 'Libris webbsök', '</a></br>' ) , CASE WHEN ExtractValue (bm.metadata,'//datafield[@tag="887"]/subfield[@code="a"]') ='' THEN 'Marcfält 887 saknas' ELSE CONCAT('<a href=\"https://libris.kb.se/katalogisering/', SUBSTRING(ExtractValue (bm.metadata,'//datafield[@tag="887"][1]/subfield[@code="a"]'),9,15), '\" target="_blank">', 'LibrisXL', '</a>' ) END ) ELSE 'Ej Libris-post' END AS 'Bestånd i Libris' FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) LEFT JOIN authorised_values avplac ON (items.location=avplac.authorised_value) LEFT JOIN itemtypes ON (items.itype=itemtypes.itemtype) INNER JOIN biblio_metadata bm ON bm.biblionumber = biblio.biblionumber AND bm.format = 'marcxml' WHERE items.homebranch = <<Bibliotek|branches>> AND items.location LIKE <<Placering|LOC:all>> AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>> AND items.itype LIKE <<Exemplartyp|itemtypes:all>> AND items.itemcallnumber LIKE <<Hyllsignum. Trunkera med %. För att få med alla hyllsignum, skriv bara %>> AND (YEAR(NOW())-YEAR(items.datelastborrowed) > <<Antal år sedan senaste utlån>> OR items.datelastborrowed IS NULL) AND (YEAR(NOW())-YEAR(items.dateaccessioned) > <<Antal år sedan senaste utlån>>) AND items.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) AND items.itemlost='0' ORDER BY ccode,avplac.lib,itemcallnumber, author, title