<?php
global $Route;

function cleanData(&$str)
{
  // Fonction de nettoyage des données pour l'export Excel
  if ($str == 't') $str = 'TRUE';
  if ($str == 'f') $str = 'FALSE';
  if (preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
    $str = "'$str";
  }
  if (strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  $str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
}

function filterData(&$str)
{
  $str = preg_replace("/\t/", "\\t", $str);
  $str = preg_replace("/\r?\n/", "\\n", $str);
  if ($str == 't') $str = 'TRUE';
  if ($str == 'f') $str = 'FALSE';
  if (preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
    $str = "'$str";
  }
  if (strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  $str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
  // $str = iconv('UTF-8', 'UTF-8', $str);
}


switch ($Route->_action) {
  case "export_tpg":
    $sql = 'SELECT p.code, p.libelle, p.groupe, IF(p.prix_vente=0, "", FORMAT(p.prix_vente, 2, "fr_FR")) AS vente FROM produits p WHERE p.fk_marche=999 AND p.active=1 ORDER BY p.code;';
    $prods = getinfos($sql, "gen");

    $fileName = "TPG_" . date('Y_m_d_hi') . ".xls";

    $excelData = "TPG AU " . date('d/m/Y') . "\n";

    $cols = array("code", "Libelle", "Groupe", "Prix Vente");
    $excelData .= implode("\t", array_values($cols)) . "\n";

    foreach ($prods as $prod) {
      array_walk($prod, 'filterData');
      $excelData .= implode("\t", $prod) . "\n";
    }

    // une ligne vierge de séparation
    $excelData .= "\n";
    header('Content-Type: application/vnd.ms-excel; charset=utf-16le');
    header("Content-type: application/x-msexcel; charset=utf-16le");
    header('Content-Disposition: attachment; filename="' . $fileName . '"');
    header('Cache-Control: max-age=0');
    echo $excelData;
    exit();
    break;

  case "export_sap_devis":
    $cid = nettoie_input($Route->_param1);

    $sql = 'SELECT d.* FROM devis d WHERE d.rowid=' . $cid . ';';
    eLog("Export Excel SAP Devis : " . $sql);
    $dev = getinfos($sql, "gen");
    $devis = $dev[0];

    $fileName = "devis_" . $cid . "_" . date('Y_m_d_hi') . ".xls";

    // On affiche les données client
    $fields = array("Code", "Etablissement", "Adresse 1", "Adresse 2", "Adresse 3", "Code Postal", "Ville");
    $excelData = implode("\t", array_values($fields)) . "\n";

    $sql = 'SELECT c.code, c.libelle, c.adresse1, c.adresse2, c.adresse3, c.cp, c.ville FROM clients c WHERE c.rowid=' . $devis["fk_client"] . ';';
    $cli = getinfos($sql, "gen");
    if (count($cli) == 0) {
      // c'est un nouveau client, on affiche les données client enregistrées dans le devis
      $sql = 'SELECT "0" AS code, d.lib_new_client, d.adresse1_new_client, d.adresse2_new_client, d.adresse3_new_client, d.cp_new_client, d.ville_new_client FROM devis d WHERE d.rowid=' . $cid . ';';
      $cli = getinfos($sql, "gen");
      $client = $cli[0];

      array_walk($client, 'filterData');
      $excelData .= implode("\t", array_values($client)) . "\n";

      // une ligne vierge de séparation
      $excelData .= "\n";

      // Les données du contact à prendre aussi dans le devis
      $sql = 'SELECT d.contact_new_nom, d.contact_new_prenom, d.contact_new_fonction, d.new_telephone, d.new_mobile, d.new_email FROM devis d WHERE d.rowid=' . $cid . ';';
      $cont = getinfos($sql, "gen");
      $contact = $cont[0];
      $fields = array("Contact Nom", "Prenom", "Fonction", "Fixe", "Mobile", "Email");
      $excelData .= implode("\t", array_values($fields)) . "\n";

      array_walk($contact, 'filterData');
      $excelData .= implode("\t", array_values($contact)) . "\n";

    } else {
      $client = $cli[0];

      array_walk($client, 'filterData');
      $excelData .= implode("\t", array_values($client)) . "\n";

      // une ligne vierge de séparation
      $excelData .= "\n";

      // Les données du contact
      $sql = 'SELECT c.contact_nom, c.contact_prenom, c.contact_fonction, c.telephone, c.mobile, c.email FROM clients c WHERE c.rowid=' . $devis["fk_client"] . ';';
      $cont = getinfos($sql, "gen");
      $contact = $cont[0];
      $fields = array("Contact Nom", "Prenom", "Fonction", "Fixe", "Mobile", "Email");
      $excelData .= implode("\t", array_values($fields)) . "\n";

      array_walk($contact, 'filterData');
      $excelData .= implode("\t", array_values($contact)) . "\n";
    }
    // une ligne vierge de séparation
    $excelData .= "\n";

    // On affiche les données devis
    $sql = 'SELECT d.rowid, d.num_opportunite, IF(d.date_demande IS NULL OR d.date_demande="0000-00-00", "", DATE_FORMAT(d.date_demande, "%d/%m/%Y")) AS datedem, ';
    $sql .= 'IF(d.date_remise IS NULL OR d.date_remise="0000-00-00", "", DATE_FORMAT(d.date_remise, "%d/%m/%Y")) AS daterem, m.libelle AS lib_marche, m.numero AS num_marche, m.nom AS nom_marche, ';
    $sql .= 'IF(d.chk_devis_photos=1, "Oui", "Non") AS photos, d.commentaire, IF(d.chk_speciaux=1, "Oui", "Non") AS speciaux ';
    $sql .= 'FROM devis d LEFT JOIN marches m ON d.fk_marche=m.rowid WHERE d.rowid=' . $cid . ';';
    $dev = getinfos($sql, "gen");
    $devis = $dev[0];
    $chkSpeciaux = $devis["speciaux"];
    $fields = array("Devis", "Opportunite", "Date Demande", "Date remise client", "Marche", "Num Marche", "Nom Marche", "Avec photos", "Commentaire RR", "Speciaux");
    $excelData .= implode("\t", array_values($fields)) . "\n";

    array_walk($devis, 'filterData');
    $excelData .= implode("\t", array_values($devis)) . "\n";

    // une ligne vierge de séparation
    $excelData .= "\n";

    // on affiche les totaux du devis
    $sql = 'SELECT d.montant_total_ht, d.montant_total_ht_remise, d.marge_totale FROM devis d WHERE d.rowid=' . $cid . ';';
    $dev = getinfos($sql, "gen");
    $totaux = $dev[0];
    $fields = array("Total HT", "Total HT Remise", "Marge Totale");
    $excelData .= implode("\t", array_values($fields)) . "\n";

    array_walk($totaux, 'filterData');
    $excelData .= implode("\t", array_values($totaux)) . "\n";

    // une ligne vierge de séparation
    $excelData .= "\n";

    // on affiche les produits
    $sql = 'SELECT p.code, p.libelle, IF(p.prix_vente=0, "", FORMAT(p.prix_vente, 2, "fr_FR")) AS vente, ';
    $sql .= 'IF(dp.qte=0, "", dp.qte) AS qute, IF(dp.remise=0, "", FORMAT(dp.remise, 2, "fr_FR")) as remise, IF(dp.pu_vente_remise=0, "", FORMAT(dp.pu_vente_remise, 2, "fr_FR")) as puventeremise, ';
    $sql .= 'IF(dp.totalht=0, "", FORMAT(dp.totalht, 2, "fr_FR")) AS totht, IF(dp.marge=0, "", FORMAT(dp.marge, 2, "fr_FR")) AS marge, dp.commentaire ';
    $sql .= 'FROM devis_produits dp ';
    $sql .= 'LEFT JOIN produits p ON dp.fk_produit=p.rowid ';
    $sql .= 'LEFT JOIN produits_familles pf ON p.groupe=pf.groupe ';
    $sql .= 'LEFT JOIN x_familles xf ON pf.fk_famille=xf.rowid ';
    $sql .= 'WHERE dp.fk_devis=' . $cid . ' ORDER BY dp.ordre, xf.ordre, p.libelle;';
    $data = getinfos($sql, "gen");

    $fields = array("Code", "Designation", "Prix Vente", "Quantite", "Remise", "PU vente avec remise", "Total HT", "Marge", "Commentaire");
    $excelData .= implode("\t", array_values($fields)) . "\n";

    foreach ($data as $row) {
      array_walk($row, 'filterData');
      $excelData .= implode("\t", $row) . "\n";
    }

    if ($chkSpeciaux == "Oui") {
      // une ligne vierge de séparation
      $excelData .= "\n";
      $excelData .= "----" . "\n";
      $excelData .= "PRODUITS SPECIAUX" . "\n";
      $excelData .= "----" . "\n";
      $sql = 'SELECT IF(ds.chk_livr_multi=1, "Oui", "Non") AS livr_multi, ds.nb_livr, DATE_FORMAT(ds.date_livr_1, "%d/%m/%Y") AS datelivr ';
      $sql .= 'FROM devis_speciaux ds WHERE ds.fk_devis=' . $cid . ';';
      $spec = getinfos($sql, "gen");
      $speciaux = $spec[0];

      $fields = array("Livraisons multiples", "Nbre livraisons", "Date 1ere livraison");
      $excelData .= implode("\t", array_values($fields)) . "\n";

      array_walk($speciaux, 'filterData');
      $excelData .= implode("\t", array_values($speciaux)) . "\n";

      $excelData .= "\n";

      $fields = array("#", "Code", "Designation", "Quantite", "Surcout", "Echantillon", "Date echantillon", "Concurrent", "Description");
      $excelData .= implode("\t", array_values($fields)) . "\n";

      for ($i = 1; $i <= 5; $i++) {
        $sql = 'SELECT ds.fk_produit_' . $i . ', ds.code_produit_' . $i . ', ds.lib_produit_' . $i . ', ds.qte_' . $i . ', IF(ds.surcout_' . $i . '=0, "", FORMAT(ds.surcout_' . $i . ', 2, "fr_FR")), IF(ds.chk_echantillon_' . $i . '=1, "Oui", "Non") AS echantillon, ';
        $sql .= 'DATE_FORMAT(ds.date_echantillon_' . $i . ', "%d/%m/%Y") AS date_ech, ds.lib_concurrent_' . $i . ', ds.description_' . $i . ' ';
        $sql .= 'FROM devis_speciaux ds WHERE ds.fk_devis=' . $cid . ';';
        eLog($sql, "sql");
        $spec = getinfos($sql, "gen");
        $speciaux = $spec[0];

        if ($speciaux["fk_produit_" . $i] > 0) {
          array_walk($speciaux, 'filterData');
          $excelData .= implode("\t", array_values($speciaux)) . "\n";
        }
      }
    }

    // une ligne vierge de séparation
    $excelData .= "\n";
    $excelData .= "----" . "\n";
    $excelData .= "FIN DU DEVIS" . "\n";
    $excelData .= "----" . "\n";
    $excelData .= "\n";
    header('Content-Type: application/vnd.ms-excel; charset=utf-16le');
    header("Content-type: application/x-msexcel; charset=utf-16le");
    header('Content-Disposition: attachment; filename="' . $fileName . '"');
    header('Cache-Control: max-age=0');
    echo $excelData;
    exit();

}