<?php

namespace Modules\Ajax\Repository;

use Entity\Model\ConvArt;
use Entity\Model\OxaUserPanier;
use Entity\Model\Paravrac;
use Helpers\Filter\FilterTable;
use Helpers\Outil\Date;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Modules\GlobalApplication\Outil\UOService;
use Helpers\Outil\Roles;
use Modules\GlobalApplication\Repository\Centre;
use Modules\Panier\Repository\PanierEntete;
use Modules\Panier\Service\ListeClauseWhere;
use Entity\Model\Articles;
use Entity\Model\Convers;
use Modules\GlobalApplication\Repository\Utilisateur;

/* TODO réfléchir à une solution pour remplacer le COLLATE des requêtes filtres OXAMobility (pour les bases qui ne seraient pas de base en latin_general) / aller chercher le code_BIN avant la requête et le stocker dans une variable ? */

class Listes extends AbsRepository
{

    public bool $convertLibelle;

    public function __construct(\PDO $pdo, bool $convertLibelle = false)
    {
        parent::__construct($pdo);

        $this->convertLibelle = $convertLibelle;
    }

    public function societe($groupeSoc = null)
    {
        $req =
            "SELECT SOCIETE.CODE_STE, SOCIETE.RAIS_SOC 
        FROM (
            SELECT CODE_STE 
            FROM SOC_USR WITH (nolock) 
            LEFT JOIN SOCIETE WITH (nolock) ON SOCIETE.CODE_STE = SOC_USR.COD_STE 
            WHERE SOC_USR.COD_USR = :value1
        
            UNION
        
            SELECT HASTUTIL.CODE_STE 
            FROM HASTUTIL with(nolock)
            LEFT JOIN SOCIETE WITH (nolock) ON SOCIETE.CODE_STE = HASTUTIL.CODE_STE 
            WHERE CODE=:value2
        ) listeSte
        LEFT JOIN SOCIETE WITH (nolock) ON SOCIETE.CODE_STE = listeSte.CODE_STE";

        $arrayReq = [
            ':value1' => $_SESSION['Session_Login'],
            ':value2' => $_SESSION['Session_Login']
        ];

        if ($groupeSoc) {
            $req .= " WHERE GROUPE = :grpSoc";
            $arrayReq[':grpSoc'] = $groupeSoc;
        }

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


    public function findGroupedCompanies($post = [])
    {
        $multiCompanies = ($_SESSION['Session_UsrMultiSoc']);

        $sql =
            "SELECT DISTINCT  CODE_GRP, RS_GRP, CODE_GRP CODE_STE, RS_GRP RAIS_SOC
                FROM GROUPSOC g WITH (nolock), SOCIETE s WITH (nolock)
                WHERE g.CODE_GRP = s.GROUPE
                AND DTEVALMIN <= '".date('Ymd')."'
                AND (DTEVALMAX > '".date('Ymd')."' OR DTEVALMAX='00000000')";

        if (!$multiCompanies) {
            $sql .= "
                AND (
           CODE_STE in (
                        SELECT hastutil.CODE_STE
                        FROM HASTUTIL hastutil WITH (nolock)
                        WHERE hastutil.CODE='" . $_SESSION['Session_Login'] . "')
           OR CODE_STE in (
                            SELECT socUsr.COD_STE
                            FROM SOC_USR socUsr WITH (nolock)
                            WHERE socUsr.COD_USR='" . $_SESSION['Session_Login'] . "')
             )";
        }

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $sql .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    /**
     * listeArticles
     *
     * @param  array<mixed> $post
     * @param  string|bool $autoCompleteRow
     * @param  array<mixed> $param
     * @return array<mixed>
     */
    public function listeArticles(array $post, $autoCompleteRow = false, array $param = []): ?array
    {
        if ($autoCompleteRow) {
            $selectRow = $autoCompleteRow;
        } else {
            $selectRow = '*';
        }

        $req = "SELECT $selectRow FROM (
                    SELECT CODE_ART, CASE WHEN DES_ART IS NULL OR DES_ART = '' THEN DESIGN else DES_ART end DESIGN, DTEVALMIN, DTEVALMAX, ARTFRAISPORT, GENRERIQUE
                    FROM ARTICLES WITH (nolock)
                    left join TRAD_ART WITH (nolock) on TRAD_ART.COD_ART = ARTICLES.CODE_ART AND COD_LNG = :langue
                ) ARTICLES WHERE 1=1";

        $arrayReq[':langue'] = $_SESSION['Session_Langue'];

        // Validité
        if (isset($post['valide']) && $post['valide'] == 1) {
            $today = Date('Ymd');

            $req .=
                "  AND
            (
                DTEVALMIN <= $today
                AND (DTEVALMAX >= $today OR DTEVALMAX = 00000000)
            )";
        }

        if (isset($post['CODE_ART']) && $post['CODE_ART'] != '') {
            $req .= " AND (UPPER(CODE_ART) like upper(:codeArt) OR upper(DESIGN) like upper(:libelle))";

            $arrayReq[":codeArt"] = '%' . $post['CODE_ART'] . '%';
            $arrayReq[":libelle"] = '%' . $post['CODE_ART'] . '%';
        } elseif (isset($param['RechCode']) && $param['RechCode'] != '' && $param['RechCode'] != null) {
            $req .= '  AND (UPPER(CODE_ART) like upper(:code)';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['RechCode'] . '%');
        } elseif (isset($post['egal_CODE_ART']) && $post['egal_CODE_ART'] != '') {
            $req .= '  AND (UPPER(CODE_ART) = upper(:code))';
            $arrayReq[':code'] = mb_strtoupper($post['egal_CODE_ART']);
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechLib']) && $param['RechLib'] != '' && $param['RechLib'] != null) {
            $req .= ' AND (upper(DESIGN) like upper(:LibelleOxamobility))';
            $arrayReq[':LibelleOxamobility'] = mb_strtoupper('%' . $param['RechLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['ClauseWhereAdd']) && $param['ClauseWhereAdd'] != '' && $param['ClauseWhereAdd'] != null) {
            $req .= ' AND ' . $param['ClauseWhereAdd'];
        }

        // filtre API OXA.MOBILITY site de stock
        if (isset($param['SiteStock']) && $param['SiteStock'] != '' && $param['SiteStock'] != null) {
            $req .= ' AND upper(STOCKE) like "' . \Helpers\Outil\Sql::setBoolSql(1) . '"';
            // $arrayReq[':siteStock'] = mb_strtoupper('%'.$post['SiteStock'].'%');
        }

        if (($param['OrderBy'] ?? '')) {
            $req .= " ORDER BY " . $param['OrderBy'];
        }

        if (!$this->convertLibelle) {
            $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
            if (count($whereArray) >  0) {
                $req .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $arrayReq = array_merge($arrayReq, $whereArray[1]);
                }
            }
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        }

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

    /**
     * getSupplierItems
     *
     * @param  array<mixed> $post
     * @param  array<mixed> $param
     * @return array<mixed>
     */
    public function getSupplierItems(array $post, array $param = []): ?array
    {
        $query = "SELECT ARTICLES.CODE_ART, 
            CASE 
                WHEN DES_ART IS NULL OR DES_ART = '' 
                THEN ARTICLES.DESIGN 
                ELSE DES_ART 
            END DESIGN, ARTICLES.DTEVALMIN, ARTICLES.DTEVALMAX, ARTICLES.ARTFRAISPORT, ARTICLES.GENRERIQUE
        FROM ARTICLES with(nolock)
            LEFT JOIN ARTFOURN WITH (NOLOCK) 
                ON ARTFOURN.CODE_ART = ARTICLES.CODE_ART
            LEFT JOIN TRAD_ART WITH (NOLOCK) 
                ON TRAD_ART.COD_ART = ARTICLES.CODE_ART 
                    AND TRAD_ART.COD_LNG = :COD_LNG
        WHERE ARTFOURN.FOU = :FOU";

        $params = [
            ':COD_LNG' => $_SESSION['Session_Langue'],
            ':FOU' => $post['art_fou']
        ];

        // Validité
        if (isset($post['valide']) && $post['valide'] == 1) {
            $today = Date('Ymd');

            $query .=
                "  AND
            (
                ARTICLES.DTEVALMIN <= '{$today}'
                AND (ARTICLES.DTEVALMAX >= '{$today}' OR ARTICLES.DTEVALMAX = '00000000')
            )";
        }

        if (isset($post['CODE_ART']) && $post['CODE_ART'] != '') {
            $query .= " AND (UPPER(CODE_ART) like upper(:codeArt) OR upper(DESIGN) like upper(:libelle))";

            $params[":codeArt"] = '%' . $post['CODE_ART'] . '%';
            $params[":libelle"] = '%' . $post['CODE_ART'] . '%';
        } elseif (isset($param['RechCode']) && $param['RechCode'] != '' && $param['RechCode'] != null) {
            $query .= '  AND (UPPER(CODE_ART) like upper(:code)';
            $params[':code'] = mb_strtoupper('%' . $param['RechCode'] . '%');
        } elseif (isset($post['egal_CODE_ART']) && $post['egal_CODE_ART'] != '') {
            $query .= '  AND (UPPER(CODE_ART) = upper(:code))';
            $params[':code'] = mb_strtoupper($post['egal_CODE_ART']);
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechLib']) && $param['RechLib'] != '' && $param['RechLib'] != null) {
            $query .= ' AND (upper(DESIGN) like upper(:LibelleOxamobility))';
            $params[':LibelleOxamobility'] = mb_strtoupper('%' . $param['RechLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['ClauseWhereAdd']) && $param['ClauseWhereAdd'] != '' && $param['ClauseWhereAdd'] != null) {
            $query .= ' AND ' . $param['ClauseWhereAdd'];
        }

        // filtre API OXA.MOBILITY site de stock
        if (isset($param['SiteStock']) && $param['SiteStock'] != '' && $param['SiteStock'] != null) {
            $query .= ' AND upper(STOCKE) like "' . \Helpers\Outil\Sql::setBoolSql(1) . '"';
            // $params[':siteStock'] = mb_strtoupper('%'.$post['SiteStock'].'%');
        }

        if (($param['OrderBy'] ?? '')) {
            $query .= " ORDER BY " . $param['OrderBy'];
        }

        if (!$this->convertLibelle) {
            $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
            if (count($whereArray) >  0) {
                $query .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $params = array_merge($params, $whereArray[1]);
                }
            }
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($query, $post, $params, $this->pdo);
        }

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

    /**
     * getListeBr
     *
     * @param  array<mixed> $post
     * @return array<mixed>|false
     */
    public function getListeBr($post)
    {
        $req = "SELECT * FROM BR_RECEP with (nolock) WHERE 1=1 ";
        // Montant maxi pour l'utilisateur ou Montant maxi en tant que responsable d'une UO
        $arrayReq = [];

        $whereArray = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * getListBrFromOrderLine
     *
     * @param  array<mixed> $post
     * @return array<mixed>|false
     */
    public function getListBrFromOrderLine(array $post)
    {
        $query = "SELECT * FROM (SELECT NUM_BR, DATE_PER, NUM_CDE, VAL_DEV, DATE_CRE, TOTAL_HT, TOTAL_TTC, BON_LIVR, DATE_BL, DATE_VAL, VALDEVTER, NM_BNOTE, CODE_STE, CREATEUR, VAL_DEVSTE, TYP_BR, NO_BR_ANN, NM_COMMENT, NO_PREM_EXP
        FROM BR_RECEP brc with(nolock)
        WHERE EXISTS (SELECT NUM_BR FROM BR_LIGNE brl with(nolock) WHERE brl.NUM_BR = brc.NUM_BR AND brl.NO_PO_CDE = :NO_PO_CDE)
        ) BR_RECEP ";

        $params = [
            ':NO_PO_CDE'    => $post['ligneCommande'],
        ];

        if ($post['filter_date_DATE_CRE'] !== '') {
            $post['filter_DATE_CRE'] = $post['filter_date_DATE_CRE'];
        }

        $whereArray = FilterTable::splitFilter($post);
            
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);

        if (!empty($whereArray) &&  isset($whereArray[1])) {
            $query .= $whereArray[0].$whereArray['tri'];
            return $this->fetchAssocAll($query, $params + $whereArray[1]);
        }
        
        $reqProvisoire = $filtreAvance->generateSql(count($whereArray));
        
        $filtreAvance->pushArray($whereArray);
        
        if (!empty($whereArray)) {
            $query .= " WHERE ".$reqProvisoire;
            return $this->fetchAssocAll($query, $params + $whereArray[1]);
        }
        
        return $this->fetchAssocAll($query, $params + $whereArray[1]);
    }


    /**
     * getFrameworkItems
     *
     * @param  array<mixed> $post
     * @param  array<mixed> $param
     * @return array<mixed>
     */
    public function getFrameworkItems(array $post, array $param = []): ?array
    {
        $query = "SELECT ARTICLES.CODE_ART, 
            CASE 
                WHEN DES_ART IS NULL OR DES_ART = '' 
                THEN ARTICLES.DESIGN 
                ELSE DES_ART 
            END DESIGN, ARTICLES.DTEVALMIN, ARTICLES.DTEVALMAX, ARTICLES.ARTFRAISPORT, ARTICLES.GENRERIQUE
        FROM ARTICLES
            LEFT JOIN ACART WITH (NOLOCK)
                ON ACART.CODE_ART = ARTICLES.CODE_ART
            LEFT JOIN TRAD_ART WITH (NOLOCK) 
                ON TRAD_ART.COD_ART = ARTICLES.CODE_ART 
                    AND TRAD_ART.COD_LNG = :COD_LNG
        WHERE ACART.CODE_ACD = :CODE_ACD";

        $params = [
            ':COD_LNG' => $_SESSION['Session_Langue'],
            ':CODE_ACD' => $post['art_acd']
        ];

        // Validité
        if (isset($post['valide']) && $post['valide'] == 1) {
            $today = Date('Ymd');

            $query .=
                "  AND
            (
                ARTICLES.DTEVALMIN <= '{$today}'
                AND (ARTICLES.DTEVALMAX >= '{$today}' OR ARTICLES.DTEVALMAX = '00000000')
            )";
        }

        if (isset($post['CODE_ART']) && $post['CODE_ART'] != '') {
            $query .= " AND (UPPER(CODE_ART) like upper(:codeArt) OR upper(DESIGN) like upper(:libelle))";

            $params[":codeArt"] = '%' . $post['CODE_ART'] . '%';
            $params[":libelle"] = '%' . $post['CODE_ART'] . '%';
        } elseif (isset($param['RechCode']) && $param['RechCode'] != '' && $param['RechCode'] != null) {
            $query .= '  AND (UPPER(CODE_ART) like upper(:code)';
            $params[':code'] = mb_strtoupper('%' . $param['RechCode'] . '%');
        } elseif (isset($post['egal_CODE_ART']) && $post['egal_CODE_ART'] != '') {
            $query .= '  AND (UPPER(CODE_ART) = upper(:code))';
            $params[':code'] = mb_strtoupper($post['egal_CODE_ART']);
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechLib']) && $param['RechLib'] != '' && $param['RechLib'] != null) {
            $query .= ' AND (upper(DESIGN) like upper(:LibelleOxamobility))';
            $params[':LibelleOxamobility'] = mb_strtoupper('%' . $param['RechLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['ClauseWhereAdd']) && $param['ClauseWhereAdd'] != '' && $param['ClauseWhereAdd'] != null) {
            $query .= ' AND ' . $param['ClauseWhereAdd'];
        }

        // filtre API OXA.MOBILITY site de stock
        if (isset($param['SiteStock']) && $param['SiteStock'] != '' && $param['SiteStock'] != null) {
            $query .= ' AND upper(STOCKE) like "' . \Helpers\Outil\Sql::setBoolSql(1) . '"';
            // $params[':siteStock'] = mb_strtoupper('%'.$post['SiteStock'].'%');
        }

        if (($param['OrderBy'] ?? '')) {
            $query .= " ORDER BY " . $param['OrderBy'];
        }

        if (!$this->convertLibelle) {
            $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
            if (count($whereArray) >  0) {
                $query .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $params = array_merge($params, $whereArray[1]);
                }
            }
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($query, $post, $params, $this->pdo);
        }

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

    /**
     * @param string|bool $centreForZoneGeo
     * @return array<mixed>
     */
    public function calculZoneGeoCentre($centreForZoneGeo = '')
    {
        if ($centreForZoneGeo) {
            $centre = new Centre($this->pdo);
            $listeZoneGeo = $centre->ListeZoneGeoCentre((string)$centreForZoneGeo);

            return $listeZoneGeo;
        }

        return [];
    }

    /**
     * @param array<mixed> $post
     * @param string|array<mixed>|bool|null $centreForZoneGeo
     * @param array<mixed> $adresseExist
     * @return array<mixed>
     */
    public function adresseFournisseur($post, $centreForZoneGeo = false, bool $orderByOxaMobility = null, $adresseExist = [])
    {
        $listeZoneGeo = false;
        if (is_array($centreForZoneGeo)) {
            $listeZoneGeo = $centreForZoneGeo;
        } elseif ($centreForZoneGeo) {
            $listeZoneGeo = $this->calculZoneGeoCentre($centreForZoneGeo);
        }

        $req = "SELECT DISTINCT COD_ADR, LIBELLE, ADRESSE, VILLE1, VILLE, CPOSTAL, CODPAYS, NOMPAYS, CODE, TELEPHONE, FAX, TELEX, EMAIL, ADR_COMMANDE FROM (
                	SELECT 'ADR_FOU' as COD_ADR, RS LIBELLE, ADR ADRESSE, VILLE1, VILLE, CPOSTAL, FOURNISS.CODE_PAYS CODPAYS, COUNTRY.LIBELLE NOMPAYS, CODE, TEL TELEPHONE, FAX, TELEX, EMAIL, ADR_COMMANDE " . (is_array($listeZoneGeo) ? ', DCOUP, ZONEGEO ' : '') . " FROM FOURNISS WITH (nolock)
                    left join COUNTRY with(nolock) on COUNTRY.CODE_PAYS = FOURNISS.CODE_PAYS
                    " . (is_array($listeZoneGeo) ? " left join DGEOFOUR with(nolock) on DGEOFOUR.CODE_FOU = FOURNISS.CODE " : "") . "
                	union
                    SELECT 'REG' as COD_ADR, RS LIBELLE, ADR_REGL ADRESSE, VILREGL VILLE1, VILLE, CPREGL CPOSTAL, PAYSREGL CODPAYS, COUNTRY.LIBELLE NOMPAYS, CODE, '' as TELEPHONE, '' as FAX, '' as TELEX, '' as EMAIL, 0x46 " . (is_array($listeZoneGeo) ? ', DCOUP, ZONEGEO ' : '') . " FROM FOURNISS WITH (nolock)
                    left join COUNTRY with(nolock) on COUNTRY.CODE_PAYS = FOURNISS.CODE_PAYS
                    " . (is_array($listeZoneGeo) ? " left join DGEOFOUR with(nolock) on DGEOFOUR.CODE_FOU = FOURNISS.CODE " : "") . "
                	union 
                    SELECT ADRFOURN.COD_ADR, ADRFOURN.LIBELLE, ADRESSE, VILLE1, VILLE, CPOSTAL, CODPAYS, COUNTRY.LIBELLE NOMPAYS, COD_FOU CODE, TELEPHONE, FAX, TELEX, EMAIL, ADR_COMMANDE " . (is_array($listeZoneGeo) ? ', DCOUP, ZONEGEO ' : '') . " FROM ADRFOURN WITH (nolock)
                	left join COUNTRY with(nolock) on COUNTRY.CODE_PAYS = ADRFOURN.CODPAYS
                    " . (is_array($listeZoneGeo) ? " left join DGEOADRFOU with(nolock) on DGEOADRFOU.CODE_FOU = ADRFOURN.COD_FOU AND DGEOADRFOU.COD_ADR = ADRFOURN.COD_ADR " : "") . "      
                ) FOURNISS WHERE 1=1 ";
        $arrayReq = [];

        if ($listeZoneGeo) {
            $req .= " AND (";

            foreach ($listeZoneGeo as $zoneGeo) {
                $req .= "(DCOUP ='" . $zoneGeo['DCOUP'] . "' AND ZONEGEO='" . $zoneGeo['ZONEGEO'] . "') OR ";
            }

            $req .= " (DCOUP is NULL AND ZONEGEO is null))";
        }


        if (isset($post['ADR_COMMANDE']) && $post['ADR_COMMANDE']) {
            $req .= " AND ADR_COMMANDE = " . $post['ADR_COMMANDE'];
        }

        if ($adresseExist) {
            $req .= " and ADRESSE = :foundAdresse and VILLE1 = :foundVille and CPOSTAL = :foundCpostal and CODPAYS = :foundCodPays";

            $arrayReq[':foundAdresse']  = $adresseExist['ADR'];
            $arrayReq[':foundVille']    = $adresseExist['VILLE1'];
            $arrayReq[':foundCpostal']  = $adresseExist['CPOSTAL'];
            $arrayReq[':foundCodPays']  = $adresseExist['CODPAYS'];
        }

        //filtre API OXA.MOBILITY
        if (isset($post['RechCPOuVille']) && $post['RechCPOuVille'] != '' && $post['RechCPOuVille'] != null) {
            $req .= ' AND (upper(VILLE1) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :ville OR upper(CPOSTAL) LIKE :cpostal)';
            $arrayReq[':ville'] = mb_strtoupper('%' . $post['RechCPOuVille'] . '%');
            $arrayReq[':cpostal'] = mb_strtoupper('%' . $post['RechCPOuVille'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($post['RechCodeOuLib']) && $post['RechCodeOuLib'] != '' && $post['RechCodeOuLib'] != null) {
            $req .= ' AND (upper(COD_ADR) LIKE :code2 OR upper(LIBELLE) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :libelle2)';
            $arrayReq[':code2'] = mb_strtoupper('%' . $post['RechCodeOuLib'] . '%');
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $post['RechCodeOuLib'] . '%');
        }

        if (isset($post['RechAdrsFourniss']) && $post['RechAdrsFourniss'] != '' && $post['RechAdrsFourniss'] != null) {
            $req .= '   AND (upper(ADRESSE) LIKE :RechAdrsFourniss1 
                        or upper(VILLE) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE upper(:RechAdrsFourniss2) 
                        or upper(NOMPAYS) LIKE :RechAdrsFourniss3 
            )';

            $arrayReq[':RechAdrsFourniss1'] = mb_strtoupper('%' . $post['RechAdrsFourniss'] . '%');
            $arrayReq[':RechAdrsFourniss2'] = mb_strtoupper('%' . $post['RechAdrsFourniss'] . '%');
            $arrayReq[':RechAdrsFourniss3'] = mb_strtoupper('%' . $post['RechAdrsFourniss'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($post['ClauseWhereAdd']) && $post['ClauseWhereAdd'] != '' && $post['ClauseWhereAdd'] != null) {
            $req .= ' AND (' . $post['ClauseWhereAdd'] . ')';
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    public function adresseCentre($post)
    {
        $req = "SELECT * FROM (
                    SELECT CODE_CTR, 'ADR_CTR' COD_ADR, ADRESSE, VILLE1, CPOSTAL, PAYS CODPAYS FROM CENTRE WITH (nolock)
                    union
                    SELECT COD_CTR CODE_CTR, COD_ADR, ADRESSE, VILLE1, CPOSTAL, CODPAYS FROM ADRCTR WITH (nolock)
                ) CENTRE";

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    public function commandeModele($post)
    {
        $req = "SELECT * FROM CA_ENTET WITH (nolock) WHERE MODELE != ''";
        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);

        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    public function typeAchatFournisseur($post)
    {
        $req = "SELECT TACFOTAC,LIB_TAC,TYP_ACH 
        FROM TAC_TYFO WITH (nolock)
            LEFT JOIN TYPEACHAT WITH (nolock) on TYPEACHAT.TYP_ACH = TAC_TYFO.TACFOTAC";
        $arrayReq = [];
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);

        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

        if ($return) {
            return $return;
        }

        $req = "SELECT ta.TYP_ACH, 
            ISNULL(t.TRA_TAC, ta.LIB_TAC) AS LIB_TAC, 
            ta.STRAIT, 
            ta.INVEST, case when ta.TYP_ACH is not null then 1 end as DEFAUT 
            FROM TYPEACHAT ta	WITH (nolock) 
                LEFT JOIN TRAD_TAC t WITH(NOLOCK) ON t.TYP_ACH = ta.TYP_ACH AND t.COD_LNG = :lang
                JOIN PARAVRAC p WITH (nolock) ON p.LIB01PARV = ta.TYP_ACH  
            WHERE p.TYPEPARV='PARAMFOU' AND p.CODEPARV='INITTAC'";

        return $this->fetchAssocAll($req, [
            ':lang' => $_SESSION['Session_Langue']
        ]);
    }

    // phpcs:ignore
    public function CaEntet($post)
    {
        $req = "SELECT 
            NO_CDE, ACH, ETAT_CDE, TOT_CDEHT, CA_ENTET.DEVISE, OBJET_CA,
            (
                SELECT TOP 1 count(*) 
                FROM BR_RECEP brRecep WITH (nolock) 
                WHERE brRecep.NUM_CDE=CA_ENTET.NO_CDE
            ) ligneBr,
            HASTUTIL.NOM as HASTUTIL_NOM
        FROM CA_ENTET WITH (nolock) 
        LEFT JOIN SOCIETE with (nolock) on SOCIETE.CODE_STE = CA_ENTET.CODE_STE
        LEFT JOIN HASTUTIL with(nolock) ON CA_ENTET.ACH = HASTUTIL.CODE
        WHERE (ETAT_CDE ='E' OR ETAT_CDE ='S') AND ETAT_FIN =''
        " . Roles::getClauseSocieteOuvert('SOCIETE');

        $arrayReq = [];

        if (isset($post['CODE_STE']) &&  $post['CODE_STE'] != '') {
            $req .= ' AND CA_ENTET.CODE_STE = :codeSte';
            $arrayReq[':codeSte'] = $post['CODE_STE'];
        }

        if (isset($post['CODE_FOU']) &&  $post['CODE_FOU'] != '') {
            $req .= ' AND CODE_FOU = :codeFou';
            $arrayReq[':codeFou'] = $post['CODE_FOU'];
        }

        if (isset($post['filter_NO_CDE']) &&  $post['filter_NO_CDE'] != '') {
            $req .= ' AND NO_CDE = :noCDE';
            $arrayReq[':noCDE'] = $post['filter_NO_CDE'];
        }

        if (isset($post['filter_ETAT_CDE']) &&  $post['filter_ETAT_CDE'] != '') {
            if ($post['filter_ETAT_CDE'] == 'L') {
                $req .= " AND (NO_CDE in (SELECT DISTINCT(brRecep2.NUM_CDE) FROM BR_RECEP brRecep2 WITH (nolock)) AND ETAT_CDE='E')";
            } elseif ($post['filter_ETAT_CDE'] == 'E') {
                $req .= " AND (NO_CDE not in (SELECT DISTINCT(brRecep2.NUM_CDE) FROM BR_RECEP brRecep2 WITH (nolock)) AND ETAT_CDE='E')";
            } else {
                $req .= ' AND ETAT_CDE = :etat';
                $arrayReq[':etat'] = $post['filter_ETAT_CDE'];
            }
        }

        if (isset($post['filter_ACH']) &&  $post['filter_ACH'] != '') {
            $req .= " AND (UPPER(ACH) like :ach OR ACH in (SELECT CODE FROM HASTUTIL WITH (nolock) WHERE UPPER(NOM) LIKE '" . mb_strtoupper('%' . $post['filter_ACH'] . '%') . "'))";
            $arrayReq[':ach'] = mb_strtoupper('%' . $post['filter_ACH'] . '%');
        }

        if (isset($post['filter_TOT_CDEHT']) &&  $post['filter_TOT_CDEHT'] != '') {
            $req .= ' AND TOT_CDEHT = :totCDEHT';
            $arrayReq[':totCDEHT'] = $post['filter_TOT_CDEHT'];
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);

        if (count($whereArray) >  0) {
            $req .= $whereArray['tri'];
        }

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

    // TYPE D'ACHAT DANS LE CAS OU IL Y A UN FOURNISSEUR DE PRESENT
    public function typeAchatWith($idFourniss)
    {
        $req = "SELECT TACFOTAC FROM TAC_TYFO WITH  (nolock) WHERE TACFOFOU = :idFourniss";

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

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

    // Concernant l'écran des impression d'accord-cadre //
    public function fournisseurImpression($codeAcd)
    {
        $req = "SELECT * FROM FS_ACD WITH (nolock) WHERE CODE_ACD = :codeAcd";

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

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


    // Concernant l'écran des impression d'accord-cadre //
    /**
     * marches
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function marches(array $post): ?array
    {
        $req = "SELECT * FROM MARCHES WITH (nolock)";

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        } // (!$post) {
        //     $post['filter_valid']='VALIDE';
        //     $post['tri_RS'] = 'asc';
        // }

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

    public function centreLivraisonImpression($codesociete)
    {
        $req = "SELECT CODE_CTR,RAIS_SOC FROM CENTRE WITH (nolock) WHERE CODE_STE = :codeCentre AND LIVRAISON = 0x54";

        $arrayReq = [
            ':codeCentre' => $codesociete
        ];

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

    public function centreEmetteurImpression($codesociete)
    {
        $req = "SELECT CODE_CTR,RAIS_SOC FROM CENTRE WITH (nolock) WHERE CODE_STE = :codeCentre AND EMISSION = 0x54";

        $arrayReq = [
            ':codeCentre' => $codesociete
        ];

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

    // TYPE D'ACHAT DANS LE CAS OU IL N Y A PAS DE FOURNISSEUR PRESENT
    public function typeAchatWithout($idFourniss)
    {
        $req =
            "SELECT tyfo.TACFOFOU, tyfo.TACFOTAC, tyfo.TACFODEF, typ.LIB_TAC 
        FROM TAC_TYFO tyfo WITH (nolock) 
        INNER JOIN TYPEACHAT typ WITH (nolock) ON typ.TYP_ACH = tyfo.TACFOTAC 
        WHERE TACFOFOU = :idFourniss";

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

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

    public function societeImpression($codeAcd)
    {
        $req =
            "SELECT msco.CODEMARCH, msco.CODESOC 
        FROM MARCHSOC msco WITH (nolock) 
        INNER JOIN ACCCADRE acd WITH (nolock) ON acd.MARCHE = msco.CODEMARCH
        WHERE acd.CODE_ACD = :codeAcd";

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

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

    /**
     * centreByType
     * @param string $typeCentre
     * @param mixed $sqlLite
     * @return array<mixed>
     */
    public function centreByType(String $typeCentre = null, $post = [], $orderByOxaMobility = null, $sqlLite = false): ?array
    {
        $arrayReq = [];
        $droitAll = false;
        switch ($typeCentre) {
            case 'CENTRE_R':
                $typeCentre = 'LIVRAISON';

                if (Roles::droitsUtilisateur($this->pdo, 'HA_DA_TTCT', $_SESSION["Session_Login"])) {
                    $droitAll = true;
                }

                break;
            case 'CENTRE_E':
                $typeCentre = 'EMISSION';

                if (Roles::droitsUtilisateur($this->pdo, 'HA_DA_TTCE', $_SESSION["Session_Login"])) {
                    $droitAll = true;
                }
                break;
            case 'CENTRE_F':
                $typeCentre = 'FACTURAT';
                break;
        }

        if ($sqlLite == true) {
            $req = "SELECT CODE_CTR, RAIS_SOC ";
        } else {
            $req = "SELECT 
            CODE_CTR, RAIS_SOC, ADRESSE, VILLE, PAYS, TELEPHONE, FAX, E_MAIL, NUM_TVAIC, ZONELEGAL, CODE_STE, EMISSION, LIVRAISON, FACTURAT, CTRFACLIE, TRS_INTER, MOD_TRS, DEV_FEX, SEPMIL, SEPDEC, LNGFACT, LOGO_ENT, LOGO_PIE, PROFILDA, COD_MQT, SITSTOCK, CPOSTAL, VILLE1, NM_COMM, TX_TAXLOC, ACHETEUR, BRAUTOPREAFF ";
        }

        $req .=
            " FROM CENTRE WITH (nolock) 
            WHERE 1=1 ";

        if ($typeCentre) {
            $req .=
                " AND " . $typeCentre . " = " . \Helpers\Outil\Sql::setBoolSql(1);
            // $arrayReq[':typeCentre'] = $typeCentre;
        }

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

        if (isset($post['TYPE_SG']) && $post['TYPE_SG'] != '') {
            if ($post['TYPE_SG'] == 'S') {
                $post['filter_egal_CODE_STE'] = $post['CODE_STE'];
            } else {
                $req .= " AND CODE_STE in (SELECT CODE_STE FROM SOCIETE WITH (nolock) WHERE GROUPE = :grpSoc)";
                $arrayReq[':grpSoc'] = $post['GRP_SOC'];
            }
        }

        if (isset($post['code_ste']) && $post['code_ste'] != '') {
            $post['filter_egal_CODE_STE'] = $post['code_ste'];
        }

        //filtre API OXA.MOBILITY
        if (isset($post['RechCPOuVille']) && $post['RechCPOuVille'] != '' && $post['RechCPOuVille'] != null) {
            $req .= ' AND (upper(VILLE) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :ville OR upper(CPOSTAL) LIKE :cpostal)';
            $arrayReq[':ville'] = mb_strtoupper('%' . $post['RechCPOuVille'] . '%');
            $arrayReq[':cpostal'] = mb_strtoupper('%' . $post['RechCPOuVille'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($post['RechCodeOuLib']) && $post['RechCodeOuLib'] != '' && $post['RechCodeOuLib'] != null) {
            $req .= ' AND (upper(CODE_CTR) LIKE :code OR  upper(RAIS_SOC) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :libelle)';
            $arrayReq[':code'] = mb_strtoupper('%' . $post['RechCodeOuLib'] . '%');
            $arrayReq[':libelle'] = mb_strtoupper('%' . $post['RechCodeOuLib'] . '%');
        }

        // filtre API OXA.MOBILITY
        if (isset($post['RechCode']) && $post['RechCode'] != '' && $post['RechCode'] != null) {
            $req .= ' AND upper(CODE_CTR) LIKE :code2';
            $arrayReq[':code2'] = mb_strtoupper('%' . $post['RechCode'] . '%');
        }

        // filtre API OXA.MOBILITY
        if (isset($post['RechLib']) && $post['RechLib'] != '' && $post['RechLib'] != null) {
            $req .= ' AND upper(RAIS_SOC) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2';
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $post['RechLib'] . '%');
        }
        // filtre API OXA.MOBILITY site de stock
        if (isset($post['SiteStock']) && $post['SiteStock'] == 1 && $post['SiteStock'] != '' && $post['SiteStock'] != null) {
            $req .= " AND SITSTOCK != '' ";
        }

        //filtre API OXA.MOBILITY
        if (isset($post['ClauseWhereAdd']) && $post['ClauseWhereAdd'] != '' && $post['ClauseWhereAdd'] != null) {
            $req .= ' AND (' . $post['ClauseWhereAdd'] . ')';
        }

        //Clause where paramétrée pour panier
        if ($typeCentre == 'LIVRAISON' && isset($post['fromPanier']) && $post['fromPanier'] == true) {
            $listeService = new ListeClauseWhere($this->pdo);
            $req .= $listeService->generateClauseWhere('CENTRE_R', ['CENTRE_R' => 'CENTRE'], (int)$post['idUser']);
        }
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);

        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        if ($orderByOxaMobility) {
            $req .= " ORDER BY $orderByOxaMobility";
        }

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

    // phpcs:ignore
    public function Devise($params = [])
    {
        $arrayReq = [];
        $req = "SELECT 
                CODE_DEVI,
                " . $this->selectTrad('DEVISES.LIBELLE') . "
            FROM DEVISES WITH (NOLOCK)
            " . $this->joinTrad('DEVISES.LIBELLE', $arrayReq);

        $reqWhere = '';
        if (isset($params['not_INEURO']) && $params['not_INEURO'] == 1) {
            $today = Date('Ymd');
            $reqWhere = " WHERE INEURO = " . \Helpers\Outil\Sql::setBoolSql(0);
        }

        if (isset($params['ouvert']) && $params['ouvert'] == 1) {
            $today = Date('Ymd');
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= " (DEVISES.DTEFINVAL='00000000' OR DEVISES.DTEFINVAL>='" . $today . "')";
        }

        if (isset($params['filter_CODE_DEVI']) &&  $params['filter_CODE_DEVI'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(CODE_DEVI) like :codeDevi ';
            $arrayReq[':codeDevi'] = mb_strtoupper('%' . $params['filter_CODE_DEVI'] . '%');
        }

        if (isset($params['filter_LIBELLE']) &&  $params['filter_LIBELLE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(LIBELLE) like :libelle ';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $params['filter_LIBELLE'] . '%');
        }

        $req .= $reqWhere;
        $whereArray = FilterTable::splitFilter($params, 0, 0, $reqWhere ? 1 : 0);
        if ($whereArray) {
            $req .= $whereArray['tri'];
        }

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

    // phpcs:ignore
    public function CentreEmetteur($codeSte = null, $post = null)
    {
        $req = "SELECT cen.CODE_CTR, cen.RAIS_SOC, cen.CODE_STE , cen.ADRESSE, cen.CPOSTAL, cen.VILLE1 
                FROM CENTRE cen WITH (nolock) WHERE cen.EMISSION = " . \Helpers\Outil\Sql::setBoolSql(1);
        $arrayReq = [];

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

        if ($codeSte != '') {
            $req .= " AND cen.CODE_STE = :codeSte";
            $arrayReq = [
                ':codeSte' => $codeSte
            ];
        }

        if (isset($post['type']) && $post['type'] == 'marches') {
            $req .= " AND
            (
                CASE WHEN (SELECT STEMARCHE FROM MARCHES WITH (nolock)  WHERE CODE='" . $post['idType'] . "')=''
                    THEN
                        CASE WHEN cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['idType'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC WITH (nolock) WHERE CODEMARCH = '" . $post['idType'] . "')
                            THEN 1	
                            ELSE 0
                            END	
                    WHEN (SELECT STEMARCHE FROM MARCHES  WITH (nolock) WHERE CODE='" . $post['idType'] . "')<>'' 
                    THEN 
                        CASE WHEN cen.CODE_STE = (SELECT STEMARCHE FROM MARCHES  WITH (nolock) WHERE CODE='" . $post['idType'] . "') OR cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC WITH (nolock) WHERE CODEMARCH = '" . $post['idType'] . "')
                        THEN 1
                        ELSE 0
                        END
                    ELSE 0
                END =1
            )";
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);

        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    // phpcs:ignore
    public function CentreLivraison($codeSte = null, $post)
    {
        $req = "SELECT cen.CODE_CTR, cen.RAIS_SOC, cen.CODE_STE, cen.ADRESSE, cen.CPOSTAL, cen.VILLE1 
                FROM CENTRE cen WITH (nolock) WHERE cen.LIVRAISON = " . \Helpers\Outil\Sql::setBoolSql(1);

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

        if ($codeSte != '') {
            $req .= " AND cen.CODE_STE = :codeSte";
            $arrayReq = [
                ':codeSte' => $codeSte
            ];
        }

        if ($post) {
            if (isset($post['type']) && $post['type'] == 'marches') {
                $req .= " AND
                (
                    CASE WHEN (SELECT STEMARCHE FROM MARCHES  WITH (nolock) WHERE CODE='" . $post['idType'] . "')=''
                        THEN
                            CASE WHEN cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['idType'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC WITH (nolock) WHERE CODEMARCH = '" . $post['idType'] . "')
                                THEN 1	
                                ELSE 0
                                END	
                        WHEN (SELECT STEMARCHE FROM MARCHES with(nolock) WHERE CODE='" . $post['idType'] . "')<>'' 
                        THEN 
                            CASE WHEN cen.CODE_STE = (SELECT STEMARCHE FROM MARCHES WITH (nolock)  WHERE CODE='" . $post['idType'] . "') OR cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC with(nolock) WHERE CODEMARCH = '" . $post['idType'] . "')
                            THEN 1
                            ELSE 0
                            END
                        ELSE 0
                    END =1
                )";
            }
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);

        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    // phpcs:ignore
    public function SectionImputation($param, $panier = false, $orderByOxaMobility = null)
    {
        $postSave = $param;

        $req =
            "SELECT CONCAT(SECTIMPU.CODE_SI, SECTIMPU.CODE_STE) ID_SI,
            SECTIMPU.CODE_SI,
            SECTIMPU.LIBELLE libelleSi,
            SECTIMPU.CODE_STE,
            SECTIMPU.CODE_DIR, SECTIMPU.SICODG, IMPCODG,
        SECTIMPU.BUDGET, SECTIMPU.DATE_DEB, SECTIMPU.DATE_FIN,
        DIRECTIO.LIBELLE libelleDirectio, SOCIETE.RAIS_SOC, SOCIETE.DEVISE
        FROM SECTIMPU WITH (nolock) 
        LEFT JOIN DIRECTIO WITH (nolock) ON DIRECTIO.CODE_DIR = SECTIMPU.CODE_DIR AND DIRECTIO.CODE_STE = SECTIMPU.CODE_STE 
        LEFT JOIN SOCIETE WITH (nolock) ON SOCIETE.CODE_STE = SECTIMPU.CODE_STE 
        WHERE 1=1 ";

        $reqWhere = '';
        $arrayReq = [];

        if (!isset($param['CODE_STE']) && isset($param['codeSte'])) {
            $param['CODE_STE'] = $param['codeSte'];
        }

        if ($_SESSION['Session_Login']) {
            if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
                if (!isset($param['CODE_STE']) || $param['CODE_STE'] == '') {
                    if (!$_SESSION['Session_UsrMultiSoc']) {
                        $arrayReq[':codeSte'] = $_SESSION['Session_SocUsrProfil'];
                        $reqWhere =
                            " AND (SECTIMPU.CODE_STE = :codeSte OR SECTIMPU.CODE_STE in (
                            SELECT socUsr.COD_STE FROM SOC_USR socUsr WITH (nolock) WHERE socUsr.COD_USR = '" . $_SESSION['Session_Login'] . "'
                            )
                        )";
                    }
                }

                if (isset($param['CODE_STE']) && $param['CODE_STE'] != '') {
                    $reqWhere = " AND SECTIMPU.CODE_STE = :codeSte ";
                    $arrayReq[':codeSte'] = $param['CODE_STE'];
                }
            } else {
                $reqWhere = " AND SECTIMPU.CODE_STE = '' ";
            }
        } else {
            $reqWhere = " AND SECTIMPU.CODE_STE = :codeSte ";
            $arrayReq[':codeSte'] = $param['CODE_STE'];
        }

        $siPar = sql::paravrac($this->pdo, 'SOURCE_SI', 'OXAPROC');

        if ($panier) {
            $specifique = new \Helpers\Additional\Specifique\Specifique();
            $namespaceSpecifique = $specifique->specifiqueFunction('\\Modules\\Liste\\Service\\SpecifiqueSI');
            if (class_exists($namespaceSpecifique)) {
                $array = [
                    'filtreUO' => '',
                    'codeSte' => $param['CODE_STE']??'',
                    'CenterEmetteur' => ($param['centerEmetteur']??$_SESSION['Session_CodeCtrEmetteur']),
                ];

                $reqWhere  .= \Helpers\Additional\Specifique\Specifique::specifique('\\Modules\\Liste\\Service\\SpecifiqueSI', 'clauseSI', $this->pdo, $array);
            }
        }
        
        if ($panier && $siPar) {
            $infoPanier = (new PanierEntete($this->pdo))->getEntetePanier($_SESSION['id_panier'], $_SESSION['id_user']);

            if ($infoPanier && $infoPanier['LIV_CTR']) {
                $centreLivraison = $infoPanier['LIV_CTR'];
            } else {
                $centreLivraison = $_SESSION['Session_CodeCtrLivraison'];
            }

            if ($infoPanier && $infoPanier['LIV_CTR']) {
                $centreEmetteur = $infoPanier['CENTRE_EMETTEUR'];
            } else {
                $centreEmetteur = $_SESSION['Session_CodeCtrEmetteur'];
            }

            if ($siPar == 'UO') {
                $reqWhere   .= " AND CODE_UO in (select CodeUO from LstUO) ";
                $with = "with LstUO(CodeUO,UOMere, Niveau) as
                (
                    select u.CODE_UO, u.UO_MERE, 0 as Niveau from UO u
                    where u.CODE_UO in
                    (
                    select CODE_UO from HASTUTIL  where CODE=:codeUser1
                    union
                    select CODE_UO from UO  where RESP_HIER=:codeUser2
                    union
                    select CODE_UO from LSTUOPARUSR  where CODE_USER=:codeUser3
                    )
                    union all
                    select u2.CODE_UO,u2.UO_MERE, l.Niveau+1
                    from UO u2
                    join LstUO l  on u2.UO_MERE=l.CodeUO
                ) ";
                
                $req .= " left join LSTSIPARUO siuo with(nolock) on siuo.CODE_STESI=SECTIMPU.CODE_STE and siuo.CODE_SI=SECTIMPU.CODE_SI ";

                $arrayReq[':codeUser1'] = $arrayReq[':codeUser2'] = $arrayReq[':codeUser3'] = $param['USER'] ?? $_SESSION['Session_Login'];
            } elseif ($siPar == 'CEM' || $siPar == 'CLIV') {
                $reqWhere .= " AND SECTIMPU.CODE_STE= case when (select TRIM(LIB01PARV) from PARAVRAC with(nolock) where TYPEPARV='PARAMSTE' and CODEPARV='SISECTOR')='O' then :codeSte2 else SECTIMPU.CODE_STE end
                and SECTIMPU.CODE_DIR= :centre";
                $arrayReq[':centre'] = ($siPar == 'CEM') ? $centreEmetteur : $centreLivraison;
                $arrayReq[':codeSte2'] = $param['CODE_STE'];
            } elseif ($siPar == 'LSTCEM' || $siPar == 'LSTCLIV') {
                $req .= "left join CTR_LSTDIR c with(nolock) on c.CODE_DIR=SECTIMPU.CODE_DIR";
                $reqWhere   .= " AND SECTIMPU.CODE_STE= case when (select TRIM(LIB01PARV) from PARAVRAC with(nolock) where TYPEPARV='PARAMSTE' and CODEPARV='SISECTOR')='O' then :codeSte2 else SECTIMPU.CODE_STE end and c.CODE_CTR=:centre";
                $arrayReq[':centre'] = ($siPar == 'LSTCEM') ? $centreEmetteur : $centreLivraison;
                $arrayReq[':codeSte2'] = $param['CODE_STE'];
            }
        }


        /* FILTRES */
        $datejour = date('Ymd');
        if ((isset($param['valide_obligatoire']) && $param['valide_obligatoire'] == 1) ||
            (isset($param['si_valide']) && ($param['si_valide'] == 'on' || $param['si_valide'] == '1'))
        ) {
            $reqWhere .= " AND (SECTIMPU.DATE_DEB <= $datejour AND (SECTIMPU.DATE_FIN >= $datejour OR SECTIMPU.DATE_FIN = '00000000'))";
        }

        if (!empty($param['filter_date_DATE_DEB'])) {
            Date::dateToSql($param['filter_date_DATE_DEB']);
            $reqWhere .= ' AND SECTIMPU.DATE_DEB = :dateDeb ';
            $arrayReq[':dateDeb'] = $param['filter_date_DATE_DEB'];
            unset($param['filter_date_DATE_DEB']);
        }

        if (!empty($param['filter_date_DATE_FIN'])) {
            Date::dateToSql($param['filter_date_DATE_FIN']);
            $reqWhere .= ' AND SECTIMPU.DATE_FIN = :dateFin';
            $arrayReq[':dateFin'] = $param['filter_date_DATE_FIN'];
            unset($param['filter_date_DATE_FIN']);
        }

        if (!empty($param['filter_CODE_SI'])) {
            $reqWhere .= ' AND upper(SECTIMPU.CODE_SI) like :code ';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['filter_CODE_SI'] . '%');
            unset($param['filter_CODE_SI']);
        }

        if (!empty($param['filter_egal_CODE_SI'])) {
            $reqWhere .= ' AND upper(SECTIMPU.CODE_SI) = :code ';
            $arrayReq[':code'] = $param['filter_egal_CODE_SI'];
            unset($param['filter_egal_CODE_SI']);
        }

        if (!empty($param['filter_LIBELLE'])) {
            $reqWhere .= ' AND upper(SECTIMPU.LIBELLE) like :libelle ';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['filter_LIBELLE'] . '%');
            unset($param['filter_LIBELLE']);
        }

        if (!empty($param['filter_CODE_STE'])) {
            $filtreSte = addcslashes($param['filter_CODE_STE'], '%_');
            $reqWhere .= ' AND (upper(SECTIMPU.CODE_STE) like \'' . mb_strtoupper('%' . $filtreSte . '%') . '\' OR upper(SOCIETE.RAIS_SOC) like \'' . mb_strtoupper('%' . $filtreSte . '%') . '\') ';
            unset($param['filter_CODE_STE']);
        }

        if (!empty($param['filter_CODE_DIR'])) {
            $codeDir = addcslashes($param['filter_CODE_DIR'], '%_');
            $reqWhere .= ' AND (upper(SECTIMPU.CODE_DIR) like \'' . mb_strtoupper('%' . $codeDir . '%') . '\' OR upper(DIRECTIO.LIBELLE) like \'' . mb_strtoupper('%' . $codeDir . '%') . '\') ';
            unset($param['filter_CODE_DIR']);
        }

        if (!empty($param['filter_DEVISE'])) {
            $reqWhere .= ' AND SOCIETE.DEVISE like :devise ';
            $arrayReq[':devise'] = strtoupper('%' . $param['filter_DEVISE'] . '%');
            unset($param['filter_DEVISE']);
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechCodeOuLib']) && $param['RechCodeOuLib'] != '' && $param['RechCodeOuLib'] != null) {
            $reqWhere .= ' AND (upper(SECTIMPU.CODE_SI) like :code2 OR upper(SECTIMPU.LIBELLE) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2)';
            $arrayReq[':code2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['ClauseWhereAdd']) && $param['ClauseWhereAdd'] != '' && $param['ClauseWhereAdd'] != null) {
            $reqWhere .= " AND ".$param['ClauseWhereAdd'];
        }

        /* FIN FILTRES */
        /* DEBUT TRI */
        if (!empty($param['tri_LIBELLE'])) {
            $param['tri_SECTIMPU.LIBELLE'] = $param['tri_LIBELLE'];
            unset($param['tri_LIBELLE']);
        }

        /* FIN TRI */

        $req = $req . $reqWhere;

        $whereArray = FilterTable::splitFilter($param, 0, 0, $reqWhere ? 1 : 0);
        if ($whereArray) {
            $req .= $whereArray['tri'];
        } elseif ($orderByOxaMobility) {
            $req = "SELECT * FROM ($req) test";
            $orderByOxaMobility = str_replace('LIBELLE', 'libelleSi', $orderByOxaMobility);
            $req .= " ORDER BY $orderByOxaMobility";
        } else {
            $req .= " ORDER BY SECTIMPU.CODE_SI ASC";
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $postSave, $arrayReq, $this->pdo, false, $with??'');
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /*public function listeCodeComp($post)
    {
        $req = "SELECT * FROM CODECOMP WHERE 1=1";
        $arrayReq=[];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, $this->pdo, 0,0,1);

        if(isset($post['CODE_STE']) && $post['CODE_STE'])
        {
            $req .= " AND CODE_STE = :value";
            $arrayReq[':value']=$post['CODE_STE'];
        }

        if(count($whereArray )>  0)
        {
            $req .= $whereArray[0].$whereArray['tri'];

            if(count($whereArray[1]) != 0)
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $param, $arrayReq,  $this->pdo);

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

    // phpcs:ignore
    public function Cdc($param, $orderByOxaMobility = null)
    {

        $req = "SELECT 
            CONCAT(CODECOMP.CODE_CDC, CODECOMP.CODE_STE) ID_CDC,
            CODECOMP.CODE_CDC, CODECOMP.LIBELLE libelleCdc, 
            CODECOMP.CODE_STE, CODECOMP.CODNAN, 
            CODECOMP.BUDGET, CODECOMP.DATE_DEB, CODECOMP.DATE_FIN,
            NATANA.LIBELLE libelleNature, SOCIETE.RAIS_SOC, SOCIETE.DEVISE 
        FROM CODECOMP WITH (nolock)
        LEFT JOIN NATANA WITH (nolock) ON NATANA.CODNAN = CODECOMP.CODNAN  AND NATANA.CODE_STE = CODECOMP.CODE_STE
        LEFT JOIN SOCIETE WITH (nolock) ON SOCIETE.CODE_STE = CODECOMP.CODE_STE";


        $reqWhere = '';
        $arrayReq = [];

        if (\Helpers\Outil\Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')) {
            if (!isset($param['CODE_STE']) || $param['CODE_STE'] == '') {
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $arrayReq[':codeSte'] = $_SESSION['Session_SocUsrProfil'];
                    $reqWhere =
                        " WHERE (CODECOMP.CODE_STE = :codeSte OR CODECOMP.CODE_STE in (
                        SELECT socUsr.COD_STE FROM SOC_USR socUsr WITH (nolock) WHERE socUsr.COD_USR = '" . $_SESSION['Session_Login'] . "'))";
                }
            }

            if (isset($param['CODE_STE']) && $param['CODE_STE'] != '') {
                $reqWhere = " WHERE CODECOMP.CODE_STE = :codeSte ";
                $arrayReq[':codeSte'] = $param['CODE_STE'];
            }
        } else {
            $reqWhere = " WHERE CODECOMP.CODE_STE = '' ";
        }

        /* FILTRES */
        $datejour = date('Ymd');
        if ((isset($param['valide_obligatoire']) && $param['valide_obligatoire'] == 1) || (isset($param['cdc_valide']) && ($param['cdc_valide'] == 'on' || $param['cdc_valide'] == '1'))) {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= " (CODECOMP.DATE_DEB <= $datejour AND (CODECOMP.DATE_FIN >= $datejour OR CODECOMP.DATE_FIN = '00000000'))";
            //$arrayReq[':datej'] = $datejour;
        }

        if (isset($param['filter_date_DATE_DEB']) &&  $param['filter_date_DATE_DEB'] != '') {
            $date = $param['filter_date_DATE_DEB'];
            \Helpers\Outil\Date::dateToSql($date);

            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' CODECOMP.DATE_DEB = :dateDeb ';
            $arrayReq[':dateDeb'] = $date;
        }

        if (isset($param['filter_date_DATE_FIN']) &&  $param['filter_date_DATE_FIN'] != '') {
            $date = $param['filter_date_DATE_FIN'];
            \Helpers\Outil\Date::dateToSql($date);

            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' (CODECOMP.DATE_FIN = :dateFin OR CODECOMP.DATE_FIN=\'00000000\') ';
            $arrayReq[':dateFin'] = $date;
        }

        if (isset($param['filter_CODE_CDC']) &&  $param['filter_CODE_CDC'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(CODECOMP.CODE_CDC) like :code ';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['filter_CODE_CDC'] . '%');
        } else {
            if (!empty($param['filter_egal_CODECOMP.CODE_CDC'])) {
                $reqWhere .= ' upper(CODECOMP.CODE_CDC) like :code ';
                $arrayReq[':code'] = mb_strtoupper('%' . $param['filter_egal_CODECOMP.CODE_CDC'] . '%');
                unset($param['filter_egal_CODECOMP.CODE_CDC']);
            }
        }

        if (isset($param['filter_LIBELLE']) &&  $param['filter_LIBELLE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(CODECOMP.LIBELLE) like :libelle ';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['filter_LIBELLE'] . '%');
        }

        if (isset($param['filter_CODE_STE']) &&  $param['filter_CODE_STE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $filtreSte = addcslashes($param['filter_CODE_STE'], '%_');
            $reqWhere .= ' (upper(CODECOMP.CODE_STE) like \'' . mb_strtoupper('%' . $filtreSte . '%') . '\' OR upper(SOCIETE.RAIS_SOC) like \'' . mb_strtoupper('%' . $filtreSte . '%') . '\') ';
        }

        if (isset($param['filter_CODNAN']) &&  $param['filter_CODNAN'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $codeDir = addcslashes($param['filter_CODNAN'], '%_');
            $reqWhere .= ' (upper(CODECOMP.CODNAN) like \'' . mb_strtoupper('%' . $codeDir . '%') . '\' OR upper(NATANA.LIBELLE) like \'' . mb_strtoupper('%' . $codeDir . '%') . '\') ';
        }

        if (isset($param['filter_DEVISE']) &&  $param['filter_DEVISE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' SOCIETE.DEVISE like :devise';
            $arrayReq[':devise'] = strtoupper('%' . $param['filter_DEVISE'] . '%');
        }

        if (isset($param['filter_BUDGET']) &&  $param['filter_BUDGET'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' CODECOMP.BUDGET = :budget';
            $arrayReq[':budget'] = $param['filter_BUDGET'];
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechCodeOuLib']) && $param['RechCodeOuLib'] != '' && $param['RechCodeOuLib'] != null) {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' (upper(CODECOMP.CODE_CDC) like :code2 OR upper(CODECOMP.LIBELLE) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2)';
            $arrayReq[':code2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['ClauseWhereAdd']) && $param['ClauseWhereAdd'] != '' && $param['ClauseWhereAdd'] != null) {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= $param['ClauseWhereAdd'];
        }

        //Clause where paramétrée pour panier
        if (isset($param['fromPanier']) && $param['fromPanier'] == true) {
            $clauseExistante = ($reqWhere != '');
            $listeService = new ListeClauseWhere($this->pdo);
            $reqWhere .= $listeService->generateClauseWhere('CODECOMP', ['CODECOMP' => 'CODECOMP'], $param['idUser'], $clauseExistante);
        }

        /* FIN FILTRES */

        $req = $req . $reqWhere;
        $whereArray = FilterTable::splitFilter($param, 0, 0, $reqWhere ? 1 : 0);
        if ($whereArray) {
            $req .= $whereArray['tri'];
        } elseif ($orderByOxaMobility) {
            $req = "SELECT * FROM ($req) test";
            $orderByOxaMobility = str_replace('LIBELLE', 'libelleCdc', $orderByOxaMobility);

            $req .= " ORDER BY $orderByOxaMobility";
        } else {
            $req .= " ORDER BY CODECOMP.CODE_CDC ASC";
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $param, $arrayReq, $this->pdo);

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

    /**
     * Sca
     */
    // phpcs:ignore
    public function Sca($param, $orderByOxaMobility = null)
    {

        $post = $param; // $param will be modified, so we copy an original into $post for the Paginator
        $req =
            "SELECT DISTINCT SSCPTANA.CODE_SCA, CONCAT(SSCPTANA.CODE_SCA, SSCPTANA.CODE_STE) ID_SCA, SSCPTANA.LIBELLE libelleSca, SSCPTANA.CODE_STE, SSCPTANA.CODE_CTA, 
        SSCPTANA.BUDGET, SSCPTANA.DATE_MINI, SSCPTANA.DATE_MAXI,
        CENTRANA.LIBELLE libelleCentreAnalyse, SOCIETE.RAIS_SOC, SOCIETE.DEVISE
        FROM SSCPTANA WITH (nolock)
        LEFT JOIN CENTRANA WITH (nolock) ON CENTRANA.CODE_CTA = SSCPTANA.CODE_CTA  AND CENTRANA.CODE_STE = SSCPTANA.CODE_STE
        LEFT JOIN SOCIETE WITH (nolock) ON SOCIETE.CODE_STE = SSCPTANA.CODE_STE
        WHERE 1=1";

        $reqWhere = [];
        $arrayReq = [];

        if ($_SESSION['Session_Login']) {
            if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
                if (!isset($param['CODE_STE']) || $param['CODE_STE'] == '') {
                    if (!$_SESSION['Session_UsrMultiSoc']) {
                        $arrayReq[':codeSte'] = $_SESSION['Session_SocUsrProfil'];
                        $reqWhere[] =
                            " (
                                SSCPTANA.CODE_STE = :codeSte OR SSCPTANA.CODE_STE in (
                                    SELECT socUsr.COD_STE 
                                    FROM SOC_USR socUsr WITH (nolock) 
                                    WHERE socUsr.COD_USR = '" . $_SESSION['Session_Login'] . "'
                                )
                            )";
                    }
                }
                if (isset($param['CODE_STE']) && $param['CODE_STE'] != '') {
                    $reqWhere[] = " SSCPTANA.CODE_STE = :codeSte";
                    $arrayReq[':codeSte'] = $param['CODE_STE'];
                }
            } else {
                $reqWhere[] = " SSCPTANA.CODE_STE = ''";
            }
        } else {
            $_SESSION['DATE_FORMAT'] == 'aaaa-mm-jj';
            $reqWhere[] = " SSCPTANA.CODE_STE = :codeSte";
            $arrayReq[':codeSte'] = $param['CODE_STE'];
        }

        /* FILTRES */

        if ((
                isset($param['valide_obligatoire'])
                && $param['valide_obligatoire'] == 1
            ) || (
                isset($param['sca_valide'])
                && ($param['sca_valide'] == 'on' || $param['sca_valide'] == '1')
            )
        ) {
            $datejour = date('Ymd');
            $reqWhere[] = " (SSCPTANA.DATE_MINI <= $datejour AND (SSCPTANA.DATE_MAXI >= $datejour OR SSCPTANA.DATE_MAXI = '00000000'))";
            unset($param['valide_obligatoire']);
        }

        if (!empty($param['filter_date_DATE_MINI'])) {
            \Helpers\Outil\Date::dateToSql($param['filter_date_DATE_MINI']);
            $reqWhere[] = ' SSCPTANA.DATE_MINI = :dateDeb ';
            $arrayReq[':dateDeb'] = $param['filter_date_DATE_MINI'];
            unset($param['filter_date_DATE_MINI']);
        }

        if (!empty($param['filter_date_DATE_MAXI'])) {
            \Helpers\Outil\Date::dateToSql($param['filter_date_DATE_MAXI']);
            $reqWhere[] = ' SSCPTANA.DATE_MAXI = :dateFin ';
            $arrayReq[':dateFin'] = $param['filter_date_DATE_MAXI'];
            unset($param['filter_date_DATE_MAXI']);
        }

        if (!empty($param['filter_CODE_SCA'])) {
            $reqWhere[] = ' upper(SSCPTANA.CODE_SCA) like :code';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['filter_CODE_SCA'] . '%');
            unset($param['filter_CODE_SCA']);
        }

        if (!empty($param['filter_LIBELLE'])) {
            $reqWhere[] = ' upper(SSCPTANA.LIBELLE) like :libelle';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['filter_LIBELLE'] . '%');
            unset($param['filter_LIBELLE']);
        }

        if (!empty($param['filter_CODE_STE'])) {
            $reqWhere[] = ' (
                upper(SSCPTANA.CODE_STE) LIKE :search_soc_code 
                OR upper(SOCIETE.RAIS_SOC) LIKE :search_soc_label
            ) ';
            $arrayReq[':search_soc_code'] = mb_strtoupper('%' . $param['filter_CODE_STE'] . '%');
            $arrayReq[':search_soc_label'] = mb_strtoupper('%' . $param['filter_CODE_STE'] . '%');
            unset($param['filter_CODE_STE']);
        }

        if (!empty($param['filter_egal_CODE_STE'])) {
            $reqWhere[] = ' upper(SSCPTANA.CODE_STE) = :soc_code ';
            $arrayReq[':soc_code'] = mb_strtoupper($param['filter_egal_CODE_STE']);
            unset($param['filter_egal_CODE_STE']);
        }

        if (!empty($param['filter_CODE_CTA'])) {
            $codeDir = $param['filter_CODE_CTA'];
            $reqWhere[] = ' (
                upper(SSCPTANA.CODE_CTA) LIKE :search_cta_code 
                OR upper(CENTRANA.LIBELLE) LIKE :search_cta_label
            ) ';
            $arrayReq[':search_cta_code'] = mb_strtoupper('%' . $codeDir . '%');
            $arrayReq[':search_cta_label'] = mb_strtoupper('%' . $codeDir . '%');
            unset($param['filter_CODE_CTA']);
        }

        if (!empty($param['filter_egal_CODE_CTA'])) {
            $cta = $param['filter_egal_CODE_CTA'];
            $reqWhere[] = ' upper(SSCPTANA.CODE_CTA) = :cta_code';
            $arrayReq[':cta_code'] = mb_strtoupper($cta);
            unset($param['filter_egal_CODE_CTA']);
        }

        if (!empty($param['filter_DEVISE'])) {
            $reqWhere[] = ' SOCIETE.DEVISE like :devise ';
            $arrayReq[':devise'] = strtoupper('%' . $param['filter_DEVISE'] . '%');
            unset($param['filter_DEVISE']);
        }

        if (!empty($param['filter_BUDGET'])) {
            $reqWhere[] = ' SSCPTANA.BUDGET like :budget ';
            $arrayReq[':budget'] = '%' . $param['filter_BUDGET'] . '%';
            unset($param['filter_BUDGET']);
        }

        //filtre API OXA.MOBILITY
        if (!empty($param['RechCodeOuLib'])) {
            $reqWhere[] = ' (
                upper(SSCPTANA.CODE_SCA) like :code2 
                OR upper(SSCPTANA.LIBELLE) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2
            )';
            $arrayReq[':code2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
            unset($param['RechCodeOuLib']);
        }

        //filtre API OXA.MOBILITY
        if (!empty($param['ClauseWhereAdd'])) {
            $reqWhere[] = $param['ClauseWhereAdd'];
            unset($param['ClauseWhereAdd']);
        }
        /* FIN FILTRES */
        if (!empty($reqWhere)) {
            $req .= ' AND ' . implode(' AND ', $reqWhere);
        }


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

        if (count($whereArray) > 0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        } elseif ($orderByOxaMobility) {
            $req = "SELECT * FROM ($req) test";
            $orderByOxaMobility = str_replace('LIBELLE', 'libelleSca', $orderByOxaMobility);

            $req .= " ORDER BY $orderByOxaMobility";
        } else {
            $req .= " ORDER BY SSCPTANA.CODE_SCA ASC";
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * codeGestion
     * @return array<mixed>
     */
    public function codeGestion($param, $orderByOxaMobility = null): ?array
    {
        $req = "SELECT * FROM CODGEST WITH (nolock) WHERE CODE_SI = :codeSI";
        $arrayReq[':codeSI'] = $param['SI'];

        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            $req .= " AND CODE_STE = :societe";
            $arrayReq[':societe'] = $param['CODE_STE'];
        } else {
            $req .= " AND CODE_STE = ''";
        }

        /* FILTRES */
        $datejour = date('Ymd');
        if ((isset($param['valide_obligatoire']) && $param['valide_obligatoire'] == 1) || (isset($param['sca_valide']) && ($param['cg_valide'] == 'on' || $param['cg_valide'] == '1'))) {
            $req .= " (CODGEST.DTEVALMIN <= $datejour AND (CODGEST.DTEVALMAX >= $datejour OR CODGEST.DTEVALMAX = '00000000'))";
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechCode']) && $param['RechCode'] != '' && $param['RechCode'] != null) {
            $req .= ' AND upper(CODE_AFF) like :code';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['RechCode'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechLib']) && $param['RechLib'] != '' && $param['RechLib'] != null) {
            $req .= ' AND upper(LIB_GES) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['RechLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechCodeOuLib']) && $param['RechCodeOuLib'] != '' && $param['RechCodeOuLib'] != null) {
            $req .= ' AND (upper(CODE_AFF) like :code2 OR upper(LIB_GES) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2)';
            $arrayReq[':code2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['ClauseWhereAdd']) && $param['ClauseWhereAdd'] != '' && $param['ClauseWhereAdd'] != null) {
            $req .= ' AND ' . $param['ClauseWhereAdd'];
        }

        if ($orderByOxaMobility) {
            $req .= " ORDER BY $orderByOxaMobility";
        }

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



    /**
     * UO
     * @param array<string> $param
     * @return array<mixed>
     */
    public function UO($param, $orderByOxaMobility = null): ?array
    {
        $req = "SELECT * FROM UO WITH(NOLOCK) where 1=1 ";

        $arrayReq = [];
        $droit = \Helpers\Outil\Roles::droit($this->pdo, 'UOSECTO', 'PARAMSTE');
        if ($droit) {
            $req .= "AND CODE_STE = :societe";
            $arrayReq[':societe'] = $param['CODE_STE'];
        } else {
            $req .= "AND CODE_STE = ''";
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechCode']) && $param['RechCode'] != '' && $param['RechCode'] != null) {
            $req .= 'AND UPPER(CODE_UO) LIKE :code';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['RechCode'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechLib']) && $param['RechLib'] != '' && $param['RechLib'] != null) {
            $req .= 'AND upper(LIB_UO) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['RechLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['RechCodeOuLib']) && $param['RechCodeOuLib'] != '' && $param['RechCodeOuLib'] != null) {
            $req .= 'AND (upper(CODE_UO) like :code2 OR upper(LIB_UO) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2)';
            $arrayReq[':code2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $param['RechCodeOuLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($param['ClauseWhereAdd']) && $param['ClauseWhereAdd'] != '' && $param['ClauseWhereAdd'] != null) {
            $req .= 'AND ' . $param['ClauseWhereAdd'];
        }

        if ($orderByOxaMobility) {
            $req .= " ORDER BY $orderByOxaMobility";
        }

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


    // phpcs:ignore
    public function Directio($param)
    {
        $req = "SELECT CODE_STE, CODE_DIR, LIBELLE FROM DIRECTIO WITH (nolock)";

        $reqWhere = '';
        $arrayReq = [];

        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            if (!isset($param['CODE_STE']) || $param['CODE_STE'] == '') {
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $arrayReq[':codeSte'] = $_SESSION['Session_SocUsrProfil'];
                    $reqWhere =
                        " WHERE (DIRECTIO.CODE_STE = :codeSte OR DIRECTIO.CODE_STE in (
                        SELECT socUsr.COD_STE FROM SOC_USR socUsr WITH (nolock) WHERE socUsr.COD_USR = '" . $_SESSION['Session_Login'] . "'))";
                }
            }
            if (isset($param['CODE_STE']) && $param['CODE_STE'] != '') {
                $reqWhere = " WHERE DIRECTIO.CODE_STE = :codeSte ";
                $arrayReq[':codeSte'] = $param['CODE_STE'];
            }
        } else {
            $reqWhere = " WHERE DIRECTIO.CODE_STE = '' ";
        }


        /* FILTRES */
        if (isset($param['filter_CODE_DIR']) &&  $param['filter_CODE_DIR'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(DIRECTIO.CODE_DIR) like :code ';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['filter_CODE_DIR'] . '%');
        }

        if (isset($param['filter_LIBELLE']) &&  $param['filter_LIBELLE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(DIRECTIO.LIBELLE) like :libelle ';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['filter_LIBELLE'] . '%');
        }

        if (isset($param['filter_CODE_STE']) &&  $param['filter_CODE_STE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(DIRECTIO.CODE_STE) like :codeSte  ';
            $arrayReq[':codeSte'] = mb_strtoupper('%' . $param['filter_CODE_STE'] . '%');
        }
        /* FIN FILTRES */

        $req = $req . $reqWhere;
        $whereArray = FilterTable::splitFilter($param, 0, 0, $reqWhere ? 1 : 0);
        if ($whereArray) {
            $req .= $whereArray['tri'];
        } else {
            $req .= " ORDER BY CODE_DIR ASC";
        }

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

    // phpcs:ignore
    public function Natana($param)
    {
        $req = "SELECT CODE_STE, CODNAN, LIBELLE FROM NATANA WITH (nolock)";

        $reqWhere = '';
        $arrayReq = [];

        if (\Helpers\Outil\Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')) {
            if (!isset($param['CODE_STE']) || $param['CODE_STE'] == '') {
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $arrayReq[':codeSte'] = $_SESSION['Session_SocUsrProfil'];
                    $reqWhere =
                        " WHERE (NATANA.CODE_STE = :codeSte OR NATANA.CODE_STE in (
                        SELECT socUsr.COD_STE FROM SOC_USR socUsr WITH (nolock) WHERE socUsr.COD_USR = '" . $_SESSION['Session_Login'] . "'))";
                }
            }
            if (isset($param['CODE_STE']) && $param['CODE_STE'] != '') {
                $reqWhere = " WHERE NATANA.CODE_STE = :codeSte ";
                $arrayReq[':codeSte'] = $param['CODE_STE'];
            }
        } else {
            $reqWhere = " WHERE NATANA.CODE_STE = '' ";
        }

        /* FILTRES */
        if (isset($param['filter_CODNAN']) &&  $param['filter_CODNAN'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(NATANA.CODNAN) like :code ';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['filter_CODNAN'] . '%');
        }

        if (isset($param['filter_LIBELLE']) &&  $param['filter_LIBELLE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(NATANA.LIBELLE) like :libelle ';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['filter_LIBELLE'] . '%');
        }

        if (isset($param['filter_CODE_STE']) &&  $param['filter_CODE_STE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(NATANA.CODE_STE) like :codeSte  ';
            $arrayReq[':codeSte'] = mb_strtoupper('%' . $param['filter_CODE_STE'] . '%');
        }
        /* FIN FILTRES */

        $req = $req . $reqWhere;
        $whereArray = FilterTable::splitFilter($param, 0, 0, $reqWhere ? 1 : 0);
        if ($whereArray) {
            $req .= $whereArray['tri'];
        } else {
            $req .= " ORDER BY CODNAN ASC";
        }

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

    // phpcs:ignore
    public function Centrana($param)
    {
        $req = "SELECT CODE_STE, CODE_CTA, LIBELLE FROM CENTRANA WITH (nolock)";

        $reqWhere = '';
        $arrayReq = [];

        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            if (!isset($param['CODE_STE']) || $param['CODE_STE'] == '') {
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $arrayReq[':codeSte'] = $_SESSION['Session_SocUsrProfil'];
                    $reqWhere =
                        " WHERE (CENTRANA.CODE_STE = :codeSte OR CENTRANA.CODE_STE in (
                        SELECT socUsr.COD_STE FROM SOC_USR socUsr WITH (nolock) WHERE socUsr.COD_USR = '" . $_SESSION['Session_Login'] . "'))";
                }
            }
            if (isset($param['CODE_STE']) && $param['CODE_STE'] != '') {
                $reqWhere = " WHERE CENTRANA.CODE_STE = :codeSte ";
                $arrayReq[':codeSte'] = $param['CODE_STE'];
            }
        } else {
            $reqWhere = " WHERE CENTRANA.CODE_STE = '' ";
        }


        /* FILTRES */
        if (isset($param['filter_CODE_CTA']) &&  $param['filter_CODE_CTA'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(CENTRANA.CODE_CTA) like :code ';
            $arrayReq[':code'] = mb_strtoupper('%' . $param['filter_CODE_CTA'] . '%');
        }

        if (isset($param['filter_LIBELLE']) &&  $param['filter_LIBELLE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(CENTRANA.LIBELLE) like :libelle ';
            $arrayReq[':libelle'] = mb_strtoupper('%' . $param['filter_LIBELLE'] . '%');
        }

        if (isset($param['filter_CODE_STE']) &&  $param['filter_CODE_STE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND ";
            }
            $reqWhere .= ' upper(CENTRANA.CODE_STE) like :codeSte  ';
            $arrayReq[':codeSte'] = mb_strtoupper('%' . $param['filter_CODE_STE'] . '%');
        }
        /* FIN FILTRES */

        $req = $req . $reqWhere;
        $whereArray = FilterTable::splitFilter($param, 0, 0, $reqWhere ? 1 : 0);
        if ($whereArray) {
            $req .= $whereArray['tri'];
        } else {
            $req .= " ORDER BY CODE_CTA ASC";
        }

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

    public function acheteur($post = [])
    {
        $today = Date('Ymd');

        $req =
            "SELECT CODE, NOM 
        FROM HASTUTIL WITH (nolock) 
        WHERE HASTUTIL.DTE_OUV <= '" . $today . "' 
        AND (HASTUTIL.DTE_FERM = '00000000' OR HASTUTIL.DTE_FERM >= '" . $today . "')
        AND HASTUTIL.ACHETEUR = " . Sql::TRUE;

        $arrayReq = [];
        if (($post['code_ste'] ?? null) != '') {
            $req .=
                " AND ( 
                HASTUTIL.UTIMULTI = " . Sql::TRUE . " 
                OR HASTUTIL.CODE_STE = :code_ste_0
                OR EXISTS (
                    SELECT * 
                    FROM SOC_USR WITH (nolock) 
                    WHERE SOC_USR.COD_USR = :current_user
                        AND SOC_USR.COD_STE = :code_ste_1)
                )";
            $arrayReq += [
                ':current_user' => $_SESSION['Session_Login'],
                ':code_ste_0' => $post['code_ste'],
                ':code_ste_1' => $post['code_ste'],
            ];
        }
        $whereArray = FilterTable::splitFilter($post, 0, 0, 1);
        if (!empty($whereArray)) {
            $req .= $whereArray[0] . $whereArray['tri'];

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

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

    public function utilisateurSociete($param)
    {
        $req = "SELECT CODE, NOM, CODE_STE, CTR_ADR, FONCTION, EMAIL, CODE_UO FROM HASTUTIL WITH (nolock)";

        $whereArray = [];
        $reqWhere = '';
        if (isset($param['code_ste']) && $param['code_ste'] != '') {
            $codeSte = addcslashes($param['code_ste'], '%_');
            $reqWhere =
                " WHERE (HASTUTIL.UTIMULTI = " . \Helpers\Outil\Sql::setBoolSql(1) . "
            OR (HASTUTIL.CODE_STE = '" . $codeSte . "'
            OR EXISTS (
                SELECT * 
                FROM SOC_USR WITH (nolock) 
                WHERE SOC_USR.COD_USR = '" . $_SESSION['Session_Login'] . "' AND SOC_USR.COD_STE = '" . $codeSte . "')))";
        }

        if ($param['email']) {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= " HASTUTIL.EMAIL !=''";
        }

        if (isset($param['filter_CODE']) &&  $param['filter_CODE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= ' upper(HASTUTIL.CODE) like \'%' . mb_strtoupper($param['filter_CODE']) . '%\'';
        }

        if (isset($param['filter_NOM']) &&  $param['filter_NOM'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= ' upper(HASTUTIL.NOM) like \'%' . mb_strtoupper($param['filter_NOM']) . '%\'';
        }

        if (isset($param['filter_CODE_STE']) &&  $param['filter_CODE_STE'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= ' upper(HASTUTIL.CODE_STE) like \'%' . mb_strtoupper($param['filter_CODE_STE']) . '%\'';
        }

        if (isset($param['filter_CTR_ADR']) &&  $param['filter_CTR_ADR'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= ' upper(HASTUTIL.CTR_ADR) like \'%' . mb_strtoupper($param['filter_CTR_ADR']) . '%\'';
        }

        if (isset($param['filter_FONCTION']) &&  $param['filter_FONCTION'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= ' upper(HASTUTIL.FONCTION) like \'%' . mb_strtoupper($param['filter_FONCTION']) . '%\'';
        }

        if (isset($param['filter_EMAIL']) &&  $param['filter_EMAIL'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= ' upper(HASTUTIL.EMAIL) like \'%' . mb_strtoupper($param['filter_EMAIL']) . '%\'';
        }

        if (isset($param['filter_CODE_UO']) &&  $param['filter_CODE_UO'] != '') {
            if ($reqWhere == '') {
                $reqWhere = " WHERE";
            } else {
                $reqWhere .= " AND";
            }
            $reqWhere .= ' upper(HASTUTIL.CODE_UO) = ' . mb_strtoupper($param['filter_CODE_UO']);
        }

        $req = $req . $reqWhere;

        $filterTable = FilterTable::splitFilter($param, 0, 0, $reqWhere ? 1 : 0);

        if (!($filterTable['tri'] ?? '')) {
            $filterTable = ['tri' => ' ORDER BY HASTUTIL.CODE asc', 0 => '', 1 => []];
        }

        $req .= $filterTable['tri'];

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $param, $whereArray, $this->pdo);

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

    // SOCIETE
    public function codeSteUtilisateur($post)
    {
        $codeUser = trim($_SESSION['Session_Login']);
        $arrayReq = [];

        $req = "SELECT soc.CODE_STE, soc.RAIS_SOC 
        FROM SOCIETE soc WITH (nolock)
        WHERE (
            CASE WHEN '" . $post['codeSte'] . "'=''
                THEN
                    CASE WHEN CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC WITH (nolock)  WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                        THEN 1	
                        ELSE 0
                        END
                WHEN '" . $post['codeSte'] . "'<>'' 
                THEN 
                    CASE WHEN CODE_STE = '" . $post['codeSte'] . "' OR CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                    THEN 1
                    ELSE 0
                    END
                ELSE 0
            END =1
        )";

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

        if (isset($post['filter_CODE_STE']) &&  $post['filter_CODE_STE'] != '') {
            $req .= ' AND upper(CODE_STE) like :filtreCodeSte';
            $arrayReq[':filtreCodeSte'] = '%' . mb_strtoupper($post['filter_CODE_STE']) . '%';
        }
        if (isset($post['filter_RAIS_SOC']) &&  $post['filter_RAIS_SOC'] != '') {
            $req .= ' AND upper(RAIS_SOC) like :filtreRaisSoc';
            $arrayReq[':filtreRaisSoc'] = '%' . mb_strtoupper($post['filter_RAIS_SOC']) . '%';
        }

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

    // centre emetteur
    public function centreEmetteurUtilisateur($post)
    {
        $codeUser = trim($_SESSION['Session_Login']);

        $arrayReq = [];

        $req = "SELECT CODE_STE, CODE_CTR, RAIS_SOC 
        FROM CENTRE WITH (nolock) 
        WHERE EMISSION = " . \Helpers\Outil\Sql::setBoolSql(1);

        if (isset($post['steSelected']) && $post['steSelected'] != '') {
            $req .= " AND
            (
                CODE_STE = :codeSte
            )";
            if (!$_SESSION['Session_UsrMultiSoc']) {
                $req .= " AND '" . $post['steSelected'] . "' IN (
                    SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                    UNION
                    SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                )";
            }
            $arrayReq[':codeSte'] = $post['steSelected'];
        } else {
            $req .= " AND
            (
                CASE WHEN '" . $post['codeSte'] . "'=''
                    THEN
                        CASE WHEN CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC WITH (nolock)  WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                            THEN 1	
                            ELSE 0
                            END	
                    WHEN '" . $post['codeSte'] . "'<>'' 
                    THEN 
                        CASE WHEN CODE_STE = '" . $post['codeSte'] . "' OR CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                        THEN 1
                        ELSE 0
                        END
                    ELSE 0
                END =1
            )";
            if (!$_SESSION['Session_UsrMultiSoc']) {
                $req .= " AND CODE_STE IN (
                    SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                    UNION
                    SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                )";
            }
        }

        if (isset($post['filter_CODE_STE']) &&  $post['filter_CODE_STE'] != '') {
            $req .= ' AND upper(CODE_STE) like :filtreCodeSte';
            $arrayReq[':filtreCodeSte'] = '%' . mb_strtoupper($post['filter_CODE_STE']) . '%';
        }
        if (isset($post['filter_CODE_CTR']) &&  $post['filter_CODE_CTR'] != '') {
            $req .= ' AND upper(CODE_CTR) like :filtreCodeCtr';
            $arrayReq[':filtreCodeCtr'] = '%' . mb_strtoupper($post['filter_CODE_CTR']) . '%';
        }
        if (isset($post['filter_RAIS_SOC']) &&  $post['filter_RAIS_SOC'] != '') {
            $req .= ' AND upper(RAIS_SOC) like :filtreRaisSoc';
            $arrayReq[':filtreRaisSoc'] = '%' . mb_strtoupper($post['filter_RAIS_SOC']) . '%';
        }

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

    // centre Livraison
    public function centreLivraisonUtilisateur($post)
    {
        $codeUser = trim($_SESSION['Session_Login']);
        $arrayReq = [];

        $req = "SELECT CODE_STE, CODE_CTR, RAIS_SOC 
        FROM CENTRE WITH (nolock) 
        WHERE LIVRAISON = " . \Helpers\Outil\Sql::setBoolSql(1);

        if (isset($post['steSelected']) && $post['steSelected'] != '') {
            $req .= " AND
            (
                CODE_STE = :codeSte
            )";
            if (!$_SESSION['Session_UsrMultiSoc']) {
                $req .= " AND '" . $post['steSelected'] . "' IN (
                    SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                    UNION
                    SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                )";
            }
            $arrayReq[':codeSte'] = $post['steSelected'];
        } else {
            $req .= " AND
            (
                CASE WHEN '" . $post['codeSte'] . "'=''
                    THEN
                        CASE WHEN CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                            THEN 1	
                            ELSE 0
                            END	
                    WHEN '" . $post['codeSte'] . "'<>'' 
                    THEN 
                        CASE WHEN CODE_STE = '" . $post['codeSte'] . "' OR CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                        THEN 1
                        ELSE 0
                        END
                    ELSE 0
                END =1
            )";
            if (!$_SESSION['Session_UsrMultiSoc']) {
                $req .= " AND CODE_STE IN (
                    SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                    UNION
                    SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                )";
            }
        }

        if (isset($post['filter_CODE_STE']) &&  $post['filter_CODE_STE'] != '') {
            $req .= ' AND upper(CODE_STE) like :filtreCodeSte';
            $arrayReq[':filtreCodeSte'] = '%' . mb_strtoupper($post['filter_CODE_STE']) . '%';
        }
        if (isset($post['filter_CODE_CTR']) &&  $post['filter_CODE_CTR'] != '') {
            $req .= ' AND upper(CODE_CTR) like :filtreCodeCtr';
            $arrayReq[':filtreCodeCtr'] = '%' . mb_strtoupper($post['filter_CODE_CTR']) . '%';
        }
        if (isset($post['filter_RAIS_SOC']) &&  $post['filter_RAIS_SOC'] != '') {
            $req .= ' AND upper(RAIS_SOC) like :filtreRaisSoc';
            $arrayReq[':filtreRaisSoc'] = '%' . mb_strtoupper($post['filter_RAIS_SOC']) . '%';
        }

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

    // section Imputation
    public function sectionImputationUtilisateur($post)
    {
        $codeUser = trim($_SESSION['Session_Login']);
        $arrayReq = [];

        $req = "SELECT * FROM SECTIMPU WITH (nolock)";

        if (\Helpers\Outil\Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE')) {
            if (isset($post['steSelected']) && $post['steSelected'] != '') {
                $req .= " WHERE
                (
                    CODE_STE = :codeSte
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND '" . $post['steSelected'] . "' IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
                $arrayReq[':codeSte'] = $post['steSelected'];
            } else {
                $req .= " WHERE
                (
                    CASE WHEN '" . $post['codeSte'] . "'=''
                        THEN
                            CASE WHEN CODE_STE in (SELECT CODESOC FROM MARCHSOC with(nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                                THEN 1	
                                ELSE 0
                                END	
                        WHEN '" . $post['codeSte'] . "'<>'' 
                        THEN 
                            CASE WHEN CODE_STE = '" . $post['codeSte'] . "' OR CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                            THEN 1
                            ELSE 0
                            END
                        ELSE 0
                    END =1
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND CODE_STE IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
            }
        } else {
            $req .= " WHERE CODE_STE = '' ";
        }

        if (isset($post['filter_CODE_STE']) &&  $post['filter_CODE_STE'] != '') {
            $req .= ' AND upper(CODE_STE) like :filtreCodeSte';
            $arrayReq[':filtreCodeSte'] = '%' . mb_strtoupper($post['filter_CODE_STE']) . '%';
        }
        if (isset($post['filter_CODE_SI']) &&  $post['filter_CODE_SI'] != '') {
            $req .= ' AND upper(CODE_SI) like :filtreCodeSI';
            $arrayReq[':filtreCodeSI'] = '%' . mb_strtoupper($post['filter_CODE_SI']) . '%';
        }
        if (isset($post['filter_LIBELLE']) &&  $post['filter_LIBELLE'] != '') {
            $req .= ' AND upper(LIBELLE) like :filtreLibelle';
            $arrayReq[':filtreLibelle'] = '%' . mb_strtoupper($post['filter_LIBELLE']) . '%';
        }

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

    // Code Completementaire
    public function codeCompletementaireUtilisateur($post)
    {
        $codeUser = trim($_SESSION['Session_Login']);
        $arrayReq = [];

        $req = "SELECT * FROM CODECOMP WITH (nolock)";

        if (\Helpers\Outil\Roles::droit($this->pdo, 'CDCSECTO', 'PARAMSTE')) {
            if (isset($post['steSelected']) && $post['steSelected'] != '') {
                $req .= " WHERE
                (
                    CODE_STE = :codeSte
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND '" . $post['steSelected'] . "' IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
                $arrayReq[':codeSte'] = $post['steSelected'];
            } else {
                $req .= " WHERE
                (
                    CASE WHEN '" . $post['codeSte'] . "'=''
                        THEN
                            CASE WHEN CODE_STE in (SELECT CODESOC FROM MARCHSOC WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC WITH (nolock)  WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                                THEN 1	
                                ELSE 0
                                END	
                        WHEN '" . $post['codeSte'] . "'<>'' 
                        THEN 
                            CASE WHEN CODE_STE = '" . $post['codeSte'] . "' OR CODE_STE in (SELECT CODESOC FROM MARCHSOC WITH (nolock)  WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                            THEN 1
                            ELSE 0
                            END
                        ELSE 0
                    END =1
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND CODE_STE IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
            }
        } else {
            $req .= " WHERE CODE_STE = '' ";
        }

        if (isset($post['filter_CODE_STE']) &&  $post['filter_CODE_STE'] != '') {
            $req .= ' AND upper(CODE_STE) like :filtreCodeSte';
            $arrayReq[':filtreCodeSte'] = '%' . mb_strtoupper($post['filter_CODE_STE']) . '%';
        }
        if (isset($post['filter_CODE_CDC']) &&  $post['filter_CODE_CDC'] != '') {
            $req .= ' AND upper(CODE_CDC) like :filtreCodeCdc';
            $arrayReq[':filtreCodeCdc'] = '%' . mb_strtoupper($post['filter_CODE_CDC']) . '%';
        }
        if (isset($post['filter_LIBELLE']) &&  $post['filter_LIBELLE'] != '') {
            $req .= ' AND upper(LIBELLE) like :filtreLibelle';
            $arrayReq[':filtreLibelle'] = '%' . mb_strtoupper($post['filter_LIBELLE']) . '%';
        }

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

    // Sous Compte d'analyse
    public function sousCompteAnalyseUtilisateur($post)
    {
        $codeUser = trim($_SESSION['Session_Login']);
        $arrayReq = [];

        $req = "SELECT * FROM SSCPTANA WITH (nolock)";

        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            if (isset($post['steSelected']) && $post['steSelected'] != '') {
                $req .= " WHERE
                (
                    CODE_STE = :codeSte
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND '" . $post['steSelected'] . "' IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
                $arrayReq[':codeSte'] = $post['steSelected'];
            } else {
                $req .= " WHERE
                (
                    CASE WHEN '" . $post['codeSte'] . "'=''
                        THEN
                            CASE WHEN CODE_STE in (SELECT CODESOC FROM MARCHSOC WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                                THEN 1	
                                ELSE 0
                                END	
                        WHEN '" . $post['codeSte'] . "'<>'' 
                        THEN 
                            CASE WHEN CODE_STE = '" . $post['codeSte'] . "' OR CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                            THEN 1
                            ELSE 0
                            END
                        ELSE 0
                    END =1
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND CODE_STE IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
            }
        } else {
            $req .= " WHERE CODE_STE = ''";
        }

        if (isset($post['filter_CODE_STE']) &&  $post['filter_CODE_STE'] != '') {
            $req .= ' AND upper(CODE_STE) like :filtreCodeSte';
            $arrayReq[':filtreCodeSte'] = '%' . mb_strtoupper($post['filter_CODE_STE']) . '%';
        }
        if (isset($post['filter_CODE_SCA']) &&  $post['filter_CODE_SCA'] != '') {
            $req .= ' AND upper(CODE_SCA) like :filtreCodeSCA';
            $arrayReq[':filtreCodeSCA'] = '%' . mb_strtoupper($post['filter_CODE_SCA']) . '%';
        }
        if (isset($post['filter_LIBELLE']) &&  $post['filter_LIBELLE'] != '') {
            $req .= ' AND upper(LIBELLE) like :filtreLibelle';
            $arrayReq[':filtreLibelle'] = '%' . mb_strtoupper($post['filter_LIBELLE']) . '%';
        }

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

    // Centre d'analyse
    public function centreAnalyseUtilisateur($post)
    {
        $codeUser = trim($_SESSION['Session_Login']);
        $arrayReq = [];

        $req = "SELECT CODE_STE, CODE_CTA, LIBELLE FROM CENTRANA WITH (nolock)";

        if (\Helpers\Outil\Roles::droit($this->pdo, 'SCASECTO', 'PARAMSTE')) {
            if (isset($post['steSelected']) && $post['steSelected'] != '') {
                $req .= " WHERE
                (
                    CODE_STE = :codeSte
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND '" . $post['steSelected'] . "' IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
                $arrayReq[':codeSte'] = $post['steSelected'];
            } else {
                $req .= " WHERE
                (
                    CASE WHEN '" . $post['codeSte'] . "'=''
                        THEN
                            CASE WHEN CODE_STE in (SELECT CODESOC FROM MARCHSOC WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "') OR not exists (SELECT CODESOC FROM MARCHSOC WITH (nolock)  WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                                THEN 1	
                                ELSE 0
                                END	
                        WHEN '" . $post['codeSte'] . "'<>'' 
                        THEN 
                            CASE WHEN CODE_STE = '" . $post['codeSte'] . "' OR CODE_STE in (SELECT CODESOC FROM MARCHSOC  WITH (nolock) WHERE CODEMARCH = '" . $post['codeMarche'] . "')
                            THEN 1
                            ELSE 0
                            END
                        ELSE 0
                    END =1
                )";
                if (!$_SESSION['Session_UsrMultiSoc']) {
                    $req .= " AND CODE_STE IN (
                        SELECT CODE_STE FROM HASTUTIL WITH (nolock) WHERE CODE= '" . $codeUser . "'
                        UNION
                        SELECT COD_STE FROM SOC_USR WITH (nolock) WHERE COD_USR= '" . $codeUser . "'
                    )";
                }
            }
        } else {
            $req .= " WHERE CODE_STE = ''";
        }

        if (isset($post['filter_CODE_STE']) &&  $post['filter_CODE_STE'] != '') {
            $req .= ' AND upper(CODE_STE) like :filtreCodeSte';
            $arrayReq[':filtreCodeSte'] = '%' . mb_strtoupper($post['filter_CODE_STE']) . '%';
        }
        if (isset($post['filter_CODE_CTA']) &&  $post['filter_CODE_CTA'] != '') {
            $req .= ' AND upper(CODE_CTA) like :filtreCodeCTA';
            $arrayReq[':filtreCodeCTA'] = '%' . mb_strtoupper($post['filter_CODE_CTA']) . '%';
        }
        if (isset($post['filter_LIBELLE']) &&  $post['filter_LIBELLE'] != '') {
            $req .= ' AND upper(LIBELLE) like :filtreLibelle';
            $arrayReq[':filtreLibelle'] = '%' . mb_strtoupper($post['filter_LIBELLE']) . '%';
        }

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

    // Multi Societe
    public function multiSocieteUtilisateur($post)
    {
        $req = "SELECT CODE, NOM FROM HASTUTIL WITH (nolock) WHERE UTIMULTI = 0x54";

        $arrayReq = [];

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

    /**
     * utilisateursOxalink
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function utilisateursOxalink($post)
    {
        unset($post['filter_CODE']);
        unset($post['filter_CTR_ADRdebut-----finRAIS_SOC']);
        unset($post['tri_CODE']);
        unset($post['tri_CTR_ADR']);

        $req = "select NOM CODE,* from OXALINK_USER with (nolock) WHERE STATUT in ('C', 'A', 'V')";

        $whereArray = FilterTable::splitFilter($post, 0, 0, 1);

        $arrayReq = [];
        if (count($whereArray) >  0 &&  isset($whereArray[1])) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    public function utilisateurListe($post, bool $multiSelect = false)
    {
        $today = Date('Ymd');
        $req = "SELECT CODE, NOM, CTR_ADR,EMAIL, CENTRE.RAIS_SOC, HASTUTIL.COD_LNG, HASTUTIL.CODE_STE, SOCIETE.RAIS_SOC AS RS
                FROM HASTUTIL WITH (nolock) 
                    LEFT JOIN CENTRE WITH (NOLOCK) on CENTRE.CODE_CTR = HASTUTIL.CTR_ADR
                    LEFT JOIN SOCIETE WITH (NOLOCK) ON SOCIETE.CODE_STE = HASTUTIL.CODE_STE
                WHERE 1=1";

        $arrayReq = [];

        unset($post['filter_EMAIL']);
        unset($post['tri_EMAIL']);

        if (isset($post['filter_egal_CODE_STE']) && $post['filter_egal_CODE_STE'] != '') {
            $req .= ' and (HASTUTIL.CODE_STE = :codeSTE OR HASTUTIL.UTIMULTI = 0x54)';
            $arrayReq['codeSTE'] = $post['filter_egal_CODE_STE'];
            unset($post['filter_egal_CODE_STE']);
        }

        if (isset($post['filter_CTR_ADRdebut-----finRAIS_SOC'])) {
            $post['filter_CTR_ADRdebut-----finSOCIETE.RAIS_SOC'] = $post['filter_CTR_ADRdebut-----finRAIS_SOC']??'';
            unset($post['filter_CTR_ADRdebut-----finRAIS_SOC']);
        }
        if (isset($post['filter_CODE_STEdebut-----finRS'])) {
            $post['filter_SOCIETE.CODE_STEdebut-----finSOCIETE.RAIS_SOC'] = $post['filter_CODE_STEdebut-----finRS']??'';
            unset($post['filter_CODE_STEdebut-----finRS']);
        }
        

        if (isset($post['ACHETEUR']) && $post['ACHETEUR']) {
            $req .= ' AND HASTUTIL.ACHETEUR = ' . SQL::setBoolSql(1);
        }

        if (isset($post['MAGASIN']) && $post['MAGASIN']) {
            $req .= ' AND HASTUTIL.MAGASIN = ' . SQL::setBoolSql(1);
        }

        if (isset($post['NOTEUR']) && $post['NOTEUR']) {
            $req .= ' AND HASTUTIL.NOTEUR = ' . SQL::setBoolSql(1);
        }

        if (isset($post['DEMAND']) && $post['DEMAND']) {
            $req .= ' AND DEMAND = ' . SQL::setBoolSql(1);
        }

        if (isset($post['SIG_BAP']) && $post['SIG_BAP']) {
            $req .= ' AND SIG_BAP = ' . SQL::setBoolSql(1);
        }

        if (isset($post['SIG_CDE']) && $post['SIG_CDE']) {
            $req .= ' AND SIG_CDE = ' . SQL::setBoolSql(1);
        }

        if (isset($post['SIG_DA']) && $post['SIG_DA']) {
            $req .= ' AND SIG_DA = ' . SQL::setBoolSql(1);
        }

        if (isset($post['SIG_REF']) && $post['SIG_REF']) {
            $req .= ' AND SIG_REF = ' . SQL::setBoolSql(1);
        }

        if (isset($post['ACCOXAPR']) && $post['ACCOXAPR']) {
            $req .= ' AND ACCOXAPR = ' . SQL::setBoolSql(1);
        }

        if (isset($post['NOTME']) && $post['NOTME']) {
            $req .= ' AND CODE <>  :codeUserLog ';
            $arrayReq[':codeUserLog'] = $_SESSION["Session_Login"];
        }

        // TODO : factorize with filter_egal_CODE_STE
        if (isset($post['CODE_STE']) && $post['CODE_STE'] != '') {
            $req .= " AND (HASTUTIL.UTIMULTI = 'T' OR HASTUTIL.CODE_STE = :societe)";
            $arrayReq[':societe'] = $post['CODE_STE'];

            // /*OR EXISTS (
            //     SELECT *
            //     FROM SOC_USR WITH (nolock)
            //     WHERE SOC_USR.COD_USR = '" . $_SESSION['Session_Login'] . "' AND SOC_USR.COD_STE = '" . $post['CODE_STE'] . "')*/
            // )";
        }

        if (isset($post['VALIDE']) && $post['VALIDE']) {
            $req .=
                " AND HASTUTIL.DTE_OUV <= $today AND (HASTUTIL.DTE_FERM = '00000000' OR HASTUTIL.DTE_FERM >= $today)";
        }

        if (isset($post['withEmail']) && $post['withEmail']) {
            $req .= " AND HASTUTIL.EMAIL <> ''";
        }

        if ($multiSelect) {
            $notIn = $post['notin'] ?? '';

            $i = 1;
            if ($notIn != '') {
                $notIn = explode('|', substr($notIn, 1));
                $req .= "HASTUTIL.CODE not in (";
                $passageNotInt = 0;
                foreach ($notIn as $not) {
                    $i++;
                    if ($passageNotInt == 0) {
                        $passageNotInt++;
                        $req .= " :where" . $i;
                    } else {
                        $req .= ", :where" . $i;
                    }
                    $arrayReq[':where' . $i] = $not;
                }

                $req .= ") ";
            }

            if ($post['value'] ?? false) {
                $req .= " and (";

                $req .= " (upper(HASTUTIL.CODE) like upper(:val1) OR upper(HASTUTIL.NOM) like upper(:val2)) ";

                $arrayReq[':val1'] = '%' . $post['value'] . '%';
                $arrayReq[':val2'] = '%' . $post['value'] . '%';

                $req .= ")";
            }
        }

        if (!isset($post['tri_CODE'])) {
            $post['tri_CODE'] = 'asc';
        }

        $whereArray = FilterTable::splitFilter($post, 0, 0, 1);

        if (count($whereArray) >  0 &&  isset($whereArray[1])) {
            $req .= str_ireplace('where', 'and', $whereArray[0]) . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * Equivalent v1:
     * @see \SqlSign::rqPopupGetAllSignDelegataires
     * @param array<mixed> $post
     * @return array<string>[]
     */
    public function listDeleg(string $societe, $post) :array
    {
        $userId = $_SESSION['Session_Login'];

        $montantMax = null;
        if ((new Paravrac($this->pdo))->getFlagForSig('DELEG_AVEC_CTRL_MNT')) {
            $montantMax = $this->getMontantMaxDelegation($userId);
        }


        $param = ['delegator' => $userId];
        $req = "SELECT DISTINCT HASTUTIL.*, s.RAIS_SOC
            FROM HASTUTIL with(nolock)
                LEFT JOIN UO with(nolock) ON HASTUTIL.CODE = UO.RESP_HIER
                LEFT JOIN SOCIETE s WITH(NOLOCK) ON HASTUTIL.CODE_STE = s.CODE_STE 
                JOIN HASTUTIL as Delegator with(nolock) 
                    ON Delegator.CODE = :delegator
            WHERE HASTUTIL.CODE != Delegator.CODE
                AND HASTUTIL.DTE_OUV <= '".date('Ymd')."' and (HASTUTIL.DTE_FERM = '00000000' or HASTUTIL.DTE_FERM >= '".date('Ymd')."')
                AND (
                    HASTUTIL.SIG_DA = 'T'
                    OR HASTUTIL.SIG_CDE = 'T'
                    OR HASTUTIL.SIG_BAP = 'T'
                    OR HASTUTIL.SIG_REF = 'T'
                ) 
                -- si utimulti
                AND (
                        (HASTUTIL.UTIMULTI = 0x54 AND Delegator.UTIMULTI = 0x54)
                            OR (Delegator.UTIMULTI <> 0x54 AND (HASTUTIL.CODE_STE = Delegator.CODE_STE OR HASTUTIL.UTIMULTI = 0x54))
                    ) "
            . ($montantMax !== null ?
                ' AND (HASTUTIL.MNT_ENGINV >= ' . $montantMax . ' 
                    or HASTUTIL.MNT_ENGINV = 999999999 
                    or HASTUTIL.MNT_ENG >= ' . $montantMax . ' 
                    or HASTUTIL.MNT_ENG = 999999999 ' . '    
                    or UO.MNT_ENG >= ' . $montantMax . ' 
                    or UO.MNT_ENG = 999999999 
                    or UO.MNT_ENGINV >= ' . $montantMax . ' 
                    or UO.MNT_ENGINV = 999999999 
                    or UO.MNT_ENG_DAC >= ' . $montantMax . ' 
                    or UO.MNT_ENG_DAC = 999999999 
                    or UO.MNT_ENGINV_DAC >= ' . $montantMax . ' 
                    or UO.MNT_ENGINV_DAC = 999999999) ' : ''
            );
            
        $post['tri_NOM'] = $post['tri_NOM'] ?? 'asc';

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

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $param, $this->pdo);

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

    /**
     * Equivalent v1:
     * @see \Sign::GetMontantMaxDelegation
     */
    private function getMontantMaxDelegation(string $userId) :int
    {
        // Montant maxi pour l'utilisateur ou Montant maxi en tant que responsable d'une UO
        return intval($this->fetchAssocOne(
            "SELECT GREATEST(
                HASTUTIL.MNT_ENG,
                HASTUTIL.MNT_ENGINV,
                UO.MNT_ENG, 
                UO.MNT_ENGINV, 
                UO.MNT_ENG_DAC, 
                UO.MNT_ENGINV_DAC
            ) as Montant
            FROM HASTUTIL with (nolock)
            LEFT JOIN UO with (nolock) ON RESP_HIER = HASTUTIL.CODE
            WHERE CODE = :userId",
            ['userId' => $userId]
        )['Montant'] ?? 0);
    }

    

    // phpcs:ignore
    public function AccordCadre($params = '')
    {
        $req = "SELECT CODE_ACD, LIBELLE FROM ACCCADRE acc WITH (nolock) WHERE 1=1";
        $arrayReq = [];

        $reqWhere = '';
        $today = Date('Ymd');

        // Fournisseur
        if (isset($params['CODE_FOUR']) && $params['CODE_FOUR'] != '') {
            $codeFour = $params['CODE_FOUR'];
            $reqWhere .=
                " AND 
            (
                (
                    acc.TYPE_FG = 'F' 
                    AND acc.CODE_FOUR = '$codeFour'
                )
                OR
                (
                    acc.TYPE_FG = 'G' 
                    AND acc.CODE_FOUR=(SELECT four.FOURPRIN FROM FOURNISS four WITH (nolock) WHERE four.CODE = '$codeFour') 
                    AND exists (SELECT null FROM FS_ACD fs WITH (nolock) WHERE fs.CODE_ACD=acc.CODE_ACD AND fs.CODE_FOU = '$codeFour')
                )
            )";
        }

        // existance de fournisseur(s) du groupement dans FS_ACD
        if (isset($params['FS_ACD_EXISTE']) && $params['FS_ACD_EXISTE'] == 1) {
            $reqWhere .=
                " AND 
            (  
                (
                    acc.TYPE_FG = 'F'
                    AND exists (
                        SELECT CODE 
                        FROM FOURNISS fourniss WITH (nolock)
                        WHERE fourniss.CODE=acc.CODE_FOUR 
                        AND ( 
                            fourniss.DTEVALMIN <= $today 
                            AND (fourniss.DTEVALMAX >= $today OR fourniss.DTEVALMAX = 00000000)
                        )
                    )
                )
                OR
                (              
                    acc.TYPE_FG = 'G' 
                    AND exists (
                        SELECT CODE_FOU 
                        FROM FS_ACD fs WITH (nolock)
                        inner join FOURNISS fourniss WITH (nolock) on fs.CODE_FOU=fourniss.CODE 
                        WHERE fs.CODE_ACD=acc.CODE_ACD 
                        AND ( 
                            fourniss.DTEVALMIN <= $today 
                            AND (fourniss.DTEVALMAX >= $today OR fourniss.DTEVALMAX = 00000000)
                        )
                    )
                )
            )";
        }

        // Validité
        if (isset($params['valide']) && $params['valide'] == 1) {
            $reqWhere .=
                "  AND 
            (
                acc.DATE_DEB <= $today 
                AND (acc.DATE_FIN >= $today OR acc.DATE_FIN = 00000000)
            )";
        }

        // Centre émetteur et livraison
        if (isset($params['CENTRE_EMETTEUR']) && $params['CENTRE_EMETTEUR'] != '' && isset($params['CENTRE_LIVRAISON']) && $params['CENTRE_LIVRAISON'] != '') {
            $reqWhere .=
                " AND 
            (
                exists (SELECT lien.CODE_ACD FROM ACCTR lien WITH (nolock) WHERE lien.CODE_ACD=acc.CODE_ACD AND lien.CODE_CTR = '" . $params['CENTRE_EMETTEUR'] . "')
                OR
                exists (SELECT lien.CODE_ACD FROM ACCTRLIV lien WITH (nolock) WHERE lien.CODE_ACD=acc.CODE_ACD AND lien.CODE_CTR = '" . $params['CENTRE_LIVRAISON'] . "')
                OR 
                (
                    not exists (SELECT emet.CODE_ACD FROM ACCTR emet WITH (nolock) WHERE emet.CODE_ACD=acc.CODE_ACD) 
                    AND 
                    not exists (SELECT liv.CODE_ACD FROM ACCTRLIV liv WITH (nolock) WHERE liv.CODE_ACD=acc.CODE_ACD)
                )
            
            )";
        }

        // Société du Marché
        if (isset($params['CODE_STE']) && $params['CODE_STE'] != '') {
            $reqWhere .=
                " AND 
            (
                acc.MARCHE = '' 
                OR 
                exists (SELECT march.CODEMARCH FROM MARCHSOC march WITH (nolock) WHERE acc.MARCHE=march.CODEMARCH AND march.CODESOC = '" . $params['CODE_STE'] . "')
            )";
        }

        // Code Article
        if (isset($params['code_art_haplus']) && $params['code_art_haplus'] != '') {
            $reqWhere .=
                " AND 
            (
                not exists (SELECT * FROM ACART artacd WITH (nolock) WHERE artacd.CODE_ACD=acc.CODE_ACD AND artacd.CODE_ART = '" . $params['code_art_haplus'] . "')
                OR
                exists (
                    SELECT * 
                    FROM ACART artacd2 WITH (nolock) 
                    WHERE artacd2.CODE_ACD=acc.CODE_ACD 
                    AND artacd2.CODE_ART = '" . $params['code_art_haplus'] . "' 
                    AND artacd2.DDEBVAL <= $today 
                    AND (artacd2.DFINVAL = '00000000' OR artacd2.DFINVAL >= $today)
                )
            )";
        }

        $req .= $reqWhere;


        $whereArray = FilterTable::splitFilter($params ?? [], 0, 0, 1);
        if (count($whereArray) >  0 &&  isset($whereArray[1])) {
            $req .= str_ireplace('where', 'and', $whereArray[0]) . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    /**
     * @param array<mixed> $params
     * @param array<mixed> $cs
     */
    public function countFournisseur(array $params = [], array $cs = []): int
    {
        // TODO : make independent of Fournisseur() and factorize common logic
        return intval($this->Fournisseur($params, 'CODE', $cs, null, 'default', true)['nbr'] ?? 0);
    }

    /**
     * TODO A revoir le $select = '*' car dans certaine classe comme .src\Modules\DemandeAchat\Controller\CreationDemandeAchatController.php il manque le paramettre
     * select
     */
    // phpcs:ignore
    public function Fournisseur(array $params = [], string $select = '*', array $cs = [], string $orderByOxaMobility = null, string $context = 'defaut', bool $count = false, bool $multiSelect = false)
    {
        $whereMobility = '';
        $arrayReq = [];
        $paravrac = new Paravrac($this->pdo);

        if ($count != true) {
            $select = ' DISTINCT ' . $select;
        } else {
            $select = ' CODE ';// prevent COD_TAB declare multiple time in 'frs'
        }

        $req = "SELECT $select 
        FROM FOURNISS WITH (nolock)";

        if (isset($params['filter_typeFourniss']) && $params['filter_typeFourniss'] != '') {
            $gestFourniss = new \Modules\Fournisseur\Repository\GestFourniss($this->pdo);
            $params['filter_typeFourniss'] = $gestFourniss->shortListeId($params['filter_typeFourniss']);

            $req .= " JOIN FOU_LSTFOURN with(nolock) on FOURNISS.CODE = FOU_LSTFOURN.CODFOURN and CODLST=:CODLST ";
            $arrayReq[':CODLST'] = $params['filter_typeFourniss'];
            unset($params['filter_typeFourniss']);
        }

        if (($params['typeActif'] ?? '') == 'notFourn') {
            $req .= "right join FOU_NOT with (nolock) on FOU_NOT.COD_FOUR = FOURNISS.CODE";
        }


        if (isset($params['CODLST']) && $params['CODLST'] != '') {
            $req .= "left join FOU_LSTFOURN with(nolock) on FOU_LSTFOURN.CODFOURN = FOURNISS.CODE
            left join LST_FOURN with(nolock) on LST_FOURN.CODLST = FOU_LSTFOURN.CODLST";
        }

        //TODO utilisé ??
        if ($context == 'article') {
            $req .= " inner join ARTFOURN art with(nolock) on art.FOU=FOURNISS.CODE and art.CODE_ART='U071150' ";
        } elseif ($context == 'sousFamille') {
            $req .= " inner join SSFAMFOU sf with(nolock) on sf.CODE_FOUR=FOURNISS.CODE and sf.CODE_SFAM='061102' ";
        }

        if (isset($params['motClef']) && $params['motClef'] != '') {
            $req .= " inner join CLEFFOUR cl with(nolock) on cl.COD_FOUR=FOURNISS.CODE and cl.MOTCLEF = :motClef";
            $arrayReq[':motClef'] = $params['motClef'];
        }

        $reqWhereCS = ' WHERE 1=1';
        if (count($cs)) {
            $i = 0;

            foreach ($cs as $csLigne) {
                $req .= " LEFT JOIN  CS_DON d" . $i . " with(nolock) ON  d" . $i . ".COD_ENR = FOURNISS.CODE ";

                $csLigneExplode = explode('|', $csLigne);
                $req .= " AND d" . $i . ".NUM_FIX= '" . (int)$csLigneExplode[1] . "'";

                $req .= " LEFT JOIN CS_CHPS c" . $i . " with(nolock) ON d" . $i . ".NUM_FIX = c" . $i . ".NUM_FIX AND d" . $i . ".COD_TAB='FOURNISS'";

                $i++;
            }
        }
        $req .= $reqWhereCS;

        $today = Date('Ymd');

        // Validité
        if (isset($params['valide']) && $params['valide'] == 1) {
            $req .=
                "  AND 
            (
                DTEVALMIN <= $today 
                AND (DTEVALMAX >= $today OR DTEVALMAX = 00000000)
            )";
        }

        //filtre API OXA.MOBILITY
        if ($params['body']??null) {
            $body = $params['body'];
            $filtrePays    = $body['FournFiltrePays']??[];
            $fournDispoMobility = is_array($body['FournDispoMobility']) ? '' : $body['FournDispoMobility'];
            $fournAutoCC = is_array($body['FournAutoCC']) ? '' : $body['FournAutoCC'];
            $fournShortList = $body['FournFiltreShortList']??[];
            $fournSousFamille = $body['FournFiltreSousFamille']??[];

            $body['ClauseWhereAdd'] = [];
            if ($filtrePays) {
                if (is_array($filtrePays['CodePays'])) {
                    $filtrePays = $filtrePays['CodePays'];
                } elseif ($filtrePays['CodePays']) {
                    $filtrePays = [$filtrePays['CodePays']];
                }
                $body['ClauseWhereAdd'][] = " CODE_PAYS IN ('".implode("','", $filtrePays)."')";
            }

            if (sql::boolSql($fournDispoMobility)) {
                $body['ClauseWhereAdd'][] = " frs.DISPO_MOBILITY = ".sql::setBoolSql(sql::boolSql($fournDispoMobility))." ";
            }
            if (sql::boolSql($fournAutoCC)) {
                $body['ClauseWhereAdd'][] = " frs.AUTOCC = ".sql::setBoolSql(sql::boolSql($fournAutoCC))." ";
            }
            
            if ($fournShortList) {
                if (is_array($fournShortList['CodeShortList'])) {
                    $fournShortList = $fournShortList['CodeShortList'];
                } elseif ($fournShortList['CodeShortList']) {
                    $fournShortList = [$fournShortList['CodeShortList']];
                }
                $body['ClauseWhereAdd'][] = " frs.CODE in (select CODFOURN from FOU_LSTFOURN with(nolock) where CODLST IN ('".implode("','", $fournShortList)."')) ";
            }

            if ($fournSousFamille) {
                if (is_array($fournSousFamille['CodeSousFamille'])) {
                    $fournSousFamille = $fournSousFamille['CodeSousFamille'];
                } elseif ($fournSousFamille['CodeSousFamille']) {
                    $fournSousFamille = [$fournSousFamille['CodeSousFamille']];
                }
                $body['ClauseWhereAdd'][] = " frs.CODE in (select CODE_FOUR from SSFAMFOU with(nolock) where CODE_SFAM in ('".implode("','", $fournSousFamille)."'))";
            }
            
            $whereMobility = implode(" and ", $body['ClauseWhereAdd']);
        }

        if ($whereMobility) {
            $req .= ' AND ' . $whereMobility;
        }

        if (isset($params['NO_SIRET']) && $params['NO_SIRET'] != '') {
            $req .= ' AND NO_SIRET like :NO_SIRET ';
            $arrayReq[':NO_SIRET'] = '%' . $params['NO_SIRET'] . '%';
        }

        if (isset($params['TAXE2']) && $params['TAXE2'] != '') {
            $req .= ' AND upper(TAXE2) like :TAXE2 ';
            $arrayReq[':TAXE2'] = '%' . strtoupper($params['TAXE2']) . '%';
        }

        if (($paravrac->find(['TYPEPARV' => 'OXAPROC', 'CODEPARV' => 'CACHER_PROSPECTS'], ['LIB01PARV'])['LIB01PARV'] ?? 'N') == '0'  || (array_key_exists('popup_contexte', $params) && 'panier_execution' == $params['popup_contexte'])) {
            $req .= " and PROSPECT = 0x46 ";
        } elseif (isset($params['TYPE_FRS']) && $params['TYPE_FRS'] != '') {
            if ($params['TYPE_FRS'] == 'F') { // Si filtre sur les fournisseurs
                $req .= ' and PROSPECT = 0x46 ';
            } elseif ($params['TYPE_FRS'] == 'P') { // Si filtre sur les prospects
                $req .= ' and PROSPECT = 0x54 ';
            }
        }


        $reqWhere = '';

        if (array_key_exists('popup_contexte', $params) && $params['popup_contexte'] != '') {
            $contexte = $params['popup_contexte'];
            $signataire = in_array($contexte, ['DELSIGDA', 'DELSIGCDE', 'DELSIGBAP']) ?
                $params['SIGNAT'] : ($_SESSION['Session_Login'] ?? $params['login']);
            switch ($contexte) {
                case 'DELSIGBAP':
                case 'SIGDA':
                    $req .= " and CODE in (select distinct DA_ENTET.FOU
						from SIG_DA with (nolock), DA_ENTET with (nolock)
						where SIG_DA.NO_DA = DA_ENTET.NO_DA
						and DA_ENTET.SIGNEE = 0x46	
						and	SIG_DA.SIGNAT='{$signataire}'
						and SIG_DA.SIGNABLE= 0x54) ";
                    break;
                case 'DELSIGCDE':
                case 'SIGCDE':
                    $req .= " and CODE in (select distinct CA_ENTET.CODE_FOU
						from SIG_CDE with (nolock), CA_ENTET with (nolock)
						where SIG_CDE.NO_CDE = CA_ENTET.NO_CDE
						and CA_ENTET.SIGNEE = 0x46
						and SIG_CDE.SIGNAT='{$signataire}'
						and SIG_CDE.SIGNABLE= 0x54 and SIG_CDE.BON_SIGNE = 0x54 )";
                    break;
                case 'SIGBAP':
                    $req .= " and CODE in (select distinct FAC_ENT.CODE_FOUR
						from SIG_BAP with (nolock), FAC_ENT with (nolock)
						where SIG_BAP.NO_FAC   = FAC_ENT.NUM_FACT
						   and FAC_ENT.ATT_SIG  = 0x54	
						   and SIG_BAP.SIGNAT   = '{$signataire}'
						   and SIG_BAP.SIGNABLE = 0x54 and SIG_BAP.BAP_BAS = 0x54) ";
                    break;
            }
        }



        if (($params['typeActif'] ?? '') != 'ALL') {
            if (array_key_exists('code_sous_famille', $params) && $params['code_sous_famille'] != '' || array_key_exists('code_art_haplus', $params) && $params['code_art_haplus'] != '') {
                // Dossier 26591 : gestion de plusieur sous-familles / codes articles
                $code_sous_famille = (array_key_exists('code_sous_famille', $params) && trim($params['code_sous_famille']) != '') ? trim($params['code_sous_famille']) : '';
                $code_sous_famille = str_replace('|', '\',\'', $code_sous_famille);
                $code_sous_famille = '\'' . $code_sous_famille . '\'';

                $code_art_haplus = (array_key_exists('code_art_haplus', $params) && trim($params['code_art_haplus']) != '') ? trim($params['code_art_haplus']) : '';
                $code_art_haplus = str_replace('|', '\',\'', $code_art_haplus);
                $code_art_haplus = '\'' . $code_art_haplus . '\'';

                // à vérifier pour le contexte ; ici on ne s'occupe pas encore des AO
                // if (true) {
                // Si on vient du panier
                $sql_ssfam = '';
                $sql_art_ha = '';
                if (($params['typeActif'] ?? '') == 'sousFamille') {
                    $sql_ssfam = ($code_sous_famille != "''") ? ' CODE in (select distinct(ssfam.CODE_FOUR) CODE_FOUR from SSFAMFOU ssfam with(nolock) where ssfam.CODE_SFAM in (' . $code_sous_famille . '))' : '';
                }
                if (($params['typeActif'] ?? '') == 'article') {
                    $sql_art_ha = ($code_art_haplus != "''") ? ' CODE in (select distinct(af.FOU) CODE_FOUR from ARTFOURN af with(nolock) where af.CODE_ART in (' . $code_art_haplus . '))' : '';
                }
                // }

                if ($sql_ssfam != '' && $sql_art_ha != '') {
                    $reqWhere .= ' and (' . $sql_ssfam . ' or ' . $sql_art_ha . ')';
                } elseif ($sql_ssfam != '') {
                    $reqWhere .= ' and ' . $sql_ssfam;
                } elseif ($sql_art_ha != '') {
                    $reqWhere .= ' and ' . $sql_art_ha;
                }
            }
        }

        // if (array_key_exists('FRS_PERTINENTS', $params) && $params['FRS_PERTINENTS'] == 'on' ) {


        // if (isset($params['CODLST']) && $params['CODLST'] != '') {
        //     $reqWhere .= " AND FOU_LSTFOURN.CODLST= :CODLST";
        //     $arrayReq[':CODLST'] = $params['CODLST'];
        // }

        if (array_key_exists('departement', $params) && $params['departement'] != '') {
            $tableau_dep = explode(',', $params['departement']);
            $strDep = "";
            if (is_array($tableau_dep)) {
                foreach ($tableau_dep as $no_dep_tmp) {
                    $no_dep = trim($no_dep_tmp);
                    if (strlen($no_dep) == 2 && is_int((int)$no_dep)) {
                        $strDep .= '\'' . (int)$no_dep . '\'';
                    }
                }
                $strDep = str_replace('\'\'', '\',\'', $strDep);
                if ($strDep != '') {
                    $reqWhere .= ' and substring(CPOSTAL, 1, 2) in (' . $strDep . ') ';
                    //  $arrayReq[':listeDepartement'] = $strDep;
                }
            }
        }

        //filtre API OXA.MOBILITY
        if (isset($params['RechCodeOuLib']) && $params['RechCodeOuLib'] != '' && $params['RechCodeOuLib'] != null) {
            $reqWhere .= ' AND (upper(CODE) LIKE :code2 OR upper(RS) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :libelle2)';
            $arrayReq[':code2'] = mb_strtoupper('%' . $params['RechCodeOuLib'] . '%');
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $params['RechCodeOuLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($params['RechLib']) && $params['RechLib'] != '' && $params['RechLib'] != null) {
            $reqWhere .= ' AND upper(RS) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :libelle2';
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $params['RechLib'] . '%');
        }

        //filtre API OXA.MOBILITY
        if (isset($params['RechCPOuVille']) && $params['RechCPOuVille'] != '' && $params['RechCPOuVille'] != null) {
            $reqWhere .= ' AND (upper(VILLE1) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :ville OR upper(CPOSTAL) LIKE :cpostal)';
            $arrayReq[':ville'] = mb_strtoupper('%' . $params['RechCPOuVille'] . '%');
            $arrayReq[':cpostal'] = mb_strtoupper('%' . $params['RechCPOuVille'] . '%');
        }

        if (isset($params['ville']) && $params['ville'] != '') {
            $reqWhere .= ' AND upper(VILLE1) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :ville2';
            $arrayReq[':ville2'] = mb_strtoupper('%' . $params['ville'] . '%');
        }

        // accord-cadre de type groupement fournisseur
        if (isset($params['ACD_FG']) && $params['ACD_FG'] != '') {
            $reqWhere .=
                "  AND CODE in (SELECT CODE_FOU FROM FS_ACD WITH (nolock) WHERE CODE_ACD = '" . $params['ACD_FG'] . "')";
        }

        // Numéro de commande
        if (isset($params['NO_CDE']) && $params['NO_CDE'] != '') {
            $reqWhere .=
                "  AND CODE = (SELECT CODE_FOU FROM CA_ENTET WITH (nolock) WHERE NO_CDE = '" . $params['NO_CDE'] . "')";
        }

        if (isset($params['AO_RETENU']) && $params['AO_RETENU'] != '') {
            $reqWhere .= " and CODE in (select CODE_FOU from AO_RETPARLIGNE with (nolock) where NO_AO = '" . $params['AO_RETENU'] . "')";
        }

        //

        if (isset($params['excludeFrs']) && $params['excludeFrs']) {
            $reqWhere .= " AND CODE not in ('" . $params['excludeFrs'] . "')";
        }

        // if ($count == true) {
        //     $req = 'SELECT count(CODE) as nbr FROM ('.$req.") as frs WHERE 1=1 ";
        // }
        if ($count != true) {
            $req = 'SELECT * FROM (' . $req . ") as frs WHERE 1=1 ";
        } else {
            $req = 'SELECT count(*) as nbr FROM (' . $req . ") as frs WHERE 1=1 ";
        }

        if (sql::toBool($params['activeZone'] ?? false)) {
            $filterCentre = $params['centre'] ?? null;

            $req .= " AND (
                    not exists (
                        select null from DGEOFOUR with(nolock) 
                        where DGEOFOUR.CODE_FOU = frs.CODE
                    ) ";
            if ($filterCentre) {
                $req .= " OR exists (
                    select null from DGEOFOUR with(nolock) 
                    where DGEOFOUR.CODE_FOU = frs.CODE 
                        AND (DGEOFOUR.DCOUP + DGEOFOUR.ZONEGEO)  IN (select CONCAT(DCOUP, ZONEGEO) test from DGEOCTRE where CODE_CTR = :centrZG)
                )";
                $arrayReq[':centrZG'] = $params['centre'];
            }
            $req .= ")";
        }

        if ($multiSelect) {
            $notIn = $params['notin'] ?? '';

            $i = 1;
            if ($notIn != '') {
                $notIn = explode('|', substr($notIn, 1));
                $req .= " and CODE not in (";
                $passageNotInt = 0;
                foreach ($notIn as $not) {
                    $i++;
                    if ($passageNotInt == 0) {
                        $passageNotInt++;
                        $req .= " :where" . $i;
                    } else {
                        $req .= ", :where" . $i;
                    }
                    $arrayReq[':where' . $i] = $not;
                }

                $req .= ") ";
            }

            if ($params['value'] ?? false) {
                $req .= " and (";

                $req .= " (upper(CODE) like upper(:val1) OR upper(RS) like upper(:val2) OR upper(ENSCOMM) like upper(:val3)) ";

                $arrayReq[':val1'] = '%' . $params['value'] . '%';
                $arrayReq[':val2'] = '%' . $params['value'] . '%';
                $arrayReq[':val3'] = '%' . $params['value'] . '%';

                $req .= ")";
            }
        }

        //unset($params['centre']);

        $req .= $reqWhere;
        $whereArray = FilterTable::splitFilter($params, 0, 0, 1);

        if ((count($whereArray) >  0 &&  isset($whereArray[1])) || ($orderByOxaMobility && count($whereArray) >  0)) {
            $req .= str_ireplace('where', 'and', $whereArray[0]) . (!$count ? $whereArray['tri'] : '');

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $params, $arrayReq, $this->pdo);
        if ($count == true) {
            return $this->fetchAssocOne($req, $arrayReq);
        } else {
            return $this->fetchAssocAll($req, $arrayReq);
        }
    }


    /**
     * @return mixed[]
     */
    public function listeZoneGeoCentre(string $centre)
    {
        $req = "select (DCOUP+ZONEGEO) zoneGeo from DGEOCTRE with (nolock) WHERE CODE_CTR = :centre";

        $arrayReq[':centre'] = $centre;

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

    /**
     * @param array<mixed> $params
     * @param bool $api
     * @return mixed[]
     */
    public function siteStock($params = [], $api = false)
    {
        if (isset($params['filter_egal_CODE_STE']) && $params['filter_egal_CODE_STE'] != '') {
            $params['filter_egal_SSITESTO.CODE_STE'] = $params['filter_egal_CODE_STE'];
            unset($params['filter_egal_CODE_STE']);
        }

        $req = "SELECT DISTINCT SSITESTO.SITE, SSITESTO.DESIGN, SSITESTO.DEVISE FROM SSITESTO WITH (nolock)
        LEFT JOIN SSTOCKLO WITH (nolock) on SSTOCKLO.SITE=SSITESTO.SITE AND SSTOCKLO.CODE_STE=SSITESTO.CODE_STE
        LEFT JOIN SARTICLE WITH (nolock) on SARTICLE.CODE_ART=SSTOCKLO.CODE_ART
        WHERE 1=1";
        $arrayReq = [];

        $reqWhere = '';


        if (isset($params['CODE_ART']) &&  $params['CODE_ART'] != '') {
            $reqWhere .= ' AND SSTOCKLO.CODE_ART = :codeArt ';
            $arrayReq[':codeArt'] = $params['CODE_ART'];
        }
        if (isset($params['date_art_ouvert']) && $params['date_art_ouvert'] != '') {
            Date::dateToSql($params['date_art_ouvert']);
            $reqWhere .= " AND (SSTOCKLO.DTE_FERM >= " . $params['date_art_ouvert'] . " OR SSTOCKLO.DTE_FERM = '00000000') AND (SARTICLE.DTE_FERM >= " . $params['date_art_ouvert'] . " OR SARTICLE.DTE_FERM = '00000000')";
        }

        $req .= $reqWhere;
        if ($api) {
            //filtre API OXA.MOBILITY
            if (isset($params['RechCodeOuLib']) && $params['RechCodeOuLib']) {
                $req .= ' AND (upper(SSITESTO.SITE) LIKE :code OR  upper(SSITESTO.DESIGN) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :libelle)';
                $arrayReq[':code'] = mb_strtoupper('%' . $params['RechCodeOuLib'] . '%');
                $arrayReq[':libelle'] = mb_strtoupper('%' . $params['RechCodeOuLib'] . '%');
            }

            // filtre API OXA.MOBILITY
            if (isset($params['RechCode']) && $params['RechCode']) {
                $req .= ' AND upper(SSITESTO.SITE) LIKE :code2';
                $arrayReq[':code2'] = mb_strtoupper('%' . $params['RechCode'] . '%');
            }

            // filtre API OXA.MOBILITY
            if (isset($params['RechLib']) && $params['RechLib']) {
                $req .= ' AND upper(SSITESTO.DESIGN) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2';
                $arrayReq[':libelle2'] = mb_strtoupper('%' . $params['RechLib'] . '%');
            }

            if (isset($params['CodeSoc']) && $params['CodeSoc']) {
                $req .= ' AND (upper(SSITESTO.CODE_STE) = :codeSte)';
                $arrayReq[':codeSte'] = mb_strtoupper($params['CodeSoc']);
            }



            //filtre API OXA.MOBILITY
            if (isset($params['ClauseWhereAdd']) && $params['ClauseWhereAdd']) {
                $req .= ' AND (' . $params['ClauseWhereAdd'] . ')';
            }

            if (isset($params['FiltreValid']) && $params['FiltreValid']) {
                $datejour = date('Ymd');
                $req .= " AND ((SSITESTO.SIST_DTE_FERM >= $datejour OR SSITESTO.SIST_DTE_FERM = '00000000'))";
            }
            $whereArray = FilterTable::splitFilter($params, 0, 0, 1);
        } else {
            $whereArray = FilterTable::splitFilter($params, 0, 0, 1);
        }

        if ($whereArray) {
            if (count($whereArray[1]) > 0) {
                $req .= $whereArray[0];
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }

            $req .= $whereArray['tri'];
        }

        if ($api) {
            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $params, $arrayReq, $this->pdo);
        }

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



    public function siteStockAlone($params = [])
    {
        $req = "SELECT * FROM SSITESTO WITH (nolock)
        WHERE 1=1";
        $arrayReq = [];

        $reqWhere = '';


        if (isset($params['CODE_ART']) &&  $params['CODE_ART'] != '') {
            $reqWhere .= ' AND SSTOCKLO.CODE_ART = :codeArt ';
            $arrayReq[':codeArt'] = $params['CODE_ART'];
        }

        if (isset($params['filter_CODE_STE']) &&  $params['filter_CODE_STE'] != '') {
            $reqWhere .= ' AND SSITESTO.CODE_STE = :filter_CODE_STE ';
            $arrayReq[':filter_CODE_STE'] = $params['filter_CODE_STE'];
        }

        if (isset($params['date_art_ouvert']) && $params['date_art_ouvert'] != '') {
            Date::dateToSql($params['date_art_ouvert']);
            $reqWhere .= " AND (SSTOCKLO.DTE_FERM >= " . $params['date_art_ouvert'] . " OR SSTOCKLO.DTE_FERM = '00000000') ";
        }


        $req .= $reqWhere;
        $whereArray = FilterTable::splitFilter($params, 0, 0, 1);
        if ($whereArray) {
            $req .= $whereArray['tri'];
        }

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

    public function contactsGroupement($params = [])
    {
        $req = "SELECT * FROM CONTGF WITH (nolock) WHERE 1=1";

        $reqWhere = "";
        $arrayReq = [];

        if (isset($params['CODE']) &&  $params['CODE'] != '') {
            $reqWhere .= ' AND CONTGF.CODE_GF = :codeFou ';
            $arrayReq[':codeFou'] = $params['CODE'];
        }

        $req .= $reqWhere;

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

    public function contactsFournisseur($params = [])
    {
        $req = "SELECT CONTFOUR.*, 
            ADRFOURN.LIBELLE adrfournLibelle, 
            ADRFOURN.VILLE adrfournVille, 
            FOURNISS.RS as Fourniss_RS,
            FOURNISS.VILLEREGL reglementVille
        FROM CONTFOUR WITH (nolock)
        LEFT JOIN FOURNISS WITH (nolock) on FOURNISS.CODE=CONTFOUR.CODE_FOU
        LEFT JOIN ADRFOURN WITH (nolock) on ADRFOURN.COD_FOU=CONTFOUR.CODE_FOU AND ADRFOURN.COD_ADR=CONTFOUR.COD_ADR "
        . ((isset($params['NUM_FACT']) && $params['NUM_FACT'] != '' &&  $params['NUM_FACT'] != '0') ?
            "LEFT JOIN FAC_ENT WITH (nolock) on CONTFOUR.CODE_FOU = FAC_ENT.CODE_FOUR " : "")
        . " WHERE 1=1";
        $arrayReq = [];

        $reqWhere = '';


        if (isset($params['CODE_FOU']) &&  $params['CODE_FOU'] != '') {
            $reqWhere .= ' AND CONTFOUR.CODE_FOU = :codeFou ';
            $arrayReq[':codeFou'] = $params['CODE_FOU'];
        }

        if (isset($params['NUM_FACT']) &&  $params['NUM_FACT'] != '' &&  $params['NUM_FACT'] != '0') {
            $reqWhere .= ' AND FAC_ENT.NUM_FACT = :numfact ';
            $arrayReq[':numfact'] = $params['NUM_FACT'];
        }
        if (isset($params['NUM_CDE']) &&  $params['NUM_CDE'] != '') {
            $reqWhere .= ' AND FAC_ENT.NUM_CDE = :numCde ';
            $arrayReq[':numCde'] = $params['NUM_CDE'];
        }

        if ($params['email'] ?? null) {
            $reqWhere .= " AND CONTFOUR.EMAIL != ''";
        }

        $req .= $reqWhere;
        $whereArray = FilterTable::splitFilter($params, 0, 0, 1);

        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    public function etatStat($params = [])
    {
        $req = "SELECT CODETAT, DESCRIPT
        FROM ETATSTAT WITH (nolock)
        WHERE 1=1";
        $arrayReq = [];

        $reqWhere = '';


        if (isset($params['filtreUtilisateur']) &&  $params['filtreUtilisateur']) {
            $reqWhere .= " AND (CODETAT in (SELECT CODETAT FROM ETATUSER with(nolock) WHERE CODUSR='" . $_SESSION['Session_Login'] . "')
            OR CODETAT in (
                SELECT distinct(CODETAT) 
                FROM ETGRPET with(nolock) 
                join ETGRPUSR with(nolock) on ETGRPET.CODGRP=ETGRPUSR.CODGRP 
                WHERE CODUSR='" . $_SESSION['Session_Login'] . "' ))";
        }

        $req .= $reqWhere;
        $whereArray = FilterTable::splitFilter($params, 0, 0, 1);
        if (count($whereArray) >  0 &&  isset($whereArray[1])) {
            $req .= str_ireplace('where', 'and', $whereArray[0]) . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function equivArt(string $numCleq, string $codeFou, $post = [])
    {
        $req = "SELECT ARTICLES.NUM_CLEQ, ARTICLES.CODE_ART, 
                    (CASE 
                        WHEN ISNULL(t.DES_ART, '') = ''
                        THEN ARTICLES.DESIGN
                        ELSE t.DES_ART
                    END) AS DESIGN, REFARTFAB, COEFFCLA
                FROM ARTICLES WITH (NOLOCK)
                    LEFT JOIN PXAFO WITH (NOLOCK) ON PXAFO.CODE_ART = ARTICLES.CODE_ART AND PXAFO.CODE_FOU = :codeFou AND QTE = 0
                    LEFT OUTER JOIN TRAD_ART t WITH(NOLOCK) ON ARTICLES.CODE_ART = t.COD_ART AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
                WHERE ARTICLES.NUM_CLEQ = :numCleq";

        $arrayReq[':numCleq'] = $numCleq;
        $arrayReq[':codeFou'] = $codeFou;

        if (isset($post['filter_CODE_ART'])) {
            $req .= " and upper(ARTICLES.CODE_ART) like upper(:codeArt)";
            $arrayReq[':codeArt'] = '%' . $post['filter_CODE_ART'] . '%';
            unset($post['filter_CODE_ART']);
        }


        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    public function famille($post = [], $autoCompleteRow = null)
    {

        if ($autoCompleteRow) {
            $selectRow = $autoCompleteRow;
        } else {
            $selectRow = '*';
        }

        $req = "SELECT $selectRow FROM (
                    SELECT FAMILLE.CODE_FAM,
                        CASE 
                            WHEN ISNULL(t.DESIGNAT, '') = ''
                            THEN FAMILLE.DESIGNAT 
                            ELSE t.DESIGNAT 
                        END DESIGNAT
                    FROM FAMILLE WITH(NOLOCK)
                    LEFT JOIN TRAD_FAM t WITH(NOLOCK) ON t.CODE_FAM = FAMILLE.CODE_FAM AND COD_LNG = :langue
                ) FAMILLE WHERE 1=1";

        $arrayReq[':langue'] = $_SESSION['Session_Langue'];

        if (isset($post['CODE_FAM']) && $post['CODE_FAM'] != '') {
            $req .= " AND (UPPER(CODE_FAM) LIKE UPPER(:code) OR UPPER(DESIGNAT) LIKE UPPER(:libelle) OR (ISNULL(DESIGNAT, '') = '' AND UPPER(DESIGNAT) LIKE UPPER(:libelle2)))";

            $arrayReq[":code"]      = '%' . $post['CODE_FAM'] . '%';
            $arrayReq[":libelle"]   = '%' . $post['CODE_FAM'] . '%';
            $arrayReq[":libelle2"]  = '%' . $post['CODE_FAM'] . '%';
        }

        if (!$this->convertLibelle) {
            $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
            if (count($whereArray) >  0) {
                $req .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $arrayReq = array_merge($arrayReq, $whereArray[1]);
                }
            }

            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        }

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

    // phpcs:ignore
    public function Sousfamille($params = [], $autoCompleteRow = null)
    {

        if ($autoCompleteRow) {
            $selectRow = $autoCompleteRow;
        } else {
            $selectRow = '*';
        }

        $req = "SELECT $selectRow FROM (
                    SELECT SSFAMILL.CODE_SFA, SSFAMILL.SFA_ICDE,
                        CASE 
                            WHEN ISNULL(trad.DESIGNAT, '') = ''
                            THEN SSFAMILL.DESIGNAT 
                            ELSE trad.DESIGNAT 
                        END DESIGNAT
                    FROM SSFAMILL WITH(NOLOCK)
                    LEFT JOIN TRAD_SFA trad WITH(NOLOCK) 
                        ON trad.CODE_SFA = SSFAMILL.CODE_SFA 
                        AND trad.COD_LNG = :langue
                ) SousFam WHERE 1=1";

        $arrayReq[':langue'] = $_SESSION['Session_Langue'];
        $reqWhere = '';

        if (($params['contexte']??'' == 'CDE') && empty($params['autoriserCde'])) {
            $reqWhere.= " and SousFam.SFA_ICDE = 0x46 ";
        }

        if (isset($params['listeSousFamille']) && $params['listeSousFamille'] != '') {
            $reqWhere .= " AND CODE_SFA in (" . $params['listeSousFamille'] . ")";
        }

        if (isset($params['CODE_SFA']) && $params['CODE_SFA'] != '') {
            $req .= " AND (
                UPPER(CODE_SFA) LIKE UPPER(:code) 
                OR UPPER(DESIGNAT) LIKE UPPER(:libelle)
            )";

            $arrayReq[":code"]      = '%' . $params['CODE_SFA'] . '%';
            $arrayReq[":libelle"]   = '%' . $params['CODE_SFA'] . '%';
        }

        $req .= $reqWhere;

        if (!$this->convertLibelle) {
            $whereArray = FilterTable::splitFilter($params, 0, 0, 1);

            if (count($whereArray) >  0 &&  isset($whereArray[1])) {
                $req .= str_ireplace('where', 'and', $whereArray[0]) . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $arrayReq = array_merge($arrayReq, $whereArray[1]);
                }
            }
        }

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

    // phpcs:ignore
    public function ConditionPaiement($post, $autoCompleteRow = false)
    {
        if ($autoCompleteRow) {
            $selectRow = $autoCompleteRow;
        } else {
            $selectRow = '*';
        }

        $req = "SELECT $selectRow FROM (
                    SELECT CONDPAIE.CODE_CDP, 
                        CASE 
                            WHEN ISNULL(t.LIBELLE,'') = ''
                            THEN CONDPAIE.LIBELLE
                            ELSE t.LIBELLE 
                        END LIBELLE
                    FROM CONDPAIE WITH(nolock)
                    LEFT JOIN TRAD_CDP t WITH(NOLOCK) ON t.CODE_CDP = CONDPAIE.CODE_CDP AND COD_LNG = :langue
                ) CONDPAIE WHERE 1=1";

        $arrayReq[':langue'] = $_SESSION['Session_Langue'];

        if (isset($post['CODE_CDP']) && $post['CODE_CDP'] != '') {
            $req .= " AND (
                UPPER(CODE_CDP) LIKE UPPER(:code) 
                OR UPPER(LIBELLE) LIKE UPPER(:libelle) 
            )";

            $arrayReq[":code"]      = '%' . $post['CODE_CDP'] . '%';
            $arrayReq[":libelle"]   = '%' . $post['CODE_CDP'] . '%';
        }

        if (!$this->convertLibelle) {
            $whereArray = FilterTable::splitFilter($post, 0, 0, 1);
            if (!empty($whereArray)) {
                $req .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $arrayReq = array_merge($arrayReq, $whereArray[1]);
                }
            }

            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        }

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

    public function modePaiement($post, $autoCompleteRow = false)
    {
        if ($autoCompleteRow) {
            $selectRow = $autoCompleteRow;
        } else {
            $selectRow = '*';
        }

        $req = "SELECT $selectRow FROM (
                    SELECT MODEPAIE.CODE_MDP, 
                        CASE 
                            WHEN ISNULL(t.LIBELLE,'') = ''
                            THEN MODEPAIE.LIBELLE 
                            ELSE t.LIBELLE 
                        END LIBELLE
                    FROM MODEPAIE WITH(NOLOCK)
                    LEFT JOIN TRAD_MDP t WITH(NOLOCK) ON t.CODE_MDP = MODEPAIE.CODE_MDP AND COD_LNG = :langue
                ) MODEPAIE WHERE 1=1";

        $arrayReq[':langue'] = $_SESSION['Session_Langue'];
        if (isset($post['CODE_MDP']) && $post['CODE_MDP'] != '') {
            $req .= " AND (UPPER(CODE_MDP) LIKE UPPER(:code) OR UPPER(LIBELLE) LIKE UPPER(:libelle) OR (ISNULL(LIBELLE, '') = '' AND UPPER(LIBELLE) LIKE UPPER(:libelle2)))";

            $arrayReq[":code"]      = '%' . $post['CODE_MDP'] . '%';
            $arrayReq[":libelle"]   = '%' . $post['CODE_MDP'] . '%';
            $arrayReq[":libelle2"]  = '%' . $post['CODE_MDP'] . '%';
        }

        if (!$this->convertLibelle) {
            $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
            if (count($whereArray) >  0) {
                $req .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $arrayReq = array_merge($arrayReq, $whereArray[1]);
                }
            }

            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        }
        return $this->fetchAssocAll($req, $arrayReq);
    }

    public function typeAchat($post, $autoCompleteRow = false)
    {
        if ($autoCompleteRow) {
            $selectRow = $autoCompleteRow;
        } else {
            $selectRow = '*';
        }

        $req = "SELECT $selectRow FROM (
                    SELECT TYPEACHAT.TYP_ACH,
                        CASE 
                            WHEN ISNULL(t.TRA_TAC,'') = ''
                            THEN TYPEACHAT.LIB_TAC 
                            ELSE t.TRA_TAC 
                        END LIB_TAC
                    FROM TYPEACHAT WITH(NOLOCK)
                    LEFT JOIN TRAD_TAC t WITH(NOLOCK) ON t.TYP_ACH = TYPEACHAT.TYP_ACH AND COD_LNG = :langue
                ) TYPEACHAT WHERE 1=1";

        $arrayReq[':langue'] = $_SESSION['Session_Langue'];
        if (isset($post['TYP_ACH']) && $post['TYP_ACH'] != '') {
            $req .= " AND (
                UPPER(TYP_ACH) LIKE UPPER(:code) 
                OR UPPER(LIB_TAC) LIKE UPPER(:libelle)
            )";

            $arrayReq[":code"]      = '%' . $post['TYP_ACH'] . '%';
            $arrayReq[":libelle"]   = '%' . $post['TYP_ACH'] . '%';
        }

        if (!$this->convertLibelle) {
            $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
            if (count($whereArray) >  0) {
                $req .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $arrayReq = array_merge($arrayReq, $whereArray[1]);
                }
            }

            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        }

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

    public function modeLivraison($post, $autoCompleteRow = false)
    {
        if ($autoCompleteRow) {
            $selectRow = $autoCompleteRow;
        } else {
            $selectRow = '*';
        }

        $req = "SELECT $selectRow FROM (
                    SELECT MODELIVR.CODE_MDL, 
                        CASE 
                            WHEN ISNULL(t.LIBELLE,'') = ''
                            THEN MODELIVR.LIBELLE 
                            ELSE t.LIBELLE 
                        END LIBELLE
                    FROM MODELIVR WITH(NOLOCK)
                    LEFT JOIN TRAD_MDL t WITH(NOLOCK) ON t.CODE_MDL = MODELIVR.CODE_MDL AND COD_LNG = :langue
                ) MODELIVR WHERE 1=1";

        $arrayReq[':langue'] = $_SESSION['Session_Langue'];
        if (isset($post['CODE_MDL']) && $post['CODE_MDL'] != '') {
            $req .= " AND (UPPER(CODE_MDL) LIKE UPPER(:code) OR UPPER(LIBELLE) LIKE UPPER(:libelle) OR (ISNULL(LIBELLE, '') = '' AND UPPER(LIBELLE) LIKE UPPER(:libelle2)))";

            $arrayReq[":code"]      = '%' . $post['CODE_MDL'] . '%';
            $arrayReq[":libelle"]   = '%' . $post['CODE_MDL'] . '%';
            $arrayReq[":libelle2"]  = '%' . $post['CODE_MDL'] . '%';
        }

        if (!$this->convertLibelle) {
            $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
            if (count($whereArray) >  0) {
                $req .= $whereArray[0] . $whereArray['tri'];

                if (count($whereArray[1]) != 0) {
                    $arrayReq = array_merge($arrayReq, $whereArray[1]);
                }
            }

            $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
            $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);
        }

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

    /**
     * retourne la liste des mode de transport avec traduction
     * @return array<mixed>
     */
    public function getModeTransportTrad(string $value = '')
    {
        $req = "SELECT mdt.MOD_TRS, isnull(tr.DES_ART,mdt.MOD_TRS) MOD_TRS_TRAD 
        FROM MODTRANS mdt WITH (NOLOCK)
            LEFT OUTER JOIN TRAD_MDT tr WITH (NOLOCK) ON tr.COD_ART=mdt.MOD_TRS AND tr.COD_LNG = :codeLangue";

        $arrayReq = [
            ':codeLangue' => $_SESSION['Session_Langue'],
        ];
        if ($value) {
            $req .= " WHERE mdt.MOD_TRS = :value";
            $arrayReq[':value'] = $value;
        }

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

    public function listeCLausier($post)
    {
        $req = "SELECT * FROM CLAUSIER WITH (nolock)
        left join MEMOS WITH (nolock) on MEMOS.NODEMEMO = NM_PARAG";

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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


    /**
     * listeTypeDoc
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function listeTypeDoc(array $post, bool $multiSelect = false): array
    {
        $req = "select * from TYPDOC with (nolock) where 1=1";

        if (isset($post['valide']) && ($post['valide'] == 'on' ||  $post['valide'] == '1' || $post['valide'] == true)) {
            $req .= " and (DATE_DEB = '00000000' or DATE_DEB <= '".date('Ymd')."') and (DATE_FIN = '00000000' or DATE_FIN >= '".date('Ymd')."') ";
        }

        if (isset($_SESSION['NiveauConfidentialite'])) {
            $req .= " and NIV_CONF <= " . $_SESSION['NiveauConfidentialite'];
        }

        if (isset($post['oxaproc']) && $post['oxaproc']) {
            $exclus1 = Sql::paravrac($this->pdo, 'OXAMPROC', 'TYPE_DOC_CDE_COMPTOIR');
            $exclus2 = Sql::paravrac($this->pdo, 'OXAPROC', 'TYPDOCUMENT');
            $req .=  " and TYPDOC.TYPE not in ('$exclus1', '$exclus2') ";
        }

        if ($multiSelect) {
            $notIn = $post['notin'] ?? '';

            $i = 1;
            if ($notIn != '') {
                $notIn = explode('|', substr($notIn, 1));
                $req .= "TYPDOC.TYPE not in (";
                $passageNotInt = 0;
                foreach ($notIn as $not) {
                    $i++;
                    if ($passageNotInt == 0) {
                        $passageNotInt++;
                        $req .= " :where" . $i;
                    } else {
                        $req .= ", :where" . $i;
                    }
                    $arrayReq[':where' . $i] = $not;
                }

                $req .= ") ";
            }

            if ($post['value'] ?? false) {
                $req .= " and (";

                $req .= " (upper(TYPDOC.TYPE) like upper(:val1) OR upper(TYPDOC.LIBELLE) like upper(:val2)) ";

                $arrayReq[':val1'] = '%' . $post['value'] . '%';
                $arrayReq[':val2'] = '%' . $post['value'] . '%';

                $req .= ")";
            }
        }

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * listeUnite
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function listeUnite(array $post): array
    {
        $listeUnite = '';

        /** Ce bloque sert pour les AO, CDE, DA, si l'unité est lié a un article, il faut filtré si celui-ci en a, uniquement sur ces unités paramétré */
        if (isset($post['filtreArticle']) && $post['filtreArticle'] != '') {
            $articleEntity = new articles($this->pdo);
            $article = $articleEntity->find($post['filtreArticle']);
            $arrayTmp[$article['UNIT']] = $article['UNIT'];

            $critique = false;
            if ($article) {
                $critique = sql::boolSql($article['CRITIQUE']);
            }

            $arrayUnite = [];
            if (!$critique) {
                $convArtEntity = new ConvArt($this->pdo);
                $arrayUnite = $convArtEntity->fetchAll([], ['filter_egal_CODE_ART' => $post['filtreArticle']]);

                if ($arrayUnite) {
                    foreach ($arrayUnite as $unite) {
                        $arrayTmp[$unite['CODE_UNI2']] = $unite['CODE_UNI2'];
                    }
                }
            }

            if ($critique || !$arrayUnite) {
                $conversEntity = new Convers($this->pdo);
                $convers = $conversEntity->fetchall([], ['filter_egal_CODE_UNI1' => $article['UNIT']]);

                if ($convers) {
                    foreach ($convers as $unite) {
                        $arrayTmp[$unite['CODE_UNI2']] = $unite['CODE_UNI2'];
                    }
                }
            }

            $listeUnite = " and CODE_UNI in ('" . implode("','", $arrayTmp) . "')";
        }

        $req = "select * from UNITES with (nolock) where 1=1";

        $req .= $listeUnite;

        $arrayReq = [];
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    /**
     * listeCampNotex
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function listeCampNotex($post): ?array
    {

        /**
         * Creation de la requete
         */

        $arrayReq = [];

        $req = "SELECT ID_NOTEUR, NOM_NOTEUR, FCT_NOTEUR, EMAIL_NOTEUR, SOC_NOTEUR, CREATEUR, DTE_CREAT, CAMP_NOTEX.DTE_FERM , FERME_PAR, HASTUTIL.NOM
               FROM CAMP_NOTEX with(nolock)
               LEFT JOIN HASTUTIL with(nolock) ON HASTUTIL.CODE = CAMP_NOTEX.CREATEUR " . ((isset($post['valide']) && $post['valide'] == 'on') ? ' WHERE 1=1 ' : ' WHERE (DTE_CREAT <=\'' . date('Ymd') . '\' OR DTE_CREAT = \'00000000\') ');


        /**
         * Filtre
         */

        $arrayReq = [];

        if (isset($post['filter_DTE_CREAT']) &&  $post['filter_DTE_CREAT'] != '') {
            \Helpers\Outil\Date::dateToSql($post['filter_DTE_CREAT']);

            $req .= " AND  DTE_CREAT like :DTE_CREAT ";
            $arrayReq[':DTE_CREAT'] = '%' . $post['filter_DTE_CREAT'] . '%';
            echo $post['filter_DTE_CREAT'];
            unset($post['filter_DTE_CREAT']);
        }


        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        /**
         * Validation de la requete et execution
         */
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * getServiceRequests
     *
     * @param  string $user
     * @param  string $company
     * @param  string $status
     * @param  array<mixed> $filters
     * @param  string $orderBy
     * @param  int $page
     * @return array<mixed>
     */
    public function getServiceRequests(string $user, string $company, string $status, array $filters, string $orderBy, int $page)
    {
        $query = "
        DECLARE 
        @v_Utilisateur			VARCHAR(30)	    = :USER, -- SGR
        @v_CodeSoc				VARCHAR(7)		= :COMPANY, -- DEMO
        @v_StatutDS				VARCHAR(1)		= :SR_STATUS, -- S = soldée (traitée), E = en cours 
        @v_FiltreCodeSI			VARCHAR(14)	    = :CODE_SI, -- S5, S1 
        @v_FiltreCodeCG			VARCHAR(12)	    = :CODE_CG, 
        @v_FiltreCodeCDC		VARCHAR(14)	    = :CODE_CDC,  -- 606350
        @v_FiltreCodeSCA		VARCHAR(14)	    = :CODE_SCA, 
        @v_FiltreSFA			VARCHAR(7)		= :SUBFAMILY, -- 2800
        @v_FiltreCodeArt		VARCHAR(13)	    = :CODE_ARTICLE, -- 295
        @v_FiltreRechDesign		VARCHAR(30)	    = :DESIGNATION, -- CHAUSS, CARTON, GEL
        @v_FiltreDateCreation	VARCHAR(8)		= :CREATION_DATE,
        @v_FiltreNumDA			INT				= :PR_NUMBER, -- 39
        @v_FiltreSiteStock		VARCHAR(7)		= :SITE_STOCK, -- MAG_IDF
        @v_ClauseCollate		VARCHAR(30)	    = 'SQL_Latin1_General_CP1_CI_AI' -- COLLATE French_100_CI_AI -- à utiliser ?
        ;

        /* Une Demande de Service (DS) liée aux DAs de l'utilisateur est une DS 
            dont au moins une ligne porte sur une des Demandes d'achat (DA) de l'utilisateur. 
        */
        SELECT DISTINCT 
            dse.NO_DS AS NO_DOC, 
            dse.DTE_CREAT, 
            dsl.NO_DA,
            /* Date de livraison : la première date non nulle (la plus récente) trouvée dans les lignes de planning */
            (
                SELECT ISNULL(MIN(dsp.DTE_SOUH), '00000000') 
                    FROM SDS_PLAN dsp WITH(NOLOCK)
                    WHERE dsp.NO_DS = dse.NO_DS AND dsp.DTE_SOUH <> '00000000'
            ) AS DTE_LIV,
            dse.SITE, 
            stk.DESIGN AS LIB_SITE, 
            dse.CODE_STE 
        /* Lecture des en-têtes de DS */
        FROM SDS_ENTE dse WITH(NOLOCK) 
            /* Récupération des lignes de chaque DS 
                - On utilise cette jointure pour filtrer, mais d'autres méthodes sont possibles avec des sous-requêtes (un IN, ou un EXISTS)
                - Chaque DS doit avoir au moins une ligne avec la valeur d'au moins un des filtres (donc ON combine par des OR dans la clause de jointure)
                - Si les filtres ne sont pas précisés, il ne faut pas les inclure dans la jointure (requête dynamique à construire)
            */ 
            INNER JOIN SDS_LIGN dsl WITH(NOLOCK) 
                ON (dsl.NO_DS = dse.NO_DS)  
            /* Récupération de l'en-tête de chaque DA trouvée sur les lignes de DS */ 
            INNER JOIN DA_ENTET dae WITH(NOLOCK) 
                ON dae.NO_DA = dsl.NO_DA 
            /* Récupération du site de stock de la DS */ 
            LEFT OUTER JOIN SSITESTO stk WITH(NOLOCK) 
                ON stk.SITE = dse.SITE
    
        WHERE 
            /* Filtre imposé : l'utilisateur ('Mes demandes') */
            dae.NO_DEM = @v_Utilisateur
            /* Filtre imposé : la société de la session */
            AND dse.CODE_STE = @v_CodeSoc
            /* Filtre imposé : le statut de la DS */
            AND dse.SOLDE_DS = @v_StatutDS
            /* Filtres optionnels sur l'en-tête de la DA ou de la DS (le filtre NUM_DA est trompeur, il filtre sur le dae.NO_DA ou sur dse.NO_DS) */
            AND (
                    (dae.NO_DA = @v_FiltreNumDA OR @v_FiltreNumDA = 0)   
                        OR 
                    (dse.NO_DS = @v_FiltreNumDA OR @v_FiltreNumDA = 0)   
                )                   
            /* Filtres optionnels sur l'en-tête de la DS */
            AND (dse.SITE = @v_FiltreSiteStock OR @v_FiltreSiteStock = '') 
            AND (dse.DTE_CREAT = @v_FiltreDateCreation OR @v_FiltreDateCreation = '') 
            /* Filtres optionnels sur les imputations & code article */
            AND (dsl.SECTIMP = @v_FiltreCodeSI OR @v_FiltreCodeSI = '')
            AND (dsl.ID_CG = @v_FiltreCodeCG OR '' = @v_FiltreCodeCG)
            AND (dsl.CODE_COMP = @v_FiltreCodeCDC OR '' = @v_FiltreCodeCDC)
            AND (dsl.SSCPTEANA = @v_FiltreCodeSCA OR '' = @v_FiltreCodeSCA) 
            AND (dsl.ART = @v_FiltreCodeArt OR '' = @v_FiltreCodeArt) 
            /* Filtres optionnels (Sous famille, Désignation de l'article) sur la table Article de stock */
            AND EXISTS (
                SELECT TOP 1 art.CODE_ART FROM SARTICLE art WITH(NOLOCK) 
                WHERE art.CODE_ART = dsl.ART 
                    AND (art.SSFAM = @v_FiltreSFA OR '' = @v_FiltreSFA)
                    AND (art.DESIGN COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%' + @v_FiltreRechDesign + '%' COLLATE SQL_Latin1_General_CP1_CI_AI OR @v_FiltreRechDesign = '')
            ) 			
";

        //filtre API OXA.MOBILITY
        if (isset($filters['ClauseWhereAddDS']) && $filters['ClauseWhereAddDS'] != '' && $filters['ClauseWhereAddDS'] != null) {
            $query .= ' AND (' . $filters['ClauseWhereAddDS'] . ')';
        }

        $params = [
            ':USER'             => $user,
            ':COMPANY'          => $company,
            ':SR_STATUS'        => $status,
            ':CODE_SI'          => $filters['code_si'],
            ':CODE_CG'          => $filters['code_cg'],
            ':CODE_CDC'         => $filters['code_cdc'],
            ':CODE_SCA'         => $filters['code_sca'],
            ':SUBFAMILY'        => $filters['subfamily'],
            ':CODE_ARTICLE'     => $filters['code_article'],
            ':DESIGNATION'      => $filters['designation'],
            ':CREATION_DATE'    => $filters['creation_date'],
            ':PR_NUMBER'        => $filters['pr_number'],
            ':SITE_STOCK'       => $filters['site_stock'],
        ];

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($query, $filters, $params, $this->pdo, true);

        $query .= " ORDER BY " . $orderBy;
        $query .= " OFFSET ((" . $page . " - 1) * " . $_SESSION['PaginatorGestion']['nbrArticleParPage'] . ") ROWS
        FETCH NEXT " . $_SESSION['PaginatorGestion']['nbrArticleParPage'] . " ROWS ONLY";

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


    /**
     * getSignaturePurchaseRequests
     *
     * @param  string $user
     * @param  string $company
     * @param  array<mixed> $filters
     * @param  string $orderBy
     * @param  int $page
     * @return array<mixed>
     */
    public function getSignaturePurchaseRequests(string $user, string $company, array $filters, string $orderBy, int $page)
    {
        $query = "
        DECLARE 
        @v_Utilisateur			VARCHAR(30)	    = :USER, -- ACHETEUR1, SGR
        @v_CodeSoc				VARCHAR(7)		= :COMPANY, -- DEMO
        @v_FiltreCodeSI			VARCHAR(14)	    = :CODE_SI, -- S5, S1 
        @v_FiltreCodeCG			VARCHAR(12)	    = :CODE_CG, 
        @v_FiltreCodeCDC		VARCHAR(14)	    = :CODE_CDC,  -- 606350, 606351
        @v_FiltreCodeSCA		VARCHAR(14)	    = :CODE_SCA, 
        @v_FiltreSFA			VARCHAR(7)		= :SUBFAMILY, -- 2800, 3100
        @v_FiltreCodeArt		VARCHAR(13)	    = :CODE_ARTICLE, -- 295, 1126
        @v_FiltreRechDesign		VARCHAR(30)	    = :DESIGNATION, -- CHAUSS, CARTON, GEL
        @v_FiltreDateCreation	VARCHAR(8)		= :CREATION_DATE,
        @v_FiltreNumDA			INT				= :PR_NUMBER, -- 39, 21
        @v_FiltreSiteStock		VARCHAR(7)		= :SITE_STOCK, -- MAG_IDF
        @v_ClauseCollate		VARCHAR(30)	    = 'SQL_Latin1_General_CP1_CI_AI' -- COLLATE French_100_CI_AI -- à utiliser ?
        ;

        /* Une Demande de Service (DS) en signature est en réalité une Demande d'achat (DA)
            - de l'utilisateur, 
            - avec au moins une ligne sur stock, 
            - et en attente de signature
        */
        SELECT DISTINCT 
                dae.NO_DA AS NO_DOC, 
                dae.DTE_EMISS AS DTE_CREAT, 
                dae.NO_DA,
                /* Date de livraison : la première date non nulle (la plus récente) trouvée dans les lignes de planning */
                /*
                (
                    SELECT ISNULL(MIN(dap.DTE_LIV), '00000000') 
                        FROM DA_PLANN dap WITH(NOLOCK)
                        WHERE dap.NO_DA = dae.NO_DA AND dap.DTE_LIV <> '00000000'
                ) AS DTE_LIV,
                */
                (
                    SELECT ISNULL( MIN(ISNULL(dap.DTE_LIV, dal2.DTELIVSOU)), '00000000') 
                        FROM DA_LIGNE dal2 WITH(NOLOCK) 
						LEFT OUTER JOIN DA_PLANN dap WITH(NOLOCK) ON (dal2.NO_DA = dap.NO_DA AND dal2.NO_PO = dap.NO_PO)  
                        WHERE dal2.NO_DA = dae.NO_DA
                ) AS DTE_LIV,
                dal.SITE_STK AS SITE, 
                stk.DESIGN AS LIB_SITE, 
                dae.CODE_STE 
            /* Lecture des en-têtes de DA */
            FROM DA_ENTET dae WITH(NOLOCK) 
                /* Récupération des lignes de chaque DA 
                    - On utilise cette jointure pour filtrer, mais d'autres méthodes sont possibles avec des sous-requêtes (un IN, ou un EXISTS)
                    - Chaque DA doit avoir au moins une ligne avec la valeur d'au moins un des filtres (donc ON combine par des OR dans la clause de jointure)
                    - Si les filtres ne sont pas précisés, il ne faut pas les inclure dans la jointure (requête dynamique à construire)
                */ 
                INNER JOIN DA_LIGNE dal WITH(NOLOCK) ON (
                    dal.NO_DA = dae.NO_DA 
                    /* avec au moins une ligne sur stock (imposé)*/
                    AND dal.SITE_STK <> ''
                )  
                /* Récupération du site de stock de la ligne de DA */ 
                LEFT OUTER JOIN SSITESTO stk WITH(NOLOCK) ON stk.SITE = dal.SITE_STK
        
            WHERE 
                /* Filtre imposé : l'utilisateur ('Mes demandes') */
                dae.NO_DEM = @v_Utilisateur
                /* Filtre imposé : la société de la session */
                AND dae.CODE_STE = @v_CodeSoc
                /* Filtre imposé : DA validée et en attente de signature */
                AND dae.VALIDITE = 0x54 
                AND dae.SIGNEE = 0x46 
                AND dae.REFUSEE = 0x46 
                /* Filtres optionnels sur l'en-tête de la DA */
                AND (dae.NO_DA = @v_FiltreNumDA OR 0 = @v_FiltreNumDA)
                AND (dae.DTE_EMISS = @v_FiltreDateCreation OR '' = @v_FiltreDateCreation)
                AND (dal.SITE_STK = @v_FiltreSiteStock OR '' = @v_FiltreSiteStock)
                /* Filtres optionnels l'article de la ligne de DA (code article, sous-famille, désignation) */
                AND (dal.CODE_ART	= @v_FiltreCodeArt OR @v_FiltreCodeArt = '') 
                AND (dal.SSFAM = @v_FiltreSFA OR '' = @v_FiltreSFA)
                AND (dal.DES_SOMM COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%' + @v_FiltreRechDesign + '%' COLLATE SQL_Latin1_General_CP1_CI_AI OR @v_FiltreRechDesign = '') 
                /* Filtres optionnels sur les imputations */
                AND (dal.SECTIMP = @v_FiltreCodeSI  OR '' = @v_FiltreCodeSI)
                AND (dal.ID_CG = @v_FiltreCodeCG  OR '' = @v_FiltreCodeCG)
                AND (dal.CODE_COMP = @v_FiltreCodeCDC OR '' = @v_FiltreCodeCDC)
                AND (dal.SSCPTEANA = @v_FiltreCodeSCA  OR '' = @v_FiltreCodeSCA)
        ";

        //filtre API OXA.MOBILITY
        if (isset($filters['ClauseWhereAddDS']) && $filters['ClauseWhereAddDS'] != '' && $filters['ClauseWhereAddDS'] != null) {
            $query .= ' AND (' . $filters['ClauseWhereAddDS'] . ')';
        }

        $params = [
            ':USER'             => $user,
            ':COMPANY'          => $company,
            ':CODE_SI'          => $filters['code_si'],
            ':CODE_CG'          => $filters['code_cg'],
            ':CODE_CDC'         => $filters['code_cdc'],
            ':CODE_SCA'         => $filters['code_sca'],
            ':SUBFAMILY'        => $filters['subfamily'],
            ':CODE_ARTICLE'     => $filters['code_article'],
            ':DESIGNATION'      => $filters['designation'],
            ':CREATION_DATE'    => $filters['creation_date'],
            ':PR_NUMBER'        => $filters['pr_number'],
            ':SITE_STOCK'       => $filters['site_stock'],
        ];

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($query, $filters, $params, $this->pdo, true);

        $query .= " ORDER BY " . $orderBy;
        $query .= " OFFSET ((" . $page . " - 1) * " . $_SESSION['PaginatorGestion']['nbrArticleParPage'] . ") ROWS
        FETCH NEXT " . $_SESSION['PaginatorGestion']['nbrArticleParPage'] . " ROWS ONLY";

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

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getListeModeleCommande($post)
    {
        $utilisateur = new Utilisateur($this->pdo);
        $listeSociete = $utilisateur->rqListSocieteForUser($_SESSION["Session_Login"]);

        $listeSoc = [];
        foreach ($listeSociete as $soc) {
            $listeSoc[] = $soc['Code'];
        }

        $implode = implode("','", $listeSoc);

        $req = "select * from M_CA with (nolock) where (SOC in ('$implode') or SOC = '')
                AND DATE_DEB <= :today_0
                AND (DATE_FIN >= :today_1 OR DATE_FIN = '00000000') ";

        $arrayReq = [
            ':today_0' => date('Ymd'),
            ':today_1' => date('Ymd'),
        ];
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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

    /**
     * @param array<mixed> $post
     * @return array<mixed>|false
     */
    public function getListArticlesSiteStock($post)
    {
        $stmt = "
            SELECT
                stk.CODE_ART,
                art.DESIGN,
                stk.EMPLAC,
                art.UNITE_STK,
                CASE WHEN (stk.DTE_FERM = '00000000' OR stk.DTE_FERM > '".date('Ymd')."') THEN '' ELSE 'O' END AS FERME,
                stk.VAL_LOCAL,
                art.TYPE_VALO
            FROM
                SSTOCKLO stk WITH(nolock)
            INNER JOIN
                SARTICLE art WITH(nolock) ON art.CODE_ART = stk.CODE_ART
            WHERE
                stk.SITE = :SITE
                AND (
                    (stk.DTE_FERM = '00000000' or stk.DTE_FERM > '".date('Ymd')."')
                    OR
                    (stk.QTE_PHYS - stk.QTE_TRF - stk.QTE_RES > 0)
                )
        ";

        $query = $this->pdo->prepare($stmt);
        $query->bindValue('SITE', $post['SITE'], $this->pdo::PARAM_STR);
        $query->execute();

        return $query->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * getListeTiers
     *
     * @param  array<mixed> $post
     * @return mixed[]
     */
    public function getListeTiers(array $post = [])
    {
        $stmt = "SELECT
                    T.CODE,
                    T.RS
                 FROM
                    TIERS T WITH(nolock)
                WHERE 1 = 1
        ";


        $arrayReq = [];

        //filtre API OXA.MOBILITY
        if (isset($post['RechCodeOuLib']) && $post['RechCodeOuLib']) {
            $stmt .= ' AND (upper(CODE) LIKE :code OR  upper(RS) COLLATE SQL_Latin1_General_CP1_CI_AI LIKE :libelle)';
            $arrayReq[':code'] = mb_strtoupper('%' . $post['RechCodeOuLib'] . '%');
            $arrayReq[':libelle'] = mb_strtoupper('%' . $post['RechCodeOuLib'] . '%');
        }

        // filtre API OXA.MOBILITY
        if (isset($post['RechCode']) && $post['RechCode']) {
            $stmt .= ' AND upper(CODE) LIKE :code2';
            $arrayReq[':code2'] = mb_strtoupper('%' . $post['RechCode'] . '%');
        }

        // filtre API OXA.MOBILITY
        if (isset($post['RechLib']) && $post['RechLib']) {
            $stmt .= ' AND upper(RS) COLLATE SQL_Latin1_General_CP1_CI_AI like :libelle2';
            $arrayReq[':libelle2'] = mb_strtoupper('%' . $post['RechLib'] . '%');
        }


        //filtre API OXA.MOBILITY
        if (isset($post['ClauseWhereAdd']) && $post['ClauseWhereAdd']) {
            $stmt .= ' AND (' . $post['ClauseWhereAdd'] . ')';
        }

        if (isset($post['FiltreValid']) && $post['FiltreValid']) {
            $datejour = date('Ymd');
            $stmt .= " AND (T.DTE_OUV <= $datejour AND (T.DTE_FER >= $datejour OR T.DTE_FER = '00000000'))";
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);

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

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

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

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

    /**
     * @param array<mixed> $post
     * @return mixed[]
     */
    public function getListeDocAtt(array $post = [])
    {
        $stmt = "
            SELECT
                DOC.NO_ORDRE,
                DOC.TITRE,
                DOC.NOM_FIC,
                DOC.NOM_ORI,
                DOC.AUTEUR,
                HASTUTIL.NOM,
                DOC.DT_PARUT,
                DOC.DT_VALID,
                MEMOS.CONTMEMO
            FROM
                DOC_ATT DOC WITH(nolock)
            LEFT JOIN
                HASTUTIL WITH(nolock) ON HASTUTIL.CODE = DOC.AUTEUR
            LEFT JOIN
                MEMOS WITH(nolock) ON MEMOS.NODEMEMO = DOC.NM_COMM
            WHERE
                DOC.NO_DA = :noDa
                AND CAST(DOC.COD_ENR as int) = :codEnr
                AND DOC.COPIE = 0x54
        ";

        $params = [
            'noDa'      => $post['CodeTable'],
            'codEnr'    => $post['NumDoc'],
        ];

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


    /**
     * Liste des Bons d'entrée (BE)
        - Le code société est obligatoire
        - Les autres filtres sont facultatifs
     * @param array<mixed> $post
     * @param bool $typed
     * @return mixed[]
     */
    public function getListBE(array $post = [], $typed = false)
    {
        $addedFiltreJointure    = "";
        $addedFiltreWhere       = "";

        if (!empty($post['FiltreRech'])) {
            $addedFiltreJointure = "
                /* Lecture des lignes de chaque document */
                LEFT OUTER JOIN SBE_LIGN lig WITH(nolock) ON lig.NO_BE = ent.NO_BE
                /* Lecture de l'article de chaque ligne */
                LEFT OUTER JOIN SARTICLE art WITH(nolock) ON art.CODE_ART = lig.CODE_ART
                /* Lecture du stock local de chaque article, pour le site du document */
                LEFT OUTER JOIN SSTOCKLO stk WITH(nolock) ON stk.SITE = ent.SITE AND stk.CODE_ART = lig.CODE_ART
            ";

            $addedFiltreWhere = "
            AND (
                @v_FiltreRech = ''
                OR lower(lig.CODE_ART) like '%'+ lower(trim(@v_FiltreRech))+'%'
                OR lower(art.DESIGN) like '%'+ lower(trim(@v_FiltreRech))+'%'
                OR lower(stk.EMPLAC) like '%'+ lower(trim(@v_FiltreRech))+'%'
            )
            ";
        }

        $listType = ($typed) ? ", 'ListeBE' as LIST_TYPE" : '';

        $stmt = "
            DECLARE
            @v_CodeSoc					varchar(7)		= :CodeSoc,
            @v_FiltreSite				varchar(7)		= :FiltreSite,
            @v_FiltreMagasinier			varchar(30)		= :FiltreMagasinier, -- 'MAGASINIER' ou 'RA'
            @v_FiltreStatut				varchar(1)		= :FiltreStatut, -- 'V' ou 'N' ou ''
            @v_FiltreRech				varchar(200)	= :FiltreRech,
            @v_FiltreNumDoc				int				= :FiltreNumDoc -- 18
            ;
            
            SELECT DISTINCT
                ent.NO_BE as NUM_DOC, ent.DTE_CREAT, ent.DTE_VALID,
                ent.MAG, mag.NOM as NOM_MAG,
                ent.SITE as SITE, site.DESIGN as NOM_SITE,
                ent.ORIGINE,
                ent.PROV as TIERS,
                ent.TYPE_BE
                {$listType}
            FROM SBE_ENTE ent WITH(nolock)
            /* Lecture du site et du magasinier de chaque document */
            LEFT OUTER JOIN SSITESTO site WITH(nolock) ON site.SITE = ent.SITE
            LEFT OUTER JOIN HASTUTIL mag WITH(nolock) ON mag.CODE = ent.MAG
            {$addedFiltreJointure}
            WHERE ent.CODE_STE = @v_CodeSoc
            AND (ent.SITE = @v_FiltreSite OR @v_FiltreSite = '')
            AND (ent.MAG = @v_FiltreMagasinier OR @v_FiltreMagasinier = '')
            AND (ent.DTE_VALID <> '00000000' OR @v_FiltreStatut <> 'V')
            AND (ent.DTE_VALID = '00000000' OR @v_FiltreStatut <> 'N')
            AND (ent.NO_BE = @v_FiltreNumDoc OR @v_FiltreNumDoc = 0)
            {$addedFiltreWhere}
            ORDER BY ent.NO_BE DESC
        ";

        $params = [
            'CodeSoc'           => $post['CodeSoc'],
            'FiltreSite'        => $post['FiltreSite'],
            'FiltreMagasinier'  => $post['FiltreMagasinier'],
            'FiltreStatut'      => $post['FiltreStatut'],
            'FiltreRech'        => $post['FiltreRech'],
            'FiltreNumDoc'      => $post['FiltreNumDoc'],
        ];



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

        $stmt .= " OFFSET (( {$post['NumPage']} - 1 ) * {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ) ROWS
                   FETCH NEXT {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ROWS ONLY";

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

    /**
     * Liste des Bons de sortie (BS)
        - Le code société est obligatoire
        - Les autres filtres sont facultatifs
     * @param array<mixed> $post
     * @param bool $typed
     * @return mixed[]
     */
    public function getListBS(array $post = [], $typed = false)
    {
        $addedFiltreJointure    = "";
        $addedFiltreWhere       = "";

        if (!empty($post['FiltreRech'])) {
            $addedFiltreJointure = "
                /* Lecture des lignes de chaque document */
                LEFT OUTER JOIN SBS_LIGN lig WITH(nolock) ON lig.NO_BS = ent.NO_BS
                /* Lecture de l'article de chaque ligne */
                LEFT OUTER JOIN SARTICLE art WITH(nolock) ON art.CODE_ART = lig.ART
                /* Lecture du stock local de chaque article, pour le site du document */
                LEFT OUTER JOIN SSTOCKLO stk WITH(nolock) ON stk.SITE = ent.SITE AND stk.CODE_ART = lig.ART
            ";

            $addedFiltreWhere = "
            AND (
                @v_FiltreRech = ''
                OR lower(lig.ART) like '%'+ lower(trim(@v_FiltreRech))+'%'
                OR lower(art.DESIGN) like '%'+ lower(trim(@v_FiltreRech))+'%'
                OR lower(stk.EMPLAC) like '%'+ lower(trim(@v_FiltreRech))+'%'
            )
            ";
        }

        $listType = ($typed) ? ", 'ListeBS' as LIST_TYPE" : '';

        $stmt = "
            DECLARE
            @v_CodeSoc					varchar(7)		= :CodeSoc,
            @v_FiltreSite				varchar(7)		= :FiltreSite,
            @v_FiltreMagasinier			varchar(30)		= :FiltreMagasinier, -- 'MAGASINIER' ou 'RA'
            @v_FiltreStatut				varchar(1)		= :FiltreStatut, -- 'V' ou 'N' ou ''
            @v_FiltreRech				varchar(200)	= :FiltreRech, --'MOTeur',
            @v_FiltreNumDoc				int				= :FiltreNumDoc -- 18
            ;
            
            SELECT DISTINCT
                ent.NO_BS as NUM_DOC, ent.DTE_CREAT, ent.DTE_VALID,
                ent.MAG, mag.NOM as NOM_MAG,
                ent.SITE as SITE, site.DESIGN as NOM_SITE,
                ent.ORIGINE,
                ent.DEST as TIERS
                {$listType}
            FROM SBS_ENTE ent WITH(nolock)
            /* Lecture du site et du magasinier de chaque document */
            LEFT OUTER JOIN SSITESTO site WITH(nolock) ON site.SITE = ent.SITE
            LEFT OUTER JOIN HASTUTIL mag WITH(nolock) ON mag.CODE = ent.MAG
            {$addedFiltreJointure}
            WHERE ent.CODE_STE = @v_CodeSoc
            AND (ent.SITE = @v_FiltreSite OR @v_FiltreSite = '')
            AND (ent.MAG = @v_FiltreMagasinier OR @v_FiltreMagasinier = '')
            AND (ent.DTE_VALID <> '00000000' OR @v_FiltreStatut <> 'V')
            AND (ent.DTE_VALID = '00000000' OR @v_FiltreStatut <> 'N')
            AND (ent.NO_BS = @v_FiltreNumDoc OR @v_FiltreNumDoc = 0)
            {$addedFiltreWhere}
            ORDER BY ent.NO_BS DESC
        ";

        $params = [
            'CodeSoc'           => $post['CodeSoc'],
            'FiltreSite'        => $post['FiltreSite'],
            'FiltreMagasinier'  => $post['FiltreMagasinier'],
            'FiltreStatut'      => $post['FiltreStatut'],
            'FiltreRech'        => $post['FiltreRech'],
            'FiltreNumDoc'      => $post['FiltreNumDoc'],
        ];

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

        $stmt .= " OFFSET (( {$post['NumPage']} - 1 ) * {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ) ROWS
                   FETCH NEXT {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ROWS ONLY";

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

    /**
     * Liste des Bons de transfert (BT)
        - Le code société est obligatoire
        - Les autres filtres sont facultatifs
        - Le filtre Site s'applique sur les 2 sites (origine ou destinataire)
        - On calcule une date de validation DTE_VALID à partir des 2 dates de validation Entrée et Sortie :
        si les 2 dates sont <> de '00000000', on prends la date max, sinon c'est '00000000' (non validé)
     *
     * @param array<mixed> $post
     * @param bool $typed
     * @return mixed[]
     */
    public function getListBT(array $post = [], $typed = false)
    {
        $addedFiltreJointure = '';
        $addedFiltreWhere    = '';

        if (!empty($post['FiltreRech'])) {
            $addedFiltreJointure = "
                /* Lecture des lignes de chaque document */
                LEFT OUTER JOIN SBT_LIGN lig WITH(nolock) ON lig.NO_BT = ent.NO_BT
                /* Lecture de l'article de chaque ligne */
                LEFT OUTER JOIN SARTICLE art WITH(nolock) ON art.CODE_ART = lig.CODE_ART
                /* Lecture du stock local de chaque article, pour le site du document */
                LEFT OUTER JOIN SSTOCKLO stk WITH(nolock) ON stk.SITE = ent.SITE AND stk.CODE_ART = lig.CODE_ART  
            ";

            $addedFiltreWhere = "
            AND (
                @v_FiltreRech = ''
                OR lower(lig.CODE_ART) like '%'+ lower(trim(@v_FiltreRech))+'%'
                OR lower(art.DESIGN) like '%'+ lower(trim(@v_FiltreRech))+'%'
                OR lower(stk.EMPLAC) like '%'+ lower(trim(@v_FiltreRech))+'%'
            )
            ";
        }

        $listType = ($typed) ? ", 'ListeBT' as LIST_TYPE" : '';

        $stmt = "
            DECLARE
            @v_CodeSoc					varchar(7)		= :CodeSoc,
            @v_FiltreSite				varchar(7)		= :FiltreSite,
            @v_FiltreMagasinier			varchar(30)		= :FiltreMagasinier, -- 'MAGASINIER' ou 'RA'
            @v_FiltreStatut				varchar(1)		= :FiltreStatut, -- 'V' ou 'N' ou ''
            @v_FiltreRech				varchar(200)	= :FiltreRech, --'MOTeur',
            @v_FiltreNumDoc				int				= :FiltreNumDoc -- 18
            ;
            
            SELECT DISTINCT
                ent.NO_BT as NUM_DOC, ent.DTE_CREAT,
                CASE WHEN ent.DTE_VAL_E = '00000000' OR ent.DTE_VAL_S = '00000000'
                    THEN '00000000'
                    ELSE (
                        CASE WHEN ent.DTE_VAL_S > ent.DTE_VAL_E THEN ent.DTE_VAL_S ELSE ent.DTE_VAL_E END /*fonction max?*/
                    )
                END as DTE_VALID,
                ent.MAG, mag.NOM as NOM_MAG,
                ent.SITE as SITE, site.DESIGN as NOM_SITE,
                ent.SITE_DEST as SITE_DEST, dest.DESIGN as NOM_SITE_DEST
                {$listType}
            FROM SBT_ENTE ent WITH(nolock)
            /* Lecture du site d'origne, du site destinataire et du magasinier de chaque document */
            LEFT OUTER JOIN SSITESTO site WITH(nolock) ON site.SITE = ent.SITE
            LEFT OUTER JOIN SSITESTO dest WITH(nolock) ON dest.SITE = ent.SITE_DEST
            LEFT OUTER JOIN HASTUTIL mag WITH(nolock) ON mag.CODE = ent.MAG
            {$addedFiltreJointure}
            WHERE ent.CODE_STE = @v_CodeSoc
            AND (ent.SITE = @v_FiltreSite OR ent.SITE_DEST = @v_FiltreSite OR @v_FiltreSite = '')
            AND (ent.MAG = @v_FiltreMagasinier OR @v_FiltreMagasinier = '')
            AND ( (ent.DTE_VAL_E <> '00000000' AND ent.DTE_VAL_S <> '00000000') OR @v_FiltreStatut <> 'V')
            AND (ent.DTE_VAL_E = '00000000' OR ent.DTE_VAL_S = '00000000' OR @v_FiltreStatut <> 'N')
            AND (ent.NO_BT = @v_FiltreNumDoc OR @v_FiltreNumDoc = 0)
            {$addedFiltreWhere}
            ORDER BY ent.NO_BT DESC
        ";

        $params = [
            'CodeSoc'           => $post['CodeSoc'],
            'FiltreSite'        => $post['FiltreSite'],
            'FiltreMagasinier'  => $post['FiltreMagasinier'],
            'FiltreStatut'      => $post['FiltreStatut'],
            'FiltreRech'        => $post['FiltreRech'],
            'FiltreNumDoc'      => $post['FiltreNumDoc'],
        ];

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

        $stmt .= " OFFSET (( {$post['NumPage']} - 1 ) * {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ) ROWS
                   FETCH NEXT {$_SESSION['PaginatorGestion']['nbrArticleParPage']} ROWS ONLY";

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

    /**
     * Retourne la liste des BE/BS/BT en une seule liste
     *
     * @param array<mixed> $post
     * @return mixed[]
     */
    public function getListBEST(array $post = [])
    {
        $filtreBEJointure   = '';
        $filtreBEWhere      = '';
        $filtreBSJointure   = '';
        $filtreBSWhere      = '';
        $filtreBTJointure   = '';
        $filtreBTWhere      = '';

        if (!empty($post['FiltreRech'])) {
            $filtreBEJointure   = "
                /* Lecture des lignes de chaque document */
                LEFT OUTER JOIN SBE_LIGN lig WITH(nolock) ON lig.NO_BE = ent.NO_BE
                /* Lecture de l'article de chaque ligne */
                LEFT OUTER JOIN SARTICLE art WITH(nolock) ON art.CODE_ART = lig.CODE_ART
                /* Lecture du stock local de chaque article, pour le site du document */
                LEFT OUTER JOIN SSTOCKLO stk WITH(nolock) ON stk.SITE = ent.SITE AND stk.CODE_ART = lig.CODE_ART
            ";
            $filtreBEWhere      = "
                AND (
                    @v_FiltreRech = ''
                    OR lower(lig.CODE_ART) like '%'+ lower(trim(@v_FiltreRech))+'%'
                    OR lower(art.DESIGN) like '%'+ lower(trim(@v_FiltreRech))+'%'
                    OR lower(stk.EMPLAC) like '%'+ lower(trim(@v_FiltreRech))+'%'
                )
            ";

            $filtreBSJointure   = "
                /* Lecture des lignes de chaque document */
                LEFT OUTER JOIN SBS_LIGN lig WITH(nolock) ON lig.NO_BS = ent.NO_BS
                /* Lecture de l'article de chaque ligne */
                LEFT OUTER JOIN SARTICLE art WITH(nolock) ON art.CODE_ART = lig.ART
                /* Lecture du stock local de chaque article, pour le site du document */
                LEFT OUTER JOIN SSTOCKLO stk WITH(nolock) ON stk.SITE = ent.SITE AND stk.CODE_ART = lig.ART
            ";
            $filtreBSWhere      = "
                AND (
                    @v_FiltreRech = ''
                    OR lower(lig.ART) like '%'+ lower(trim(@v_FiltreRech))+'%'
                    OR lower(art.DESIGN) like '%'+ lower(trim(@v_FiltreRech))+'%'
                    OR lower(stk.EMPLAC) like '%'+ lower(trim(@v_FiltreRech))+'%'
                )
            ";

            $filtreBTJointure   = "
                /* Lecture des lignes de chaque document */
                LEFT OUTER JOIN SBT_LIGN lig WITH(nolock) ON lig.NO_BT = ent.NO_BT
                /* Lecture de l'article de chaque ligne */
                LEFT OUTER JOIN SARTICLE art WITH(nolock) ON art.CODE_ART = lig.CODE_ART
                /* Lecture du stock local de chaque article, pour le site du document */
                LEFT OUTER JOIN SSTOCKLO stk WITH(nolock) ON stk.SITE = ent.SITE AND stk.CODE_ART = lig.CODE_ART
            ";
            $filtreBTWhere      = "
                AND (
                    @v_FiltreRech = ''
                    OR lower(lig.CODE_ART) like '%'+ lower(trim(@v_FiltreRech))+'%'
                    OR lower(art.DESIGN) like '%'+ lower(trim(@v_FiltreRech))+'%'
                    OR lower(stk.EMPLAC) like '%'+ lower(trim(@v_FiltreRech))+'%'
                )
            ";
        }

        $stmt = "
            DECLARE
            @v_CodeSoc					varchar(7)		= :CodeSoc,
            @v_FiltreSite				varchar(7)		= :FiltreSite,
            @v_FiltreMagasinier			varchar(30)		= :FiltreMagasinier, -- 'MAGASINIER' ou 'RA'
            @v_FiltreStatut				varchar(1)		= :FiltreStatut, -- 'V' ou 'N' ou ''
            @v_FiltreRech				varchar(200)	= :FiltreRech,
            @v_FiltreNumDoc				int				= :FiltreNumDoc -- 18
            ;
            
            SELECT * FROM (
                -- Partie BE
                SELECT DISTINCT
                    ent.NO_BE as NUM_DOC, ent.DTE_CREAT, ent.DTE_VALID,
                    ent.MAG, mag.NOM as NOM_MAG,
                    ent.SITE as SITE, site.DESIGN as NOM_SITE,
                    ent.ORIGINE,
                    ent.PROV as TIERS,
                    NULL AS SITE_DEST,
                    NULL AS NOM_SITE_DEST,
                    ent.TYPE_BE,
                    'ListeBE' as LIST_TYPE
                FROM SBE_ENTE ent WITH(nolock)
                /* Lecture du site et du magasinier de chaque document */
                LEFT OUTER JOIN SSITESTO site WITH(nolock) ON site.SITE = ent.SITE
                LEFT OUTER JOIN HASTUTIL mag WITH(nolock) ON mag.CODE = ent.MAG
                {$filtreBEJointure}
                WHERE ent.CODE_STE = @v_CodeSoc
                AND (ent.SITE = @v_FiltreSite OR @v_FiltreSite = '')
                AND (ent.MAG = @v_FiltreMagasinier OR @v_FiltreMagasinier = '')
                AND (ent.DTE_VALID <> '00000000' OR @v_FiltreStatut <> 'V')
                AND (ent.DTE_VALID = '00000000' OR @v_FiltreStatut <> 'N')
                AND (ent.NO_BE = @v_FiltreNumDoc OR @v_FiltreNumDoc = 0)
                {$filtreBEWhere}
                
                UNION
                
                -- Partie BS
                SELECT DISTINCT
                    ent.NO_BS as NUM_DOC, ent.DTE_CREAT, ent.DTE_VALID,
                    ent.MAG, mag.NOM as NOM_MAG,
                    ent.SITE as SITE, site.DESIGN as NOM_SITE,
                    ent.ORIGINE,
                    ent.DEST as TIERS,
                    NULL AS SITE_DEST,
                    NULL AS NOM_SITE_DEST,
                    NULL as TYPE_BE,
                    'ListeBS' as LIST_TYPE
                FROM SBS_ENTE ent WITH(nolock)
                /* Lecture du site et du magasinier de chaque document */
                LEFT OUTER JOIN SSITESTO site WITH(nolock) ON site.SITE = ent.SITE
                LEFT OUTER JOIN HASTUTIL mag WITH(nolock) ON mag.CODE = ent.MAG
                {$filtreBSJointure}
                WHERE ent.CODE_STE = @v_CodeSoc
                AND (ent.SITE = @v_FiltreSite OR @v_FiltreSite = '')
                AND (ent.MAG = @v_FiltreMagasinier OR @v_FiltreMagasinier = '')
                AND (ent.DTE_VALID <> '00000000' OR @v_FiltreStatut <> 'V')
                AND (ent.DTE_VALID = '00000000' OR @v_FiltreStatut <> 'N')
                AND (ent.NO_BS = @v_FiltreNumDoc OR @v_FiltreNumDoc = 0)
                {$filtreBSWhere}
                
                UNION
                
                -- Partie BT
                SELECT DISTINCT
                    ent.NO_BT as NUM_DOC, ent.DTE_CREAT,
                    CASE WHEN ent.DTE_VAL_E = '00000000' OR ent.DTE_VAL_S = '00000000'
                        THEN '00000000'
                        ELSE (
                            CASE WHEN ent.DTE_VAL_S > ent.DTE_VAL_E THEN ent.DTE_VAL_S ELSE ent.DTE_VAL_E END /*fonction max?*/
                        )
                    END as DTE_VALID,
                    ent.MAG, mag.NOM as NOM_MAG,
                    ent.SITE as SITE, site.DESIGN as NOM_SITE,
                    NULL as ORIGINE,
                    NULL as TIERS,
                    ent.SITE_DEST as SITE_DEST, dest.DESIGN as NOM_SITE_DEST,
                    NULL as TYPE_BE,
                    'ListeBT' as LIST_TYPE
                FROM SBT_ENTE ent WITH(nolock)
                /* Lecture du site d'origne, du site destinataire et du magasinier de chaque document */
                LEFT OUTER JOIN SSITESTO site WITH(nolock) ON site.SITE = ent.SITE
                LEFT OUTER JOIN SSITESTO dest WITH(nolock) ON dest.SITE = ent.SITE_DEST
                LEFT OUTER JOIN HASTUTIL mag WITH(nolock) ON mag.CODE = ent.MAG
                {$filtreBTJointure}
                WHERE ent.CODE_STE = @v_CodeSoc
                AND (ent.SITE = @v_FiltreSite OR ent.SITE_DEST = @v_FiltreSite OR @v_FiltreSite = '')
                AND (ent.MAG = @v_FiltreMagasinier OR @v_FiltreMagasinier = '')
                AND ( (ent.DTE_VAL_E <> '00000000' AND ent.DTE_VAL_S <> '00000000') OR @v_FiltreStatut <> 'V')
                AND (ent.DTE_VAL_E = '00000000' OR ent.DTE_VAL_S = '00000000' OR @v_FiltreStatut <> 'N')
                AND (ent.NO_BT = @v_FiltreNumDoc OR @v_FiltreNumDoc = 0)
                {$filtreBTWhere}
            ) as DATA
            ORDER BY LIST_TYPE ASC, NUM_DOC DESC
        ";

        $params = [
            'CodeSoc'           => $post['CodeSoc'],
            'FiltreSite'        => $post['FiltreSite'],
            'FiltreMagasinier'  => $post['FiltreMagasinier'],
            'FiltreStatut'      => $post['FiltreStatut'],
            'FiltreRech'        => $post['FiltreRech'],
            'FiltreNumDoc'      => $post['FiltreNumDoc'],
        ];

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

    /**
     * @param array<mixed> $post
     * @return mixed[]|\string[][]
     * @throws \Exception
     */
    public function listeDocCat($post)
    {
        $req = "SELECT * FROM DOC_CAT WITH (nolock)";

        $arrayReq = [];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        if (count($whereArray) >  0) {
            $req .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

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