1.1.0_to_1.2.0.sql 28.5 KB
Newer Older
tguillon's avatar
tguillon committed
1 2
BEGIN;

3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
--
-- Création de la table articles
--
CREATE TABLE articles (
    id serial NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    createdbyorganisation_id INTEGER NOT NULL REFERENCES organisations(id) ON DELETE CASCADE ON UPDATE CASCADE,
    created timestamp without time zone NOT NULL,
    modified timestamp without time zone NOT NULL
);

--
-- Création de la table fichierarticles
--
CREATE TABLE fichierarticles (
    id SERIAL NOT NULL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    url VARCHAR(100) NOT NULL,
    article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE ON UPDATE CASCADE,
    created timestamp without time zone NOT NULL,
    modified timestamp without time zone NOT NULL
);

CREATE UNIQUE INDEX fichierarticles_nom_article_id_idx ON fichierarticles (nom, article_id);

--
-- Création de la table de jointure Articles Organisations
--
CREATE TABLE articles_organisations (
    id SERIAL PRIMARY KEY NOT NULL,
    article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE ON UPDATE CASCADE,
    organisation_id INTEGER NOT NULL REFERENCES organisations(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE UNIQUE INDEX articles_organisations_article_id_organisation_id_idx ON articles_organisations (article_id, organisation_id);

tguillon's avatar
tguillon committed
40 41 42 43
--
-- Insertion de valeur dans la table liste_droits
--
INSERT INTO liste_droits (libelle, value, created, modified) VALUES
tguillon's avatar
tguillon committed
44 45 46 47
('Créer un article dans la FAQ', 28, NOW(), NOW()),
('Modifier un article dans la FAQ', 29, NOW(), NOW()),
('Consulter la FAQ', 30, NOW(), NOW()),
('Supprimer un article dans la FAQ', 31, NOW(), NOW());
tguillon's avatar
tguillon committed
48

49 50 51 52 53 54 55
--
-- Insertion de valeur dans la table etats
--
INSERT INTO etats (libelle, value, created, modified) VALUES
('Initialisation du traitement par le DPO', 11, NOW(), NOW()),
('Rédaction du traitement initialisé', 12, NOW(), NOW());

56
ALTER TABLE organisations DROP verrouiller;
tguillon's avatar
tguillon committed
57

58 59 60
--
-- ALTER TABLE fg_formulaires TO formulaires
--
61 62
ALTER TABLE fg_formulaires RENAME TO formulaires;
ALTER SEQUENCE fg_formulaires_id_seq RENAME TO formulaires_id_seq;
63
ALTER INDEX fg_formulaires_pkey RENAME TO formulaires_pkey;
64 65 66 67 68

ALTER TABLE formulaires
    DROP CONSTRAINT fg_formulaires_organisations_id_fkey,
    ADD CONSTRAINT formulaires_organisations_id_fkey FOREIGN KEY (organisations_id) REFERENCES organisations (id) ON DELETE CASCADE ON UPDATE CASCADE;

69 70 71 72 73 74
ALTER TABLE formulaires ADD COLUMN usesousfinalite BOOLEAN DEFAULT FALSE;
ALTER TABLE formulaires ADD COLUMN usebaselegale BOOLEAN DEFAULT FALSE;
ALTER TABLE formulaires ADD COLUMN usedecisionautomatisee BOOLEAN DEFAULT FALSE;
ALTER TABLE formulaires ADD COLUMN usetransferthorsue BOOLEAN DEFAULT FALSE;
ALTER TABLE formulaires ADD COLUMN usedonneessensible BOOLEAN DEFAULT FALSE;
ALTER TABLE formulaires ADD COLUMN useallextensionfiles BOOLEAN DEFAULT FALSE;
tguillon's avatar
tguillon committed
75
ALTER TABLE formulaires ADD COLUMN usepia BOOLEAN DEFAULT FALSE;
76

77 78 79
ALTER TABLE formulaires ADD COLUMN oldformulaire BOOLEAN DEFAULT FALSE;
UPDATE formulaires SET oldformulaire = true;

80
ALTER TABLE formulaires ALTER COLUMN soustraitant DROP NOT NULL;
81

82 83
ALTER TABLE formulaires ALTER COLUMN active DROP NOT NULL;
ALTER TABLE formulaires ALTER COLUMN active SET DEFAULT FALSE;
84
UPDATE formulaires SET active = false;
85

86 87 88
--
-- ALTER TABLE fg_champs TO champs
--
89 90
ALTER TABLE fg_champs RENAME TO champs;
ALTER SEQUENCE fg_champs_id_seq RENAME TO champs_id_seq;
91
ALTER INDEX fg_champs_pkey RENAME TO champs_pkey;
92

93
ALTER TABLE champs RENAME COLUMN formulaires_id TO formulaire_id;
94

95 96
ALTER TABLE champs
    DROP CONSTRAINT fg_champs_formulaires_id_fkey,
97 98
    ADD CONSTRAINT champs_formulaire_id_fkey FOREIGN KEY (formulaire_id) REFERENCES formulaires (id) ON DELETE CASCADE ON UPDATE CASCADE;

99 100 101
ALTER TABLE champs ADD COLUMN champ_coresponsable BOOLEAN DEFAULT FALSE;
ALTER TABLE champs ADD COLUMN champ_soustraitant BOOLEAN DEFAULT FALSE;

102 103 104
--
-- ALTER TABLE fiches
--
105 106 107 108
ALTER TABLE fiches
    DROP CONSTRAINT fiches_form_id_fkey,
    ADD CONSTRAINT fiches_form_id_fkey FOREIGN KEY (form_id) REFERENCES formulaires (id) ON DELETE CASCADE ON UPDATE CASCADE;

109
ALTER TABLE fiches ADD COLUMN soustraitance BOOL NOT NULL DEFAULT FALSE;
tguillon's avatar
tguillon committed
110 111 112
ALTER TABLE fiches ADD COLUMN obligation_pia BOOL DEFAULT NULL;
ALTER TABLE fiches ADD COLUMN realisation_pia BOOL DEFAULT NULL;
ALTER TABLE fiches ADD COLUMN depot_pia BOOL DEFAULT NULL;
113

114 115 116 117 118
--
-- on passe le champ concernant la soutraitance à true en fonction si la fiche à un soustraitant
--
UPDATE fiches SET soustraitance = true WHERE soustraitantid IS NOT NULL;

119 120 121
--
-- ALTER TABLE modeles
--
122 123 124
ALTER TABLE modeles
    DROP CONSTRAINT modeles_formulaires_id_fkey,
    ADD CONSTRAINT modeles_formulaires_id_fkey FOREIGN KEY (formulaires_id) REFERENCES formulaires (id) ON DELETE CASCADE ON UPDATE CASCADE;
125

126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
DELETE FROM valeurs
    WHERE champ_name IN (
        'raisonsociale',
        'telephone',
        'fax',
        'adresse',
        'email',
        'sigle',
        'siret',
        'ape',
        'civiliteresponsable',
        'nomresponsable',
        'prenomresponsable',
        'fonctionresponsable',
        'emailresponsable',
        'telephoneresponsable',
        'dpo',
        'numerodpo',
        'fixDpo',
        'portableDpo',
        'emailDpo',
        'soustraitantid',
        'id'
    )
    AND fiche_id IN (
        SELECT fiche_id
        FROM etat_fiches
        WHERE etat_fiches.actif = true
        AND etat_fiches.etat_id NOT IN (5,7,9)
    );

tguillon's avatar
tguillon committed
157 158 159 160 161 162
-- Abroger normes RU-059
UPDATE normes SET abroger = true WHERE id = 42;

-- DROP contrainte libelle unique
DROP INDEX normes_libelle_idx;

tguillon's avatar
tguillon committed
163 164 165 166
ALTER TABLE normes
    DROP CONSTRAINT normes_norme_in_list_chk,
    ADD CONSTRAINT normes_norme_in_list_chk CHECK (cakephp_validate_in_list(norme, ARRAY['AU','DI','NS','RU', 'MR', 'RS']));

tguillon's avatar
tguillon committed
167 168 169 170 171 172 173 174
--
-- Insertion des normes
--
INSERT INTO normes (norme, numero, libelle, description, name_fichier, fichier, created, modified) VALUES
('RU', '060', 'Procès-verbaux des accidents de la circulation routière', 'Le décret n° 2017-1776 du 27 décembre 2017, qui constitue l’acte réglementaire unique RU-060, autorise l’observatoire national interministériel de la sécurité routière ainsi que les services chargés des missions d’observatoire régional ou départemental de la sécurité routière à mettre en œuvre des traitements de données à caractère personnel dénommés « enregistrement des procès-verbaux des accidents de la circulation routière » afin de permettre l’enregistrement, la conservation et la mise à disposition pour consultation des pièces de procédure mentionnées à l’article 11-1 du code de procédure pénale relatives aux accidents de la circulation routière.', 'RU-060.pdf', '1584721779.pdf', NOW(), NOW()),
('RU', '063', 'Gestion de l’Allocation personnalisée d’autonomie (APA) et de l’Aide sociale à l’hébergement (ASH)', 'L''acte réglementaire unique RU-063 autorise les conseils départementaux à mettre en œuvre des traitements de données à caractère personnel pour la gestion de l’allocation personnalisée d’autonomie (APA) et de l’aide sociale à l’hébergement (ASH).', 'RU-063.pdf', '1584721805.pdf', NOW(), NOW()),
('RU', '065', 'Caméras mobiles des agents de police municipale', 'Le RU-065 porte sur l’utilisation, par les agents de police municipale, de caméras mobiles pour procéder à l’enregistrement de leurs interventions, lorsque se produit ou est susceptible de se produire un incident. Il prévoit les conditions de mise en œuvre des traitements de données à caractère personnel provenant de ces caméras.', 'RU-065.pdf', '1584721836.pdf', NOW(), NOW()),
('RU', '066', 'Caméras mobiles des sapeurs-pompiers', 'Le RU-066 porte sur l’utilisation, par les sapeurs-pompiers, de caméras mobiles pour procéder à l’enregistrement de leurs interventions, lorsque se produit ou est susceptible de se produire un incident de nature à mettre en péril leur intégrité physique. Il prévoit les conditions de mise en œuvre des traitements de données à caractère personnel provenant de ces caméras.', 'RU-066.pdf', '1584721860.pdf', NOW(), NOW()),
tguillon's avatar
tguillon committed
175 176 177 178 179 180 181 182
('NS', '060', 'Administrateurs et mandataires judiciaires', 'La norme simplifiée NS-060 concerne les traitements mis en œuvre par les administrateurs judiciaires et mandataires judiciaires (AJMJ) dans le cadre de l’exécution de leurs missions commerciales et civiles, de représentation et d’administration des personnes. Ces missions leurs sont confiées par le juge judiciaire. Les traitements mis en œuvre dans le cadre de cette norme permettent à ces professionnels de gérer les entreprises en difficulté ou les entreprises in bonis lorsque le fonctionnement des organes sociaux n’est plus correctement assuré. Ils permettent également d’administrer des structures ou situations en état de crise (société, association, indivision, succession). A cet effet des données relatives aux infractions, condamnations et mesures de sûreté ainsi que le NIR peuvent être collectés. La NS-060 ne couvre pas les traitements dont les personnes physiques ou morales représentées ou administrées sont responsables et que les administrateurs et mandataires judiciaires continuent de mettre en œuvre pour la bonne exécution de leurs missions. Seuls les traitements directement mis en œuvre par les professionnels, pour leurs besoins propres, au sein de leurs études sont concernées par cette norme.', 'NS-060.pdf', '1584721877.pdf', NOW(), NOW()),
('MR', '001', 'Recherches dans le domaine de la santé avec recueil du consentement', 'La méthodologie de référence MR-001 encadre les traitements comprenant des données de santé et présentant un caractère d’intérêt public, réalisés dans le cadre de recherches nécessitant le recueil du consentement de la personne concernée ou celui de ses représentants légaux. Il est précisé que le consentement évoqué dans la méthodologie de référence est relatif à la participation à la recherche des patients et/ou à l’accord pour la réalisation d’un examen des caractéristiques génétiques, non à la base légale du traitement au sens du RGPD. Il s’agit plus précisément des recherches interventionnelles, y compris les recherches à risques et contraintes minimes, des essais cliniques de médicaments et des recherches nécessitant la réalisation d’un examen des caractéristiques génétiques. L’information individuelle des patients est obligatoire. Le responsable de traitement s’engage à ne collecter que les données strictement nécessaires et pertinentes au regard des objectifs de la recherche.', 'MR-001.pdf', '1584952158.pdf', NOW(), NOW()),
('MR', '002', 'Études non interventionnelles de performances concernant les dispositifs médicaux de diagnostic in vitro', 'La méthodologie de référence MR-002 concerne les études non interventionnelles de performances menées sur les dispositifs médicaux in vitro (DM DIV) en vue de leur mise sur le marché.<br /><br />Sont exclues du champ de cette méthodologie de référence les recherches biomédicales, les études faisant apparaître l’identité complète des personnes participant à l’étude, les études épidémiologiques, les études en génétique ayant pour objet d’identifier les personnes par leurs caractéristiques génétiques.', 'MR-002.pdf', '1584952209.pdf', NOW(), NOW()),
('MR', '003', 'Recherches dans le domaine de la santé sans recueil du consentement', 'La méthodologie de référence MR-003 encadre les traitements comprenant des données de santé et présentant un caractère d’intérêt public, réalisés dans le cadre de recherches impliquant la personne humaine pour lesquelles la personne concernée ne s’oppose pas à participer après avoir été informée. Il s’agit plus précisément des recherches non interventionnelles et des essais cliniques de médicaments par grappe. L’information individuelle des patients est obligatoire. Le responsable de traitement s’engage à ne collecter que les données strictement nécessaires et pertinentes au regard des objectifs de la recherche.', 'MR-003.pdf', '1584952226.pdf', NOW(), NOW()),
('MR', '004', 'Recherches n’impliquant pas la personne humaine, études et évaluations dans le domaine de la santé', 'La méthodologie de référence MR-004 encadre les traitements de données à caractère personnel à des fins d’étude, évaluation ou recherche n’impliquant pas la personne humaine. Il s’agit plus précisément des études ne répondant pas à la définition d’une recherche impliquant la personne humaine, en particulier les études portant sur la réutilisation de données. La recherche doit présenter un caractère d’intérêt public. Le responsable de traitement s’engage à ne collecter que les données strictement nécessaires et pertinentes au regard des objectifs de la recherche.', 'MR-004.pdf', '1584952244.pdf', NOW(), NOW()),
('MR', '005', 'Études nécessitant l’accès aux données du PMSI et/ou des RPU par les établissements de santé et les fédérations hospitalières', 'La méthodologie de référence MR-005 encadre l’accès par des établissements de santé et des fédérations hospitalières aux données du Programme de médicalisation des systèmes d''information (PMSI) et aux RPU (Résumé de passage aux urgences) mises à disposition sur la plateforme sécurisée de l’Agence technique de l’information sur l’hospitalisation (ATIH). Les responsables de traitement ont l’obligation de documenter les projets menés dans le registre des activités de traitement. Les études menées doivent présenter un caractère d’intérêt public et aucun appariement avec d’autres données à caractère personnel n’est autorisé. Les responsables de traitement doivent enregistrer leurs traitements auprès d’un répertoire public tenu par l’INDS.', 'MR-005.pdf', '1584952257.pdf', NOW(), NOW()),
('MR', '006', 'Études nécessitant l’accès aux données du PMSI par les industriels de santé', 'La MR 006 encadre l’accès par des industriels de santé aux données du Programme de médicalisation des systèmes d''information (PMSI) de l’Agence technique de l’information sur l’hospitalisation (ATIH) mises à disposition via une solution sécurisée. Les responsables de traitement ont l’obligation de documenter les projets menés dans le registre des activités de traitement. Les études menées doivent présenter un caractère d’intérêt public et aucun appariement avec d’autres données à caractère personnel n’est possible. Les responsables de traitement doivent enregistrer leurs traitements auprès d’un répertoire public tenu par l’INDS. Les industriels devront recourir à un bureau d’études/laboratoires de recherches ayant réalisé un engagement de conformité au référentiel fixé par l’arrêté du 17 juillet 2017 auprès de la CNIL. Ils devront également faire réaliser un audit indépendant tous les 3 ans sur l’utilisation des données et le respect de l’interdiction des finalités interdites.', 'MR-006.pdf', '1584952271.pdf', NOW(), NOW()),
('RS', '001', 'Gestion des vigilances sanitaires', 'Le référentiel « RS-001 » encadre les traitements de données à caractère personnel mis en œuvre à des fins de gestion des vigilances sanitaires. Ce référentiel s’applique à l’ensemble des vigilances sanitaires visées par l''arrêté du 27 février 2017 fixant la liste des catégories d''événements sanitaires indésirables pour lesquels la déclaration ou le signalement peut s''effectuer au moyen du portail de signalement des événements sanitaires indésirables (pharmacovigilance, cosmétovigilance, addictovigilance, hémovigilance, etc.) . Seuls les fabricants, entreprises, exploitants, organismes responsables de la mise sur le marché d''un médicament, d''un dispositif ou d''un produit peuvent effectuer un engagement de conformité au référentiel « RS-001 ». Ce référentiel ne couvre pas les traitements mis en œuvre par les professionnels de santé et les systèmes ou services de soins de santé (p. ex. : établissements de santé, maisons de santé, centres de santé, agences sanitaires, etc.).', 'RS-001.pdf', '1584952286.pdf', NOW(), NOW());
tguillon's avatar
tguillon committed
183

184
-- Typages des annexes
185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212
INSERT INTO liste_droits (libelle, value, created, modified) VALUES
('Gestion du typage des annexes', 32, NOW(), NOW());

CREATE TABLE typages (
    id SERIAL NOT NULL PRIMARY KEY,
    libelle VARCHAR(255) NOT NULL,
    createdbyorganisation INT DEFAULT NULL,
    created timestamp without time zone NOT NULL,
    modified timestamp without time zone NOT NULL
);
CREATE UNIQUE INDEX typages_libelle_idx ON typages (libelle);

INSERT INTO typages (libelle, created, modified) VALUES
('Sous-traitance',  NOW(), NOW()),
('Co-responsabilité',  NOW(), NOW()),
('AIPD',  NOW(), NOW()),
('Autre',  NOW(), NOW());

CREATE TABLE typages_organisations (
    id SERIAL PRIMARY KEY NOT NULL,
    typage_id INTEGER NOT NULL REFERENCES typages(id) ON DELETE CASCADE ON UPDATE CASCADE,
    organisation_id INTEGER NOT NULL REFERENCES organisations(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE UNIQUE INDEX typages_organisations_typage_id_organisation_id_idx ON typages_organisations (typage_id, organisation_id);

INSERT INTO typages_organisations (typage_id, organisation_id)
    SELECT typages.id, organisations.id
        FROM typages
213
            INNER JOIN organisations ON (1 = 1);
214

215
ALTER TABLE fichiers ADD COLUMN typage_id INTEGER DEFAULT NULL REFERENCES typages(id) ON DELETE CASCADE ON UPDATE CASCADE;
tguillon's avatar
tguillon committed
216

Théo GUILLON's avatar
Théo GUILLON committed
217
CREATE TABLE coresponsables (
218 219 220
    id serial NOT NULL PRIMARY KEY,
    fiche_id INTEGER NOT NULL REFERENCES fiches(id) ON DELETE CASCADE ON UPDATE CASCADE,
    responsable_id INTEGER NOT NULL,
221 222 223 224 225
    nomcoresponsable VARCHAR(50) NOT NULL,
    prenomcoresponsable VARCHAR(50) NOT NULL,
    fonctioncoresponsable VARCHAR(75) NOT NULL,
    emailcoresponsable VARCHAR(75) NOT NULL,
    telephonecoresponsable VARCHAR(15) NOT NULL,
226 227 228 229 230 231 232 233 234 235 236
    raisonsocialestructure VARCHAR(75) NOT NULL,
    siretstructure VARCHAR(14) NOT NULL,
    apestructure VARCHAR(5) NOT NULL,
    telephonestructure VARCHAR(15),
    faxstructure VARCHAR(15),
    adressestructure TEXT,
    emailstructure VARCHAR(75),
    created timestamp without time zone NOT NULL,
    modified timestamp without time zone NOT NULL
);

237 238
ALTER TABLE coresponsables ADD CONSTRAINT coresponsables_emailresponsable_email_chk CHECK ( cakephp_validate_email( emailcoresponsable ) );
ALTER TABLE coresponsables ADD CONSTRAINT coresponsables_telephoneresponsable_phone_chk CHECK ( cakephp_validate_phone( telephonecoresponsable, NULL, 'fr' ) );
239 240 241 242
ALTER TABLE coresponsables ADD CONSTRAINT coresponsables_apestructure_alpha_numeric_chk CHECK ( cakephp_validate_alpha_numeric( apestructure ) );
ALTER TABLE coresponsables ADD CONSTRAINT coresponsables_telephonestructure_phone_chk CHECK ( cakephp_validate_phone( telephonestructure, NULL, 'fr' ) );
ALTER TABLE coresponsables ADD CONSTRAINT coresponsables_faxstructure_phone_chk CHECK ( cakephp_validate_phone( faxstructure, NULL, 'fr' ) );
ALTER TABLE coresponsables ADD CONSTRAINT coresponsables_emailstructure_email_chk CHECK ( cakephp_validate_email( emailstructure ) );
243

244 245 246 247 248 249 250
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='telephonecoresponsable';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='siretstructure';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='apestructure';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='telephonestructure';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='faxstructure';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='emailstructure';

251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
INSERT INTO coresponsables (
fiche_id,
responsable_id,
nomcoresponsable,
prenomcoresponsable,
fonctioncoresponsable,
emailcoresponsable,
telephonecoresponsable,
raisonsocialestructure,
siretstructure,
apestructure,
telephonestructure,
faxstructure,
adressestructure,
emailstructure,
created,
modified
)
    SELECT
        fiches.id,
        fiches.coresponsableid,
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'nomcoresponsable' LIMIT 1) AS "nomcoresponsable",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'prenomcoresponsable' LIMIT 1) AS "prenomcoresponsable",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'fonctioncoresponsable' LIMIT 1) AS "fonctioncoresponsable",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'emailcoresponsable' LIMIT 1) AS "emailcoresponsable",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'telephonecoresponsable' LIMIT 1) AS "telephonecoresponsable",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'raisonsocialestructure' LIMIT 1) AS "raisonsocialestructure",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'siretstructure' LIMIT 1) AS "siretstructure",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'apestructure' LIMIT 1) AS "apestructure",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'telephonestructure' LIMIT 1) AS "telephonestructure",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'faxstructure' LIMIT 1) AS "faxstructure",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'adressestructure' LIMIT 1) AS "adressestructure",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'emailstructure' LIMIT 1) AS "emailstructure",
        NOW(),
        NOW()
    FROM fiches
    WHERE
        fiches.coresponsableid IS NOT NULL ;

290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305
ALTER TABLE fiches DROP coresponsableid;

DELETE FROM valeurs WHERE champ_name IN (
    'nomcoresponsable',
    'prenomcoresponsable',
    'fonctioncoresponsable',
    'emailcoresponsable',
    'telephonecoresponsable',
    'raisonsocialestructure',
    'siretstructure',
    'apestructure',
    'telephonestructure',
    'faxstructure',
    'adressestructure',
    'emailstructure'
);
306

307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372
CREATE TABLE soustraitances (
    id serial NOT NULL PRIMARY KEY,
    fiche_id INTEGER NOT NULL REFERENCES fiches(id) ON DELETE CASCADE ON UPDATE CASCADE,
    soustraitant_id INTEGER NOT NULL,
    raisonsociale VARCHAR(75) NOT NULL,
    siret VARCHAR(14) NOT NULL,
    ape VARCHAR(5) NOT NULL,
    telephone VARCHAR(15),
    fax VARCHAR(15),
    adresse TEXT,
    email VARCHAR(75),
    created timestamp without time zone NOT NULL,
    modified timestamp without time zone NOT NULL
);
ALTER TABLE soustraitances ADD CONSTRAINT soustraitances_ape_alpha_numeric_chk CHECK ( cakephp_validate_alpha_numeric( ape ) );
ALTER TABLE soustraitances ADD CONSTRAINT soustraitances_telephone_phone_chk CHECK ( cakephp_validate_phone( telephone, NULL, 'fr' ) );
ALTER TABLE soustraitances ADD CONSTRAINT soustraitances_fax_phone_chk CHECK ( cakephp_validate_phone( fax, NULL, 'fr' ) );
ALTER TABLE soustraitances ADD CONSTRAINT soustraitances_email_email_chk CHECK ( cakephp_validate_email( email ) );

UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='soustraitantsiret';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='soustraitantape';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='soustraitanttelephone';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='soustraitantfax';
UPDATE valeurs SET valeur = replace(valeur, ' ', '') WHERE champ_name='soustraitantemail';

INSERT INTO soustraitances (
fiche_id,
soustraitant_id,
raisonsociale,
siret,
ape,
telephone,
fax,
adresse,
email,
created,
modified
)
    SELECT
        fiches.id,
        fiches.soustraitantid,
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'soustraitantraisonsociale' LIMIT 1) AS "soustraitantraisonsociale",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'soustraitantsiret' LIMIT 1) AS "soustraitantsiret",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'soustraitantape' LIMIT 1) AS "soustraitantape",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'soustraitanttelephone' LIMIT 1) AS "soustraitanttelephone",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'soustraitantfax' LIMIT 1) AS "soustraitantfax",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'soustraitantadresse' LIMIT 1) AS "soustraitantadresse",
        (SELECT valeurs.valeur FROM valeurs WHERE valeurs.fiche_id = fiches.id AND valeurs.champ_name = 'soustraitantemail' LIMIT 1) AS "soustraitantemail",
        NOW(),
        NOW()
    FROM fiches
    WHERE
        fiches.soustraitantid IS NOT NULL ;

ALTER TABLE fiches DROP soustraitantid;

DELETE FROM valeurs WHERE champ_name IN (
    'soustraitantraisonsociale',
    'soustraitantsiret',
    'soustraitantape',
    'soustraitanttelephone',
    'soustraitantfax',
    'soustraitantadresse',
    'soustraitantemail'
);

373 374 375 376 377 378 379 380 381 382
ALTER TABLE users DROP COLUMN createdby;

ALTER TABLE commentaires DROP CONSTRAINT "commentaires_user_id_fkey";
ALTER TABLE commentaires ALTER COLUMN user_id DROP NOT NULL;
ALTER TABLE commentaires ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE commentaires DROP CONSTRAINT "commentaires_destinataire_id_fkey";
ALTER TABLE commentaires ALTER COLUMN destinataire_id DROP NOT NULL;
ALTER TABLE commentaires ADD FOREIGN KEY (destinataire_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE;

383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401
UPDATE valeurs SET champ_name = 'rt_organisation_raisonsociale' WHERE champ_name='raisonsociale';
UPDATE valeurs SET champ_name = 'rt_organisation_telephone' WHERE champ_name='telephone';
UPDATE valeurs SET champ_name = 'rt_organisation_fax' WHERE champ_name='fax';
UPDATE valeurs SET champ_name = 'rt_organisation_adresse' WHERE champ_name='adresse';
UPDATE valeurs SET champ_name = 'rt_organisation_email' WHERE champ_name='email';
UPDATE valeurs SET champ_name = 'rt_organisation_sigle' WHERE champ_name='sigle';
UPDATE valeurs SET champ_name = 'rt_organisation_siret' WHERE champ_name='siret';
UPDATE valeurs SET champ_name = 'rt_organisation_ape' WHERE champ_name='ape';
UPDATE valeurs SET champ_name = 'rt_organisation_civiliteresponsable' WHERE champ_name='civiliteresponsable';
UPDATE valeurs SET champ_name = 'rt_organisation_prenomresponsable' WHERE champ_name='prenomresponsable';
UPDATE valeurs SET champ_name = 'rt_organisation_nomresponsable' WHERE champ_name='nomresponsable';
UPDATE valeurs SET champ_name = 'rt_organisation_fonctionresponsable' WHERE champ_name='fonctionresponsable';
UPDATE valeurs SET champ_name = 'rt_organisation_emailresponsable' WHERE champ_name='emailresponsable';
UPDATE valeurs SET champ_name = 'rt_organisation_telephoneresponsable' WHERE champ_name='telephoneresponsable';
UPDATE valeurs SET champ_name = 'rt_organisation_nom_complet_dpo' WHERE champ_name='dpo';
UPDATE valeurs SET champ_name = 'rt_organisation_numerodpo' WHERE champ_name='numerodpo';
UPDATE valeurs SET champ_name = 'rt_organisation_telephonefixe_dpo' WHERE champ_name='fixDpo';
UPDATE valeurs SET champ_name = 'rt_organisation_email_dpo' WHERE champ_name='emailDpo';
UPDATE valeurs SET champ_name = 'rt_organisation_telephoneportable_dpo' WHERE champ_name='portableDpo';
402 403 404 405

DELETE FROM valeurs WHERE champ_name = 'coresponsable';
DELETE FROM valeurs WHERE champ_name = 'soustraitance';

406 407
ALTER TABLE formulaires ADD COLUMN rt_externe BOOL DEFAULT FALSE;
ALTER TABLE fiches ADD COLUMN rt_externe BOOL NOT NULL DEFAULT FALSE;
408

409
ALTER TABLE fiches ADD COLUMN transfert_hors_ue BOOL DEFAULT FALSE;
410 411 412 413
UPDATE fiches SET transfert_hors_ue = true WHERE id IN (SELECT valeurs.fiche_id FROM valeurs WHERE valeurs.champ_name = 'transfertHorsUe' AND valeurs.valeur = 'Oui');
UPDATE fiches SET transfert_hors_ue = false WHERE id IN (SELECT valeurs.fiche_id FROM valeurs WHERE valeurs.champ_name = 'transfertHorsUe' AND valeurs.valeur = 'Non');
DELETE FROM valeurs WHERE champ_name = 'transfertHorsUe';

414
ALTER TABLE fiches ADD COLUMN donnees_sensibles BOOL DEFAULT FALSE;
415 416 417
UPDATE fiches SET donnees_sensibles = true WHERE id IN (SELECT valeurs.fiche_id FROM valeurs WHERE valeurs.champ_name = 'donneesSensible' AND valeurs.valeur = 'Oui');
UPDATE fiches SET donnees_sensibles = false WHERE id IN (SELECT valeurs.fiche_id FROM valeurs WHERE valeurs.champ_name = 'donneesSensible' AND valeurs.valeur = 'Non');
DELETE FROM valeurs WHERE champ_name = 'donneesSensible';
418

tguillon's avatar
tguillon committed
419
COMMIT;