<?php

namespace Modules\Suivi\Repository;

use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Trad\ITrad;
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',
        ]);
    }

    /**
     * @param array<mixed> $rupture
     * @return string
     */
    private function getMainRequest($rupture = [], string $select = '', bool $count = false)
    {
        if ($count) {
            $select = 'distinct(DA_ENTET.NO_DA)';
        } elseif ($select) {
            $select = 'DISTINCT '.$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, RIGHT(CONCAT('000000000', DA_ENTET.NO_DA), 9) DOC_NO_DA,
                        SOCIETE.RAIS_SOC, FM_CODE, DA_ENTET.NOCDEDIR,
                        FOURNISS.RS,
                        demandeurDA.NOM,
                        SECTIMPU.LIBELLE LIB_SI,

                        /* Menu contextuel */
						(select COUNT(*) from CA_LIGNE with (nolock) where CA_LIGNE.NO_DA = DA_ENTET.NO_DA and CA_LIGNE.NO_PO_DA = DA_LIGNE.NO_DA) CDE_EXIST, 
                        DA_ENTET.DTE_VAL,

                        /* LIB FOR RUPTURE */

                        FOURNISS.RS lib_FOU, demandeurDA.NOM lib_ACH, SOCIETE.RAIS_SOC lib_CODE_STE,
                        SECTIMPU.LIBELLE lib_SECTIMP, CODECOMP.LIBELLE lib_CODECOMP, SSCPTANA.LIBELLE lib_SSCPTEANA,

                        (select COUNT(*) from DA_LIGNE with (nolock) where DA_LIGNE.NO_DA = DA_ENTET.NO_DA and DA_LIGNE.SECTIMP <> DA_ENTET.SECTIMP) SI_DIFF, 
                        (select COUNT(*) from DOC_ATT with (nolock) where COD_ENR= RIGHT(CONCAT('000000000', DA_ENTET.NO_DA), 9) and NO_DA='DA_ENTET') NB_DOC,
                        (select COUNT(*) from DA_PIJO with (nolock) where NO_DA = DA_ENTET.NO_DA) NB_PIJO,
                        CASE
                            WHEN (SELECT count(*) FROM SIG_DA WITH(NOLOCK) WHERE NO_DA = DA_ENTET.NO_DA )> 0 THEN 1 ELSE 0
                        END EN_SIGNATURE,
                        CASE WHEN EXISTS(
                            SELECT DA_LIGNE.NO_PO
                            FROM DA_LIGNE with(nolock) 
                            WHERE DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                                AND DA_LIGNE.ACHATDIR = 'T'
                        ) THEN 1 ELSE 0 
                        END 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);
        }

        $req = "select 
                    ".$select."
                from DA_ENTET WITH (nolock)
                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 Commentaire with (nolock) on Commentaire.NODEMEMO = DA_ENTET.NM_COMMEN
                left join MEMOS memoTitle with (nolock) on memoTitle.NODEMEMO = DA_ENTET.NM_TITRE
                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
                $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 $select = '')
    {
        $postPagination = $post;

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

        $arrayReq = [];

        //$jointureCSDON89 = false; // Dossier 26636 / spécifique NGE a faire sur tout les suivi

        /** 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\\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 .= " EXISTS
                (   
                    SELECT NO_DA FROM DA_LIGNE with (nolock)
                    LEFT OUTER JOIN LSTSIPARUO with (nolock) ON LSTSIPARUO.CODE_SI = DA_LIGNE.SECTIMP ".($this->parametrage['sisecto']? " and LSTSIPARUO.CODE_STESI = DA_LIGNE.CODE_STE ":" and LSTSIPARUO.CODE_STESI = '' ")."
                    WHERE DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    AND LSTSIPARUO.CODE_UO IN (select CodeUO from LstUO) ".($this->parametrage['sisecto']? " AND CODE_STESI = DA_LIGNE.CODE_STE":"")." 
                )";
            }
                
            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 ($_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 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 CA_LIGNE.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;

        // Column ambigu
        $post['filter_DA_ENTET.DTE_LIV'] = $post['filter_DTE_LIV'] ?? null;
        unset($post['filter_DTE_LIV']);

        $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 (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);

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


    /**
     * @return array<mixed>
     */
    public function getInfoEntete(string $noDa)
    {
        $req = "
            select 
                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.DAENT_CENTRE_EMETTEUR,ctrEmett.RAIS_SOC LIB_EMETT,
                DA_ENTET.SECTIMP, SECTIMPU.LIBELLE LIB_SI,
                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 SECTIMPU with (nolock) on SECTIMPU.CODE_SI = DA_ENTET.SECTIMP
            left join CODECOMP with (nolock) on CODECOMP.CODE_CDC = DA_ENTET.CODECOMP
            left join SSCPTANA with (nolock) on SSCPTANA.CODE_SCA = DA_ENTET.SSCPTEANA
            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, DTE_LIV, QTE_DEM, UNITE, PU, DA_ENTET.DEVISE,TVA,
                SSFAMILL.DESIGNAT, FOURNISS.RS, DA_LIGNE.CODE_FOU, MEMOS.CONTMEMO DES_COMPL,DTELIVSOU,
                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 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
            where DA_LIGNE.NO_DA = :noDa";

        $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)
    {
        $req = $this->getMainRequest();

        $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);
    }
}
