<?php

$nbNumClient = [];
function dropAccents($varMaChaine)
{
    $search  = array('À', 'Á', 'Â', 'Ã', 'Ä', 'Å', 'Ç', 'È', 'É', 'Ê', 'Ë', 'Ì', 'Í', 'Î', 'Ï', 'Ò', 'Ó', 'Ô', 'Õ', 'Ö', 'Ù', 'Ú', 'Û', 'Ü', 'Ý', 'à', 'á', 'â', 'ã', 'ä', 'å', 'ç', 'è', 'é', 'ê', 'ë', 'ì', 'í', 'î', 'ï', 'ð', 'ò', 'ó', 'ô', 'õ', 'ö', 'ù', 'ú', 'û', 'ü', 'ý', 'ÿ');
    $replace = array('A', 'A', 'A', 'A', 'A', 'A', 'C', 'E', 'E', 'E', 'E', 'I', 'I', 'I', 'I', 'O', 'O', 'O', 'O', 'O', 'U', 'U', 'U', 'U', 'Y', 'a', 'a', 'a', 'a', 'a', 'a', 'c', 'e', 'e', 'e', 'e', 'i', 'i', 'i', 'i', 'o', 'o', 'o', 'o', 'o', 'o', 'u', 'u', 'u', 'u', 'y', 'y');
    $varMaChaine = str_replace($search, $replace, $varMaChaine);
    return $varMaChaine;
}

function formateAdresse($varMaChaine){
    //lower, drop accent, remove espace
    $varMaChaine = strtolower(dropAccents($varMaChaine));
    $varMaChaine = str_replace(' ', '', $varMaChaine);
    return $varMaChaine;
}

function getNumClient($value, &$nbNumClient) { //Format YY MM XXXXX
    $yearmonth =  (is_null($value)) ? (string)date('ym') : $value;
    if (!isset($nbNumClient[$yearmonth]))$nbNumClient[$yearmonth] = 1;
    else $nbNumClient[$yearmonth] ++;
    return $yearmonth . str_pad($nbNumClient[$yearmonth], 5, "0", STR_PAD_LEFT);
}


$connPostgre->beginTransaction();

//Installateur
$idInstallateur = 0;
$tabCorrespondanceInstallateur = [];
foreach($sites as $cle => $site){
    $sql = "SELECT * FROM INSTALLATEUR";
    $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.installateur(id, code_agi, nom, adresse1, adresse2, code_postal, ville, tel1, tel2, gere_sav) VALUES ";
    $count = 0;
    foreach($result as $cle => $row){
        $idInstallateur++;
        $tabCorrespondanceInstallateur[$site['id']][$row['CODE']] = $idInstallateur;
        $gereSav = (isset($row['GERE_SAV']) && $row['GERE_SAV'] == 'O') ? '1' : '0';
        $insert .= "('".$idInstallateur."','".str_replace("'", "''", $row['CODE'])."','".str_replace("'", "''", $row['NOM'])."','".str_replace("'", "''", $row['ADR1'])."','".str_replace("'", "''", $row['ADR2'])."','".substr('0' . $row['CODEPOS'], -5)."','".str_replace("'", "''", $row['VILLE'])."','".str_replace(".", "", $row['TEL1'])."','".str_replace(".", "", $row['TEL2'])."','".$gereSav."')";
        $count++;
        if(($count) != $nbResult){
            $insert .= ",";
        }
    }
    exec_free($connPostgre, $insert, $stid);
}
$idInstallateur++;
$alter = "ALTER SEQUENCE sav.installateur_id_seq RESTART WITH ".($idInstallateur).";";
$connPostgre->exec($alter);


$sql = "SELECT * FROM ETAT_CIVIL WHERE CODE_TVA = 4";
$stid = oracle_execute_with_reconnect($connOracle['ROSNY'], $settings, 'ROSNY', $sql);
$nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
$insert = "INSERT INTO sav.categorie_pro(id, libelle) VALUES ";
$count = 0;
$tabCorrespondanceCategoriePro = [];
$tabCorrespondanceCategorieProLibelle = [];
foreach($result as $cle => $row){
    $count++;
    $tabCorrespondanceCategoriePro['ROSNY'][$row['CODE_EC']] = $count;
    $libelle = ucfirst(mb_strtolower(str_replace(' ','',$row['LIBELLE_EC'])));
    $tabCorrespondanceCategorieProLibelle[$libelle] = $row['CODE_EC'];
    $insert .= "('" . $count . "', '" . ucfirst(mb_strtolower($row['LIBELLE_EC'])) . "')";
    if (($count) != $nbResult) {
        $insert .= ",";
    }
}
exec_free($connPostgre, $insert, $stid);
$count++;
$alter = "ALTER SEQUENCE sav.categorie_pro_id_seq RESTART WITH ".($count).";";
$connPostgre->exec($alter);

$connPostgre->commit();


$tabCorrespondanceEtatCivil = [];
$madame = array('madame','mademoiselle','mesdames');
$monsieur = array('monsieur','messieurs');
$monsieurMadame = array('mroumme');

foreach($sites as $cle => $site) {
    if($site['nom'] != 'ROSNY') {
        $sql = "SELECT * FROM ETAT_CIVIL WHERE CODE_TVA = 4";
        $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);

        foreach ($result as $cle => $row) {
            $libelle = ucfirst(mb_strtolower(str_replace(' ','',$row['LIBELLE_EC'])));
            if(isset($tabCorrespondanceCategorieProLibelle[$libelle])) {
                $tabCorrespondanceCategoriePro[$site['nom']][$row['CODE_EC']] = $tabCorrespondanceCategoriePro['ROSNY'][$tabCorrespondanceCategorieProLibelle[$libelle]];
            }else{
                $tabCorrespondanceCategoriePro[$site['nom']][$row['CODE_EC']] = null;
            }
        }
        oci_free_statement($stid);
    }

    $sql = "SELECT * FROM ETAT_CIVIL WHERE CODE_TVA = 5";
    $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);

    foreach ($result as $cle => $row) {
        $libelle = mb_strtolower(str_replace(' ','',$row['LIBELLE_EC']));
        if(in_array($libelle,$madame)) { // madame
            $tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_EC']] = 1;
        }else if(in_array($libelle,$monsieur)) { // monsieur
            $tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_EC']] = 2;
        }else if(in_array($libelle,$monsieurMadame)) { // monsieur
            $tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_EC']] = 3;
        }else{ //autre
            $tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_EC']] = null;
        }
    }
    oci_free_statement($stid);
}
$tabClients = [];
$idClient = 0;
$idHauteurEquip = 0;
//$idAdresse = 382;
$idEquipement = 0;
$countInsertClient = 0;


foreach($sites as $cle => $site) {
    if(!$connPostgre->inTransaction()) {
        $connPostgre->beginTransaction();
    }

    $insertClient = "INSERT INTO sav.client(id, idetat_civil, nom, idtype_client, idcategorie_pro, nom_entreprise, numero_client) VALUES ";
    $insertAdresse = "INSERT INTO sav.adresse(id, idtype_adresse, adresse, adresse_complement, code_postal, ville, latitude, longitude) VALUES ";
    $insertAdresseComplement = "INSERT INTO sav.adresse_complement(idadresse, idtype_complement_adresse, valeur, commentaire_valeur) VALUES ";
    $insertClientAdresse = "INSERT INTO sav.client_adresse(idclient, idadresse, actif) VALUES ";
    $insertClientEmail = "INSERT INTO sav.client_email(idclient, email, actif) VALUES ";
    $insertClientTel = "INSERT INTO sav.client_tel(idclient, num_tel, idtype_tel, fr) VALUES ";
    $insertClientSite = "INSERT INTO sav.client_site(idclient, idsite) VALUES ";
    $insertEquipement = "INSERT INTO sav.equipement(id, idclient_souscripteur, num_serie_principal, idequipement_type_puissance, idenergie, idequipement_tranche, logement_plus_2_ans, idraccordement, date_premier_allumage, actif, idadresse, salarie_frisquet, idequipement_famille_contrat, lieu_si_autre, idequipement_local, idinstallateur, idtaux_tva, idclient_utilisateur, idsite, date_debut_garantie, date_fin_garantie, contact_fin_garantie, idequipement_famille, numero_client_oracle, date_eligible_privilege, date_derniere_v1) VALUES ";
    $insertEquipementOption = "INSERT INTO sav.equipement_option(idequipement, idoption, sous_contrat, date_souscription, prix_ht, date_debut) VALUES ";
    $insertEquipementHauteur = "INSERT INTO sav.equipement_hauteur(id, idequipement, date, hauteur, mesure_possible, escabeau_ok, entretien_ok) VALUES ";
    $insertOldBlocNoteClient = "INSERT INTO sav.old_bloc_note_client(idequipement, texte) VALUES ";
    $insertTmp = "INSERT INTO sav.tmp_correspondance_equipement(idsite, idequipement, idchaudiere_oracle, idclient) VALUES ";
    
//    if($site['nom']=='ROSNY'){
//        continue;
//    }
    $sql = "SELECT COUNT(NOM_CLIENT) AS NB, NOM_CLIENT, ADRESSE1, ADRESSE2, CODEP_CLIENT, VILLE_CLIENT FROM CLIENT GROUP BY NOM_CLIENT, ADRESSE1, ADRESSE2, CODEP_CLIENT, VILLE_CLIENT";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
        if ($row['NB'] > 1 && isset($row['NOM_CLIENT']) && isset($row['CODEP_CLIENT'])) {
            $tabClients[$row['NOM_CLIENT']][] = ['create' => false, 'adresse1' => (isset($row['ADRESSE1']) ? $row['ADRESSE1'] : null), 'adresse2' => (isset($row['ADRESSE2']) ? $row['ADRESSE2'] : null), 'code_postal' => $row['CODEP_CLIENT'], 'ville' => $row['VILLE_CLIENT']];
        }
    }
    oci_free_statement($stid);
    $sql = "SELECT COUNT(NOM_UTILISATEUR) AS NB, NOM_UTILISATEUR, ADRESSE1_UT, ADRESSE2_UT, CODEP_UTILISATEUR, VILLE_UTILISATEUR FROM CLIENT GROUP BY NOM_UTILISATEUR, ADRESSE1_UT, ADRESSE2_UT, CODEP_UTILISATEUR, VILLE_UTILISATEUR";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
    while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
        if ($row['NB'] > 1 && isset($row['NOM_UTILISATEUR']) && isset($row['CODEP_UTILISATEUR'])) {
            $tabClients[$row['NOM_UTILISATEUR']][] = ['create' => false, 'adresse1' => (isset($row['ADRESSE1_UT']) ? $row['ADRESSE1_UT'] : null), 'adresse2' => (isset($row['ADRESSE2_UT']) ? $row['ADRESSE2_UT'] : null), 'code_postal' => $row['CODEP_UTILISATEUR'], 'ville' => $row['VILLE_UTILISATEUR']];
        }
    }
    oci_free_statement($stid);

    var_dump('apres client count ' . $site['nom'] . ' ' . date('h:i:s'));


    /*  $stid = oci_parse($connOracle[$site['nom']], "SELECT c.*, trunc(c.ALLUMAGE, 'MM') DEBUT_GARANTIE, trunc(c.ALLUMAGE, 'MM')+364 FIN_GARANTIE, g.LATITUDE, g.LONGITUDE,
  l.ID_LOCAL_EQUIP, l.LIEU_SI_AUTRE, t.AGE, b.TEXTE, d.DATE_DERNIER_ENTRETIEN FROM CLIENT c LEFT JOIN GEO_CLIENT g ON  c.IDCHAUDIERE = g.IDCHAUDIERE LEFT JOIN CLIENT_LOCAL_EQUIPEMENT l ON c.IDCHAUDIERE = l.IDCHAUDIERE
  LEFT JOIN TRANCHE t ON c.TRANCHE_CHAUDIERE = t.CATEGORIE LEFT JOIN BLOC_NOTE_CLIENT b ON c.IDCHAUDIERE = b.IDCHAUDIERE LEFT JOIN CLIENT_DERNIER_ENTRETIEN d ON c.IDCHAUDIERE = d.IDCHAUDIERE");
    */

//exclusion des clients fictifs techniciens
    $sql = "SELECT * FROM (
    SELECT c.*, 
           TO_CHAR(TO_DATE(c.ALLUMAGE, 'DD/MM/RRRR'), 'YYMM') as yearmonth, 
           TRUNC(c.ALLUMAGE, 'MM') AS DEBUT_GARANTIE, 
           TRUNC(c.ALLUMAGE, 'MM') + 364 AS FIN_GARANTIE, 
           g.LATITUDE, 
           g.LONGITUDE, 
           l.ID_LOCAL_EQUIP, 
           l.LIEU_SI_AUTRE, 
           t.AGE, 
           b.TEXTE, 
           d.DATE_DERNIER_ENTRETIEN, 
           ROWNUM AS rn 
    FROM CLIENT c 
    LEFT JOIN GEO_CLIENT g ON c.IDCHAUDIERE = g.IDCHAUDIERE 
    LEFT JOIN CLIENT_LOCAL_EQUIPEMENT l ON c.IDCHAUDIERE = l.IDCHAUDIERE 
    LEFT JOIN TRANCHE t ON c.TRANCHE_CHAUDIERE = t.CATEGORIE 
    LEFT JOIN BLOC_NOTE_CLIENT b ON c.IDCHAUDIERE = b.IDCHAUDIERE 
    LEFT JOIN CLIENT_DERNIER_ENTRETIEN d ON c.IDCHAUDIERE = d.IDCHAUDIERE
    ) 
    WHERE NOT (
        (num_dep = (SELECT VALEUR_ALPHANUM FROM PLN_PARAMETRES WHERE NOM_PARAM = 'NUM_DEP_AGENT')
        AND num_commune = (SELECT VALEUR_ALPHANUM FROM PLN_PARAMETRES WHERE NOM_PARAM = 'NUM_COMMUNE_AGENT')
        AND num_famille = (SELECT VALEUR_ALPHANUM FROM PLN_PARAMETRES WHERE NOM_PARAM = 'NUM_FAMILLE_AGENT')
        AND num_ordre IN (SELECT CODE_AGENT FROM AGENT) 
        AND UPPER(nom_client) LIKE '%TECHNICIEN%')  
        OR 
        (num_dep = 77
        AND num_commune = 290
        AND num_famille = 2
        AND num_ordre IN (SELECT CODE_AGENT FROM AGENT) 
        AND UPPER(nom_client) LIKE '%TECHNICIEN%') 
    )";
    $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;
    }
    $selectOptionOracle = 'SELECT * FROM CLIENT_OPTION ';
    $stid2 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $selectOptionOracle);
    $nbResultOptions = oci_fetch_all($stid2, $resultOptions, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
    $tabOptionsOracle = [];
    foreach ($resultOptions as $cle => $rowOption) {
        $tabOptionsOracle[$rowOption['IDCHAUDIERE']][] = $rowOption;
    }
    oci_free_statement($stid2);
    $nbClient = 0;
    $nb = 0;
    foreach ($result as $cle => $row) {
        $nb++;
        if ($row['NUM_FAMILLE'] > 3) {
            continue;
        }

        if ($nb % 5000 == 0) {
            var_dump($nb . ' ' . date('h:i:s'));
        }


        $idSouscripteur = '';
        $idUtilisateur = '';
        $idAdresseSouscripteur = "null";
        $idAdresseUtilisateur = "null";
        $flagUtilisateur = false;

        if (isset($row['CODEP_UTILISATEUR'])) {
            $flagUtilisateur = true;
        }
        $numClientOracle = substr('00' . $row['NUM_DEP'], -2) . substr('000' . $row['NUM_COMMUNE'], -3) . $row['NUM_FAMILLE'] . substr('0000' . $row['NUM_ORDRE'], -4);

        //client souscripteur
        $cleCorrespondance = null;
        if (isset($tabClients[$row['NOM_CLIENT']])) {
            foreach ($tabClients[$row['NOM_CLIENT']] as $cle => $value) {
                //strtolower($value['adresse1'])
                if (((isset($value['adresse1']) && (formateAdresse($value['adresse1']) == formateAdresse($row['ADRESSE1']) || formateAdresse($value['adresse1']) == formateAdresse($row['ADRESSE2'])))
                        || (isset($value['adresse2']) && (formateAdresse($value['adresse2']) == formateAdresse($row['ADRESSE2']) || formateAdresse($value['adresse2']) == formateAdresse($row['ADRESSE1']))))
                    && (isset($value['code_postal']) && $value['code_postal'] == $row['CODEP_CLIENT']) && formateAdresse($value['ville']) == formateAdresse($row['VILLE_CLIENT'])) {
                    $cleCorrespondance = $cle;
                    break;
                }
            }
        }

        if ((isset($row['CODE_ETATCIV']) || isset($row['NOM_CLIENT'])) && (is_null($cleCorrespondance) || (!is_null($cleCorrespondance) && $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['create'] == false))) {
            $idClient++;
            $idAdresse++;
            $idtype_client = '1';
            $idetat_civil = "null";
            $idcategorie_pro = "null";
            $nomClient = '';
            $nomEntreprise = '';

            if (isset($tabCorrespondanceCategoriePro[$site['nom']][$row['CODE_ETATCIV']])) {
                $idtype_client = '2';
                $idcategorie_pro = "'" . $tabCorrespondanceCategoriePro[$site['nom']][$row['CODE_ETATCIV']] . "'";
                $nomEntreprise = ucfirst(mb_strtolower(str_replace("'", "''", $row['NOM_CLIENT'])));
            } else {
                if (isset($tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_ETATCIV']])) {
                    $idetat_civil = "'" . $tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_ETATCIV']] . "'";
                }
                $nomClient = ucfirst(mb_strtolower(str_replace("'", "''", $row['NOM_CLIENT'])));
            }
            $latitude = (is_null($row['LATITUDE']) ? '0' : str_replace(',', '.', $row['LATITUDE']));
            $longitude = (is_null($row['LONGITUDE']) ? '0' : str_replace(',', '.', $row['LONGITUDE']));
            if ($flagUtilisateur) {
                $latitude = '0';
                $longitude = '0';
            }
            $numClient = getNumClient($row['YEARMONTH'], $nbNumClient);
            $insertClient .= "('".$idClient."',".$idetat_civil.",'".strtoupper($nomClient)."','".$idtype_client."',".$idcategorie_pro.",'".$nomEntreprise."', '".$numClient."'),";
            $insertAdresse .= "('" . $idAdresse . "','1','" . str_replace("'", "''", $row['ADRESSE1']) . "','" . str_replace("'", "''", $row['ADRESSE2']) . "','" . substr('0' . $row['CODEP_CLIENT'], -5) . "','" .  ucfirst(mb_strtolower(str_replace("'", "''", $row['VILLE_CLIENT']))) . "','" . $latitude . "','" . $longitude . "'),";
            if(!$flagUtilisateur){
                if(isset($row['BATIMENT']) && $row['BATIMENT']!=''){
                    $insertAdresseComplement .= "('" . $idAdresse . "','2','" . str_replace("'", "''", $row['BATIMENT']) . "',null),";
                }
                if (isset($row['ESCALIER']) && $row['ESCALIER'] != '') {
                    $insertAdresseComplement .= "('" . $idAdresse . "','7','" . str_replace("'", "''", $row['ESCALIER']) . "','null'),";
                }
                if (isset($row['ETAGE']) && $row['ETAGE'] != '') {
                    $insertAdresseComplement .= "('" . $idAdresse . "','4','" . str_replace("'", "''", $row['ETAGE']) . "',null),";
                }
                if (isset($row['PORTE']) && $row['PORTE'] != '') {
                    $insertAdresseComplement .= "('" . $idAdresse . "','3','" . str_replace("'", "''", $row['PORTE']) . "',null),";
                }
                if (isset($row['DIGICODE']) && $row['DIGICODE'] != '') {
                    $insertAdresseComplement .= "('" . $idAdresse . "','1','" . str_replace("'", "''", $row['DIGICODE']) . "',null),";
                }
            }
            $insertClientAdresse .= "('" . $idClient . "','" . $idAdresse . "','1'),";

            if (!isset($tabClients[$row['NOM_CLIENT']]) || is_null($cleCorrespondance)) {
                $cleCorrespondance = 0;
                if (isset($tabClients[$row['NOM_CLIENT']])) {
                    $cleCorrespondance = count($tabClients[$row['NOM_CLIENT']]);
                }
                $tabClients[$row['NOM_CLIENT']][$cleCorrespondance] = ['create' => true, 'adresse1' => (isset($row['ADRESSE1']) ? $row['ADRESSE1'] : null), 'adresse2' => (isset($row['ADRESSE2']) ? $row['ADRESSE2'] : null), 'code_postal' => $row['CODEP_CLIENT'], 'ville' => $row['VILLE_CLIENT']];
            }
            if (isset($tabClients[$row['NOM_CLIENT']]) && isset($tabClients[$row['NOM_CLIENT']][$cleCorrespondance])) {
                $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['create'] = true;
                $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['id'] = $idClient;
                $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['idAdresse'] = $idAdresse;
                $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['email'] = [];
                $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'] = [];
                $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site'] = [];
            }

            if (isset($row['EMAIL']) && $row['EMAIL'] != '') {
                $email = str_replace("'", "", $row['EMAIL']);
                if (!in_array($email, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['email']) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $insertClientEmail .= "('" . $idClient . "','" . $email . "','1'),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['email'][] = $email;
                }
            }

            if (isset($row['TEL_DOMICILE']) && cleanTel($row['TEL_DOMICILE']) != '') {
                $tel = cleanTel($row['TEL_DOMICILE']);
                if (!in_array($tel, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idClient . "','" . $tel . "','1'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
            if (isset($row['TEL_PORTABLE']) && cleanTel($row['TEL_PORTABLE']) != '') {
                $tel = cleanTel($row['TEL_PORTABLE']);
                if (!in_array($tel, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idClient . "','" . $tel . "','2'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
            if (isset($row['TEL_BUREAU']) && cleanTel($row['TEL_BUREAU']) != '') {
                $tel = cleanTel($row['TEL_BUREAU']);
                if (!in_array($tel, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idClient . "','" . $tel . "','3'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
        }
        if (isset($row['NOM_CLIENT']) && !is_null($cleCorrespondance) && isset($tabClients[$row['NOM_CLIENT']]) && $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['create'] == true) {
            $idSouscripteur = $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['id'];
            $idAdresseSouscripteur = $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['idAdresse'];

            //doublon ajout numéro + mail différent
            if (isset($row['EMAIL']) && $row['EMAIL'] != '') {
                $email = str_replace("'", "", $row['EMAIL']);
                if (!in_array($email, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['email']) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $insertClientEmail .= "('" . $idSouscripteur . "','" . $email . "','1'),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['email'][] = $email;
                }
            }
            if (isset($row['TEL_DOMICILE']) && cleanTel($row['TEL_DOMICILE']) != '') {
                $tel = cleanTel($row['TEL_DOMICILE']);
                if (!in_array($tel, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idSouscripteur . "','" . $tel . "','1'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
            if (isset($row['TEL_PORTABLE']) && cleanTel($row['TEL_PORTABLE']) != '') {
                $tel = cleanTel($row['TEL_PORTABLE']);
                if (!in_array($tel, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idSouscripteur . "','" . $tel . "','2'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
            if (isset($row['TEL_BUREAU']) && cleanTel($row['TEL_BUREAU']) != '') {
                $tel = cleanTel($row['TEL_BUREAU']);
                if (!in_array($tel, $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idSouscripteur . "','" . $tel . "','3'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
        } else if (isset($row['CODE_ETATCIV']) || isset($row['NOM_CLIENT'])) {
            $idSouscripteur = $idClient;
            $idAdresseSouscripteur = $idAdresse;
        }
        if ($idSouscripteur != '' && !is_null($cleCorrespondance) && (!isset($tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site']) || (isset($tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site']) && !in_array($site['id'], $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site'])))) {
            if (is_null($idSouscripteur)) var_dump('null $idSouscripteur');
            if (is_null($site['id'])) var_dump($site['id']);
            $insertClientSite .= "(" . $idSouscripteur . ",'" . $site['id'] . "'),";
            $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site'][] = $site['id'];

        }

        if ($idSouscripteur != '' && !is_null($cleCorrespondance) && (!isset($tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site']) || (isset($tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site']) && !in_array($site['id'], $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site'])))) {
            $insertClientSite .= "(" . $idSouscripteur . ",'" . $site['id'] . "'),";
            $tabClients[$row['NOM_CLIENT']][$cleCorrespondance]['site'][] = $site['id'];
        }

        //client utilisateur
        $cleCorrespondance = null;
        if (isset($tabClients[$row['NOM_UTILISATEUR']])) {
            foreach ($tabClients[$row['NOM_UTILISATEUR']] as $cle => $value) {
                if (((isset($value['adresse1']) && formateAdresse($value['adresse1']) == formateAdresse($row['ADRESSE1_UT'])) && (isset($value['adresse2']) && formateAdresse($value['adresse2']) == formateAdresse($row['ADRESSE2_UT'])))
                    && (isset($value['code_postal']) && $value['code_postal'] == $row['CODEP_UTILISATEUR'])
                    && (isset($value['ville']) && formateAdresse($value['ville']) == formateAdresse($row['VILLE_UTILISATEUR']))) {
                    $cleCorrespondance = $cle;
                    break;
                }
            }
        }
        if ((isset($row['CODE_ETATCIV_UT']) && isset($row['CODEP_UTILISATEUR'])) && (is_null($cleCorrespondance) || (!is_null($cleCorrespondance) && $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['create'] == false))) {
            $idClient++;
            $idAdresse++;
            $idtype_client = '1';
            $idetat_civil = "null";
            $idcategorie_pro = "null";
            $nomClient = '';
            $nomEntreprise = '';
            if (isset($tabCorrespondanceCategoriePro[$site['nom']][$row['CODE_ETATCIV_UT']])) {
                $idtype_client = '2';
                $idcategorie_pro = "'" . $tabCorrespondanceCategoriePro[$site['nom']][$row['CODE_ETATCIV_UT']] . "'";
                $nomEntreprise = ucfirst(mb_strtolower(str_replace("'", "''", $row['NOM_UTILISATEUR'])));
            } else {
                if (isset($tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_ETATCIV_UT']])) {
                    $idetat_civil = "'" . $tabCorrespondanceEtatCivil[$site['nom']][$row['CODE_ETATCIV_UT']] . "'";
                }
                $nomClient = ucfirst(mb_strtolower(str_replace("'", "''", $row['NOM_UTILISATEUR'])));
            }


            //numéro client
            $numClient = '';
            $numClient = getNumClient($row['YEARMONTH'], $nbNumClient);

            $insertClient .= "('" . $idClient . "'," . $idetat_civil . ",'" . $nomClient . "','" . $idtype_client . "'," . $idcategorie_pro . ",'" . $nomEntreprise . "', '" . $numClient . "'),";
            $insertAdresse .= "('" . $idAdresse . "','3','" . str_replace("'", "''", $row['ADRESSE1_UT']) . "','" . str_replace("'", "''", $row['ADRESSE2_UT']) . "','" . substr('0' . $row['CODEP_UTILISATEUR'], -5) . "','" . ucfirst(mb_strtolower(str_replace("'", "''", $row['VILLE_UTILISATEUR']))) . "','" . (is_null($row['LATITUDE']) ? '0' : str_replace(',', '.', $row['LATITUDE'])) . "','" . (is_null($row['LONGITUDE']) ? '0' : str_replace(',', '.', $row['LONGITUDE'])) . "'),";

            if (isset($row['BATIMENT']) && $row['BATIMENT'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','2','" . str_replace("'", "''", $row['BATIMENT']) . "',null),";
            }
            if (isset($row['ESCALIER']) && $row['ESCALIER'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','6','" . str_replace("'", "''", $row['ESCALIER']) . "','Escalier'),";
            }
            if (isset($row['ETAGE']) && $row['ETAGE'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','4','" . str_replace("'", "''", $row['ETAGE']) . "',null),";
            }
            if (isset($row['PORTE']) && $row['PORTE'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','3','" . str_replace("'", "''", $row['PORTE']) . "',null),";
            }
            if (isset($row['DIGICODE']) && $row['DIGICODE'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','1','" . str_replace("'", "''", $row['DIGICODE']) . "',null),";
            }
            $insertClientAdresse .= "('" . $idClient . "','" . $idAdresse . "','1'),";


            if (!isset($tabClients[$row['NOM_UTILISATEUR']]) || is_null($cleCorrespondance)) {
                $cleCorrespondance = 0;
                if (isset($tabClients[$row['NOM_CLIENT']])) {
                    $cleCorrespondance = count($tabClients[$row['NOM_CLIENT']]);
                }
                $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance] = ['create' => true, 'adresse1' => (isset($row['ADRESSE1']) ? $row['ADRESSE1'] : null), 'adresse2' => (isset($row['ADRESSE2']) ? $row['ADRESSE2'] : null), 'code_postal' => $row['CODEP_CLIENT'], 'ville' => $row['VILLE_CLIENT']];
            }
            if (isset($tabClients[$row['NOM_UTILISATEUR']]) && isset($tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance])) {
                $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['create'] = true;
                $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['id'] = $idClient;
                $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['idAdresse'] = $idAdresse;
                $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['email'] = [];
                $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'] = [];
            }
            if (isset($row['EMAIL_UT']) && $row['EMAIL_UT'] != '') {
                $email = str_replace("'", "", $row['EMAIL_UT']);
                if (!in_array($email, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['email']) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $insertClientEmail .= "('" . $idClient . "','" . $email . "','1'),";
                    $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['email'][] = $email;
                }
            }
            if (isset($row['TEL_DOMICILE_UT']) && cleanTel($row['TEL_DOMICILE_UT']) != '') {
                $tel = cleanTel($row['TEL_DOMICILE_UT']);
                if (!in_array($tel, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idClient . "','" . $tel . "','1'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
            if (isset($row['TEL_PORTABLE_UT']) && cleanTel($row['TEL_PORTABLE_UT']) != '') {
                $tel = cleanTel($row['TEL_PORTABLE_UT']);
                if (!in_array($tel, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idClient . "','" . $tel . "','2'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }
            if (isset($row['TEL_BUREAU_UT']) && cleanTel($row['TEL_BUREAU_UT']) != '') {
                $tel = cleanTel($row['TEL_BUREAU_UT']);
                if (!in_array($tel, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'])) {
                    $insertClientTel .= "('" . $idClient . "','" . $tel . "','3'," . telFr($tel) . "),";
                    $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'][] = $tel;
                }
            }

            if (!is_null($cleCorrespondance) && isset($tabClients[$row['NOM_UTILISATEUR']]) && $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['create'] == true) {
                $idUtilisateur = $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['id'];
                $idAdresseUtilisateur = $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['idAdresse'];

                //doublon ajout numéro + mail différent
                if (isset($row['EMAIL_UT']) && $row['EMAIL_UT'] != '') {
                    $email = str_replace("'", "", $row['EMAIL_UT']);
                    if (!in_array($email, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['email']) && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                        $insertClientEmail .= "('" . $idUtilisateur . "','" . $email . "','1'),";
                        $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['email'][] = $email;
                    }
                }
                if (isset($row['TEL_DOMICILE_UT']) && cleanTel($row['TEL_DOMICILE_UT']) != '') {
                    $tel = cleanTel($row['TEL_DOMICILE_UT']);
                    if (!in_array($tel, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'])) {
                        $insertClientTel .= "('" . $idUtilisateur . "','" . $tel . "','1'," . telFr($tel) . "),";
                        $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'][] = $tel;
                    }
                }
                if (isset($row['TEL_PORTABLE_UT']) && cleanTel($row['TEL_PORTABLE_UT']) != '') {
                    $tel = cleanTel($row['TEL_DOMICILE_UT']);
                    if (!in_array($tel, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'])) {
                        $insertClientTel .= "('" . $idUtilisateur . "','" . $tel . "','2'," . telFr($tel) . "),";
                        $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'][] = $tel;
                    }
                }
                if (isset($row['TEL_BUREAU_UT']) && cleanTel($row['TEL_BUREAU_UT']) != '') {
                    $tel = cleanTel($row['TEL_DOMICILE_UT']);
                    if (!in_array($tel, $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'])) {
                        $insertClientTel .= "('" . $idUtilisateur . "','" . $tel . "','3'," . telFr($tel) . "),";
                        $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['telephone'][] = $tel;
                    }
                }
            } else {
                $idUtilisateur = $idClient;
                $idAdresseUtilisateur = $idAdresse;
            }
        } else if (!isset($row['CODE_ETATCIV_UT']) && isset($row['CODEP_UTILISATEUR'])) { //logement vide
            $idAdresse++;
            $insertAdresse .= "('" . $idAdresse . "','3','" . str_replace("'", "''", $row['ADRESSE1_UT']) . "','" . str_replace("'", "''", $row['ADRESSE2_UT']) . "','" . substr('0' . $row['CODEP_UTILISATEUR'], -5) . "','" . str_replace("'", "''", $row['VILLE_UTILISATEUR']) . "','" . (is_null($row['LATITUDE']) ? '0' : str_replace(',', '.', $row['LATITUDE'])) . "','" . (is_null($row['LONGITUDE']) ? '0' : str_replace(',', '.', $row['LONGITUDE'])) . "'),";
            $idAdresseUtilisateur = $idAdresse;
            if (isset($row['BATIMENT']) && $row['BATIMENT'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','2','" . str_replace("'", "''", $row['BATIMENT']) . "',null),";
            }
            if (isset($row['ESCALIER']) && $row['ESCALIER'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','6','" . str_replace("'", "''", $row['ESCALIER']) . "','Escalier'),";
            }
            if (isset($row['ETAGE']) && $row['ETAGE'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','4','" . str_replace("'", "''", $row['ETAGE']) . "',null),";
            }
            if (isset($row['PORTE']) && $row['PORTE'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','3','" . str_replace("'", "''", $row['PORTE']) . "',null),";
            }
            if (isset($row['DIGICODE']) && $row['DIGICODE'] != '') {
                $insertAdresseComplement .= "('" . $idAdresse . "','1','" . str_replace("'", "''", $row['DIGICODE']) . "',null),";
            }
        }

        if ($idUtilisateur != '' && !is_null($cleCorrespondance) && (!isset($tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['site']) || (isset($tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['site']) && !in_array($site['id'], $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['site'])))) {
            $insertClientSite .= "(" . $idUtilisateur . ",'" . $site['id'] . "'),";
            $tabClients[$row['NOM_UTILISATEUR']][$cleCorrespondance]['site'][] = $site['id'];
        }

        //equipement
        if ((isset($row['NUM_CHAUDIERE']) && $row['NUM_CHAUDIERE'] != '') || $row['TYPE_CHAUDIERE'] == 'TEAMAO') {
            $idEquipementTypePuissance = "null";
            if (isset($row['TYPE_CHAUDIERE']) && isset($row['PUISSANCE'])) {
                $select = "SELECT id FROM sav.equipement_type_puissance WHERE idequipement_type = (SELECT id FROM sav.equipement_type WHERE type = '" . $row['TYPE_CHAUDIERE'] . "') AND idequipement_puissance = (SELECT id FROM sav.equipement_puissance WHERE libelle = '" . $row['PUISSANCE'] . "')";
                if (!is_bool($connPostgre->query($select)) && $connPostgre->query($select)->fetch() != false) {
                    $idEquipementTypePuissance = $connPostgre->query($select)->fetch()['id'];
                }
            }
            $idEnergie = "null";
            $idEquipementTranche = "null";
            if (isset($row['AGE'])) {
                $select = "SELECT id FROM sav.equipement_tranche WHERE age ='" . $row['AGE'] . "'";
                if (!is_bool($connPostgre->query($select)) && $connPostgre->query($select)->fetch()) {
                    $idEquipementTranche = $connPostgre->query($select)->fetch()['id'];
                }
            }
            if (isset($row['NATURE_GAZ']) && $row['NATURE_GAZ'] == 'NAT') {
                $idEnergie = '1';
            } else if (isset($row['NATURE_GAZ']) && $row['NATURE_GAZ'] == 'PRO') {
                $idEnergie = '3';
            }
            $idEquipementFamille = "null";
            if (isset($row['FAMILLE_CHAUDIERE'])) {
                $select = "SELECT id FROM sav.equipement_famille WHERE code ='" . $row['FAMILLE_CHAUDIERE'] . "'";
                if (!is_bool($connPostgre->query($select)) && $connPostgre->query($select)->fetch()) {
                    $idEquipementFamille = $connPostgre->query($select)->fetch()['id'];
                }
            }
            $row['ALLUMAGE'] = is_null($row['ALLUMAGE']) ? "null" : "'" . $row['ALLUMAGE'] . "'";
            $row['NUM_CHAUDIERE'] = is_null($row['NUM_CHAUDIERE']) ? "null" : "'" . substr('00000000000000' . $row['NUM_CHAUDIERE'], -14) . "'";
            $allumageDate = null; // Utiliser null au lieu de "null"
            if (!empty($row['ALLUMAGE'])) {
                // Si ALLUMAGE est disponible, on l’utilise
                $allumageDate = DateTime::createFromFormat('d/m/y', $row['ALLUMAGE']);
            } elseif (!empty($row['NUM_CHAUDIERE'])) {
                // Sinon, on essaie de la calculer à partir du NUM_SERIE
                $now = new DateTime();
                $year = substr($row['NUM_CHAUDIERE'], 0, 2);
                $week = substr($row['NUM_CHAUDIERE'], 2, 2);
                $year = ($year > $now->format('y')) ? '19' . $year : '20' . $year;
                $allumageDate = (new DateTime())->setISODate($year, $week);
            }
            // Vérifier si $allumageDate est bien un objet DateTime avant d'utiliser modify()
            $row['DATE_PRIVILEGE'] = $allumageDate instanceof DateTime
                ? "'" . $allumageDate->modify('+23 years')->format('Y-m-d') . "'"
                : "null";
            $row['DEBUT_GARANTIE'] = is_null($row['DEBUT_GARANTIE']) ? "null" : "'" . $row['DEBUT_GARANTIE'] . "'";
            $row['FIN_GARANTIE'] = is_null($row['FIN_GARANTIE']) ? "null" : "'" . $row['FIN_GARANTIE'] . "'";
            $row['DATE_DERNIER_ENTRETIEN'] = is_null($row['DATE_DERNIER_ENTRETIEN']) ? "null" : "'" . $row['DATE_DERNIER_ENTRETIEN'] . "'";
            $idAdresseEquipement = ($idAdresseUtilisateur != "null") ? "'" . $idAdresseUtilisateur . "'" : ($idAdresseSouscripteur != "null" ? "'" . $idAdresseSouscripteur . "'" : "null");
            $idequipement_famille_contrat = ($row['NUM_FAMILLE'] == '3' ? $row['NUM_FAMILLE'] : ($row['NUM_FAMILLE'] + 1));
            $row['ID_LOCAL_EQUIP'] = (isset($row['ID_LOCAL_EQUIP']) && $row['ID_LOCAL_EQUIP'] != '0') ? "'" . $row['ID_LOCAL_EQUIP'] . "'" : "null";
            $idInstallateurEquip = "null";
            if (isset($row['INSTALLATEUR']) && $row['INSTALLATEUR'] != '' && isset($tabCorrespondanceInstallateur[$site['id']][$row['INSTALLATEUR']])) {
                $idInstallateurEquip = $tabCorrespondanceInstallateur[$site['id']][$row['INSTALLATEUR']];
            }
            $contactFinGarantie = (isset($row['ECHEANCIER']) && $row['ECHEANCIER'] == 'MOD') ? '1' : '0';
            $salarieFrisquet = (isset($row['GROUPE']) && $row['GROUPE'] == '92') ? '1' : '0';
            $row['VMC'] = (isset($row['VMC']) && $row['VMC'] == '1') ? "'2'" : "null";
            $idEquipement++;

            $insertEquipement .= "('" . $idEquipement . "'," . ($idSouscripteur == '' ? "null" : "'" . $idSouscripteur . "'") . "," . $row['NUM_CHAUDIERE'] . "," . $idEquipementTypePuissance . "," . $idEnergie . "," . $idEquipementTranche . ",'" . ($row['LOGEMENT_PLUS_2_ANS'] == '' ? '0' : $row['LOGEMENT_PLUS_2_ANS']) . "'," . $row['VMC'] . "," . $row['ALLUMAGE'] . ",'1'," . $idAdresseEquipement . ",'" . $salarieFrisquet . "','" . $idequipement_famille_contrat . "','" . str_replace("'", "''", $row['LIEU_SI_AUTRE']) . "'," . $row['ID_LOCAL_EQUIP'] . "," . $idInstallateurEquip . ",'" . $row['CODE_TVA'] . "'," . ($idUtilisateur == '' ? "null" : "'" . $idUtilisateur . "'") . "," . $site['id'] . "," . $row['DEBUT_GARANTIE'] . "," . $row['FIN_GARANTIE'] . ",'" . $contactFinGarantie . "'," . $idEquipementFamille . ",'" . $numClientOracle . "'," . $row['DATE_PRIVILEGE'] . "," . $row['DATE_DERNIER_ENTRETIEN'] . "),";

            $sql = "SELECT * FROM CLIENT_OPTION WHERE IDCHAUDIERE = '" . $row['IDCHAUDIERE'] . "'";
            $stid2 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
            $nbResult = oci_fetch_all($stid2, $result, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
            foreach ($result as $cle => $rowOption) {
                $select = "SELECT id FROM sav.option WHERE libelle_court ='" . $rowOption['OPT_DESIGNATION'] . "'";
                $idOption = (!is_bool($connPostgre->query($select)) && $connPostgre->query($select)->fetch() ? $connPostgre->query($select)->fetch()['id'] : '');
                if ($idOption != '') {
                    $rowOption['OPT_DATE'] = is_null($rowOption['OPT_DATE']) ? "null" : "'" . $rowOption['OPT_DATE'] . "'";
                    $rowOption['PRIX_HT'] = (isset($rowOption['PRIX_HT']) ? str_replace(',', '.', $rowOption['PRIX_HT']) : '0');
                    $insertEquipementOption .= "('" . $idEquipement . "', '" . $idOption . "', '" . ($rowOption['IDC_SOUS_CONTRAT'] == 'O' ? '1' : '0') . "', " . $rowOption['OPT_DATE'] . ", '" . $rowOption['PRIX_HT'] . "', " . $rowOption['OPT_DATE'] . "),";
                }
            }
            oci_free_statement($stid2);

            if($site['nom'] == 'ROSNY' || $site['nom'] == 'BRON') { //condition à enlever quand ce sera déployé partout.
                $sql = "SELECT * FROM HAUTEUR_CHAUDIERE WHERE IDCHAUDIERE = '" . $row['IDCHAUDIERE'] . "'";
                $stid2 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
                if ($rowHauteur = oci_fetch_assoc($stid2)) {
                    $idHauteurEquip++;
                    $rowHauteur['HAUTEUR_CHAUDIERE'] = (isset($rowHauteur['HAUTEUR_CHAUDIERE']) ? str_replace(',', '.', $rowHauteur['HAUTEUR_CHAUDIERE']) : '0');
                    $insertEquipementHauteur .= "('" . $idHauteurEquip . "', '" . $idEquipement . "', NOW(), '" . $rowHauteur['HAUTEUR_CHAUDIERE'] . "','" . $rowHauteur['MESURE_POSSIBLE'] . "','" . $rowHauteur['ESCABEAU_OK'] . "','" . $rowHauteur['ENTRETIEN_OK'] . "'),";
                }
                oci_free_statement($stid2);
            }


            $blocNote = str_replace("'", "''", $row['TEXTE']);
            if (isset($row['MEMOA']) && $row['MEMOA'] != '') {
                $blocNote .= ' ' . str_replace("'", "''", $row['MEMOA']);
            }
            if (isset($row['MEMOB']) && $row['MEMOB'] != '') {
                $blocNote .= ' ' . str_replace("'", "''", $row['MEMOB']);
            }
            if ($blocNote != '') {
                $insertOldBlocNoteClient .= "('" . $idEquipement . "', '" . $blocNote . "'),";
            }

            $insertTmp .= "('" . $site['id'] . "','" . $idEquipement . "', '" . $row['IDCHAUDIERE'] . "'," . ($idSouscripteur == '' ? "null" : "'" . $idSouscripteur . "'") . "),";
        }

        if ($nb == 100000) {
            if (substr($insertClient, -1) == ',') {
                $insertClient = substr($insertClient, 0, -1);
                $connPostgre->exec($insertClient);
                // file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_client.log', $insertClient, FILE_APPEND);
                if (substr($insertAdresse, -1) == ',') {
                    $insertAdresse = substr($insertAdresse, 0, -1);
                    $connPostgre->exec($insertAdresse);
                }
                if (substr($insertAdresseComplement, -1) == ',') {
                    $insertAdresseComplement = substr($insertAdresseComplement, 0, -1);
                    $connPostgre->exec($insertAdresseComplement);
                }
                if (substr($insertClientAdresse, -1) == ',') {
                    $insertClientAdresse = substr($insertClientAdresse, 0, -1);
                    $connPostgre->exec($insertClientAdresse);
                }
                if (substr($insertClientEmail, -1) == ',') {
                    $insertClientEmail = substr($insertClientEmail, 0, -1);
                    $connPostgre->exec($insertClientEmail);
                }
                if (substr($insertClientTel, -1) == ',') {
                    $insertClientTel = substr($insertClientTel, 0, -1);
                    $connPostgre->exec($insertClientTel);
                }
                if (substr($insertEquipement, -1) == ',') {
                    $insertEquipement = substr($insertEquipement, 0, -1);
                    $connPostgre->exec($insertEquipement);
                }
                if (substr($insertEquipementOption, -1) == ',') {
                    $insertEquipementOption = substr($insertEquipementOption, 0, -1);
                    $connPostgre->exec($insertEquipementOption);
                }
                if (substr($insertEquipementHauteur, -1) == ',') {
                    $insertEquipementHauteur = substr($insertEquipementHauteur, 0, -1);
                    $connPostgre->exec($insertEquipementHauteur);
                }
                if (substr($insertOldBlocNoteClient, -1) == ',') {
                    $insertOldBlocNoteClient = substr($insertOldBlocNoteClient, 0, -1);
                    $connPostgre->exec($insertOldBlocNoteClient);
                }
                if (substr($insertClientSite, -1) == ',') {
                    $insertClientSite = substr($insertClientSite, 0, -1);
                    $connPostgre->exec($insertClientSite);
                }
                if (substr($insertTmp, -1) == ',') {
                    $insertTmp = substr($insertTmp, 0, -1);
                    $connPostgre->exec($insertTmp);
                }

                $connPostgre->commit();
                if(!$connPostgre->inTransaction()) {
                    $connPostgre->beginTransaction();
                }

                $count=0;

                $insertClient = "INSERT INTO sav.client(id, idetat_civil, nom, idtype_client, idcategorie_pro, nom_entreprise, numero_client) VALUES ";
                $insertAdresse = "INSERT INTO sav.adresse(id, idtype_adresse, adresse, adresse_complement, code_postal, ville, latitude, longitude) VALUES ";
                $insertAdresseComplement = "INSERT INTO sav.adresse_complement(idadresse, idtype_complement_adresse, valeur, commentaire_valeur) VALUES ";
                $insertClientAdresse = "INSERT INTO sav.client_adresse(idclient, idadresse, actif) VALUES ";
                $insertClientEmail = "INSERT INTO sav.client_email(idclient, email, actif) VALUES ";
                $insertClientTel = "INSERT INTO sav.client_tel(idclient, num_tel, idtype_tel, fr) VALUES ";
                $insertClientSite = "INSERT INTO sav.client_site(idclient, idsite) VALUES ";
                $insertEquipement = "INSERT INTO sav.equipement(id, idclient_souscripteur, num_serie_principal, idequipement_type_puissance, idenergie, idequipement_tranche, logement_plus_2_ans, idraccordement, date_premier_allumage, actif, idadresse, salarie_frisquet, idequipement_famille_contrat, lieu_si_autre, idequipement_local, idinstallateur, idtaux_tva, idclient_utilisateur, idsite, date_debut_garantie, date_fin_garantie, contact_fin_garantie, idequipement_famille, numero_client_oracle, date_eligible_privilege, date_derniere_v1) VALUES ";
                $insertEquipementOption = "INSERT INTO sav.equipement_option(idequipement, idoption, sous_contrat, date_souscription, prix_ht, date_debut) VALUES ";
                $insertEquipementHauteur = "INSERT INTO sav.equipement_hauteur(id, idequipement, date, hauteur, mesure_possible, escabeau_ok, entretien_ok) VALUES ";
                $insertOldBlocNoteClient = "INSERT INTO sav.old_bloc_note_client(idequipement, texte) VALUES ";
                $insertTmp = "INSERT INTO sav.tmp_correspondance_equipement(idsite, idequipement, idchaudiere_oracle, idclient) VALUES ";
            }
        }
    }
    oci_free_statement($stid);

    if(substr($insertClient, -1) == ',') {
        $insertClient = substr($insertClient, 0, -1);
        $connPostgre->exec($insertClient);
        // file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_client.log', $insertClient, FILE_APPEND);
        if(substr($insertAdresse, -1) == ',') {
            $insertAdresse = substr($insertAdresse, 0, -1);
            $connPostgre->exec($insertAdresse);
        }
        if(substr($insertAdresseComplement, -1) == ',') {
            $insertAdresseComplement = substr($insertAdresseComplement, 0, -1);
            $connPostgre->exec($insertAdresseComplement);
        }
        if(substr($insertClientAdresse, -1) == ',') {
            $insertClientAdresse = substr($insertClientAdresse, 0, -1);
            $connPostgre->exec($insertClientAdresse);
        }
        if(substr($insertClientEmail, -1) == ',') {
            $insertClientEmail = substr($insertClientEmail, 0, -1);
            $connPostgre->exec($insertClientEmail);
        }
        if(substr($insertClientTel, -1) == ',') {
            $insertClientTel = substr($insertClientTel, 0, -1);
            $connPostgre->exec($insertClientTel);
        }
        if(substr($insertEquipement, -1) == ',') {
            $insertEquipement = substr($insertEquipement, 0, -1);
            $connPostgre->exec($insertEquipement);
        }
        if(substr($insertEquipementOption, -1) == ',') {
            $insertEquipementOption = substr($insertEquipementOption, 0, -1);
            $connPostgre->exec($insertEquipementOption);
        }
        if(substr($insertEquipementHauteur, -1) == ',') {
            $insertEquipementHauteur = substr($insertEquipementHauteur, 0, -1);
            $connPostgre->exec($insertEquipementHauteur);
        }
        if(substr($insertOldBlocNoteClient, -1) == ',') {
            $insertOldBlocNoteClient = substr($insertOldBlocNoteClient, 0, -1);
            $connPostgre->exec($insertOldBlocNoteClient);
        }
        if(substr($insertClientSite, -1) == ',') {
            $insertClientSite = substr($insertClientSite, 0, -1);
            $connPostgre->exec($insertClientSite);
        }
        if(substr($insertTmp, -1) == ',') {
            $insertTmp = substr($insertTmp, 0, -1);
            $connPostgre->exec($insertTmp);
        }


        $connPostgre->commit();
    }
}


if(!$connPostgre->inTransaction()) {
    $connPostgre->beginTransaction();
}

$idClient++;
$alter = "ALTER SEQUENCE sav.client_id_seq RESTART WITH ".($idClient).";";
$connPostgre->exec($alter);
$idAdresse++;
$alter = "ALTER SEQUENCE sav.adresse_id_seq RESTART WITH ".($idAdresse).";";
$connPostgre->exec($alter);
$idEquipement++;
$alter = "ALTER SEQUENCE sav.equipement_id_seq RESTART WITH ".($idEquipement).";";
$connPostgre->exec($alter);

$connPostgre->exec("DELETE FROM sav.client_tel WHERE TRIM(num_tel) = '' OR num_tel IS NULL;");

$connPostgre->commit();