Rapport : Paiements reçus à une date, par type de frais

Aller dans Bilans et statistiques > Créer à partir de SQL
Donner un nom au rapport
Au besoin, le classer dans un groupe/sous-groupe
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:
Koha 20.05 et versions +
- Rapport de base
- Rapport version de base (pour plusieurs bibliothèques)

Koha 21.05
- Rapport simplifié

Koha 22.05 et +

Koha 19.05 et versions antérieures

Cliquer pour consulter les rapports des versions 19.05 et moins

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

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

Cliquer pour consulter les rapports de la 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

Version 21.05 – rapport simplifié

Cliquer pour consulter les rapports de la version 21.05

Depuis la version 20.05, Koha permet de personnaliser les libellés de types de frais et types de crédits directement par l’interface professionnelle. Enfin, il n’est plus nécessaire de décrire manuellement les types de frais dans le rapport de caisse.

Vous pouvez utiliser ce rapport simplifié:

SELECT
     account_debit_types.description AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
FROM
     account_offsets
     JOIN accountlines AS debit ON (debit.accountlines_id = account_offsets.debit_id)
     JOIN accountlines AS credit ON (credit.accountlines_id = account_offsets.credit_id)
     JOIN account_debit_types ON (debit.debit_type_code = account_debit_types.code)
WHERE
     type IN ("Payment", "Credit Applied")
     AND DATE(created_on) BETWEEN <<Date entre le(aaaa-mm-jj)|date>> AND <<et le|date>>
     AND account_offsets.debit_id IS NOT NULL
     AND credit.credit_type_code !="LOST_FOUND"
GROUP BY debit.debit_type_code

À noter: ce rapport cherche aussi les transactions du bouton «Payer le montant», celles enregistrées comme étant «Credit applied» (bogue de version 21.05)

Version 22.05 et plus

Certains termes ont changé dans la version 22.05.

Faites attention aux critères de recherche de la section « WHERE », on doit voir account_offsets.type IN (« APPLY ») pour rechercher des paiements.

Rapport 22.05 de base pour une date précise

SELECT
     account_debit_types.description AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
FROM
     account_offsets
     JOIN accountlines AS debit ON (debit.accountlines_id = account_offsets.debit_id)
     JOIN accountlines AS credit ON (credit.accountlines_id = account_offsets.credit_id)
     JOIN account_debit_types ON (debit.debit_type_code = account_debit_types.code)
WHERE
     type IN ("APPLY")
     AND DATE(created_on)=<<Choisir la date(aaaa-mm-jj)|date>>
     AND account_offsets.debit_id IS NOT NULL
     AND credit.credit_type_code !="LOST_FOUND"
GROUP BY debit.debit_type_code

Rapport 22.05 pour une date et une bibliothèque précise

SELECT
     account_debit_types.description AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
FROM
     account_offsets
     JOIN accountlines AS debit ON (debit.accountlines_id = account_offsets.debit_id)
     JOIN accountlines AS credit ON (credit.accountlines_id = account_offsets.credit_id)
     JOIN account_debit_types ON (debit.debit_type_code = account_debit_types.code)
     LEFT JOIN borrowers ON (credit.manager_id = borrowers.borrowernumber)
WHERE
     type IN ("APPLY")
     AND DATE(created_on)=<<Choisir la date(aaaa-mm-jj)|date>>
     AND account_offsets.debit_id IS NOT NULL
     AND credit.credit_type_code !="LOST_FOUND"
     AND borrowers.branchcode=<<Bibliothèque |branches>>
GROUP BY debit.debit_type_code

Rapport 22.05 pour une plage de date pour l’ensemble de vos bibliothèques

SELECT
     account_debit_types.description AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
FROM
     account_offsets
     JOIN accountlines AS debit ON (debit.accountlines_id = account_offsets.debit_id)
     JOIN accountlines AS credit ON (credit.accountlines_id = account_offsets.credit_id)
     JOIN account_debit_types ON (debit.debit_type_code = account_debit_types.code)
WHERE
     type IN ("APPLY")
     AND DATE(created_on) BETWEEN <<Date entre le(aaaa-mm-jj)|date>> AND <<et le|date>>
     AND account_offsets.debit_id IS NOT NULL
     AND credit.credit_type_code !="LOST_FOUND"
GROUP BY debit.debit_type_code

Rapport 22.05 d’une plage de dates et une bibliothèques précise

SELECT
     account_debit_types.description AS "Type de frais",
     SUM(ABS(ROUND(account_offsets.amount,2))) AS "Montant"
FROM
     account_offsets
     JOIN accountlines AS debit ON (debit.accountlines_id = account_offsets.debit_id)
     JOIN accountlines AS credit ON (credit.accountlines_id = account_offsets.credit_id)
     JOIN account_debit_types ON (debit.debit_type_code = account_debit_types.code)
     LEFT JOIN borrowers ON (credit.manager_id = borrowers.borrowernumber)
WHERE
     type IN ("APPLY")
     AND DATE(created_on) BETWEEN <<Date entre le(aaaa-mm-jj)|date>> AND <<et le|date>>
     AND account_offsets.debit_id IS NOT NULL
     AND credit.credit_type_code !="LOST_FOUND"
     AND borrowers.branchcode=<<Bibliothèque |branches>>
GROUP BY debit.debit_type_code

Voir aussi

Ressources de la communauté Koha

SQL reports library (anglais)
Basic SQL for Koha users (anglais)

Tags: