<?php
namespace Modules\Fournisseur\Repository;

use Entity\Model\Metadata\EntityMetaQb;
use Entity\Model\Metadata\PartnFournissDemModif;
use Entity\Model\Paravrac;
use Helpers\Filter\FilterTable;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Entity\Model\Fourniss;
use Helpers\QueryBuilder\AbsRepository;

class GestFourniss extends AbsRepository
{
    /**
     * @return string
     */
    private function getMainRequest(string $user, string $listeSelect = '', bool $count = false, string $envoi = null)
    {
        if ($envoi=='wsdl' || $count) {
            $select = " F.CODE ";
        } elseif ($listeSelect) {
            $select = $listeSelect;
        } else {
            $select = " 
                F.*,
                FF.CODE_FOUGR,
                COUNTRY.LIBELLE PAYS,
                WFT_DEM_ENT.STATUT_DEMANDE,
                WFT_DEM_ENT.CODE_DEMANDE,
                WFT_DEM_ENT.CODE_ENREG,
                (
                    select count(LST_FOURN.CODLST) 
                    from FOU_LSTFOURN with (nolock) 
                    left join LST_FOURN with (nolock) 
                        on LST_FOURN.CODLST = FOU_LSTFOURN.CODLST
                    where FOU_LSTFOURN.CODFOURN = F.CODE 
                        and (DTE_FIN='' or DTE_FIN='00000000' or DTE_FIN > '".date('Ymd')."')
                ) CODFOURN,
                (
                    select count(CODE_FOU) 
                    from FOUR_PARTENAIRE_DATA with (nolock) 
                    where CODE_FOU = F.CODE
                ) PARTENAIRE,
                (
                    select count(LIENFOUGRPSTE.CODE_FOUGR) 
                    from LIENFOUGRPSTE with (nolock) 
                    where LIENFOUGRPSTE.CODE_FOUGR = F.CODE 
                        and LIENFOUGRPSTE.TYPE_FG='F'
                ) societes,
                (
                    select TOP 1 STATUT_DEMANDE 
                    from WFT_DEM_ENT with (nolock) 
                    WHERE WFT_DEM_ENT.TYPE_ENT='FOURNISS' 
                        and WFT_DEM_ENT.CODE_ENREG= F.CODE 
                        and WFT_DEM_ENT.STATUT_DEMANDE IN ('1','0','S')
                    ORDER BY WFT_DEM_ENT.DATE DESC, WFT_DEM_ENT.HEURE DESC
                ) WF_ATTENT,
                ISNULL(
                    (
                        SELECT TOP 1 CODE 
                        FROM FOURNINC with(nolock) 
                        WHERE FOURNINC.FOU = F.CODE
                    ), 0
                ) INCIDENT_PRESENT,

                (   select top 1 ISNULL(d.DT_VALID, '200000000') DT_VALID
                    from (select * from DOC_ATT with (nolock) where NO_DA='FOURNISS' and COD_ENR=F.CODE) d
                    FULL JOIN (
                        select distinct(LST_DOC_DEM.CODE_DOC), LST_DOCS.TITRE_DOC, CODE_CATEGORIE,NUM_ORDRE, A_SURVEILLER, null Partenaire, null Corresp 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' AND CODE_ENREG=F.CODE_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 = F.CODE and CODE_DC is not null )) 
                    ) p  ON d.TITRE = p.TITRE_DOC
                    WHERE CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = 0x46) and ISNULL(d.DOC_INUTILE, 0x46) = 0x46
                    ORDER BY CASE WHEN d.DT_VALID='00000000' THEN 0 ELSE 99999999 END DESC, d.DT_VALID  ASC
                ) DOC_NO_CRIT,
            
                (select top 1 ISNULL(d.DT_VALID, '200000000') DT_VALID
                    from (select * from DOC_ATT with (nolock) where NO_DA='FOURNISS' and COD_ENR=F.CODE) d
                    FULL JOIN (
                        select distinct(LST_DOC_DEM.CODE_DOC), LST_DOCS.TITRE_DOC, CODE_CATEGORIE,NUM_ORDRE, A_SURVEILLER, null Partenaire, null Corresp 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' AND CODE_ENREG=F.CODE_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 = F.CODE and CODE_DC is not null )) 
                    ) p  ON d.TITRE = p.TITRE_DOC
                    WHERE CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = 0x54) and ISNULL(d.DOC_INUTILE, 0x46) = 0x46
                    ORDER BY CASE WHEN d.DT_VALID='00000000' THEN 0 ELSE 99999999 END DESC, d.DT_VALID  ASC
                ) DOC_CRIT,
            
                CASE WHEN EXISTS(
                    SELECT 1 FROM ENTITYMETA with(nolock)
                    WHERE ENTITYMETA.ENTITY_TYPE = 'FOUR'
                        AND ENTITYMETA.ENTITY_ID = F.CODE
                        AND ENTITYMETA.STRUCT_TYPE = 'PFDM'
                ) THEN 1 ELSE 0 END as hasPartnDemModif
            ";
        }

        $req = "
                select $select
                from FOURNISS F with (nolock)
                left join FAVUSRFOUGR FF with(nolock) on  FF.CODE_FOUGR =  F.CODE and FF.CODE_USER='$user' and FF.TYPE_FG='F'
                LEFT JOIN COUNTRY  with (nolock) ON COUNTRY.CODE_PAYS=F.CODE_PAYS
                LEFT JOIN WFT_DEM_ENT with(nolock) on WFT_DEM_ENT.CODE_ENREG = F.CODE and STATUT_DEMANDE = '1'        
                where 1=1   
        ";
        
        return $req;
    }


    /**
     * listeFournisseur
     * @param string $user
     * @param array<mixed> $post
     * @param string $delaiRelance
     * @param mixed $envoi
     * @param string $listeSelect
     * @return mixed
     */
    public function listeFournisseur(string $user, array $post, string $delaiRelance, $envoi = false, string $listeSelect = '')
    {
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        $postPagination = $post;

        $req = $this->getMainRequest($user, $listeSelect, false, $envoi);
        
        $filter = $this->getFilter($post);

        $req = $req. $filter['where'] . $filter['tri'];
        $arrayReq = $filter['param'];
       
        if ($envoi!='wsdl' && $envoi!='csv') {
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $postPagination, $arrayReq, $this->pdo);
        }
        
        // TODO : move somewhere else
        if ($envoi=='wsdl') {
            foreach ($arrayReq as $key => $value) {
                $req = str_replace($key, "'".$value."'", $req);
            }
            return $req;
        }

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

    /**
     * @param array<mixed> $post
     */
    public function countFournisseur(string $user, array $post) :int
    {
        $filter = $this->getFilter($post);
        return intval(
            $this->fetchAssocOne(
                $this->getMainRequest($user, 'COUNT(*) as count')
                . $filter['where'],
                $filter['param']
            )['count'] ?? 0
        );
    }

    /**
     * @param array<mixed> $post
     * @return array{where:string,tri:string,param:array<mixed>}
     */
    public function getFilter(array $post) :array
    {

        $where = '';

        
        $post['filter_COUNTRY.LIBELLE'] = $post['filter_PAYS']??'';
        unset($post['filter_PAYS']);

        // Column ambigu
        $post['filter_F.TYPE_FG'] = $post['filter_TYPE_FG']??'';
        unset($post['filter_TYPE_FG']);

        if ((sql::paravrac($this->pdo, 'PARAMFOU', 'LIEN_FOU_STE_ACTIF') == 'O') && !Roles::droitsUtilisateur($this->pdo, 'HA_FOU_TST')) {
            $where .= " and (
                exists (select * from LIENFOUGRPSTE lien with (nolock) where lien.TYPE_FG = 'F' and lien.CODE_FOUGR = F.CODE and lien.CODE_STE = :codeSte)
                or not exists (select * from LIENFOUGRPSTE lien with (nolock) where lien.TYPE_FG = 'F' and lien.CODE_FOUGR = F.CODE)
            ) ";
            $arrayReq[':codeSte'] = $_SESSION["Session_SocUsr"];
        }

        if (isset($post['filter_HasPartenaire']) && $post['filter_HasPartenaire']!= '') {
            // True if fourniss is link to one or more partenaire which does not have any pending invite
            $where .= " AND EXISTS (
                SELECT CODE_FOU, CODEPARTENAIRE 
                FROM FOUR_PARTENAIRE_DATA with (nolock)
                WHERE CODE_FOU = F.CODE
                    AND CODE_FOU not in (
                        SELECT ID3 FROM PARAMDICO with(nolock)
                        WHERE TYPEDICO = 'INVIT_PARTFOU' 
                            AND ALPHA2 != 'A' 
                            AND ID1 = CODEPARTENAIRE
                    )
            )";
            unset($post['filter_HasPartenaire']);
        }

        if (! empty($post['filter_typeFourniss'])) {
            $arrayReq['typeFourniss'] = $this->shortListeId($post['filter_typeFourniss']);
            $where .= " AND EXISTS (
                SELECT 1
                from FOURNISS with (nolock)
                JOIN FOU_LSTFOURN with(nolock) 
                    on FOU_LSTFOURN.CODFOURN = FOURNISS.CODE
                    and FOU_LSTFOURN.CODLST = :typeFourniss
                where FOURNISS.CODE = F.CODE
            )";
            unset($post['filter_typeFourniss']);
        }

        if (isset($post['filter_statut']) && $post['filter_statut'] != '') {
            if ($post['filter_statut']=='Prospect') {
                $where .=" and F.PROSPECT=".Sql::setBoolSql(1)."";
            } elseif ($post['filter_statut']=='Reference') {
                $where .=" and F.PROSPECT=".Sql::setBoolSql(0)." AND F.OCCASION=".Sql::setBoolSql(0)."";
            } elseif ($post['filter_statut']=='Occasionnel') {
                $where .=" and F.PROSPECT=".Sql::setBoolSql(0)." AND F.OCCASION=".Sql::setBoolSql(1)."";
            }

            unset($post['filter_statut']);
        }

        if (isset($post['filter_INV_PART']) && $post['filter_INV_PART']) {
            $where .=" and CODE in (select CODE from FOURNISS frs with(nolock)
                     left join (select * from PARAMDICO with(nolock) where TYPEDICO='INVIT_PARTFOU') par  on par.ID3=frs.CODE
                     where ALPHA2<>'A' and ID1='".$post['filter_INV_PART']."')";
            
            unset($post['filter_INV_PART']);
        }

        if (isset($post['filter_typeDoc']) && $post['filter_typeDoc'] != '') {
            $conditionCritique = '';
            $sqlDateValid = '';
            switch ($post['filter_typeDoc']) {
                case 'VIGI_A_JOUR':
                case 'SURV_ABSENT':
                case 'ABSENT':
                case 'A_JOUR':
                    $where .= "	and ( 
	
                        select count(distinct(LST_DOCS.TITRE_DOC)) from FOURNISS f with (nolock)
                            left join SSFAMFOU sf with (nolock) on sf.CODE_FOUR = f.CODE
                            left join D_C_FAM_SSFAM dc with(nolock) on dc.FAM_SSFAM='S' and dc.CODE=sf.CODE_SFAM
                            join LST_DOC_DEM d with(nolock) on (d.CODE_TABLE='COUNTRY' and d.CODE_ENREG=f.CODE_PAYS) or (d.CODE_TABLE='DOM_COMP' and d.CODE_ENREG=dc.CODE_DC)
                            left join LST_DOCS with (nolock) on LST_DOCS.CODE_DOC = d.CODE_DOC
                            left join DOC_ATT with (nolock) on DOC_ATT.NO_DA = 'FOURNISS' 
                            and DOC_ATT.TITRE = LST_DOCS.TITRE_DOC
                            and DOC_ATT.COD_ENR = sf.CODE_FOUR
                            and DOC_ATT.CODE_CAT = LST_DOCS.CODE_CATEGORIE 
                            and ((DT_VALID>='".date('Ymd')."' or DT_VALID='00000000') or DOC_INUTILE = 0x54)
                        where f.CODE = F.CODE
                        and TITRE IS NULL
                        ".(($post['filter_typeDoc']=='VIGI_A_JOUR' || $post['filter_typeDoc']=='SURV_ABSENT')? " and LST_DOCS.A_SURVEILLER = 0x54 ":"")."

                        ) ".(($post['filter_typeDoc']=='ABSENT' || $post['filter_typeDoc']=='SURV_ABSENT')? "!=":"=")." 0 ";
                    break;
                case 'SURV_RELANCE':
                case 'RELANCE':
                    $delaiRelance = $this->getDelaiRelance();
                    /** @phpstan-ignore-next-line */
                    $dateRelance = date('Ymd') - (int)$delaiRelance;

                    $where .= "	and ( 
	
                    select count(distinct(LST_DOCS.TITRE_DOC)) from FOURNISS f with (nolock)
                        left join SSFAMFOU sf with (nolock) on sf.CODE_FOUR = f.CODE
                        left join D_C_FAM_SSFAM dc with(nolock) on dc.FAM_SSFAM='S' and dc.CODE=sf.CODE_SFAM
                        join LST_DOC_DEM d with(nolock) on (d.CODE_TABLE='COUNTRY' and d.CODE_ENREG=f.CODE_PAYS) or (d.CODE_TABLE='DOM_COMP' and d.CODE_ENREG=dc.CODE_DC)
                        left join LST_DOCS with (nolock) on LST_DOCS.CODE_DOC = d.CODE_DOC
                        left join DOC_ATT with (nolock) on DOC_ATT.NO_DA = 'FOURNISS' 
                        and DOC_ATT.TITRE = LST_DOCS.TITRE_DOC
                        and DOC_ATT.COD_ENR = sf.CODE_FOUR
                        and DOC_ATT.CODE_CAT = LST_DOCS.CODE_CATEGORIE 
                        and (DT_VALID <= '".$dateRelance."')
                    where f.CODE = F.CODE
                    and TITRE IS NULL

                    ) != 0 ";

                    break;
                    // phpcs:ignore
                case 'CRITIQUE_RELANCE':
                    //hgdhg
                    $delaiRelance = $this->getDelaiRelance();
                    /** @phpstan-ignore-next-line */
                    $dateRelance = date('Ymd') - (int)$delaiRelance;
                    $sqlDateValid = " and ((DT_VALID = '00000000' or DT_VALID<'".date('Ymd')."' or DT_VALID<'".$dateRelance."')  or DOC_INUTILE = 0x54)";
                    $conditionCritique = ' !=  ';
                    // phpcs:ignore
                case 'CRITIQUE_A_JOUR':
                    if ($conditionCritique == '') {
                        $sqlDateValid = '';
                        $conditionCritique = " = ";
                    }
                    // phpcs:ignore
                case 'CRITIQUE_ABSENT':
                    if ($conditionCritique == '') {
                        $conditionCritique = " != ";
                    }

                    if ($sqlDateValid =='') {
                        $sqlDateValid = " and ((DT_VALID = '00000000' or DT_VALID>='".date('Ymd')."')  or DOC_INUTILE = 0x54)";
                    }

                    $where .= "	and ( 
	
                    select count(distinct(LST_DOCS.TITRE_DOC)) from FOURNISS f with (nolock)
                        left join SSFAMFOU sf with (nolock) on sf.CODE_FOUR = f.CODE
                        left join D_C_FAM_SSFAM dc with(nolock) on dc.FAM_SSFAM='S' and dc.CODE=sf.CODE_SFAM
                        join LST_DOC_DEM d with(nolock) on (d.CODE_TABLE='COUNTRY' and d.CODE_ENREG=f.CODE_PAYS) or (d.CODE_TABLE='DOM_COMP' and d.CODE_ENREG=dc.CODE_DC)
                        left join LST_DOCS with (nolock) on LST_DOCS.CODE_DOC = d.CODE_DOC
                        left join DOC_CAT with (nolock) on LST_DOCS.CODE_CATEGORIE = DOC_CAT.CODE_CAT
                        left join DOC_ATT with (nolock) on DOC_ATT.NO_DA = 'FOURNISS' 
                        and DOC_ATT.TITRE = LST_DOCS.TITRE_DOC
                        and DOC_ATT.COD_ENR = sf.CODE_FOUR
                        and DOC_ATT.CODE_CAT = LST_DOCS.CODE_CATEGORIE 
                        $sqlDateValid and CRITIQUE = 0x54
                    where f.CODE = F.CODE
                    and TITRE IS NULL

                    ) $conditionCritique 0 ";
                    
                    break;
                case substr($post['filter_typeDoc'], 0, 9)=='DOC_PART_':
                    $codePartenaire = substr($post['filter_typeDoc'], 9);

                    $subRequeteNbDocPart = "select count(*) nbrDocPartenairePourUnFournisseur 
                                            from PARAMDICO with(nolock)
                                            where TYPEDICO='DOCATTFNR_LSTDOCS_PARTNER' and ID1='$codePartenaire'";
                    
                    $subReq = $this->pdo->prepare($subRequeteNbDocPart);
                    
                    $subReq->execute();
                    $nbDocPart =  $subReq->fetch()['nbrDocPartenairePourUnFournisseur'];
                    
                    $where .= " and CODE in (select ID3 from PARAMDICO with(nolock) 
                             left join(
                                select COD_ENR, count(*) as nbrDoc  from DOC_ATT with(nolock) inner join 
                                (
                                	select *   from FOUR_PARTENAIRE_DATA with(nolock) where DOC_LEGAUX=0x54 and CODEPARTENAIRE='$codePartenaire' 
                                ) as fourniss on fourniss.CODE_FOU = DOC_ATT.COD_ENR where  GERE_PAR_PARTENAIRE =0x54 and DT_VALID>='".date('Ymd')."' GROUP BY COD_ENR
                             )  Doc   on PARAMDICO.ID3 = Doc.COD_ENR
                             where TYPEDICO='INVIT_PARTFOU' and ALPHA2 = 'A' AND ID1='HIVEO' and (nbrDoc < '$nbDocPart' or nbrDoc is null))";
                    
                    break;
                case 'ALL':
                case '':
                default:
                    // Rien à faire
                    break;
            }
            
            unset($post['filter_typeDoc']);
        }

        /** Filter complémentaire **/
        if (isset($post['filter_recherche']) && $post['filter_recherche'] != '') {
            $where .=" AND (
                UPPER(F.RS) LIKE :value5
                or UPPER(F.VILLE1) LIKE :value6 
                or UPPER(COUNTRY.LIBELLE) LIKE :value7 
                or UPPER(F.CODE) LIKE :value8 
                or UPPER(F.CPOSTAL) LIKE :value9
            )";
            $arrayReq[':value5']='%'.mb_strtoupper($post['filter_recherche']).'%';
            $arrayReq[':value6']='%'.mb_strtoupper($post['filter_recherche']).'%';
            $arrayReq[':value7']='%'.mb_strtoupper($post['filter_recherche']).'%';
            $arrayReq[':value8']='%'.mb_strtoupper($post['filter_recherche']).'%';
            $arrayReq[':value9']='%'.mb_strtoupper($post['filter_recherche']).'%';
            
            unset($post['filter_recherche']);
        }
        
        if (isset($post['SSFAM']) && $post['SSFAM']) {
            if ($post['SSFAMCond'] == 'egal') {
                $where .=" and CODE in (select CODE_FOUR from SSFAMFOU with(nolock) WHERE CODE_SFAM= :SSFAM)";
            } else {
                $where .=" and CODE not in (select CODE_FOUR from SSFAMFOU with(nolock) WHERE CODE_SFAM= :SSFAM)";
            }
            $arrayReq[':SSFAM']=$post['SSFAM'];
        }

        
        if (! empty($post['filter_DOMAINE'])) {
            $post['DOMAINE'] = $post['filter_DOMAINE'];
            unset($post['filter_DOMAINE']);
        }
        if (! empty($post['DOMAINE'])) {
            $where .= " AND "
                .($post['DOMAINECond'] == 'egal'? '' : 'NOT')
                ." EXISTS (
                    select SSFAMFOU.CODE_FOUR from D_C_FAM_SSFAM with (nolock)
                    join SSFAMFOU with (nolock) 
                        on SSFAMFOU.CODE_SFAM = D_C_FAM_SSFAM.CODE
                        and SSFAMFOU.CODE_FOUR = F.CODE
                    WHERE D_C_FAM_SSFAM.CODE_DC = :filter_domaine
                )";
            $arrayReq[':filter_domaine'] = $post['filter_DOMAINE'];
            unset($post['DOMAINE']);
        }

        if (isset($post['CLEFFOUR']) && $post['CLEFFOUR']) {
            if ($post['CLEFFOURCond'] == 'egal') {
                $where .=" and CODE in (select COD_FOUR from CLEFFOUR with(nolock) WHERE MOTCLEF= :CLEFFOUR)";
            } else {
                $where .=" and CODE not in (select COD_FOUR from CLEFFOUR with(nolock) WHERE MOTCLEF= :CLEFFOUR)";
            }

            $arrayReq[':CLEFFOUR']=$post['CLEFFOUR'];
        }

        if (isset($post['RAT_PART']) && $post['RAT_PART']) {
            $ratPart = " AND ";
            if ($post['RAT_PART'] == 'NON') {
                $ratPart .= " not ";
            }
            $ratPart .= "exists (select CODE_FOU from FOUR_PARTENAIRE_DATA with (nolock) where CODE_FOU = F.CODE)";
            
            $where .= $ratPart;
        }

        if (isset($post['filter_RS']) && $post['filter_RS'] != '') {
            $where .=" and (UPPER(F.RS) LIKE :RSS1  or UPPER(F.ENSCOMM) LIKE :RSS2 )";
            $arrayReq[':RSS1']='%'.mb_strtoupper($post['filter_RS']).'%';
            $arrayReq[':RSS2']='%'.mb_strtoupper($post['filter_RS']).'%';
            unset($post['filter_RS']);
        }
        if (isset($post['filter_motclef']) && $post['filter_motclef'] != '') {
            $where .=" and EXISTS (SELECT MOTCLEF FROM CLEFFOUR with (nolock) WHERE COD_FOUR = F.CODE and MOTCLEF=:value11)";
            $arrayReq[':value11']=$post['filter_motclef'];
            
            unset($post['filter_motclef']);
        }

        if (isset($post['filter_sousFamille']) && $post['filter_sousFamille'] != '') {
            $where .=" and EXISTS (SELECT CODE_SFAM FROM SSFAMFOU with (nolock) WHERE CODE_FOUR = F.CODE and CODE_SFAM=:value12)";
            $arrayReq[':value12']=$post['filter_sousFamille'];
            
            unset($post['filter_sousFamille']);
        }

        if (isset($post['filter_article']) && $post['filter_article'] != '') {
            $where .=" and EXISTS (SELECT CODE_ART FROM ARTFOURN with (nolock) WHERE FOU = F.CODE and CODE_ART=:value13)";
            $arrayReq[':value13']=$post['filter_article'];
            
            unset($post['filter_article']);
        }

        if (isset($post['filter_typeAchat']) && $post['filter_typeAchat'] != '') {
            $where .=" and EXISTS (SELECT TACFOTAC FROM TAC_TYFO with (nolock) WHERE TACFOFOU = F.CODE and TACFOTAC=:value14)";
            $arrayReq[':value14']=$post['filter_typeAchat'];
            
            unset($post['filter_typeAchat']);
        }

        if (isset($post['filter_valid']) && $post['filter_valid'] != '') {
            if ($post['filter_valid']=='VALIDE') {
                $where .= " and ((F.DTEVALMIN<='".date('Ymd')."') AND F.DTEVALMAX>='".date('Ymd')."' or F.DTEVALMAX='00000000')";
            }
            if ($post['filter_valid']=='NON_VALIDE') {
                $where .=" and ((F.DTEVALMAX!='00000000' and F.DTEVALMAX<'".date('Ymd')."'))";
            }
            
            unset($post['filter_valid']);
        }
        
        if (isset($post['filter_PARTENAIRE']) && $post['filter_PARTENAIRE'] != '') {
            $partenaire = $post['filter_PARTENAIRE'];
            
            $where .=  " and F.CODE in (select CODE_FOU from FOUR_PARTENAIRE_DATA with (nolock) where CODEPARTENAIRE = '".$partenaire."') ";
            
            unset($post['filter_PARTENAIRE']);
        }
        
        
        if (!empty($post['filter_DemPart'])) {
            $where .= " AND EXISTS(
                SELECT NULL FROM ENTITYMETA with(nolock) 
                WHERE ENTITYMETA.ENTITY_TYPE = 'FOUR'
                AND ENTITYMETA.ENTITY_ID = F.CODE
                AND ENTITYMETA.STRUCT_TYPE = "
                    . Sql::quoteString(EntityMetaQb::getStructKey(PartnFournissDemModif::class))
                . ") ";
            unset($post['filter_DemPart']);
        }

        //______________

        $whereArray = FilterTable::splitFilter($post, 0, 0, 1, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $where .= $filtreAvance->generateSql(1);
         
        $filtreAvance->pushArray($arrayReq);
        if (count($whereArray)>  0) {
            if ($whereArray['tri']??''!='') {
                $tri = " order by CODE_FOUGR DESC ".$whereArray['tri'];
                $where .= $whereArray[0];
            } else {
                $tri = "  order by CODE_FOUGR DESC, F.RS";
                $where .= $whereArray[0];
            }
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        return [
            'where' => $where,
            'tri' => ($tri??''),
            'param' => $arrayReq,
        ];
    }


    private function getDelaiRelance() :string
    {
        $value = (new Paravrac($this->pdo))->getGeneric("FOURN_DOCRAT_DELAIRELANCE");
        return explode("|", $value)[1] ?? '';
    }

    /**
     * listeSocieteFourniss
     * @param string $code
     * @return array<mixed>
     */
    public function listeSocieteFourniss(string $code) : ?array
    {
        $req = "select RAIS_SOC,SOCIETE.CODE_STE from LIENFOUGRPSTE with (nolock)
				left join SOCIETE with (nolock) on SOCIETE.CODE_STE=LIENFOUGRPSTE.CODE_STE
				where CODE_FOUGR=:value and TYPE_FG = 'F'";

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

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

    /**
     * @param array<string> $listFournissId
     * @return array<array<mixed>>
     */
    public function listSocieteForFour(array $listFournissId) :array
    {
        $param = [];
        return $this->fetchAssocAll(
            "SELECT SOCIETE.*, LIENFOUGRPSTE.CODE_FOUGR
            FROM SOCIETE with (nolock)
            JOIN LIENFOUGRPSTE with (nolock) on LIENFOUGRPSTE.CODE_STE = SOCIETE.CODE_STE
            WHERE " . $this->makeWhereIn('LIENFOUGRPSTE.CODE_FOUGR', $listFournissId, $param) . "
            AND TYPE_FG = 'F'",
            $param
        );
    }

    /**
     * @param array<array<mixed>> $listFourniss List of Fourniss entities
     */
    public function joinFournissListSoc(array &$listFourniss, string $key) :void
    {
        $this->arrayJoinManyToMany(
            $listFourniss,
            $this->listSocieteForFour(array_column($listFourniss, 'CODE')),
            'CODE',
            $key,
            'CODE_FOUGR'
        );
    }

    /**
     * addFavori
     * @param string $code
     * @param string $user
     * @return void
     */
    public function addFavori(string $code, string $user) : void
    {
        $req = "INSERT INTO FAVUSRFOUGR(CODE_USER,TYPE_FG,CODE_FOUGR) 
            VALUES (:value1, 'F', :value2)";

        $arrayReq = [':value1' => $user, ':value2' => $code];
        $fav = $this->pdo->prepare($req);
        $fav->execute($arrayReq);
    }

    /**
     * rmvFavori
     * @param string $code
     * @param string $user
     * @return void
     */
    public function rmvFavori(string $code, string $user) : void
    {
        $req = "DELETE FROM FAVUSRFOUGR WHERE CODE_USER=:value1 and CODE_FOUGR=:value2";

        $arrayReq = [':value1' => $user, ':value2' => $code];
        $fav = $this->pdo->prepare($req);
        $fav->execute($arrayReq);
    }

    /**
     * statutDocFrs
     * @param string $code
     * @param mixed $critique
     * @return array<mixed>
     */
    public function statutDocFrs(string $code, $critique = true, string $codePays = '') : ?array
    {
        if (!$codePays) {
            $fourniss = new Fourniss($this->pdo);
            $fournisseur = $fourniss->find($code);
            
            $codePays = $fournisseur['CODE_PAYS'];
        }
        
        $req = "select 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='$code') d
                    FULL JOIN (
            			select distinct(LST_DOC_DEM.CODE_DOC), LST_DOCS.TITRE_DOC, CODE_CATEGORIE,NUM_ORDRE, A_SURVEILLER, null Partenaire, null Corresp 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' AND CODE_ENREG='$codePays') 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 = '$code' and CODE_DC is not null )) 
                ) p  ON d.TITRE = p.TITRE_DOC
                    WHERE CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = ".($critique? '0x54':'0x46').")
                    ORDER BY CASE WHEN d.DT_VALID='00000000' THEN 0 ELSE 99999999 END DESC, d.DT_VALID  ASC";
        
        return $this->fetchAssocAll($req, []);
    }


    /**
     * dateFactureFrs
     * @param string $codeFrs
     * @return array<mixed>
     */
    public function dateFactureFrs(string $codeFrs) : ?array
    {
        $req = "select MAX(DATE_FAC) DATE_FAC from (
                select DATE_FAFO DATE_FAC from FAC_ENT with (nolock)
                where FAC_ENT.CODE_FOUR =:codeFrs 
                union
                select DTE_REG DATE_FAC from FAC_REG with (nolock)
                left join FAC_ENT with (nolock) on FAC_ENT.NUM_FACT = FAC_REG.NUM_FAC
                where FAC_ENT.CODE_FOUR =:codeFrs2) as fac";

                $arrayReq = [
                    ':codeFrs' => $codeFrs,
                    ':codeFrs2' => $codeFrs,
                ];
                
                $requete = $this->pdo->prepare($req);
                
                $requete->execute($arrayReq);
                return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * listeTypeFournisseur
     * @return array<mixed>
     */
    public function listeTypeFournisseur() : ?array
    {
        $req="select * from LST_FOURN with (nolock) WHERE DTE_FIN='' or DTE_FIN>'".date('Ymd')."' or DTE_FIN='00000000' order by LIBELLE";
        
        return $this->fetchAssocAll($req, []);
    }

    /**
     * shortListeFourniss
     * @param string $code
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function shortListeFourniss(string $code, array $post = []) : ?array
    {
        $code = str_replace("'", "", $code);
        $code = str_replace("/", "", $code);
        $code = str_replace("\"", "", $code);
        $code = str_replace(" ", "", $code);

        $arrayReq = [];
        
        $req = "select F.CODFOURN, FF.CODLST, FF.LIBELLE, FF.COMM, FF.DTE_DEB, FF.DTE_FIN
                from (select * from FOU_LSTFOURN with (nolock) where CODFOURN='$code') F
                FULL JOIN (select * from LST_FOURN with (nolock)) FF on F.CODLST = FF.CODLST
                WHERE (DTE_FIN='' or DTE_FIN='00000000' or DTE_FIN>'".date('Ymd')."') ";

        if (isset($post['filter_LIBELLE']) && $post['filter_LIBELLE'] != '') {
            $req .=" and UPPER(FF.LIBELLE) like UPPER(:value1)";
            $arrayReq[':value1']='%'.$post['filter_LIBELLE'].'%';
        }
        

        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (isset($whereArray['tri'])) {
            $req .= $whereArray['tri'];
        } else {
            $req .= " order by LIBELLE";
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * ParavraCreation
     * @return array<mixed>
     */
    // phpcs:ignore
    public function ParavraCreation() : ?array
    {
        $req = "SELECT * FROM PARAVRAC with (nolock) WHERE TYPEPARV='PARAMFOU' AND LIB01PARV!=''";

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

    /**
     * shortListeId
     * @param string $libelle
     * @return string
     */
    public function shortListeId(string $libelle) : ?string
    {
        $req = "SELECT * FROM LST_FOURN with (nolock) WHERE LIBELLE= :libelle ";

        $arrayReq[':libelle'] =  $libelle;
        return $this->fetchAssocOne($req, $arrayReq)['CODLST']??'';
    }

    /**
     * listeCAFournisseurXML
     */
    public function listeCAFournisseurXML($listeCA, $all = false)
    {
        /** @phpstan-ignore-next-line */
        if (empty($listeCA) || empty($listeCA->ChiffreAffaire)) {
            return [];
        }
        if ($all) {
            $req  = " select * from (";
        } else {
            $req  = " select TOP 5 * from (";
        }

        $first = true;
        // TODO : replace concat by param
        foreach ($listeCA->ChiffreAffaire as $CA) {
            if (!$first) {
                $req .= " union ";
            }
                $req .= " select '$CA->ANNEE' ANNEE, '$CA->CHIFF_AFF' CHIFF_AFF, '$CA->DEVISE_CA' DEVISE_CA, '".$CA->ANNEE->attributes()->ValeurModif."' OLD_ANNEE, '".$CA->CHIFF_AFF->attributes()->ValeurModif."' OLD_CHIFF_AFF, '".$CA->DEVISE_CA->attributes()->ValeurModif."' OLD_DEVISE_CA";

                $first=false;
        }

        $req .= " ) CA where 1=1 order by ANNEE DESC";

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

    /**
     * fournissEstEnSignature
     * @param string $codeEnr
     * @return array<mixed>
     */
    public function fournissEstEnSignature(string $codeEnr) : ?array
    {
        $req = "select * from WFT_DEM_ENT with (nolock) where TYPE_ENT='FOURNISS' and CODE_ENREG=:codeEnrg and STATUT_DEMANDE='1' and AUTEUR=:user";

        $arrayReq = [
            ':codeEnrg' => $codeEnr,
            ':user' => $_SESSION['Session_Login'],
        ];

        $requete = $this->pdo->prepare($req);

        $requete->execute($arrayReq);
        return $requete->fetch();
    }
    
    /**
     * listePartenaireFrs
     * @param array<string> $listeFrs
     * @return array<mixed>
     */
    public function listePartenaireFrs(array $listeFrs) : ?array
    {
        // TODO : use join instead of subquery ?
        $req = "SELECT CODE_FOU, CODEPARTENAIRE 
            FROM FOUR_PARTENAIRE_DATA with (nolock)
            WHERE CODE_FOU in (".implode(', ', $listeFrs).") 
                AND CODE_FOU not in (
				    SELECT ID3 from PARAMDICO with(nolock) where TYPEDICO ='INVIT_PARTFOU' and ALPHA2 != 'A' and ID1 = CODEPARTENAIRE)";
        
        $requete = $this->pdo->prepare($req);
        
        $requete->execute();
        return $requete->fetchAll(\PDO::FETCH_ASSOC);
    }
    
    /**
     * compteOxalink
     * @param string $email
     * @param string $codeFou
     * @param mixed $idFou
     * @return array<mixed>
     */
    public function compteOxalink(string $email, string $codeFou, $idFou) : ?array
    {
        $req = "SELECT * from OXALINK_USER with(nolock)
                left join CONTFOUR with(nolock) on CONTFOUR.OXALINK_ID = OXALINK_USER.USER_ID where OXALINK_USER.EMAIL=:email";
        
        $arrayReq = [
            ':email'    => $email,
        ];
        
        if ($codeFou && ($idFou || $idFou === '0' || $idFou === 0)) {
            $req .= " and (IND_FOU!=:idfou and CODE_FOU=:codeFou)";
            
            $arrayReq[':codeFou'] = $codeFou;
            $arrayReq[':idfou'] = $idFou;
        }
        
        $requete = $this->pdo->prepare($req);
        
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
     * parametrageFournisseurActif
     * @return array<mixed>
     */
    public function parametrageFournisseurActif() : ?array
    {
        $req = "select * from PARAVRAC with (nolock) where TYPEPARV = 'GEST_FOURN' and LIB01PARV='O'";
        
        $requete = $this->pdo->prepare($req);
        
        $requete->execute();
        return $requete->fetch();
    }
    
    /**
     * parametrageOngletFournisseur
     * @return array<mixed>
     */
    public function parametrageOngletFournisseur() : ?array
    {
        $paravrac = new Paravrac($this->pdo);
        return $paravrac->findAll(['TYPEPARV' => 'GEST_FOURN']);
    }
    
    /**
     * parametreObligatoire
     * @deprecated use Paravrac::getMap('PARAMFOU') instead
     * @return array<mixed>
     */
    public function parametreObligatoire() : ?array
    {
        $req = "select * from PARAVRAC  with (nolock) where TYPEPARV='PARAMFOU' and CODEPARV IN ('CTLCODEBIC', 'CTLIBAN', 'CTLCNOM', 'CTLCTELFIXE', 'CTLCTELP', 'CTLCMAIL', 'CTLADR', 'CTLVILLE', 'CTLCDP', 'CTLMDP', 'CTLMDL', 'FOU_SAISIE_SIRET', 'PRO_SAISIE_SIRET')";
    
        $requete = $this->pdo->prepare($req);
        
        $requete->execute();
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * shortListePresent
     * @param string $codeFrs
     * @return array<mixed>
     */
    public function shortListePresent(string $codeFrs) : ?array
    {
        $req = "select * from FOU_LSTFOURN  with (nolock) 
                LEFT JOIN LST_FOURN  with (nolock) on LST_FOURN.CODLST = FOU_LSTFOURN.CODLST 
                WHERE FOU_LSTFOURN.CODFOURN=:codeFrs and (DTE_FIN='' or DTE_FIN='00000000' or DTE_FIN>'20210108')";
        
        
        $arrayReq = [
            ':codeFrs' => $codeFrs,
        ];
        
        $requete = $this->pdo->prepare($req);
        
        $requete->execute($arrayReq);
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * contactFrsExist
     * @param string $email
     * @return array<mixed>
     */
    public function contactFrsExist(string $email) : ?array
    {
        $req = "select * from OXALINK_USER with (nolock) WHERE EMAIL = :email";
        
        $arrayReq = [
            ':email' => $email,
        ];
        
        $requete = $this->pdo->prepare($req);
        
        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getInfoOnFrsAndMainContact
     * @param string $codeFrs
     * @return array<mixed>
     */
    public function getInfoOnFrsAndMainContact(string $codeFrs) : ?array
    {
        $req = "select FOURNISS.*, t.NOM CTC_NOM, t.FONCTION CTC_FCT, t.TEL CTC_TEL, t.FAX CTC_FAX, t.TELPORT CTC_GSM, t.EMAIL CTC_MAIL " . "from FOURNISS with(nolock) left outer join (select top 1 * from CONTFOUR with (nolock) where CONTFOUR.CODE_FOU = :codeFrs and VIBOXAP=0x54 order by IND_FOU) t on FOURNISS.CODE = t.CODE_FOU " . "where  FOURNISS.CODE = :codeFrs2 ";

        
        $arrayReq = [
            ':codeFrs' => $codeFrs,
            ':codeFrs2' => $codeFrs
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getContactFournisseurAll
     * @param string $codeFrs
     * @return array<mixed>
     */
    public function getContactFournisseurAll(string $codeFrs) : ?array
    {
        $req = "select * from CONTFOUR with(nolock) where CODE_FOU = :codeFrs AND VIBOXAP = 0x54  ";

        
        $arrayReq = [
            ':codeFrs' => $codeFrs,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
}
