Commit | Line | Data |
---|---|---|
b9ad0878 PC |
1 | -- Import complet des adresses |
2 | ||
22f043e4 | 3 | DROP TABLE IF EXISTS fusionax_adresses; |
b9ad0878 | 4 | |
22f043e4 SJ |
5 | CREATE TABLE IF NOT EXISTS fusionax_adresses ( |
6 | provenance CHAR(2) NOT NULL COMMENT 'Vaut AC, AD ou AN selon la provenance de l''info', | |
7 | id_ancien VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien', | |
8 | Type_adr ENUM('E', 'P') character set binary NOT NULL DEFAULT 'P' COMMENT 'Type d''adresse : E pour Entreprise, P pour Personnelle', | |
9 | hash_adresse BINARY(5) NOT NULL COMMENT 'Hash pour différencier les diverses adresses', | |
10 | Ligne1 VARCHAR(90) character set utf8 NOT NULL, | |
11 | Ligne2 VARCHAR(90) character set utf8 NOT NULL, | |
12 | Ligne3 VARCHAR(90) character set utf8 NOT NULL, | |
13 | code_postal VARCHAR(20) character set utf8 NOT NULL, | |
14 | ville VARCHAR(80) character set utf8 NOT NULL, | |
15 | zip_cedex VARCHAR(20) character set utf8 NOT NULL, | |
16 | etat_distr VARCHAR(20) character set utf8 NOT NULL, | |
17 | pays VARCHAR(50) character set utf8 NOT NULL, | |
18 | tel VARCHAR(30) NOT NULL, | |
19 | fax VARCHAR(30) NOT NULL, | |
20 | Date_maj DATE NOT NULL, | |
21 | PRIMARY KEY (id_ancien, hash_adresse) | |
73be4434 | 22 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; |
b9ad0878 PC |
23 | |
24 | LOAD DATA LOCAL INFILE 'Adresses.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' | |
25 | (provenance, id_ancien, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) | |
73be4434 | 26 | SET |
b9ad0878 PC |
27 | `Type_adr` = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')), |
28 | `hash_adresse` = SUBSTRING( MD5( @Type_adr ), 1, 5), | |
29 | `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); | |
30 | ||
31 | LOAD DATA LOCAL INFILE 'Anciens.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' | |
32 | (provenance, id_ancien, @Login, @Password, @promotion_etude, @Groupe_promo, @Nom_patronymique, @partic_patro, @prenom, @Nom_usuel, @partic_nom, | |
33 | @Nom_complet, @Civilite, @Code_nationalite, @Type_membre, @corps_sortie, @StringDate_deces, @grade, @Mel_usage, @Mel_publiable, | |
34 | @tel_mobile, @annee_dernCot, @Representant, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville, | |
35 | zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) | |
73be4434 | 36 | SET |
b9ad0878 PC |
37 | `Type_adr` = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')), |
38 | `hash_adresse` = SUBSTRING( MD5( @Type_adr ), 1, 5), | |
39 | `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); | |
40 | ||
41 | LOAD DATA LOCAL INFILE 'Activites.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' | |
42 | (provenance, id_ancien, @Code_etab, @Raison_sociale, @Libelle_fonctio, @Annuaire, | |
43 | Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) | |
73be4434 | 44 | SET |
b9ad0878 PC |
45 | `Type_adr` = 'E', |
46 | `hash_adresse` = SUBSTRING( MD5( CONCAT(@Code_etab, @Libelle_fonctio) ), 1, 5), | |
47 | `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); |