public string updEmpresa(string ip, int id)
{
string result = "";
int total;
try
{
using (BancoContext bc = new BancoContext())
{
string sql = "BEGIN UPD_EMPRESA_NAT( :VNOMBRE, :VDIRECCION, :VEMAIL, :VID_EMPRESA , :VIP , :VLIBRETA, :VCOPNIA, :IID_PROFESION, :RESULT ); END;";
OracleParameter pNombre = new OracleParameter("VNOMBRE", this.NOM_EMPRESA);
OracleParameter pDireccion = new OracleParameter("VDIRECCION", this.DIRECCION);
OracleParameter pMail = new OracleParameter("VEMAIL", this.EMAIL);
OracleParameter pNit = new OracleParameter("VID_EMPRESA", id);
OracleParameter pVIP = new OracleParameter("VIP", ip);
OracleParameter pLibreta = new OracleParameter("VLIBRETA", this.LIBRETA);
OracleParameter pCopnia = new OracleParameter("VCOPNIA", this.COPNIA);
OracleParameter pProfes = new OracleParameter("IID_PROFESION", this.ID_PROFESION);
OracleParameter pResult = new OracleParameter("RESULT", OracleDbType.Varchar2, 150);
pResult.Direction = ParameterDirection.Output;
total = bc.Database.ExecuteSqlCommand(sql, pNombre, pDireccion, pMail, pNit, pVIP, pLibreta, pCopnia, pProfes, pResult);
result = Convert.ToString(pResult.Value);
}
}
catch { throw; }
return(result);
}
public static string addRevision(decimal carga, string usuario, int estado, string motivo)
{
string result = "";
int total;
try
{
using (BancoContext bc = new BancoContext())
{
string sql = "BEGIN ADD_REVISION( :IID_CARGA , :VUSUARIO , :VESTADO , :VMOTIVO, :RESULT ); END;";
OracleParameter oC = new OracleParameter("IID_CARGA", carga);
OracleParameter oU = new OracleParameter("VUSUARIO", usuario);
OracleParameter oE = new OracleParameter("VESTADO", estado);
OracleParameter oM = new OracleParameter("VMOTIVO", motivo);
OracleParameter pResult = new OracleParameter("RESULT", OracleDbType.Varchar2, 150);
pResult.Direction = ParameterDirection.Output;
total = bc.Database.ExecuteSqlCommand(sql, oC, oU, oE, oM, pResult);
result = Convert.ToString(pResult.Value);
}
}
catch { throw; }
return(result);
}
public string updJuridica(string ip, int id)
{
string result = "";
int total;
try
{
using (BancoContext bc = new BancoContext())
{
string sql = "BEGIN UPD_EMPRESA_JUR( :VNOMBRE, :VDIRECCION, :VEMAIL,:VID_EMPRESA ,:VIP , :VNATURALEZA, "
+ " :IID_ACTIVIDAD, :VNOMBREREP, :VCEDREP, :VDIR_PRINCI, :RESULT); END;";
OracleParameter pNombre = new OracleParameter("VNOMBRE", this.NOM_EMPRESA);
OracleParameter pDireccion = new OracleParameter("VDIRECCION", this.DIRECCION);
OracleParameter pMail = new OracleParameter("VEMAIL", this.EMAIL);
OracleParameter pEmpresa = new OracleParameter("VID_EMPRESA", id);
OracleParameter pVIP = new OracleParameter("VIP", ip);
OracleParameter pNat = new OracleParameter("VNATURALEZA", this.NATURALEZA);
OracleParameter pActividad = new OracleParameter("IID_ACTIVIDAD", this.ID_ACTIVIDAD);
OracleParameter pNomRep = new OracleParameter("VNOMBREREP", this.NOMREPRESENTANTE);
OracleParameter pCedRep = new OracleParameter("VCEDREP", this.CEDREPRESENTANE);
OracleParameter pDomPrin = new OracleParameter("VDIR_PRINCI", this.DIRECCIONPRINCI);
OracleParameter pResult = new OracleParameter("RESULT", OracleDbType.Varchar2, 150);
pResult.Direction = ParameterDirection.Output;
total = bc.Database.ExecuteSqlCommand(sql, pNombre, pDireccion, pMail, pEmpresa, pVIP, pNat, pActividad, pNomRep, pCedRep, pDomPrin, pResult);
result = Convert.ToString(pResult.Value);
}
}
catch { throw; }
return(result);
}
public static List <JURIDICA_DETALLE> getJuridicaDetalleCondicion(string empresa, string actividad, string nit)
{
List <JURIDICA_DETALLE> nd = new List <JURIDICA_DETALLE>();
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM JURIDICA_DETALLE EM "
+ " WHERE UPPER(EM.NOM_EMPRESA) LIKE NVL('%' || UPPER(:EMPRESA) || '%', EM.NOM_EMPRESA ) AND "
+ " UPPER(EM.NOM_ACTIVIDAD) LIKE NVL('%' || UPPER(:PROFESION) || '%', EM.NOM_ACTIVIDAD ) AND "
+ " UPPER(EM.NIT) LIKE NVL('%' || UPPER(:VNIT) || '%', EM.NIT ) ";
OracleParameter oE = new OracleParameter("EMPRESA", empresa);
OracleParameter oP = new OracleParameter("PROFESION", actividad);
OracleParameter oN = new OracleParameter("VNIT", nit);
nd = bc.Database.SqlQuery <JURIDICA_DETALLE>(sql, oE, oP, oN).ToList();
}
}
catch { throw; }
return(nd);
}
public string add_documento(string ip)
{
string result = "";
int total;
try
{
using (BancoContext bc = new BancoContext())
{
string sql = "BEGIN ADD_DOCUMENTO( :IID_DOCUMENTO, :IID_EMPRESA, :VRUTA, :VIP,:RESULT ); END;";
OracleParameter pD = new OracleParameter("IID_DOCUMENTO", this.ID_DOCUMENTO);
OracleParameter pE = new OracleParameter("IID_EMPRESA", this.ID_EMPRESA);
OracleParameter pR = new OracleParameter("VRUTA", this.RUTA);
OracleParameter pI = new OracleParameter("VIP", ip);
OracleParameter pResult = new OracleParameter("RESULT", OracleDbType.Varchar2, 150);
pResult.Direction = ParameterDirection.Output;
total = bc.Database.ExecuteSqlCommand(sql, pD, pE, pR, pI, pResult);
result = Convert.ToString(pResult.Value);
}
}
catch { throw; }
return(result);
}
public static List <PROFESION> getProfesiones()
{
List <PROFESION> pr = new List <PROFESION>();
using (BancoContext bc = new BancoContext())
{
string sql = "SELECT * FROM PROFESION ";
pr = bc.Database.SqlQuery <PROFESION>(sql).ToList();
}
return(pr);
}
public static List <ACTVIDAD_ECO> getActividades()
{
List <ACTVIDAD_ECO> ae = new List <ACTVIDAD_ECO>();
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * FROM ACTVIDAD_ECO ";
ae = bc.Database.SqlQuery <ACTVIDAD_ECO>(sql).ToList();
}
return(ae);
}
public static List <string> getActividadesString()
{
List <string> ae = new List <string>();
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT '[' || ID_ACTIVIDAD || ']' || NOM_ACTIVIDAD NOMBRE "
+ " FROM ACTVIDAD_ECO ";
ae = bc.Database.SqlQuery <string>(sql).ToList();
}
return(ae);
}
public static List <string> getProfesionesString()
{
List <string> ae = new List <string>();
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT '[' || ID_PROFESION || ']' || NOM_PROFESION NOMBRE "
+ " FROM PROFESION ";
ae = bc.Database.SqlQuery <string>(sql).ToList();
}
return(ae);
}
public static string getNomDocumento(decimal tipo)
{
string nombre = "";
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT NOM_DOCUMENTO "
+ " FROM DOCUMENTO "
+ " WHERE ID_DOCUMENTO = :TIPO ";
OracleParameter oT = new OracleParameter("TIPO", tipo);
nombre = bc.Database.SqlQuery <string>(sql, oT).Single();
}
return(nombre);
}
public static bool isRevisor(string documento)
{
int result = 0;
using (BancoContext bc = new BancoContext())
{
string SQL = " SELECT COUNT(*) total "
+ " FROM USUARIO_ADMON UA "
+ " WHERE UA.DOC = :DOC AND TIPO = 1 ";
OracleParameter od = new OracleParameter("DOC", documento);
result = bc.Database.SqlQuery <int>(SQL, od).Single();
}
return((result == 0)?false:true);
}
public static List <DOCUMENTO> getDocumentosTipo(int tipo)
{
List <DOCUMENTO> documentos = new List <DOCUMENTO>();
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM DOCUMENTO "
+ " WHERE id_TIPO = :TIPO "
+ " ORDER BY NOM_DOCUMENTO ";
OracleParameter oT = new OracleParameter("TIPO", tipo);
documentos = bc.Database.SqlQuery <DOCUMENTO>(sql, oT).ToList();
}
return(documentos);
}
public static List <NATURAL_DETALLE> getNaturalesDetalle()
{
List <NATURAL_DETALLE> nd = new List <NATURAL_DETALLE>();
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM EMPRESA_NATURAL_DETALLE "
+ " ORDER BY NOM_EMPRESA";
nd = bc.Database.SqlQuery <NATURAL_DETALLE>(sql).ToList();
}
}
catch { throw; }
return(nd);
}
public static List <JURIDICA_DETALLE> getPersonasJuridicas()
{
List <JURIDICA_DETALLE> em = new List <JURIDICA_DETALLE>();
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM JURIDICA_DETALLE "
+ " ORDER BY NOM_EMPRESA";
em = bc.Database.SqlQuery <JURIDICA_DETALLE>(sql).ToList();
}
}
catch { throw; }
return(em);
}
public static string getActividadNombre(string id)
{
string nombre = "";
string sql = " SELECT '['|| ID_ACTIVIDAD || ']' || NOM_ACTIVIDAD actividad "
+ " FROM ACTVIDAD_ECO "
+ " WHERE ID_ACTIVIDAD = TO_NUMBER(:ID_ACTIVIDAD) ";
try
{
using (BancoContext bc = new BancoContext())
{
OracleParameter oA = new OracleParameter("ID_ACTIVIDAD", id);
nombre = bc.Database.SqlQuery <string>(sql, oA).Single();
}
}
catch { throw; }
return(nombre);
}
public static USUARIO getUsuario(string nit)
{
USUARIO usuario = new USUARIO();
string sql = " SELECT * "
+ " FROM USUARIO "
+ " WHERE DOCUMENTO = :NIT ";
try
{
using (BancoContext bc = new BancoContext())
{
OracleParameter oN = new OracleParameter("NIT", nit);
usuario = bc.Database.SqlQuery <USUARIO>(sql, oN).Single();
}
}
catch { throw; }
return(usuario);
}
public static NATURAL_DETALLE getNaturalDetalle(int id)
{
NATURAL_DETALLE nd = new NATURAL_DETALLE();
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM EMPRESA_NATURAL_DETALLE "
+ " WHERE ID_EMPRESA = :ID_EMPRESA ";
OracleParameter pE = new OracleParameter("ID_EMPRESA", id);
nd = bc.Database.SqlQuery <NATURAL_DETALLE>(sql, pE).Single();
}
}
catch { throw; }
return(nd);
}
public static string getEstadoNombre(int empresa, decimal documento)
{
string result = "";
try
{
string sql = " SELECT GET_ESTADO_NOMBRE( :IID_EMPRESA , :IID_DOCUMENTO ) "
+ " FROM DUAL ";
using (BancoContext bc = new BancoContext())
{
OracleParameter oE = new OracleParameter("IID_EMPRESA", empresa);
OracleParameter oD = new OracleParameter("IID_DOCUMENTO", documento);
result = bc.Database.SqlQuery <string>(sql, oE, oD).Single();
}
}
catch { throw; }
return(result);
}
public static JURIDICA_DETALLE getJuridicaDetalle(int id)
{
JURIDICA_DETALLE em = new JURIDICA_DETALLE();
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM JURIDICA_DETALLE "
+ " WHERE ID_EMPRESA = :ID ";
OracleParameter pE = new OracleParameter("ID", id);
em = bc.Database.SqlQuery <JURIDICA_DETALLE>(sql, pE).Single();
}
}
catch { throw; }
return(em);
}
public static EMPRESA_JUR getEmpresaJuridica(int id)
{
EMPRESA_JUR em = new EMPRESA_JUR();
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM EMPRESA_JURIDICA"
+ " WHERE ID_EMPRESA = :ID ";
OracleParameter pE = new OracleParameter("ID", id);
em = bc.Database.SqlQuery <EMPRESA_JUR>(sql, pE).Single();
}
}
catch { throw; }
return(em);
}
public static string getprofesionNombre(string id)
{
string nombre = "";
string sql = " SELECT '['|| ID_PROFESION || ']' || NOM_PROFESION actividad "
+ " FROM PROFESION "
+ " WHERE ID_PROFESION = TO_NUMBER(:ID_PROFESION) ";
try
{
using (BancoContext bc = new BancoContext())
{
OracleParameter oA = new OracleParameter("ID_PROFESION", id);
nombre = bc.Database.SqlQuery <string>(sql, oA).Single();
}
}
catch { throw; }
return(nombre);
}
public static int getPenultimoEstado(int empresa, decimal documento)
{
int result = 0;
try
{
string sql = " SELECT GET_PENULTIMO_ESTADO( :IID_EMPRESA , :IID_DOCUMENTO ) "
+ " FROM DUAL ";
using (BancoContext bc = new BancoContext())
{
OracleParameter oE = new OracleParameter("IID_EMPRESA", empresa);
OracleParameter oD = new OracleParameter("IID_DOCUMENTO", documento);
result = bc.Database.SqlQuery <int>(sql, oE, oD).Single();
}
}
catch { throw; }
return(result);
}
public static bool isDocCargado(decimal documento, int empresa)
{
int resultTmp;
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT IS_DOC_CARGADO( :DOC, :EMPRESA ) "
+ " FROM DUAL ";
OracleParameter oD = new OracleParameter("DOC", documento);
OracleParameter oE = new OracleParameter("EMPRESA", empresa);
resultTmp = bc.Database.SqlQuery <int>(sql, oD, oE).Single();
}
}
catch { throw; }
return((resultTmp == 0) ? false:true);
}
public static EMPRESA getEmpresaNIT(string nit)
{
EMPRESA em = new EMPRESA();
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT * "
+ " FROM EMPRESA"
+ " WHERE NIT = :NIT ";
OracleParameter pE = new OracleParameter("NIT", nit);
em = bc.Database.SqlQuery <EMPRESA>(sql, pE).Single();
}
}
catch { throw; }
return(em);
}
public static bool existsEmpresa(string NIT)
{
int conteo = -1;
try
{
using (BancoContext bc = new BancoContext())
{
string sql = " SELECT COUNT( ID_EMPRESA ) "
+ " FROM EMPRESA"
+ " WHERE NIT = :NIT ";
OracleParameter pE = new OracleParameter("NIT", NIT);
conteo = bc.Database.SqlQuery <int>(sql, pE).Single();
}
}
catch { throw; }
return((conteo == 0)?false:true);
}
public static List <Detalle_documentos> get_documentoEmpresa(int empresa, int documento)
{
List <Detalle_documentos> cargados = new List <Detalle_documentos>();
string sql = " SELECT DC.RUTA, DC.FECHA_CARGA, DC.VERSION, E.NOM_ESTADO "
+ " FROM DOCS_CARGADO DC "
+ " INNER JOIN ESTADO E ON E.ID_ESTADO = DC.ID_ESTADO "
+ " WHERE DC.ID_EMPRESA = :ID_EMPRESA AND DC.ID_DOCUMENTO = :ID_DOCUMENTO ";
try
{
using (BancoContext bc = new BancoContext())
{
OracleParameter oE = new OracleParameter("ID_EMPRESA", empresa);
OracleParameter oD = new OracleParameter("ID_DOCUMENTO", documento);
cargados = bc.Database.SqlQuery <Detalle_documentos>(sql, oE, oD).ToList();
}
}
catch { throw; }
return(cargados);
}
public static EMPRESA getEmpresa(decimal empresa)
{
EMPRESA em = new EMPRESA();
try
{
string sql = " SELECT * "
+ " FROM EMPRESA E "
+ " WHERE E.ID_EMPRESA = :EMPRESA ";
using (BancoContext bc = new BancoContext())
{
OracleParameter oE = new OracleParameter("EMPRESA", empresa);
em = bc.Database.SqlQuery <EMPRESA>(sql, oE).Single();
}
}
catch { throw; }
return(em);
}
public static bool isUserAdmon(string user, string pass)
{
int result = -1;
string sql = " SELECT COUNT( * ) "
+ " FROM USUARIO_ADMON "
+ " WHERE DOC = :NIT AND PASS = REGIS.MD5(:CLAVE) ";
try
{
using (BancoContext bc = new BancoContext())
{
OracleParameter oN = new OracleParameter("NIT", user);
OracleParameter oP = new OracleParameter("PASS", pass);
result = bc.Database.SqlQuery <int>(sql, oN, oP).Single();
}
}
catch { throw; }
return((result == 0) ? false : true);
}
public static string delEmpresa(string NIT)
{
string result = "";
int total;
try
{
using (BancoContext bc = new BancoContext())
{
string sql = "BEGIN DEL_USUARIO(:VNIT, :RESULT ); END;";
OracleParameter pNit = new OracleParameter("VNIT", NIT);
OracleParameter pResult = new OracleParameter("RESULT", OracleDbType.Varchar2, 150);
pResult.Direction = ParameterDirection.Output;
total = bc.Database.ExecuteSqlCommand(sql, pNit, pResult);
result = Convert.ToString(pResult.Value);
}
}
catch { throw; }
return(result);
}
public static int getTipoEmpresa(decimal empresa)
{
int tipo = -1;
try
{
string sql = " SELECT E.ID_TIPO "
+ " FROM EMPRESA E "
+ " INNER JOIN TIPO_EMPRESA TE ON E.ID_TIPO = TE.ID_TIPO "
+ " WHERE E.ID_EMPRESA = :EMPRESA ";
using (BancoContext bc = new BancoContext())
{
OracleParameter oE = new OracleParameter("EMPRESA", empresa);
tipo = bc.Database.SqlQuery <int>(sql, oE).Single();
}
}
catch { throw; }
return(tipo);
}