<?php

namespace Modules\Commande\Repository;

use Entity\Model\Hastutil;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\Pagination\PaginatorGestion;
use Modules\GlobalApplication\Repository\LicenceOxalys;
use Modules\GlobalApplication\Repository\Societe;
use Helpers\QueryBuilder\AbsRepository;

class CreationCommande extends AbsRepository
{
        
    /** REQUETE ELEMENT D UNE AO  **/

    /**
     * Retourne le titer de l'ao (memos)
     * @param integer $numAO
     * @return mixed
     */
    public function getTitreAO($numAO)
    {
        $req = "select CONTMEMO from AO_ENTET with (nolock) 
                LEFT JOIN MEMOS with (nolock) on NODEMEMO = NM_TITRE
                where NO_AO=:id";
        
        $arrayReq = [':id' => $numAO];
                
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * retourne la liste des articles d'une AO
     * @param integer $numAO
     * @param string $codeSte
     * @return array<string>[]
     */
    public function getLigneAO($numAO, string $codeSte)
    {
        $req = "select AO_LIGNE.NO_PO, AO_LIGNE.CODE_ART, DES , MEMOS.CONTMEMO DES_COMPL
                from AO_LIGNE with (nolock)
                left join DA_LIGNE with (nolock) on DA_LIGNE.NO_DA = AO_LIGNE.NO_DA and DA_LIGNE.NO_PO = AO_LIGNE.NO_PO_DA 
                left join  MEMOS with (nolock) on MEMOS.NODEMEMO = AO_LIGNE.NM_DCOMP
                where NO_AO=:id and isnull(DA_LIGNE.TRAITEE,0x46)=0x46 and AO_LIGNE.CODE_STE = :codeSte order by AO_LIGNE.NO_PO";
        
        $arrayReq = [':id' => $numAO, ':codeSte' => $codeSte];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * retourne la liste des clauses d'une AO
     * @param integer $numAO
     * @return array<string>[]
     */
    public function getClauseAO($numAO)
    {
        $req = "select NO_ORDRE, CONTMEMO from AO_CLAUS with (nolock) 
                LEFT JOIN MEMOS with (nolock) on NODEMEMO = NM_PARAG
                where NO_AO=:id";
        
        $arrayReq = [':id' => $numAO];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * retourne la liste des PJ d'une AO
     * @deprecated use QueryBuilder instead
     * @param integer $numAO
     * @return array<string>[]
     */
    public function getJPAO($numAO)
    {
        $req = "SELECT NO_ORDRE, TYPE_PJ, TITRE from AO_PIJO with (nolock)
                where NO_AO=:id";
        
        return $this->fetchAssocAll($req, [':id' => $numAO]);
    }
    
    /**
     * retourne l'entête d'une AO
     * @deprecated use QueryBuilder intead
     * @param integer $ao
     * @return ?array<string>
     */
    public function getEnteteAo($ao)
    {
        $req = "select TYPE_SG, CODE_STE from AO_ENTET with (nolock) where NO_AO=:ao";
        
        return $this->fetchAssocOne($req, [':ao' => $ao]);
    }
    
    /**
     * retourne le detail de l'entête d'une AO
     * @param string $element
     * @param integer $ao
     * @return ?array<string>
     */
    public function getElementAO($element, $ao)
    {
        $element = Sql::qc('dae.'.$element);
        $req = "select CASE COUNT(DISTINCT ".$element.") 
                       WHEN 1 THEN (select ".$element." from AO_LIGNE aol with(nolock) left outer join DA_ENTET dae with(nolock) on dae.NO_DA=aol.NO_DA where aol.NO_AO=:ao and isnull(".$element.",'')<>'' group by ".$element.") 
                       ELSE ''
                END VALUE
                from AO_LIGNE aol with(nolock) left outer join DA_ENTET dae with(nolock) on dae.NO_DA=aol.NO_DA where aol.NO_AO=:ao1 and isnull(".$element.",'')<>''";

        return $this->fetchAssocOne($req, [':ao' => $ao, ':ao1' => $ao]);
    }
    
    /* ******************************************************* */
    
    /** REQUETE ELEMENT D UNE DA  **/
    /**
     * Retourne le titer de la DA (memos)
     * @param integer $numDA
     * @return mixed
     */
    public function getTitreDA($numDA)
    {
        $req = "select CONTMEMO from DA_ENTET with (nolock)
                LEFT JOIN MEMOS with (nolock) on NODEMEMO = NM_TITRE
                where NO_DA=:id";
        
        $arrayReq = [':id' => $numDA];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * Retourne la liste des ligne de a DA (memos)
     * @param integer $numDA
     * @return mixed
     */
    public function getLigneDA($numDA)
    {
        $req = "select NO_PO, CODE_ART, DES_SOMM DES, MEMOS.CONTMEMO DES_COMPL from DA_LIGNE with (nolock)
                left join  MEMOS with (nolock) on MEMOS.NODEMEMO = DA_LIGNE.NM_DCOMP
                where NO_DA=:id and TRAITEE = 0x46";
        
        $arrayReq = [':id' => $numDA];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * Retourne la liste des clauses de a DA (memos)
     * @param integer $numDA
     * @return mixed
     */
    public function getClauseDA($numDA)
    {
        $req = "select NO_ORDRE, CONTMEMO from DA_CLAUS with (nolock)
                LEFT JOIN MEMOS with (nolock) on NODEMEMO = NM_PARAG
                where NO_DA=:id";
        
        $arrayReq = [':id' => $numDA];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * Summary of getNumberTP
     * @param mixed $numDA
     */
    public function getNumberTP($numDA): mixed
    {
        $req = "select count(*) as TOTAL from TP_ENT with (nolock)
                where NUM_CDE=:id AND TYPEOBJ='DA_ENTET'";

        $arrayReq = [':id' => $numDA];
        
        return $this->fetchAssocOne($req, $arrayReq)['TOTAL']??0;
    }

     /**
     * Retourne la liste des PJ de a DA (memos)
     * @param integer $numDA
     * @return mixed
     */
    public function getJPDA($numDA)
    {
        $req = "select NO_ORDRE, TYPE_PJ, TITRE from DA_PIJO with (nolock)
                where NO_DA=:id";

        $arrayReq = [':id' => $numDA];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
        
    /* ************************* */
    
    /**
     * Retourne les information du fournisseur selectionné
     * @param integer $fournis
     * @return ?array<string>
     */
    public function getInfoFournisseur($fournis)
    {
        $req = "select DEVISE, COD_LNG, LIBELLE, RECEP_AUTO, PER_REC_AUTO 
            from FOURNISS with (nolock)
            left join DEVISES with (nolock) on DEVISES.CODE_DEVI = DEVISE                    
            where CODE=:id";
        return $this->fetchAssocOne($req, [':id' => $fournis]);
    }
    
    /**
     * retourne la liste des fournisseur disponible pour un AO
     * @param array<mixed> $post
     * @return array<string>[]
     */
    public function fournisseurParAo($post)
    {
        $req = "select * from (
                select CODE, RS, AO_FOUR.CPOSTAL CP, AO_FOUR.VILLE VILLE1, NO_AO from AO_FOUR with (nolock)
                LEFT JOIN FOURNISS with (nolock) on FOURNISS.CODE = AO_FOUR.CODE_FOU
                where FOU_RETEN = 0x54) AO_FOUR";
        
        $arrayReq = [];
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * retourne la liste des type d'achat dispo pour un frs
     * @param string $fournis
     * @return ?array<mixed>
     */
    public function getTypeAchatFourniss($fournis)
    {
        $req = "SELECT TYPEACHAT.TYP_ACH, ISNULL(t.TRA_TAC, TYPEACHAT.LIB_TAC) AS LIB_TAC
        FROM TAC_TYFO WITH (NOLOCK)
        LEFT JOIN TYPEACHAT WITH (NOLOCK) on TYPEACHAT.TYP_ACH = TAC_TYFO.TACFOTAC
        LEFT JOIN TRAD_TAC t WITH(NOLOCK) ON t.TYP_ACH = TYPEACHAT.TYP_ACH AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
        WHERE TACFOFOU = :id AND TACFODEF = 0x54";
        
        $arrayReq = [':id' => $fournis];

        return $this->fetchAssocOne($req, []);
    }
    
    /** REGLES DES GESTION CREATION CDE **/

    /**
     * retourn P si le modèle de cde est désactiver
     * @deprecated
     * @return ?array<string>
     */
    public function modeleDesactiver()
    {
        $req = "select isnull((select LIB01PARV from PARAVRAC with(nolock) where TYPEPARV='PARAMGEN' and CODEPARV='UTILISATION_MODELE_CDE'),'P') as MODELE_DESACTIVE";

        return $this->fetchAssocOne($req, []);
    }
    
    /**
     * retourne la modale par defaut de l'utilsateur
     * @deprecated
     * @return ?array<string>
     */
    // phpcs:ignore
    public function ModeleUser()
    {
        return (new Hastutil($this->pdo))->find($_SESSION['Session_Login']);
    }
    
    /**
     * retourne un modèle
     * @param string $codeModele
     * @return array<string>
     */
    public function getModele($codeModele)
    {
        $req = "select * from M_CA with(nolock) where COD_MOD=:codeModele";
        
        $arrayReq = [':codeModele' => $codeModele];

        return $this->fetchAssocOne($req, $arrayReq);
    }
    
    /**
     * retourne les elements d'un DA pour l'initialisation des champs
     * @param integer $DA
     * @return array<string>
     */
    public function getDA($DA)
    {
        $req = "SELECT NO_DA, NO_DEM, DAENT_CENTRE_EMETTEUR, CTR_LIV, MOD_TRS,SECTIMP,CODGES,CODECOMP,SSCPTEANA,FOU,CODE_STE, ID_CG,DEVISE, TYP_DOC
                from DA_ENTET with(nolock) 
                where NO_DA=:codeDA";
        
        $arrayReq = [':codeDA' => $DA];
        return $this->fetchAssocOne($req, $arrayReq);
    }
    
    /**
     * permet de voir si un Section d'imputation a le code gestion activé
     * @param string $codeSECTIMPU
     * @param string $codeSte
     * @return array<string>
     */
    public function codeGestionActif(?string $codeSECTIMPU = null, ?string  $codeSte = null)
    {
        $req = "select SICODG from SECTIMPU with(nolock)
                where CODE_SI = :codeSi";
        
        $arrayReq = [
            
            ':codeSi' => $codeSECTIMPU
        ];

        if ($codeSte) {
            $req .= "  and CODE_STE=:codeSte ";
            $arrayReq[':codeSte'] = $codeSte;
        }
        return $this->fetchAssocOne($req, $arrayReq);
    }
    
    /**
     * permet de voir si le mode de transport est actif pour un CENTRE
     * @param integer $CentreLivr
     * @return array<string>
     */
    public function modeTransportActif($CentreLivr)
    {
        return $this->fetchAssocOne(
            "SELECT TRS_INTER, MOD_TRS from CENTRE with(nolock)
            where CODE_CTR = :ctrLiv",
            [':ctrLiv' => $CentreLivr]
        );
    }
    
    /**
     * retourne la liste des mode de transport avec traduction
     * @return array<string>[]
     */
    public function getAllModeTransportTrad()
    {
        $req = "SELECT mdt.MOD_TRS, isnull(tr.DES_ART,mdt.MOD_TRS) MOD_TRS_TRAD 
        FROM MODTRANS mdt WITH (NOLOCK)
            LEFT OUTER JOIN TRAD_MDT tr WITH (NOLOCK) ON tr.COD_ART=mdt.MOD_TRS AND tr.COD_LNG='".$_SESSION['Session_Langue']."'";
        
        return $this->fetchAssocAll($req, []);
    }
    

    /**
     * retourne la liste des DA pour la création d'une commande
     * /!\ requete présente aussi dans les validation CDE /!\
     * @param array<mixed> $post
     * @return array<string>[]
     */
    public function listeDA($post)
    {
        $user = $_SESSION['Session_Login'];
        $arrayReq = [];

        $req = "SELECT DISTINCT NO_DA, NO_DEM, FOU, CTR_LIV, DTE_VAL, TOTAL_DA, TOTAL_DA_TTC 
            from DA_ENTET with (nolock), TYPDOC with (nolock)
            where TYPDOC.TYPE=DA_ENTET.TYP_DOC and TRAITEE=0x46 and TRIM(FM_CODE) != '1-'";

        $DaSigneObli = Sql::paravrac($this->pdo, 'PARAMSIG', 'NOTCREATCDEDANONSIG');
        if ($DaSigneObli == 'O') {
            $req .= " and DA_ENTET.SIGNEE = 0x54 ";
        }

        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $req .=" and TYPDOC.NIV_CONF<= (select NIV_CONF from HASTUTIL with (nolock) where CODE='$user') and TYPDOC.NIV_CONF<=(select NIV_CONF from HASTUTIL with (nolock) where CODE='$user')";
        }
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq);
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * retourne la liste des AO pour la création d'une commande
     * /!\ requete présente aussi dans les validation CDE /!\
     * @param array<mixed> $post
     * @return array<string>[]
     */
    public function listeAO($post)
    {

        $societes = (new Societe($this->pdo))->listRestrictedSocieteForUser();
        
        $whereSoc = '';
        if ($societes !== null) {
            $listeSociete = implode("','", array_column($societes, 'CODE_STE'));
            //$whereSoc .= " and ((aol.CODE_STE in ('$listeSociete') AND aoe.TYPE_SG = 'S') or (aol.CODE_STE in (select GROUPE from SOCIETE with(nolock) where CODE_STE IN  ('$listeSociete')) AND aoe.TYPE_SG = 'G'))";
            $whereSoc .= " and (aol.CODE_STE in ('$listeSociete') AND aoe.TYPE_SG = 'S') ";
        }

        $codeSte = $post['code_ste'];

        if ($codeSte) {
            //$whereSoc .= " and (aol.CODE_STE='$codeSte' or aol.CODE_STE in (select GROUPE from SOCIETE with(nolock) where CODE_STE IN  ('$codeSte')) ) ";

            $whereSoc .= " and (aol.CODE_STE='$codeSte') ";
        }

        $arrayReq = [];
        $req = " SELECT NO_AO, DTE_AO, ACH,HASTUTIL.NOM, EMETTEUR, DEL_REP , OBJET_AO
                    from AO_ENTET with(nolock)
                    left join HASTUTIL with(nolock) on HASTUTIL.CODE = AO_ENTET.ACH
                    where NO_AO in (
                        select aoe.NO_AO from AO_LIGNE aol with(nolock)
                        join AO_ENTET aoe with(nolock) on aoe.NO_AO=aol.NO_AO
                        left join DA_LIGNE dal with(nolock) on dal.NO_DA=aol.NO_DA and dal.NO_PO=aol.NO_PO_DA
                        where 1=1 $whereSoc and aoe.VALCHXFOU!='00000000' and isnull(dal.TRAITEE,0x46)=0x46 group by aoe.NO_AO
                    )";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq);

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    
    /**
     * retourne la liste des lignes DA pour la création d'une commande
     * @param array<mixed> $post
     * @return array<string>[]
     */
    public function listeLigneDaPourCde($post)
    {
        $arrayReq = [];
        $DaSigneObli = Sql::paravrac($this->pdo, 'PARAMSIG', 'NOTCREATCDEDANONSIG');
        $clause = '';
        if ($DaSigneObli == 'O') {
            $clause .= " and DA_ENTET.SIGNEE = 0x54";
        }

        if (Roles::droitsUtilisateur($this->pdo, 'HA_DA_TTDM')!='1') {
            $clause .= " and DA_ENTET.NO_DEM =:demandeur";
            $arrayReq[':demandeur'] = $_SESSION["Session_Login"];
        }

        if (Roles::droitsUtilisateur($this->pdo, 'HA_DA_TTCT')!='1') {
            $clause .= " and DA_ENTET.CTR_LIV =:centre";
            $arrayReq[':centre'] = $_SESSION['Session_CodeCtrLivraison'];
        }

        $hastutil = new Hastutil($this->pdo);
        $infoUser = $hastutil->find($_SESSION["Session_Login"]);
        $arrayReq[':nivConf'] = $infoUser['NIV_CONF'];

        $req = "select * from (
                select DA_LIGNE.NO_DA, NO_PO, NO_DEM, FOU, CTR_LIV, DTE_VAL, TOTAL_DA, TOTAL_DA_TTC, DA_ENTET.CODE_STE from DA_LIGNE with (nolock)
                LEFT JOIN DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
                left join TYPDOC with (nolock) on TYPDOC.TYPE = DA_LIGNE.TYP_DOC
                WHERE DA_LIGNE.TRAITEE='F' and TYPDOC.NIV_CONF <= :nivConf  and DA_ENTET.CHIF_EXEC = 'E' $clause) DA
                ";



        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /** creation depuis da/ao */
    /**
     * getListeDA
     *
     * @param  string $clauseWhere
     * @param  array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getListeDA(string $clauseWhere, array $arrayReq, string $codeFou) : ?array
    {
        $req = "select DA_LIGNE.*, acc.DATE_DEB, acc.DATE_FIN, acc.CODE_ACD,ARTICLES.DOUANE, ARTICLES.NONRECEPT ARTICLE_NONRECPT, ARTICLES.LIMITPERSTOCK,
                SSFAMILL.NONRECEPT SSFAMILL_NONRECEPT, SSFAMILL.CODE_FAM, ARTICLES.AV_TAXLOC ARTICLES_AV_TXLOC, SSFAMILL.AV_TAXLOC SSFAMILL_AV_TAXLOC, MEMOS.CONTMEMO DES_COMPL,

                ARTFOURN.POIDS ARTFOURN_POIDS, ARTFOURN.LONGU ARTFOURN_LONGU, ARTFOURN.LARG ARTFOURN_LARG, ARTFOURN.HAUT ARTFOURN_HAUT, 
                ARTFOURN.VOLUME ARTFOURN_VOLUME, ARTFOURN.PAY_ORI ARTFOURN_PAY_ORI, ARTFOURN.PAY_PROV ARTFOURN_PAY_PROV, ARTFOURN.POIDSNET ARTFOURN_POIDSNET, ARTFOURN.REFARTFOU ARTFOURN_REF_FOURN,

                ARTGF.POIDS ARTGF_POIDS, ARTGF.LONGU ARTGF_LONGU, ARTGF.LARG ARTGF_LARG, ARTGF.HAUT ARTGF_HAUT, 
                ARTGF.VOLUME ARTGF_VOLUME, ARTGF.PAY_ORI ARTGF_PAY_ORI, ARTGF.PAY_PROV ARTGF_PAY_PROV, ARTGF.POIDSNET ARTGF_POIDSNET, ARTGF.REFARTFOU ARTGF_REF_FOURN,
                CASE 
                    WHEN ARTFOURN.CODE_ART != '' THEN 'ARTFOURN'
                    WHEN ARTGF.CODE_ART != ''  THEN 'ARTGF'
                    ELSE '' 
                END INFO_ART
                from DA_LIGNE with (nolock)
                left join DA_ENTET de with (nolock) on de.NO_DA = DA_LIGNE.NO_DA
                left join FOURNISS fou with (nolock) on fou.CODE = :codeFou
                left join ACCCADRE acc with (nolock) on acc.CODE_ACD = DA_LIGNE.ACD and acc.CODE_FOUR = fou.CODE
                left join ARTFOURN with (nolock) on ARTFOURN.CODE_ART = DA_LIGNE.CODE_ART and ARTFOURN.FOU = fou.CODE
                left join ARTGF with (nolock) on ARTGF.CODE_ART = DA_LIGNE.CODE_ART and ARTGF.CODE_GF = fou.FOURPRIN
                left join ARTICLES with (nolock) on ARTICLES.CODE_ART = DA_LIGNE.CODE_ART
                left join SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = DA_LIGNE.SSFAM
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = DA_LIGNE.NM_DCOMP
                where ";

        $req .= $clauseWhere;

        $arrayReq[':codeFou'] = $codeFou;

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getInfoArticle(string $codeArt, string $codeFou)
    {

        $req = "SELECT ARTICLES.CODE_ART, 
                (CASE 
                    WHEN ISNULL(t.DES_ART, '') = ''
                    THEN ARTICLES.DESIGN
                    ELSE t.DES_ART
                END) AS DESIGN, 
                ARTICLES.DTE_COUT, 
                ARTICLES.PRIX_COUT, 
                ARTICLES.CUM_COUT, 
                ARTICLES.SSFAM, 
                ARTICLES.UNIT, 
                ARTICLES.CLASSE, 
                ARTICLES.ZONE_NUM, 
                ARTICLES.ZONEALPHA, 
                ARTICLES.STOCKE, 
                ARTICLES.DTEVALMIN, 
                ARTICLES.DTEVALMAX, 
                ARTICLES.CTRL_QUA, 
                ARTICLES.TXTVA, 
                ARTICLES.CODEFAB, 
                ARTICLES.CODEGAM, 
                ARTICLES.REFARTFAB, 
                ARTICLES.TYPEGAM, 
                ARTICLES.PRXPLBAS, 
                ARTICLES.DMAJPBAS, 
                ARTICLES.IMAGART, 
                ARTICLES.DOUANE, 
                ARTICLES.C_UNSPSC, 
                ARTICLES.CDIRFMT, 
                ARTICLES.DATEMODI, 
                ARTICLES.NUM_CLEQ, 
                ARTICLES.COEFFCLA, 
                ARTICLES.NVIBOXAP, 
                ARTICLES.CRITIQUE, 
                ARTICLES.IDRAYON, 
                ARTICLES.GENRERIQUE, 
                ARTICLES.LIMITPERSTOCK, 
                ARTICLES.RECEP_AUTO, 
                ARTICLES.PER_REC_AUTO, 
                ARTICLES.ARTFRAISPORT, 
                ARTICLES.MODIFPAR, ARTICLES.NONRECEPT ARTICLE_NONRECPT, ARTICLES.AV_TAXLOC ARTICLE_AV_TAXLOC,
                SSFAMILL.NONRECEPT SSFAMILL_NONRECEPT, SSFAMILL.CODE_FAM, SSFAMILL.AV_TAXLOC SSFAMILL_AV_TAXLOC, 

                ARTFOURN.POIDS ARTFOURN_POIDS, ARTFOURN.LONGU ARTFOURN_LONGU, ARTFOURN.LARG ARTFOURN_LARG, ARTFOURN.HAUT ARTFOURN_HAUT, ARTFOURN.CTRL_QUA ARTFOURN_CTRL_QUA,
                ARTFOURN.VOLUME ARTFOURN_VOLUME, ARTFOURN.PAY_ORI ARTFOURN_PAY_ORI, ARTFOURN.PAY_PROV ARTFOURN_PAY_PROV, ARTFOURN.POIDSNET ARTFOURN_POIDSNET, ARTFOURN.REFARTFOU ARTFOURN_REF_FOURN,

                ARTGF.POIDS ARTGF_POIDS, ARTGF.LONGU ARTGF_LONGU, ARTGF.LARG ARTGF_LARG, ARTGF.HAUT ARTGF_HAUT, 
                ARTGF.VOLUME ARTGF_VOLUME, ARTGF.PAY_ORI ARTGF_PAY_ORI, ARTGF.PAY_PROV ARTGF_PAY_PROV, ARTGF.POIDSNET ARTGF_POIDSNET, ARTGF.REFARTFOU ARTGF_REF_FOURN,
                (SELECT COUNT(*) FROM CONV_ART WITH (NOLOCK) WHERE CODE_ART = ARTICLES.CODE_ART) NB_UNITE,
                CASE 
                    WHEN ARTFOURN.CODE_ART != '' THEN 'ARTFOURN'
                    WHEN ARTGF.CODE_ART != ''  THEN 'ARTGF'
                    ELSE '' 
                END INFO_ART
                FROM ARTICLES WITH (NOLOCK)
                LEFT JOIN FOURNISS fou WITH (NOLOCK) ON fou.CODE = :codeFou
                LEFT OUTER JOIN TRAD_ART t WITH(NOLOCK) ON ARTICLES.CODE_ART = t.COD_ART AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
                LEFT JOIN ARTFOURN WITH (NOLOCK) ON ARTFOURN.CODE_ART = ARTICLES.CODE_ART AND ARTFOURN.FOU = fou.CODE
                LEFT JOIN ARTGF WITH (NOLOCK) ON ARTGF.CODE_ART = ARTICLES.CODE_ART AND ARTGF.CODE_GF = fou.FOURPRIN
                LEFT JOIN SSFAMILL WITH (NOLOCK) ON SSFAMILL.CODE_SFA = ARTICLES.SSFAM
                WHERE ARTICLES.CODE_ART = :codeArt";

        
        $arrayReq[':codeFou'] = $codeFou;
        $arrayReq[':codeArt'] = $codeArt;

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * getListeAO
     *
     * @param  string $clauseWhere
     * @param  array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getListeAO(string $clauseWhere, array $arrayReq, string $codeFou, string $societe) : ?array
    {
        $req = "select 
        
                    l.*, 

                    ARTICLES.DOUANE, ARTICLES.NONRECEPT ARTICLE_NONRECPT, ARTICLES.AV_TAXLOC ARTICLES_AV_TXLOC, ARTICLES.TXTVA ART_TVA,
                    ARTICLES.PER_REC_AUTO ARTICLE_PER_REC_AUTO,ARTICLES.RECEP_AUTO ARTICLES_RECEP_AUTO, 

                    SSFAMILL.NONRECEPT SSFAMILL_NONRECEPT, SSFAMILL.CODE_FAM, SSFAMILL.AV_TAXLOC SSFAMILL_AV_TAXLOC, 
                    SSFAMILL.PER_REC_AUTO SSFAMILL_PER_REC_AUTO,SSFAMILL.RECEP_AUTO SSFAMILL_RECEP_AUTO,SSFAMILL.CODE_SFA sousFamille,
                    
                    TXDEVPIVOT.TAUX TAUX_DEV_BDD, r.DELAI DELAI_REP, dr.DEVISE DEVISE_REP, devAO.VALEUR TAUX_DEV_SOC,

                    ARTFOURN.POIDS ARTFOURN_POIDS, ARTFOURN.LONGU ARTFOURN_LONGU, ARTFOURN.LARG ARTFOURN_LARG, ARTFOURN.HAUT ARTFOURN_HAUT, 
                    MEMOS.CONTMEMO DES_COMPL,ARTFOURN.VOLUME ARTFOURN_VOLUME, ARTFOURN.PAY_ORI ARTFOURN_PAY_ORI, ARTFOURN.PAY_PROV ARTFOURN_PAY_PROV, 
                    ARTFOURN.POIDSNET ARTFOURN_POIDSNET, ARTFOURN.REFARTFOU ARTFOURN_REF_FOURN, 

                    ARTGF.POIDS ARTGF_POIDS, ARTGF.LONGU ARTGF_LONGU, ARTGF.LARG ARTGF_LARG, ARTGF.HAUT ARTGF_HAUT, 
                    ARTGF.VOLUME ARTGF_VOLUME, ARTGF.PAY_ORI ARTGF_PAY_ORI, ARTGF.PAY_PROV ARTGF_PAY_PROV, ARTGF.POIDSNET ARTGF_POIDSNET, 
                    ARTGF.REFARTFOU ARTGF_REF_FOURN,

                    DA_LIGNE.RECEP_AUTO DA_RECEP_AUTO,DA_LIGNE.DEJA_COMM, SITE_STK, CTRL_BR, DA_LIGNE.TYPNMP, 
                    DA_LIGNE.TEMPINIT,DA_LIGNE.DATEDEBCONT, DA_LIGNE.DATEFINCONT, DA_LIGNE.CONTRAT_PERIOD, DA_LIGNE.FIN_DEB_PER, DA_LIGNE.DALI_RECEPAUTO_JOUR, QTE_DEM, 
                    DA_LIGNE.TVA DA_TVA, 
                    DA_LIGNE.SECTIMP, DA_LIGNE.CODE_COMP, DA_LIGNE.SSCPTEANA, DA_LIGNE.ID_CG, DA_LIGNE.LIBGES, DA_LIGNE.CODGES,

                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.CODE_ART else r.CODE_ART end CodeArt,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.DES_ART else r.DES_ART end Designation,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.DCOMP else MEMOS.CONTMEMO end DesComp,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.QTE_PROP * r.QTE else r.QTE end Qte,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.UNITE else r.UNITE end Unit,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.PU else r.PU end PxUnit,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.DES_FOUR else r.DES_FOUR end DesFourn,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.REF_FOUR else r.REF_FOUR end RefFourn,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.COD_FAB else r.COD_FAB end CodFab,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.COD_GAM else r.COD_GAM end CodGam,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.REF_FAB else r.REF_FAB end RefFab,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.PAY_ORI else r.PAY_ORI end PaysOrigine,
                    case when ISNULL(s.NO_SSLIG,0)<>0 then s.PAY_PROV else r.PAY_PROV end PayProvenance,
                    CASE 
                        WHEN ARTFOURN.CODE_ART != '' THEN 'ARTFOURN'
                        WHEN ARTGF.CODE_ART != ''  THEN 'ARTGF'
                        ELSE '' 
                    END INFO_ART

                from AO_LIGNE l with (nolock)

                LEFT join AO_REP r with (nolock) on r.NO_AO = l.NO_AO AND r.NO_PO_AO = l.NO_PO and r.CODE_FOU = r.CODE_FOU
                left join AO_REPSL s with(nolock) on s.NO_AO=r.NO_AO and s.CODE_FOU=r.CODE_FOU and s.TYPE_FG=r.TYPE_FG and s.DTE_REP=r.DTE_REP and s.C_V=r.C_V and s.NO_PO_AO=r.NO_PO_AO       
                LEFT JOIN AO_RETPARLIGNE rl with (nolock) on rl.NO_AO = r.NO_AO and rl.NO_PO = r.NO_PO_AO and r.CODE_FOU = rl.CODE_FOU 
                cross apply (
                    select top 1 * from AO_DAREP with(nolock)
                    where NO_AO = r.NO_AO and AO_DAREP.CODE_FOU= r.CODE_FOU and AO_DAREP.TYPE_FG = r.TYPE_FG 
                    and AO_DAREP.EVE_REP != '0' and AO_DAREP.C_V = rl.RET_CV
                    ORDER BY AO_DAREP.DTE_REP DESC, AO_DAREP.C_V DESC
                ) dr
                left join FOURNISS fou with (nolock) on fou.CODE = r.CODE_FOU
                left join ARTFOURN with (nolock) on ARTFOURN.CODE_ART = (case when ISNULL(s.NO_SSLIG,0)<>0 then s.CODE_ART else r.CODE_ART end) and ARTFOURN.FOU = fou.CODE
                left join ARTGF with (nolock) on ARTGF.CODE_ART = (case when ISNULL(s.NO_SSLIG,0)<>0 then s.CODE_ART else r.CODE_ART end) and ARTGF.CODE_GF = fou.FOURPRIN
                left join ARTICLES with (nolock) on ARTICLES.CODE_ART = (case when ISNULL(s.NO_SSLIG,0)<>0 then s.CODE_ART else r.CODE_ART end)
				left join SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = (case when ISNULL(ARTICLES.SSFAM,'0')<>'0' then ARTICLES.SSFAM else l.SSFAMILLE end)
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = l.NM_DCOMP
                left join DA_LIGNE with (nolock) on DA_LIGNE.NO_DA = l.NO_DA and DA_LIGNE.NO_PO = l.NO_PO_DA
				left join SOCIETE with (nolock) on SOCIETE.CODE_STE = '$societe'
				left join TXDEVPIVOT with (nolock) on TXDEVPIVOT.DEV_PIVOT = SOCIETE.DEVISE and TXDEVPIVOT.DEV_A_CONV = dr.DEVISE
                left join DEVISES devAO with (nolock) on devAO.CODE_DEVI = dr.DEVISE
                where r.CODE_FOU = :codeFou and dr.DTE_REP = r.DTE_REP
        ";


                

        $req .= ' and '.$clauseWhere;

        $arrayReq[':codeFou'] = $codeFou;

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * getListeClauseDA
     *
     * @param  string $clauseWhere
     * @param  array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getListeClauseDA(string $clauseWhere, array $arrayReq) : ?array
    {
        $req = "select * from DA_CLAUS with (nolock) 
                left join MEMOS with (nolock)  on MEMOS.NODEMEMO = DA_CLAUS.NM_PARAG 
                where ";

        $req .= $clauseWhere;

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param  string $clauseWhere
     * @param  array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getListeClauseAO($clauseWhere, $arrayReq)
    {
        $req = "select * from AO_CLAUS with (nolock) 
                left join MEMOS with (nolock)  on MEMOS.NODEMEMO = AO_CLAUS.NM_PARAG 
                where ";

        $req .= $clauseWhere;

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param  string $clauseWhere
     * @param  array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getListePJDA($clauseWhere, $arrayReq)
    {
        $req = "select * from DA_PIJO with (nolock) where ";

        $req .= $clauseWhere;

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param  string $clauseWhere
     * @param  array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getListePJAO($clauseWhere, $arrayReq)
    {
        $req = "select * from AO_PIJO with (nolock) where ";

        $req .= $clauseWhere;

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getListePJbyDoc(string $codeFou, string $codeSte, string $livraison, string $emetteur)
    {
        return $this->fetchAssocAll(
            "SELECT * 
            FROM DOC_ATT with (nolock) 
            WHERE PJ_CDE = 0x54 and (
                (NO_DA = 'FOURNISS' and COD_ENR = :codeFou) 
                OR (NO_DA = 'GRPFOU' and COD_ENR = (
                    SELECT TOP 1 FOURPRIN FROM FOURNISS with (nolock) 
                    WHERE CODE = :codeFou2)
                ) 
                OR (NO_DA = 'SOCIETE' and COD_ENR = :societe) 
                OR (NO_DA = 'CENTRE' and COD_ENR =:livraison) 
                OR (NO_DA = 'CENTRE' and COD_ENR =:emetteur)
            )",
            [
                ':codeFou' => $codeFou,
                ':codeFou2' => $codeFou,
                ':societe' => $codeSte,
                ':livraison' => $livraison,
                ':emetteur' => $emetteur
            ]
        );
    }

    /**
     * @return array<mixed>
    */
    public function getAccordCadre(string $codeArt, string $ctrEmeteur, string $ctrLivr, string $societe, string $codeFou)
    {
        $req = "select  distinct a.CODE_ACD, f.CODE CodeFournisseur, f.RS from ACCCADRE a with (nolock)
                        left outer join ACCTR e with (nolock) on e.CODE_ACD=a.CODE_ACD
                        left outer join ACCTRLIV l with (nolock) on l.CODE_ACD=a.CODE_ACD
                        left outer join GRPFOU g with (nolock) on a.TYPE_FG='G' and g.CODE=a.CODE_FOUR
                        left outer join FS_ACD s with (nolock) on a.TYPE_FG='G' and s.CODE_ACD=a.CODE_ACD
                        left outer join FOURNISS f with (nolock) on (a.TYPE_FG='F' and f.CODE=a.CODE_FOUR) or (a.TYPE_FG='G' and f.FOURPREF=a.CODE_FOUR and f.CODE=s.CODE_FOU)
                        left outer join ACART art with (nolock) on art.CODE_ACD=a.CODE_ACD
                        left outer join MARCHES m with (nolock) on m.CODE=a.MARCHE
                        left outer join MARCHSOC ms with (nolock) on ms.CODEMARCH=a.MARCHE and ms.CODESOC=:societe
                        left outer join MARCHUSR mu with (nolock) on mu.CODE_MAR=a.MARCHE
                        where ISNULL(f.CODE,'')<>'' and f.CODE= :codeFou
                        and (isnull(e.CODE_CTR,'')='' or e.CODE_CTR=:centreE)
                        and (isnull(l.CODE_CTR,'')='' or l.CODE_CTR=:centreL)
                        and (ISNULL(art.CODE_ART,'')='' or art.CODE_ART=:codeArt)
                        and (a.MARCHE='' or ms.CODESOC<>'')
                        and (a.MARCHE='' or ISNULL(mu.CODE_USR,'')='' or mu.CODE_USR=:ach)
                        and (a.DATE_FIN='00000000' or a.DATE_FIN='' or a.DATE_FIN > '".date('Ymd')."') and a.DATE_DEB < '".date('Ymd')."'";

            $arrayReq = [
                ':codeArt' => $codeArt,
                ':centreE' => $ctrEmeteur,
                ':centreL' => $ctrLivr,
                ':societe' => $societe,
                ':codeFou' => $codeFou,
                ':ach' => $_SESSION["Session_Login"]
            ];

            return $this->fetchAssocAll($req, $arrayReq);
    }

    public function updateQteCommande(string $noDa, string $noPo, string $qteCommande, bool $traitee = false, bool $add = true) : void
    {
        if ($add) {
            $req = "update DA_LIGNE set DEJA_COMM = DEJA_COMM+$qteCommande";
        } else {
            $req = "update DA_LIGNE set DEJA_COMM = DEJA_COMM-$qteCommande";
        }

        if ($traitee) {
            $req .= " , TRAITEE = 0x54 ";
        }

        $req .= " where NO_DA = :noDa and NO_PO = :noPo";

        $arrayReq = [
            ':noDa' => $noDa,
            ':noPo' => $noPo,
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getCalculMontantCde(string $noCde)
    {
        $req = "select 
                    SUM(PU*QTE_COMM) TOT_CDE, 
                    SUM((PU*QTE_COMM) * (1 - (REMISE/100))) TOT_CDEHT,
                    SUM(((PU*QTE_COMM) * ((1 - (REMISE/100))) * (1 + (TVA/100)))) TOT_TTC
                from CA_LIGNE with(nolock) where NO_CDE = :noCde";

        $arrayReq = [':noCde' => $noCde];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
     */
    public function getCampsFrsActif(string $codeFrs)
    {
        $req = "select * from CAMP_LST_FOUR with (nolock) WHERE CODE_FOU = :codeFrs and (('".date('Ymd')."' BETWEEN DTEDEB and DATEFIN) or DATEFIN = '00000000')";
    
        $arrayReq = [':codeFrs' => $codeFrs];

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getCampsDAActif(string $listeDA)
    {
        $req = "select * from DA_ENTET with (nolock) where NOCDISDA = 0x54 and NO_DA in ('$listeDA')";
                                                                          
        return $this->fetchAssocAll($req, []);
    }

    /**
     * @return array<mixed>
     */
    public function getListeCritere()
    {
        $req = "select * from CRITERES with (nolock) where (('".date('Ymd')."' BETWEEN DTEDEBVAL and DTEFINVAL) or DTEFINVAL= '00000000')";

        return $this->fetchAssocAll($req, []);
    }

    /**
     * @return array<mixed>
     */
    public function controleDA(string $noDa, string $societe)
    {
        $req = "select DA_ENTET.NO_DA, DA_ENTET.TRAITEE,DA_ENTET.SIGNEE,
                (SELECT COUNT(*) FROM DA_LIGNE with (nolock) where DA_LIGNE.NO_DA = DA_ENTET.NO_DA and DA_LIGNE.TRAITEE =0x46) countLigne
                from DA_ENTET with(nolock) where NO_DA = :noDa and DA_ENTET.CODE_STE = :codeSte /*and DA_ENTET.SIGNEE = 0x54*/ ";

        $arrayReq = [
            ':noDa' => $noDa,
            ':codeSte' => $societe,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }

    /**
     * @return array<mixed>
     */
    public function getClauseTypeAchat(string $typeAchat, string $codeSte, string $codeLng)
    {
        $req = "select * from TAC_CLAU with (nolock)
        left join MEMOS with (nolock) on MEMOS.NODEMEMO = TAC_CLAU.NM_PARAG
        where COD_SOC = :codeSte and COD_LNG = :codeLng AND TYP_ACH =:typeAchat";

        $arrayReq = [
            ':codeSte' => $codeSte,
            ':codeLng' => $codeLng,
            ':typeAchat' => $typeAchat
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getPJTypeAchat(string $typeAchat, string $codeSte, string $codeLng)
    {
        $req = "select * from TAC_PIJO with (nolock)
                where COD_SOC = :codeSte and COD_LNG = :codeLng AND TYP_ACH =:typeAchat";

        $arrayReq = [
            ':codeSte' => $codeSte,
            ':codeLng' => $codeLng,
            ':typeAchat' => $typeAchat
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function checkIfArticleIsStock(string $codeArt, string $site)
    {
        $req = "select * from SSTOCKLO with (nolock)
                left join SARTICLE with (nolock) on SARTICLE.CODE_ART = SSTOCKLO.CODE_ART
                where SSTOCKLO.CODE_ART = :codeArt AND SSTOCKLO.SITE = :site
                AND (SSTOCKLO.DTE_FERM >= '".date('Ymd')."' OR SSTOCKLO.DTE_FERM = '00000000')
                and (SARTICLE.DTE_FERM >= '".date('Ymd')."' OR SARTICLE.DTE_FERM = '00000000')";

        $arrayReq = [
            ':codeArt' => $codeArt,
            ':site' => $site,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }

    /**
     * @return array<mixed>
     */
    public function searchCasDoc(string $centre, string $modTrans, string $modeLiv, string $modePaie)
    {
        $req = "SELECT TOP 1 CENTRE,MOD_TRS,MOD_LIV,MOD_PAI from CASDOC with(nolock)
        where 
        CENTRE=:centre and MOD_TRS=:modTrans
        and (MOD_LIV=:modLiv or MOD_LIV='')
        and (MOD_PAI=:modPai or MOD_PAI='')
        ORDER BY MOD_LIV DESC, MOD_PAI DESC";

        $arrayReq = [
            ':centre' => $centre,
            ':modTrans' => $modTrans,
            ':modLiv' => $modeLiv,
            ':modPai' => $modePaie,
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }

    /**
     * @return array<mixed>
     */
    public function getPiedExpedition(string $centre, string $modTransport, string $transitaire, string $societe, string $lngCde)
    {
        $req = "select 
                    *, ARTICLES.TXTVA,
                    
                    CASE 
                        WHEN TRAD_ART.DES_ART != '' THEN TRAD_ART.DES_ART 
                        ELSE PIEDEXPE.DESIGN 
                    END LIB_ART,
                    
                    CASE  PIEDEXPE.TYP_FOURN
                        WHEN 'T' THEN :transitaire
                        WHEN 'A' THEN PIEDEXPE.FOURNDIV
                        WHEN 'D' THEN (select ASSUREUR from SOCIETE where CODE_STE = :societe)
                        else ''
                    end FOURNI_PRIX
                    
                from PIEDEXPE with (nolock)
                left join TRAD_ART with (nolock) on TRAD_ART.COD_ART = PIEDEXPE.COD_ART and COD_LNG = :langue
                left join ARTICLES with (nolock) on ARTICLES.CODE_ART = PIEDEXPE.COD_ART
                where CENTRE = :centre and MOD_TRS = :modTrs order by NO_LIG";

        $arrayReq = [
            ':centre'       => $centre,
            ':modTrs'       => $modTransport,
            ':transitaire'  => $transitaire,
            ':societe'      => $societe,
            ':langue'       => $lngCde,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getPriceAcd(string $acd, string $frs, string $art, string $qte)
    {
        $req = "select 
                    PU
                from ACART with (nolock) 
                where CODE_ART = :codeArt and CODE_ACD = :codeAcd and FOURNISS = :fourniss and QTEMAX > :qte and PU <> 0";
        
        $arrayReq = [
            ':codeArt'  => $art,
            ':codeAcd'  => $acd,
            ':fourniss' => $frs,
            ':qte'      => $qte
        ];

        return $this->fetchAssocOne($req, $arrayReq);
    }

    
    /**
     * Summary of areTPComplete
     * @param mixed $numDA
     * @return mixed
     */
    public function areTPComplete($numDA): mixed
    {
        $req = <<<SQL
                SELECT 
                    CASE
                        WHEN 
                            (TOTAL_POS = 0)
                            OR
                            (TYEVTSTP.HAPLUS = 0x46 AND TP_EVT.RES_TP = '')  
                        THEN 'INCOMPLETE'
                        ELSE 'COMPLETE'
                    END IS_COMPLETE
                FROM TP_EVT
                LEFT JOIN TYEVTSTP ON TP_EVT.EVENEMENT = TYEVTSTP.CODE_EVT
                OUTER APPLY (
                    SELECT COUNT(*) AS TOTAL_POS
                    FROM TP_POS
                    WHERE TP_EVT.NUM_CDE = TP_POS.NUM_CDE
                    AND TP_EVT.NUM_TP = TP_POS.NUM_TP
                    AND TP_POS.TYPEOBJ = 'DA_ENTET'
                ) tp_pos
                WHERE TP_EVT.NUM_CDE = :id AND TP_EVT.TYPEOBJ = 'DA_ENTET'
        SQL;

        $arrayReq = [':id' => $numDA];

        $dbResponse = $this->fetchAssocAll($req, $arrayReq);
        
        return array_unique(array_column($dbResponse, 'IS_COMPLETE'))[0] === 'COMPLETE';
    }

    /**
     * Summary of getCountDaLigneTraitee
     * @param mixed $numDA
     * @return bool
     */
    public function getCountDaLigneTraitee($numDA): bool
    {
        $req = <<<SQL
            select count(*) as total_traitee from DA_LIGNE where NO_DA = :no_da and TRAITEE = 0x54
        SQL;

        $arrayReq = [':no_da' => $numDA];

        $dbResponse = $this->fetchAssocOne($req, $arrayReq);

        $intVal = intval($dbResponse['total_traitee']);

        return $intVal > 0;
    }
}
