<?php

namespace Modules\DemandeAchat\Repository;

use Entity\Model\BrRecep;
use Entity\Model\CaEntet;
use Entity\Model\DaEntet;
use Entity\Model\Fourniss;
use Entity\Model\Hastutil;
use Helpers\Outil\ArrayHelper;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Reception\Repository\WorkflowBonRecepRepo;
use Modules\Commande\Repository\WorkflowCommandeRepo;
use Modules\Facture\Outil\ImageFacture;
use Modules\Facture\Repository\WorkflowBapRepo;

class WorkflowDemandeAchatRepo extends AbsRepository
{

    /**
     * @return array<string,string>
     */
    public function getWorkflowState(string $demId) :array
    {
        return $this->fetchAssocOne(
            'SELECT 
                DA_ENTET.NO_DA,
                DA_ENTET.CHIF_EXEC,
                DA_ENTET.VALIDITE,
                DA_ENTET.REFUSEE,
                DA_ENTET.SIGNEE,
                DA_ENTET.FM_CODE,
                DA_ENTET.TRAITEE,
                CENTRE.TRS_INTER,
                CASE WHEN EXISTS(
                    SELECT DA_LIGNE.NO_PO
                    FROM DA_LIGNE with(nolock) 
                    WHERE NO_DA = DA_ENTET.NO_DA
                        AND rtrim(ltrim(SITE_STK)) = \'\'
                ) THEN 1 ELSE 0 
                END as hasLigneNonStocke,
                CASE WHEN EXISTS(
                    SELECT DA_LIGNE.NO_PO
                    FROM DA_LIGNE with(nolock) 
                    WHERE DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                        AND rtrim(ltrim(DA_LIGNE.SITE_STK)) != \'\'
                ) THEN 1 ELSE 0 
                END as hasLigneStock,
                CASE WHEN EXISTS(
                    SELECT DA_LIGNE.NO_PO
                    FROM DA_LIGNE with(nolock) 
                    WHERE DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                        AND rtrim(ltrim(DA_LIGNE.SITE_STK)) = \'\'
                        AND DA_LIGNE.TRAITEE = 0x46
                ) THEN 1 ELSE 0 
                END as hasLigneNonStockeNonTraite,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasCommande,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    JOIN CA_ENTET with(nolock) 
                        ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                        AND CA_ENTET.DTE_VALID = \'00000000\'
                    WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasCommandeNonValide,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    JOIN CA_ENTET with(nolock) 
                        ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                        AND CA_ENTET.REFUSEE = 0x54
                    WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasCommandeRefuse,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    JOIN CA_ENTET with(nolock) 
                        ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                        AND CA_ENTET.SIGNEE = 0x46
                    WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasCommandeNonSigne,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    JOIN CA_ENTET with(nolock) 
                        ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                        AND CA_ENTET.ETAT_CDE IN (\'E\',\'S\')
                    WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasCommandeCommence,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    JOIN CA_ENTET with(nolock) 
                        ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                        AND CA_ENTET.ETAT_CDE NOT IN (\'E\',\'S\')
                    WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasCommandeNonCommence,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    JOIN CA_ENTET with(nolock) 
                        ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                        AND CA_ENTET.ETAT_CDE != \'S\'
                    WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasCommandeNonSolde,
                
                CASE WHEN EXISTS(
                    SELECT distinct SBS_LIGN.NO_BS 
                    FROM SBS_LIGN with(nolock) 
                    JOIN SDS_LIGN with(nolock) 
                        ON SDS_LIGN.NO_DS = SBS_LIGN.NO_DS
                        AND SDS_LIGN.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasBonSortie,
                CASE WHEN EXISTS(
                    SELECT SBS_ENTE.NO_BS 
                    FROM SBS_ENTE with(nolock) 
                    JOIN SBS_LIGN with(nolock) 
                        ON SBS_LIGN.NO_BS = SBS_ENTE.NO_BS
                    JOIN SDS_LIGN with(nolock) 
                        ON SDS_LIGN.NO_DS = SBS_LIGN.NO_DS
                        AND SDS_LIGN.NO_DA = DA_ENTET.NO_DA
                    AND SBS_ENTE.DTE_VALID = \'00000000\'
                ) THEN 1 ELSE 0 
                END as hasBonSortieNonValide,

                CASE WHEN EXISTS(
                    SELECT FAC_LIG.NUM_FACT, CA_LIGNE.NO_CDE 
                    FROM FAC_LIG with(nolock)
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    
                    UNION
                    
                    SELECT FAC_LIG.NUM_FACT, CA_LIGNE.NO_CDE 
                    FROM FAC_LIG with(nolock)
                    JOIN EXPENTET with(nolock) ON EXPENTET.NUM_EXP = FAC_LIG.NUM_EXP
                    JOIN DETCOLIS with(nolock) ON DETCOLIS.NUM_EXP = EXPENTET.NUM_EXP 
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasFacExp,

                CASE WHEN EXISTS(
                    SELECT FAC_LIG.*
                    FROM FAC_LIG with(nolock) 
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    JOIN CA_ENTET with(nolock) 
                        ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE 
                        AND CA_ENTET.ETAT_FIN <> \'F\'
                    
                    UNION

                    SELECT FAC_LIG.*
                    FROM FAC_LIG with(nolock)
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    JOIN DETCOLIS with(nolock) 
                        ON DETCOLIS.NO_CDE = CA_LIGNE.NO_CDE
                    JOIN EXPENTET with(nolock) 
                        ON EXPENTET.NUM_EXP = DETCOLIS.NUM_EXP
                        AND EXPENTET.ETATEXP <> \'S\' 
                ) THEN 1 ELSE 0 
                END as hasFacExpNonSolde,
                CASE WHEN EXISTS(
                    SELECT DETCOLIS.NO_CDE
                    FROM DETCOLIS with(nolock)
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasExp,
                CASE WHEN EXISTS(
                    SELECT DETCOLIS.NUM_EXP
                    FROM DETCOLIS with(nolock)
                    JOIN EXPENTET with(nolock) 
                        ON EXPENTET.NUM_EXP = DETCOLIS.NUM_EXP
                        AND EXPENTET.ETATEXP NOT IN (\'A\',\'S\') 
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasExpNonArrive,

                CASE WHEN EXISTS(
                    SELECT FAC_LIG.NUM_FACT , FAC_LIG.NUM_EXP, FAC_LIG.NUM_CDE
                    FROM FAC_LIG with(nolock)
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA

                    UNION

                    SELECT FAC_LIG.NUM_FACT, FAC_LIG.NUM_EXP, FAC_LIG.NUM_CDE
                    FROM FAC_LIG with(nolock)
                    JOIN DETCOLIS with(nolock) ON DETCOLIS.NUM_EXP = FAC_LIG.NUM_EXP
                    JOIN CA_LIGNE with(nolock) 
                        ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                        AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                ) THEN 1 ELSE 0 
                END as hasBap,
                CASE WHEN EXISTS(
                    SELECT FAC_ENT.NUM_FACT
                    FROM FAC_ENT with(nolock)
                    JOIN (
                        SELECT FAC_LIG.NUM_FACT
                        FROM FAC_LIG with(nolock)
                        JOIN CA_LIGNE with(nolock) 
                            ON CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE
                            AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA

                        UNION

                        SELECT FAC_LIG.NUM_FACT
                        FROM FAC_LIG with(nolock)
                        JOIN DETCOLIS with(nolock) ON DETCOLIS.NUM_EXP = FAC_LIG.NUM_EXP
                        JOIN CA_LIGNE with(nolock) 
                            ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                            AND CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    ) as FacLigne ON FacLigne.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE FAC_ENT.BON_A_PAY = 0x54
                        AND FAC_ENT.DATE_BAP = \'00000000\'
                ) THEN 1 ELSE 0 
                END as hasBapNonValide
            FROM DA_ENTET  with (nolock)
            LEFT JOIN CENTRE with (nolock) ON CENTRE.CODE_CTR = DA_ENTET.CTR_LIV
            WHERE DA_ENTET.NO_DA = :demId',
            ['demId' => $demId]
        );
    }

    /**
     * @param array<string> $listDemAchatId
     * @return array<array<mixed>>
     */
    public function listSignat(array $listDemAchatId) :array
    {
        if (empty($listDemAchatId)) {
            return [];
        }
        $param = [];

        return $this->fetchAssocAll(
            "SELECT 
                SIG_DA.NO_DA,
                HASTUTIL.CODE,
                HASTUTIL.NOM,
                SIG_DA.ORDRE, 
                SIG_DA.DTE_MISE,
                DAROLES as ROLES,
                INFORME
            FROM SIG_DA with (nolock)
            LEFT JOIN HASTUTIL with (nolock) ON SIG_DA.SIGNAT = HASTUTIL.CODE
            WHERE ".$this->makeWhereIn('SIG_DA.NO_DA', $listDemAchatId, $param)."
            ORDER BY SIG_DA.ORDRE ",
            $param
        );
    }

    /**
     * @TODO factorize with WorkflowCommandeRepo::listDemAchatEvent
     * @param array<string> $listDemAchatId
     * @return array<array<string>>
     */
    public function listDemAchatEvent(array $listDemAchatId, bool $signature) :array
    {
        $param = [];
        return $this->fetchAssocAll(
            'SELECT 
                DA_EVENT.*,'
                .$this->selectTrad('SUIDADAT.LIBELLE', 'LIBELLE').',
                Signataire.NOM as Signataire_NOM,
                MEMOS.CONTMEMO as Comment_CONTMEMO,
                Auteur.NOM as Auteur_NOM
            FROM DA_EVENT with(nolock) 
            LEFT JOIN SUIDADAT with(nolock) ON DA_EVENT.CODE = SUIDADAT.CODE_SDA '
            . $this->joinTrad('SUIDADAT.LIBELLE', $param)
            ." LEFT JOIN HASTUTIL as Auteur with(nolock) ON Auteur.CODE = DA_EVENT.UTIL
            LEFT JOIN HASTUTIL as Signataire with(nolock) ON Signataire.CODE = DA_EVENT.SIGNAT
            LEFT JOIN MEMOS with(nolock) ON MEMOS.NODEMEMO = DA_EVENT.NM_COMME
            WHERE ".$this->makeWhereIn('DA_EVENT.NO_DA', $listDemAchatId, $param)."  
                AND DA_EVENT.CODE ".($signature ? "IN" : "NOT IN")
                    ." ('SDA','DAR','SCH','ESI','ESO','ESR','INF','ERS','DAM','CAM')
            ORDER BY DA_EVENT.NUM_ORD DESC",
            $param
        );
    }

    /**
     * @return array<mixed>
     */
    public function getDemAchatWithListEvent(string $demandeId) :array
    {
        $entity = (new DaEntet($this->pdo))->find($demandeId);
        $entity['Demandeur_NOM'] =
            (new Hastutil($this->pdo))->find($entity['NO_DEM'])['NOM'] ?? '';
        $entity['listEvent'] = $this->listDemAchatEvent([$demandeId], false);
        return $entity;
    }

    /**
     * @return array<mixed>
     */
    public function getDemAchatApproWithListEvent(string $demandeId) :array
    {
        $entity = (new DaEntet($this->pdo))->find($demandeId);
        $entity['Demandeur_NOM'] =
            (new Hastutil($this->pdo))->find($entity['NO_DEM'])['NOM'] ?? '';
        $entity['listEvent'] = $this->listDemAchatEvent([$demandeId], true);
        $entity['listSignat'] = $this->listSignat([$demandeId]);
        return $entity;
    }

    /**
     * @return array<mixed>
     */
    public function listCommandeWithListEvent(string $demandeId) :array
    {
        $listCommande = $this->listCommandeAssociated($demandeId);

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

        // Load commande.listEvent[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeEvent(
                    array_column($listCommande, 'NO_CDE'),
                    'not_signature'
                ),
            'NO_CDE',
            'listEvent'
        );
            
        return $listCommande;
    }

    /**
     * @return array<mixed>
     */
    public function listCommandeApproWithListEvent(string $demandeId) :array
    {

        $listCommande = $this->listCommandeAssociated($demandeId);

        if (empty($listCommande)) {
            return [];
        }
        
        // Load commande.listEvent[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeEvent(
                    array_column($listCommande, 'NO_CDE'),
                    'not_signature'
                ),
            'NO_CDE',
            'listEvent'
        );

        // Load commande.listSignat[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeSignat(array_column($listCommande, 'NO_CDE')),
            'NO_CDE',
            'listSignat'
        );
            
        return $listCommande;
    }

    /**
     * @return array<mixed>
     */
    public function listCommandeEnvoiWithListEvent(string $demandeId) :array
    {
        $listCommande = $this->listCommandeAssociated($demandeId);

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

        // Load commande.listEvent[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeEvent(
                    array_column($listCommande, 'NO_CDE'),
                    'envoi'
                ),
            'NO_CDE',
            'listEvent'
        );
        
        return $listCommande;
    }

    /**
     * Load :
     * - Commande[].Fournisseur_CODE
     * - Commande[].Fournisseur_RS
     * - Commande[].Acheteur_CODE
     * - Commande[].Acheteur_NOM
     * @return array<array<mixed>>
     */
    private function listCommandeAssociated(string $demandeId) :array
    {
        return $this->fetchAssocAll(
            'SELECT DISTINCT 
                CA_ENTET.*,
                FOURNISS.CODE as Fournisseur_CODE,
                FOURNISS.RS as Fournisseur_RS,
                Acheteur.CODE as Acheteur_CODE,
                Acheteur.NOM as Acheteur_NOM
            FROM CA_ENTET with(nolock)
            JOIN CA_LIGNE with(nolock) 
                ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE 
                AND CA_LIGNE.NO_DA = :demandeId
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            JOIN HASTUTIL as Acheteur with(nolock) ON Acheteur.CODE = CA_ENTET.ACH
            ORDER BY CA_ENTET.NO_CDE',
            ['demandeId' => $demandeId]
        );
    }

    
    /**
     * @return array<array<mixed>>
     */
    public function listBonRecepWithListEvent(string $demandeId) :array
    {
        $listBonRecep = $this->listBonRecepAssociated($demandeId);
        
        if (empty($listBonRecep)) {
            return [];
        }
        $this->arrayJoinManyToMany(
            $listBonRecep,
            (new WorkflowBonRecepRepo($this->pdo))
                ->listBonRecepEvent(array_column($listBonRecep, 'NUM_BR')),
            'NUM_BR',
            'listEvent'
        );
            
        return $listBonRecep;
    }

    /**
     * @return array<array<mixed>>
     */
    private function listBonRecepAssociated(string $demandeId) :array
    {
        return $this->fetchAssocAll(
            'SELECT DISTINCT BR_RECEP.*
            FROM BR_RECEP with(nolock)
            JOIN CA_LIGNE with(nolock) 
                ON CA_LIGNE.NO_CDE = BR_RECEP.NUM_CDE
                AND CA_LIGNE.NO_DA = :demandeId',
            ['demandeId' => $demandeId]
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function listExpedition(string $demandeId) :array
    {
        return $this->fetchAssocAll(
            'SELECT DISTINCT EXPENTET.* 
            FROM EXPENTET with(nolock) 
            JOIN DETCOLIS with(nolock) 
                ON DETCOLIS.NUM_EXP = EXPENTET.NUM_EXP 
            JOIN CA_LIGNE with(nolock) 
                ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                AND CA_LIGNE.NO_DA = :demandeId',
            ['demandeId' => $demandeId]
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function listFactureExpWithEvent(string $demandeId) :array
    {
        $listFactureExp = $this->listFactureAssociated($demandeId);
        if (empty($listFactureExp)) {
            return [];
        }

        $this->arrayJoinManyToMany(
            $listFactureExp,
            (new WorkflowBapRepo($this->pdo))
                ->listFactureEvent(array_column($listFactureExp, 'NUM_FACT')),
            'NUM_FACT',
            'listEvent'
        );

        foreach ($listFactureExp as &$facture) {
            $facture['urlImageFacture'] = ImageFacture::getUrlImageFacture($facture);
        }
        
        return $listFactureExp;
    }

    /**
     * @return array<array<mixed>>
     */
    public function listFactureExpApproWithEvent(string $demandeId) :array
    {
        $listFactureExp = $this->listFactureAssociated($demandeId);
        if (empty($listFactureExp)) {
            return [];
        }
        $listFactureId = array_column($listFactureExp, 'NUM_FACT');

        $this->arrayJoinManyToMany(
            $listFactureExp,
            (new WorkflowBapRepo($this->pdo))
                ->listFactureEvent($listFactureId, true),
            'NUM_FACT',
            'listEvent'
        );

        $this->arrayJoinManyToMany(
            $listFactureExp,
            (new WorkflowBapRepo($this->pdo))
                ->listFactureSignat($listFactureId),
            'NUM_FACT',
            'listSignat'
        );

        foreach ($listFactureExp as &$facture) {
            $facture['urlImageFacture'] = ImageFacture::getUrlImageFacture($facture);
        }
        
        return $listFactureExp;
    }

    /**
     * @return array<array<mixed>>
     */
    private function listFactureAssociated(string $demandeId) :array
    {

        return $this->fetchAssocAll(
            'SELECT distinct 
                FAC_LIG.NUM_EXP,
                FAC_ENT.*,
                FOURNISS.CODE as Fournisseur_CODE,
                FOURNISS.RS as Fournisseur_RS
            FROM (
                SELECT FAC_LIG.NUM_FACT, FAC_LIG.NUM_EXP
                FROM FAC_LIG with(nolock) 
                JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE
                WHERE CA_LIGNE.NO_DA = :demandeId_0

                UNION

                SELECT FAC_LIG.NUM_FACT, FAC_LIG.NUM_EXP
                FROM FAC_LIG with(nolock)
                JOIN EXPENTET with(nolock) ON EXPENTET.NUM_EXP = FAC_LIG.NUM_EXP
                JOIN DETCOLIS with(nolock) ON DETCOLIS.NUM_EXP = EXPENTET.NUM_EXP
                JOIN CA_LIGNE with(nolock) 
                    ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                    AND CA_LIGNE.NO_DA = :demandeId_1
            ) as FAC_LIG
            JOIN FAC_ENT with(nolock) ON FAC_ENT.NUM_FACT = FAC_LIG.NUM_FACT
            LEFT JOIN FOURNISS with(nolock) ON FOURNISS.CODE = FAC_ENT.CODE_FOUR',
            [
                'demandeId_0' => $demandeId,
                'demandeId_1' => $demandeId,
            ]
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function listBonSortie(string $demandeId) :array
    {
        $list = $this->fetchAssocAll(
            'SELECT DISTINCT SBS_ENTE.* 
            FROM SBS_ENTE with(nolock) 
            JOIN SBS_LIGN with(nolock) 
                ON SBS_LIGN.NO_BS = SBS_ENTE.NO_BS
            JOIN SDS_LIGN with(nolock) 
                ON SDS_LIGN.NO_DS = SBS_LIGN.NO_DS
                AND SDS_LIGN.NO_DA = :demandeId',
            ['demandeId' => $demandeId]
        );

        return $list;
    }
}
