<?php

class SqlEtat
{

    /**
     * rqSelectAllEtatByUser : retourne les etats d'un utilisateur
     *
     * @param $login :
     *            login d'un utilisaeur
     * @return les etats d'un utilisateur
     */
    public static function rqSelectAllEtatByUser(& $login)
    {
        $sql = "select * from ETATUSER with (nolock) where CODUSR = '" . ReplaceSqlPointeur($login) . "'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * rqSelectAllEtatGroupeByUser : retourne les etats d'un utilisateur lié à groupe d'état
     *
     * @param $login :
     *            login d'un utilisaeur
     * @return les etats liés à un groupe dont l'utilisateur est associé
     */
    public static function rqSelectAllEtatGroupeByUser(& $login)
    {
        $sql = "select * from ETGRPUSR with (nolock)  
				left outer join ETGRPET with (nolock) on ETGRPET.CODGRP = ETGRPUSR.CODGRP 
				where CODUSR = '" . ReplaceSqlPointeur($login) . "'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * rqSelectOneInfoByEtat : retourne les informations liées à un état
     *
     * @param $etat :
     *            etat a partir duquel on va recupérer les informations
     * @return les infos () liés à un état
     */
    public static function rqSelectOneInfoByEtat(& $etat)
    {
        $sql = "select CODETAT, DESCRIPT, NOMFIC, ETATSTAT.CODTHEM, LIBELLE 
				from ETATSTAT with (nolock)  
				left outer join ETATTHEM with (nolock) on ETATTHEM.CODTHEM = ETATSTAT.CODTHEM
				where CODETAT = '" . ReplaceSqlPointeur($etat) . "'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * rqSelectAllTheme : retourne toutesles infos concernant les themes
     *
     * @return les infos liés aux etats
     */
    public static function rqSelectAllTheme()
    {
        $sql = 'select * from ETATTHEM with (nolock)';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqExistUserLOGISES()
    {
        $sql = "select * from LOGISES where LOGIN = '" . $_SESSION['Session_Login'] . "'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqInsertUserLOGISES()
    {
        $sql = "insert into LOGISES (LOGIN, SOC, SI, CDC, SCA, CE, CL, SITE_STK, STYLE, LANGUE)" . "values " . "('" . $_SESSION['Session_Login'] . "','" . $_SESSION['Session_SocUsr'] . "','" . $_SESSION['Session_SectionImputation'] . "','" . $_SESSION['Session_CDC'] . "','" . $_SESSION['Session_SCA'] . "','" . $_SESSION['Session_CodeCtrLivraison'] . "','" . $_SESSION['Session_CodeCtrEmetteur'] . "','" . $_SESSION['SiteStock'] . "','" . $_SESSION['SESSION_SKIN_CODE'] . "','" . $_SESSION['Session_Langue'] . "')";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqUpdatetUserLOGISES()
    {
        $sql = " update LOGISES set 
				SOC = '" . $_SESSION['Session_SocUsr'] . "',
				SI = '" . $_SESSION['Session_SectionImputation'] . "',
				CDC = '" . $_SESSION['Session_CDC'] . "',
				SCA = '" . $_SESSION['Session_SCA'] . "',
				CE = '" . $_SESSION['Session_CodeCtrLivraison'] . "',
				CL = '" . $_SESSION['Session_CodeCtrEmetteur'] . "',
				SITE_STK = '" . $_SESSION['SiteStock'] . "',
				STYLE = '" . $_SESSION['SESSION_SKIN_CODE'] . "',
				LANGUE ='" . $_SESSION['Session_Langue'] . "'
			WHERE LOGIN = '" . $_SESSION['Session_Login'] . "'";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    // Spec 3581
    public static function rqGetListeEtatsHighChart()
    {
        $sql = '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';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    // Spec 3581
    public static function rqGetListePeriodes()
    {
        $sql = 'select * from PERIODES order by LIBELLE desc';
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    // Spec 3581
    public static function rqGetInfoEtat(& $codeEtat)
    {
        $sql = 'select * from ETATSTAT where CODETAT=\'' . ReplaceSqlPointeur($codeEtat) . '\'';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    // Mail YA 20160421
    public static function rqGeFiltresEtat(& $codeEtat)
    {
        $sql = 'select * from REQ_FILTRES where REQ_ETAT=\'' . ReplaceSqlPointeur($codeEtat) . '\'';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    // Mail YA 20161010
    public static function rqGetListeACDPourEtats()
    {
        $sql = 'select a.CODE_ACD,a.LIBELLE, a.TYPE_FG, case when a.TYPE_FG=\'G\' then g.RS else f.RS end as RS from ACCCADRE a with(nolock)
				left outer join FOURNISS f with(nolock) on f.CODE=a.CODE_FOUR
				left outer join GRPFOU g with(nolock)  on g.CODE=a.CODE_FOUR
				order by a.LIBELLE, case when a.TYPE_FG=\'G\' then g.RS else f.RS end';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getNbrIncident($numCde,$nomTable = false)
{
    if($nomTable == false)
    {
        $nomTable = 'NO_CDE';
    }
    $sql="SELECT count(*) as countNbr FROM FOURNINC with (nolock) LEFT JOIN INCIDENT with (nolock) on INCIDENT.COD_INC = FOURNINC.CODE LEFT JOIN CATINCID with (nolock) on CATINCID.CODE_CAT = INCIDENT.CODE_CAT LEFT JOIN MEMOS with (nolock) ON MEMOS.NODEMEMO = FOURNINC.NM_COM WHERE " .$nomTable."='".ReplaceSqlPointeur($numCde)."'";
    return new RequeteSQL($sql, DB_OXASQLDB);
}

}	
	