<?php

namespace Modules\Index\Repository;

use Helpers\Outil\Sql;
use Helpers\Trad\Trad;
use Helpers\QueryBuilder\AbsRepository;
use Entity\Model\ParamDico;

class Connexion extends AbsRepository
{

    /**
     * mailExist
     * @param string $mail
     * @return array<mixed>
     */
    public function mailExist(string $mail): ?array
    {
        $req = "select EMAIL from HASTUTIL with (NOLOCK) where upper(EMAIL) = (:email)";

        $arrayReq = [':email' => $mail];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * userExist
     * @param string $user
     * @param bool $upper
     * @return array<mixed>
     */
    public function userExist(string $user, bool $upper = false): ?array
    {
        if ($upper) {
            $req = "select * from HASTUTIL with (NOLOCK) where upper(CODE) = upper(:user)";
        } else {
            $req = "select * from HASTUTIL with (NOLOCK) where CODE = :user";
        }

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

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * reqGetVersion
     * @return string
     */
    public function reqGetVersion(): string
    {
        $result1 = $this->getNumVers();
        $result2 = $this->getLib01parv();
        return sprintf('%.2f', $result1['NUM_VERS']) . $result2['LIB01PARV'];
    }

    /**
     * getLib01parv
     * @return array<string>
     */
    public function getLib01parv(): ?array
    {
        $req = "SELECT LIB01PARV from PARAVRAC with (nolock) where TYPEPARV ='PARAMGEN' AND CODEPARV='RELMIN'";

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

        $requete->execute();
        return $requete->fetch();
    }

    /**
     * getNumVers
     * @return array<string>
     */
    public function getNumVers(): ?array
    {
        $req = "SELECT NUM_VERS from VERSION with (nolock) where CLE='1'";

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

        $requete->execute();
        return $requete->fetch();
    }

    /**
     * insertConnexionUser
     * @param string $login
     * @param string $langue
     * @param string $societe
     * @return void
     */
    public function insertConnexionUser(string $login, string $langue, string $societe): void
    {
        /*
         * vue avec YA
         * Les sessions en cas de deconnexion ne sont pas regénéré, si un user ce deco/reco alors la tracelogin est créer avec le même id session
         * ce meme id session qui est envoyer a SRM, YA a conseillé de supprimer (si il est présent), le record TRACELOGIN/session_id()
         */
        $paramDico = new ParamDico($this->pdo);
        $paramDico->delete(['TYPEDICO' => 'TRACELOGIN', 'ID1' => session_id()]);
        
        
        $date_jour = date('Ymd'); // Date sous la format aaaammjj
        $heure_jour = date("His"); // Date sous la format hhmmss

        $req = "insert into PARAMDICO (TYPEDICO, ID1, ID2, ID3, ID4, LIBELLE) values ('TRACELOGIN',:session, :login ,:langue_societe,'{$date_jour}|{$heure_jour}','OXAPROC' )";

        $arrayReq = [
        ':session' => session_id(),
        ':login' => $login,
        ':langue_societe' => $langue . '|' . $societe,
        ];

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

        $requete->execute($arrayReq);
    }

    /**
     * infoOxaUser
     * @param string $login
     * @return array<string>
     */
    public function infoOxaUser(string $login): array
    {
        $req = "select * from oxa_users with (nolock) where login = :login";

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

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * updateOxaUser
     * @param string $login
     * @param array<string> $result
     * @return void
     */
    public function updateOxaUser(string $login, array $result): void
    {
        $result['ExpirationMdP'] = sql::boolSql($result['ExpirationMdP']);
        $result['NbLignePagination'] = sql::boolSql($result['NbLignePagination']);
        $result['AuthLigneManuelle'] = sql::boolSql($result['AuthLigneManuelle']);
        $result['GestionMotdePasse'] = sql::boolSql($result['GestionMotdePasse']);

        $req = 'update oxa_users set ExpirationMdP = ' . $result['ExpirationMdP'] . ', CodeCtrLivraison  = :CodeCtrLivraison, NbLignePagination = ' . $result['NbLignePagination'] . ', AuthLigneManuelle = ' . $result['AuthLigneManuelle'] . ', GestionMotdePasse = ' . $result['GestionMotdePasse'] . ', NomUser = :NomUser where  login  = :login';

        $arrayReq = [
        ':CodeCtrLivraison' => $result['CodeCtrLivraison'],
        ':NomUser' => $result['NomUser'],
        ':login' => $login,
        ];

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

        $requete->execute($arrayReq);
    }

    /**
     * insertOxaUser
     * @param string $login
     * @param array<string> $result
     * @return void
     */
    public function insertOxaUser(string $login, array $result): void
    {

        $result['ExpirationMdP'] = sql::boolSql($result['ExpirationMdP']);
        $result['NbLignePagination'] = sql::boolSql($result['NbLignePagination']);
        $result['AuthLigneManuelle'] = sql::boolSql($result['AuthLigneManuelle']);
        $result['GestionMotdePasse'] = sql::boolSql($result['GestionMotdePasse']);

        $req = 'insert into oxa_users (login,ExpirationMdP , CodeCtrLivraison , NbLignePagination , AuthLigneManuelle , GestionMotdePasse , NomUser ) 
        values (:login, ' . $result['ExpirationMdP'] . ', :CodeCtrLivraison, ' . $result['NbLignePagination'] . ', ' . $result['AuthLigneManuelle'] . ', ' . $result['GestionMotdePasse'] . ', :NomUser)';

        $arrayReq = [
        ':CodeCtrLivraison' => $result['CodeCtrLivraison'],
        ':NomUser' => $result['NomUser'],
        ':login' => $login,
        ];

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

        $requete->execute($arrayReq);
    }

    /**
     * rqTrouveContexteDisposition
     * @param string $COD_TYPE
     * @param string $COD_CHAMP
     * @return array<string>
     */
    public function rqTrouveContexteDisposition(string $COD_TYPE, string $COD_CHAMP): ?array
    {
        $req = 'select VALEUR from OXA_USERS_CNT with (nolock) where COD_USR   = :login and   COD_TYPE  = :codeType and   COD_CHAMP = :codeChamp';

        $arrayReq = [
        ':login' => $_SESSION['Session_Login'],
        ':codeType' => $COD_TYPE,
        ':codeChamp' => $COD_CHAMP,
        ];

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * rqInsertContexteDisposition
     * @param string $COD_TYPE
     * @param string $COD_CHAMP
     * @param string $VALEUR
     * @return Void
     */
    public function rqInsertContexteDisposition(string $COD_TYPE, string $COD_CHAMP, string $VALEUR): void
    {
        $req = 'insert INTO OXA_USERS_CNT (COD_USR,COD_TYPE,COD_CHAMP,VALEUR) VALUES (:login,:codeType,:codeChamp, :valeur)';

        $arrayReq = [
        ':login' => $_SESSION['Session_Login'],
        ':codeType' => $COD_TYPE,
        ':codeChamp' => $COD_CHAMP,
        ':valeur' => $VALEUR,
        ];

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

        $requete->execute($arrayReq);
    }

    /**
     * /!\ This function does not apply for an User multisoc (HASTUTIL.UTIMULTI = true)
     * @return string[] list of CODE_STE
     */
    public function listeSocForUser(string $user): ?array
    {
        // Case : Demo/Admin -> grant access to all
        if (!in_array($user, ['OXAUSER','ADMIN'])) {
            return array_column(
                $this->fetchAssocAll(
                    'SELECT SOCIETE.CODE_STE
                    FROM SOCIETE with(nolock)',
                    []
                ),
                'CODE_STE'
            );
        }

        return array_column(
            $this->fetchAssocAll(
                '(
                    SELECT SOC_USR.COD_STE 
                    FROM SOC_USR with(nolock), HASTUTIL with(nolock)  
                    WHERE SOC_USR.COD_USR = :user_0
                        AND HASTUTIL.CODE = SOC_USR.COD_USR 
                        AND HASTUTIL.UTIMULTI = 0x46
                ) UNION (
                    SELECT HASTUTIL.CODE_STE 
                    FROM HASTUTIL with(nolock)
                    WHERE HASTUTIL.CODE = :user_1
                )',
                [
                'user_0' => $user,
                'user_1' => $user,
                ]
            ),
            'CODE_STE'
        );
    }

    
    // TODO : move somewher ein UserSession or a class alike
    /**
     * @return string[] list of CODE_STE
     */
    public function listeSocForUserCurrent() :array
    {
        // Failsafe
        if (! isset($_SESSION['Session_Login'])) {
            throw new \Exception('!!!');
        }
        // heat up cache
        if (! isset($_SESSION['UserSession_listUserSoc'])) {
            $_SESSION['UserSession_listUserSoc'] =
            $this->listeSocForUser($_SESSION['Session_Login']);
        }
        return $_SESSION['UserSession_listUserSoc'];
    }

    /**
     * @return array<mixed>[]
     */
    public function getparamdicoMATOMO(): array
    {
        $req = "SELECT * FROM PARAMDICO with (nolock) where TYPEDICO='MATOMO'";
        return $this->fetchAssocAll($req, []);
    }

    /**
     * getInfoUser
     * @param string $login
     * @return array<string>
     */
    public function getInfoUser(string $login): ?array
    {
        $req = "select HASTUTIL.*, DILISPEC.LIB01DICO as SIGNAT_DA_FAV from HASTUTIL with (nolock)
                left outer join DILISPEC with (nolock) on CODEDICO = HASTUTIL.CODE and DILISPEC.TYPEDICO = 'ETDESDAFAV'  
                where HASTUTIL.CODE = upper(:login)";

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

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getValueSessionByUser
     * @param string $login
     * @return array<mixed>
     */
    public function getValueSessionByUser(string $login): ?array
    {

        $isSISecto  = Sql::paravrac($this->pdo, 'PARAMSTE', 'SISECTOR') == 'O' ? true : false;
        $isSCASecto = Sql::paravrac($this->pdo, 'PARAMSTE', 'SCASECTO') == 'O' ? true : false;
        $isCDCSecto = Sql::paravrac($this->pdo, 'PARAMSTE', 'CDCSECTO') == 'O' ? true : false;

        $req = "select
			HASTUTIL.CODE ,
			HASTUTIL.CODE_STE		SOCIETE,
			HASTUTIL.CTR_ADR		CTR_EMET,
			HASTUTIL.CTRLIV			CTR_LIV,
			HASTUTIL.SI				SI,
			HASTUTIL.CDC			CDC,
			HASTUTIL.SCA			SCA,
			HASTUTIL.SIT_STO		SITE_STK,
			SOCIETE.RAIS_SOC		SOCIETE_LIB,
			SECTIMPU.LIBELLE		SI_LIB,
			SSCPTANA.LIBELLE		SCA_LIB,
			CODECOMP.LIBELLE		CDC_LIB,
			CENTRE_LIVR.RAIS_SOC	CTR_LIV_RS,
			CENTRE_EMET.RAIS_SOC	CTR_EMET_RS,
			SSITESTO.DESIGN			SITE_STK_RS
			from HASTUTIL with(nolock)
			left outer join SOCIETE   	with(nolock) on SOCIETE.CODE_STE	= HASTUTIL.CODE_STE
			left outer join SECTIMPU 		with(nolock) on SECTIMPU.CODE_SI	= HASTUTIL.SI   AND SECTIMPU.CODE_STE = " . ($isSISecto ? " HASTUTIL.CODE_STE " : "'' ") . "
			left outer join SSCPTANA 		with(nolock) on SSCPTANA.CODE_SCA	= HASTUTIL.SCA  AND SSCPTANA.CODE_STE = " . ($isSCASecto ? " HASTUTIL.CODE_STE " : "'' ") . "
			left outer join CODECOMP 		with(nolock) on CODECOMP.CODE_CDC	= HASTUTIL.CDC  AND CODECOMP.CODE_STE = " . ($isCDCSecto ? " HASTUTIL.CODE_STE " : "'' ") . "
			left outer join CENTRE CENTRE_LIVR  with(nolock) on	 CENTRE_LIVR.CODE_CTR	= HASTUTIL.CTRLIV
			left outer join CENTRE CENTRE_EMET  with(nolock) on  CENTRE_EMET.CODE_CTR	= HASTUTIL.CTR_ADR
			left outer join SSITESTO with(nolock) on  SSITESTO.SITE	= HASTUTIL.SIT_STO
			where HASTUTIL.CODE = upper(:login)";

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

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getValueSessionByUserAndSte
     * @param string $login
     * @param string $societe
     * @return array<string>
     */
    public function getValueSessionByUserAndSte(string $login, ?string $societe = ''): ?array
    {

        $isSISecto  = Sql::paravrac($this->pdo, 'PARAMSTE', 'SISECTOR') == 'O' ? true : false;
        $isSCASecto = Sql::paravrac($this->pdo, 'PARAMSTE', 'SCASECTO') == 'O' ? true : false;
        $isCDCSecto = Sql::paravrac($this->pdo, 'PARAMSTE', 'CDCSECTO') == 'O' ? true : false;

        $req = " select
	                    COD_USR ,
	                    COD_STE					SOCIETE,
	                    CTREMET					CTR_EMET,
	                    CTRLIVR					CTR_LIV,
	                    CODSI					SI,
	                    CODCDC					CDC,
	                    CODSCA					SCA,
	                    CODSIT					SITE_STK,
	                    SOCIETE.RAIS_SOC		SOCIETE_LIB,
	                    SECTIMPU.LIBELLE		SI_LIB,
	                    SSCPTANA.LIBELLE		SCA_LIB,
	                    CODECOMP.LIBELLE		CDC_LIB,
	                    CENTRE_LIVR.RAIS_SOC	CTR_LIV_RS,
	                    CENTRE_EMET.RAIS_SOC	CTR_EMET_RS,
	                    SSITESTO.DESIGN			SITE_STK_RS
	                    from SOC_USR with(nolock)
	                    left outer join SOCIETE   	with(nolock) on SOCIETE.CODE_STE	= SOC_USR.COD_STE
	                    left outer join SECTIMPU 		with(nolock) on SECTIMPU.CODE_SI	= SOC_USR.CODSI   AND SECTIMPU.CODE_STE = '" . ($isSISecto ? ($societe) : '') . "'
	                    left outer join SSCPTANA 		with(nolock) on SSCPTANA.CODE_SCA	= SOC_USR.CODSCA  AND SSCPTANA.CODE_STE = '" . ($isSCASecto ? ($societe) : '') . "'
	                    left outer join CODECOMP 		with(nolock) on CODECOMP.CODE_CDC	= SOC_USR.CODCDC  AND CODECOMP.CODE_STE = '" . ($isCDCSecto ? ($societe) : '') . "'
	                    left outer join CENTRE CENTRE_LIVR  with(nolock) on	 CENTRE_LIVR.CODE_CTR	= SOC_USR.CTRLIVR
	                    left outer join CENTRE CENTRE_EMET  with(nolock) on  CENTRE_EMET.CODE_CTR	= SOC_USR.CTREMET
	                    left outer join SSITESTO with(nolock) on  SSITESTO.SITE	= SOC_USR.CODSIT
	                    where SOC_USR.COD_USR = upper(:login)";

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

        if ('' != $societe) {
            $req .= " and SOC_USR.COD_STE = :societe";

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

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getInfoSte
     * @param string $societe
     * @return array<string>
     */
    public function getInfoSte($societe)
    {
        $req = "SELECT * from SOCIETE with (nolock) where CODE_STE=:societe";
        $arrayReq = [':societe' => $societe];

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getInfoPannier
     * @param Int $id_panier
     * @param string $id_user
     * @return array<mixed>
     */
    public function getInfoPannier(Int $id_panier, string $id_user): ?array
    {
        $req = 'select id_user,id_panier,id_rowid_panier,lib_panier,flg_panier_modele,NO_CDE,CODE_FM,COD_DEV,mnt_budget,NO_DA,CHPSPECCLT,IS_INITIALIZED,NO_DEM,TOTAL_DA,BUDGET_DA,CODE_STE,DEVISE,SI_CODE,CDC_CODE,ORIGINE,SCA_CODE,CG_CODE,CHIF_EXEC,CHIF_EXEC_LOCK,LIV_CTR,MOD_TRS,LIV_ADR,LIV_PAYS,LIV_CP,LIV_VILLE,CTC_TEL,CTC_FCT,CTC_NOM,CTC_FAX,CTC_MAIL,CTC_GSM,CAST(ZONE_LIBRE AS TEXT)  AS ZONE_LIBRE,CAST(COMMENT_INT AS TEXT) AS COMMENT_INT,CAST(TITRE_DA AS TEXT) AS TITRE_DA, OUPA_CENTRE_EMETTEUR AS CENTRE_EMETTEUR  from oxa_users_panier with (nolock) ' . "WHERE oxa_users_panier.id_user     = '" . $id_user . "'   " . "AND   oxa_users_panier.id_panier   = '" . $id_panier . "' ";

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

        $requete->execute();
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * nbLignePanier
     */
    public function nbLignePanier($id_panier, $id_user)
    {
        if ($id_panier != '') {
            $req = "select COUNT(*) TOTAL from oxa_users_panier_ligne with (nolock) WHERE oxa_users_panier_ligne.id_user   = '" . $id_user . "'  AND   oxa_users_panier_ligne.id_panier = '" . $id_panier . "' ";

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

            $requete->execute();
            return $requete->fetch($this->pdo::FETCH_ASSOC)['TOTAL'];
        } else {
            return 0;
        }
    }

    /**
     * infoCentreLivraison
     * @param mixed $centre
     * @return mixed
     */
    public function infoCentreLivraison($centre)
    {
        $req = "select * from CENTRE with (nolock) WHERE CODE_CTR=:centre and LIVRAISON = 0x54 ";

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

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getContactCentre
     * @param string $centre
     * @return array<mixed>
     */
    public function getContactCentre(string $centre): ?array
    {
        $req = "select CTC_CTR.* from CTC_CTR with (nolock) where COD_CTR = '" . $centre . "' and CTC_IND = (select min(CTC_IND)  from  CTC_CTR with (nolock) where COD_CTR = '" . $centre . "' and CTC_RCA = 0x54)";

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

        $requete->execute();
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }


    /**
     * SelectSIbySte
     * @param string|null $ste
     * @param string $cod_rech
     * @return array<mixed>
     */
    // phpcs:ignore
    public function SelectSIbySte($ste, string $cod_rech = ''): ?array
    {

        $isSCASecto = Sql::paravrac($this->pdo, 'PARAMSTE', 'SCASECTO') == 'O' ? true : false;

        $sql = "select distinct CODE_SI as Code, LIBELLE as Libelle from SECTIMPU with (nolock) where 1 = 1 ";

        if ('' != $ste && $isSCASecto) {
            $sql .= "and CODE_STE='" . ($ste) . "' ";
        } else {
            $sql .= "and CODE_STE='' ";
        }

        /*if ($mode_exact)
        {*/
        $sql .= " and CODE_SI = '" . ($cod_rech) . '\'';
        /*}
        else
        {
            if ($cod_rech != '')
            {
                $sql .= " and upper(CODE_SI) LIKE '%" . strtoupper(($cod_rech)) . "%'";
            }
            if ($lib_rech != '')
            {
                $sql .= " and upper(LIBELLE) LIKE '%" . strtoupper(($lib_rech)) . "%'";
            }
        }*/

        if (sql::parametr($this->pdo, 'CMAX_SIDA') == 'B') {
            $sql .= ' and (DATE_FIN =00000000 or DATE_FIN >= \''.date('Ymd').'\')';
        }
        if (sql::parametr($this->pdo, 'CMIN_SIDA') == 'B') {
            $sql .= ' and (DATE_DEB =00000000 or DATE_DEB <= \''.date('Ymd').'\')';
        }

        $sql .= 'order by CODE_SI';

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

        $requete->execute();
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getNumFormatDate
     * @param string $langue
     * @return array<string>
     */
    public function getNumFormatDate(string $langue): array
    {
        $req = " SELECT
					(select TYPDTEPROC from LANGUES with(nolock) where COD_LNG = :langue)  LANGUEDEF,
					LIB01PARV
				FROM  PARAVRAC with(nolock)
				WHERE TYPEPARV='OXAPROC' and CODEPARV='TYPE_DATE_DEF' ";

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

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * isPublicationGenerique
     * @return array<string>
     */
    public function isPublicationGenerique(): ?array
    {
        $req = "select PARAVAL from PROXACAT with(nolock) where TYPEIDX = 'PUBSFAG'";

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

        $requete->execute();
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getUOUser
     * @param string $user
     * @return string
     */
    public function getUOUser(string $user): string
    {

        $req = "SELECT CODE_UO FROM HASTUTIL with (nolock) where CODE = upper(:login)";

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

        $arrayReq = [':login' => $user];

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC)['CODE_UO'] ?? '';
    }

    /**
     * getUserOxaproc
     * @param string $login
     * @return array<string>
     */
    public function getUserOxaproc(string $login): array
    {
        $req = "select * from oxa_users with (nolock) where login = :login";
        $arrayReq = [':login' => $login];

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

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array<string> $buf_user
     * @return array<mixed>[]
     */
    public function existePanierUser(array $buf_user): array
    {
        $req = 'select * from oxa_users_panier with (nolock) '
            . "where oxa_users_panier.id_user = {$buf_user['id_user']} "
            . 'and   oxa_users_panier.id_panier = 0';
        return $this->fetchAssocAll($req, []);
    }

    /**
     * insertPanierUser
     */
    public function insertPanierUser(string $userId, string $label): void
    {
        $req = "insert into oxa_users_panier (id_user,id_panier,lib_panier) 
        values ({$userId},0,'" . $label . "')";

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

        $requete->execute();
    }

    /**
     * fonction permettant de savoir si on ne doit pas proposer la saisie d'un commentaire lors de la signature d'une DA
     * return 'O' si on ne doit pas
     */
    /**
     * signatureDANoComment
     * @return string
     */
    public function signatureDANoComment()
    {
        $req = "select LIB01PARV from PARAVRAC with(nolock) where TYPEPARV = :TYPEPARV and CODEPARV = :CODEPARV";
        $arrayReq = [
            ':TYPEPARV' => 'PARAMSIG',
            ':CODEPARV' => 'NOCOMMENTSIGDA'
        ];
        $requete = $this->pdo->prepare($req);

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC)['LIB01PARV'] ?? '';
    }

    /**
     * fonction qui précise dans quels cas on doit proposer le commentaire lors de la signature du BAP
     * retourne blanc, 'O' ou 'E'
     */
    /**
     * signatureBAPCommentMode
     * @return string
     */
    public function signatureBAPCommentMode()
    {
        $req = "select LIB01PARV from PARAVRAC with(nolock) where TYPEPARV = :TYPEPARV and CODEPARV = :CODEPARV";
        $arrayReq = [
            ':TYPEPARV' => 'PARAMSIG',
            ':CODEPARV' => 'BAP_SIGN_COMMENTAIRE'
        ];
        $requete = $this->pdo->prepare($req);

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC)['LIB01PARV'] ?? '';
    }

    /**
     * fonction qui vérifie si la saisie (lorsqu'elle est proposée) du Commentaire est obligatoire lors de la signature d'un BAP.
     * retourne 'O' si obligatoire
     */
    /**
     * signatureBAPCommentOblig
     * @return string
     */
    public function signatureBAPCommentOblig()
    {
        $req = "SELECT LIB01PARV 
                    FROM PARAVRAC WITH (nolock) 
                    WHERE TYPEPARV = :TYPEPARV 
                    AND CODEPARV = :CODEPARV";

        $arrayReq = [
            ':TYPEPARV' => 'PARAMSIG',
            ':CODEPARV' => 'BAP_SIGN_COMMENTAIRE_OBLIG'
        ];
        $requete = $this->pdo->prepare($req);

        $requete->execute($arrayReq);
        return $requete->fetch($this->pdo::FETCH_ASSOC)['LIB01PARV'] ?? '';
    }

    /**
     * rqSignInDelegationV10
     * @return array<mixed>
     */
    public function rqSignInDelegationV10(): ?array
    {
        $req = "select count(SIG_DELEG.SIGNAT) count
		from SIG_DELEG with (nolock),HASTUTIL with (nolock)
		where SIGNAT='{$_SESSION['Session_Login']}' AND  HASTUTIL.CODE = SIG_DELEG.DELEG";
        return $this->fetchAssocAll($req, []);
    }

    /**
     * Fonction permettant de tester si un utilisateur a délégué sa signature
     */
    /**
     * rqDelegationEnCoursV10
     * @return array<mixed>
     */
    public function rqDelegationEnCoursV10() :array
    {
        $req = "select count(SIG_DELEG.SIGNAT) count
		from SIG_DELEG with (nolock),HASTUTIL with (nolock)
		where SIGNAT='{$_SESSION['Session_Login']}' AND  HASTUTIL.CODE = SIG_DELEG.DELEG
		AND '".date('Ymd')."' >= DTE_DEB
		AND '".date('Ymd')."' <= DTE_FIN OR DTE_FIN = '00000000' ";
        return $this->fetchAssocOne($req, []);
    }

    /**
     * countDemandeCompte
     * @param Object $pdo
     * @param string $login
     * @param string $mode
     * @return array<mixed>
     */
    public static function countDemandeCompte(Object $pdo, string $login, string $mode)
    {
        $req = "select COUNT(*) TOTAL
			from   DILISPEC with (nolock), UTILCREAT with (nolock), SOCIETE with (nolock)
			where 	TYPEDICO  = 'WRKFL_CMUS'
			and 	DILISPEC.CODEDICO  = UTILCREAT.CODE
			and 	CREATMOD   = :login								 
			and 	LIB01DICO = :mode
			and    UTILCREAT.CODE_STE = SOCIETE.CODE_STE";

        $arrayReq = [
            ':login' => $login,
            ':mode' => $mode,
        ];

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

        $requete->execute($arrayReq);
        return $requete->fetch($pdo::FETCH_ASSOC)['TOTAL'];
    }

    /**
     * etatStatPresent
     * @param \PDO $pdo
     * @return array<mixed>
     */
    public static function etatStatPresent(\PDO $pdo): ?array
    {
        $req = 'select LISTETAT.TYPERES,isnull(LISTETAT.SOURCE, \'-\') as SOURCE, LISTETAT.CODETAT, LISTETAT.DESCRIPT, LISTETAT.NOMFIC, isnull(ETATETTH.CODTHEM, \'-\') as CODTHEM, ETATTHEM.LIBELLE from  (
					select distinct ETGRPET.CODETAT, ETATSTAT.DESCRIPT, ETATSTAT.SOURCE, ETATSTAT.NOMFIC, ETATSTAT.TYPERES TYPERES
						from ETGRPET with(nolock), ETGRPUSR with(nolock), ETATSTAT with (nolock)
						where ETGRPET.CODGRP = ETGRPUSR.CODGRP and ETGRPET.CODETAT = ETATSTAT.CODETAT and ETGRPUSR.CODUSR = \'' . $_SESSION['Session_Login'] . '\'
							 and ETATSTAT.TYPEREPORT != \'Structure\'
					union
					select ETATUSER.CODETAT, ETATSTAT.DESCRIPT, ETATSTAT.SOURCE, ETATSTAT.NOMFIC, ETATSTAT.TYPERES TYPERES
						from ETATUSER with (nolock), ETATSTAT with (nolock)
						where ETATUSER.CODETAT = ETATSTAT.CODETAT and ETATSTAT.TYPEREPORT != \'Structure\'  and ETATUSER.CODUSR = \'' . $_SESSION['Session_Login'] . '\'
				) LISTETAT  left join ETATETTH with (nolock) on ETATETTH.CODETAT = LISTETAT.CODETAT
				left join ETATTHEM with (nolock) on ETATETTH.CODTHEM = ETATTHEM.CODTHEM
				order by ETATETTH.CODTHEM, LISTETAT.CODETAT';

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

        $requete->execute();

        $return = $requete->fetchAll($pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * rqGetNumCSLieuDALigne
     * @return array<string>
     */
    public function rqGetNumCSLieuDALigne(): ?array
    {
        $sql = "select CHP_DESTI NUM_CS  from CSPARINI with(nolock) where CSPARINI.TAB_DESTI = 'DA_LIGNE' and CSPARINI.TAB_SOUR = 'CODECOMP' and CSPARINI.CHP_SOUR = (select LIB01DICO NUM_CS from DILISPEC with(nolock) where TYPEDICO ='PARAMCC' and CODEDICO = 'NCS_CODECOMP_LIEUANA_VAL')	";

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

        $requete->execute();
        return $requete->fetch();
    }

    /**
     * rqGetNumCSProjetDALigne
     * @return array<string>
     */
    public function rqGetNumCSProjetDALigne(): ?array
    {
        $sql = "select CHP_DESTI NUM_CS  from CSPARINI with(nolock) where CSPARINI.TAB_DESTI = 'DA_LIGNE' and CSPARINI.TAB_SOUR = 'CODECOMP' and CSPARINI.CHP_SOUR = (select LIB01DICO NUM_CS from DILISPEC with(nolock) where TYPEDICO ='PARAMCC' and CODEDICO = 'NCS_CODECOMP_PROJET_VAL')";

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

        $requete->execute();
        return $requete->fetch();
    }

    /**
     * getNbrLisrtePanierModeleBrouillon
     * @param int $numFlague
     * @return array<mixed>
     */
    public function getNbrLisrtePanierModeleBrouillon(int $numFlague = 1): ?array
    {
        $req = " SELECT oxa_users_panier.*,  
        (
            select count(oxa_users_panier_ligne.id_lig)  
            from oxa_users_panier_ligne with (nolock) 
            where oxa_users_panier_ligne.id_panier = oxa_users_panier.id_panier 
            and oxa_users_panier_ligne.id_user = oxa_users_panier.id_user
        ) NB_ARTICLES  
        from oxa_users_panier with(nolock)
        where  oxa_users_panier.id_user = :nuUser  and  oxa_users_panier.id_panier > 0
        and    flg_panier_modele =  :numFlague 
        order by  lib_panier";

        $arrayReq = [
            ':numFlague' => $numFlague,
            ':nuUser' => $_SESSION['id_user'],
        ];
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * rqLangues
     * @param OBject $pdo
     * @return array<mixed>
     */
    public static function rqLangues(Object $pdo, $codeLangue = null): ?array
    {

        if ($codeLangue) {
            $sql = 'select * from LANGUES with (nolock) where COD_LNG = \'' . $codeLangue . '\' order by LIBELLE';
        } else {
            $sql = 'select * from LANGUES with (nolock) order by LIBELLE';
        }

        $requete = $pdo->prepare($sql);

        $requete->execute();
        return $requete->fetch();
    }

    /**
     * trackConnexionIn
     * @param string $login
     * @return void
     */
    public function trackConnexionIn(string $login): void
    {
        $id1_obj = $login;
        $id2_obj = mb_substr((string)session_id(), 0, 40);

        $req = ' insert into OXALOG (USER_HA, DAT_LOG, HEU_LOG, APPLI , OPE, TYP_OBJ, ID1_OBJ, ID2_OBJ) ' . "values (:login, '" . date('Ymd') . "', substring(convert(char(8),getdate(),108),1,2) + substring(convert(char(8),getdate(),108),4,2) + substring(convert(char(8),getdate(),108),7,2), 'Oxaproc', 'LOGIN', 'USER', :id1_obj, :id2_obj);";

        $arrayReq = [
            ':login' => $login,
            ':id1_obj' => $id1_obj,
            ':id2_obj' => $id2_obj
        ];

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

    public function trackConnexionOut(string $login): void
    {
        $id1_obj = $login;
        $id2_obj = mb_substr((string)session_id(), 0, 40);

        $req = ' insert into OXALOG (USER_HA, DAT_LOG, HEU_LOG, APPLI , OPE, TYP_OBJ, ID1_OBJ, ID2_OBJ) ' . "values (:login, '" . date('Ymd') . "', substring(convert(char(8),getdate(),108),1,2) + substring(convert(char(8),getdate(),108),4,2) + substring(convert(char(8),getdate(),108),7,2), 'Oxaproc', 'LOGOUT', 'USER', :id1_obj, :id2_obj);";

        $arrayReq = [
            ':login' => $login,
            ':id1_obj' => $id1_obj,
            ':id2_obj' => $id2_obj
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    /**
     * myIP
     * @return string
     */
    public function myIP()
    {
        if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
            $ip = $_SERVER['HTTP_CLIENT_IP'];
        } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
            $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
        } else {
            $ip = $_SERVER['REMOTE_ADDR'];
        }
        return $ip;
    }

    /**
     * ipIsBan
     * @param string $ip
     * @return array<mixed>
     */
    public function ipIsBan(string $ip, $pdoStorage): array
    {
        $returnStorage = [];

        $sql1 = " 
        {CALL dbo.STD_UTI_FAIL2BAN(  
            0, 
            ?, 
            0, 
            ?, 
            ?, 
            ?, 
            ?)}
        ";
        $date = date("Y-m-d H:i:s");
        $ip = $this->myIP();
        $ok = '0';
        $liberee = '0';
        $impose = '0';
        $params = array(
            array($date, SQLSRV_PARAM_IN),
            array($ip, SQLSRV_PARAM_IN),
            array(&$ok, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT), //$count must already be initialised
            array(&$liberee, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT), //$count must already be initialised
            array(&$impose, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT) //$count must already be initialised
        );
        $stmt = sqlsrv_query($pdoStorage, $sql1, $params);

        $returnStorage['OK'] = $ok;
        $returnStorage['captcha'] = $impose;
        $returnStorage['liberee'] = $liberee;

        return $returnStorage;
    }

    /**
     * getSiteStock
     *
     * @param  string $deliveryCenterCode
     * @param  string $user
     * @param  string $company
     * @return array<mixed>
     */
    public function getSiteStock(string $deliveryCenterCode, string $user, string $company)
    {
        if ($deliveryCenterCode == '') {
            return [
                'CODSIT'    => '',
                'DESIGN'    => ''
            ];
        } else {
            $query = "
            DECLARE 
                @delivery_center    VARCHAR(7) = '', 
                @stock_site         VARCHAR(7) = ''

            SET @delivery_center = :CTRLIVR
            -- on stocke dans une variable le CODSIT lié à l'utilisateur et sa société dans la table SOC_USR
            SET @stock_site = (SELECT ISNULL(CODSIT, '') FROM SOC_USR with(nolock) WHERE COD_USR = :USER AND COD_STE = :COMPANY)

            -- on renvoie cette valeur si elle n'est pas nulle, sinon le site de stock lié au centre de livraison de l'utilisateur
            SELECT @stock_site =  
                CASE 
                    WHEN @stock_site <> ''
                    THEN @stock_site
                    ELSE (SELECT SITSTOCK FROM CENTRE with(nolock) WHERE CODE_CTR = @delivery_center)
                END 
                
            SELECT @stock_site AS CODSIT, DESIGN 
            FROM SSITESTO with(nolock)
            WHERE SITE = @stock_site ";

            $query = $this->pdo->prepare($query);
            $query->bindParam(':CTRLIVR', $deliveryCenterCode, \PDO::PARAM_STR);
            $query->bindParam(':USER', $user, \PDO::PARAM_STR);
            $query->bindParam(':COMPANY', $company, \PDO::PARAM_STR);

            $query->execute();

            $result = $query->fetch(\PDO::FETCH_ASSOC);
            return $result;
        }
    }
}
