80286ddc0b840c121f30ed7852dd9ffac3bab6b3
3 require_once 'connect.db.inc.php';
4 require_once '../../classes/address.php';
5 require_once '../../classes/phone.php';
7 $globals->debug
= 0; // Do not store backtraces.
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');
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');
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)
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()) {
41 while ($jobXorg['pid'] == $jobAX['pid']) {
42 if ($jobXorg['name'] == $jobAX['name'] ||
$jobXorg['acronym'] == $jobAX['name']) {
43 $jobXorg = $jobsXorg->next();
44 $jobAX = $jobsAX->next();
47 list($pid, $id, $name, $acronym) = $jobXorg;
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,
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));
68 XDB
::execute("UPDATE profile_merge_issues
69 SET issues = IF(issues, CONCAT(issues, ',', 'job'), 'job')
70 WHERE pid = {?}", $jobsAX['pid']);
74 XDB
::rawExecute('DROP TABLE IF EXISTS fusionax_activities');
75 XDB
::rawExecute('DELETE FROM fusionax_adresses WHERE Type_adr = \'E\'');
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 *
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)");
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)');
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\'
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
126 WHERE name = 'Aucun (anc. démissionnaire)'");
127 XDB
::rawExecute("DELETE FROM profile_corps_enum
128 WHERE name = 'Ancien élève étranger'");
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");
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
153 $phoneXorg = $phonesXorg->next();
154 while ($phoneAX = new Phone($phonesAX->next())) {
158 while ($phoneXorg->pid() == $phoneAX->pid()) {
159 if ($phoneXorg->display
== $phoneAX->display
) {
163 $phoneXorg = $phonesXorg->next();
166 $phoneAX->setId($id);
169 XDB
::execute("UPDATE profile_merge_issues
170 SET issues = IF(issues, CONCAT(issues, ',', 'phone'), 'phone')
171 WHERE pid = {?}", $phoneAX->pid());
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');
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 != ''
193 $addressesXorg = Address
::iterate(array(), array('home'));
194 $addressXorg = $addressesXorg->next();
195 while ($addressAX = new Address($addressesAX->next())) {
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
) {
206 $addressXorg = $addressesXorg->next();
209 $addressAX->setId($id);
212 XDB
::execute("UPDATE profile_merge_issues
213 SET issues = IF(issues, CONCAT(issues, ',', 'address'), 'address')
214 WHERE pid = {?}", $addressAX->pid
);
218 XDB
::rawExecute('DROP TABLE IF EXISTS fusionax_adresses');
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
226 while ($res as $item->next()) {
227 $degree_list[$item[1]] = $item[0];
229 $res = XDB
::rawIterator('SELECT level, abbreviation AS name
230 FROM profile_education_degree_enum
232 while ($res as $item->next()) {
233 $level_list[$item[1]] = $item[0];
235 $res = XDB
::rawIterator("SELECT id, IF(abbreviation = '', name, abbreviation) AS name
236 FROM profile_education_enum
238 while ($res as $item->next()) {
239 $university_list[$item[1]] = $item[0];
241 $res = XDB
::rawIterator('SELECT id, field AS name
242 FROM profile_education_field_enum
244 while ($res as $item->next()) {
245 $field_list[$item[1]] = $item[0];
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)
261 $eduXorg = $edusXorg->next();
262 while ($eduAX = $edusAX->next()) {
264 while ($eduXorg['pid'] == $eduAX['pid']) {
265 if ($eduXorg['university'] == $eduAX['university'] && $level_list[$eduXorg['degree']] == $level_list[$eduAX['degree']]) {
269 $eduXorg = $edusXorg->next();
271 if (isset($field_list[$eduAX['program']])) {
272 $fieldid = $field_list[$eduAX['program']];
276 $program = $eduAX['program'];
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);
284 XDB
::execute("UPDATE profile_merge_issues
285 SET issues = IF(issues, CONCAT(issues, ',', 'education'), 'education')
286 WHERE pid = {?}", $addressAX->pid
);
290 XDB
::rawExecute('DROP TABLE IF EXISTS fusionax_formations');
292 /* vim:set et sw=4 sts=4 ts=4: */