Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
Boite de rapports SQL
Informations initiales
Utilisation des questions à l’exécution
Koha permet de poser des questions à l’exécution et ainsi rendre les requêtes
automatiquement dynamique. Le plus utile est probablement lors de l’utilisation de date qui
change régulièrement.
Exemple :
select * from items where timstamps = ;
Lors de l’exécution de ce rapport, vous devrez entrer une date pour que cette date soit inscrite
dans la requête SQL.
Vous pouvez consulter également les rapports crées par la communauté internationale à cette
adresse http://wiki.koha-community.org/wiki/SQL_Reports_Library
Troncature en SQL
La troncature est possible en SQL en utilisant le caractère ‘%’ accompagné de la fonction de
validation : ‘like’
Exemple :
select * from items where barocode ilike ‘0123%’; -- Troncature à droite.
select * from items where barocode ilike ‘%0123’; -- Troncature à gauche.
select * from items where barocode ilike ‘%0123%’; -- Troncature des 2 côtés
Extraction à partir du marcXML
L'extraction des données à partir du marxml peut-être réaliser, mais demande quelques
connaissances supplémentaires.
1. marcxml est le champ complet de chacune des notices que possède votre bibliothèque.
2. marcXml est situé dans la table : biblioitems.
Exemple simple : On veut avec la 100$a :
select ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code="a"]') from biblioitems
1 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
limit 10;
Extraction du Leader : 000
Syntaxe : ExtractValue(marcxml,'//leader')
Il faut comprendre que nous allons avoir une série de caractères complets du tag sélectionner.
Ce sera à nous par la suite de faire le tri pour retrouver l'information que nous avons besoin. Il
est possible de le faire avec la fonction substring de mysql.
Exemple : On veut retrouver l'encodage du leader.
Il s'agit ici du caractère 17.
substring(ExtractValue(marcxml,'//leader'),17,1)
Donc à partir du 17e caractère, retourner 1 caractère.
Extraction d’un ControlField : 0XX
Syntaxe : ExtractValue(marcxml,'//controlfield[@tag="008"]')
Il faut comprendre que nous allons avoir une série de caractères complets du tag sélectionner.
Ce sera à nous par la suite de faire le tri pour retrouver l'information que nous avons besoin.
Il est possible de le faire avec la fonction substring de mysql.
Ainsi, si on désire obtenir uniquement la date de publication de la zone 008.
Il s'agit des caractères : 7 à 10 inclusivement.
Il faut donc inscrire :
substring(ExtractValue(marcxml,'//controlfield[@tag="008"]'),8,4)
Donc à partir du 8e caractère retourner les 4 prochains caractères.
Extraction d’un datafield : 1XX - 9XX
Syntaxe : ExtractValue(marcxml,'//datafield[@tag="260"]/subfield[@code="a"]')
Circulation
Solde des usagers ayant empruntés entre 2 dates.
2 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
Description
Rapport SQL nous permettant de d’avoir en main tous les usagers avec un Solde plus grand
que X parmis entre 2 dates spécifique.
Colonnes affichées :
1.
2.
3.
4.
5.
6.
borrowernumber : Numéro unique de l’usager
cardnumber : Numéro de carte de l’usager
surname : Nom de famille de l’usager
firstname : Prénom de l’usager
date d’expiration : Date d’expiration de l’usager
Solde : Solde total de toutes les transactions entre les dates entrées.
*Remarque : Ce rapport vous demande de répondre à 3 questions.
1. À partir de : Transaction effectué par l’usager après une date précise.
2. Avant le : Transaction effectué par l’usager avant une date précise.
3. Solde minimal : Solde minimal que vous voulez avoir.
Requête SQL
SELECT b.borrowernumber, b.cardnumber,b.surname, b.firstname, b.dateex
piry as 'Date Expiration',format(sum(amountoutstanding),2) as 'Solde d
es documents'
FROM
accountlines a
JOIN borrowers
b on (a.borrowernumber=b.borro
wernumber)
JOIN issues i on
(a.borrowernumber=i.borrowernumber
and a.itemnumber= i.itemnumber)
WHERE
i.issuedate >= and
i.issuedate = >
ORDER BY sum(amountoutstanding) DESC;
Exemple visuel :
3 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
Exemple CSV
Solde des usagers ( Incluant les documents retournés )
Description
Rapport SQL nous permettant de d’avoir en main tous les usagers avec un solde X.
Colonnes affichées :
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
borrowernumber : Numéro unique de l’usager
Nom de famille : Nom de famille de l’usager
Prénom : Prénom de l’usager
Numéro de carte : Numéro de carte de l’usager
Date expiration : Date d’expiration de l’usager
Numéro téléphone : Numéro de téléphone de l’usager
Courriel : Courriel de l’usager
Solde courant : Solde complet courant de l’usager
Document en prêt : Nombre de document présentement dans les mains de l’usager
Date dernière inscription : Date de la dernière inscription dans le dossier de l’usager.
1. Attention, la date de migration sera utilisé pour toutes les transactions provenant de
Manitou.
Requête SQL
SELECT b.borrowernumber,b.surname as 'Nom de famille', b.firstname as
'Prénom', b.cardnumber as 'Numéro de carte', b.dateexpiry as 'Date exp
iration',
b.phone as 'Numéro téléphone',b.email as 'Courriel',format(sum(a.amoun
toutstanding),2) as 'Solde courant', count(distinct i.itemnumber) as '
Document en prêt',
min(a.timestamp) as 'Da
te dernière inscription'
FROM
accountlines a
JOIN borrower
4 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
s b on (a.borrowernumber =
b.borrowernumber)
left join i
ssues i on (b.borrower
number = i.borrowernumber)
GROUP BY
b.borrowernumber,b.surname, b.firstname, b.cardnumber, b.dateexpiry,b.
phone,b.email
having sum(amountoutstanding) >=
Exemple visuel :
Exemple CSV
Prêt courant pour paramétrer les notifications
Description
Rapport SQL qui permet d’avoir la liste des prêt en cours au moment de l’édition du rapport
avec une colonne qui envoi vers le compte de l’usager pour modifier ses préférences de
notifications d’envoi de courriel.
Remarque: Il y a une limite à l’affichage de 1000 résultats pour que tous les résultats
s’affichent dans la première page. Il faut ajuster le nombre selon le nombre de vos résultats.
Colonnes affichées
1. issues.datedue : date de retour
2. borrowers.surname : nom de l’usager
3. borrowers.cardnumber : numéro de l’usager
Reqûete SQL
SELECT max(issues.date_due) as "Date de retour", borrowers.surname as
"Nom de famille",
CONCAT('
5 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
',borrowers.cardnumber,'') as "Numéro usager"
FROM issues
LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber
)
LEFT JOIN borrower_message_preferences ON (issues.borrowernumber=borro
wer_message_preferences.borrowe
rnumber)
WHERE b
orrower_messag
e_preferences.message_attrib
ute_id is NULL
group by borrowers.sur
name, borrowers.cardnumber
ORDER BY borrowers.surname ASC
LIMIT 1000
Exemplaire visuel
Exemple CSV Lien
Catalogage
Liste des documents avec une valeur précise dans un champ
Marc.
Description
Rapport SQL nous permettant de retrouver tous les documents ayant une valeur précise dans
6 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
le MARC. L’exemple utilisé offre la possibilité de chercher dans la zone 260$a. Si vous
désirez la zone 550$c, il suffit de faire les modifications en ce sens.
Remarque : Ce rapport demande beaucoup de ressources puisqu’il doit toujours filtrer sur la
valeur SQL dans le marcXML et non pas l’utilisation du Zebra.
Colonnes affichées :
17.
18.
19.
20.
21.
22.
23.
Biblionumber : Numéro uniquement de la notice.
Author : Auteur
Title : Titre du document
Itemcallnumber : Cote du document
Barcode : Code à barres
Notforloan : Valeur d’exclusion du prêt.
_260$a : Valeur extraite.
Requête SQL
SELECT biblio.biblionumber,biblio.author,biblio.title,items.itemcallnu
mber,items.barcode,items.notforloa
n,
ExtractValue(marcxml,'//datafield[@tag="260"]/subfield[@code="a"]') as
'_260$a'
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioite
mnumber)
LEFT
JOIN bi
blio on (biblioi
tems.biblionumber=biblio.biblion
umber)
where
ExtractValue(marcxml,'//datafield[@tag="260"]/subfield[@code="a"]') li
ke ;
Exemple visuel :
Exemple CSV Lien
7 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
Liste des documents pour une localisation précise.
Description
Rapport SQL qui liste tous les documents d’une localisation ( étagère ) précise.
Remarque : Ce rapport vous montre aussi qu’il est possible d’utiliser les valeurs autorisées
pour poser une question lors de l’exécution du rapport.
Colonnes affichées :
24.
25.
26.
27.
28.
29.
30.
Biblionumber : Numéro uniquement de la notice.
Author : Auteur
Title : Titre du document
Année : Date de publication
Itemcallnumber : Cote du document
Barcode : Code à barres
Notforloan : Valeur d’exclusion du prêt.
Requête SQL
SELECT biblio.biblionumber,biblio.author,biblio.title,biblio.copyright
date,items.location,items.itemcallnumber,items.barcode,items.notforloa
n
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioite
mnumber)
LEFT
JOIN bi
blio on (biblioi
tems.biblionumber=biblio.biblionumber)
where
location = ;
Exemple visuel :
Exemple CSV
8 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
Acquisitions
Récapitulatifs des acquisitions pour une année budgetaire
Description: Rapport SQL qui permet d’avoir ltoutes les commandes faites pour une année
budgetaire.
Remarque: L’année 2011-2012 correspond à l’année 2. Pour avoir une autre année, il faut
changer le chiffre à la ligne “aqbudgets.budget_period_id = '2' “. Ce rapport a été crée par
Joanne Laforest et modifié par Marjorie Barry-Vila.
Colonnes affichées:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
ordernumber: Numéro commande
booksellerinvoicenumber: Numéro facture
aqbooksellers.name: Nom du fournisseur
budget_name: Budget
basketno: Numéro du panier
aqorders.biblionumber: Numéro de la notice
biblio.title: Titre
listprice:Prix unitaire commandé
quantity: Quantité
entrydate: Date de la commande
ecost: Prix unitaire facturé
quantityreceive:Quantité facturé
Total prix facturé
datereceived:Date reception
Requête SQL:
SELECT aqorders.ordernumber as ' Num commande', aqorders.booksellerinv
oicenumber as 'Num facture', aqbooksellers.name as 'Nom du fournisseur
', aqbudgets.budget_name as 'Budget', aqorders.basketno as 'Panier', a
qorders.biblionumber as 'Num Notice', biblio.title as 'Titre', format
(aqorders.listprice,2) as 'Prix unitaire commandé', aqorders.quantity
as 'Quantité', aqorders.entrydate as 'Date commande', aqorders.ecost a
s 'Prix unitaire facturé', aqorders.quantityreceived as "Quantité fact
uré", (aqorders.quantityreceived * aqorders.ecost) as 'Total prix fact
uré', aqorders.daterece
ived as 'Date réception'
FROM aqorders LEFT JOIN biblio ON aqorders.biblionumber = biblio.
biblionumber LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.bu
dget_id LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
LEFT JOIN aqbooksellers ON aqbooksellers.id = aqbasket.booksellerid
where aqorders.datecancellationprinted is null and aqbudgets.budget_p
9 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
eriod_id = '2'
order by aqorders.datereceived,aqbudgets.budget_name, aqorders.bas
ketno
Exemple visuel
Exemple csv Lien
Utilisateurs
Périodiques
Administration
Liste des préférences systèmes
Description
Rapport SQL nous permettant de prendre une sauvegarde de nos préférences systèmes avant
d’effectuer des changements majeurs. Une nouvelle version par exemple, ou encore si vous
désirez tenter une paramétrisation plus complexe.
Il s’agit donc ici d’une requête qui permettra plus facilement déceler des erreurs de migrations
ou encore des erreurs de manipulations de la part des usagers ayant accès à cette section.
Colonnes affichées :
31.
32.
33.
34.
variable : Nom de préférence système.
value : Valeur sauvegardée de la préférence système.
options : Permets une sélection multiple.
explanation : Explication sommaire de la raison d’être de la préférence système.
10 / 11
Communauté Koha-CCSR
http://communaute.koha.ccsr.qc.ca
35. type : Le type de question que cette préférence système demande.
Requête SQL
select * from systempreferences limit 1000;
Exemple visuel :
Exemple CSV
Lien
11 / 11
Powered by TCPDF (www.tcpdf.org)
© Copyright 2024 ExpyDoc
