Différences

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

--- tecdoc:requetesql [2024/05/17 11:55]
mamadou.gueye_sensoft.sn
+++ tecdoc:requetesql [2025/02/10 14:34] (Version actuelle)
mamadou.gueye_sensoft.sn
@@ Ligne 3: / Ligne 3: @@
 Le but de cette documentation est de lister puis de commenter les différentes requêtes SQL qui sont les plus utilisés au sein sur CYNOD.\\
-* **Récupérer le code Pin d'un client Wallet**
+<code sql>
+-- Récupérer le code Pin d'un client Wallet**
-<color #00a2e8>select pin \\
+select pin
-from wallet \\
+from wallet
-where wallet_id=22791746657;</color>\\
+where wallet_id=22791746657
+</code>
-* **Remettre le statut de bon de commande ticket a généré pour impression de la maquette PDF **\\
+<code sql>
+-- Augmenter la durée de validité des cartes sur X années**
-<color #00a2e8>update bon_commande  b\\
+update carte c set
-set b.statut = "G",\\
+user_update = "sensoft_arame",
-b.version  = b.version + 1,\\
+last_updated = Now(),
-b.user_update = "sensoft_mamadou",\\
+version = version + 1,
-b.last_updated =now()\\
+fin_validite = date_add(c.fin_validite , INTERVAL 2 YEAR)
-where \\
+where
-b.reference like "%2104112023070223%";</color>\\
+c.statut = "VA"
+and c.is_genere = false
+and ind_corporate = false;
+</code>
-* ** Listes des notifications non délivrées par SMSGW **\\
+<code sql>
+-- Remettre le statut de bon de commande ticket a généré pour impression de la maquette PDF
-<color #00a2e8>select \\
+update bon_commande  b
-c.version, c.code_etatsms,c.cout_service_sms, c.com_message_indiv_source_id,c.date_envoi_sms, date_message,c.cout_service_sms,\\ c.date_created,last_updated ,c.libelle_service,c.numero_destinataire,c.sender_etablissement_code,c.source,c.type_service,\\ c.max_sms_resend_retry,country_code ,c.flag_envoi_sms +"0",c.flag_traitement_sms +"0", c.ind_service_sms_facture +"0",c.ind_service_sms_paye +"0",\\ c.sms_resend_retry +"0"\\
+set b.statut = "G",
-from\\
+b.version  = b.version + 1,
-com_message_indiv_web_service c \\
+b.user_update = "sensoft_mamadou",
+b.last_updated =now()
 where
-date_created >="2022-11-18 18:00:00" \\
+b.reference like "%2104112023070223%";
-and flag_envoi_sms = 0; </color>\\
+</code>
-* ** Lever l'opposition ticket carburant expiré **\\
+<code sql>
+-- Listes des notifications non délivrées par SMSGW
-<color #00a2e8>
+select
-update \\
+c.version, c.code_etatsms,c.cout_service_sms, c.com_message_indiv_source_id,c.date_envoi_sms, date_message,c.cout_service_sms, c.date_created,last_updated ,c.libelle_service,c.numero_destinataire,c.sender_etablissement_code,c.source,c.type_service, c.max_sms_resend_retry,country_code ,c.flag_envoi_sms +"0",c.flag_traitement_sms +"0", c.ind_service_sms_facture +"0",c.ind_service_sms_paye +"0", c.sms_resend_retry +"0"
-ticket_carburant \\
+from
-set \\
+com_message_indiv_web_service c
-last_updated = now(),\\
+where
-comptabilise = 0,\\
+date_created >="2022-11-18 18:00:00"
-fin_validation = "2023-02-15 23:59:59",\\
+and flag_envoi_sms = 0;
-user_update = "m.wade",\\
+</code>
-version = version + 1 \\
-where \\
-numero between "2000640000000001" and "2000640000001846" \\
-and statut = "V";</color>\\
-* ** Lever l'opposition des tickets carburant valides et archivés **\\
+<code sql>
+-- Lever l'opposition ticket carburant expiré
-Etape 1- Transférer les tickets archivés depuis la table historique vers la table principale ticket carburant \\
+update
+ticket_carburant
+set
+last_updated = now(),
+comptabilise = 0,
+fin_validation = "2023-02-15 23:59:59",
+user_update = "m.wade",
+version = version + 1
+where
+numero between "2000640000000001" and "2000640000001846"
+and statut = "V";
+</code>
-<color #00a2e8>
+<code sql>
-INSERT INTO ticket_carburant \\
+-- Lever l'opposition des tickets carburant valides et archivés
-(id,\\
-version,\\
-comptabilise,\\
-date_created,\\
-date_validation,\\
-deleted,\\
-etablissement_id,\\
-fin_validation,\\
-last_updated,\\
-lot_ticket_detail_id,\\
-montant,\\
-numero,\\
-qr_code,\\
-service_point_consommation,\\
-statut,\\
-user_consommation,\\
-user_create,\\
-user_update,\\
-user_validation,\\
-volume,\\
-code_secret,\\
-opposition_ticket_id)\\
-(select \\
-id,\\
-version,\\
-comptabilise,\\
-date_created,\\
-date_validation,\\
-deleted,\\
-etablissement_id,\\
-fin_validation,\\
-last_updated,\\
-lot_ticket_detail_id,\\
-montant,\\
-numero,\\
-qr_code,\\
-service_point_consommation,\\
-statut,\\
-user_consommation,\\
-user_create,\\
-user_update,\\
-user_validation,\\
-volume,\\
-code_secret,\\
-opposition_ticket_id\\
-from historique_ticket_carburant \\
-where numero between "822001960000048586" \\
-and "822001960000048785";</color>\\
-Etape 2- Suppression des tickets carburant sur la table historique \\
+Etape 1- Transférer les tickets archivés depuis la table historique vers la table principale ticket carburant
-<color #00a2e8>delete from \\
+INSERT INTO ticket_carburant
-historique_ticket_carburant \\
+(id,
-where \\
+version,
-numero between "822001960000048586" \\
+comptabilise,
-and "822001960000048785";</color>\\
+date_created,
+date_validation,
+deleted,
+etablissement_id,
+fin_validation,
+last_updated,
+lot_ticket_detail_id,
+montant,
+numero,
+qr_code,
+service_point_consommation,
+statut,
+user_consommation,
+user_create,
+user_update,
+user_validation,
+volume,
+code_secret,
+opposition_ticket_id)
+(select
+id,
+version,
+comptabilise,
+date_created,
+date_validation,
+deleted,
+etablissement_id,
+fin_validation,
+last_updated,
+lot_ticket_detail_id,
+montant,
+numero,
+qr_code,
+service_point_consommation,
+statut,
+user_consommation,
+user_create,
+user_update,
+user_validation,
+volume,
+code_secret,
+opposition_ticket_id
+from historique_ticket_carburant
+where numero between "822001960000048586"
+and "822001960000048785";
-Etape 3- Lever l'opposition des tickets carburant\\
+Etape 2- Suppression des tickets carburant sur la table historique
-<color #00a2e8>update ticket_carburant set \\
+delete from
-statut = "V",\\
+historique_ticket_carburant
-last_updated = now(),\\
+where
-comptabilise = 0,\\
+numero between "822001960000048586"
-user_update = "mindinga",\\
+and "822001960000048785";
-fin_validation = "2023-02-15 23:59:59",\\
-version = version + 1\\
-where\\
-numero between "822001960000048586" and "822001960000048785" \\
-and statut = "X" \\
-and opposition_ticket_id is not NULL;</color>\\
-* ** Mettre à jour la matrice de version **\\
+Etape 3- Lever opposition des tickets carburant
-<color #00a2e8>INSERT INTO matrice_version \\
+update ticket_carburant set
-(version, \\
+statut = "V",
-action, \\
+last_updated = now(),
-date_created, \\
+comptabilise = 0,
-date_version_mobile, \\
+user_update = "mindinga",
-date_version_serveur, \\
+fin_validation = "2023-02-15 23:59:59",
-deleted, \\
+version = version + 1
-etablissement_id, \\
+where
-last_updated, \\
+numero between "822001960000048586" and "822001960000048785"
-user_create, \\
+and statut = "X"
-user_update, \\
+and opposition_ticket_id is not NULL;
-version_mobile, \\
+</code>
-version_serveur)\\
-VALUES\\
+<code sql>
-(1,\\
+-- Mettre à jour la matrice de version
-"OK",\\
-now(),\\
+INSERT INTO matrice_version
-now(),\\
+(version,
-now(), \\
+action,
-False,\\
+date_created,
-,\\
+date_version_mobile,
-now(),\\
+date_version_serveur,
-"amadou.diop",\\
+deleted,
+etablissement_id,
+last_updated,
+user_create,
+user_update,
+version_mobile,
+version_serveur)
+VALUES
+(1,
+"OK",
+now(),
+now(),
+now(),
+False,
+,
+now(),
+"amadou.diop",
 "amadou.diop",
-"3.1.16",\\
+"3.1.16",
-"3.6.2_4");</color>\\
+"3.6.2_4");
+</code>
-* ** Afficher la liste des notifications PIN générés côté BO sur une période **\\
+<code sql>
+-- Afficher la liste des notifications PIN générés côté BO sur une période
-<color #00a2e8>select \\
+select
-c.date_depot, c.date_traitement, c.ind_msg_email+"0", c.ind_msg_releve+"0", c.ind_msg_valide+"0",\\ c.ind_traitement+"0",cm.is_email_sent+"0",cm.date_envoi,cm.is_sms_sent+"0",cm.date_created,cm.date_envoi_sms,cm.flag_envoi_sms+"0",\\ flag_envoi_email+"0",cm.ws_error_code,cm.ws_error_code_sms,cm.ws_error_code_mail,\\
+c.date_depot, c.date_traitement, c.ind_msg_email+"0", c.ind_msg_releve+"0", c.ind_msg_valide+"0", c.ind_traitement+"0",cm.is_email_sent+"0",cm.date_envoi,cm.is_sms_sent+"0",cm.date_created,cm.date_envoi_sms,cm.flag_envoi_sms+"0", flag_envoi_email+"0",cm.ws_error_code,cm.ws_error_code_sms,cm.ws_error_code_mail,
-cm.code_etatsms,c.objet, u.email, u.tel_mobile \\
+cm.code_etatsms,c.objet, u.email, u.tel_mobile
-from \\
+from
-com_message c, com_message_indiv cm, user u \\
+com_message c, com_message_indiv cm, user u
-where \\
+where
-c.date_created BETWEEN '2022-10-24' and '2022-10-29' \\
+c.date_created BETWEEN '2022-10-24' and '2022-10-29'
-and cm.com_message_id = c.id \\
+and cm.com_message_id = c.id
-and cm.destinataire_individuel_id = u.id\\
+and cm.destinataire_individuel_id = u.id
-and c.objet like "%pin%";</color>\\
+and c.objet like "%pin%";
+</code>
-* ** Prolonger la date de fin de validité d'une carte **\\
+<code sql>
+-- Prolonger la date de fin de validité d'une carte
-Etape 1- Lister des cartes expirant sur une période \\
+Etape 1- Lister des cartes expirant sur une période
-<color #00a2e8>select c.intitule, c.num_carte, c.solde_online, c.debut_validite, c.fin_validite \\
+select c.intitule, c.num_carte, c.solde_online, c.debut_validite, c.fin_validite
-from carte c \\
+from carte c
-where  \\
+where
-c.fin_validite >= STR_TO_DATE('01/01/2021 00:00', '%d/%m/%Y %H:%i') AND c.fin_validite <= STR_TO_DATE('31/12/2021 23:59', '%d/%m/%Y %H:%i') \\
+c.fin_validite >= STR_TO_DATE('01/01/2021 00:00', '%d/%m/%Y %H:%i') AND c.fin_validite <= STR_TO_DATE('31/12/2021 23:59', '%d/%m/%Y %H:%i')
- and c.ind_annulation = false \\
+ and c.ind_annulation = false
- and c.statut = "VA" \\
+ and c.statut = "VA"
- and c.is_genere = false \\
+ and c.is_genere = false
- and c.ind_corporate = false order by c.fin_validite,c.intitule desc;</color>\\
+ and c.ind_corporate = false order by c.fin_validite,c.intitule desc;
-Etape 2- Mettre à jour les cartes expirant sur une période \\
+Etape 2- Mettre à jour les cartes expirant sur une période
-<color #00a2e8>update carte c set user_update = "sensoft_mamadou", \\
+update carte c set user_update = "sensoft_mamadou",
-last_updated = Now(),\\
+last_updated = Now(),
-version = version + 1,\\
+version = version + 1,
-fin_validite = '2022-12-31 23:59:59', \\
+fin_validite = '2022-12-31 23:59:59',
-ind_annulation = false\\
+ind_annulation = false
-where fin_validite >= STR_TO_DATE('01/01/2022 00:00', '%d/%m/%Y %H:%i') AND fin_validite <= STR_TO_DATE('30/06/2022 23:59', '%d/%m/%Y %H:%i')\\
+where fin_validite >= STR_TO_DATE('01/01/2022 00:00', '%d/%m/%Y %H:%i') AND fin_validite <= STR_TO_DATE('30/06/2022 23:59', '%d/%m/%Y %H:%i')
-and c.statut = "VA"\\
+and c.statut = "VA"
-and c.is_genere = false\\
+and c.is_genere = false
-and ind_corporate = false;</color>\\
+and ind_corporate = false;
+</code>
-* **Lever opposition des cartes mises en opposition automatiquement et prolonger la date de fin de validité **\\
+<code sql>
+-- Lever opposition des cartes mises en opposition automatiquement et prolonger la date de fin de validité
-Etape 1- Lister des cartes expirées sur la période \\
+Etape 1- Lister des cartes expirées sur la période
-<color #00a2e8>Create table tmp_carte_levee_opposition as \\
+Create table tmp_carte_levee_opposition as
-Select *\\
+Select *
-From carte c\\
+From carte c
-where fin_validite >= "2021-01-01 00:00:00" and fin_validite <= "2021-12-31 23:59:59" and is_point_service = FALSE\\
+where fin_validite >= "2021-01-01 00:00:00" and fin_validite <= "2021-12-31 23:59:59" and is_point_service = FALSE
-and c.deleted = false\\
+and c.deleted = false
-and c.is_genere = false\\
+and c.is_genere = false
-and c.ind_annulation = True\\
+and c.ind_annulation = True
-and c.ind_corporate = false;</color>\\
+and c.ind_corporate = false;
-Etape 2- Mettre à jour les cartes expirées\\
+Etape 2- Mettre à jour les cartes expirées
-<color #00a2e8>update tmp_carte_levee_opposition tmp, carte c \\
+update tmp_carte_levee_opposition tmp, carte c
-set c.user_update = "sensoft_mamadou",\\
+set c.user_update = "sensoft_mamadou",
-c.last_updated = Now(),\\
+c.last_updated = Now(),
-c.version = c.version +1,\\
+c.version = c.version +1,
-c.fin_validite = '2024-12-31 23:59:59',\\
+c.fin_validite = '2024-12-31 23:59:59',
-c.statut="VA",\\
+c.statut="VA",
-c.ind_annulation = false\\
+c.ind_annulation = false
-Where c.id = tmp.id;</color>\\
+Where c.id = tmp.id;
-Etape 3- Transférer les cartes depuis la table historique vers la table carte\\
+Etape 3- Transférer les cartes depuis la table historique vers la table carte
-<color #00a2e8>INSERT INTO opposition_carte\\
+INSERT INTO opposition_carte
-(`version`,\\
+(`version`,
-`carte_id`,\\
+`carte_id`,
-`date_created`,\\
+`date_created`,
-`date_opposition`,\\
+`date_opposition`,
-`deleted`,\\
+`deleted`,
-`etablissement_id`,\\
+`etablissement_id`,
-`ind_opposition`,\\
+`ind_opposition`,
-`last_updated`,\\
+`last_updated`,
-`motif`,\\
+`motif`,
-`opposition`,\\
+`opposition`,
-`user_create`,\\
+`user_create`,
-`utilisateur_saisi`,\\
+`utilisateur_saisi`,
-`from_portal`)\\
+`from_portal`)
-select \\
+select
-op.`version`,\\
+op.`version`,
-op.`carte_id`,\\
+op.`carte_id`,
-NOW(),\\
+NOW(),
-NOW(),\\
+NOW(),
-op.`deleted`,\\
+op.`deleted`,
-op.`etablissement_id`,\\
+op.`etablissement_id`,
-,\\
+,
-NOW(),\\
+NOW(),
-op.`motif`,\\
+op.`motif`,
-op.`opposition`,\\
+op.`opposition`,
-"sensoft_mamadou",\\
+"sensoft_mamadou",
-"sensoft_mamadou",\\
+"sensoft_mamadou",
-op.`from_portal`\\
+op.`from_portal`
-From \\
+From
-	opposition_carte op,\\
+	opposition_carte op,
-	tmp_carte_levee_opposition tmp\\
+	tmp_carte_levee_opposition tmp
-Where op.carte_id = tmp.id;</color>\\
+Where op.carte_id = tmp.id;
-Etape 4- Mettre à jour la colonne opposition_carte_id de la table carte\\
+Etape 4- Mettre à jour la colonne opposition_carte_id de la table carte
-<color #00a2e8>
+SET SQL_SAFE_UPDATES = 0;
-SET SQL_SAFE_UPDATES = 0;\\
+update carte c
-update carte c\\
+set c.opposition_carte_id = (select o.id from opposition_carte o where o.carte_id = c.id order by o.date_created DESC limit 1)
-set c.opposition_carte_id = (select o.id from opposition_carte o where o.carte_id = c.id order by o.date_created DESC limit 1) \\
+where c.deleted = FALSE;
-where c.deleted = FALSE;</color>\\
-Etape 5- Supprimer la table temporaire\\
+Etape 5- Supprimer la table temporaire
+drop table tmp_carte_levee_opposition;
+</code>
+<code sql>
+-- Rendre une carte mise en opposition automatiquement visible sur la fonctionnalité levée opposition
+update carte
+set
+version = version + 1,
+last_updated = NOW(),
+user_update = "sensoft_mamadou",
+ind_annulation = false,
+fin_validite = "2025-01-13"
+where num_carte in ("5812340100034468","5812340100034088");
+</code>
+<code sql>
+-- Lever opposition d'une carte mise en opposition automatiquement et prolonger la date de fin de validité
+Etape 1- Mettre à jour les cartes expirant sur une période
+update carte c
+set c.user_update = "GUELADIO BA",
+c.last_updated = Now(),
+c.version = c.version + 1,
+c.fin_validite = '2029-12-31 23:59:59',
+c.statut="VA",
+c.ind_annulation = false
+Where
+c.num_carte ="5812340100041448";
+Etape 2- Transférer la carte depuis la table historique vers la table carte
+INSERT INTO opposition_carte(
+version,
+carte_id,
+date_created,
+date_opposition,
+deleted,
+etablissement_id,
+ind_opposition,
+last_updated,
+motif,
+opposition,
+user_create,
+utilisateur_saisi,
+from_portal)
+select
+op.version,
+op.carte_id,
+NOW(),
+NOW(),
+op.deleted,
+op.etablissement_id,
+,
+NOW(),
+op.motif,
+op.opposition,
+"GUELADIO BA",
+"GUELADIO BA",
+op.from_portal
+From
+    opposition_carte op,
+    carte c
+Where op.carte_id = c.id
+and c.num_carte ="5812340100041448";
+Etape 3- Mettre à jour la colonne opposition_carte_id de la table carte
+update carte c
+set c.opposition_carte_id = (select o.id from opposition_carte o where o.carte_id = c.id order by o.date_created DESC limit 1)
+where
+c.num_carte ="5812340100041448";
+</code>
-<color #00a2e8>drop table tmp_carte_levee_opposition;\\
+<code sql>
-</color>
+-- Lister les opérations sans écriture
-* ** Rendre une carte mise en opposition automatiquement visible sur la fonctionnalité levée opposition **\\
+SELECT
+o.numero_operation,
+o.date_transaction,
+o.date_created,
+o.code_evenement,
+o.montantttc,
+o.solde_avant_emission,
+o.solde_apres_emmission,
+o.solde_avant_reception,
+o.solde_apres_reception,
+o.motif,
+o.ind_annulation+"0",
+o.is_online + "0",
+c.num_carte,
+c.intitule,
+c.fin_validite as "fin valite carte"
+FROM operation o, carte c
+WHERE o.numero_operation
+AND o.statut = 'VA'
+AND o.deleted = false
+AND o.carte_emettrice_id = c.id
+AND c.statut = 'VA'
+AND o.id NOT IN (SELECT ecriture.operation_id FROM ecriture)
+ORDER BY o.date_created DESC, c.intitule;
+</code>
-<color #00a2e8>update carte\\
+<code sql>
-set\\
+-- Virtualiser une carte
-version = version + 1,\\
-last_updated = NOW(),\\
-user_update = "sensoft_mamadou",\\
-ind_annulation = false,\\
-fin_validite = "2025-01-13"\\
-where num_carte in ("5812340100034468","5812340100034088");</color>\\
-* **Lever opposition d'une carte mise en opposition automatiquement et prolonger la date de fin de validité **\\
+  * update carte  set statut = 'VA' where num_carte = ‘5812340101000260’;
+  * update lot_carte set statut = 'LV' where numero_lot =’3510’;
+</code>
-Etape 1- Mettre à jour les cartes expirant sur une période \\
+<code sql>
+-- Connaître le numéro de lot d'une carte
-<color #00a2e8>update carte c \\
+select l.numero_lot
-set c.user_update = "GUELADIO BA",\\
+from lot_carte l, carte c
-c.last_updated = Now(),\\
+where c.num_carte ="5812340100006805" and c.lot_carte_id=l.id;
-c.version = c.version + 1,\\
+</code>
-c.fin_validite = '2029-12-31 23:59:59',\\
-c.statut="VA",\\
-c.ind_annulation = false\\
-Where \\
-c.num_carte ="5812340100041448";\\</color>
-Etape 2- Transférer la carte depuis la table historique vers la table carte\\
+<code sql>
+-- Permettre d'encoder à nouveau une carte
-<color #00a2e8>INSERT INTO opposition_carte(\\
+  * update carte  set statut = 'IM' where num_carte="5812340100049854";
-version,\\
+  * update lot_carte set statut = 'IM' where numero_lot = "1810";
-carte_id,\\
+</code>
-date_created,\\
-date_opposition,\\
-deleted,\\
-etablissement_id,\\
-ind_opposition,\\
-last_updated,\\
-motif,\\
-opposition,\\
-user_create,\\
-utilisateur_saisi,\\
-from_portal)\\
-select \\
+<code sql>
-op.version,\\
+-- Permettre d'imprimer à nouveau une carte
-op.carte_id,\\
-NOW(),\\
-NOW(),\\
-op.deleted,\\
-op.etablissement_id,\\
-,\\
-NOW(),\\
-op.motif,\\
-op.opposition,\\
-"GUELADIO BA",\\
-"GUELADIO BA",\\
-op.from_portal \\
-From\\
-    opposition_carte op,\\
-    carte c\\
-Where op.carte_id = c.id \\
-and c.num_carte ="5812340100041448";\\</color>
-Etape 3- Mettre à jour la colonne opposition_carte_id de la table carte\\
+  * update carte  set statut = 'SA' where num_carte="5812340100049854";
+  * update lot_carte set statut = 'VA' where numero_lot = "1810";
+</code>
-<color #00a2e8>update carte c\\
+<code sql>
-set c.opposition_carte_id = (select o.id from opposition_carte o where o.carte_id = c.id order by o.date_created DESC limit 1)\\
+-- Affecter une carte prépayée à un autre point de service
-where \\
-c.num_carte ="5812340100041448";\\</color>
-* ** Lister les opérations n'ayant pas générées d'écriture **\\
+update
+    miseadisposition_carte_carte mcc,
+    miseadisposition_carte mc,
+    carte c
+    set    mc.point_service_id=34
+WHERE
+    mcc.miseadisposition_carte_cartes_id = mc.id
+        AND mcc.carte_id = c.id
+        and c.id in (SELECT
+            id
+        FROM
+            carte
+        WHERE
+            num_carte = "3538000000000000");
+</code>
-<color #00a2e8>SELECT \\
+<code sql>
-o.numero_operation,\\
+-- Supprimer tous les rapports jasper de CYNOD pour faire une nouvelle MAJ
-o.date_transaction,\\
-o.date_created,\\
-o.code_evenement,\\
-o.montantttc,\\
-o.solde_avant_emission,\\
-o.solde_apres_emmission,\\
-o.solde_avant_reception,\\
-o.solde_apres_reception,\\
-o.motif,\\
-o.ind_annulation+"0",\\
-o.is_online + "0",\\
-c.num_carte,\\
-c.intitule,\\
-c.fin_validite as "fin valite carte" \\
-FROM operation o, carte c\\
-WHERE o.numero_operation\\
-AND o.statut = 'VA'\\
-AND o.deleted = false\\
-AND o.carte_emettrice_id = c.id\\
-AND c.statut = 'VA'\\
-AND o.id NOT IN (SELECT ecriture.operation_id FROM ecriture)\\
-ORDER BY o.date_created DESC, c.intitule;</color>\\
-* ** Virtualiser une carte ** \\
+  * set foreign_key_checks=0;
+  * truncate jasper_report_unit_resource;
+  * truncate jasper_report_unit_resource_role;
+  * set foreign_key_checks=1;
+</code>
-  * <color #00a2e8>update carte  set statut = 'VA' where num_carte = ‘5812340101000260’;</color>\\
+<code sql>
-  * <color #00a2e8>update lot_carte set statut = 'LV' where numero_lot =’3510’;</color>\\
+-- Supprimer l'enrôlement Wallet d'un client sur Cynod
-* ** Connaître le numéro de lot d'une carte **\\
+Etape 1- Récupération de ID du compte Wallet avant suppression
-<color #00a2e8>select l.numero_lot \\
-from lot_carte l, carte c \\
-where c.num_carte ="5812340100006805" and c.lot_carte_id=l.id;</color> \\
-* ** Permettre d'encoder à nouveau une carte **\\
+select id from wallet w where wallet_id = "221775784470";
-  * <color #00a2e8>update carte  set statut = 'IM' where num_carte="5812340100049854";</color>\\
+NB : Il est important de bien noté cet id qui va servir à étape 7
-  * <color #00a2e8>update lot_carte set statut = 'IM' where numero_lot = "1810";</color>\\
-* ** Permettre d'imprimer à nouveau une carte **\\
+Etape 2- Désactivation contrainte
+SET FOREIGN_KEY_CHECKS=0;
-  * <color #00a2e8>update carte  set statut = 'SA' where num_carte="5812340100049854";</color>\\
+Etape 3- Suppression du wallet_terminal
-  * <color #00a2e8>update lot_carte set statut = 'VA' where numero_lot = "1810";</color>\\
+DELETE wallet_terminal
+FROM wallet_terminal
+INNER JOIN wallet ON wallet.id = wallet_terminal.wallet_id
+WHERE wallet.id in (select id from wallet where wallet_id ="22792544040");
-* ** Affecter une carte prépayée à un autre point de service **\\
+Etape 4- Suppression du wallet_carte
+DELETE wallet_carte
+FROM wallet_carte
+INNER JOIN wallet ON wallet.id = wallet_carte.wallet_id
+WHERE wallet.wallet_id in (select id from wallet where wallet_id ="22792544040");
-<color #00a2e8>update\\
+Etape 5- Suppression du compte wallet
-    miseadisposition_carte_carte mcc,\\
+delete from wallet where wallet_id="22792544040";
-    miseadisposition_carte mc,\\
-    carte c\\
-    set    mc.point_service_id=34\\
-WHERE\\
-    mcc.miseadisposition_carte_cartes_id = mc.id\\
-        AND mcc.carte_id = c.id\\
-        and c.id in (SELECT\\
-            id\\
-        FROM\\
-            carte\\
-        WHERE\\
-            num_carte = "3538000000000000");</color>\\
-* ** Supprimer tous les rapports jasper de CYNOD pour faire une nouvelle MAJ **\\
-  * <color #00a2e8>set foreign_key_checks=0;</color>\\
+Etape 6- Activation contrainte
-  * <color #00a2e8>truncate jasper_report_unit_resource;</color>\\
+SET FOREIGN_KEY_CHECKS=1;
-  * <color #00a2e8>truncate jasper_report_unit_resource_role;</color>\\
-  * <color #00a2e8>set foreign_key_checks=1;</color>\\
-* **Supprimer l'enrolement Wallet d'un client sur Cynod **
+Etape 7- Mise à jour des opérations wallet suite à la nouvelle activation du client
-Etape 1- Désactivation contrainte \\
+# 1
-<color #00a2e8>SET FOREIGN_KEY_CHECKS=0;</color>\\
+update historique_operation set
+wallet_id = (select id from wallet w where wallet_id = "221775784470")
+where wallet_id = 4 or wallet_receiver_id = 4;
-Etape 2- Suppression du wallet_terminal \\
+#2
-<color #00a2e8>DELETE wallet_terminal\\
+update operation set
-FROM wallet_terminal\\
+wallet_id = (select id from wallet w where wallet_id = "221775784470")
-INNER JOIN wallet ON wallet.id = wallet_terminal.wallet_id\\
+where wallet_id = 4 or wallet_receiver_id = 4;
-WHERE wallet.id in (select id from wallet where wallet_id ="22792544040");</color>\\
-Etape 3- Suppression du wallet_carte \\
+NB: Merci de remplacer les valeurs du wallet_id et du wallet_receiver_id par ID récupéré à l'étape 1
-<color #00a2e8>DELETE wallet_carte\\
+</code>
-FROM wallet_carte\\
-INNER JOIN wallet ON wallet.id = wallet_carte.wallet_id\\
-WHERE wallet.wallet_id in (select id from wallet where wallet_id ="22792544040");</color>\\
-Etape 4- Suppression du compte wallet \\
+<code sql>
-<color #00a2e8>delete from wallet where wallet_id="22792544040";</color>\\
+-- Rechercher l'existence d'une transaction sur Cynod
-Etape 5- Activation contrainte \\
+select numero_operation_offline as "numero", "Request Synchro Trx Offline" from request_synchro_trx_offline where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')
-<color #00a2e8>SET FOREIGN_KEY_CHECKS=1;</color>\\
+union
+select numero_operation_offline as "numero", "Operation_Offline_Synchronised" as tablename from operation_offline_synchronized where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')
+union
+select numero_operation_offline as "numero", "Numero_Operation_Offline" as "tablename" from operation_offline_synchronized_history where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')
+UNION
+select numero_operation_offline as "numero", "Operation Offline Failed Synchronized" as "tablename" from operation_offline_failed_synchronized where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')
+UNION
+select numero_operation  as "numero", "Anomalie Carte" as "tablename" from anomalies_carte where numero_operation in ('6201-0006-220807230912', '6201-0006-220807230720')
+UNION
+select numero_operation as "numero", "Historique Oepration" as "tablename" from historique_operation where numero_operation in ('6201-0006-220807230912', '6201-0006-220807230720')
+UNION
+select numero_operation as "numero", "Operation" as "tablename" from operation where numero_operation in ('6201-0006-220807230912', '6201-0006-220807230720');
+</code>
-* **Rechercher l'existence d'une transaction sur Cynod **\\
+<code sql>
-<color #00a2e8> select numero_operation_offline as "numero", "Request Synchro Trx Offline" from request_synchro_trx_offline where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')\\
+-- Désactiver restriction Mot de passe expiré
-union\\
-select numero_operation_offline as "numero", "Operation_Offline_Synchronised" as tablename from operation_offline_synchronized where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')\\
-union\\
-select numero_operation_offline as "numero", "Numero_Operation_Offline" as "tablename" from operation_offline_synchronized_history where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')\\
-UNION\\
-select numero_operation_offline as "numero", "Operation Offline Failed Synchronized" as "tablename" from operation_offline_failed_synchronized where numero_operation_offline in ('6201-0006-220807230912', '6201-0006-220807230720')\\
-UNION\\
-select numero_operation  as "numero", "Anomalie Carte" as "tablename" from anomalies_carte where numero_operation in ('6201-0006-220807230912', '6201-0006-220807230720')\\
-UNION\\
-select numero_operation as "numero", "Historique Oepration" as "tablename" from historique_operation where numero_operation in ('6201-0006-220807230912', '6201-0006-220807230720')\\
-UNION\\
-select numero_operation as "numero", "Operation" as "tablename" from operation where numero_operation in ('6201-0006-220807230912', '6201-0006-220807230720');</color>\\
-* **Désactiver restriction Mot de passe expiré**\\
+update user u set
-<color #00a2e8>update user u set\\
+password_expired = false,
-password_expired = false,\\
+u.user_update = "sensoft_arame",
-u.user_update = "sensoft_arame",\\
+u.last_updated = now(),
-u.last_updated = now(),\\
+u.version = u.version + 1
-u.version = u.version + 1\\
+where
-where\\
+u.username = "sensoft_ibrahima";
-u.username = "sensoft_ibrahima";</color>
+</code>
-* **Mise à jour date erronée des transactions**\\
+<code sql>
+-- Mise à jour date erronée des transactions
-### Transactions archivées \\
+### Transactions archivées
-<color #00a2e8>update historique_ecriture e, historique_operation o\\
+update historique_ecriture e, historique_operation o
-set e.date_ecriture = "2024-02-08 22:40:00",\\
+set e.date_ecriture = "2024-02-08 22:40:00",
-e.last_updated = NOW(),\\
+e.last_updated = NOW(),
-e.user_update = "djibril.traore",\\
+e.user_update = "djibril.traore",
-e.version = e.version +1\\
+e.version = e.version +1
-where e.numero_operation=o.numero_operation\\
+where e.numero_operation=o.numero_operation
-and o.numero_operation in\\
+and o.numero_operation in
-('5806-0043-700102202211','5806-0043-700102202530');\\</color>
+('5806-0043-700102202211','5806-0043-700102202530');
-<color #00a2e8>update historique_operation o, historique_ecriture e\\
+update historique_operation o, historique_ecriture e
-set o.date_transaction = e.date_ecriture,\\
+set o.date_transaction = e.date_ecriture,
-o.last_updated = NOW(),\\
+o.last_updated = NOW(),
-o.user_update = "djibril.traore",\\
+o.user_update = "djibril.traore",
-o.version = o.version +1\\
+o.version = o.version +1
-where o.numero_operation = e.numero_operation\\
+where o.numero_operation = e.numero_operation
-and o.numero_operation in\\
+and o.numero_operation in
-('5806-0043-700102202211','5806-0043-700102202530');\\</color>
+('5806-0043-700102202211','5806-0043-700102202530');
-### Transactions non archivées \\
+### Transactions non archivées
-<color #00a2e8>update ecriture e, operation o\\
+update ecriture e, operation o
-set e.date_ecriture = "2024-02-08 22:40:00",\\
+set e.date_ecriture = "2024-02-08 22:40:00",
-e.last_updated = NOW(),\\
+e.last_updated = NOW(),
-e.user_update = "djibril.traore",\\
+e.user_update = "djibril.traore",
-e.version = e.version +1\\
+e.version = e.version +1
-where e.operation_id =o.id\\
+where e.operation_id =o.id
-and o.numero_operation in\\
+and o.numero_operation in
-('5806-0043-700102202211','5806-0043-700102202530');</color>\\
+('5806-0043-700102202211','5806-0043-700102202530');
-<color #00a2e8>update operation o, ecriture e\\
+update operation o, ecriture e
-set o.date_transaction = e.date_ecriture,\\
+set o.date_transaction = e.date_ecriture,
-o.last_updated = NOW(),\\
+o.last_updated = NOW(),
-o.user_update = "djibril.traore",\\
+o.user_update = "djibril.traore",
-o.version = o.version +1\\
+o.version = o.version +1
-where o.id = e.operation_id\\
+where o.id = e.operation_id
-and o.numero_operation in\\
+and o.numero_operation in
-('5806-0043-700102202211','5806-0043-700102202530');</color>\\
+('5806-0043-700102202211','5806-0043-700102202530');
+</code>