[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/view'     => $this->make_hook('view',     AUTH_MDP, 'admin'),
            'fusionax/ids'      => $this->make_hook('ids',      AUTH_MDP, 'admin'),
            'fusionax/deceased' => $this->make_hook('deceased', 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;
    }

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

        $page->changeTpl('fusionax/view.tpl');
        if ($action == 'create') {
            XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
            XDB::execute('CREATE VIEW  fusionax_deceased AS
                               SELECT  u.user_id, a.id_ancien, u.nom, u.prenom, u.promo, u.deces AS deces_xorg, a.Date_deces AS deces_ax
                                 FROM  auth_user_md5 i  AS u
                           INNER JOIN  fusionax_anciens AS a ON (a.id_ancien = u.matricule_ax)
                                WHERE  u.deces != a.Date_deces');
            XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
            XDB::execute('CREATE VIEW  fusionax_promo AS
                               SELECT  u.user_id, u.matricule_ax, u.nom, u.prenom, u.promo AS promo_etude_xorg,
                                       f.promotion_etude AS promo_etude_ax, u.promo_sortie AS promo_sortie_xorg
                                 FROM  auth_user_md5    AS u
                           INNER JOIN  fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien)
                                WHERE  u.promo != f.promotion_etude AND !(f.promotion_etude = u.promo + 1 AND u.promo_sortie = u.promo + 4)');
        }
    }

    /* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
    private static function clear_wrong_in_xorg($user_id)
    {
        $res = XDB::execute("UPDATE  fusionax_xorg_anciens
                                SET  matricule_ax = NULL
                              WHERE  user_id = {?}", $user_id);
        if (!$res) {
            return 0;
        }
        return XDB::affectedRows() / 2;
    }

    /* 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 et 1923 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.user_id, u.display_name
                                FROM  fusionax_xorg_anciens AS u
                               WHERE  NOT EXISTS (SELECT  *
                                                    FROM  fusionax_anciens AS f
                                                   WHERE  f.id_ancien = u.matricule_ax)
                                      AND u.matricule_ax IS NOT NULL AND promo != 1921 AND promo != 1923");
    }

    /** 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  u.display_name, u.promo, u.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 u ON (u.matricule_ax IS NULL
                                               AND ax.Nom_patronymique = u.nom
                                               AND ax.prenom = u.prenom
                                               AND u.promo = ax.promotion_etude)
      GROUP BY  u.user_id
        HAVING  u.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  u.display_name, u.promo, u.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 u ON (u.matricule_ax IS NULL
                                               AND (ax.Nom_patronymique = u.nom
                                                    OR ax.Nom_patronymique LIKE CONCAT(u.nom, ' %')
                                                    OR ax.Nom_patronymique LIKE CONCAT(u.nom, '-%')
                                                    OR ax.Nom_usuel = u.nom
                                                    OR u.nom LIKE CONCAT('% ', ax.Nom_patronymique))
                                               AND u.promo < ax.promotion_etude + 2
                                               AND u.promo > ax.promotion_etude - 2)
      GROUP BY  u.user_id
        HAVING  u.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();
        $nbToLink = 100;

        $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  promo, user_id, display_name
                                            FROM  fusionax_xorg_anciens
                                           WHERE  matricule_ax IS NULL");
            $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");
            $page->assign('missingInXorg', $missingInXorg);
            return;
        }
        if ($part == 'wrongInXorg') {
            // locate all persons from Xorg database that have a bad AX id
            $page->changeTpl('fusionax/idswrongInXorg.tpl');
            $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
            $page->assign('wrongInXorg', $wrongInXorg);
            return;
        }
        if ($part == 'cleanwronginxorg') {
            $linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
            while ($l = $linksToDo->next()) {
                FusionAxModule::clear_wrong_in_xorg($l['user_id']);
            }
            pl_redirect('fusionax/ids/wrongInXorg');
        }
        if ($part == 'lier') {
            if (Post::has('user_id') && Post::has('matricule_ax')) {
                FusionAxModule::link_by_ids(Post::i('user_id'), Post::v('matricule_ax'));
            }
        }
        if ($part == 'link') {
            FusionAxModule::link_by_ids($user_id, $matricule_ax);
            exit;
        }
        if ($part == 'linknext') {
            $linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
            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());
            }
            $wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
            if ($wrongInXorg->total() > 0) {
                $page->assign('wrongInXorg', $wrongInXorg->total());
            }
            $easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
            if ($easyToLink->total() > 0) {
                $page->assign('nbMatch', $easyToLink->total());
                $page->assign('easyToLink', $easyToLink);
            }
        }
    }

    function handler_deceased(&$page, $action = '')
    {
        if ($action == 'updateXorg') {
            XDB::execute('UPDATE  fusionax_deceased
                             SET  deces_xorg = deces_ax
                           WHERE  deces_xorg = "0000-00-00"');
        }
        if ($action == 'updateAX') {
            XDB::execute('UPDATE  fusionax_deceased
                             SET  deces_ax = deces_xorg
                           WHERE  deces_ax = "0000-00-00"');
        }
        if ($action == 'update') {
            if (Post::has('user_id') && Post::has('date')) {
                XDB::execute('UPDATE  fusionax_deceased
                                 SET  deces_ax = {?}, deces_xorg = {?}
                               WHERE  user_id = {?}',
                             Post::v('date'), Post::v('date'), Post::i('user_id'));
            }
        }
        $page->changeTpl('fusionax/deceased.tpl');
        // deceased
        $deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
        $page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
        $res = 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('nbDeceasedMissingInXorg', $res->total());
        $page->assign('deceasedMissingInXorg', $res);
        $res = 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('nbDeceasedMissingInAX', $res->total());
        $page->assign('deceasedMissingInAX', $res);
        $res = 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, " ", d.user_id) AS display_name
                                FROM  fusionax_deceased AS d
                               WHERE  d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"');
        $page->assign('nbDeceasedDifferent', $res->total());
        $page->assign('deceasedDifferent', $res);
    }
}

// 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(
29459c49 SJ	41	'fusionax' => $this->make_hook('index', AUTH_MDP, 'admin'),
	42	'fusionax/import' => $this->make_hook('import', AUTH_MDP, 'admin'),
	43	'fusionax/view' => $this->make_hook('view', AUTH_MDP, 'admin'),
	44	'fusionax/ids' => $this->make_hook('ids', AUTH_MDP, 'admin'),
	45	'fusionax/deceased' => $this->make_hook('deceased', AUTH_MDP, 'admin'),
b9ad0878 PC	46	);
b9ad0878 PC	47	}
c15c0053 PC	48
c15c0053 PC	49
b9ad0878 PC	50	function handler_index(&$page)
b9ad0878 PC	51	{
73be4434 SJ	52	$globals = Platal::globals();
73be4434 SJ	53
b9ad0878	54	$page->changeTpl('fusionax/index.tpl');
73be4434 SJ	55	$page->assign('xorg_title', 'Polytechnique.org - Fusion des annuaires');
	56	if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
	57	$page->assign('lastimport', date("d-m-Y", $globals->fusionax->LastUpdate));
	58	}
b9ad0878	59	}
22f043e4	60
73be4434	61	/** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
b9ad0878 PC	62	function handler_import(&$page, $action = 'index', $fileSQL = '')
b9ad0878 PC	63	{
73be4434 SJ	64	$globals = Platal::globals();
73be4434 SJ	65
b9ad0878 PC	66	if ($action == 'index') {
	67	$page->changeTpl('fusionax/import.tpl');
	68	$page->addJsLink('jquery.js');
73be4434	69	if (isset($globals->fusionax) && isset($globals->fusionax->LastUpdate)) {
e8591429 PC	70	$page->assign(
e8591429 PC	71	'lastimport',
73be4434	72	"le " . date("d/m/Y à H:i", $globals->fusionax->LastUpdate));
b9ad0878	73	}
b9ad0878 PC	74	return;
b9ad0878 PC	75	}
22f043e4	76
cc8ea8b2	77	// toutes les actions sont faites en ajax en utilisant jquery
b9ad0878	78	header("Content-type: text/javascript; charset=utf-8");
22f043e4	79
cc8ea8b2 PC	80	// log des actions
cc8ea8b2 PC	81	$report = array();
22f043e4	82
cc8ea8b2	83	// création d'un fichier temporaire si nécessaire
b9ad0878 PC	84	if (Env::has('tmpdir')) {
	85	$tmpdir = Env::v('tmpdir');
	86	} else {
b9ad0878 PC	87	$tmpdir = tempnam('/tmp', 'fusionax');
	88	unlink($tmpdir);
	89	mkdir($tmpdir);
cc8ea8b2	90	chmod($tmpdir, 0700);
b9ad0878	91	}
22f043e4	92
73be4434	93	$modulepath = realpath(dirname(__FILE__) . '/fusionax/') . '/';
b9ad0878 PC	94	$olddir = getcwd();
b9ad0878 PC	95	chdir($tmpdir);
22f043e4	96
b9ad0878	97	if ($action == 'launch') {
73be4434 SJ	98	// séparation de l'archive en fichiers par tables
	99	exec($modulepath . 'import-ax.sh', $report);
	100	$report[] = 'Fichier parsé.';
b9ad0878 PC	101	$report[] = 'Import dans la base en cours...';
b9ad0878 PC	102	$next = 'integrateSQL';
73be4434	103	} elseif ($action == 'integrateSQL') {
cc8ea8b2 PC	104	// intégration des données dans la base MySQL
	105	// liste des fichiers sql à exécuter
	106	$filesSQL = array(
	107	'Activites.sql',
	108	'Adresses.sql',
	109	'Anciens.sql',
	110	'Formations.sql',
	111	'Entreprises.sql');
b9ad0878	112	if ($fileSQL != '') {
cc8ea8b2	113	// récupère le contenu du fichier sql
73be4434 SJ	114	$queries = explode(';', file_get_contents($modulepath . $fileSQL));
	115	foreach ($queries as $q) {
	116	if (trim($q)) {
	117	// coupe le fichier en requêtes individuelles
	118	if (substr($q, 0, 2) == '--') {
	119	// affiche les commentaires dans le report
	120	$lines = explode("\n", $q);
	121	$l = $lines[0];
	122	$report[] = addslashes($l);
	123	}
	124	// exécute la requête
	125	XDB::execute($q);
b9ad0878	126	}
b9ad0878	127	}
cc8ea8b2 PC	128	// trouve le prochain fichier à exécuter
	129	$trans = array_flip($filesSQL);
	130	$nextfile = $trans[$fileSQL] + 1;
b9ad0878 PC	131	} else {
	132	$nextfile = 0;
	133	}
	134	if (!isset($filesSQL[$nextfile])) {
e8591429 PC	135	// tous les fichiers ont été exécutés, on passe à l'étape
e8591429 PC	136	// suivante
b9ad0878 PC	137	$next = 'clean';
b9ad0878 PC	138	} else {
cc8ea8b2	139	// on passe au fichier suivant
73be4434	140	$next = 'integrateSQL/' . $filesSQL[$nextfile];
b9ad0878	141	}
73be4434	142	} elseif ($action == 'clean') {
cc8ea8b2	143	// nettoyage du fichier temporaire
b9ad0878	144	chdir($olddir);
73be4434 SJ	145	exec("rm -Rf $tmpdir", $report);
73be4434 SJ	146	$report[] = "Fin de l\'import";
cc8ea8b2	147	// met à jour la date de dernier import
73be4434	148	//$globals->change_dynamic_config(array('LastUpdate' => time()), 'FusionAx');
b9ad0878	149	}
73be4434	150	foreach($report as $t) {
cc8ea8b2	151	// affiche les lignes de report
73be4434 SJ	152	echo "$('#fusionax_import').append('" . $t . "<br/>');\n";
73be4434 SJ	153	}
b9ad0878	154	if (isset($next)) {
73be4434 SJ	155	$tmpdir = getcwd();
73be4434 SJ	156	chdir($olddir);
cc8ea8b2	157	// lance le prochain script s'il y en a un
73be4434	158	echo "$.getScript('fusionax/import/" . $next . "?tmpdir=" . urlencode($tmpdir) . "');";
b9ad0878	159	}
cc8ea8b2	160	// exit pour ne pas afficher la page template par défaut
b9ad0878 PC	161	exit;
b9ad0878 PC	162	}
22f043e4	163
29459c49 SJ	164	/** Import de l'annuaire de l'AX depuis l'export situé dans le home de jacou */
	165	function handler_view(&$page, $action = '')
	166	{
	167	$globals = Platal::globals();
	168
	169	$page->changeTpl('fusionax/view.tpl');
	170	if ($action == 'create') {
	171	XDB::execute('DROP VIEW IF EXISTS fusionax_deceased');
	172	XDB::execute('CREATE VIEW fusionax_deceased AS
	173	SELECT u.user_id, a.id_ancien, u.nom, u.prenom, u.promo, u.deces AS deces_xorg, a.Date_deces AS deces_ax
	174	FROM auth_user_md5 i AS u
	175	INNER JOIN fusionax_anciens AS a ON (a.id_ancien = u.matricule_ax)
	176	WHERE u.deces != a.Date_deces');
	177	XDB::execute('DROP VIEW IF EXISTS fusionax_promo');
	178	XDB::execute('CREATE VIEW fusionax_promo AS
	179	SELECT u.user_id, u.matricule_ax, u.nom, u.prenom, u.promo AS promo_etude_xorg,
	180	f.promotion_etude AS promo_etude_ax, u.promo_sortie AS promo_sortie_xorg
	181	FROM auth_user_md5 AS u
	182	INNER JOIN fusionax_anciens AS f ON (u.matricule_ax = f.id_ancien)
	183	WHERE u.promo != f.promotion_etude AND !(f.promotion_etude = u.promo + 1 AND u.promo_sortie = u.promo + 4)');
	184	}
	185	}
	186
	187	/* Mets à NULL le matricule_ax de ces camarades pour marquer le fait qu'ils ne figurent pas dans l'annuaire de l'AX */
	188	private static function clear_wrong_in_xorg($user_id)
	189	{
	190	$res = XDB::execute("UPDATE fusionax_xorg_anciens
	191	SET matricule_ax = NULL
	192	WHERE user_id = {?}", $user_id);
	193	if (!$res) {
	194	return 0;
	195	}
	196	return XDB::affectedRows() / 2;
	197	}
	198
	199	/* Cherche les les anciens présents dans Xorg avec un matricule_ax ne correspondant à rien dans la base de l'AX
	200	* (mises à part les promo 1921 et 1923 qui ne figurent pas dans les données de l'AX)*/
	201	private static function find_wrong_in_xorg($limit = 10)
	202	{
	203	return XDB::iterator("SELECT u.promo, u.user_id, u.display_name
	204	FROM fusionax_xorg_anciens AS u
	205	WHERE NOT EXISTS (SELECT *
	206	FROM fusionax_anciens AS f
	207	WHERE f.id_ancien = u.matricule_ax)
	208	AND u.matricule_ax IS NOT NULL AND promo != 1921 AND promo != 1923");
	209	}
	210
b65beb64 PC	211	/** Lier les identifiants d'un ancien dans les deux annuaires
	212	* @param user_id identifiant dans l'annuaire X.org
	213	* @param matricule_ax identifiant dans l'annuaire de l'AX
	214	* @return 0 si la liaison a échoué, 1 sinon
22f043e4	215	*/
b9ad0878 PC	216	private static function link_by_ids($user_id, $matricule_ax)
b9ad0878 PC	217	{
29459c49	218	$res = XDB::execute("UPDATE fusionax_import AS i
73be4434 SJ	219	INNER JOIN fusionax_xorg_anciens AS u
	220	SET u.matricule_ax = i.id_ancien,
	221	i.user_id = u.user_id,
	222	i.date_match_id = NOW()
	223	WHERE i.id_ancien = {?} AND u.user_id = {?}
	224	AND (u.matricule_ax != {?} OR u.matricule_ax IS NULL
	225	OR i.user_id != {?} OR i.user_id IS NULL)",
	226	$matricule_ax, $user_id, $matricule_ax, $user_id);
	227	if (!$res) {
b9ad0878 PC	228	return 0;
b9ad0878 PC	229	}
73be4434	230	return XDB::affectedRows() / 2;
b9ad0878	231	}
22f043e4	232
b65beb64 PC	233	/** Recherche automatique d'anciens à lier entre les deux annuaires
	234	* @param limit nombre d'anciens à trouver au max
	235	* @param sure si true, ne trouve que des anciens qui sont quasi sûrs
e8591429 PC	236	* @return un XOrgDBIterator sur les entrées avec display_name, promo,
e8591429 PC	237	* user_id, id_ancien et display_name_ax
22f043e4	238	*/
b65beb64	239	private static function find_easy_to_link($limit = 10, $sure = false)
b9ad0878	240	{
e8591429	241	$easy_to_link = XDB::iterator("
29459c49	242	SELECT u.display_name, u.promo, u.user_id, ax.id_ancien,
73be4434 SJ	243	CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
73be4434 SJ	244	COUNT(*) AS nbMatches
29459c49 SJ	245	FROM fusionax_anciens AS ax
	246	INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
	247	LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
	248	AND ax.Nom_patronymique = u.nom
	249	AND ax.prenom = u.prenom
	250	AND u.promo = ax.promotion_etude)
	251	GROUP BY u.user_id
	252	HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
b65beb64 PC	253	if ($easy_to_link->total() > 0 \|\| $sure) {
	254	return $easy_to_link;
	255	}
73be4434	256	return XDB::iterator("
29459c49	257	SELECT u.display_name, u.promo, u.user_id, ax.id_ancien,
73be4434 SJ	258	CONCAT(ax.prenom, ' ', ax.nom_complet, ' (X ', ax.promotion_etude, ')') AS display_name_ax,
73be4434 SJ	259	COUNT(*) AS nbMatches
29459c49 SJ	260	FROM fusionax_anciens AS ax
	261	INNER JOIN fusionax_import AS i ON (i.id_ancien = ax.id_ancien AND i.user_id IS NULL)
	262	LEFT JOIN fusionax_xorg_anciens AS u ON (u.matricule_ax IS NULL
	263	AND (ax.Nom_patronymique = u.nom
	264	OR ax.Nom_patronymique LIKE CONCAT(u.nom, ' %')
	265	OR ax.Nom_patronymique LIKE CONCAT(u.nom, '-%')
	266	OR ax.Nom_usuel = u.nom
	267	OR u.nom LIKE CONCAT('% ', ax.Nom_patronymique))
	268	AND u.promo < ax.promotion_etude + 2
	269	AND u.promo > ax.promotion_etude - 2)
	270	GROUP BY u.user_id
	271	HAVING u.user_id IS NOT NULL AND nbMatches = 1 " . ($limit ? ('LIMIT ' . $limit) : ''));
b9ad0878	272	}
22f043e4	273
b65beb64	274	/** Module de mise en correspondance les ids */
73be4434	275	function handler_ids(&$page, $part = 'main', $user_id = null, $matricule_ax = null)
b9ad0878	276	{
73be4434	277	$globals = Platal::globals();
29459c49	278	$nbToLink = 100;
22f043e4	279
73be4434	280	$page->addJsLink('jquery.js');
29459c49	281	$page->assign('xorg_title', 'Polytechnique.org - Fusion - Mise en correspondance simple');
73be4434	282	if ($part == 'missingInAX') {
b9ad0878 PC	283	// locate all persons from this database that are not in AX's
b9ad0878 PC	284	$page->changeTpl('fusionax/idsMissingInAx.tpl');
29459c49 SJ	285	$missingInAX = XDB::iterator("SELECT promo, user_id, display_name
	286	FROM fusionax_xorg_anciens
	287	WHERE matricule_ax IS NULL");
b9ad0878 PC	288	$page->assign('missingInAX', $missingInAX);
	289	return;
	290	}
73be4434	291	if ($part == 'missingInXorg') {
b9ad0878 PC	292	// locate all persons from AX's database that are not here
b9ad0878 PC	293	$page->changeTpl('fusionax/idsMissingInXorg.tpl');
73be4434 SJ	294	$missingInXorg = XDB::iterator("SELECT a.promotion_etude AS promo,
	295	CONCAT(a.prenom, ' ', a.Nom_usuel) AS display_name,
	296	a.id_ancien
	297	FROM fusionax_import
	298	INNER JOIN fusionax_anciens AS a USING (id_ancien)
29459c49	299	WHERE fusionax_import.user_id IS NULL");
b9ad0878 PC	300	$page->assign('missingInXorg', $missingInXorg);
	301	return;
	302	}
29459c49 SJ	303	if ($part == 'wrongInXorg') {
	304	// locate all persons from Xorg database that have a bad AX id
	305	$page->changeTpl('fusionax/idswrongInXorg.tpl');
	306	$wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
	307	$page->assign('wrongInXorg', $wrongInXorg);
	308	return;
	309	}
	310	if ($part == 'cleanwronginxorg') {
	311	$linksToDo = FusionAxModule::find_wrong_in_xorg($nbToLink);
	312	while ($l = $linksToDo->next()) {
	313	FusionAxModule::clear_wrong_in_xorg($l['user_id']);
	314	}
	315	pl_redirect('fusionax/ids/wrongInXorg');
	316	}
	317	if ($part == 'lier') {
	318	if (Post::has('user_id') && Post::has('matricule_ax')) {
	319	FusionAxModule::link_by_ids(Post::i('user_id'), Post::v('matricule_ax'));
	320	}
	321	}
73be4434 SJ	322	if ($part == 'link') {
73be4434 SJ	323	FusionAxModule::link_by_ids($user_id, $matricule_ax);
b65beb64	324	exit;
b9ad0878	325	}
73be4434	326	if ($part == 'linknext') {
29459c49	327	$linksToDo = FusionAxModule::find_easy_to_link($nbToLink);
73be4434 SJ	328	while ($l = $linksToDo->next()) {
73be4434 SJ	329	FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
b9ad0878	330	}
b65beb64	331	pl_redirect('fusionax/ids#autolink');
b9ad0878	332	}
73be4434	333	if ($part == 'linkall') {
b9ad0878	334	$linksToDo = FusionAxModule::find_easy_to_link(0);
73be4434 SJ	335	while ($l = $linksToDo->next()) {
73be4434 SJ	336	FusionAxModule::link_by_ids($l['user_id'], $l['id_ancien']);
b9ad0878 PC	337	}
	338	}
	339	{
	340	$page->changeTpl('fusionax/ids.tpl');
73be4434 SJ	341	$missingInAX = XDB::query('SELECT COUNT(*)
	342	FROM fusionax_xorg_anciens AS u
	343	WHERE u.matricule_ax IS NULL');
	344	if ($missingInAX) {
b9ad0878 PC	345	$page->assign('nbMissingInAX', $missingInAX->fetchOneCell());
b9ad0878 PC	346	}
73be4434 SJ	347	$missingInXorg = XDB::query('SELECT COUNT(*)
	348	FROM fusionax_import AS i
	349	WHERE i.user_id IS NULL');
	350	if ($missingInXorg) {
	351	$page->assign('nbMissingInXorg', $missingInXorg->fetchOneCell());
b9ad0878	352	}
29459c49 SJ	353	$wrongInXorg = FusionAxModule::find_wrong_in_xorg($nbToLink);
	354	if ($wrongInXorg->total() > 0) {
	355	$page->assign('wrongInXorg', $wrongInXorg->total());
	356	}
	357	$easyToLink = FusionAxModule::find_easy_to_link($nbToLink);
	358	if ($easyToLink->total() > 0) {
	359	$page->assign('nbMatch', $easyToLink->total());
b9ad0878 PC	360	$page->assign('easyToLink', $easyToLink);
	361	}
	362	}
	363	}
22f043e4	364
29459c49	365	function handler_deceased(&$page, $action = '')
b9ad0878	366	{
29459c49 SJ	367	if ($action == 'updateXorg') {
	368	XDB::execute('UPDATE fusionax_deceased
	369	SET deces_xorg = deces_ax
	370	WHERE deces_xorg = "0000-00-00"');
	371	}
	372	if ($action == 'updateAX') {
	373	XDB::execute('UPDATE fusionax_deceased
	374	SET deces_ax = deces_xorg
	375	WHERE deces_ax = "0000-00-00"');
	376	}
	377	if ($action == 'update') {
	378	if (Post::has('user_id') && Post::has('date')) {
	379	XDB::execute('UPDATE fusionax_deceased
	380	SET deces_ax = {?}, deces_xorg = {?}
	381	WHERE user_id = {?}',
	382	Post::v('date'), Post::v('date'), Post::i('user_id'));
	383	}
	384	}
	385	$page->changeTpl('fusionax/deceased.tpl');
b9ad0878	386	// deceased
73be4434 SJ	387	$deceasedErrorsSql = XDB::query('SELECT COUNT(*) FROM fusionax_deceased');
73be4434 SJ	388	$page->assign('deceasedErrors', $deceasedErrorsSql->fetchOneCell());
29459c49 SJ	389	$res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_ax,
	390	CONCAT(d.prenom, " ", d.nom) AS display_name
	391	FROM fusionax_deceased AS d
	392	WHERE d.deces_xorg = "0000-00-00"
	393	LIMIT 10');
	394	$page->assign('nbDeceasedMissingInXorg', $res->total());
	395	$page->assign('deceasedMissingInXorg', $res);
	396	$res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo, d.deces_xorg,
	397	CONCAT(d.prenom, " ", d.nom) AS display_name
	398	FROM fusionax_deceased AS d
	399	WHERE d.deces_ax = "0000-00-00"
	400	LIMIT 10');
	401	$page->assign('nbDeceasedMissingInAX', $res->total());
	402	$page->assign('deceasedMissingInAX', $res);
	403	$res = XDB::iterator('SELECT d.user_id, d.id_ancien, d.nom, d.prenom, d.promo,
	404	d.deces_ax, d.deces_xorg,
	405	CONCAT(d.prenom, " ", d.nom, " ", d.user_id) AS display_name
	406	FROM fusionax_deceased AS d
	407	WHERE d.deces_xorg != "0000-00-00" AND d.deces_ax != "0000-00-00"');
	408	$page->assign('nbDeceasedDifferent', $res->total());
	409	$page->assign('deceasedDifferent', $res);
b9ad0878 PC	410	}
b9ad0878 PC	411	}
e8591429	412
b9ad0878	413	// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:?>