Différences

Ci-dessous, les différences entre deux révisions de la page.

--- cynod:config_avancee:controletransactions [2024/05/05 13:25]
yannick.bribaud créée
+++ cynod:config_avancee:controletransactions [2024/05/05 14:35] (Version actuelle)
yannick.bribaud
@@ Ligne 1: / Ligne 1: @@
 ====== 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
+ <WRAP left round box 100%>
+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;
+</WRAP>
+----
+  * Nombre d'operations par mois sur une année données
+<WRAP left round box 100%>
+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;
+</WRAP>
+----
+  * Nombre d'opérations historisées par mois sur une année
+<WRAP left round box 100%>
+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;
+</WRAP>
+  * Liste historique opérations sans écritures sur une années
+<WRAP left round box 100%>
+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;
+</WRAP>
+  * Historique opérations par mois et sur un intervalle d'années
+<WRAP left round box 100%>
+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;
+</WRAP>
+  * Les opérations sans leurs écritures
+<WRAP left round box 100%>
+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;
+</WRAP>
+  * Vérification de toutes les opérations historisées ayant un statut <**validé**> et qui sont sans écritures
+<WRAP left round box 100%>
+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;
+</WRAP>
+  * Liste des opérations et leurs nombres d'écritures
+<WRAP center round box 100%>
+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;
+</WRAP>
+  * Liste des opérations historisées et leurs nombres d'écritures
+<WRAP left round box 100%>
+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;
+</WRAP>
-  - Vérification de toutes les écritures historisées et leurs opérations manquantes sur une période:
-  -