| 1 | -- Import complet des adresses |
| 2 | |
| 3 | DROP TABLE IF EXISTS fusionax_adresses; |
| 4 | |
| 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) |
| 22 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; |
| 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) |
| 26 | SET |
| 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) |
| 36 | SET |
| 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) |
| 44 | SET |
| 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)); |