$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
}
/* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
- * (mises à part les promo 1921, 1922, 1923, 1924, 1925, 1927 qui ne figurent pas dans les données de l'AX)*/
+ * (mises à part les promo 1921, 1922, 1923, 1924, 1925, 1927, 1928, 1929 qui ne figurent pas dans les données de l'AX)*/
private static function find_wrong_in_xorg($limit = 10)
{
- return XDB::iterator('SELECT u.promo, u.pid, u.private_name
+ return XDB::iterator('SELECT u.promo, u.pid, u.private_name, u.ax_id
FROM fusionax_xorg_anciens AS u
WHERE NOT EXISTS (SELECT *
FROM fusionax_anciens AS f
WHERE f.ax_id = u.ax_id)
- AND u.ax_id IS NOT NULL AND promo NOT IN (\'X1921\', \'X1922\', \'X1923\', \'X1924\', \'X1925\', \'X1927\')');
+ AND u.ax_id IS NOT NULL
+ AND promo NOT IN (\'X1921\', \'X1922\', \'X1923\', \'X1924\', \'X1925\', \'X1927\', \'X1928\', \'X1929\')');
}
/** Lier les identifiants d'un ancien dans les deux annuaires
function handler_promo($page, $action = '')
{
$page->changeTpl('fusionax/promo.tpl');
- $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
+ $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
FROM fusionax_promo
WHERE !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg)
AND !(promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg)
$page->assign('nbMissmatchingPromos', $res->total());
$page->assign('missmatchingPromos', $res);
- $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
+ $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
FROM fusionax_promo
WHERE promo_etude_ax = promo_etude_xorg + 1 AND groupe_promo = 'X'
ORDER BY promo_etude_xorg");
$page->assign('nbMissmatchingPromos1', $res->total());
$page->assign('missmatchingPromos1', $res);
- $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
+ $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
FROM fusionax_promo
WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 3 = promo_sortie_xorg AND groupe_promo = 'X'
ORDER BY promo_etude_xorg");
$page->assign('nbMissmatchingPromos2', $res->total());
$page->assign('missmatchingPromos2', $res);
- $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
+ $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
FROM fusionax_promo
WHERE promo_etude_ax + 1 = promo_etude_xorg AND promo_etude_xorg + 4 = promo_sortie_xorg AND groupe_promo = 'X'
ORDER BY promo_etude_xorg");
$page->assign('nbMissmatchingPromos3', $res->total());
$page->assign('missmatchingPromos3', $res);
- $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
+ $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
FROM fusionax_promo
WHERE groupe_promo = 'M'
ORDER BY promo_etude_xorg");
$page->assign('missmatchingPromosM', $res);
- $res = XDB::iterator("SELECT pid, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
+ $res = XDB::iterator("SELECT pid, ax_id, private_name, promo_etude_xorg, promo_sortie_xorg, promo_etude_ax, promo
FROM fusionax_promo
WHERE groupe_promo = 'D'
ORDER BY promo_etude_xorg");
$page->assign('firstnameIssues', $res);
}
} elseif ($action == 'last' || $action == 'last3' || $action == 'last2' || $action == 'last1') {
- $ax_patro = "(IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(' ', f.Nom_patronymique)), f.Nom_patronymique) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
- $ax_ordinary = "(IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(' ', f.Nom_usuel)), f.Nom_usuel) NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
- $ax_full = "(f.Nom_complet NOT IN (ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary))";
+ // Define some variables to build queries
+ function sql_trim_partic($sqlstring) {
+ $sqlstring = 'TRIM(LEADING \'d\\\'\' FROM ' . $sqlstring . ')';
+ $sqlstring = 'TRIM(LEADING \'D\\\'\' FROM ' . $sqlstring . ')';
+ $sqlstring = 'TRIM(LEADING \'de \' FROM ' . $sqlstring . ')';
+ $sqlstring = 'TRIM(LEADING \'De \' FROM ' . $sqlstring . ')';
+ $sqlstring = 'TRIM(LEADING \'du \' FROM ' . $sqlstring . ')';
+ $sqlstring = 'TRIM(LEADING \'Du \' FROM ' . $sqlstring . ')';
+ return $sqlstring;
+ }
+ //$field_ax_patro = 'IF(f.partic_patro, CONCAT(f.partic_patro, CONCAT(\' \', f.Nom_patronymique)), f.Nom_patronymique)';
+ //$field_ax_usuel = 'IF(f.partic_nom, CONCAT(f.partic_nom, CONCAT(\' \', f.Nom_usuel)), f.Nom_usuel)';
+ $fields_p_list = '(' . \
+ sql_trim_partic('ppn.lastname_initial') . ', ' . \
+ sql_trim_partic('ppn.lastname_main') . ', ' . \
+ sql_trim_partic('ppn.lastname_marital') . ', ' . \
+ sql_trim_partic('ppn.lastname_ordinary') . ')';
+ $ax_patro = '(' . sql_trim_partic('f.Nom_patronymique') . ' NOT IN ' . $fields_p_list . ')';
+ $ax_ordinary = '(' . sql_trim_partic('f.Nom_usuel') . ' NOT IN ' . $fields_p_list . ')';
+ $ax_full = '(' . sql_trim_partic('f.Nom_complet') . ' NOT IN ' . $fields_p_list . ')';
switch ($action) {
case 'last':
$res = XDB::rawFetchAllAssoc('SELECT p.pid, p.ax_id, p.hrpid,
f.Nom_patronymique, f.Nom_usuel, f.Nom_complet,
- ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary
+ ppn.lastname_initial, ppn.lastname_main, ppn.lastname_marital, ppn.lastname_ordinary,
+ ' . $ax_patro . ' AS cond_patro,
+ ' . $ax_ordinary . ' AS cond_ordinary,
+ ' . $ax_full . ' AS cond_full
FROM fusionax_anciens AS f
INNER JOIN profiles AS p ON (f.ax_id = p.ax_id)
INNER JOIN profile_public_names AS ppn ON (p.pid = ppn.pid)
- WHERE ' . $where);
+ WHERE ' . $where . '
+ ORDER BY p.ax_id');
if ($csv) {
function format($string)
pl_cached_content_headers('text/x-csv', 'utf-8', 1, 'lastnames.csv');
$csv = fopen('php://output', 'w');
- fputcsv($csv, array('pid', 'ax_id', 'hrpid', 'AX patro', 'AX usuel', 'AX complet', 'initial', 'principal', 'marital', 'ordinaire'), ';');
+ fputcsv($csv, array(
+ 'pid', 'ax_id', 'hrpid',
+ 'AX patro', 'AX usuel', 'AX complet',
+ 'initial', 'principal', 'marital', 'ordinaire',
+ 'pb patro', 'pb usuel', 'pb complet'), ';');
foreach ($res as $item) {
$ax = array(
'Nom_patronymique' => format(mb_strtolower(replace_accent($item['Nom_patronymique']))),
projects / platal.git / blobdiff