<?php

/**
 * Fonction de retraitement de l'information CS pour obtenir les valeur en fonction des formats
 * Enter description here ...
 * @param unknown_type $res
 */
class SqlCs
{

    public static function traiteResultatCs(& $res, $codeSociete = null)
    {
        if (is_null($codeSociete))
            $codeSociete = $_SESSION['Session_SocUsr'];
        if (is_array($res))
            foreach ($res as & $value) {
                // A RAJOUTER $value['FORMAT']=='T' et s'assurer que tous les types CS s'affichent
                if (BoolSql($value['CONTROLE'])) {
                    if (trim($value['VALEUR']) != '' && trim($value['LIBELLE_CS']) != '') {
                        // Traitement pour remplacer le $value['VALEUR'] qui contient un code cs par le libelle de ce cs
                        $TBLSECTO = (array_key_exists('TBLSECTO', $value)) ? BoolSql($value['TBLSECTO']) : false;
                        
                        if ($lib = SqlCs::rqGetLibCS($value['VALEUR'], $value['TYPE'], ($TBLSECTO ? $codeSociete : ''))->Trouve1())
                            $value['VALEUR'] = $lib['LIB01DICO'];
                    }
                } else if ($value['FORMAT'] == 'L') {
                    if ($value['VALEUR'] == 'O')
                        $value['VALEUR'] = Trad('Oui');
                    else
                        $value['VALEUR'] = Trad('Non');
                } else if ($value['FORMAT'] == 'D') {
                    if ($value['VALEUR'] == ('00/00/0000'))
                        $value['VALEUR'] = '';
                } else if ($value['FORMAT'] == 'M') {
                    if ($value['VALEUR'] == 0 || $value['VALEUR'] == '')
                        $value['VALEUR'] = '';
                    else if ($valeur_memo = SqlPanier::rqGetQueryMemo($value['VALEUR'])->Trouve1())
                        $value['VALEUR'] = $valeur_memo['CONTMEMO'];
                    else
                        $value['VALEUR'] = '';
                }
                $value['VALEUR'] = trim($value['VALEUR']);
            }
    }

    /**
     * Fonction retournant le libelle a partir du code d'un champ supplementaire et de son type
     * 
     * @param string $code
     */
    public static function rqGetLibCS(& $code, & $type, $codeSociete)
    { // REFERENCE INTERDITE sur code societe
                                                                        // Correction Dossier 24649
        $sql = "SELECT LIB01DICO FROM TS_DON with (nolock) WHERE COD_STE = '".ReplaceSqlPointeur($codeSociete)."' AND TYPEDICO = '" . replaceSql($type) . "' AND CODEDICO = '" . replaceSql($code) . "'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    public static function rqGetInfoByType($type)
    { // REFERENCE INTERDITE sur code societe
                                                                        // Correction Dossier 24649
                                                            
        $sql = "SELECT CONTROLE FROM CS_TYPES with (nolock) WHERE TYPE = '".ReplaceSqlPointeur($type)."'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    /**
     * Fonction retournant la requete récupérant pour un article d'une commande sa désignation complémentaire et l'image associée
     */
    public static function rqGetInfoCS($COD_TAB, $NUM_FIX = null)
    {
        $sql = "SELECT CS_CHPS.COD_TAB, CS_CHPS.NUM_FIX, CS_CHPS.NUM_VAR, CS_CHPS.TYPE, 
            (CASE 
                WHEN ISNULL(t.TRAD, '') = ''
                THEN CS_CHPS.NOM COLLATE French_BIN 
                ELSE t.TRAD
            END) AS NOM, CS_CHPS.OBLIG, CS_CHPS.NONDISPO, CS_CHPS.MODAPVAL,  
            (CASE 
                WHEN CS_CHPS.NONMODIF =0x46 
                THEN 0 
                ELSE 1 
            END) AS NONMODIF,    
            ( CASE 
                WHEN CS_CHPS.NONDUPLI =0x46 
                THEN 0 
                ELSE 1 
            END) AS NONDUPLI,  CS_CHPS.MODIFSIGN,  
            (CASE 
                WHEN ISNULL(t2.TRAD, '') = ''
                THEN CS_TYPES.LIBELLE  COLLATE French_BIN
                ELSE t2.TRAD
            END) AS LIBELLE, CS_TYPES.FORMAT, CS_TYPES.CONTROLE, CS_TYPES.ALP_LONG, CS_TYPES.PE_LONG, CS_TYPES.PD_LONG, CS_TYPES.CONTROLE, CS_TYPES.CTRL_STD, CS_TYPES.NOM_CODE, CS_TYPES.NOM_LIB, CS_TYPES.LIB_LONG, CS_TYPES.NEG_AUTO, CS_TYPES.TBLSECTO, CSPARINI.TAB_DESTI, CSPARINI.CHP_DESTI, CSPARINI.TAB_SOUR, CSPARINI.CHP_SOUR  
        FROM CS_CHPS WITH (NOLOCK)
            LEFT OUTER JOIN CS_TYPES WITH (NOLOCK)  
                ON CS_CHPS.TYPE = CS_TYPES.TYPE 
            LEFT OUTER JOIN CSPARINI WITH (NOLOCK)  
                ON CSPARINI.TAB_DESTI = CS_CHPS.COD_TAB 
                    AND CSPARINI.CHP_DESTI = CS_CHPS.NUM_FIX 
            LEFT OUTER JOIN TXTTRAD t WITH(NOLOCK) 
                ON t.COD_PROD = 'OXAPROC' 
                    AND t.TXT = CS_CHPS.NOM COLLATE French_BIN 
                    AND t.COD_LNG = '{$_SESSION['Session_Langue']}'
            LEFT OUTER JOIN TXTTRAD t2 WITH(NOLOCK) 
                ON t2.COD_PROD = 'OXAPROC' 
                    AND t2.TXT = CS_TYPES.LIBELLE COLLATE French_BIN 
                    AND t2.COD_LNG = '{$_SESSION['Session_Langue']}'
        WHERE CS_CHPS.COD_TAB = '" . ReplaceSqlPointeur($COD_TAB) . "' " . (is_null($NUM_FIX) || $NUM_FIX === '' ? '' : " AND CS_CHPS.NUM_FIX = '".ReplaceSqlPointeur($NUM_FIX)."' ") . '
        ORDER BY CS_CHPS.NUM_VAR ';

        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requete récupérant pour un article d'une commande sa désignation complémentaire et l'image associée
     */
    public static function rqGetNbCSModApresVal($COD_TAB)
    {
        $sql = "select count(*) NB_MODAPVAL from CS_CHPS with (nolock) where CS_CHPS.COD_TAB = '".ReplaceSqlPointeur($COD_TAB)."' and CS_CHPS.MODAPVAL = 0x54";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction recherchant la valeur des CS pour un enregistrement
     * 
     * @param string $code_table
     *            : Code de la table contenant le CS
     * @param string $cod_enr
     *            : Numéro d'enregistrement
     * @param int $num_fix
     *            : Identifiant du CS pour la table donnée
     * @return string sql
     */
    public static function rqRechCs($code_tab, $cod_enr, $num_fix = NULL)
    {
        $sql = ' select * from CS_DON with (nolock) ' . 
            " where COD_TAB = '".ReplaceSqlPointeur($code_tab)." '" . " and COD_ENR = '".ReplaceSqlPointeur($cod_enr)."'" . (is_null($num_fix) ? '' : " AND NUM_FIX = '".ReplaceSqlPointeur($num_fix)."' ") . "  order by NUM_FIX";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }

    /**
     * Fonction recherchant pour un CS panier , un numéro de ligne, un code de table donnés la valeur
     * 
     * @param int $id_ligne
     *            : Numéro de la ligne (0 pour l'entete sinon Num de la ligne)
     * @param string $code_table
     *            : Code de la table contenant le CS
     * @param int $num_fix
     *            : Identifiant du CS pour la table donnée
     * @return string sql
     */
    public static function rqRechCsFixe(& $id_panier, $id_ligne, $num_fix = NULL, $code_tab = null)
    {
        //dossier 32034 : le CAST char tronc les mémos a 30 caractères.
        //$sql = ' select NUM_FIX,CAST(VALEUR AS CHAR) AS VALEUR from oxa_users_panier_CS_DON with (nolock) ' . " where id_panier = {$id_panier} " . " and   id_user = {$_SESSION['id_user']} " . "  and   ID_LIGNE = {$id_ligne} " . (is_null($num_fix) ? '' : " AND NUM_FIX = {$num_fix} ") . "  order by VALEUR desc";
        $sql = ' select NUM_FIX,VALEUR AS VALEUR from oxa_users_panier_CS_DON with (nolock) ' . 
            " where id_panier = '".ReplaceSqlPointeur($id_panier)."' " . " and   id_user = {$_SESSION['id_user']} " . "  and   ID_LIGNE = '".ReplaceSqlPointeur($id_ligne)."' " . (is_null($num_fix) ? '' : " AND NUM_FIX = '".ReplaceSqlPointeur($num_fix)."' ") . (is_null($code_tab) ? '' : " AND COD_TAB = '".ReplaceSqlPointeur($code_tab)."' ") .
        "  order by VALEUR desc";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }

    /**
     * Fonction retournant la requête mettant à jour la valeur d'un CS dans oxa_users_panier_CS_DON
     *
     * @param int $num_fix
     *            : Numéro du CS
     * @param int $id_ligne
     *            : Identifiant de la ligne (Si enete valeur 0 sinon le numéro de la ligne)
     * @param string $code_table
     *            : Code de la table de référence
     * @param string $valeur
     *            : Valeur du CS
     * @return string SQL
     */
    public static function rqMajCsFixe(& $num_fix, $id_ligne, $code_table, & $valeur)
    {
        $valeur = trim($valeur); // On trim le resultat du CS OBLIGATOIRE CAR DANS HAPLUS PEUT AVOIR DES ESPACES DEVANT POUR LES NUMERIQUES
        $sql = 'SELECT * FROM oxa_users_panier_CS_DON with (nolock) ' . " WHERE id_panier = {$_SESSION['id_panier']} " . " AND   id_user   = {$_SESSION['id_user']} " . " AND   NUM_FIX   = '".ReplaceSqlPointeur($num_fix)."' " . " AND 	COD_TAB	 = '".ReplaceSqlPointeur($code_table)."'" . " AND   ID_LIGNE = '".ReplaceSqlPointeur($id_ligne)."' ";
        $result = (new RequeteSQL($sql, DB_OXAPROCDB))->Trouve1();

        if ($result) {
            $sql = ' update oxa_users_panier_CS_DON ' . " set VALEUR = '" . ReplaceSqlPointeur($valeur) . '\'' . " where id_panier = {$_SESSION['id_panier']} " . " and   id_user   = {$_SESSION['id_user']} " . " and   NUM_FIX   = '".ReplaceSqlPointeur($num_fix)."' " . " and 	COD_TAB	 = '".ReplaceSqlPointeur($code_table)."'" . " and   ID_LIGNE = '".ReplaceSqlPointeur($id_ligne)."' ";
            return new RequeteSQL($sql, DB_OXAPROCDB);
        } else
            return SqlCs::rqCreCsFixe($_SESSION['id_panier'], $num_fix, $code_table, $id_ligne, $valeur);
    }

    /**
     * Enter description here...
     *
     * @param unknown_type $num_fix
     * @param unknown_type $code_table
     * @param unknown_type $id_ligne
     * @param unknown_type $valeur
     * @return unknown
     */
    public static function rqCreCsFixe(& $id_panier, & $num_fix, & $code_table, & $id_ligne, & $valeur)
    {
        $sql = ' insert into oxa_users_panier_CS_DON (id_user,id_panier,NUM_FIX,COD_TAB,ID_LIGNE,VALEUR) VALUES ' . " ({$_SESSION['id_user']}, '".ReplaceSqlPointeur($id_panier)."', '".ReplaceSqlPointeur($num_fix)."', '".ReplaceSqlPointeur($code_table)."', '".ReplaceSqlPointeur($id_ligne)."','" . ReplaceSqlPointeur($valeur) . "')";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }

    public static function rqSelectCsPanier(& $id_panier, $id_ligne = null)
    {
        if (is_null($id_panier))
            $id_panier = $_SESSION['id_panier'];
        
        $sql = ' select * from oxa_users_panier_CS_DON with(nolock) ' . " where  id_panier 	= '".ReplaceSqlPointeur($id_panier)."' " . " and    id_user   	= {$_SESSION['id_user']} ";
        
        if (null !== $id_ligne && is_numeric($id_ligne))
            $sql .= ' and ID_LIGNE = \'' . ReplaceSqlPointeur($id_ligne).'\'';
            
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }

    /**
     * Fonction retournant la requete permettant de supprimer les CS entete et ligne d'un panier donné
     *
     * @param int $id_panier
     *            : Id du panier dont on souhaite supprimer les CS
     * @return string sql
     */
    public static function rqSupCsPanier(& $id_panier, $id_ligne = null)
    {
        if (is_null($id_panier))
            $id_panier = $_SESSION['id_panier'];
        
        $sql = ' delete from oxa_users_panier_CS_DON ' . " where  id_panier 	= '".ReplaceSqlPointeur($id_panier)."' " . " and    id_user   	= {$_SESSION['id_user']} ";
        
        if (null != $id_ligne && is_numeric($id_ligne))
            $sql .= ' and ID_LIGNE = \'' . ReplaceSqlPointeur($id_ligne).'\'';
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }

    /**
     * Fonction retournant la requete permettant de lister les valeurs possibles pour un CS
     * contrôlé par une table non standard
     * 
     * @param
     *            string type : type du CS dont on recherche les valeurs potentielles
     * @return string sql
     */
    //TODO spec 4205
    public static function rqGetListeValueForCSforTableNotStd(& $type, & $code, $libelle = '', $modeExact = false, $top = false, $societe = false)
    {
        if($top == false )
        {
            $sql = ' select TOP 500 TS_DON.TYPEDICO, TS_DON.CODEDICO as Code, TS_DON.LIB01DICO as Libelle ' . 
                ' from 	TS_DON with(nolock) ' . 
                " where TS_DON.TYPEDICO = '" . ReplaceSqlPointeur($type) . "'".
                ' and TS_DON.DATE_MIN<=CONVERT(VARCHAR,GETDATE(),112) AND (TS_DON.DATE_MAX>=CONVERT(VARCHAR,GETDATE(),112) OR TS_DON.DATE_MAX=\'00000000\')' .
                " and   TS_DON.COD_STE = '" . (Param::isCSSectorise($type) ? ($societe?: $_SESSION['Session_SocUsr']) : '') . "'";
        }
        else
        {
            $sql = ' select TS_DON.TYPEDICO, TS_DON.CODEDICO as Code, TS_DON.LIB01DICO as Libelle ' . 
                ' from 	TS_DON with(nolock) ' . 
                " where TS_DON.TYPEDICO = '" . ReplaceSqlPointeur($type) . "'".
                ' and TS_DON.DATE_MIN<=CONVERT(VARCHAR,GETDATE(),112) AND (TS_DON.DATE_MAX>=CONVERT(VARCHAR,GETDATE(),112) OR TS_DON.DATE_MAX=\'00000000\')' .
                " and   TS_DON.COD_STE = '" . (Param::isCSSectorise($type) ? ($societe?: $_SESSION['Session_SocUsr']) : '') . "'";
        }

        
        if ($modeExact)
            $sql .= " and TS_DON.CODEDICO = '" . ReplaceSqlPointeur($code) . "' ";
        else
            $sql .= " and upper(TS_DON.CODEDICO) LIKE upper('%" . ReplaceSqlPointeur($code) . "%')";
        
        if ($libelle != '')
            $sql .= " and upper(TS_DON.LIB01DICO) LIKE upper('%" . ReplaceSqlPointeur($libelle) . "%')";

        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getSocPanierByLigne($idLigne) 
    {
        $reqSoc = "select CODE_STE 
        from oxa_users_panier with (nolock)
        where id_user='".$_SESSION['id_user']."' and id_panier = (select id_panier from oxa_users_panier_ligne with (nolock) where id_lig='$idLigne')";    

        return new RequeteSQL($reqSoc, DB_OXASQLDB);
    }

    /**
     * Fonction permettant de retourner la requete SQL de sélection, recherche et controle de valeurs pour un type de champ supplémentaire donné
     *
     * @param string $type
     *            : type de CS
     * @param string $mode
     *            : mode d'utilisation de la requete
     * @param string $societe
     *            : société pour laquelle les valeurs sont recherchées
     * @param string $code
     *            : code à contrôler ou a rechercher
     * @param string $libelle
     *            : libéllé à contrôler ou a rechercher
     * @return string sql : requete SQL
     */
    public static function &getListeTableStd()
    {
        $array = array(
            // TYPE => NOM TAB ,champs sélectionnés , Secto? , CHAMP Secto, ,Filtre? ,CHAMP Filtre
            // Table sans filtre sur societe ni un autre attribut
            'ACD' => array(
                'ACCCADRE',
                'CODE_ACD',
                'LIBELLE',
                'CODE_ACD 	as	 	"Code"		,  	LIBELLE		as "Libelle"',
                0,
                '',
                0,
                ''
            ),
            'ARTICLES' => array(
                'ARTICLES',
                'CODE_ART',
                'DESIGN',
                "CODE_ART 	as 		\"Code\"		,	DESIGN 		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'CENTRE' => array(
                'CENTRE',
                'CODE_CTR',
                'RAIS_SOC',
                "CODE_CTR 	as 		\"Code\" 		,	RAIS_SOC	as \"Libelle\"",
                1,
                'CODE_STE',
                0,
                ''
            ),
            'CENTRE_R' => array(
                'CENTRE',
                'CODE_CTR',
                'RAIS_SOC',
                "CODE_CTR 	as 		\"Code\" 		,	RAIS_SOC	as \"Libelle\"",
                1,
                'CODE_STE',
                0,
                ''
            ), // DOA
            'CENTRE_E' => array(
                'CENTRE',
                'CODE_CTR',
                'RAIS_SOC',
                "CODE_CTR 	as 		\"Code\" 		,	RAIS_SOC	as \"Libelle\"",
                1,
                'CODE_STE',
                0,
                ''
            ), // DOA
            'CENTRE_F' => array(
                'CENTRE',
                'CODE_CTR',
                'RAIS_SOC',
                "CODE_CTR 	as 		\"Code\" 		,	RAIS_SOC	as \"Libelle\"",
                1,
                'CODE_STE',
                0,
                ''
            ), // DOA
            
            'CONDPAIE' => array(
                'CONDPAIE',
                'CODE_CDP',
                'LIBELLE',
                "CODE_CDP 	as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'COUNTRY' => array(
                'COUNTRY',
                'CODE_PAYS',
                'LIBELLE',
                "CODE_PAYS as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'DEVISES' => array(
                'DEVISES',
                'CODE_DEVI',
                'LIBELLE',
                "CODE_DEVI as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'FAMILLE' => array(
                'FAMILLE',
                'CODE_FAM',
                'DESIGNAT',
                "CODE_FAM 	as 		\"Code\" 		,	DESIGNAT	as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'FOURNISS' => array(
                'FOURNISS',
                'CODE',
                'RS',
                "CODE 		as 		\"Code\"		,	RS			as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'GRPFOU' => array(
                'GRPFOU',
                'CODE',
                'RS',
                "CODE 		as 		\"Code\"		,	RS			as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'HASTUTIL' => array(
                'HASTUTIL',
                'CODE',
                'NOM',
                "CODE		as 		\"Code\"	 	,	NOM			as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'MARCHES' => array(
                'MARCHES',
                'CODE',
                'LIBELLE',
                "CODE 		as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'MODELIVR' => array(
                'MODELIVR',
                'CODE_MDL',
                'LIBELLE',
                "CODE_MDL 	as 		\"Code\" 		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'MODEPAIE' => array(
                'MODEPAIE',
                'CODE_MDP',
                'LIBELLE',
                "CODE_MDP 	as 		\"Code\" 		,	LIBELLE 	as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'SOCIETE' => array(
                'SOCIETE',
                'CODE_STE',
                'RAIS_SOC',
                "CODE_STE 	as 		\"Code\"		,	RAIS_SOC	as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'SSFAMILL' => array(
                'SSFAMILL',
                'CODE_SFA',
                'DESIGNAT',
                "CODE_SFA 	as 		\"Code\" 		,	DESIGNAT	as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'SSITESTO' => array(
                'SSITESTO',
                'SITE',
                'DESIGN',
                "SITE 		as 		\"Code\"		,	DESIGN		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'TYPDOC' => array(
                'TYPDOC',
                'TYPE',
                'LIBELLE',
                "TYPE 		as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'TYPETVA' => array(
                'TYPETVA',
                'CODE_TTV',
                'LIBELLE',
                "CODE_TTV 	as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'UNITES' => array(
                'UNITES',
                'CODE_UNI',
                'LIBELLE',
                "CODE_UNI 	as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            'FABRICAN' => array(
                'FABRICAN',
                'CODE',
                'RS',
                "CODE 	as 		\"Code\"		,	RS		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
            
            // Table avec nécessite d'un filtre sur attribut
            'SIGBAP' => array(
                'HASTUTIL',
                'CODE',
                'NOM',
                "CODE 		as 		\"Code\"	 	,	NOM			as \"Libelle\"",
                1,
                'CODE_STE',
                1,
                'SIG_BAP'
            ),
            'SIGCDE' => array(
                'HASTUTIL',
                'CODE',
                'NOM',
                "CODE 		as 		\"Code\"	 	,	NOM			as \"Libelle\"",
                1,
                'CODE_STE',
                1,
                'SIG_CDE'
            ),
            'SIGDA' => array(
                'HASTUTIL',
                'CODE',
                'NOM',
                "CODE 		as 		\"Code\"	 	,	NOM			as \"Libelle\"",
                1,
                'CODE_STE',
                1,
                'SIG_DA'
            ),
            'ACHETEUR' => array(
                'HASTUTIL',
                'CODE',
                'NOM',
                "CODE 		as 		\"Code\"	 	,	NOM			as \"Libelle\"",
                1,
                'CODE_STE',
                1,
                'ACHETEUR'
            ),
            'DEMANDEU' => array(
                'HASTUTIL',
                'CODE',
                'NOM',
                "CODE 		as 		\"Code\"	 	,	NOM			as \"Libelle\"",
                1,
                'CODE_STE',
                1,
                'DEMAND'
            ),
            // Table avec présence de filtre sur societe si sectorisation activée
            'SECTIMPU' => array(
                'SECTIMPU',
                'CODE_SI',
                'LIBELLE',
                "CODE_SI 	as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                Param::IsSISecto(),
                'CODE_STE',
                0,
                ''
            ),
            'SSCPTANA' => array(
                'SSCPTANA',
                'CODE_SCA',
                'LIBELLE',
                "CODE_SCA 	as 		\"Code\" 		,	LIBELLE		as \"Libelle\"",
                Param::IsSCASecto(),
                'CODE_STE',
                0,
                ''
            ),
            'CODECOMP' => array(
                'CODECOMP',
                'CODE_CDC',
                'LIBELLE',
                "CODE_CDC 	as 		\"Code\" 		,	LIBELLE		as \"Libelle\"",
                Param::IsCDCSecto(),
                'CODE_STE',
                0,
                ''
            ),
            'CENTRANA' => array(
                'CENTRANA',
                'CODE_CTA',
                'LIBELLE',
                "CODE_CTA 	as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                Param::IsSCASecto(),
                'CODE_STE',
                0,
                ''
            ),
            'DIRECTIO' => array(
                'DIRECTIO',
                'CODE_DIR',
                'LIBELLE',
                "CODE_DIR 	as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                Param::IsSISecto(),
                'CODE_STE',
                0,
                ''
            ),
            'UO' => array(
                'UO',
                'CODE_UO',
                'LIB_UO',
                "CODE_UO 	as 		\"Code\"		,	LIB_UO		as \"Libelle\"",
                Param::IsUOSecto(),
                'CODE_STE',
                0,
                ''
            ),
            'LANGUES' => array(
                'LANGUES',
                'COD_LNG',
                'LIBELLE',
                "COD_LNG 	as 		\"Code\"		,	LIBELLE		as \"Libelle\"",
                0,
                '',
                0,
                ''
            ),
        );
        
        return $array;
    }

    public static function getRqForCS($type, $mode = 'liste', $societe = '', $code = '', $libelle = '')
    {
        $array = SqlCs::getListeTableStd();
        
        // Initialisation de la première partie de la requete, présente qq soit le mode d'appel
        $sql = " select {$array[$type][3]}  from {$array[$type][0]} with(nolock) where 1=1 ";
        if (1 == $array[$type][6])
            $sql .= " and  {$array[$type][7]}=0x54 ";
     
        // $societe = array('S' => $_SESSION['Session_SocUsr']);
        if (1 == $array[$type][4]) {
            if (is_array($societe)) {
                $query = '';
                foreach ($societe as & $data)
                    if ($data['type'] == 'S') {
                        $query .= ($query == '' ? '' : ' OR ') . " {$array[$type][5]} = '" . ReplaceSqlPointeur($data['value']) . "' ";
                    } else if ($data['type'] == 'G') { // LIAIDON SUR LES GROUPES
                        $query .= ($query == '' ? '' : ' OR ') . " {$array[$type][5]} IN (select CODE_STE from SOCIETE with (nolock) where GROUPE = '" . ReplaceSqlPointeur($data['value']) . "') ";
                    }
                
                if ($query != '')
                    $sql .= ' and (' . $query . ') ';
            } else
                $sql .= " and  {$array[$type][5]} = '" . ReplaceSqlPointeur($societe) . "' ";
        }
        
        switch ($mode) {
            case 'recherche': // mode recherche de données
                if ($code != '')
                    $sql .= " and upper({$array[$type][1]}) LIKE '%" . strtoupper(ReplaceSqlPointeur($code)) . "%'";
                if ($libelle != '')
                    $sql .= " and upper({$array[$type][2]}) LIKE '%" . strtoupper(ReplaceSqlPointeur($libelle)) . "%'";
                break;
            case 'controle': // mode controle d'existence
                $sql .= " and {$array[$type][1]} = '" . ReplaceSqlPointeur($code) . '\'';
                break;
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requete permettant de vérifier l'existenve d'une valeur de CS
     * pour une table non standard donnée
     * 
     * @param string $type
     *            : type du CS dont on souhaite vérifier l'existence de la valeur
     * @param string $code
     * @return string sql
     */
    public static function rqIsValueTS_DON(& $type, & $code, &$code_ste = null)
    {
        $sql = ' select TS_DON.TYPEDICO, TS_DON.CODEDICO as "Code", TS_DON.LIB01DICO as "Libelle"' . ' from 	TS_DON with(nolock)' . " where 	TS_DON.TYPEDICO = '" . ReplaceSqlPointeur($type) . '\'' . " and 	TS_DON.CODEDICO = '" . ReplaceSqlPointeur($code) . '\'' . " and   TS_DON.COD_STE = '" . (Param::isCSSectorise($type) ? ($code_ste ?? $_SESSION['Session_SocUsr']) : '') . "'";

        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requete permettant d'initialiser un CS
     * 
     * @param string $tab_source
     *            : Table source
     * @param int $cs_source
     *            : CS de la table source
     * @param string $code_enr
     *            : Code de l'enregistrement en question
     */
    public static function rqSelectValueCSforInit($tab_source, $cs_source, $code_enr)
    {
        // Si le code du cs source n'est pas défini
        if (! is_numeric($cs_source)) {
            $cs_source = 0;
            $error = new ErrorHandle();
            $error->logError("Fonction : SqlCs::rqSelectValueCSforInit, le paramètre cs_source n'est pas défini, paramètre tab_source = {$tab_source}");
        }
        
        if ('CA_ENTET' === $tab_source || 'CA_LIGNE' === $tab_source) {
            // On complète le numéro de commande sur 9 position en préfixant avec des 0
            for ($i = mb_strlen($code_enr); $i < 9; $i ++)
                $code_enr = '0' . (string) $code_enr;
        } elseif ('CODECOMP' === $tab_source){
            if (Param::IsCDCSecto())
                $code_enr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $code_enr;
            else
                $code_enr = str_pad($code_enr, 7, ' ', STR_PAD_LEFT);
        } elseif ('DOA' === $tab_source){
            $code_enr = str_pad($code_enr, 9, 0, STR_PAD_LEFT);
        }
        
        $sql = ' select CS_DON.COD_TAB, CS_DON.COD_ENR, CS_DON.NUM_FIX, CS_DON.VALEUR, CS_CHPS.TYPE	';
        $sql .= ' from  CS_DON with(nolock), CS_CHPS with(nolock), CS_TYPES with (nolock)			';
        $sql .= " where CS_DON.COD_TAB 	= '".ReplaceSqlPointeur($tab_source)."'			";
        $sql .= " and   CS_DON.NUM_FIX 	= '".ReplaceSqlPointeur($cs_source)."'   			";
        $sql .= " and   CS_CHPS.NUM_FIX = '".ReplaceSqlPointeur($cs_source)."'   			";
        $sql .= ' and   CS_DON.COD_TAB 	= CS_CHPS.COD_TAB   										';
        $sql .= " and   CS_DON.COD_ENR 	= '".ReplaceSqlPointeur($code_enr)."'				";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqIsValueCSExiste($tab_source, $cs_source, $code_enr, $valeur)
    {
        // Si le code du cs source n'est pas défini
        if (! is_numeric($cs_source)) {
            $cs_source = 0;
            $error = new ErrorHandle();
            $error->logError("Fonction : SqlCs::rqSelectValueCSforInit, le paramètre cs_source n'est pas défini, paramètre tab_source = {$tab_source}");
        }
        
        $sql = ' select * ';
        $sql .= ' from  CS_DON with(nolock) ';
        $sql .= " where CS_DON.COD_TAB 	= '".ReplaceSqlPointeur($tab_source)."'";
        $sql .= " and   CS_DON.NUM_FIX 	= '".ReplaceSqlPointeur($cs_source)."' ";
        $sql .= " and   CS_DON.VALEUR 	= '".ReplaceSqlPointeur($valeur) . "'";
        
        if ('CA_ENTET' === $tab_source || 'CA_LIGNE' === $tab_source) {
            // On complète le numéro de commande sur 9 position en préfixant avec des 0
            for ($i = mb_strlen($code_enr); $i < 9; $i ++)
                $code_enr = '0' . (string) $code_enr;
        } elseif ('CODECOMP' === $tab_source)
            
            if (is_null($code_enr)) {
                if (Param::IsCDCSecto())
                    $code_enr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT);
                else
                    $code_enr = '';
                $sql .= " and   CS_DON.COD_ENR 	LIKE '" . ReplaceSqlPointeur($code_enr) . "%' ";
            } else {
                if (Param::IsCDCSecto())
                    $code_enr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $code_enr;
                else
                    $code_enr = str_pad($code_enr, 7, ' ', STR_PAD_LEFT);
                
                $sql .= " and   CS_DON.COD_ENR 	= '" . ReplaceSqlPointeur($code_enr) . "' ";
            }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction permettant de récupérer tous les CS pour un panier d'un utilsateur
     *
     * @param int $id_user
     * @param int $id_panier
     * @return string sql
     */
    public static function rqGetAllCSPanierUser(& $id_user, & $id_panier)
    {
        $sql = ' select NUM_FIX, CAST(VALEUR AS TEXT) AS VALEUR, ID_LIGNE, COD_TAB from oxa_users_panier_CS_DON with (nolock) ' . " where id_panier 	= '".ReplaceSqlPointeur($id_panier)."' " . " and   id_user   	= '".ReplaceSqlPointeur($id_user)."' order by COD_TAB, NUM_FIX ";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }

    /**
     * Fonction retournant la requête permettant de supprimer toutes les lignes de CS qui ne sont liées à aucune ligne de panier
     * ou bien celle différente du type entête ou ligne et ce afin d'éviter les erreurs d'insertion lors des enregsitrements ou transferts de panier
     */
    public static function rqDeleteCSwithoutLigne()
    {
        $sql = "  delete from oxa_users_panier_CS_DON where
					 (
					  COD_TAB = 'DA_LIGNE'
					  and
					  not exists (	select id_lig 	  from oxa_users_panier_ligne with(nolock) where id_lig 		=  ID_LIGNE 
									union  
									select OLD_ID_LIG from oxa_users_panier_ligne with(nolock) where OLD_ID_LIG  	=  ID_LIGNE
								)
					 )
					 or (COD_TAB != 'DA_LIGNE' and COD_TAB != 'DA_ENTET')";
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }
    
    /**
     * Fonction retournant les numéros des champs suplémentaires contenant les valeurs des axes d'imputations liés au CDC
     *
     * @return string SQL
     */
    public static function rqGetListeCSFromCDC()
    {
        $sql = " select CHP_DESTI, CHP_SOUR from CSPARINI with(nolock) 	 where TAB_DESTI = 'DA_LIGNE' and TAB_SOUR = 'CODECOMP' and CHP_SOUR in (select  LIB01DICO NUM_CS from DILISPEC with(nolock)   where CODEDICO like 'NCS_CODECOMP_%' and LIB01DICO <> '0') ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    /**
     * Fonction pour le client 130 : retournant la valeur du champs champs supplémentaire CDC pour l'initialisation du CDC
     * 
     * @param string $code_enr
     * @return RequeteSQL
     */
    public static function rqGetValueCSSectimp($code_enr)
    {
        $sql = "select * from CS_DON with (nolock) 
                where COD_TAB='SECTIMPU' and 
                      NUM_FIX = (select RTRIM(LTRIM(LIB01DICO)) LIB01DICO from DILISPEC with (nolock) where TYPEDICO='PARNCS0130' and CODEDICO='NCS_SECTIMPU_REGION') and 
                      COD_ENR = '".ReplaceSqlPointeur($code_enr)."'
                ";

        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetListeCSbyListNumCs($code_enr, $tab_source, $liste_num_cs)
    {
        if ('CODECOMP' == $tab_source) {
            if (Param::IsCDCSecto())
                $code_enr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $code_enr;
            else
                $code_enr = str_pad($code_enr, 7, ' ', STR_PAD_LEFT);
        }
        if ('SECTIMPU' == $tab_source) {
            if (Param::IsSISecto())
                $code_enr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $code_enr;
            else
                $code_enr = str_pad($code_enr, 7, ' ', STR_PAD_LEFT);
        }
        if ('SSCPTANA' == $tab_source) {
            if (Param::IsSCASecto())
                $code_enr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $code_enr;
            else
                $code_enr = str_pad($code_enr, 7, ' ', STR_PAD_LEFT);
        }
        $sql = ' select * from CS_DON with (nolock) ' . " WHERE COD_TAB  = '".ReplaceSqlPointeur($tab_source)."' " . " AND   COD_ENR  = '".ReplaceSqlPointeur($code_enr)."'" . " AND   NUM_FIX  in ({$liste_num_cs}) ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetInfoCSForImputCdc($COD_TAB, $NUM_FIX = null)
    {
        $sql = ' select CS_CHPS.COD_TAB	, ' . ' CS_CHPS.NUM_FIX			, ' . ' CS_CHPS.NUM_VAR			, ' . ' CS_CHPS.TYPE				, ' . ' CS_CHPS.NOM				, ' . ' CS_CHPS.OBLIG				, ' . ' CS_CHPS.NONDISPO			, ' . ' CS_CHPS.MODAPVAL			, ' . ' CS_CHPS.NONMODIF			, ' . ' CS_CHPS.NONDUPLI			, ' . '  CS_TYPES.LIBELLE			, ' . '	CS_TYPES.FORMAT 		,' . ' CS_TYPES.CONTROLE,' . '	CS_TYPES.ALP_LONG		, ' . '	CS_TYPES.PE_LONG		, ' . '	CS_TYPES.PD_LONG		, ' . '	CS_TYPES.CONTROLE		, ' . '	CS_TYPES.CTRL_STD		, ' . '	CS_TYPES.NOM_CODE		, ' . '	CS_TYPES.NOM_LIB		, ' . '	CS_TYPES.LIB_LONG		, ' . '	CS_TYPES.NEG_AUTO		, ' . '       CS_TYPES.TBLSECTO, ' . ' CSPARINI.TAB_DESTI			, ' . ' CSPARINI.CHP_DESTI			, ' . ' CSPARINI.TAB_SOUR			, ' . ' CSPARINI.CHP_SOUR			, ' . ' DILISPEC.CODEDICO  		  ' . ' from CS_TYPES with (nolock), DILISPEC with (nolock), CS_CHPS with (nolock)' . ' left outer join CSPARINI with (nolock) on CSPARINI.TAB_DESTI = CS_CHPS.COD_TAB and CSPARINI.CHP_DESTI = CS_CHPS.NUM_FIX ' . " where CS_CHPS.COD_TAB = '".ReplaceSqlPointeur($COD_TAB)."' " . (is_null($NUM_FIX) ? '' : " AND CS_CHPS.NUM_FIX = '".ReplaceSqlPointeur($NUM_FIX)."' ") . ' and   CS_CHPS.TYPE    = CS_TYPES.TYPE' . " and   DILISPEC.TYPEDICO = 'PARAMCC' and DILISPEC.CODEDICO like 'NCS_CODECOMP_%' and LIB01DICO <> '0' and rtrim(ltrim(LIB01DICO)) = rtrim(ltrim(cast(CS_CHPS.NUM_FIX   as varchar(200)))) " . ' order by CS_CHPS.NUM_VAR ';
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetInfoCSDALigneForImputCdc($COD_TAB, $NUM_FIX = null)
    {
        $sql = ' select CS_CHPS.COD_TAB	, ' . ' CS_CHPS.NUM_FIX			, ' . ' CS_CHPS.NUM_VAR			, ' . ' CS_CHPS.TYPE				, ' . ' CS_CHPS.NOM				, ' . ' CS_CHPS.OBLIG				, ' . ' CS_CHPS.NONDISPO			, ' . ' CS_CHPS.MODAPVAL			, ' . ' CS_CHPS.NONMODIF			, ' . ' CS_CHPS.NONDUPLI			, ' . '  CS_TYPES.LIBELLE			, ' . '	CS_TYPES.FORMAT 		,' . ' CS_TYPES.CONTROLE,' . '	CS_TYPES.ALP_LONG		, ' . '	CS_TYPES.PE_LONG		, ' . '	CS_TYPES.PD_LONG		, ' . '	CS_TYPES.CONTROLE		, ' . '	CS_TYPES.CTRL_STD		, ' . '	CS_TYPES.NOM_CODE		, ' . '	CS_TYPES.NOM_LIB		, ' . '	CS_TYPES.LIB_LONG		, ' . '	CS_TYPES.NEG_AUTO		, ' . '       CS_TYPES.TBLSECTO, ' . ' CSPARINI.TAB_DESTI			, ' . ' CSPARINI.CHP_DESTI			, ' . ' CSPARINI.TAB_SOUR			, ' . ' CSPARINI.CHP_SOUR			, ' . ' DILISPEC.CODEDICO  		  ' . ' from CS_TYPES with (nolock), DILISPEC with (nolock), CS_CHPS with (nolock)' . ' left outer join CSPARINI with (nolock) on CSPARINI.TAB_DESTI = CS_CHPS.COD_TAB and CSPARINI.CHP_DESTI = CS_CHPS.NUM_FIX ' . " where CS_CHPS.COD_TAB = '".ReplaceSqlPointeur($COD_TAB)."' " . (is_null($NUM_FIX) ? '' : " AND CS_CHPS.NUM_FIX = '".ReplaceSqlPointeur($NUM_FIX)."' ") . ' and   CS_CHPS.TYPE    = CS_TYPES.TYPE' . " and   DILISPEC.TYPEDICO = 'PARAMCC' and DILISPEC.CODEDICO like 'NCS_DA_LIGNE_%' and LIB01DICO <> '0' " . ' and rtrim(ltrim(LIB01DICO)) = rtrim(ltrim(cast(CS_CHPS.NUM_FIX   as varchar(200))))' . ' order by CS_CHPS.NUM_VAR ';
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * NE PAS METTRE DE REFERENCE ICI
     *
     * @param unknown $code_enr
     * @param unknown $cs_propriete
     * @return RequeteSQL
     */
    public static function rqGetParamCSForCDCImput($code_enr, $cs_propriete)
    {
        if (Param::IsCDCSecto())
            $code_enr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $code_enr;
        else
            $code_enr = str_pad($code_enr, 7, ' ', STR_PAD_LEFT);
        
        $sql = " select VALEUR from CS_DON with(nolock)	where CS_DON.COD_ENR = '" . ReplaceSqlPointeur($code_enr) . "' and CS_DON.COD_TAB = 'CODECOMP' and CS_DON.NUM_FIX = (select LIB01DICO NUM_CS from DILISPEC with(nolock) where TYPEDICO ='PARAMCC' and CODEDICO = '".ReplaceSqlPointeur($cs_propriete)."') ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête permettant de dupliquer les CS d'une ligne panier
     *
     * @param int $id_user
     *            : Identifiant utilisateur
     * @param int $id_panier
     *            : Identifiant du panier
     * @param int $id_ligne_source
     *            : Identifiant de la ligne source
     * @param int $id_ligne_cible
     *            : Identifiant de la nouvelle ligne créée
     * @return string sql
     */
    public static function rqDuplicateCsLigne($id_user, $id_panier, $id_ligne_source, $id_ligne_cible)
    {
        $sql = ' insert into oxa_users_panier_CS_DON (id_user,id_panier,NUM_FIX,COD_TAB,ID_LIGNE,VALEUR)  ' . " select id_user, id_panier, NUM_FIX, COD_TAB, '".ReplaceSqlPointeur($id_ligne_cible)."', VALEUR from oxa_users_panier_CS_DON where id_user = '".ReplaceSqlPointeur($id_user)."' and id_panier = '".ReplaceSqlPointeur($id_panier)."' and ID_LIGNE = '".ReplaceSqlPointeur($id_ligne_source)."'";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }

    /**
     * Fonction retournant la requête permettant de sélectionner la valeur d'un CS présent sur les lignes CDE et initialisés par la les CS de ligne de DA
     *
     * @param int $code_enr
     *            : Numéro de la commande dont on recherche les valeurs de CS
     * @param int $num_fix
     *            : Numéro du CS dont on recherche la valeur
     * @return string sql
     */
    public static function rqGetValueCSFromCDEinitByDA($code_enr, & $num_fix)
    {
        // Le code enregistré est sur 9 caractères, il est nécessaire de préfixer autant que nécessaire avec des 0
        $code_enr = str_pad($code_enr, 9, '0', STR_PAD_LEFT);
        $sql = " select * from CS_DON with(nolock) where COD_TAB = 'CA_LIGNE' and COD_ENR like '".ReplaceSqlPointeur($code_enr)."%' and NUM_FIX in (select CHP_DESTI from CSPARINI with(nolock)  where TAB_SOUR='DA_LIGNE' and TAB_DESTI='CA_LIGNE' and CHP_SOUR   = '".ReplaceSqlPointeur($num_fix)."')  ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Spec 3652 / 3654
     * 
     * @return RequeteSQL
     */
    public static function rqGetNumFixCsCtrLivForSiNge()
    {
        $sql = "
            select
                LIB01DICO
            from
                DILISPEC with(nolock)
            where
                TYPEDICO = 'PARAMNGE'
                and CODEDICO = 'NCS_SECTIMPU_CODE_CTR_LIVR'
        ";

        $cacheName = __FUNCTION__;

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

    public static function rqGetNumFixCsCtrLivForAdrLiv()
    {
        $sql = "
            select
                LIB01DICO
            from
                DILISPEC with(nolock)
            where
                TYPEDICO ='PARAMLEONG'
                and CODEDICO='NCS_SECTIMPU_TYPE_CHANTIER'
        ";

        $cacheName = __FUNCTION__;

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

    public static function rqGetNumFixCsCtrLivForSiLeonG()
    {
        $sql = "
            select
                LIB01DICO
            from
                DILISPEC with(nolock)
            where
                TYPEDICO ='PARAMLEONG'
                and CODEDICO='NCS_SECTIMPU_TYPE_CHANTIER'
        ";

        $cacheName = __FUNCTION__;

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

    public static function rqGetValCsDonForNumFix($codeTab, $codeSoc, $padding, & $codeEnr, & $numFix)
    {
        // Le code enregistré est sur 7 caractères, il est nécessaire de préfixer autant que nécessaire avec des 0
        $codeSoc = str_pad($codeSoc, $padding, ' ', STR_PAD_RIGHT);
        $sql = " select VALEUR from CS_DON with(nolock) where COD_TAB = '".ReplaceSqlPointeur($codeTab)."' and COD_ENR = '".ReplaceSqlPointeur($codeSoc).ReplaceSqlPointeur($codeEnr)."' and NUM_FIX = '".ReplaceSqlPointeur($numFix)."'  ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetCsCorrespondanceSource($tableSource, $tableCible, & $numFixCible)
    {
        $sql = " select * from CSPARINI with (nolock) where TAB_SOUR  = '".ReplaceSqlPointeur($tableSource)."'  and  TAB_DESTI = '".ReplaceSqlPointeur($tableCible)."'  and   CHP_DESTI = '".ReplaceSqlPointeur($numFixCible)."'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    public static function rqDeleteCSCdePanier($idPanier, $idUser)
    {
        $sql = " delete from oxa_users_panier_CS_DON where id_panier = '".ReplaceSqlPointeur($idPanier)."' and id_user = '".ReplaceSqlPointeur($idUser)."' and (COD_TAB = 'CA_ENTET' or COD_TAB = 'CA_LIGNE')";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetCsDispoPourTable(& $nomTable)
    {
        $sql = "select NUM_FIX, TYPE, NOM from CS_CHPS with(nolock) where COD_TAB = '".ReplaceSqlPointeur($nomTable)."' and  NONDISPO = 0x46 order by NUM_FIX";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetNumDA()
    {
        $sql = "select CHP_SOUR from CSPARINI with(nolock) where TAB_DESTI='CA_ENTET' and TAB_SOUR='DA_ENTET' 
        and CHP_DESTI=cast((select LIB01DICO from DILISPEC with(nolock) where TYPEDICO='PARAMLEONG' and CODEDICO='NCS_CA_ENTET_NO_DA') as int)";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetValeurEtNumFixDesti($tabDesti, $tabSour, $codeEnr)
    {
        if ($tabSour == 'CODECOMP') {
            if (Param::IsCDCSecto())
                $codeEnr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $codeEnr;
            else
                $codeEnr = str_pad('', 7, ' ', STR_PAD_LEFT).$codeEnr;
        }
        if ($tabSour == 'SECTIMPU') {
            if (Param::IsSISecto())
                $codeEnr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $codeEnr;
            else
                $codeEnr = str_pad('', 7, ' ', STR_PAD_LEFT).$codeEnr;
        }
        if ($tabSour == 'SSCPTANA') {
            if (Param::IsSCASecto())
                $codeEnr = str_pad($_SESSION['Session_SocUsr'], 7, ' ', STR_PAD_RIGHT) . $codeEnr;
            else
                $codeEnr = str_pad('', 7, ' ', STR_PAD_LEFT).$codeEnr;
        }
        $sql = "select CHP_DESTI, VALEUR from CSPARINI with(nolock) left join CS_DON with(nolock) on CSPARINI.TAB_SOUR=CS_DON.COD_TAB and CSPARINI.CHP_SOUR=CS_DON.NUM_FIX where TAB_DESTI='".ReplaceSqlPointeur($tabDesti)."' and TAB_SOUR='".ReplaceSqlPointeur($tabSour)."' and COD_ENR='".ReplaceSqlPointeur($codeEnr)."'";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requête mettant à jour la valeur d'un CS dans CS_DON
     *
     * @param int $num_fix
     *            : Numéro du CS
     * @param string $code_enr
     *            : Identifiant du document
     * @param string $code_table
     *            : Code de la table de référence
     * @param string $valeur
     *            : Valeur du CS
     * @return string SQL
     */
    public static function rqMajCs($num_fix, $code_enr, $code_table, $valeur)
    {
        $code_enr = $code_table == 'DOA' ? str_pad($code_enr, 9, 0, STR_PAD_LEFT) : $code_enr;
        
        $valeur = trim($valeur); // On trim le resultat du CS OBLIGATOIRE CAR DANS HAPLUS PEUT AVOIR DES ESPACES DEVANT POUR LES NUMERIQUES
        $sql = 'SELECT * FROM CS_DON with (nolock) ' . " WHERE NUM_FIX   = '".ReplaceSqlPointeur($num_fix)."' " . " AND 	COD_TAB	 = '".ReplaceSqlPointeur($code_table)."'" . " AND   COD_ENR = '".ReplaceSqlPointeur($code_enr)."' ";
        
        
        // echo 'jenregistrelolo';exit;
        if ($result = (new RequeteSQL($sql, DB_OXASQLDB))->Trouve1()) {
            
            $ListeCs = SqlCs::rqGetInfoCS($code_table,$num_fix)->Trouve1();
          
            if($ListeCs['TYPE']=='LOGIQUE')
            {
                if($valeur =='on')
                {
                    $valeur =1;
                }else
                {
                    $valeur =0;
                }

            }
            if($ListeCs['TYPE']=='MEMO')
            {
                $sql = "select * from CS_DON where NUM_FIX   = '".ReplaceSqlPointeur($num_fix)."' " . " and 	COD_TAB	 = '".ReplaceSqlPointeur($code_table)."'" . " and   COD_ENR = '".ReplaceSqlPointeur($code_enr)."' ";
                $resutat = (new RequeteSQL($sql, DB_OXASQLDB))->Trouve1()['VALEUR'];

                $sql = ' update MEMOS ' . " set CONTMEMO = '" . ReplaceSqlPointeur($valeur) . '\'' . " where NODEMEMO   = '".ReplaceSqlPointeur($resutat)."' ";
                return new RequeteSQL($sql, DB_OXASQLDB);
            }else
            {
                $sql = ' update CS_DON ' . " set VALEUR = '" . ReplaceSqlPointeur($valeur) . '\'' . " where NUM_FIX   = '".ReplaceSqlPointeur($num_fix)."' " . " and 	COD_TAB	 = '".ReplaceSqlPointeur($code_table)."'" . " and   COD_ENR = '".ReplaceSqlPointeur($code_enr)."' ";
                return new RequeteSQL($sql, DB_OXASQLDB);
            }

         
        } else{
            // echo 'jenregistre';exit;
            return SqlCs::rqCreCs($num_fix, $code_table, $code_enr, $valeur);
        }
    }

    /**
     * Fonction retournant la requête créant une nouvelle entrée d'un CS dans CS_DON
     *
     * @param int $num_fix
     *            : Numéro du CS
     * @param string $code_table
     *            : Code de la table de référence
     * @param string $code_enr
     *            : Identifiant du document
     * @param string $valeur
     *            : Valeur du CS
     * @return string SQL
     */
    public static function rqCreCs($num_fix, $code_table, $code_enr, $valeur)
    {
        $code_enr = $code_table == 'DOA' ? str_pad($code_enr, 9, 0, STR_PAD_LEFT) : $code_enr;
        
        $ListeCs = SqlCs::rqGetInfoCS($code_table,$num_fix)->Trouve1();
        if($ListeCs['TYPE']=='MEMO')
        {
           $numCS = Memos::getNumMemo();
            
            $sql = 'insert into MEMOS (NODEMEMO,CONTMEMO) VALUES ' . " ('".ReplaceSqlPointeur($numCS)."','".ReplaceSqlPointeur($valeur)."')";
            (new RequeteSQL($sql, DB_OXASQLDB))->ExecuteSql();
           $valeur = $numCS;
        }
        
        $sql = 'insert into CS_DON (COD_TAB,COD_ENR,NUM_FIX,VALEUR) VALUES ' . " ('".ReplaceSqlPointeur($code_table)."','".ReplaceSqlPointeur($code_enr)."','".ReplaceSqlPointeur($num_fix)."','" . ReplaceSqlPointeur($valeur) . "')";
        
    
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getValueCsByCodeEnr($code_table, $code_enr)
    {
        $code_enr = $code_table == 'DOA' ? str_pad($code_enr, 9, 0, STR_PAD_LEFT) : $code_enr;
        $tmp = [];
        $sql = "SELECT * FROM CS_DON  with (nolock)   WHERE COD_TAB	 = '".ReplaceSqlPointeur($code_table)."' AND   COD_ENR = '".ReplaceSqlPointeur($code_enr)."'";
        $result = (new RequeteSQL($sql, DB_OXASQLDB))->TrouveTous();
     
        foreach($result as $res)
        {
            $tmp[$res['NUM_FIX']] = $res;
        }

        $ListeCs = SqlCs::rqGetInfoCS($code_table)->TrouveTous();
 
        foreach($ListeCs as $cs)
        {
            if(!isset($tmp[$cs['NUM_FIX']]))
            {
                $tmp[$cs['NUM_FIX']]= $cs;
            }
            if($cs['TYPE'] == 'MEMO')
            {
               $sql = "SELECT * FROM MEMOS  with (nolock)    WHERE NODEMEMO = '".$tmp[$cs['NUM_FIX']]['VALEUR']."'";
               $result = (new RequeteSQL($sql, DB_OXASQLDB))->Trouve1();
            
               $tmp[$cs['NUM_FIX']]['VALEUR'] = $result['CONTMEMO'];
               $tmp[$cs['NUM_FIX']]['MEMO'] = 'MEMO';
            }
            $tmp[$cs['NUM_FIX']]['LIBELLE'] = $cs['NOM'];
            
        }
        
        
        return $tmp;
         
    }

    public static function duplicateCS($codeEnr, $newCodeEnr, $codeTable)
    {
        $codeEnr = $codeTable == 'DOA' ? str_pad($codeEnr, 9, 0, STR_PAD_LEFT) : $codeEnr;
        $newCodeEnr = $codeTable == 'DOA' ? str_pad($newCodeEnr, 9, 0, STR_PAD_LEFT) : $newCodeEnr;
        $tmp = [];
        $sql = "SELECT * FROM CS_DON  with (nolock)   WHERE COD_TAB	 = '".ReplaceSqlPointeur($codeTable)."' AND   COD_ENR = '".ReplaceSqlPointeur($codeEnr)."'";
        $result = (new RequeteSQL($sql, DB_OXASQLDB))->TrouveTous();
        foreach($result as $res)
        {
            $tmp[$res['NUM_FIX']] = $res;
        }
     
        $ListeCs = SqlCs::rqGetInfoCS($codeTable)->TrouveTous();
        foreach($ListeCs as $cs)
        {
            if(!$cs['NONDUPLI'])
            {
                if($cs['TYPE'] == 'MEMO')
                {
                   $sql = "SELECT * FROM MEMOS  with (nolock)    WHERE NODEMEMO = '".$tmp[$cs['NUM_FIX']]['VALEUR']."'";
                   $result = (new RequeteSQL($sql, DB_OXASQLDB))->Trouve1();
                
                   $tmp[$cs['NUM_FIX']]['VALEUR'] = $result['CONTMEMO'];
                   $tmp[$cs['NUM_FIX']]['MEMO'] = 'MEMO';
                }
                $tmp[$cs['NUM_FIX']]['LIBELLE'] = $cs['NOM'];
            }else
            {
                unset($tmp[$cs['NUM_FIX']]);
            }
           
            
        }
        

        foreach($tmp as $newCS)
        {
            if($newCS['VALEUR']!='')
            {
                SqlCs::rqCreCs($newCS['NUM_FIX'], $codeTable, $newCodeEnr, $newCS['VALEUR'])->ExecuteSql();
            }

        }
    }

    public static function deleteCS($codeEnr, $codeTable)
    {
        $tmp = [];
        $sql = "SELECT * FROM CS_DON  with (nolock)   WHERE COD_TAB	 = '".ReplaceSqlPointeur($codeTable)."' AND   COD_ENR = '".ReplaceSqlPointeur($codeEnr)."'";
        $result = (new RequeteSQL($sql, DB_OXASQLDB))->TrouveTous();
        foreach($result as $res)
        {
            $tmp[$res['NUM_FIX']] = $res;
        }
     
        $ListeCs = SqlCs::rqGetInfoCS($codeTable)->TrouveTous();
        foreach($ListeCs as $cs)
        {
            if($cs['TYPE'] == 'MEMO')
            {
               $sql = "delete FROM MEMOS     WHERE NODEMEMO = '".$tmp[$cs['NUM_FIX']]['VALEUR']."'";
               $result = (new RequeteSQL($sql, DB_OXASQLDB))->ExecuteSql();
           
            }
            
        }
        $sql = "delete FROM CS_DON  WHERE COD_TAB	 = '".ReplaceSqlPointeur($codeTable)."' AND   COD_ENR = '".ReplaceSqlPointeur($codeEnr)."'";
        $result = (new RequeteSQL($sql, DB_OXASQLDB))->ExecuteSql();
        
    }


    
    //4474
    public static function getInfoCS($codeTab, $codeEnreg)
    {
        $sql = "select NOM, FORMAT, (select case when CS_TYPES.FORMAT='M' then (select CONTMEMO from MEMOS with (nolock) where NODEMEMO=cast(CS_DON.VALEUR as int)) else CS_DON.VALEUR end as VALEUR from CS_DON where CS_DON.COD_TAB = CS_CHPS.COD_TAB and CS_DON.COD_ENR='".ReplaceSqlPointeur($codeEnreg)."' and CS_DON.NUM_FIX = CS_CHPS.NUM_FIX) VALEUR
                from CS_CHPS
                left join CS_TYPES on CS_TYPES.TYPE = CS_CHPS.TYPE where CS_CHPS.COD_TAB = '".ReplaceSqlPointeur($codeTab)."'";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }
    
    public static function getEmailclient_132($codeEnreg)
    {
        $sql = "select * from CS_DON where COD_TAB='SERVICES_ACHATS' and COD_ENR='".ReplaceSqlPointeur($codeEnreg)."' and NUM_FIX = (select LIB01DICO from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0132' and CODEDICO = 'NCS_SERVICES_ACHATS_DEST_MAIL_TRANSMISSION')";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }
    
    public static function getServiceAchatclient_132($codeEnreg)
    {
        $sql = "select * from CS_DON where COD_TAB='DOA' and COD_ENR='".ReplaceSqlPointeur($codeEnreg)."' and NUM_FIX = (select LIB01DICO from DILISPEC with(nolock) where TYPEDICO = 'PARNCS0132' and CODEDICO = 'NCS_DOA_SERVICES_ACHATS')";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }
    
    public static function getCSValueSpecific174($codeEnreg)
    {
        $sql = "SELECT CS.NUM_FIX, CS.VALEUR, SI.LIBELLE
        FROM CS_DON CS WITH (NOLOCK) 
            LEFT JOIN SECTIMPU SI WITH (NOLOCK) ON SI.CODE_SI = CS.VALEUR
        WHERE CS.COD_TAB = 'CENTRE' 
            AND CS.COD_ENR = '" . ReplaceSqlPointeur($codeEnreg) . "' 
            AND CS.NUM_FIX = (
                SELECT LIB01DICO  
                FROM DILISPEC with (nolock) 
                WHERE TYPEDICO = 'PARNCS0174' 
                AND CODEDICO = 'NCS_CENTRE_CODE_SI'
            )
        ";

        return new RequeteSQL($sql, DB_OXAPROCDB);
    }
    
    public static function getCSBR_OXAPROC()
    {
        $sql = "select CS_CHPS.NUM_FIX, VALEUR from CS_CHPS with (nolock)
                left join CS_DON with (nolock) on CS_DON.COD_TAB = 'BR_OXAPROC' and CS_DON.COD_ENR = '".$_SESSION["Session_Login"]."' and CS_DON.NUM_FIX =CS_CHPS.NUM_FIX
                where CS_CHPS.COD_TAB = 'BR_RECEP'";
        
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }
    
    public static function getCsPanierSEM($codeSte)
    {
        $sql = "select * 
                from CS_DON 
                where COD_TAB = 'SOCIETE' and COD_ENR = '".ReplaceSqlPointeur($codeSte)."' and NUM_FIX = (SELECT LIB01DICO FROM DILISPEC WHERE TYPEDICO='SEMPARAM' AND CODEDICO='NCS_SOCIETE_AFFICHE_CS_SPECIF')";
   
        return new RequeteSQL($sql, DB_OXAPROCDB);
    }
    
}