From a48b23c3d3e357a2f6e776f452074fe0ee5d594f Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Mon, 25 Oct 2010 15:27:17 +0200 Subject: [PATCH] Prevents joins in merge.php. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- modules/fusionax/Activites.sql | 12 +- modules/fusionax/Adresses.sql | 6 +- modules/fusionax/Anciens.sql | 7 +- modules/fusionax/Entreprises.sql | 3 +- modules/fusionax/Formations.sql | 7 +- upgrade/1.0.1/merge.php | 261 +++++++++++++++------------------------ 6 files changed, 130 insertions(+), 166 deletions(-) diff --git a/modules/fusionax/Activites.sql b/modules/fusionax/Activites.sql index b436100..653ec74 100644 --- a/modules/fusionax/Activites.sql +++ b/modules/fusionax/Activites.sql @@ -9,7 +9,13 @@ CREATE TABLE IF NOT EXISTS `fusionax_activites` ( `Raison_sociale` VARCHAR(255) NOT NULL COMMENT 'Raison sociale de l''établissement', `Libelle_fonctio` VARCHAR(255) NOT NULL COMMENT 'Libéllé de la fonction', `Annuaire` BOOLEAN NOT NULL COMMENT 'publiable dans l''annuaire papier', - PRIMARY KEY( `ax_id` , `Code_etab` ) + pid INT(11) UNSIGNED DEFAULT NULL, + jobid INT(6) UNSIGNED DEFAULT NULL, + description VARCHAR(255) DEFAULT NULL, + PRIMARY KEY (`ax_id`, `Code_etab`), + INDEX (Code_etab), + INDEX (pid), + INDEX (jobid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOAD DATA LOCAL INFILE '{?}Activites.txt' INTO TABLE `fusionax_activites` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' @@ -18,3 +24,7 @@ LOAD DATA LOCAL INFILE '{?}Activites.txt' INTO TABLE `fusionax_activites` FIELDS UPDATE fusionax_activites SET Raison_sociale = TRIM(Raison_sociale), Libelle_fonctio = TRIM(Libelle_fonctio); +UPDATE fusionax_activites + SET description = IF(Raison_sociale, + IF(Libelle_fonctio, CONCAT(Raison_sociale, ', ', Libelle_fonctio), Raison_sociale), + Libelle_fonctio); diff --git a/modules/fusionax/Adresses.sql b/modules/fusionax/Adresses.sql index 077b876..b72f851 100644 --- a/modules/fusionax/Adresses.sql +++ b/modules/fusionax/Adresses.sql @@ -17,8 +17,12 @@ CREATE TABLE IF NOT EXISTS fusionax_adresses ( tel VARCHAR(30) NOT NULL, fax VARCHAR(30) NOT NULL, 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) + PRIMARY KEY (ax_id, Type_adr), + INDEX (pid), + INDEX (jobid) ) ENGINE=InnoDB, CHARSET=utf8; LOAD DATA LOCAL INFILE '{?}Adresses.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' diff --git a/modules/fusionax/Anciens.sql b/modules/fusionax/Anciens.sql index 906ccf8..4736e2d 100644 --- a/modules/fusionax/Anciens.sql +++ b/modules/fusionax/Anciens.sql @@ -18,13 +18,16 @@ CREATE TABLE IF NOT EXISTS fusionax_anciens ( grade VARCHAR(50) NOT NULL COMMENT 'Grade actuel dans son corps', Mel_usage VARCHAR(255) NOT NULL COMMENT 'Adresse e-mail d''usage', Mel_publiable TINYINT(4) NOT NULL COMMENT 'Autorisation d''utiliser le mail', + Mob_publiable TINYINT(4) NOT NULL COMMENT 'Autorisation d''utiliser le mobile', tel_mobile VARCHAR(30) NOT NULL COMMENT 'Numéro de téléphone mobile', - PRIMARY KEY (ax_id) + pid INT(11) UNSIGNED DEFAULT NULL, + PRIMARY KEY (ax_id), + INDEX (pid) ) ENGINE=InnoDB, CHARSET=utf8; LOAD DATA LOCAL INFILE '{?}Anciens.txt' INTO TABLE `fusionax_anciens` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (AN, 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, + Nom_complet, @civilite, Code_nationalite, @type, corps_sortie, @StringDate_deces, grade, Mel_usage, Mel_publiable, @xxx, Mob_publiable, 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, @date_MAJ) SET diff --git a/modules/fusionax/Entreprises.sql b/modules/fusionax/Entreprises.sql index acfdd67..3e3cfeb 100644 --- a/modules/fusionax/Entreprises.sql +++ b/modules/fusionax/Entreprises.sql @@ -7,7 +7,8 @@ CREATE TABLE IF NOT EXISTS `fusionax_entreprises` ( `Code_etab` BIGINT(10) NOT NULL COMMENT 'Code de l''établissement', `Raison_sociale` VARCHAR(255) NOT NULL COMMENT 'Raison sociale de l''établissement', `Sigle` VARCHAR(50) NOT NULL COMMENT 'Sigle de l''établissement', - PRIMARY KEY(`Code_etab`) + PRIMARY KEY(`Code_etab`), + INDEX (Raison_sociale(20)) ) ENGINE=InnoDB, CHARSET=utf8; LOAD DATA LOCAL INFILE '{?}Entreprises.txt' INTO TABLE `fusionax_entreprises` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' diff --git a/modules/fusionax/Formations.sql b/modules/fusionax/Formations.sql index 43c89cf..94131c5 100644 --- a/modules/fusionax/Formations.sql +++ b/modules/fusionax/Formations.sql @@ -8,13 +8,18 @@ CREATE TABLE IF NOT EXISTS `fusionax_formations` ( Intitule_diplome CHAR(60) NOT NULL DEFAULT 0 COMMENT 'Intitulé du diplôme', Intitule_formation CHAR(60) NOT NULL DEFAULT 0 COMMENT 'Intitulé de la formation', Descr_formation CHAR(60) NOT NULL COMMENT 'Description de la formation', + pid INT(11) UNSIGNED DEFAULT NULL, eduid INT(4) DEFAULT NULL, degreeid INT(4) DEFAULT NULL, fieldid INT(2) DEFAULT NULL, PRIMARY KEY (ax_id, Intitule_diplome, Intitule_formation, Descr_formation), INDEX (Intitule_diplome), INDEX (Intitule_formation), - INDEX (Descr_formation) + INDEX (Descr_formation), + INDEX (pid), + INDEX (eduid), + INDEX (degreeid), + INDEX (fieldid) ) ENGINE=InnoDB, CHARSET=utf8; LOAD DATA LOCAL INFILE '{?}Formations.txt' INTO TABLE fusionax_formations FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' diff --git a/upgrade/1.0.1/merge.php b/upgrade/1.0.1/merge.php index 7733880..be5407e 100755 --- a/upgrade/1.0.1/merge.php +++ b/upgrade/1.0.1/merge.php @@ -11,9 +11,20 @@ XDB::rawExecute('DROP VIEW IF EXISTS fusionax_deceased'); XDB::rawExecute('DROP VIEW IF EXISTS fusionax_promo'); XDB::rawExecute('DROP TABLE IF EXISTS fusionax_import'); +// Fills pid fields in all table, to avoid to many joins. +foreach (array('fusionax_activites', 'fusionax_adresses', 'fusionax_anciens', 'fusionax_formations') as $table) { + XDB::rawExecute("UPDATE $table AS f + INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) + SET f.pid = p.pid"); + XDB::rawExecute("DELETE FROM $table WHERE pid IS NULL"); +} + // Includes entreprises we do not have into profile_job_enum. // We first retrieve AX code, then add missing compagnies. echo "Starts jobs inclusions.\n"; +XDB::rawExecute('ALTER TABLE profile_job_enum ADD INDEX (name(20))'); +XDB::rawExecute('ALTER TABLE profile_job_enum ADD INDEX (acronym(20))'); +XDB::rawExecute('ALTER TABLE profile_job_enum ADD INDEX (AX_code)'); XDB::rawExecute("UPDATE profile_job_enum, fusionax_entreprises SET profile_job_enum.AX_code = fusionax_entreprises.Code_etab WHERE (profile_job_enum.name = fusionax_entreprises.Raison_sociale AND profile_job_enum.name != '' AND fusionax_entreprises.Raison_sociale != '') @@ -34,137 +45,95 @@ XDB::rawExecute("INSERT IGNORE INTO profile_job_enum (name, acronym, AX_code) // - the job is complete but the profile already has a job or more : this is an issue, // - the job is complete and the the profile has no previous job : there is no issue. -// We delete obvious duplicates. +// We delete obvious duplicates and avoid multiple joins. XDB::rawExecute("DELETE f FROM fusionax_activites AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab) - INNER JOIN profile_job AS pj ON (p.pid = pj.pid AND pj.jobid = pe.id)"); + INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = pe.id)"); +foreach (array('fusionax_activites', 'fusionax_adresses') as $table) { + XDB::rawExecute("UPDATE $table AS f + INNER JOIN profile_job_enum AS pe ON (f.Code_etab = pe.AX_code) + SET f.jobid = pe.id"); +} +XDB::rawExecute('ALTER TABLE profile_job_enum DROP INDEX name'); +XDB::rawExecute('ALTER TABLE profile_job_enum DROP INDEX acronym'); +XDB::rawExecute('ALTER TABLE profile_job_enum DROP INDEX AX_code'); +XDB::rawExecute('DROP TABLE IF EXISTS fusionax_entreprises'); // We first update the issues table. XDB::rawExecute("INSERT INTO profile_merge_issues (pid, issues) - SELECT DISTINCT(p.pid), 'job' - FROM profiles AS p - INNER JOIN fusionax_activites AS f ON (f.ax_id = p.ax_id) - INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab) - WHERE (fe.Raison_sociale = '' AND NOT EXISTS (SELECT * - FROM profile_job AS pj - WHERE pj.pid = p.pid)) - OR (fe.Raison_sociale != '' AND EXISTS (SELECT * - FROM profile_job AS pj - WHERE pj.pid = p.pid))"); -// Then we retrieve jobs without entreprise name. -XDB::rawExecute("INSERT INTO profile_job (id, pid, jobid, pub, description) - SELECT 0, p.pid, NULL, IF(f.Annuaire = 1, 'ax', 'private'), - IF(f.Raison_sociale, - IF(f.Libelle_fonctio, CONCAT(f.Raison_sociale, ' ', f.Libelle_fonctio), f.Raison_sociale), - f.Libelle_fonctio) - FROM fusionax_activites AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab AND fe.Raison_sociale = '') - WHERE NOT EXISTS (SELECT * - FROM profile_job AS pj - WHERE pj.pid = p.pid)"); -// We insert complete jobs for profile already having jobs. -XDB::rawExecute("INSERT INTO profile_job (id, pid, jobid, pub, description) - SELECT MAX(pj.id) + 1, p.pid, pe.id, IF(f.Annuaire = 1, 'ax', 'private'), - IF(f.Raison_sociale, - IF(f.Libelle_fonctio, CONCAT(f.Raison_sociale, ' ', f.Libelle_fonctio), f.Raison_sociale), - f.Libelle_fonctio) - FROM fusionax_activites AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab AND fe.Raison_sociale != '') - INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab) - INNER JOIN profile_job AS pj ON (pj.pid = p.pid) - GROUP BY p.pid"); -// Delete everything that has already been inserted. -XDB::rawExecute("DELETE f - FROM fusionax_activites AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN fusionax_entreprises AS fe ON (fe.Code_etab = f.Code_etab) - WHERE fe.Raison_sociale = '' - OR (fe.Raison_sociale != '' AND EXISTS (SELECT * - FROM profile_job AS pj - WHERE pj.pid = p.pid))"); -// We finally add new complete jobs. + SELECT DISTINCT(f.pid), 'job' + FROM fusionax_activites AS f + WHERE (f.jobid IS NULL AND NOT EXISTS (SELECT * + FROM profile_job AS pj + WHERE pj.pid = f.pid)) + OR (f.jobid IS NOT NULL AND EXISTS (SELECT * + FROM profile_job AS pj + WHERE pj.pid = f.pid))"); +// We then add new jobs. $id = 0; $continue = 1; while ($continue > 0) { XDB::rawExecute("INSERT IGNORE INTO profile_job (id, pid, jobid, pub, description) - SELECT $id, p.pid, pe.id, IF(f.Annuaire = 1, 'ax', 'private'), - IF(f.Raison_sociale, - IF(f.Libelle_fonctio, CONCAT(f.Raison_sociale, ' ', f.Libelle_fonctio), f.Raison_sociale), - f.Libelle_fonctio) - FROM fusionax_activites AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab)"); + SELECT $id, pid, jobid, IF(Annuaire = 1, 'ax', 'private'), description + FROM fusionax_activites"); XDB::rawExecute("DELETE f - FROM fusionax_activites AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab) - INNER JOIN profile_job AS pj ON (p.pid = pj.pid AND pj.id = $id AND pj.jobid = pe.id)"); + FROM fusionax_activites AS f + INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.id = $id AND pj.jobid = f.jobid AND pj.description = f.description)"); $continue = XDB::affectedRows(); } +XDB::rawExecute('DROP TABLE IF EXISTS fusionax_activites'); // We also have to add related phones and addresses. XDB::rawExecute("INSERT IGNORE INTO profile_addresses (type, pid, id, text) - SELECT 'job', p.pid, pj.id, f.text + SELECT 'job', f.pid, pj.id, f.text FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab) - INNER JOIN profile_job AS pj ON (p.pid = pj.pid AND pj.jobid = pe.id) + INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = f.jobid) WHERE f.Type_adr = 'E' AND f.text IS NOT NULL"); XDB::rawExecute("INSERT IGNORE INTO profile_phones (link_type, link_id, tel_id, tel_type, pid, display_tel) - SELECT 'pro', pj.id, 0, 'fixed', p.pid, f.tel + SELECT 'pro', pj.id, 0, 'fixed', f.pid, f.tel FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab) - INNER JOIN profile_job AS pj ON (p.pid = pj.pid AND pj.jobid = pe.id) + INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = f.jobid) WHERE f.Type_adr = 'E' AND f.tel != ''"); XDB::rawExecute("INSERT IGNORE INTO profile_phones (link_type, link_id, tel_id, tel_type, pid, display_tel) - SELECT 'pro', pj.id, IF(f.tel = '', 0, 1), 'fax', p.pid, f.fax + SELECT 'pro', pj.id, IF(f.tel = '', 0, 1), 'fax', f.pid, f.fax FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab) - INNER JOIN profile_job AS pj ON (p.pid = pj.pid AND pj.jobid = pe.id) + INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = f.jobid) WHERE f.Type_adr = 'E' AND f.fax != ''"); -// Drops job related tables and addresses -XDB::rawExecute('DROP TABLE IF EXISTS fusionax_entreprises'); -XDB::rawExecute('DROP TABLE IF EXISTS fusionax_activites'); -XDB::rawExecute("DELETE FROM fusionax_adresses WHERE Type_adr = 'P'"); +XDB::rawExecute("DELETE FROM fusionax_adresses WHERE Type_adr = 'E'"); echo "Jobs inclusions finished.\n"; // Retrieves information from fusionax_anciens: promo, nationality, corps, email, phone, deathdate. // Updates uncertain promotions, but when we are we are right. echo "Starts various informations inclusions.\n"; -XDB::rawExecute("UPDATE profile_merge_issues AS pm, fusionax_anciens AS f, profiles AS p, profile_display AS pd, profile_education AS pe +XDB::rawExecute("UPDATE profile_merge_issues AS pm, fusionax_anciens AS f, profile_display AS pd, profile_education AS pe SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'promo'), 'promo'), pm.entry_year_ax = f.promotion_etude - WHERE pm.pid = p.pid AND p.ax_id = f.ax_id AND p.pid NOT IN (18399,21099,40616) - AND pd.pid = p.pid AND pe.pid = p.pid AND FIND_IN_SET('primary', pe.flags) + WHERE pm.pid = f.pid AND f.pid NOT IN (18399,21099,40616) + AND pd.pid = f.pid AND pe.pid = f.pid AND FIND_IN_SET('primary', pe.flags) AND pd.promo != CONCAT('X', f.promotion_etude) AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)"); + XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues, entry_year_ax) - SELECT p.pid, 'promo', f.promotion_etude - FROM profiles AS p - INNER JOIN profile_display AS pd ON (p.pid = pd.pid) - INNER JOIN profile_education AS pe ON (p.pid = pe.pid) - INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) - WHERE pd.promo != CONCAT('X', f.promotion_etude) AND p.pid NOT IN (18399,21099,40616) + SELECT f.pid, 'promo', f.promotion_etude + FROM fusionax_anciens AS f + INNER JOIN profile_display AS pd ON (f.pid = pd.pid) + INNER JOIN profile_education AS pe ON (f.pid = pe.pid) + WHERE pd.promo != CONCAT('X', f.promotion_etude) AND f.pid NOT IN (18399,21099,40616) AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)"); // Updates nationality. XDB::rawExecute('ALTER TABLE geoloc_countries ADD INDEX (licensePlate)'); XDB::rawExecute('UPDATE profiles AS p - INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) + INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid) INNER JOIN geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite) SET p.nationality1 = g.iso_3166_1_a2 WHERE p.nationality1 IS NULL'); XDB::rawExecute('UPDATE profiles AS p - INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) + INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid) INNER JOIN geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite) SET p.nationality2 = g.iso_3166_1_a2 WHERE p.nationality1 != g.iso_3166_1_a2 AND p.nationality2 IS NULL'); XDB::rawExecute('UPDATE profiles AS p - INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) + INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid) INNER JOIN geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite) SET p.nationality3 = g.iso_3166_1_a2 WHERE p.nationality1 != g.iso_3166_1_a2 AND p.nationality2 != g.iso_3166_1_a2 AND p.nationality3 IS NULL'); @@ -172,14 +141,13 @@ XDB::rawExecute('ALTER TABLE geoloc_countries DROP INDEX licensePlate'); // Updates corps. XDB::rawExecute("INSERT IGNORE INTO profile_corps (pid, original_corpsid, current_corpsid, rankid, corps_pub) - SELECT p.pid, c.id, c.id, r.id, 'ax' - FROM profiles AS p - INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) + SELECT f.pid, c.id, c.id, r.id, 'ax' + FROM fusionax_anciens AS f INNER JOIN profile_corps_enum AS c ON (f.corps_sortie = c.abbreviation) INNER JOIN profile_corps_rank_enum AS r ON (f.grade = r.abbreviation) WHERE NOT EXISTS (SELECT * FROM profile_corps AS pc - WHERE p.pid = pc.pid AND pc.original_corpsid != 1)"); + WHERE f.pid = pc.pid AND pc.original_corpsid != 1)"); XDB::rawExecute("UPDATE profile_corps AS c INNER JOIN profile_corps_enum AS e ON (c.original_corpsid = e.id) LEFT JOIN profile_corps_enum AS a ON (a.name = 'Aucun (anc. démissionnaire)') @@ -193,14 +161,13 @@ XDB::rawExecute("DELETE FROM profile_corps_enum // Updates email_directory. XDB::rawExecute("UPDATE profiles AS p - INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) + INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid) SET p.email_directory = f.Mel_usage - WHERE f.Mel_publiable != '0' AND f.Mel_usage != '' AND p.email_directory IS NULL"); + WHERE f.Mel_publiable = 1 AND f.Mel_usage != '' AND p.email_directory IS NULL"); XDB::rawExecute("INSERT IGNORE INTO register_marketing (uid, email, type) SELECT ap.uid, f.Mel_usage, 'ax' FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (p.ax_id = f.ax_id) - INNER JOIN account_profiles AS ap ON (ap.pid = p.pid AND FIND_IN_SET('owner', perms)) + INNER JOIN account_profiles AS ap ON (ap.pid = f.pid AND FIND_IN_SET('owner', perms)) LEFT JOIN emails AS e ON (e.uid = ap.uid AND e.flags = 'active') WHERE f.Mel_usage != '' AND f.Mel_usage NOT LIKE '%@polytechnique.edu' AND f.Mel_usage NOT LIKE '%@polytechnique.org' AND f.Mel_usage NOT LIKE '%@m4x.org' @@ -209,13 +176,13 @@ XDB::rawExecute("INSERT IGNORE INTO register_marketing (uid, email, type) // Retrieves different deathdates. XDB::rawExecute("UPDATE profile_merge_issues AS pm INNER JOIN profiles AS p ON (pm.pid = p.pid) - INNER JOIN fusionax_anciens AS f ON (f.ax_id = p.ax_id) + INNER JOIN fusionax_anciens AS f ON (f.pid = p.pid) SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'deathdate'), 'deathdate'), pm.deathdate_ax = f.Date_deces WHERE p.deathdate != f.Date_deces"); XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues, deathdate_ax) - SELECT p.pid, 'deathdate', f.Date_deces + SELECT f.pid, 'deathdate', f.Date_deces FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) + INNER JOIN profiles AS p ON (f.pid = p.pid) WHERE p.deathdate != f.Date_deces"); echo "Various informations inclusions finished.\n"; @@ -223,33 +190,29 @@ echo "Various informations inclusions finished.\n"; // We consider there is conflict if a profile has a phone in both databases. echo "Starts phones inclusions.\n"; XDB::rawExecute("UPDATE profile_merge_issues AS pm - INNER JOIN profiles AS p ON (pm.pid = p.pid) - INNER JOIN profile_phones AS pp ON (pp.pid = p.pid AND pp.link_type = 'user' AND pp.tel_id = 0) - INNER JOIN fusionax_anciens AS f ON (f.ax_id = p.ax_id) + INNER JOIN fusionax_anciens AS f ON (f.pid = pm.pid) + INNER JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0) SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'phone'), 'phone') WHERE f.tel_mobile != ''"); XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues) - SELECT p.pid, 'phone' + SELECT f.pid, 'phone' FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_phones AS pp ON (pp.pid = p.pid AND pp.link_type = 'user' AND pp.tel_id = 0) - WHERE f.tel_mobile != ''"); + INNER JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0) + WHERE f.tel_mobile != '' AND f.Mob_publiable = 1"); XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub) - SELECT p.pid, 'user', 0, 'mobile', f.tel_mobile, 'ax' + SELECT f.pid, 'user', 0, 'mobile', f.tel_mobile, 'ax' FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) WHERE NOT EXISTS (SELECT * FROM profile_phones AS pp - WHERE pp.pid = p.pid AND pp.link_type = 'user' AND pp.tel_id = 0) - AND f.tel_mobile != ''"); + WHERE pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0) + AND f.tel_mobile != '' AND f.Mob_publiable = 1"); XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub) - SELECT p.pid, 'user', MAX(pp.tel_id) + 1, 'mobile', f.tel_mobile, 'ax' + SELECT f.pid, 'user', MAX(pp.tel_id) + 1, 'mobile', f.tel_mobile, 'ax' FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_phones AS pp ON (pp.pid = p.pid AND pp.link_type = 'user') - WHERE f.tel_mobile != '' - GROUP BY p.pid"); + INNER JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'user') + WHERE f.tel_mobile != '' AND f.Mob_publiable = 1 + GROUP BY f.pid"); XDB::rawExecute('DROP TABLE IF EXISTS fusionax_anciens'); echo "Phones inclusions finished.\n"; @@ -258,57 +221,43 @@ echo "Starts addresses inclusions.\n"; XDB::rawExecute('ALTER TABLE profile_addresses ADD INDEX (text(20))'); XDB::rawExecute("DELETE f FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_addresses AS pa ON (pa.pid = p.pid AND pa.type = 'home') + INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home') WHERE pa.text = f.text"); // Deletes addresses of unknown type. XDB::rawExecute("DELETE FROM fusionax_adresses WHERE Type_adr != 'E' AND Type_adr != 'P'"); XDB::rawExecute("UPDATE profile_merge_issues AS pm - INNER JOIN profiles AS p ON (pm.pid = p.pid) - INNER JOIN profile_addresses AS pa ON (pa.pid = p.pid AND pa.type = 'home' AND pa.id = 0) - INNER JOIN fusionax_adresses AS f ON (f.ax_id = p.ax_id) + INNER JOIN fusionax_adresses AS f ON (f.pid = pm.pid) + INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND pa.id = 0) SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'address'), 'address') WHERE f.text IS NOT NULL"); XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues) - SELECT p.pid, 'address' + SELECT f.pid, 'address' FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_addresses AS pa ON (pa.pid = p.pid AND pa.type = 'home' AND pa.id = 0) + INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND pa.id = 0) WHERE f.text IS NOT NULL"); XDB::rawExecute("INSERT INTO profile_addresses (pid, type, id, pub, text) - SELECT p.pid, 'home', 0, 'ax', f.text - FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - WHERE NOT EXISTS (SELECT * - FROM profile_addresses AS pa - WHERE pa.pid = p.pid AND pa.type = 'home' AND pa.id = 0) - AND f.text IS NOT NULL"); -XDB::rawExecute("INSERT INTO profile_addresses (pid, type, id, pub, text) - SELECT p.pid, 'home', MAX(pa.id) + 1, 'ax', f.text + SELECT f.pid, 'home', IF(pa.id IS NULL , 0, MAX(pa.id) + 1), 'ax', f.text FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_addresses AS pa ON (pa.pid = p.pid AND pa.type = 'home') + LEFT JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home') WHERE f.text IS NOT NULL - GROUP BY p.pid"); + GROUP BY f.pid"); XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_id) - SELECT p.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fixed', f.tel, 'ax', pa.id + SELECT f.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fixed', f.tel, 'ax', pa.id FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_addresses AS pa ON (pa.pid = p.pid AND pa.type = 'home' AND f.text = pa.text) - LEFT JOIN profile_phones AS pp ON (pp.pid = p.pid AND pp.link_type = 'address' AND pp.link_id = pa.id) + INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND f.text = pa.text) + LEFT JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'address' AND pp.link_id = pa.id) WHERE f.tel != '' - GROUP BY p.pid"); + GROUP BY f.pid"); XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_id) - SELECT p.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fax', f.fax, 'ax', pa.id + SELECT f.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fax', f.fax, 'ax', pa.id FROM fusionax_adresses AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_addresses AS pa ON (pa.pid = p.pid AND pa.type = 'home' AND f.text = pa.text) - LEFT JOIN profile_phones AS pp ON (pp.pid = p.pid AND pp.link_type = 'address' AND pp.link_id = pa.id) + INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND f.text = pa.text) + LEFT JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'address' AND pp.link_id = pa.id) WHERE f.fax != '' - GROUP BY p.pid"); + GROUP BY f.pid"); XDB::rawExecute('ALTER TABLE profile_addresses DROP INDEX text'); XDB::rawExecute('DROP TABLE IF EXISTS fusionax_adresses'); echo "Addresses inclusions finished.\n"; @@ -323,46 +272,39 @@ XDB::rawExecute('UPDATE fusionax_formations AS f SET f.eduid = pe.id, f.degreeid = pd.id, f.fieldid = pf.id'); // Updates non complete educations. XDB::rawExecute("UPDATE profile_education AS e - INNER JOIN profiles AS p ON (e.pid = p.pid) - INNER JOIN fusionax_formations AS f ON (f.ax_id = p.ax_id) + INNER JOIN fusionax_formations AS f ON (f.pid = e.pid) INNER JOIN profile_education_degree_enum AS pd ON (e.degreeid = pd.id) INNER JOIN profile_education_degree_enum AS fd ON (f.degreeid = fd.id) SET e.eduid = f.eduid WHERE NOT FIND_IN_SET('primary', e.flags) AND e.eduid IS NULL AND pd.level = fd.level"); XDB::rawExecute("UPDATE profile_education AS e - INNER JOIN profiles AS p ON (e.pid = p.pid) - INNER JOIN fusionax_formations AS f ON (f.ax_id = p.ax_id) + INNER JOIN fusionax_formations AS f ON (f.pid = e.pid) SET e.degreeid = f.degreeid WHERE NOT FIND_IN_SET('primary', e.flags) AND e.degreeid IS NULL AND e.eduid = f.eduid"); // Deletes duplicates. XDB::rawExecute("DELETE f FROM fusionax_formations AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) INNER JOIN profile_education_degree_enum AS fd ON (fd.abbreviation = f.Intitule_diplome) - INNER JOIN profile_education AS e ON (e.pid = p.pid AND NOT FIND_IN_SET('primary', e.flags)) + INNER JOIN profile_education AS e ON (e.pid = f.pid AND NOT FIND_IN_SET('primary', e.flags)) INNER JOIN profile_education_degree_enum AS pd ON (pd.id = e.degreeid) WHERE f.eduid = e.eduid AND fd.level = pd.level"); // Updates merge_issues table. XDB::rawExecute("UPDATE profile_merge_issues AS pm - INNER JOIN profiles AS p ON (pm.pid = p.pid) - INNER JOIN fusionax_formations AS f ON (f.ax_id = p.ax_id) + INNER JOIN fusionax_formations AS f ON (f.pid = pm.pid) SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'education'), 'education')"); XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues) - SELECT p.pid, 'education' - FROM fusionax_formations AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)"); + SELECT pid, 'education' + FROM fusionax_formations"); $id = 0; $continue = 1; while ($continue > 0) { XDB::rawExecute("INSERT IGNORE INTO profile_education (id, pid, eduid, degreeid, fieldid, program) - SELECT $id, p.pid, f.eduid, f.degreeid, f.fieldid, f.Descr_formation - FROM fusionax_formations AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)"); + SELECT $id, pid, eduid, degreeid, fieldid, Descr_formation + FROM fusionax_formations"); XDB::rawExecute("DELETE f FROM fusionax_formations AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND pe.id = $id AND pe.eduid = f.eduid AND pe.degreeid = f.degreeid + INNER JOIN profile_education AS pe ON (pe.pid = f.pid AND pe.id = $id AND pe.eduid = f.eduid AND pe.degreeid = f.degreeid AND pe.fieldid = f.fieldid AND pe.program = f.Descr_formation)"); $continue = XDB::affectedRows(); } @@ -376,7 +318,6 @@ XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues) FROM profile_education WHERE eduid = '' OR eduid IS NULL OR degreeid = '' OR degreeid IS NULL"); - XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations'); echo "Educations inclusions finished.\n"; -- 2.1.4