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