Fusion: strip leading particules from names when comparing them

[platal.git] / modules / fusionax.php

diff --git a/modules/fusionax.php b/modules/fusionax.php
index d223feb..31f6fd4 100644 (file)
--- a/modules/fusionax.php
+++ b/modules/fusionax.php
@@ -310,7 +310,7 @@ class FusionAxModule extends PLModule
      * (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
@@ -517,7 +517,7 @@ class FusionAxModule extends PLModule
     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)
@@ -527,7 +527,7 @@ class FusionAxModule extends PLModule
         $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");
@@ -535,7 +535,7 @@ class FusionAxModule extends PLModule
         $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");
@@ -543,7 +543,7 @@ class FusionAxModule extends PLModule
         $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");
@@ -551,7 +551,7 @@ class FusionAxModule extends PLModule
         $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");
@@ -560,7 +560,7 @@ class FusionAxModule extends PLModule
         $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");
@@ -619,9 +619,26 @@ class FusionAxModule extends PLModule
                 $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':
@@ -644,11 +661,15 @@ class FusionAxModule extends PLModule
 
             $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)
@@ -661,7 +682,11 @@ class FusionAxModule extends PLModule
                 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']))),