<?php

namespace Modules\DemandeAchat\Repository;

use Entity\Model\DaPlann;
use Entity\Model\Hastutil;
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 Modules\GlobalApplication\Repository\LicenceOxalys;
use Modules\Suivi\Repository\SuiviDaRepo;

class DemandeAchat extends AbsRepositoryExport
{

    /**
     * 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);
    }

    /**
     * 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);

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

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

    /**
     * listeDemandeAchat
     * @param array<mixed> $post
     * @param array<string> $listField
     * @return \stdClass{query:string,params:array<mixed>}
     */
    public function getMainQuery(array $post, array $listField = []): object
    {
        $tmpPost = $post; // $tmpPost will be modified, so we copy an original into $post for the Paginator
        $params = [];

        $whereClause = '';

        // Explication des états :
        // WHEN DA_ENTET.REFUSEE = 0x54 THEN 'R' => Refusées
        // WHEN DA_ENTET.TRAITEE = 0x54 AND DA_ENTET.REFUSEE = 0x46 THEN 'T' => Traitées
        // WHEN DA_ENTET.SIGNEE = 0x54 AND DA_ENTET.TRAITEE = 0x46 THEN 'AT' => A traiter
        // WHEN DA_ENTET.VALIDITE = 0x54 AND DA_ENTET.SIGNEE = 0x46 THEN 'ES' => En signature
        // ELSE 'AV' => A valider

        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,
                HASTUTIL.NOM,
                SOCIETE.RAIS_SOC,
                SOCIETE.SUIVI_BPA,
                FOURNISS.RS,
                MEMOS.CONTMEMO COMMENTAIRE,
                memoTitle.CONTMEMO TITRE,
                FOUDACDE.COD_TAC,
                CASE
                    WHEN DA_ENTET.REFUSEE = 0x54 THEN 'R'
                    WHEN DA_ENTET.TRAITEE = 0x54 AND DA_ENTET.REFUSEE = 0x46 THEN 'T'
                    WHEN DA_ENTET.SIGNEE = 0x54 AND DA_ENTET.TRAITEE = 0x46 THEN 'AT'
                    WHEN DA_ENTET.VALIDITE = 0x54 AND DA_ENTET.SIGNEE = 0x46 THEN 'ES'
                    ELSE 'AV'
                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 = DA_ENTET.NO_DA)
                    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";
        } else {
            $sel = $this->processSpecialField($listField);
            SuiviDaRepo::jointureExport($this->pdo, $sel->join);
            $select = $this->stringifySelect($sel->select);
        }

        $query =
            "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 = '" . $_SESSION['Session_Login'] . "' 
                    AND SIG_DA.SIGNABLE = 'T' "
            . implode(' ', $sel->join ?? [])
            . " WHERE TYPDOC.NIV_CONF <= :nivConf 
        ";

        $params[':nivConf'] = $_SESSION["Session_Login"] == 'OXAUSER'
            ? 99
            : (new Hastutil($this->pdo))
                ->find($_SESSION["Session_Login"], ['NIV_CONF'])['NIV_CONF'];

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

        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 (isset($tmpPost['demandeur']) && $tmpPost['demandeur']) {
            $tmpPost['filter_egal_NO_DEM'] = $tmpPost['demandeur'];
        }

        if (isset($post['acheteur']) && $post['acheteur']) {
            $params['acheteur'] = $post['acheteur'];
            $query .= ' AND DA_ENTET.NO_DA IN (SELECT NO_DA FROM DA_LIGNE with(nolock) WHERE ACHETEUR = :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($tmpPost['filter_NO_DA'])) {
            $tmpPost['filter_DA_ENTET.NO_DA'] = $tmpPost['filter_NO_DA'];
            unset($tmpPost['filter_NO_DA']);
        }

        if (isset($tmpPost['filter_egal_CODE_STE']) && $tmpPost['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($tmpPost['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($tmpPost['filter_NO_DA'])) {
            $tmpPost['filter_DA_ENTET.NO_DA'] = $tmpPost['filter_NO_DA'];
            unset($tmpPost['filter_NO_DA']);
        }
        
        $tmpPost['tri_DA_ENTET.NO_DA'] = $tmpPost['tri_NO_DA'] ?? null;
        unset($tmpPost['tri_NO_DA']);

        if (isset($tmpPost['filter_egal_CODE_STE']) && $tmpPost['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($tmpPost['filter_egal_CODE_STE']);
        }

        if (isset($tmpPost['filter_ETAT']) && $tmpPost['filter_ETAT'] != '') {
            switch ($tmpPost['filter_ETAT']) {
                case 'R':
                    $query .= " AND DA_ENTET.REFUSEE = 0x54 ";
                    break;

                case 'T':
                    $query .= " AND DA_ENTET.TRAITEE = 0x54 AND DA_ENTET.REFUSEE = 0x46 ";
                    break;

                case 'AT':
                    $query .= " AND DA_ENTET.SIGNEE = 0x54 AND DA_ENTET.TRAITEE = 0x46 ";
                    break;

                case 'ES':
                    $query .= " AND DA_ENTET.VALIDITE = 0x54 AND DA_ENTET.SIGNEE = 0x46 AND DA_ENTET.TRAITEE = 0x46 ";
                    break;

                default:
                    $query .= " AND DA_ENTET.REFUSEE = 0x46 AND DA_ENTET.TRAITEE = 0x46 AND DA_ENTET.VALIDITE = 0x46 AND DA_ENTET.SIGNEE = 0x46 ";
                    break;
            }
            unset($tmpPost['filter_ETAT']);
        }

        // filtres sur les fournisseurs => il faut aussi chercher dans les lignes des DA
        if (isset($tmpPost['filter_FOU' . Constante::spliter . 'RS']) && $tmpPost['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'] = $tmpPost['filter_FOU' . Constante::spliter . 'RS'];
            unset($tmpPost['filter_FOU' . Constante::spliter . 'RS']);
        }

        //filtre avancé sur les accords-cadres => à chercher aussi dans les lignes des DA
        foreach ($tmpPost 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 ($tmpPost['fa-' . $filterKey . '-2'] == 'egal') {
                            $operator = '=';
                        }
                        $query .= " AND (DA_ENTET.ACD {$operator} :daEntetAcd OR UPPER(DA_LIGNE.ACD) {$operator} :daLigneAcd) ";
                        $params['daEntetAcd'] = $params['daLigneAcd'] = $tmpPost['fa-' . $filterKey . '-3'];
                        unset($tmpPost['fa-' . $filterKey . '-1']);
                        unset($tmpPost['fa-' . $filterKey . '-2']);
                        unset($tmpPost['fa-' . $filterKey . '-3']);
                        if (is_array($tmpPost['idFiltreAvance'])) {
                            foreach ($tmpPost['idFiltreAvance'] as $k => $v) {
                                if ($v === $filterKey) {
                                    unset($tmpPost['idFiltreAvance'][$k]);
                                }
                            }
                        }
                    }
                }
            }
        }

        $query = $query . $whereClause;

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

        if (! empty($whereArray)) {
            $query .= $whereArray[0] . $whereArray['tri'];

            if (! empty($whereArray[1])) {
                $params = array_merge($params, $whereArray[1]);
            }
        }

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