[platal.git] / modules / fusionax.php

<?php
/***************************************************************************
 *  Copyright (C) 2003-2007 Polytechnique.org                              *
 *  http://opensource.polytechnique.org/                                   *
 *                                                                         *
 *  This program is free software; you can redistribute it and/or modify   *
 *  it under the terms of the GNU General Public License as published by   *
 *  the Free Software Foundation; either version 2 of the License, or      *
 *  (at your option) any later version.                                    *
 *                                                                         *
 *  This program is distributed in the hope that it will be useful,        *
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
 *  GNU General Public License for more details.                           *
 *                                                                         *
 *  You should have received a copy of the GNU General Public License      *
 *  along with this program; if not, write to the Free Software            *
 *  Foundation, Inc.,                                                      *
 *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
 ***************************************************************************/

/**
 * @brief Module to merge data from AX database
 *
 * 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.
 */
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/ids'      => $this->make_hook('ids', AUTH_MDP, 'admin'),
            'fusionax/misc'     => $this->make_hook('misc', AUTH_MDP, 'admin'),
        );
    }


    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));
        }
    }

    /** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
    function handler_import(&$page, $action = 'index', $fileSQL = '')
    {
        $globals = Platal::globals();

        if ($action == 'index') {
            $page->changeTpl('fusionax/import.tpl');
            $page->addJsLink('jquery.js');
            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");

        // 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);

        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';
        } 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 != '') {
                // récupère le contenu du fichier sql
                $queries = explode(';', file_get_contents($modulepath . $fileSQL));
                foreach ($queries as $q) {
                    if (trim($q)) {
                        // coupe le fichier en requêtes individuelles
                        if (substr($q, 0, 2) == '--') {
                            // affiche les commentaires dans le report
                            $lines = explode("\n", $q);
                            $l = $lines[0];
                            $report[] = addslashes($l);
                        }
                        // exécute la requête
                        XDB::execute($q);
                    }
                }
                // trouve le prochain fichier à exécuter
                $trans = array_flip($filesSQL);
                $nextfile = $trans[$fileSQL] + 1;
            } else {
                $nextfile = 0;
            }
            if (!isset($filesSQL[$nextfile])) {
                // tous les fichiers ont été exécutés, on passe à l'étape
                // suivante
                $next = 'clean';
            } else {
                // on passe au fichier suivant
                $next = 'integrateSQL/' . $filesSQL[$nextfile];
            }
        } 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');
        }
        foreach($report as $t) {
            // affiche les lignes de report
            echo "$('#fusionax_import').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) . "');";
        }
        // exit pour ne pas afficher la page template par défaut
        exit;
    }

    /** Lier les identifiants d'un ancien dans les deux annuaires
     * @param user_id identifiant dans l'annuaire X.org
     * @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)
    {
        $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,
                                     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);
        if (!$res) {
            return 0;
        }
        return XDB::affectedRows() / 2;
    }

    /** Recherche automatique d'anciens à lier entre les deux annuaires
     * @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
     */
    private static function find_easy_to_link($limit = 10, $sure = false)
    {
        $easy_to_link = XDB::iterator("
        SELECT  xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien,
                CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', 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 xorg ON (xorg.matricule_ax IS NULL
                                                  AND ax.Nom_complet = xorg.nom
                                                  AND ax.prenom = xorg.prenom
                                                  AND xorg.promo = ax.promotion_etude)
      GROUP BY  xorg.user_id
        HAVING  xorg.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
        if ($easy_to_link->total() > 0 || $sure) {
            return $easy_to_link;
        }
        return XDB::iterator("
        SELECT  xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien,
                CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', 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 xorg ON (xorg.matricule_ax IS NULL
                                                  AND (ax.Nom_complet = xorg.nom
                                                       OR ax.Nom_complet LIKE CONCAT(xorg.nom,' %')
                                                       OR ax.Nom_complet LIKE CONCAT(xorg.nom,'-%')
                                                       OR ax.Nom_usuel = xorg.nom
                                                       OR xorg.nom LIKE CONCAT('% ',ax.Nom_complet))
                                                  AND xorg.promo < ax.promotion_etude + 2
                                                  AND xorg.promo > ax.promotion_etude - 2)
      GROUP BY  xorg.user_id
        HAVING  xorg.user_id 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)
    {
        $globals = Platal::globals();

        $page->addJsLink('jquery.js');
        $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  u.promo, u.user_id, u.display_name
                                            FROM  fusionax_xorg_anciens AS u
                                           WHERE  u.matricule_ax IS NULL
                                           LIMIT  20");
            $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
                                              FROM  fusionax_import
                                        INNER JOIN  fusionax_anciens AS a USING (id_ancien)
                                             WHERE  fusionax_import.user_id IS NULL
                                             LIMIT  20");
            $page->assign('missingInXorg', $missingInXorg);
            return;
        }
        if ($part == 'link') {
            FusionAxModule::link_by_ids($user_id, $matricule_ax);
            exit;
        }
        if ($part == 'linknext') {
            $linksToDo = FusionAxModule::find_easy_to_link(10);
            while ($l = $linksToDo->next()) {
                FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
            }
            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']);
            }
        }
        {
            $page->changeTpl('fusionax/ids.tpl');
            $missingInAX = XDB::query('SELECT  COUNT(*)
                                         FROM  fusionax_xorg_anciens AS u
                                        WHERE  u.matricule_ax 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');
            if ($missingInXorg) {
                $page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
            }
            $easyToLink = FusionAxModule::find_easy_to_link(10);
            if ($easyToLink->total() > 0) { 
                $page->assign('easyToLink', $easyToLink);
            }
        }
    }

    function handler_misc(&$page)
    {
        $page->changeTpl('fusionax/misc.tpl');
        // deceased
        $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
        $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
        $page->assign('deceasedMissingInXorg',
                      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"
                                      LIMIT  10'));
        $page->assign('deceasedMissingInAX',
                      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"
                                      LIMIT  10'));
        $page->assign('deceasedDifferent',
                      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) AS display_name
                                       FROM  fusionax_deceased AS d
                                      WHERE  d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"
                                      LIMIT  10'));
    }
}

// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?>
Commit	Line	Data
b9ad0878 PC	1	<?php
	2	/***************************************************************************
	3	* Copyright (C) 2003-2007 Polytechnique.org *
	4	* http://opensource.polytechnique.org/ *
	5	* *
	6	* This program is free software; you can redistribute it and/or modify *
	7	* it under the terms of the GNU General Public License as published by *
	8	* the Free Software Foundation; either version 2 of the License, or *
	9	* (at your option) any later version. *
	10	* *
	11	* This program is distributed in the hope that it will be useful, *
	12	* but WITHOUT ANY WARRANTY; without even the implied warranty of *
	13	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
	14	* GNU General Public License for more details. *
	15	* *
	16	* You should have received a copy of the GNU General Public License *
	17	* along with this program; if not, write to the Free Software *
	18	* Foundation, Inc., *
	19	* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
	20	***************************************************************************/
	21
c15c0053 PC	22	/**
	23	* @brief Module to merge data from AX database
	24	*
	25	* Module to import data from another database of alumni that had
	26	* different schemas. The organization that used this db is called AX
	27	* hence the name of this module.
	28	*
	29	* Datas are stored in an external server and you need a private key
	30	* to connect to their server.
	31	*/
73be4434 SJ	32	class FusionAxModule extends PLModule
73be4434 SJ	33	{
c15c0053 PC	34	function __construct()
c15c0053 PC	35	{
c15c0053	36	}
9cb8882b	37
b9ad0878 PC	38	function handlers()
	39	{
	40	return array(
	41	'fusionax' => $this->make_hook('index', AUTH_MDP, 'admin'),
e8591429	42	'fusionax/import' => $this->make_hook('import', AUTH_MDP,'admin'),
b9ad0878 PC	43	'fusionax/ids' => $this->make_hook('ids', AUTH_MDP, 'admin'),
	44	'fusionax/misc' => $this->make_hook('misc', AUTH_MDP, 'admin'),
	45	);
	46	}
c15c0053 PC	47
c15c0053 PC	48
b9ad0878 PC	49	function handler_index(&$page)
b9ad0878 PC	50	{
73be4434 SJ	51	$globals = Platal::globals();
73be4434 SJ	52
b9ad0878	53	$page->changeTpl('fusionax/index.tpl');
73be4434 SJ	54	$page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires');
	55	if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
	56	$page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate));
	57	}
b9ad0878	58	}
22f043e4	59
73be4434	60	/** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
b9ad0878 PC	61	function handler_import(&$page, $action = 'index', $fileSQL = '')
b9ad0878 PC	62	{
73be4434 SJ	63	$globals = Platal::globals();
73be4434 SJ	64
b9ad0878 PC	65	if ($action == 'index') {
	66	$page->changeTpl('fusionax/import.tpl');
	67	$page->addJsLink('jquery.js');
73be4434	68	if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
e8591429 PC	69	$page->assign(
e8591429 PC	70	'lastimport',
73be4434	71	"le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate));
b9ad0878	72	}
b9ad0878 PC	73	return;
b9ad0878 PC	74	}
22f043e4	75
cc8ea8b2	76	// toutes les actions sont faites en ajax en utilisant jquery
b9ad0878	77	header("Content-type: text/javascript; charset=utf-8");
22f043e4	78
cc8ea8b2 PC	79	// log des actions
cc8ea8b2 PC	80	$report = array();
22f043e4	81
cc8ea8b2	82	// création d'un fichier temporaire si nécessaire
b9ad0878 PC	83	if (Env::has('tmpdir')) {
	84	$tmpdir = Env::v('tmpdir');
	85	} else {
b9ad0878 PC	86	$tmpdir = tempnam('/tmp', 'fusionax');
	87	unlink($tmpdir);
	88	mkdir($tmpdir);
cc8ea8b2	89	chmod($tmpdir, 0700);
b9ad0878	90	}
22f043e4	91
73be4434	92	$modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
b9ad0878 PC	93	$olddir = getcwd();
b9ad0878 PC	94	chdir($tmpdir);
22f043e4	95
b9ad0878	96	if ($action == 'launch') {
73be4434 SJ	97	// séparation de l'archive en fichiers par tables
	98	exec($modulepath . 'import-ax.sh', $report);
	99	$report[] = 'Fichier parsé.';
b9ad0878 PC	100	$report[] = 'Import dans la base en cours...';
b9ad0878 PC	101	$next = 'integrateSQL';
73be4434	102	} elseif ($action == 'integrateSQL') {
cc8ea8b2 PC	103	// intégration des données dans la base MySQL
	104	// liste des fichiers sql à exécuter
	105	$filesSQL = array(
	106	'Activites.sql',
	107	'Adresses.sql',
	108	'Anciens.sql',
	109	'Formations.sql',
	110	'Entreprises.sql');
b9ad0878	111	if ($fileSQL != '') {
cc8ea8b2	112	// récupère le contenu du fichier sql
73be4434 SJ	113	$queries = explode(';', file_get_contents($modulepath . $fileSQL));
	114	foreach ($queries as $q) {
	115	if (trim($q)) {
	116	// coupe le fichier en requêtes individuelles
	117	if (substr($q, 0, 2) == '--') {
	118	// affiche les commentaires dans le report
	119	$lines = explode("\n", $q);
	120	$l = $lines[0];
	121	$report[] = addslashes($l);
	122	}
	123	// exécute la requête
	124	XDB::execute($q);
b9ad0878	125	}
b9ad0878	126	}
cc8ea8b2 PC	127	// trouve le prochain fichier à exécuter
	128	$trans = array_flip($filesSQL);
	129	$nextfile = $trans[$fileSQL] + 1;
b9ad0878 PC	130	} else {
	131	$nextfile = 0;
	132	}
	133	if (!isset($filesSQL[$nextfile])) {
e8591429 PC	134	// tous les fichiers ont été exécutés, on passe à l'étape
e8591429 PC	135	// suivante
b9ad0878 PC	136	$next = 'clean';
b9ad0878 PC	137	} else {
cc8ea8b2	138	// on passe au fichier suivant
73be4434	139	$next = 'integrateSQL/' . $filesSQL[$nextfile];
b9ad0878	140	}
73be4434	141	} elseif ($action == 'clean') {
cc8ea8b2	142	// nettoyage du fichier temporaire
b9ad0878	143	chdir($olddir);
73be4434 SJ	144	exec("rm -Rf $tmpdir", $report);
73be4434 SJ	145	$report[] = "Fin de l\'import";
cc8ea8b2	146	// met à jour la date de dernier import
73be4434	147	//$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx');
b9ad0878	148	}
73be4434	149	foreach($report as $t) {
cc8ea8b2	150	// affiche les lignes de report
73be4434 SJ	151	echo "$('#fusionax_import').append('" . $t . "<br/>');\n";
73be4434 SJ	152	}
b9ad0878	153	if (isset($next)) {
73be4434 SJ	154	$tmpdir = getcwd();
73be4434 SJ	155	chdir($olddir);
cc8ea8b2	156	// lance le prochain script s'il y en a un
73be4434	157	echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');";
b9ad0878	158	}
cc8ea8b2	159	// exit pour ne pas afficher la page template par défaut
b9ad0878 PC	160	exit;
b9ad0878 PC	161	}
22f043e4	162
b65beb64 PC	163	/** Lier les identifiants d'un ancien dans les deux annuaires
	164	* @param user_id identifiant dans l'annuaire X.org
	165	* @param matricule_ax identifiant dans l'annuaire de l'AX
	166	* @return 0 si la liaison a échoué, 1 sinon
22f043e4	167	*/
b9ad0878 PC	168	private static function link_by_ids($user_id, $matricule_ax)
b9ad0878 PC	169	{
73be4434 SJ	170	$res = XDB::execute("UPDATE fusionax_import AS i
	171	INNER JOIN fusionax_xorg_anciens AS u
	172	SET u.matricule_ax = i.id_ancien,
	173	i.user_id = u.user_id,
	174	i.date_match_id = NOW()
	175	WHERE i.id_ancien = {?} AND u.user_id = {?}
	176	AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL
	177	OR i.user_id != {?} OR i.user_id IS NULL)",
	178	$matricule_ax, $user_id, $matricule_ax, $user_id);
	179	if (!$res) {
b9ad0878 PC	180	return 0;
b9ad0878 PC	181	}
73be4434	182	return XDB::affectedRows() / 2;
b9ad0878	183	}
22f043e4	184
b65beb64 PC	185	/** Recherche automatique d'anciens à lier entre les deux annuaires
	186	* @param limit nombre d'anciens à trouver au max
	187	* @param sure si true, ne trouve que des anciens qui sont quasi sûrs
e8591429 PC	188	* @return un XOrgDBIterator sur les entrées avec display_name, promo,
e8591429 PC	189	* user_id, id_ancien et display_name_ax
22f043e4	190	*/
b65beb64	191	private static function find_easy_to_link($limit = 10, $sure = false)
b9ad0878	192	{
e8591429	193	$easy_to_link = XDB::iterator("
73be4434 SJ	194	SELECT xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien,
	195	CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
	196	COUNT(*) AS nbMatches
	197	FROM fusionax_anciens AS ax
	198	INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
	199	LEFT JOIN fusionax_xorg_anciens AS xorg ON (xorg.matricule_ax IS NULL
	200	AND ax.Nom_complet = xorg.nom
	201	AND ax.prenom = xorg.prenom
	202	AND xorg.promo = ax.promotion_etude)
	203	GROUP BY xorg.user_id
abed2826	204	HAVING xorg.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
b65beb64 PC	205	if ($easy_to_link->total() > 0 \|\| $sure) {
	206	return $easy_to_link;
	207	}
73be4434 SJ	208	return XDB::iterator("
	209	SELECT xorg.display_name, xorg.promo, xorg.user_id, ax.id_ancien,
	210	CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
	211	COUNT(*) AS nbMatches
	212	FROM fusionax_anciens AS ax
	213	INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
	214	LEFT JOIN fusionax_xorg_anciens AS xorg ON (xorg.matricule_ax IS NULL
	215	AND (ax.Nom_complet = xorg.nom
	216	OR ax.Nom_complet LIKE CONCAT(xorg.nom,' %')
	217	OR ax.Nom_complet LIKE CONCAT(xorg.nom,'-%')
	218	OR ax.Nom_usuel = xorg.nom
	219	OR xorg.nom LIKE CONCAT('% ',ax.Nom_complet))
	220	AND xorg.promo < ax.promotion_etude + 2
	221	AND xorg.promo > ax.promotion_etude - 2)
	222	GROUP BY xorg.user_id
	223	HAVING xorg.user_id IS NOT NULL AND nbMatches = 1" . ($limit ? ('LIMIT ' . $limit) : ''));
b9ad0878	224	}
22f043e4	225
b65beb64	226	/** Module de mise en correspondance les ids */
73be4434	227	function handler_ids(&$page, $part = 'main', $user_id = null, $matricule_ax = null)
b9ad0878	228	{
73be4434	229	$globals = Platal::globals();
22f043e4	230
73be4434	231	$page->addJsLink('jquery.js');
e8591429 PC	232	$page->assign(
	233	'xorg_title',
	234	'Polytechnique.org - Fusion - Mise en correspondance simple');
73be4434	235	if ($part == 'missingInAX') {
b9ad0878 PC	236	// locate all persons from this database that are not in AX's
b9ad0878 PC	237	$page->changeTpl('fusionax/idsMissingInAx.tpl');
73be4434 SJ	238	$missingInAX = XDB::iterator("SELECT u.promo, u.user_id, u.display_name
	239	FROM fusionax_xorg_anciens AS u
	240	WHERE u.matricule_ax IS NULL
	241	LIMIT 20");
b9ad0878 PC	242	$page->assign('missingInAX', $missingInAX);
	243	return;
	244	}
73be4434	245	if ($part == 'missingInXorg') {
b9ad0878 PC	246	// locate all persons from AX's database that are not here
b9ad0878 PC	247	$page->changeTpl('fusionax/idsMissingInXorg.tpl');
73be4434 SJ	248	$missingInXorg = XDB::iterator("SELECT a.promotion_etude AS promo,
	249	CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name,
	250	a.id_ancien
	251	FROM fusionax_import
	252	INNER JOIN fusionax_anciens AS a USING (id_ancien)
	253	WHERE fusionax_import.user_id IS NULL
	254	LIMIT 20");
b9ad0878 PC	255	$page->assign('missingInXorg', $missingInXorg);
	256	return;
	257	}
73be4434 SJ	258	if ($part == 'link') {
73be4434 SJ	259	FusionAxModule::link_by_ids($user_id, $matricule_ax);
b65beb64	260	exit;
b9ad0878	261	}
73be4434	262	if ($part == 'linknext') {
b9ad0878	263	$linksToDo = FusionAxModule::find_easy_to_link(10);
73be4434 SJ	264	while ($l = $linksToDo->next()) {
73be4434 SJ	265	FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
b9ad0878	266	}
b65beb64	267	pl_redirect('fusionax/ids#autolink');
b9ad0878	268	}
73be4434	269	if ($part == 'linkall') {
b9ad0878	270	$linksToDo = FusionAxModule::find_easy_to_link(0);
73be4434 SJ	271	while ($l = $linksToDo->next()) {
73be4434 SJ	272	FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
b9ad0878 PC	273	}
	274	}
	275	{
	276	$page->changeTpl('fusionax/ids.tpl');
73be4434 SJ	277	$missingInAX = XDB::query('SELECT COUNT(*)
	278	FROM fusionax_xorg_anciens AS u
	279	WHERE u.matricule_ax IS NULL');
	280	if ($missingInAX) {
b9ad0878 PC	281	$page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
b9ad0878 PC	282	}
73be4434 SJ	283	$missingInXorg = XDB::query('SELECT COUNT(*)
	284	FROM fusionax_import AS i
	285	WHERE i.user_id IS NULL');
	286	if ($missingInXorg) {
	287	$page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
b9ad0878 PC	288	}
b9ad0878 PC	289	$easyToLink = FusionAxModule::find_easy_to_link(10);
73be4434	290	if ($easyToLink->total() > 0) {
b9ad0878 PC	291	$page->assign('easyToLink', $easyToLink);
	292	}
	293	}
	294	}
22f043e4	295
b9ad0878 PC	296	function handler_misc(&$page)
	297	{
	298	$page->changeTpl('fusionax/misc.tpl');
	299	// deceased
73be4434 SJ	300	$deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
	301	$page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
	302	$page->assign('deceasedMissingInXorg',
	303	XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax,
	304	CONCAT(d.prenom, " ", d.nom) AS display_name
	305	FROM fusionax_deceased AS d
	306	WHERE d.deces_xorg = "0000-00-00"
	307	LIMIT 10'));
	308	$page->assign('deceasedMissingInAX',
	309	XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg,
	310	CONCAT(d.prenom, " ", d.nom) AS display_name
abed2826	311	FROM fusionax_deceased AS d
73be4434 SJ	312	WHERE d.deces_ax = "0000-00-00"
	313	LIMIT 10'));
	314	$page->assign('deceasedDifferent',
	315	XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo,
	316	d.deces_ax, d.deces_xorg,
	317	CONCAT(d.prenom, " ", d.nom) AS display_name
	318	FROM fusionax_deceased AS d
	319	WHERE d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"
	320	LIMIT 10'));
b9ad0878 PC	321	}
b9ad0878 PC	322	}
e8591429	323
b9ad0878	324	// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?>