-- fix vaccination
UPDATE vaccination v SET v.ETAT = '' WHERE v.ETAT IS NULL;
-- Parametre lié aux vaccins
CREATE TABLE parametre_vaccin (
ID_PARAM INT(11) NOT NULL AUTO_INCREMENT,
ID_CTR INT(11) NOT NULL,
ORDRE_ASC TINYINT(4) NOT NULL DEFAULT '0',
SHOW_CTRL_END TINYINT(4) NOT NULL DEFAULT '0',
PRIMARY KEY (ID_PARAM) USING BTREE,
UNIQUE INDEX ID_CTR (ID_CTR) USING BTREE
)
COMMENT='Paramétrages lié au vaccin, vaccinations, par centre';
-- fix valeur NULL scolarite
ALTER TABLE scolarite
CHANGE COLUMN LIB_SECTION LIB_SECTION VARCHAR(45) NULL DEFAULT '' COLLATE 'latin1_swedish_ci' AFTER COD_ANU,
CHANGE COLUMN LIB_ETAB_2 LIB_ETAB_2 VARCHAR(50) NULL DEFAULT '' COLLATE 'latin1_swedish_ci' AFTER LIB_SECTION,
CHANGE COLUMN SPECIALITE SPECIALITE VARCHAR(45) NULL DEFAULT '' COLLATE 'latin1_swedish_ci' AFTER LIB_ETAB_2;
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;
-- multi choix questionnaire
UPDATE elementformulaire ef INNER JOIN formulaire f ON f.ID_QUE = ef.ID_QUE
SET `TYPE` = "MUL", VALUE_INI = NULL
WHERE ef.TYPE = "SEL"
AND (f.LIB_QUE = "Alimentation"
OR (f.LIB_QUE = "Trajets" AND ef.LIB_EF_COURT = "Moyen")
OR f.LIB_QUE = "Exercice physique"
OR f.LIB_QUE = "Logement"
OR f.LIB_QUE = "Etudes"
OR f.LIB_QUE = "Loisirs");
-- Droit reinitilialisation des handicap, via listing
INSERT INTO droit (COD_DRO, LIB_DRO) VALUES ('DS_HDP', 'Droit de réinitialisation des handicap');
-- Ajout bloc suivi pour fiche Handicap
INSERT INTO formulaire VALUES (NULL, "SHP", "Suivi", (SELECT fi.ID_FIC FROM fiche fi WHERE fi.COD_FIC = "HCL"), NULL, 1, NULL, NOW());
-- STATISTIQUE MINISTERE
ALTER TABLE `itemnomenclature`CHANGE COLUMN `LIB_C_IN` `LIB_C_IN` VARCHAR(250) NULL DEFAULT NULL AFTER `LIB_IN`;
INSERT INTO droit (`COD_DRO`, `LIB_DRO`) VALUES ('DA_STA_TDB', 'Gestion de l\'envoi de stat au tableau de bord');
-- nouvelle nomenclature
INSERT INTO nomenclature (`COD_NMC`, `LIB_NMC`, `TEM_SERVICE`, `DAT_NMC`, `TYPE`, `DESCRIPTION`, `NIVEAU`) VALUES ('MESRI', 'Nomenclature interne issus du Ministère', '1', '2021-02-17', 'I', 'Motifs de consultation du Ministère pour l\'envoi automatique de statistiques', '1');
-- nouveaux items
-- Urgence
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'URG', 'Urgence', '', 0, 1, CURDATE());
-- Médecine
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'MED', 'Médecine', 'Consultation medecine générale; Gynecologie; Psychiatrie; Addictologie', 1, 1, CURDATE());
-- Cslt medecine générale
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_MED_GEN', 'Consultation médecine générale', '', 0, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "MED" AND n.COD_NMC = "MESRI"), CURDATE());
-- Gynecologie
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_GYN', 'Gynécologie', '', 1, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "MED" AND n.COD_NMC = "MESRI"), CURDATE());
-- Psychiatrie
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_PSY', 'Psychiatrie', '', 2, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "MED" AND n.COD_NMC = "MESRI"), CURDATE());
-- Addictologie
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_ADD', 'Addictologie', '', 3, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "MED" AND n.COD_NMC = "MESRI"), CURDATE());
-- Orientation Chèque Psychologue
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_ORI_PSY', 'Orientation Chèque Psychologue', '', 4, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "MED" AND n.COD_NMC = "MESRI"), CURDATE());
-- Psychologie
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'PSY', 'Psychologie', '', 2, 1, CURDATE());
-- Vaccination
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'VACC', 'Vaccination', 'Contrôle de carnet; Vaccination', 3, 1, CURDATE());
-- Contrôle de carnet
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_CTRL', 'Contrôle de carnet', '', 0, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "VACC" AND n.COD_NMC = "MESRI"), CURDATE());
-- Vaccination
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_VACC', 'Vaccination', '', 1, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "VACC" AND n.COD_NMC = "MESRI"), CURDATE());
-- Paramédical
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'PARA', 'Paramédical', 'RdV Infirmier; Diététique', 4, 1, CURDATE());
-- Rdv Infirmier
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_RDV_INF', 'Rdv Infirmier', '', 0, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "PARA" AND n.COD_NMC = "MESRI"), CURDATE());
-- Diététique
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_DIET', 'Diététique', '', 1, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "PARA" AND n.COD_NMC = "MESRI"), CURDATE());
-- Prévention
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'PREV', 'Prévention', 'Examen de santé; Suivi Sanitaire préventif des étudiants internationaux; Dépistage tuberculose; Bien-être et de gestion du stress (relaxation, sophrologie, méditation...)', 5, 1, CURDATE());
-- Examen de santé
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_EXAM', 'Examen de santé', '', 0, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "PREV" AND n.COD_NMC = "MESRI"), CURDATE());
-- Suivi sanitaire préventif des étudiants internationaux
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_SUIVI_PREV', 'Suivi sanitaire préventif des étudiants internationaux', '', 1, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "PREV" AND n.COD_NMC = "MESRI"), CURDATE());
-- Dépistage tuberculose
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_DEP_TUB', 'Dépistage tuberculose', '', 2, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "PREV" AND n.COD_NMC = "MESRI"), CURDATE());
-- Bien-être et de gestion du stress
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_BIEN_ETRE', 'Bien-être et de gestion du stress', '', 3, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "PREV" AND n.COD_NMC = "MESRI"), CURDATE());
-- Handicap
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'HAND', 'Handicap', '', 6, 1, CURDATE());
-- Accompagnement Social
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'ACCSOC', 'Accompagnement Social', 'Entretien service social; Assistance sociale', 7, 1, CURDATE());
-- Entretien service social
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_ENTR_SOC', 'Entretien service social', '', 0, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "ACCSOC" AND n.COD_NMC = "MESRI"), CURDATE());
-- Assistance sociale
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, PARENT_ID, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'CSL_ASS_SOC', 'Assistance sociale', '', 1, 1, (SELECT i.ID_IN FROM itemnomenclature i LEFT JOIN nomenclature n ON n.ID_NMC = i.ID_NMC WHERE i.COD_IN = "ACCSOC" AND n.COD_NMC = "MESRI"), CURDATE());
-- Divers
INSERT INTO itemnomenclature (ID_NMC, COD_IN, LIB_IN, LIB_C_IN, ORD_IN, TEM_SERVICE, DAT_CRE) VALUES ((SELECT n.ID_NMC FROM nomenclature n WHERE n.COD_NMC = 'MESRI'), 'DIV', 'Divers', '', 7, 1, CURDATE());
INSERT INTO batch (`cod_batch`, `lib_batch`, `tes_batch`, `tem_is_launch_imedia_batch`, `tem_lundi_batch`, `tem_mardi_batch`, `tem_mercr_batch`, `tem_jeudi_batch`, `tem_vendredi_batch`, `tem_samedi_batch`, `tem_diman_batch`, `fixe_hour_batch`) VALUES ('STAT_DASHBOARD', 'Envoi des statistiques vers calcium-dashboard', '1', '0', b'0', b'0', b'0', b'0', b'0', b'1', b'0', '06:30:00');
CREATE TABLE `item_pivot` (
`ID_ITEM_MESRI` INT(11) NOT NULL,
`ID_ITEM` INT(11) NOT NULL,
PRIMARY KEY (`ID_ITEM_MESRI`, `ID_ITEM`),
INDEX `Index item mesri` (`ID_ITEM_MESRI`),
INDEX `Index item` (`ID_ITEM`)
)
COMMENT='Items lié à d\'autre item (pivot)'
COLLATE='latin1_swedish_ci';
CREATE TABLE `parametre_remontee_stat` (
`ID_PARAM` INT(11) NOT NULL AUTO_INCREMENT,
`TEM_ARRET` TINYINT(1) NOT NULL DEFAULT 1,
`DATE_DEB` DATE NOT NULL DEFAULT '2019-09-01',
PRIMARY KEY (`ID_PARAM`)
)COLLATE='latin1_swedish_ci';
INSERT INTO `parametre_remontee_stat` VALUES (1, 1, '2019-09-01');
-- fix
ALTER TABLE `scolarite`CHANGE COLUMN `COD_ANU` `COD_ANU` INT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci' AFTER `COD_COM`;
ALTER TABLE scolarite CONVERT TO CHARACTER SET UTF8;
UPDATE recherche r SET r.DAA_ENS_SUP = NULL WHERE r.DAA_ENS_SUP = '';
UPDATE recherche r SET r.DAA_ETB = NULL WHERE r.DAA_ETB = '0' OR r.DAA_ETB = '';