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