<?php

$select = "SELECT * FROM sav.tmp_correspondance_intervention_planning";
$result = $connPostgre->query($select)->fetchAll();
$correspondanceInterventionPlanning = [];
foreach ($result as $value){
    $correspondanceInterventionPlanning[$value['idsite']][$value['idpln_planning']] = $value['idintervention'];
    //$correspondanceInterventionPlanning[$value['idpln_planning']] = $value['idintervention'];
}


$select = "SELECT tmp.*, e.idadresse FROM sav.tmp_correspondance_equipement tmp LEFT JOIN sav.equipement e ON e.id = tmp.idequipement ";
$result = $connPostgre->query($select)->fetchAll();
$correspondanceIdEquip = [];
foreach ($result as $value){
    $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idequipement'] = $value['idequipement'];
}

$connPostgre->beginTransaction();
foreach($sites as $cle => $site) {
    if($site['nom'] == 'ROSNY') {
        oci_close($connOracle[$site['nom']]);
        $connOracle[$site['nom']] = getConnexionOracle($site['nom'], $settings);
        var_dump('privilege');
        $sql = "SELECT * FROM CLIENT_PRIVILEGE";
        $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);
        $count = 0;
        foreach($result as $cle => $row){
            $insertPrivilege = "INSERT INTO sav.privilege(idequipement, date_candidat, date_proposition, commentaire, idprivilege_statut, idraison_refus, idintervention) VALUES ";
            $idEquipement = (isset($row['IDCHAUDIERE']) && isset($correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']])) ? "'" . $correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idequipement'] . "'" : "null";
            $idIntervention = (isset($row['IDPLN_PLANNING']) && isset($correspondanceInterventionPlanning[$site['id']][$row['IDPLN_PLANNING']])) ? "'".$correspondanceInterventionPlanning[$site['id']][$row['IDPLN_PLANNING']]."'" : "null";
            //$idIntervention = (isset($row['IDPLN_PLANNING']) && isset($correspondanceInterventionPlanning[$row['IDPLN_PLANNING']])) ? "'" . $correspondanceInterventionPlanning[$row['IDPLN_PLANNING']] . "'" : "null";
            $idRaisonRefus = ($row['RAISON_REFUS'] == 0) ? "null" : "'".$row['RAISON_REFUS']."'";
            if($idEquipement != "null" && $idIntervention != "null") {
                $insertPrivilege .= "(" . $idEquipement . ",'" . $row['DATE_CANDIDAT'] . "','" . $row['DATE_PROPOSITION'] . "','" . str_replace("'", "''", $row['COMMENTAIRE']) . "','" . $row['STATUS'] . "'," . $idRaisonRefus . "," . $idIntervention . ")";
                //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_privilege.log', $insertPrivilege.';', FILE_APPEND);
                $connPostgre->exec($insertPrivilege);
            }
        }
        oci_free_statement($stid);
    }
}
$connPostgre->commit();