<?php

namespace Modules\Suivi\Repository;

use Entity\Model\CaPlPr;
use Entity\Model\Hastutil;
use Entity\Model\Paravrac;
use Helpers\Additional\Specifique\Specifique;
use Helpers\Log\LogSysteme;
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\SuiviCdeContextuel;
use Modules\Suivi\Service\RuptureService;
use Skeleton\Router;

class SuiviCdeRepo extends SuiviRepo
{
    private Specifique $specifique;

    public function __construct(\PDO $pdo, Router $router, ITrad $trad)
    {
        parent::__construct($pdo, $router, $trad);
        $this->specifique = new Specifique();
        $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 HASTUTIL oxaExportACH with (nolock) on oxaExportACH.CODE = CA_ENTET.ACH";
        $jointure[ ]= "LEFT JOIN HASTUTIL oxaExportDEMANDEUR with (nolock) on oxaExportDEMANDEUR.CODE = CA_ENTET.DEMANDEUR";
        $jointure[ ]= "LEFT JOIN HASTUTIL oxaExportLOGIST with (nolock) on oxaExportLOGIST.CODE = CA_ENTET.LOGIST";
        $jointure[ ]= "LEFT JOIN CENTRE oxaExportCTR_EMETT with (nolock) on oxaExportCTR_EMETT.CODE_CTR = CA_ENTET.CTR_EMETT ";
        $jointure[ ]= "LEFT JOIN CENTRE oxaExportCTR_LIV with (nolock) on oxaExportCTR_LIV.CODE_CTR = CA_ENTET.CTR_LIV ";
        $jointure[ ]= "LEFT JOIN CENTRE oxaExportCTR_FACTU with (nolock) on oxaExportCTR_FACTU.CODE_CTR = CA_ENTET.CTR_FACTU ";
        $jointure[ ]= "LEFT JOIN ACCCADRE oxaExportACC_CADRE with (nolock) on oxaExportACC_CADRE.CODE_ACD = CA_ENTET.ACC_CADRE ";
        $jointure[ ]= "LEFT JOIN CONDPAIE oxaExportCOND_PAIE with (nolock) on oxaExportCOND_PAIE.CODE_CDP = CA_ENTET.COND_PAIE ";
        $jointure[ ]= "LEFT JOIN MODEPAIE oxaExportMODE_PAIE with (nolock) on oxaExportMODE_PAIE.CODE_MDP = CA_ENTET.MODE_PAIE ";
        $jointure[ ]= "LEFT JOIN MODELIVR oxaExportMODE_LIV with (nolock) on oxaExportMODE_LIV.CODE_MDL = CA_ENTET.MODE_LIV ";
        $jointure[ ]= "LEFT JOIN SOCIETE oxaExportCODE_STE with (nolock) on oxaExportCODE_STE.CODE_STE = CA_ENTET.CODE_STE ";
        $jointure[ ]= "LEFT JOIN SECTIMPU oxaExportSECTIMP with (nolock) on oxaExportSECTIMP.CODE_SI = CA_ENTET.SECTIMP and oxaExportSECTIMP.CODE_STE = ".($parametrage['sisecto']? "CA_ENTET.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN CODECOMP oxaExportCODECOMP with (nolock) on oxaExportCODECOMP.CODE_CDC = CA_ENTET.CODECOMP  and oxaExportCODECOMP.CODE_STE = ".($parametrage['cdcSecto']? "CA_ENTET.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN SSCPTANA oxaExportSSCPTEANA with (nolock) on oxaExportSSCPTEANA.CODE_SCA = CA_ENTET.SSCPTEANA  and oxaExportSSCPTEANA.CODE_STE = ".($parametrage['scaSecto']? "CA_ENTET.CODE_STE":"''");

        $jointure[ ]= "LEFT JOIN SSFAMILL oxaExport_LIGNE_SSFAM with (nolock) on oxaExport_LIGNE_SSFAM.CODE_SFA = CA_LIGNE.SSFAM";
        $jointure[ ]= "LEFT JOIN SSITESTO oxaExport_LIGNE_SITE_STK with (nolock) on oxaExport_LIGNE_SITE_STK.SITE = CA_LIGNE.SITE_STK";
        $jointure[ ]= "LEFT JOIN SOCIETE oxaExport_LIGNE_CODE_STE with (nolock) on oxaExport_LIGNE_CODE_STE.CODE_STE = CA_LIGNE.CODE_STE";
        $jointure[ ]= "LEFT JOIN ACCCADRE oxaExport_LIGNE_ACCORCA with (nolock) on oxaExport_LIGNE_ACCORCA.CODE_ACD = CA_LIGNE.ACCORCA";
        $jointure[ ]= "LEFT JOIN FABRICAN oxaExport_LIGNE_COD_FAB with (nolock) on oxaExport_LIGNE_COD_FAB.CODE = CA_LIGNE.COD_FAB";
        $jointure[ ]= "LEFT JOIN GAMMES oxaExport_LIGNE_COD_GAM with (nolock) on oxaExport_LIGNE_COD_GAM.CODEGAMME = CA_LIGNE.COD_GAM and oxaExport_LIGNE_COD_GAM.CODEFABR = CA_LIGNE.COD_FAB";
        $jointure[ ]= "LEFT JOIN SECTIMPU oxaExport_LIGNE_SECTIMP with (nolock) on oxaExport_LIGNE_SECTIMP.CODE_SI = CA_LIGNE.SECTIMP and oxaExport_LIGNE_SECTIMP.CODE_STE = ".($parametrage['sisecto']? "CA_LIGNE.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN CODECOMP oxaExport_LIGNE_CODE_COMP with (nolock) on oxaExport_LIGNE_CODE_COMP.CODE_CDC = CA_LIGNE.CODE_COMP and oxaExport_LIGNE_CODE_COMP.CODE_STE = ".($parametrage['cdcSecto']? "CA_LIGNE.CODE_STE":"''");
        $jointure[ ]= "LEFT JOIN SSCPTANA oxaExport_LIGNE_SSCPTEANA with (nolock) on oxaExport_LIGNE_SSCPTEANA.CODE_SCA = CA_LIGNE.SSCPTEANA and oxaExport_LIGNE_SSCPTEANA.CODE_STE = ".($parametrage['scaSecto']? "CA_LIGNE.CODE_STE":"''");
    }
    
    /**
     * @param array<mixed> $rupture
     * @param ?array<string> $select
     */
    private function getMainRequest($rupture = [], array $select = null, bool $count = false) :string
    {
        if ($count) {
            $select = "count(distinct(CA_ENTET.NO_CDE)) countElement";
        } elseif ($select) {
            $q = $this->processSpecialField($select);

            $q->select = array_map(function ($item) {
                if ($item == 'MONTANT_LIGNE_HT') {
                    return [
                        'type' => 'sql_expr',
                        'expr' => '(CA_LIGNE.PU * CA_LIGNE.QTE_COMM)',
                    ];
                }
                return $item;
            }, $q->select);

            $this->jointureExport($this->pdo, $q->join);

            $select = 'DISTINCT '. $this->stringifySelect($q->select);
        } elseif ($rupture) {
            $affTTC = Sql::paravrac($this->pdo, 'OXAPROC', 'AFFTTC') == 'O';

            $select = "distinct(CA_ENTET.NO_CDE), ".($affTTC? "TOT_TTC": "TOT_CDE")." RUPTURE";
            foreach ($rupture as $k => $r) {
                if ($k != '') {
                    $select .= ", ".$k." ";
                }
            }
        } else {
            $select = "distinct(CA_ENTET.NO_CDE), CA_ENTET.CODE_STE, CA_ENTET.CODE_FOU, CA_ENTET.ACH, CA_ENTET.TYP_DOC, CA_ENTET.COD_TAC, CA_ENTET.AVENANT, CA_ENTET.SIGNEE, CA_ENTET.REFUSEE,
                    CA_ENTET.SECTIMP, CA_ENTET.DTE_CREAT, CA_ENTET.DTE_LIV, CA_ENTET.TOT_TTC, CA_ENTET.TOT_CDE, CA_ENTET.TOT_CDEHT,CA_ENTET.ETAT_CDE, CA_ENTET.DEVISE,
                    SOCIETE.RAIS_SOC, RIGHT(CONCAT('000000000', CA_ENTET.NO_CDE), 9) DOC_NO_CDE, CA_ENTET.CDE_EXP,CA_ENTET.OBJET_CA,
                    FOURNISS.RS, CA_ENTET.CAEN_ECART_LIVR, demandeur.NOM LIB_DEMANDEUR,
                    acheteur.NOM,CDE_NOT.NO_CDE as Notation_NO_CDE,
                    CA_ENTET.CODECOMP,CA_ENTET.SSCPTEANA,

                    /* Menu contextuel */
                    CA_ENTET.DEMANDEUR, TYPDOC.TYDO_IND_IMPR_PAR_DEM, CA_ENTET.ETAT_FIN,CA_ENTET.DTE_VALID,
                    blocNote.CONTMEMO blocNote, 

                    (select COUNT(*) FROM BR_RECEP with(nolock) WHERE NUM_CDE = CA_ENTET.NO_CDE) BR_EXIST,
                    (select COUNT(*) from FAC_ENT with(nolock) where FAC_ENT.NUM_CDE = CA_ENTET.NO_CDE) FAC_EXITS,
                    (select COUNT(*) from FAC_LIG with(nolock) where FAC_LIG.NUM_CDE = CA_ENTET.NO_CDE) FAC_LIG_EXISTS,

                    /* LIB FOR RUPTURE */

                    FOURNISS.RS lib_CODE_FOU, acheteur.NOM lib_ACH, SOCIETE.RAIS_SOC lib_CODE_STE,

                    (
                        select 
                            count(distinct pl.NO_CA) as nb 
                        from CA_PL_PR pl with(nolock)
                        LEFT OUTER JOIN CA_LIGNE l with(nolock) on l.NO_CDE=pl.NO_CA and l.NO_PO=pl.NO_PO_CA
                        LEFT OUTER JOIN CA_ENTET e with(nolock) on e.NO_CDE=pl.NO_CA
                        where
                            e.NO_CDE = CA_ENTET.NO_CDE and
                            e.ETAT_CDE='E' and
                        (
                            (pl.DTE_REL_R1<>'00000000' and pl.DTE_REL_R1<= '".date('Ymd')."')
                            or
                            (pl.DTE_REL_R2<>'00000000' and pl.DTE_REL_R2<= '".date('Ymd')."')
                        )
                        and l.SOLD_PHYS=0x46 and l.SAISIEREC=0x54
                        and (l.QTE_COMM-l.RAL) < (select sum(cumpl.QTE_COMM) from CA_PL_CT cumpl with(nolock) where cumpl.NO_CA=pl.NO_CA and cumpl.NO_PO_CA=pl.NO_PO_CA)
                    ) brPossible,     

                    (
                        select top 1 CODE from (
                            SELECT CODE 
                            FROM HASTUTIL with (nolock) 
                            WHERE CODE = '".$_SESSION["Session_Login"]."' AND ADMCENTR = 0x54
                        
                            union
                        
                            SELECT COD_USR CODE FROM ADMLGRSO with (nolock)
                            left join SOCIETE with (nolock) on SOCIETE.GROUPE = ADMLGRSO.COD_GRPS 
                            left join CENTRE with (nolock) on SOCIETE.CODE_STE = CENTRE.CODE_STE
                            left join CA_ENTET subCA with (nolock) on CENTRE.CODE_CTR = subCA.CTR_EMETT
                            WHERE ADMLGRSO.COD_USR = '".$_SESSION["Session_Login"]."' AND ADMLGRSO.TTSOC = 0x54 and subCA.NO_CDE = CA_ENTET.NO_CDE
                        
                            union 
                        
                            SELECT COD_USR CODE 
                            FROM ADML_SOC with (nolock) 
                            left join CA_ENTET subCA2 with (nolock) on SOC_COD = subCA2.CODE_STE
                            WHERE COD_USR = '".$_SESSION["Session_Login"]."' AND TTCTR = 0x54 AND subCA2.NO_CDE = CA_ENTET.NO_CDE
                        
                            union
                        
                            SELECT COD_USR CODE 
                            FROM ADML_CTR with (nolock)
                            left join  HASTUTIL with (nolock) on ADML_CTR.COD_USR = HASTUTIL.CODE
                            WHERE  ADML_CTR.COD_USR = '".$_SESSION["Session_Login"]."' AND HASTUTIL.ADMLOCAL = 0x54
                        ) test
                    ) droitReimp,

                    (
                        select count(*) as nbDARattachees
                        from CA_LIGNE cal with(nolock), DA_ENTET dae with(nolock)
                        where cal.NO_DA = dae.NO_DA and cal.NO_CDE = CA_ENTET.NO_CDE and dae.NO_DEM = '".$_SESSION["Session_Login"]."'
                    ) nbDARattachees,

                    (select COUNT(*) from CA_LIGNE with (nolock) where CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE and CA_LIGNE.SECTIMP <> CA_ENTET.SECTIMP) SI_DIFF, 
                    (select COUNT(*) from BR_LIGNE with (nolock) where NUM_CDE=CA_ENTET.NO_CDE) LIVR_PART, 
                    (select COUNT(*) from DOC_ATT with (nolock) where COD_ENR= RIGHT(CONCAT('000000000', CA_ENTET.NO_CDE), 9) and NO_DA='CA_ENTET') NB_DOC,
                    (select COUNT(*) from CA_PIJO with (nolock) where NO_CA = CA_ENTET.NO_CDE) NB_PIJO,
                    (select COUNT(*) from FOURNINC with (nolock) where NO_CDE = CA_ENTET.NO_CDE) NB_INC,
                    (select COUNT(*) from BR_RECEP with(nolock) where BR_RECEP.NUM_CDE=CA_ENTET.NO_CDE) nbBrCde,
                    (select COUNT(*) from CA_LIGNE with (nolock) where CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE and CA_LIGNE.SAISIEREC = 0x54 and CA_LIGNE.SOLD_PHYS = 0x46 AND CA_LIGNE.RECEP_AUTO = 0x46) countLigneRecep,
                    SECTIMPU.LIBELLE LIB_SI";
        }


        
        $namespaceSpecifique = $this->specifique->specifiqueFunction('\\Modules\\Suivi\\Repository\\SpecSuviCde');

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

        $req = "
            SELECT 
                ".$select."
            from CA_ENTET WITH (nolock)
            left join CA_LIGNE with (nolock) on CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
            left join DA_LIGNE with (nolock) on CA_LIGNE.NO_DA = DA_LIGNE.NO_DA and CA_LIGNE.NO_PO_DA = DA_LIGNE.NO_DA
            left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = CA_LIGNE.NO_DA
            left join SOCIETE with (nolock) on SOCIETE.CODE_STE = CA_ENTET.CODE_STE
            left join FOURNISS with (nolock) on FOURNISS.CODE = CA_ENTET.CODE_FOU
            left join HASTUTIL acheteur with (nolock) on acheteur.CODE = CA_ENTET.ACH
            left join HASTUTIL demandeur with (nolock) on demandeur.CODE = CA_ENTET.DEMANDEUR
            left join TYPEACHAT as tac with (nolock) on tac.TYP_ACH = CA_ENTET.COD_TAC
            left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = CA_ENTET.SECTIMP and SECTIMPU.CODE_STE =  ".($this->parametrage['sisecto']? "CA_ENTET.CODE_STE":"''")." 
            left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = CA_ENTET.CODECOMP and CODECOMP.CODE_STE =  ".($this->parametrage['cdcSecto']? "CA_ENTET.CODE_STE":"''")." 
            left join SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = CA_ENTET.SSCPTEANA and SSCPTANA.CODE_STE =  ".($this->parametrage['scaSecto']? "CA_ENTET.CODE_STE":"''")." 
            left join MEMOS DCompArt with (nolock) on CA_LIGNE.NM_DCOMP = DCompArt.NODEMEMO
            left join TYPDOC with (nolock) on TYPDOC.TYPE = CA_ENTET.TYP_DOC
            left join CABNOTE with (nolock) on CABNOTE.NO_CA = CA_ENTET.NO_CDE
            left join MEMOS blocNote with (nolock) on blocNote.NODEMEMO = CABNOTE.CA_NOTE
            LEFT JOIN CDE_NOT with (nolock) ON CDE_NOT.NO_CDE = CA_ENTET.NO_CDE "
            . implode("\r\n", $q->join ?? [])
            . $jointureSpecifique
            . " where 1=1 ".Roles::getClauseSocieteOuvert('SOCIETE');
            
        return $req;
    }

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getListe($post, string $typeSuivi = '', bool $count = false)
    {
        $postPagination = $post;
        $arrayReq = [];
        $rupture = [];
        
        $req = $this->getMainRequest([], null, $count);
        $this->rupture->cleanPostForRupture($post, $rupture, 'NO_CDE');

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

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

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

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

        $listCde = $this->fetchAssocAll($req, $arrayReq);

        if (empty($listCde)) {
            return [];
        }

        // Get Associated planning prev
        $this->arrayJoinManyToMany(
            $listCde,
            count($listCde) > 0
            ? (new CaPlPr($this->pdo))->findAll([
                'NO_CA' => array_column($listCde, 'NO_CDE'),
            ])
            : [],
            'NO_CDE',
            'listPlanningPrev',
            'NO_CA'
        );

        // Calculate list of unique date in the Planning prev
        return array_map(function ($cde) {
            return $cde + [
                'listDatePlanningPrev' => array_unique(
                    array_column($cde['listPlanningPrev'], 'DTE_LIV')
                ),
            ];
        }, $listCde);
    }

    /**
     * @param array<mixed> $post
     * @param array<mixed> $select
     * @return \PDOStatement<array<string>>
     */
    public function export(
        $post,
        string $typeSuivi,
        array $select
    ) :\PDOStatement {
        $arrayReq = [];
        $filter = $this->getMainFilter($post, [], $typeSuivi, $arrayReq);

        return $this->createStatement(
            $filter->with
            . $this->getMainRequest([], $select)
            . $filter->where
            . $filter->orderBy,
            $arrayReq
        );
    }

    /**
     * @param array<mixed> $post
     * @param array<mixed> $rupture
     * @return array<mixed>
     */
    public function getRupture($post, $rupture = [])
    {
        $this->rupture->cleanPostForRupture($post, $rupture, 'NO_CDE');
        $req = $this->getMainRequest($rupture, null);

        $filter = $this->getMainFilter($post, $rupture, '', $arrayReq);

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

        $req = $this->rupture->genrateRequeteRupture($req, $rupture, $filter->with);
        return $this->fetchAssocOne($req, $arrayReq);
    }


    // /**
    //  * Shorthand for splitFilter + FiltreAvance + fetchAssocAll
    //  * @param array<mixed> $param
    //  * @param array<mixed> $post
    //  */
    // protected function applyFilter(
    //     string $query,
    //     array &$param,
    //     array $post
    // ) :string {

    /**
     * @param array<mixed> $post
     * @param array<mixed> $rupture
     * @param array<mixed> $arrayReq
     * @return object{where:string,with:string}
     * @return \stdClass
     */
    private function getMainFilter(
        $post,
        $rupture,
        string $typeSuivi,
        &$arrayReq
    ) :object {

        $where = '';


        if ($typeSuivi == 'SCdeToRate') {
            $where .= " AND exists(select null from CDE_NOT with(nolock) where CA_ENTET.NO_CDE = CDE_NOT.NO_CDE)";
        } elseif ($typeSuivi == 'CdeToRate') {
            $where .= " AND CA_ENTET.NO_CDE in (	
                select distinct(CDE_NOT.NO_CDE)
                from CDE_NOT with (nolock)
                left join CDE_THEM with (nolock) on CDE_NOT.NO_CDE	 = CDE_THEM.NO_CDE  
                left join THEMEUSR with (nolock) on THEMEUSR.COD_THEM  = CDE_THEM.COD_THEM 
                left join THEMES with (nolock) on THEMES.COD_THEM = CDE_THEM.COD_THEM
                left join CA_ENTET with (nolock) on CA_ENTET.NO_CDE = CDE_NOT.NO_CDE
                where CDE_NOT.DTEVALID = '00000000' and THEMES.TH_AUT = 0x54
                and (THEMEUSR.COD_UTIL  = :userRate1 or CA_ENTET.ACH = :userRate2 or CA_ENTET.DEMANDEUR   = :userRate3)
            )";

            $arrayReq[':userRate1'] = $_SESSION["Session_Login"];
            $arrayReq[':userRate2'] = $_SESSION["Session_Login"];
            $arrayReq[':userRate3'] = $_SESSION["Session_Login"];
        }

        /** 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\\SpecSuviCde');
        
                $clauseUOSpecifique = '';
                if (class_exists($namespaceSpecifique)) {
                    $clauseUOSpecifique = \Helpers\Additional\Specifique\Specifique::specifique('\\Modules\\Suivi\\Repository\\SpecSuviCde', 'getClauseUOSpecifique');
                }

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

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

                $whereUO .= " EXISTS
                (   
                    SELECT NO_CDE FROM CA_LIGNE with (nolock)
                    LEFT OUTER JOIN LSTSIPARUO with (nolock) ON LSTSIPARUO.CODE_SI = CA_LIGNE.SECTIMP ".($this->parametrage['sisecto']? " and LSTSIPARUO.CODE_STESI = CA_LIGNE.CODE_STE ":" and LSTSIPARUO.CODE_STESI = '' ")."
                    WHERE CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                    AND LSTSIPARUO.CODE_UO IN (select CodeUO from LstUO) ".($this->parametrage['sisecto']? " AND CODE_STESI = CA_LIGNE.CODE_STE":"")." 
                )";
            }
                
            if ($whereUO) {
                $whereUO = " AND ($whereUO)";
                $clauseUO = $this->getClauseUO($_SESSION["Session_Login"], $post['UO']??'');
            }
        }

        /** Périmètre */
        /** Société ? multi / pluri / mono */
        $clausePerimetre = Roles::getClausePerimetreSociete('CA_ENTET.CODE_STE', $arrayReq);

        /** Centre */
        if (!$this->droit['HA_DA_TTCT']) {
            $clausePerimetre .= " AND CA_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 CA_ENTET.CTR_EMETT 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 ($typeSuivi != 'CdeToRate') {
            if (!$this->droit['HA_DA_TTDM']) {
                $perimetreAcheteur .= "(CA_ENTET.ACH = :userAch1 or CA_ENTET.DEMANDEUR = :userAch2 or DA_ENTET.NO_DEM = :userAch3 or DA_LIGNE.ACHETEUR = :userAch4)";
    
                $arrayReq[':userAch1'] = $_SESSION["Session_Login"];
                $arrayReq[':userAch2'] = $_SESSION["Session_Login"];
                $arrayReq[':userAch3'] = $_SESSION["Session_Login"];
                $arrayReq[':userAch4'] = $_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 CA_EVENT with (nolock) WHERE CA_ENTET.NO_CDE = CA_EVENT.NO_CDE AND   (CA_EVENT.UTIL = :userEvent1 or CA_EVENT.SIGNAT = :userEvent2)) 
                or
                exists(select null from SIG_CDE with (nolock) where CA_ENTET.NO_CDE = SIG_CDE.NO_CDE AND   SIG_CDE.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;
        }

        /** Filtre */
        //acheteur
        $post['filter_ACHdebut-----finacheteur.NOM'] = $post['filter_ACHdebut-----finNOM']??'';
        unset($post['filter_ACHdebut-----finNOM']);

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

        $post['filter_CA_ENTET.NO_CDE'] = $post['filter_NO_CDE']??'';
        unset($post['filter_NO_CDE']);

        $post['filter_CA_ENTET.CODE_FOUdebut-----finFOURNISS.RS'] = $post['filter_egal_CODE_FOUdebut-----finRS']??'';
        unset($post['filter_egal_CODE_FOUdebut-----finRS']);

        $post['filter_CA_ENTET.CTR_LIV'] = $post['filter_CTR_LIV']??'';
        unset($post['filter_CTR_LIV']);

        $post['filter_CA_ENTET.SECTIMP'] = $post['filter_SECTIMP']??'';
        unset($post['filter_SECTIMP']);
        
        $post['filter_date_CA_ENTET.DTE_LIV'] = $post['filter_date_DTE_LIV']??'';
        unset($post['filter_date_DTE_LIV']);

        $post['filter_acheteur.NOMdebut-----finCA_ENTET.ACH'] = $post['filter_Acheteur']??'';
        unset($post['filter_Acheteur']);

        $where .= $this->getStatusClause($post['filter_ETAT_CDE'] ?? '');
        unset($post['filter_ETAT_CDE']);

        /** sous traiteance */
        $sousTraitance = (isset($post['sousTraitance'])) ? trim(($post['sousTraitance'])) : '';
        if ($sousTraitance) {
            switch ($sousTraitance) {
                case 'TOUTES':
                    break;
                case 'SANS_ST':
                    $where .= " and (tac.STRAIT = 0x46 or tac.TYP_ACH is null or CA_ENTET.COD_TAC = '')";
                    break;
                case 'AVEC_ST':
                    $where .= " and tac.STRAIT = 0x54";
                    break;
                case 'SANS_CONTRAT':
                    $where .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT = 0)";
                    break;
                case 'AVEC_CONTRAT':
                    $where .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT <> 0)";
                    break;
                case 'CONTRAT_NON_RECEP':
                    $where .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT <> 0 and CA_ENTET.DTE_RECCONT = '00000000')";
                    break;
                case 'CONTRAT_RECEP':
                    $where .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT <> 0 and CA_ENTET.DTE_RECCONT <> '00000000')";
                    break;
            }
        }

        /** filtre acheteur */
        if ($post['ACH']??'') {
            $where .= " and (CA_ENTET.ACH = :ach1 or CA_ENTET.DEMANDEUR = :ach2 or DA_ENTET.NO_DEM = :ach3)";

            $arrayReq[':ach1'] = $post['ACH'];
            $arrayReq[':ach2'] = $post['ACH'];
            $arrayReq[':ach3'] = $post['ACH'];
        }
        
        /** Axe d'imputation (SI/CDC/SCA) | (DIRECTION/NA/CA) */
        if (($post['SELECT_SI_DIR']??'') == 'SI' && ($post['CODE_SI']??'')) {
            $where .= " AND (
                CA_ENTET.SECTIMP = :sectimpE 
                    OR 
                CA_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';
                }

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

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

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

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

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

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

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

        /** Filtre article */
        if (isset($post['ARTICLE']) && $post['ARTICLE']) {
            $where.= ' and (
                                upper(CA_LIGNE.DES_SOMM) LIKE :art1 
                                or upper(DCompArt.CONTMEMO) like :art2 
                                or upper(CA_LIGNE.REF_FOUR) like :art3 
                                or upper(CA_LIGNE.CODE_ART) like :art4
                        ) ';

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

        /** filtre da ou cde */
        if ($post['DA_CDE']??'') {
            $where.= ' AND (CA_LIGNE.NO_DA = :noDa or CA_LIGNE.NO_CDE = :noCde) ';

            $arrayReq[':noDa'] = $post['DA_CDE'];
            $arrayReq[':noCde'] = $post['DA_CDE'];
        }

        /** Case a coché "signataire ?" */
        if ($post['SIG_CDE']??null) {
            $where .= " AND (
                exists (select NO_CDE  from SIG_CDE with (nolock) where SIGNAT = :signat1 and CA_ENTET.NO_CDE = SIG_CDE.NO_CDE)
                    or 
                exists (select NO_CDE  from CA_EVENT with (nolock) where (UTIL = :signat2 or SIGNAT = :signat3) and CODE = 'SIG' and CA_ENTET.NO_CDE = CA_EVENT.NO_CDE)
            ) ";

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



        $finalWhere = $where.$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, 'CA_ENTET');
        $filtreAvance->pushArray($arrayReq);

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

        return (object)[
            'with' => $clauseUO,
            'where' => $finalWhere,
            'orderBy' => $whereArray['tri'] ?? '',
        ];
    }

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getListeSend($post)
    {
        $req =
        $this->getMainRequest()
        . " AND (
                CA_ENTET.DEMANDEUR = :demandeur1
                OR (
                    CA_ENTET.AVENANT <>'' and (
                        select UTIL from CA_EVENT with (nolock) 
                        where CA_EVENT.NO_CDE=CA_ENTET.NO_CDE 
                        and CA_EVENT.NUM_ORD = CA_ENTET.EVE_VAL
                    ) = :demandeur2
                ) 
            )   
            and (CA_ENTET.ETAT_CDE		='V'	or ( CA_ENTET.ETAT_CDE 		='Z' and CA_ENTET.EVE_IMP 		=0))
            and CA_ENTET.SIGNEE			= 0x54 
            and CA_ENTET.REFUSEE		= 0x46";

        $arrayReq = [
        ':demandeur1' => $_SESSION["Session_Login"],
        ':demandeur2' => $_SESSION["Session_Login"],
        ];
        
        // TODO : factorize with Commande::countCdeSend
        /** @see Modules\Commande\Repository\Commande::countCdeSend */
        if (!Roles::droitsUtilisateur($this->pdo, 'HA_CA_IMP')) {
            $req .= " AND (
                CA_ENTET.TYP_DOC = :type_doc
                OR TYPDOC.TYDO_IND_IMPR_PAR_DEM = 0x54
            )";
            $arrayReq[':type_doc'] = (new Paravrac($this->pdo))->getValue('OXAPROC', 'TYPDOCUMENT');
        }

        /** Filtre */
        $post['filter_egal_CA_ENTET.CODE_STE'] = $post['filter_egal_CODE_STE']??'';
        unset($post['filter_egal_CODE_STE']);

        $post['filter_CA_ENTET.NO_CDE'] = $post['filter_NO_CDE']??'';
        unset($post['filter_NO_CDE']);

        $post['filter_CA_ENTET.CODE_FOU'] = $post['filter_CODE_FOU']??'';
        unset($post['filter_CODE_FOU']);

        $post['filter_CA_ENTET.SECTIMP'] = $post['filter_SECTIMP']??'';
        unset($post['filter_SECTIMP']);
        
        $post['filter_date_CA_ENTET.DTE_LIV'] = $post['filter_date_DTE_LIV']??'';
        unset($post['filter_date_DTE_LIV']);

        $post['filter_acheteur.NOMdebut-----finCA_ENTET.ACH'] = $post['filter_Acheteur']??'';
        unset($post['filter_Acheteur']);

        $post['filter_CA_ENTET.CODE_FOUdebut-----finRS'] = $post['filter_CODE_FOUdebut-----finRS']??'';
        unset($post['filter_CODE_FOUdebut-----finRS']);

        $req .= $this->getStatusClause($post['filter_ETAT_CDE'] ?? '');

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

        if (count($whereArray)>  0) {
            $req .= $whereArray[0]. ' ' . $whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getInfoEntete(string $noCde)
    {
        $req = "
            select 
                CA_ENTET.NO_CDE,
                CA_ENTET.AVENANT, CA_ENTET.MOT_AVNT,
                CA_ENTET.CODE_STE, CA_ENTET.OBJET_CA,
                CA_ENTET.TYP_DOC ,TYPDOC.LIBELLE LIB_TYDOC,
                CA_ENTET.ACC_CADRE, ACCCADRE.LIBELLE LIB_ACD,
                CA_ENTET.COD_TAC ,TYPEACHAT.LIB_TAC,
                CA_ENTET.SECTIMP, SECTIMPU.LIBELLE LIB_SI,
                CA_ENTET.CODECOMP, CODECOMP.LIBELLE LIB_CDC,
                CA_ENTET.SSCPTEANA, SSCPTANA.LIBELLE LIB_SCA,
                CA_ENTET.CODGES, CA_ENTET.LIBGES, CA_ENTET.ID_CG,
                CA_ENTET.CODE_FOU, FOURNISS.RS, CA_ENTET.TOT_CDEHT,
                CA_ENTET.CTR_LIV, ctrLiv.RAIS_SOC LIB_LIV,
                CA_ENTET.CTR_EMETT, ctrEmett.RAIS_SOC LIB_EMETT,
                ach.CODE, ach.NOM, CA_ENTET.DEVISE, CA_ENTET.TOT_TTC,
                demandeur.CODE DEMANDEUR, demandeur.NOM LIB_DEMANDEUR,
                uoAch.CODE_UO UO_ACH,uoAch.LIB_UO LIB_UO_ACH,
                uoDem.CODE_UO UO_DEM,uoDem.LIB_UO LIB_UO_DEM,
                premierAvnt.TOTAL_HT PREM_TOTAL_HT, 
                premierAvnt.TOTAL_TTC PREM_TOTAL_TTC,
                CA_ENTET.TOT_CDEHT LAST_TOTAL_HT, 
                CA_ENTET.TOT_TTC LAST_TOTAL_TTC
            from CA_ENTET with (nolock)
            left join TYPDOC with (nolock) on TYPDOC.TYPE = CA_ENTET.TYP_DOC
            left join TYPEACHAT with (nolock) on TYPEACHAT.TYP_ACH = CA_ENTET.COD_TAC
            left join ACCCADRE WITH (nolock) on ACCCADRE.CODE_ACD = CA_ENTET.ACC_CADRE
            left join SECTIMPU with (nolock) on SECTIMPU.CODE_SI = CA_ENTET.SECTIMP and SECTIMPU.CODE_STE =  ".($this->parametrage['sisecto']? "CA_ENTET.CODE_STE":"''")." 
            left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = CA_ENTET.CODECOMP and CODECOMP.CODE_STE =  ".($this->parametrage['cdcSecto']? "CA_ENTET.CODE_STE":"''")." 
            left join SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = CA_ENTET.SSCPTEANA and SSCPTANA.CODE_STE =  ".($this->parametrage['scaSecto']? "CA_ENTET.CODE_STE":"''")." 
            left join FOURNISS with (nolock) on FOURNISS.CODE = CA_ENTET.CODE_FOU
            left join UO uoAch with (nolock) on uoAch.CODE_UO = CA_ENTET.UO_ACH and uoAch.CODE_STE = ".($this->parametrage['uoSecto']? "CA_ENTET.CODE_STE":"''")."
            left join UO uoDem with (nolock) on uoDem.CODE_UO = CA_ENTET.UO_DEM and uoAch.CODE_STE = ".($this->parametrage['uoSecto']? "CA_ENTET.CODE_STE":"''")."
            left join HASTUTIL ach with (nolock) on ach.CODE = CA_ENTET.ACH
            left join HASTUTIL demandeur with (nolock) on demandeur.CODE = CA_ENTET.DEMANDEUR
            left join CENTRE ctrLiv with (nolock) on ctrLiv.CODE_CTR = CA_ENTET.CTR_LIV
            left join CENTRE ctrEmett with (nolock) on ctrEmett.CODE_CTR = CA_ENTET.CTR_EMETT
            outer apply (
                select TOTAL_HT, TOTAL_TTC 
                from HCA_ENTE
                where HCA_ENTE.NO_CDE = CA_ENTET.NO_CDE and HCA_ENTE.AVNT = ''
            ) premierAvnt
            where CA_ENTET.NO_CDE = :noCde";

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

    /**
     * @return array<mixed>
     */
    public function getLiseLigne(string $noCde)
    {
        $req = "
            select 
                NO_PO, CODE_ART, DES_SOMM, SSFAM, QTE_COMM, RAL, UNITE, PU, '' TTC, CA_ENTET.DEVISE,TVA, DTELIVCON, DTELIVPRE,SSFAMILL.DESIGNAT,
                MEMOS.CONTMEMO DES_COMPL,
                (
                    SELECT count(*) 
                    FROM CA_PL_CT with (nolock) 
                    WHERE NO_CA = CA_LIGNE.NO_CDE 
                        AND NO_PO_CA = CA_LIGNE.NO_PO 
                ) as PLANNING,
                /* utilisé pour les modif & signer */
                CA_LIGNE.SECTIMP, CA_LIGNE.CODE_COMP, CA_LIGNE.CODGES, CA_LIGNE.SSCPTEANA, PU, QTE_COMM, TVA,
                (select COUNT(*) from CC_LDOC with(nolock) where DOC_TYPE = 'CDE' AND DOC_NUM = CA_LIGNE.NO_CDE and DOC_LINE_NUM = CA_LIGNE.NO_PO) cc_exist
            from CA_LIGNE with (nolock)
            left join MEMOS with (nolock) on MEMOS.NODEMEMO = CA_LIGNE.NM_DCOMP
            left join ACCCADRE with (nolock) on ACCCADRE.CODE_ACD = CA_LIGNE.ACCORCA
            LEFT JOIN CA_ENTET with (nolock) on CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
            LEFT JOIN SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = CA_LIGNE.SSFAM
            where CA_LIGNE.NO_CDE = :noCde";

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

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

    /**
     * Undocumented function
     *
     * @param string $typeDoc
     * @param string|array<mixed> $idDoc
     * @return array<mixed>
     */
    public function getCdeLinkDoc(string $typeDoc, $idDoc)
    {
        $req = $this->getMainRequest();

        $arrayReq = [];

        if ($typeDoc == 'CDE' && is_array($idDoc)) {
            $req .=  " and ".$this->makeWhereIn('CA_ENTET.NO_CDE', $idDoc, $arrayReq);
        } elseif ($typeDoc == 'DA') {
            $req .=  " and CA_ENTET.NO_CDE in (select DISTINCT(NO_CDE) from CA_LIGNE WITH (nolock) where NO_DA = :idDoc)";
            $arrayReq = [
            ':idDoc' => $idDoc
            ];
        } elseif ($typeDoc == 'Recep') {
            $req .=  " and CA_ENTET.NO_CDE in (select distinct(NUM_CDE) from BR_LIGNE WITH (nolock) where NUM_BR = :idDoc)";
            $arrayReq = [
            ':idDoc' => $idDoc
            ];
        } elseif ($typeDoc == 'Fac') {
            $req .=  " and CA_ENTET.NO_CDE in (select distinct(NUM_CDE) from FAC_LIG WITH (nolock) where NUM_FACT = :idDoc)";
            $arrayReq = [
            ':idDoc' => $idDoc
            ];
        }

        $listeCde = $this->fetchAssocAll($req, $arrayReq);

        $menuContextuel = new SuiviCdeContextuel($this->pdo, $this->router, $this->trad);
        foreach ($listeCde as &$cde) {
            if ($typeDoc == 'DA') {
                $pathRefresh = $this->router->generateUri('Suivi.SuiviDa.ajaxDetails', ['idDoc' => $idDoc, 'source' => 'DA']);
            } 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 = '';
            }

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

        return $listeCde;
    }

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

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

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

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

    /**
     * Undocumented function
     *
     * @param string $noCde
     * @return array<mixed>
     */
    public function notationActif(string $noCde)
    {
        $req = "select 
                    count(*) countActiveNotation 
                from CDE_NOT with (nolock) 
                where NOTMANU = 0x54 
                and DTEDEMAND <= '".date('Ymd')."' and (DTEVALID >= '".date('Ymd')."' or DTEVALID = '00000000') 
                and CDE_NOT.NO_CDE =  :noCde";
    
        $arrayReq = [
        ':noCde' => $noCde,
        ];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }

    private function getStatusClause(string $status) :string
    {
        if (empty($status)) {
            return '';
        }
        switch ($status) {
            case 'EN_COUR':
                return " AND CA_ENTET.ETAT_CDE='E'";
            case 'EN_ATTENTE':
                return " AND CA_ENTET.ETAT_CDE='A'";
            case 'EN_SIGNATURE':
                return " AND (CA_ENTET.ETAT_CDE='V' and CA_ENTET.SIGNEE=0x46 and CA_ENTET.REFUSEE=0x46)";
            case 'REFUSEE':
                return " AND (CA_ENTET.ETAT_CDE='V' and CA_ENTET.SIGNEE=0x46 and CA_ENTET.REFUSEE=0x54)";
            case 'A_ENVOYER':
                return " AND (CA_ENTET.ETAT_CDE='V' and CA_ENTET.SIGNEE=0x54 and CA_ENTET.REFUSEE=0x46)";
            case 'ENVOYEE':
                return " AND (CA_ENTET.ETAT_CDE='E')";
            case 'ANNULEE':
                return " AND (CA_ENTET.ETAT_CDE='Z')";
            case 'A_RECEPTIONNER':
                return " AND (
                    CA_ENTET.ETAT_CDE='E'
                    and exists(select null from CA_LIGNE with(nolock) where CA_ENTET.NO_CDE=CA_LIGNE.NO_CDE and CA_LIGNE.SOLD_PHYS=0x46 and CA_LIGNE.SAISIEREC=0x54 and CA_LIGNE.RECEP_AUTO=0x46 and CA_LIGNE.RAL>0)
                    and exists(select null from CA_PL_PR with(nolock) where CA_PL_PR.CODE_STE=CA_ENTET.CODE_STE and CA_PL_PR.NO_CA=CA_ENTET.NO_CDE and CA_PL_PR.DTE_LIV<='".date('Ymd')."')
                )";
            case 'RECEPTIONNEE_PARTIEL':
                return " AND ( CA_ENTET.ETAT_CDE='E' and exists ( select null from BR_RECEP with(nolock) where NUM_CDE = CA_ENTET.NO_CDE ))";
            case 'RECEPTIONNEE':
            case 'SOLDER_PHY':
                return " AND CA_ENTET.ETAT_CDE='S'";
            case 'FACTUREE':
                return " AND CA_ENTET.ETAT_FIN='F' AND EXISTS (select null from FAC_LIG with (nolock) WHERE FAC_LIG.NUM_CDE = CA_ENTET.NO_CDE)";
            case 'EN_LITIGE':
                return " AND exists(select null from FAC_ENT with (nolock) where CA_ENTET.NO_CDE=FAC_ENT.NUM_CDE and LITIGE=0x54)";
        }
        LogSysteme::prefixErrorLog((string)new \Exception('Invalid status "'.$status.'"'));
        return '';
    }
}
