<?php

namespace Modules\Suivi\Repository;

use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Trad\ITrad;
use Modules\GlobalApplication\Repository\LicenceOxalys;
use Modules\Suivi\MenuContextuel\SuiviDaContextuel;
use Skeleton\Router;

class SuiviDaRepo extends SuiviRepo
{
    public function __construct(\PDO $pdo, Router $router, ITrad $trad)
    {
        parent::__construct($pdo, $router, $trad);
                
        $this->droit = Roles::droitsUtilMulti($this->pdo, [
            'HA_DA_TTDM',
            'HA_DA_TTCT',
            'HA_DA_TTCE',
            'OX_SUITTUO',
        ]);
    }


    /**
     * Summary of jointureExport
     * @param array<mixed> $jointure
     * @return void
     */
    public static function jointureExport(\PDO $pdo, &$jointure)
    {
        $parametrage = [
            'sisecto' => \Helpers\Outil\Roles::droit($pdo, 'SISECTOR', 'PARAMSTE'),
            'scaSecto' => \Helpers\Outil\Roles::droit($pdo, 'SCASECTO', 'PARAMSTE'),
            'cdcSecto' => \Helpers\Outil\Roles::droit($pdo, 'CDCSECTO', 'PARAMSTE'),
        ];
        $jointure[ ]= "LEFT JOIN SECTIMPU oxaExportSECTIMP with (nolock) on oxaExportSECTIMP.CODE_SI = DA_ENTET.SECTIMP and oxaExportSECTIMP.CODE_STE = ".($parametrage['sisecto']? "DA_ENTET.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN CODECOMP oxaExportCODECOMP with (nolock) on oxaExportCODECOMP.CODE_CDC = DA_ENTET.CODECOMP  and oxaExportCODECOMP.CODE_STE = ".($parametrage['cdcSecto']? "DA_ENTET.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN SSCPTANA oxaExportSSCPTEANA with (nolock) on oxaExportSSCPTEANA.CODE_SCA = DA_ENTET.SSCPTEANA  and oxaExportSSCPTEANA.CODE_STE = ".($parametrage['scaSecto']? "DA_ENTET.CODE_STE":"''");

        $jointure[ ]= "LEFT JOIN FOURNISS oxaExportFOU with (nolock) on oxaExportFOU.CODE = DA_ENTET.FOU";
        $jointure[ ]= "LEFT JOIN SOCIETE oxaExportCODE_STE with (nolock) on oxaExportCODE_STE.CODE_STE = DA_ENTET.CODE_STE";
        $jointure[ ]= "LEFT JOIN HASTUTIL oxaExportNO_DEM with (nolock) on oxaExportNO_DEM.CODE = DA_ENTET.NO_DEM";
        $jointure[ ]= "LEFT JOIN ACCCADRE oxaExportACD with (nolock) on oxaExportACD.CODE_ACD = DA_ENTET.ACD";
        $jointure[ ]= "LEFT JOIN CENTRE oxaExportDAENT_CENTRE_EMETTEUR with (nolock) on oxaExportDAENT_CENTRE_EMETTEUR.CODE_CTR = DA_ENTET.DAENT_CENTRE_EMETTEUR";
        $jointure[ ]= "LEFT JOIN CENTRE oxaExportCTR_LIV with (nolock) on oxaExportCTR_LIV.CODE_CTR = DA_ENTET.CTR_LIV";
        $jointure[ ]= "LEFT JOIN CONDPAIE oxaExportCOND_PAIE with (nolock) on oxaExportCOND_PAIE.CODE_CDP = DA_ENTET.COND_PAIE";
        $jointure[ ]= "LEFT JOIN MODEPAIE oxaExportMODE_PAIE with (nolock) on oxaExportMODE_PAIE.CODE_MDP = DA_ENTET.MODE_PAIE";
        $jointure[ ]= "LEFT JOIN MODELIVR oxaExportMODE_LIV with (nolock) on oxaExportMODE_LIV.CODE_MDL = DA_ENTET.MODE_LIV";

        


        $jointure[ ]= "LEFT JOIN SSFAMILL oxaExport_LIGNE_SSFAM with (nolock) on oxaExport_LIGNE_SSFAM.CODE_SFA = DA_LIGNE.SSFAM";
        $jointure[ ]= "LEFT JOIN CENTRE oxaExportCTRLIVR with (nolock) on oxaExportCTRLIVR.CODE_CTR = DA_LIGNE.CTRLIVR";
        $jointure[ ]= "LEFT JOIN HASTUTIL oxaExport_LIGNE_ACHETEUR with (nolock) on oxaExport_LIGNE_ACHETEUR.CODE = DA_LIGNE.ACHETEUR";
        $jointure[ ]= "LEFT JOIN FOURNISS oxaExport_LIGNE_CODE_FOU with (nolock) on oxaExport_LIGNE_CODE_FOU.CODE = DA_LIGNE.CODE_FOU";
        $jointure[ ]= "LEFT JOIN SSITESTO oxaExport_LIGNE_SITE_STK with (nolock) on oxaExport_LIGNE_SITE_STK.SITE = DA_LIGNE.SITE_STK";
        $jointure[ ]= "LEFT JOIN SOCIETE oxaExport_LIGNE_CODE_STE with (nolock) on oxaExport_LIGNE_CODE_STE.CODE_STE = DA_LIGNE.CODE_STE";
        $jointure[ ]= "LEFT JOIN ACCCADRE oxaExport_LIGNE_ACD with (nolock) on oxaExport_LIGNE_ACD.CODE_ACD = DA_LIGNE.ACD";
        $jointure[ ]= "LEFT JOIN FABRICAN oxaExport_LIGNE_COD_FAB with (nolock) on oxaExport_LIGNE_COD_FAB.CODE = DA_LIGNE.COD_FAB";
        $jointure[ ]= "LEFT JOIN GAMMES oxaExport_LIGNE_COD_GAM with (nolock) on oxaExport_LIGNE_COD_GAM.CODEGAMME = DA_LIGNE.COD_GAM and oxaExport_LIGNE_COD_GAM.CODEFABR = DA_LIGNE.COD_FAB";
        $jointure[ ]= "LEFT JOIN SECTIMPU oxaExport_LIGNE_SECTIMP with (nolock) on oxaExport_LIGNE_SECTIMP.CODE_SI = DA_LIGNE.SECTIMP and oxaExport_LIGNE_SECTIMP.CODE_STE = ".($parametrage['sisecto']? "DA_ENTET.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN CODECOMP oxaExport_LIGNE_CODE_COMP with (nolock) on oxaExport_LIGNE_CODE_COMP.CODE_CDC = DA_LIGNE.CODE_COMP and oxaExport_LIGNE_CODE_COMP.CODE_STE = ".($parametrage['cdcSecto']? "DA_ENTET.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN SSCPTANA oxaExport_LIGNE_SSCPTEANA with (nolock) on oxaExport_LIGNE_SSCPTEANA.CODE_SCA = DA_LIGNE.SSCPTEANA and oxaExport_LIGNE_SSCPTEANA.CODE_STE = ".($parametrage['scaSecto']? "DA_ENTET.CODE_STE":"''");
    }

    /**
     * @param array<mixed> $rupture
     * @param array<mixed> $listField
     * @return \stdClass{requete:string,with:string}
     */
    private function getMainRequest($rupture = [], array $listField = null, bool $delete = false)
    {
        if (!empty($listField)) {
            $sel = $this->processSpecialField($listField);
            $this->jointureExport($this->pdo, $sel->join);
            $select = 'DISTINCT '. $this->stringifySelect($sel->select);
        } elseif ($rupture) {
            $affTTC = Sql::paravrac($this->pdo, 'OXAPROC', 'AFFTTC') == 'O';

            $select = "distinct(DA_ENTET.NO_DA), ".($affTTC? "TOTAL_DA_TTC": "TOTAL_DA")." RUPTURE";
            foreach ($rupture as $k => $r) {
                if ($k != '') {
                    $select .= ",".$k;
                }
            }
        } else {
            $select = " DISTINCT (DA_ENTET.NO_DA), 
                        DA_ENTET.CODE_STE, 
                        DA_ENTET.FOU, 
                        DA_ENTET.NO_DEM, 
                        DA_ENTET.TYP_DOC, 
                        DA_ENTET.CHIF_EXEC,
                        DA_ENTET.SECTIMP, 
                        DA_ENTET.DTE_LIV, 
                        DA_ENTET.TOTAL_DA_TTC, 
                        DA_ENTET.TOTAL_DA, 
                        DA_ENTET.DEVISE,
                        DA_ENTET.VALIDITE, 
                        DA_ENTET.TRAITEE, 
                        DA_ENTET.SIGNEE,
                        DA_ENTET.REFUSEE, 
                        DA_ENTET.CODECOMP,
                        DA_ENTET.SSCPTEANA,
                        /* Formatage du numéro de document */
                        RIGHT(CONCAT('000000000', DA_ENTET.NO_DA), 9) AS DOC_NO_DA,
                    
                        /* Nombre de dates de livraison */
                        COALESCE(DA_STATS.planning_count, 0) AS planning,
                    
                        /* Nombre de lignes */
                        COALESCE(DA_STATS.countLigne, 0) AS countLigne,
                    
                        SOCIETE.RAIS_SOC, 
                        DA_ENTET.FM_CODE,
                        FOURNISS.RS,
                        demandeurDA.NOM,
                        SECTIMPU.LIBELLE AS LIB_SI,
                    
                        /* Menu contextuel */
                        COALESCE(DA_STATS.NB_LCDE, 0) AS CDE_EXIST,
                    
                        DA_ENTET.DTE_VAL,
                    
                        /* LIB FOR RUPTURE */
                        FOURNISS.RS AS lib_FOU, 
                        demandeurDA.NOM AS lib_ACH, 
                        SOCIETE.RAIS_SOC AS lib_CODE_STE,
                        SECTIMPU.LIBELLE AS lib_SECTIMP, 
                        CODECOMP.LIBELLE AS lib_CODECOMP, 
                        SSCPTANA.LIBELLE AS lib_SSCPTEANA,
                    
                        /* Nombre de lignes avec SECTIMP différent */
                        COALESCE(DA_STATS.SI_DIFF, 0) AS SI_DIFF,
                    
                        /* Nombre de documents associés */
                        COALESCE(DA_STATS.NB_DOC, 0) AS NB_DOC,
                    
                        /* Nombre de pièces jointes */
                        COALESCE(DA_STATS.NB_PIJO, 0) AS NB_PIJO,
                    
                        /* Signature en cours */
                        case when DA_ENTET.VALIDITE=0x54 and DA_ENTET.SIGNEE=0x46 and DA_ENTET.REFUSEE=0x46 then 1 else 0 end AS EN_SIGNATURE,
                    
                        /* DA issue de e-procurement */
                        COALESCE(DA_STATS.isDaCdeEproc, 0) AS isDaCdeEproc";
        }

        $specifique = new \Helpers\Additional\Specifique\Specifique();
        $namespaceSpecifique = $specifique->specifiqueFunction('\\Modules\\Suivi\\Repository\\SpecSuviDa');

        $jointureSpecifique = '';
        if (class_exists($namespaceSpecifique)) {
            $jointureSpecifique = \Helpers\Additional\Specifique\Specifique::specifique('\\Modules\\Suivi\\Repository\\SpecSuviDa', 'getJointureSepcifique', $this->pdo);
        }

        $with = "   WITH DA_STATS AS (
                        SELECT
                            DE.NO_DA,
                            COUNT(DISTINCT DP.DTE_LIV) AS planning_count,
                            COUNT(DISTINCT DL.NO_PO) AS countLigne,
                            COUNT(CASE WHEN DL.SECTIMP <> DE.SECTIMP THEN 1 END) AS SI_DIFF,
                            COUNT(DOC.COD_ENR) AS NB_DOC,
                            COUNT(DPIJ.NO_ORDRE) AS NB_PIJO,
                            COUNT(cal.NO_CDE) AS NB_LCDE,
                            CASE WHEN MAX(CASE WHEN DL.ACHATDIR = 'T' THEN 1 ELSE 0 END) = 1 THEN 1 ELSE 0 END AS isDaCdeEproc
                        FROM DA_ENTET DE
                        LEFT JOIN DA_LIGNE DL with (nolock) ON DL.NO_DA = DE.NO_DA
                        LEFT JOIN DA_PLANN DP with (nolock) ON DP.NO_DA = DE.NO_DA
                        LEFT JOIN DOC_ATT DOC with (nolock) ON DOC.COD_ENR = RIGHT(CONCAT('000000000', DE.NO_DA), 9) AND DOC.NO_DA = 'DA_ENTET'
                        LEFT JOIN DA_PIJO DPIJ with (nolock) ON DPIJ.NO_DA = DE.NO_DA
                        outer apply (select top 1 * from CA_LIGNE with (nolock) where CA_LIGNE.NO_DA = DL.NO_DA) cal
                        GROUP BY DE.NO_DA, DE.SECTIMP
                )";

        $req = "select 
                    ".$select."
                from DA_ENTET WITH (nolock)
                LEFT JOIN DA_STATS ON DA_STATS.NO_DA = DA_ENTET.NO_DA
                left join DA_LIGNE with (nolock) on DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                left join CA_LIGNE with (nolock) on CA_LIGNE.NO_DA = DA_ENTET.NO_DA and CA_LIGNE.NO_PO_DA = DA_LIGNE.NO_DA
                left join CA_ENTET with (nolock) on CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                left join FOUDACDE with (nolock) on FOUDACDE.NO_DA = DA_ENTET.NO_DA
                left join SOCIETE with (nolock) on SOCIETE.CODE_STE = DA_ENTET.CODE_STE
                left join FOURNISS with (nolock) on FOURNISS.CODE = DA_ENTET.FOU
                left join HASTUTIL demandeurDA with (nolock) on demandeurDA.CODE = DA_ENTET.NO_DEM
                left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = DA_ENTET.SECTIMP and SECTIMPU.CODE_STE =  ".($this->parametrage['sisecto']? "DA_ENTET.CODE_STE":"''")." 
                left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = DA_ENTET.CODECOMP and CODECOMP.CODE_STE =  ".($this->parametrage['cdcSecto']? "DA_ENTET.CODE_STE":"''")." 
                left join SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = DA_ENTET.SSCPTEANA and SSCPTANA.CODE_STE =  ".($this->parametrage['scaSecto']? "DA_ENTET.CODE_STE":"''")." 
                left join MEMOS DCompArtCDE with (nolock) on CA_LIGNE.NM_DCOMP = DCompArtCDE.NODEMEMO
                left join MEMOS DCompArtDA with (nolock) on DA_LIGNE.NM_DCOMP = DCompArtDA.NODEMEMO
                left join TYPDOC with (nolock) on TYPDOC.TYPE = DA_ENTET.TYP_DOC ";

        if (!$delete && (roles::droit($this->pdo, 'SUIVI_HIRARCHIQUE_UO', 'OXAPROC') && !$this->droit['OX_SUITTUO'])) {
            if ($this->parametrage['SOURCE_SI'] == 'UO') {
                $req .= "outer apply
                            (   
                                SELECT COUNT(*) as NBSIUO FROM DA_LIGNE with (nolock)
                                LEFT OUTER JOIN LSTSIPARUO with (nolock) ON LSTSIPARUO.CODE_SI = DA_LIGNE.SECTIMP  and LSTSIPARUO.CODE_STESI = DA_LIGNE.CODE_STE
                                WHERE DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                                AND LSTSIPARUO.CODE_UO IN (select CodeUO from LstUO)  AND CODE_STESI = DA_LIGNE.CODE_STE
                            ) lstUOSI ";
            }
        }
                
                $req .=  implode("\r\n", $sel->join ?? [])
                . $jointureSpecifique
                . " where 1=1 ".Roles::getClauseSocieteOuvert('SOCIETE');
                
        return (object)[
            'requete' => $req,
            'with'  => $with,
        ];
    }
    
    /**
     * @param array<mixed> $post
     * @param array<mixed> $rupture
     * @param array<mixed> $arrayReq
     * @return \stdClass{where:string,with:string}
     */
    private function getFilter($post, $rupture, array &$arrayReq) :object
    {
        //$jointureCSDON89 = false; // Dossier 26636 / spécifique NGE a faire sur tout les suivi

        /** UO */
        $whereUO = '';
        $clauseUO = '';
        /** filtre et/ou périmètre */
        if (roles::droit($this->pdo, 'SUIVI_HIRARCHIQUE_UO', 'OXAPROC') && (($post['UO']??'') || !$this->droit['OX_SUITTUO'])) {
            if ($this->parametrage['SUIVI_EXCLURE_LIENDOC_UO_HASTUTIL'] != 'O') {
                $specifique = new \Helpers\Additional\Specifique\Specifique();
                $namespaceSpecifique = $specifique->specifiqueFunction('\\Modules\\Suivi\\Repository\\SpecSuviDa');
        
                $clauseUOSpecifique = '';
                if (class_exists($namespaceSpecifique)) {
                    $clauseUOSpecifique = \Helpers\Additional\Specifique\Specifique::specifique('\\Modules\\Suivi\\Repository\\SpecSuviDa', 'getClauseUOSpecifique');
                }

                $whereUO .= " (
                                DA_ENTET.UO_DEM in (select CodeUO from LstUO) 
                                $clauseUOSpecifique
                            )";
            }

            if ($this->parametrage['SOURCE_SI'] == 'UO') {
                if ($whereUO) {
                    $whereUO .= " or ";
                }

                $whereUO .= " isnull(NBSIUO,0)>0 ";
            }
                
            if ($whereUO) {
                $whereUO = " AND ($whereUO)";
                
                $clauseUO = $this->getClauseUO($_SESSION["Session_Login"], $post['UO']??'');
            }
        }

        /** Périmètre */
        $clausePerimetre = '';
        $clausePerimetre .= Roles::getClausePerimetreSociete('DA_ENTET.CODE_STE', $arrayReq);
        
        // deux posibilite :
        // le user ne peut voir que ses DA et celles dont il a fait des evenements case a coché SEL_MYDA
        // si le user ne peut voir que ses DA en tenant compte du perimetre UO le droit HA_DA_TTDM sans le droit OX_SUITTUO
        // Avoir le droit HA_DA_TTDM et OX_SUITTUO reviens a avoir la case a coché SEL_MYDA
        if (Sql::boolSql($this->user['SEL_MYDA'])) {
            $clausePerimetre .= " and (
                DA_ENTET.NO_DEM=:userMyDa1 
                or exists(select null from DA_EVENT with(nolock) WHERE DA_EVENT.NO_DA = DA_ENTET.NO_DA AND DA_EVENT.SIGNAT = :userMyDa2 ) 
                or exists(select * from SIG_DA with(nolock) where DA_ENTET.NO_DA = SIG_DA.NO_DA and   SIG_DA.SIGNAT = :userMyDa3 ) 
            )";
        
            $arrayReq[':userMyDa1'] = $_SESSION["Session_Login"];
            $arrayReq[':userMyDa2'] = $_SESSION["Session_Login"];
            $arrayReq[':userMyDa3'] = $_SESSION["Session_Login"];
        }

        /** Centre */
        if (!$this->droit['HA_DA_TTCT']) {
            $clausePerimetre .= " and DA_ENTET.CTR_LIV in (
                select CTRLIV from HASTUTIL with(nolock) where CODE = :userCtrL1
                union
                select CTRLIVR from SOC_USR with(nolock) where COD_USR = :userCtrL2        
            )";

            $arrayReq[':userCtrL1'] = $_SESSION["Session_Login"];
            $arrayReq[':userCtrL2'] = $_SESSION["Session_Login"];
        }

        if (!$this->droit['HA_DA_TTCE']) {
            $clausePerimetre .= " and (DA_ENTET.DAENT_CENTRE_EMETTEUR = '' or DA_ENTET.DAENT_CENTRE_EMETTEUR in (
                select CTR_ADR from HASTUTIL with(nolock) where CODE = :userCtrE1
                union
                select CTREMET CTR_ADR from SOC_USR with(nolock) where COD_USR = :userCtrE2
            ))";

            $arrayReq[':userCtrE1'] = $_SESSION["Session_Login"];
            $arrayReq[':userCtrE2'] = $_SESSION["Session_Login"];
        }

        /** acheteur */
        $perimetreAcheteur = '';
        if ((!$this->droit['HA_DA_TTDM'])) {
            $perimetreAcheteur .= " (DA_ENTET.NO_DEM = :userAch1)";

            $arrayReq[':userAch1'] = $_SESSION["Session_Login"];
        }

        /** CONFIDENTIALIE */
        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $clausePerimetre .= ' and TYPDOC.NIV_CONF<= :nivConf ';

            $arrayReq[':nivConf'] = intval($this->user['NIV_CONF']??'0');
        }


        /* Clause EVENEMENT ou SIGNATAIRE */
        $perimetreEvent = '';
        if (!$this->droit['OX_SUITTUO']) {
            $perimetreEvent = "
                exists(select null from DA_EVENT with (nolock) WHERE DA_ENTET.NO_DA = DA_EVENT.NO_DA AND   (DA_EVENT.UTIL = :userEvent1 or DA_EVENT.SIGNAT = :userEvent2)) 
                or
                exists(select null from SIG_DA with (nolock) where DA_ENTET.NO_DA = SIG_DA.NO_DA AND   SIG_DA.SIGNAT  = :userEvent3)
            ";
        }
        
        if ($perimetreAcheteur && $perimetreEvent) {
            $clausePerimetre .= ' and ( '.$perimetreAcheteur.' or '.$perimetreEvent.' )';

            $arrayReq[':userEvent1'] = $_SESSION["Session_Login"];
            $arrayReq[':userEvent2'] = $_SESSION["Session_Login"];
            $arrayReq[':userEvent3'] = $_SESSION["Session_Login"];
        } elseif ($perimetreAcheteur) {
            $clausePerimetre .= 'and '.$perimetreAcheteur;
        }

                
        /** Filter */
        $requeteWhere = '';

        $post['filter_egal_DA_ENTET.CODE_STE'] = $post['filter_egal_CODE_STE']??'';
        unset($post['filter_egal_CODE_STE']);

        $post['filter_DA_ENTET.NO_DA'] = $post['filter_NO_DA']??'';
        unset($post['filter_NO_DA']);

        $post['filter_DA_ENTET.SECTIMP'] = $post['filter_SECTIMP']??'';
        unset($post['filter_SECTIMP']);

        $post['filter_DA_ENTET.CHIF_EXEC'] = $post['filter_CHIF_EXEC']??'';
        unset($post['filter_CHIF_EXEC']);

        $post['filter_DA_ENTET.DTE_LIV'] = $post['filter_DTE_LIV']??'';
        unset($post['filter_DTE_LIV']);


        if (isset($post['filter_egal_NO_DEM']) && $post['filter_egal_NO_DEM']) {
            $requeteWhere .= " and (DA_ENTET.NO_DEM = :filterUser1 or DA_LIGNE.ACHETEUR = :filterUser2)";

            $arrayReq[':filterUser1'] = $post['filter_egal_NO_DEM'];
            $arrayReq[':filterUser2'] = $post['filter_egal_NO_DEM'];
            unset($post['filter_egal_NO_DEM']);
        }


        $Statut = $post['filter_ETAT_DA']??'';
        unset($post['filter_ETAT_DA']);
        if ($Statut) {
            switch ($Statut) {
                case 'EN_SIGNATURE':
                    $requeteWhere .= ' and ( DA_ENTET.DTE_VAL != \'00000000\' and DA_ENTET.SIGNEE = 0x46 and DA_ENTET.TRAITEE = 0x46 and DA_ENTET.REFUSEE = 0x46)'; // Fin partie requete
                    break;
                case 'A_TRAITER':
                    $requeteWhere .= ' and ( DA_ENTET.SIGNEE = 0x54 and DA_ENTET.TRAITEE = 0x46 )'; // Fin partie requete
                    break;
                case 'A_VALIDER':
                    $requeteWhere .= ' and ( DA_ENTET.VALIDITE = 0x46 )'; // Fin partie requete
                    break;
                case 'TRAITEE': // toutes les lignes de la DA présentes les CDE sont sur des commandes en etats S
                    $requeteWhere .= ' and (DA_ENTET.SIGNEE = 0x54 and DA_ENTET.TRAITEE = 0x54)'; // Fin partie requete
                    break;
                case 'REFUSEE':
                    $requeteWhere .= ' and ( DA_ENTET.REFUSEE = 0x54)'; // Fin partie requete
                    break;
            }
        }

        if (($post['SELECT_SI_DIR']??'') == 'SI' && ($post['CODE_SI']??'')) {
            $requeteWhere .= " AND (
                DA_ENTET.SECTIMP = :sectimpE 
                    OR 
                DA_LIGNE.SECTIMP = :sectimpL
            ) ";
            $arrayReq[':sectimpE'] = $post['CODE_SI'];
            $arrayReq[':sectimpL'] = $post['CODE_SI'];

            if ($post['CODE_CODGES']??'') {
                $cgParId = \Helpers\Outil\Roles::droit($this->pdo, 'CODE_GESTION_PAR_ID');

                if ($cgParId) {
                    $row = 'ID_CG';
                } else {
                    $row = 'CODGES';
                }

                $requeteWhere .= " AND (
                    DA_ENTET.$row = :codeGestE 
                        or 
                    DA_LIGNE.$row = :codeGestL
                )";
                
                $arrayReq[':codeGestE'] = $post['CODE_CODGES'];
                $arrayReq[':codeGestL'] = $post['CODE_CODGES'];
            }
        } elseif (($post['SELECT_SI_DIR']??'') == 'DIR' && ($post['CODE_DIR']??'')) {
            $requeteWhere .= " AND (
                SECTIMPU.CODE_DIR = :codeDirE
                or 
                exists (
                    select NO_DA 
                    from DA_LIGNE with (nolock) 
                    left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = DA_LIGNE.SECTIMP and SECTIMPU.CODE_STE =  ".($this->parametrage['sisecto']? "DA_ENTET.CODE_STE":"''")." 
                    where DA_LIGNE.NO_DA = DA_ENTET.NO_DA and SECTIMPU.CODE_DIR = :codeDirL)
            )";

            $arrayReq[':codeDirE'] = $post['CODE_DIR'];
            $arrayReq[':codeDirL'] = $post['CODE_DIR'];
        }
        
        if (($post['SELECT_CDC_NA']??'') == 'CDC' && ($post['CODE_CDC']??'')) {
            $requeteWhere .= " AND (
                DA_ENTET.CODECOMP = :codeCompE 
                    OR 
                DA_LIGNE.CODE_COMP = :codeCompL
            ) ";

            $arrayReq[':codeCompE'] = $post['CODE_CDC'];
            $arrayReq[':codeCompL'] = $post['CODE_CDC'];
        } elseif (($post['SELECT_CDC_NA']??'') == 'NA' && ($post['CODE_NA']??'')) {
            $requeteWhere .= " AND (
                CODECOMP.CODNAN = :codeNaE
                or 
                exists (
                    select NO_DA 
                    from DA_LIGNE with (nolock) 
                    left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = DA_LIGNE.CODE_COMP and CODECOMP.CODE_STE =  ".($this->parametrage['cdcSecto']? "DA_ENTET.CODE_STE":"''")." 
                    where DA_LIGNE.NO_DA = DA_ENTET.NO_DA and CODECOMP.CODNAN = :codeNaL)
            )";

            $arrayReq[':codeNaE'] = $post['CODE_NA'];
            $arrayReq[':codeNaL'] = $post['CODE_NA'];
        }

        if (($post['SELECT_SCA_CA']??'') == 'SCA' && ($post['CODE_SCA']??'')) {
            $requeteWhere .= " AND (
                DA_ENTET.SSCPTEANA = :scaE 
                    OR 
                DA_LIGNE.SSCPTEANA = :scaL
            ) ";

            $arrayReq[':scaE'] = $post['CODE_SCA'];
            $arrayReq[':scaL'] = $post['CODE_SCA'];
        } elseif (($post['SELECT_SCA_CA']??'') == 'CA' && ($post['CODE_CA']??'')) {
            $requeteWhere .= " AND (
                SSCPTANA.CODE_CTA = :codeCaE
                or 
                exists (
                    select NO_DA 
                    from DA_LIGNE with (nolock) 
                    left join SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = DA_LIGNE.SSCPTEANA and SSCPTANA.CODE_STE =  ".($this->parametrage['cdcSecto']? "DA_ENTET.CODE_STE":"''")." 
                    where DA_LIGNE.NO_DA = DA_ENTET.NO_DA and SSCPTANA.CODE_CTA = :codeCaL)
            )";

            $arrayReq[':codeCaE'] = $post['CODE_CA'];
            $arrayReq[':codeCaL'] = $post['CODE_CA'];
        }

        if (isset($post['COD_TAC']) && $post['COD_TAC']) {
            if ($post['COD_TACCond'] == 'egal') {
                $reqTypAchDefaut = '';
                $typeAchatsDefault = Sql::paravrac($this->pdo, 'PARAMFOU', 'INITTAC');
                if ($post['COD_TAC'] == $typeAchatsDefault) {
                    $reqTypAchDefaut = " or (DA_ENTET.FOU <> '' and FOUDACDE.COD_TAC = '')";
                }

                $requeteWhere .=" and (FOUDACDE.COD_TAC = :typeAchat $reqTypAchDefaut)";
            } else {
                $requeteWhere .=" and FOUDACDE.COD_TAC != :typeAchat";
            }
            $arrayReq[':typeAchat']=$post['COD_TAC'];
        }

        if ($post['DA_CDE']??'') {
            $requeteWhere .= ' AND (
                DA_ENTET.NO_DA LIKE :noDa 
                or exists (select null from CA_LIGNE with(nolock) where NO_DA=DA_ENTET.NO_DA and NO_CDE LIKE :noCde)
            ) ';
            
            $arrayReq[':noCde'] = $post['DA_CDE'];
            $arrayReq[':noDa'] = $post['DA_CDE'];
        }

        if ($post['SIG_DA']??null) {
            $requeteWhere .= "
                and (
                    exists (select NO_DA  from SIG_DA with (nolock) where SIGNAT = :signat1 and DA_ENTET.NO_DA = SIG_DA.NO_DA)
                        or 
                    exists (select NO_DA  from DA_EVENT with (nolock) where (UTIL = :signat2 or SIGNAT = :signat3) and CODE = 'SIG' and DA_ENTET.NO_DA = DA_EVENT.NO_DA)
                )
            ";

            $arrayReq[':signat1'] = $_SESSION["Session_Login"];
            $arrayReq[':signat2'] = $_SESSION["Session_Login"];
            $arrayReq[':signat3'] = $_SESSION["Session_Login"];
        }

        if ($post['ARTICLE']??null) {
            $requeteWhere.= ' and (
                                (upper(CA_LIGNE.DES_SOMM) LIKE :art1 or upper(DCompArtCDE.CONTMEMO) like :art2) 
                                or 
                                (upper(DA_LIGNE.DES_SOMM) LIKE :art3 or upper(DCompArtDA.CONTMEMO) like :art4) 

                                or 
                                (upper(DA_LIGNE.REF_FOUR) LIKE :art5)
                                or 
                                (upper(CA_LIGNE.REF_FOUR) LIKE :art6)

                                or 
                                (upper(DA_LIGNE.CODE_ART) LIKE :art7)
                                or 
                                (upper(CA_LIGNE.CODE_ART) LIKE :art8)
                            )';

            $arrayReq[':art1'] = '%'.strtoupper($post['ARTICLE']).'%';
            $arrayReq[':art2'] = '%'.strtoupper($post['ARTICLE']).'%';
            $arrayReq[':art3'] = '%'.strtoupper($post['ARTICLE']).'%';
            $arrayReq[':art4'] = '%'.strtoupper($post['ARTICLE']).'%';
            $arrayReq[':art5'] = '%'.strtoupper($post['ARTICLE']).'%';
            $arrayReq[':art6'] = '%'.strtoupper($post['ARTICLE']).'%';
            $arrayReq[':art7'] = '%'.strtoupper($post['ARTICLE']).'%';
            $arrayReq[':art8'] = '%'.strtoupper($post['ARTICLE']).'%';
        }

        $finalWhere = $requeteWhere.$whereUO.$clausePerimetre;

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1, 0, $rupture);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $finalWhere .= $filtreAvance->generateSql(1, false, 'DA_ENTET');
        $filtreAvance->pushArray($arrayReq);

        if (! empty($whereArray)) {
            $finalWhere .= $whereArray[0];
            
            if (! empty($whereArray[1])) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        return (object) [
            'where' => $finalWhere,
            'with' => $clauseUO,
            'orderBy' => $whereArray['tri'] ?? '',
        ];
    }
    
    /**
     * @param array<mixed> $post
     * @param ?array<mixed> $select
     * @return array<array<string>>
     */
    public function getListe($post, array $select = null)
    {
        $postPagination = $post;
        $rupture = [];
        $this->rupture->cleanPostForRupture($post, $rupture, 'NO_DA');
        $arrayReq = [];
        $requete = $this->getMainRequest([], $select);
        $req = $requete->requete;
        $with = $requete->with;

        $filter = $this->getFilter($post, [], $arrayReq);

        $req = $req . $filter->where . $filter->orderBy;

        if ($filter->with) {
            $with = str_replace('WITH DA_STATS AS', ', DA_STATS AS', $with);
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount(
            $req,
            $postPagination,
            $arrayReq,
            $this->pdo,
            false,
            $filter->with.$with
        );

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

    /**
     * @param array<mixed> $post
     * @param array<string> $select
     * @return iterable<array<string>>
     */
    public function export($post, array $select)
    {
        $requete = $this->getMainRequest([], $select);
        $req = $requete->requete;
        $with = $requete->with;

        $arrayReq = [];
        $q = $this->getFilter($post, [], $arrayReq);

        if ($q->with) {
            $with = str_replace('WITH DA_STATS AS', ', DA_STATS AS', $with);
        }

        return $this->createStatement(
            $q->with.$with. $req . $q->where,
            $arrayReq
        );
    }

    /**
     * @param array<mixed> $post
     * @param array<mixed> $rupture
     * @return array<mixed>
     */
    public function getListeRupture($post, $rupture = [])
    {
        /** Rupture */
        $this->rupture->cleanPostForRupture($post, $rupture, 'NO_DA');
        $requete = $this->getMainRequest($rupture);
        $req = $requete->requete;
        $with = $requete->with;

        $arrayReq = [];
        $q = $this->getFilter($post, $rupture, $arrayReq);

        if ($q->with) {
            $with = str_replace('WITH DA_STATS AS', ', DA_STATS AS', $with);
        }
        
        return $this->fetchAssocOne(
            $this->rupture->genrateRequeteRupture(
                $req . $q->where,
                $rupture,
                $q->with.$with
            ),
            $arrayReq
        );
    }


    /**
     * @return array<mixed>
     */
    public function getInfoEntete(string $noDa)
    {
        $req = "
            select 
                DA_ENTET.NO_DA,
                DA_ENTET.TYP_DOC ,TYPDOC.LIBELLE LIB_TYDOC,
                DA_ENTET.CODE_STE, SOCIETE.SUIVI_BPA, comm.CONTMEMO COMMENTAIRE,
                FOUDACDE.COD_TAC ,TYPEACHAT.LIB_TAC, DOSSIER.CONTMEMO LIB_DOSACHCOMPLET,DOSACHCOMPLET,
                DA_ENTET.CTR_LIV, ctrLiv.RAIS_SOC LIB_LIV, DA_ENTET.OBJET_DA,
                DA_ENTET.DAENT_CENTRE_EMETTEUR,ctrEmett.RAIS_SOC LIB_EMETT,
                DA_ENTET.SECTIMP, SECTIMPU.LIBELLE LIB_SI,
                DA_ENTET.ACD, ACCCADRE.LIBELLE LIB_ACD,
                DA_ENTET.CODECOMP, CODECOMP.LIBELLE LIB_CDC,
                DA_ENTET.SSCPTEANA, SSCPTANA.LIBELLE LIB_SCA,
                DA_ENTET.CODGES, DA_ENTET.LIBGES, DA_ENTET.ID_CG, DA_ENTET.DEVISE,
                uoDem.CODE_UO UO_DEM,uoDem.LIB_UO LIB_UO_DEM, dem.NOM, DA_ENTET.NO_DEM,
                DA_ENTET.FOU, FOURNISS.RS, DA_ENTET.TOTAL_DA, DA_ENTET.TOTAL_DA_TTC
            from DA_ENTET with (nolock)
            left join SOCIETE with (nolock) on SOCIETE.CODE_STE = DA_ENTET.CODE_STE
            left join FOURNISS with (nolock) on FOURNISS.CODE = DA_ENTET.FOU
            left join TYPDOC with (nolock) on TYPDOC.TYPE = DA_ENTET.TYP_DOC
            left join ACCCADRE with (nolock) on ACCCADRE.CODE_ACD = DA_ENTET.ACD
            left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = DA_ENTET.SECTIMP and SECTIMPU.CODE_STE =  ".($this->parametrage['sisecto']? "DA_ENTET.CODE_STE":"''")." 
            left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = DA_ENTET.CODECOMP and CODECOMP.CODE_STE =  ".($this->parametrage['cdcSecto']? "DA_ENTET.CODE_STE":"''")." 
            left join SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = DA_ENTET.SSCPTEANA and SSCPTANA.CODE_STE =  ".($this->parametrage['scaSecto']? "DA_ENTET.CODE_STE":"''")." 
            left join CENTRE ctrLiv with (nolock) on ctrLiv.CODE_CTR = DA_ENTET.CTR_LIV
            left join CENTRE ctrEmett with (nolock) on ctrEmett.CODE_CTR = DA_ENTET.DAENT_CENTRE_EMETTEUR
            left join FOUDACDE with (nolock) on FOUDACDE.NO_DA = DA_ENTET.NO_DA AND FOUDACDE.CODE_FOU = DA_ENTET.FOU
            LEFT OUTER JOIN MEMOS DOSSIER WITH(NOLOCK) on DOSSIER.NODEMEMO = FOUDACDE.NM_COMSUIVIACH
            left join MEMOS comm with (nolock) on comm.NODEMEMO = DA_ENTET.NM_COMMEN
            left join TYPEACHAT with (nolock) on TYPEACHAT.TYP_ACH = FOUDACDE.COD_TAC
            left join HASTUTIL dem with (nolock) on dem.CODE = DA_ENTET.NO_DEM
            left join UO uoDem with (nolock) on uoDem.CODE_UO = DA_ENTET.UO_DEM and uoDem.CODE_STE = ".($this->parametrage['uoSecto']? "DA_ENTET.CODE_STE":"''")."
            where DA_ENTET.NO_DA = :noDa";

            $arrayReq = [
                ':noDa' => $noDa
            ];
            
            return $this->fetchAssocOne($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getLiseLigne(string $noDa)
    {
        $req = "
            select 
                NO_PO, CODE_ART, DES_SOMM, SSFAM, DTELIVSOU, QTE_DEM, UNITE, PU, DA_ENTET.DEVISE,TVA,
                SSFAMILL.DESIGNAT, FOURNISS.RS, DA_LIGNE.CODE_FOU, MEMOS.CONTMEMO DES_COMPL,
                DA_LIGNE.ACD, ACCCADRE.LIBELLE LIB_ACD, DA_LIGNE.SITE_STK, SSITESTO.DESIGN LIB_SITE_STK,
                DA_LIGNE.SECTIMP, DA_LIGNE.CODGES, DA_LIGNE.ID_CG, DA_LIGNE.CODE_COMP, DA_LIGNE.SSCPTEANA
            from DA_LIGNE with (nolock)
            left join MEMOS with (nolock) on MEMOS.NODEMEMO = DA_LIGNE.NM_DCOMP
            left join ACCCADRE with (nolock) on ACCCADRE.CODE_ACD = DA_LIGNE.ACD
            LEFT JOIN DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
            LEFT JOIN SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = DA_LIGNE.SSFAM
            LEFT JOIN FOURNISS with (nolock) on FOURNISS.CODE = DA_LIGNE.CODE_FOU
            left join SSITESTO with (nolock) on SSITESTO.SITE = DA_LIGNE.SITE_STK and SSITESTO.CODE_STE = DA_LIGNE.CODE_STE
            where DA_LIGNE.NO_DA = :noDa order by DA_LIGNE.NO_PO";

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

    /**
     * Undocumented function
     *
     * @param string $typeDoc
     * @param string|array<mixed> $idDoc
     * @return array<mixed>
     */
    public function getDaLinkDoc(string $typeDoc, $idDoc)
    {
        $requete = $this->getMainRequest([], null, true);
        $req = $requete->with.$requete->requete;

        $arrayReq = [];

        if ($typeDoc == 'DA' && is_array($idDoc)) {
            $req .=  " and ".$this->makeWhereIn('DA_ENTET.NO_DA', $idDoc, $arrayReq);
        } elseif ($typeDoc == 'CDE') {
            $req .=  " 
                    and DA_ENTET.NO_DA in (
                        select DISTINCT(NO_DA) from CA_LIGNE with (nolock) where NO_CDE = :idDoc
                    )";
            $arrayReq = [
                ':idDoc' => $idDoc
            ];
        } elseif ($typeDoc == 'Recep') {
            // TODO : should be a JOIN
            $req .= " AND DA_ENTET.NO_DA in (
                SELECT DA_LIGNE.NO_DA
                FROM DA_LIGNE WITH(NOLOCK)
                JOIN CA_LIGNE with (nolock) ON CA_LIGNE.NO_DA = DA_LIGNE.NO_DA
                JOIN BR_LIGNE with(nolock) 
                    ON CA_LIGNE.NO_CDE = BR_LIGNE.NUM_CDE
                    AND BR_LIGNE.NUM_BR = :idDoc
                WHERE DA_LIGNE.NO_DA != ''
            )";
            $arrayReq = [
                ':idDoc' => $idDoc
            ];
        } elseif ($typeDoc == 'Fac') {
            $req .= "
                    and DA_ENTET.NO_DA in (
                        SELECT DISTINCT(DA_LIGNE.NO_DA)
                        FROM DA_LIGNE WITH(NOLOCK)
                        left join CA_LIGNE WITH (NOLOCK) on CA_LIGNE.NO_DA = DA_LIGNE.NO_DA
                        left join FAC_LIG WITH (NOLOCK) on FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        where FAC_LIG.NUM_FACT = :idDoc
                    )";
            $arrayReq = [
                ':idDoc' => $idDoc
            ];
        }

        $liste = $this->fetchAssocAll($req, $arrayReq);
        
        $menuContextuel = new SuiviDaContextuel($this->pdo, $this->router, $this->trad);
        foreach ($liste as &$doc) {
            if ($typeDoc == 'CDE') {
                $pathRefresh = $this->router->generateUri('Suivi.SuiviCde.ajaxDetails', ['idDoc' => $idDoc, 'source' => 'CDE']);
            } elseif ($typeDoc == 'Recep') {
                $pathRefresh = $this->router->generateUri('Suivi.SuiviRecep.ajaxDetails', ['idDoc' => $idDoc, 'source' => 'Recep']);
            } elseif ($typeDoc == 'Fac') {
                $pathRefresh = $this->router->generateUri('Suivi.SuiviFac.ajaxDetails', ['idDoc' => $idDoc, 'source' => 'Fac']);
            } else {
                $pathRefresh = '';
            }


            $doc['menuContextuel'] = $menuContextuel->generate($doc, 'sousDocument', $pathRefresh);
        }
        return $liste;
    }


    /**
     * Undocumented function
     *
     * @param string $noDa
     * @return array<mixed>
     */
    public function getClause($noDa)
    {
        $req = "select 
                    NO_ORDRE, NODEMEMO, CONTMEMO, ISSU_TAC 
                from DA_CLAUS with (nolock)
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = DA_CLAUS.NM_PARAG
                where DA_CLAUS.NO_DA = :noDa and ISSU_TAC = 0x46";

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

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

    /**
     * Undocumented function
     *
     * @param string $noDa
     * @return array<mixed>
     */
    public function getPJ($noDa)
    {
        $req = "SELECT DA_PIJO.*, TAC_PIJO.TYP_ACH 
                from DA_PIJO with (nolock)  
                LEFT JOIN TAC_PIJO with(nolock) on DA_PIJO.NOM_ORI = TAC_PIJO.NOM_FIC  
                where DA_PIJO.NO_DA = :noDa  and DA_PIJO.NOM_FIC != '' and ISSU_TAC = 0x46 and ISSU_RATT = 0x46 ";

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

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