X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=modules%2Ffusionax.php;h=892b3934e3ddda5379cdc1ad422ec3fdcae3923e;hb=96b778a679b261235ede8c5c4e7356680f4e5895;hp=dfde915f7d14c39e00b7f7525e22cb2b9c6c3125;hpb=654caec538912e37949b1942f81b8e7d0e2b2deb;p=platal.git diff --git a/modules/fusionax.php b/modules/fusionax.php index dfde915..892b393 100644 --- a/modules/fusionax.php +++ b/modules/fusionax.php @@ -1,6 +1,6 @@ merge->state == 'pending') { - $auth = 'admin'; + 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') { - $auth = 'admin,edit_directory'; + 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'), + ); } - - return array( - 'fusionax' => $this->make_hook('index', AUTH_MDP, $auth), - 'fusionax/import' => $this->make_hook('import', AUTH_MDP, 'admin'), - 'fusionax/view' => $this->make_hook('view', AUTH_MDP, 'admin'), - 'fusionax/ids' => $this->make_hook('ids', AUTH_MDP, 'admin'), - 'fusionax/deceased' => $this->make_hook('deceased', AUTH_MDP, 'admin'), - 'fusionax/promo' => $this->make_hook('promo', AUTH_MDP, 'admin'), - 'fusionax/names' => $this->make_hook('names', AUTH_MDP, 'admin'), - - 'fusionax/deathdate_issues' => $this->make_hook('deathdate_issue', AUTH_MDP, 'admin,edit_directory'), - 'fusionax/promo_issues' => $this->make_hook('promo_issue', AUTH_MDP, 'admin,edit_directory'), - 'fusionax/name_issues' => $this->make_hook('name_issue', AUTH_MDP, 'admin,edit_directory'), - 'fusionax/phone_issues' => $this->make_hook('phone_issue', AUTH_MDP, 'admin,edit_directory'), - 'fusionax/education_issues' => $this->make_hook('education_issue', AUTH_MDP, 'admin,edit_directory'), - 'fusionax/address_issues' => $this->make_hook('address_issue', AUTH_MDP, 'admin,edit_directory'), - 'fusionax/job_issues' => $this->make_hook('job_issue', AUTH_MDP, 'admin,edit_directory'), - ); } - function handler_index(&$page) + 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, @@ -74,13 +80,14 @@ class FusionAxModule extends PLModule SUM(FIND_IN_SET('education', issues)) DIV 7 AS education FROM profile_merge_issues WHERE issues IS NOT NULL OR issues != ''"); - $page->assign('issues', $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 = '') + function handler_import($page, $action = 'index', $file = '') { if ($action == 'index') { $page->changeTpl('fusionax/import.tpl'); @@ -104,19 +111,22 @@ class FusionAxModule extends PLModule } else { // séparation de l'archive en fichiers par tables $file = $spoolpath . $file; - // Removes master and doctorate students - exec('grep -v "^[A-Z]\{2\}.[0-9]\{4\}[MD][0-9]\{3\}" ' . $file . ' > ' . $file . '.tmp'); - exec('mv -f ' . $file . '.tmp ' . $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') { @@ -127,7 +137,8 @@ class FusionAxModule extends PLModule 1 => 'Adresses.sql', 2 => 'Anciens.sql', 3 => 'Formations.sql', - 4 => 'Entreprises.sql' + 4 => 'Entreprises.sql', + 5 => 'Formations_MD.sql' ); if ($file != '') { // récupère le contenu du fichier sql @@ -150,7 +161,7 @@ class FusionAxModule extends PLModule } else { $nextfile = 0; } - if ($nextfile > 4) { + if ($nextfile > 5) { // tous les fichiers ont été exécutés, on passe à l'étape suivante $next = 'adds1920'; } else { @@ -164,8 +175,6 @@ class FusionAxModule extends PLModule FROM fusionax_anciens WHERE promotion_etude = 1920;'); - $nameTypes = DirEnum::getOptions(DirEnum::NAMETYPES); - $nameTypes = array_flip($nameTypes); $eduSchools = DirEnum::getOptions(DirEnum::EDUSCHOOLS); $eduSchools = array_flip($eduSchools); $eduDegrees = DirEnum::getOptions(DirEnum::EDUDEGREES); @@ -174,12 +183,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); @@ -193,43 +206,109 @@ class FusionAxModule extends PLModule $directoryName = $lastname . ' ' . $firstname; ++$xorgId; - XDB::execute('REPLACE INTO profiles (hrpid, xorg_id, ax_id, sex) - VALUES ({?}, {?}, {?}, {?})', + XDB::execute('INSERT INTO profiles (hrpid, xorg_id, ax_id, sex) + VALUES ({?}, {?}, {?}, {?})', $hrid, $xorgId, $ax_id, $sex); $pid = XDB::insertId(); - XDB::execute('REPLACE INTO profile_name (pid, name, typeid) - VALUES ({?}, {?}, {?})', - $pid, $lastname, $nameTypes['name_ini']); - XDB::execute('REPLACE INTO profile_name (pid, name, typeid) - VALUES ({?}, {?}, {?})', - $pid, $firstname, $nameTypes['firstname_ini']); - XDB::execute('REPLACE INTO profile_display (pid, yourself, public_name, private_name, - directory_name, short_name, sort_name, promo) - VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?}, {?})', + 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('REPLACE INTO profile_education (pid, eduid, degreeid, entry_year, grad_year, flags) - VALUES ({?}, {?}, {?}, {?}, {?}, {?})', + 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('REPLACE INTO accounts (hruid, type, is_admin, state, full_name, directory_name, display_name, sex) - VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?})', - $hrid, $type, 0, 'active', $fullName, $directoryName, $lastname, $sex); + 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('REPLACE INTO account_profiles (uid, pid, perms) - VALUES ({?}, {?}, {?})', + 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 USING(pid)'); + INNER JOIN profile_display AS pd ON (pd.pid = p.pid)'); $next = 'clean'; } elseif ($action == 'clean') { // nettoyage du fichier temporaire - exec('rm -Rf ' . $spoolpath); + //exec('rm -Rf ' . $spoolpath); $report[] = 'Import finit.'; } foreach($report as $t) { @@ -244,27 +323,32 @@ class FusionAxModule extends PLModule exit; } - function handler_view(&$page, $action = '') + 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 + 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'); + 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, + 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) + 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 pd.promo != CONCAT(\'X\', f.promotion_etude) - AND !(f.promotion_etude = pe.entry_year + 1 AND pe.grad_year = pe.entry_year + 4)'); + 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.'); } } @@ -282,7 +366,7 @@ 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 et 1923 qui ne figurent pas dans les données 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 @@ -290,7 +374,7 @@ class FusionAxModule extends PLModule 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 != \'X1921\' AND promo != \'X1923\''); + 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 @@ -325,12 +409,12 @@ class FusionAxModule extends PLModule { $easy_to_link = XDB::iterator(" SELECT u.private_name, u.promo, u.pid, ax.ax_id, - CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax, + 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('X', ax.promotion_etude) + 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)) @@ -341,7 +425,7 @@ class FusionAxModule extends PLModule } return XDB::iterator(" SELECT u.private_name, u.promo, u.pid, ax.ax_id, - CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X', ax.promotion_etude, ')') AS display_name_ax, + 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) @@ -349,14 +433,14 @@ class FusionAxModule extends PLModule 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('X', ax.promotion_etude + 2) - AND u.promo > CONCAT('X', ax.promotion_etude - 2)) + 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) + function handler_ids($page, $part = 'main', $pid = null, $ax_id = null) { $nbToLink = 100; $page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple'); @@ -366,7 +450,8 @@ class FusionAxModule extends PLModule $page->changeTpl('fusionax/idsMissingInAx.tpl'); $missingInAX = XDB::iterator('SELECT promo, pid, private_name FROM fusionax_xorg_anciens - WHERE ax_id IS NULL'); + WHERE ax_id IS NULL + ORDER BY promo'); $page->assign('missingInAX', $missingInAX); return; } @@ -374,10 +459,11 @@ class FusionAxModule extends PLModule // 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, - a.promotion_etude AS promo, a.ax_id + 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"); + WHERE fusionax_import.pid IS NULL + ORDER BY promo"); $page->assign('missingInXorg', $missingInXorg); return; } @@ -443,12 +529,12 @@ class FusionAxModule extends PLModule } } - function handler_deceased(&$page, $action = '') + function handler_deceased($page, $action = '') { if ($action == 'updateXorg') { XDB::execute('UPDATE fusionax_deceased SET deces_xorg = deces_ax - WHERE deces_xorg = "0000-00-00"'); + WHERE deces_xorg IS NULL'); } if ($action == 'updateAX') { XDB::execute('UPDATE fusionax_deceased @@ -469,7 +555,7 @@ class FusionAxModule extends PLModule $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell()); $res = XDB::iterator('SELECT pid, ax_id, promo, private_name, deces_ax FROM fusionax_deceased - WHERE deces_xorg = "0000-00-00" + WHERE deces_xorg IS NULL LIMIT 10'); $page->assign('nbDeceasedMissingInXorg', $res->total()); $page->assign('deceasedMissingInXorg', $res); @@ -486,90 +572,391 @@ class FusionAxModule extends PLModule $page->assign('deceasedDifferent', $res); } - function handler_promo(&$page, $action = '') + 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, 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) - ORDER BY promo_etude_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 + $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 - ORDER BY promo_etude_xorg'); + 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 + $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 - ORDER BY promo_etude_xorg'); + 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 + $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 - ORDER BY promo_etude_xorg'); + 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); } - 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()); - - // To be checked: - // | lastname | 1 | - // | lastname_marital | 2 | - // | lastname_ordinary | 3 | - // | firstname | 4 | - // | firstname_ordinary | 7 | - // | firstname_other | 8 | - // | name_other | 9 | - // | name_ini | 10 | - // | firstname_ini | 11 | - $res = XDB::query("SELECT COUNT(*) - FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - LEFT JOIN profile_name AS pnp ON (p.pid = pnp.pid AND pnp.typeid = 1) - LEFT JOIN profile_name AS pnm ON (p.pid = pnm.pid AND pnm.typeid = 2) - LEFT JOIN profile_name AS pno ON (p.pid = pno.pid AND pno.typeid = 3) - LEFT JOIN profile_name AS pne ON (p.pid = pne.pid AND pne.typeid = 9) - LEFT JOIN profile_name AS pni ON (p.pid = pni.pid AND pni.typeid = 10) - WHERE IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name) - OR IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name) - OR f.Nom_complet NOT IN (pnp.name, pno.name, pnm.name, pne.name, pni.name)"); - $page->assign('lastnameIssues', $res->fetchOneCell()); - - $res = XDB::query('SELECT COUNT(*) - FROM fusionax_anciens AS f - INNER JOIN profiles AS p ON (f.ax_id = p.ax_id) - LEFT JOIN profile_name AS pnf ON (p.pid = pnf.pid AND pnf.typeid = 4) - LEFT JOIN profile_name AS pno ON (p.pid = pno.pid AND pno.typeid = 7) - LEFT JOIN profile_name AS pne ON (p.pid = pne.pid AND pne.typeid = 8) - LEFT JOIN profile_name AS pni ON (p.pid = pni.pid AND pni.typeid = 11) - WHERE f.prenom NOT IN (pnf.name, pno.name, pne.name, pni.name)'); - $page->assign('firstnameIssues', $res->fetchOneCell()); + 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 enc=utf-8: +// vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8: ?>