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 | ||
f8c2bf68 SJ |
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)"); | |
6b9e0135 | 30 | |
f8c2bf68 SJ |
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(); | |
6b9e0135 | 107 | } |
f8c2bf68 SJ |
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"; | |
6b9e0135 | 135 | |
f8c2bf68 SJ |
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)"); | |
6b9e0135 | 153 | |
f8c2bf68 SJ |
154 | // Updates nationality. |
155 | XDB::rawExecute('ALTER TABLE geoloc_countries ADD INDEX (licensePlate)'); | |
6b9e0135 SJ |
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) | |
f8c2bf68 SJ |
159 | SET p.nationality1 = g.iso_3166_1_a2 |
160 | WHERE p.nationality1 IS NULL'); | |
6b9e0135 SJ |
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) | |
f8c2bf68 SJ |
164 | SET p.nationality2 = g.iso_3166_1_a2 |
165 | WHERE p.nationality1 != g.iso_3166_1_a2 AND p.nationality2 IS NULL'); | |
6b9e0135 SJ |
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) | |
f8c2bf68 SJ |
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'); | |
6b9e0135 | 172 | |
f8c2bf68 SJ |
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)"); | |
6b9e0135 SJ |
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 | ||
f8c2bf68 SJ |
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"); | |
6b9e0135 SJ |
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 | ||
f8c2bf68 SJ |
209 | // Retrieves different deathdates. |
210 | XDB::rawExecute("UPDATE profile_merge_issues AS pm | |
211 | INNER JOIN profiles AS p ON (pm.pid = p.pid) | |
6b9e0135 | 212 | INNER JOIN fusionax_anciens AS f ON (f.ax_id = p.ax_id) |
f8c2bf68 | 213 | SET pm.issues = IF(pm.issues, CONCAT(pm.issues, ',', 'deathdate'), 'deathdate'), pm.deathdate_ax = f.Date_deces |
6b9e0135 | 214 | WHERE p.deathdate != f.Date_deces"); |
f8c2bf68 SJ |
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"); | |
6b9e0135 | 253 | XDB::rawExecute('DROP TABLE IF EXISTS fusionax_anciens'); |
f8c2bf68 | 254 | echo "Phones inclusions finished.\n"; |
6b9e0135 | 255 | |
f8c2bf68 SJ |
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"); | |
325af12b SJ |
264 | // Deletes addresses of unknown type. |
265 | XDB::rawExecute("DELETE FROM fusionax_adresses | |
266 | WHERE Type_adr != 'E' AND Type_adr != 'P'"); | |
267 | ||
f8c2bf68 SJ |
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) | |
325af12b | 297 | SELECT p.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fixed', f.tel, 'ax', pa.id |
f8c2bf68 SJ |
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) | |
325af12b SJ |
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"); | |
f8c2bf68 | 304 | XDB::rawExecute("INSERT INTO profile_phones (pid, link_type, tel_id, tel_type, display_tel, pub, link_id) |
325af12b | 305 | SELECT p.pid, 'address', IF(pp.tel_id IS NULL, 0, MAX(pp.tel_id) + 1), 'fax', f.fax, 'ax', pa.id |
f8c2bf68 SJ |
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) | |
325af12b SJ |
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"); | |
f8c2bf68 | 312 | XDB::rawExecute('ALTER TABLE profile_addresses DROP INDEX text'); |
6b9e0135 | 313 | XDB::rawExecute('DROP TABLE IF EXISTS fusionax_adresses'); |
f8c2bf68 | 314 | echo "Addresses inclusions finished.\n"; |
6b9e0135 | 315 | |
f8c2bf68 SJ |
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"; | |
325af12b SJ |
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(); | |
6b9e0135 | 368 | } |
325af12b SJ |
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 | ||
6b9e0135 | 380 | XDB::rawExecute('DROP TABLE IF EXISTS fusionax_formations'); |
f8c2bf68 SJ |
381 | echo "Educations inclusions finished.\n"; |
382 | ||
383 | echo "All inclusions are done.\n"; | |
384 | ||
385 | XDB::commit(); | |
6b9e0135 SJ |
386 | |
387 | /* vim:set et sw=4 sts=4 ts=4: */ | |
388 | ?> |