<?php

namespace Modules\Suivi\Repository;

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\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',
        ]);
    }
    
    /**
     * @param array<mixed> $rupture
     * @return string
     */
    private function getMainRequest($rupture = [], string $select = '', bool $count = false)
    {
        if ($count) {
            $select = 'distinct(CA_ENTET.NO_CDE)';
        } elseif ($select) {
            $select = 'DISTINCT '.$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,
                    FOURNISS.RS, CA_ENTET.CAEN_ECART_LIVR, demandeur.NOM LIB_DEMANDEUR,
                    acheteur.NOM,CDE_NOT.NO_CDE as Notation_NO_CDE,

                    /* 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 with (nolock) on CENTRE.CODE_CTR = CA_ENTET.CTR_EMETT
                            WHERE ADMLGRSO.COD_USR = '".$_SESSION["Session_Login"]."' AND ADMLGRSO.TTSOC = 0x54 and CA_ENTET.NO_CDE = CA_ENTET.NO_CDE
                        
                            union 
                        
                            SELECT COD_USR CODE 
                            FROM ADML_SOC with (nolock) 
                            left join CA_ENTET with (nolock) on SOC_COD = CA_ENTET.CODE_STE
                            WHERE COD_USR = '".$_SESSION["Session_Login"]."' AND TTCTR = 0x54 AND CA_ENTET.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 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
            $jointureSpecifique
            where 1=1 ".Roles::getClauseSocieteOuvert('SOCIETE');

        return $req;
    }

    /**
     * @param array<mixed> $post
     * @param array<mixed> $rupture
     * @return array<mixed>
     */
    public function getListe($post, $rupture = [], string $typeSuivi = '', string $select = '', bool $export = false)
    {
        $postPagination = $post;

        /** Rupture */
        $this->rupture->cleanPostForRupture($post, $rupture, 'NO_CDE');
        $req = $this->getMainRequest($rupture, $select);

        $arrayReq = [];

        if ($typeSuivi == 'SCdeToRate') {
            $req        .= "and exists(select null from CDE_NOT with(nolock) where CA_ENTET.NO_CDE = CDE_NOT.NO_CDE)";
        } elseif ($typeSuivi == 'CdeToRate') {
            $req .= " 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 ($_SESSION['FiltreUOparDefaut'] && (($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 = '';
        $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 ($_SESSION['CONFIDENTIALITE_ACTIVE']) {
            $clausePerimetre .= ' and TYPDOC.NIV_CONF<= :nivConf ';

            $arrayReq[':nivConf'] = $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_FOU'] = $post['filter_CODE_FOU']??'';
        unset($post['filter_CODE_FOU']);

        $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']);

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

        $requeteWhere = '';
        $requeteWhere .= $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':
                    $requeteWhere .= " and (tac.STRAIT = 0x46 or tac.TYP_ACH is null or CA_ENTET.COD_TAC = '')";
                    break;
                case 'AVEC_ST':
                    $requeteWhere .= " and tac.STRAIT = 0x54";
                    break;
                case 'SANS_CONTRAT':
                    $requeteWhere .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT = 0)";
                    break;
                case 'AVEC_CONTRAT':
                    $requeteWhere .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT <> 0)";
                    break;
                case 'CONTRAT_NON_RECEP':
                    $requeteWhere .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT <> 0 and CA_ENTET.DTE_RECCONT = '00000000')";
                    break;
                case 'CONTRAT_RECEP':
                    $requeteWhere .= " and (tac.STRAIT = 0x54 and CA_ENTET.NO_DOC_CONT <> 0 and CA_ENTET.DTE_RECCONT <> '00000000')";
                    break;
            }
        }

        /** filtre acheteur */
        if ($post['ACH']??'') {
            $requeteWhere .= " 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']??'')) {
            $requeteWhere .= " 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';
                }

                $requeteWhere .= " 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']??'')) {
            $requeteWhere .= " 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']??'')) {
            $requeteWhere .= " 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']??'')) {
            $requeteWhere .= " 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']??'')) {
            $requeteWhere .= " 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']??'')) {
            $requeteWhere .= " 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']) {
            $requeteWhere.= ' 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']??'') {
            $requeteWhere.= ' 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) {
            $requeteWhere .= "
                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 = $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, 'CA_ENTET');
        $filtreAvance->pushArray($arrayReq);

        if (count($whereArray)>  0) {
            $finalWhere .= $whereArray[0]. ' ' . $whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        $req .= $finalWhere;

        if (!$rupture) {
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $postPagination, $arrayReq, $this->pdo, false, $clauseUO);

            if ($export) {
                $req = $clauseUO.$req;
            }

            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            $req = $this->rupture->genrateRequeteRupture($req, $rupture, $clauseUO);
            return $this->fetchAssocOne($req, $arrayReq);
        }
    }

    /**
     * @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 ($_SESSION['CDE_AuthImprim'] != true) {
            $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_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.AVENANT, CA_ENTET.MOT_AVNT,
                CA_ENTET.CODE_STE, CA_ENTET.OBJET_CA,
                CA_ENTET.TYP_DOC ,TYPDOC.LIBELLE LIB_TYDOC,
                CA_ENTET.COD_TAC ,TYPEACHAT.LIB_TAC,
                CA_ENTET.ACC_CADRE, ACCCADRE.LIBELLE LIB_ACD,
                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 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 '';
    }
}
