<?php
/***************************************************************************
- * Copyright (C) 2003-2011 Polytechnique.org *
+ * Copyright (C) 2003-2014 Polytechnique.org *
* http://opensource.polytechnique.org/ *
* *
* This program is free software; you can redistribute it and/or modify *
/** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
function handler_import($page, $action = 'index', $file = '')
{
+ global $globals;
if ($action == 'index') {
$page->changeTpl('fusionax/import.tpl');
return;
if ($file != '') {
// récupère le contenu du fichier sql
$queries = explode(';', file_get_contents($modulepath . $filesSQL[$file]));
+ $db = mysqli_init();
+ $db->options(MYSQLI_OPT_LOCAL_INFILE, true);
+ $db->real_connect($globals->dbhost, $globals->dbuser, $globals->dbpwd, $globals->dbdb);
+ $db->autocommit(true);
+ $db->set_charset($globals->dbcharset);
foreach ($queries as $q) {
if (trim($q)) {
// coupe le fichier en requêtes individuelles
$report[] = addslashes($l);
}
// exécute la requête
- XDB::execute(str_replace('{?}', $spoolpath, $q));
+ $res = $db->query(str_replace('{?}', $spoolpath, $q));
+ if ($res === false) {
+ throw new XDBException($q, $db->error);
+ }
}
}
+ $db->close();
// trouve le prochain fichier à exécuter
$nextfile = $file + 1;
} else {
$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
$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 = 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)');
-
+ $res = $this->retrieve_firstnames();
if ($csv) {
pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'firstnames.csv');
} else {
$page->assign('firstnameIssues', $res);
}
- } elseif ($action == 'last') {
- $res = XDB::rawFetchAllAssoc("SELECT p.pid, p.ax_id, p.hrpid,
+ } 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 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)");
+ 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) {
- fputcsv($csv, $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(*)
INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
WHERE f.prenom NOT IN (ppn.firstname_initial, ppn.firstname_main, ppn.firstname_ordinary)');
- $page->assign('firstnameIssues', $res);
+ $page->assign('firstnameIssues', count($this->retrieve_firstnames()));
}
$page->assign('action', $action);
}
}
}
-// 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