Also merges masters and doctorates info; XXX: to be removed if we don't get the M...

[platal.git] / upgrade / 1.0.1 / merge.php

diff --git a/upgrade/1.0.1/merge.php b/upgrade/1.0.1/merge.php
index 109f8b9..19f82a7 100755 (executable)
--- a/upgrade/1.0.1/merge.php
+++ b/upgrade/1.0.1/merge.php
@@ -12,13 +12,21 @@ XDB::rawExecute('DROP VIEW IF EXISTS fusionax_promo');
 XDB::rawExecute('DROP TABLE IF EXISTS fusionax_import');
 
 // Fills pid fields in all table, to avoid to many joins.
-foreach (array('fusionax_activites', 'fusionax_adresses', 'fusionax_anciens', 'fusionax_formations') as $table) {
+foreach (array('fusionax_activites', 'fusionax_adresses', 'fusionax_anciens', 'fusionax_formations', 'fusionax_formations_md') as $table) {
     XDB::rawExecute("UPDATE  $table   AS f
                  INNER JOIN  profiles AS p ON (f.ax_id = p.ax_id)
                         SET  f.pid = p.pid");
     XDB::rawExecute("DELETE FROM $table WHERE pid IS NULL");
 }
 
+echo "Update timestamp.\n";
+XDB::rawExecute("UPDATE  profiles AS p
+                    SET  p.last_change = GREATEST(COALESCE((SELECT MAX(Date_maj) FROM fusionax_anciens    AS f WHERE f.pid = p.pid), '0000-00-00'),
+                                                  COALESCE((SELECT MAX(Date_maj) FROM fusionax_activites  AS f WHERE f.pid = p.pid), '0000-00-00'),
+                                                  COALESCE((SELECT MAX(Date_maj) FROM fusionax_adresses   AS f WHERE f.pid = p.pid), '0000-00-00'),
+                                                  COALESCE((SELECT MAX(Date_maj) FROM fusionax_formations AS f WHERE f.pid = p.pid), '0000-00-00'),
+                                                  COALESCE(p.last_change, '0000-00-00'))");
+
 // Includes entreprises we do not have into profile_job_enum.
 // We first retrieve AX code, then add missing compagnies.
 echo "Starts jobs inclusions.\n";
@@ -138,11 +146,23 @@ XDB::rawExecute('UPDATE  profiles         AS p
 XDB::rawExecute('ALTER TABLE geoloc_countries DROP INDEX licensePlate');
 
 // Updates corps.
+XDB::rawExecute("INSERT IGNORE INTO  profile_corps_enum (name, abbreviation)
+                             VALUES  ('Ancien élève étranger', 'Z')");
+XDB::rawExecute("INSERT IGNORE INTO  profile_corps_rank_enum (name, abbreviation)
+                             VALUES  ('Ing.ch.P.C.hon.', 'DEL1'), ('Ing.Mil.Air Retr.', 'DEL2'),
+                                     ('Col.hon.Tra.', 'DEL3'), ('Colonel CR', 'DEL4'),
+                                     ('Conseil d\'Etat', 'DEL5'), ('Commiss.Gén. Brigade aérienne', 'DEL6'),
+                                     ('Off. Mar. dém.', 'DEL7'), ('Maître des Requêtes', 'DEL8'),
+                                     ('', 'DEL9'), ('autres', 'DEL10')");
+XDB::rawExecute('UPDATE  profile_corps      AS pc
+             INNER JOIN  fusionax_anciens   AS f ON (f.pid = pc.pid)
+             INNER JOIN  profile_corps_enum AS c ON (f.corps_sortie = c.abbreviation)
+                    SET  pc.original_corpsid = c.id');
 XDB::rawExecute("INSERT IGNORE INTO  profile_corps (pid, original_corpsid, current_corpsid, rankid, corps_pub)
                              SELECT  f.pid, c.id, c.id, r.id, 'ax'
                                FROM  fusionax_anciens        AS f
                          INNER JOIN  profile_corps_enum      AS c ON (f.corps_sortie = c.abbreviation)
-                         INNER JOIN  profile_corps_rank_enum AS r ON (f.grade = r.abbreviation)
+                         INNER JOIN  profile_corps_rank_enum AS r ON (f.grade = r.name)
                               WHERE  NOT EXISTS (SELECT  *
                                                    FROM  profile_corps AS pc
                                                   WHERE  f.pid = pc.pid AND pc.original_corpsid != 1)");
@@ -156,6 +176,13 @@ XDB::rawExecute("UPDATE  profile_corps_enum
                   WHERE  name = 'Aucun (anc. démissionnaire)'");
 XDB::rawExecute("DELETE FROM  profile_corps_enum
                        WHERE  name = 'Ancien élève étranger'");
+XDB::rawExecute("UPDATE  profile_corps           AS c
+             INNER JOIN  profile_corps_rank_enum AS r ON (c.rankid = r.id)
+             INNER JOIN  profile_corps_rank_enum AS a ON (a.name = 'Aucun')
+                    SET  c.rankid = a.id
+                  WHERE  r.name LIKE 'DEL%'");
+XDB::rawExecute("DELETE FROM  profile_corps_rank_enum
+                       WHERE  name LIKE 'DEL%'");
 
 // Updates email_directory.
 XDB::rawExecute("UPDATE  profiles         AS p
@@ -236,8 +263,8 @@ XDB::rawExecute("INSERT IGNORE INTO  profile_merge_issues (pid, issues)
                          INNER JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home' AND pa.id = 0)
                               WHERE  f.text IS NOT NULL");
 
-XDB::rawExecute("INSERT INTO  profile_addresses (pid, type, id, pub, text)
-                      SELECT  f.pid, 'home', IF(pa.id IS NULL , 0, MAX(pa.id) + 1), 'ax', f.text
+XDB::rawExecute("INSERT INTO  profile_addresses (pid, type, id, pub, text, flags)
+                      SELECT  f.pid, 'home', IF(pa.id IS NULL , 0, MAX(pa.id) + 1), 'ax', f.text, 'mail'
                         FROM  fusionax_adresses AS f
                    LEFT JOIN  profile_addresses AS pa ON (pa.pid = f.pid AND pa.type = 'home')
                        WHERE  f.text IS NOT NULL
@@ -262,6 +289,12 @@ echo "Addresses inclusions finished.\n";
 
 // Retrieves education from AX database. This is the hardest part since AX only kept education as an unformated string.
 echo "Starts educations inclusions.\n";
+// Updates master and doctorate educational fields.
+XDB::rawExecute("UPDATE  profile_education      AS e
+             INNER JOIN  fusionax_formations_md AS f ON (f.pid = e.pid AND FIND_IN_SET('primary', e.flags))
+                    SET  e.program = f.field, e.fieldid = f.fieldid");
+XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations_md');
+
 // Deletes empty educations.
 XDB::rawExecute("DELETE FROM  fusionax_formations
                        WHERE  Intitule_formation = '' AND Intitule_diplome = '' AND Descr_formation = ''");