1 -- Import complet des adresses
3 DROP TABLE IF EXISTS fusionax_adresses
;
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
;
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
)
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));
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
)
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));
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
)
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));