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') 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('UPDATE  profile_corps      AS pc
 150              INNER JOIN  fusionax_anciens   AS f ON (f.pid = pc.pid)
 151              INNER JOIN  profile_corps_enum AS c ON (f.corps_sortie = c.abbreviation)
 152                     SET  pc.original_corpsid = c.id');
 153 XDB::rawExecute("INSERT IGNORE INTO  profile_corps (pid, original_corpsid, current_corpsid, rankid, corps_pub)
 154                              SELECT  f.pid, c.id, c.id, r.id, 'ax'
 155                                FROM  fusionax_anciens        AS f
 156                          INNER JOIN  profile_corps_enum      AS c ON (f.corps_sortie = c.abbreviation)
 157                          INNER JOIN  profile_corps_rank_enum AS r ON (f.grade = r.abbreviation)
 158                               WHERE  NOT EXISTS (SELECT  *
 159                                                    FROM  profile_corps AS pc
 160                                                   WHERE  f.pid = pc.pid AND pc.original_corpsid != 1)");
 161 XDB::rawExecute("UPDATE  profile_corps      AS c
 162              INNER JOIN  profile_corps_enum AS e ON (c.original_corpsid = e.id)
 163               LEFT JOIN  profile_corps_enum AS a ON (a.name = 'Aucun (anc. démissionnaire)')
 164                     SET  c.original_corpsid = a.id
 165                   WHERE  e.name = 'Ancien élève étranger'");
 166 XDB::rawExecute("UPDATE  profile_corps_enum
 167                     SET  name = 'Aucun'
 168                   WHERE  name = 'Aucun (anc. démissionnaire)'");
 169 XDB::rawExecute("DELETE FROM  profile_corps_enum
 170                        WHERE  name = 'Ancien élève étranger'");
 171
 172 // Updates email_directory.
 173 XDB::rawExecute("UPDATE  profiles         AS p
 174              INNER JOIN  fusionax_anciens AS f ON (p.pid = f.pid)
 175                     SET  p.email_directory = f.Mel_usage
 176                   WHERE  f.Mel_publiable = 1 AND f.Mel_usage != '' AND p.email_directory IS NULL");
 177 XDB::rawExecute("INSERT IGNORE INTO  register_marketing (uid, email, type)
 178                              SELECT  ap.uid, f.Mel_usage, 'ax'
 179                                FROM  fusionax_anciens AS f
 180                          INNER JOIN  account_profiles AS ap ON (ap.pid = f.pid AND FIND_IN_SET('owner', perms))
 181                           LEFT JOIN  emails           AS e  ON (e.uid = ap.uid AND e.flags = 'active')
 182                               WHERE  f.Mel_usage != '' AND f.Mel_usage NOT LIKE '%@polytechnique.edu'
 183                                      AND f.Mel_usage NOT LIKE '%@polytechnique.org' AND f.Mel_usage NOT LIKE '%@m4x.org'
 184                                      AND f.Mel_usage NOT LIKE '%@melix.%' AND e.email IS NULL");
 185
 186 // Retrieves different deathdates.
 187 XDB::rawExecute("UPDATE  profile_merge_issues AS pm
 188              INNER JOIN  profiles         AS p ON (pm.pid = p.pid)
 189              INNER JOIN  fusionax_anciens AS f ON (f.pid = p.pid)
 190                     SET  pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'deathdate'), 'deathdate'), pm.deathdate_ax = f.Date_deces
 191                   WHERE  p.deathdate != f.Date_deces");
 192 XDB::rawExecute("INSERT IGNORE INTO  profile_merge_issues (pid, issues, deathdate_ax)
 193                              SELECT  f.pid, 'deathdate', f.Date_deces
 194                                FROM  fusionax_anciens AS f
 195                          INNER JOIN  profiles         AS p ON (f.pid = p.pid)
 196                               WHERE  p.deathdate != f.Date_deces");
 197 echo "Various informations inclusions finished.\n";
 198
 199 // Updates phone.
 200 // We consider there is conflict if a profile has a phone in both databases.
 201 echo "Starts phones inclusions.\n";
 202 XDB::rawExecute("UPDATE  profile_merge_issues AS pm
 203              INNER JOIN  fusionax_anciens     AS f  ON (f.pid = pm.pid)
 204              INNER JOIN  profile_phones       AS pp ON (pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0)
 205                     SET  pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'phone'), 'phone')
 206                   WHERE  f.tel_mobile != ''");
 207 XDB::rawExecute("INSERT IGNORE INTO  profile_merge_issues (pid, issues)
 208                              SELECT  f.pid, 'phone'
 209                                FROM  fusionax_anciens AS f
 210                          INNER JOIN  profile_phones   AS pp ON (pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0)
 211                               WHERE  f.tel_mobile != '' AND f.Mob_publiable = 1");
 212
 213 XDB::rawExecute("INSERT INTO  profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub)
 214                       SELECT  f.pid, 'user', 0, 'mobile', f.tel_mobile, 'ax'
 215                         FROM  fusionax_anciens AS f
 216                        WHERE  NOT EXISTS (SELECT  *
 217                                             FROM  profile_phones AS pp
 218                                            WHERE  pp.pid = f.pid AND pp.link_type = 'user' AND pp.tel_id = 0)
 219                               AND f.tel_mobile != '' AND f.Mob_publiable = 1");
 220 XDB::rawExecute("INSERT INTO  profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub)
 221                       SELECT  f.pid, 'user', MAX(pp.tel_id) + 1, 'mobile', f.tel_mobile, 'ax'
 222                         FROM  fusionax_anciens AS f
 223                   INNER JOIN  profile_phones   AS pp ON (pp.pid = f.pid AND pp.link_type = 'user')
 224                        WHERE  f.tel_mobile != '' AND f.Mob_publiable = 1
 225                     GROUP BY  f.pid");
 226 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_anciens');
 227 echo "Phones inclusions finished.\n";
 228
 229 // Retrieves addresses from AX database (one address per preofile maximum).
 230 echo "Starts addresses inclusions.\n";
 231 XDB::rawExecute('ALTER TABLE profile_addresses ADD INDEX (text(20))');
 232 XDB::rawExecute("DELETE  f
 233                    FROM  fusionax_adresses AS f
 234              INNER JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home')
 235                   WHERE  pa.text = f.text");
 236 // Deletes addresses of unknown type.
 237 XDB::rawExecute("DELETE FROM  fusionax_adresses
 238                        WHERE  Type_adr != 'E' AND Type_adr != 'P'");
 239
 240 XDB::rawExecute("UPDATE  profile_merge_issues AS pm
 241              INNER JOIN  fusionax_adresses    AS f  ON (f.pid = pm.pid)
 242              INNER JOIN  profile_addresses    AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND pa.id = 0)
 243                     SET  pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'address'), 'address')
 244                   WHERE  f.text IS NOT NULL");
 245 XDB::rawExecute("INSERT IGNORE INTO  profile_merge_issues (pid, issues)
 246                              SELECT  f.pid, 'address'
 247                                FROM  fusionax_adresses AS f
 248                          INNER JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND pa.id = 0)
 249                               WHERE  f.text IS NOT NULL");
 250
 251 XDB::rawExecute("INSERT INTO  profile_addresses (pid, type, id, pub, text)
 252                       SELECT  f.pid, 'home', IF(pa.id IS NULL , 0, MAX(pa.id) + 1), 'ax', f.text
 253                         FROM  fusionax_adresses AS f
 254                    LEFT JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home')
 255                        WHERE  f.text IS NOT NULL
 256                     GROUP BY  f.pid");
 257 XDB::rawExecute("INSERT INTO  profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_id)
 258                       SELECT  f.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fixed', f.tel, 'ax', pa.id
 259                         FROM  fusionax_adresses AS f
 260                   INNER JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND f.text = pa.text)
 261                    LEFT JOIN  profile_phones    AS pp ON (pp.pid = f.pid AND pp.link_type = 'address' AND pp.link_id = pa.id)
 262                        WHERE  f.tel != ''
 263                     GROUP BY  f.pid");
 264 XDB::rawExecute("INSERT INTO  profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_id)
 265                       SELECT  f.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fax', f.fax, 'ax', pa.id
 266                         FROM  fusionax_adresses AS f
 267                   INNER JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND f.text = pa.text)
 268                    LEFT JOIN  profile_phones    AS pp ON (pp.pid = f.pid AND pp.link_type = 'address' AND pp.link_id = pa.id)
 269                        WHERE  f.fax != ''
 270                     GROUP BY  f.pid");
 271 XDB::rawExecute('ALTER TABLE profile_addresses DROP INDEX text');
 272 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_adresses');
 273 echo "Addresses inclusions finished.\n";
 274
 275 // Retrieves education from AX database. This is the hardest part since AX only kept education as an unformated string.
 276 echo "Starts educations inclusions.\n";
 277 // Deletes empty educations.
 278 XDB::rawExecute("DELETE FROM  fusionax_formations
 279                        WHERE  Intitule_formation = '' AND Intitule_diplome = '' AND Descr_formation = ''");
 280 // Insert ids into fusionax_formations to prevent many joins.
 281 XDB::rawExecute('ALTER TABLE profile_education_enum ADD INDEX (name(60))');
 282 XDB::rawExecute('ALTER TABLE profile_education_degree_enum ADD INDEX (abbreviation(60))');
 283 XDB::rawExecute('UPDATE  fusionax_formations           AS f
 284               LEFT JOIN  profile_education_enum        AS pe ON (pe.name = f.Intitule_formation)
 285               LEFT JOIN  profile_education_degree_enum AS pd ON (pd.abbreviation = f.Intitule_diplome)
 286               LEFT JOIN  profile_education_field_enum  AS pf ON (pf.field = f.Descr_formation)
 287                     SET  f.eduid = pe.id, f.degreeid = pd.id, f.fieldid = pf.id');
 288 XDB::rawExecute('ALTER TABLE profile_education_enum DROP INDEX name');
 289 XDB::rawExecute('ALTER TABLE profile_education_degree_enum DROP INDEX abbreviation');
 290 // Updates non complete educations.
 291 XDB::rawExecute("UPDATE  profile_education             AS e
 292              INNER JOIN  fusionax_formations           AS f  ON (f.pid = e.pid)
 293              INNER JOIN  profile_education_degree_enum AS pd ON (e.degreeid = pd.id)
 294              INNER JOIN  profile_education_degree_enum AS fd ON (f.degreeid = fd.id)
 295                     SET  e.eduid = f.eduid
 296                   WHERE  NOT FIND_IN_SET('primary', e.flags) AND e.eduid IS NULL AND pd.level = fd.level");
 297 XDB::rawExecute("UPDATE  profile_education   AS e
 298              INNER JOIN  fusionax_formations AS f ON (f.pid = e.pid)
 299                     SET  e.degreeid = f.degreeid
 300                   WHERE  NOT FIND_IN_SET('primary', e.flags) AND e.degreeid IS NULL AND e.eduid = f.eduid");
 301 // Deletes duplicates.
 302 XDB::rawExecute("DELETE  f
 303                    FROM  fusionax_formations           AS f
 304              INNER JOIN  profile_education_degree_enum AS fd ON (fd.abbreviation = f.Intitule_diplome)
 305              INNER JOIN  profile_education             AS e  ON (e.pid = f.pid AND NOT FIND_IN_SET('primary', e.flags))
 306              INNER JOIN  profile_education_degree_enum AS pd ON (pd.id = e.degreeid)
 307                   WHERE  f.eduid = e.eduid AND fd.level = pd.level");
 308 // Updates merge_issues table.
 309 XDB::rawExecute("UPDATE  profile_merge_issues AS pm
 310              INNER JOIN  fusionax_formations  AS f ON (f.pid = pm.pid)
 311                     SET  pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'education'), 'education')");
 312 XDB::rawExecute("INSERT IGNORE INTO  profile_merge_issues (pid, issues)
 313                              SELECT  pid, 'education'
 314                                FROM  fusionax_formations");
 315
 316 $id = 0;
 317 $continue = 1;
 318 while ($continue > 0) {
 319     XDB::rawExecute("INSERT IGNORE INTO  profile_education (id, pid, eduid, degreeid, fieldid, program)
 320                                  SELECT  $id, pid, eduid, degreeid, fieldid, Descr_formation
 321                                    FROM  fusionax_formations");
 322     XDB::rawExecute("DELETE  f
 323                        FROM  fusionax_formations AS f
 324                  INNER JOIN  profile_education   AS pe ON (pe.pid = f.pid AND pe.id = $id)
 325                       WHERE  (pe.eduid = f.eduid OR (pe.eduid IS NULL AND f.eduid IS NULL))
 326                              AND (pe.degreeid = f.degreeid OR (pe.degreeid IS NULL AND f.degreeid IS NULL))
 327                              AND (pe.fieldid = f.fieldid OR (pe.fieldid IS NULL AND f.fieldid IS NULL))
 328                              AND (pe.program = f.Descr_formation OR (pe.program IS NULL AND f.Descr_formation IS NULL))");
 329     $continue = XDB::affectedRows();
 330     ++$id;
 331 }
 332 // Updates merge_issues table (eduid and degreeid should never be empty).
 333 XDB::rawExecute("UPDATE  profile_merge_issues AS pm
 334              INNER JOIN  profile_education    AS pe ON (pe.pid = pm.pid)
 335                     SET  pm.issues = CONCAT(pm.issues, ',', 'education')
 336                   WHERE  NOT FIND_IN_SET('education', pm.issues) AND (pe.eduid = '' OR pe.eduid IS NULL OR pe.degreeid = '' OR pe.degreeid IS NULL)");
 337 XDB::rawExecute("INSERT IGNORE INTO  profile_merge_issues (pid, issues)
 338                              SELECT  pid, 'education'
 339                                FROM  profile_education
 340                               WHERE  eduid = '' OR eduid IS NULL OR degreeid = '' OR degreeid IS NULL");
 341
 342 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations');
 343 echo "Educations inclusions finished.\n";
 344
 345 echo "All inclusions are done.\n";
 346
 347 XDB::commit();
 348
 349 /* vim:set et sw=4 sts=4 ts=4: */
 350 ?>