upgrade/merge-0.0.1/07_education.php

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