<?php

$aModel = array();

$sql = 'SELECT m.* FROM medias m WHERE m.support="devis_pdf_sap" ORDER BY m.support_rowid;';
$aModel["medias"] = getinfos($sql, "gen");

$sql = 'SELECT d.*, c.libelle, c.adresse1, c.adresse2, c.adresse3, c.code, c.cp, c.ville, cc.email, u.libelle as nom, u.prenom, s.libelle as lib_statut, m.libelle as lib_marche ';
$sql .= 'FROM devis d ';
$sql .= 'LEFT JOIN clients c on d.fk_client = c.rowid ';
$sql .= 'LEFT JOIN clients_contacts cc ON d.fk_contact = cc.rowid ';
$sql .= 'LEFT JOIN users u ON d.fk_user = u.rowid ';
$sql .= 'LEFT JOIN marches m ON d.fk_marche = m.rowid ';
$sql .= 'LEFT JOIN x_statuts_devis s ON d.fk_statut_devis = s.rowid ';
$sql .= 'WHERE (d.fk_statut_devis > 3 AND d.fk_statut_devis < 20) ORDER BY d.date_demande;';
$aModel["devisEnCours"] = getinfos($sql, "gen");

//! on compte le nombre de devis par statut
$aModel["nb_devis"] = array();
foreach ($aModel["devisEnCours"] as $devis) {
  if (!isset($aModel["nb_devis"][$devis["fk_statut_devis"]])) {
    $aModel["nb_devis"][$devis["fk_statut_devis"]] = 1;
  } else {
    $aModel["nb_devis"][$devis["fk_statut_devis"]]++;
  }
}

$sql = 'SELECT d.*, c.libelle, c.adresse1, c.adresse2, c.adresse3, c.code, c.cp, c.ville, cc.email, u.libelle as nom, u.prenom, s.libelle as lib_statut, m.libelle as lib_marche ';
$sql .= 'FROM devis d ';
$sql .= 'LEFT JOIN clients c on d.fk_client = c.rowid ';
$sql .= 'LEFT JOIN clients_contacts cc ON d.fk_contact = cc.rowid ';
$sql .= 'LEFT JOIN users u ON d.fk_user = u.rowid ';
$sql .= 'LEFT JOIN marches m ON d.fk_marche = m.rowid ';
$sql .= 'LEFT JOIN x_statuts_devis s ON d.fk_statut_devis = s.rowid ';
$sql .= 'WHERE d.fk_statut_devis = 20 ORDER BY d.date_remise;';
$aModel["devisArchives"] = getinfos($sql, "gen");

//! les statuts de devis sans le 20 - Archivés pour afficher les devis en cours à traiter au niveau de l'ADV
$sql = 'SELECT rowid, libelle FROM x_statuts_devis WHERE active=1 AND rowid>3 AND rowid<20 ORDER BY rowid;';
$aModel["statuts_devis"] = getinfos($sql, "gen");

//! On récupère la liste des dossiers des devis pour afficher les devis archivés par département
$sql = 'SELECT DISTINCT d.dossier FROM devis d WHERE d.fk_statut_devis = 20 ORDER BY dossier;';
$aModel["dossiers"] = getinfos($sql, "gen");