<?php

class SqlCampEve {
    
    public static function rqGetInfoSuiviCampEnt() {
        $sql = 'select * from CAMP_ENT with(nolock) ';
        
        $vueWhere = CampEve::getVueUser();
        if ($vueWhere!='') $sql .= 'WHERE '.$vueWhere;
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * Liste des grille catégorie, ATTENTION cette information sera en liaison avec CAMP_ENT.ID_GRILLE
     * @return RequeteSQL
     */
    public static function rqGetGrilCate(){
        $sql = 'select ID_GRILLE AS CODE,LIB_GRILLE AS LIBELLE from GRIL_CATE with(nolock) ORDER BY LIB_GRILLE';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * Liste des periodes
     * @return RequeteSQL
     */
    public static function rqGetPeriode(){
        $sql = 'select CODE_PER AS CODE, LIBELLE from PERIODES with(nolock) ORDER BY LIBELLE';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * Recupere toutes les informations de la table des entetes de campagne ansi que les liens avec periodes et type de campagne (grille) et le nom du createur
     * @param unknown $idCamp
     * @return RequeteSQL
     */
    public static function rqGetCampagneEnt($idCamp){
        $sql = 'select CAMP_ENT.*,PERIODES.LIBELLE AS LIB_PERIODE, GRIL_CATE.LIB_GRILLE,HASTUTIL.NOM AS NOM_CREAT from CAMP_ENT with(nolock) '.
            'left outer join PERIODES  with (nolock) on PERIODES.CODE_PER = CAMP_ENT.COD_PER '.
            'left outer join GRIL_CATE with (nolock) on GRIL_CATE.ID_GRILLE = CAMP_ENT.ID_GRILLE '.
            'left outer join HASTUTIL with (nolock) on HASTUTIL.CODE = CAMP_ENT.CREEEPAR '.
            'WHERE CAMP_ENT.ID_CAMP = \''.ReplaceSqlPointeur($idCamp).'\'';
            
            return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * recupere la liste des fournisseurs affecté à une campagne
     *
     * @param unknown $idCamp
     * @return RequeteSQL
     */
    public static function rqGetCampagneFournisseur(& $idCamp, $typeRegle){
        
        if ($typeRegle==0){ // Restriction de part le suivi du document
            // Si preparation alors tous sinon juste celui du connect (noteur)
            $reqCamp = SqlCampEve::rqGetCampagneEnt($idCamp)->trouve1();
            
            if ($reqCamp['STATUT']==CampEve::STATUT_INITIAL){
                $addsql = '';
            }
            else{
                $addsql = 'AND (EXISTS (select null from CAMP_REP_QST with (nolock) '.
                    'WHERE CAMP_REP_QST.ID_CAMP = CAMP_LST_FOUR.ID_CAMP '.
                    'AND   CAMP_REP_QST.CODE_FOU = CAMP_LST_FOUR.CODE_FOU '.
                    'AND   CAMP_REP_QST.COD_NOTEUR = \''.ReplaceSqlPointeur($_SESSION['Session_Login']).'\' '.
                    ')';
                
                $addsql .= ($reqCamp['CREEEPAR'] == $_SESSION['Session_Login'] ? ' OR EXISTS (select null from CAMP_QST_Q with (nolock) '.
                    'WHERE CAMP_QST_Q.ID_CAMP = CAMP_LST_FOUR.ID_CAMP '.
                    'AND CAMP_QST_Q.OUV_NOTEPAR = \'D\' ))' : ')');
            }
        }
        else
            $addsql = "";
            
            
            $sql = 'select CAMP_LST_FOUR.*,FOURNISS.RS from CAMP_LST_FOUR with (nolock) '.
                'left outer join FOURNISS with (nolock) ON FOURNISS.CODE = CAMP_LST_FOUR.CODE_FOU '.
                'WHERE CAMP_LST_FOUR.ID_CAMP = \''.ReplaceSqlPointeur($idCamp).'\' '.
                $addsql;
                return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * Fonction retourne l'ensemble des themes d'une camapgne pour un id de parent donnée
     * les informations de la branche principal ont un parent valant rien
     *
     * @param unknown $idCamp
     * @param unknown $strPere
     * @return RequeteSQL
     */
    public static function rgGetListeThemeCampPere($idCamp, $strPere,$strIn=null){
        $sql = 'SELECT CAMP_QST_TH.*,CAMP_QST_TH.DESCRIPTION MEMO_THEME 
        FROM CAMP_QST_TH WITH (NOLOCK) 
        WHERE CAMP_QST_TH.ID_CAMP = \''.ReplaceSqlPointeur($idCamp).'\' 
        AND CAMP_QST_TH.ID_PERE =  \''.ReplaceSqlPointeur($strPere).'\' '.
        (is_null($strIn) ? '' : 'and CAMP_QST_TH.ID_THEME IN ('.$strIn.') ').
        ' order by CAMP_QST_TH.NM_ORDRE';
            
            return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * Fonction permettant d'obtenir toutes les questions relatif à un theme d'une campagne
     */
    public static function rqGetQuestionThemeCamp($idCamp, $idTheme = null, $idQuest = null){
        $sql = 'select CAMP_QST_Q.*,CAMP_QST_Q.DESCRIPTION COMENT, CAMP_QST_Q.ID_QUEST from CAMP_QST_Q with (nolock) '.
            'where CAMP_QST_Q.ID_CAMP = \''.ReplaceSqlPointeur($idCamp).'\' '.
            (is_null($idTheme) ? '' : 'and   CAMP_QST_Q.ID_THEME = \''.ReplaceSqlPointeur($idTheme).'\' ').
            (is_null($idQuest) ? '' : 'and   CAMP_QST_Q.ID_QUEST = \''.ReplaceSqlPointeur($idQuest).'\' ').
            'order by CAMP_QST_Q.ID_QUEST ';
            
            return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * Fonction permet d'avoir la liste des reponses attendues
     *
     * @param unknown $idCamp
     * @param string $idTheme (null value)
     * @param string $idQuest (null value)
     * @param string $codNoteur (null value)
     * @param string $codFour   (null value)
     *
     * @return RequeteSQL
     */
    public static function rqGetListeReponsesAttendu($idCamp, $idTheme = null, $idQuest = null, $codNoteur=null , $codFour = null){
        $recCamp = SqlCampEve::rqGetCampagneEnt($idCamp)->Trouve1();
        $sql = 'select * from CAMP_REP_QST with (nolock) '.
            'where CAMP_REP_QST.ID_CAMP = \''.ReplaceSqlPointeur($idCamp).'\' '.
            (is_null($idTheme)  ? '' : 'and CAMP_REP_QST.ID_THEME   = \''.ReplaceSqlPointeur($idTheme).'\' ').
            (is_null($idQuest)  ? '' : 'and CAMP_REP_QST.ID_QUEST   = \''.ReplaceSqlPointeur($idQuest).'\' ').
            
            (is_null($codFour)  ? '' : 'and CAMP_REP_QST.CODE_FOU   = \''.ReplaceSqlPointeur($codFour).'\' ').
            
            (is_null($codNoteur)? '' : 'and (CAMP_REP_QST.COD_NOTEUR = \''.ReplaceSqlPointeur($codNoteur).'\' ').
            ($recCamp['CREEEPAR'] == $_SESSION['Session_Login'] ? ' OR EXISTS (select null from CAMP_QST_Q with (nolock) '.
                'WHERE CAMP_QST_Q.ID_CAMP = CAMP_REP_QST.ID_CAMP and CAMP_REP_QST.ID_THEME=CAMP_QST_Q.ID_THEME and CAMP_REP_QST.ID_QUEST=CAMP_QST_Q.ID_QUEST '.
                'AND CAMP_QST_Q.OUV_NOTEPAR = \'D\' ))' : ')');
            'order by CAMP_REP_QST.ID_QUEST ';
            //print_r($sql);exit;
            
            return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    
    /**
     * Fonction permettant d'obtenir la liste des choix possibles pour une question
     *
     * @param unknown $idCamp
     * @param unknown $idTheme
     * @param unknown $idQuest
     *
     * @return RequeteSQL
     */
    public static function rqGetListeValeurQuestion($idCamp, $idTheme, $idQuest, $idRep=null){
        $sql = 'select * from CAMP_QST_R with (nolock) '.
            'where CAMP_QST_R.ID_CAMP  = \''.ReplaceSqlPointeur($idCamp).'\' '.
            'and   CAMP_QST_R.ID_THEME = \''.ReplaceSqlPointeur($idTheme).'\' '.
            'and   CAMP_QST_R.ID_QUEST = \''.ReplaceSqlPointeur($idQuest).'\' '.
            (is_null($idRep)  ? '' : 'and CAMP_QST_R.ID_REP   = \''.ReplaceSqlPointeur($idRep).'\' ').
            'ORDER BY CAMP_QST_R.ID_REP ';
            
            return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    
    public static function getListeTheme($idCamp, $codNoteur,$codFour=null){
        $reqCamp = SqlCampEve::rqGetCampagneEnt($idCamp)->trouve1();
        $sql = 'select distinct CAMP_QST_TH.ID_THEME,CAMP_QST_TH.ID_PERE,CAMP_QST_TH.NM_ORDRE from CAMP_REP_QST with (nolock) '.
            'inner join CAMP_QST_TH with (nolock) on CAMP_QST_TH.ID_CAMP = CAMP_REP_QST.ID_CAMP AND CAMP_QST_TH.ID_THEME = CAMP_REP_QST.ID_THEME '.
            'where CAMP_REP_QST.ID_CAMP = \''.ReplaceSqlPointeur($idCamp).'\' '.
            ($reqCamp['CREEEPAR'] != $codNoteur ? 'AND   CAMP_REP_QST.COD_NOTEUR = \''.ReplaceSqlPointeur($codNoteur).'\' ' : '') .
            
            (is_null($codFour) ? '' : 'AND   CAMP_REP_QST.CODE_FOU = \''.ReplaceSqlPointeur($codFour).'\' ').
            ' order by CAMP_QST_TH.NM_ORDRE';
            
            $retour = null;
            $req = new RequeteSQL($sql, DB_OXASQLDB);
            if ($liste = $req->TrouveTous()){
                foreach ($liste as & $element){
                    $idTheme = $element['ID_THEME'];
                    if (is_null($retour) || ! array_key_exists($idTheme,$retour)){
                        // recherche du pere
                        $retour[$idTheme] = true;
                        
                        $idPere = $element['ID_PERE'];
                        while (! array_key_exists($idPere,$retour)){
                            
                            $sql = 'select * from CAMP_QST_TH with (nolock) '.
                                'where CAMP_QST_TH.ID_CAMP = \''.ReplaceSqlPointeur($idCamp).'\' '.
                                'and   CAMP_QST_TH.ID_THEME = \''.$idPere.'\' ';
                            
                            $req = new RequeteSQL($sql, DB_OXASQLDB);
                            if ($elementPere = $req->Trouve1()){
                                $retour[$idPere] = true;
                                $idPere = $elementPere['ID_PERE'];
                            }
                            else
                                break;
                        }
                    }
                }
            }
            
            return $retour;
    }
    
    public static function getListeCampagneFourAnoter($codeFour){
        $sql = 'select * from CAMP_LST_FOUR with (nolock),CAMP_ENT with (nolock) '.
            'WHERE CAMP_LST_FOUR.TERMINE = 0x46 '.
            'and   CAMP_LST_FOUR.CODE_FOU = \''.ReplaceSqlPointeur($codeFour).'\' '.
            'and   exists (select null from CAMP_REP_QST with(nolock) '.
            'where CAMP_REP_QST.COD_NOTEUR = \''.ReplaceSqlPointeur($_SESSION['Session_Login']).'\' '.
            'and   CAMP_REP_QST.CODE_FOU = CAMP_LST_FOUR.CODE_FOU '.
            'and   CAMP_REP_QST.ID_CAMP = CAMP_LST_FOUR.ID_CAMP '.
            ') '.
            'AND   CAMP_ENT.ID_CAMP = CAMP_LST_FOUR.ID_CAMP '.
            'AND   CAMP_ENT.STATUT = \''.CampEve::STATUT_ENCOURS.'\' '.
            'order by CAMP_LST_FOUR.ID_CAMP';
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
}
