<?php
$MODULE="RH";
$TITLE="Récupération RH";
$DFTENDSTATUS="OK";
$ARGS="-noactualizeprod -raz";
/** Laisser les lignes 2 à 5 sans changer la casse et sans espaces et dans cet ordre
 * car le script est lu et analysé quand il est exécuté via .exe ou Jenkins
 * pour gestion des fichiers .pid dans system\batch\logs
 */

set_time_limit(0);

if (!defined('__ROOT__') ) {
    list($environmentPath) = explode(DIRECTORY_SEPARATOR.'modules', dirname(__FILE__));
    $environmentPath .= DIRECTORY_SEPARATOR;
    define('__ROOT__', $environmentPath);
}

require_once __ROOT__ . '/modules/base/php/lib/cti/Batch/iCTI_batch_common.php';
require_once __ROOT__ . '/modules/base/php/classSimpleXML.php';
require_once __ROOT__ . '/modules/outils/php/httpService_cacheManager_cleanCache.php';
require_once __ROOT__ . '/modules/base/php/lib/cti/Database/Database2Factory.class.php';
require_once __ROOT__ . '/modules/base/php/lib/cti/Environnement/Environnement.class.php';
require_once __ROOT__ . '/modules/base/php/lib/cti/DatabaseContent/DatabaseContentParametersManager.php';
require_once __ROOT__ . '/modules/base/php/lib/cti/Provider/Provider.php';

global $ENDSTATUS;
global $ENV_TYPEETS;
global $ENV_FINESS;
global $ENV_SIREN;
global $RH_PROVIDER;
global $iCTI_connexion;
global $cti_args;

$prefixes = array(
    'adp' => 'AD',
	'aliciarh' => 'AL',
    'cador' => 'CA',
    'ccmx' => 'CX',
    'cegi' => 'CJ',
    'cegid' => 'CD',
    'pegase' => 'PG',
    'qsp' => 'QP',
    'quadrarh' => 'QR',
    'sage' => 'SA',
    'sagex3rhp' => 'SX',
    'shs' => 'SH',
    'shspub' => 'SP',
    'sigems' => 'SI',
    'cegidhru' => 'CU',
    'rhplace' => 'RE',
);

$p2Histo = false; // Est-ce que le second prestataire est de type historique.

logStartMsg();

// Récupération des arguments passés en paramètres et traitement de ces paramètres.
$cti_args = new CTI_Args_class();
$cti_args->checkArgs();

$multiP = Environnement::getInstance()->hasRhProvider2();
$p1 = Environnement::getInstance()->getRhProvider();
$p2 = Environnement::getInstance()->getRhProvider2();

// Si $p2 est suffixé par _histo, passer le booléen $p2Histo à true.
if (substr($p2, -6, 6) === "_histo") {
    // Vérification que le prestataire en cours est bien égal au prestataire histo.
    if ($p1 !== substr($p2, 0, -6)) {
        logErrorMsg("Le prestataire en cours $p1 est différent du prestataire historisé $p2.");
        logEndMsg();
        exit(1);
    }
    $p2Histo = true;
}

$p1Start = Environnement::getInstance()->getRhProviderStart();
$p2Start = Environnement::getInstance()->getRhProvider2Start();
$tmpDate = date_create($p1Start);
date_modify($tmpDate, '-1 day');
$p2End = date_format($tmpDate, 'Y-m-d');

// Vérification de la remontée.
$exitCode1 = 0;
$exitCode2 = 0;
$exitCodePlanning = 0;

// Si quadra RH, creation put_result avec contenu des tables pour verifier que COPY est OK
if ($p1 == 'quadrarh') {
	$iCTI_connexion = Database2Factory::getInstance(Environnement::PROVIDER_CTI);
    $ret = $iCTI_connexion->connect();
	
	$putResultFilename = __ROOT__."/prodsgbd/quadrarh/data/iCTI_synchronize_quadrarh_put_result.XML";
	
	$executeControlSql = "
	DROP TABLE IF EXISTS w_putresult;
	CREATE TEMP TABLE w_putresult AS
	SELECT tablename, 0::numeric AS nbrows
	FROM pg_tables
	WHERE schemaname = 'prod_quadrarh'
	ORDER BY 1
	;

	SELECT base.cti_execute('UPDATE w_putresult SET nbrows = (SELECT count(*) FROM prod_quadrarh.'||tablename||') WHERE tablename = '''||tablename||''' ',1)
	FROM w_putresult
	;
	
	COPY  (
	SELECT data
	FROM
	(
	SELECT 1, '<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>' AS data
	UNION
	SELECT 2, '<MAIN prod_schema=\"prod_quadrarh\" exit-code=\"0\" stale_data=\"\">'
	UNION
	SELECT 9, '</MAIN>'
	UNION
	SELECT 3, '<TABLE name=\"'||tablename||'\" modules=\"RH\" iCTI_synchronize_put_result=\"'||CASE WHEN tablename = 'services' OR nbrows > 0 THEN 'OK' ELSE 'KO' END||'\" />'
	FROM w_putresult
	ORDER BY 1,2
	) subview
	)
	TO '$putResultFilename'
	";
    $iCTI_connexion->query($executeControlSql);
	
}


$exitCode1 = checkLoadedData($p1, $MODULE);
if ($multiP && !$p2Histo) {
	// Prestataire p2 arrêté, p1 uniquement ?
	if (!$cti_args->args["-p1"]) {
		$exitCode2 = checkLoadedData($p2, $MODULE, false);
	}
}

if ($exitCode1 + $exitCode2 + $exitCodePlanning === 0 || $cti_args->args["-f"]) {
    logInfoMsg("Données prestataire chargées avec succès.");

    // Chargement des paramètres et connexion BD.
    $iCTI_connexion = Database2Factory::getInstance(Environnement::PROVIDER_CTI);
    $ret = $iCTI_connexion->connect();

    // Auto-réparation des schémas si plantage en cours.
    if (schemaExists('rh_ori')) {
        logInfoMsg("Auto-réparation des schémas.");
        if (schemaExists("rh_$p2") && schemaExists('rh')) {
            changeSchemaName("rh", "rh_$p1");
        } elseif (schemaExists("rh_$p1") && schemaExists('rh')) {
            changeSchemaName("rh", "rh_$p2");
        }
        if (!schemaExists('rh')) {
            changeSchemaName("rh_ori", "rh");
        }
    }
    if (schemaExists('rh_src')) {
        logInfoMsg("Auto-réparation des schémas.");
        if (schemaExists("rh_$p2")) {
            changeSchemaName("rh_src", "rh_$p1");
        } elseif (schemaExists("rh_$p1")) {
            changeSchemaName("rh_src", "rh_$p2");
        }
        if (!schemaExists('rh') && schemaExists('rh_ori')) {
            changeSchemaName("rh_ori", "rh");
        }
    }
    if (schemaExists('prod_rh_p1')) {
        logInfoMsg("Auto-réparation des schémas prod.");
        changeSchemaName("prod_$p1", "prod_$p2");
        changeSchemaName("prod_rh_p1", "prod_$p1");
    }

    // Import CTI
    if ($multiP && !isset($cti_args->args["--fusion_only"]) && (isset($cti_args->args["-p1"])  || !isset($cti_args->args["-p2"]))) {
        logInfoMsg("___________________________________________");
        logInfoMsg(">>> FUSION " . strtoupper($p1) . " (actuel) / " . strtoupper($p2) . " (ancien)");
        logInfoMsg("");

        logInfoMsg("___________________________________________");
        logInfoMsg(">>> PREPARATION TRANSFORMATION " . strtoupper($p1));
        logInfoMsg("");

        changeSchemaName("rh", "rh_ori");
        changeSchemaName("rh_$p1", "rh");
        initialisationSchemas(0, '');

        logInfoMsg("___________________________________________");
        logInfoMsg(">>> TRANSFORMATION " . strtoupper($p1));
        logInfoMsg("");
    }

    if (!isset($cti_args->args["--fusion_only"]) && (isset($cti_args->args["-p1"])  || !isset($cti_args->args["-p2"]))) {
        extractionEnvironnement($p1, Environnement::getInstance()->getSiren(), $p1Start, $cti_args->args["-raz"]);
    }

    if ($multiP && !isset($cti_args->args["--fusion_only"]) && (isset($cti_args->args["-p1"])  || !isset($cti_args->args["-p2"]))) {
        changeSchemaName("rh", "rh_$p1");
        changeSchemaName("rh_ori", "rh");
    }

    if ($multiP && !isset($cti_args->args["--fusion_only"]) && (isset($cti_args->args["-p2"])  || !isset($cti_args->args["-p1"]))) {
        logInfoMsg("___________________________________________");
        logInfoMsg(">>> PREPARATION TRANSFORMATION " . strtoupper($p2));
        logInfoMsg("");

        changeSchemaName("rh", "rh_ori");
        changeSchemaName("rh_$p2", "rh");
        initialisationSchemas(1, $prefixes[($p2Histo ? $p1 : $p2)]);

        if ($p2Histo) {
            changeSchemaName("prod_$p1", "prod_rh_p1");
            changeSchemaName("prod_$p2", "prod_$p1");
        }

        logInfoMsg("___________________________________________");
        logInfoMsg(">>> TRANSFORMATION " . strtoupper($p2));
        logInfoMsg("");

        if (!isset($cti_args->args["--fusion_only"]) && (isset($cti_args->args["-p2"])  || !isset($cti_args->args["-p1"]))) {
            extractionEnvironnement(($p2Histo ? $p1 : $p2), Environnement::getInstance()->getSiren2(), $p2Start, $cti_args->args["-raz"]);
        }

        changeSchemaName("rh", "rh_$p2");
        changeSchemaName("rh_ori", "rh");

        if ($p2Histo) {
            changeSchemaName("prod_$p1", "prod_$p2");
            changeSchemaName("prod_rh_p1", "prod_$p1");
        }
    }

    // Fusion CTI
    if ($multiP && !isset($cti_args->args["--no_fusion"])) {
        logInfoMsg("___________________________________________");
        logInfoMsg(">>> FUSION " . strtoupper($p1));
        logInfoMsg("");
        changeSchemaName("rh_$p1", "rh_src");
        fusionEnvironnement($p1, 0, '', $p1Start);
        changeSchemaName("rh_src", "rh_$p1");

        logInfoMsg("___________________________________________");
        logInfoMsg(">>> FUSION " . strtoupper($p2));
        logInfoMsg("");
        changeSchemaName("rh_$p2", "rh_src");
        fusionEnvironnement($p2, 1, $prefixes[($p2Histo ? $p1 : $p2)], $p2Start, $p2End);
        changeSchemaName("rh_src", "rh_$p2");

        logInfoMsg("___________________________________________");
        logInfoMsg(">>> POST-TRAITEMENTS FUSION");
        logInfoMsg("");
        postEnvironnement();
    }

    // Déconnexion BD
    $iCTI_connexion->close();

    // Calcul des Essentiels.
    if ($cti_args->args['-e'] || $cti_args->args['--essentiels']) {
        essentielCompute();
    }

    executeExpertControls($iCTI_connexion);

    // nettoyage du cache
    logInfoMsg("Nettoyage cache");
    cleanAllCache("iCTI_rh", true);
} else {
    logErrorMsg("Problème de chargement des données prestataire.");
}
logEndMsg();

/**
 * ******************************************************
 * FONCTIONS *
 * ******************************************************
 */

function extractionEnvironnement($_rhProvider, $_siren, $_start, $_raz = false)
{
    global $iCTI_connexion;
    global $cti_args;

    logInfoMsg("DEBUT RECUPERATION DE LA PRODUCTION " . strtoupper($_rhProvider));

    $time = strtotime("-5 year", time());
    $start = date("Y-m-d", $time);

    // RHPROVIDER_START
    if (strlen($_start) > 0) {
        $start = $_start;
    }

    // Préparation
    // Initiatlisation du tableau de paramètres
    $environmentVars = array();

    // Spécifique SHS (permet d'avoir des imports génériques indépendants du code client SHS).
    if (strtolower($_rhProvider) === 'shs') {

        $provider = Provider::createFromName($_rhProvider);
        $extractionReportFile = $provider->getDataExtractionReportFile();
        if (file_exists($extractionReportFile)) {
            $dcpm = new DatabaseContentParametersManager($extractionReportFile);
            $providerDatabaseParameters = $dcpm->getParameters();

            // Générer les vues génériques
            // Bibl. RH.
            if ($providerDatabaseParameters['rhlib'] !== '') {
                $environmentVars['rhlib'] = $providerDatabaseParameters['rhlib'];
                $listViewsSql = "
							SELECT table_name as tablename
							FROM information_schema.columns
							WHERE 1=1
								AND table_schema = 'prod_shs'
								AND table_name ilike '" . $environmentVars['rhlib'] . "%'
							GROUP BY 1
							ORDER BY 1
						";
                $res = $iCTI_connexion->query($listViewsSql);
                if ($res !== false) {
                    while ($prodShsTable = pg_fetch_assoc($res)) {
                        $createViewSql = "
									DROP VIEW IF EXISTS prod_shs." . str_ireplace($environmentVars['rhlib'], 'rhlib', $prodShsTable["tablename"]) . "
									;
									CREATE VIEW prod_shs." . str_ireplace($environmentVars['rhlib'], 'rhlib', $prodShsTable["tablename"]) . " AS
										SELECT * FROM prod_shs." . $prodShsTable["tablename"] . "
									;
								";
                        $iCTI_connexion->query($createViewSql);
                    }
                }
            }

            // Bibl. RHDIF.
            if ($providerDatabaseParameters['rhdiflib'] !== '') {
                $environmentVars['rhdiflib'] = $providerDatabaseParameters['rhdiflib'];
                $listViewsSql = "
							SELECT table_name as tablename
							FROM information_schema.columns
							WHERE 1=1
								AND table_schema = 'prod_shs'
								AND table_name ilike '" . $environmentVars['rhdiflib'] . "%'
							GROUP BY 1
							ORDER BY 1
						";
                $res = $iCTI_connexion->query($listViewsSql);
                if ($res !== false) {
                    while ($prodShsTable = pg_fetch_assoc($res)) {
                        $createViewSql = "
									DROP VIEW IF EXISTS prod_shs." . str_ireplace($environmentVars['rhdiflib'], 'rhdiflib', $prodShsTable["tablename"]) . "
									;
									CREATE VIEW prod_shs." . str_ireplace($environmentVars['rhdiflib'], 'rhdiflib', $prodShsTable["tablename"]) . " AS
										SELECT * FROM prod_shs." . $prodShsTable["tablename"] . "
									;
								";
                        $iCTI_connexion->query($createViewSql);
                    }
                }
            }

        }

    }

    // Initialisation du requêteur
    try {
        $sqlOptions = array();
        $environmentVars["FINESS"] = Environnement::getInstance()->getFiness();

        if (strlen($_siren) > 0) {
            $environmentVars["SIREN"] = $_siren;
        }

        $environmentVars["OPTRH_AVT_NATURE"] = 'false';
        if (strtolower($OPTRH_AVT_NATURE) == '1') {
            $environmentVars["OPTRH_AVT_NATURE"] = 'true';
        }

        $environmentVars["APP_ROOT"] = __ROOT__;

        // Création d'une table SQL temporaire contenant les paramètres à passer à l'import RH.
        // A appeler dans le SQL de la façon suivante : rhp('finess');

        $sqlSiren = '';
        if (isset($environmentVars["SIREN"])) {
            foreach (explode(',', $_siren) as $siren) {
                $sqlSiren .= "('siren'::text,  '" . trim($siren) . "'::text)," . PHP_EOL;
            }
        }

        $sqlEnt = '';
        if (isset($environmentVars["entreprise"])) {
            foreach ($environmentVars["entreprise"] as $ent) {
                $sqlEnt .= "('entreprise'::text,  '$ent'::text)," . PHP_EOL;
            }
        }

        $tmpParamSql = "
        DROP TABLE IF EXISTS rhp
        ;
        CREATE TEMP TABLE rhp AS
        VALUES
        	$sqlSiren
        	$sqlEnt
    	    ('finess'::text, '" . $environmentVars["FINESS"] . "'::text),
			('opt_avt_nature'::text,  '" . $environmentVars["OPTRH_AVT_NATURE"] . "'::text),
            ('rhprovider_start'::text,  '" . $start . "'::text)
		;

        DROP FUNCTION IF EXISTS rhp(text)
	    ;
		CREATE FUNCTION rhp(text)
			RETURNS text AS
			\$BODY\$
				SELECT column2 FROM rhp WHERE column1 = \$1
				;
			\$BODY\$
		LANGUAGE 'sql' VOLATILE
		;

	    DROP FUNCTION IF EXISTS rhp_in(text)
	    ;
	    CREATE FUNCTION rhp_in(text)
			RETURNS setof text AS
			\$BODY\$
				SELECT column2 FROM rhp WHERE column1 = \$1
				;
			\$BODY\$
		LANGUAGE 'sql' VOLATILE
		;
		";
        $iCTI_connexion->query($tmpParamSql);

        foreach ($cti_args->args as $key => $value) {
            $sqlOptions[] = $key;
        }

        $cti_sqlStatements = new CTI_SqlStatements_class("iCTI_import_rh_" . strtoupper($_rhProvider) . ".XML", $iCTI_connexion, $environmentVars, $sqlOptions, "iCTI_import_rh_SHARE.XML");

        // Raz automatique le samedi
        if (date("w") == 6) {
            $_raz = "ONLYKEY";
        }

        if ($_raz) {
            $cti_sqlStatements->executeStatementsNode("RAZ");
            $cti_sqlStatements->executeStatementsNode("VACUUM");
        }

        $cti_sqlStatements->executeStatementsNode("INIT");
        $cti_sqlStatements->executeStatementsNode("PROD");
        $cti_sqlStatements->executeStatementsNode("POST");
        $cti_sqlStatements->executeStatementsNode("VACUUM");
    } catch (Exception $e) {
        logErrorMsg("Impossible de charger les requêtes SQL.");
        logEndMsg();
        exit(1);
    }

    logInfoMsg("FIN RECUPERATION DE LA PRODUCTION " . strtoupper($_rhProvider));
}

function postEnvironnement()
{
    global $iCTI_connexion;
    global $cti_args;

    $sqlOptions = array();
    $environmentVars = array();

    foreach ($cti_args->args as $key => $value) {
        $sqlOptions[] = $key;
    }

    $sqlStatements = new CTI_SqlStatements_class("iCTI_import_rh_FUSION.XML", $iCTI_connexion, $environmentVars, $sqlOptions, "iCTI_import_rh_SHARE.XML");

    $sqlStatements->executeStatementsNode("POST");
    $sqlStatements->executeStatementsNode("VACUUM");
}

function essentielCompute()
{
    logInfoMsg("Calcul des indicateurs essentiels RH.");

    $exe = "..\..\outils\dashboard\iCTI_aggregateCtiIndicators.exe";

    if (file_exists($exe)) {
        $return = exec("$exe -m rh -e");
    } else {
        logErrorMsg("La fonction de calcul des essentiels RH est absente.");
    }
}

function executeExpertControls($iCTI_connexion)
{
    logInfoMsg("Contrôles expert");
    $executeControlSql = "SELECT base.cti_calcul_expert_controls('rh', 0);";
    $res = $iCTI_connexion->query($executeControlSql);
    if ($res === false) {
        logErrorMsg("Le traitement des contrôles experts a echoué");
    }
}