<?php

namespace Modules\GlobalApplication\Repository;

use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;

class Utilisateur extends AbsRepository
{
    
    /**
     * getNbCdeToPrint
     * @param string $demandeur
     * @return string
     */
    public function getNbCdeToPrint(string $demandeur) : string
    {
        $result = Sql::paravrac($this->pdo, 'OXAPROC', 'TYPDOCUMENT');
        
        
        $filtreTYP_DOC = Roles::droitsUtilisateur($this->pdo, 'HA_CA_IMP')? '' : " and TYP_DOC = '" . $result. "' ";
        
        if (true) {
            $clauseSelect = ' count(*) as TOTAL ';
            $clauseOrder = '';
        } else {
            $clauseSelect = ' CA_ENTET.* ';
            $clauseOrder = ' order by CA_ENTET.NO_CDE desc ';
        }
        
        $sql = "select $clauseSelect
	                from CA_ENTET with (nolock)
	                where
						(
							CA_ENTET.DEMANDEUR 		='" . $demandeur . "'
							OR (CA_ENTET.AVENANT<>'' and '" . $demandeur . "' = (select UTIL from CA_EVENT with (nolock) where CA_EVENT.NO_CDE=CA_ENTET.NO_CDE and CA_EVENT.NUM_ORD = CA_ENTET.EVE_VAL))
						)
	                and (CA_ENTET.ETAT_CDE		='V'	or ( CA_ENTET.ETAT_CDE 		='Z' and CA_ENTET.EVE_IMP 		=0))
	                and CA_ENTET.SIGNEE			= 0x54
	                and CA_ENTET.REFUSEE		= 0x46
	                $filtreTYP_DOC
					$clauseOrder";
        $nbr = $this->pdo->prepare($sql);
        $nbr->execute([]);
        return $nbr->fetch()['TOTAL'];
    }

    
    // phpcs:ignore
    public function ReplaceSqlPointeur(&$utf8str)
    {
        $result = str_replace('\'', '\'\'', $utf8str);
        return $result;
    }
    
    /**
     * getNbCdeToRate
     * @param string $login
     * @return int
     */
    public function getNbCdeToRate(string $login) :int
    {
        $login = $this->ReplaceSqlPointeur($login);
        
        $sql = "select 
                    COUNT(distinct(CDE_NOT.NO_CDE)) NB_CDE_TO_RATE
                from CDE_NOT with (nolock)
                left join CDE_THEM with (nolock) on CDE_NOT.NO_CDE	 = CDE_THEM.NO_CDE  
                left join THEMEUSR with (nolock) on THEMEUSR.COD_THEM  = CDE_THEM.COD_THEM 
                left join THEMES with (nolock) on THEMES.COD_THEM = CDE_THEM.COD_THEM
                left join CA_ENTET with (nolock) on CA_ENTET.NO_CDE = CDE_NOT.NO_CDE
                where CDE_NOT.DTEVALID = '00000000' and THEMES.TH_AUT = 0x54
                and (THEMEUSR.COD_UTIL  = '$login' or CA_ENTET.ACH = '$login' or CA_ENTET.DEMANDEUR   = '$login')";

        /*$sql = "select count(distinct(NO_CDE)) NB_CDE_TO_RATE
                            from (
                                select CDE_NOT.NO_CDE
                                from    CDE_NOT with (nolock), CDE_THEM with (nolock), THEMEUSR with (nolock), THEMES with (nolock)
                                where CDE_NOT.NO_CDE       = CDE_THEM.NO_CDE
                                    and CDE_NOT.DTEVALID   = '00000000'
                                    and THEMEUSR.COD_THEM  = CDE_THEM.COD_THEM
                                    and THEMEUSR.COD_UTIL  = '$login'
                                    and THEMES.COD_THEM    = CDE_THEM.COD_THEM
                                    and THEMES.TH_AUT      = 0x54
                                union
                                select CDE_NOT.NO_CDE
                                from    CDE_NOT with (nolock), CDE_THEM with (nolock), CA_ENTET with (nolock), THEMES with (nolock)
                                where CDE_NOT.NO_CDE     = CDE_THEM.NO_CDE
                                    and CDE_NOT.DTEVALID = '00000000'
                                    and CA_ENTET.ACH     = '$login'
                                    and CA_ENTET.NO_CDE  = CDE_NOT.NO_CDE
                                    and THEMES.COD_THEM  = CDE_THEM.COD_THEM
                                    and THEMES.TH_ACHET  = 0x54
                                union
                                select CDE_NOT.NO_CDE
                                from CDE_NOT with (nolock), CDE_THEM with (nolock), CA_ENTET with (nolock), THEMES with (nolock)
                                where CDE_NOT.NO_CDE     = CDE_THEM.NO_CDE
                                    and CDE_NOT.DTEVALID = '00000000'
                                    and CA_ENTET.DEMANDEUR = '$login'
                                    and CA_ENTET.NO_CDE  = CDE_NOT.NO_CDE
                                    and THEMES.COD_THEM  = CDE_THEM.COD_THEM
                                    and THEMES.TH_DEM    = 0x54
                            ) t";*/
        
        $nbr = $this->pdo->prepare($sql);
        $nbr->execute([]);
        return $nbr->fetch()['NB_CDE_TO_RATE'];
    }
    
    
    public function totalNoDaToSign($Signataire)
    {
        
        if ($Signataire == $_SESSION['Session_Login']) {
            $typeSuivi = 'SIGDA';
        } else {
            $typeSuivi = 'DELSIGDA';
        }
        $tabinfo = array(
            'SIGNAT' => $Signataire
        );
        
//         SuiviRecherche::SearchDA(false, $tabinfo, $typeSuivi)->TrouveTous()
    }


    /**
     * rqSqlGetUOUSERReq
     * @param string $SsTable
     * @param string $str_UO
     * @return string
     */
    public static function rqSqlGetUOUSERReq(string $SsTable, string &$str_UO) : string
    {
  
        if ($str_UO == '') {
            $str_UO = '\'___\''; // EDP ATTENTION BIDOUILLE A CHANGER
        }
            
            $sql = '' . $SsTable . '(CODE_UO) as
							(
							select CODE_UO from UO with (nolock) where CODE_UO in (' . $str_UO . ')
							union all
							select nplus1.CODE_UO from UO nplus1 with (nolock), ' . $SsTable . ' where ' . $SsTable . '.CODE_UO = nplus1.UO_MERE
							)
						 ';

            return $sql;
    }
    
    public function getNbAOAEnvoyer()
    {
        $resultat = 0;
        
        $tab['GrpSoc'] = 'soc';
        if (! ($_SESSION['Session_UsrMultiSoc'] || $_SESSION['Session_UsrPluriSoc'])) {
            $tab['Societe'] = $_SESSION['Session_SocUsr'];
        }
        $tab['Dem_Ach'] = $_SESSION['Session_Login'];
        $tab['Statut'] = 'ENVO';
        
        $suivi = new \Modules\GlobalApplication\Repository\Suivi($this->pdo);
        
        $reqSql = $suivi->searchAOPanier($tab);
        if (is_array($reqSql)) {
            $resultat = count($reqSql);
        }
            
        return $resultat;
    }
    
    /**
     * rqInfoUserByLogin
     * @param string $login
     * @return array<string>
     */
    public function rqInfoUserByLogin(string &$login) : ?array
    {
        // TODO : use prepare ?
        $sql = "select * from oxa_users with (nolock) where login = upper(:user) ";

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

        $info = $this->pdo->prepare($sql);
        $info->execute($arrayReq);
        return $info->fetch();
    }
    
    /**
     * rqListSocieteForUser
     * @param string $codeUtilisateur
     * @param string $code
     * @param string $libelle
     * @param bool $testerValiditeSociete
     * @param bool $strict
     * @return array<mixed>
     */
    public function rqListSocieteForUser(string $codeUtilisateur, string $code = '', string $libelle = '', bool $testerValiditeSociete = true, bool $strict = false) : ?array
    {
        $sql = 'select distinct(CODE_STE) as Code, RAIS_SOC as Libelle from SOCIETE with (nolock) where 1=1 ';
        $sqlDate = '';
        if ($testerValiditeSociete) {
            $sql .= ' and (DTEVALMAX >= \''.date('Ymd').'\' OR DTEVALMAX = 0) and (DTEVALMIN <= \''.date('Ymd').'\' OR DTEVALMIN = 0) ';
        }
        if ($code != '') {
            if ($strict) {
                $sql .= " and upper(CODE_STE) = upper('" . $this->ReplaceSqlPointeur($code) . "')";
            } else {
                $sql .= " and upper(CODE_STE) LIKE upper('%" . $this->$this->ReplaceSqlPointeur($code) . "%')";
            }
        }
        
        if ($libelle != '') {
            $sql .= " and upper(RAIS_SOC) LIKE upper('%" . $this->ReplaceSqlPointeur($libelle) . "%')";
        }
            $sql .= " and CODE_STE in ( select SOC_USR.COD_STE from SOC_USR with(nolock), HASTUTIL with(nolock)  where  (" . (($codeUtilisateur != 'OXAUSER' && $codeUtilisateur != 'ADMIN') ? " SOC_USR.COD_USR 	= upper('" . $this->ReplaceSqlPointeur($codeUtilisateur) . "') and " : '') . "HASTUTIL.CODE   = SOC_USR.COD_USR and HASTUTIL.UTIMULTI 	    = 0x46 )
				UNION 	select distinct(SOCIETE.CODE_STE) from SOCIETE with(nolock), HASTUTIL with(nolock)
						where  HASTUTIL.UTIMULTI 	    = 0x54 " . (($codeUtilisateur != 'OXAUSER' && $codeUtilisateur != 'ADMIN') ? "and HASTUTIL.CODE = upper('" . $this->ReplaceSqlPointeur($codeUtilisateur) . "') " : '') . " UNION	select HASTUTIL.CODE_STE from HASTUTIL  with(nolock) " . (($codeUtilisateur != 'OXAUSER' && $codeUtilisateur != 'ADMIN') ? "where HASTUTIL.CODE = upper('" . $this->ReplaceSqlPointeur($codeUtilisateur) . "')" : '') . ')';
            $sql .= ' order by RAIS_SOC ';
           
            return $this->fetchAssocAll($sql, []);
    }
    
    
    /**
     * getNbCampagneANoter
     * @return string
     */
    public function getNbCampagneANoter() : string
    {
        
        $sql = 'select count(*) as COMPTEUR from ( ' . 'select CAMP_ENT.ID_CAMP from CAMP_REP_QST with (nolock), CAMP_ENT with (nolock) ' . 'where CAMP_REP_QST.COD_NOTEUR = \'' . $this->ReplaceSqlPointeur($_SESSION['Session_Login']) . '\' ' . 'and   CAMP_REP_QST.TERMINE = 0x46 ' . 'AND   CAMP_ENT.ID_CAMP = CAMP_REP_QST.ID_CAMP ' . 'AND   CAMP_ENT.STATUT = \'E\' ' . 'group by CAMP_ENT.ID_CAMP ' . ') as tmp';
        $info = $this->pdo->prepare($sql);
        $info->execute([]);
        return $info->fetch()['COMPTEUR'];
    }
    
    /**
     * rqGetDOAPourTDBOperationnel
     * @return array<mixed>
     */
    public function rqGetDOAPourTDBOperationnel() : ?array
    {
        
        
        $sql = 'select act.*,tac.LIB LIB_tache' . ',tac.DATE_DEB LIB_date_deb ' . ',tac.DATE_FIN LIB_date_fin ' . ',tac.DELAI_PREAVIS ' . ',d.LIB LIB_doa ' . 'from DOA_ACTIONS_RACI raci with(nolock) ' . 'inner join DOA_ACTIONS act with(nolock) on act.ID_OBJET=raci.ID_DOA and act.ID_TACHE=raci.ID_TACHE and act.ID_ACTION=raci.ID_ACTION and act.TYPE_OBJET = \'D\' ' . 'inner join DOA_TACHES tac with(nolock) on tac.ID_OBJET=raci.ID_DOA and tac.ID_TACHE=raci.ID_TACHE and tac.TYPE_OBJET = \'D\' ' . 'inner join DOA d with(nolock) on d.MODELE=0x46 and d.ID=raci.ID_DOA ' . 'where d.STATUT=\'E\' and act.TERMINEE=0x46 and tac.TERMINEE=0x46 and tac.DATE_DEB<=\''.date('Ymd').'\' ' . 'and tac.DATE_DEB>\'00000000\' and tac.DATE_FIN>\'00000000\' ' . 'and raci.RACI IN (\'R\',\'A\',\'C\') ' . 'and raci.CODE_USER=\'' . $this->ReplaceSqlPointeur($_SESSION['Session_Login']) . '\' ' . 'order by tac.DATE_FIN';
        
        return $this->fetchAssocAll($sql, []);
    }
    
    /**
     * rqGetListUserMultiSoc
     * @param string $codeUser
     * @param bool $onlyAcheteur
     * @return array<mixed>
     */
    public function rqGetListUserMultiSoc(string $codeUser, bool $onlyAcheteur = false) : ?array
    {
        $societes = $this->rqListSocieteForUser($codeUser);
        $sqlSte = '';
        
        foreach ($societes as $soc) {
            if ($sqlSte !== '') {
                $sqlSte .= ',';
            }
            
            $sqlSte .= "'".$soc['Code']."'";
        }
        
        $req = "select * from HASTUTIL with(nolock) where (CODE_STE IN (" . $sqlSte . ") OR HASTUTIL.UTIMULTI = 0x54) ";
        
        if ($onlyAcheteur == true) {
            $req .=" and ACHETEUR = 0x54";
        }
        
        $req .= "order by NOM";
        
        return $this->fetchAssocAll($req, []);
    }

    /**
     * getAllAcheteur
     * @return array<mixed>
     */
    public function getAllAcheteur() : ?array
    {
        $req = "select NOM, EMAIL,COD_LNG  from HASTUTIL with(nolock) where ACHETEUR = 0x54";
        
        return $this->fetchAssocAll($req, []);
    }

    public function countPanierBrouillerTransferer():string
    {
        $req = "SELECT COUNT(*) nb 
        FROM oxa_users_panier WITH (NOLOCK) 
        WHERE id_user = :idUser 
                AND NO_DEM <> :login 
                AND id_panier != '0' 
                and flg_panier_modele = 0";

        $arrayReq = [
            ':idUser' => $_SESSION['id_user'],
            ':login' => $_SESSION["Session_Login"]
        ];

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