Fragments de rapports SQL

Cet article vous aidera à personnaliser les rapports SQL avec différentes fonctions. On y apprend comment ajouter des hyperliens dans un rapport, comment extraire l’information de champs MARC spécifiques, comment ajouter un menu d’options dans la page d’exécution du rapport et comment ajouter un tri de résultats.

Informations MARC
Ajouter des liens
Ajouter des choix

Ajouter un tri de résultats

Informations MARC

Toutes les informations MARC sont dans la table biblio_metadata. Assurez-vous que celle-ci soit dans votre section « FROM ».

Lieu de publication (008/15-17)

SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 16,3 )

Exemples d'utilisations

Rapport pour obtenir une liste de titres et le lieu de publication

SELECT
    title AS "Titre",
    SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 16,3 ) AS "Lieu de publication"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Rapport pour sortir les titres des notices qui ont « quc » dans le lieu de publication

SELECT
     title AS "Titre"
 FROM 
     biblio_metadata
     LEFT JOIN biblio USING (biblionumber)
 WHERE 
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 16,3 ) = "quc"

Lieu de publication (264 $a)

ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]')

Exemples d'utilisations

Rapport pour obtenir une liste de titres et le lieu de publication

SELECT
    title AS "Titre",
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]') AS "Lieu de publication"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Rapport pour obtenir la liste de titres dont le lieu de publication est (contient) Montréal

SELECT
    title AS "Titre",
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]') AS "Lieu de publication"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
WHERE
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]') LIKE "%Montréal%"

Langue (008/35-37)

SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )

Exemples d'utilisations

Rapport pour obtenir une liste de titres et la langue

SELECT
    title AS "Titre",
    SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Rapport qui montre les notices dont la langue dans la zone 008 n’est pas la même que dans la zone 041$a

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue (008)",
     ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]') AS "Langue (041a)"
 FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)
 WHERE
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) != ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]')

Éditeur (264 $b)

ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]')

Exemples d'utilisations

Rapport pour obtenir le nombre de notices par éditeur

SELECT
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]') AS "Éditeur",
    count(*) AS "Nombre de notices"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
GROUP BY ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]')

Rapport pour obtenir la liste de titres dont l’éditeur est La courte échelle (contient « courte échelle »)

SELECT
    title AS "Titre",
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]') AS "Éditeur"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
WHERE
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]') LIKE "%courte échelle%"

Année de publication (008/07-10)

SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 )

Exemples d'utilisations

Rapport pour obtenir une liste de titres et l’année de publication

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS "Année de publication" 
FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)

Année de publication (264$ c)

ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]')

Exemples d'utilisations

Rapport pour obtenir une liste de tous titres et leurs dates de publication 264$ c

SELECT
   biblionumber,
   title AS "Titre",
   ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') AS "Date de publication 264c"
FROM
   biblio
   LEFT JOIN biblio_metadata USING (biblionumber)

Rapport pour obtenir la liste des titres de l’année de publication 2024 (contient « 2024 »)

SELECT
   biblionumber,
   title AS "Titre",
   ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') AS "Éditeur"
FROM
   biblio
   LEFT JOIN biblio_metadata USING (biblionumber)
WHERE
   ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') LIKE "%2024%"

Ajouter des liens

Dans l’interface des rapports de Koha, il est possible d’ajouter des liens pour aller vers une notice ou un dossier d’utilisateur. Cela ne donne toutefois pas de bons résultats si vous exportez le résultat du rapport dans un tableur par exemple.

Les liens doivent être dans la section SELECT du rapport.

Lien vers la notice

CONCAT('<a href=\"cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">Voir la notice</a>')

Exemples d'utilisations

Rapport qui montre les notices dont la langue dans la zone 008 n’est pas la même que dans la zone 041$a, avec lien vers la notice

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue (008)",
     ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]') AS "Langue (041a)",
    CONCAT('<a href=\"cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">Voir la notice</a>') AS ""
 FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)
 WHERE
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) != ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]')

Lien vers la modification de la notice

CONCAT('<a href=\"/cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=', biblionumber, '\">Modifier la notice</a>')

Exemples d'utilisations

Rapport qui montre les notices dont la langue dans la zone 008 n’est pas la même que dans la zone 041$a, avec lien vers la modification de la notice

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue (008)",
     ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]') AS "Langue (041a)",
    CONCAT('<a href=\"cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=', biblionumber, '\">Modifier la notice</a>') AS ""
 FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)
 WHERE
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) != ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]')

Lien vers la modification de l’exemplaire

CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">Modifier l\'exemplaire</a>')

Exemples d'utilisations

Liste de titres et cotes (090 $a et exemplaire), avec lien vers la modification de l’exemplaire

SELECT
    title AS "Titre",
    Extractvalue(metadata, '//datafield[@tag="090"]/subfield[@code="a"]') AS "Cote 090$a",
    itemcallnumber AS "Cote (exemplaire)",
    CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">Modifier l\'exemplaire</a>')
FROM
    items
    LEFT JOIN biblio USING (biblionumber)
    LEFT JOIN biblio_metadata USING (biblionumber)

Lien vers le dossier de l’usager

CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">Voir dossier</a>') AS ""

Exemples d'utilisations

Rapport des utilisateurs qui n’ont pas de garant, avec choix de catégorie d’utilisateur et lien vers le dossier de l’usager

SELECT
    cardnumber AS "Numéro de carte",
    surname AS "Nom de famille",
    firstname AS "Prénom",
    dateofbirth AS "Date de naissance",
    CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">Voir dossier</a>') AS ""
FROM
    borrowers
WHERE
    categorycode = <<Catégorie d'utilisateur |categorycode>>
    AND guarantorid IS NULL

Ajouter des choix

Koha permet de choisir des variables au moment d’exécuter le rapport. Ceci permet de faire un seul rapport qui pourra servir pour plus qu’une utilisation.

Le principe des choix est de mettre le texte qui sera affiché, suivi d’une barre verticale (ou pipe en anglais), |, puis de la liste de choix, et ce, entre double chevrons (<< et >>). La liste de choix peut être un liste de valeurs autorisées :

LOC pour les localisations
CCODE pour les codes de collection
LOST pour les statuts « perdu »
DAMAGED pour les statuts « endommagé »
WITHDRAWN pour les statuts « élagué »
NOT_LOAN pour les statuts de prêt
ou toute autre liste de valeurs autorisées dans votre système

ou une des listes prédéfinies suivantes :

branches pour les bibliothèques
categorycode pour les catégories d’utilisateur
itemtypes pour les types de document
date donnera un petit calendrier pour choisir une date

Par exemple, <<Bibliothèque |branches>> affichera

Alors que <<Choisir la bibliothèque |branches>> affichera

Ces choix doivent être dans la section WHERE du rapport.

Choix de bibliothèque

<<Bibliothèque |branches>>

Exemples d'utilisations

Rapport pour obtenir la liste des utilisateurs expirés par bibliothèque

SELECT
    cardnumber AS "Numéro de carte",
    surname AS "Nom de famille",
    firstname AS "Prénom",
    dateexpiry AS "Date d'échéance"
FROM
    borrowers
WHERE
    dateexpiry <= CURDATE()
    AND branchcode = <<Bibliothèque |branches>>

Rapport pour obtenir la liste des documents perdus par bibliothèque et par localisation

SELECT
    title AS "Titre",
    itemlost_on AS "Perdu le",
    itemcallnumber AS "Cote"
FROM
    items
    LEFT JOIN biblio USING (biblionumber)
WHERE
    holdingbranch = <<Bibliothèque |branches>>
    AND location = <<Localisation |LOC>>
    AND itemlost != 0
ORDER BY
    cn_sort

Choix de catégorie d’utilisateur

<<Catégorie d'utilisateur |categorycode>>

Exemples d'utilisations

Rapport pour obtenir la liste des utilisateurs expirés par catégorie

SELECT
    cardnumber AS "Numéro de carte",
    surname AS "Nom de famille",
    firstname AS "Prénom",
    dateexpiry AS "Date d'échéance"
FROM
    borrowers
WHERE
    dateexpiry <= CURDATE()
    AND categorycode = <<Catégorie d'utilisateur |categorycode>>

Choix de type de document

<<Type de document |itemtype>>

Exemples d'utilisations

Rapport pour créer une liste de titre par type de document

SELECT  
     biblio.title AS "Titre",
     items.itemcallnumber AS "Cote",
     biblio.seriestitle AS "Collection",
     items.itype AS "Type de document",
     items.location AS "Localisation",
     items.barcode AS "Code-barres" 
 FROM 
     items 
     LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
     LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
 WHERE 
     items.homebranch=<<Bibliothèque|branches>> 
     AND items.itype=<<Type de document|itemtypes>>
 ORDER BY 
     items.barcode asc

Choix de dates (entre le … et le…)

 BETWEEN << Date début(aaaa-mm-dd)|date>> AND << jusqu'au|date>>

Exemples d'utilisations

Rapport qui identifie une liste des nouveaux abonnés selon la date d’inscription

SELECT
    surname AS "Nom",
    firstname AS "Prénom",
    city AS "Ville",
    categorycode AS "Catégorie d'utilisateur",
    dateenrolled AS "Date d'inscription",
    dateexpiry AS "Date d'expiration"
 FROM
    borrowers
 WHERE
    DATE(dateenrolled) BETWEEN << Date début(aaaa-mm-dd)|date>> AND << jusqu'au|date>>
 ORDER BY dateenrolled, categorycode, surname ASC

Ajouter un tri de résultats

ORDER BY ...

Un simple ajout d’une fonction « ORDER BY » permet de trier les résultats directement dans le rapport SQL.

Exemples d'utilisations

Pour ajouter un tri de résultats, inscrire à la fin du rapport «ORDER BY» + [nom du champ] + type de tri

Les résultats peuvent être triés selon:

ASC : ordre croissant (ex. A à Z, ou 0 à 9)
DESC : ordre décroissant (ex. Z à A, ou 9 à 0)

Dans cet exemple de rapport, on utilise la fonction «ORDER BY» pour trier les résultats en ordre croissant de codes-barres (voir la dernière portion du rapport)

ORDER BY 
     items.barcode asc

SELECT  
     biblio.title AS "Titre",
     items.itemcallnumber AS "Cote",
     biblio.seriestitle AS "Collection",
     items.itype AS "Type de document",
     items.location AS "Localisation",
     items.barcode AS "Code-barres" 
 FROM 
     items 
     LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
     LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
 WHERE 
     items.homebranch=<<Bibliothèque|branches>> 
     AND items.itype=<<Type de document|itemtypes>>
 ORDER BY 
     items.barcode asc

pour obtenir un tri en ordre décroissant, remplacer «asc» par «desc»

ORDER BY 
     items.barcode desc

Voir aussi

Ressources de la communauté Koha

SQL Reports Library (anglais)
Query MARC (SQL reports library) (anglais)
Links (SQL reports library) (anglais)
Runtime parameters (SQL reports library) (anglais)
Schéma de la base de données (anglais)

Tags: