<?php
/***************************************************************************
- * Copyright (C) 2003-2010 Polytechnique.org *
+ * Copyright (C) 2003-2014 Polytechnique.org *
* http://opensource.polytechnique.org/ *
* *
* This program is free software; you can redistribute it and/or modify *
***************************************************************************/
/**
- * @brief Module to merge data from AX database
+ * Module to merge data from AX database: this will only be used once on
+ * production site and should be removed afterwards.
*
* Module to import data from another database of alumni that had
* different schemas. The organization that used this db is called AX
* hence the name of this module.
*
- * Datas are stored in an external server and you need a private key
- * to connect to their server.
+ * Datas are stored in an export file.
*/
class FusionAxModule extends PLModule
{
- function __construct()
- {
- }
-
function handlers()
{
- return array(
- 'fusionax' => $this->make_hook('index', AUTH_MDP, 'admin'),
- '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'),
- );
+ if (Platal::globals()->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)
+ function handler_index($page)
{
- $globals = Platal::globals();
-
- $page->changeTpl('fusionax/index.tpl');
- $page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires');
- if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
- $page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate));
+ 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', $fileSQL = '')
+ function handler_import($page, $action = 'index', $file = '')
{
- $globals = Platal::globals();
-
+ global $globals;
if ($action == 'index') {
$page->changeTpl('fusionax/import.tpl');
- if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
- $page->assign(
- 'lastimport',
- "le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate));
- }
return;
}
// toutes les actions sont faites en ajax en utilisant jquery
- header("Content-type: text/javascript; charset=utf-8");
+ header('Content-type: text/javascript; charset=utf-8');
// log des actions
$report = array();
- // création d'un fichier temporaire si nécessaire
- if (Env::has('tmpdir')) {
- $tmpdir = Env::v('tmpdir');
- } else {
- $tmpdir = tempnam('/tmp', 'fusionax');
- unlink($tmpdir);
- mkdir($tmpdir);
- chmod($tmpdir, 0700);
- }
-
$modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
- $olddir = getcwd();
- chdir($tmpdir);
+ $spoolpath = realpath(dirname(__FILE__) . '/../spool/fusionax/') . '/';
if ($action == 'launch') {
- // séparation de l'archive en fichiers par tables
- exec($modulepath . 'import-ax.sh', $report);
- $report[] = 'Fichier parsé.';
- $report[] = 'Import dans la base en cours...';
- $next = 'integrateSQL';
+ 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(
- 'Activites.sql',
- 'Adresses.sql',
- 'Anciens.sql',
- 'Formations.sql',
- 'Entreprises.sql');
- if ($fileSQL != '') {
+ 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 . $fileSQL));
+ $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
$report[] = addslashes($l);
}
// exécute la requête
- XDB::execute($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
- $trans = array_flip($filesSQL);
- $nextfile = $trans[$fileSQL] + 1;
+ $nextfile = $file + 1;
} else {
$nextfile = 0;
}
- if (!isset($filesSQL[$nextfile])) {
- // tous les fichiers ont été exécutés, on passe à l'étape
- // suivante
- $next = 'clean';
+ 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/' . $filesSQL[$nextfile];
+ $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
- chdir($olddir);
- exec("rm -Rf $tmpdir", $report);
- $report[] = "Fin de l\'import";
- // met à jour la date de dernier import
- //$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx');
+ //exec('rm -Rf ' . $spoolpath);
+ $report[] = 'Import finit.';
}
foreach($report as $t) {
// affiche les lignes de report
- echo "$('#fusionax_import').append('" . $t . "<br/>');\n";
+ echo "$('#fusionax').append('" . $t . "<br/>');\n";
}
if (isset($next)) {
- $tmpdir = getcwd();
- chdir($olddir);
// lance le prochain script s'il y en a un
- echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');";
+ echo "$.getScript('fusionax/import/" . $next . "');";
}
// exit pour ne pas afficher la page template par défaut
exit;
}
- /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
- function handler_view(&$page, $action = '')
+ function handler_view($page, $action = '')
{
- $globals = Platal::globals();
-
$page->changeTpl('fusionax/view.tpl');
if ($action == 'create') {
XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
- XDB::execute('CREATE VIEW fusionax_deceased AS
- SELECT u.user_id, a.id_ancien, u.nom, u.prenom, u.promo, u.deces AS deces_xorg, a.Date_deces AS deces_ax
- FROM auth_user_md5 AS u
- INNER JOIN fusionax_anciens AS a ON (a.id_ancien = u.matricule_ax)
- WHERE u.deces != a.Date_deces');
+ 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 u.user_id, u.matricule_ax, CONCAT(u.nom, " ", u.prenom) AS display_name, u.promo AS promo_etude_xorg,
- f.promotion_etude AS promo_etude_ax, u.promo_sortie AS promo_sortie_xorg
- FROM auth_user_md5 AS u
- INNER JOIN fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien)
- WHERE u.promo != f.promotion_etude AND !(f.promotion_etude = u.promo + 1 AND u.promo_sortie = u.promo + 4)');
+ 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($user_id)
+ private static function clear_wrong_in_xorg($pid)
{
- $res = XDB::execute("UPDATE fusionax_xorg_anciens
- SET matricule_ax = NULL
- WHERE user_id = {?}", $user_id);
+ $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 et 1923 qui ne figurent pas dans les données de l'AX)*/
+ /* 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.user_id, u.display_name
+ 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.id_ancien = u.matricule_ax)
- AND u.matricule_ax IS NOT NULL AND promo != 1921 AND promo != 1923");
+ 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 matricule_ax identifiant dans l'annuaire de l'AX
* @return 0 si la liaison a échoué, 1 sinon
*/
- private static function link_by_ids($user_id, $matricule_ax)
+ private static function link_by_ids($pid, $ax_id)
{
- $res = XDB::execute("UPDATE fusionax_import AS i
+ $res = XDB::execute('UPDATE fusionax_import AS i
INNER JOIN fusionax_xorg_anciens AS u
- SET u.matricule_ax = i.id_ancien,
- i.user_id = u.user_id,
+ SET u.ax_id = i.ax_id,
+ i.pid = u.pid,
i.date_match_id = NOW()
- WHERE i.id_ancien = {?} AND u.user_id = {?}
- AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL
- OR i.user_id != {?} OR i.user_id IS NULL)",
- $matricule_ax, $user_id, $matricule_ax, $user_id);
+ 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;
}
* @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,
- * user_id, id_ancien et display_name_ax
+ * 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.display_name, u.promo, u.user_id, ax.id_ancien,
- CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
+ 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.id_ancien = ax.id_ancien AND i.user_id IS NULL)
- LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
- AND ax.Nom_patronymique = u.nom
- AND ax.prenom = u.prenom
- AND u.promo = ax.promotion_etude)
- GROUP BY u.user_id
- HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
+ 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.display_name, u.promo, u.user_id, ax.id_ancien,
- CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
+ 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.id_ancien = ax.id_ancien AND i.user_id IS NULL)
- LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
- AND (ax.Nom_patronymique = u.nom
- OR ax.Nom_patronymique LIKE CONCAT(u.nom, ' %')
- OR ax.Nom_patronymique LIKE CONCAT(u.nom, '-%')
- OR ax.Nom_usuel = u.nom
- OR u.nom LIKE CONCAT('% ', ax.Nom_patronymique))
- AND u.promo < ax.promotion_etude + 2
- AND u.promo > ax.promotion_etude - 2)
- GROUP BY u.user_id
- HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
+ 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', $user_id = null, $matricule_ax = null)
+ function handler_ids($page, $part = 'main', $pid = null, $ax_id = null)
{
- $globals = Platal::globals();
$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, user_id, display_name
+ $missingInAX = XDB::iterator('SELECT promo, pid, private_name
FROM fusionax_xorg_anciens
- WHERE matricule_ax IS NULL");
+ 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 a.promotion_etude AS promo,
- CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name,
- a.id_ancien
+ $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 (id_ancien)
- WHERE fusionax_import.user_id IS NULL");
+ 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 == 'cleanwronginxorg') {
$linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
while ($l = $linksToDo->next()) {
- FusionAxModule::clear_wrong_in_xorg($l['user_id']);
+ 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('user_id'), Post::v('matricule_ax'));
+ FusionAxModule::link_by_ids(Post::i('pid'), Post::v('ax_id'));
}
}
if ($part == 'link') {
- FusionAxModule::link_by_ids($user_id, $matricule_ax);
+ 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['user_id'], $l['id_ancien']);
+ 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['user_id'], $l['id_ancien']);
+ FusionAxModule::link_by_ids($l['pid'], $l['ax_id']);
}
}
{
$page->changeTpl('fusionax/ids.tpl');
$missingInAX = XDB::query('SELECT COUNT(*)
- FROM fusionax_xorg_anciens AS u
- WHERE u.matricule_ax IS NULL');
+ FROM fusionax_xorg_anciens
+ WHERE ax_id IS NULL');
if ($missingInAX) {
$page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
}
$missingInXorg = XDB::query('SELECT COUNT(*)
- FROM fusionax_import AS i
- WHERE i.user_id IS NULL');
+ FROM fusionax_import
+ WHERE pid IS NULL');
if ($missingInXorg) {
$page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
}
}
}
- 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
WHERE deces_ax = "0000-00-00"');
}
if ($action == 'update') {
- if (Post::has('user_id') && Post::has('date')) {
+ if (Post::has('pid') && Post::has('date')) {
XDB::execute('UPDATE fusionax_deceased
SET deces_ax = {?}, deces_xorg = {?}
- WHERE user_id = {?}',
- Post::v('date'), Post::v('date'), Post::i('user_id'));
+ 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 d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax,
- CONCAT(d.prenom, " ", d.nom) AS display_name
- FROM fusionax_deceased AS d
- WHERE d.deces_xorg = "0000-00-00"
+ $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 d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg,
- CONCAT(d.prenom, " ", d.nom) AS display_name
- FROM fusionax_deceased AS d
- WHERE d.deces_ax = "0000-00-00"
+ $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 d.user_id, d.id_ancien, d.nom, d.prenom, d.promo,
- d.deces_ax, d.deces_xorg,
- CONCAT(d.prenom, " ", d.nom, " ", d.user_id) AS display_name
- FROM fusionax_deceased AS d
- WHERE d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"');
+ $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 = '')
+ function handler_promo($page, $action = '')
{
$page->changeTpl('fusionax/promo.tpl');
- $res = XDB::iterator('SELECT user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax
+ $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)');
+ 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 user_id, display_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax
+
+ $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');
+ 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);
- $page->assign('nbMissmatchingPromos', $nbMissmatchingPromos);
+
+ $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 enc=utf-8:?>
+// vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8:
+?>
projects / platal.git / blobdiff