X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=modules%2Ffusionax.php;h=31f6fd433afcd89f61ea3f1fc361c8ce80f95c69;hb=8526cfe046865202710c548a3a58c7dbddeaee65;hp=d631dc3085b69e301fd25f3c5bdd84dd53e8b031;hpb=c8ab27eb822db39d5e3f26e202fa4ac01f76489f;p=platal.git diff --git a/modules/fusionax.php b/modules/fusionax.php index d631dc3..31f6fd4 100644 --- a/modules/fusionax.php +++ b/modules/fusionax.php @@ -1,6 +1,6 @@ changeTpl('fusionax/import.tpl'); return; @@ -124,10 +125,10 @@ class FusionAxModule extends PLModule exec('mv -f ' . $spoolpath . 'Formations_MD_out.txt ' . $spoolpath . 'Formations_MD.txt'); $report[] = 'Fichier parsé.'; $report[] = 'Import dans la base en cours...'; - $next = 'integrateSQL'; XDB::execute("UPDATE profiles SET ax_id = NULL WHERE ax_id = ''"); + $next = 'integrateSQL'; } } elseif ($action == 'integrateSQL') { // intégration des données dans la base MySQL @@ -143,6 +144,11 @@ class FusionAxModule extends PLModule if ($file != '') { // récupère le contenu du fichier sql $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file])); + $db = mysqli_init(); + $db->options(MYSQLI_OPT_LOCAL_INFILE, true); + $db->real_connect($globals->dbhost, $globals->dbuser, $globals->dbpwd, $globals->dbdb); + $db->autocommit(true); + $db->set_charset($globals->dbcharset); foreach ($queries as $q) { if (trim($q)) { // coupe le fichier en requêtes individuelles @@ -153,9 +159,13 @@ class FusionAxModule extends PLModule $report[] = addslashes($l); } // exécute la requête - XDB::execute(str_replace('{?}', $spoolpath, $q)); + $res = $db->query(str_replace('{?}', $spoolpath, $q)); + if ($res === false) { + throw new XDBException($q, $db->error); + } } } + $db->close(); // trouve le prochain fichier à exécuter $nextfile = $file + 1; } else { @@ -183,12 +193,16 @@ class FusionAxModule extends PLModule $entry_year = 1920; $grad_year = 1923; $promo = 'X1920'; - $sex = PlUser::GENDER_MALE; + $hrpromo = '1920'; + $sex = 'male'; $xorgId = 19200000; $type = 'x'; - while (list($firstname, $lastname, $ax_id) = $res->next()) { - $hrid = self::getHrid($firstname, $lastname, $promo); + while ($new = $res->next()) { + $firstname = $new['prenom']; + $lastname = $new['Nom_complet']; + $ax_id = $new['ax_id']; + $hrid = User::makeHrid($firstname, $lastname, $hrpromo); $res1 = XDB::query('SELECT COUNT(*) FROM accounts WHERE hruid = {?}', $hrid); @@ -206,9 +220,9 @@ class FusionAxModule extends PLModule VALUES ({?}, {?}, {?}, {?})', $hrid, $xorgId, $ax_id, $sex); $pid = XDB::insertId(); - XDB::execute('INSERT INTO profile_public_names (pid, lastname_initial, firstname_initial) - VALUES ({?}, {?}, {?})', - $pid, $lastname, $firstname); + XDB::execute('INSERT INTO profile_public_names (pid, lastname_initial, firstname_initial, lastname_main, firstname_main) + VALUES ({?}, {?}, {?}, {?}, {?})', + $pid, $lastname, $firstname, $lastname, $firstname); XDB::execute('INSERT INTO profile_display (pid, yourself, public_name, private_name, directory_name, short_name, sort_name, promo) VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})', @@ -217,8 +231,8 @@ class FusionAxModule extends PLModule VALUES ({?}, {?}, {?}, {?}, {?}, {?})', $pid, $eduSchools[Profile::EDU_X], $degreeid, $entry_year, $grad_year, 'primary'); XDB::execute('INSERT INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, lastname, firstname, sex) - VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})', - $hrid, $type, 0, 'active', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex); + VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})', + $hrid, $type, 0, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex); $uid = XDB::insertId(); XDB::execute('INSERT INTO account_profiles (uid, pid, perms) VALUES ({?}, {?}, {?})', @@ -267,10 +281,12 @@ class FusionAxModule extends PLModule f.promotion_etude AS promo_etude_ax, pe.grad_year AS promo_sortie_xorg FROM profiles AS p INNER JOIN profile_display AS pd ON (p.pid = pd.pid) - INNER JOIN profile_education AS pe ON (p.pid = pe.pid) + INNER JOIN profile_education AS pe ON (p.pid = pe.pid AND FIND_IN_SET('primary', pe.flags)) INNER JOIN fusionax_anciens AS f ON (p.ax_id = f.ax_id) WHERE (f.groupe_promo = 'X' AND pd.promo != CONCAT('X', f.promotion_etude) - AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)) + AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4) + AND !(f.promotion_etude = pe.entry_year + 2 AND pe.grad_year = pe.entry_year + 5) + AND f.promotion_etude != 0) OR (f.groupe_promo = 'D' AND f.promotion_etude != pe.grad_year) OR (f.groupe_promo = 'M' AND f.promotion_etude != pe.entry_year) GROUP BY p.pid"); @@ -291,15 +307,16 @@ class FusionAxModule extends PLModule } /* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX - * (mises à part les promo 1921, 1922, 1923, 1924, 1925, 1927 qui ne figurent pas dans les données de l'AX)*/ + * (mises à part les promo 1921, 1922, 1923, 1924, 1925, 1927, 1928, 1929 qui ne figurent pas dans les données de l'AX)*/ private static function find_wrong_in_xorg($limit = 10) { - return XDB::iterator('SELECT u.promo, u.pid, u.private_name + return XDB::iterator('SELECT u.promo, u.pid, u.private_name, u.ax_id FROM fusionax_xorg_anciens AS u WHERE NOT EXISTS (SELECT * FROM fusionax_anciens AS f WHERE f.ax_id = u.ax_id) - AND u.ax_id IS NOT NULL AND promo NOT IN (\'X1921\', \'X1922\', \'X1923\', \'X1924\', \'X1925\', \'X1927\')'); + AND u.ax_id IS NOT NULL + AND promo NOT IN (\'X1921\', \'X1922\', \'X1923\', \'X1924\', \'X1925\', \'X1927\', \'X1928\', \'X1929\')'); } /** Lier les identifiants d'un ancien dans les deux annuaires @@ -500,7 +517,7 @@ class FusionAxModule extends PLModule function handler_promo($page, $action = '') { $page->changeTpl('fusionax/promo.tpl'); - $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo + $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg) AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg) @@ -510,7 +527,7 @@ class FusionAxModule extends PLModule $page->assign('nbMissmatchingPromos', $res->total()); $page->assign('missmatchingPromos', $res); - $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo + $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE promo_etude_ax = promo_etude_xorg + 1 AND groupe_promo = 'X' ORDER BY promo_etude_xorg"); @@ -518,7 +535,7 @@ class FusionAxModule extends PLModule $page->assign('nbMissmatchingPromos1', $res->total()); $page->assign('missmatchingPromos1', $res); - $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo + $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg AND groupe_promo = 'X' ORDER BY promo_etude_xorg"); @@ -526,7 +543,7 @@ class FusionAxModule extends PLModule $page->assign('nbMissmatchingPromos2', $res->total()); $page->assign('missmatchingPromos2', $res); - $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo + $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg AND groupe_promo = 'X' ORDER BY promo_etude_xorg"); @@ -534,7 +551,7 @@ class FusionAxModule extends PLModule $page->assign('nbMissmatchingPromos3', $res->total()); $page->assign('missmatchingPromos3', $res); - $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo + $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE groupe_promo = 'M' ORDER BY promo_etude_xorg"); @@ -543,7 +560,7 @@ class FusionAxModule extends PLModule $page->assign('missmatchingPromosM', $res); - $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo + $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE groupe_promo = 'D' ORDER BY promo_etude_xorg"); @@ -554,31 +571,173 @@ class FusionAxModule extends PLModule $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos); } - function handler_names($page, $action = '') + private function format($string) + { + return preg_replace('/(\s+|\-)/', '', $string); + } + + private function retrieve_firstnames() + { + $res = XDB::rawFetchAllAssoc('SELECT p.pid, p.ax_id, p.hrpid, + f.prenom, ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary + FROM fusionax_anciens AS f + INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) + INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid) + WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)'); + + $issues = array(); + foreach ($res as $item) { + if (!($item['firstname_ordinary'] != '' || $item['firstname_main'] != $item['firstname_initial'])) { + $ax = $this->format(mb_strtolower(replace_accent($item['prenom']))); + $xorg = $this->format(mb_strtolower(replace_accent($item['firstname_main']))); + if ($ax != $xorg) { + $issues[] = $item; + } + } + } + + return $issues; + } + + function handler_names($page, $action = '', $csv = false) { $page->changeTpl('fusionax/names.tpl'); - $res = XDB::query('SELECT COUNT(*) - FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)'); - $page->assign('total', $res->fetchOneCell()); - - $res = XDB::rawFetchOneCell("SELECT COUNT(*) - FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid) - WHERE IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary) - OR IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary) - OR f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary)"); - $page->assign('lastnameIssues', $res); - - $res = XDB::rawFetchOneCell('SELECT COUNT(*) - FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid) - WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)'); - $page->assign('firstnameIssues', $res); + if ($action == 'first') { + $res = $this->retrieve_firstnames(); + if ($csv) { + pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'firstnames.csv'); + $csv = fopen('php://output', 'w'); + fputcsv($csv, array('pid', 'ax_id', 'hrpid', 'AX', 'initial', 'principal', 'ordinaire'), ';'); + foreach ($res as $item) { + fputcsv($csv, $item, ';'); + } + fclose($csv); + exit(); + } else { + $page->assign('firstnameIssues', $res); + } + } elseif ($action == 'last' || $action == 'last3' || $action == 'last2' || $action == 'last1') { + // Define some variables to build queries + function sql_trim_partic($sqlstring) { + $sqlstring = 'TRIM(LEADING \'d\\\'\' FROM ' . $sqlstring . ')'; + $sqlstring = 'TRIM(LEADING \'D\\\'\' FROM ' . $sqlstring . ')'; + $sqlstring = 'TRIM(LEADING \'de \' FROM ' . $sqlstring . ')'; + $sqlstring = 'TRIM(LEADING \'De \' FROM ' . $sqlstring . ')'; + $sqlstring = 'TRIM(LEADING \'du \' FROM ' . $sqlstring . ')'; + $sqlstring = 'TRIM(LEADING \'Du \' FROM ' . $sqlstring . ')'; + return $sqlstring; + } + //$field_ax_patro = 'IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(\' \', f.Nom_patronymique)), f.Nom_patronymique)'; + //$field_ax_usuel = 'IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(\' \', f.Nom_usuel)), f.Nom_usuel)'; + $fields_p_list = '(' . \ + sql_trim_partic('ppn.lastname_initial') . ', ' . \ + sql_trim_partic('ppn.lastname_main') . ', ' . \ + sql_trim_partic('ppn.lastname_marital') . ', ' . \ + sql_trim_partic('ppn.lastname_ordinary') . ')'; + $ax_patro = '(' . sql_trim_partic('f.Nom_patronymique') . ' NOT IN ' . $fields_p_list . ')'; + $ax_ordinary = '(' . sql_trim_partic('f.Nom_usuel') . ' NOT IN ' . $fields_p_list . ')'; + $ax_full = '(' . sql_trim_partic('f.Nom_complet') . ' NOT IN ' . $fields_p_list . ')'; + + switch ($action) { + case 'last': + $where = $ax_patro . ' OR ' . $ax_ordinary . ' OR ' . $ax_full; + break; + case 'last3': + $where = $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full; + break; + case 'last2': + $where = '(' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR (' + . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR (' + . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND ' . $ax_full . ')'; + break; + case 'last1': + $where = '(' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND NOT ' . $ax_full . ') OR (' + . 'NOT ' . $ax_patro . ' AND NOT ' . $ax_ordinary . ' AND ' . $ax_full . ') OR (' + . 'NOT ' . $ax_patro . ' AND ' . $ax_ordinary . ' AND NOT ' . $ax_full . ')'; + break; + } + + $res = XDB::rawFetchAllAssoc('SELECT p.pid, p.ax_id, p.hrpid, + f.Nom_patronymique, f.Nom_usuel, f.Nom_complet, + ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary, + ' . $ax_patro . ' AS cond_patro, + ' . $ax_ordinary . ' AS cond_ordinary, + ' . $ax_full . ' AS cond_full + FROM fusionax_anciens AS f + INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) + INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid) + WHERE ' . $where . ' + ORDER BY p.ax_id'); + + if ($csv) { + function format($string) + { + $string = preg_replace('/\-/', ' ', $string); + return preg_replace('/\s+/', ' ', $string); + } + + + pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'lastnames.csv'); + + $csv = fopen('php://output', 'w'); + fputcsv($csv, array( + 'pid', 'ax_id', 'hrpid', + 'AX patro', 'AX usuel', 'AX complet', + 'initial', 'principal', 'marital', 'ordinaire', + 'pb patro', 'pb usuel', 'pb complet'), ';'); + foreach ($res as $item) { + $ax = array( + 'Nom_patronymique' => format(mb_strtolower(replace_accent($item['Nom_patronymique']))), + 'Nom_usuel' => format(mb_strtolower(replace_accent($item['Nom_usuel']))), + 'Nom_complet' => format(mb_strtolower(replace_accent($item['Nom_complet']))) + ); + $xorg = array( + 'lastname_initial' => format(mb_strtolower(replace_accent($item['lastname_initial']))), + 'lastname_main' => format(mb_strtolower(replace_accent($item['lastname_main']))), + 'lastname_ordinary' => format(mb_strtolower(replace_accent($item['lastname_ordinary']))) + ); + + if (!in_array($ax['Nom_patronymique'], $xorg) || !in_array($ax['Nom_usuel'], $xorg) || !in_array($ax['Nom_complet'], $xorg)) { + fputcsv($csv, $item, ';'); + } + } + fclose($csv); + exit(); + } else { + $page->assign('lastnameIssues', $res); + $page->assign('total', count($res)); + $page->assign('issuesTypes', array( + 'last' => "1, 2 ou 3 noms de l'AX manquant", + 'last1' => "1 nom de l'AX manquant", + 'last2' => "2 noms de l'AX manquant", + 'last3' => "3 noms de l'AX manquant" + )); + } + } else { + $res = XDB::query('SELECT COUNT(*) + FROM fusionax_anciens AS f + INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)'); + $page->assign('total', $res->fetchOneCell()); + + $res = XDB::rawFetchOneCell("SELECT COUNT(*) + FROM fusionax_anciens AS f + INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) + INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid) + WHERE IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary) + OR IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary) + OR f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary)"); + $page->assign('lastnameIssues', $res); + + $res = XDB::rawFetchOneCell('SELECT COUNT(*) + FROM fusionax_anciens AS f + INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) + INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid) + WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)'); + $page->assign('firstnameIssues', count($this->retrieve_firstnames())); + } + $page->assign('action', $action); } function handler_edu($page, $action = '') @@ -766,5 +925,5 @@ class FusionAxModule extends PLModule } } -// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: +// vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8: ?>