Requêtes SQL

-- suppression table inutilisée
DROP TABLE `modele`;    

-- creation table user
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`id_uti` INT(11) NOT NULL,
`username` VARCHAR(100) NOT NULL,
`password` VARCHAR(255) NULL DEFAULT NULL,
`first_name` VARCHAR(30) NULL DEFAULT NULL,
`last_name` VARCHAR(30) NULL DEFAULT NULL,
`display_name` VARCHAR(61) NULL DEFAULT NULL,
`mail` VARCHAR(255) NULL DEFAULT NULL,
`enabled` BIT(1) NOT NULL,
`last_login` DATETIME(6) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `UK_sb8bbouer5wak8vyiiy4pf2bx` (`username`) USING BTREE,
INDEX `id_uti` (`id_uti`) USING BTREE
);  
-- reset mot de passe
UPDATE utilisateur u SET u.PWD = NULL;
-- augmente taille mot de passe
ALTER TABLE utilisateur CHANGE COLUMN PWD PWD VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER TEM_SERVICE;

-- création élément user
   -- possible duplicata de login case sensitive (ex: GERARD & gerard, Pouard & pouard) => correction à la main
INSERT INTO user (id_uti, username, first_name, last_name, display_name, mail, enabled)
SELECT ut.ID_UTI, ut.LOGIN, ut.NOM, ut.PRENOM, CONCAT(UPPER(ut.NOM), ' ', ut.PRENOM), ut.MAIL, 1
FROM utilisateur ut;

-- MAJ type date entree enseignement sup & entree etablissement
  -- fix potentielle erreur
UPDATE recherche r SET r.DAA_ENS_SUP = NULL WHERE r.DAA_ENS_SUP NOT REGEXP '^-?[0-9]+$';
UPDATE recherche r SET r.DAA_ETB = NULL WHERE r.DAA_ETB NOT REGEXP '^-?[0-9]+$';
  -- passage de VARCHAR a INT
ALTER TABLE `recherche`
    CHANGE COLUMN `DAA_ETB` `DAA_ETB` INT(4) NULL DEFAULT NULL COMMENT 'Date entrée établissement' COLLATE 'latin1_swedish_ci' AFTER `ID_CVE`,
    CHANGE COLUMN `DAA_ENS_SUP` `DAA_ENS_SUP` INT(4) NULL DEFAULT NULL COMMENT 'Date entrée enseignement supérieur' COLLATE 'latin1_swedish_ci' AFTER `DAA_ETB`;

-- MAJ certificat antérieur
UPDATE certificat c SET c.CORPS = CONCAT('<div>', REPLACE(c.CORPS, '\n', '</div>\n<div>'), '</div>') WHERE c.CORPS NOT LIKE '<div>%';

-- augmentation taille commentaires modele ordo
ALTER TABLE `modeleordonnance`
    CHANGE COLUMN `LIB_COM` `LIB_COM` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Commentaire du modèle d\'ordonnance / imprimé une seule fois sur l\'ordonnance vers l\'entête' COLLATE 'latin1_swedish_ci' AFTER `LIB_MOD`;

-- ajout specialite
ALTER TABLE `utilisateur` ADD `TITRE_PRO` VARCHAR(50) NULL AFTER `TITRE`;

-- MAJ profession conjoint
UPDATE recherche r LEFT JOIN itemnomenclature pc ON pc.ID_IN = r.PRO_CONJ SET r.PRO_CONJ = NULL WHERE pc.ID_IN IS NULL AND r.PRO_CONJ IS NOT NULL;
-- MAJ profession parent 1
UPDATE recherche r LEFT JOIN itemnomenclature pp ON pp.ID_IN = r.PRO_PAR1 SET r.PRO_PAR1 = NULL WHERE pp.ID_IN IS NULL AND r.PRO_PAR1 IS NOT NULL;
-- MAJ profession parent 2
UPDATE recherche r LEFT JOIN itemnomenclature pp ON pp.ID_IN = r.PRO_PAR2 SET r.PRO_PAR2 = NULL WHERE pp.ID_IN IS NULL AND r.PRO_PAR2 IS NOT NULL;
-- MAJ situation famille
UPDATE recherche r LEFT JOIN itemnomenclature sf ON sf.ID_IN = r.SITUATION_FAMILLE SET r.SITUATION_FAMILLE = NULL WHERE sf.ID_IN IS NULL AND r.SITUATION_FAMILLE IS NOT NULL;
-- MAJ situation parent
UPDATE recherche r LEFT JOIN itemnomenclature sp ON sp.ID_IN = r.SITUATION_PARENTS SET r.SITUATION_PARENTS = NULL WHERE sp.ID_IN IS NULL AND r.SITUATION_PARENTS IS NOT NULL;

-- MAJ INFO SCOL
UPDATE scolarite s SET s.LIB_SECTION = "" WHERE s.LIB_SECTION IS NULL;
UPDATE scolarite s SET s.LIB_ETAB_2 = "" WHERE s.LIB_ETAB_2 IS NULL;
UPDATE scolarite s SET s.SPECIALITE = "" WHERE s.SPECIALITE IS NULL;
UPDATE scolarite s SET s.CYCLE = "" WHERE s.CYCLE IS NULL;
UPDATE scolarite s SET s.ANNEE = "" WHERE s.ANNEE IS NULL;

-- MAJ Fratrie (VARCHAR -> INT)
UPDATE recherche r SET r.FRATRIE = '0' WHERE r.FRATRIE NOT REGEXP '^[0-9]+$' OR r.FRATRIE IS NULL;
ALTER TABLE `recherche`    CHANGE COLUMN `FRATRIE` `FRATRIE` INT(2) NULL DEFAULT NULL AFTER `ID_ADR_FAMILLE`;
-- MAJ Nb Enfants (NULL -> 0)
UPDATE recherche r SET r.NB_ENFANTS = 0 WHERE r.NB_ENFANTS IS NULL;
-- MAJ ItemNomenclature inexistante lié à Recherche, Scolarite ou Adresse (infos etudiantes)
UPDATE recherche r LEFT JOIN itemnomenclature n ON n.ID_IN = r.NATIONALITE SET r.NATIONALITE = NULL WHERE n.ID_IN IS NULL;
UPDATE recherche r LEFT JOIN itemnomenclature s ON s.ID_IN = r.SITUATION_FAMILLE SET r.SITUATION_FAMILLE = NULL WHERE s.ID_IN IS NULL;
UPDATE recherche r LEFT JOIN itemnomenclature p ON p.ID_IN = r.PRO_CONJ SET r.PRO_CONJ = NULL WHERE p.ID_IN IS NULL;
UPDATE recherche r LEFT JOIN itemnomenclature b ON b.ID_IN = r.BAC_COD SET r.BAC_COD = NULL WHERE b.ID_IN IS NULL;
UPDATE recherche r LEFT JOIN itemnomenclature p ON p.ID_IN = r.PRO_PAR1 SET r.PRO_PAR1 = NULL WHERE p.ID_IN IS NULL;
UPDATE recherche r LEFT JOIN itemnomenclature p ON p.ID_IN = r.PRO_PAR2 SET r.PRO_PAR2 = NULL WHERE p.ID_IN IS NULL;
UPDATE recherche r LEFT JOIN itemnomenclature s ON s.ID_IN = r.SITUATION_PARENTS SET r.SITUATION_PARENTS = NULL WHERE s.ID_IN IS NULL;
UPDATE scolarite s LEFT JOIN itemnomenclature e ON e.ID_IN = s.COD_ETB SET s.COD_ETB = NULL WHERE e.ID_IN IS NULL;
UPDATE scolarite s LEFT JOIN itemnomenclature c ON c.ID_IN = s.COD_COM SET s.COD_COM = NULL WHERE c.ID_IN IS NULL;
UPDATE adresse a LEFT JOIN itemnomenclature p ON p.ID_IN = a.PAYS SET a.PAYS = NULL WHERE p.ID_IN IS NULL;

-- MAJ Id fournisseur vaccination
UPDATE vaccination v SET v.ID_FOU = NULL WHERE v.ID_FOU = 0;
-- MAJ temoin service Vaccin
UPDATE vaccin SET TEM_SERVICE = '0' WHERE TEM_SERVICE IS NULL;
ALTER TABLE `vaccin` CHANGE COLUMN `TEM_SERVICE` `TEM_SERVICE` TINYINT(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci' AFTER `UTI_CRE`;

-- MAJ libellé
UPDATE elementformulaire SET LIB_EF_LONG = 'Poids' WHERE COD_EF = 'POIDS';
UPDATE elementformulaire SET LIB_EF_LONG = 'Taille' WHERE COD_EF = 'TAILLE';

-- MAJ Sexe Identité
ALTER TABLE `identite`
    CHANGE COLUMN `SEXE` `SEXE` VARCHAR(10) NULL DEFAULT NULL COMMENT 'sexe (M ou F)' COLLATE 'latin1_swedish_ci' AFTER `COD_COM`;

-- modification pour les files d'attente

UPDATE file_attente f SET f.COD_FAT = '0';
ALTER TABLE `file_attente` CHANGE COLUMN `COD_FAT` `ORD_FAT` INT(11) NULL DEFAULT NULL;

-- pour permettre des identifiants plus long ( ex 64 caractères ). (testé et opérationnel Paris Saclay)

ALTER TABLE `tracevisite` CHANGE `COMMENTAIRES` `COMMENTAIRES` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ;
ALTER TABLE `utilisateur` CHANGE `LOGIN` `LOGIN` VARCHAR( 75 ) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '';

-- modification de droits

UPDATE `droit` SET `LIB_DRO` = 'Accès aux connexions utilisateurs' WHERE `droit`.`COD_DRO` = 'DA_SES';
INSERT INTO `droit` (`ID_DRO`, `COD_DRO`, `LIB_DRO`) VALUES (NULL, 'DA_SES_LOG', 'Accès aux logs applicatifs');
INSERT INTO `droit` (`ID_DRO`, `COD_DRO`, `LIB_DRO`) VALUES (NULL, 'DE_ACT', 'Option APICRYPT');

-- ajout element bac nomenclature

INSERT INTO `itemnomenclature` (`ID_IN`, `ID_NMC`, `COD_IN`, `LIB_IN`, `LIB_C_IN`, `ORD_IN`, `CPL_IN`, `TEM_SERVICE`, `TEM_IMPORT`, `PARENT_ID`, `DAT_CRE`, `UTI_CRE`) VALUES (NULL, (SELECT ID_NMC from nomenclature where COD_NMC='BAC'), '0038', '0038-brevet des métiers d''art', NULL, '100', '', 1, NULL, NULL, NULL, NULL);
INSERT INTO `itemnomenclature` (`ID_IN`, `ID_NMC`, `COD_IN`, `LIB_IN`, `LIB_C_IN`, `ORD_IN`, `CPL_IN`, `TEM_SERVICE`, `TEM_IMPORT`, `PARENT_ID`, `DAT_CRE`, `UTI_CRE`) VALUES (NULL, (SELECT ID_NMC from nomenclature where COD_NMC='BAC'), '2TMD', 'ST théatre musique et danse', NULL, '110', '', 1, NULL, NULL, NULL, NULL);
INSERT INTO `itemnomenclature` (`ID_IN`, `ID_NMC`, `COD_IN`, `LIB_IN`, `LIB_C_IN`, `ORD_IN`, `CPL_IN`, `TEM_SERVICE`, `TEM_IMPORT`, `PARENT_ID`, `DAT_CRE`, `UTI_CRE`) VALUES (NULL, (SELECT ID_NMC from nomenclature where COD_NMC='BAC'), 'NBGE', 'Série générale à partir bac 2021', NULL, '120', '', 1, NULL, NULL, NULL, NULL);
INSERT INTO `itemnomenclature` (`ID_IN`, `ID_NMC`, `COD_IN`, `LIB_IN`, `LIB_C_IN`, `ORD_IN`, `CPL_IN`, `TEM_SERVICE`, `TEM_IMPORT`, `PARENT_ID`, `DAT_CRE`, `UTI_CRE`) VALUES (NULL, (SELECT ID_NMC from nomenclature where COD_NMC='BAC'), 'STHR', 'ST de l''Hôtellerie et restauration', NULL, '130', '', 1, NULL, NULL, NULL, NULL);

-- modification des noms de couleurs pour planning

UPDATE groupecreneau g SET g.COLOR = '#FF0000' WHERE g.COLOR = 'RED';
UPDATE motifcreneau m SET m.COLOR = '#FF0000' WHERE m.COLOR = 'RED';
UPDATE groupecreneau g SET g.COLOR = '#FFC800' WHERE g.COLOR = 'ORANGE';
UPDATE motifcreneau m SET m.COLOR = '#FFC800' WHERE m.COLOR = 'ORANGE';
UPDATE groupecreneau g SET g.COLOR = '#FFFF00' WHERE g.COLOR = 'YELLOW';
UPDATE motifcreneau m SET m.COLOR = '#FFFF00' WHERE m.COLOR = 'YELLOW';
UPDATE groupecreneau g SET g.COLOR = '#00FF00' WHERE g.COLOR = 'GREEN';
UPDATE motifcreneau m SET m.COLOR = '#00FF00' WHERE m.COLOR = 'GREEN';
UPDATE groupecreneau g SET g.COLOR = '#00FFFF' WHERE g.COLOR = 'CYAN';
UPDATE motifcreneau m SET m.COLOR = '#00FFFF' WHERE m.COLOR = 'CYAN';
UPDATE groupecreneau g SET g.COLOR = '#0000FF' WHERE g.COLOR = 'BLUE';
UPDATE motifcreneau m SET m.COLOR = '#0000FF' WHERE m.COLOR = 'BLUE';
UPDATE groupecreneau g SET g.COLOR = '#FF00FF' WHERE g.COLOR = 'MAGENTA';
UPDATE motifcreneau m SET m.COLOR = '#FF00FF' WHERE m.COLOR = 'MAGENTA';
UPDATE groupecreneau g SET g.COLOR = '#FFAFAF' WHERE g.COLOR = 'PINK';
UPDATE motifcreneau m SET m.COLOR = '#FFAFAF' WHERE m.COLOR = 'PINK';

UPDATE groupecreneau g SET g.COLOR = '#FFFFFF' WHERE g.COLOR = 'WHITE';
UPDATE motifcreneau m SET m.COLOR = '#FFFFFF' WHERE m.COLOR = 'WHITE';
UPDATE groupecreneau g SET g.COLOR = '#C0C0C0' WHERE g.COLOR = 'LIGHT_GRAY';
UPDATE motifcreneau m SET m.COLOR = '#C0C0C0' WHERE m.COLOR = 'LIGHT_GRAY';
UPDATE groupecreneau g SET g.COLOR = '#808080' WHERE g.COLOR = 'GRAY';
UPDATE motifcreneau m SET m.COLOR = '#808080' WHERE m.COLOR = 'GRAY';
UPDATE groupecreneau g SET g.COLOR = '#000000' WHERE g.COLOR = 'BLACK';
UPDATE motifcreneau m SET m.COLOR = '#000000' WHERE m.COLOR = 'BLACK';

-- modification des récurrences : ajout du/des jours concernés par la récurrence
ALTER TABLE groupecreneau ADD COLUMN `JOUR_RECURRENCE` VARCHAR(10) NULL AFTER `TYPE_RECURRENCE`;

UPDATE groupecreneau g SET g.JOUR_RECURRENCE = '1-2-3-4-5' WHERE g.TYPE_RECURRENCE = 'EVERYDAY';

UPDATE groupecreneau g SET g.JOUR_RECURRENCE =
(SELECT GROUP_CONCAT(distinct c.NUM_JOUR SEPARATOR "-" )
FROM groupecreneau gc
LEFT JOIN creneau c ON c.ID_GRP_CRENEAU = gc.ID_GRP_CRENEAU
WHERE gc.TYPE_RECURRENCE = 'EVERY_WEEK'
AND gc.ID_GRP_CRENEAU = g.ID_GRP_CRENEAU
ORDER BY c.NUM_JOUR)
WHERE g.TYPE_RECURRENCE = 'EVERY_WEEK';

UPDATE groupecreneau g SET g.JOUR_RECURRENCE =
(SELECT GROUP_CONCAT(distinct c.NUM_JOUR SEPARATOR "-" )
FROM groupecreneau gc
LEFT JOIN creneau c ON c.ID_GRP_CRENEAU = gc.ID_GRP_CRENEAU
WHERE gc.TYPE_RECURRENCE = 'EVERY_2_WEEK'
AND gc.ID_GRP_CRENEAU = g.ID_GRP_CRENEAU
ORDER BY c.NUM_JOUR)
WHERE g.TYPE_RECURRENCE = 'EVERY_2_WEEK';

UPDATE groupecreneau g SET g.JOUR_RECURRENCE =
(SELECT GROUP_CONCAT(distinct c.NUM_JOUR SEPARATOR "-" )
FROM groupecreneau gc
LEFT JOIN creneau c ON c.ID_GRP_CRENEAU = gc.ID_GRP_CRENEAU
WHERE gc.TYPE_RECURRENCE = 'EVERY_MONTH'
AND gc.ID_GRP_CRENEAU = g.ID_GRP_CRENEAU
ORDER BY c.NUM_JOUR)
WHERE g.TYPE_RECURRENCE = 'EVERY_MONTH';

-- ajout heure de fin pour RDV (à la volée)
ALTER TABLE `rdv` ADD COLUMN `H_FIN` TIME NULL AFTER `H_PREVUE`;
-- ajout témoin rdv "à bloquer"
ALTER TABLE `rdv` ADD COLUMN `TO_BLOCK` TINYINT NOT NULL DEFAULT '0' AFTER `UTI_MOD`;

-- requetes pour performance affichage planning
ALTER TABLE `creneau` ADD COLUMN `DATE` DATE NOT NULL AFTER `ID_UTI`;
UPDATE creneau c SET c.DATE = STR_TO_DATE(CONCAT(c.NUM_ANNEE, ' ', c.NUM_SEMAINE, ' ', c.NUM_JOUR), '%x %v %w');
ALTER TABLE `presence`
ADD INDEX `IDX_CRENEAU` (`ID_CRENEAU`) USING BTREE,
ADD INDEX `IDX_USER` (`ID_USER`) USING BTREE;

-- modification Complementaire Sante Solidaire
ALTER TABLE `securitesociale` ADD `CSS` TINYINT(1) NULL AFTER `INSCRIPTION_MUT`;

-- partie module APICRYPT

-- ajout colonne utilisateur
ALTER TABLE `utilisateur` ADD `API_MAIL` VARCHAR(100) NOT NULL AFTER `MAIL`;

-- Structure de la table `parametre_apicrypt`

CREATE TABLE `parametre_apicrypt` (
 `ID_PAPI` int(11) NOT NULL,
 `ID_UTI` int(11) NOT NULL,
 `API_MAIL` varchar(250) NOT NULL,
 `API_PASS` varchar(250) NOT NULL,
 `API_ID` varchar(250) NOT NULL,
 `API_DIR` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `parametre_apicrypt`
 ADD PRIMARY KEY (`ID_PAPI`);

ALTER TABLE `parametre_apicrypt`
 MODIFY `ID_PAPI` int(11) NOT NULL AUTO_INCREMENT;