| 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 | ax_id 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 | Ligne1 VARCHAR(90) NOT NULL, |
| 10 | Ligne2 VARCHAR(90) NOT NULL, |
| 11 | Ligne3 VARCHAR(90) NOT NULL, |
| 12 | code_postal VARCHAR(20) NOT NULL, |
| 13 | ville VARCHAR(80) NOT NULL, |
| 14 | zip_cedex VARCHAR(20) NOT NULL, |
| 15 | etat_distr VARCHAR(20) NOT NULL, |
| 16 | pays VARCHAR(50) NOT NULL, |
| 17 | tel VARCHAR(30) NOT NULL, |
| 18 | fax VARCHAR(30) NOT NULL, |
| 19 | Date_maj DATE NOT NULL COMMENT 'Date de mise à jour de ces informations', |
| 20 | Code_etab BIGINT(10) DEFAULT NULL, |
| 21 | pid INT(11) UNSIGNED DEFAULT NULL, |
| 22 | jobid INT(6) UNSIGNED DEFAULT NULL, |
| 23 | text TEXT DEFAULT NULL, |
| 24 | PRIMARY KEY (ax_id, Type_adr), |
| 25 | INDEX (pid), |
| 26 | INDEX (jobid) |
| 27 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 28 | |
| 29 | LOAD DATA LOCAL INFILE '{?}Adresses.txt' INTO TABLE `fusionax_adresses` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' |
| 30 | (provenance, ax_id, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) |
| 31 | SET |
| 32 | `Type_adr` = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')), |
| 33 | `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); |
| 34 | |
| 35 | LOAD DATA LOCAL INFILE '{?}Anciens.txt' INTO TABLE `fusionax_adresses` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' |
| 36 | (provenance, ax_id, @login, @password, @promotion_etude, @gpe_promo, @Nom_patronymique, @partic_patro, @prenom, @Nom_usuel, @partic_nom, |
| 37 | @Nom_complet, @civilite, @Code_nationalite, @type, @corps_sortie, @StringDate_deces, @grade, @Mel_usage, @Mel_publiable, @xxx, @xxx, |
| 38 | @tel_mobile, @xxx, @xxx, @xxx, @xxx, @xxx, @xxx, @xxx, @X_M_D, @xxx, @xxx, @xxx, @xxx, @xxx, @xxx, @Type_adr, |
| 39 | Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) |
| 40 | SET |
| 41 | Type_adr = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')), |
| 42 | `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); |
| 43 | |
| 44 | LOAD DATA LOCAL INFILE '{?}Activites.txt' INTO TABLE `fusionax_adresses` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' |
| 45 | (provenance, ax_id, Code_etab, @Raison_sociale, @Libelle_fonctio, @Annuaire, |
| 46 | Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj) |
| 47 | SET |
| 48 | `Type_adr` = 'E', |
| 49 | `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2)); |
| 50 | |
| 51 | UPDATE fusionax_adresses SET Ligne1 = TRIM(Ligne1), Ligne2 = TRIM(Ligne2), Ligne3 = TRIM(Ligne3), pays = TRIM(pays), |
| 52 | code_postal = TRIM(code_postal), ville = TRIM(ville), zip_cedex = TRIM(zip_cedex), |
| 53 | tel = TRIM(tel), fax = TRIM(fax); |
| 54 | UPDATE fusionax_adresses SET ville = '' WHERE ville = '.'; |
| 55 | DELETE FROM fusionax_adresses WHERE ville = '' AND tel = '' AND fax = ''; |
| 56 | UPDATE fusionax_adresses SET text = CONCAT(IF(Ligne1 != '', CONCAT(Ligne1, '\n'), ''), |
| 57 | IF(Ligne2 != '', CONCAT(Ligne2, '\n'), ''), |
| 58 | IF(Ligne3 != '', CONCAT(Ligne3, '\n'), ''), |
| 59 | IF(code_postal != '', code_postal, zip_cedex), ' ', ville, |
| 60 | IF(pays != '', CONCAT('\n', pays), '')) |
| 61 | WHERE ville != ''; |
| 62 | ALTER TABLE fusionax_adresses ADD INDEX (text(20)); |