<?php
$connPostgre->beginTransaction();

$select = "SELECT * FROM sav.type_planification";
$result = $connPostgre->query($select)->fetchAll();
$typeVisite = [];
foreach ($result as $value) {
    $typeVisite[$value['code']] = $value['id'];
}

//POLES
$tabCorrespondanceIds = [];
$idPole = 0;
foreach($sites as $cle => $site){
    //groupes de type pôle
    $sql = "SELECT * FROM TCHAT_SALON";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    if($nbResult == 0){
        continue;
    }
    $insert = "INSERT INTO sav.groupe(id, nom, idgroupe_type, idsite, idutilisateur_admin_groupe) VALUES ";
    $count = 0;
    foreach($result as $cle => $row){
        $idPole++;
        $tabCorrespondanceIds[$site['id']]['salon'][$row['ID_TCHAT_SALON']] = $idPole;
        $insert .= "('".$idPole."','".$row['NOM_SALON']."','4','".$site['id']."',null)";
        $count++;
        if(($count) != $nbResult){
            $insert .= ",";
        }
    }
    exec_free($connPostgre, $insert, $stid);

    //groupes de type planning (anciennement liste personalisées
    $sql = "SELECT * FROM PLN_LISTE_AGENTS WHERE LOGIN IS NULL AND NBAGENTS>1 AND DESACTIVER=0 ORDER BY LIBELLE";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    if($nbResult == 0){
        continue;
    }
    $insert = "INSERT INTO sav.groupe(id, nom, idgroupe_type, idsite, idutilisateur_admin_groupe) VALUES ";
    $count = 0;
    foreach($result as $cle => $row){
        $idPole++;
        $tabCorrespondanceIds[$site['id']]['salon'][$row['IDPLN_LISTE_AGENTS']] = $idPole;
        $insert .= "('".$idPole."','".$row['LIBELLE']."','5','".$site['id']."',null)";
        $count++;
        if(($count) != $nbResult){
            $insert .= ",";
        }
    }
    exec_free($connPostgre, $insert, $stid);
}
$idPole++;
$alter = "ALTER SEQUENCE sav.groupe_id_seq RESTART WITH ".($idPole).";";
$connPostgre->exec($alter);

//utilisateurs Mathieu et Coralie
$hash = "$2y$13\$IMalnQpo7xfZD5FJGbEadOcqyj2mi/NQbQiI8v2wBXfjZ4nwshJlG";
$insert = "INSERT INTO sav.utilisateur (id, nom, prenom, idtype_login, login, password, cle_salage, idtype_utilisateur, url_homepage, idutilisateur_valide_conges, date_embauche, idagence, code_client_agi, token_mobilite, droits, idutilisateur_role, role_id, initiales, actif) VALUES (1, 'Grivault', 'Coralie', 3, 'cgrivault@frisquet.fr', '".$hash."', NULL, 1, NULL, NULL, NULL, '".$idAgenceDev."', NULL, NULL, '{\"1\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"2\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"3\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"4\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"5\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"6\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"7\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1}}', 1, 1, NULL, true);";
$connPostgre->exec($insert);
$insert = "INSERT INTO sav.utilisateur (id, nom, prenom, idtype_login, login, password, cle_salage, idtype_utilisateur, url_homepage, idutilisateur_valide_conges, date_embauche, idagence, code_client_agi, token_mobilite, droits, idutilisateur_role, role_id, initiales, actif) VALUES (2, 'Dauphin', 'Mathieu', 3, 'mdauphin@frisquet.fr', '".$hash."', NULL, 1, NULL, NULL, NULL, '".$idAgenceDev."', NULL, NULL, '{\"1\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"2\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"3\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"4\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"5\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"6\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"7\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1}}', 1, 1, NULL, true);";
$connPostgre->exec($insert);
$insert = "INSERT INTO sav.utilisateur (id, nom, prenom, idtype_login, login, password, cle_salage, idtype_utilisateur, url_homepage, idutilisateur_valide_conges, date_embauche, idagence, code_client_agi, token_mobilite, droits, idutilisateur_role, role_id, initiales, actif) VALUES (3, 'Becquet', 'Florine', 3, 'fbecquet@frisquet.fr', '".$hash."', NULL, 1, NULL, NULL, NULL, '".$idAgenceDev."', NULL, NULL, '{\"1\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"2\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"3\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"4\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"5\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"6\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"7\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1}}', 1, 1, NULL, true);";
$connPostgre->exec($insert);
$insert = "INSERT INTO sav.utilisateur (id, nom, prenom, idtype_login, login, password, cle_salage, idtype_utilisateur, url_homepage, idutilisateur_valide_conges, date_embauche, idagence, code_client_agi, token_mobilite, droits, idutilisateur_role, role_id, initiales, actif) VALUES (4, 'Beaujon', 'Cédric', 3, 'cbeaujon@frisquet.fr', '".$hash."', NULL, 1, NULL, NULL, NULL, '".$idAgenceDev."', NULL, NULL, '{\"1\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"2\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"3\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"4\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"5\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"6\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"7\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1}}', 1, 1, NULL, true);";
$connPostgre->exec($insert);
$insert = "INSERT INTO sav.utilisateur (id, nom, prenom, idtype_login, login, password, cle_salage, idtype_utilisateur, url_homepage, idutilisateur_valide_conges, date_embauche, idagence, code_client_agi, token_mobilite, droits, idutilisateur_role, role_id, initiales, actif) VALUES (5, 'Rue-Garcia', 'Christophe', 3, 'christophe.ruegarcia@pinksoft.fr', '".$hash."', NULL, 1, NULL, NULL, NULL, '".$idAgenceDev."', NULL, NULL, '{\"1\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"2\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"3\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"4\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"5\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"6\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"7\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1}}', 1, 1, NULL, true);";
$connPostgre->exec($insert);
$insert = "INSERT INTO sav.utilisateur (id, nom, prenom, idtype_login, login, password, cle_salage, idtype_utilisateur, url_homepage, idutilisateur_valide_conges, date_embauche, idagence, code_client_agi, token_mobilite, droits, idutilisateur_role, role_id, initiales, actif) VALUES (6, 'De Carvalho', 'Edson', 3, 'edecarvalho@frisquet.fr', '".$hash."', NULL, 1, NULL, NULL, NULL, '".$idAgenceDev."', NULL, NULL, '{\"1\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"2\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"3\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"4\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"5\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"6\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1},\"7\":{\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1,\"6\":1,\"7\":1,\"8\":1,\"9\":1,\"10\":1,\"11\":1,\"12\":1,\"13\":1,\"14\":1,\"15\":1}}', 1, 1, NULL, true);";
$connPostgre->exec($insert);
$insert="INSERT INTO sav.utilisateur_site (idutilisateur,idsite) VALUES(1,1),(2,1),(3,1),(4,1),(5,1),(6,1);";
$connPostgre->exec($insert);

$connPostgre->commit();
//Agents
$options = [
    'cost' => 13,
];
$tabCorrespondanceService = ['ADM' => ['role' => 11, 'profil' => 4],
    'APLN' => ['role' => 6, 'profil' => 2],
    'DIR' => ['role' => 2, 'profil' => 1],
    'ROP' => ['role' => 2, 'profil' => 1],
    'MAG' => ['role' => 9, 'profil' => 7],
    'PLN' => ['role' => 7, 'profil' => 8],
    'POLY' => ['role' => 7, 'profil' => 9],
    'RTS' => ['role' => 10, 'profil' => 6]];
$idUtilisateur = 6;
//$idAdresse = 9;
foreach($sites as $cle => $site){

    $sql = "SELECT distinct(CODE_AGENT) FROM ATT_AGENT";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    if($nbResult == 0){
        continue;
    }
    $insertUtilisateur = "INSERT INTO sav.utilisateur(id, nom, prenom, idtype_login, login, password, idtype_utilisateur, date_embauche, idagence, code_client_agi, idutilisateur_role, initiales, actif, code_agent) VALUES ";
    $insertUtilisateurAffectation = "INSERT INTO sav.utilisateur_affectation(idutilisateur, idutilisateur_profil, debut_effet, fin_effet, est_responsable, planifiable, temps_travail, actif) VALUES ";
    $insertAdresse = "INSERT INTO sav.adresse(id, idtype_adresse, adresse, adresse_complement, code_postal, ville, latitude, longitude) VALUES ";
    $insertUtilisateurAdresse = "INSERT INTO sav.utilisateur_adresse(idutilisateur, idadresse, date_application, actif) VALUES ";
    $insertUtilisateurSite = "INSERT INTO sav.utilisateur_site(idutilisateur, idsite) VALUES ";
    $insertUtilisateurTel = "INSERT INTO sav.utilisateur_tel(numero, idutilisateur, idtype_tel, priorite) VALUES ";
    $insertAgentAppareil = "INSERT INTO sav.agent_appareil(idappareil, idutilisateur_agent, date_delivrance) VALUES ";
    $insertAgentQualif = "INSERT INTO sav.agent_qualification(idutilisateur_agent, idequipement_famille) VALUES ";
    $insertAgentRestriction = "INSERT INTO sav.agent_restriction(idutilisateur_agent, idtype_planification, motif) VALUES ";
    $insertCorrespondance = "INSERT INTO sav.tmp_correspondance_agent(idsite, idutilisateur, code_agent_oracle) VALUES ";


    $sql = "SELECT * FROM AGENT";
    $stid2 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResultAgents = oci_fetch_all($stid2, $resultAgents,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabAgents = [];
    foreach($resultAgents as $cle => $rowAgent){
        $tabAgents[$rowAgent['CODE_AGENT']] = $rowAgent;
    }

    $sql = "SELECT * FROM ATT_AGENT ORDER BY DEBUT_EFFET ASC";
    $stid3 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResultAttAgents = oci_fetch_all($stid3, $resultAttAgents,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabAttAgents = [];
    foreach($resultAttAgents as $cle => $rowAttAgent){
        $tabAttAgents[$rowAttAgent['CODE_AGENT']] = $rowAttAgent;
    }

    $sql = "SELECT * FROM TCHAT_SALON_TECH";
    $stid4 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResultTchatSalonTec = oci_fetch_all($stid4, $resultTchatTec,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabSalontTech = [];
    foreach($resultTchatTec as $cle => $rowSalonTech){
        $tabSalontTech[$rowSalonTech['CODE_AGENT']] = $rowSalonTech;
    }

    $sql = "SELECT * FROM TCHAT_SALON_SEDE";
    $stid5 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResultTchatSalonSed = oci_fetch_all($stid5, $resultTchatSed,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabSalontSed = [];
    foreach($resultTchatSed as $cle => $rowSalonSed){
        $tabSalontSed[$rowSalonSed['CODE_AGENT']] = $rowSalonSed;
    }

    $sql = "SELECT * FROM PLN_AGENT_FAM_CHA";
    $stid6 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResultFam = oci_fetch_all($stid6, $resultFam,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabFam = [];
    foreach($resultFam as $cle => $rowFam){
        $tabFam[$rowFam['CODE_AGENT']][] = $rowFam;
    }

    $sql = "SELECT * FROM PLN_VISITE_EXCLUSION";
    $stid7 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResultRestrictions = oci_fetch_all($stid7, $resultRestrictions,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabRestrictions = [];
    foreach($resultRestrictions as $cle => $rowRestriction){    
        $tabRestrictions[$rowRestriction['CODE_AGENT']][] = $rowRestriction;
    }

    $sql = "SELECT * FROM PLN_AGENT";
    $stid8 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResultPLN = oci_fetch_all($stid8, $resultPLN,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabPLN = [];
    foreach($resultPLN as $cle => $rowPLN){
        $tabPLN[$rowPLN['CODE_AGENT']] = $rowPLN;
    }

    var_dump('Apres generate table ' .  date('h:i:s'));

    $count = 0;
    foreach($result as $cle => $row){
        $idUtilisateur++;
        $idAdresse++;
        $rowAgent = $tabAgents[$row['CODE_AGENT']];
        $rowAtt = $tabAttAgents[$row['CODE_AGENT']];
        $actif = true;
        if(isset($rowAtt['SERVICE']) && $rowAtt['SERVICE'] !=""){ // pas agent
            $idUtilisateurProfil = is_null($tabCorrespondanceService[$rowAtt['SERVICE']]['profil']) ? "null" : "'" . $tabCorrespondanceService[$rowAtt['SERVICE']]['profil'] . "'";
            $idUtilisateurRole = is_null($tabCorrespondanceService[$rowAtt['SERVICE']]['role']) ? "null" : "'" . $tabCorrespondanceService[$rowAtt['SERVICE']]['role'] . "'";
        }else if($rowAgent['CHEF_GROUPE'] == 'O'){//chef groupe
            $idUtilisateurProfil = "10";
            $idUtilisateurRole = "4";
        }else if(!is_null($rowAtt['FIN_EFFET']) && is_null($rowAgent['MARQUE_CO'])){//ancien salarié avec service supprimé
            $idUtilisateurProfil = "null";
            $idUtilisateurRole = "null";
            $actif=false;
        }else{//agent
            $idUtilisateurProfil = "11";
            $idUtilisateurRole = "5";
        }

        if(in_array($idUtilisateurRole, array('4','5'))){
            $rowSalon = (isset($tabSalontTech[$row['CODE_AGENT']])) ? $tabSalontTech[$row['CODE_AGENT']] : '';
        }else{
            $rowSalon = (isset($tabSalontSed[$row['CODE_AGENT']])) ? $tabSalontSed[$row['CODE_AGENT']] : '';
        }
        $resultQualif = isset($tabFam[$row['CODE_AGENT']]) ? $tabFam[$row['CODE_AGENT']] : null;
        $rowPln = isset($tabPLN[$row['CODE_AGENT']]) ? $tabPLN[$row['CODE_AGENT']]['PLANIFIABLE'] : '0';

        $idAppareil = (isset($tabSavAppareils[$rowAgent['MARQUE_CO']]) && isset($tabSavAppareils[$rowAgent['MARQUE_CO']][$rowAgent['MODELE_CO']]))
                    ? $tabSavAppareils[$rowAgent['MARQUE_CO']][$rowAgent['MODELE_CO']]
                    : '';
        $login = substr(mb_strtolower(str_replace(' ','-',$rowAgent['PRENOM_AGENT']), 'UTF-8'),0,1) . '.' . mb_strtolower(str_replace(array(' ',"'"),'',$rowAgent['NOM_AGENT']), 'UTF-8');
        $typeUtilisateur = $site['antenne'] == true ? '3' : '2';
        if($idAgenceDev == 1){ //import de seulement Bron
            $idAgence = $idAgenceDev;
        }else if($site['nom'] == 'ROSNY'){
            $idAgence = 2;
        }else{
            $idAgence = $site['id'] + 2;
        }
        $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']]=$idUtilisateur;
        $tabCorrespondanceIds[$site['id']]['responsable'][$idUtilisateur] = $rowAgent['CODE_GROUPE'];
        $tabCorrespondanceIds[$site['id']]['resp_planning'][$idUtilisateur] = $rowAtt['NUM_SEDENTAIRE'];
        $rowAgent['DATE_EMBAUCHE'] = is_null($rowAgent['DATE_EMBAUCHE']) ? "null" : "'".$rowAgent['DATE_EMBAUCHE']."'";
        $rowAtt['FIN_EFFET'] = is_null($rowAtt['FIN_EFFET']) ? "null" : "'".$rowAtt['FIN_EFFET']."'";

        $insertUtilisateur .= "('".$idUtilisateur."','".mb_strtoupper(str_replace("'","''",$rowAgent['NOM_AGENT']))."','".ucfirst(mb_strtolower(str_replace("'","''",$rowAgent['PRENOM_AGENT'])))."','3','".$login."','".password_hash($login, PASSWORD_BCRYPT, $options)."','".$typeUtilisateur."',".$rowAgent['DATE_EMBAUCHE'].",'".$idAgence."','".$rowAgent['CODE_CLIENT_AGI']."',".$idUtilisateurRole.",'".strtoupper(substr($rowAgent['PRENOM_AGENT'],0,1).substr($rowAgent['NOM_AGENT'],0,1))."','".(!$actif ? '0' : '1')."','".substr($site['nom'], 0, 1) . $rowAgent['CODE_AGENT'] ."')";
        $insertUtilisateurAffectation .= "('".$idUtilisateur."',".$idUtilisateurProfil.",".$rowAgent['DATE_EMBAUCHE'].",".$rowAtt['FIN_EFFET'].",'".($rowAgent['CHEF_GROUPE'] == 'O' ? '1' : '0')."','".$rowPln."','".(!$actif ? '0' : $rowAtt['TEMPS_PARTIEL'])."','".(!$actif ? '0' : '1')."')";

        if($rowAgent['VILLE_AGENT'] != '') {
            $insertAdresse .= "('" . $idAdresse . "','6','" . str_replace("'", "''", $rowAgent['ADR1_AGENT']) . "','" . str_replace("'", "''", $rowAgent['ADR2_AGENT']) . "','" . substr('0' . $rowAgent['CODP_AGENT'], -5) . "','" . str_replace("'", "''", $rowAgent['VILLE_AGENT']) . "','" . (is_null($rowAgent['LATITUDE']) ? '0' : str_replace(',', '.', $rowAgent['LATITUDE'])) . "','" . (is_null($rowAgent['LONGITUDE']) ? '0' : str_replace(',', '.', $rowAgent['LONGITUDE'])) . "'),";
            $insertUtilisateurAdresse .= "('" . $idUtilisateur . "','" . $idAdresse . "'," . $rowAgent['DATE_EMBAUCHE'] . ",'1'),";
        }
        $insertUtilisateurSite .= "('".$idUtilisateur."','".$site['id']."')";
        $telAgent = cleanTel($rowAgent['TEL_AGENT']);
        if($telAgent !='') {
            $insertUtilisateurTel .= "('" . $telAgent . "','" . $idUtilisateur . "','2','1'),";
        }
        if($idAppareil != ''){
            $insertAgentAppareil .= "('".$idAppareil."','".$idUtilisateur."',".$rowAgent['DATE_EMBAUCHE']."),";
        }
        $insertCorrespondance .= "('".$site['id']."','".$idUtilisateur."','".$row['CODE_AGENT']."')";
        $count++;
        $tabCorrespondanceAgent[$row['CODE_AGENT']][$site['id']] = $count;
        if(($count) != $nbResult){
            $insertUtilisateur .= ",";
            $insertUtilisateurAffectation .= ",";
            $insertUtilisateurSite .= ",";
            $insertCorrespondance .= ",";
        }
        if ($resultQualif) {
            foreach($resultQualif as $cleQualif => $rowQualif){
                if(isset($tabEquipementFamille[$rowQualif['CODE_FAM_CHA']])) {
                    $idEquipementFamille = $tabEquipementFamille[$rowQualif['CODE_FAM_CHA']];
                    $insertAgentQualif .= "('".$idUtilisateur."','".$idEquipementFamille."'),";
                }
            }
        }

        $ids = [];
        if(isset($tabRestrictions[$row['CODE_AGENT']])){
            foreach ($tabRestrictions[$row['CODE_AGENT']] as $cle => $rowRestriction){
                if(isset($typeVisite[$rowRestriction['CODE_VISITE']]) && (!isset($ids[$rowRestriction['CODE_AGENT']]) || (isset($ids[$rowRestriction['CODE_AGENT']]) && !in_array($typeVisite[$rowRestriction['CODE_VISITE']], $ids[$rowRestriction['CODE_AGENT']], true)))) {
                    $idTypePlanification = $typeVisite[$rowRestriction['CODE_VISITE']];
                    $motif = "'" . str_replace("'", "''", $rowRestriction['COMMENTAIRE']) . "'";
                    $insertAgentRestriction .= "('" . $idUtilisateur . "','" . $idTypePlanification . "'," . $motif . "),";
                    $ids[$rowRestriction['CODE_AGENT']][] = $typeVisite[$rowRestriction['CODE_VISITE']];
                }
            }
        }

    }
    $connPostgre->beginTransaction();

    $connPostgre->exec($insertUtilisateur);
    $connPostgre->exec($insertUtilisateurAffectation);
    $insertAdresse = substr($insertAdresse, 0, -1);
    $connPostgre->exec($insertAdresse);
    $insertUtilisateurAdresse = substr($insertUtilisateurAdresse, 0, -1);
    $connPostgre->exec($insertUtilisateurAdresse);
    $connPostgre->exec($insertUtilisateurSite);
    $insertUtilisateurTel = substr($insertUtilisateurTel, 0, -1);
    $connPostgre->exec($insertUtilisateurTel);
    $insertAgentAppareil = substr($insertAgentAppareil, 0, -1);
    $connPostgre->exec($insertAgentAppareil);
    $insertAgentQualif = substr($insertAgentQualif, 0, -1);
    $connPostgre->exec($insertAgentQualif);
    if (substr($insertAgentRestriction, -1) === ',') {
        $insertAgentRestriction = substr($insertAgentRestriction, 0, -1);
        $connPostgre->exec($insertAgentRestriction);
    }
    $connPostgre->exec($insertCorrespondance);

    $connPostgre->commit();
    oci_free_statement($stid);
    oci_free_statement($stid2);
    oci_free_statement($stid3);
    oci_free_statement($stid4);
    oci_free_statement($stid5);
    oci_free_statement($stid6);
    oci_free_statement($stid7);
    oci_free_statement($stid8);

//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_utilisateur.log', $insertUtilisateur, FILE_APPEND);
//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_utilisateur_affectation.log', $insertUtilisateurAffectation, FILE_APPEND);
//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_adresse.log', $insertAdresse, FILE_APPEND);
//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_utilisateur_adresse.log', $insertUtilisateurAdresse, FILE_APPEND);
//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_utilisateur_site.log', $insertUtilisateurSite, FILE_APPEND);
//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_utilisateur_tel.log', $insertUtilisateurTel, FILE_APPEND);
//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_agent_appareil.log', $insertAgentAppareil, FILE_APPEND);
//    file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_agent_qualif.log', $insertAgentQualif, FILE_APPEND);

}

$connPostgre->beginTransaction();
$idUtilisateur++;
$alter = "ALTER SEQUENCE sav.utilisateur_id_seq RESTART WITH ".($idUtilisateur).";";
$connPostgre->exec($alter);

var_dump('apres insert users ' .  date('h:i:s'));
//$idAdresse++;
//$alter = "ALTER SEQUENCE sav.adresse_id_seq RESTART WITH ".($idAdresse).";";
//$connPostgre->query($alter);

foreach($sites as $cle => $site){
    //init utilisateur responsable et utilisateur planning
    foreach($tabCorrespondanceIds[$site['id']]['responsable'] as $cle => $row) {
        if ($row != '0' && $row != '' && isset($tabCorrespondanceIds[$site['id']]['agent'][$row]) && $tabCorrespondanceIds[$site['id']]['agent'][$row] != '0' && $tabCorrespondanceIds[$site['id']]['agent'][$row] != '') {
            $update = "UPDATE sav.utilisateur_affectation SET idutilisateur_responsable = '" . $tabCorrespondanceIds[$site['id']]['agent'][$row] . "' WHERE idutilisateur = '" . $cle . "';";
            $connPostgre->exec($update);
        }
    }
    foreach($tabCorrespondanceIds[$site['id']]['resp_planning'] as $cle => $row) {
        if ($row != '0' && $row != '' && isset($tabCorrespondanceIds[$site['id']]['agent'][$row]) && $tabCorrespondanceIds[$site['id']]['agent'][$row] != '0' && $tabCorrespondanceIds[$site['id']]['agent'][$row] != '') {
            $update = "UPDATE sav.utilisateur_affectation SET idutilisateur_planning = '" . $tabCorrespondanceIds[$site['id']]['agent'][$row] . "' WHERE idutilisateur = '" . $cle . "';";
            $connPostgre->exec($update);
        }
    }

    //utilisateurs des groupes
    //groupe pôle
    $insert = "INSERT INTO sav.groupe_utilisateur(idutilisateur, idgroupe) VALUES ";
    $sql = "SELECT * FROM TCHAT_SALON_SEDE";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    if($nbResult > 0) {
        foreach ($result as $cle => $row) {
            $idPole++;
            if ($tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] != '0' && $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] != '' && $tabCorrespondanceIds[$site['id']]['salon'][$row['ID_TCHAT_SALON']] != '0' && $tabCorrespondanceIds[$site['id']]['salon'][$row['ID_TCHAT_SALON']] != '') {
                $insert .= "('" . $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] . "','" . $tabCorrespondanceIds[$site['id']]['salon'][$row['ID_TCHAT_SALON']] . "'),";
            }
        }
    }
    oci_free_statement($stid);

    $sql = "SELECT * FROM TCHAT_SALON_TECH";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    if($nbResult > 0) {
        foreach ($result as $cle => $row) {
            $idPole++;
            if ($tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] != '0' && $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] != '' && $tabCorrespondanceIds[$site['id']]['salon'][$row['ID_TCHAT_SALON']] != '0' && $tabCorrespondanceIds[$site['id']]['salon'][$row['ID_TCHAT_SALON']] != '') {
                $insert .= "('" . $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] . "','" . $tabCorrespondanceIds[$site['id']]['salon'][$row['ID_TCHAT_SALON']] . "'),";
            }
        }
    }
    oci_free_statement($stid);

    //groupe planning
    $sql = "SELECT * FROM PLN_AGENTS_LISTE WHERE IDPLN_LISTE_AGENTS IN (SELECT IDPLN_LISTE_AGENTS FROM PLN_LISTE_AGENTS WHERE LOGIN IS NULL AND NBAGENTS>1 AND DESACTIVER=0)";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    if($nbResult > 0){
        foreach($result as $cle => $row){
            $idPole++;
            if ($tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] != '0' && $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] != '' && $tabCorrespondanceIds[$site['id']]['salon'][$row['IDPLN_LISTE_AGENTS']] != '0' && $tabCorrespondanceIds[$site['id']]['salon'][$row['IDPLN_LISTE_AGENTS']] != '') {
                $insert .= "('" . $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] . "','" . $tabCorrespondanceIds[$site['id']]['salon'][$row['IDPLN_LISTE_AGENTS']] . "'),";
            }
        }
    }
    if (substr($insert, -1) === ',') {
        $insert = substr($insert, 0, -1);
        exec_free($connPostgre, $insert, $stid);
    }else{
        oci_free_statement($stid);
    }


    //coordonnées gps agent
    $batchSize = 500;  // Nombre de lignes par insertion
    $insertAgentGpsBase = "INSERT INTO sav.agent_gps(date_heure, latitude, longitude, idutilisateur_agent, radius) VALUES ";
    $batchValues = [];
    $counter = 0;

    $sql = "SELECT * FROM FMOB_GPS";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    $nbResult = oci_fetch_all($stid, $result, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);

    if ($nbResult == 0) {
        continue;
    }

    foreach ($result as $row) {
        if (!isset($tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']]) ||
            $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] == '0' ||
            $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] == '') {
            continue;
        }

        $dateGps = DateTime::createFromFormat('d/m/y H:i:s,u', $row['TS']);
        if ($dateGps) {
            $row['TS'] = $dateGps->format('Y-m-d H:i:s.u');
        } else {
            $row['TS'] = null; // Gestion des erreurs de format
        }
        // Remplacement des virgules pour PostgreSQL
        $row['LAT'] = str_replace(',', '.', $row['LAT']);
        $row['LNG'] = str_replace(',', '.', $row['LNG']);
        $row['RADIUS'] = str_replace(',', '.', $row['RADIUS']);

        // Ajout des valeurs dans le batch
        $batchValues[] = "('" . $row['TS'] . "'," . $row['LAT'] . "," . $row['LNG'] . ",'" . $tabCorrespondanceIds[$site['id']]['agent'][$row['CODE_AGENT']] . "'," . $row['RADIUS'] . ")";
        $counter++;
        // Si on atteint la taille du batch, on exécute l'insertion et on vide le tableau
        if ($counter % $batchSize == 0) {
            $insertAgentGps = $insertAgentGpsBase . implode(",", $batchValues);
            $connPostgre->exec($insertAgentGps);
            $batchValues = []; // Réinitialiser pour le prochain batch
        }
    }
    oci_free_statement($stid);

    // Insérer les dernières lignes restantes
    if (!empty($batchValues)) {
        $insertAgentGps = $insertAgentGpsBase . implode(",", $batchValues);
        $connPostgre->exec($insertAgentGps);
    }
}
$connPostgre->commit();