-// {{{ First, we need to build a few lists.
-$degree_list = $level_list = $university_list = $field_list = array();
-$res = XDB::rawIterator('SELECT id, abbreviation AS name
- FROM profile_education_degree_enum
- ORDER BY id');
-while ($res = $item->next()) {
- $degree_list[$item[1]] = $item[0];
-}
-$res = XDB::rawIterator('SELECT level, abbreviation AS name
- FROM profile_education_degree_enum
- ORDER BY id');
-while ($res = $item->next()) {
- $level_list[$item[1]] = $item[0];
-}
-$res = XDB::rawIterator("SELECT id, IF(abbreviation = '', name, abbreviation) AS name
- FROM profile_education_enum
- ORDER BY id");
-while ($res = $item->next()) {
- $university_list[$item[1]] = $item[0];
-}
-$res = XDB::rawIterator('SELECT id, field AS name
- FROM profile_education_field_enum
- ORDER BY id');
-while ($res = $item->next()) {
- $field_list[$item[1]] = $item[0];
-}
-// }}}
-$edusXorg = XDB::rawIterator("SELECT p.pid, d.abbreviation AS degree, IF(e.abbreviation = '', e.name, e.abbreviation) AS university,
- pe.program, pe.id AS no
- FROM profile_education AS pe
- INNER JOIN profiles AS p ON (pe.pid = p.pid)
- INNER JOIN fusionax_formations AS f ON (f.ax_id = p.ax_id)
- INNER JOIN profile_education_enum AS e ON (pe.eduid = e.id)
- INNER JOIN profile_education_degree_enum AS d ON (pe.degreeid = d.id)
- WHERE NOT FIND_IN_SET('primary', pe.flags)
- ORDER BY p.pid, pe.id");
-$edusAX = XDB::rawIterator("SELECT p.pid, f.Intitule_diplome AS degree, f.Intitule_formation AS university, f.Descr_formation AS program
- FROM fusionax_formations AS f
- INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
- ORDER BY p.pid");
-$eduXorg = $edusXorg->next();
-while ($eduAX = $edusAX->next()) {
- $id = 0;
- while ($eduXorg['pid'] == $eduAX['pid']) {
- if ($eduXorg['university'] == $eduAX['university'] && $level_list[$eduXorg['degree']] == $level_list[$eduAX['degree']]) {
- $already = true;
- }
- ++$id;
- $eduXorg = $edusXorg->next();
- }
- if (isset($field_list[$eduAX['program']])) {
- $fieldid = $field_list[$eduAX['program']];
- $program = null;
- } else {
- $fieldid = null;
- $program = $eduAX['program'];
- }
- if (!$already) {
- XDB::execute('INSERT INTO profile_education (pid, degreeid, eduid, program, fieldid, id)
- VALUES {?}, {?}, {?}, {?}, {?}, {?}',
- $eduAX['pid'], $degree_list[$eduAX['degree']], $university_list[$eduAX['university']],
- $program, $fieldid, $id);
- if ($id > 0) {
- XDB::execute("UPDATE profile_merge_issues
- SET issues = IF(issues, CONCAT(issues, ',', 'education'), 'education')
- WHERE pid = {?}", $addressAX->pid);
- }
- }
+// Updates master and doctorate educational fields.
+XDB::rawExecute("UPDATE profile_education AS e
+ INNER JOIN fusionax_formations_md AS f ON (f.pid = e.pid AND FIND_IN_SET('primary', e.flags))
+ SET e.program = f.field, e.fieldid = f.fieldid");
+XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations_md');
+
+// 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;