<?php

class SqlRayon
{

    public static function rqGetInfoRayon(& $code_rayon)
    {
        $sql = "SELECT * from CAT_RAYON with(nolock) WHERE CAT_RAYON.IDRAYON = '" . ReplaceSqlPointeur($code_rayon) . "' ";
        return new RequeteSQL($sql, DB_OXACATDB);
    }

    /**
     * Fonction retournant la requête sql permettant de récuperer tous les rayons
     * paramétré comme visible dans l'Oxaproc
     *
     * @return string sql
     *        
     */
    public static function rqGetListeRayon($code_rayon = '', $niveau_rayon = 0, $afficher_dans_eproc = -1, $afficher_dans_liste = -1)
    {
        $chaine_rayon = '';
        $sql = "  SELECT
		case when rtrim(ltrim(CAT_RAYON.RAYRENVOI)) != '' then CAT_RAYON.RAYRENVOI else CAT_RAYON.IDRAYON end IDRAYONCIBLE,
		CAT_RAYON.IDRAYON,
		CAT_RAYON.LIBRAYON,
		CAT_RAYON.NIVRAYON,
		CAT_RAYON.RAYPERE,
		CAT_RAYON.AFFEPROC,
		CAT_RAYON.AFFRECH,
		CAT_RAYON.ORDEAFF,
		CAT_RAYON.AUTHCLIC,
		CAT_RAYON.ACHREFERENT,
		CAT_RAYON.ACHAFFICHE,
		CAT_RAYON.RAYCOULEUR,
		CAT_RAYON.AVECCARACT,
		CAT_RAYON.RAYRENVOI,
		CAT_RAYON.CHAINEARBOR,

		case when  rtrim(ltrim(CAT_RAYON_TRAD.TXT_COMMENT)) != '' then CAT_RAYON_TRAD.TXT_COMMENT else CAT_RAYON.TXT_COMMENT end COMMENTAIRE,
		case when  rtrim(ltrim(CAT_RAYON_TRAD.LIBRAYON)) != '' then CAT_RAYON_TRAD.LIBRAYON else CAT_RAYON.LIBRAYON end LIBELLE_RAYON
		from CAT_RAYON with(nolock)
		left outer join CAT_RAYON_TRAD with(nolock) on CAT_RAYON.IDRAYON = CAT_RAYON_TRAD.IDRAYON and COD_LNG = '{$_SESSION['Session_Langue']}'
		where 1=1";
        
        if (1 == $afficher_dans_eproc)
            $sql .= ' and CAT_RAYON.AFFEPROC = 0x54 ';
        elseif (0 == $afficher_dans_eproc)
            $sql .= ' and CAT_RAYON.AFFEPROC = 0x46 ';
        
        if (1 == $afficher_dans_liste)
            $sql .= ' and CAT_RAYON.AFFRECH = 0x54 ';
        elseif (0 == $afficher_dans_liste)
            $sql .= ' and (CAT_RAYON.AFFRECH = 0x46)';
        
        if ('' != $code_rayon)
            $sql .= " and CAT_RAYON.IDRAYON = '" . ReplaceSqlPointeur($code_rayon) . "'";
        
        if (0 != $niveau_rayon)
            $sql .= " and CAT_RAYON.NIVRAYON = '" . ReplaceSqlPointeur($niveau_rayon) . "' ";
        
        if ('' != $chaine_rayon)
            $sql .= " and ($chaine_rayon) ";
        
        $sql = "SELECT tmp.*,CAT_RAYON.NIVRAYON AS NIVRAYONRENVOI from (" . $sql . ") AS tmp left outer join CAT_RAYON ON CAT_RAYON.IDRAYON = tmp.IDRAYONCIBLE " . "order by CAT_RAYON.NIVRAYON, CAT_RAYON.ORDEAFF ,CAT_RAYON.LIBRAYON ";
        
        return new RequeteSQL($sql, DB_OXACATDB);
    }

    public static function regGetListeRayonRenvoi(& $rayon)
    {
        $sql = 'SELECT a.RAYRENVOI,b.NIVRAYON ' . 'from RAYON ori with(nolock) ,RAYON a with(nolock)  ' . 'left outer join RAYON b with(nolock) on b.IDRAYON=a.RAYRENVOI ' . "where ori.IDRAYON = '" . ReplaceSqlPointeur($rayon) . "' and   a.RAYRENVOI<>'' and a.CHAINEARBOR LIKE ori.CHAINEARBOR+';%' ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête sql permettant de récuperer tous les sous-rayons associés à un rayon de niveau 1
     * paramétré comme visible dans l'Oxaproc
     *
     * @return string sql
     */
    public static function rqGetListeSousRayonByRayon($rayon_premier_niveau = '')
    {
        $sql = "  select
		case when rtrim(ltrim(CAT_RAYON.RAYRENVOI)) != '' then CAT_RAYON.RAYRENVOI else CAT_RAYON.IDRAYON end  AS IDRAYONCIBLE,
		CAT_RAYON.IDRAYON ,
		CAT_RAYON.LIBRAYON,CAT_RAYON.NIVRAYON,CAT_RAYON.RAYPERE,CAT_RAYON.AFFEPROC,CAT_RAYON.AFFRECH,CAT_RAYON.ORDEAFF,
		CAT_RAYON.AUTHCLIC, CAT_RAYON.ACHREFERENT, CAT_RAYON.ACHAFFICHE,
		CAT_RAYON.RAYCOULEUR, CAT_RAYON.AVECCARACT, CAT_RAYON.RAYRENVOI, CAT_RAYON.CHAINEARBOR,
		case when  rtrim(ltrim(CAT_RAYON_TRAD.TXT_COMMENT)) != '' then CAT_RAYON_TRAD.TXT_COMMENT else CAT_RAYON.TXT_COMMENT end COMMENTAIRE,
		case when  rtrim(ltrim(CAT_RAYON_TRAD.LIBRAYON)) != '' then CAT_RAYON_TRAD.LIBRAYON else CAT_RAYON.LIBRAYON end LIBELLE_RAYON
		from CAT_RAYON with(nolock)
		left outer join CAT_RAYON_TRAD with(nolock) on CAT_RAYON.IDRAYON = CAT_RAYON_TRAD.IDRAYON and COD_LNG = '{$_SESSION['Session_Langue']}'
		where CAT_RAYON.AFFEPROC = 0x54
		and (CAT_RAYON.CHAINEARBOR like '" . ReplaceSqlPointeur($rayon_premier_niveau) . ";%' or CAT_RAYON.CHAINEARBOR = '" . ReplaceSqlPointeur($rayon_premier_niveau) . "')
		and CAT_RAYON.NIVRAYON > 1 ";
        
        $sql = "select tmp.*,CAT_RAYON.NIVRAYON AS NIVRAYONRENVOI from (" . $sql . ") AS tmp left outer join CAT_RAYON ON CAT_RAYON.IDRAYON = tmp.IDRAYONCIBLE " . "order by CAT_RAYON.NIVRAYON, CAT_RAYON.ORDEAFF , CAT_RAYON.LIBRAYON ";
        
        return new RequeteSQL($sql, DB_OXACATDB);
    }
}