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