Liste et description des requêtes SQL les plus utilisés sur CYNOD

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

select pin
from wallet
where wallet_id=22791746657;

* Remettre le statut de bon de commande ticket a généré pour impression de la maquette PDF

update bon_commande b
set b.statut = “G”,
b.version = b.version + 1,
b.user_update = “sensoft_mamadou”,
b.last_updated =now()
where
b.reference like “%2104112023070223%”;

* Listes des notifications non délivrées par SMSGW

select
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”
from
com_message_indiv_web_service c
where date_created >=“2022-11-18 18:00:00”
and flag_envoi_sms = 0;

* Lever l'opposition ticket carburant expiré

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

* Lever l'opposition des tickets carburant valides et archivés

Etape 1- Transférer les tickets archivés depuis la table historique vers la table principale ticket carburant

INSERT INTO ticket_carburant
(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”;

Etape 2- Suppression des tickets carburant sur la table historique

delete from
historique_ticket_carburant
where
numero between “822001960000048586”
and “822001960000048785”;

Etape 3- Lever l'opposition des tickets carburant

update ticket_carburant set
statut = “V”,
last_updated = now(),
comptabilise = 0,
user_update = “mindinga”,
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;

* Mettre à jour la matrice de version

INSERT INTO matrice_version
(version,
action,
date_created,
date_version_mobile,
date_version_serveur,
deleted,
etablissement_id,
last_updated,
user_create,
user_update,
version_mobile,
version_serveur)
VALUES
(1,
“OK”,
now(),
now(),
now(),
False,
1,
now(),
“amadou.diop”,
“amadou.diop”, “3.1.16”,
“3.6.2_4”);

* Afficher la liste des notifications PIN générés côté BO sur une période

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,
cm.code_etatsms,c.objet, u.email, u.tel_mobile
from
com_message c, com_message_indiv cm, user u
where
c.date_created BETWEEN '2022-10-24' and '2022-10-29'
and cm.com_message_id = c.id
and cm.destinataire_individuel_id = u.id
and c.objet like “%pin%”;

* Prolonger la date de fin de validité d'une carte

Etape 1- Lister des cartes expirant sur une période

select c.intitule, c.num_carte, c.solde_online, c.debut_validite, c.fin_validite
from carte c
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')
and c.ind_annulation = false
and c.statut = “VA”
and c.is_genere = false
and c.ind_corporate = false order by c.fin_validite,c.intitule desc;

Etape 2- Mettre à jour les cartes expirant sur une période

update carte c set user_update = “sensoft_mamadou”,
last_updated = Now(),
version = version + 1,
fin_validite = '2022-12-31 23:59:59',
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')
and c.statut = “VA”
and c.is_genere = false
and ind_corporate = false;

* 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

Create table tmp_carte_levee_opposition as
Select *
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
and c.deleted = false
and c.is_genere = false
and c.ind_annulation = True
and c.ind_corporate = false;

Etape 2- Mettre à jour les cartes expirées

update tmp_carte_levee_opposition tmp, carte c
set c.user_update = “sensoft_mamadou”,
c.last_updated = Now(),
c.version = c.version +1,
c.fin_validite = '2024-12-31 23:59:59',
c.statut=“VA”,
c.ind_annulation = false
Where c.id = tmp.id;

Etape 3- Transférer les cartes 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`,
0,
NOW(),
op.`motif`,
op.`opposition`,
“sensoft_mamadou”,
“sensoft_mamadou”,
op.`from_portal`
From
opposition_carte op,
tmp_carte_levee_opposition tmp
Where op.carte_id = tmp.id;

Etape 4- 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.deleted = FALSE;

Etape 5- Supprimer la table temporaire

drop table tmp_carte_levee_opposition;

* 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”);

* 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,
0,
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”;\\

* Lister les opérations n'ayant pas générées d'écriture

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;

* Virtualiser une carte

• update carte set statut = 'VA' where num_carte = ‘5812340101000260’;
  
• update lot_carte set statut = 'LV' where numero_lot =’3510’;
  

* Connaître le numéro de lot d'une carte
select l.numero_lot
from lot_carte l, carte c
where c.num_carte =“5812340100006805” and c.lot_carte_id=l.id;

* Permettre d'encoder à nouveau une carte

• update carte set statut = 'IM' where num_carte=“5812340100049854”;
  
• update lot_carte set statut = 'IM' where numero_lot = “1810”;
  

* Permettre d'imprimer à nouveau une carte

• update carte set statut = 'SA' where num_carte=“5812340100049854”;
  
• update lot_carte set statut = 'VA' where numero_lot = “1810”;
  

* Affecter une carte prépayée à un autre point de service

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”);

* Supprimer tous les rapports jasper de CYNOD pour faire une nouvelle MAJ

• set foreign_key_checks=0;
  
• truncate jasper_report_unit_resource;
  
• truncate jasper_report_unit_resource_role;
  
• set foreign_key_checks=1;
  

* Supprimer l'enrolement Wallet d'un client sur Cynod

Etape 1- Désactivation contrainte
SET FOREIGN_KEY_CHECKS=0;

Etape 2- Suppression du wallet_terminal
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”);

Etape 3- 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”);

Etape 4- Suppression du compte wallet
delete from wallet where wallet_id=“22792544040”;

Etape 5- Activation contrainte
SET FOREIGN_KEY_CHECKS=1;

* Rechercher l'existence d'une transaction sur Cynod
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')
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');

* Désactiver restriction Mot de passe expiré
update user u set
password_expired = false,
u.user_update = “sensoft_arame”,
u.last_updated = now(),
u.version = u.version + 1
where
u.username = “sensoft_ibrahima”;

* Mise à jour date erronée des transactions

### Transactions archivées
update historique_ecriture e, historique_operation o
set e.date_ecriture = “2024-02-08 22:40:00”,
e.last_updated = NOW(),
e.user_update = “djibril.traore”,
e.version = e.version +1
where e.numero_operation=o.numero_operation
and o.numero_operation in
('5806-0043-700102202211','5806-0043-700102202530');\\

update historique_operation o, historique_ecriture e
set o.date_transaction = e.date_ecriture,
o.last_updated = NOW(),
o.user_update = “djibril.traore”,
o.version = o.version +1
where o.numero_operation = e.numero_operation
and o.numero_operation in
('5806-0043-700102202211','5806-0043-700102202530');\\

### Transactions non archivées
update ecriture e, operation o
set e.date_ecriture = “2024-02-08 22:40:00”,
e.last_updated = NOW(),
e.user_update = “djibril.traore”,
e.version = e.version +1
where e.operation_id =o.id
and o.numero_operation in
('5806-0043-700102202211','5806-0043-700102202530');

update operation o, ecriture e
set o.date_transaction = e.date_ecriture,
o.last_updated = NOW(),
o.user_update = “djibril.traore”,
o.version = o.version +1
where o.id = e.operation_id
and o.numero_operation in
('5806-0043-700102202211','5806-0043-700102202530');