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