Cleo/models/mexpxls.php

<?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);
    $cidSafe = intval($cid);

    try {
      $db = Database::getInstance();
      $sql = 'SELECT d.* FROM devis d WHERE d.rowid = :devis_id';
      $devis = $db->fetchOne($sql, [':devis_id' => $cidSafe]);

      if (!$devis) {
        throw new Exception("Devis non trouvé");
      }

      eLog("Export Excel SAP Devis : " . $cidSafe);

      $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";

      $fkClientSafe = intval($devis["fk_client"]);

      if ($fkClientSafe == 0) {
        // Nouveau client : données depuis 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 = :devis_id';
        $client = $db->fetchOne($sql, [':devis_id' => $cidSafe]);

        array_walk($client, 'filterData');
        $excelData .= implode("\t", array_values($client)) . "\n";
        $excelData .= "\n";

        // Contact depuis 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 = :devis_id';
        $contact = $db->fetchOne($sql, [':devis_id' => $cidSafe]);

        $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 existant : données depuis la table clients
        $sql = 'SELECT c.code, c.libelle, c.adresse1, c.adresse2, c.adresse3, c.cp, c.ville FROM clients c WHERE c.rowid = :client_id';
        $client = $db->fetchOne($sql, [':client_id' => $fkClientSafe]);

        array_walk($client, 'filterData');
        $excelData .= implode("\t", array_values($client)) . "\n";
        $excelData .= "\n";

        // Contact lié au devis via devis.fk_contact et clients_contacts
        $fkContactSafe = intval($devis["fk_contact"]);
        if ($fkContactSafe > 0) {
          $sql = 'SELECT cc.nom, cc.prenom, cc.fonction, cc.telephone, cc.mobile, cc.email FROM clients_contacts cc WHERE cc.rowid = :contact_id AND cc.active = 1';
          $contact = $db->fetchOne($sql, [':contact_id' => $fkContactSafe]);
        } else {
          // Fallback : contact principal du client
          $sql = 'SELECT cc.nom, cc.prenom, cc.fonction, cc.telephone, cc.mobile, cc.email FROM clients_contacts cc WHERE cc.fk_client = :client_id AND cc.principal = 1 AND cc.active = 1 LIMIT 1';
          $contact = $db->fetchOne($sql, [':client_id' => $fkClientSafe]);
        }

        if ($contact) {
          $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 {
          // Aucun contact trouvé
          $excelData .= "Contact Nom\tPrenom\tFonction\tFixe\tMobile\tEmail\n";
          $excelData .= "\t\t\t\t\t\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 = :devis_id';
      $devisData = $db->fetchOne($sql, [':devis_id' => $cidSafe]);
      $chkSpeciaux = $devisData["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($devisData, 'filterData');
      $excelData .= implode("\t", array_values($devisData)) . "\n";
      $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 = :devis_id';
      $totaux = $db->fetchOne($sql, [':devis_id' => $cidSafe]);

      $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";
      $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 = :devis_id ORDER BY dp.ordre, xf.ordre, p.libelle';
      $data = $db->fetchAll($sql, [':devis_id' => $cidSafe]);

      $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") {
        $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 = :devis_id';
        $speciaux = $db->fetchOne($sql, [':devis_id' => $cidSafe]);

        $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 = :devis_id';

          $produitSpecial = $db->fetchOne($sql, [':devis_id' => $cidSafe]);

          if ($produitSpecial && $produitSpecial["fk_produit_" . $i] > 0) {
            array_walk($produitSpecial, 'filterData');
            $excelData .= implode("\t", array_values($produitSpecial)) . "\n";
          }
        }
      }

      $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;
    } catch (Exception $e) {
      error_log("Erreur export Excel : " . $e->getMessage());
      http_response_code(500);
      echo "Erreur lors de l'export du devis";
    }
    exit();
}