X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=modules%2Ffusionax.php;h=31f6fd433afcd89f61ea3f1fc361c8ce80f95c69;hb=8526cfe046865202710c548a3a58c7dbddeaee65;hp=16aa4882cfed4666b517d31207aa49bbd13ae554;hpb=cfceef1ef8148bef4f7d1319ceabe43096110332;p=platal.git diff --git a/modules/fusionax.php b/modules/fusionax.php index 16aa488..31f6fd4 100644 --- a/modules/fusionax.php +++ b/modules/fusionax.php @@ -1,6 +1,6 @@ changeTpl('fusionax/import.tpl'); return; @@ -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 { @@ -229,75 +239,6 @@ class FusionAxModule extends PLModule $uid, $pid, 'owner'); } $report[] = 'Promo 1920 ajoutée.'; - $next = 'adds2011'; - } elseif ($action == 'adds2011') { - // Adds promotion 2011 from AX db. - $report[] = 'Ajout des élèves manquant de la promotion 2011'; - $res = XDB::iterator("SELECT prenom, Nom_complet, ax_id, Civilite - FROM fusionax_anciens - WHERE promotion_etude = 2011 AND groupe_promo = 'X' - AND NOT EXISTS (SELECT 1 - FROM profiles - WHERE profiles.ax_id = fusionax_anciens.ax_id)"); - - $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS); - $eduSchools = array_flip($eduSchools); - $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES); - $eduDegrees = array_flip($eduDegrees); - $degreeid = $eduDegrees[Profile::DEGREE_X]; - $entry_year = 2011; - $grad_year = 2014; - $promo = 'X2011'; - $hrpromo = '2011'; - $type = 'x'; - - while ($new = $res->next()) { - $firstname = $new['prenom']; - $lastname = $new['Nom_complet']; - $ax_id = $new['ax_id']; - $civilite = $new['Civilite']; - $hrid = User::makeHrid($firstname, $lastname, $hrpromo); - $res1 = XDB::query('SELECT COUNT(*) - FROM accounts - WHERE hruid = {?}', $hrid); - $res2 = XDB::query('SELECT COUNT(*) - FROM profiles - WHERE hrpid = {?}', $hrid); - if (is_null($hrid) || $res1->fetchOneCell() > 0 || $res2->fetchOneCell() > 0) { - $report[] = $ax_id . ' non ajouté'; - } - $fullName = $firstname . ' ' . $lastname; - $directoryName = $lastname . ' ' . $firstname; - if ($civilite == 'M') { - $sex = 'male'; - } else { - $sex = 'female'; - } - - XDB::execute('INSERT INTO profiles (hrpid, ax_id, sex, title) - VALUES ({?}, {?}, {?}, {?})', - $hrid, $ax_id, $sex, $civilite); - $pid = XDB::insertId(); - 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 ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})', - $pid, $firstname, $fullName, $fullName, $directoryName, $fullName, $directoryName, $promo); - XDB::execute('INSERT INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags) - 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, 'pending', $fullName, $directoryName, $firstname, $lastname, $firstname, $sex); - $uid = XDB::insertId(); - XDB::execute('INSERT INTO account_profiles (uid, pid, perms) - VALUES ({?}, {?}, {?})', - $uid, $pid, 'owner'); - } - $report[] = 'Promo 2011 ajoutée.'; - $next = 'view'; } elseif ($action == 'view') { XDB::execute('CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS @@ -366,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 @@ -575,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) @@ -585,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"); @@ -593,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"); @@ -601,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"); @@ -609,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"); @@ -618,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"); @@ -677,9 +619,26 @@ class FusionAxModule extends PLModule $page->assign('firstnameIssues', $res); } } elseif ($action == 'last' || $action == 'last3' || $action == 'last2' || $action == 'last1') { - $ax_patro = "(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))"; - $ax_ordinary = "(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))"; - $ax_full = "(f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))"; + // 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': @@ -702,11 +661,15 @@ class FusionAxModule extends PLModule $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 + 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); + WHERE ' . $where . ' + ORDER BY p.ax_id'); if ($csv) { function format($string) @@ -719,7 +682,11 @@ class FusionAxModule extends PLModule 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'), ';'); + 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']))), @@ -958,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: ?>