<?php

class SqlStatutDocument
{

    /**
     * Fonction qui verifie qu'une CDE est egal à 'E' et
     * n'est pas rattachée à aucune réception ni facture
     *
     * @param int $num_cde
     * @return string requête sql
     */
    public static function rqCheckAnnul(& $num_cde)
    {
        $sql = 'SELECT NO_CDE , ETAT_CDE' . ' FROM CA_ENTET with(nolock) ' . " WHERE NO_CDE = " . ReplaceSqlPointeur($num_cde) . " AND ETAT_CDE = 'E'" . " AND not exists (SELECT null FROM FAC_LIG  WHERE NUM_CDE = " . ReplaceSqlPointeur($num_cde) . ")" . " AND not exists (SELECT null FROM BR_LIGNE WHERE NUM_CDE = " . ReplaceSqlPointeur($num_cde) . ")";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Spec 3103 pour la reimpression de la commande
     * Renvoi un resultat si l'utilisateur a le droit d'administration sur le centre emetteur de la CDE
     * 
     * @param unknown_type $num_cde
     */
    public static function rqCheckDroitCtrReimp(& $num_cde)
    {
        $sql = 'SELECT * FROM (' . " SELECT CODE CODE FROM HASTUTIL with (nolock) WHERE CODE = '{$_SESSION['Session_Login']}' AND ADMCENTR = 0x54" . ' union' . " SELECT COD_USR CODE FROM ADMLGRSO with (nolock), CA_ENTET with (nolock), SOCIETE with (nolock), CENTRE with (nolock) WHERE ADMLGRSO.COD_USR = '{$_SESSION['Session_Login']}' AND ADMLGRSO.TTSOC = 0x54 AND CA_ENTET.NO_CDE = '" . ReplaceSqlPointeur($num_cde) . "' AND CA_ENTET.CTR_EMETT = CENTRE.CODE_CTR AND CENTRE.CODE_STE = CA_ENTET.CODE_STE AND SOCIETE.GROUPE = ADMLGRSO.COD_GRPS" . ' union' . " SELECT COD_USR CODE FROM ADML_SOC with (nolock) WHERE COD_USR = '{$_SESSION['Session_Login']}' AND TTCTR = 0x54 AND SOC_COD = '{$_SESSION['Session_SocUsr']}'" . ' union' . " SELECT COD_USR CODE FROM ADML_CTR with (nolock), CA_ENTET with (nolock), HASTUTIL with (nolock) WHERE ADML_CTR.COD_USR = HASTUTIL.CODE AND COD_USR = '{$_SESSION['Session_Login']}' AND CTR_EMETT = COD_CTR AND NO_CDE = '" . ReplaceSqlPointeur($num_cde) . "' AND HASTUTIL.ADMLOCAL = 0x54" . ' ) tmp';
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Spec 3103 pour la reimpression de la commande
     * Si la commande est bien en etat E ou S renvoit un resultat
     * 
     * @param unknown_type $num_cde
     */
    public static function rqCheckImpReImp(& $num_cde)
    {
        $sql = 'SELECT NO_CDE , ETAT_CDE' . ' FROM CA_ENTET with(nolock) ' . " WHERE NO_CDE = " . ReplaceSqlPointeur($num_cde) . " " . " AND (ETAT_CDE = 'E' OR ETAT_CDE = 'S' OR ETAT_CDE = 'Z' )";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction qui retourne le numero de la CDE si elle est bloquee dans SAP
     *
     * @param int $num_cde
     * @return string requête sql
     */
    public static function rqEtatSapCDE(& $num_cde)
    {
        $sql = 'SELECT COD_ENR FROM DILISPEC with (nolock), CS_DON with (nolock)' . " WHERE DILISPEC.TYPEDICO='PARAMSAP'" . " AND DILISPEC.CODEDICO='NCS_CA_ENTET_CA_BLOQUEE'" . ' AND CS_DON.NUM_FIX=DILISPEC.LIB01DICO' . " AND CS_DON.COD_TAB = 'CA_ENTET' " . // Dossier 24720
" AND VALEUR='O'" . " AND COD_ENR='" . ReplaceSqlPointeur($num_cde) . "'";
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction qui retourne l'ETAT d'une commande
     *
     * @param int $num_cde
     * @return string requête sql
     */
    public static function rqGetEtatCDE(& $num_cde)
    {
        $sql = 'SELECT NO_CDE , ETAT_CDE, DTE_VALID, ETAT_FIN FROM CA_ENTET with(nolock) WHERE NO_CDE = ' . ReplaceSqlPointeur($num_cde);
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * Fonction qui prends en paramètre le type et le numéro de document
     * et qui renvoie les informations concernant le statut du document
     *
     * DA : VALIDE, TRAITEE, REFUSEE, SIGNEE
     * CDE : REFUSEE, SIGNEE, ETAT_CDE, CDE_EXP
     * BR : VALIDE
     * BS : VALIDE
     *
     * @param entier $numDoc
     * @param chaine $typeDoc
     * @return la requête
     */
    public static function rqGetStatutDocument(& $numDoc, & $typeDoc)
    {
        switch ($typeDoc) {
            case TypeSuivi::DelSignDA:
            case TypeSuivi::SignDA:
            case TypeSuivi::DA:
                // nous renvoie les infos VALIDE (0 ou 1), TRAITEE(0 ou 1), REFUSEE(0 ou 1), SIGNEE(0 ou 1), CHIF_EXEC(C ou E)
                $sql = 'select DA_ENTET.VALIDITE VALIDE,
								DA_ENTET.TRAITEE TRAITEE,
								DA_ENTET.REFUSEE REFUSEE,
								DA_ENTET.SIGNEE SIGNEE,
								DA_ENTET.CHIF_EXEC CHIF_EXEC,
								DA_ENTET.NO_DA,
								DA_ENTET.CODE_STE
						from  DA_ENTET with(nolock)
						where DA_ENTET.NO_DA= ' . ReplaceSqlPointeur($numDoc);
                break;
            
            // nous renvoie les infos REFUSEE, SIGNEE, ETAT_CDE (A, V, E, S...), CDE_EXP
            case TypeSuivi::DelSignCDE:
            case TypeSuivi::SignCDE:
            case TypeSuivi::CDE:
            case TypeSuivi::CdeToRate:
            case TypeSuivi::SCdeToRate:
                $sql = "select CA_ENTET.REFUSEE REFUSEE,
								CA_ENTET.SIGNEE SIGNEE,
								case when CA_ENTET.DTE_VALID	<>	'00000000'  then  0x54 else 0x46 end VALIDEE,
								CA_ENTET.ETAT_CDE ETAT_CDE,
								CA_ENTET.ETAT_FIN ETAT_FIN,
								CA_ENTET.CDE_EXP CDE_EXP,
								CA_ENTET.EVE_IMP,
                                CA_ENTET.DTE_VALID,
								CA_ENTET.NO_CDE,
								CA_ENTET.CODE_STE
						from  CA_ENTET with(nolock)
						where CA_ENTET.NO_CDE= " . ReplaceSqlPointeur($numDoc);
                break;
            
            case TypeSuivi::BR:
                // nous renvoie les infos VALIDE
                $sql = "select case when BR_RECEP.DATE_VAL='00000000' then '0' else '1' end VALIDE, TYP_BR,
								case when exists( select null from BR_LIGNE where BR_LIGNE.A_CONTROL = 0x54 and BR_LIGNE.CTRL_FAIT = 0x46 and BR_LIGNE.NUM_BR= " . ReplaceSqlPointeur($numDoc) . ") then 0x54 else 0x46 end A_CONTROLER
						from  BR_RECEP
						where BR_RECEP.NUM_BR=" . ReplaceSqlPointeur($numDoc);
                break;
            
            case TypeSuivi::BS:
                // nous renvoie les infos VALIDE
                $sql = "select case when SBS_ENTE.DTE_VALID='00000000' then '0' else '1' end VALIDE
						from  SBS_ENTE with(nolock)
						where SBS_ENTE.NO_BS= " . ReplaceSqlPointeur($numDoc);
                break;
            
            case TypeSuivi::DelSignBAP:
            case TypeSuivi::SignBAP:
            case TypeSuivi::BAP:
                $sql = ' select * from FAC_ENT with(nolock) where NUM_FACT = ' . ReplaceSqlPointeur($numDoc);
                break;
        }
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    /**
     * fonction qui ramène le nombre de PJ présentsur un document DA ou CDE
     *
     *
     * @param
     *            numero du document $numDoc
     * @param
     *            type du document $typeDoc (DA ou CDE)
     * @return la requête
     */
    public static function rqGetNumPJ(& $numDoc, & $typeDoc)
    {
        switch ($typeDoc) {
            case TypeSuivi::DA:
                $sql = 'select count(*) NbPJ  		from 		DA_PIJO with(nolock)  where  NO_DA=' . ReplaceSqlPointeur($numDoc);
                break;
            case TypeSuivi::CDE:
                if (false !== stripos($numDoc, "/")) {
                    $no_avenant_tmp = explode("/", $numDoc);
                    $no_avenant = str_pad($no_avenant_tmp[1], 2, " ", STR_PAD_LEFT);
                    $numDoc = $no_avenant_tmp[0];
                    
                    $sql = "select count(*) NbPJ  from 			HCA_PIJO with(nolock) where  NO_CA=" . ReplaceSqlPointeur($numDoc) . " and AVENANT = '" . ReplaceSqlPointeur($no_avenant) . "'";
                } else
                    $sql = "select count(*) NbPJ  from 			CA_PIJO with(nolock)  where  NO_CA=" . ReplaceSqlPointeur($numDoc);
                break;
            case TypeSuivi::BAP:
                $sql = "select count(*) NbPJ  		from 		FAC_PIJO with(nolock)  where  NO_FAC=" . ReplaceSqlPointeur($numDoc);
                break;
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
}