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