<?php
$connPostgre->beginTransaction();
//pièces
foreach($sites as $cle => $site){
    $select = "SELECT * FROM sav.famille_agi";
    $idFamilleAgi = $connPostgre->query($select);
    $insert = "INSERT INTO sav.produit_famille(idfamille_agi, idsite, commandable) VALUES ";
    $count = 0;
    while($row = $idFamilleAgi->fetch()){
        if(($count) > 0){
            $insert .= ",";
        }
        if($row['libelle'] == 'T00' || $row['libelle'] == 'T01'){
            $insert .= "('".$row['id']."','".$site['id']."','0')";
        }else if(($row['libelle'] == 'T32' || $row['libelle'] == 'T34') && ($site['nom'] != 'ROSNY' && $site['nom'] != 'BRON')){
            $insert .= "('".$row['id']."','".$site['id']."','0')";
        }else{
            $insert .= "('".$row['id']."','".$site['id']."','1')";
        }
        $count++;
    }
    $connPostgre->exec($insert);
}


//Prestations
$insert = "INSERT INTO sav.produit(id, code, designation, idproduit_type) VALUES ";
$insert .= "('1','MO','Main d''œuvre (1/4 d''heure)','2'),";
$insert .= "('2','EHC','Entretien pendant détartrage hors contrat','2'),";
$insert .= "('3','DS','Forfait diagnostic solaire','2'),";
$insert .= "('4','DB','Forfait détartrage ballon (2x2h00)','2'),";
$insert .= "('5','DE','Forfait détartrage serpentin (2h00)','2'),";
$insert .= "('6','DEPL','Déplacement','3'),";
$insert .= "('7','SUPP','Supplément contrat','3')";
$connPostgre->exec($insert);

$insert = "INSERT INTO sav.produit_prestation_tarif(idproduit, idcontrat_regroupement, prix_ht, date_debut, date_fin, idcontrat_plage) VALUES ";
$insert .= "('1',null,'18.00','01/01/2022',null,null),";
$insert .= "('2',null,'56.00','01/01/2022',null,null),";
$insert .= "('3',null,'36.00','01/01/2022',null,null),";
$insert .= "('3','1','36.00','01/01/2022',null,1),";
$insert .= "('3','1','36.00','01/01/2022',null,2),";
$insert .= "('3','2','36.00','01/01/2022',null,null),";
$insert .= "('4',null,'275.00','01/01/2022',null,null),";
$insert .= "('4','1','192.50','01/01/2022',null,1),";
$insert .= "('4','1','192.50','01/01/2022',null,2),";
$insert .= "('4','2','275.00','01/01/2022',null,null),";
$insert .= "('5',null,'175.00','01/01/2022',null,null),";
$insert .= "('5','1','122.50','01/01/2022',null,1),";
$insert .= "('5','1','122.50','01/01/2022',null,2),";
$insert .= "('5','2','175.00','01/01/2022',null,null)";
$connPostgre->exec($insert);

$insert = "INSERT INTO sav.secteur_tarif(idsecteur_zone, tarif_ht_deplacement, date_debut, date_fin, idproduit) VALUES ";
$insert .= "('2', '70.75', '01/01/2022', null,'6'),";
$insert .= "('1', '58.00', '01/01/2022', null,'6'),";
$insert .= "('2', '12.75', '01/01/2022', null,'7'),";
$insert .= "('1', '0', '01/01/2022', null,'7')";
$connPostgre->exec($insert);

//Pièces et consommables
$idProduit = 7;
$select = "SELECT id FROM sav.produit_type WHERE libelle = 'Pièce'";
$idProduitType = $connPostgre->query($select)->fetch()['id'];
$sql = "SELECT * FROM PROD where code_famille_agi is not null and code_article not like 'SAV%'";
$stid = oracle_execute_with_reconnect($connOracle['ROSNY'], $settings, 'ROSNY', $sql);
$nbResult = oci_fetch_all($stid, $result,  0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW);
$insertProduit = "INSERT INTO sav.produit(id, code, designation, gtin, idfamille_agi, gu_etendue, idproduit_type, prix_ht, date_creation) VALUES ";
$insertProduitStockSite = "INSERT INTO sav.produit_stock_site(idsite, idproduit, visible_tablette, stock_mini, stock_reel, stock_theorique, gere_en_stock, stock_reappro) VALUES ";
$count = 0;


$select = "SELECT * FROM sav.famille_agi";
$famillesAgi = $connPostgre->query($select)->fetchAll();
$tabAgi = [];
foreach ($famillesAgi as $familleAgi) {
    $tabAgi[$familleAgi['libelle']] = $familleAgi['id'];
}
foreach($result as $cle => $row){
    $idProduit++;
    $idFamilleAgi = $tabAgi[$row['CODE_FAMILLE_AGI']];
    $row['PRIX_HT'] = ($row['PRIX_HT'] == '') ? 0 : $row['PRIX_HT'];
    $insertProduit .= "('".$idProduit."','".$row['CODE_ARTICLE']."','".str_replace("'","''", $row['DESIGNATION'])."','".$row['GTIN_EAN_13']."','".$idFamilleAgi."','".$row['GU5ANS']."','".$idProduitType."','".str_replace(',','.', $row['PRIX_HT'])."','01/01/2022')";
    $count++;
    if(($count) != $nbResult){
        $insertProduit .= ",";
    }

    foreach($sites as $cle => $site){
        $sql = "SELECT * FROM PROD WHERE CODE_ARTICLE='".$row['CODE_ARTICLE']."'";
        $stid2 = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);
        $rowSite = oci_fetch_array($stid2);
        $insertProduitStockSite .= "('".$site['id']."','".$idProduit."','".((!$rowSite || $rowSite['TARIF'] == 'N') ? 0 : 1)."','".((!$rowSite || $rowSite['STOCK_MINI'] == '') ? 0 : $rowSite['STOCK_MINI'])."','".((!$rowSite || $rowSite['STOCK_REEL'] == '') ? 0 : $rowSite['STOCK_REEL'])."','".((!$rowSite || $rowSite['STOCK_INITIAL'] == '') ? 0 : $rowSite['STOCK_INITIAL'])."','".((!$rowSite || $rowSite['INCLURE_COMMANDE'] == '') ? 0 : $rowSite['INCLURE_COMMANDE'])."','".((!$rowSite || $rowSite['STOCK_REAPPRO'] == '') ? 0 : $rowSite['STOCK_REAPPRO'])."'),";
        oci_free_statement($stid2);
    }
}
$insertProduitStockSite = substr($insertProduitStockSite, 0, -1);
exec_free($connPostgre, $insertProduit, $stid);
$connPostgre->exec($insertProduitStockSite);

//Consommables par site
foreach($sites as $cle => $site){
    var_dump($site['nom'] . ' ' . date('h:i:s'));
    $sql = "SELECT * FROM PROD, MAG_PETITE_PIECE where PROD.CODE_ARTICLE = MAG_PETITE_PIECE.CODE_ARTICLE and MAG_PETITE_PIECE.GESTION_PAR_SAV = '1'";
    $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;
    }
    $insertProduit = "INSERT INTO sav.produit(id, code, designation, gtin, idfamille_agi, gu_etendue, idproduit_type, idsite) VALUES ";
    $insertProduitStockSite = "INSERT INTO sav.produit_stock_site(idsite, idproduit, visible_tablette, stock_mini, stock_reel, stock_theorique, gere_en_stock, stock_reappro) VALUES ";
    $count = 0;
    foreach($result as $cle => $row){
        $idProduit++;
        $insertProduit .= "('".$idProduit."','".$row['CODE_ARTICLE']."','".str_replace("'","''", $row['DESIGNATION'])."',null,null,null,'".$idProduitType."','".$site['id']."')";
        $insertProduitStockSite .= "('".$site['id']."','".$idProduit."','".($row['TARIF'] == 'N' ? 0 : 1)."','".($row['STOCK_MINI'] == '' ? 0 : $row['STOCK_MINI'])."','".($row['STOCK_REEL'] == '' ? 0 : $row['STOCK_REEL'])."','".($row['STOCK_INITIAL'] == '' ? 0 : $row['STOCK_INITIAL'])."','".($row['INCLURE_COMMANDE'] == '' ? 0 : $row['INCLURE_COMMANDE'])."','".($row['STOCK_REAPPRO'] == '' ? 0 : $row['STOCK_REAPPRO'])."')";
        $count++;
        if(($count) != $nbResult){
            $insertProduit .= ",";
            $insertProduitStockSite .= ",";
        }
    }
    exec_free($connPostgre, $insertProduit, $stid);
    $connPostgre->exec($insertProduitStockSite);
}

$idProduit++;
$alter = "ALTER SEQUENCE sav.produit_id_seq RESTART WITH ".($idProduit).";";
$connPostgre->exec($alter);

$connPostgre->commit();