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 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 Code_etab
BIGINT(10) DEFAULT NULL,
20 pid
INT(11) UNSIGNED
DEFAULT NULL,
21 jobid
INT(6) UNSIGNED
DEFAULT NULL,
22 text TEXT DEFAULT NULL,
23 PRIMARY KEY (ax_id
, Type_adr
),
26 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
28 LOAD DATA LOCAL INFILE
'{?}Adresses.txt' INTO TABLE `fusionax_adresses`
CHARACTER SET utf8 FIELDS TERMINATED
BY '\t' LINES TERMINATED
BY '\r\n'
29 (provenance
, ax_id
, @Type_adr
, Ligne1
, Ligne2
, Ligne3
, code_postal
, ville
, zip_cedex
, etat_distr
, pays
, tel
, fax
, @StringDate_maj
)
31 `Type_adr`
= IF(@Type_adr
= 'E', 'E', IF(@Type_adr
= '', '', 'P'));
33 LOAD DATA LOCAL INFILE
'{?}Anciens.txt' INTO TABLE `fusionax_adresses`
CHARACTER SET utf8 FIELDS TERMINATED
BY '\t' LINES TERMINATED
BY '\r\n'
34 (provenance
, ax_id
, @
login, @
password, @promotion_etude
, @gpe_promo
, @Nom_patronymique
, @partic_patro
, @prenom
, @Nom_usuel
, @partic_nom
,
35 @Nom_complet
, @civilite
, @Code_nationalite
, @
type, @corps_sortie
, @StringDate_deces
, @grade
, @Mel_usage
, @Mel_publiable
, @xxx
, @xxx
,
36 @tel_mobile
, @xxx
, @xxx
, @xxx
, @xxx
, @xxx
, @xxx
, @xxx
, @X_M_D
, @xxx
, @xxx
, @xxx
, @xxx
, @xxx
, @xxx
, @Type_adr
,
37 Ligne1
, Ligne2
, Ligne3
, code_postal
, ville
, zip_cedex
, etat_distr
, pays
, tel
, fax
, @date_MAJ
)
39 Type_adr
= IF(@Type_adr
= 'E', 'E', IF(@Type_adr
= '', '', 'P'));
41 LOAD DATA LOCAL INFILE
'{?}Activites.txt' INTO TABLE `fusionax_adresses`
CHARACTER SET utf8 FIELDS TERMINATED
BY '\t' LINES TERMINATED
BY '\r\n'
42 (provenance
, ax_id
, Code_etab
, @Raison_sociale
, @Libelle_fonctio
, @Annuaire
,
43 Ligne1
, Ligne2
, Ligne3
, code_postal
, ville
, zip_cedex
, etat_distr
, pays
, tel
, fax
, @StringDate_maj
)
47 UPDATE fusionax_adresses
SET Ligne1
= TRIM(Ligne1
), Ligne2
= TRIM(Ligne2
), Ligne3
= TRIM(Ligne3
), pays
= TRIM(pays
),
48 code_postal
= TRIM(code_postal
), ville
= TRIM(ville
), zip_cedex
= TRIM(zip_cedex
),
49 tel
= TRIM(tel
), fax
= TRIM(fax
);
50 UPDATE fusionax_adresses
SET ville
= '' WHERE ville
= '.';
51 DELETE FROM fusionax_adresses
WHERE ville
= '' AND tel
= '' AND fax
= '';
52 UPDATE fusionax_adresses
SET text = CONCAT(IF(Ligne1
!= '', CONCAT(Ligne1
, '\n'), ''),
53 IF(Ligne2
!= '', CONCAT(Ligne2
, '\n'), ''),
54 IF(Ligne3
!= '', CONCAT(Ligne3
, '\n'), ''),
55 IF(code_postal
!= '', code_postal
, zip_cedex
), ' ', ville
,
56 IF(pays
!= '', CONCAT('\n', pays
), ''))
58 ALTER TABLE fusionax_adresses
ADD INDEX (text(20));