<?php

class SqlCg
{

    public static function getUniqCGSecto($codeSte, $si, $lot, $code_ges)
    {
        $date = date('Ymd', time());
        
        $sql = 'select CODE_STE,CODE_SI,NUM_LOT,CODE_GES,ID_PARENT,CODE_AFF,LIB_GES, ' . "(CASE WHEN DTEVALMIN<='".ReplaceSqlPointeur($date)."' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '".ReplaceSqlPointeur($date)."') AND AUTHACHAT = 0x54 THEN 0 ELSE 1 END) AS Blocage " . " from CODGEST with (nolock) " . " where CODE_SI  = '" . ReplaceSql($si) . "'  " . " and   NUM_LOT  =  '".ReplaceSqlPointeur($lot)."' " . " and   CODE_AFF = '" . ReplaceSql($code_ges) . "' " . " and   CODE_STE = '" . ReplaceSql($codeSte) . "' " . " AND  DTEVALMIN<='{$date}' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '".ReplaceSqlPointeur($date)."') AND AUTHACHAT = 0x54 ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getUniqCG($si, $lot, $code_ges)
    {
        $date = date('Ymd', time());
        
        $sql = "select CODE_STE,CODE_SI,NUM_LOT,CODE_GES,ID_PARENT,CODE_AFF,LIB_GES, " . "(CASE WHEN DTEVALMIN<='".ReplaceSqlPointeur($date)."' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '".ReplaceSqlPointeur($date)."') AND AUTHACHAT = 0x54 THEN 0 ELSE 1 END) AS Blocage " . "from CODGEST with (nolock) " . "where CODE_SI  = '" . ReplaceSql($si) . "'  " . "and   NUM_LOT  =  '".ReplaceSqlPointeur($lot)."'" . "and   CODE_AFF = '" . ReplaceSql($code_ges) . "' " . "and   CODE_STE = '' " . "AND  DTEVALMIN<='".ReplaceSqlPointeur($date)."' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '{$date}') AND AUTHACHAT = 0x54 ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getNumLot()
    {
        // On récupère le numéro de code gestion actif
        $date = date('Ymd', time());
        $sql = 'select top 1 * from LOTGEST with (nolock) where DAT_EFF <= \'' . $date . '\' order by DAT_EFF DESC';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlCGSteSecto($code_ste, $num_lot, $cg, $si)
    {
        $date = date('Ymd', time());
        
        $sql = "select CODE_STE,CODE_SI,NUM_LOT,CODE_GES,ID_PARENT,CODE_AFF,LIB_GES, " . "(CASE WHEN DTEVALMIN<='{$date}' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '{$date}') AND AUTHACHAT = 0x54 THEN 0 ELSE 1 END) AS Blocage " . "from CODGEST with (nolock) " . "where CODE_STE = '" . ReplaceSql($code_ste) . "' " . "and   NUM_LOT  =  {$num_lot} " . "and   CODE_GES = '" . ReplaceSql($cg) . "' " . "and   CODE_SI = '" . ReplaceSql($si) . "' ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function SqlCGSte($num_lot, $cg, $si)
    {
        $date = date('Ymd', time());
        
        $sql = 'select CODE_STE,CODE_SI,NUM_LOT,CODE_GES,ID_PARENT,CODE_AFF,LIB_GES, ' . "(CASE WHEN DTEVALMIN<='{$date}' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '{$date}') AND AUTHACHAT = 0x54 THEN 0 ELSE 1 END) AS Blocage " . 'from CODGEST with (nolock) ' . "where NUM_LOT  =  {$num_lot} " . "and   CODE_GES = '" . ReplaceSql($cg) . "' " . "and   CODE_SI = '" . ReplaceSql($si) . "' " . "and   CODE_STE = '" . (Param::IsSISecto() ? ReplaceSql($_SESSION['Session_SocUsr']) : '') . "' ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getLibelleCG($si, $lot, $code_aff)
    {
        $date = date('Ymd', time());
        
        $sql = "select CODE_STE,CODE_SI,NUM_LOT,CODE_GES as 'code',ID_PARENT,CODE_AFF,LIB_GES as 'libelle', " . "(CASE WHEN DTEVALMIN<='{$date}' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '{$date}') AND AUTHACHAT = 0x54 THEN 0 ELSE 1 END) AS Blocage " . 'from CODGEST with (nolock) ' . "where CODE_SI  = '" . ReplaceSql($si) . "'  " . "and   NUM_LOT  =  {$lot} " . "and   CODE_AFF = '" . ReplaceSql($code_aff) . "' " . "and   CODE_STE = '" . (Param::IsSISecto() ? ReplaceSql($_SESSION['Session_SocUsr']) : '') . '\' ' . "AND  DTEVALMIN<='".ReplaceSqlPointeur($date)."' AND (DTEVALMAX = '00000000' OR DTEVALMAX >= '".ReplaceSqlPointeur($date)."') AND AUTHACHAT = 0x54 ";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction retournant la requete permettant d'obtenir les informations relatives à un code gestion (code affaire donné)
     *
     * @param string $si
     * @param string $cg
     * @param string $ste
     * @return string sql
     */
    public static function rqGetInfoOnCG(& $si, & $cg, $ste = '', $numLot = 0)
    {
        $sql = "select CODE_AFF, LIB_GES from CODGEST with(nolock)
					where 		CODE_SI = '" . ReplaceSql($si) . "'
					and CODE_AFF = '" . ReplaceSql($cg) . "'
					and NUM_LOT = '" . ReplaceSql($numLot) . "'
					and CODE_STE = '" . (Param::IsSISecto() ? ReplaceSql($ste) : '') . "' ";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqGetInfoCG($si)
    {
        $sql = "select TOP 1 * from CODGEST with(nolock)
					where 		CODE_SI = '" . ReplaceSql($si) . "'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
}