<?php

/**
 * Fonction retournant la requête determinant le nombre de commandes à noter pour un utilisateur donné
 *
 * @param  string $login : Login de l'utilisateur dont on calcule le nombre de commande à noter
 * @return string        : Requête SQL
 */
class SqlNotation
{

    public static function rqGetNbCdeToRate($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  = '" . ReplaceSqlPointeur($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     = '" . ReplaceSqlPointeur($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 = '" . ReplaceSqlPointeur($login) . "'
									and CA_ENTET.NO_CDE  = CDE_NOT.NO_CDE
									and THEMES.COD_THEM  = CDE_THEM.COD_THEM
									and THEMES.TH_DEM    = 0x54
							) t";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête determinant les commandes à noter pour un utilisateur donné
     *
     * @param string $login
     *            : Login de l'utilisateur dont on calcule le nombre de commande à noter
     * @return string : Requête SQL
     */
    public static function rqGetCdeToRate($login)
    {
        $sql = "select distinct(NO_CDE)
					from (
						select distinct(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  = '" . ReplaceSqlPointeur($login) . "'
							and THEMES.COD_THEM = CDE_THEM.COD_THEM
							and THEMES.TH_AUT = 0x54
						union
						select distinct(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 = '" . ReplaceSqlPointeur($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 distinct(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   = '" . ReplaceSqlPointeur($login) . "'
							and CA_ENTET.NO_CDE = CDE_NOT.NO_CDE
							and THEMES.COD_THEM = CDE_THEM.COD_THEM
							and THEMES.TH_DEM = 0x54
					) t";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête listant pour un utilisateur donné l'ensemble des thèmes à noter
     *
     * @param boolean $no_cde
     *            : Numéro de la commande
     * @param string $login
     *            : Login de l'utilisateur dont on calcule le nombre de commande à noter
     * @param string $limite
     *            : "all" => On prend en compte les thèmes obligatoires et non obligatoires
     *            "obl" => On prend en compte les thèmes obligatoires
     *            "nobl" => On prend en compte les thèmes non obligatoires
     * @return string $sql
     */
    public static function rqGetNbNotationCdeToRate($no_cde, $login, $limite = 'all', $code_theme = '')
    {
        $sql = " 	select		COUNT(CRITERES.COD_CRIT) NB_CRITERES
						from		CRITERES with(nolock)
						where		
									CRITERES.COD_THEM in (
										select	CDE_THEM.COD_THEM 
										from	CDE_THEM with(nolock), CDE_NOT with(nolock)
										where	
												CDE_NOT.NO_CDE  	= CDE_THEM.NO_CDE
												and CDE_THEM.NO_CDE = '" . ReplaceSqlPointeur($no_cde) . "'
												and DTEVALID    	= '00000000')
									and (
										exists( select null from THEMEUSR with(nolock) where THEMEUSR.COD_THEM  = CRITERES.COD_THEM  and THEMEUSR.COD_UTIL  = '" . ReplaceSqlPointeur($login) . "' and (select THEMES.TH_AUT from THEMES with(nolock) where THEMES.COD_THEM = CRITERES.COD_THEM) = 0x54)
										or
										exists( select null from CA_ENTET with(nolock) where CA_ENTET.ACH		 = '" . ReplaceSqlPointeur($login) . "' and CA_ENTET.NO_CDE = '" . ReplaceSqlPointeur($no_cde) . "' and (select THEMES.TH_ACHET from THEMES with(nolock) where THEMES.COD_THEM = CRITERES.COD_THEM) = 0x54)
										or
										exists( select null from CA_ENTET with(nolock) where CA_ENTET.DEMANDEUR = '" . ReplaceSqlPointeur($login) . "' and CA_ENTET.NO_CDE = '" . ReplaceSqlPointeur($no_cde) . "' and (select THEMES.TH_DEM   from THEMES with(nolock) where THEMES.COD_THEM = CRITERES.COD_THEM) = 0x54)
									)";
        
        if ('nobl' == $limite)
            $sql .= ' and CRITERES.OBLIGAT = 0x46 ';
        elseif ('obl' == $limite)
            $sql .= ' and CRITERES.OBLIGAT = 0x54 ';
        if ('' != $code_theme)
            $sql .= " and CRITERES.COD_THEM = '" . ReplaceSqlPointeur($code_theme) . "' ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête indiquant si tous les thèmes obligatoires requis sur une notation sont renseignés
     *
     * @param int $no_cde
     *            : Numéro de la commande
     * @return string $sql
     */
    public static function rqCanNotationCdeBeValidate($no_cde)
    {
        $sql = "select  count(*) NB_CRITRE_OBL_VIDE	from CRITERES with(nolock)
				where	COD_THEM in (
					select CDE__CRIT.COD_THEM from CDE__CRIT with(nolock) where CDE__CRIT.COD_CRIT = CRITERES.COD_CRIT and CDE__CRIT.NO_CDE = '" . ReplaceSqlPointeur($no_cde) . "' and (CDE__CRIT.NOTLIB = '' or isnull(CDE__CRIT.NOTLIB,'') = '')
				) and CRITERES.OBLIGAT = 0x54";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête indiquant si la notation d'une commande donné a été validée
     *
     * @param int $no_cde
     *            : Numéro de la commande
     * @return string $sql
     */
    public static function rqIsNotationCdeValidated($no_cde)
    {
        $sql = 'select DTEVALID from CDE_NOT with(nolock) where NO_CDE = ' . ReplaceSqlPointeur($no_cde);
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête permettant de lister les thèmes à noter sur une commande donnée
     *
     * @param int $no_cde
     *            : Numéro de la commande pour laquelle on recherche les thèmes à noter
     * @return string $sql
     */
    public static function rqGetListThemesNotationByCde($no_cde)
    {
        $sql = " select CDE_THEM.COD_THEM, THEMES.LIBELLE, CDE_THEM.NOTMOYTH from CDE_THEM with(nolock),  THEMES with(nolock) where CDE_THEM.COD_THEM = THEMES.COD_THEM and CDE_THEM.NO_CDE = '" . ReplaceSqlPointeur($no_cde) . "' ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête permettant de lister les critère à noter pour un thème donné
     *
     * @param int $code_theme
     *            : Code du theme sur lequel on recherche les critères à noter
     * @return string $sql
     */
    public static function rqGetListCriteresByThemeNotation($code_theme)
    {
        $sql = " select * from CRITERES with(nolock)  where COD_THEM = '" . ReplaceSqlPointeur($code_theme) . "'  ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête permettant de lister les notations possibles associées à un thème et un critère donné
     *
     * @param int $code_theme
     *            : Code du theme
     * @param int $code_critere
     *            : Code du critère
     * @return string $sql
     */
    public static function rqGetListNotationByThemeNotationAndCritere($code_theme, $code_critere)
    {
        $sql = " select * from GRILLES with(nolock) where COD_THEM = '" . ReplaceSqlPointeur($code_theme) . "' and COD_CRIT = '" . ReplaceSqlPointeur($code_critere) . "' ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête permettant de récupérer la notation choisie et le commentaire associé pour une commande, un thème et un critère donnés
     * 
     * @param int $no_cde
     *            : Numéro de la commande
     * @param int $code_theme
     *            : Code du theme
     * @param int $code_critere
     *            : Code du critère
     * @return string $sql
     */
    public static function rqGetValueSelectedNotationForCdeByThemeAndCritere($no_cde, $code_theme, $code_critere)
    {
        $sql = " select * from CDE__CRIT with(nolock) left outer join MEMOS with(nolock) on CDE__CRIT.NMCOMMENT = MEMOS.NODEMEMO 
					 where CDE__CRIT.NO_CDE = '" . ReplaceSqlPointeur($no_cde) . "' and CDE__CRIT.COD_THEM = '" . ReplaceSqlPointeur($code_theme) . "' and CDE__CRIT.COD_CRIT = '" . ReplaceSqlPointeur($code_critere) . "' ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
}