upgrade/1.0.1/merge.php

   1 #!/usr/bin/php5
   2 <?php
   3 require_once 'connect.db.inc.php';
   4 require_once '../../classes/address.php';
   5 require_once '../../classes/phone.php';
   6
   7 $globals->debug = 0; // Do not store backtraces.
   8
   9 /* Drops temporary tables and views used to checked if the merge was possible. */
  10 XDB::rawExecute('DROP VIEW IF EXISTS fusionax_xorg_anciens');
  11 XDB::rawExecute('DROP VIEW IF EXISTS fusionax_deceased');
  12 XDB::rawExecute('DROP VIEW IF EXISTS fusionax_promo');
  13 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_import');
  14
  15 /* Includes entreprises we do not have into profile_job_enum. */
  16 XDB::rawExecute('INSERT INTO  profile_job_enum (name, acronym, AX_code)
  17                       SELECT  f.Raison_sociale, f.Sigle, f.Code_etab
  18                         FROM  fusionax_entreprises AS f
  19                        WHERE  NOT EXISTS (SELECT  *
  20                                             FROM  profile_job_enum AS j
  21                                            WHERE  j.name = f.Raison_sociale OR j.name = f.Sigle OR f.Code_etab = j.AX_code)');
  22 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_entreprises');
  23
  24 /* Includes jobs we do not have into profile_job_enum. */
  25 $jobsAX = XDB::rawIterator('SELECT  p.pid, pje.name, pje.id, IF(f.Annuaire = 1, \'ax\', \'private\') AS pub, p.ax_id,
  26                                     IF(f.Raison_sociale, CONCAT(f.Raison_sociale, CONCAT(\' \', f.Libelle_fonctio), f.Libelle_fonctio) AS description
  27                               FROM  fusionax_activities AS f
  28                         INNER JOIN  profile_job_enum    AS pje ON (pje.AX_code = f.Code_etab)
  29                         INNER JOIN  profiles            AS p   ON (f.id_ancien = p.ax_id)
  30                           ORDER BY  p.pid');
  31 $jobsXorg = XDB::rawIterator('SELECT  p.pid, pj.id, pje.name, pje.acronym
  32                                 FROM  profile_job         AS pj
  33                           INNER JOIN  profile_job_enum    AS pje ON (pje.id = pj.jobid)
  34                           INNER JOIN  profiles            AS p   ON (p.pid = pj.pid)
  35                           INNER JOIN  fusionax_activities AS f   ON (f.id_ancien = p.ax_id)
  36                             ORDER BY  p.pid, pj.id');
  37 $jobXorg = $jobsXorg->next();
  38 while ($jobAX = $jobsAX->next()) {
  39     $already = false;
  40     $id = 0;
  41     while ($jobXorg['pid'] == $jobAX['pid']) {
  42         if ($jobXorg['name'] == $jobAX['name'] || $jobXorg['acronym'] == $jobAX['name']) {
  43             $jobXorg = $jobsXorg->next();
  44             $jobAX = $jobsAX->next();
  45             $already = true;
  46         }
  47         list($pid, $id, $name, $acronym) = $jobXorg;
  48     }
  49     if (!$already) {
  50         ++$id;
  51         XDB::execute('INSERT INTO  profile_job (id, pid, jobid, description, pub)
  52                            VALUES  {?}, {?}, {?}, {?}',
  53                      $id, $jobsAX['pid'], $jobsAX['id'], $jobsAX['description'], $jobsAX['pub']);
  54         $res = XDB::query("SELECT  CONCAT(Ligne1, IF(Ligne2 != '', CONCAT('\n', Ligne2), ''),
  55                                               IF(Ligne3 != '', CONCAT('\n', Ligne3), ''),
  56                                               '\n', IF(code_postal, code_postal, zip_cedex), ' ', zip_cedex) AS address,
  57                                    tel, fax
  58                              FROM  fusionax_adresses
  59                             WHERE  Type_adr = 'E' and id_ancien = {?}", $jobsAX['ax_id']);
  60         $res = $res->fetchOneRow();
  61         $phone = new Phone(array('display' => $res['tel'], 'link_id' => $id, 'pid' => $jobsAX['pid'], 'type' => 'fixed', 'link_type' => Phone::LINK_JOB, 'pub' => $jobsAX['pub']));
  62         $fax = new Phone(array('display' => $res['fax'], 'link_id' => $id, 'pid' => $jobsAX['pid'], 'type' => 'fax', 'link_type' => Phone::LINK_JOB, 'pub' => $jobsAX['pub']));
  63         $address = new Address(array('type' => Address:LINK_JOB, 'text' => $res['address'], 'pid' => $jobsAX['pid'], 'id' => $id));
  64         $phone->save();
  65         $fax->save();
  66         $address->save();
  67         if ($id > 1) {
  68             XDB::execute("UPDATE  profile_merge_issues
  69                              SET  issues = IF(issues, CONCAT(issues, ',', 'job'), 'job')
  70                            WHERE  pid = {?}", $jobsAX['pid']);
  71         }
  72     }
  73 }
  74 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_activities');
  75 XDB::rawExecute('DELETE FROM fusionax_adresses WHERE  Type_adr = \'E\'');
  76
  77 /* Retrieves information from fusionax_anciens: promo, nationality, corps, email, phone, deathdate */
  78 /* Updates uncertain promotions, but when we are we are right. */
  79 XDB::rawExecute("UPDATE  profile_merge_issues
  80                     SET  issues = IF(issues, CONCAT(issues, ',', 'promo'), 'promo'), entry_year_ax = f.promotion_etude
  81                   WHERE  EXISTS (SELECT  *
  82                                    FROM  profiles          AS p
  83                              INNER JOIN  profile_display   AS pd ON (p.pid = pd.pid)
  84                              INNER JOIN  profile_education AS pe ON (p.pid = pe.pid)
  85                              INNER JOIN  fusionax_anciens  AS f  ON (p.ax_id = f.ax_id)
  86                                   WHERE  pd.promo != CONCAT('X', f.promotion_etude)
  87                                          AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4))
  88                          AND pid NOT IN (18399,21099,40616)");
  89
  90 /* Updates nationality. */
  91 XDB::rawExecute('ALTER TABLE geoloc_pays ADD INDEX (license_plate);');
  92 XDB::rawExecute('UPDATE  profiles         AS p
  93              INNER JOIN  fusionax_anciens AS f ON (p.ax_id = f.ax_id)
  94              INNER JOIN  geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite)
  95                     SET  p.nationality1 = g.a2
  96                   WHERE  p.nationality1 IS NULL;');
  97 XDB::rawExecute('UPDATE  profiles         AS p
  98              INNER JOIN  fusionax_anciens AS f ON (p.ax_id = f.ax_id)
  99              INNER JOIN  geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite)
 100                     SET  p.nationality2 = g.a2
 101                   WHERE  p.nationality1 != g.a2 AND p.nationality2 IS NULL;');
 102 XDB::rawExecute('UPDATE  profiles         AS p
 103              INNER JOIN  fusionax_anciens AS f ON (p.ax_id = f.ax_id)
 104              INNER JOIN  geoloc_countries AS g ON (g.licensePlate = f.Code_nationalite)
 105                     SET  p.nationality3 = g.a2
 106                   WHERE  p.nationality1 != g.a2 AND p.nationality2 != g.a2 AND p.nationality3 IS NULL;');
 107 XDB::rawExecute('ALTER TABLE geoloc_pays DROP INDEX (license_plate)');
 108
 109 /* Updates corps. */
 110 XDB::rawExecute('REPLACE IGNORE INTO  profile_corps (pid, original_corpsid, current_corpsid, rankid, corps_pub)
 111                               SELECT  p.pid, c.id, c.id, r.id, \'ax\'
 112                                 FROM  profiles AS p
 113                           INNER JOIN  fusionax_anciens        AS f ON (p.ax_id = f.ax_id)
 114                           INNER JOIN  profile_corps_enum      AS c ON (f.corps_sortie = c.abbreviation)
 115                           INNER JOIN  profile_corps_rank_enum AS r ON (f.grade = r.abbreviation)
 116                                WHERE  NOT EXISTS (SELECT  *
 117                                                     FROM  profile_corps AS pc
 118                                                    WHERE  p.pid = pc.pid AND pc.original_corpsid != 1)');
 119 XDB::rawExecute("UPDATE  profile_corps      AS c
 120              INNER JOIN  profile_corps_enum AS e ON (c.original_corpsid = e.id)
 121               LEFT JOIN  profile_corps_enum AS a ON (a.name = 'Aucun (anc. démissionnaire)')
 122                     SET  c.original_corpsid = a.id
 123                   WHERE  e.name = 'Ancien élève étranger'");
 124 XDB::rawExecute("UPDATE  profile_corps_enum
 125                     SET  name = 'Aucun'
 126                   WHERE  name = 'Aucun (anc. démissionnaire)'");
 127 XDB::rawExecute("DELETE FROM  profile_corps_enum
 128                        WHERE  name = 'Ancien élève étranger'");
 129
 130 /* Updates email_directory. */
 131 XDB::rawExecute("INSERT IGNORE INTO  profile_directory (pid, email_directory)
 132                              SELECT  p.pid, f.Mel_usage
 133                                FROM  fusionax_anciens AS f
 134                          INNER JOIN  profiles         AS p ON (p.ax_id = f.ax_id)
 135                               WHERE  f.Mel_publiable != '0' AND f.Mel_usage != ''");
 136 XDB::rawExecute("INSERT IGNORE INTO  register_marketing (uid, email, type)
 137                              SELECT  ap.uid, f.Mel_usage, 'ax'
 138                                FROM  fusionax_anciens AS f
 139                          INNER JOIN  profiles         AS p  ON (p.ax_id = f.ax_id)
 140                          INNER JOIN  account_profiles AS ap ON (ap.pid = p.pid AND FIND_IN_SET('owner', perms))
 141                           LEFT JOIN  emails           AS e  ON (e.uid = ap.uid AND e.flags = 'active')
 142                               WHERE  f.Mel_usage != '' AND f.Mel_usage NOT LIKE '%@polytechnique.edu'
 143                                      AND f.Mel_usage NOT LIKE '%@polytechnique.org' AND f.Mel_usage NOT LIKE '%@m4x.org'
 144                                      AND f.Mel_usage NOT LIKE '%@melix.%' AND e.email IS NULL");
 145
 146 /* Updates phone. */
 147 $phonesXorg = Phone::iterate(array(), array('user'));
 148 $phonesAX = XDB::rawIterator("SELECT  p.pid, f.tel_mobile AS display, 'user' AS link_type, 'mobile' AS type, 'ax' AS pub
 149                                 FROM  fusionax_anciens AS f
 150                           INNER JOIN  profiles         AS p ON (f.ax_id = p.ax_id)
 151                                WHERE  f.tel_mobile IS NOT NULL
 152                             ORDER BY  p.pid");
 153 $phoneXorg = $phonesXorg->next();
 154 while ($phoneAX = new Phone($phonesAX->next())) {
 155     $already = false;
 156     $id = 0;
 157     $phoneAX->format();
 158     while ($phoneXorg->pid() == $phoneAX->pid()) {
 159         if ($phoneXorg->display == $phoneAX->display) {
 160             $already = true;
 161         }
 162         ++$id;
 163         $phoneXorg = $phonesXorg->next();
 164     }
 165     if (!$already) {
 166         $phoneAX->setId($id);
 167         $phoneAX->save();
 168         if ($id > 0) {
 169             XDB::execute("UPDATE  profile_merge_issues
 170                              SET  issues = IF(issues, CONCAT(issues, ',', 'phone'), 'phone')
 171                            WHERE  pid = {?}", $phoneAX->pid());
 172         }
 173     }
 174 }
 175
 176 /* Retrieves different deathdates. */
 177 XDB::rawExecute("UPDATE  profile_merge_issues AS pi
 178                     SET  issues = IF(issues, CONCAT(issues, ',', 'deathdate'), 'deathdate'), deathdate_ax = f.Date_deces
 179              INNER JOIN  profiles         AS p ON (pi.pid = p.pid)
 180              INNER JOIN  fusionax_anciens AS f ON (f.ax_id = p.ax_id)
 181                   WHERE  p.deathdate != f.Date_deces");
 182 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_anciens');
 183
 184 /* Retrieves addresses from AX database (one address per user maximum). */
 185 $addressesAX = XDB::rawIterator("SELECT  CONCAT(Ligne1, IF(Ligne2 != '', CONCAT('\n', Ligne2), ''),
 186                                                 IF(Ligne3 != '', CONCAT('\n', Ligne3), ''),
 187                                                 '\n', IF(code_postal, code_postal, zip_cedex), ' ', zip_cedex) AS text,
 188                                          f.tel, f.fax, p.pid, 'home' AS type, 'ax' AS pub
 189                                    FROM  fusionax_adresses AS f
 190                              INNER JOIN  profiles          AS p ON (f.id_ancien = p.ax_id)
 191                                   WHERE  f.Type_adr = 'E' and f.id_ancien = {?} AND Ligne1 != ''
 192                                ORDER BY  p.pid");
 193 $addressesXorg = Address::iterate(array(), array('home'));
 194 $addressXorg = $addressesXorg->next();
 195 while ($addressAX = new Address($addressesAX->next())) {
 196     $already = false;
 197     $id = 0;
 198     $addressAX->format();
 199     $addressAX->phones[0] = array('display' => $addressAX->tel, 'type' => 'fixed');
 200     $addressAX->phones[1] = array('display' => $addressAX->fax, 'type' => 'fax');
 201     while ($addressXorg->pid == $addressAX->pid) {
 202         if ($addressXorg->text == $addressAX->text) {
 203             $already = true;
 204         }
 205         ++$id;
 206         $addressXorg = $addressesXorg->next();
 207     }
 208     if (!$already) {
 209         $addressAX->setId($id);
 210         $addressAX->save();
 211         if ($id > 0) {
 212             XDB::execute("UPDATE  profile_merge_issues
 213                              SET  issues = IF(issues, CONCAT(issues, ',', 'address'), 'address')
 214                            WHERE  pid = {?}", $addressAX->pid);
 215         }
 216     }
 217 }
 218 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_adresses');
 219
 220 /* Retrieves education from AX database. This is the hardest part since AX only kept education as an unformated string. */
 221 // {{{ First, we need to build a few lists.
 222 $degree_list = $level_list = $university_list = $field_list = array();
 223 $res = XDB::rawIterator('SELECT  id, abbreviation AS name
 224                            FROM  profile_education_degree_enum
 225                        ORDER BY  id');
 226 while ($res as $item->next()) {
 227     $degree_list[$item[1]] = $item[0];
 228 }
 229 $res = XDB::rawIterator('SELECT  level, abbreviation AS name
 230                            FROM  profile_education_degree_enum
 231                        ORDER BY  id');
 232 while ($res as $item->next()) {
 233     $level_list[$item[1]] = $item[0];
 234 }
 235 $res = XDB::rawIterator("SELECT  id, IF(abbreviation = '', name, abbreviation) AS name
 236                            FROM  profile_education_enum
 237                        ORDER BY  id");
 238 while ($res as $item->next()) {
 239     $university_list[$item[1]] = $item[0];
 240 }
 241 $res = XDB::rawIterator('SELECT  id, field AS name
 242                            FROM  profile_education_field_enum
 243                        ORDER BY  id');
 244 while ($res as $item->next()) {
 245     $field_list[$item[1]] = $item[0];
 246 }
 247 // }}}
 248 $edusXorg = XDB::rawIterator("SELECT  p.pid, d.abbreviation AS degree, IF(e.abbreviation = '', e.name, e.abbreviation) AS university,
 249                                       pe.program, pe.id AS no
 250                                 FROM  profile_education             AS pe
 251                           INNER JOIN  profiles                      AS p ON (pe.pid = p.pid)
 252                           INNER JOIN  fusionax_formations           AS f ON (f.id_ancien = p.ax_id)
 253                           INNER JOIN  profile_education_enum        AS e ON (pe.eduid = e.id)
 254                           INNER JOIN  profile_education_degree_enum AS d ON (pe.degreeid = d.id)
 255                                WHERE  NOT FIND_IN_SET('primary', pe.flags)
 256                             ORDER BY  p.pid, pe.id");
 257 $edusAX = XDB::rawIterator("SELECT  p.pid, f.Intitule_diplome AS degree, f.Intitule_formation AS university, f.Descr_formation AS program
 258                               FROM  fusionax_formations AS f
 259                         INNER JOIN  profiles            AS p ON (f.id_ancien = p.ax_id)
 260                           ORDER BY  p.pid");
 261 $eduXorg = $edusXorg->next();
 262 while ($eduAX = $edusAX->next()) {
 263     $id = 0;
 264     while ($eduXorg['pid'] == $eduAX['pid']) {
 265         if ($eduXorg['university'] == $eduAX['university'] && $level_list[$eduXorg['degree']] == $level_list[$eduAX['degree']]) {
 266             $already = true;
 267         }
 268         ++$id;
 269         $eduXorg = $edusXorg->next();
 270     }
 271     if (isset($field_list[$eduAX['program']])) {
 272         $fieldid = $field_list[$eduAX['program']];
 273         $program = null;
 274     } else {
 275         $fieldid = null;
 276         $program = $eduAX['program'];
 277     }
 278     if (!$already) {
 279         XDB::execute('INSERT INTO  profile_education (pid, degreeid, eduid, program, fieldid, id)
 280                            VALUES  {?}, {?}, {?}, {?}, {?}, {?}',
 281                      $eduAX['pid'], $degree_list[$eduAX['degree']], $university_list[$eduAX['university']],
 282                      $program, $fieldid, $id);
 283         if ($id > 0) {
 284             XDB::execute("UPDATE  profile_merge_issues
 285                              SET  issues = IF(issues, CONCAT(issues, ',', 'education'), 'education')
 286                            WHERE  pid = {?}", $addressAX->pid);
 287         }
 288     }
 289 }
 290 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations');
 291
 292 /* vim:set et sw=4 sts=4 ts=4: */
 293 ?>