<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
register_shutdown_function(function () {
    $err = error_get_last();
    if ($err) {
        echo "Erreur fatale : ";
        print_r($err);
    } else {
        echo "Script terminé proprement\n";
    }
});
$settings['postgre'] = array(
    'localhost' => '172.16.3.78',
    'user' => 'frisquet',
    'password' => '(Fr1squet)!',
    'db' => 'frisquet_sav_20250124'
);

try{
    $connPostgre = new PDO('pgsql:host='.$settings['postgre']['localhost'].';dbname='.$settings['postgre']['db'], $settings['postgre']['user'], $settings['postgre']['password']);
    $connPostgre->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
    die($e->getMessage());
}
//prod
$settings['ROSNY'] = array(
    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 100.1.1.100)(PORT = 1521)) (CONNECT_DATA = (SID = ROSNY)))',
    'user' => 'sav',
    'password' => 'jcd9160'
);
//HIER
//$settings['ROSNY'] = array(
//    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 100.1.1.98)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = HIER.ROSNY.frisquet.fr)))',
//    'user' => 'sav',
//    'password' => 'jcd9160'
//);
$connOracle['ROSNY'] = oci_connect($settings['ROSNY']['user'], $settings['ROSNY']['password'], $settings['ROSNY']['connexion'], 'AL32UTF8');

if (!$connOracle['ROSNY']) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}


$settings['BRON'] = array(
    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SID = LYON)))',
    'user' => 'sav',
    'password' => 'jcd9160'
);
$connOracle['BRON'] = oci_connect($settings['BRON']['user'], $settings['BRON']['password'], $settings['BRON']['connexion'], 'AL32UTF8');

if (!$connOracle['BRON']) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}


$settings['CHEZE'] = array(
    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.100)(PORT = 1521)) (CONNECT_DATA = (SID = MASSY)))',
    'user' => 'sav',
    'password' => 'jcd9160'
);
$connOracle['CHEZE'] = oci_connect($settings['CHEZE']['user'], $settings['CHEZE']['password'], $settings['CHEZE']['connexion'], 'AL32UTF8');
if (!$connOracle['CHEZE']) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}


$settings['LAHALLE'] = array(
    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.100)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = LAHALLE)))',
    'user' => 'sav',
    'password' => 'jcd9160'
);
$connOracle['LAHALLE'] = oci_connect($settings['LAHALLE']['user'], $settings['LAHALLE']['password'], $settings['LAHALLE']['connexion'], 'AL32UTF8');
if (!$connOracle['LAHALLE']) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}


$settings['HANNIQUET'] = array(
    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.100)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = monterea.frisquet)))',
    'user' => 'sav',
    'password' => 'jcd9160'
);
$connOracle['HANNIQUET'] = oci_connect($settings['HANNIQUET']['user'], $settings['HANNIQUET']['password'], $settings['HANNIQUET']['connexion'], 'AL32UTF8');
if (!$connOracle['HANNIQUET']) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}


$settings['GOSNET'] = array(
    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.100)(PORT = 1521)) (CONNECT_DATA = (SID = cergy)))',
    'user' => 'sav',
    'password' => 'jcd9160'
);
$connOracle['GOSNET'] = oci_connect($settings['GOSNET']['user'], $settings['GOSNET']['password'], $settings['GOSNET']['connexion'], 'AL32UTF8');
if (!$connOracle['GOSNET']) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}


$settings['VERGNIEUX'] = array(
    'connexion' => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.100)(PORT = 1521)) (CONNECT_DATA = (SID = maurepas)))',
    'user' => 'sav',
    'password' => 'jcd9160'
);
$connOracle['VERGNIEUX'] = oci_connect($settings['VERGNIEUX']['user'], $settings['VERGNIEUX']['password'], $settings['VERGNIEUX']['connexion'], 'AL32UTF8');
if (!$connOracle['VERGNIEUX']) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

function getConnexionOracle($nomSite, $settings){
    $connexion = oci_connect($settings[$nomSite]['user'], $settings[$nomSite]['password'], $settings[$nomSite]['connexion'], 'AL32UTF8');
    if (!$connexion) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    return $connexion;
}

function cleanTel($tel){
    //Supprimer tous les caractères qui ne sont pas des chiffres
    $tel = preg_replace('/[^0-9]+/', '', $tel);
    if(strlen($tel) != 10 && strlen($tel) != 0){//numéro étranger si + ou - 10 chiffres et non vide
        return $tel;
    }
    $tel = substr($tel, 0, 10);
    $motif = '\1.\2.\3.\4.\5';
    $tel = preg_replace('/(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/', $motif, $tel);
    return $tel;
}

function telFr($tel){
    if(strpos($tel, '.')){
        return 'true';
    }
    return 'false';
}

function exec_free($connPostgre, $request, $stid){
    $connPostgre->exec($request);
    oci_free_statement($stid);
}

function oracle_execute_with_reconnect(&$conn, $settings, $site, $sql) {
    $maxTries = 2;
    $try = 0;
    do {
        $try++;
        $stid = @oci_parse($conn, $sql);
        if (!$stid) {
            throw new Exception("Erreur oci_parse : " . print_r(oci_error($conn), true));
        }
        $success = @oci_execute($stid);
        if ($success) {
            return $stid;
        }
        $error = oci_error($stid);
        if ((strpos($error['message'], 'ORA-03135') !== false || strpos($error['message'], 'ORA-03113') !== false) && $try < $maxTries) {
            // Reconnexion
            if (function_exists('oci_close')) @oci_close($conn);
            $conn = oci_connect($settings[$site]['user'], $settings[$site]['password'], $settings[$site]['connexion'], 'AL32UTF8');
            if (!$conn) {
                throw new Exception("Impossible de se reconnecter à Oracle ($site)");
            }
        } else {
            throw new Exception($error['message']);
        }
    } while ($try < $maxTries);
    throw new Exception("Impossible d'exécuter la requête Oracle après reconnexion.");
}

//$settings['mysql'] = array(
//    'localhost' => 'localhost',
//    'port' => '3306',
//    'user' => 'root',
//    'password' => '',
//    'db' => 'sectorisation'
//);
$settings['mysql'] = array(
    'localhost' => '172.16.3.103',
    'port' => '3306',
    'user' => 'padmin',
    'password' => 'akcw3from89100',
    'db' => 'sectorisation'
);

try{
    $connMySql = new PDO('mysql:host='.$settings['mysql']['localhost'].';port='.$settings['mysql']['port'].';dbname='.$settings['mysql']['db'], $settings['mysql']['user'], $settings['mysql']['password']);
}catch(PDOException $e){
    die($e->getMessage());
}
$connPostgre->beginTransaction();
$insert = "INSERT INTO sav.site (num, nom, antenne) VALUES
(1, 'ROSNY','0'),
(2, 'BRON', '0'),
(3, 'CHEZE', '1'),
(4, 'LAHALLE', '1'),
(5, 'HANNIQUET', '1'),
(7, 'GOSNET', '1'),
(8, 'VERGNIEUX', '1');";
$connPostgre->exec($insert);
$alter = "ALTER SEQUENCE sav.site_id_seq RESTART WITH 9;";
$connPostgre->exec($alter);
$connPostgre->commit();

//utiliser pour les tables qui ont la notion de site
$select = "SELECT * FROM sav.site";
//$select = "SELECT * FROM sav.site WHERE num IN (1,2)";
$idAgenceDev = 2;
//$select = "SELECT * FROM sav.site WHERE num = 2";
//$idAgenceDev = 1;
$sites = $connPostgre->query($select)->fetchAll();

$idAdresse = 9;
$tabEquipementFamille = [];
$tabEquipementTranches = [];
$tabEquipementPuissances = [];
$tabEquipementTypes = [];
$tabEquipementTypePuissances=[];
$tabEquipementOptions=[];
$tabSavAppareils=[];
$tabCorrespondanceAgent = [];

var_dump('new_listes ' .  date('h:i:s'));
include('new_listes.php');
var_dump('listes_without_site ' .  date('h:i:s'));
include('listes_without_site.php');
var_dump('listes_with_site ' .  date('h:i:s'));
include('listes_with_site.php');
var_dump('produits ' .  date('h:i:s'));
include('produits.php');
var_dump('utilisateurs ' .  date('h:i:s'));
include('utilisateurs.php');
var_dump('clients ' .  date('h:i:s'));
include('clients.php');
var_dump('contrats ' .  date('h:i:s'));
include('contrats.php');
var_dump('planning ' .  date('h:i:s'));
include('planning.php');
var_dump('privileges ' .  date('h:i:s'));
include('privilege.php');
var_dump('doublon_equipement_actif ' .  date('h:i:s'));
include('doublon_equipement_actif.php');
var_dump('anomalies ' .  date('h:i:s'));
include('anomalies_pdc.php');
var_dump('secto ' .  date('h:i:s'));
include('secto.php');
var_dump('fini ' .  date('h:i:s'));