You are here:
< Back
  1. Aller dans Bilans et statistiques > Créer à partir de SQL
  2. Donner un nom au rapport
  3. Au besoin, le classer dans un groupe/sous-groupe
  4. Dans la boîte SQL, coller le code suivant

ATTENTION!

Utilisez le bon rapport selon votre version Koha!

Le rapport de la version 19.05 n’est pas compatible avec la version Koha 20.05.

Consultez:

Koha 19.05 et versions antérieures

SELECT
    CASE accounttype 
      WHEN "F" THEN "Amendes"
      WHEN "A" THEN "Frais d'inscription"
      WHEN "L" THEN "Documents perdus"
      WHEN "Rent" THEN "Frais de location"
      WHEN "S" THEN "Divers"
      WHEN "M" THEN "Divers"
      WHEN "Pay" THEN "Paiements"
      WHEN "W" THEN "Amnisties"
      WHEN "N" THEN "Nouvelles cartes"
      WHEN "C" THEN "Crédits"
      WHEN "FOR" THEN "Dettes remises"
      WHEN "Frais" THEN "Frais de copie"
      WHEN "PF" THEN "Frais d'administration"
      WHEN "VOID" THEN "Paiements annulés"
      WHEN "LR" THEN "Documents perdus retournés"
      WHEN "CR" THEN "Crédits"
      ELSE accounttype END AS "Type de frais",
    SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
FROM
    account_offsets
    JOIN accountlines ON (accountlines.accountlines_id = account_offsets.debit_id)
WHERE
    type = "Payment"
    AND DATE(created_on) = <<Date |date>>
GROUP BY accounttype
  1. Cliquer sur « Sauvegarder le rapport »

Plusieurs bibliothèques

Attention : La version 19.05 a un bogue qui fait que la bibliothèque du paiement n’est pas enregistrée. Vous pouvez utiliser la version alternative.

SELECT 
     CASE frais.accounttype
       WHEN "F" THEN "Amendes"
       WHEN "A" THEN "Frais d'inscription"
       WHEN "L" THEN "Documents perdus"
       WHEN "Rent" THEN "Frais de location"
       WHEN "S" THEN "Divers"
       WHEN "M" THEN "Divers"
       WHEN "Pay" THEN "Paiements"
       WHEN "W" THEN "Amnisties"
       WHEN "N" THEN "Nouvelles cartes"
       WHEN "C" THEN "Crédits"
       WHEN "FOR" THEN "Dettes remises"
       WHEN "Frais" THEN "Frais de copie"
       WHEN "PF" THEN "Frais d'administration"
       WHEN "VOID" THEN "Paiements annulés"
       WHEN "LR" THEN "Documents perdus retournés"
       WHEN "CR" THEN "Crédits"
       ELSE accounttype END AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant" 
 FROM account_offsets
     JOIN accountlines frais ON (frais.accountlines_id = account_offsets.debit_id) 
     JOIN accountlines pay ON (pay.accountlines_id = account_offsets.credit_id) 
 WHERE type = "Payment" 
     AND DATE(created_on) = <<Date |date>> 
     AND pay.branchcode = <<Bibliothèque |branches>> 
 GROUP BY frais.accounttype;

Version alternative pour 19.05

Note : Ce rapport donne la bibliothèque où a été enregistré le frais plutôt que la bibliothèque du paiement.

SELECT 
     CASE accounttype
       WHEN "F" THEN "Amendes"
       WHEN "A" THEN "Frais d'inscription"
       WHEN "L" THEN "Documents perdus"
       WHEN "Rent" THEN "Frais de location"
       WHEN "S" THEN "Divers"
       WHEN "M" THEN "Divers"
       WHEN "Pay" THEN "Paiements"
       WHEN "W" THEN "Amnisties"
       WHEN "N" THEN "Nouvelles cartes"
       WHEN "C" THEN "Crédits"
       WHEN "FOR" THEN "Dettes remises"
       WHEN "Frais" THEN "Frais de copie"
       WHEN "PF" THEN "Frais d'administration"
       WHEN "VOID" THEN "Paiements annulés"
       WHEN "LR" THEN "Documents perdus retournés"
       WHEN "CR" THEN "Crédits"
       ELSE accounttype END AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant" 
 FROM account_offsets
     JOIN accountlines ON (accountlines.accountlines_id = account_offsets.debit_id) 
 WHERE type = "Payment" 
     AND DATE(created_on) = <<Date |date>>
     AND branchcode = <<Bibliothèque |branches>> 
 GROUP BY accounttype

Version Koha 20.05

Depuis la version 20.05, plusieurs changements ont été faits dans la structure de la base de données pour améliorer la gestion des frais au dossier.

Voici la nouvelle version 20.05 du rapport de Paiement reçus par types de frais

Version Koha 20.05: rapport de base

SELECT
     CASE account_debit_types.description
       WHEN "Overdue fine" THEN "Frais de retard"
       WHEN "Account creation fee" THEN "Frais d'abonnement"
       WHEN "Account renewal fee" THEN "Renouvellement abonnement"
       WHEN "Lost item" THEN "Documents perdus"
       WHEN "Lost item processing fee" THEN "Frais de traitement"
       WHEN "Rental fee" THEN "Frais de location"
       WHEN "Daily rental fee" THEN "Frais de location/jour)"
       WHEN "Renewal of daily rental item" THEN "Renouvellement de location/jour"
       WHEN "Rental fee" THEN "Renouvellement de location"
       WHEN "Hold fee" THEN "Frais de réservation"
       WHEN "Hold waiting too long" THEN "Frais de réservation non-honorée"
       WHEN "New card fee" THEN "Nouvelle carte"
       WHEN "VOID" THEN "Paiements annulés"
       WHEN "Pay" THEN "Paiement"
       WHEN "Payment from library to patron" THEN "Remboursement"
       WHEN "WRITEOFF" THEN "Amnisties"
       ELSE account_debit_types.description END AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
 FROM
     account_offsets
     JOIN accountlines ON (accountlines.accountlines_id = account_offsets.debit_id)
     JOIN account_debit_types ON (accountlines.debit_type_code = account_debit_types.code)
 WHERE
     account_offsets.type = "Payment"
     AND DATE(created_on) BETWEEN <<Entre le |date>> AND <<et le |date>>
 GROUP BY accountlines.debit_type_code

Version 20.05: rapport pour plusieurs bibliothèques

SELECT
     CASE account_debit_types.description
       WHEN "Overdue fine" THEN "Frais de retard"
       WHEN "Account creation fee" THEN "Frais d'abonnement"
       WHEN "Account renewal fee" THEN "Frais de Renouvellement abonnement"
       WHEN "Lost item" THEN "Documents perdus"
       WHEN "Lost item processing fee" THEN "Frais de traitement"
       WHEN "Rental fee" THEN "Frais de location"
       WHEN "Daily rental fee" THEN "Frais de location/jour"
       WHEN "Renewal of daily rental item" THEN "Renouvellement de location/jour"
       WHEN "Renewal of rental item" THEN "Renouvellement de location"
       WHEN "Hold fee" THEN "Frais de réservation"
       WHEN "Hold waiting too long" THEN "Frais de réservation non-honorée"
       WHEN "New card fee" THEN "Nouvelle carte"
       WHEN "VOID" THEN "Paiements annulés"
       WHEN "Pay" THEN "Paiement"
       WHEN "Payment from library to patron" THEN "Remboursement"
       WHEN "WRITEOFF" THEN "Amnisties"
       ELSE account_debit_types.description END AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
 FROM
     account_offsets
     JOIN accountlines ON (accountlines.accountlines_id = account_offsets.debit_id)
     JOIN account_debit_types ON (accountlines.debit_type_code = account_debit_types.code)
     LEFT JOIN borrowers ON (accountlines.manager_id = borrowers.borrowernumber)
 WHERE
     account_offsets.type = "Payment"
     AND DATE(created_on) BETWEEN <<Entre le |date>> AND <<et le |date>>
     AND borrowers.branchcode = <<Bibliothèque |branches>>
 GROUP BY accountlines.debit_type_code
    

    

Voir aussi

Ressources de la communauté Koha

Last Updated On mars 04, 2021