<?php

/**
 * rq_sql_ste.php
 * 
 * @author edp
 *
 */
class SqlSociete
{

    public static function rqPopupGetAllSocieteForUser($array_filters = array(), $modeData = TRUE, $limitListe = true)
    {
        $sql = 'select ' . ($modeData ? ($limitListe ? "" : '') . "CODE_STE, RAIS_SOC, CPOSTAL as CODE_POSTAL, VILLE1 as VILLE, SIRET " : "count(*) CPTRESTBL  ") . "from SOCIETE with (nolock) " . "where (DTEVALMAX >= CONVERT(char(8),GETDATE(),112) OR DTEVALMAX = 0) and (DTEVALMIN <= CONVERT(char(8),GETDATE(),112) OR DTEVALMIN = 0) ";
        
        // Spec 3560
        if (array_key_exists('CODE_POSTAL', $array_filters))
            if ('' != $array_filters['CODE_POSTAL'])
                $sql .= " and CPOSTAL like '%" . ReplaceSqlPointeur($array_filters['CODE_POSTAL']) . "%'";
        
        if (array_key_exists('VILLE', $array_filters))
            if ('' != $array_filters['VILLE'])
                $sql .= " and upper(VILLE1) like upper('%" . ReplaceSqlPointeur($array_filters['VILLE']) . "%')";
        
        $sql .= " and CODE_STE in 	(	
												select SOC_USR.COD_STE from SOC_USR with(nolock), HASTUTIL with(nolock)  where 
												(
												 SOC_USR.COD_USR 	= upper('" . ReplaceSqlPointeur($_SESSION['Session_Login']) . "') 
												 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  and HASTUTIL.CODE = upper('" . ReplaceSqlPointeur($_SESSION['Session_Login']) . "') 
	
	
	
												UNION	select HASTUTIL.CODE_STE from HASTUTIL  with(nolock) where HASTUTIL.CODE = upper('" . ReplaceSqlPointeur($_SESSION['Session_Login']) . "')
										   )";
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $sql .= " and upper(CODE_STE) = upper('" . ReplaceSqlPointeur($array_filters['DATA_STRICT']) . "')";
        }
        if (array_key_exists('GROUPE', $array_filters)) {
            $sql .= " and upper(GROUPE) = upper('" . ReplaceSqlPointeur($array_filters['GROUPE']) . "')";
        }
        
        $clause = '';
        $is_clause = false;
        
        if (array_key_exists('relevance', $array_filters)) {
            switch ($array_filters['relevance']) {
                case 'OR':
                case 'AND':
                    
                    if ('' != trim($array_filters['CODE_STE'])) {
                        $liste_ste = explode(' ', $array_filters['CODE_STE']);
                        
                        foreach ($liste_ste as & $ste)
                            if ('' !== trim($ste))
                                $clause .= " upper(CODE_STE) like upper('%" . ReplaceSqlPointeur($ste) . "%') {$array_filters['relevance']}";
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != $clause) { 
                            $clause = " ({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        $liste_rs = explode(' ', $array_filters['RAIS_SOC']);
                        
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        foreach ($liste_rs as & $rs)
                            if ('' !== trim($rs))
                                $clause .= " upper(RAIS_SOC) like upper('%" . ReplaceSqlPointeur($rs) . "%') {$array_filters['relevance']}";
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    break;
                
                case 'strict':
                    if ('' != trim($array_filters['CODE_STE'])) {
                        $clause .= "(upper(CODE_STE) like upper('" . ReplaceSqlPointeur($array_filters['CODE_STE']) . "%'))";
                        $is_clause = true;
                    }
                    
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $clause .= "(upper(RAIS_SOC) like upper('" . ReplaceSqlPointeur($array_filters['RAIS_SOC']) . "%'))";
                        $is_clause = true;
                    }
                    
                    break;
            }
            
            if ($is_clause)
                $sql .= ' AND ' . $clause;
            
            if ($modeData && array_key_exists('orderByField', $array_filters) && '' != $array_filters['orderByValue'])
                $sql .= " order by {$array_filters['orderByField']} {$array_filters['orderByValue']}";
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqPopupGetAllSociete(& $array_filters, $modeData)
    {
        $sql = 'select ' . ($modeData ? "CODE_STE, RAIS_SOC " : "count(*) CPTRESTBL  ") . "from SOCIETE with (nolock) " . "where (DTEVALMAX >= CONVERT(char(8),GETDATE(),112) OR DTEVALMAX = 0) ";
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $sql .= " and CODE_STE = '" . ReplaceSqlPointeur($array_filters['DATA_STRICT']) . "'";
        }
        
        $clause = '';
        $is_clause = false;
        
        if (array_key_exists('relevance', $array_filters)) {
            switch ($array_filters['relevance']) {
                case 'OR':
                case 'AND':
                    
                    if ('' != trim($array_filters['CODE_STE'])) {
                        
                        $liste_ste = explode(' ', $array_filters['CODE_STE']);
                        
                        foreach ($liste_ste as $ste)
                            if ('' !== trim($ste))
                                $clause .= " upper(CODE_STE) like upper('%" . ReplaceSqlPointeur($ste) . "%') {$array_filters['relevance']}";
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != $clause) {
                            $clause = " ({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        $liste_rs = explode(' ', $array_filters['RAIS_SOC']);
                        
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        foreach ($liste_rs as $rs)
                            if ('' !== trim($rs))
                                $clause .= " upper(RAIS_SOC) like upper('%" . ReplaceSqlPointeur($rs) . "%') {$array_filters['relevance']}";
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    break;
                
                case 'strict':
                    if ('' != trim($array_filters['CODE_STE'])) {
                        $clause .= "(upper(CODE_STE) like upper('" . ReplaceSqlPointeur($array_filters['CODE_STE']) . "%'))";
                        $is_clause = true;
                    }
                    
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $clause .= "(upper(RAIS_SOC) like upper('" . ReplaceSqlPointeur($array_filters['RAIS_SOC']) . "%'))";
                        $is_clause = true;
                    }
                    
                    break;
            }
            
            if ($is_clause)
                $sql .= ' AND ' . $clause;
            
            if ($modeData && array_key_exists('orderByField', $array_filters) && '' != $array_filters['orderByValue'])
                $sql .= " order by {$array_filters['orderByField']} {$array_filters['orderByValue']}";
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqPopupGetAllGroupeSocieteForUser($array_filters = array(), $modeData = TRUE)
    {
        $array_filters['CODE_GRP'] = (array_key_exists('CODE_GRP', $array_filters)) ? ReplaceSql(trim($array_filters['CODE_GRP'])) : '';
        $array_filters['RS_GRP'] = (array_key_exists('RS_GRP', $array_filters)) ? ReplaceSql(trim($array_filters['RS_GRP'])) : '';
        
        $sql = 'select ' . ($modeData ? "CODE_GRP, RS_GRP, SIEGE, SOCIETE.RAIS_SOC as SIEGE_RS " : "count(*) CPTRESTBL  ") . 'from GROUPSOC with (nolock) ' . 'left join SOCIETE with (nolock) on GROUPSOC.SIEGE = SOCIETE.CODE_STE ' . 'where 1=1 ';
        
        // Périmètre de base : les groupe dont au moins une société est dans le péimètre de l'utilisateur
        if (! utilisateur::isMultiSoc()) {
            $sql .= ' and CODE_GRP in (select distinct GROUPE from SOCIETE with (nolock) where CODE_STE in (' . OxalysBib::array1DToSqlStr(utilisateur::getAllSocietes()) . '))';
        }
        
        // Mail YA 2017.04.07 : on ne restreint pas le périmètre de l'utilisateur selon qu'il est administrateur ou pas.
        // Si l'utilisateur est administrateur local
        // if (utilisateur::isAdminLocal()) {
        // $sql.= ' and CODE_GRP in ';
        // $sql.= " (select COD_GRPS from ADMLGRSO with(nolock) where COD_USR = '" . ReplaceSqlPointeur(utilisateur::getCode()) . "')";
        // }
        
        if ('' != $array_filters['CODE_GRP'])
            $sql .= ' and upper(CODE_GRP) like upper(\'%' . ReplaceSqlPointeur($array_filters['CODE_GRP']) . '%\')';
        
        if ('' != $array_filters['RS_GRP'])
            $sql .= ' and upper(RS_GRP) like upper(\'%' . ReplaceSqlPointeur($array_filters['RS_GRP']) . '%\')';
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getRaisonSociale($code)
    {
        $sql = "select RAIS_SOC FROM SOCIETE WHERE CODE_STE = '".$code."'";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
}