✎ tecdoc:requetesql [cynod]

Vous affichez une ancienne révision du document ! Si vous l'enregistrez, vous créerez une nouvelle version avec ce contenu.

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

<code sql>
-- Récupérer le code Pin d'un client Wallet**

select pin
from wallet
where wallet_id=22791746657
</code>

<code sql>
-- 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;
</code>

<code sql>
-- Remettre le statut de bon de commande ticket a généré pour impression de la maquette PDF

<color #00a2e8>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%";</color>
</code>

<code sql>
-- 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;
</code>

<code sql>
-- 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";
</code>

<code sql>
-- 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;
</code>

<code sql>
-- 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");
</code>

<code sql>
-- 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%";
</code>

<code sql>
-- 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;</color>
</code>

<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

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;</color>

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;
</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");

-- 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";
</code>

<code sql>
-- 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;
</code>

<code sql>
-- Virtualiser une carte

* update carte  set statut = 'VA' where num_carte = ‘5812340101000260’;
  * update lot_carte set statut = 'LV' where numero_lot =’3510’;
</code>

<code sql>
-- 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;
</code>

<code sql>
-- 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";
</code>

<code sql>
-- 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";
</code>

<code sql>
-- 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");
</code>

<code sql>            
-- 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;
</code>

<code sql>
-- Supprimer l'enrôlement Wallet d'un client sur Cynod

Etape 1- Récupération de l'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 à l'é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 l'id récupéré à l'étape 1**
</code>

<code sql>
-- 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');
</code>

<code sql>
-- 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";
</code>

<code sql>
-- 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');
</code>

Résumé

Note : En modifiant cette page, vous acceptez que le contenu soit placé sous les termes de la licence suivante : CC Attribution-Share Alike 4.0 International