| 1 | -- Import complet des adresses |
| 2 | |
| 3 | DROP TABLE IF EXISTS `fusionax_adresses`; |
| 4 | |
| 5 | CREATE TABLE IF NOT EXISTS `fusionax_adresses` ( |
| 6 | `provenance` varbinary(2) NOT NULL COMMENT 'Vaut AC, AD ou AN selon la provenance de l''info', |
| 7 | `id_ancien` varbinary(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(60) character set utf8 NOT NULL, |
| 11 | `Ligne2` varchar(60) character set utf8 NOT NULL, |
| 12 | `Ligne3` varchar(60) character set utf8 NOT NULL, |
| 13 | `code_postal` varchar(20) character set utf8 NOT NULL, |
| 14 | `ville` varchar(40) 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` varbinary(30) NOT NULL, |
| 19 | `fax` varbinary(30) NOT NULL, |
| 20 | `Date_maj` date NOT NULL, |
| 21 | PRIMARY KEY (`id_ancien`, `hash_adresse`) |
| 22 | ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_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)); |