Commit | Line | Data |
---|---|---|
c7eac294 SJ |
1 | #!/usr/bin/php5 |
2 | <?php | |
3 | require_once 'connect.db.inc.php'; | |
4 | ||
5 | $globals->debug = 0; //do not store backtraces | |
6 | ||
7 | // get degree list | |
8 | $res = XDB::iterator("SELECT id, abbreviation AS name | |
9 | FROM profile_education_degree_enum | |
10 | ORDER BY id"); | |
11 | foreach ($res as $item) { | |
12 | $degree_list[$item[1]] = $item[0]; | |
13 | } | |
14 | ||
15 | // get degree's level list | |
16 | $res = XDB::iterator("SELECT id, level AS name | |
17 | FROM profile_education_degree_enum | |
18 | ORDER BY id"); | |
19 | foreach ($res as $item) { | |
20 | $level_list [$item[1]] = $item[0]; | |
21 | } | |
22 | ||
23 | // get university list | |
24 | $res = XDB::iterator("SELECT id, IF(abbreviation = '', name, abbreviation) AS name | |
25 | FROM profile_education_enum | |
26 | ORDER BY id"); | |
27 | foreach ($res as $item) { | |
28 | $university_list [$item[1]] = $item[0]; | |
29 | } | |
30 | ||
31 | // get field list | |
32 | $res = XDB::iterator("SELECT id, field AS name | |
33 | FROM profile_education_field_enum | |
34 | ORDER BY id"); | |
35 | foreach ($res as $item) { | |
36 | $field_list [$item[1]] = $item[0]; | |
37 | } | |
38 | ||
39 | // get Xorg education data | |
40 | $res = XDB::query("SELECT p.uid, d.abbreviation AS degree, IF(e.abbreviation = '', e.name, e.abbreviation) AS university, p.program, p.id AS no | |
41 | FROM profile_education AS p | |
42 | INNER JOIN profile_education_enum AS e ON (p.eduid = e.id) | |
43 | INNER JOIN profile_education_degree_enum AS d ON (p.degreeid = d.id) | |
44 | WHERE p.id != 100 | |
45 | ORDER BY p.uid"); | |
46 | $xorg_edu = $res->fetchAllAssoc(); | |
47 | ||
48 | // get AX education data | |
49 | $res = XDB::iterator("SELECT u.user_id AS uid, f.Intitule_diplome AS degree, f.Intitule_formation AS university, | |
50 | CONCAT(Descr_formation, ' ', tmp_1, ' ', tmp_2, ' ', tmp_3, ' ', tmp_4) AS program | |
51 | FROM fusionax_formations AS f | |
52 | INNER JOIN fusionax_xorg_anciens AS u ON (f.id_ancien = u.matricule_ax) | |
53 | ORDER BY u.user_id"); | |
54 | $ax_edu = $res->fetchAllAssoc(); | |
55 | ||
56 | // merge education data | |
57 | $nb_merge_succes = 0; | |
58 | $nb_total = 0; | |
59 | $xorg = next($xorg_edu); | |
60 | while ($ax = next($ax_edu)) { | |
61 | array_walk($ax, 'trim'); | |
62 | if (($ax['degree'] == '') && ($ax['university'] = '')) { | |
63 | continue; | |
64 | } | |
65 | while ($xorg['uid'] && ($xorg['uid'] < $ax['uid'])) { | |
66 | $xorg = next($xorg_edu); | |
67 | } | |
68 | ||
69 | $no = 0; | |
70 | if($xorg['uid'] == $ax['uid']) { | |
71 | $uid = $xorg['uid']; | |
72 | $i = 0; | |
73 | ||
74 | while (($xorg['uid'] == $uid) && (!merge($ax, $xorg))) { | |
75 | $xorg = next($xorg_edu); | |
76 | $i++; | |
77 | $no++; | |
78 | } | |
79 | while ($xorg['uid'] == $uid) { | |
80 | $xorg = next($xorg_edu); | |
81 | $no++; | |
82 | } | |
83 | ||
84 | if ($i > 0) { | |
85 | $i = $no; | |
86 | } else { | |
87 | $i = $no - 1; | |
88 | } | |
89 | while ($i != 0) { | |
90 | $xorg = prev($xorg_edu); | |
91 | $i--; | |
92 | } | |
93 | if ($ax['no']) { | |
94 | $no = $ax['no']; | |
95 | $nb_merge_succes++; | |
96 | } | |
97 | } | |
98 | adapt_ax($ax); | |
99 | XDB::execute("REPLACE INTO profile_education (uid, degreeid, eduid, program, fieldid, id) | |
100 | VALUES {?}, {?}, {?}, {?}, {?}, {?}", | |
101 | $ax['uid'], $ax['degree'], $ax['university'], $ax['program'], $ax['field'], $no); | |
102 | $nb_total++; | |
103 | if (($nb_total % 1000) == 0) { | |
104 | echo "."; | |
105 | } | |
106 | } | |
107 | ||
108 | echo "\n"; | |
109 | echo "$nb_merge_succes educations were succesfully merged among $nb_total entries.\n"; | |
110 | ||
111 | // auxilliary functions | |
112 | ||
113 | // replaces AX data by corresponding id in Xorg database | |
114 | function adapt_ax(&$ax) | |
115 | { | |
116 | if ($field_list[$ax['program']]) { | |
117 | $ax['field'] = $field_list[$ax['program']]; | |
118 | $ax['program'] = null; | |
119 | } | |
120 | $ax['degree'] = $degree_list[$ax['degree']]; | |
121 | $ax['university'] = $university_list[$ax['university']]; | |
122 | } | |
123 | ||
124 | // tries to merge two educations into ax and returns 1 in case of merge | |
125 | function merge(&$ax, $xorg) | |
126 | { | |
127 | if ($ax['degree'] == '') { | |
128 | if ($ax['university'] != $xorg['university']) { | |
129 | return 0; | |
130 | } | |
131 | $ax['degree'] = $xorg['degree']; | |
132 | $ax['university'] = $xorg['university']; | |
133 | } else { | |
134 | if ($ax['university'] == '') { | |
135 | if (($level_list[$ax['degree']] == $level_list[$xorg['degree']]) || ($xorg['degree'] == "Dipl.") || ($ax['degree'] == "Dipl.")) { | |
136 | if ($xorg['degree'] != "Dipl.") { | |
137 | $ax['degree'] = $xorg['degree']; | |
138 | } | |
139 | $ax['university'] = $xorg['university']; | |
140 | } else { | |
141 | return 0; | |
142 | } | |
143 | } else { | |
144 | if (($ax['university'] == $xorg['university']) && | |
145 | (($level_list[$ax['degree']] == $level_list[$xorg['degree']]) || ($xorg['degree'] == "Dipl.") || ($ax['degree'] == "Dipl."))) { | |
146 | if ($xorg['degree'] != "Dipl.") { | |
147 | $ax['degree'] = $xorg['degree']; | |
148 | } | |
149 | } else { | |
150 | return 0; | |
151 | } | |
152 | } | |
153 | } | |
154 | if ($xorg['program']) { | |
155 | $ax['field'] = $field_list[$ax['program']]; | |
156 | $ax['program'] = $xorg['program']; | |
157 | } | |
158 | $ax['no'] = $xorg['no']; | |
159 | return 1; | |
160 | } | |
161 | ||
162 | /* vim:set et sw=4 sts=4 ts=4: */ | |
163 | ?> |