Requêtes de vérification de disparition de transactions

Ces requêtes doivent être exécutées sur la base de données en production du client afin de prendre connaissance d'une possible disparition de transactions.

Vérification de toutes les écritures historisées et leurs opérations manquantes sur une période

SELECT distinct ho.numero_operation AS numero_operation, ho.date_created AS date_operation, he.numero_operation AS historique_ecriture_numero_operation, he.date_ecriture FROM historique_ecriture he LEFT JOIN historique_operation ho ON he.numero_operation = ho.numero_operation WHERE ho.numero_operation IS NULL AND EXTRACT(YEAR FROM he.date_created) in ('2023','2024') ORDER BY he.date_ecriture DESC, ho.date_created DESC;

Nombre d'operations par mois sur une année données

SELECT EXTRACT(YEAR FROM o.date_created) AS Annee, EXTRACT(MONTH FROM o.date_created) AS Mois, COUNT(*) AS Nombre_transaction FROM operation o WHERE EXTRACT(YEAR FROM o.date_created) = '2022' GROUP BY EXTRACT(YEAR FROM o.date_created), EXTRACT(MONTH FROM o.date_created) ORDER BY Annee, Mois;

Nombre d'opérations historisées par mois sur une année

SELECT EXTRACT(YEAR FROM ho.date_created) AS Annee, EXTRACT(MONTH FROM ho.date_created) AS Mois, COUNT(*) AS Nombre_transaction FROM historique_operation ho WHERE EXTRACT(YEAR FROM ho.date_created) = '2023' GROUP BY EXTRACT(YEAR FROM ho.date_created), EXTRACT(MONTH FROM ho.date_created) ORDER BY Annee, Mois;

Liste historique opérations sans écritures sur une années

SELECT ho.date_created, ho.numero_operation AS hist_operation, he.numero_operation as hist_ecriture_numOperation, he.date_ecriture FROM historique_operation ho LEFT JOIN historique_ecriture he ON ho.numero_operation = he.numero_operation where EXTRACT(YEAR FROM ho.date_created) = '2020' and he.numero_operation is null ORDER BY ho.date_created DESC, he.date_ecriture DESC;

Historique opérations par mois et sur un intervalle d'années

SELECT EXTRACT(YEAR FROM ho.date_created) AS Annee, EXTRACT(MONTH FROM ho.date_created) AS Mois, COUNT(*) AS Nombre_transaction FROM historique_operation ho WHERE EXTRACT(YEAR FROM ho.date_created) IN ('2023', '2024') GROUP BY EXTRACT(YEAR FROM ho.date_created), EXTRACT(MONTH FROM ho.date_created) ORDER BY Annee, Mois;

Les opérations sans leurs écritures

SELECT o.id, o.date_created, o.numero_operation AS operation, e.operation_id as ecriture_operation_id, e.date_ecriture FROM operation o LEFT JOIN ecriture e ON o.id = e.operation_id where e.operation_id is null ORDER BY o.date_created, e.date_ecriture;

Vérification de toutes les opérations historisées ayant un statut <validé> et qui sont sans écritures

SELECT ho.numero_operation AS numero_operation, ho.date_created AS date_operation, he.numero_operation AS hist_ecriture_numOperation, he.date_ecriture, ho.statut AS statut FROM historique_operation ho LEFT JOIN historique_ecriture he ON ho.numero_operation = he.numero_operation WHERE ho.statut = 'VA' AND he.numero_operation IS NULL ORDER BY he.date_ecriture ASC, ho.date_created ASC;

Liste des opérations et leurs nombres d'écritures

SELECT EXTRACT(YEAR FROM o.date_created) AS Annee, EXTRACT(MONTH FROM o.date_created) AS Mois, COUNT(*) AS Nombre_transaction, (SELECT COUNT(*) FROM ecriture e WHERE EXTRACT(YEAR FROM e.date_created) = '2024' AND EXTRACT(MONTH FROM e.date_created) = EXTRACT(MONTH FROM o.date_created) ) AS nombre_ecriture FROM operation o WHERE EXTRACT(YEAR FROM o.date_created) = '2024' GROUP BY EXTRACT(YEAR FROM o.date_created), EXTRACT(MONTH FROM o.date_created) ORDER BY Annee, Mois;

Liste des opérations historisées et leurs nombres d'écritures

SELECT EXTRACT(YEAR FROM ho.date_created) AS Annee, EXTRACT(MONTH FROM ho.date_created) AS Mois, COUNT(*) AS Nombre_transaction, (SELECT COUNT(*) FROM historique_ecriture he WHERE EXTRACT(YEAR FROM he.date_created) = '2023' AND EXTRACT(MONTH FROM he.date_created) = EXTRACT(MONTH FROM ho.date_created) ) AS Nombre_historique_ecriture FROM historique_operation ho WHERE EXTRACT(YEAR FROM ho.date_created) = '2023' GROUP BY EXTRACT(YEAR FROM ho.date_created), EXTRACT(MONTH FROM ho.date_created) ORDER BY Annee, Mois;