Merge remote branch 'origin/xorg/maint' into xorg/1.0.2/master

[platal.git] / modules / fusionax / Adresses.sql

diff --git a/modules/fusionax/Adresses.sql b/modules/fusionax/Adresses.sql
index f7d31a8..6e2b2fe 100644 (file)
--- a/modules/fusionax/Adresses.sql
+++ b/modules/fusionax/Adresses.sql
@@ -4,9 +4,8 @@ DROP TABLE IF EXISTS fusionax_adresses;
 
 CREATE TABLE IF NOT EXISTS fusionax_adresses (
   provenance CHAR(2) NOT NULL COMMENT 'Vaut AC, AD ou AN selon la provenance de l''info',
 
 CREATE TABLE IF NOT EXISTS fusionax_adresses (
   provenance CHAR(2) NOT NULL COMMENT 'Vaut AC, AD ou AN selon la provenance de l''info',

-  id_ancien VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien',
+  ax_id VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien',

   Type_adr ENUM('E', 'P') character set binary NOT NULL DEFAULT 'P' COMMENT 'Type d''adresse : E pour Entreprise, P pour Personnelle',
   Type_adr ENUM('E', 'P') character set binary NOT NULL DEFAULT 'P' COMMENT 'Type d''adresse : E pour Entreprise, P pour Personnelle',

-  hash_adresse BINARY(5) NOT NULL COMMENT 'Hash pour différencier les diverses adresses',

   Ligne1 VARCHAR(90) NOT NULL,
   Ligne2 VARCHAR(90) NOT NULL,
   Ligne3 VARCHAR(90) NOT NULL,
   Ligne1 VARCHAR(90) NOT NULL,
   Ligne2 VARCHAR(90) NOT NULL,
   Ligne3 VARCHAR(90) NOT NULL,


@@ -17,31 +16,47 @@ CREATE TABLE IF NOT EXISTS fusionax_adresses (
   pays VARCHAR(50) NOT NULL,
   tel VARCHAR(30) NOT NULL,
   fax VARCHAR(30) NOT NULL,
   pays VARCHAR(50) NOT NULL,
   tel VARCHAR(30) NOT NULL,
   fax VARCHAR(30) NOT NULL,

-  Date_maj DATE NOT NULL,
-  PRIMARY KEY  (id_ancien, hash_adresse)
+  Date_maj DATE NOT NULL COMMENT 'Date de mise à jour de ces informations',
+  Code_etab BIGINT(10) DEFAULT NULL,
+  pid INT(11) UNSIGNED DEFAULT NULL,
+  jobid INT(6) UNSIGNED DEFAULT NULL,
+  text TEXT DEFAULT NULL,
+  PRIMARY KEY (ax_id, Type_adr),
+  INDEX (pid),
+  INDEX (jobid)

 ) ENGINE=InnoDB, CHARSET=utf8;
 
 ) ENGINE=InnoDB, CHARSET=utf8;
 

-LOAD DATA LOCAL INFILE '{?}Adresses.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
-(provenance, id_ancien, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj)
+LOAD DATA LOCAL INFILE '{?}Adresses.txt' INTO TABLE `fusionax_adresses` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
+(provenance, ax_id, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj)

 SET
 `Type_adr` = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')),
 SET
 `Type_adr` = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')),

-`hash_adresse` = SUBSTRING( MD5( @Type_adr ), 1, 5),

 `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2));
 
 `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2));
 

-LOAD DATA LOCAL INFILE '{?}Anciens.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
-(provenance, id_ancien, @Login, @Password, @promotion_etude, @Groupe_promo, @Nom_patronymique, @partic_patro, @prenom, @Nom_usuel, @partic_nom,
-    @Nom_complet, @Civilite, @Code_nationalite, @Type_membre, @corps_sortie, @StringDate_deces, @grade, @Mel_usage, @Mel_publiable,
-    @tel_mobile, @annee_dernCot, @Representant, @Type_adr, Ligne1, Ligne2, Ligne3, code_postal, ville,
-    zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj)
+LOAD DATA LOCAL INFILE '{?}Anciens.txt' INTO TABLE `fusionax_adresses` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
+(provenance, ax_id, @login, @password, @promotion_etude, @gpe_promo, @Nom_patronymique, @partic_patro, @prenom, @Nom_usuel, @partic_nom,
+  @Nom_complet, @civilite, @Code_nationalite, @type, @corps_sortie, @StringDate_deces, @grade, @Mel_usage, @Mel_publiable, @xxx, @xxx,
+  @tel_mobile, @xxx, @xxx, @xxx, @xxx, @xxx, @xxx, @xxx, @X_M_D, @xxx, @xxx, @xxx, @xxx, @xxx, @xxx, @Type_adr,
+  Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj)

 SET
 SET

-`Type_adr` = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')),
-`hash_adresse` = SUBSTRING( MD5( @Type_adr ), 1, 5),
+Type_adr = IF(@Type_adr = 'E', 'E', IF(@Type_adr = '', '', 'P')),

 `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2));
 
 `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2));
 

-LOAD DATA LOCAL INFILE '{?}Activites.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
-(provenance, id_ancien, @Code_etab, @Raison_sociale, @Libelle_fonctio, @Annuaire,
+LOAD DATA LOCAL INFILE '{?}Activites.txt' INTO TABLE `fusionax_adresses` CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
+(provenance, ax_id, Code_etab, @Raison_sociale, @Libelle_fonctio, @Annuaire,

 Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj)
 SET
 `Type_adr` = 'E',
 Ligne1, Ligne2, Ligne3, code_postal, ville, zip_cedex, etat_distr, pays, tel, fax, @StringDate_maj)
 SET
 `Type_adr` = 'E',

-`hash_adresse` = SUBSTRING( MD5( CONCAT(@Code_etab, @Libelle_fonctio) ), 1, 5),

 `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2));
 `Date_maj` = CONCAT(SUBSTRING(@StringDate_maj,7),'-',SUBSTRING(@StringDate_maj,4,2),'-',SUBSTRING(@StringDate_maj,1,2));

+
+UPDATE fusionax_adresses SET Ligne1 = TRIM(Ligne1), Ligne2 = TRIM(Ligne2), Ligne3 = TRIM(Ligne3), pays = TRIM(pays),
+                             code_postal = TRIM(code_postal), ville = TRIM(ville), zip_cedex = TRIM(zip_cedex),
+                             tel = TRIM(tel), fax = TRIM(fax);
+UPDATE fusionax_adresses SET ville = '' WHERE ville = '.';
+DELETE FROM fusionax_adresses WHERE ville = '' AND tel = '' AND fax = '';
+UPDATE fusionax_adresses SET text = CONCAT(IF(Ligne1 != '', CONCAT(Ligne1, '\n'), ''),
+                                           IF(Ligne2 != '', CONCAT(Ligne2, '\n'), ''),
+                                           IF(Ligne3 != '', CONCAT(Ligne3, '\n'), ''),
+                                           IF(code_postal != '', code_postal, zip_cedex), ' ', ville,
+                                           IF(pays != '', CONCAT('\n', pays), ''))
+                         WHERE ville != '';
+ALTER TABLE fusionax_adresses ADD INDEX (text(20));