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

-- Augmenter la durée de validité des cartes sur X années**
 
UPDATE carte c SET
user_update = "sensoft_arame",
last_updated = Now(),
version = version + 1,
fin_validite = date_add(c.fin_validite , INTERVAL 2 YEAR)
WHERE
c.statut = "VA"
AND c.is_genere = FALSE
AND ind_corporate = FALSE;

-- 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 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
 
SET SQL_SAFE_UPDATES = 0;
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 sans é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'enrôlement Wallet d'un client sur Cynod
 
Etape 1- Récupération de ID du compte Wallet avant suppression
 
SELECT id FROM wallet w WHERE wallet_id = "221775784470";
 
NB : Il est important de bien NOTé cet id qui va servir à étape 7
 
Etape 2- Désactivation contrainte
SET FOREIGN_KEY_CHECKS=0;
 
Etape 3- 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 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");
 
Etape 5- Suppression du compte wallet
DELETE FROM wallet WHERE wallet_id="22792544040";
 
Etape 6- Activation contrainte
SET FOREIGN_KEY_CHECKS=1;
 
Etape 7- Mise à jour des opérations wallet suite à la nouvelle activation du client
 
# 1
UPDATE historique_operation SET
wallet_id = (SELECT id FROM wallet w WHERE wallet_id = "221775784470")
WHERE wallet_id = 4 OR wallet_receiver_id = 4;
 
#2
UPDATE operation SET
wallet_id = (SELECT id FROM wallet w WHERE wallet_id = "221775784470")
WHERE wallet_id = 4 OR wallet_receiver_id = 4;
 
NB: Merci de remplacer les valeurs du wallet_id et du wallet_receiver_id par ID récupéré à l'étape 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');