<?php

class SqlREF
{

    public static function getNbSignat($user, $typeEnt=null)
    {
        $sql = "SELECT COUNT(*) NBSIGNAT FROM WFT_SIG WHERE SIGNAT='" . replacesqlpointeur($user) . "' AND WFT_SIG.SIGNABLE=0x54 ";
        
        if($typeEnt)
            $sql .= " and TYPE_ENT = '$typeEnt'";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function getListeDelegation($user)
    {
        $today = Date('Ymd');

        // Spec 3523
        $req = "SELECT distinct SIGNAT, 
        (
            select count(*) from WFT_SIG with (nolock) 
            left outer join WF_GRP_SIG_LIST grp with(nolock) on (grp.WF_GS_ENT_TYPE = WFT_SIG.TYPE_ENT and grp.WF_GS_CODE = WFT_SIG.GROUPE_SIG) 
            where TYPE_ENT = 'FOURNISS' and (WFT_SIG.SIGNAT=SIG_DELEG.SIGNAT or grp.WF_GS_CODE_SIG=SIG_DELEG.SIGNAT) and SIGNABLE=0x54
        ) nbSign
        FROM SIG_DELEG with(nolock)
        WHERE DELEG = '" . replacesqlpointeur($user) . "'
        AND DTE_DEB <= $today
        AND (DTE_FIN >= $today or DTE_FIN = '00000000')";
        
        if(Param::isDelegationAutoSupHier()) {
            $req .= " UNION 
            SELECT CODE as SIGNAT,
            (
                select count(*) from WFT_SIG with (nolock) 
                left outer join WF_GRP_SIG_LIST grp with(nolock) on (grp.WF_GS_ENT_TYPE = WFT_SIG.TYPE_ENT and grp.WF_GS_CODE = WFT_SIG.GROUPE_SIG) 
                where TYPE_ENT = 'FOURNISS' and (WFT_SIG.SIGNAT=HASTUTIL.CODE or grp.WF_GS_CODE_SIG=HASTUTIL.CODE) and SIGNABLE=0x54
            ) nbSign
            FROM HASTUTIL with(nolock)
            WHERE SUPHIER = '" . replacesqlpointeur($user) . "' AND (SIG_DA = 0x54 or SIG_CDE = 0x54 or SIG_BAP = 0x54 or SIG_REF = 0x54)";
        }
        if(Param::isDelegationAutoRespUO()) {
            $req .= " UNION 
            SELECT CODE as SIGNAT,
            (
                select count(*) from WFT_SIG with (nolock) 
                left outer join WF_GRP_SIG_LIST grp with(nolock) on (grp.WF_GS_ENT_TYPE = WFT_SIG.TYPE_ENT and grp.WF_GS_CODE = WFT_SIG.GROUPE_SIG) 
                where TYPE_ENT = 'FOURNISS' and (WFT_SIG.SIGNAT=HASTUTIL.CODE or grp.WF_GS_CODE_SIG=HASTUTIL.CODE) and SIGNABLE=0x54
            ) nbSign
            FROM HASTUTIL with(nolock)
            WHERE CODE_UO in (SELECT CODE_UO FROM UO with(nolock) WHERE RESP_HIER = '". ReplaceSqlPointeur($user) ."')
            AND (SIG_DA = 0x54 or SIG_CDE = 0x54 or SIG_BAP = 0x54 or SIG_REF = 0x54)";
        }
        
        return new RequeteSQL($req, DB_OXASQLDB);
    }

}