From 08aaf92e8048294f8e9e7a9adc3b3483e1e4faca Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Sat, 10 Dec 2011 17:25:01 +0100 Subject: [PATCH] Imports AX names. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- modules/fusionax.php | 60 ++++++++++++-- templates/fusionax/names.tpl | 11 ++- upgrade/1.0.1/merge_names.php | 186 ++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 249 insertions(+), 8 deletions(-) create mode 100755 upgrade/1.0.1/merge_names.php diff --git a/modules/fusionax.php b/modules/fusionax.php index 9d35861..16aa488 100644 --- a/modules/fusionax.php +++ b/modules/fusionax.php @@ -676,29 +676,77 @@ class FusionAxModule extends PLModule } 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(*) diff --git a/templates/fusionax/names.tpl b/templates/fusionax/names.tpl index 519d034..41f94fd 100644 --- a/templates/fusionax/names.tpl +++ b/templates/fusionax/names.tpl @@ -58,9 +58,16 @@ {/foreach} {/if} -{elseif $action eq "last"} +{elseif $action eq "last" || $action eq "last1" || $action eq "last2" || $action eq "last3"} +

{$issuesTypes.$action} : {$total}

+ {if $lastnameIssues|@count eq 0} -

Aucun problème avec les noms.

+

Aucun problème de ce type avec les noms.

{else}

Obtenir le csv des conflits.

diff --git a/upgrade/1.0.1/merge_names.php b/upgrade/1.0.1/merge_names.php new file mode 100755 index 0000000..c1c784b --- /dev/null +++ b/upgrade/1.0.1/merge_names.php @@ -0,0 +1,186 @@ +#!/usr/bin/php5 -q +debug = 0; // Do not store backtraces + +$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)"); + +function fix_ax_particles($ax, $xorg, $item) +{ + $count = 0; + foreach ($ax as $ax_key => $ax_name) { + if (!in_array($ax_name, $xorg)) { + $new_name = ''; + foreach ($xorg as $xorg_key => $xorg_name) { + if ($xorg_name && strpos($xorg_name, $ax_name) !== false) { + if ($xorg_name == 'de ' . $ax_name) { + $new_name = 'de ' . $item[$ax_key]; + } elseif ($xorg_name == "d'" . $ax_name) { + $new_name = "d'" . $item[$ax_key]; + } elseif ($xorg_name == 'du ' . $ax_name) { + $new_name = 'du ' . $item[$ax_key]; + } + + if ($new_name) { + XDB::execute("UPDATE fusionax_anciens + SET $ax_key = {?} + WHERE ax_id = {?}", + $new_name, $item['ax_id']); + ++$count; + break; + } + } + } + if ($new_name) { + continue; + } + foreach ($ax as $ax_key2 => $ax_name2) { + if ($ax_name2 && strpos($ax_name2, $ax_name) !== false) { + if ($ax_name2 == 'de ' . $ax_name) { + $new_name = 'de ' . $item[$ax_key]; + } elseif ($ax_name2 == "d'" . $ax_name) { + $new_name = "d'" . $item[$ax_key]; + } elseif ($ax_name2 == 'du ' . $ax_name) { + $new_name = 'du ' . $item[$ax_key]; + } + + if ($new_name) { + XDB::execute("UPDATE fusionax_anciens + SET $ax_key = {?} + WHERE ax_id = {?}", + $new_name, $item['ax_id']); + ++$count; + break; + } + } + } + } + } + + return $count; +} + +function fix_xorg_particles($ax, $xorg, $item) +{ + $count = 0; + foreach ($ax as $ax_key => $ax_name) { + if (!in_array($ax_name, $xorg)) { + foreach ($xorg as $xorg_key => $xorg_name) { + $new_name = ''; + if ($xorg_name && strpos($ax_name, $xorg_name) !== false) { + if ($ax_name == 'de ' . $xorg_name) { + $new_name = 'de ' . $item[$xorg_key]; + } elseif ($ax_name == "d'" . $xorg_name) { + $new_name = "d'" . $item[$xorg_key]; + } elseif ($ax_name == 'du ' . $xorg_name) { + $new_name = 'du ' . $item[$xorg_key]; + } + + if ($new_name) { + XDB::execute("UPDATE profile_public_names + SET $xorg_key = {?} + WHERE pid = {?}", + $new_name, $item['pid']); + ++$count; + break; + } + } + } + } + } + + return $count; +} + +function fix_xorg_full_name($ax, $xorg, $item) +{ + $new_name = ''; + if (!in_array($ax['Nom_complet'], $xorg) && strpos($ax['Nom_complet'], $xorg['lastname_main']) !== false && $xorg['lastname_ordinary'] == '') { + XDB::execute("UPDATE profile_public_names + SET lastname_main = {?}, lastname_ordinary = {?} + WHERE pid = {?}", + $item['Nom_complet'], $item['lastname_main'], $item['pid']); + return 1; + } + + return 0; +} + +function fix_xorg_ordinary_name($ax, $xorg, $item) +{ + $new_name = ''; + if (!in_array($ax['Nom_usuel'], $xorg) && $xorg['lastname_ordinary'] == '') { + XDB::execute("UPDATE profile_public_names + SET lastname_ordinary = {?} + WHERE pid = {?}", + $item['Nom_usuel'], $item['pid']); + return 1; + } + + return 0; +} + +function format($string) +{ + $string = preg_replace('/\-/', ' ', $string); + return preg_replace('/\s+/', ' ', $string); +} + +$updates_count = 0; +$count = 0; +$total = count($res); +foreach($res as $item) { + array_map('trim', $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']))) + ); + + $updates_count += fix_ax_particles($ax, $xorg, $item); + $updates_count += fix_xorg_particles($ax, $xorg, $item); + $updates_count += fix_xorg_full_name($ax, $xorg, $item); + $updates_count += fix_xorg_ordinary_name($ax, $xorg, $item); + printf("\r%u / %u", $count, $total); + ++$count; +} +printf("\r%u / %u\n\n", $count, $total); + +echo "Nombre de mises à jour effectuées : " . $updates_count . ".\n"; + +// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: +?> -- 2.1.4