| 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 | ?> |