select `premier`, `deuxieme`, `troisieme` from pronostics_kokanturf3_user where date_course = ( SELECT DATE_COURSE FROM preference WHERE preference.email = 'FELICITE@ANADER.CI' ) and pronostics_kokanturf3_user.email ='KOKANP@HOTMAIL.COM' ------------------------------------------------------1------------------------------------------------------------------------------------- -- suppression table DROP TABLE `base`; DROP TABLE `mei_a`; DROP TABLE `mei_b`; DROP TABLE `mei_c`; DROP TABLE `mei_d`; DROP TABLE `mei_e`;DROP TABLE `condition0`; DROP TABLE `condition2`; DROP TABLE `condition3`; DROP TABLE `condition4`; DROP TABLE `condition5`;DROP TABLE `resultat`; DROP TABLE stat; -- fin suppression table ------------------------------------------------------------------------------------------------------------------------------------------ Importer le ficher excel ------------------------------------------------2------------------------------------------------------------------------------------------- -- condition2; Update preference set TYPE_PARI= 'COUPLE' where email='KOKANP@HOTMAIL.COM'; delete FROM `conditions` WHERE EMAIL='KOKANP@HOTMAIL.COM' ; -- INSERTION DES DONNEES PROVENANT DE EXCEL INSERT INTO conditions(`email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, OBSERVATION) SELECT `email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, `OBSERVATION` FROM `condition2` ; -- condition2; -- condition3; Update preference set TYPE_PARI= 'TIERCE' where email='KOKANP@HOTMAIL.COM'; delete FROM `conditions` WHERE EMAIL='KOKANP@HOTMAIL.COM' ; -- INSERTION DES DONNEES PROVENANT DE EXCEL INSERT INTO conditions(`email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, OBSERVATION) SELECT `email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, `OBSERVATION` FROM `condition3` ; -- condition3; ------------------------------------------------------5------------------------------------------------------------------------------------- -- condition4 delete FROM `conditions` WHERE EMAIL='KOKANP@HOTMAIL.COM' ; Update preference set TYPE_PARI= 'QUARTE' where email='KOKANP@HOTMAIL.COM'; -- INSERTION DES DONNEES PROVENANT DE EXCEL INSERT INTO conditions(`email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, OBSERVATION) SELECT `email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, `OBSERVATION` FROM `condition4` ; -- condition4 -------------------------------------------------------------6------------------------------------------------------------------------------ -- condition5 delete FROM `conditions` WHERE EMAIL='KOKANP@HOTMAIL.COM' ; -- INSERTION DES DONNEES PROVENANT DE EXCEL Update preference set TYPE_PARI= 'QUINTE' where email='KOKANP@HOTMAIL.COM'; INSERT INTO conditions(`email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, OBSERVATION) SELECT `email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, `OBSERVATION` FROM `condition5` ; -- condition5 ------------------------------------------------------------7------------------------------------------------------------------------------- -- condition5 -- INSERTION DES DONNEES PROVENANT DE EXCEL insert into conditions (EMAIL, C1, C2, C3, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.premier, arrivee.deuxieme, arrivee.troisieme, "Au plus" AS SENS, 3 AS VALEUR, "_120 QUINTE PASSE" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 120 > (select id from arrivee where date_course = ( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) )) AND date_course <( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ); insert into conditions (EMAIL, C1, C2, C3, C4, C5, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.premier, arrivee.deuxieme, arrivee.troisieme, arrivee.quatrieme, arrivee.cinquieme, "Au plus" AS SENS, 3 AS VALEUR, "meme date et mois" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and day(date_course)=( SELECT DAY(date_course) FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) AND MONTH(date_course)= (SELECT MONTH(date_course) FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) AND date_course <( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) ; -- condition3; Update preference set TYPE_PARI= 'TIERCE' where email='KOKANP@HOTMAIL.COM'; delete FROM `conditions` WHERE EMAIL='KOKANP@HOTMAIL.COM' ; -- INSERTION DES DONNEES PROVENANT DE EXCEL INSERT INTO conditions SELECT * FROM `condition3`; insert into conditions (EMAIL, C1, C2,C3, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.premier, arrivee.deuxieme, arrivee.troisieme, "Au plus" AS SENS, 1 AS VALEUR, "7 tierce passe " AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 8 > ( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) AND date_course <( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ); ========================================================================================================================== ===========================================DEBUT MON JEU TRIO ================================================================= -- condition3; Update preference set TYPE_PARI= 'TIERCE' where email='KOKANP@HOTMAIL.COM'; delete FROM `conditions` WHERE EMAIL='KOKANP@HOTMAIL.COM' ; -- INSERTION DES DONNEES PROVENANT DE EXCEL INSERT INTO conditions(`email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, OBSERVATION) SELECT `email`, `C1`, `C2`, `C3`, `C4`, `C5`, `C6`, `C7`, `C8`, `C9`, `C10`, `C11`, `C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `SENS`, `VALEUR`, `OBSERVATION` FROM `condition3` ; -- TIERCE PASSE insert into conditions (EMAIL, C1, C2, C3, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.premier, arrivee.deuxieme, arrivee.troisieme, "Au plus" AS SENS, 2 AS VALEUR, "_30 TIECE PASSE 123" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 30 > (select id from arrivee where date_course = ( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) )) AND date_course <( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ); -- TIERCE PASSE insert into conditions (EMAIL, C1, C2, C3, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.deuxieme, arrivee.troisieme,arrivee.quatrieme, "Au plus" AS SENS, 2 AS VALEUR, "_30 TIECE PASSE 234" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 30 > (select id from arrivee where date_course = ( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) )) AND date_course <( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ); -- TIERCE PASSE insert into conditions (EMAIL, C1, C2, C3, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.troisieme,arrivee.quatrieme, arrivee.cinquieme, "Au plus" AS SENS, 2 AS VALEUR, "_30 TIECE PASSE 345" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 30 > (select id from arrivee where date_course = ( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) )) AND date_course <( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ); -- QUINTE PASSE insert into conditions (EMAIL, C1, C2, C3, C4, C5, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.premier, arrivee.deuxieme, arrivee.troisieme, arrivee.quatrieme, arrivee.cinquieme, "Au plus" AS SENS, 3 AS VALEUR, "_30 QUINTE PASSE" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 30 > (select id from arrivee where date_course = ( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ) )) AND date_course <( SELECT date_course FROM `preference` WHERE email='KOKANP@HOTMAIL.COM' ); ------------------------------------------------------------------------------------------------------------------ SELECT demande.numero , `CODE_REGION`, `matricule`, `prenom`, `nom`, `fonction`, `lieu_affectation`, `service`, `telephonemoov`, `telephoneorange`, `objet_demande`, `piece_fournie`, `date_demande`, `observation`,vague.numero , vague.date_traitement FROM `demande` , vague where vague.numero = ( SELECT numero from vague where numero = (select max(numero) from vague ) )