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