<?php

namespace Modules\Commande\Repository;

use Entity\Model\Hastutil;
use Helpers\Outil\Date;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Societe;

class GareDeTriage
{
    
    private $pdo;
    
    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * retourne la liste des commandes a traiter dans la gare de triage
     * @param array $post
     * @return array
     */
    public function listeGareDeTriage($post = [])
    {
        $param = [
            'NOTCREATCDEDANONSIG' => Sql::paravrac($this->pdo, 'PARAMSIG', 'NOTCREATCDEDANONSIG'),

            'SISECTOR' => Sql::paravrac($this->pdo, 'PARAMSTE', 'SISECTOR'),
            'CDCSECTO' => Sql::paravrac($this->pdo, 'PARAMSTE', 'CDCSECTO'),
            'SCASECTO' => Sql::paravrac($this->pdo, 'PARAMSTE', 'SCASECTO'),
        ];

        $where = '';

        if (isset($post['CODE_FOU']) && $post['CODE_FOU'] != '') {
            $split = explode('/', $post['CODE_FOU']);
            $liste = implode("','", $split);

            $where .= " and DA_LIGNE.CODE_FOU in ('$liste')";
        }

        if (isset($post['CTRLIVR']) && $post['CTRLIVR'] != '') {
            $split = explode('/', $post['CTRLIVR']);
            $liste = implode("','", $split);

            $where .= " and DA_LIGNE.CTRLIVR in ('$liste')";
        }

        if (isset($post['SSFAM']) && $post['SSFAM'] !='') {
            $split = explode('/', $post['SSFAM']);
            $liste = implode("','", $split);

            $where .= " and DA_LIGNE.SSFAM in ('$liste')";
        }

        if (isset($post['LIGNE_NON_AFFECT'])) {
            $where .= " and DA_LIGNE.ACHETEUR = ''";
        }

        if (isset($post['ACHETEUR']) && $post['ACHETEUR'] != '') {
            $split = explode('/', $post['ACHETEUR']);
            $liste = implode("','", $split);

            $where .= " and DA_LIGNE.ACHETEUR in ('$liste')";
        }

        if (isset($post['TYPE_DOC']) && $post['TYPE_DOC'] != '') {
            $split = explode('/', $post['TYPE_DOC']);
            $liste = implode("','", $split);

            $where .= " and DA_LIGNE.TYP_DOC in ('$liste')";
        }

        if (isset($post['MODTRANS']) && $post['MODTRANS'] != '') {
            $split = explode('/', $post['MODTRANS']);
            $liste = implode("','", $split);

            $where .= " and DA_LIGNE.MOD_TRS in ('$liste')";
        }

        if (isset($post['DAENT_CENTRE_EMETTEUR']) && $post['DAENT_CENTRE_EMETTEUR'] != '') {
            $split = explode('/', $post['DAENT_CENTRE_EMETTEUR']);
            $liste = implode("','", $split);

            $where .= " and DAENT_CENTRE_EMETTEUR in ('$liste')";
        }

        if (isset($post['DA_EN_ATTENTE']) && $post['DA_EN_ATTENTE'] != '') {
            //DA_EN_ATTENTE
            $duree = (int)$post['DA_EN_ATTENTE'];
            $dateEnAttente = date('Ymd', (int)strtotime(date('Ymd'). ' - '.$duree.' days'));
            $where .= " and DA_ENTET.DTE_VAL <= :dateEnAttente";
            $arrayReq[':dateEnAttente'] = $dateEnAttente;
        }
        
        if (!$_SESSION['Session_UsrMultiSoc']) {
            $where .=
            " AND (DA_LIGNE.CODE_STE in (
                SELECT hastutil.CODE_STE 
                FROM HASTUTIL hastutil with (nolock) 
                WHERE hastutil.CODE='".$_SESSION['Session_Login']."') OR DA_LIGNE.CODE_STE in (
                    SELECT socUsr.COD_STE FROM SOC_USR socUsr with (nolock) WHERE socUsr.COD_USR='".$_SESSION['Session_Login']."'))";
        }

        $req = "SELECT 
                    /* Critère de regroupement */
                    distinct DA_LIGNE.CODE_FOU,DA_LIGNE.NO_DA, DA_LIGNE.NO_PO,  DA_LIGNE.SECTIMP, DA_LIGNE.CODE_COMP,DA_LIGNE.SSCPTEANA,
                    DA_LIGNE.SSFAM,DAENT_CENTRE_EMETTEUR,DA_LIGNE.CTRLIVR, DA_LIGNE.TYP_DOC, DA_LIGNE.ACD,  DA_LIGNE.MOD_TRS, 
                    /* Info de la DA */
                    DA_LIGNE.CODE_ART, DES_SOMM,SIGNEE,dcomp.CONTMEMO,titre.CONTMEMO TITRE,comm.CONTMEMO COMM, DTELIVSOU, DA_ENTET.COD_LNG, 
                    devDA.VALEUR TAUX_DEV_SOC, tauxBDD.TAUX TAUX_DEV_BDD, soc.DEVISE DEVISE_SOC, DA_LIGNE.ID_CAT_ORI,
                    (select TOP 1 DTE_LIV from DA_PLANN with (nolock) where DA_PLANN.NO_DA=DA_LIGNE.NO_DA AND DA_PLANN.NO_PO = DA_LIGNE.NO_PO order by DTE_LIV asc) DTE_PLANN,
                    /* Info QTE DA */
                    DA_LIGNE.QTE_DEM,DA_LIGNE.DEJA_COMM,DA_LIGNE.QTE_DEM-DA_LIGNE.DEJA_COMM RAL,DA_LIGNE.UNITE,
                    /* Info prix DA */
                    DA_LIGNE.BUDGET,DA_LIGNE.TX_REMISE, DA_ENTET.DEVISE,DA_LIGNE.PU,MNT_LIG,
                    bestPrixFrs.pu_remise,bestPrixFrs.pu, bestPrixFrs.remise,bestPrixFrs.fournisseur,bestPrixFrs.ACD acd, bestPrixFrs.lib_origine PROVENANCE,
                    /* Lib critère de regroupement */
                    FOURNISS.RS LIB_CODE_FOU,SECTIMPU.LIBELLE LIB_SECTIMP,CODECOMP.LIBELLE LIB_CODE_COMP,SSCPTANA.LIBELLE LIB_SSCPTEANA,
                    ISNULL(t.DESIGNAT, SSFAMILL.DESIGNAT) AS LIB_SSFAM,
                    ce.RAIS_SOC LIB_DAENT_CENTRE_EMETTEUR,
                    cl.RAIS_SOC LIB_CTRLIVR,
                    TYPDOC.LIBELLE LIB_TYP_DOC,
                    ACCCADRE.LIBELLE LIB_ACD, TRAD_MDT.DES_ART LIB_MOD_TRS,
                    /* Info fournisseur */
                    FOURNISS.DEVISE DEVISE_FOU, FOURNISS.MNTMINFAC, FOURNISS.MNTMINFRA, bestPrixFrs.FOURPREF, FOURNISS.RMINCPXN,
                    /* Info fournisseur AO */
                    frsAO.DEVISE AO_DEVISE_FOU, frsAO.MNTMINFAC AO_MNTMINFAC, frsAO.MNTMINFRA AO_MNTMINFRA, frsAO.RMINCPXN AO_RMINCPXN, frsAO.RS AO_RS,
                    /* dernier Signataire */
                    signataire.NOM SIG_NOM, signataire.UTIL SIG_UTIL, signataire.DTE SIG_DTE,
                    /* Autre */
                    AO_LIGNE.NO_AO, art.NUM_CLEQ,AO_RETPARLIGNE.CODE_FOU FRS_AO, cl.TRS_INTER, DELAI_ACART,
                    DA_LIGNE.REF_FOUR, DA_LIGNE.REF_FAB, DA_LIGNE.COD_FAB, bestPrix.pu_remise bestPrix, AO_REP.PU prixAO

                    from DA_LIGNE with (nolock)
                    left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
                    left join MEMOS dcomp with (nolock) on dcomp.NODEMEMO = DA_LIGNE.NM_DCOMP
                    left join AO_LIGNE with (nolock) on AO_LIGNE.NO_DA = DA_LIGNE.NO_DA and AO_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO
                    LEFT join AO_ENTET with (nolock) on AO_ENTET.NO_AO = AO_LIGNE.NO_AO
                    left join FOURNISS with (nolock) on FOURNISS.CODE = DA_LIGNE.CODE_FOU
                    left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = DA_LIGNE.SECTIMP and SECTIMPU.CODE_STE = ".($param['SISECTOR'] == 'O'? "DA_LIGNE.CODE_STE": "''")."
                    left join SSFAMILL with (nolock) on  SSFAMILL.CODE_SFA = DA_LIGNE.SSFAM
                    LEFT JOIN TRAD_SFA t WITH(NOLOCK) ON t.CODE_SFA = SSFAMILL.CODE_SFA AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
                    left join CENTRE ce with (nolock) on ce.CODE_CTR = DAENT_CENTRE_EMETTEUR and ce.CODE_STE = DA_LIGNE.CODE_STE
                    left join CENTRE cl with (nolock) on cl.CODE_CTR = DA_LIGNE.CTRLIVR and cl.CODE_STE = DA_LIGNE.CODE_STE
                    left join TYPDOC with (nolock) on TYPDOC.TYPE = DA_LIGNE.TYP_DOC
                    left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = DA_LIGNE.CODE_COMP and CODECOMP.CODE_STE = ".($param['CDCSECTO'] == 'O'? "DA_LIGNE.CODE_STE": "''")."
                    LEFT JOIN SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = DA_LIGNE.SSCPTEANA and SSCPTANA.CODE_STE = ".($param['SCASECTO'] == 'O'? "DA_LIGNE.CODE_STE": "''")."
                    left join ACCCADRE with (nolock) on ACCCADRE.CODE_ACD = DA_LIGNE.ACD
                    left join TRAD_MDT with (nolock) on TRAD_MDT.COD_ART = DA_LIGNE.MOD_TRS and TRAD_MDT.COD_LNG = :langue
                    left join MEMOS titre with (nolock) on titre.NODEMEMO = DA_ENTET.NM_TITRE 
                    left join MEMOS comm with (nolock) on comm.NODEMEMO = DA_ENTET.NM_COMMEN 
                    left join ARTICLES art with (nolock) on art.CODE_ART = DA_LIGNE.CODE_ART
                    left join DEVISES devDA with (nolock) on devDA.CODE_DEVI = DA_ENTET.DEVISE
                    left join SOCIETE soc with (nolock) on soc.CODE_STE = DA_ENTET.CODE_STE
                    left join TXDEVPIVOT tauxBDD with (nolock) on tauxBDD.DEV_PIVOT = soc.DEVISE and tauxBDD.DEV_A_CONV = DA_ENTET.DEVISE
                    LEFT JOIN AO_RETPARLIGNE with (nolock) on AO_RETPARLIGNE.NO_AO = AO_LIGNE.NO_AO and AO_RETPARLIGNE.NO_PO = AO_LIGNE.NO_PO
                    left join AO_REP with (nolock) on AO_REP.NO_AO = AO_RETPARLIGNE.NO_AO and AO_REP.NO_PO_AO = AO_RETPARLIGNE.NO_PO and AO_REP.CODE_FOU = AO_RETPARLIGNE.CODE_FOU and AO_RETPARLIGNE.RET_CV = AO_REP.C_V and AO_RETPARLIGNE.TYPE_FG = AO_REP.TYPE_FG
                    outer apply (
						select TOP 1 NOM, UTIL, DA_EVENT.DTE from DA_EVENT with (nolock)
						left join HASTUTIL with (nolock) on HASTUTIL.CODE = DA_EVENT.UTIL
						WHERE DA_EVENT.NO_DA = DA_LIGNE.NO_DA and DA_EVENT.CODE = 'SDA' order by NUM_ORD desc
					) signataire

                    outer apply (
                        select top 1 fournisseur,pu_remise,pu, testPx.REMISE remise, f.FOURPREF, origine, lib_origine, ACD, DELAI_ACART from (
                            select 
                                CASE 
                                    WHEN acd.TYPE_FG='G' THEN (select TOP 1 CODE from FOURNISS with (nolock) where FOURPRIN = acd.CODE_FOUR order by FOURPREF DESC)
                                    ELSE acd.CODE_FOUR
                                END fournisseur,
                                case when isnull(prix, 0)=0 then ac.PU else prix end pu_remise,
                                case when isnull(prix, 0)=0 then ac.PU else prix end pu,
                                0 REMISE, 
                                1 origine, 
                                ac.CODE_ACD ACD, 
                                'ACD' lib_origine,
                                ac.DELAI DELAI_ACART
                            from ACART ac with (nolock)
                            left join ACCCADRE acd with (nolock) on acd.CODE_ACD = ac.CODE_ACD
                            left outer join ACCTR e with (nolock) on e.CODE_ACD=acd.CODE_ACD
                            left outer join ACCTRLIV l with (nolock) on l.CODE_ACD=acd.CODE_ACD
                            left outer join MARCHSOC ms with (nolock) on ms.CODEMARCH=acd.MARCHE and ms.CODESOC=DA_ENTET.CODE_STE
                            left outer join MARCHUSR mu with (nolock) on mu.CODE_MAR=acd.MARCHE
                            outer apply (
                                    select TOP 1 
                                        case TYPTAR 
                                            when 'P' then (acp.PU_TX) 
                                            when 'T' then (ac.PU)*(1-(acp.TAUX/100))
                                        end  prix 
                                    from ACARTDET acp with (nolock)
                                    where acp.CODE_ACD = ac.CODE_ACD and acp.QTEMIN <= (DA_LIGNE.QTE_DEM-DA_LIGNE.DEJA_COMM) and CODE_ART = DA_LIGNE.CODE_ART and PU_TX != 0
                                ) acdPx 
                            WHERE (
                                    ac.DFINVAL='00000000' 
                                    or ac.DFINVAL='' 
                                    or ac.DFINVAL > :today_2
                                ) and ac.DDEBVAL < :today_3
                                and (isnull(e.CODE_CTR,'')='' or e.CODE_CTR=DA_ENTET.DAENT_CENTRE_EMETTEUR)
                                and (isnull(l.CODE_CTR,'')='' or l.CODE_CTR=DA_LIGNE.CTRLIVR)
                                AND ac.CODE_ART = DA_LIGNE.CODE_ART
                                and (acd.MARCHE='' or ms.CODESOC<>'')
                        union
                            select 
                                FOU fournisseur, 
                                pu_remise, 
                                pu, 
                                (CASE WHEN arf.REMISE>0 THEN arf.REMISE else f.REMISE end) REMISE, 
                                2 origine , 
                                '' ACD, 
                                'AFO' lib_origine,
                                '' DELAI_ACART
                            from ARTFOURN arf with (nolock)
                            left join FOURNISS f with (nolock) on f.CODE = arf.FOU
                            cross apply (
                                select top 1 (PU)*(1-((CASE WHEN arf.REMISE>0 THEN arf.REMISE else f.REMISE end)/100)) pu_remise, (PU) pu from PXAFO fp with (nolock)
                                where fp.CODE_FOU = FOU and fp.CODE_ART = arf.CODE_ART and fp.QTE <= (DA_LIGNE.QTE_DEM-DA_LIGNE.DEJA_COMM) and CODE_ART = DA_LIGNE.CODE_ART and PU != 0
                                order by QTE desc
                            ) frsPx 
                            where (
                                    arf.DTEVALMAX='00000000' 
                                    or arf.DTEVALMAX='' 
                                    or arf.DTEVALMAX > :today_0
                                ) 
                                and arf.DTEVALMIN < :today_1
                        union 
                            select 
                                rg.CODEFOURN fournisseur,
                                (a.PRIX_COUT)*(1-(rg.REMISE/100)) pu_remise,
                                (a.PRIX_COUT) pu,
                                rg.REMISE ,
                                2 origine ,
                                '' ACD, 
                                'FAB' lib_origine,
                                '' DELAI_ACART
                            from ARTICLES a with (nolock)
                            right join REMGAMM rg with (nolock) on rg.CODEFABR = a.CODEFAB and rg.CODEGAMME = a.CODEGAM
                            where a.TYPEGAM = 0x54 and a.CODE_ART = DA_LIGNE.CODE_ART and PRIX_COUT != 0
                        union
                            select 
                                (select TOP 1 CODE from FOURNISS with (nolock) where FOURPRIN = arg.CODE_GF order by FOURPREF DESC) fournisseur, 
                                pu_remise, 
                                pu, 
                                arg.REMISE, 
                                2 origine , 
                                '' ACD, 
                                'AGF' lib_origine,
                                '' DELAI_ACART
                            from ARTGF arg with (nolock)
                            cross apply (
                                select top 1 (PU)*(1-(arg.REMISE/100)) pu_remise, (PU) pu from PXAGF fp with (nolock)
                                where fp.CODE_GF = arg.CODE_GF and fp.CODE_ART = arg.CODE_ART and fp.QTE <= (DA_LIGNE.QTE_DEM-DA_LIGNE.DEJA_COMM) and CODE_ART = DA_LIGNE.CODE_ART and PU != 0
                                order by QTE desc
                            ) frsPx 
                            where (
                                    arg.DTEVALMAX='00000000' 
                                    or arg.DTEVALMAX='' 
                                    or arg.DTEVALMAX > :today_4
                                ) and arg.DTEVALMIN < :today_5  
                        ) testPx
                        left join FOURNISS f with (nolock) on f.CODE = fournisseur
                        where (
                                f.DTEVALMAX='00000000' 
                                or f.DTEVALMAX='' 
                                or f.DTEVALMAX > :today_6
                            ) and f.DTEVALMIN < :today_7
                        and (origine = 0 or pu != 0) and (DA_LIGNE.CODE_FOU = fournisseur or DA_LIGNE.CODE_FOU = '')
                        order by origine asc, FOURPREF desc, pu_remise asc
                    ) bestPrixFrs


                    
                    outer apply (
                        select top 1 fournisseur,pu_remise,pu, testPx.REMISE remise, f.FOURPREF, origine, lib_origine, ACD from (
                            select 
                                CASE 
                                    WHEN acd.TYPE_FG='G' THEN (select TOP 1 CODE from FOURNISS with (nolock) where FOURPRIN = acd.CODE_FOUR order by FOURPREF DESC)
                                    ELSE acd.CODE_FOUR
                                END fournisseur,
                                case when isnull(prix, 0)=0 then ac.PU else prix end pu_remise,
                                case when isnull(prix, 0)=0 then ac.PU else prix end pu,
                                0 REMISE, 
                                1 origine, 
                                ac.CODE_ACD ACD, 
                                'ACD' lib_origine
                            from ACART ac with (nolock)
                            left join ACCCADRE acd with (nolock) on acd.CODE_ACD = ac.CODE_ACD
                            left outer join ACCTR e with (nolock) on e.CODE_ACD=acd.CODE_ACD
                            left outer join ACCTRLIV l with (nolock) on l.CODE_ACD=acd.CODE_ACD
                            left outer join MARCHSOC ms with (nolock) on ms.CODEMARCH=acd.MARCHE and ms.CODESOC=DA_ENTET.CODE_STE
                            left outer join MARCHUSR mu with (nolock) on mu.CODE_MAR=acd.MARCHE
                            outer apply (
                                    select TOP 1 
                                        case TYPTAR 
                                            when 'P' then (acp.PU_TX) 
                                            when 'T' then (ac.PU)*(1-(acp.TAUX/100))
                                        end  prix 
                                    from ACARTDET acp with (nolock)
                                    where acp.CODE_ACD = ac.CODE_ACD and acp.QTEMIN <= (DA_LIGNE.QTE_DEM-DA_LIGNE.DEJA_COMM) and CODE_ART = DA_LIGNE.CODE_ART and PU_TX != 0
                                ) acdPx 
                            WHERE (
                                    ac.DFINVAL='00000000' 
                                    or ac.DFINVAL='' 
                                    or ac.DFINVAL > :today_8
                                ) and ac.DDEBVAL < :today_9
                                and (isnull(e.CODE_CTR,'')='' or e.CODE_CTR=DA_ENTET.DAENT_CENTRE_EMETTEUR)
                                and (isnull(l.CODE_CTR,'')='' or l.CODE_CTR=DA_LIGNE.CTRLIVR)
                                AND ac.CODE_ART = DA_LIGNE.CODE_ART
                                and (acd.MARCHE='' or ms.CODESOC<>'')
                        union
                            select 
                                FOU fournisseur, 
                                pu_remise, 
                                pu, 
                                (CASE WHEN arf.REMISE>0 THEN arf.REMISE else f.REMISE end) REMISE, 
                                2 origine , 
                                '' ACD, 
                                'AFO' lib_origine
                            from ARTFOURN arf with (nolock)
                            left join FOURNISS f with (nolock) on f.CODE = arf.FOU
                            cross apply (
                                select top 1 (PU)*(1-((CASE WHEN arf.REMISE>0 THEN arf.REMISE else f.REMISE end)/100)) pu_remise, (PU) pu from PXAFO fp with (nolock)
                                where fp.CODE_FOU = FOU and fp.CODE_ART = arf.CODE_ART and fp.QTE <= (DA_LIGNE.QTE_DEM-DA_LIGNE.DEJA_COMM) and CODE_ART = DA_LIGNE.CODE_ART and PU != 0
                                order by QTE desc
                            ) frsPx 
                            where (
                                    arf.DTEVALMAX='00000000' 
                                    or arf.DTEVALMAX='' 
                                    or arf.DTEVALMAX > :today_10
                                ) and arf.DTEVALMIN < :today_11
                        union 
                            select 
                                rg.CODEFOURN fournisseur,
                                (a.PRIX_COUT)*(1-(rg.REMISE/100)) pu_remise,
                                (a.PRIX_COUT) pu,
                                rg.REMISE ,
                                2 origine ,
                                '' ACD, 
                                'FAB' lib_origine
                            from ARTICLES a with (nolock)
                            right join REMGAMM rg with (nolock) on rg.CODEFABR = a.CODEFAB and rg.CODEGAMME = a.CODEGAM
                            where a.TYPEGAM = 0x54 and a.CODE_ART = DA_LIGNE.CODE_ART and PRIX_COUT != 0
                        union
                            select 
                                (select TOP 1 CODE from FOURNISS with (nolock) where FOURPRIN = arg.CODE_GF order by FOURPREF DESC) fournisseur, 
                                pu_remise, 
                                pu, 
                                arg.REMISE, 
                                2 origine , 
                                '' ACD, 
                                'AGF' lib_origine
                            from ARTGF arg with (nolock)
                            cross apply (
                                select top 1 (PU)*(1-(arg.REMISE/100)) pu_remise, (PU) pu from PXAGF fp with (nolock)
                                where fp.CODE_GF = arg.CODE_GF and fp.CODE_ART = arg.CODE_ART and fp.QTE <= (DA_LIGNE.QTE_DEM-DA_LIGNE.DEJA_COMM) and CODE_ART = DA_LIGNE.CODE_ART and PU != 0
                                order by QTE desc
                            ) frsPx 
                            where (
                                    arg.DTEVALMAX='00000000' 
                                    or arg.DTEVALMAX='' 
                                    or arg.DTEVALMAX > :today_12
                                ) and arg.DTEVALMIN < :today_13   
                        ) testPx
                        left join FOURNISS f with (nolock) on f.CODE = fournisseur
                        where (
                                f.DTEVALMAX='00000000' 
                                or f.DTEVALMAX='' 
                                or f.DTEVALMAX > :today_14
                            ) and f.DTEVALMIN < :today_15
                        and (origine = 0 or pu != 0) 
                        order by pu_remise asc
                    ) bestPrix
                left join FOURNISS frsAO with (nolock) on frsAO.CODE = AO_RETPARLIGNE.CODE_FOU
                where  DA_LIGNE.CHIF_EXEC='E' and DA_LIGNE.VALIDEE=0x54 and DA_LIGNE.TRAITEE=0x46 and REFUSEE=0x46 /*and DA_LIGNE.RECEP_AUTO = 0x46*/
                and (DA_LIGNE.CODE_ART='' or not exists (select null from DCC_LIEN with(nolock) where TYPEOBJ='DA_ENTET' and cast(COD_ENR as int)=DA_LIGNE.NO_DA))
                and (DA_LIGNE.ACHATDIR = 0x46 or (DA_LIGNE.ACHATDIR = 0x54 and DA_ENTET.SIGNEE = 0x54))
                and DA_ENTET.CODE_STE=:societe and (AO_ENTET.VALCHXFOU != 0  or AO_ENTET.EVE_CHXFOU is null)
                and TYPDOC.NIV_CONF <= :nivConf
                ".Roles::getClauseSocieteOuvert('soc')."
                ".($param['NOTCREATCDEDANONSIG'] == 'O'? " and DA_ENTET.SIGNEE = 0x54  ":'')." $where 
                order by CODE_FOU";
        

        $hastutil = new Hastutil($this->pdo);
        $infoUser = $hastutil->find($_SESSION["Session_Login"]);
        $arrayReq[':nivConf'] = $infoUser['NIV_CONF'];
        $arrayReq[':societe']=$post['societe'];
        $arrayReq[':langue'] = $_SESSION['Session_Langue'];
        $now = date('Ymd');
        $arrayReq += [ // TODO : simplify
            'today_0' => $now,
            'today_1' => $now,
            'today_2' => $now,
            'today_3' => $now,
            'today_4' => $now,
            'today_5' => $now,
            'today_6' => $now,
            'today_7' => $now,
            'today_8' => $now,
            'today_9' => $now,
            'today_10' => $now,
            'today_11' => $now,
            'today_12' => $now,
            'today_13' => $now,
            'today_14' => $now,
            'today_15' => $now,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * retourne le montant total des commandes a traiter par frs
     * @param string $fournisseur
     * @return mixed
     */
    public function totalDAFour($fournisseur)
    {
        $req = "select SUM(
                    CASE MNT_LIG
                    	when 0 then BUDGET
                    	else MNT_LIG
                    	end
                    ) TOTAL_LIG
                    from DA_LIGNE with (nolock)
                    left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
                    left join MEMOS with (nolock) on MEMOS.NODEMEMO = DA_LIGNE.NM_DCOMP
                    where DA_LIGNE.NO_DA = DA_ENTET.NO_DA and DA_ENTET.CODE_STE='DEMO' and DA_LIGNE.TRAITEE=0x46 and REFUSEE=0x46 and DA_LIGNE.VALIDEE=0x54 and DA_LIGNE.CHIF_EXEC='E'
                    and CODE_FOU='$fournisseur'";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
     */
    public function getInfoLigneDa(string $noDa, string $noPo)
    {
        $param = [
            'SISECTOR' => Sql::paravrac($this->pdo, 'PARAMSTE', 'SISECTOR'),
            'CDCSECTO' => Sql::paravrac($this->pdo, 'PARAMSTE', 'CDCSECTO'),
            'SCASECTO' => Sql::paravrac($this->pdo, 'PARAMSTE', 'SCASECTO'),
        ];

        $req = "select dl.SECTIMP, dl.ID_CG, dl.LIBGES, dl.CODGES, dl.CODE_COMP, dl.SSCPTEANA, msg.LIBELLE MESSAGE, dl.ACHETEUR, mdl.LIBELLE LIB_MDS, 
                titre.CONTMEMO TITRE, comm.CONTMEMO COMMENTAIRE, ach.NOM lib_ACHETEUR, de.ZONELIB, de.MOD_TRS, de.DAENT_CENTRE_EMETTEUR,
                si.LIBELLE LIB_SI, cdc.LIBELLE LIB_CDC, sca.LIBELLE LIB_SCA, dem.NOM DEMANDEUR, ce.RAIS_SOC LIB_CE,  dem.CODE CODE_DEM
                from DA_LIGNE dl with (nolock)
                left join DA_ENTET de with (nolock) on de.NO_DA = dl.NO_DA
                left join MESSSTAN msg with (nolock) on msg.CODE_MSG = de.CODE_MESS
                left join MEMOS titre with (nolock) on titre.NODEMEMO = de.NM_TITRE
                left join MEMOS comm with (nolock) on comm.NODEMEMO = de.NM_COMMEN
                left join SECTIMPU si with (nolock) on si.CODE_SI = dl.SECTIMP and si.CODE_STE = ".($param['SISECTOR'] == 'O'? "dl.CODE_STE": "''")."
                left join CODECOMP cdc with (nolock) on cdc.CODE_CDC = dl.CODE_COMP and cdc.CODE_STE = ".($param['CDCSECTO'] == 'O'? "dl.CODE_STE": "''")."
                left join SSCPTANA sca with (nolock) on sca.CODE_SCA = dl.SSCPTEANA and sca.CODE_STE = ".($param['SCASECTO'] == 'O'? "dl.CODE_STE": "''")."
                left join HASTUTIL ach with (nolock) on ach.CODE = dl.ACHETEUR
                left join HASTUTIL dem with (nolock) on dem.CODE = de.NO_DEM
                left join CENTRE ce with (nolock) on ce.CODE_CTR = de.DAENT_CENTRE_EMETTEUR and ce.CODE_STE = dl.CODE_STE
                left join TRAD_MDL mdl with (nolock) on mdl.CODE_MDL = de.MOD_TRS and mdl.COD_LNG =:langue
                where dl.NO_DA = :noDa and NO_PO = :noPo";

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

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @param array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getOptimalListPlanning(string $clauseWhere, $arrayReq)
    {
        if (empty($clauseWhere)) {
            return [];
        }
        $req = "SELECT * from DA_PLANN da with(nolock) where ".$clauseWhere;

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


    /**
     * @param array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getOptimalListClause(string $clauseWhere, $arrayReq)
    {
        if (empty($clauseWhere)) {
            return [];
        }
        $req = "SELECT * from DA_CLAUS da with(nolock) 
                left join MEMOS with(nolock) on MEMOS.NODEMEMO = da.NM_PARAG
                where CONTMEMO != '' and (".$clauseWhere.")";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getOptimalListPJ(string $clauseWhere, $arrayReq)
    {
        if (empty($clauseWhere)) {
            return [];
        }
        $req = "SELECT * from DA_PIJO da with(nolock) 
                where ".$clauseWhere;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array<mixed> $arrayReq
     * @return array<mixed>
     */
    public function getOptimalListDoc(string $clauseWhere, $arrayReq)
    {
        $req = "select * from DOC_ATT da with(nolock) 
                where da.NO_DA = 'DA_ENTET' and (".$clauseWhere.")";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array<mixed> $listeDA
     * @return array<mixed>
     */
    public function getDifferentDemandeur($listeDA)
    {
        $liste = implode("','", $listeDA);
        $req = "select distinct NO_DEM from DA_ENTET with (nolock) where NO_DA in ('$liste')";

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

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

    /**
     * @param array<mixed> $listeFrs
     * @return array<mixed>
     */
    public function getDifferentFrs($listeFrs)
    {
        $liste = implode("','", $listeFrs);
        $req = "select * from FOURNISS with (nolock) where CODE in ('$liste')";

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

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

    /**
     * @return array<mixed>
     */
    public function getBestPrixArtFrs(string $codeFrs, string $codeArt, string $qte, string $societe, string $centreLivr, string $centreEmett, bool $all = false)
    {
        $req = "SELECT ".($all? '':"top 1")." fournisseur,pu_remise,pu, testPx.REMISE remise, f.FOURPREF, origine, lib_origine, ACD, f.RS, a.LIBELLE from (
                    SELECT 
                        CASE 
                            WHEN acd.TYPE_FG='G' THEN (select TOP 1 CODE from FOURNISS with (nolock) where FOURPRIN = acd.CODE_FOUR order by FOURPREF DESC)
                            ELSE acd.CODE_FOUR
                        END fournisseur,
                        case when isnull(prix, 0)=0 then ac.PU else prix end pu_remise,
                        case when isnull(prix, 0)=0 then ac.PU else prix end pu,
                        0 REMISE, 
                        1 origine, 
                        ac.CODE_ACD ACD, 
                        'ACD' lib_origine
                    from ACART ac with (nolock)
                    left join ACCCADRE acd with (nolock) on acd.CODE_ACD = ac.CODE_ACD
                    left outer join ACCTR e with (nolock) on e.CODE_ACD=acd.CODE_ACD
                    left outer join ACCTRLIV l with (nolock) on l.CODE_ACD=acd.CODE_ACD
                    left outer join MARCHSOC ms with (nolock) on ms.CODEMARCH=acd.MARCHE and ms.CODESOC=:societe
                    left outer join MARCHUSR mu with (nolock) on mu.CODE_MAR=acd.MARCHE
                    outer apply (
                            select TOP 1 
                                case TYPTAR 
                                    when 'P' then (acp.PU_TX) 
                                    when 'T' then (ac.PU)*(1-(acp.TAUX/100))
                                end  prix 
                            from ACARTDET acp with (nolock)
                            where acp.CODE_ACD = ac.CODE_ACD and acp.QTEMIN <= :qte1 and CODE_ART = :article1 and PU_TX != 0
                        ) acdPx 
                    WHERE (ac.DFINVAL='00000000' or ac.DFINVAL='' or ac.DFINVAL > '".date('Ymd')."') and ac.DDEBVAL < '".date('Ymd')."'
                        and (isnull(e.CODE_CTR,'')='' or e.CODE_CTR=:centreEmett)
                        and (isnull(l.CODE_CTR,'')='' or l.CODE_CTR=:centreLiv)
                        AND ac.CODE_ART = :article2
                        -- and (acd.MARCHE='' or ms.CODESOC<>'')
                union
                    select 
                        FOU fournisseur, 
                        pu_remise, 
                        pu, 
                        /*(CASE WHEN arf.REMISE>0 THEN arf.REMISE else f.REMISE end) REMISE,*/
                        arf.REMISE,
                        2 origine , 
                        '' ACD, 
                        'AFO' lib_origine
                    from ARTFOURN arf with (nolock)
                    left join FOURNISS f with (nolock) on f.CODE = arf.FOU
                    cross apply (
                        select top 1 (PU)*(1-((CASE WHEN arf.REMISE>0 THEN arf.REMISE else f.REMISE end)/100)) pu_remise, (PU) pu from PXAFO fp with (nolock)
                        where fp.CODE_FOU = FOU and fp.CODE_ART = arf.CODE_ART and fp.QTE <= :qte2 and CODE_ART = :article3 and PU != 0
                        order by QTE desc
                    ) frsPx 
                    where (arf.DTEVALMAX='00000000' or arf.DTEVALMAX='' or arf.DTEVALMAX > '".date('Ymd')."') and arf.DTEVALMIN < '".date('Ymd')."'
                union 
                    select 
                        rg.CODEFOURN fournisseur,
                        (a.PRIX_COUT)*(1-(rg.REMISE/100)) pu_remise,
                        (a.PRIX_COUT) pu,
                        rg.REMISE ,
                        2 origine ,
                        '' ACD, 
                        'FAB' lib_origine
                    from ARTICLES a with (nolock)
                    right join REMGAMM rg with (nolock) on rg.CODEFABR = a.CODEFAB and rg.CODEGAMME = a.CODEGAM
                    where a.TYPEGAM = 0x54 and a.CODE_ART = :article4 and PRIX_COUT != 0
                union
                    select 
                        (select TOP 1 CODE from FOURNISS with (nolock) where FOURPRIN = arg.CODE_GF order by FOURPREF DESC) fournisseur, 
                        pu_remise, 
                        pu, 
                        arg.REMISE, 
                        2 origine , 
                        '' ACD, 
                        'AGF' lib_origine
                    from ARTGF arg with (nolock)
                    cross apply (
                        select top 1 (PU)*(1-(arg.REMISE/100)) pu_remise, (PU) pu 
                        from PXAGF fp with (nolock)
                        where fp.CODE_GF = arg.CODE_GF and fp.CODE_ART = arg.CODE_ART and fp.QTE <= :qte3 and CODE_ART = :article5 and PU != 0
                        order by QTE desc
                    ) frsPx 
                    where (arg.DTEVALMAX='00000000' or arg.DTEVALMAX='' or arg.DTEVALMAX > '".date('Ymd')."') and arg.DTEVALMIN < '".date('Ymd')."'    
                ) testPx
                left join FOURNISS f with (nolock) on f.CODE = fournisseur
                left join ACCCADRE a with (nolock) on a.CODE_ACD = ACD
                where (f.DTEVALMAX='00000000' or f.DTEVALMAX='' or f.DTEVALMAX > '".date('Ymd')."') and f.DTEVALMIN < '".date('Ymd')."'
                and (origine = 0 or pu != 0) ".($codeFrs? " and fournisseur = :fournisseur ": "")."
                order by origine asc, FOURPREF desc, pu_remise asc";

        $arrayReq = [
            ':societe' => $societe,
            ':qte1' => $qte,
            ':article1' => $codeArt,
            ':centreEmett' => $centreEmett,
            ':centreLiv' => $centreLivr,
            ':article2' => $codeArt,
            ':qte2' => $qte,
            ':article3' => $codeArt,
            ':article4' => $codeArt,
            ':qte3' => $qte,
            ':article5' => $codeArt,
        ];

        if ($codeFrs) {
            $arrayReq[':fournisseur'] = $codeFrs;
        }

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        if ($all) {
            return $requete->fetchAll($this->pdo::FETCH_ASSOC);
        } else {
            return $requete->fetch($this->pdo::FETCH_ASSOC);
        }
    }

    /**
     * @param array<mixed> $listeTaux
     * @return array<mixed>
     */
    public function getListeTaux(string $deviseProjet, $listeTaux)
    {
        $implode = implode("','", $listeTaux);
        $req = "SELECT * FROM TXDEVPIVOT with (nolock) WHERE DEV_A_CONV = :devProjet and DEV_PIVOT in ('$implode')";

        $requete = $this->pdo->prepare($req);
        $requete->execute([':devProjet' => $deviseProjet]);

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