[platal.git] / upgrade / 1.0.1 / merge.php

#!/usr/bin/php5
<?php
require_once 'connect.db.inc.php';

$globals->debug = 0; // Do not store backtraces.
XDB::startTransaction();

// Drops temporary tables and views used to checked if the merge was possible.
XDB::rawExecute('DROP VIEW IF EXISTS fusionax_xorg_anciens');
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 != '')
                         OR (profile_job_enum.name = fusionax_entreprises.Sigle AND profile_job_enum.name != '' AND fusionax_entreprises.Sigle != '')
                         OR (profile_job_enum.acronym = fusionax_entreprises.Sigle AND profile_job_enum.acronym != '' AND fusionax_entreprises.Sigle != '')
                         OR (profile_job_enum.acronym = fusionax_entreprises.Raison_sociale AND profile_job_enum.acronym != '' AND fusionax_entreprises.Raison_sociale != '')");
XDB::rawExecute("INSERT IGNORE INTO  profile_job_enum (name, acronym, AX_code)
                             SELECT  f.Raison_sociale, f.Sigle, f.Code_etab
                               FROM  fusionax_entreprises AS f
                              WHERE  f.Raison_sociale != ''
                                     AND NOT EXISTS (SELECT  *
                                                       FROM  profile_job_enum AS j
                                                      WHERE  f.Code_etab = j.AX_code)");

// Includes jobs we do not have into profile_job_enum.
// There are 3 cases:
//  - the job is incomplete (ie no compagny name) : this is an issue,
//  - 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 and avoid multiple joins.
XDB::rawExecute("DELETE  f
                   FROM  fusionax_activites   AS f
             INNER JOIN  profile_job_enum     AS pe ON (pe.AX_code = f.Code_etab)
             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 IGNORE INTO  profile_merge_issues (pid, issues)
                             SELECT  DISTINCT(f.pid), 'job'
                               FROM  fusionax_activites AS f
                              WHERE  f.jobid IS NULL OR 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, pid, jobid, IF(Annuaire = 1, 'ax', 'private'), description
                                   FROM  fusionax_activites");
    XDB::rawExecute("DELETE  f
                       FROM  fusionax_activites AS f
                 INNER JOIN  profile_job        AS pj ON (f.pid = pj.pid AND pj.id = $id AND pj.description = f.description)
                      WHERE  pj.jobid = f.jobid OR (pj.jobid IS NULL AND f.jobid IS NULL)");
    $continue = XDB::affectedRows();
    ++$id;
}
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', f.pid, pj.id, f.text
                               FROM  fusionax_adresses AS f
                         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', f.pid, f.tel
                               FROM  fusionax_adresses AS f
                         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', f.pid, f.fax
                               FROM  fusionax_adresses AS f
                         INNER JOIN  profile_job       AS pj ON (f.pid = pj.pid AND pj.jobid = f.jobid)
                              WHERE  f.Type_adr = 'E' AND f.fax != ''");
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, 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 = 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  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.pid = f.pid)
             INNER JOIN  geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite AND g.nationalityFR IS NOT NULL)
                    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.pid = f.pid)
             INNER JOIN  geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite AND g.nationalityFR IS NOT NULL)
                    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.pid = f.pid)
             INNER JOIN  geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite AND g.nationalityFR IS NOT NULL)
                    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');
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  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  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)')
                    SET  c.original_corpsid = a.id
                  WHERE  e.name = 'Ancien élève étranger'");
XDB::rawExecute("UPDATE  profile_corps_enum
                    SET  name = 'Aucun'
                  WHERE  name = 'Aucun (anc. démissionnaire)'");
XDB::rawExecute("DELETE FROM  profile_corps_enum
                       WHERE  name = 'Ancien élève étranger'");

// Updates email_directory.
XDB::rawExecute("UPDATE  profiles         AS p
             INNER JOIN  fusionax_anciens AS f ON (p.pid = f.pid)
                    SET  p.email_directory = f.Mel_usage
                  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  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'
                                     AND f.Mel_usage NOT LIKE '%@melix.%' AND e.email IS NULL");

// 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.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  f.pid, 'deathdate', f.Date_deces
                               FROM  fusionax_anciens AS f
                         INNER JOIN  profiles         AS p ON (f.pid = p.pid)
                              WHERE  p.deathdate != f.Date_deces");
echo "Various informations inclusions finished.\n";

// Updates phone.
// 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  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  f.pid, 'phone'
                               FROM  fusionax_anciens AS f
                         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  f.pid, 'user', 0, 'mobile', f.tel_mobile, 'ax'
                        FROM  fusionax_anciens AS f
                       WHERE  NOT EXISTS (SELECT  *
                                            FROM  profile_phones AS pp
                                           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  f.pid, 'user', MAX(pp.tel_id) + 1, 'mobile', f.tel_mobile, 'ax'
                        FROM  fusionax_anciens AS f
                  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";

// Retrieves addresses from AX database (one address per preofile maximum).
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  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  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  f.pid, 'address'
                               FROM  fusionax_adresses AS f
                         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  f.pid, 'home', IF(pa.id IS NULL , 0, MAX(pa.id) + 1), 'ax', f.text
                        FROM  fusionax_adresses AS f
                   LEFT JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home')
                       WHERE  f.text IS NOT NULL
                    GROUP BY  f.pid");
XDB::rawExecute("INSERT INTO  profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_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  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  f.pid");
XDB::rawExecute("INSERT INTO  profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_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  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  f.pid");
XDB::rawExecute('ALTER TABLE profile_addresses DROP INDEX text');
XDB::rawExecute('DROP TABLE IF EXISTS fusionax_adresses');
echo "Addresses inclusions finished.\n";

// Retrieves education from AX database. This is the hardest part since AX only kept education as an unformated string.
echo "Starts educations inclusions.\n";
// Deletes empty educations.
XDB::rawExecute("DELETE FROM  fusionax_formations
                       WHERE  Intitule_formation = '' AND Intitule_diplome = '' AND Descr_formation = ''");
// Insert ids into fusionax_formations to prevent many joins.
XDB::rawExecute('ALTER TABLE profile_education_enum ADD INDEX (name(60))');
XDB::rawExecute('ALTER TABLE profile_education_degree_enum ADD INDEX (abbreviation(60))');
XDB::rawExecute('UPDATE  fusionax_formations           AS f
              LEFT JOIN  profile_education_enum        AS pe ON (pe.name = f.Intitule_formation)
              LEFT JOIN  profile_education_degree_enum AS pd ON (pd.abbreviation = f.Intitule_diplome)
              LEFT JOIN  profile_education_field_enum  AS pf ON (pf.field = f.Descr_formation)
                    SET  f.eduid = pe.id, f.degreeid = pd.id, f.fieldid = pf.id');
XDB::rawExecute('ALTER TABLE profile_education_enum DROP INDEX name');
XDB::rawExecute('ALTER TABLE profile_education_degree_enum DROP INDEX abbreviation');
// Updates non complete educations.
XDB::rawExecute("UPDATE  profile_education             AS e
             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  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  profile_education_degree_enum AS fd ON (fd.abbreviation = f.Intitule_diplome)
             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  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  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, pid, eduid, degreeid, fieldid, Descr_formation
                                   FROM  fusionax_formations");
    XDB::rawExecute("DELETE  f
                       FROM  fusionax_formations AS f
                 INNER JOIN  profile_education   AS pe ON (pe.pid = f.pid AND pe.id = $id)
                      WHERE  (pe.eduid = f.eduid OR (pe.eduid IS NULL AND f.eduid IS NULL))
                             AND (pe.degreeid = f.degreeid OR (pe.degreeid IS NULL AND f.degreeid IS NULL))
                             AND (pe.fieldid = f.fieldid OR (pe.fieldid IS NULL AND f.fieldid IS NULL))
                             AND (pe.program = f.Descr_formation OR (pe.program IS NULL AND f.Descr_formation IS NULL))");
    $continue = XDB::affectedRows();
    ++$id;
}
// Updates merge_issues table (eduid and degreeid should never be empty).
XDB::rawExecute("UPDATE  profile_merge_issues AS pm
             INNER JOIN  profile_education    AS pe ON (pe.pid = pm.pid)
                    SET  pm.issues = CONCAT(pm.issues, ',', 'education')
                  WHERE  NOT FIND_IN_SET('education', pm.issues) AND (pe.eduid = '' OR pe.eduid IS NULL OR pe.degreeid = '' OR pe.degreeid IS NULL)");
XDB::rawExecute("INSERT IGNORE INTO  profile_merge_issues (pid, issues)
                             SELECT  pid, 'education'
                               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";

echo "All inclusions are done.\n";

XDB::commit();

/* vim:set et sw=4 sts=4 ts=4: */
?>
Commit	Line	Data
6b9e0135 SJ	1	#!/usr/bin/php5
	2	<?php
	3	require_once 'connect.db.inc.php';
6b9e0135 SJ	4
6b9e0135 SJ	5	$globals->debug = 0; // Do not store backtraces.
f8c2bf68	6	XDB::startTransaction();
6b9e0135	7
f8c2bf68	8	// Drops temporary tables and views used to checked if the merge was possible.
6b9e0135 SJ	9	XDB::rawExecute('DROP VIEW IF EXISTS fusionax_xorg_anciens');
	10	XDB::rawExecute('DROP VIEW IF EXISTS fusionax_deceased');
	11	XDB::rawExecute('DROP VIEW IF EXISTS fusionax_promo');
	12	XDB::rawExecute('DROP TABLE IF EXISTS fusionax_import');
	13
a48b23c3 SJ	14	// Fills pid fields in all table, to avoid to many joins.
	15	foreach (array('fusionax_activites', 'fusionax_adresses', 'fusionax_anciens', 'fusionax_formations') as $table) {
	16	XDB::rawExecute("UPDATE $table AS f
	17	INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
	18	SET f.pid = p.pid");
	19	XDB::rawExecute("DELETE FROM $table WHERE pid IS NULL");
	20	}
	21
f8c2bf68 SJ	22	// Includes entreprises we do not have into profile_job_enum.
	23	// We first retrieve AX code, then add missing compagnies.
	24	echo "Starts jobs inclusions.\n";
a48b23c3 SJ	25	XDB::rawExecute('ALTER TABLE profile_job_enum ADD INDEX (name(20))');
	26	XDB::rawExecute('ALTER TABLE profile_job_enum ADD INDEX (acronym(20))');
	27	XDB::rawExecute('ALTER TABLE profile_job_enum ADD INDEX (AX_code)');
f8c2bf68 SJ	28	XDB::rawExecute("UPDATE profile_job_enum, fusionax_entreprises
	29	SET profile_job_enum.AX_code = fusionax_entreprises.Code_etab
	30	WHERE (profile_job_enum.name = fusionax_entreprises.Raison_sociale AND profile_job_enum.name != '' AND fusionax_entreprises.Raison_sociale != '')
	31	OR (profile_job_enum.name = fusionax_entreprises.Sigle AND profile_job_enum.name != '' AND fusionax_entreprises.Sigle != '')
	32	OR (profile_job_enum.acronym = fusionax_entreprises.Sigle AND profile_job_enum.acronym != '' AND fusionax_entreprises.Sigle != '')
	33	OR (profile_job_enum.acronym = fusionax_entreprises.Raison_sociale AND profile_job_enum.acronym != '' AND fusionax_entreprises.Raison_sociale != '')");
	34	XDB::rawExecute("INSERT IGNORE INTO profile_job_enum (name, acronym, AX_code)
	35	SELECT f.Raison_sociale, f.Sigle, f.Code_etab
	36	FROM fusionax_entreprises AS f
	37	WHERE f.Raison_sociale != ''
	38	AND NOT EXISTS (SELECT *
	39	FROM profile_job_enum AS j
	40	WHERE f.Code_etab = j.AX_code)");
6b9e0135	41
f8c2bf68 SJ	42	// Includes jobs we do not have into profile_job_enum.
	43	// There are 3 cases:
	44	// - the job is incomplete (ie no compagny name) : this is an issue,
	45	// - the job is complete but the profile already has a job or more : this is an issue,
	46	// - the job is complete and the the profile has no previous job : there is no issue.
a48b23c3	47	// We delete obvious duplicates and avoid multiple joins.
f8c2bf68 SJ	48	XDB::rawExecute("DELETE f
f8c2bf68 SJ	49	FROM fusionax_activites AS f
f8c2bf68	50	INNER JOIN profile_job_enum AS pe ON (pe.AX_code = f.Code_etab)
a48b23c3 SJ	51	INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = pe.id)");
	52	foreach (array('fusionax_activites', 'fusionax_adresses') as $table) {
	53	XDB::rawExecute("UPDATE $table AS f
	54	INNER JOIN profile_job_enum AS pe ON (f.Code_etab = pe.AX_code)
	55	SET f.jobid = pe.id");
	56	}
	57	XDB::rawExecute('ALTER TABLE profile_job_enum DROP INDEX name');
	58	XDB::rawExecute('ALTER TABLE profile_job_enum DROP INDEX acronym');
	59	XDB::rawExecute('ALTER TABLE profile_job_enum DROP INDEX AX_code');
	60	XDB::rawExecute('DROP TABLE IF EXISTS fusionax_entreprises');
f8c2bf68 SJ	61
f8c2bf68 SJ	62	// We first update the issues table.
f27cbbb3 SJ	63	XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues)
	64	SELECT DISTINCT(f.pid), 'job'
	65	FROM fusionax_activites AS f
	66	WHERE f.jobid IS NULL OR EXISTS (SELECT *
	67	FROM profile_job AS pj
	68	WHERE pj.pid = f.pid)");
a48b23c3	69	// We then add new jobs.
f8c2bf68 SJ	70	$id = 0;
	71	$continue = 1;
	72	while ($continue > 0) {
	73	XDB::rawExecute("INSERT IGNORE INTO profile_job (id, pid, jobid, pub, description)
a48b23c3 SJ	74	SELECT $id, pid, jobid, IF(Annuaire = 1, 'ax', 'private'), description
a48b23c3 SJ	75	FROM fusionax_activites");
f8c2bf68	76	XDB::rawExecute("DELETE f
a48b23c3	77	FROM fusionax_activites AS f
f27cbbb3 SJ	78	INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.id = $id AND pj.description = f.description)
f27cbbb3 SJ	79	WHERE pj.jobid = f.jobid OR (pj.jobid IS NULL AND f.jobid IS NULL)");
f8c2bf68	80	$continue = XDB::affectedRows();
247b7728	81	++$id;
6b9e0135	82	}
a48b23c3	83	XDB::rawExecute('DROP TABLE IF EXISTS fusionax_activites');
f8c2bf68 SJ	84	// We also have to add related phones and addresses.
f8c2bf68 SJ	85	XDB::rawExecute("INSERT IGNORE INTO profile_addresses (type, pid, id, text)
a48b23c3	86	SELECT 'job', f.pid, pj.id, f.text
f8c2bf68	87	FROM fusionax_adresses AS f
a48b23c3	88	INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = f.jobid)
f8c2bf68 SJ	89	WHERE f.Type_adr = 'E' AND f.text IS NOT NULL");
f8c2bf68 SJ	90	XDB::rawExecute("INSERT IGNORE INTO profile_phones (link_type, link_id, tel_id, tel_type, pid, display_tel)
a48b23c3	91	SELECT 'pro', pj.id, 0, 'fixed', f.pid, f.tel
f8c2bf68	92	FROM fusionax_adresses AS f
a48b23c3	93	INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = f.jobid)
f8c2bf68 SJ	94	WHERE f.Type_adr = 'E' AND f.tel != ''");
f8c2bf68 SJ	95	XDB::rawExecute("INSERT IGNORE INTO profile_phones (link_type, link_id, tel_id, tel_type, pid, display_tel)
a48b23c3	96	SELECT 'pro', pj.id, IF(f.tel = '', 0, 1), 'fax', f.pid, f.fax
f8c2bf68	97	FROM fusionax_adresses AS f
a48b23c3	98	INNER JOIN profile_job AS pj ON (f.pid = pj.pid AND pj.jobid = f.jobid)
f8c2bf68	99	WHERE f.Type_adr = 'E' AND f.fax != ''");
a48b23c3	100	XDB::rawExecute("DELETE FROM fusionax_adresses WHERE Type_adr = 'E'");
f8c2bf68	101	echo "Jobs inclusions finished.\n";
6b9e0135	102
f8c2bf68 SJ	103	// Retrieves information from fusionax_anciens: promo, nationality, corps, email, phone, deathdate.
	104	// Updates uncertain promotions, but when we are we are right.
	105	echo "Starts various informations inclusions.\n";
a48b23c3	106	XDB::rawExecute("UPDATE profile_merge_issues AS pm, fusionax_anciens AS f, profile_display AS pd, profile_education AS pe
f8c2bf68	107	SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'promo'), 'promo'), pm.entry_year_ax = f.promotion_etude
a48b23c3 SJ	108	WHERE pm.pid = f.pid AND f.pid NOT IN (18399,21099,40616)
a48b23c3 SJ	109	AND pd.pid = f.pid AND pe.pid = f.pid AND FIND_IN_SET('primary', pe.flags)
f8c2bf68 SJ	110	AND pd.promo != CONCAT('X', f.promotion_etude)
f8c2bf68 SJ	111	AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)");
a48b23c3	112
f8c2bf68	113	XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues, entry_year_ax)
a48b23c3 SJ	114	SELECT f.pid, 'promo', f.promotion_etude
	115	FROM fusionax_anciens AS f
	116	INNER JOIN profile_display AS pd ON (f.pid = pd.pid)
	117	INNER JOIN profile_education AS pe ON (f.pid = pe.pid)
	118	WHERE pd.promo != CONCAT('X', f.promotion_etude) AND f.pid NOT IN (18399,21099,40616)
f8c2bf68	119	AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)");
6b9e0135	120
f8c2bf68 SJ	121	// Updates nationality.
f8c2bf68 SJ	122	XDB::rawExecute('ALTER TABLE geoloc_countries ADD INDEX (licensePlate)');
6b9e0135	123	XDB::rawExecute('UPDATE profiles AS p
a48b23c3	124	INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid)
d187b7cc	125	INNER JOIN geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite AND g.nationalityFR IS NOT NULL)
f8c2bf68 SJ	126	SET p.nationality1 = g.iso_3166_1_a2
f8c2bf68 SJ	127	WHERE p.nationality1 IS NULL');
6b9e0135	128	XDB::rawExecute('UPDATE profiles AS p
a48b23c3	129	INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid)
d187b7cc	130	INNER JOIN geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite AND g.nationalityFR IS NOT NULL)
f8c2bf68 SJ	131	SET p.nationality2 = g.iso_3166_1_a2
f8c2bf68 SJ	132	WHERE p.nationality1 != g.iso_3166_1_a2 AND p.nationality2 IS NULL');
6b9e0135	133	XDB::rawExecute('UPDATE profiles AS p
a48b23c3	134	INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid)
d187b7cc	135	INNER JOIN geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite AND g.nationalityFR IS NOT NULL)
f8c2bf68 SJ	136	SET p.nationality3 = g.iso_3166_1_a2
	137	WHERE p.nationality1 != g.iso_3166_1_a2 AND p.nationality2 != g.iso_3166_1_a2 AND p.nationality3 IS NULL');
	138	XDB::rawExecute('ALTER TABLE geoloc_countries DROP INDEX licensePlate');
6b9e0135	139
f8c2bf68 SJ	140	// Updates corps.
f8c2bf68 SJ	141	XDB::rawExecute("INSERT IGNORE INTO profile_corps (pid, original_corpsid, current_corpsid, rankid, corps_pub)
a48b23c3 SJ	142	SELECT f.pid, c.id, c.id, r.id, 'ax'
a48b23c3 SJ	143	FROM fusionax_anciens AS f
f8c2bf68 SJ	144	INNER JOIN profile_corps_enum AS c ON (f.corps_sortie = c.abbreviation)
	145	INNER JOIN profile_corps_rank_enum AS r ON (f.grade = r.abbreviation)
	146	WHERE NOT EXISTS (SELECT *
	147	FROM profile_corps AS pc
a48b23c3	148	WHERE f.pid = pc.pid AND pc.original_corpsid != 1)");
6b9e0135 SJ	149	XDB::rawExecute("UPDATE profile_corps AS c
	150	INNER JOIN profile_corps_enum AS e ON (c.original_corpsid = e.id)
	151	LEFT JOIN profile_corps_enum AS a ON (a.name = 'Aucun (anc. démissionnaire)')
	152	SET c.original_corpsid = a.id
	153	WHERE e.name = 'Ancien élève étranger'");
	154	XDB::rawExecute("UPDATE profile_corps_enum
	155	SET name = 'Aucun'
	156	WHERE name = 'Aucun (anc. démissionnaire)'");
	157	XDB::rawExecute("DELETE FROM profile_corps_enum
	158	WHERE name = 'Ancien élève étranger'");
	159
f8c2bf68 SJ	160	// Updates email_directory.
f8c2bf68 SJ	161	XDB::rawExecute("UPDATE profiles AS p
a48b23c3	162	INNER JOIN fusionax_anciens AS f ON (p.pid = f.pid)
f8c2bf68	163	SET p.email_directory = f.Mel_usage
a48b23c3	164	WHERE f.Mel_publiable = 1 AND f.Mel_usage != '' AND p.email_directory IS NULL");
6b9e0135 SJ	165	XDB::rawExecute("INSERT IGNORE INTO register_marketing (uid, email, type)
	166	SELECT ap.uid, f.Mel_usage, 'ax'
	167	FROM fusionax_anciens AS f
a48b23c3	168	INNER JOIN account_profiles AS ap ON (ap.pid = f.pid AND FIND_IN_SET('owner', perms))
6b9e0135 SJ	169	LEFT JOIN emails AS e ON (e.uid = ap.uid AND e.flags = 'active')
	170	WHERE f.Mel_usage != '' AND f.Mel_usage NOT LIKE '%@polytechnique.edu'
	171	AND f.Mel_usage NOT LIKE '%@polytechnique.org' AND f.Mel_usage NOT LIKE '%@m4x.org'
	172	AND f.Mel_usage NOT LIKE '%@melix.%' AND e.email IS NULL");
	173
f8c2bf68 SJ	174	// Retrieves different deathdates.
	175	XDB::rawExecute("UPDATE profile_merge_issues AS pm
	176	INNER JOIN profiles AS p ON (pm.pid = p.pid)
a48b23c3	177	INNER JOIN fusionax_anciens AS f ON (f.pid = p.pid)
f8c2bf68	178	SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'deathdate'), 'deathdate'), pm.deathdate_ax = f.Date_deces
6b9e0135	179	WHERE p.deathdate != f.Date_deces");
f8c2bf68	180	XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues, deathdate_ax)
a48b23c3	181	SELECT f.pid, 'deathdate', f.Date_deces
f8c2bf68	182	FROM fusionax_anciens AS f
a48b23c3	183	INNER JOIN profiles AS p ON (f.pid = p.pid)
f8c2bf68 SJ	184	WHERE p.deathdate != f.Date_deces");
	185	echo "Various informations inclusions finished.\n";
	186
	187	// Updates phone.
	188	// We consider there is conflict if a profile has a phone in both databases.
	189	echo "Starts phones inclusions.\n";
	190	XDB::rawExecute("UPDATE profile_merge_issues AS pm
a48b23c3 SJ	191	INNER JOIN fusionax_anciens AS f ON (f.pid = pm.pid)
a48b23c3 SJ	192	INNER JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0)
f8c2bf68 SJ	193	SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'phone'), 'phone')
	194	WHERE f.tel_mobile != ''");
	195	XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues)
a48b23c3	196	SELECT f.pid, 'phone'
f8c2bf68	197	FROM fusionax_anciens AS f
a48b23c3 SJ	198	INNER JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0)
a48b23c3 SJ	199	WHERE f.tel_mobile != '' AND f.Mob_publiable = 1");
f8c2bf68 SJ	200
f8c2bf68 SJ	201	XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub)
a48b23c3	202	SELECT f.pid, 'user', 0, 'mobile', f.tel_mobile, 'ax'
f8c2bf68	203	FROM fusionax_anciens AS f
f8c2bf68 SJ	204	WHERE NOT EXISTS (SELECT *
f8c2bf68 SJ	205	FROM profile_phones AS pp
a48b23c3 SJ	206	WHERE pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0)
a48b23c3 SJ	207	AND f.tel_mobile != '' AND f.Mob_publiable = 1");
f8c2bf68	208	XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub)
a48b23c3	209	SELECT f.pid, 'user', MAX(pp.tel_id) + 1, 'mobile', f.tel_mobile, 'ax'
f8c2bf68	210	FROM fusionax_anciens AS f
a48b23c3 SJ	211	INNER JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'user')
	212	WHERE f.tel_mobile != '' AND f.Mob_publiable = 1
	213	GROUP BY f.pid");
6b9e0135	214	XDB::rawExecute('DROP TABLE IF EXISTS fusionax_anciens');
f8c2bf68	215	echo "Phones inclusions finished.\n";
6b9e0135	216
f8c2bf68 SJ	217	// Retrieves addresses from AX database (one address per preofile maximum).
	218	echo "Starts addresses inclusions.\n";
	219	XDB::rawExecute('ALTER TABLE profile_addresses ADD INDEX (text(20))');
	220	XDB::rawExecute("DELETE f
	221	FROM fusionax_adresses AS f
a48b23c3	222	INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home')
f8c2bf68	223	WHERE pa.text = f.text");
325af12b SJ	224	// Deletes addresses of unknown type.
	225	XDB::rawExecute("DELETE FROM fusionax_adresses
	226	WHERE Type_adr != 'E' AND Type_adr != 'P'");
	227
f8c2bf68	228	XDB::rawExecute("UPDATE profile_merge_issues AS pm
a48b23c3 SJ	229	INNER JOIN fusionax_adresses AS f ON (f.pid = pm.pid)
a48b23c3 SJ	230	INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND pa.id = 0)
f8c2bf68 SJ	231	SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'address'), 'address')
	232	WHERE f.text IS NOT NULL");
	233	XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues)
a48b23c3	234	SELECT f.pid, 'address'
f8c2bf68	235	FROM fusionax_adresses AS f
a48b23c3	236	INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND pa.id = 0)
f8c2bf68 SJ	237	WHERE f.text IS NOT NULL");
	238
	239	XDB::rawExecute("INSERT INTO profile_addresses (pid, type, id, pub, text)
a48b23c3	240	SELECT f.pid, 'home', IF(pa.id IS NULL , 0, MAX(pa.id) + 1), 'ax', f.text
f8c2bf68	241	FROM fusionax_adresses AS f
a48b23c3	242	LEFT JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home')
f8c2bf68	243	WHERE f.text IS NOT NULL
a48b23c3	244	GROUP BY f.pid");
f8c2bf68	245	XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_id)
a48b23c3	246	SELECT f.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fixed', f.tel, 'ax', pa.id
f8c2bf68	247	FROM fusionax_adresses AS f
a48b23c3 SJ	248	INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND f.text = pa.text)
a48b23c3 SJ	249	LEFT JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'address' AND pp.link_id = pa.id)
325af12b	250	WHERE f.tel != ''
a48b23c3	251	GROUP BY f.pid");
f8c2bf68	252	XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_id)
a48b23c3	253	SELECT f.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fax', f.fax, 'ax', pa.id
f8c2bf68	254	FROM fusionax_adresses AS f
a48b23c3 SJ	255	INNER JOIN profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND f.text = pa.text)
a48b23c3 SJ	256	LEFT JOIN profile_phones AS pp ON (pp.pid = f.pid AND pp.link_type = 'address' AND pp.link_id = pa.id)
325af12b	257	WHERE f.fax != ''
a48b23c3	258	GROUP BY f.pid");
f8c2bf68	259	XDB::rawExecute('ALTER TABLE profile_addresses DROP INDEX text');
6b9e0135	260	XDB::rawExecute('DROP TABLE IF EXISTS fusionax_adresses');
f8c2bf68	261	echo "Addresses inclusions finished.\n";
6b9e0135	262
f8c2bf68 SJ	263	// Retrieves education from AX database. This is the hardest part since AX only kept education as an unformated string.
f8c2bf68 SJ	264	echo "Starts educations inclusions.\n";
323813b3 SJ	265	// Deletes empty educations.
	266	XDB::rawExecute("DELETE FROM fusionax_formations
	267	WHERE Intitule_formation = '' AND Intitule_diplome = '' AND Descr_formation = ''");
325af12b	268	// Insert ids into fusionax_formations to prevent many joins.
1dd21852 SJ	269	XDB::rawExecute('ALTER TABLE profile_education_enum ADD INDEX (name(60))');
1dd21852 SJ	270	XDB::rawExecute('ALTER TABLE profile_education_degree_enum ADD INDEX (abbreviation(60))');
325af12b SJ	271	XDB::rawExecute('UPDATE fusionax_formations AS f
	272	LEFT JOIN profile_education_enum AS pe ON (pe.name = f.Intitule_formation)
	273	LEFT JOIN profile_education_degree_enum AS pd ON (pd.abbreviation = f.Intitule_diplome)
	274	LEFT JOIN profile_education_field_enum AS pf ON (pf.field = f.Descr_formation)
	275	SET f.eduid = pe.id, f.degreeid = pd.id, f.fieldid = pf.id');
1dd21852 SJ	276	XDB::rawExecute('ALTER TABLE profile_education_enum DROP INDEX name');
1dd21852 SJ	277	XDB::rawExecute('ALTER TABLE profile_education_degree_enum DROP INDEX abbreviation');
325af12b SJ	278	// Updates non complete educations.
325af12b SJ	279	XDB::rawExecute("UPDATE profile_education AS e
a48b23c3	280	INNER JOIN fusionax_formations AS f ON (f.pid = e.pid)
325af12b SJ	281	INNER JOIN profile_education_degree_enum AS pd ON (e.degreeid = pd.id)
	282	INNER JOIN profile_education_degree_enum AS fd ON (f.degreeid = fd.id)
	283	SET e.eduid = f.eduid
	284	WHERE NOT FIND_IN_SET('primary', e.flags) AND e.eduid IS NULL AND pd.level = fd.level");
	285	XDB::rawExecute("UPDATE profile_education AS e
a48b23c3	286	INNER JOIN fusionax_formations AS f ON (f.pid = e.pid)
325af12b SJ	287	SET e.degreeid = f.degreeid
	288	WHERE NOT FIND_IN_SET('primary', e.flags) AND e.degreeid IS NULL AND e.eduid = f.eduid");
	289	// Deletes duplicates.
	290	XDB::rawExecute("DELETE f
	291	FROM fusionax_formations AS f
325af12b	292	INNER JOIN profile_education_degree_enum AS fd ON (fd.abbreviation = f.Intitule_diplome)
a48b23c3	293	INNER JOIN profile_education AS e ON (e.pid = f.pid AND NOT FIND_IN_SET('primary', e.flags))
325af12b SJ	294	INNER JOIN profile_education_degree_enum AS pd ON (pd.id = e.degreeid)
	295	WHERE f.eduid = e.eduid AND fd.level = pd.level");
	296	// Updates merge_issues table.
	297	XDB::rawExecute("UPDATE profile_merge_issues AS pm
a48b23c3	298	INNER JOIN fusionax_formations AS f ON (f.pid = pm.pid)
325af12b SJ	299	SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'education'), 'education')");
325af12b SJ	300	XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues)
a48b23c3 SJ	301	SELECT pid, 'education'
a48b23c3 SJ	302	FROM fusionax_formations");
325af12b SJ	303
	304	$id = 0;
	305	$continue = 1;
	306	while ($continue > 0) {
	307	XDB::rawExecute("INSERT IGNORE INTO profile_education (id, pid, eduid, degreeid, fieldid, program)
a48b23c3 SJ	308	SELECT $id, pid, eduid, degreeid, fieldid, Descr_formation
a48b23c3 SJ	309	FROM fusionax_formations");
325af12b SJ	310	XDB::rawExecute("DELETE f
325af12b SJ	311	FROM fusionax_formations AS f
1dd21852 SJ	312	INNER JOIN profile_education AS pe ON (pe.pid = f.pid AND pe.id = $id)
	313	WHERE (pe.eduid = f.eduid OR (pe.eduid IS NULL AND f.eduid IS NULL))
	314	AND (pe.degreeid = f.degreeid OR (pe.degreeid IS NULL AND f.degreeid IS NULL))
	315	AND (pe.fieldid = f.fieldid OR (pe.fieldid IS NULL AND f.fieldid IS NULL))
	316	AND (pe.program = f.Descr_formation OR (pe.program IS NULL AND f.Descr_formation IS NULL))");
325af12b	317	$continue = XDB::affectedRows();
247b7728	318	++$id;
6b9e0135	319	}
325af12b SJ	320	// Updates merge_issues table (eduid and degreeid should never be empty).
	321	XDB::rawExecute("UPDATE profile_merge_issues AS pm
	322	INNER JOIN profile_education AS pe ON (pe.pid = pm.pid)
	323	SET pm.issues = CONCAT(pm.issues, ',', 'education')
	324	WHERE NOT FIND_IN_SET('education', pm.issues) AND (pe.eduid = '' OR pe.eduid IS NULL OR pe.degreeid = '' OR pe.degreeid IS NULL)");
	325	XDB::rawExecute("INSERT IGNORE INTO profile_merge_issues (pid, issues)
	326	SELECT pid, 'education'
	327	FROM profile_education
	328	WHERE eduid = '' OR eduid IS NULL OR degreeid = '' OR degreeid IS NULL");
	329
6b9e0135	330	XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations');
f8c2bf68 SJ	331	echo "Educations inclusions finished.\n";
	332
	333	echo "All inclusions are done.\n";
	334
	335	XDB::commit();
6b9e0135 SJ	336
	337	/* vim:set et sw=4 sts=4 ts=4: */
	338	?>