<?php

namespace Modules\Fournisseur\Repository;

use Entity\Model\CsChps;
use Entity\Model\Fourniss;
use Entity\Model\FourPartenaireData;
use Helpers\Log\LogSysteme;
use Helpers\Filter\FilterTable;
use Helpers\Outil\Date;
use Helpers\Outil\Outils;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;

class DocAtt extends AbsRepository
{
    
    /**
     * @param string $parentType enum 'WFT_DEM_ENT'|'FOURNISS'
     * @param array<mixed> $valeur
     * @return array<mixed>[]
     */
    public function listForFourniss(
        string $parentType,
        string $code,
        array $valeur
    ) :array {
        $query = $this->getCanvaSql($parentType, $code);
        // TODO : simplify this query
        $req =
            "WITH 
                Fournisseur as ({$query->with['Fourniss']}),
                Canva as ({$query->query})
            SELECT
                COALESCE(doc.TITRE, Canva.TITRE_DOC) as TITRE,
                Canva.MODE_CONTROL,
                doc.COD_ENR,
                COALESCE(Canva.NUM_ORDRE, -1) as NumOrder,
                doc.NO_ORDRE,
                doc.DT_PARUT,
                doc.DT_VALID,
                doc.DTE_DERNREL,
                doc.NOM_FIC,
                doc.DOC_INUTILE,
                doc.NPRELANCE,
                doc.GERE_PAR_PARTENAIRE,
                doc.NOM_ORI,
                Canva.CODE_DOC,
                Canva.Corresp as Matching,
                case
                    when (
                        (
                            doc.CODE_CAT IS NULL
                            OR TRIM(doc.CODE_CAT) =''
                        )
                        AND Canva.CODE_CATEGORIE is not null
                        AND (Canva.CODE_CATEGORIE) !=''
                    ) THEN Canva.CODE_CATEGORIE
                    when (
                        (
                            doc.CODE_CAT IS NULL
                            OR TRIM(doc.CODE_CAT) =''
                        )
                        and (
                            Canva.CODE_CATEGORIE is null
                            or (Canva.CODE_CATEGORIE) = ''
                        )
                    )  then 'CAT99'
                    when doc.CODE_CAT = 'Autre' then 'CAT99'
                    ELSE doc.CODE_CAT
                end CODE_CAT,
                case
                    when (
                        (
                            doc.CODE_CAT IS NULL
                            OR TRIM(doc.CODE_CAT) =''
                        )
                        AND Canva.CODE_CATEGORIE is not null
                        AND (Canva.CODE_CATEGORIE) !=''
                    ) THEN docCatCaneva.NO_ORDRE
                    when (
                        (
                            doc.CODE_CAT IS NULL
                            OR TRIM(doc.CODE_CAT) =''
                        )
                        and (
                            Canva.CODE_CATEGORIE is null
                            or (Canva.CODE_CATEGORIE) = ''
                        )
                    )  then '999'
                    when doc.CODE_CAT = 'Autre' then '999'
                    ELSE DOC_CAT.NO_ORDRE
                end NO_ORDER_CAT,
                DOC_CAT.LIBELLE_CAT LIBELLE_CAT,
                CENTRE.RAIS_SOC RAIS_SOC,
                Canva.Partenaire ALPHA3,
                Canva.Partenaire CodePartenaire,
                CENTRE.CODE_CTR CODE_CTR,
                HASTUTIL.NOM NOM,
                HASTUTIL.CODE AUTEUR,
                LTRIM(Canva.NUM_ORDRE) ORDRE_PARAM,
                MEMOS.CONTMEMO COMMENTAIRE,
                Histo.DT_VERSION
            FROM Fournisseur
            JOIN DOC_ATT as doc with(nolock) 
                ON doc.NO_DA = Fournisseur.Type 
                AND doc.COD_ENR = Fournisseur.Id
            FULL JOIN Canva ON Canva.TITRE_DOC = doc.TITRE
            left join DOC_CAT docCatCaneva on docCatCaneva.CODE_CAT = Canva.CODE_CATEGORIE AND docCatCaneva.CODE_TABLE = 'FOURNISS'
            left join DOC_CAT with (nolock) on DOC_CAT.CODE_CAT = doc.CODE_CAT AND DOC_CAT.CODE_TABLE = 'FOURNISS'
            left join CENTRE with (nolock) on CENTRE.CODE_CTR = doc.CENTRE
            left join HASTUTIL with (nolock) on HASTUTIL.CODE = doc.AUTEUR
            LEFT JOIN MEMOS with(nolock) on MEMOS.NODEMEMO = doc.NM_COMM
            OUTER APPLY ( -- LEFT JOIN TOP 1
                SELECT TOP 1 DOC_ATT_ANNEXE.*
                FROM DOC_ATT_ANNEXE with(nolock)
                WHERE DOC_ATT_ANNEXE.COD_TAB = Fournisseur.Type
                    AND DOC_ATT_ANNEXE.COD_ENR = Fournisseur.Id
                    AND DOC_ATT_ANNEXE.TITRE = COALESCE(doc.TITRE, Canva.TITRE_DOC)
            ) as Histo
            WHERE 1=1
        ";
        
        $arrayReq = $query->param;
        
        if (isset($valeur["filter_CODE_CAT"]) && $valeur["filter_CODE_CAT"]) {
            if ($valeur["filter_CODE_CAT"]!="Autre") {
                $req .= " and (doc.CODE_CAT=:value1 or Canva.CODE_CATEGORIE=:value10)";
                    $arrayReq[':value1'] = $valeur["filter_CODE_CAT"];
                    $arrayReq[':value10'] = $valeur["filter_CODE_CAT"];
            } else {
                // TODO : untrim
                $req .=' AND TRIM(doc.CODE_CAT) = \'\' 
                    AND Canva.CODE_CATEGORIE IS NULL ';
            }
        }
        
        if (isset($valeur["filter_TITRE"]) && $valeur["filter_TITRE"]) {
            $req .= " and (UPPER(doc.TITRE) LIKE :value2 or UPPER(Canva.Corresp) LIKE :value20)";
            $arrayReq[':value2'] = '%'.strtoupper($valeur["filter_TITRE"]).'%';
            $arrayReq[':value20'] = '%'.strtoupper($valeur["filter_TITRE"]).'%';
        }
        
        if (isset($valeur["filter_CENTRE"]) && $valeur["filter_CENTRE"]) {
            $req .= " and CODE_CTR=:value3";
            $arrayReq[':value3'] = $valeur["filter_CENTRE"];
        }
        
        if (isset($valeur["filter_AUTEUR"]) && $valeur["filter_AUTEUR"]) {
            $req .= " and HASTUTIL.CODE=:value4";
            $arrayReq[':value4'] = $valeur["filter_AUTEUR"];
        }

        if (isset($valeur["filter_NUM_ORDRE"]) && $valeur["filter_NUM_ORDRE"]) {
            $req .= " and LTRIM(Canva.NUM_ORDRE) =:noOrdre";
            $arrayReq[':noOrdre'] = $valeur["filter_NUM_ORDRE"];
        }
        
        if (isset($valeur["filter_date_DT_VALID"]) && $valeur["filter_date_DT_VALID"]) {
            Date::dateToSql($valeur["filter_date_DT_VALID"]);


            $req .= " AND (
                    doc.DT_VALID >= :value5 OR doc.DT_VALID = '00000000'
                ) AND (
                    doc.DT_PARUT <= :value6 OR doc.DT_PARUT = '00000000'
                )";
            $arrayReq[':value5'] = $valeur["filter_date_DT_VALID"];
            $arrayReq[':value6'] = $valeur["filter_date_DT_VALID"];
        }
        
        if (isset($valeur["filter_date_DTE_DERNREL"]) && $valeur["filter_date_DTE_DERNREL"]) {
            Date::dateToSql($valeur["filter_date_DTE_DERNREL"]);
            
            $req .= " and doc.DTE_DERNREL=:value7";
            $arrayReq[':value7'] = $valeur["filter_date_DTE_DERNREL"];
        }
        
        
        if (isset($valeur["filter_etat"]) && $valeur["filter_etat"]) {
            $date=date("Ymd");
            switch ($valeur["filter_etat"]) {
                case 1: // a jours
                    $req .= " AND doc.NOM_FIC != '' 
                        AND ( '$date'<doc.DT_VALID OR doc.DT_VALID='00000000')";
                    
                    break;
                case 2: // périmé
                    $req .= " and doc.NOM_FIC != '' and '$date'>doc.DT_VALID and doc.DT_VALID!='00000000'";
                    break;
                case 3:// sans objet
                    $req .= " and doc.NOM_FIC=''";
                    break;
                case 4: // absent
                    $req .= " and doc.DOC_INUTILE IS NULL";
                    break;
            }
        }

        if (!empty($valeur['filter_egal_Canva.NUM_ORDRE'])) {
            $req .= " AND LTRIM(Canva.NUM_ORDRE) = :filterCanvaOrdre";
            $arrayReq[':filterCanvaOrdre'] = $valeur['filter_egal_Canva.NUM_ORDRE'];
            unset($valeur['filter_egal_Canva.NUM_ORDRE']);
        }

        if (!empty($valeur['filter_equal_NUM_ORDRE'])) {
            $req .= " AND LTRIM(Canva.NUM_ORDRE) = :filterCanvaOrdre";
            $arrayReq[':filterCanvaOrdre'] = $valeur['filter_equal_NUM_ORDRE'];
            unset($valeur['filter_equal_NUM_ORDRE']);
        }

        if (!empty($valeur['filter_equal_NO_ORDRE'])) {
            $req .= " AND doc.NO_ORDRE = :docNum ";
            $arrayReq[':docNum'] = $valeur['filter_equal_NO_ORDRE'];
            unset($valeur['filter_equal_NO_ORDRE']);
        }
        
        $req .= " ORDER BY NO_ORDER_CAT ASC, NumOrder ASC ";

        return array_map(function ($item) {
            return $item +[
                'Etat' => $this->getDocEtat($item),
            ];
        }, $this->fetchAssocAll($req, $arrayReq));
    }

    /**
     * @return string[][]
     */
    public function getCanvaForPartenaire(string $fournissId, string $partenaire)
    {
        $query = $this->getCanvaSql('FOURNISS', $fournissId);
        return $this->fetchAssocAll(
            "WITH Fournisseur as ({$query->with['Fourniss']})
            {$query->query}
            WHERE PartnMatching.Partenaire = :partn",
            $query->param
            + [':partn' => $partenaire]
        );
    }

    /**
     * @return \stdClass
     */
    private function getCanvaSql(string $parentType, string $code)
    {
        return (object) [
            'with' => [
                'Fourniss' =>
                    $parentType == 'FOURNISS'
                    ? "SELECT 
                            'FOURNISS' as Type, 
                            CODE as Id, 
                            CODE as BaseId, 
                            CODE_PAYS as Pays 
                        FROM FOURNISS with(nolock)
                        WHERE CODE = :entityId_0"
                    : "SELECT 
                            'WFT_DEM_ENT' as Type,
                            WFT_DEM_ENT.CODE_DEMANDE as Id,
                            CODE_ENREG as BaseId, 
                            VALEUR_CHAMP as Pays
                        FROM WFT_DEM_ENT with(nolock)
                        LEFT JOIN WFT_DEM_LIG with(nolock) 
                            ON WFT_DEM_LIG.TYPE_ENT = WFT_DEM_ENT.TYPE_ENT
                            AND WFT_DEM_LIG.CODE_DEMANDE = WFT_DEM_ENT.CODE_DEMANDE
                            AND WFT_DEM_LIG.CODE_CHAMP = 'CODE_PAYS'
                        WHERE WFT_DEM_ENT.TYPE_ENT = 'FOURNISS'
                            AND WFT_DEM_ENT.CODE_DEMANDE = :entityId_0"
            ],
            'query' =>
                "SELECT
                    distinct(LST_DOC_DEM.CODE_DOC),
                    LST_DOCS.TITRE_DOC,
                    LST_DOCS.CODE_CATEGORIE,
                    LST_DOCS.NUM_ORDRE,
                    LST_DOCS.A_SURVEILLER,
                    LST_DOC_DEM.MODE_CONTROL,
                    PartnMatching.Partenaire,
                    PartnMatching.Corresp
                FROM LST_DOCS with(nolock)
                JOIN Fournisseur ON 1=1
                CROSS APPLY ( -- JOIN TOP 1 : get first matching liaison for given fourniss
                    SELECT TOP 1 
                        LST_DOC_DEM.CODE_DOC,
                        MAX(LST_DOC_DEM.MODE_CONTROL) as MODE_CONTROL
                    FROM LST_DOC_DEM with(nolock) 
                    WHERE LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
                        AND (
                            (CODE_TABLE='COUNTRY' AND CODE_ENREG = Fournisseur.Pays)
                            OR (CODE_TABLE='DOM_COMP' and CODE_ENREG in (
                                select DISTINCT(CODE_DC) from SSFAMFOU with(nolock)
                                left join D_C_FAM_SSFAM with(nolock) on D_C_FAM_SSFAM.CODE = SSFAMFOU.CODE_SFAM
                                where SSFAMFOU.CODE_FOUR = Fournisseur.Id and CODE_DC is not null
                            ))
                        )
                    GROUP BY LST_DOC_DEM.CODE_DOC
                ) as LST_DOC_DEM
                LEFT JOIN (
                    SELECT 
                        Corresp.ID1 as Partenaire,
                        Corresp.ID2 as CodeDoc,
                        Corresp.LIBELLE Corresp
                    FROM Fournisseur
                    JOIN FOUR_PARTENAIRE_DATA as FourPartn 
                        ON FourPartn.CODE_FOU = Fournisseur.BaseId
                    JOIN PARAMDICO as Corresp with (nolock)
                        ON Corresp.ID1 = FourPartn.CODEPARTENAIRE
                        AND Corresp.TYPEDICO = 'DOCATTFNR_LSTDOCS_PARTNER'
                ) as PartnMatching ON PartnMatching.CodeDoc = LST_DOCS.CODE_DOC ",
            'param' => [':entityId_0' => $code],
        ];
    }

    /**
     * @param array<mixed> $docAtt
     */
    private function getDocEtat(array $docAtt) :string
    {
        if (Sql::toBool($docAtt['DOC_INUTILE'])) {
            return 'sans_objet';
        }
        if ($docAtt['NOM_FIC'] == "") {
            return 'absent';
        }
        $now = date('Ymd');
        if ($docAtt['DT_VALID'] != '00000000'
            && (
                $docAtt['DT_VALID'] < $now
                || $docAtt['DT_PARUT'] > $now
            )
        ) {
            return 'perime';
        }
        return 'ajour';
    }

    /**
     * AuteurRelance
     * @param string $code
     * @param string $Categorie
     * @return array<mixed>
     */
    // phpcs:ignore
    public function AuteurRelance(string $code, string $Categorie) : ?array
    {
        return $this->fetchAssocOne(
            "SELECT NOM, DTE_EVE  FROM FOUR_EVE with (nolock)
            left join HASTUTIL user_relance with (nolock) on user_relance.CODE = FOUR_EVE.ID_USER
            WHERE FOUR_EVE.CODE=:value1 AND COMMENTAIRE like :value2 
            ORDER BY NUM_ORDR desc",
            [':value1' => $code, ':value2' => "%".$Categorie."%"]
        );
    }
    
    /**
     * PartenaireALG
     * Détection qu'un fournisseur est lié à ALG
     *
     * @param string $code
     * @return array<mixed>
     */
    // phpcs:ignore
    public function PartenaireALG(string $code) : ?array
    {
        return $this->fetchAssocOne(
            "SELECT CODEPARTENAIRE FROM FOUR_PARTENAIRE_DATA with(nolock) 
            WHERE CODE_FOU=:value AND DOC_LEGAUX=".Sql::setBoolSql(1),
            [':value' => $code]
        );
    }
    
    /**
     * DocumentALG
     * Détection qu'un document du canevas est géré par ALG
     *
     * @param string $titre
     * @return array<mixed>
     */
    // phpcs:ignore
    public function DocumentALG(string $titre) : ?array
    {
        return $this->fetchAssocOne(
            "SELECT ALPHA3  FROM PARAMDICO with (nolock) 
            WHERE ID2='' and ID3='' and ID4='' and LIBELLE=:value",
            [':value' => $titre]
        );
    }
    
    /**
     * suiviRelanceParCode
     * @param string $code
     * @return array<mixed>
     */
    public function suiviRelanceParCode(string $code) : ?array
    {
        $req="SELECT DTE_EVE,HEU_EVE,NOM,COMMENTAIRE  FROM FOUR_EVE with (nolock) 
              LEFT JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = FOUR_EVE.ID_USER 
              WHERE FOUR_EVE.COD_EVE='REL' AND FOUR_EVE.CODE = :value order by NUM_ORDR DESC";
        $arrayReq = [':value' => $code];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * insertHistoRelance
     * @param string $code
     * @param string $commentaire
     * @return void
     */
    public function insertHistoRelance(string $code, string $commentaire) : void
    {
        $req="select max(NUM_ORDR)+1 as nbr from FOUR_EVE where CODE = :code";
        $arrayReq = [':code' => $code];
        
        $relance = $this->pdo->prepare($req);

        $relance->execute($arrayReq);
        $nbr = $relance->fetch()['nbr'];
        if ($nbr == 'null' || $nbr ==0) {
            $nbr ='1';
        }

        $req="insert into FOUR_EVE (CODE,NUM_ORDR, DTE_EVE, HEU_EVE, ID_USER, COD_EVE, COMMENTAIRE) 
        values(:code, :nbr,'".date('Ymd')."',
        '".date("His")."', '".$_SESSION['Session_Login']."', 'REL',:commentaire);
        ";
        $arrayReq = [':code' => $code,
                    ':commentaire' => $commentaire,
                    ':nbr' =>$nbr];
        
        $relance = $this->pdo->prepare($req);
        $relance->execute($arrayReq);
    }

    /**
     * docAttAvecMemos
     * @param string $code
     * @param string $titre
     * @param mixed $codeDemande
     * @return array<mixed>
     */
    public function docAttAvecMemos(string $code, string $titre, $codeDemande = '') : ?array
    {
        if ($codeDemande) {
            $code = $codeDemande;
            $noDA = 'WFT_DEM_ENT';
        } else {
            $noDA = 'FOURNISS';
        }

        $req = "SELECT * FROM DOC_ATT with (nolock) 
            LEFT JOIN MEMOS with (nolock) on MEMOS.NODEMEMO = DOC_ATT.NM_COMM 
            WHERE NO_DA='$noDA' and COD_ENR=:value and TITRE=:value1";
            
        $arrayReq =  [':value' => $code, ':value1' => $titre];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }
    
    /**
     * docAttHisto
     * @param string $code
     * @param string $titre
     * @return array<mixed>
     */
    public function docAttHisto(string $code, string $titre) : ?array
    {
        return (new \Entity\Model\Docatt($this->pdo))->find([
            'COD_ENR' => $code,
            'TITRE' => $titre,
        ]);
    }
    
    /**
     * docAttHistoAll
     * @param string $code
     * @param string $titre
     * @return array<mixed>
     */
    public function docAttHistoAll(string $code, string $titre) : ?array
    {
        $req="select DT_VERSION from DOC_ATT_ANNEXE with (nolock) where COD_ENR=:value and TITRE=:value1";
        $arrayReq = [':value' => $code, ':value1' => $titre];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * listeDocHisto
     * @param string $code
     * @param string $titre
     * @return array<mixed>
     */
    public function listeDocHisto(string $code, string $titre) : ?array
    {
        $req="select COD_ENR, NO_ORDRE, TITRE, NOM, RAIS_SOC, DT_VALID, DT_PARUT  from DOC_ATT_ANNEXE  with (nolock)
            	left join CENTRE with (nolock) on CENTRE.CODE_CTR = DOC_ATT_ANNEXE.CENTRE
            	left join HASTUTIL with (nolock) on HASTUTIL.CODE = DOC_ATT_ANNEXE.AUTEUR
            	WHERE COD_ENR=:value and TITRE=:value1";
        $arrayReq = [':value' => $code, ':value1' => $titre];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * updateOXALINK
     * @param string $code
     * @param string $pseudo
     * @param string $mdp
     * @param string $acces
     * @return bool
     */
    public function updateOXALINK(string $code, string $pseudo, string $mdp, string $acces) : bool
    {
        $req="UPDATE FOURNISS SET PF_PSEUDO = :value0, PF_MDP = :value1, GEREPORTAIL =$acces WHERE CODE= :id";
        
        $arrayReq = [':value0' => $pseudo, ':value1' => $mdp, ':id' => $code];
        
        $fourniss = $this->pdo->prepare($req);
        
        return $fourniss->execute($arrayReq);
    }
    
    /**
     * libelleChampSupp
     * @param string $id
     * @return array<mixed>
     */
    public function libelleChampSupp(string $id) : ?array
    {
        return (new CsChps($this->pdo))->find([
            'COD_TAB' => 'FOURNISS',
            'NUM_VAR' => $id,
        ]);
    }
    
    /**
     * titreExistCaneva
     * @param string $table
     * @param string $codeFrs
     * @param string $pays
     * @param string $titre
     * @return array<mixed>
     */
    public function titreExistCaneva(string $table, string $codeFrs, string $pays, string $titre) : ?array
    {
        
        if ($table == 'FOURNISS') {
            $jointureSSFAMILLE = "(select DISTINCT(CODE_DC) from SSFAMFOU with(nolock) 
        			left join D_C_FAM_SSFAM with(nolock) on D_C_FAM_SSFAM.CODE = SSFAMFOU.CODE_SFAM
        			where SSFAMFOU.CODE_FOUR = :frs and CODE_DC is not null )";
        } else {
            $jointureSSFAMILLE = "(select DISTINCT(VALEUR_CHAMP) VALEUR_CHAMP from WFT_DEM_LIG with (nolock) where TYPE_ENT = 'FOURNISS' and CODE_DEMANDE = :frs and CODE_CHAMP = 'CODE_SFAM')";
        }
        
        return $this->fetchAssocOne(
            "SELECT * from  LST_DOCS with(nolock) 
            left join LST_DOC_DEM with(nolock) on LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
            where ((CODE_TABLE='COUNTRY' AND CODE_ENREG=:pays) or (CODE_TABLE='DOM_COMP'  and CODE_ENREG in $jointureSSFAMILLE )) 
            and LST_DOCS.TITRE_DOC=:value",
            [
                ':frs'   => $codeFrs,
                ':pays'  => $pays,
                ':value' => $titre,
            ]
        );
    }
    
    /**
     * titreExist
     * @param string $titre
     * @param string $codeFrs
     * @param string $pays
     * @return array<mixed>
     */
    public function titreExist(string $titre, string $codeFrs, $pays) : ?array
    {
        return $this->fetchAssocOne(
            "SELECT * from (    
                select distinct(LST_DOC_DEM.CODE_DOC), LST_DOCS.TITRE_DOC, CODE_CATEGORIE,NUM_ORDRE, A_SURVEILLER, p2.ID1 Partenaire, p2.LIBELLE Corresp from  LST_DOCS with(nolock) 
                left join LST_DOC_DEM with(nolock) on LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
                left outer join PARAMDICO p2 with (nolock) on p2.ID2=LST_DOCS.CODE_DOC and p2.TYPEDICO='DOCATTFNR_LSTDOCS_PARTNER'
                where (CODE_TABLE='COUNTRY' AND CODE_ENREG='$pays') or (CODE_TABLE='DOM_COMP'  and CODE_ENREG in (select DISTINCT(CODE_DC) from SSFAMFOU with(nolock) 
                left join D_C_FAM_SSFAM with(nolock) on D_C_FAM_SSFAM.CODE = SSFAMFOU.CODE_SFAM
                where SSFAMFOU.CODE_FOUR = '$codeFrs' and CODE_DC is not null ))) canevaFrs  where TITRE_DOC=:value",
            [':value' => $titre]
        );
    }
    
    /**
     * titeExistForFrs
     * @param string $table
     * @param string $idFournisseur
     * @param string $titre
     * @return array<mixed>
     */
    public function titeExistForFrs(string $table, string $idFournisseur, string $titre) : ?array
    {
        return (new \Entity\Model\Docatt($this->pdo))->find([
            'NO_DA' => $table,
            'COD_ENR' => $idFournisseur,
            'TITRE' => $titre
        ]);
    }

    /**
     * getCodePaysDemande
     * @param string $codeDemande
     * @return array<mixed>
     */
    public function getCodePaysDemande(string $codeDemande) : ?array
    {
        return $this->fetchAssocOne(
            "SELECT VALEUR_CHAMP from WFT_DEM_LIG with(nolock) 
            WHERE CODE_DEMANDE=:codeDemande and CODE_CHAMP='CODE_PAYS'",
            [':codeDemande' => $codeDemande]
        );
    }
 
    
    /**
     * fournissIsDemandeCreation
     * @param string $idFournisseur
     * @return array<mixed>
     */
    public function fournissIsDemandeCreation(string $idFournisseur) : ?array
    {
        $arrayReq = [':value1' => $idFournisseur];
        $req = "SELECT 
            WFT_DEM_ENT.TYPE_DEMANDE, 
            WFT_DEM_ENT.CODE_DEMANDE,
            {$this->selectTrad('WFT_DEM_ENT.WDEN_LIBELLE_ENREG')}
        FROM WFT_DEM_ENT WITH (NOLOCK) 
        {$this->joinTrad('WFT_DEM_ENT.WDEN_LIBELLE_ENREG', $arrayReq)}
        WHERE WFT_DEM_ENT.CODE_ENREG = :value1";
        
        return $this->fetchAssocOne($req, $arrayReq);
    }
    
    /**
     * libelleDemandeFrs
     * @param string $codeDemande
     * @return array<mixed>
     */
    public function libelleDemandeFrs(string $codeDemande) : ?array
    {
        //$req = "select VALEUR_CHAMP from WFT_DEM_LIG with (nolock) where CODE_DEMANDE = :value1 and CODE_CHAMP = 'RS'";
        $arrayReq = [':value1' => $codeDemande];
        $req = "SELECT 
            {$this->selectTrad('WFT_DEM_ENT.WDEN_LIBELLE_ENREG', 'VALEUR_CHAMP')}
        FROM WFT_DEM_ENT WITH (NOLOCK) 
            {$this->joinTrad('WFT_DEM_ENT.WDEN_LIBELLE_ENREG', $arrayReq)}
        WHERE WFT_DEM_ENT.CODE_DEMANDE = :value1";
        
        // $docAtt = $this->pdo->prepare($req);
        
        // $docAtt->execute($arrayReq);
        // return $docAtt->fetch();
        return $this->fetchAssocOne($req, $arrayReq);
    }

    /**
     * getDocumentCategoriesList
     * @param string $tableCode
     * @return array<mixed>
     */
    public function getDocumentCategoriesList(string $tableCode) : ?array
    {
        $params = [':CODE_TABLE' => $tableCode];
        $query = "SELECT 
            DOC_CAT.CODE_TABLE, 
            DOC_CAT.CODE_CAT, 
            {$this->selectTrad('DOC_CAT.LIBELLE_CAT')},
            DOC_CAT.NO_ORDRE, 
            DOC_CAT.CRITIQUE             
        FROM DOC_CAT WITH (NOLOCK) 
            {$this->joinTrad('DOC_CAT.LIBELLE_CAT', $params)}
        WHERE DOC_CAT.CODE_TABLE = :CODE_TABLE";
        
        return $this->fetchAssocAll($query, $params);
    }

    /**
     * getAttachementTypesList
     * @return array<mixed>
     */
    public function getAttachementTypesList() : ?array
    {
        $params = [];
        $query = "SELECT 
            PJTYPES.PJ_TYPE, 
            {$this->selectTrad('PJTYPES.PJLIBELLE')},
            PJTYPES.PHYSIQUE, 
            PJTYPES.OUVRABLE, 
            PJTYPES.IMPRIMABL, 
            PJTYPES.CAOUVERTU, 
            PJTYPES.CA_IMP
        FROM PJTYPES WITH (NOLOCK) 
            {$this->joinTrad('PJTYPES.PJLIBELLE', $params)}";
        return $this->fetchAssocAll($query, $params);
    }

    /**
     * codePaysDemandeFrs
     * @param string $codeDemande
     * @return array<mixed>
     */
    public function codePaysDemandeFrs(string $codeDemande) : ?array
    {
        return $this->fetchAssocOne(
            "SELECT VALEUR_CHAMP from WFT_DEM_LIG with (nolock) 
            where CODE_DEMANDE = :value1 and CODE_CHAMP = 'CODE_PAYS'",
            [':value1' => $codeDemande]
        );
    }
    
    /**
     * insertDocPartenaire
     * @deprecated use \Entity\Model\Docatt::insertForFourniss instead
     * @return bool
     */
    public function insertDocPartenaire(
        string $fournissId,
        string $titre,
        string $dateParution,
        string $dateVelidite,
        string $nomFic = '',
        bool $partenaire = true,
        bool $copie = true
    ) : bool {
        $req = "INSERT INTO DOC_ATT (
            NO_DA,
            COD_ENR,
            TITRE,
            NOM_FIC,
            DT_PARUT,
            DT_VALID,
            GERE_PAR_PARTENAIRE,
            COPIE,
            NO_ORDRE
        ) VALUES (
            'FOURNISS',
            :fournissId_1,
            :titre,
            :nom_fic,
            :dateParution,
            :dateVelidite,
            ".($partenaire?'0x54':'0x46').",
            ".($copie?'0x54':'0x46').",
            (
                SELECT COALESCE( MAX(NO_ORDRE) + 1 , 1)
                FROM DOC_ATT
                WHERE COD_ENR = :fournissId_2
                AND NO_DA = 'FOURNISS'
            )
        )";
        
        $docAtt = $this->pdo->prepare($req);
        $b = $docAtt->execute([
            ':fournissId_1' => $fournissId,
            ':fournissId_2' => $fournissId,
            ':titre' => $titre,
            ':nom_fic' => $nomFic,
            ':dateParution' => $dateParution,
            ':dateVelidite' => $dateVelidite
        ]);
        if ($b === false) {
            LogSysteme::prefixErrorLog('!!!');
        }
        return $b;
    }

    /**
     * controlDocumentAllFrs
     * @param string $listFrs
     * @param string|bool $critique
     * @return array
     */
    public function controlDocumentAllFrs(string $listFrs, $critique) : ?array
    {
        
        // $fourniss = new Fourniss($this->pdo);
        // $fournisseur = $fourniss->find($code);
        
        // $codePays = $fournisseur['CODE_PAYS'];
        $req = "SELECT COD_ENR,d.TITRE, p.CODE_CATEGORIE, d.DT_PARUT, d.DT_VALID, DOC_INUTILE
        from (select * from DOC_ATT with (nolock) where NO_DA='FOURNISS' and COD_ENR in (".$listFrs.") ) d
        join FOURNISS with(nolock) on FOURNISS.CODE = d.COD_ENR
        FULL JOIN (
            select LST_DOC_DEM.CODE_DOC, LST_DOCS.TITRE_DOC, CODE_CATEGORIE,NUM_ORDRE, A_SURVEILLER, null Partenaire, null Corresp,CODE_ENREG from LST_DOC_DEM with (nolock)
            left join LST_DOCS with (nolock) on LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
            where (CODE_TABLE='COUNTRY') or (CODE_TABLE='DOM_COMP'  and CODE_ENREG in (select DISTINCT(CODE_DC) from SSFAMFOU with (nolock)
            left join D_C_FAM_SSFAM with (nolock) on D_C_FAM_SSFAM.CODE = SSFAMFOU.CODE_SFAM
            where SSFAMFOU.CODE_FOUR  in (".$listFrs.") and CODE_DC is not null )) 
    ) p  ON d.TITRE = p.TITRE_DOC and p.CODE_ENREG=FOURNISS.CODE_PAYS  
        WHERE CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = ".($critique? '0x54':'0x46').") and TITRE is not null
		ORDER BY COD_ENR, CASE WHEN d.DT_VALID='00000000' THEN 0 ELSE 99999999 END DESC, d.DT_VALID  ASC";
        
        $arrayReq = [];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    public function deleteProvigisFile(string $fournissCode) :bool
    {
        $req = "DELETE DOC_ATT 
            WHERE NO_DA = 'FOURNISS' 
                AND COD_ENR = :fourniss
                AND NOM_FIC LIKE 'provigis:%'";

        $typeMarche = $this->pdo->prepare($req);
        return $typeMarche->execute([
            ':fourniss' => $fournissCode,
        ]);
    }

    /**
     * @param string $noDa
     * @param array<mixed> $post
     * @param array<mixed> $arrayMultiSelect
     * @return array<mixed>
     * @throws \Exception
     */
    public function getDocumentsDetailed($noDa, $post, $arrayMultiSelect = [])
    {
        $date = new \DateTime('last year');
        $lastYearYmd = $date->format('Ymd');


        $listeSelectTmp     = [];
        $selectables        = '';
        if ($arrayMultiSelect) {
            foreach ($arrayMultiSelect as $key => &$val) {
                $listeSelectTmp[] = $val;
            }
            $selectables = implode(',', $listeSelectTmp);
        }

        $selectors = (!empty($selectables)) ?
            $selectables :
            "
            DOC.COD_ENR,
            DOC.NO_DA,
            DOC.TITRE,
            DOC.DT_PARUT,
            DOC.DT_VALID,
            DOC.CODE_CAT,
            DOC.AUTEUR,
            DOC.CODE_FOU,
            DOC.NO_ORDRE,
            F.RS,
            CAT.LIBELLE_CAT,
            M.CONTMEMO
            ";

        $stmt = "
            SELECT
                {$selectors}
            FROM
                DOC_ATT DOC WITH(nolock)
            LEFT JOIN
                FOURNISS F ON F.CODE = DOC.COD_ENR
            LEFT JOIN
                DOC_CAT CAT ON CAT.CODE_CAT = DOC.CODE_CAT AND CAT.CODE_TABLE = :codeTable
            LEFT JOIN
                MEMOS M with(nolock) ON M.NODEMEMO = DOC.NM_COMM
            WHERE
                DOC.NO_DA = :noDa
                and DOC.DT_PARUT >= :lastYear
        ";

        $params = [
            'codeTable' => $noDa,
            'noDa'      => $noDa,
            'lastYear'  => $lastYearYmd
        ];

        if (!empty($post)) {
            $whereArray = FilterTable::splitFilter($post, 1, 0, 1);
            if ($whereArray[0]) {
                $stmt .= $whereArray[0].$whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $params = array_merge($params, $whereArray[1]);
                }
            } elseif ($whereArray['tri']) {
                $stmt .= $whereArray['tri'];
            }
        }

        $stmt .= (!empty($selectables)) ?
            " GROUP BY {$selectors} " :
            "";

        $postPagination = $post;


        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($stmt, $postPagination, $params, $this->pdo);

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

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getFicheDocument($post)
    {
        $stmt = "
            SELECT
                DOC.*,
                FOU.RS,
                CAT.LIBELLE_CAT,
				M.CONTMEMO as COMMENTAIRE
            FROM
                DOC_ATT DOC with(nolock)
            LEFT JOIN
                FOURNISS FOU with(nolock) ON FOU.CODE = DOC.COD_ENR
            LEFT JOIN
                DOC_CAT CAT with(nolock) ON CAT.CODE_CAT = DOC.CODE_CAT AND CAT.CODE_TABLE = :codeTable
            LEFT JOIN
				MEMOS M with(nolock) ON M.NODEMEMO = DOC.NM_COMM
            WHERE
                DOC.COD_ENR = :codEnr
                AND DOC.NO_ORDRE = :noOrdre
                AND DOC.NO_DA = :noDa
        ";

        $params = [
            'codEnr'    => $post['COD_ENR'],
            'noOrdre'   => $post['NO_ORDRE'],
            'noDa'      => $post['MODULE'],
            'codeTable' => $post['MODULE']
        ];

        $query = $this->pdo->prepare($stmt);
        $query->execute($params);

        $result = $query->fetch($this->pdo::FETCH_ASSOC);

        return $result;
    }
}
