<?php

namespace Modules\Fournisseur\Repository;

use Entity\Model\ParamDico;
use Helpers\Filter\FilterTable;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class Indicateur extends AbsRepository
{


    /**
     * listePartenaire
     * @return array<mixed>
     */
    public function listePartenaire(): ?array
    {
        $req = "select * from PARAMDICO with (nolock) where TYPEDICO = 'CLIENT0_PARTFOU' and ALPHA1 = 'O'";

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

    /** INDICATEUR FOURISSEUR GLOBAL **/

    /**
     * nbrFournissActif
     * @return array<mixed>
     */
    public function nbrFournissActif(): ?array
    {
        $req = "SELECT 'Actif' libelle, count(*) value from FOURNISS with (nolock) where DTEVALMAX='00000000' or DTEVALMAX>CURRENT_TIMESTAMP
                union
                SELECT 'Total' libelle, COUNT(*) value FROM FOURNISS with (nolock)";

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

    /**
     * nbrFournissParPeriode
     * @param string $annee
     * @return array<mixed>
     */
    public function nbrFournissParPeriode(string $annee): ?array
    {
        $req = "SELECT count(distinct(cae.CODE_FOU)) value FROM CA_ENTET cae with (nolock)
                WHERE substring (cae.DTE_PERIO,1,4)=:date
                union
                SELECT COUNT (*) value FROM FOURNISS with (nolock) where DTEVALMAX='00000000' or DTEVALMAX>CURRENT_TIMESTAMP";

        $arrayReq = [':date' => $annee];

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

    /**
     * @return array<mixed>
     */
    public function totalCAParPeriode(string $annee)
    {
        $req = "select SUM(ENGAGE) CA, SUM(NB_CDES) NB_CDES 
                from CUM_PERI with (nolock) 
                WHERE CODE_PER = :annee and CODE_TAB = 'FOU'";

        $arrayReq = [':annee' => $annee];

        $req .= " group by CODE_TAB";

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

    /**
     * Summary of getCAFournisseurByPeriode
     * @param mixed $fournisseur
     * @param mixed $annee
     * @return array<mixed>
     */
    public function getCAFournisseurByPeriode($fournisseur = null, $annee = null)
    {
        $req = "select fca.CHIFF_AFF*1000 CA 
                from CHIFF_AFFAIRE_FOUR fca with(nolock)  
                where fca.ANNEE=:annee 
                and fca.CODE_FOUR=:codeFou";

        $arrayReq = [
            ':annee' => $annee,
            ':codeFou' => $fournisseur
        ];

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

    /**
     * @return array<mixed>
     */
    public function totalCAParPeriodeBySte(string $fournisseur, string $annee, string $codeSte)
    {
        $req = "select SUM(ENGAGE) CA, SUM(NB_CDES) NB_CDES 
                from STE_PERI with (nolock) 
                WHERE CODE_PER = :annee and CODE_TAB = 'FOU' and CODE_STE = :codeSte";

        $arrayReq = [
            ':annee' => $annee,
            ':codeSte' => $codeSte
        ];

        if ($fournisseur) {
            $req .= " and CODE_IDEN = :codeFou";
            $arrayReq[':codeFou'] = $fournisseur;
        }

        $req .= " group by CODE_TAB";

        return $this->fetchAssocOne($req, $arrayReq);
    }
    
    /**
     * totalCAFourPeriode
     */
    public function totalCAFourPeriode($annee): string
    {
        $req = "select SUM(cae.TOT_CDEHT) count from CA_ENTET cae with (nolock) 
                where cae.DTE_PERIO > " . ($annee - 1) . "0101 and cae.DTE_PERIO< " . ($annee + 1) . "1231 and cae.ETAT_CDE != 'Z' and cae.ETAT_CDE != 'A' ";

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

        $indicateur->execute();
        return $indicateur->fetch()['count'];
    }

    /**
     * moyenCAFourPeriode
     */
    public function moyenCAFourPeriode($annee): string
    {
        $req = "select AVG(cae.TOT_CDEHT) count from CA_ENTET cae with (nolock) 
                where cae.DTE_PERIO >" . ($annee - 1) . "0101 and cae.DTE_PERIO<" . ($annee + 1) . "1231 and cae.ETAT_CDE != 'Z' and cae.ETAT_CDE != 'A'";

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

        $indicateur->execute();
        return $indicateur->fetch()['count'];
    }

    /**
     * nbrIncidentFou
     * @param string $annee
     * @return array<mixed>
     */
    public function nbrIncidentFou(string $annee): ?array
    {
        $req = "select month(ssper.DATE_DEB) mois, count(IDUNIQUE) NOMBRE_INCIDENT from FOURNINC fic with (nolock)
                join SSPERIOD ssper with (nolock) on substring (ssper.DATE_DEB,1,6)=substring (fic.DTE_OCCUR,1,6)
                where ssper.CODE_PER=:date group by ssper.LIBELLE,ssper.DATE_DEB order by ssper.DATE_DEB";

        $arrayReq = [':date' => $annee];

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

    /**
     * palmaresFou
     * @param string $annee
     * @return array<mixed>
     */
    public function palmaresFou(string $annee): ?array
    {
        $req = "select  TOP (10) CODE_FOU,RS, sum(TOT_CDEHT) as MONTANT,count(NO_CDE) as NBRE_COMMANDE from (
                    SELECT  cae.CODE_FOU, cae.TOT_CDEHT,frs.RS, cae.NO_CDE from CA_ENTET cae with (nolock)
                    join SSPERIOD ssper with (nolock) on substring (ssper.DATE_DEB,1,6)=substring (cae.DTE_PERIO,1,6)
                    left outer join FOURNISS frs with (nolock) on frs.CODE=cae.CODE_FOU
                    where ssper.CODE_PER=:date and ETAT_CDE<>'A' ) graph group by CODE_FOU,RS order by MONTANT desc";

        $arrayReq = [':date' => $annee];

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

    /**INDICATEUR POUR UN FOURNISSEUR **/
    /**
     * nbrCommandeParAn
     * @param string $code
     * @param mixed $annee
     * @param mixed $societe
     */
    public function nbrCommandeParAn(string $code, $annee = null, $societe = null): string
    {
        $req = "select count(cae.NO_CDE) as count from CA_ENTET  cae with (nolock) 
        left outer join FOURNISS frs  with (nolock) on frs.CODE=cae.CODE_FOU
        where frs.CODE=:value
        and cae.ETAT_CDE in ('E', 'V', 'S')
        and SUBSTRING (DTE_PERIO,1,4) = :date";

        $arrayReq = [':value' => $code, ':date' => $annee];

        if ($societe) {
            $req .= " and CODE_STE= :societe";

            $arrayReq[':societe'] = $societe;
        }

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

        $indicateur->execute($arrayReq);
        return $indicateur->fetch()['count'];
    }

    /**
     * CaEngageParAn
     * @param string $code
     * @param mixed $annee
     * @param mixed $societe
     * @return string
     */
    // phpcs:ignore
    public function CaEngageParAn(string $code, $annee = null, $societe = null): string
    {
        $req = "select sum (cae.TOT_CDEHT) as sum from CA_ENTET  cae with (nolock) 
        left outer join FOURNISS frs  with (nolock) on frs.CODE=cae.CODE_FOU
        where frs.CODE=:value and cae.ETAT_CDE in ('E', 'V', 'S')
        and SUBSTRING (DTE_PERIO,1,4) = :date";

        $arrayReq = [':value' => $code, ':date' => $annee];

        if ($societe) {
            $req .= " and CODE_STE= :societe";

            $arrayReq[':societe'] = $societe;
        }

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

        $indicateur->execute($arrayReq);
        return $indicateur->fetch()['sum'];
    }

    /**
     * nbrIncident
     * @param string $code
     * @param mixed $annee
     * @param mixed $societe
     * @return int
     */
    public function nbrIncident(string $code, $annee = null, $societe = null): int
    {
        $req = "select 
                count(*) count
                from FOURNINC with (nolock) 
                left join CA_ENTET with(nolock) on CA_ENTET.NO_CDE = FOURNINC.NO_CDE
                left join FAC_ENT with(nolock) on FAC_ENT.NUM_FACT = FOURNINC.NO_FACT
                where FOU=:value
                and SUBSTRING (DTE_OCCUR,1,4) = :date
                ";

        $arrayReq = [':value' => $code, ':date' => $annee];

        if ($societe) {
            $req .= " and ((CA_ENTET.CODE_STE=:societe1) or  (FAC_ENT.CODE_STE=:societe2))";
            $arrayReq[':societe1'] = $societe;
            $arrayReq[':societe2'] = $societe;
        }

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute($arrayReq);
        return $indicateur->fetch()['count'] ?? 0;
    }

    /**
     * tauxDeDependance
     * @param string $code
     * @param mixed $annee
     * @param mixed $societe
     * @return mixed
     */
    public function tauxDeDependance(string $code, $annee = null, $societe = null)
    {
        $req = "SELECT round((sum (cae.TOT_CDEHT)/(
            select fca.CHIFF_AFF*1000 from CHIFF_AFFAIRE_FOUR fca with(nolock) 
            where fca.ANNEE=:date2 and fca.CODE_FOUR=:value))*100,2) as result
        from CA_ENTET  cae with (nolock) 
        left join FOURNISS frs with (nolock) on frs.CODE=cae.CODE_FOU
        where frs.CODE=:value2 and cae.ETAT_CDE in ('E', 'V', 'S')
        and SUBSTRING (cae.DTE_PERIO,1,4) =:date";

        $arrayReq = [':value' => $code, ':value2' => $code, ':date' => $annee, ':date2' => $annee];

        if ($societe) {
            $req .= " and CODE_STE= :societe";

            $arrayReq[':societe'] = $societe;
        }

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

        $indicateur->execute($arrayReq);
        return $indicateur->fetch()['result'];
    }

    /**
     * periodedispo
     * @param array<mixed> $params

     * @return array<mixed>
     */
    public function periodedispo(array $params = []): ?array
    {
        $whereClause = ' ORDER BY CODE_PER DESC ';
        $arrayReq = [];

        if (!empty($params)) {
            $whereClause = " AND CODE_PER BETWEEN :start AND :end 
            ORDER BY CODE_PER ASC ";
            $arrayReq[':start'] = $params['start'];
            $arrayReq[':end'] = $params['end'];
        }

        $req = "SELECT CODE_PER, LIBELLE 
            FROM PERIODES WITH (NOLOCK) 
            WHERE SUBSTRING(DATE_DEB,0,5) <= YEAR(CURRENT_TIMESTAMP)
            {$whereClause} ";

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

    /**
     * Summary of getLastPeriodeDispo
     * @param ?array<mixed> $params
     * @return ?string
     */
    public function getLastPeriodeDispo(?array $params = []): ?string
    {
        $whereClause = ' ORDER BY CODE_PER DESC ';
        $arrayReq = [];

        if (!empty($params)) {
            $whereClause = " AND CODE_PER BETWEEN :start AND :end 
            ORDER BY CODE_PER ASC ";
            $arrayReq[':start'] = $params['start'];
            $arrayReq[':end'] = $params['end'];
        }

        $req = "SELECT TOP 1 CODE_PER, LIBELLE 
            FROM PERIODES WITH (NOLOCK) 
            WHERE SUBSTRING(DATE_DEB,0,5) <= YEAR(CURRENT_TIMESTAMP)
            {$whereClause} ";

        return $this->fetchAssocOne($req, $arrayReq)['CODE_PER'] ?? null;
    }

    /**
     * getSuppliersGradesByPeriode
     * @param string $year
     * @param array<mixed> $statuses
     * @param string $ssFam
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getSuppliersGradesByPeriode(string $year, array $statuses, string $ssFam = '', array $post = []): ?array
    {
        $clauseSsFam = '';
        $clauseClass = '';
        $clauseStatus = '';
        $clauseSupplier = '';
        $params = [];
        $filters = $post;

        if ($ssFam != '') {
            $clauseSsFam = " AND ISNULL(SF.CODE_SFAM, '') = :CODE_SFAM ";
            $params[':CODE_SFAM'] = $ssFam;
        }

        if (($post['categorization_ranking'] ?? '') != '') {
            $clauseClass = " AND F.CLASSE = :CLASSE ";
            $params[':CLASSE'] = $post['categorization_ranking'];
        }

        if (($post['filter_CODE_FOU'] ?? '') != '') {
            $clauseSupplier = " AND (UPPER(F.RS) LIKE :RSS1 OR UPPER(CNF.CODE_FOU) LIKE :RSS2) ";
            $params[':RSS1'] = '%' . mb_strtoupper($post['filter_CODE_FOU']) . '%';
            $params[':RSS2'] = '%' . mb_strtoupper($post['filter_CODE_FOU']) . '%';
            unset($filters['filter_CODE_FOU']);
        }

        if (($post['status_ranking'] ?? '') != '') {
            switch ($post['status_ranking']) {
                case 'bad':
                    $params[':min_note'] = 0;
                    $params[':max_note'] = $statuses['ALPHA3'];
                    break;

                case 'mid':
                    $params[':min_note'] = intval($statuses['ALPHA3']) + 1;
                    $params[':max_note'] = $statuses['ALPHA4'];
                    break;

                case 'good':
                    $params[':min_note'] = $statuses['ALPHA4'];
                    $params[':max_note'] = 10;
                    break;

                default:
                    $params[':min_note'] = 0;
                    $params[':max_note'] = 10;
                    break;
            }
            $clauseStatus = " HAVING ROUND(AVG(CAST(CNF.NOTE AS DECIMAL)), 0, 0) BETWEEN :min_note AND :max_note ";
        }

        $query = "SELECT CNF.CODE_FOU, F.RS, ROUND(AVG(CAST(CNF.NOTE AS DECIMAL)), 0, 0) MOYENNE
            FROM CAMP_NOT_FIN CNF
                LEFT JOIN CAMP_ENT CE ON CE.ID_CAMP = CNF.ID_CAMP
                LEFT JOIN FOURNISS F ON F.CODE = CNF.CODE_FOU
                LEFT JOIN SSFAMFOU SF ON SF.CODE_FOUR = F.CODE
            WHERE CE.COD_PER = :COD_PER
                AND CNF.ID_THEME = ''
                AND CNF.ID_QUEST = 0
                AND CNF.CODE_FOU <> ''
                AND CE.STATUT = 'T'
                AND (F.DTEVALMAX = '00000000' OR F.DTEVALMAX > CONVERT(VARCHAR, CURRENT_TIMESTAMP))
                {$clauseClass}
                {$clauseSsFam}
                {$clauseSupplier}
            GROUP BY CNF.CODE_FOU, F.RS 
            {$clauseStatus} ";

        $params[':COD_PER'] = $year;
        $tmp = $params;

        $query = $this->applyFilter($query, $tmp, $filters);

        $paginatorGestion = new PaginatorGestion();
        $paginatorGestion->injecterSqlCount($query, $post, $params, $this->pdo);

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

    /**
     * Summary of getCountSuppliersGrades
     * @param string $year
     * @param array<mixed> $statuses
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getCountSuppliersGrades($year, $statuses, $post)
    {
        $params = [];
        $clauseStatus = '';
        if (($post['status_ranking'] ?? '') != '') {
            switch ($post['status_ranking']) {
                case 'bad':
                    $params[':min_note'] = 0;
                    $params[':max_note'] = $statuses['ALPHA3'];
                    break;

                case 'mid':
                    $params[':min_note'] = intval($statuses['ALPHA3']) + 1;
                    $params[':max_note'] = $statuses['ALPHA4'];
                    break;

                case 'good':
                    $params[':min_note'] = $statuses['ALPHA4'];
                    $params[':max_note'] = 10;
                    break;

                default:
                    $params[':min_note'] = 0;
                    $params[':max_note'] = 10;
                    break;
            }
            $clauseStatus = " HAVING ROUND(AVG(CAST(CNF.NOTE AS DECIMAL)), 0, 0) BETWEEN :min_note AND :max_note ";
        }

        $query = "SELECT COUNT(*) as TOTAL FROM (
                    SELECT CNF.CODE_FOU
                    FROM CAMP_NOT_FIN CNF
                        LEFT JOIN CAMP_ENT CE ON CE.ID_CAMP = CNF.ID_CAMP
                        LEFT JOIN FOURNISS F ON F.CODE = CNF.CODE_FOU
                        LEFT JOIN SSFAMFOU SF ON SF.CODE_FOUR = F.CODE
                    WHERE CE.COD_PER = :COD_PER
                        AND CNF.ID_THEME = ''
                        AND CNF.ID_QUEST = 0
                        AND CNF.CODE_FOU <> ''
                        AND CE.STATUT = 'T'
                        AND (F.DTEVALMAX = '00000000' OR F.DTEVALMAX > CONVERT(VARCHAR, CURRENT_TIMESTAMP))
                    GROUP BY CNF.CODE_FOU, F.RS
                    {$clauseStatus}
                ) as subquery";

        $params[':COD_PER'] = $year;
        return $this->fetchAssocOne($query, $params);
    }

    /**
     * getExportData
     * @param array<mixed> $evalParam
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getExportData(array $evalParam, array $post = []): ?array
    {
        $clauseSsFam = '';
        $clauseClass = '';
        $selectSsFam = ", '' AS SSFAM ";
        $params = [
            ':DATA_START' => $post['start'],
            ':DATA_END' => $post['end'],
        ];
                
        if ($post['ssFam'] != '') {
            $selectSsFam = ", '" . $post['ssFam'] . "' AS SSFAM ";
            $clauseSsFam = " AND CNF.CODE_FOU IN (SELECT DISTINCT(ssfam.CODE_FOUR) CODE_FOUR 
                FROM SSFAMFOU ssfam WITH(NOLOCK) 
                WHERE ssfam.CODE_SFAM = :CODE_SFAM) ";
            $params[':CODE_SFAM'] = $post['ssFam'];
        }

        if (($post['categorization'] ?? '') != '') {
            $clauseClass = " AND F.CLASSE = :CLASSE ";
            $params[':CLASSE'] = $post['categorization'];
        }
        
        $query = "SELECT CE.ID_CAMP, CE.COD_PER, CNF.CODE_FOU, CNF.NOTE, F.RS, F.CLASSE, '' AS STATUS, 0 AS TOTAL_ORDERED {$selectSsFam}
            FROM CAMP_NOT_FIN CNF
                LEFT JOIN CAMP_ENT CE ON CE.ID_CAMP = CNF.ID_CAMP
                LEFT JOIN FOURNISS F ON F.CODE = CNF.CODE_FOU
            WHERE CE.COD_PER BETWEEN :DATA_START AND :DATA_END
                AND CNF.ID_THEME = ''
                AND CNF.ID_QUEST = 0
                AND CNF.CODE_FOU <> ''
                AND CE.STATUT = 'T'
                AND (F.DTEVALMAX = '00000000' OR F.DTEVALMAX > CONVERT(VARCHAR, CURRENT_TIMESTAMP))
                {$clauseClass}
                {$clauseSsFam} ";

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

    /* CA et Nbre de commandes par sous-famille  */
    /**
     * cdeParSF
     * @param string $code
     * @param mixed $annee
     * @param mixed $societe
     * @return array<mixed>
     */
    public function cdeParSF(string $code, $annee = null, $societe = null) : ?array
    {
        $req = "SELECT cal.SSFAM,ISNULL(t.DESIGNAT, sfa.DESIGNAT) AS libelle, sum(cal.QTE_COMM * cal.PU) Prix, count(distinct(cal.NO_CDE)) value from CA_LIGNE cal with (nolock)
                left outer join SSFAMILL sfa with (nolock) on sfa.CODE_SFA=cal.SSFAM
                LEFT OUTER JOIN TRAD_SFA t WITH(NOLOCK) ON t.CODE_SFA = sfa.CODE_SFA AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
                left outer join CA_ENTET cae with (nolock) on cae.NO_CDE=cal.NO_CDE
                where cae.CODE_FOU=:value and SUBSTRING (cae.DTE_PERIO,1,4) = :date and cae.ETAT_CDE in ('E', 'V', 'S')";
        
        $arrayReq = [':value' => $code, ':date' => $annee];
        
        if ($societe) {
            $req .="  and cae.CODE_STE = :societe ";
            
            $arrayReq[':societe'] = $societe;
        }

        $req .= " group by cal.SSFAM, ISNULL(t.DESIGNAT, sfa.DESIGNAT)";

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

    /* CA et Nbre de commandes par sous-famille  */
    /**
     * cdeParSF
     * @param string $code
     * @param mixed $annee
     * @param mixed $societe
     * @return array<mixed>
     */
    public function montantCdeParSF(string $code, $annee = null, $societe = null): ?array
    {
        $req = "SELECT 
                    cal.SSFAM,ISNULL(t.DESIGNAT, sfa.DESIGNAT) AS libelle, 
                    sum( CASE WHEN cal.SOLD_PHYS = 0x54 then cal.QTE_COMM-cal.RAL else cal.QTE_COMM end * cal.PU * (CASE WHEN cae.VAL_DEVIS = 0 then 1 else cae.VAL_DEVIS end)) value 
                from CA_LIGNE cal with (nolock)
                left outer join SSFAMILL sfa with (nolock) on sfa.CODE_SFA=cal.SSFAM
                LEFT OUTER JOIN TRAD_SFA t WITH(NOLOCK) ON t.CODE_SFA = sfa.CODE_SFA AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
                left outer join CA_ENTET cae with (nolock) on cae.NO_CDE=cal.NO_CDE
                where cae.CODE_FOU=:value and SUBSTRING (cae.DTE_PERIO,1,4) = :date and cae.ETAT_CDE in ('E', 'V', 'S')";

        $arrayReq = [':value' => $code, ':date' => $annee];

        if ($societe) {
            $req .= "  and cae.CODE_STE = :societe ";

            $arrayReq[':societe'] = $societe;
        }

        $req .= " group by cal.SSFAM, ISNULL(t.DESIGNAT, sfa.DESIGNAT)";

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

    /* CA et Nbre de commandes par mois   */
    /**
     * cdeParMois
     * @param string $code
     * @param mixed $annee
     * @param mixed $societte
     * @return array<mixed>
     */
    public function cdeParMois(string $code, $annee = null, $societte = null): ?array
    {
        $req = "select SUBSTRING(cae.DTE_PERIO,5,2) as 'mois', count(cae.NO_CDE) 'NB_CDE',count(cae.NO_CDE) 'NB_CDE2', sum (cae.TOT_CDEHT) 'CA' from CA_ENTET cae with (nolock)
                left outer join FOURNISS frs with (nolock) on frs.CODE=cae.CODE_FOU
                where frs.CODE=:value
                and cae.ETAT_CDE in ('E', 'V', 'S')
                and SUBSTRING (DTE_PERIO,1,4) = :date";

        $arrayReq = [':value' => $code, ':date' => $annee];

        if ($societte) {
            $req .= "  and CODE_STE = :societe ";

            $arrayReq[':societe'] = $societte;
        }

        $req .= " group by SUBSTRING(cae.DTE_PERIO,5,2)";

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

    /**
     * nbrFrs
     * Indicateur Document
     *
     * @return array<mixed>
     */
    public function nbrFrs(): ?array
    {
        $req = "select count(*) FRS from FOURNISS with (nolock)";

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute();
        return $indicateur->fetch();
    }

    /**
     * nbrDocTotal
     * @param mixed $critique
     * @param mixed $surveillance
     * @return array<mixed>
     */
    public function nbrDocTotal($critique = null, $surveillance = null): ?array
    {
        $where = '';
        if ($critique === true) {
            $where = " and CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with (nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = 0x54)";
        }

        if ($surveillance === true) {
            $where .= " and A_SURVEILLER=0x54 ";
        }

        $req = "SELECT SUM(nb_doc) NB_DOC from (
        		select (
                    select count(distinct(LST_DOC_DEM.CODE_DOC)) nb_doc from LST_DOC_DEM with(nolock)
        			left join LST_DOCS with(nolock) on LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
        			where 1=1 $where 
                    and (
                        (CODE_TABLE='COUNTRY' AND CODE_ENREG=FOURNISS.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 = FOURNISS.CODE and CODE_DC is not null
                        ))
                    ) 
                ) nb_doc
        		from FOURNISS with (nolock)
        	) total
        ";

        //$req = "select count(*) NB_DOC from PARAMDICO with(nolock) where TYPEDICO='DOCATTFNR_LSTDOCS'";

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute();


        return $indicateur->fetch();
    }

    /**
     * nbrDocValide
     * @param mixed $critique
     * @param mixed $surveillance
     * @return array<mixed>
     */
    public function nbrDocValide($critique = null, $surveillance = null): ?array
    {
        $date = date('Ymd');

        $where = '';
        if ($critique === true) {
            $where .= " and CODE_CATEGORIE in (select CODE_CAT from DOC_CAT with(nolock) where CODE_TABLE='FOURNISS' and CRITIQUE = 0x54)";
        }

        if ($surveillance === true) {
            $where .= " and A_SURVEILLER=0x54 ";
        }

        $req = "
			select SUM(nbrDoc) NB_DOC from
                (
                 select COD_ENR, count(*)
					as nbrDoc  from DOC_ATT with(nolock) inner join (
                	select *   from FOURNISS with(nolock) 
                ) as fourniss on fourniss.CODE = DOC_ATT.COD_ENR and (DT_VALID>='$date'or DT_VALID='00000000')
				where NO_DA='FOURNISS' and DOC_ATT.TITRE in (
			 select DISTINCT(LST_DOCS.TITRE_DOC)  from LST_DOC_DEM with(nolock) 
			 left join LST_DOCS with(nolock)  on LST_DOCS.CODE_DOC = LST_DOC_DEM.CODE_DOC
			 where 1=1 $where and ((CODE_TABLE='COUNTRY' AND CODE_ENREG=fourniss.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 = fourniss.CODE and CODE_DC is not null )))
				) 
                 GROUP BY COD_ENR
                ) total
        ";

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute();
        return $indicateur->fetch();
    }


    /**
     * nbrFrsInvite
     * Indicateur partenaire
     *
     * @param string $partenaire
     * @return string
     */
    public function nbrFrsInvite(string $partenaire): string
    {
        $req = "select COUNT(ALPHA2) COUNT
                from PARAMDICO with(nolock)
                where TYPEDICO='INVIT_PARTFOU' AND ID1=:partenaire";

        $arrayReq = [':partenaire' => $partenaire];

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute($arrayReq);
        return $indicateur->fetch()['COUNT'];
    }

    /**
     * nbrFrsInscrit
     * @param string $partenaire
     * @return string
     */
    public function nbrFrsInscrit(string $partenaire): string
    {
        $req = "select COUNT(ALPHA2) COUNT
                from PARAMDICO with(nolock)
                where TYPEDICO='INVIT_PARTFOU' and ALPHA2 = 'A' AND ID1=:partenaire";

        $arrayReq = [':partenaire' => $partenaire];

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute($arrayReq);
        return $indicateur->fetch()['COUNT'];
    }

    /**
     * nbrDocCollectValide
     * @param string $partenaire
     * @return string
     */
    public function nbrDocCollectValide(string $partenaire): string
    {
        $date = date('Ymd');

        $req = "select count(COD_ENR) as nbrDocumentValide  
                from DOC_ATT with(nolock) 
                inner join (
                    select *   from FOUR_PARTENAIRE_DATA with(nolock) where DOC_LEGAUX=0x54 and CODEPARTENAIRE='$partenaire' 
                ) as fourniss on fourniss.CODE_FOU = DOC_ATT.COD_ENR where  GERE_PAR_PARTENAIRE =0x54 and DT_VALID>='$date'";

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute();
        return $indicateur->fetch()['nbrDocumentValide'];
    }

    /**
     * nbrDocDemande
     * @param string $partenaire
     * @return string
     */
    public function nbrDocDemande(string $partenaire): string
    {
        $req = "select count(*) nbrDocPartenairePourUnFournisseur 
                from PARAMDICO with(nolock)
                where TYPEDICO='DOCATTFNR_LSTDOCS_PARTNER' and ID1=:partenaire";

        $arrayReq = [':partenaire' => $partenaire];

        $indicateur = $this->pdo->prepare($req);
        $indicateur->execute($arrayReq);
        return $indicateur->fetch()['nbrDocPartenairePourUnFournisseur'];
    }
}
