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
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) = "2020-01-07" 
     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) = "2020-01-07" 
     AND branchcode = <<Bibliothèque |branches>> 
 GROUP BY accounttype;

Voir aussi

Ressources de la communauté Koha

Last Updated On mai 05, 2020