<?php

namespace Modules\DemandeAchat\Repository;

use Entity\Model\DaPlann;
use Entity\Model\Hastutil;
use Helpers\Outil\MemoizeTrait;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\Pagination\PaginatorGestion;
use Helpers\Parametres\Constante;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\QueryBuilder\AbsRepositoryExport;
use Helpers\User\User;
use Modules\GlobalApplication\Repository\LicenceOxalys;
use Modules\Suivi\Repository\SuiviDaRepo;

class DemandeAchat extends AbsRepositoryExport
{
    use MemoizeTrait;
    
    /**
     * Summary of mapEtatSqlExpr
     * @var string[]
     */
    private array $mapEtatSqlExpr;

    public function __construct(\PDO $pdo)
    {

        parent::__construct($pdo);

        $this->mapEtatSqlExpr = self::getMapEtatSqlExpr();
    }

    /**
     * @return array<string,string>
     */
    public static function getMapEtatSqlExpr()
    {
        $sqlHasAoEnCour = <<<SQL
            EXISTS (
                SELECT 1 FROM AoLigneInfo
                WHERE AoLigneInfo.NO_DA = DA_ENTET.NO_DA
                    AND AoLigneInfo.DTE_VALID_AO != '00000000'
                    AND AoLigneInfo.VALCHXFOU = '00000000'
            ) 
        SQL;

        // toutes lignes ont une consultation terminé
        $sqlHasAllAoTerminer = <<<SQL
            NOT EXISTS(
                -- Select all DaLigne with no Consult or a Consult still pending
                SELECT 1 
                FROM DA_LIGNE with(nolock)
                LEFT JOIN AoLigneInfo
                    ON AoLigneInfo.NO_DA = DA_LIGNE.NO_DA
                    AND AoLigneInfo.NO_PO = DA_LIGNE.NO_PO
                WHERE DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    AND ( 
                        AoLigneInfo.NO_AO IS NULL 
                        OR AoLigneInfo.VALCHXFOU = '00000000'
                    )
            )
        SQL;

        return [ //!\\ Order is important
            'R' => // Refusée
                " DA_ENTET.REFUSEE = 0x54 ",
            'T' => // Traitée
                " DA_ENTET.TRAITEE = 0x54 AND DA_ENTET.REFUSEE = 0x46 ",
            'consult_terminer' => // Consultation terminé
                " DA_ENTET.SIGNEE = 0x54 AND DA_ENTET.TRAITEE = 0x46 "
                . " AND $sqlHasAllAoTerminer AND DA_ENTET.CHIF_EXEC = 'C' /* Chiffrage */",
            'a_commander' => // A commander
                " DA_ENTET.SIGNEE = 0x54 AND DA_ENTET.TRAITEE = 0x46 "
                . " AND $sqlHasAllAoTerminer AND DA_ENTET.CHIF_EXEC = 'E' /* Execution */ ",
            'AT' => // A traiter
                " DA_ENTET.SIGNEE = 0x54 AND DA_ENTET.TRAITEE = 0x46 "
                . " AND NOT $sqlHasAoEnCour ",
            'ao_pending' => // Consultation en cours
                " DA_ENTET.SIGNEE = 0x54 AND DA_ENTET.TRAITEE = 0x46 "
                . " AND $sqlHasAoEnCour ",
            'ES' => // En signature
                " DA_ENTET.VALIDITE = 0x54 AND DA_ENTET.SIGNEE = 0x46 AND DA_ENTET.TRAITEE = 0x46 AND DA_ENTET.REFUSEE = 0x46",
            'AV' => // A valider
                " DA_ENTET.REFUSEE = 0x46 AND DA_ENTET.TRAITEE = 0x46 "
                . " AND DA_ENTET.VALIDITE = 0x46 AND DA_ENTET.SIGNEE = 0x46 ",
        ];
    }

    /**
     * listeDemandeAchat
     * @param array<mixed> $post
     * @return array<array<string>>
     */
    public function listeDemandeAchat(array $post): ?array
    {
        $q = $this->getMainQuery($post);

        (new PaginatorGestion())
            ->injecterSqlCount($q->query, $post, $q->param, $this->pdo);
            
        return $this->fetchAssocAll($q->query, $q->param);
    }

    /**
     * countDemandeAchat
     * @param array<mixed> $post
     */
    public function countDemandeAchat($post) :int
    {
        return $this->memoize('DemandeAchat::countDemandeAchat', ['post' => $post], function ($post) {
            $q = $this->getMainQuery($post, ' count(DISTINCT DA_ENTET.NO_DA)  as TOTAL');
            return intval($this->fetchAssocOne($q->query, $q->param)['TOTAL'] ?? 0);
        });
    }

    /**
     * listeDemandeAchat
     * @param array<mixed> $post
     * @param array<string> $select
     * @return iterable<array<string>>
     */
    public function export(array $post, array $select)
    {
        $q = $this->getMainQuery($post, $select);

        // TOOD : why add paginator on an export ?
        (new PaginatorGestion())
            ->injecterSqlCount($q->query, $post, $q->param, $this->pdo, true);

        return $this->createStatement($q->query, $q->param);
    }

    /**
     * listeDemandeAchat
     * @param array<mixed> $post
     * @param array<mixed>|string $listField
     * @return \stdClass{query:string,params:array<mixed>}
     */
    public function getMainQuery(array $post, $listField = null): object
    {
        $whereClause = '';

        if (empty($listField)) {
            $select = " DISTINCT
                DA_ENTET.NO_DA,
                DA_ENTET.CODE_STE,
                DA_ENTET.FOU,
                DA_ENTET.NO_DEM,
                DA_ENTET.DTE_EMISS,
                DA_ENTET.DTE_VAL,
                DA_ENTET.TOTAL_DA,
                DA_ENTET.DEVISE,
                DA_ENTET.SIGNEE,
                DA_ENTET.REFUSEE,
                DA_ENTET.TRAITEE,
                DA_ENTET.VALIDITE,
                DA_ENTET.CHIF_EXEC,
                DA_ENTET.CTR_LIV,
                DA_ENTET.DAENT_CENTRE_EMETTEUR,
                DA_ENTET.FM_CODE,
                DA_ENTET.NOCDEDIR,
                HASTUTIL.NOM,
                SOCIETE.RAIS_SOC,
                SOCIETE.SUIVI_BPA,
                FOURNISS.RS,
                MEMOS.CONTMEMO COMMENTAIRE,
                memoTitle.CONTMEMO TITRE,
                FOUDACDE.COD_TAC, 
                ( select count(*) from TP_ENT with (nolock) where TP_ENT.NUM_CDE = DA_ENTET.NO_DA and TP_ENT.TYPEOBJ = 'DA_ENTET') TOT_TP,
                CASE "
                . implode(PHP_EOL, array_map(
                    fn($sqlExpr, $key) => 'WHEN '. $sqlExpr . ' THEN ' . Sql::quoteString($key),
                    $this->mapEtatSqlExpr,
                    array_keys($this->mapEtatSqlExpr)
                ))
                . "
                END ETAT,
                (SELECT COUNT(*) FROM SIG_DA WITH (NOLOCK) WHERE DA_ENTET.NO_DA = SIG_DA.NO_DA AND SIG_DA.SIGNAT = '" .
                $_SESSION['Session_Login'] .
                "' AND SIG_DA.SIGNABLE = 'T') signable,
                (SELECT COUNT(*) 
                    FROM DCC_LIEN WITH (NOLOCK) 
                    WHERE DCC_LIEN.COD_ENR = RIGHT('000000000' + LTRIM(RTRIM(DA_ENTET.NO_DA)), 9)
                    AND DCC_LIEN.TYPEOBJ = 'DA_ENTET') dcc,
                CASE WHEN EXISTS (SELECT CA_LIGNE.NO_DA FROM CA_LIGNE WITH (NOLOCK) WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA)
                    THEN 1 ELSE 0 END as nbCommande,
                CASE WHEN EXISTS (SELECT COD_ENR FROM DOC_ATT WITH (NOLOCK) WHERE NO_DA = 'DA_ENTET' AND COD_ENR = RIGHT('000000000' + LTRIM(RTRIM(DA_ENTET.NO_DA)), 9))
                    THEN 1 ELSE 0 END as DOC_ATT,
                (SELECT COUNT(*) FROM DA_PIJO WITH (NOLOCK) WHERE NO_DA = DA_ENTET.NO_DA) NB_PIJO,
                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,
                (
                    SELECT COUNT(DISTINCT AoLigneInfo.NO_AO) 
                    FROM AoLigneInfo
                    WHERE AoLigneInfo.NO_DA = DA_ENTET.NO_DA
                ) as countConsult
                
            ";
        } elseif (!is_array($listField)) {
            $select = $listField;
        } else {
            $sel = $this->processSpecialField($listField);
            SuiviDaRepo::jointureExport($this->pdo, $sel->join);
            $select = $this->stringifySelect($sel->select);
        }

        $query =
            "WITH AoLigneInfo as (
                SELECT 
                    DA_LIGNE.NO_DA,
                    DA_LIGNE.NO_PO,
                    AO_ENTET.NO_AO,
                    AO_ENTET.DTE_VALID_AO,
                    AO_ENTET.VALCHXFOU
                FROM DA_LIGNE with(nolock)
                JOIN AO_LIGNE with(nolock) 
                    ON AO_LIGNE.NO_DA = DA_LIGNE.NO_DA
                    AND AO_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO
                JOIN AO_ENTET with(nolock)
                    ON AO_ENTET.NO_AO = AO_LIGNE.NO_AO
            )
            
            SELECT $select
            FROM DA_ENTET WITH (NOLOCK)
                LEFT JOIN SOCIETE WITH (NOLOCK) ON SOCIETE.CODE_STE = DA_ENTET.CODE_STE
                LEFT JOIN HASTUTIL WITH (NOLOCK) ON HASTUTIL.CODE = DA_ENTET.NO_DEM
                LEFT JOIN FOURNISS WITH (NOLOCK) ON FOURNISS.CODE = DA_ENTET.FOU
                LEFT JOIN MEMOS WITH (NOLOCK) ON MEMOS.NODEMEMO = DA_ENTET.NM_COMMEN
                LEFT JOIN MEMOS memoTitle WITH (NOLOCK) ON memoTitle.NODEMEMO = DA_ENTET.NM_TITRE
                LEFT JOIN FOUDACDE WITH (NOLOCK) ON FOUDACDE.NO_DA = DA_ENTET.NO_DA
                LEFT JOIN DA_LIGNE WITH (NOLOCK) ON DA_LIGNE.NO_DA = DA_ENTET.NO_DA
                LEFT JOIN TYPDOC WITH (NOLOCK) ON DA_ENTET.TYP_DOC = TYPDOC.TYPE
                LEFT JOIN SIG_DA WITH (NOLOCK) 
                    ON DA_ENTET.NO_DA = SIG_DA.NO_DA 
                    AND SIG_DA.SIGNAT = :user
                    AND SIG_DA.SIGNABLE = 'T' "
            . implode(' ', $sel->join ?? [])
            . " WHERE TYPDOC.NIV_CONF <= :nivConf 
        ";
        
        $params = [
            ':user' => $_SESSION['Session_Login'],
            ':nivConf' => $_SESSION["Session_Login"] == 'OXAUSER'
                ? 99
                : User::getUser()->getHastutil()['NIV_CONF']
        ];

        $query .= Roles::getClausePerimetreSociete('DA_ENTET.CODE_STE', $params);
        $query .= Roles::getClauseSocieteOuvert2('SOCIETE', $params);

        //______________
        // Shorthand

        // DA without AO and assigned to nobody or the given Hastutil
        if (!empty($post['requireAOFrom'])) {
            $post['acheteur'] = [$post['requireAOFrom'], ''];
            $post['filter_ETAT'] = 'AT';
            $post['filter_hasAO'] = '0';
        }

        // DA with at least an AO which has been delegated to the given Demandeur by the Acheteur
        if (!empty($post['aoDelegatedTo'])) {
            $post['filter_egal_NO_DEM'] = $post['aoDelegatedTo'];
            $post['filter_hasAODelegated'] = '1';
        }

        if (isset($post['demandeur']) && $post['demandeur']) {
            $post['filter_egal_NO_DEM'] = $post['demandeur'];
        }

        //_______________

        if (isset($post['DELEG']) && $post['DELEG']) {
            $query .= " AND SIG_DA.SIGNAT = '" . $post['DELEG'] . "'";
        }

        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 = '')";
                }

                $whereClause .= " AND (FOUDACDE.COD_TAC = :typeAchat $reqTypAchDefaut)";
            } else {
                $whereClause .= " AND FOUDACDE.COD_TAC != :typeAchat";
            }
            $params[':typeAchat'] = $post['COD_TAC'];
        }

        if (!empty($post['filter_Signature'])) {
            $query .= " AND EXISTS (
                SELECT 1 FROM SIG_DA WITH (NOLOCK) 
                WHERE DA_ENTET.NO_DA = SIG_DA.NO_DA 
                AND SIG_DA.SIGNABLE = 'T'
                AND SIG_DA.INFORME = 0x46 
                AND SIG_DA.SIGNAT = " . Sql::quoteString($_SESSION['Session_Login']) . " 
            )";
            unset($post['filter_Signature']);
        }

        if (!empty($post['acheteur'])) {
            if (! is_array($post['acheteur'])) {
                $post['acheteur'] = [$post['acheteur']];
            }
            $query .= ' AND ' . $this->makeWhereIn(
                'DA_LIGNE.ACHETEUR',
                $post['acheteur'],
                $params,
                'acheteur_'
            ) . ' ';
        }
        if (isset($post['societe']) && $post['societe']) {
            $query .= ' AND DA_ENTET.CODE_STE = :societe ';
            $params['societe'] = $post['societe'];
        }

        if (isset($post['CHIF_EXEC']) && $post['CHIF_EXEC']) {
            $query .= ' AND DA_ENTET.CHIF_EXEC = :chifExec';
            $params['chifExec'] = $post['CHIF_EXEC'];
        }

        if (isset($post['filter_NO_DA'])) {
            $post['filter_DA_ENTET.NO_DA'] = $post['filter_NO_DA'];
            unset($post['filter_NO_DA']);
        }

        if (isset($post['filter_egal_CODE_STE']) && $post['filter_egal_CODE_STE']) {
            $query .= ' AND (DA_ENTET.CODE_STE LIKE :CODE_STE OR SOCIETE.RAIS_SOC = :CODE_STE2) ';
            $params[':CODE_STE'] = $post['filter_egal_CODE_STE'];
            $params[':CODE_STE2'] = $post['filter_egal_CODE_STE'];
            unset($post['filter_egal_CODE_STE']);
        }

        if (isset($post['TYP_DOC']) && $post['TYP_DOC']) {
            $query .= ' AND DA_ENTET.TYP_DOC = :TYP_DOC';
            $params['TYP_DOC'] = $post['TYP_DOC'];
        }

        if (isset($post['centreLivraison']) && $post['centreLivraison']) {
            $query .= ' AND DA_ENTET.CTR_LIV = :centreLivraison';
            $params['centreLivraison'] = $post['centreLivraison'];
        }
        if (isset($post['centreEmetteur']) && $post['centreEmetteur']) {
            $query .= ' AND DA_ENTET.DAENT_CENTRE_EMETTEUR = :centreEmetteur';
            $params['centreEmetteur'] = $post['centreEmetteur'];
        }

        if (isset($post['motCle']) && $post['motCle'] != '') {
            $tblMotCle = explode(' ', $post['motCle']);
            $reqMotCle = '';
            $reqMotCleComp = '';
            foreach ($tblMotCle as $value) {
                $value = addcslashes($value, '%_');
                $reqMotCle .= ' AND UPPER(dalMotCle.DES_SOMM) LIKE \'%' . strtoupper($value) . '%\'';
                $reqMotCleComp .= ' AND UPPER(dalMotCleComp.CONTMEMO) LIKE \'%' . strtoupper($value) . '%\'';
            }
            $query .= ' AND (DA_ENTET.NO_DA IN 
                (SELECT DISTINCT (dalMotCle.NO_DA) 
                FROM DA_LIGNE dalMotCle WITH (NOLOCK) 
                WHERE ' . substr($reqMotCle, 5) . ')';
            $query .= ' OR DA_ENTET.NO_DA IN 
                (SELECT DISTINCT (DA_LIGNE.NO_DA) 
                FROM DA_LIGNE WITH (NOLOCK) 
                    LEFT JOIN MEMOS dalMotCleComp 
                        ON dalMotCleComp.NODEMEMO = DA_LIGNE.NM_DCOMP 
                WHERE ' . substr($reqMotCleComp, 5) . '))';
        }
        
        // filtres sur les fournisseurs => il faut aussi chercher dans les lignes des DA
        if (isset($post['ACD']) && $post['ACD'] != '') {
            $operator = '<>';
            if ($post['ACDCond'] == 'egal') {
                $operator = '=';
            }
            $query .= " AND (DA_ENTET.ACD {$operator} :daEntetAcd OR UPPER(DA_LIGNE.ACD) {$operator} :daLigneAcd) ";
            $params['daEntetAcd'] = $params['daLigneAcd'] = $post['ACD'];
        }

        // filtres sur les fournisseurs => il faut aussi chercher dans les lignes des DA
        if (isset($post['FOU']) && $post['FOU'] != '') {
            $operator = '<>';
            if ($post['FOUCond'] == 'egal') {
                $operator = '=';
            }
            $query .= " AND (DA_ENTET.FOU {$operator} :daEntetFou OR UPPER(DA_LIGNE.CODE_FOU) {$operator} :daLigneFou) ";
            $params['daEntetFou'] = $params['daLigneFou'] = $post['FOU'];
        }


        //________________________
        // Column ambigu

        if (isset($post['filter_NO_DA'])) {
            $post['filter_DA_ENTET.NO_DA'] = $post['filter_NO_DA'];
            unset($post['filter_NO_DA']);
        }
        
        $post['tri_DA_ENTET.NO_DA'] = $post['tri_NO_DA'] ?? null;
        unset($post['tri_NO_DA']);

        if (isset($post['filter_egal_CODE_STE']) && $post['filter_egal_CODE_STE']) {
            $query .= ' AND (DA_ENTET.CODE_STE LIKE :CODE_STE OR SOCIETE.RAIS_SOC = :CODE_STE2) ';
            $params[':CODE_STE'] = $post['filter_egal_CODE_STE'];
            $params[':CODE_STE2'] = $post['filter_egal_CODE_STE'];
            unset($post['filter_egal_CODE_STE']);
        }

        //_______________

        if (array_key_exists('filter_hasAO', $post)) {
            $query .= " AND "
                . (Sql::toBool($post['filter_hasAO']) ? '' : 'NOT')
                ." EXISTS(
                    SELECT * FROM AO_LIGNE
                    WHERE AO_LIGNE.NO_DA = DA_LIGNE.NO_DA
                    AND AO_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO
                ) ";
            unset($post['filter_hasAO']);
        }

        // An AO is delegated to a Demandeur by the flag 'demDeleg' in EntityMeta
        if (array_key_exists('filter_hasAODelegated', $post)) {
            $query .= " AND "
                . (Sql::toBool($post['filter_hasAODelegated']) ? '' : 'NOT')
                ." EXISTS(
                    SELECT * FROM AO_LIGNE with(nolock)
                    JOIN ENTITYMETA with(nolock) 
                        ON ENTITY_TYPE = 'AO_E'
                        AND ENTITY_ID = AO_LIGNE.NO_AO
                        AND STRUCT_TYPE = '' -- Root
                        AND STRUCT_ID = 0
                        AND FIELD_KEY = 'demDeleg'
                        AND VALUE = 'T'
                    WHERE AO_LIGNE.NO_DA = DA_LIGNE.NO_DA
                    AND AO_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO
                ) ";
            unset($post['filter_hasAODelegated']);
        }

        if (isset($post['filter_ETAT']) && $post['filter_ETAT'] != '') {
            $query .= ' AND ' . ($this->mapEtatSqlExpr[ $post['filter_ETAT'] ] ?? '');
            unset($post['filter_ETAT']);
        }

        // filtres sur les fournisseurs => il faut aussi chercher dans les lignes des DA
        if (isset($post['filter_FOU' . Constante::spliter . 'RS']) && $post['filter_FOU' . Constante::spliter . 'RS'] != '') {
            $query .= ' AND (UPPER(DA_LIGNE.CODE_FOU) LIKE :daLigneFou OR UPPER(DA_ENTET.FOU) LIKE :daEntetFou OR UPPER(RS) LIKE :rs) ';
            $params['daLigneFou'] = $params['daEntetFou'] = $params['rs'] = $post['filter_FOU' . Constante::spliter . 'RS'];
            unset($post['filter_FOU' . Constante::spliter . 'RS']);
        }

        if ($post['termesDePaiement'] ?? null) {
            if ($post['termesDePaiement'] == 'O') {
                $query .= <<<SQL
                    AND EXISTS (
                        SELECT 1
                        FROM TP_EVT
                        WHERE TP_EVT.NUM_CDE = DA_ENTET.NO_DA
                        AND TP_EVT.TYPEOBJ = 'DA_ENTET'
                    )
                    SQL;
            } elseif ($post['termesDePaiement'] == 'N') {
                $query .= <<<SQL
                    AND NOT EXISTS (
                        SELECT 1
                        FROM TP_EVT
                        WHERE TP_EVT.NUM_CDE = DA_ENTET.NO_DA
                        AND TP_EVT.TYPEOBJ = 'DA_ENTET'
                    )
                    SQL;
            }
        }

        //filtre avancé sur les accords-cadres => à chercher aussi dans les lignes des DA
        foreach ($post as $key => $value) {
            if (strpos($key, 'fa-') === 0) {
                // TODO /!\ revoir les filtres avancés si chiffrage accepté pour pouvoir envoyer deux colonnes sur un filtre ( OR )
                if (is_string($value)) {
                    if (strpos($value, 'DA_ENTET.ACD') === 0) {
                        $filterKey = explode('-', $key)[1];
                        $operator = '<>';
                        if ($post['fa-' . $filterKey . '-2'] == 'egal') {
                            $operator = '=';
                        }
                        $query .= " AND (DA_ENTET.ACD {$operator} :daEntetAcd OR UPPER(DA_LIGNE.ACD) {$operator} :daLigneAcd) ";
                        $params['daEntetAcd'] = $params['daLigneAcd'] = $post['fa-' . $filterKey . '-3'];
                        unset($post['fa-' . $filterKey . '-1']);
                        unset($post['fa-' . $filterKey . '-2']);
                        unset($post['fa-' . $filterKey . '-3']);
                        if (is_array($post['idFiltreAvance'])) {
                            foreach ($post['idFiltreAvance'] as $k => $v) {
                                if ($v === $filterKey) {
                                    unset($post['idFiltreAvance'][$k]);
                                }
                            }
                        }
                    }
                }
            }
        }

        $query .= $whereClause;
        $query = $this->applyFilter($query, $params, $post);
        
        return (object) [
            'query' => $query,
            'param' => $params,
        ];
    }

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

        $paramListDeleg = [];
        foreach (array_values($listDeleg) as $k => $v) {
            $paramListDeleg[':param_' . $k] = $v;
        }
        $param = [];
        $req =
            'SELECT
                SIG_DA.SIGNAT,
                count(DA_ENTET.NO_DA) as count
            FROM SIG_DA WITH (NOLOCK)
            LEFT JOIN DA_ENTET WITH (NOLOCK) ON SIG_DA.NO_DA = DA_ENTET.NO_DA
            LEFT JOIN TYPDOC WITH (NOLOCK) ON DA_ENTET.TYP_DOC = TYPDOC.TYPE
            WHERE TYPDOC.NIV_CONF <= :nivConf 
            and DA_ENTET.SIGNEE = 0x46
            and SIG_DA.SIGNABLE= 0x54
            AND'
            . $this->makeWhereIn(
                'SIG_DA.SIGNAT',
                $listDeleg,
                $param
            );


        $userEntity = new Hastutil($this->pdo);
        $user = $userEntity->find($_SESSION["Session_Login"]);

        if (!Sql::boolSql($user['UTIMULTI'])) {
            $req .= " AND EXISTS (
                SELECT SOCIETE.CODE_STE
                FROM SOCIETE WITH (NOLOCK)
                LEFT JOIN SOC_USR WITH (NOLOCK)
                    ON SOC_USR.COD_STE = SOCIETE.CODE_STE
                    AND SOC_USR.COD_USR = :userSoc1
                LEFT JOIN HASTUTIL WITH (NOLOCK)
                    ON HASTUTIL.CODE_STE = SOCIETE.CODE_STE
                    AND HASTUTIL.CODE = :userSoc2
                WHERE (SOC_USR.COD_USR IS NOT NULL OR HASTUTIL.CODE IS NOT NULL)
                    AND (SOCIETE.DTEVALMIN = '00000000' OR SOCIETE.DTEVALMIN <= '".date('Ymd')."')
                    AND (SOCIETE.DTEVALMAX = '00000000' OR SOCIETE.DTEVALMAX >= '".date('Ymd')."')
                    AND SOCIETE.CODE_STE = DA_ENTET.CODE_STE
            )";

            $paramListDeleg[':userSoc1'] = $_SESSION["Session_Login"];
            $paramListDeleg[':userSoc2'] = $_SESSION["Session_Login"];
        }


        $req .= ' GROUP BY SIG_DA.SIGNAT';

        return $this->fetchAssocAll(
            $req,
            $paramListDeleg + [
                ':nivConf' => ($_SESSION["Session_Login"] == 'OXAUSER' ? 99 : $user['NIV_CONF']),
            ]
        );
    }

    public function countAwaitingSignat(string $userCode): int
    {
        $req = "SELECT COUNT( SIG_DA.SIGNAT ) as count
            FROM SIG_DA WITH (NOLOCK)
            JOIN DA_ENTET WITH (NOLOCK)
                ON DA_ENTET.NO_DA = SIG_DA.NO_DA
                AND DA_ENTET.SIGNEE = 0x46
            WHERE SIG_DA.INFORME = 0x46
                AND SIG_DA.SIGNAT = :signat
                AND SIG_DA.SIGNABLE = 0x54
            ";

        return $this->fetchAssocOne($req, [
            ':signat' => $userCode,
        ])['count'] ?? 0;
    }

    public function countAwaitingSignatDeleg(string $userCode): int
    {

        // TODO: factorize with listeAllDelegationDA
        $req =
            "SELECT COUNT(SIG_DELEG.SIGNAT) as count
        FROM SIG_DA with(nolock)
        JOIN DA_ENTET with(nolock)
            ON SIG_DA.NO_DA = DA_ENTET.NO_DA
            AND DA_ENTET.SIGNEE = 0x46
        JOIN SIG_DELEG with(nolock)
            ON SIG_DELEG.SIGNAT = SIG_DA.SIGNAT
            AND SIG_DELEG.DELEG = :signat
            AND SIG_DELEG.DTE_DEB <= :now_1
            AND (
                SIG_DELEG.DTE_FIN >= :now_2
                or SIG_DELEG.DTE_FIN = '00000000'
            )
        JOIN HASTUTIL hasig with(nolock)
            ON SIG_DELEG.SIGNAT = hasig.CODE
            AND hasig.SIG_DA = 0x54
        WHERE SIG_DA.SIGNABLE = 0x54";

        return $this->fetchAssocOne($req, [
            ':signat' => $userCode,
            ':now_1' => date('Ymd'),
            ':now_2' => date('Ymd'),
        ])['count'] ?? 0;
    }

    /**
     * demandeAchat
     * @param string $code
     * @return array<string>
     */
    public function demandeAchat(string $code): array
    {
        $siSecto = (Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')
            ? 'dae.CODE_STE'
            : "''"
        );
        $scaSecto = (Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')
            ? 'dae.CODE_STE'
            : "''"
        );
        $cdcSecto = (Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')
            ? 'dae.CODE_STE'
            : "''"
        );

        $req =
            "SELECT 
            dae.SIGNEE,
            dae.CHIF_EXEC, 
            dae.CTR_LIV, 
            dae.DAENT_CENTRE_EMETTEUR, 
            dae.ACD, 
            dae.SECTIMP,
            si.LIBELLE AS SI_LABEL, 
            cc.LIBELLE AS CDC_LABEL,
			sca.LIBELLE AS SCA_LABEL,
            dae.CODGES, 
            dae.LIBGES, 
            dae.CODECOMP, 
            dae.CODECOMP, 
            dae.SSCPTEANA, 
            dae.TOTAL_DA_TTC, 
            dae.DEVISE, 
            dae.TYP_DOC, 
            dae.ZONELIB,
            dae.NO_DA,
            CL.RAIS_SOC LIB_CTR_LIV, 
            CE.RAIS_SOC LIB_CTR_EMETT, 
            typd.LIBELLE AS TYP_DOC_LABEL, 
            acd.LIBELLE AS LIBELLE_ACD,
            DOSSIER.CONTMEMO LIB_DOSACHCOMPLET,
            DOSACHCOMPLET,
            SOCIETE.SUIVI_BPA
        FROM DA_ENTET dae WITH (NOLOCK)
            LEFT OUTER JOIN CENTRE CL WITH (NOLOCK) on CL.CODE_CTR = dae.CTR_LIV
            LEFT OUTER JOIN CENTRE CE WITH (NOLOCK) on CE.CODE_CTR = dae.DAENT_CENTRE_EMETTEUR
            LEFT OUTER JOIN TYPDOC typd WITH(NOLOCK) ON typd.TYPE = dae.TYP_DOC
            LEFT OUTER JOIN ACCCADRE acd WITH(NOLOCK) ON acd.CODE_ACD = dae.ACD
            LEFT JOIN SOCIETE WITH (NOLOCK) ON SOCIETE.CODE_STE = dae.CODE_STE
            LEFT OUTER JOIN SECTIMPU si WITH(NOLOCK) ON si.CODE_SI = dae.SECTIMP 
                AND si.CODE_STE = {$siSecto}
            LEFT OUTER JOIN CODECOMP cc WITH(NOLOCK) ON cc.CODE_CDC = dae.CODECOMP 
                AND cc.CODE_STE = {$cdcSecto}
            LEFT OUTER JOIN SSCPTANA sca WITH(NOLOCK) ON sca.CODE_SCA = dae.SSCPTEANA 
                AND sca.CODE_STE = {$scaSecto}
            LEFT JOIN FOUDACDE WITH (NOLOCK) ON FOUDACDE.NO_DA = dae.NO_DA 
                AND FOUDACDE.CODE_FOU = dae.FOU
            LEFT OUTER JOIN MEMOS DOSSIER WITH(NOLOCK) ON DOSSIER.NODEMEMO = FOUDACDE.NM_COMSUIVIACH
        WHERE dae.NO_DA = :value";

        $params = [':value' => $code];

        $da = $this->pdo->prepare($req);
        $da->execute($params);
        return $da->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param string $code
     * @return array<string>
     */
    public function demandeAchatDetail(string $code): array
    {
        $req = "SELECT 
                DA_ENTET.CHIF_EXEC, 
                DA_ENTET.CTR_LIV, 
                DA_ENTET.DAENT_CENTRE_EMETTEUR, 
                DA_ENTET.ACD, 
                DA_ENTET.SECTIMP, 
                DA_ENTET.CODGES, 
                DA_ENTET.LIBGES, 
                DA_ENTET.CODECOMP, 
                DA_ENTET.SSCPTEANA, 
                DA_ENTET.TOTAL_DA_TTC, 
                DA_ENTET.DEVISE, 
                DA_ENTET.FORMUL, 
                DA_ENTET.TYP_DOC, 
                DA_ENTET.ZONELIB,
                Demandeur.NOM as Demandeur_NOM,
                Societe.RAIS_SOC as Societe_RAIS_SOC,
                Centre.RAIS_SOC as Centre_RAIS_SOC
            FROM DA_ENTET WITH (NOLOCK)
            LEFT JOIN HASTUTIL as Demandeur WITH (NOLOCK) ON Demandeur.CODE = DA_ENTET.NO_DEM
            LEFT JOIN SOCIETE as Societe WITH (NOLOCK) ON Societe.CODE_STE = DA_ENTET.CODE_STE
            LEFT JOIN CENTRE as Centre WITH (NOLOCK) ON Centre.CODE_CTR = DA_ENTET.DAENT_CENTRE_EMETTEUR
            WHERE DA_ENTET.NO_DA = :value";

        $da = $this->pdo->prepare($req);
        $da->execute([':value' => $code]);
        return $da->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * listeLigneDemandeAchat
     * @param string $code
     * @return array<mixed>
     */
    public function listeLigneDemandeAchat(string $code, ?string $idLigne = null): ?array
    {
        if (Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $codeSteSi = "DA_LIGNE.CODE_STE";
        } else {
            $codeSteSi = "''";
        }
        if (Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')) {
            $codeSteScd = "DA_LIGNE.CODE_STE";
        } else {
            $codeSteScd = "''";
        }
        if (Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            $codeSteSca = "DA_LIGNE.CODE_STE";
        } else {
            $codeSteSca = "''";
        }

        $req =
            "SELECT DA_LIGNE.NO_DA, DA_LIGNE.NO_PO,  DA_LIGNE.ACD, DA_LIGNE.CODE_FOU, DA_LIGNE.CODE_STE, 
            DA_LIGNE.CODE_ART, DA_LIGNE.DES_SOMM, DA_LIGNE.SSFAM, DA_LIGNE.CTRL_BR, 
            DA_LIGNE.REF_FOUR, DA_LIGNE.COD_FAB, DA_LIGNE.COD_GAM, DA_LIGNE.REF_FAB, DA_LIGNE.TYPNMP,
            DA_LIGNE.QTE_DEM, DA_LIGNE.UNITE, DA_LIGNE.PU, DA_LIGNE.TX_REMISE, DA_LIGNE.BUDGET, DA_LIGNE.TVA, DA_LIGNE.TEMPINIT, 
            DA_LIGNE.DTELIVSOU, DA_LIGNE.CTRLIVR, DA_LIGNE.SITE_STK, DA_LIGNE.MOD_TRS,
            DA_LIGNE.SECTIMP, DA_LIGNE.CODE_COMP, DA_LIGNE.SSCPTEANA, DA_LIGNE.CODGES, DA_LIGNE.LIBGES,
            DA_LIGNE.RECEP_AUTO, DA_LIGNE.DATEDEBCONT, DA_LIGNE.DATEFINCONT, DA_LIGNE.CONTRAT_PERIOD, DA_LIGNE.FIN_DEB_PER, DA_LIGNE.DALI_RECEPAUTO_JOUR, 
            MEMOS.CONTMEMO DESIGNCOMP, DA_ENTET.DAENT_CENTRE_EMETTEUR DAENT_CENTRE_EMETTEUR, 
            ARTICLES.C_UNSPSC, ARTICLES.RECEP_AUTO ART_RECEP_AUTO, ARTICLES.PER_REC_AUTO ART_PER_REC_AUTO, 
            FOURNISS.RS,
            FOURNISS.RECEP_AUTO FOU_RECEP_AUTO, FOURNISS.PER_REC_AUTO FOU_PER_REC_AUTO,
            SECTIMPU.LIBELLE libelleSI, CODECOMP.LIBELLE libelleCDC, SSCPTANA.LIBELLE libelleSCA,
            (SELECT COUNT(*) FROM CA_LIGNE WITH (NOLOCK) WHERE CA_LIGNE.NO_DA = DA_ENTET.NO_DA) nbCommande,
            (SELECT TOP 1 COUNT(*) FROM CA_LIGNE WITH (NOLOCK) WHERE CA_LIGNE.NO_DA = DA_LIGNE.NO_DA AND CA_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO) nbLignesCde, 
            (SELECT TOP 1 COUNT(*) FROM AO_LIGNE WITH (NOLOCK) WHERE AO_LIGNE.NO_DA = DA_LIGNE.NO_DA AND AO_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO) nbLignesAO,
            (
                SELECT COUNT(*) 
                FROM DA_PLANN WITH (NOLOCK) 
                WHERE NO_DA = DA_LIGNE.NO_DA 
                    AND NO_PO = DA_LIGNE.NO_PO 
            ) PLANNING
        FROM DA_LIGNE WITH (NOLOCK) 
            INNER JOIN DA_ENTET WITH (NOLOCK) on DA_LIGNE.NO_DA=DA_ENTET.NO_DA
            LEFT JOIN MEMOS WITH (NOLOCK) on MEMOS.NODEMEMO = DA_LIGNE.NM_DCOMP 
            LEFT JOIN ARTICLES WITH (NOLOCK) on ARTICLES.CODE_ART = DA_LIGNE.CODE_ART 
            LEFT JOIN FOURNISS WITH (NOLOCK) on FOURNISS.CODE = DA_LIGNE.CODE_FOU 
            LEFT JOIN SECTIMPU WITH (NOLOCK) on SECTIMPU.CODE_SI = DA_LIGNE.SECTIMP AND SECTIMPU.CODE_STE={$codeSteSi}
            LEFT JOIN CODECOMP WITH (NOLOCK) on CODECOMP.CODE_CDC = DA_LIGNE.CODE_COMP AND CODECOMP.CODE_STE={$codeSteScd}
            LEFT JOIN SSCPTANA WITH (NOLOCK) on SSCPTANA.CODE_SCA = DA_LIGNE.SSCPTEANA  AND SSCPTANA.CODE_STE={$codeSteSca}
        WHERE DA_LIGNE.NO_DA = :value ";
        

        $params = [':value' => $code];

        if ($idLigne) {
            $req .= " and DA_LIGNE.NO_PO = :idLigne";
            $params[':idLigne'] = $idLigne;
        }

        $req .= " ORDER BY NO_PO";

        if ($idLigne) {
            return $this->fetchAssocOne($req, $params);
        } else {
            return $this->fetchAssocAll($req, $params);
        }
    }


    /**
     * demandeAchatEntete
     * @param string $code
     * @return array<mixed>
     */
    public function demandeAchatEntete(string $code): ?array
    {
        if (Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $codeSteSi = "DA_ENTET.CODE_STE";
        } else {
            $codeSteSi = "''";
        }
        if (Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')) {
            $codeSteCdc = "DA_ENTET.CODE_STE";
        } else {
            $codeSteCdc = "''";
        }
        if (Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            $codeSteSca = "DA_ENTET.CODE_STE";
        } else {
            $codeSteSca = "''";
        }

        $req =
            "SELECT DA_ENTET.NO_DA, DA_ENTET.CODE_STE, DA_ENTET.FOU, DA_ENTET.CHIF_EXEC, DA_ENTET.DTE_EMISS, DA_ENTET.DTE_VAL, 
            DA_ENTET.TYP_DOC, DA_ENTET.TOTAL_DA, DA_ENTET.TOTAL_DA_TTC, DA_ENTET.DEVISE, DA_ENTET.NM_COMMEN, DA_ENTET.NM_TITRE, 
            DA_ENTET.SECTIMP, DA_ENTET.CODGES, DA_ENTET.LIBGES, DA_ENTET.ID_CG, DA_ENTET.CODECOMP, DA_ENTET.SSCPTEANA, 
            DA_ENTET.NO_DEM, DA_ENTET.BUDGETDA, DA_ENTET.ZONELIB, DA_ENTET.COD_LNG, DA_ENTET.NOCDISDA,
            DA_ENTET.ACD, DA_ENTET.ADRFOU, DA_ENTET.VILFOU, DA_ENTET.PAYSFOU, DA_ENTET.CPFOU, DA_ENTET.VILLEFOU,
            DA_ENTET.CTCT_FOU, DA_ENTET.TEL_CTCT, DA_ENTET.POR_CTCT, DA_ENTET.FAX_CTCT, DA_ENTET.EMA_CTCT, 
            DA_ENTET.OBJET_DA, DA_ENTET.CTR_LIV, DA_ENTET.ADRLIV, DA_ENTET.VILLIV, DA_ENTET.PAYSLIV, DA_ENTET.CPLIV, DA_ENTET.VILLELIV,
            DA_ENTET.NMCTC1L, DA_ENTET.FTCTC1L, DA_ENTET.TLCTC1L, DA_ENTET.PRCTC1L, DA_ENTET.FXCTC1L, DA_ENTET.MLCTC1L, 
            DA_ENTET.DAENT_CENTRE_EMETTEUR, DA_ENTET.COND_PAIE, DA_ENTET.MODE_PAIE, DA_ENTET.MODE_LIV, DA_ENTET.DTE_LIV, DA_ENTET.CODE_MESS, 
            DA_ENTET.MOD_TRS, DA_ENTET.TYPNMP, DA_ENTET.VALIDITE, DA_ENTET.TRAITEE, DA_ENTET.SIGNEE,
            FOURNISS.RS, memosComment.CONTMEMO COMMENTAIRE, memosTitre.CONTMEMO TITRE, SOCIETE. DEVISE deviseSte,
            SECTIMPU.LIBELLE libelleSI, CODECOMP.LIBELLE libelleCDC, SSCPTANA.LIBELLE libelleSCA,
            SIG_DA.SIGNABLE,
            MODELIVR.LIBELLE LIB_MODE_LIV, MODEPAIE.LIBELLE LIB_MODE_PAIE, CONDPAIE.LIBELLE LIB_COND_PAIE,
            CL.RAIS_SOC LIB_CTR_LIV, CE.RAIS_SOC LIB_CTR_EMETT,
            CL.TRS_INTER as CentreLivr_TRS_INTER,
            DOSSIER.CONTMEMO LIB_DOSACHCOMPLET,
            DOSACHCOMPLET,
            SOCIETE.SUIVI_BPA
        FROM DA_ENTET WITH (NOLOCK)
            LEFT JOIN FOURNISS WITH (NOLOCK) on DA_ENTET.FOU = FOURNISS.CODE
            LEFT JOIN MEMOS memosComment WITH (NOLOCK) on memosComment.NODEMEMO = DA_ENTET.NM_COMMEN
            LEFT JOIN MEMOS memosTitre WITH (NOLOCK) on memosTitre.NODEMEMO = DA_ENTET.NM_TITRE
            LEFT JOIN SOCIETE WITH (NOLOCK) on SOCIETE.CODE_STE = DA_ENTET.CODE_STE 
            LEFT JOIN SECTIMPU WITH (NOLOCK) on SECTIMPU.CODE_SI = DA_ENTET.SECTIMP AND SECTIMPU.CODE_STE = {$codeSteSi}
            LEFT JOIN CODECOMP WITH (NOLOCK) on CODECOMP.CODE_CDC = DA_ENTET.CODECOMP AND CODECOMP.CODE_STE = {$codeSteCdc} 
            LEFT JOIN SSCPTANA WITH (NOLOCK) on SSCPTANA.CODE_SCA = DA_ENTET.SSCPTEANA  AND SSCPTANA.CODE_STE = {$codeSteSca}
            LEFT JOIN SIG_DA WITH (NOLOCK) ON SIG_DA.NO_DA = DA_ENTET.NO_DA
            LEFT JOIN MODELIVR WITH (NOLOCK) ON MODELIVR.CODE_MDL = DA_ENTET.MODE_LIV
            LEFT JOIN MODEPAIE WITH (NOLOCK) ON MODEPAIE.CODE_MDP = DA_ENTET.MODE_PAIE
            LEFT JOIN CONDPAIE WITH (NOLOCK) ON CONDPAIE.CODE_CDP = DA_ENTET.COND_PAIE
            LEFT JOIN CENTRE CL WITH (NOLOCK) ON CL.CODE_CTR = DA_ENTET.CTR_LIV
            LEFT JOIN CENTRE CE WITH (NOLOCK) ON CE.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
        WHERE DA_ENTET.NO_DA = :value";

        $params = [':value' => $code];

        $commande = $this->pdo->prepare($req);
        $commande->execute($params);
        return $commande->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getArticleLigne
     * @param string $code
     * @return array<mixed>
     */
    public function getArticleLigne(string $code): ?array
    {
        $req =
            "SELECT DA_LIGNE.NO_DA, DA_LIGNE.NO_PO, DA_LIGNE.CODE_ART 
        FROM DA_LIGNE WITH (NOLOCK)
        WHERE DA_LIGNE.NO_DA = :value 
            AND DA_LIGNE.CODE_ART != '' ";

        $params = [':value' => $code];

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

    /**
     * getListeTypePj
     * @return array<mixed>
     */
    public function getListeTypePj(): ?array
    {
        $req =
            "SELECT PJ_TYPE, PJLIBELLE, PHYSIQUE, OUVRABLE, IMPRIMABL 
        FROM PJTYPES WITH (NOLOCK) 
        WHERE PJ_TYPE='PJ' OR PJ_TYPE='PV' OR PJ_TYPE='REG' OR PJ_TYPE='TCO'";

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

    /**
     * getCommentaire
     * @param string $code
     * @return array<string>
     */
    public function getCommentaire(string $code): ?array
    {
        $req =
            "SELECT DA_ENTET.NM_COMMEN, MEMOS.CONTMEMO COMMENTAIRE  
        FROM DA_ENTET WITH (NOLOCK)
        LEFT JOIN MEMOS WITH (NOLOCK) on MEMOS.NODEMEMO = DA_ENTET.NM_COMMEN
        WHERE NO_DA = :value";

        $params = [':value' => $code];

        $da = $this->pdo->prepare($req);
        $da->execute($params);
        return $da->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getFormulaire
     * @return array<string>
     */
    public function getFormulaire(): ?array
    {
        $req =
            "SELECT * from FO_ENTET with(nolock)
        LEFT JOIN TYPDOC with(nolock) on TYPDOC.TYPE=FO_ENTET.TYP_DOC
        WHERE ACCETOUS = " . \Helpers\Outil\Sql::setBoolSql(1) . "
        OR exists (SELECT * FROM FO_UTILIS with(nolock) WHERE CODEFORM = FO_ENTET.NO_CDE AND CODEUTIL = '" . $_SESSION['Session_Login'] . "')";

        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $req .= "AND TYPDOC.NIV_CONF <= '" . $_SESSION['NiveauConfidentialite'] . "'";
        }

        if (!$_SESSION['Session_UsrMultiSoc']) {
            $req .= " AND CODE_STE IN (
                SELECT CODE_STE FROM HASTUTIL WITH (NOLOCK) WHERE CODE= '" . $_SESSION['Session_Login'] . "'
                UNION
                SELECT COD_STE FROM SOC_USR WITH (NOLOCK) WHERE COD_USR= '" . $_SESSION['Session_Login'] . "'
            )";
        }

        $da = $this->pdo->prepare($req);
        $da->execute();
        return $da->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * listeAllDelegationDA
     * @return array<mixed>
     */
    public function listeAllDelegationDA(): ?array
    {
        $date = date('Ymd');
        $req =
            "SELECT *
        FROM SIG_DA with(nolock)
        INNER JOIN DA_ENTET with(nolock) on SIG_DA.NO_DA = DA_ENTET.NO_DA AND DA_ENTET.SIGNEE = " . \Helpers\Outil\Sql::setBoolSql(0) . "
        WHERE SIG_DA.SIGNABLE = " . \Helpers\Outil\Sql::setBoolSql(1) . "
        AND SIGNAT in (
            SELECT SIGNAT
            FROM SIG_DELEG with(nolock)
            JOIN HASTUTIL hasig with(nolock) on SIG_DELEG.SIGNAT = hasig.CODE and hasig.SIG_DA = " . \Helpers\Outil\Sql::setBoolSql(1) . "
            WHERE DELEG = '" . $_SESSION['Session_Login'] . "'
            AND DTE_DEB <= '$date'
            AND (DTE_FIN >= '$date' or DTE_FIN = '00000000')
        )";

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

    // SIGDA
    /**
     * Summary of countAwaitingSign
     * @param string $signataire
     * @return int
     */
    public function countAwaitingSign(string $signataire): int
    {
        $req =
            'SELECT COUNT(SIG_DA.NO_DA) as count
        FROM SIG_DA WITH (NOLOCK)
        LEFT JOIN DA_ENTET with (nolock) on DA_ENTET.NO_DA = SIG_DA.NO_DA
        WHERE SIGNAT = :signat
            AND SIGNABLE= 0x54
            AND INFORME = 0x46
            AND SIG_DA.INFORME	 = 0x46
            and DA_ENTET.SIGNEE = 0x46
        ';

        $requete = $this->pdo->prepare($req);
        $requete->execute([
            'signat' => $signataire,
        ]);
        return $requete->fetch(\PDO::FETCH_ASSOC)['count'] ?? 0;
    }

    /**
     * Summary of listLignePlanning
     * @param string $idDA
     * @return string[][]
     */
    public function listLignePlanning(string $idDA)
    {
        $listCaLigne = $this->fetchAssocAll(
            "SELECT 
                DA_LIGNE.*,
                desigComp.CONTMEMO as DESIGNCOMP, 
                desigComp.CONTMEMO as DES_COMPL,
                SSFAMILL.DESIGNAT
            FROM DA_LIGNE with (nolock)
            LEFT JOIN MEMOS as desigComp with (nolock) on desigComp.NODEMEMO = DA_LIGNE.NM_DCOMP
            LEFT JOIN SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = DA_LIGNE.SSFAM
            WHERE NO_DA = :noDa ",
            [':noDa' => $idDA]
        );
        $this->arrayJoinManyToMany(
            $listCaLigne,
            (new DaPlann($this->pdo))->findAll(['NO_DA' => $idDA]),
            'NO_PO',
            'listPlanningPrev',
            'NO_PO'
        );
        return (array)$listCaLigne;
    }

    /**
     * isMultiFournisseur
     * @return array<mixed>
     */
    public function nbrFournisseur(string $idDA): ?array
    {
        $req =
        'SELECT dl.NO_DA,
            COUNT(DISTINCT dl.CODE_FOU) as NombreDeFournisseur
        FROM DA_LIGNE as dl with (nolock) 
        LEFT JOIN DA_ENTET as de with (nolock) on de.NO_DA = dl.NO_DA
        WHERE dl.NO_DA = :noDA
        GROUP BY dl.NO_DA';
        
        $params = [':noDA' => $idDA];

        $da = $this->pdo->prepare($req);
        $da->execute($params);
        return $da->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * Summary of getDaStatut
     * @param string|int $noDa
     * @return array<mixed>
     */
    public function getDaStatut(string|int $noDa): array
    {
        $req = "
        WITH AoLigneInfo as (
                SELECT 
                    DA_LIGNE.NO_DA,
                    DA_LIGNE.NO_PO,
                    AO_ENTET.NO_AO,
                    AO_ENTET.DTE_VALID_AO,
                    AO_ENTET.VALCHXFOU
                FROM DA_LIGNE with(nolock)
                JOIN AO_LIGNE with(nolock) 
                    ON AO_LIGNE.NO_DA = DA_LIGNE.NO_DA
                    AND AO_LIGNE.NO_PO_DA = DA_LIGNE.NO_PO
                JOIN AO_ENTET with(nolock)
                    ON AO_ENTET.NO_AO = AO_LIGNE.NO_AO
            )
            SELECT CASE "
            . implode(PHP_EOL, array_map(
                fn($sqlExpr, $key) => 'WHEN ' . $sqlExpr . ' THEN ' . Sql::quoteString($key),
                $this->mapEtatSqlExpr,
                array_keys($this->mapEtatSqlExpr)
            ))
            . "
                END ETAT
            FROM DA_ENTET WITH (NOLOCK)
            WHERE DA_ENTET.NO_DA = :noDa
        ";

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

    /**
     * Summary of verifPourcTp
     * @param mixed $idDA
     * @param mixed $numTP
     * @return string[][]
     */
    public function verifPourcTp($idDA, $numTP): array
    {
        $req = "SELECT c.NO_DA, c.NO_PO,
                SUM(e.POURC_TP) AS total_pourc_tp,
                100 - SUM(e.POURC_TP) AS pourcentage_manquant
                FROM DA_LIGNE c 
                INNER JOIN TP_POS p ON p.NUM_CDE = c.NO_DA AND p.NUM_PO_CD = c.NO_PO
                AND p.TYPEOBJ = 'DA_ENTET'
                INNER JOIN TP_ENT e
                ON e.NUM_TP = p.NUM_TP
                AND e.NUM_TP = :numTP
                AND e.NUM_CDE = p.NUM_CDE
                AND e.TYPEOBJ = p.TYPEOBJ
                WHERE c.NO_DA = :idDA
                GROUP BY c.NO_DA, c.NO_PO;
                ";

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