upgrade/1.0.1/merge.php

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