0. Informations générales sur les rapports SQL

0.1 Fouiller dans les rapports existants

0.2 Construire un nouveau rapport

Consulter la vidéo ci-dessous pour une première approche de l’outil Construire un nouveau rapport dans Bilans et statistiques.

Pour les requêtes déjà prêtes à être exécutés (code qui commence par SELECT), il faut les copier/coller dans un nouveau rapport SQL (Bilans et statistiques>Créer à partir de SQL).

0.3 Fonctions dans les rapports (à jour, 22.05)

1.Informations initiales

1.1 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 timestamp = <<Inscrire une date - AAAA-MM-JJ >>

Lors de l’exécution de ce rapport, vous devrez entrer une date pour que cette date soit inscrite dans la requête SQL.

1.2 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 barcode like '0123%'  -- Troncature à droite.

      select * from items where barcode like '%0123'  -- Troncature à gauche.

      select * from items where barcode like '%0123%'  -- Troncature des 2 côtés

1.3 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 afficher la zone 100$a de toutes les notices :

      select ExtractValue(metadata,'//datafield[@tag="100"]/subfield[@code="a"]') from biblio_metadata

1.3.1 Extraction du Leader : 000

Syntaxe : ExtractValue(metadata,’//leader’)

Il faut comprendre que nous allons avoir une série de caractères complets du tag sélectionné. 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(metadata,'//leader'),17,1)

Donc à partir du 17e caractère, retourner 1 caractère.

1.4 Extraction d’un ControlField : 0XX

Syntaxe :

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

Il faut comprendre que nous allons avoir une série de caractères complets du tag sélectionné. 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(metadata,'//controlfield[@tag="008"]'),8,4)

Donc à partir du 8e caractère retourner les 4 prochains caractères.

1.4.1 Extraction d’un datafield : 1XX – 9XX

• Syntaxe pour extraire le contenu de la 260$a
  

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

• Syntaxe pour extraire le contenu de toute une zone
  

      ExtractValue(metadata,'//datafield[@tag="260"]/*')

• Syntaxe pour extraire une sous-zone locale
  

ExtractValue(items.more_subfields_xml,'//datafield[@tag="999"]/subfield[@code>="x"]')

• Syntaxe pour faire un filtre sur une partie de texte (pour éviter de devoir inscrire les % )
  

WHERE barcode LIKE CONCAT( '%', <<Code barre>>, '%')

1.5 Faire un lien dans un rapport

Depuis la version 22.05, des liens sont faits automatiquement vers les notices et les exemplaires lorsqu'un rapport contient un biblionumber ou un itemnumber et vers les dossiers des usagers lorsqu'un rapport contient le borrowernumber ou le cardnumber. Ceci dit, la fonction peut toujours être utilisée avec d'autres champs qu'on veut rendre cliquables ou si on veut renvoyer vers d'autres pages, comme la page de détails à l'OPAC.

Par exemple, si on veut consulter la notice d'autorité :

CONCAT('<a href="/cgi-bin/koha/authorities/detail.pl?authid=',authid,'\" target="_blank" rel="noopener">',authid,'</a>') as 'Consulter notice',

ou encore rajouter un lien cliquable vers le contenu de la zone 856 (lien vers la table biblioitems)

CONCAT('<a href=\"',url,'\">',url,'</a>') AS url,

1.6 CASE WHEN pour identifier clairement des valeurs 

1.6.1 Mois 

1.6.2 Types de frais et crédit dans les rapports de caisse 

2. Circulation

2.1 Solde des usagers ayant empruntés entre 2 dates.

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 :

• 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.

• À partir de : Transaction effectué par l’usager après une date précise.
  
• Avant le : Transaction effectué par l’usager avant une date précise.
  
• Solde minimal : Solde minimal que vous voulez avoir.
  

Requête SQL

SELECT b.borrowernumber, b.cardnumber,b.surname, b.firstname, b.dateexpiry as 'Date Expiration',format(sum(amountoutstanding),2) as 'Solde des documents'
      FROM
      accountlines a
      JOIN borrowers b on (a.borrowernumber=b.borrowernumber)
      JOIN issues i on (a.borrowernumber=i.borrowernumber and a.itemnumber= i.itemnumber)
      WHERE
      i.issuedate >= <<Transaction après le AAAA-MM-JJ ?>> and
      i.issuedate <= <<Transaction avant le  AAAA-MM-JJ ?>>
      GROUP BY
      b.borrowernumber, b.cardnumber,b.surname, b.firstname, b.dateexpiry
      having sum(amountoutstanding) >= <<Solde > que ?>>
      ORDER BY sum(amountoutstanding) DESC;

2.2 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 :

• 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
• Date dernière inscription : Date de la dernière inscription dans le dossier de l’usager.
  

*Attention, la date de migration sera utilisée pour toutes les transactions provenant de Manitou.

Requête SQL

2.3 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

• issues.datedue : date de retour
  
• borrowers.surname : nom de l’usager
  
• 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",
      borrowernumber
      FROM issues
      LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber)
      LEFT JOIN borrower_message_preferences ON (issues.borrowernumber=borrower_message_preferences.borrowernumber)
      WHERE borrower_message_preferences.message_attribute_id is NULL
      group by borrowers.surname, borrowers.cardnumber
      ORDER BY borrowers.surname ASC
      LIMIT 1000

Exemple visuel

2.4 Liste de documents jamais prêtés

    SELECT biblio.biblionumber,
      items.barcode as 'Code à barres',
      biblio.title as Titre,
      items.itype as 'Type de document',
      items.itemcallnumber as 'Cote',
      biblio.biblionumber  as 'Numéro notice',
      items.dateaccessioned AS 'Date création exemplaire',
      biblio.datecreated as 'Date création notice',
      biblio.copyrightdate as 'Date publication 260$c'
      FROM items
      LEFT JOIN biblio      USING (biblionumber)
      LEFT JOIN     issues  USING (itemnumber)
      LEFT JOIN old_issues  USING (itemnumber)
      WHERE issues.itemnumber IS NULL
      AND old_issues.itemnumber IS NULL 
      order by items.itype, items.itemcallnumber

2.5 Nombre de prêts initiaux par intervalle de date

Pour ce rapport, vous pouvez vous servir du module Bilans et Statistiques > Circulation et inscrire les paramètres comme ci-dessous.

2.6 Nombre de renouvellements par intervalle de date

Pour ce rapport, vous pouvez vous servir du module Bilans et Statistiques > Circulation et inscrire les paramètres comme ci-dessous

2.7 Date du dernier prêt et nombre de prêt pour un type de document

Ce rapport vous donnera la liste des documents selon une catégorie avec une colonne pour le nombre de prêt et une colonne pour la date du dernier prêt. Un rapport qui peut être utile pour de l’élagage.

select items.itype as 'Type de document',
      biblio.title as 'Titre',
      concat(replace(ExtractValue(metadata,'//datafield[@tag="650"]/subfield[@code="a"]'),'/',''),'  ', replace(ExtractValue(metadata,'//datafield[@tag="650"]/subfield[@code="z"]'),'/','')) as Sujet,
      items.barcode as 'Code à barres',
      items.itemcallnumber as Cote, 
      count(old_issues.issuedate) as 'Total prêt',
      MAX(old_issues.issuedate) as 'Date dernier prêt'
      from 
      old_issues
      left join items on (old_issues.itemnumber=items.itemnumber)
      LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber) 
      LEFT JOIN biblio ON (biblio.biblionumber = biblioitems.biblionumber)
      where items.itype=<<Type de document|itemtypes>>
      group by items.itemnumber
      order by items.itemcallnumber

2.8 Documents avec X jours de retard

Liste des documents selon un nombre de jours de retard que vous devez indiquer dans un filtre lors de l’exécution du rapport.

SELECT 
      items.barcode as "Code barres",
      biblio.title as "Titre",
      items.itemcallnumber as "Cote",
      items.replacementprice as "Prix de remplacement",
      concat(borrowers.firstname,' ',borrowers.surname) as "Nom de l'emprunteur", 
      borrowers.borrowernumber,
      issues.date_due as 'date de retour',
      (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'jours retards'
      FROM items
      LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
      LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) 
      LEFT JOIN issues on (items.itemnumber=issues.itemnumber)
      LEFT JOIN borrowers on (issues.borrowernumber=borrowers.borrowernumber)
      WHERE (TO_DAYS(curdate())-TO_DAYS( date_due)) =<<nombre de jours de retard>>  and issues.date_due is not null
      order by borrowers.surname asc

2.9 Nombre de prêt sur la réserve de cours entre deux dates

select 
      count(statistics.datetime) as 'Total prêt'
      from
      statistics
      left join items on items.itemnumber=statistics.itemnumber
      left join course_items ci on ci.itemnumber=statistics.itemnumber
      where ci.itemnumber is not null and statistics.type='ISSUE' and statistics.datetime between <<prêt entre le |date>> and <<et le|date>>

2.10 Nombre de prêt selon une catégorie entre deux dates

Pour ce rapport, vous pouvez vous servir du module Bilans et Statistiques > Circulation et inscrire les paramètres comme ci-dessous.

2.11 Liste des usagers avec des documents en prêts et en retard avec le montant d’amende attaché

SELECT 
      b.cardnumber, 
      concat(b.firstname,' ',b.surname) as "Nom de l'emprunteur",
      i.price as Prix, i.replacementprice as 'Prix de remplacement',  
      CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',i.biblionumber,'\">',i.barcode,'</a>') AS barcode,
           bib.title as Titre,
       (TO_DAYS(curdate())-TO_DAYS(ni.date_due)) AS 'Nombre de jours de retard',
      format(a.amountoutstanding,2) as 'Amende pour le document'
      FROM accountlines a 
        LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber ) 
        LEFT JOIN items i ON ( a.itemnumber = i.itemnumber ) 
        LEFT JOIN biblio bib ON ( i.biblionumber = bib.biblionumber ) 
        LEFT JOIN ( SELECT * FROM issues) ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber ) 
      WHERE 
          a.amountoutstanding > 0 and b.categorycode=<<Catégorie usager|categorycode>> AND (TO_DAYS(curdate())-TO_DAYS(ni.date_due)) >=<<Nombre de jours de retard de plus de>>
      GROUP BY a.description
      ORDER BY b.surname asc
      limit 1000

2.12 Listes des prêts selon une notice pour voir qui a fait l’action du prêt (logs)

select b.title,
      al.timestamp,
      al.action,
      concat(bo.surname,' ', bo.firstname) as 'Utilisateur qui a fait action',
      i.barcode,
      concat(borrowers.surname,' ', borrowers.firstname) as 'Utilisateur'
      from action_logs al
      left join items i on al.info=i.itemnumber
      left join biblioitems bi on i.biblioitemnumber=bi.biblioitemnumber
      left join biblio b on b.biblionumber=bi.biblionumber
      left join borrowers bo on bo.borrowernumber=al.user
      left join borrowers borrowers on borrowers.borrowernumber=al.object
      where module='circulation' and i.biblionumber=<<Numéro notice>>

2.13 Module utilisé pour le renouvellement d’un exemplaire (opac ou intranet) selon la carte d’un utilisateur

     select s.datetime as 'Date transaction',
      branch as Site,
      biblio.title as Titre,
      itemcallnumber as Cote,
      barcode as Codebarre,
      i.itype as 'Type de document',
      i.location as Localisation
      from statistics s
      left join borrowers b on b.borrowernumber=s.borrowernumber
      left join items i on i.itemnumber=s.itemnumber
      left join biblioitems bi on bi.biblioitemnumber=i.biblioitemnumber
      left join biblio on biblio.biblionumber=bi.biblionumber
      where s.type='renew' and b.cardnumber=<<Numéro de carte>>
      order by datetime desc

2.14 Liste des exemplaires non empruntés depuis X années

SELECT b.title as Titre,
       b.author as Auteur,
       b.copyrightdate as Date,
       i.itemcallnumber as Cote,
       i.barcode as Codebarres,
       i.datelastborrowed as 'Date dernier prêt',
       i.issues as 'Total prêt',
       i.dateaccessioned as 'Date création exemplaire'
      FROM items i
      LEFT JOIN biblio b USING (biblionumber)
      LEFT JOIN issues USING (itemnumber)
      WHERE i.itype=<<Type de document|itemtypes>> and issues.issuedate IS NULL AND i.datelastborrowed < DATE_SUB(CURDATE(), INTERVAL <<Nombre années>> YEAR)
      limit 10000

2.15 File des réservations

      SELECT 
      borrowers.surname as Nom, 
      borrowers.firstname as Prénom, 
      borrowers.cardnumber as 'Numéro carte', 
      reserves.reservedate AS 'date réservation', 
      reserves.priority as priorité, 
      CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'">',biblio.title,'</a>') AS Titre, 
      biblio.author as Auteur, 
      GROUP_CONCAT(items.barcode,'-',IF(r2.found IS NOT NULL,'reserved',IF(onloan IS NOT NULL,'en prêt',IF(notforloan!=0,'notforloan',IF(itemlost!=0,'itemlost','disponible')))) SEPARATOR ' | ') AS 'Code barre - statut', 
      IF(reserves.itemnumber IS NULL, 'Prochain exemplaire disponible',barcode) AS 'Exemplaire spécifique'
      FROM reserves
      LEFT JOIN borrowers USING (borrowernumber)
      LEFT JOIN biblio USING (biblionumber)
      LEFT JOIN biblioitems USING (biblionumber)
      LEFT JOIN items USING (biblionumber)
      LEFT JOIN reserves r2 ON r2.itemnumber=items.itemnumber
      WHERE (reserves.found IS NULL OR reserves.found != 'W')
      GROUP BY reserves.reserve_id
      ORDER BY Titre
      limit 1000

2.16 Rapport de caisse

      select 
      concat(borrowers.surname,', ',borrowers.firstname) as 'Nom du responsable',
      borrowers.cardnumber,
      concat(borrowers2.surname,', ',borrowers2.firstname) as 'Nom usager',
      branches.branchname as 'site de la transaction',
      date as 'Date transaction',
      CASE 
      WHEN credit_type_code='C' then 'Credit' 
      WHEN credit_type_code='FOR' then 'Amnistie'
      WHEN credit_type_code='W' then 'Amnistie'
      WHEN debit_type_code='F' then 'Amende'
      WHEN debit_type_code='FU' then 'Amende - Longue période'
      WHEN debit_type_code='PAY' then 'Paiement'
      WHEN debit_type_code='A' then 'Frais de gestion'
      WHEN debit_type_code='M' then 'Divers'
      WHEN debit_type_code='L' then 'Item perdu'
      WHEN debit_type_code='N' then 'Nouvelle carte'
      ELSE
      'VALEUR INCONNUE'
      end as TypeTransaction,
      format(amount,2) as 'Montant',
      accountlines.note,
      accountlines.payment_type as 'Type de paiement',
      CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\"target="_blank">',b.title,'</a>') as 'Titre de la notice',
      items.barcode as 'Code à barres',
      items.itype as 'Type de document'
      FROM accountlines 
      LEFT JOIN borrowers ON (accountlines.manager_id=borrowers.borrowernumber) 
      LEFT JOIN borrowers borrowers2 ON (accountlines.borrowernumber=borrowers2.borrowernumber) 
      left join branches on (branches.branchcode =borrowers.branchcode )
      left join items on accountlines.itemnumber=items.itemnumber
      left join biblioitems bi on items.biblioitemnumber=bi.biblioitemnumber
      left join biblio b on bi.biblionumber=b.biblionumber
      where date(date) between <<Date transaction entre le|date>> and <<et le |date>> and branches.branchcode=<<Site transaction|branches>>
      order by date asc, branches.branchname

2.17 Rapport de caisse – Total par type de paiement

      select 
      format(sum(amount),2) as Total,
      accountlines.payment_type as 'Type de paiement'
      FROM accountlines 
      LEFT JOIN borrowers ON (accountlines.manager_id=borrowers.borrowernumber) 
      LEFT JOIN borrowers borrowers2 ON (accountlines.borrowernumber=borrowers2.borrowernumber) 
      left join branches on (branches.branchcode =borrowers.branchcode )
      left join items on accountlines.itemnumber=items.itemnumber
      left join biblioitems bi on items.biblioitemnumber=bi.biblioitemnumber
      left join biblio b on bi.biblionumber=b.biblionumber
      where accounttype='PAY' and date(date) between <<Date transaction entre le|date>> and <<et le |date>> and branches.branchcode=<<Site transaction|branches>>
      group by accountlines.payment_type
      order by date asc, branches.branchname

3. Catalogage

3.1 Notices contenant une valeur spécifique dans une zone et sous-zone particulière

      SELECT biblionumber, biblio.title, biblio.author
      FROM biblio_metadata
      left JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
      WHERE ExtractValue(metadata, "//datafield[@tag=<< Zone (XXX)>>]/subfield[@code=<<Sous-zone (Y)>>]")
      LIKE <<Terme recherché (utiliser les % comme troncature)>>

3.2 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 :

• 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.copyrightdate,items.location,items.itemcallnumber,items.barcode,items.notforloan
      FROM items
      LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
      LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
      where
      location = <<Localisation|LOC>>;

Exemple visuel

3.3 Liste des notices supprimées selon un intervalle de date

      select biblionumber as 'Numéro de notice',
      author as 'Auteur',
      title as Titre,
      copyrightdate as 'Date copyright'
      from deletedbiblio
      where date(timestamp) between <<notices supprimées entre le (AAAA-MM-JJ)>> AND <<et le (AAAA-MM-JJ)>>

3.4 Nombre de notices par type bibliographique (zone 000, sous-zone 6)

      SELECT 
      CASE SUBSTR(metadata,INSTR(metadata, "<leader>")+8+6,2) 
      WHEN 'am' THEN 'Livre'
      WHEN 'as' THEN 'Périodiques'
      WHEN 'cm' THEN 'Musique notée'
      WHEN 'em' THEN 'Carte'
      WHEN 'gm' THEN 'Média projeté'
      WHEN 'im' THEN 'Enregistrement non-musical'
      WHEN 'jm' THEN 'Enregistrement musical'
      WHEN 'mm' THEN 'Fichier informatique'
      WHEN 'rm' THEN 'Artefact 3D'
      WHEN 'tm' THEN 'Manuscrit'
      ElSE 'Type inconnu' END
      AS Type, count(DISTINCT biblioitems.biblionumber) AS 'Nombre de notices' 
      FROM biblioitems
      LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblioitems.biblionumber)
      GROUP BY Type

3.5 Liste des notices selon intervalle de cote Dewey

      SELECT biblio.title as Titre, items.itemcallnumber as Cote, items.biblionumber as 'Numéro notice'
      FROM items 
      LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
      LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  
      WHERE items.itemcallnumber >= <<cote dewey entre>> AND items.itemcallnumber <= <<et cote dewey>>
      limit 1000

3.6 Liste des exemplaires supprimés selon intervalle de date

      select i.biblionumber as 'Numéro de notice',
      i.barcode as Codebarres,
      b.author as 'Auteur',
      b.title as Titre,
      b.copyrightdate as 'Date copyright'
      from deleteditems i
      left join deletedbiblio b on i.biblionumber=b.biblionumber
      where date(i.timestamp) between <<exemplaires supprimés entre le (AAAA-MM-JJ)>> AND <<et le (AAAA-MM-JJ)>>

3.7 Réserve de cours (version 3.12)

      select biblio.title as Titre,
      cr.course_number as 'Numéro cours',
      cr.course_name as 'Nom du cours',
      concat(b.firstname,' ',b.surname) as 'Nom du professeur',
      i.location as Localisation,
      i.barcode as 'Code barres',
      i.itemcallnumber as Cote
      from courses cr
      left join course_instructors ci on cr.course_id=ci.course_id
      left join borrowers b on ci.borrowernumber=b.borrowernumber
      left join course_reserves on cr.course_id=course_reserves.course_id
      left join course_items on course_reserves.ci_id=course_items.ci_id
      left join items i on course_items.itemnumber=i.itemnumber
      left join biblioitems bi on bi.biblioitemnumber=i.biblioitemnumber
      left join biblio on biblio.biblionumber=bi.biblionumber
      order by cr.course_name

3.8 Liste des notices sans langues dans la 008

      Select
      biblio.biblionumber,
      substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) as 'Langue 008 35-37',
      ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="a"]') as 'Langue 041 $a',
      ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="b"]') as 'Langue 041 $b',
      ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]')  as 'Type Koha',
      ExtractValue(metadata,'//datafield[@tag="980"]/subfield[@code="m"]')  as 'Type Manitou',
      items.itype as 'ItemType',
      items.itemcallnumber as 'Cote',
      concat(replace(ExtractValue(metadata,'//datafield[@tag="245"]/subfield[@code="a"]'),'/',''),' ', replace(ExtractValue(metadata,'//datafield[@tag="245"]/subfield[@code="b"]'),'/','')) as 'Titre'
      FROM items
      LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
      LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
      LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblio.biblionumber)
      WHERE
      substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) = '   '
      LIMIT 10000

3.9 Liste des notices pour une langue

      
Select
      biblio.biblionumber as 'Notice',
      substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) as 'Langue 008 35-37',
      ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="a"]') as 'Langue 041 $a',
      ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="b"]') as 'Langue 041 $b',
      ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]')  as 'Type Koha',
      ExtractValue(metadata,'//datafield[@tag="980"]/subfield[@code="m"]')  as 'Type Manitou',
      items.itype as 'ItemType',
      items.itemcallnumber as 'Cote',
      concat(replace(ExtractValue(metadata,'//datafield[@tag="245"]/subfield[@code="a"]'),'/',''),' ', replace(ExtractValue(metadata,'//datafield[@tag="245"]/subfield[@code="b"]'),'/','')) as 'Titre'
      FROM items
      LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
      LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
      LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblio.biblionumber)

      where substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) = <<Code 008>>
      OR ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="b"]')  LIKE <<Code 041b>>
      OR ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="a"]')  LIKE <<Code 041a>>
      LIMIT 5000

3.10 Liste des notices avec 008 ni ENG ni FRE ni SPA ni vide

      Select
      biblio.biblionumber as 'Notice',
      substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) as 'Langue 008 35-37',
      ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="a"]') as 'Langue 041 $a',
      ExtractValue(metadata,'//datafield[@tag="041"]/subfield[@code="b"]') as 'Langue 041 $b',
      ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="c"]')  as 'Type Koha', 
      ExtractValue(metadata,'//datafield[@tag="980"]/subfield[@code="m"]')  as 'Type Manitou', 
      items.itype as 'ItemType',
      items.itemcallnumber as 'Cote',
      concat(replace(ExtractValue(metadata,'//datafield[@tag="245"]/subfield[@code="a"]'),'/',''),' ', replace(ExtractValue(metadata,'//datafield[@tag="245"]/subfield[@code="b"]'),'/','')) as 'Titre'
      FROM items
      LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
      LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
      LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblio.biblionumber)

      where substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) <> 'FRE'
      AND substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) <> 'ENG'
      AND substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) <> 'SPA'
      AND substring(ExtractValue(metadata,'//controlfield[@tag="008"]'),36,3) <> '   '
      LIMIT 5000

3.11 Liste des autorités en double

      select group_concat(CONCAT('<a href=\"/cgi-bin/koha/authorities/detail.pl?authid=',authid,'\"target="_blank">',authid,'</a>') separator ', ') AS Identifiant, 
      GROUP_CONCAT(DISTINCT ExtractValue(marcxml,'//datafield[@tag="040"]/subfield[@code="a"]') SEPARATOR ' , ') as '040$a',
      GROUP_CONCAT(DISTINCT ExtractValue(marcxml,'//datafield[@tag="970"]/subfield[@code="a"]') SEPARATOR ' , ') as '970$a',
      concat('<a href="/cgi-bin/koha/authorities/authorities-home.pl?op=do_search&type=intranet&marclist=all&and_or=and&operator=contains&value=',extractvalue(marcxml,"//datafield[@tag=<<Étiquette de la vedette (dans la clause SELECT)>>]/subfield[@code>='a']"),'\"target=_blank">',
      extractvalue(marcxml,"//datafield[@tag=<<Étiquette de la vedette (dans la clause SELECT)>>]/subfield[@code>='a']"),'</a>') AS Vedette
      from auth_header
      where extractvalue(marcxml,"//datafield[@tag=<<Étiquette de la vedette (dans la clause WHERE)>>]/subfield[@code>='a']")!=''
      group by extractvalue(marcxml,"//datafield[@tag=<<Étiquette de la vedette (dans la clause GROUP BY)>>]/subfield[@code>='a']")
      having count(extractvalue(marcxml,"//datafield[@tag=<<Étiquette de la vedette (dans la clause HAVING)>>]/subfield[@code>='a']"))>1

Consultez la vidéo ci-dessous pour savoir comment utiliser ce rapport.

3.12 Autorités en double avec type différent

      SELECT GROUP_CONCAT(CONCAT('<a href=\"/cgi-bin/koha/authorities/authorities-home.pl?op=do_search&type=intranet&marclist=all&and_or=and&operator=contains&value=',authid,'\">',authid,'</a>') SEPARATOR ', ') AS autorité, 
CONCAT('<a href=\"/cgi-bin/koha/authorities/merge.pl?authid=',MAX(authid),'&authid=',MIN(authid),'\" target="_blank">Fusion</a>') AS Fusion, GROUP_CONCAT(authtypecode SEPARATOR ',') as type, 
concat(
      ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), -- Nom de personne
      ExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- Collectivités
      ExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- Réunion/Congrés
      ExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- Titre uniforme
      ExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- Sub chrono
      ExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- Sujet
      ExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- Sub géo
      ExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  -- Genre forme
      ) main_heading
FROM auth_header 
GROUP BY marc HAVING count(authid) > 1 
ORDER BY marc limit 10000

Aperçu : Le lien dans la colonne Fusion amène directement à la page de fusion des autorités

3.13 Notices bibliographiques sans autorités liées

3.13.1 Sujets dans la zone 650

Ce rapport va chercher dans la notice bibliographique si un champ 650 est rempli et s’il y a également le $9 qui est rempli. Si le $9 est vide (autorité non liée), le numéro de la notice va s’afficher dans la colonne « Modifier notice » et dans la colonne « Vedettes, il y aura le contenu de la zone 650$a.

      SELECT
      biblioitems.biblionumber, 
      ExtractValue(metadata,'//datafield[@tag="650"]/subfield[@code="a"]') AS Vedettes
      FROM biblioitems
      left join items on biblioitems.biblioitemnumber=items.biblioitemnumber
      LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblioitems.biblionumber)

      WHERE length(ExtractValue(metadata, '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(metadata, '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
      ORDER BY Vedettes

3.13.2 Auteurs dans la zone 100

      SELECT
      bm.biblionumber, 
      ExtractValue(metadata,'//datafield[@tag="100"]/subfield[@code="a"]') AS Auteur100
      FROM biblio_metadata bm
      left join items on bm.biblionumber=items.biblionumber
      WHERE length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="9"]')) = 0
      group by bm.biblionumber
      ORDER BY Auteur100

3.13.3 Auteurs dans la zone 110

      SELECT
      bm.biblionumber, 
      ExtractValue(metadata,'//datafield[@tag="110"]/subfield[@code="a"]') AS Auteur110
      FROM biblio_metadata bm
      left join items on bm.biblionumber=items.biblionumber
      WHERE  length(ExtractValue(metadata, '//datafield[@tag="110"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(metadata, '//datafield[@tag="110"]/subfield[@code="9"]')) = 0
      group by bm.biblionumber
      ORDER BY Auteur110

3.13.4 Auteurs dans la zone 700

      SELECT
      bm.biblionumber, 
      ExtractValue(metadata,'//datafield[@tag="700"]/subfield[@code="a"]') AS Auteur700
      FROM biblio_metadata bm
      left join items on bm.biblionumber=items.biblionumber
      WHERE  length(ExtractValue(metadata, '//datafield[@tag="700"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(metadata, '//datafield[@tag="700"]/subfield[@code="9"]')) = 0
      group by bm.biblionumber
      ORDER BY Auteur700

3.13.5 Auteurs dans les zones 710

      SELECT
      bm.biblionumber, 
      ExtractValue(metadata,'//datafield[@tag="710"]/subfield[@code="a"]') AS Auteur700
      FROM biblio_metadata bm
      left join items on bm.biblionumber=items.biblionumber
      WHERE  length(ExtractValue(metadata, '//datafield[@tag="710"]/subfield[@code="a"]')) != 0 
      AND length(ExtractValue(metadata, '//datafield[@tag="710"]/subfield[@code="9"]')) = 0
      group by bm.biblionumber
      ORDER BY Auteur710

3.14 Notices bibliographiques arrimées à la mauvaise autorité

Permet de retrouver les notices bibliographiques contenant dans les zones d'autorités un $9 qui fait un lien avec une autorité qui n'existe plus dans votre banque locale. Lors de l'exécution du rapport, vous devez inscrire la zone d'autorités souhaitée, par exemple : 100.

SELECT

biblio_metadata.biblionumber, 

biblio.title "Titre"

      

FROM biblio_metadata

LEFT JOIN biblio USING (biblionumber)

WHERE length(ExtractValue(metadata, " // datafield[@tag ='650' ] / subfield[@code =9 ]"))>0

AND ExtractValue(metadata, " // datafield[@tag ='650' ] / subfield[@code =9 ]") NOT IN (SELECT authid FROM auth_header) /* saved_sql.id: 1461 */

3.15 Statistiques sur les liens cliqués des notices à l’opac

      select biblionumber as 'numéro notice',itemnumber as 'numéro exemplaire',url, count(timeclicked) as 'Total'
      from linktracker
      where timeclicked between <<lien cliqué entre le|date>> and <<et le|date>>
      group by url

3.16 Notices par site et par statut de perte (champs $7 de l’exemplaire)

      SELECT 
      biblio.biblionumber,
      items.itemcallnumber as Cote,
      biblio.title as Titre,
      authorised_values.lib as Statut
      FROM biblio
      LEFT JOIN items on (items.biblionumber=biblio.biblionumber)
      left join biblioitems on (biblioitems.biblionumber=biblio.biblionumber)
      LEFT JOIN authorised_values ON items.notforloan = authorised_values.authorised_value
      where items.homebranch=<<Site permanent de l'exemplaire|branches>> and items.notforloan=<<statut de perte|NOT_LOAN>> and authorised_values.category="not_loan"
      group by biblio.biblionumber
      order by items.itemcallnumber asc
      limit 10000

3.17 Nombre de notices dans le catalogue

      SELECT COUNT(*) AS 'Nombre de notices' FROM biblio

3.18 Notices Curio qui ont un statut « d » (notices supprimées du site Curio)

      select biblio.biblionumber,
      biblio.title,
      items.itemnumber,
      biblioitems.url,
      substring(ExtractValue(metadata,'//leader'),6,1)
      from biblioitems
      left join items on items.biblioitemnumber=biblioitems.biblioitemnumber
      left join biblio on biblioitems.biblionumber=biblio.biblionumber
      LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblio.biblionumber)
      where substring(ExtractValue(metadata,'//leader'),6,1)='d'
      limit 10000

3.19 Liste des autorités triées par type

      SELECT authid as Numéro,
      datecreated as 'Date création', authtypecode as Type, concat(
      ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), -- Nom de personne
      ExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- Collectivités
      ExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- Réunion/Congrés
      ExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- Titre uniforme
      ExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- Sub chrono
      ExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- Sujet
      ExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- Sub géo
      ExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  -- Genre forme
      ) main_heading
      FROM `auth_header`
      ORDER BY  authtypecode, main_heading

3.20 Notices contenant une valeur spécifique dans une zone et sous-zone particulière

      SELECT biblio.biblionumber, biblio.title as Titre, biblio.author as Auteur
      FROM biblio_metadata
      left JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
      WHERE ExtractValue(metadata, "//datafield[@tag=<< Zone marc (XXX)>>]/subfield[@code=<<sous-zone (Y)>>]")
      LIKE <<Terme recherché (utiliser % pour troncature)>>

3.21 Autorités contenant une valeur spécifique dans une zone et sous-zone particulière

      SELECT CONCAT('<a href=\"/cgi-bin/koha/authorities/detail.pl?authid=',authid,'\">',authid,'</a>') AS 'Numéro autorité',
      ExtractValue(marcxml,'//datafield[@tag="150"]/subfield[@code="a"]') as '150a',
      ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code="a"]') as '100a',
      ExtractValue(marcxml,'//datafield[@tag="110"]/subfield[@code="a"]') as '110a',
      ExtractValue(marcxml,'//datafield[@tag="111"]/subfield[@code="a"]') as '111a',
      ExtractValue(marcxml,'//datafield[@tag="130"]/subfield[@code="a"]') as '130a',
      ExtractValue(marcxml,'//datafield[@tag="151"]/subfield[@code="a"]') as '151a',
      ExtractValue(marcxml,'//datafield[@tag="155"]/subfield[@code="a"]') as '155a',
      ExtractValue(marcxml,'//datafield[@tag="040"]/subfield[@code="a"]') as '040a',
      ExtractValue(marcxml,'//datafield[@tag="970"]/subfield[@code="a"]') as '970a'
      FROM auth_header
      WHERE ExtractValue(marcxml, "//datafield[@tag=<< Zone marc (XXX)>>]/subfield[@code=<<sous-zone (Y)>>]")
      LIKE <<Terme recherché (utiliser % pour troncature)>>

3.22 Exemplaires les plus empruntés selon numéro d’autorité

      SELECT count(s.datetime) AS Total, b.title as Titre, b.author as Auteur,
      b.biblionumber
      FROM statistics s
      JOIN items i ON (i.itemnumber=s.itemnumber) 
      LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber) 
      LEFT JOIN biblio_metadata bi on (b.biblionumber=bi.biblionumber)
      WHERE ExtractValue(metadata,'//datafield[@tag="650"]/*') like <<Numéro autorité (entourés de %) >> and DATE(s.datetime) > DATE_SUB(CURRENT_DATE(),INTERVAL 12 MONTH) 
      AND DATE(s.datetime)<=CURRENT_DATE() AND 
      s.itemnumber IS NOT NULL AND s.type IN ('issue','renew')
      GROUP BY b.biblionumber 
      ORDER BY Total DESC 
      limit 100

3.23 Exemplaires avec des chiffres dans la zone 952$3

      select biblio.title as Titre, 
      biblio.biblionumber as 'Numéro notice', 
      itemcallnumber as Cote, 
      materials as Matériels,barcode as Codebarre,
      itype as 'Type de document'
      from items
      left join biblioitems on items.biblioitemnumber=biblioitems.biblioitemnumber 
      left join biblio on biblio.biblionumber=items.biblionumber 
      where materials ='1' or materials='2' or materials='3' or materials='4' or materials='5' or materials='6' or materials='7'
      order by materials asc
      limit 10000

4. Acquisitions

4.1 Récapitulatifs des acquisitions pour une année budgétaire

SELECT
      aqorders.ordernumber as ' No commande',
      aqorders.invoiceid as 'No facture',
      aqbooksellers.name as 'Nom du fournisseur',
      aqbudgets.budget_code as 'Code',
      aqorders.basketno as 'Panier',
      aqorders.biblionumber as 'No Notice',
      biblio.title as 'Titre',
      aqorders.ecost as 'Prix estimé commandé',
      aqorders.entrydate as 'Commandé le',
      format (aqorders.listprice,2) as 'Prix fournisseur commandé',
      aqorders.quantity as 'Quantité commandé',
      format (aqorders.unitprice,2) as 'Prix unitaire facturé',
      aqorders.quantityreceived as 'Quantité facturée',
      format((aqorders.quantityreceived * aqorders.unitprice),2) as 'Total prix facturé', 
      aqorders.datereceived as 'Reçu le',
      aqorders.rrp as 'Prix de remplacement'
      FROM aqorders
      LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
      LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_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_period_id = << Période budgétaire. Ex.: 21>>
      order by aqorders.datereceived, aqbudgets.budget_name, aqorders.basketno
      LIMIT 5000

4.2 Liste des documents commandés selon poste budgétaire

Il y a dans ce rapport un filtre sur la date de la commande et un filtre sur le poste budgétaire. Pour connaitre l’identifiant du poste budgétaire, il faut vous rendre dans Acquisitions > Postes budgétaires > Modifier le poste budgétaire. Dans l’url de cette page, il faut repérer le numéro inscrit après budget_id . Ce rapport va vous donner la liste des documents qui ont été commandés et pas encore reçu.

SELECT
      biblio.title as 'Titre',
      aqorders.entrydate as 'Date commande',
      format (aqorders.listprice,2) as 'Prix fournisseur commandé'
      FROM aqorders
      LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
      LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id
      LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
      LEFT JOIN aqbooksellers ON aqbooksellers.id = aqbasket.booksellerid
      where
      aqorders.unitprice='0.00' and aqorders.datecancellationprinted is null  and aqbudgets.budget_id= <<identifiant du poste budgétaire>> and aqorders.biblionumber is not null and aqorders.entrydate between <<date de la commande entre|date>> and <<et le|date>>
      order by aqorders.datereceived, aqbudgets.budget_name, aqorders.basketno
      LIMIT 5000

4.3 Liste des fournisseurs non utilisés

      select 
      CONCAT('<a href="/cgi-bin/koha/acqui/booksellers.pl?booksellerid=',a.id,'\"target="_blank">',a.name,'</a>') as Fournisseur
      from
      aqbooksellers a
      left join aqbasket ab on a.id=ab.booksellerid
      where basketno is null

4.4 Liste des documents achetés par poste budgétaire avec lien de notices à l’opac

Il faut inscrire à la place du texte en rouge, l’identifiant du budget. Pour connaitre l’identifiant du budget, il faut cliquer sur le budget dans la page d’accueil des budgets et dans l’url affiché vous aurez le texte suivant : budget_period_id=. Le chiffre qui est après le signe = est l’identifiant du budget.

Lors de l’exécution du rapport le nom du poste budgétaire vous sera demandé.

Seulement le titre et l’auteur apparaissent dans le tableau et le titre est cliquable pour ouvrir la notice à l’OPAC. En exportant ce tableau dans un fichier excel ou word, vous pourriez diffuser l’information à vos professeurs par exemple.

     SELECT
      CONCAT('<a href=\"', IF(CHAR_LENGTH(systempreferences.value), 
      CONCAT('http://', systempreferences.value), ''), '/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>') AS Titre,
      biblio.author as Auteur
      FROM systempreferences, biblio
      LEFT JOIN aqorders ON aqorders.biblionumber = biblio.biblionumber
      LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id
      LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
      where 
      systempreferences.variable='OPACBaseURL' and
      aqbudgets.budget_period_id='IDENTIFIANT_BUDGET' and
      aqorders.datecancellationprinted is null and aqorders.biblionumber is not null and aqbudgets.budget_name=<<Poste budgétaire>>
      LIMIT 5000

4.5 Total facturé par fournisseur (Rapport au Ministère)

Pour connaitre l’identifiant du budget, il faut cliquer sur le budget dans la page d’accueil des budgets et dans l’url affiché vous aurez le texte suivant : budget_period_id=. Le chiffre qui est après le signe = est l’identifiant du budget.

Ce rapport peut exclure également deux types de document, à savoir par exemple les DVD et les CD, il faut pour cela changer le texte TYPE_DOC par les codes des types de document que vous trouverez dans le tableau des Types de documents dans le module Administration. Si vous n’avez qu’un seul type de document à exclure, il faut alors supprimer cette partie du code and biblioitems.itemtype NOT LIKE ‘TYPE_DOC’.

SELECT
aqbooksellers.name as 'Nom du fournisseur',
SUM(aqorders.quantityreceived) as 'Quantité facturée',
format(sum(aqorders.quantityreceived * aqorders.unitprice),2) as 'Total prix facturé' 

FROM aqorders
LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_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_period_id = << Période budgétaire. Ex.: 51>> and biblioitems.itemtype NOT LIKE 'TYPE_DOC' and biblioitems.itemtype NOT LIKE 'TYPE_DOC'
group by aqbooksellers.name

order by aqbooksellers.name

4.6 Commandes par budget (avec notes)

      SELECT
      aqbudgets.budget_name as 'Poste budgétaire',
      CONCAT('<a href="/cgi-bin/koha/acqui/basket.pl?basketno=',aqbasket.basketno,'\"target="_blank">',aqbasket.basketno,'</a>') as Panier,
      concat(borrowers.firstname,' ',borrowers.surname) as 'Panier par',
      CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\"target="_blank">',biblio.title,'</a>') as 'Titre',
      items.itemcallnumber as Cote,
      aqorders.currency as Devise,
      format (aqorders.listprice,2) as 'Prix fournisseur commandé',
      format (aqorders.rrp,2) as 'Prix de remplacement',
      format (aqorders.ecost,2) as 'Coût budgeté',
      aqorders.quantity as Quantité,
      aqorders.quantity * aqorders.rrp as 'Total',
      format((aqorders.quantity * aqorders.ecost),2) as 'Coût total',
      aqorders.entrydate as 'Date entrée',
      aqorders.datereceived as 'Reçu le',
      aqorders.order_internalnote as 'Note interne',
      aqorders.order_vendornote as 'Note au fournisseur'
      FROM aqorders
      LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
      left join aqorders_items ai on ai.ordernumber = aqorders.ordernumber
      left join items on items.itemnumber = ai.itemnumber
      LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id
      LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
      LEFT JOIN aqbooksellers ON aqbooksellers.id = aqbasket.booksellerid
      LEFT JOIN aqbudgetperiods ON aqbudgetperiods.budget_period_id=aqbudgets.budget_period_id
      LEFT JOIN borrowers on borrowers.borrowernumber=aqbasket.authorisedby
      where  aqorders.datecancellationprinted is null and
      aqbudgets.budget_name like <<nom du poste budgétaire>> and aqbudgetperiods.budget_period_active='1'
      order by aqbasket.basketno
      LIMIT 5000

4.7 Notices avec requérant

Ce rapport aide à repérer les notices dont le champ Requérant est rempli. Le champ Requérant a été rajouté dans la grille de catalogage dans la zone 952, dans l’exemple cette sous-zone se nomme D. Si votre champ porte une autre lettre, il faut changer le code en rouge dans la requête ci-dessous.

Select
b.biblionumber,
b.title 'Titre',
b.author as Auteur,
itemtypes.description as 'Type de document',
authorised_values.lib as Localisation,
av.lib as Statut,
ExtractValue(items.more_subfields_xml,'//datafield[@tag="999"]/subfield[@code="D"]') as Requérant
from items
left join biblioitems bi on bi.biblioitemnumber=items.biblioitemnumber
left join biblio b on b.biblionumber=bi.biblionumber
left join itemtypes on itemtypes.itemtype=items.itype
LEFT JOIN authorised_values ON items.location = authorised_values.authorised_value
left join authorised_values av on items.notforloan = av.authorised_value
where authorised_values.category='loc' and av.category='not_loan'  and ExtractValue(items.more_subfields_xml,'//datafield[@tag="999"]/subfield[@code="D"]')<>'' and ExtractValue(items.more_subfields_xml,'//datafield[@tag="999"]/subfield[@code="D"]') like <<Nom requerant>>
order by Requerant asc, b.title asc
limit 10000

5. Utilisateurs

5.1 Liste des usagers expirés qui n’ont pas de prêt en cours

Ce rapport donne la liste des usagers dont le dossier est expiré pour une année choisie. Ces usagers n’auront pas de prêt en cours.

      SELECT
      concat(surname,' ', firstname) as Usager,
      cardnumber as 'Numéro carte'
      FROM borrowers 
      WHERE borrowernumber 
      NOT IN (SELECT borrowernumber FROM issues) 
      AND YEAR(dateexpiry) = <<Année>>
 

5.2 Liste des usagers sans adresse courriel

      SELECT 
      concat(surname,' ', firstname) as Usager,
      cardnumber as 'Numéro carte',
      branchcode as Site, 
      dateexpiry as 'Date expiration'
      FROM borrowers 
      WHERE ' ' IN (email)

5.3 Liste des usagers avec un message ou une note

Ce rapport vous donnera la liste des usagers qui ont un message de circulation ou une note à leur dossier.

SELECT b.cardnumber as 'Numéro carte', b.surname as Nom, b.firstname as Prénom,
b.opacnote as 'Note opac', b.borrowernotes as 'Note intranet', group_concat(DISTINCT m.message separator ', ') AS 'Message de circulation'
FROM borrowers b
LEFT JOIN messages m USING (borrowernumber)
WHERE b.branchcode=<<Branch|branches>> AND ((b.opacnote IS NOT
NULL AND b.opacnote != '') OR (b.borrowernotes IS NOT NULL AND
b.borrowernotes != '') OR (m.message IS NOT NULL AND
m.message != '')) GROUP BY b.borrowernumber ORDER BY b.surname ASC,
b.firstname ASC

5.4 Liste des usagers en double

      SELECT surname as Nom, firstname as Prénom, GROUP_CONCAT(CONCAT('<a href="/cgi-bin/koha/circ/circulation.pl?borrowernumber=',borrowers.borrowernumber,'\"target="_blank">',borrowers.borrowernumber,'</a>') SEPARATOR ', ') AS 'Numéro de carte'
      FROM borrowers 
      GROUP BY CONCAT(trim(surname),"/",trim(firstname),"/") 
      HAVING COUNT(CONCAT(trim(surname),"/",trim(firstname),"/"))>1

5.5 Nombre d’usagers par catégorie et par site

      select count(*) as Total,
      categorycode as Catégorie,
      branchcode as Site
      from borrowers
      group  by branchcode,categorycode

6. Périodiques

6.1 Liste des fascicules en retard

Le rapport vous donnera la liste des fascicules en retard.

8= Arrêté

      SELECT serial.subscriptionid as 'Num abonnement',
      aqbooksellers.name as Fournisseur,
      serial.biblionumber as 'Num notice',
      biblio.title as 'Titre',
      serial.serialseq as Numérotation,
      serial.planneddate as 'Date prévu publication',
      serial.publisheddate as '
      Date publication', 
      serial.STATUS as Statut
      FROM serial
      LEFT JOIN biblio on (serial.biblionumber = biblio.biblionumber)
      LEFT JOIN biblioitems on (serial.biblionumber=biblioitems.biblionumber) 
      LEFT JOIN subscription on (serial.subscriptionid=subscription.subscriptionid) 
      LEFT JOIN aqbooksellers on (subscription.aqbooksellerid=aqbooksellers.id)
      WHERE serial.STATUS=<<Numéro du statut>>
      ORDER BY serial.subscriptionid ASC
      limit 10000

6.2 Liste des abonnements selon les modèles de numérotation

      select 
      biblio.title as Titre,
      sn.label as Modèle,
      sn.numberingmethod as Description
      from subscription s
      left join biblio on s.biblionumber=biblio.biblionumber
      left join subscription_numberpatterns sn on sn.id=s.numberpattern
      limit 10000

6.3 Liste des abonnements selon les périodicités

      select 
      biblio.title as Titre,
      sn.description
      from subscription s
      left join biblio on s.biblionumber=biblio.biblionumber
      left join subscription_frequencies sn on sn.id=s.periodicity
      limit 10000

6.4 Périodiques selon la durée de vie (mis à jour pour 3.22)

           SELECT
                biblio.title AS "Abonnement",
                subscription.subscriptionid AS "No. Abonnement",
                subscription.callnumber as Cote,
                subscription.location AS "Localisation",
                subscription.notes AS "Note abonnement",
            subscription.internalnotes as 'Note interne',
            v.lib as 'Durée de vie'
            FROM
                subscription
                INNER JOIN serial
                    ON subscription.subscriptionid = serial.subscriptionid
                    INNER JOIN biblioitems
                        ON serial.biblionumber = biblioitems.biblionumber
                        INNER JOIN biblio
                            ON biblioitems.biblionumber = biblio.biblionumber
            INNER join additional_field_values d on (d.record_id=subscription.subscriptionid) 
            INNER join authorised_values v on (d.value=v.authorised_value)
            where v.category='lifespan'
            group by subscription.subscriptionid
            limit 10000

6.5 Liste des abonnés aux périodiques

      select
      CONCAT('<a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=',s.subscriptionid,'\"target="_blank">',bi.title,'</a>') as 'Consulter abonnement',
      s.biblionumber,
      cardnumber,
      surname,
      firstname
      from alert
      left join borrowers b on b.borrowernumber=alert.borrowernumber
      left join subscription s on s.subscriptionid=alert.externalid
      left join biblio bi on bi.biblionumber=s.biblionumber
      order by bi.title
      limit 10000

7. Administration

7.1 Liste des préférences systèmes

      select * from systempreferences limit 1000;

7.2 Usagers avec permissions

      SELECT cardnumber, firstname, surname, categorycode,
      description, flags
      FROM borrowers
      JOIN user_permissions USING (borrowernumber)
      JOIN permissions USING (code)
      UNION (
      SELECT cardnumber, firstname, surname,
      categorycode, 'module-level permissions;
      1 is superlibrarian' AS description, flags
      FROM borrowers
      WHERE flags > 0)
      ORDER BY cardnumber ASC
      limit 10000

8. OPAC

8.1 Les mots recherchés entre deux dates

Les recherches effectuées à l’opac sont sauvegardées dans la base de données seulement quand l’utilisateur est connecté à son compte. Cette requête vous permet d’afficher les mots recherchés entre deux dates lorsque les utilisateurs sont connectés

Aperçu des résultats

      SELECT
      search_history.query_desc as Recherche,
      search_history.type as 'Type de notice',
      search_history.total as 'Nombre résultat',
      search_history.time as Date,
      borrowers.branchcode as 'Site utilisateur',
      borrowers.categorycode as 'Catégorie utilisateur'
      FROM search_history
      JOIN borrowers ON (search_history.userid=borrowers.borrowernumber)
      JOIN branches ON (borrowers.branchcode=branches.branchcode)
      WHERE  search_history.time BETWEEN <<Entre le|date>> AND <<et le|date>>

8.2 Les mots les plus recherchés entre deux dates

Les recherches effectuées à l’opac sont sauvegardées dans la base de données seulement quand l’utilisateur est connecté à son compte. Cette requête vous permet d’afficher les mots les plus recherchés entre deux dates lorsque les utilisateurs sont connectés

Aperçu du résultat :

      SELECT COUNT(*) AS 'Nombre de fois recherché',
      query_desc as Recherche
      FROM search_history
      WHERE search_history.time BETWEEN <<Entre le|date>> AND <<et le|date>>
      GROUP BY query_desc
      ORDER BY COUNT(*) DESC