------------------------------------------------------1------------------------------------------------------------------------------------- DROP TABLE `base`; DROP TABLE `condition0`; DROP TABLE `condition2`; DROP TABLE `condition3`; DROP TABLE `condition4`; DROP TABLE `condition5`; ------------------------------------------------------------------------------------------------------------------------------------------ Importer le ficher excel ------------------------------------------------2------------------------------------------------------------------------------------------- ---------------------------------------------------3-------------------------------------------------------------------------------------- -- 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 SELECT * FROM condition2; -------------------------------------------------------4-------------------------------------------------------------------------------------- condition 3 -- 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`, `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` ; 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, "360 tierce passe " AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 360 > (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' ) -- 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 SELECT * FROM `condition4`; 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` ; 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' ) ; insert into conditions (EMAIL, C1, C2, C3, C4, SENS, VALEUR, OBSERVATION ) SELECT 'KOKANP@HOTMAIL.COM', arrivee.premier, arrivee.deuxieme, arrivee.troisieme, arrivee.quatrieme, "Au plus" AS SENS, 3 AS VALEUR, "180 QUARTE PASSE" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 180 > (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`, `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` WHERE `SENS` ='Au moins';; -------------------------------------------------------------6------------------------------------------------------------------------------ -- condition5 delete FROM `conditions` WHERE EMAIL='KOKANP@HOTMAIL.COM' ; -- INSERTION DES DONNEES PROVENANT DE EXCEL INSERT INTO conditions SELECT * FROM `condition5`; Update preference set TYPE_PARI= 'QUINTE' 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' ) ; 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, 4 AS VALEUR, "_360 QUINTE PASSE" AS OBSERVATION FROM arrivee where premier<>0 and deuxieme <>0 and troisieme <>0 and quatrieme<>0 and cinquieme<>0 and ( id + 360 > (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`, `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` WHERE `SENS` ='Au moins';; ------------------------------------------------------------7------------------------------------------------------------------------------- SELECT * FROM `arrivee` where id+7 = ( select id from arrivee where date_course = date(sysdate()))