Afficher la pageAnciennes révisionsLiens de retourAjouter au livre.Exporter en PDFTout plier/déplierExportation ODTHaut de page Vous affichez une ancienne révision du document ! Si vous l'enregistrez, vous créerez une nouvelle version avec ce contenu. Sélection de fichiers ====== 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** <color #00a2e8>select pin \\ from wallet \\ where wallet_id=22791746657;</color>\\ * **Augmenter la durée de validité des cartes sur X années** <color #00a2e8> 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; \\ </color>\\ * **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>\\ * ** Listes des notifications non délivrées par SMSGW **\\ <color #00a2e8>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; </color>\\ * ** Lever l'opposition ticket carburant expiré **\\ <color #00a2e8> 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";</color>\\ * ** 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 \\ <color #00a2e8> 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";</color>\\ Etape 2- Suppression des tickets carburant sur la table historique \\ <color #00a2e8>delete from \\ historique_ticket_carburant \\ where \\ numero between "822001960000048586" \\ and "822001960000048785";</color>\\ Etape 3- Lever l'opposition des tickets carburant\\ <color #00a2e8>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;</color>\\ * ** Mettre à jour la matrice de version **\\ <color #00a2e8>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");</color>\\ * ** Afficher la liste des notifications PIN générés côté BO sur une période **\\ <color #00a2e8>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%";</color>\\ * ** Prolonger la date de fin de validité d'une carte **\\ 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 \\ 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;</color>\\ Etape 2- Mettre à jour les cartes expirant sur une période \\ <color #00a2e8>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>\\ * **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 \\ <color #00a2e8>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;</color>\\ Etape 2- Mettre à jour les cartes expirées\\ <color #00a2e8>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\\ <color #00a2e8>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;</color>\\ Etape 4- Mettre à jour la colonne opposition_carte_id de la table carte\\ <color #00a2e8> 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;</color>\\ Etape 5- Supprimer la table temporaire\\ <color #00a2e8>drop table tmp_carte_levee_opposition;\\ </color> * ** Rendre une carte mise en opposition automatiquement visible sur la fonctionnalité levée opposition **\\ <color #00a2e8>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");</color>\\ * **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 \\ <color #00a2e8>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";\\</color> Etape 2- Transférer la carte depuis la table historique vers la table carte\\ <color #00a2e8>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";\\</color> Etape 3- Mettre à jour la colonne opposition_carte_id de la table carte\\ <color #00a2e8>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";\\</color> * ** Lister les opérations n'ayant pas générées d'écriture **\\ <color #00a2e8>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;</color>\\ * ** Virtualiser une carte ** \\ * <color #00a2e8>update carte set statut = 'VA' where num_carte = ‘5812340101000260’;</color>\\ * <color #00a2e8>update lot_carte set statut = 'LV' where numero_lot =’3510’;</color>\\ * ** Connaître le numéro de lot d'une carte **\\ <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 **\\ * <color #00a2e8>update carte set statut = 'IM' where num_carte="5812340100049854";</color>\\ * <color #00a2e8>update lot_carte set statut = 'IM' where numero_lot = "1810";</color>\\ * ** Permettre d'imprimer à nouveau une carte **\\ * <color #00a2e8>update carte set statut = 'SA' where num_carte="5812340100049854";</color>\\ * <color #00a2e8>update lot_carte set statut = 'VA' where numero_lot = "1810";</color>\\ * ** Affecter une carte prépayée à un autre point de service **\\ <color #00a2e8>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");</color>\\ * ** Supprimer tous les rapports jasper de CYNOD pour faire une nouvelle MAJ **\\ * <color #00a2e8>set foreign_key_checks=0;</color>\\ * <color #00a2e8>truncate jasper_report_unit_resource;</color>\\ * <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 1- Récupération de l'ID du compte Wallet avant suppression\\ <color #00a2e8>select id from wallet w where wallet_id = "221775784470";</color>\\ **__NB__ : Il est important de bien noté cet id qui va servir à l'étape 6**\\ Etape 2- Désactivation contrainte \\ <color #00a2e8>SET FOREIGN_KEY_CHECKS=0;</color>\\ Etape 3- Suppression du wallet_terminal \\ <color #00a2e8>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");</color>\\ Etape 4- Suppression du wallet_carte \\ <color #00a2e8>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>\\ Etape 5- Suppression du compte wallet \\ <color #00a2e8>delete from wallet where wallet_id="22792544040";</color>\\ Etape 6- Activation contrainte \\ <color #00a2e8>SET FOREIGN_KEY_CHECKS=1;</color>\\ Etape 7- Mise à jour des opérations wallet suite à la nouvelle activation du client \\ # 1\\ <color #00a2e8>update historique_operation set\\ wallet_id = (select id from wallet w where wallet_id = "221775784470")\\ where wallet_id = 4 or wallet_receiver_id = 4;</color>\\ #2\\ <color #00a2e8>update operation set\\ wallet_id = (select id from wallet w where wallet_id = "221775784470")\\ where wallet_id = 4 or wallet_receiver_id = 4;</color>\\ **__NB__ : Merci de remplacer les valeurs du wallet_id et du wallet_receiver_id par l'id récupéré à l'étape 1**\\ * **Rechercher l'existence d'une transaction sur Cynod **\\ <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')\\ 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é**\\ <color #00a2e8>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";</color> * **Mise à jour date erronée des transactions**\\ ### Transactions archivées \\ <color #00a2e8>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');\\</color> <color #00a2e8>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');\\</color> ### Transactions non archivées \\ <color #00a2e8>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');</color>\\ <color #00a2e8>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');</color>\\ Enregistrer Aperçu Annuler 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 tecdoc/requetesql.1724833198.txt.gz Dernière modification: 2024/08/28 08:19par mamadou.gueye_sensoft.sn