<?php

/**
 * Fonction retournant la requête permettant de sélectionner les favoris associés à une langue donnée
 *
 * @param string $langue
 * @return string sql
 */
class SqlInfo
{

    public static function rqFavoris($langue = '')
    {
        $sql = "select SIEX_THE, SIEX_NOM, SIEX_URL, SIEX_ICO, CAST(MEMOS.CONTMEMO AS TEXT) 
				AS CONTMEMO from SIEXLIST with(nolock) left outer join MEMOS with(nolock) 
				on NM_COMM=NODEMEMO where COD_LNG = '" . ReplaceSqlPointeur($langue) . "' order by SIEX_THE, SIEX_NOM";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetMemo($NODEMEMO)
    {
        $sql = 'select CONTMEMO from MEMOS with(nolock) where NODEMEMO = ' . ReplaceSqlPointeur($NODEMEMO);
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqDeleteMemo($NODEMEMO)
    {
        $sql = 'delete MEMOS where NODEMEMO = ' . ReplaceSqlPointeur($NODEMEMO);
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête permettant de sélectionner les actualités associées à une langue donnée
     *
     * @param string $langue
     * @return string sql
     */
    public static function rqActu($langue = '')
    {
        $sql = "select URL_PAGE from PAGEINFO with (nolock) where COD_LNG ='" . ReplaceSqlPointeur($langue) . '\'';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant une requete permettant d'obtenir les informations relatives à une SI
     */
    public static function rqGetInfoOnSI(& $si, $CodeSte = '')
    {
        $sql = "select * from SECTIMPU with(nolock) where CODE_SI='" . ReplaceSqlPointeur($si) . "' ";
        if (isset($CodeSte) && ('' != $CodeSte))
            $sql .= "and CODE_STE='" . ReplaceSqlPointeur($CodeSte) . '\'';
        else
            $sql .= "and CODE_STE=''";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getInfoOnSI(& $si, $CodeSte = '')
    {
        return SqlInfo::rqGetInfoOnSI($si, $CodeSte)->TrouveTous();
    }

    /**
     * Section Paramétrage
     */
    
    /**
     * Fonction indiquant si dans le cadre de la création de nouveaux compte utilisateur
     * la saisie de la fonction est une liste imposée ou bien une saisie libre
     */
    public static function rqModeSaisieFonction()
    {
        $sql = "
            select
                *
            from
                PARAVRAC with(nolock)
            where
                TYPEPARV = 'PARAMGEN'
                and CODEPARV = 'CONTROLE_FONCTION_UTILISATEUR'
        ";

        $cacheName = __FUNCTION__;

        return \SqlWincache::getSql($cacheName, $sql);
    }

    // Chapitre concernant les CG
    public static function SqlCGSteSectoRecherche($code_ste, $code_si, $num_lot, $cod_rech, $lib_rech, $is_valid = 0)
    {
        $num_lot = ($num_lot == '' ? 0 : $num_lot);
        $date = date('Ymd', time());
        $sql = " select CODGEST.CODE_STE,
								 CODGEST.CODE_SI,
								 CODGEST.NUM_LOT,
								 CODGEST.CODE_GES,
								 CODGEST.LIB_GES,
								 CODGEST.ID_PARENT,
								 CODGEST.CODE_AFF,
								 CODGEST.DTEVALMIN,
								 CODGEST.DTEVALMAX," . " (CASE WHEN CODGEST.DTEVALMIN<='{$date}' AND (CODGEST.DTEVALMAX = '00000000' OR CODGEST.DTEVALMAX >= '{$date}') AND CODGEST.AUTHACHAT = 0x54 THEN 0 ELSE 1 END) AS Blocage " . " from CODGEST with (nolock), SECTIMPU with (nolock) " . " where  CODGEST.CODE_STE = '" . ReplaceSqlPointeur($code_ste) . "' and SECTIMPU.CODE_STE = CODGEST.CODE_STE " . " and    SECTIMPU.CODE_SI  = CODGEST.CODE_SI and SECTIMPU.SICODG = 0x54 " . " and    CODGEST.CODE_SI  = '" . ReplaceSqlPointeur($code_si) . "' " . " and    CODGEST.NUM_LOT   =  '" . ReplaceSqlPointeur($num_lot) . "'  " . ($cod_rech != '' ? "and   upper(CODGEST.CODE_AFF)  LIKE '%" . ReplaceSqlPointeur(strtoupper($cod_rech)) . "%' " : '') . ($lib_rech != '' ? "and   upper(CODGEST.LIB_GES)   LIKE '%" . ReplaceSqlPointeur(strtoupper($lib_rech)) . "%' " : '');
        
        if (1 == $is_valid) {
            $sql .= " and (CODGEST.DTEVALMAX =00000000 or CODGEST.DTEVALMAX >= CONVERT(VARCHAR,GETDATE(),112))";
            $sql .= " and (CODGEST.DTEVALMIN =00000000 or CODGEST.DTEVALMIN <= CONVERT(VARCHAR,GETDATE(),112))";
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlCGSteRecherche($code_si, $num_lot, $cod_rech, $lib_rech, $is_valid = 0)
    {
        $num_lot = ($num_lot == '' ? 0 : $num_lot);
        $date = date('Ymd', time());
        $sql = " select CODGEST.CODE_STE,
								 CODGEST.CODE_SI,
								 CODGEST.NUM_LOT,
								 CODGEST.CODE_GES,
								 CODGEST.LIB_GES,
								 CODGEST.ID_PARENT,
								 CODGEST.CODE_AFF,
								 CODGEST.DTEVALMIN,
								 CODGEST.DTEVALMAX," . " (CASE WHEN CODGEST.DTEVALMIN<='{$date}' AND (CODGEST.DTEVALMAX = '00000000' OR CODGEST.DTEVALMAX >= '{$date}') AND CODGEST.AUTHACHAT = 0x54 THEN 0 ELSE 1 END) AS Blocage " . " from CODGEST with (nolock), SECTIMPU with (nolock) " . " where  CODGEST.CODE_SI  = '" . ReplaceSqlPointeur($code_si) . "' " . " and    CODGEST.CODE_STE = '' " . " and   CODGEST.NUM_LOT   =  '" . ReplaceSqlPointeur($num_lot) . "' " . " and   SECTIMPU.CODE_SI  = CODGEST.CODE_SI and SECTIMPU.SICODG = 0x54 " . " and   upper(CODGEST.CODE_AFF)  LIKE '%" . ReplaceSqlPointeur(strtoupper($cod_rech)) . "%' " . " and   upper(CODGEST.LIB_GES)   LIKE '%" . ReplaceSqlPointeur(strtoupper($lib_rech)) . "%' ";
        
        if (1 == $is_valid) {
            $sql .= " and (CODGEST.DTEVALMAX =00000000 or CODGEST.DTEVALMAX >= CONVERT(VARCHAR,GETDATE(),112))";
            $sql .= " and (CODGEST.DTEVALMIN =00000000 or CODGEST.DTEVALMIN <= CONVERT(VARCHAR,GETDATE(),112))";
        }
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlUOSteSecto(& $code_ste, & $uo)
    {
        $sql = "select * from UO with (nolock) " . "where CODE_STE = '" . ReplaceSqlPointeur($code_ste) . "'" . " and   CODE_UO  = '" . ReplaceSqlPointeur($uo) . "' order by CODE_UO";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Function retournant le record associe à la table des UO
     */
    public static function SqlUOSte($uo)
    {
        // ATTENTION pour la non sectorisation , la societe est " " (un espace)
        $sql = "select * from UO with (nolock) where CODE_UO  = '" . ReplaceSqlPointeur($uo) . "' ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqSqlGetUOLieeUtilisateur(& $CODE_STE, & $CODE_USER, $testToutUO = false)
    {
        if(!$testToutUO)
        {
            $sql = 'select * from LSTUOPARUSR with (nolock) where CODE_USER = \'' . ReplaceSqlPointeur($CODE_USER) . '\' and CODE_STE = \'' . ReplaceSqlPointeur($CODE_STE) . '\' and CODE_UO != \'\' order by CODE_UO;';
        }else{
            $sql = "select * from UO with (nolock) where UO_MERE = '' order by CODE_UO";
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlUOSteSectoRechercheCRB($code_ste, $cod_rech = '', $lib_rech = '')
    {
        $sql = " select * from UO with (nolock) " . " where CODE_STE = '" . ReplaceSqlPointeur($code_ste) . '\' ' . " and   upper(CODE_UO)  LIKE '%" . strtoupper(ReplaceSqlPointeur($cod_rech)) . "%' " . " and   upper(LIB_UO)   LIKE '%" . strtoupper(ReplaceSqlPointeur($lib_rech)) . "%' 
				order by CODE_UO";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlUOSteSectoRechercheResponsable($code_ste, $cod_rech = '', $lib_rech = '', $user, $exitUser = false)
    {
        
        $exist = '';
        
        if($exitUser)
            $exist = "and
				not EXISTS  (
					select CODE_UO from LSTUOPARUSR with (nolock) where CODE_USER = '" . ReplaceSqlPointeur($user) . "'
				) ";
        
        $sql = " select * from UO with (nolock) 
				where CODE_STE = '" . ReplaceSqlPointeur($code_ste) . "' 
				and   ((upper(CODE_UO)  LIKE '%" . strtoupper(ReplaceSqlPointeur($cod_rech)) . "%'
				and   upper(LIB_UO)   LIKE upper('%" . ReplaceSqlPointeur($lib_rech) . "%') 
				and upper(RESP_HIER) = upper('" . ReplaceSqlPointeur($user) . "')))
            
				order by CODE_UO";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlUOSteSectoRecherche($code_ste, $cod_rech = '', $lib_rech = '', $user = '')
    {
        $sql = " select * from UO with (nolock) " . " where CODE_STE = '" . ReplaceSqlPointeur($code_ste) . '\' ' . " and   ((upper(CODE_UO)  LIKE '%" . strtoupper(ReplaceSqlPointeur($cod_rech)) . "%' " . " and   upper(LIB_UO)   LIKE upper('%" . ReplaceSqlPointeur($lib_rech) . "%') ";
        // Dossier 26009 : on supprime la recherche du niveau
        // ." and NIVEAU = 1 "
        
        if ($user != '')
            $sql .= " and upper(RESP_HIER) = upper('" . ReplaceSqlPointeur($user) . '\')';
        $sql .= ')';
        // Spec 3561
        // Suite aux dossiers 23801, 24075, 24669, 25191
        // On ne doit pas chercher les descendant vis-à-vis de la liste LSTUOPARUSR
        // if ($user != '')
        // $sql .= ' or CODE_UO in (select CODE_UO from LSTUOPARUSR where CODE_USER=\''.$user.'\' and CODE_STE = \''.$code_ste.'\' and upper(CODE_UO) LIKE \'%'.$cod_rech.'%\')';
        $sql .= ') order by CODE_UO';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlUOSteRechercheCRB($cod_rech = '', $lib_rech = '', $user = '')
    {
        $sql = "select * from UO with (nolock) " . "where CODE_STE = ' ' " . "and   upper(CODE_UO)  LIKE '%" . strtoupper(ReplaceSqlPointeur($cod_rech)) . "%' " . "and   upper(LIB_UO)   LIKE '%" . strtoupper(ReplaceSqlPointeur($lib_rech)) . "%' " . " order by CODE_UO";
        // Dossier 26034
        // if ($user != '')
        // $sql .=" and upper(RESP_HIER) = '" . ReplaceSql(strtoupper($user)) . '\'';
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    public static function lstUoSiUser($codesi = '', $ste)
    {
        $sql = "select * from LSTSIPARUO with (nolock) where CODE_STESI = '" . ReplaceSqlPointeur($ste) . "' and CODE_SI = '" . ReplaceSqlPointeur($codesi) . "' ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlUOSteRechercheResponsable($cod_rech = '', $lib_rech = '', $user, $exitUser=false)
    {
        
        $exist = '';
        
        if($exitUser)
            $exist = "and 
				not EXISTS  (
					select CODE_UO from LSTUOPARUSR with (nolock) where CODE_USER = '" . ReplaceSqlPointeur($user) . "'
				) ";
            
            
        $sql = "select * from UO with (nolock)
				where CODE_STE = ' ' 
				and 
				(
					upper(CODE_UO)  LIKE '%" . strtoupper(ReplaceSqlPointeur($cod_rech)) . "%' 
					and   upper(LIB_UO)   LIKE '%" . strtoupper(ReplaceSqlPointeur($lib_rech)) . "%'
					and RESP_HIER = '" . strtoupper(ReplaceSqlPointeur($user)) . '\'
				) '.$exist.' order by CODE_UO';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlUOSteRecherche($cod_rech = '', $lib_rech = '', $user = '')
    {
        $sql = "select * from UO with (nolock) " . "where CODE_STE = ' ' " . "and   ((upper(CODE_UO)  LIKE '%" . strtoupper(ReplaceSqlPointeur($cod_rech)) . "%' " . "and   upper(LIB_UO)   LIKE '%" . strtoupper(ReplaceSqlPointeur($lib_rech)) . "%' ";
        // Dossier 26034
        // if ($user != '')
        // $sql .=" and RESP_HIER = '" . strtoupper(ReplaceSqlPointeur($user)) . '\'';
        // else
        // $sql .=" and NIVEAU = 1 ";
        $sql .= ")";
        // Spec 3561
        // Suite aux dossiers 23801, 24075, 24669, 25191
        // On ne doit pas chercher les descendant vis-à-vis de la liste LSTUOPARUSR
        // if ($user != '')
        // $sql .= ' or CODE_UO in (select CODE_UO from LSTUOPARUSR where CODE_USER=\''.$user.'\' and CODE_STE = \' \' and upper(CODE_UO) LIKE \'%'.$cod_rech.'%\')';
        $sql .= ') order by CODE_UO';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    // nous ramene les UO filles de L'UO
    public static function SqlUOFille($code_ste, $uo)
    {
        if (trim($code_ste) == '') {
            $sql = "select * from UO with (nolock) " . "where CODE_STE = ' ' " . "and   UO_MERE  = '" . ReplaceSqlPointeur($uo) . "' order by CODE_UO";
        } else {
            $sql = "select * from UO with (nolock) " . "where CODE_STE = '" . ReplaceSqlPointeur($code_ste) . "' " . "and   UO_MERE  = '" . ReplaceSqlPointeur($uo) . "' order by CODE_UO";
        }
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    // nous ramene les UO filles d'une liste de UO
    public static function SqlUOFilleListe(& $code_ste, & $listeUO)
    {
        if (trim($code_ste) == '')
            $sql = "select CODE_UO from UO with (nolock) " . "where CODE_STE = ' ' " . "and   UO_MERE  in " . ($listeUO) . " order by CODE_UO";
        else
            $sql = "select CODE_UO from UO with (nolock) " . "where CODE_STE = '" . ReplaceSqlPointeur($code_ste) . "'" . "and   UO_MERE  in " . ($listeUO) . " order by CODE_UO";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête permettant de savoir si le paramétrage FACEO (client N°22) sur la notation ISO sur les BAP est activée
     *
     * @return string sql
     */
    public static function rqGetNumCsFaceoNotationISO()
    {
        $sql = "
            select
                LIB01DICO
            from
                DILISPEC with(nolock)
            where
                TYPEDICO = 'FACEPARAM'
                and	CODEDICO = 'NCS_NOTATION_ISO'
        ";

        $cacheName = __FUNCTION__;

        return \SqlWincache::getSql($cacheName, $sql);
    }

    public static function rqGetTypesDocument($type)
    {        
        $sql = "SELECT TYPE, 
            (CASE 
                WHEN ISNULL(t.TRAD, '') = ''
                THEN TYPDOC.LIBELLE COLLATE French_BIN 
                ELSE t.TRAD
            END) AS LIBELLE, 
            HAPLUS, 
            NIV_CONF, 
            INTERFA, 
            TXLIVEXCD, 
            MTLIVEXCD, 
            DATE_DEB, 
            DATE_FIN, 
            NVIBOXAP, 
            TYDO_IND_IMPR_PAR_DEM, 
            DEPASS_DELAI_LIVRAISON
        FROM TYPDOC WITH(NOLOCK) 
            LEFT OUTER JOIN TXTTRAD t WITH(NOLOCK) 
                ON t.COD_PROD = 'OXAPROC' 
                    AND t.TXT = TYPDOC.LIBELLE COLLATE French_BIN 
                    AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
        WHERE TYPE = '" . ReplaceSqlPointeur($type) . "'";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    public static function rqGetAllTypesDocument()
    {
        $date = date('Ymd');
        $sql = "SELECT TYPE, 
            (CASE 
                WHEN ISNULL(t.TRAD, '') = ''
                THEN typ.LIBELLE COLLATE French_BIN 
                ELSE t.TRAD
            END) AS LIBELLE, HAPLUS, NIV_CONF, INTERFA, TXLIVEXCD, MTLIVEXCD, DATE_DEB, DATE_FIN, NVIBOXAP, TYDO_IND_IMPR_PAR_DEM, DEPASS_DELAI_LIVRAISON
        FROM TYPDOC typ WITH(NOLOCK) 
            LEFT OUTER JOIN TXTTRAD t WITH(NOLOCK) 
                    ON t.COD_PROD = 'OXAPROC' 
                        AND t.TXT = typ.LIBELLE COLLATE French_BIN 
                        AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
        WHERE (typ.DATE_DEB = '00000000' OR typ.DATE_DEB >= '$date') 
            AND (typ.DATE_FIN = '00000000' OR typ.DATE_FIN >= '$date')";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    public static function rqGetAllTypesTypeAchat()
    {
        $sql = "SELECT TYPEACHAT.TYP_ACH, 
            (CASE 
                WHEN ISNULL(t.TRA_TAC, '') = ''
                THEN LIB_TAC 
                ELSE t.TRA_TAC
            END) AS LIB_TAC, 
            STRAIT, 
            INVEST 
        FROM TYPEACHAT WITH(NOLOCK)
            LEFT OUTER JOIN TRAD_TAC t WITH(NOLOCK) 
                ON t.TYP_ACH = TYPEACHAT.TYP_ACH 
                    AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
        ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqPopupGetAllTypesDocument($array_filters, $modeData)
    {
        $sql = "SELECT " . ($modeData ? "TYPE, (CASE WHEN ISNULL(t.TRAD, '') = '' THEN TYPDOC.LIBELLE COLLATE French_BIN ELSE t.TRAD END) AS LIBELLE " : "count(*) CPTRESTBL ") . " 
        FROM TYPDOC with(nolock) 
            LEFT OUTER JOIN TXTTRAD t WITH(NOLOCK) 
                ON t.COD_PROD = 'OXAPROC' 
                    AND t.TXT = TYPDOC.LIBELLE COLLATE French_BIN 
                    AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
        WHERE 1=1 ";
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and TYPE = '{$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['TYPE'])) {
                        $liste_type = explode(' ', ReplaceSqlPointeur($array_filters['TYPE']));
                        if (! is_array($liste_type) || 0 == count($liste_type))
                            $liste_type = explode(' ', $array_filters['TYPE']);
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(TYPE) like upper('%" . $type . "%') {$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['LIBELLE'])) {
                        $liste_libelle = explode(' ', ReplaceSqlPointeur($array_filters['LIBELLE']));
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(LIBELLE) like upper('%" . $libelle . "%') {$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['TYPE'])) {
                        $clause .= "(upper(TYPE) like upper('" . ReplaceSqlPointeur($array_filters['TYPE']) . "%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['LIBELLE'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(LIBELLE) like upper('" . ReplaceSqlPointeur($array_filters['LIBELLE']) . "%'))";
                        $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 rqPopupGetAllTypesAchat($array_filters, $modeData)
    {
        $sql = "SELECT " . ($modeData ? " TYPEACHAT.TYP_ACH, (CASE WHEN ISNULL(t.TRA_TAC, '') = '' THEN TYPEACHAT.LIB_TAC COLLATE French_BIN ELSE t.TRA_TAC END) AS LIB_TAC " : "count(*) CPTRESTBL  ") . "
        from TYPEACHAT with(nolock) 
            LEFT OUTER JOIN TRAD_TAC t WITH(NOLOCK) 
                ON t.TYP_ACH = TYPEACHAT.TYP_ACH COLLATE French_BIN 
                    AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
        where 1=1 ";
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and TYP_ACH = '{$array_filters['DATA_STRICT']}'";
        }

        // SPEC 4374 : ajout d'une clause pour récupérer les types achat de type sous-traitance
        if (array_key_exists('SOUS_TRAITANCE', $array_filters) && $array_filters['SOUS_TRAITANCE']) {
            $sql .= " and STRAIT = 0x54";
        }
        
        $clause = '';
        $is_clause = false;
        
        if (array_key_exists('relevance', $array_filters)) {
            switch ($array_filters['relevance']) {
                case 'OR':
                case 'AND':
                    
                    if ('' != trim($array_filters['TYP_ACH'])) {
                        $liste_type = explode(' ', ReplaceSqlPointeur($array_filters['TYP_ACH']));
                        
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(TYP_ACH) like upper('%" . $type . "%') {$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['LIB_TAC'])) {
                        $liste_libelle = explode(' ', ReplaceSqlPointeur($array_filters['LIB_TAC']));
                        
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(LIB_TAC) like upper('%" . $libelle . "%') {$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['TYP_ACH'])) {
                        $clause .= "(upper(TYP_ACH) like upper('" . ReplaceSqlPointeur($array_filters['TYP_ACH']) . "%'))";
                        $is_clause = true;
                    }
                    
                    if ('' != trim($array_filters['LIB_TAC'])) {
                        if ($is_clause) {
                            $clause .= ' AND ';
                        }
                        
                        $clause .= "(upper(LIB_TAC) like upper('" . ReplaceSqlPointeur($array_filters['LIB_TAC']) . "%'))";
                        $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 rqPopupGetAllFamillesAchat($array_filters, $modeData)
    {
        $sql = "select " . ($modeData ? "CODE_FAM, DESIGNAT " : "count(*) CPTRESTBL  ") . "from FAMILLE with(nolock) where 1=1 ";
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and CODE_FAM = '{$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_FAM'])) {
                        $liste_type = explode(' ', ReplaceSqlPointeur($array_filters['CODE_FAM']));
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(CODE_FAM) like upper('%" . $type . "%') {$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['DESIGNAT'])) {
                        $liste_libelle = explode(' ', ReplaceSqlPointeur($array_filters['DESIGNAT']));
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(DESIGNAT) like upper('%" . $libelle . "%') {$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_FAM'])) {
                        $clause .= "(upper(CODE_FAM) like upper('" . ReplaceSqlPointeur($array_filters['CODE_FAM']) . "%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['DESIGNAT'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(DESIGNAT) like upper('" . ReplaceSqlPointeur($array_filters['DESIGNAT']) . "%'))";
                        $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 rqPopupGetAllGammesFabricant($array_filters, $modeData, $codeFabr)
    {
        $sql = 'select ' . ($modeData ? "CODEGAMME, DESIGN " : "count(*) CPTRESTBL  ") . 'from GAMMES with(nolock) where CODEFABR = \'' . ReplaceSqlPointeur($codeFabr) . '\' ';
        
        if (array_key_exists('DATA_STRICT', $array_filters))
            $sql .= " and CODEGAMME = '" . 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['CODEGAMME'])) {
                        $liste_type = explode(' ', ReplaceSqlPointeur($array_filters['CODEGAMME']));
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(CODEGAMME) like upper('%" . $type . "%') {$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['DESIGN'])) {
                        $liste_libelle = explode(' ', ReplaceSqlPointeur($array_filters['DESIGN']));
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(DESIGN) like upper('%" . $libelle . "%') {$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['CODEGAMME'])) {
                        $clause .= "(upper(CODEGAMME) like upper('" . ReplaceSqlPointeur($array_filters['CODEGAMME']) . "%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['DESIGN'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(DESIGN) like upper('" . ReplaceSqlPointeur($array_filters['DESIGN']) . "%'))";
                        $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 rqPopupGetAllSousFamillesAchat($array_filters, $modeData, $objectSupport = null)
    {
        $sql = 'select ' . ($modeData ? "CODE_SFA, DESIGNAT " : "count(*) CPTRESTBL  ") . 'from SSFAMILL with(nolock) where NVISIPROC = 0x46 ';
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and CODE_SFA = '{$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_SFA'])) {
                        $liste_type = explode(' ', ReplaceSqlPointeur($array_filters['CODE_SFA']));
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(CODE_SFA) like upper('%" . $type . "%') {$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['DESIGNAT'])) {
                        $liste_libelle = explode(' ', ReplaceSqlPointeur($array_filters['DESIGNAT']));
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(DESIGNAT) like upper('%" . $libelle . "%') {$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_SFA'])) {
                        $clause .= "(upper(CODE_SFA) like upper('" . ReplaceSqlPointeur($array_filters['CODE_SFA']) . "%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['DESIGNAT'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(DESIGNAT) like upper('" . ReplaceSqlPointeur($array_filters['DESIGNAT']) . "%'))";
                        $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 rqPopupGetAllMarches($array_filters, $modeData)
    {
        $sql = "select " . ($modeData ? "CODE, LIBELLE " : "count(*) CPTRESTBL  ") . "from MARCHES with(nolock) where 1=1 ";
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and CODE = '{$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'])) {
                        $liste_type = explode(' ', ReplaceSqlPointeur($array_filters['CODE']));
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(CODE) like upper('%" . $type . "%') {$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['LIBELLE'])) {
                        $liste_libelle = explode(' ', ReplaceSqlPointeur($array_filters['LIBELLE']));
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(LIBELLE) like upper('%" . $libelle . "%') {$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'])) {
                        $clause .= "(upper(CODE) like upper('" . ReplaceSqlPointeur($array_filters['CODE']) . "%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['LIBELLE'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(LIBELLE) like upper('" . ReplaceSqlPointeur($array_filters['LIBELLE']) . "%'))";
                        $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 rqPopupGetAllACD($array_filters, $modeData)
    {
        $sql = "SELECT " . ($modeData ? "CODE_ACD, LIBELLE " : "count(*) CPTRESTBL  ") . "from ACCCADRE acc with(nolock) where 1=1 ";
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and acc.CODE_ACD = '{$array_filters['DATA_STRICT']}'";
        }
        
        // Fournisseur
        if(($array_filters['CODE_FOUR'] ?? '')!=''){
            $sql .= " and 
            (
                (
                    acc.TYPE_FG='F' 
                    and acc.CODE_FOUR = '" . ReplaceSqlPointeur($array_filters['CODE_FOUR']) . "'
                )
                or
                (
                    acc.TYPE_FG='G' 
                    and acc.CODE_FOUR=(SELECT four.FOURPRIN FROM FOURNISS four WHERE four.CODE='" . ReplaceSqlPointeur($array_filters['CODE_FOUR']) . "') 
                    and exists(SELECT null FROM FS_ACD fs WHERE fs.CODE_ACD=acc.CODE_ACD AND fs.CODE_FOU='" . ReplaceSqlPointeur($array_filters['CODE_FOUR']) . "')
                )
            )";
        }
        
        // Validité 
        if(($array_filters['valide'] ?? '')==1){
            $sql .= " and 
            (
                acc.DATE_DEB <= CONVERT(char(8),GETDATE(),112) 
                and (acc.DATE_FIN >= CONVERT(char(8),GETDATE(),112) or acc.DATE_FIN = 00000000)
            )";
        }

        // Centre émetteur et livraison  
        if(($array_filters['CENTRE_EMETTEUR'] ?? '')!='' && ($array_filters['CENTRE_LIVRAISON'] ?? '')!=''){
            $sql .= " and 
            (
                (
                    exists (SELECT lien.CODE_ACD from ACCTR lien where lien.CODE_ACD=acc.CODE_ACD and lien.CODE_CTR='" . ReplaceSqlPointeur($array_filters['CENTRE_EMETTEUR']) . "')
                    or
                    exists (SELECT lien.CODE_ACD from ACCTRLIV lien where lien.CODE_ACD=acc.CODE_ACD and lien.CODE_CTR='" . ReplaceSqlPointeur($array_filters['CENTRE_LIVRAISON']) . "')
                    or 
                    (
                        not exists (SELECT emet.CODE_ACD from ACCTR emet where emet.CODE_ACD=acc.CODE_ACD) 
                        and 
                        not exists (SELECT liv.CODE_ACD from ACCTRLIV liv where liv.CODE_ACD=acc.CODE_ACD)
                    )
                )
            
            )";
        }
        
        // Sté du Marché 
        if(($array_filters['CODE_STE'] ?? '')!=''){
            $sql .= " and 
            (
                acc.MARCHE='' 
                or 
                (
                (exists (select march.CODEMARCH from MARCHSOC march where acc.MARCHE=march.CODEMARCH and march.CODESOC='" . ReplaceSqlPointeur($array_filters['CODE_STE']) . "')
                    or not exists (select march.CODEMARCH from MARCHSOC march where acc.MARCHE=march.CODEMARCH)
                )
                and
                    (
                        not exists (select * from MARCHUSR where CODE_MAR=acc.MARCHE)
                        or
                        exists (select * from MARCHUSR where CODE_MAR=acc.MARCHE and CODE_USR='{$_SESSION['Session_Login']}')
                    )
                )
            )";
        }
        
        // Code Article
        if(($array_filters['code_art_haplus'] ?? '')!=''){
            $sql .= " and 
            (
                not exists (SELECT * FROM ACART artacd WHERE artacd.CODE_ACD=acc.CODE_ACD AND artacd.CODE_ART='" . ReplaceSqlPointeur($array_filters['code_art_haplus']) . "')
                or
                exists (SELECT * FROM ACART artacd2 WHERE artacd2.CODE_ACD=acc.CODE_ACD AND artacd2.CODE_ART='" . ReplaceSqlPointeur($array_filters['code_art_haplus']) . "' AND artacd2.DDEBVAL<=CONVERT(char(8),GETDATE(),112) AND (artacd2.DFINVAL='00000000' OR artacd2.DFINVAL>=CONVERT(char(8),GETDATE(),112)))
            )";
        }


        $clause = '';
        $is_clause = false;
        if (array_key_exists('relevance', $array_filters)) {
            switch ($array_filters['relevance']) {
                case 'OR':
                case 'AND':
                    if ('' != trim($array_filters['CODE_ACD'])) {
                        $liste_type = explode(' ', $array_filters['CODE_ACD']);
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(acc.CODE_ACD) like upper('%" . ReplaceSqlPointeur($type) . "%') {$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['LIBELLE'])) {
                        $liste_libelle = explode(' ', $array_filters['LIBELLE']);
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(acc.LIBELLE) like upper('%" . ReplaceSqlPointeur($libelle) . "%') {$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_ACD'])) {
                        $clause .= "(upper(acc.CODE_ACD) like upper('" . ReplaceSqlPointeur($array_filters['CODE_ACD']) . "'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['LIBELLE'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $clause .= "(upper(acc.LIBELLE) like upper('" . ReplaceSqlPointeur($array_filters['LIBELLE']) . "'))";
                        $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 rqPopupGetAllDirection($array_filters, $modeData)
    {
        $sql = "select " . ($modeData ? "CODE_STE, CODE_DIR, LIBELLE " : "count(*) CPTRESTBL  ") . "from DIRECTIO with(nolock) where 1=1 ";
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $sql .= " and CODE_DIR = '" . ReplaceSqlPointeur($array_filters['DATA_STRICT']) . "'";
        }
        if ('' != $array_filters['CODE_STE'] && Param::IsSISecto())
            $sql .= " and CODE_STE = '" . ReplaceSqlPointeur($array_filters['CODE_STE']) . "' ";
        else
            $sql .= " and CODE_STE = '' ";
        
        $clause = '';
        $is_clause = false;
        if (array_key_exists('relevance', $array_filters)) {
            switch ($array_filters['relevance']) {
                case 'OR':
                case 'AND':
                    if ('' != trim($array_filters['CODE_DIR'])) {
                        $liste_type = explode(' ', $array_filters['CODE_DIR']);
                        foreach ($liste_type as $type)
                            if ('' !== trim($type))
                                $clause .= " upper(CODE_DIR) like upper('%" . ReplaceSqlPointeur($type) . "%') {$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['LIBELLE'])) {
                        $liste_libelle = explode(' ', $array_filters['LIBELLE']);
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_libelle as $libelle)
                            if ('' !== trim($libelle))
                                $clause .= " upper(LIBELLE) like upper('%" . ReplaceSqlPointeur($libelle) . "%') {$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_DIR'])) {
                        $clause .= "(upper(CODE_DIR) like upper('" . ReplaceSqlPointeur($array_filters['CODE_DIR']) . "'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['LIBELLE'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(LIBELLE) like upper('" . ReplaceSqlPointeur($array_filters['LIBELLE']) . "'))";
                        $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);
    }

    /**
     * Fonction retournant la requête permettant de sélectionner les types de PJ
     *
     * @return string sql
     */
    public static function rqGetAllTypePJ()
    {
        $sql = 'select PJ_TYPE, PJLIBELLE from PJTYPES with(nolock) ';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetAllNaturesAnalytiques($array_filters, $modeData)
    {
        $sql = "select " . ($modeData ? "CODE_STE, CODNAN, LIBELLE " : "count(*) CPTNAN  ") . "from NATANA with(nolock) where 1=1 ";
        if ('' != $array_filters['CODE_STE'] && Param::IsCDCSecto()) {
            $sql .= " and CODE_STE = '" . ReplaceSqlPointeur($array_filters['CODE_STE']) . "' ";
        } else
            $sql .= " and CODE_STE = '' ";
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $sql .= " and CODNAN = '" . ReplaceSqlPointeur($array_filters['DATA_STRICT']) . "'";
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetDocRattByItem($type_item, $code_item)
    {
        if ('CA_ENTET' == $type_item || 'DA_ENTET' == $type_item || 'AO_ENTET' == $type_item || 'BR_RECEP' == $type_item || 'FAC_ENT' == $type_item || 'BS_ENTET' == $type_item)
        {
            $code_item = str_pad($code_item, 9, '0', STR_PAD_LEFT);
        }

        $sql = "	select NO_DA TYPE_ITEM, COD_ENR CODE_ITEM, NO_ORDRE, TYPE_PJ, TITRE, NOM_FIC, NOM_ORI, AUTEUR, CENTRE, DT_PARUT, DT_VALID, MEMOS.CONTMEMO COMMENT  , CODE_FOU
							from DOC_ATT with(nolock)
							left outer join MEMOS    with(nolock) on MEMOS.NODEMEMO = DOC_ATT.NM_COMM 
							left outer join HASTUTIL with(nolock) on HASTUTIL.CODE  = DOC_ATT.AUTEUR
                            where NO_DA = '".ReplaceSqlPointeur($type_item)."' and COD_ENR = '".ReplaceSqlPointeur($code_item)."' order by NO_ORDRE ";


        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetMontantMaxDelegationSignataire(&$utilisateur)
    {
        $sql = '
            select
                MNT_ENG,
                MNT_ENGINV
            from
                HASTUTIL with (nolock)
            where
                CODE = \'' . ReplaceSqlPointeur($utilisateur) . '\';';

        $cacheName = __FUNCTION__.'.'.$utilisateur;

        return \SqlWincache::getSql($cacheName, $sql);
    }

    public static function rqGetMontantMaxDelegationSignataireUO(& $utilisateur)
    {
        $sql = 'select distinct MNT_ENG, MNT_ENGINV, MNT_ENG_DAC, MNT_ENGINV_DAC from UO with (nolock) where RESP_HIER = \'' . ReplaceSqlPointeur($utilisateur) . '\';';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqPopupGetAllCountries(&$array_filters, $modeData)
    {
        $sql = '
            select
                ' . ($modeData ? "COUNTRY.CODE_PAYS, COUNTRY.LIBELLE " : "count(*) CPTRESTBL  ") . '
            from
                COUNTRY with(nolock)
            where
                1=1
        ';
        
        $CODE_PAYS = (array_key_exists('CODE_PAYS', $array_filters)) ? trim($array_filters['CODE_PAYS']) : '';
        if ('' != $CODE_PAYS) {
            $sql .= ' and upper(COUNTRY.CODE_PAYS) like upper(\'%' . ReplaceSqlPointeur($CODE_PAYS) . '%\')';
        }
        $LIBELLE = (array_key_exists('LIBELLE', $array_filters)) ? trim($array_filters['LIBELLE']) : '';
        if ('' != $LIBELLE) {
            $sql .= ' and upper(COUNTRY.LIBELLE) like upper(\'%' . ReplaceSqlPointeur($LIBELLE) . '%\')';
        }

        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqPopupGetAllDevises(&$array_filters, $modeData)
    {
        $sql = 'SELECT ' . ($modeData ? "DEVISES.CODE_DEVI, 
            (CASE 
                WHEN EXISTS (SELECT TXTTRAD.TRAD FROM TXTTRAD WITH(NOLOCK) WHERE TXTTRAD.COD_PROD='OXAPROC' AND TXTTRAD.TXT = DEVISES.LIBELLE COLLATE French_BIN AND TXTTRAD.COD_LNG = '". $_SESSION['Session_Langue'] ."') 
                THEN (SELECT TXTTRAD.TRAD FROM TXTTRAD WITH(NOLOCK) WHERE TXTTRAD.COD_PROD='OXAPROC' AND TXTTRAD.TXT = DEVISES.LIBELLE COLLATE French_BIN AND TXTTRAD.COD_LNG = '" . $_SESSION['Session_Langue'] . "')
                ELSE DEVISES.LIBELLE COLLATE French_BIN 
            END) AS LIBELLE " : "count(*) CPTRESTBL  ") . 'from DEVISES with(nolock) where 1=1 ';
        
        $CODE_DEVI = (array_key_exists('CODE_DEVI', $array_filters)) ? trim($array_filters['CODE_DEVI']) : '';
        if ('' != $CODE_DEVI) {
            $sql .= ' and upper(DEVISES.CODE_DEVI) like upper(\'%' . ReplaceSqlPointeur($CODE_DEVI) . '%\')';
        }
        $LIBELLE = (array_key_exists('LIBELLE', $array_filters)) ? trim($array_filters['LIBELLE']) : '';
        if ('' != $LIBELLE) {
            $sql .= ' and upper(DEVISES.LIBELLE) like upper(\'%' . ReplaceSqlPointeur($LIBELLE) . '%\')';
        }
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqPopupGetAllLangues(& $array_filters, $modeData)
    {
        $sql = 'select ' . ($modeData ? "LANGUES.COD_LNG, LANGUES.LIBELLE " : "count(*) CPTRESTBL  ") . 'from LANGUES with(nolock) where 1=1 ';
        
        $COD_LNG = (array_key_exists('COD_LNG', $array_filters)) ? trim($array_filters['COD_LNG']) : '';
        if ('' != $COD_LNG) {
            $sql .= ' and upper(LANGUES.COD_LNG) like upper(\'%' . ReplaceSqlPointeur($COD_LNG) . '%\')';
        }
        $LIBELLE = (array_key_exists('LIBELLE', $array_filters)) ? trim($array_filters['LIBELLE']) : '';
        if ('' != $LIBELLE) {
            $sql .= ' and upper(LANGUES.LIBELLE) like upper(\'%' . ReplaceSqlPointeur($LIBELLE) . '%\')';
        }
        return new RequeteSQL($sql, DB_OXASQLDB);
    }


    public static function rqGetNbrDocRatache($type_it, $code_it)
    {
        if($type_it=='BR')
        {
            $type_it=$type_it.'_RECEP';
        }
        else
        {
            $type_it = $type_it.'_ENTET';
        }
        


        $code_item_9_digit = 0; 

        if ('CA_ENTET' == $type_it || 'DA_ENTET' == $type_it || 'AO_ENTET' == $type_it || 'BR_RECEP' == $type_it || 'FAC_ENTET' == $type_it || 'BS_ENTET' == $type_it)
        {
            $code_item_9_digit = str_pad($code_it, 9, '0', STR_PAD_LEFT);
        }
        
        $sql = "	select NO_DA TYPE_ITEM, COD_ENR CODE_ITEM, NO_ORDRE, TYPE_PJ, TITRE, NOM_FIC, NOM_ORI, AUTEUR, CENTRE, DT_PARUT, DT_VALID, MEMOS.CONTMEMO COMMENT  , CODE_FOU
							from DOC_ATT with(nolock)
							left outer join MEMOS    with(nolock) on MEMOS.NODEMEMO = DOC_ATT.NM_COMM 
							left outer join HASTUTIL with(nolock) on HASTUTIL.CODE  = DOC_ATT.AUTEUR
                            where NO_DA = '" . ReplaceSqlPointeur($type_it) . "' and COD_ENR = '" . ReplaceSqlPointeur($code_item_9_digit) . "' order by NO_ORDRE ";


        return new RequeteSQL($sql, DB_OXASQLDB);
        
    }

    public static function rqGetAllSignatAttendusByBAPBis($no_bap)
    {
        $sql = "select HASTUTIL.CODE, HASTUTIL.NOM, SIG_BAP.ORDRE, (substring(SIG_BAP.DTE_MISE,7,2)+'/'+substring(SIG_BAP.DTE_MISE,5,2)+'/'+substring(SIG_BAP.DTE_MISE,1,4)) DTE_MISE, FAROLES, INFORME, ltrim(rtrim(FAROLES)) ROLE
		from SIG_BAP with (nolock)
		left outer join HASTUTIL with (nolock) on SIG_BAP.SIGNAT = HASTUTIL.CODE
		where SIG_BAP.NO_FAC = '" . ReplaceSqlPointeur($no_bap) . "'
		order by SIG_BAP.ORDRE ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    // Permet de vérifier si il y à bien des délégations pour un User passer en param
    public static function delegByCDE($user)
    {
        
        $req = "SELECT * from SIG_DELEG where SIGNAT = '".$user."' and (DTE_FIN >= '".date('Ymd')."'  or DTE_FIN = '00000000')";       
         
        return new RequeteSQL($req, DB_OXASQLDB);
    }

    public static function rqGetIncident($type_it, $code_it)
    {
        switch($type_it)
        {
            case 'CDE':
                $sql = "select * from FOURNINC where NO_CDE= ".ReplaceSqlPointeur($code_it)."";
                break;
            case 'BR':
                $sql = "select * from FOURNINC where NO_BR= ".ReplaceSqlPointeur($code_it)."";
                break;

            default: 
                $sql = ""; 
        }
           

        return new RequeteSQL($sql, DB_OXASQLDB);
        
    }
    public static function rqGetNbrPj($type_it, $code_it)
    {
        switch($type_it)
        {
            case 'DA':
                $sql = "select * from DA_PIJO where NO_DA= ".ReplaceSqlPointeur($code_it)."";
                break;
            case 'CA':
                $sql = "select * from CA_PIJO where NO_CA= ".ReplaceSqlPointeur($code_it)."";
                break;
            case 'FAC':
                $sql = "select * from FAC_PIJO where NO_FAC= ".ReplaceSqlPointeur($code_it)."";
                break;
            default: 
                $sql = ""; 
        }

        return new RequeteSQL($sql, DB_OXASQLDB);
    }

}