merge->state == 'pending') { return array( 'fusionax' => $this->make_hook('index', AUTH_PASSWD, 'admin'), 'fusionax/import' => $this->make_hook('import', AUTH_PASSWD, 'admin'), 'fusionax/view' => $this->make_hook('view', AUTH_PASSWD, 'admin'), 'fusionax/ids' => $this->make_hook('ids', AUTH_PASSWD, 'admin'), 'fusionax/deceased' => $this->make_hook('deceased', AUTH_PASSWD, 'admin'), 'fusionax/promo' => $this->make_hook('promo', AUTH_PASSWD, 'admin'), 'fusionax/names' => $this->make_hook('names', AUTH_PASSWD, 'admin'), 'fusionax/edu' => $this->make_hook('edu', AUTH_PASSWD, 'admin'), 'fusionax/corps' => $this->make_hook('corps', AUTH_PASSWD, 'admin') ); } elseif (Platal::globals()->merge->state == 'done') { return array( 'fusionax' => $this->make_hook('index', AUTH_PASSWD, 'admin,edit_directory'), 'fusionax/issues' => $this->make_hook('issues', AUTH_PASSWD, 'admin,edit_directory'), 'fusionax/issues/deathdate' => $this->make_hook('issues_deathdate', AUTH_PASSWD, 'admin,edit_directory'), 'fusionax/issues/promo' => $this->make_hook('issues_promo', AUTH_PASSWD, 'admin,edit_directory'), ); } } function handler_index($page) { if (Platal::globals()->merge->state == 'pending') { $page->changeTpl('fusionax/index.tpl'); } elseif (Platal::globals()->merge->state == 'done') { $issueList = array( 'name' => 'noms', 'job' => 'emplois', 'address' => 'adresses', 'promo' => 'promotions', 'deathdate' => 'dates de décès', 'phone' => 'téléphones', 'education' => 'formations', ); $issues = XDB::rawFetchOneAssoc("SELECT COUNT(*) AS total, SUM(FIND_IN_SET('name', issues)) DIV 1 AS name, SUM(FIND_IN_SET('job', issues)) DIV 2 AS job, SUM(FIND_IN_SET('address', issues)) DIV 3 AS address, SUM(FIND_IN_SET('promo', issues)) DIV 4 AS promo, SUM(FIND_IN_SET('deathdate', issues)) DIV 5 AS deathdate, SUM(FIND_IN_SET('phone', issues)) DIV 6 AS phone, SUM(FIND_IN_SET('education', issues)) DIV 7 AS education FROM profile_merge_issues WHERE issues IS NOT NULL OR issues != ''"); $page->changeTpl('fusionax/issues.tpl'); $page->assign('issues', $issues); $page->assign('issueList', $issueList); } } /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */ function handler_import($page, $action = 'index', $file = '') { if ($action == 'index') { $page->changeTpl('fusionax/import.tpl'); return; } // toutes les actions sont faites en ajax en utilisant jquery header('Content-type: text/javascript; charset=utf-8'); // log des actions $report = array(); $modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/'; $spoolpath = realpath(dirname(__FILE__) . '/../spool/fusionax/') . '/'; if ($action == 'launch') { if ($file == '') { $report[] = 'Nom de fichier non renseigné.'; } elseif (!file_exists(dirname(__FILE__) . '/../spool/fusionax/' . $file)) { $report[] = 'Le fichier ne se situe pas au bon endroit.'; } else { // séparation de l'archive en fichiers par tables $file = $spoolpath . $file; // Split export into specialised files exec('grep "^AD" ' . $file . ' > ' . $spoolpath . 'Adresses.txt'); exec('grep "^AN" ' . $file . ' > ' . $spoolpath . 'Anciens.txt'); exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Etudiant" ' . $file . ' > ' . $spoolpath . 'Formations_MD.txt'); exec('grep "^FO.[0-9]\{4\}[MD][0-9]\{3\}.Doct. de" ' . $file . ' >> ' . $spoolpath . 'Formations_MD.txt'); exec('grep "^FO" ' . $file . ' > ' . $spoolpath . 'Formations.txt'); exec('grep "^AC" ' . $file . ' > ' . $spoolpath . 'Activites.txt'); exec('grep "^EN" ' . $file . ' > ' . $spoolpath . 'Entreprises.txt'); exec($modulepath . 'formation.pl'); exec('mv -f ' . $spoolpath . 'Formations_out.txt ' . $spoolpath . 'Formations.txt'); exec('mv -f ' . $spoolpath . 'Formations_MD_out.txt ' . $spoolpath . 'Formations_MD.txt'); $report[] = 'Fichier parsé.'; $report[] = 'Import dans la base en cours...'; 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 // liste des fichiers sql à exécuter $filesSQL = array( 0 => 'Activites.sql', 1 => 'Adresses.sql', 2 => 'Anciens.sql', 3 => 'Formations.sql', 4 => 'Entreprises.sql', 5 => 'Formations_MD.sql' ); if ($file != '') { // récupère le contenu du fichier sql $queries = explode(';', file_get_contents($modulepath . $filesSQL[$file])); foreach ($queries as $q) { if (trim($q)) { // coupe le fichier en requêtes individuelles if (substr($q, 0, 2) == '--') { // affiche les commentaires dans le report $lines = explode("\n", $q); $l = $lines[0]; $report[] = addslashes($l); } // exécute la requête XDB::execute(str_replace('{?}', $spoolpath, $q)); } } // trouve le prochain fichier à exécuter $nextfile = $file + 1; } else { $nextfile = 0; } if ($nextfile > 5) { // tous les fichiers ont été exécutés, on passe à l'étape suivante $next = 'adds1920'; } else { // on passe au fichier suivant $next = 'integrateSQL/' . $nextfile; } } elseif ($action == 'adds1920') { // Adds promotion 1920 from AX db. $report[] = 'Ajout de la promotion 1920'; $res = XDB::iterator('SELECT prenom, Nom_complet, ax_id FROM fusionax_anciens WHERE promotion_etude = 1920;'); $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 = 1920; $grad_year = 1923; $promo = 'X1920'; $hrpromo = '1920'; $sex = 'male'; $xorgId = 19200000; $type = 'x'; 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); $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; ++$xorgId; XDB::execute('INSERT INTO profiles (hrpid, xorg_id, ax_id, sex) VALUES ({?}, {?}, {?}, {?})', $hrid, $xorgId, $ax_id, $sex); $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 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 SELECT p.pid, p.ax_id, pd.promo, pd.private_name, pd.public_name, pd.sort_name, pd.short_name, pd.directory_name FROM profiles AS p INNER JOIN profile_display AS pd ON (pd.pid = p.pid)'); $next = 'clean'; } elseif ($action == 'clean') { // nettoyage du fichier temporaire //exec('rm -Rf ' . $spoolpath); $report[] = 'Import finit.'; } foreach($report as $t) { // affiche les lignes de report echo "$('#fusionax').append('" . $t . "
');\n"; } if (isset($next)) { // lance le prochain script s'il y en a un echo "$.getScript('fusionax/import/" . $next . "');"; } // exit pour ne pas afficher la page template par défaut exit; } function handler_view($page, $action = '') { $page->changeTpl('fusionax/view.tpl'); if ($action == 'create') { XDB::execute('DROP VIEW IF EXISTS fusionax_deceased'); XDB::execute("CREATE VIEW fusionax_deceased AS SELECT p.pid, a.ax_id, pd.private_name, pd.promo, p.deathdate AS deces_xorg, a.Date_deces AS deces_ax FROM profiles AS p INNER JOIN profile_display AS pd ON (p.pid = pd.pid) INNER JOIN fusionax_anciens AS a ON (a.ax_id = p.ax_id) WHERE p.deathdate != a.Date_deces OR (p.deathdate IS NULL AND a.Date_deces != '0000-00-00')"); XDB::execute('DROP VIEW IF EXISTS fusionax_promo'); XDB::execute("CREATE VIEW fusionax_promo AS SELECT p.pid, p.ax_id, pd.private_name, pd.promo, pe.entry_year AS promo_etude_xorg, f.groupe_promo, 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 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 + 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"); $page->trigSuccess('Les VIEW ont bien été créées.'); } } /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */ private static function clear_wrong_in_xorg($pid) { $res = XDB::execute('UPDATE fusionax_xorg_anciens SET ax_id = NULL WHERE pid = {?}', $pid); if (!$res) { return 0; } return XDB::affectedRows() / 2; } /* 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)*/ private static function find_wrong_in_xorg($limit = 10) { return XDB::iterator('SELECT u.promo, u.pid, u.private_name 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\')'); } /** Lier les identifiants d'un ancien dans les deux annuaires * @param user_id identifiant dans l'annuaire X.org * @param matricule_ax identifiant dans l'annuaire de l'AX * @return 0 si la liaison a échoué, 1 sinon */ private static function link_by_ids($pid, $ax_id) { $res = XDB::execute('UPDATE fusionax_import AS i INNER JOIN fusionax_xorg_anciens AS u SET u.ax_id = i.ax_id, i.pid = u.pid, i.date_match_id = NOW() WHERE i.ax_id = {?} AND u.pid = {?} AND (u.ax_id != {?} OR u.ax_id IS NULL OR i.pid != {?} OR i.pid IS NULL)', $ax_id, $pid, $ax_id, $pid); if (!$res) { return 0; } return XDB::affectedRows() / 2; } /** Recherche automatique d'anciens à lier entre les deux annuaires * @param limit nombre d'anciens à trouver au max * @param sure si true, ne trouve que des anciens qui sont quasi sûrs * @return un XOrgDBIterator sur les entrées avec display_name, promo, * pid, ax_id et display_name_ax */ private static function find_easy_to_link($limit = 10, $sure = false) { $easy_to_link = XDB::iterator(" SELECT u.private_name, u.promo, u.pid, ax.ax_id, CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax, COUNT(*) AS nbMatches FROM fusionax_anciens AS ax INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL) LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL AND u.promo = CONCAT(ax.groupe_promo, ax.promotion_etude) AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name)) GROUP BY u.pid HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : '')); if ($easy_to_link->total() > 0 || $sure) { return $easy_to_link; } return XDB::iterator(" SELECT u.private_name, u.promo, u.pid, ax.ax_id, CONCAT(ax.prenom, ' ', ax.nom_complet, ' (', ax.groupe_promo, ax.promotion_etude, ')') AS display_name_ax, COUNT(*) AS nbMatches FROM fusionax_anciens AS ax INNER JOIN fusionax_import AS i ON (i.ax_id = ax.ax_id AND i.pid IS NULL) LEFT JOIN fusionax_xorg_anciens AS u ON (u.ax_id IS NULL AND (CONCAT(ax.prenom, ' ', ax.nom_complet) = u.private_name OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.public_name OR CONCAT(ax.prenom, ' ', ax.nom_complet) = u.short_name) AND u.promo < CONCAT(ax.groupe_promo, ax.promotion_etude + 2) AND u.promo > CONCAT(ax.groupe_promo, ax.promotion_etude - 2)) GROUP BY u.pid HAVING u.pid IS NOT NULL AND nbMatches = 1" . ($limit ? (' LIMIT ' . $limit) : '')); } /** Module de mise en correspondance les ids */ function handler_ids($page, $part = 'main', $pid = null, $ax_id = null) { $nbToLink = 100; $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple'); if ($part == 'missingInAX') { // locate all persons from this database that are not in AX's $page->changeTpl('fusionax/idsMissingInAx.tpl'); $missingInAX = XDB::iterator('SELECT promo, pid, private_name FROM fusionax_xorg_anciens WHERE ax_id IS NULL ORDER BY promo'); $page->assign('missingInAX', $missingInAX); return; } if ($part == 'missingInXorg') { // locate all persons from AX's database that are not here $page->changeTpl('fusionax/idsMissingInXorg.tpl'); $missingInXorg = XDB::iterator("SELECT CONCAT(a.prenom, ' ', a.Nom_usuel) AS private_name, CONCAT(a.groupe_promo, a.promotion_etude) AS promo, a.ax_id FROM fusionax_import INNER JOIN fusionax_anciens AS a USING (ax_id) WHERE fusionax_import.pid IS NULL ORDER BY promo"); $page->assign('missingInXorg', $missingInXorg); return; } if ($part == 'wrongInXorg') { // locate all persons from Xorg database that have a bad AX id $page->changeTpl('fusionax/idswrongInXorg.tpl'); $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink); $page->assign('wrongInXorg', $wrongInXorg); return; } if ($part == 'cleanwronginxorg') { $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink); while ($l = $linksToDo->next()) { FusionAxModule::clear_wrong_in_xorg($l['pid']); } pl_redirect('fusionax/ids/wrongInXorg'); } if ($part == 'lier') { if (Post::has('user_id') && Post::has('matricule_ax')) { FusionAxModule::link_by_ids(Post::i('pid'), Post::v('ax_id')); } } if ($part == 'link') { FusionAxModule::link_by_ids($pid, $ax_id); exit; } if ($part == 'linknext') { $linksToDo = FusionAxModule::find_easy_to_link($nbToLink); while ($l = $linksToDo->next()) { FusionAxModule::link_by_ids($l['pid'], $l['ax_id']); } pl_redirect('fusionax/ids#autolink'); } if ($part == 'linkall') { $linksToDo = FusionAxModule::find_easy_to_link(0); while ($l = $linksToDo->next()) { FusionAxModule::link_by_ids($l['pid'], $l['ax_id']); } } { $page->changeTpl('fusionax/ids.tpl'); $missingInAX = XDB::query('SELECT COUNT(*) FROM fusionax_xorg_anciens WHERE ax_id IS NULL'); if ($missingInAX) { $page->assign('nbMissingInAX', $missingInAX->fetchOneCell()); } $missingInXorg = XDB::query('SELECT COUNT(*) FROM fusionax_import WHERE pid IS NULL'); if ($missingInXorg) { $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell()); } $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink); if ($wrongInXorg->total() > 0) { $page->assign('wrongInXorg', $wrongInXorg->total()); } $easyToLink = FusionAxModule::find_easy_to_link($nbToLink); if ($easyToLink->total() > 0) { $page->assign('nbMatch', $easyToLink->total()); $page->assign('easyToLink', $easyToLink); } } } function handler_deceased($page, $action = '') { if ($action == 'updateXorg') { XDB::execute('UPDATE fusionax_deceased SET deces_xorg = deces_ax WHERE deces_xorg IS NULL'); } if ($action == 'updateAX') { XDB::execute('UPDATE fusionax_deceased SET deces_ax = deces_xorg WHERE deces_ax = "0000-00-00"'); } if ($action == 'update') { if (Post::has('pid') && Post::has('date')) { XDB::execute('UPDATE fusionax_deceased SET deces_ax = {?}, deces_xorg = {?} WHERE pid = {?}', Post::v('date'), Post::v('date'), Post::i('pid')); } } $page->changeTpl('fusionax/deceased.tpl'); // deceased $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased'); $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell()); $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_ax FROM fusionax_deceased WHERE deces_xorg IS NULL LIMIT 10'); $page->assign('nbDeceasedMissingInXorg', $res->total()); $page->assign('deceasedMissingInXorg', $res); $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg FROM fusionax_deceased WHERE deces_ax = "0000-00-00" LIMIT 10'); $page->assign('nbDeceasedMissingInAX', $res->total()); $page->assign('deceasedMissingInAX', $res); $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_xorg, deces_ax FROM fusionax_deceased WHERE deces_xorg != "0000-00-00" AND deces_ax != "0000-00-00"'); $page->assign('nbDeceasedDifferent', $res->total()); $page->assign('deceasedDifferent', $res); } 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 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) AND !(promo_etude_ax = promo_etude_xorg + 1) AND groupe_promo = 'X' ORDER BY promo_etude_xorg"); $nbMissmatchingPromos = $res->total(); $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 FROM fusionax_promo WHERE promo_etude_ax = promo_etude_xorg + 1 AND groupe_promo = 'X' ORDER BY promo_etude_xorg"); $nbMissmatchingPromos += $res->total(); $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 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"); $nbMissmatchingPromos += $res->total(); $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 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"); $nbMissmatchingPromos += $res->total(); $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 FROM fusionax_promo WHERE groupe_promo = 'M' ORDER BY promo_etude_xorg"); $nbMissmatchingPromos += $res->total(); $page->assign('nbMissmatchingPromosM', $res->total()); $page->assign('missmatchingPromosM', $res); $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo FROM fusionax_promo WHERE groupe_promo = 'D' ORDER BY promo_etude_xorg"); $nbMissmatchingPromos += $res->total(); $page->assign('nbMissmatchingPromosD', $res->total()); $page->assign('missmatchingPromosD', $res); $page->assign('nbMissmatchingPromosTotal', $nbMissmatchingPromos); } 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'); 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') { $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))"; 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 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); 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'), ';'); 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 = '') { $page->changeTpl('fusionax/education.tpl'); $missingEducation = XDB::rawIterator("SELECT DISTINCT(f.Intitule_formation) FROM fusionax_formations AS f WHERE f.Intitule_formation != '' AND NOT EXISTS (SELECT * FROM profile_education_enum AS e WHERE f.Intitule_formation = e.name)"); $missingDegree = XDB::rawIterator("SELECT DISTINCT(f.Intitule_diplome) FROM fusionax_formations AS f WHERE f.Intitule_diplome != '' AND NOT EXISTS (SELECT * FROM profile_education_degree_enum AS e WHERE f.Intitule_diplome = e.abbreviation)"); $missingCouple = XDB::rawIterator("SELECT DISTINCT(f.Intitule_formation) AS edu, f.Intitule_diplome AS degree, ee.id AS eduid, de.id AS degreeid FROM fusionax_formations AS f INNER JOIN profile_education_enum AS ee ON (f.Intitule_formation = ee.name) INNER JOIN profile_education_degree_enum AS de ON (f.Intitule_diplome = de.abbreviation) WHERE f.Intitule_diplome != '' AND f.Intitule_formation != '' AND NOT EXISTS (SELECT * FROM profile_education_degree AS d WHERE ee.id = d.eduid AND de.id = d.degreeid)"); $page->assign('missingEducation', $missingEducation); $page->assign('missingDegree', $missingDegree); $page->assign('missingCouple', $missingCouple); $page->assign('missingEducationCount', $missingEducation->total()); $page->assign('missingDegreeCount', $missingDegree->total()); $page->assign('missingCoupleCount', $missingCouple->total()); } function handler_corps($page) { $page->changeTpl('fusionax/corps.tpl'); $missingCorps = XDB::rawIterator('SELECT DISTINCT(f.corps_sortie) AS name FROM fusionax_anciens AS f WHERE NOT EXISTS (SELECT * FROM profile_corps_enum AS c WHERE f.corps_sortie = c.abbreviation)'); $missingGrade = XDB::rawIterator('SELECT DISTINCT(f.grade) AS name FROM fusionax_anciens AS f WHERE NOT EXISTS (SELECT * FROM profile_corps_rank_enum AS c WHERE f.grade = c.name)'); $page->assign('missingCorps', $missingCorps); $page->assign('missingGrade', $missingGrade); $page->assign('missingCorpsCount', $missingCorps->total()); $page->assign('missingGradeCount', $missingGrade->total()); } function handler_issues_deathdate($page, $action = '') { $page->changeTpl('fusionax/deathdate_issues.tpl'); if ($action == 'edit') { S::assert_xsrf_token(); $issues = XDB::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate FROM profile_merge_issues AS pm INNER JOIN profiles AS p ON (pm.pid = p.pid) INNER JOIN profile_display AS pd ON (pd.pid = p.pid) WHERE FIND_IN_SET(\'deathdate\', pm.issues) ORDER BY pd.directory_name'); while (list($pid, $name, $promo, $deathAX, $deathXorg) = $issues->next()) { $choiceAX = Post::has('AX_' . $pid); $choiceXorg = Post::has('XORG_' . $pid); if (!($choiceAX || $choiceXorg)) { continue; } if ($choiceAX) { XDB::execute('UPDATE profiles AS p INNER JOIN profile_merge_issues AS pm ON (pm.pid = p.pid) SET p.deathdate = pm.deathdate_ax, p.deathdate_rec = NOW() WHERE p.pid = {?}', $pid); } XDB::execute("UPDATE profile_merge_issues SET issues = REPLACE(issues, 'deathdate', '') WHERE pid = {?}", $pid()); $page->trigSuccess("La date de décès de $name ($promo) a bien été corrigée."); } } $issues = XDB::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.deathdate_ax, p.deathdate FROM profile_merge_issues AS pm INNER JOIN profiles AS p ON (pm.pid = p.pid) INNER JOIN profile_display AS pd ON (pd.pid = p.pid) WHERE FIND_IN_SET(\'deathdate\', pm.issues) ORDER BY pd.directory_name'); $page->assign('issues', $issues); $page->assign('total', count($issues)); } function handler_issues_promo($page, $action = '') { $page->changeTpl('fusionax/promo_issues.tpl'); if ($action == 'edit') { S::assert_xsrf_token(); $issues = XDB::rawIterRow('SELECT p.pid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year FROM profile_merge_issues AS pm INNER JOIN profiles AS p ON (pm.pid = p.pid) INNER JOIN profile_display AS pd ON (pd.pid = p.pid) INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags)) WHERE FIND_IN_SET(\'promo\', pm.issues) ORDER BY pd.directory_name'); while (list($pid, $name, $promo, $deathAX, $deathXorgEntry, $deathXorgGrad) = $issues->next()) { $choiceXorg = Post::has('XORG_' . $pid); if (!(Post::has('display_' . $pid) && Post::has('entry_' . $pid) && Post::has('grad_' . $pid))) { continue; } $display = Post::i('display_' . $pid); $entry = Post::i('entry_' . $pid); $grad = Post::i('grad_' . $pid); if (!(($grad <= $entry + 5 && $grad >= $entry + 3) && ($display >= $entry && $display <= $grad - 3))) { $page->trigError("La promotion de $name n'a pas été corrigée."); continue; } XDB::execute('UPDATE profile_display SET promo = {?} WHERE pid = {?}', 'X' . $display, $pid); XDB::execute('UPDATE profile_education SET entry_year = {?}, grad_year = {?} WHERE pid = {?} AND FIND_IN_SET(\'primary\', flags)', $entry, $grad, $pid); $page->trigSuccess("La promotion de $name a bien été corrigée."); } } $issues = XDB::rawFetchAllAssoc('SELECT p.pid, p.hrpid, pd.directory_name, pd.promo, pm.entry_year_ax, pe.entry_year, pe.grad_year FROM profile_merge_issues AS pm INNER JOIN profiles AS p ON (pm.pid = p.pid) INNER JOIN profile_display AS pd ON (pd.pid = p.pid) INNER JOIN profile_education AS pe ON (pe.pid = p.pid AND FIND_IN_SET(\'primary\', pe.flags)) WHERE FIND_IN_SET(\'promo\', pm.issues) ORDER BY pd.directory_name'); $page->assign('issues', $issues); $page->assign('total', count($issues)); } function handler_issues($page, $action = '') { static $issueList = array( 'name' => 'noms', 'phone' => 'téléphones', 'education' => 'formations', 'address' => 'adresses', 'job' => 'emplois' ); static $typeList = array( 'name' => 'general', 'phone' => 'general', 'education' => 'general', 'address' => 'adresses', 'job' => 'emploi' ); if (!array_key_exists($action, $issueList)) { pl_redirect('fusionax'); } else { $total = XDB::fetchOneCell('SELECT COUNT(*) FROM profile_merge_issues WHERE FIND_IN_SET({?}, issues)', $action); if ($total == 0) { pl_redirect('fusionax'); } $issues = XDB::fetchAllAssoc('SELECT p.hrpid, pd.directory_name, pd.promo FROM profile_merge_issues AS pm INNER JOIN profiles AS p ON (pm.pid = p.pid) INNER JOIN profile_display AS pd ON (pd.pid = p.pid) WHERE FIND_IN_SET({?}, pm.issues) ORDER BY pd.directory_name LIMIT 100', $action); $page->changeTpl('fusionax/other_issues.tpl'); $page->assign('issues', $issues); $page->assign('issue', $issueList[$action]); $page->assign('type', $typeList[$action]); $page->assign('total', $total); } } } // vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8: ?>