public static async Task <(bool, string)> hasRevision(int user, int docs)
{
scalarInt result = new scalarInt();
string msg = "";
string sql = " SELECT COUNT(*) id "
+ " FROM pbank.carga "
+ " WHERE id_documento = @iid_documento AND id_usuario = @iid_user "
+ " AND id_estado = 1";
NpgsqlParameter ni = new NpgsqlParameter("iid_documento", docs);
NpgsqlParameter nu = new NpgsqlParameter("iid_user", user);
try
{
using (providersBankContext _ctx = new providersBankContext())
{
result = await _ctx.scalarInt.FromSql(sql, ni, nu).SingleAsync();
msg = "OK";
}
}
catch (Exception ex)
{
result.id = 0;
msg = ex.Message;
}
return((result.id == 0)?(false, msg):(true, msg));
}
public static async Task <(List <vCarga>, string)> getVCarga(int id_persona)
{
List <vCarga> carga = new List <vCarga>();
string msg = "";
string sql = " SELECT c.id, di.nombre nom_docs, "
+ " d.nombre nom_doc, c.path, TO_CHAR(c.fecha,'dd/mm/yyyy') fecha, ec.nombre nom_estado "
+ " FROM pbank.carga c "
+ " INNER JOIN pbank.estado_carga ec ON c.id_estado = ec.id "
+ " INNER JOIN pbank.documento_inner di ON di.id = c.id_documento "
+ " INNER JOIN pbank.documento d ON d.id = di.id_documento "
+ " WHERE c.id_usuario = @id_usuario ";
NpgsqlParameter ni = new NpgsqlParameter("id_usuario", id_persona);
try
{
using (providersBankContext _ctx = new providersBankContext())
{
carga = await _ctx.vCarga.FromSql(sql, ni).ToListAsync();
msg = "OK";
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return(carga, msg);
}
public static async Task <string> add_carga(Carga carga)
{
string result = "";
try
{
string sql = "SELECT carga_add( @iid_documento, @ipath, @iid_usuario )";
NpgsqlParameter nI = new NpgsqlParameter("iid_documento", carga.Id_documento);
NpgsqlParameter nP = new NpgsqlParameter("ipath", carga.Path);
NpgsqlParameter nU = new NpgsqlParameter("iid_usuario", carga.Id_usuario);
NpgsqlParameter nM = new NpgsqlParameter("msg", DbType.String)
{
Direction = ParameterDirection.Output
};
using (providersBankContext _ctx = new providersBankContext())
{
int total = await _ctx.Database.ExecuteSqlCommandAsync(sql, nI, nP, nU, nM);
result = Convert.ToString(nM.Value);
}
}
catch (Exception ex)
{
return(ex.Message);
}
return(result);
}
public static async Task <string> delTipoPersona(int id, string responsable)
{
string result = "";
string sql = " SELECT pbank.tipo_persona_del( :iid, :responsable )";
NpgsqlParameter nI = new NpgsqlParameter("iid", id);
NpgsqlParameter nR = new NpgsqlParameter("responsable", responsable);
NpgsqlParameter nM = new NpgsqlParameter("msg", DbType.String)
{
Direction = ParameterDirection.Output
};
try
{
using (providersBankContext pBc = new providersBankContext())
{
int total = await pBc.Database.ExecuteSqlCommandAsync(sql, nI, nR, nM);
result = Convert.ToString(nM.Value);
}
}
catch (Exception ex)
{
result = ex.Message;
}
return(result);
}
public static async Task <(bool, string)> is_user(Account account)
{
scalarBool result = new scalarBool();
string msg = "";
string sql = "SELECT pbank.is_user( @user , @pass ) id";
NpgsqlParameter nu = new NpgsqlParameter("user", account.user);
NpgsqlParameter np = new NpgsqlParameter("pass", account.pass);
try
{
using (providersBankContext _ctx = new providersBankContext())
{
result = await _ctx.scalarBool.FromSql(sql, nu, np).SingleAsync();
msg = "OK";
}
}
catch (Exception ex)
{
msg = ex.Message;
return(false, msg);
}
return(result.id, msg);
}
public static async Task <string> updTipoPersona(PersonaTipo persona)
{
string result = "";
string sql = " SELECT pbank.ADD_TIPO_PERSONA( :iid, :iid_organizacion, :inombre, :idescripcion)";
NpgsqlParameter nI = new NpgsqlParameter("iid", persona.id);
NpgsqlParameter nO = new NpgsqlParameter("iid_organizacion", persona.id_Organizacion);
NpgsqlParameter nN = new NpgsqlParameter("inombre", persona.nombre);
NpgsqlParameter nA = new NpgsqlParameter("idescripcion", persona.ayuda);
NpgsqlParameter nM = new NpgsqlParameter("msg", DbType.String)
{
Direction = ParameterDirection.Output
};
try{
using (providersBankContext pBc = new providersBankContext())
{
int total = await pBc.Database.ExecuteSqlCommandAsync(sql, nI, nO, nN, nA, nM);
result = Convert.ToString(nM.Value);
}
}
catch (Exception ex)
{
result = ex.Message;
};
return(result);
}
public static async Task <string> add_documento(Documento documento, string usuario)
{
string result = "";
string sql = "SELECT pbank.documento_add( @iid, @inombre, "
+ " @iid_organizacion, @id_entidad, @usuario)";
NpgsqlParameter pI = new NpgsqlParameter("iid", documento.id);
NpgsqlParameter pN = new NpgsqlParameter("inombre", documento.nombre);
NpgsqlParameter pO = new NpgsqlParameter("iid_organizacion", documento.id_organizacion);
NpgsqlParameter pE = new NpgsqlParameter("id_entidad", documento.id_tipo);
NpgsqlParameter pU = new NpgsqlParameter("usuario", usuario);
NpgsqlParameter pM = new NpgsqlParameter("@msg", DbType.String)
{
Direction = ParameterDirection.Output
};
try
{
using (providersBankContext pBc = new providersBankContext())
{
int total = await pBc.Database.ExecuteSqlCommandAsync(sql, pI, pN, pO, pE, pU, pM);
result = Convert.ToString(pM.Value);
}
}
catch (Exception e)
{
result = e.Message;
}
return(result);
}
public static async Task <string> del_documento(int id, string usuario)
{
string result = "";
NpgsqlParameter pI = new NpgsqlParameter("iid", id);
NpgsqlParameter pU = new NpgsqlParameter("usuario", usuario);
NpgsqlParameter pM = new NpgsqlParameter("msg", DbType.String)
{
Direction = ParameterDirection.Output
};
string sql = "SELECT pbank.docs_delete(@iid, @usuario ) ";
try
{
using (providersBankContext pBc = new providersBankContext())
{
int total = await pBc.Database.ExecuteSqlCommandAsync(sql, pI, pU, pM);
result = Convert.ToString(pM.Value);
}
}
catch (Exception e)
{
result = e.Message;
}
return(result);
}
public static async Task <(List <vDocumento>, string)> getDocumentos( )
{
List <vDocumento> documentos = new List <vDocumento>();
string e = "";
string sql = "SELECT NEXTVAL('pbank.tmp') id ,d.id id_doc, d.nombre nombre_doc, di.nombre nombre_docs "
+ ", pt.nombre nombre_per "
+ "FROM pbank.documento d "
+ "INNER JOIN pbank.persona_tipo pt on pt.id = d.id_tipo "
+ "LEFT JOIN pbank.documento_inner di on di.id_documento = d.id ";
try
{
using (providersBankContext pBc = new providersBankContext())
documentos = await pBc.vDocumento.FromSql(sql).ToListAsync();
e = "OK";
}
catch (Exception ex)
{
e = ex.Message;
}
return(documentos, e);
}
public static async Task <string> add_documentos(documento_inner documento, string responsable)
{
string result = "";
string sql = "SELECT pbank.documentos_add( @iid, @inombre, @iid_documento, @iayuda, "
+ " @usuario ) ";
NpgsqlParameter nO = new NpgsqlParameter("iid", documento.id);
NpgsqlParameter nN = new NpgsqlParameter("inombre", documento.nombre);
NpgsqlParameter nD = new NpgsqlParameter("iid_documento", documento.id_documento);
NpgsqlParameter nA = new NpgsqlParameter("iayuda", documento.ayuda);
NpgsqlParameter nU = new NpgsqlParameter("usuario", responsable);
NpgsqlParameter nM = new NpgsqlParameter("msg", DbType.String)
{
Direction = ParameterDirection.Output
};
try
{
using (providersBankContext prb = new providersBankContext())
{
int total = await prb.Database.ExecuteSqlCommandAsync(sql, nO, nN, nD, nA, nU, nM);
result = Convert.ToString(nM.Value);
}
}
catch (Exception ex)
{
result = ex.Message;
}
return(result);
}
public static async Task <List <OrganizacionMain> > getOrganizacionMain()
{
List <OrganizacionMain> organizacionMain = new List <OrganizacionMain>();
try
{
using (providersBankContext prC = new providersBankContext())
{
organizacionMain = await prC.OrganizacionMain.FromSql <OrganizacionMain>("SELECT * FROM pbank.organizacion").ToListAsync();
}
}
catch {}
return(organizacionMain);
}
public static async Task <string> addOrganizacion(Organizacion organizacion)
{
string result = "";
try
{
string sql = " SELECT pbank.add_organizacion( "
+ ":oid, "
+ ":init, "
+ ":irazon, "
+ ":idescripcion, "
+ ":icedula, "
+ ":inombre, "
+ ":iphone, "
+ ":imail, "
+ ":icargo )";
NpgsqlParameter pI = new NpgsqlParameter("oid", organizacion.Id.ToString());
NpgsqlParameter pN = new NpgsqlParameter("init", organizacion.Nit);
NpgsqlParameter pR = new NpgsqlParameter("irazon", organizacion.Razon);
NpgsqlParameter pD = new NpgsqlParameter("idescripcion", organizacion.Descripcion);
NpgsqlParameter pC = new NpgsqlParameter("icedula", organizacion.Cedula);
NpgsqlParameter pNo = new NpgsqlParameter("inombre", organizacion.Nombre);
NpgsqlParameter pP = new NpgsqlParameter("iphone", organizacion.Phone);
NpgsqlParameter pM = new NpgsqlParameter("imail", organizacion.Mail);
NpgsqlParameter pCa = new NpgsqlParameter("icargo", organizacion.Cargo);
NpgsqlParameter pO = new NpgsqlParameter("msg", DbType.String)
{
Direction = ParameterDirection.Output
};
using (providersBankContext prC = new providersBankContext())
{
int total = await prC.Database.ExecuteSqlCommandAsync(sql, pI, pN, pR, pD, pC, pNo, pP, pM, pCa, pO);
result = Convert.ToString(pO.Value);
}
}
catch { throw; }
return(result);
}
public static async Task <(List <documento_inner>, string)> getDocumentosId(int id)
{
List <documento_inner> documentos = new List <documento_inner>();
string e = "";
try
{
using (providersBankContext pBc = new providersBankContext())
documentos = await pBc.documento_inner.Where(x => x.id_documento == id).ToListAsync();
e = "OK";
}
catch (Exception ex)
{
e = ex.Message;
}
return(documentos, e);
}
public static async Task <(List <PersonaTipo>, string)> getTipos()
{
List <PersonaTipo> result = new List <PersonaTipo>();
string msg = "";
try
{
using (providersBankContext prC = new providersBankContext())
{
result = await prC.PersonaTipo.FromSql("SELECT * FROM pbank.persona_tipo").ToListAsync();
msg = "OK";
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return(result, msg);
}
public static async Task <(Documento, string)> get(int id)
{
Documento doc = new Documento();
string msg = "";
try
{
using (providersBankContext pbc = new providersBankContext())
{
string sql = "SELECT * FROM pbank.documento WHERE id=@id";
NpgsqlParameter nI = new NpgsqlParameter("id", id);
doc = await pbc.Documento.FromSql(sql, nI).FirstAsync();
msg = "OK";
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return(doc, msg);
}
public static async Task <(List <vDocumentosPersona>, string)> GetDocumentosPersonas(int id_persona)
{
List <vDocumentosPersona> docs = new List <vDocumentosPersona>();
string ex = "";
string sql = "SELECT di.id, d.id id_doc, d.nombre nom_doc, di.nombre nom_docs, "
+ "CASE di.id "
+ " WHEN ( SELECT MIN( di1.id ) "
+ " FROM pbank.documento_inner di1 "
+ " INNER JOIN pbank.documento d1 ON d1.id = di1.id_documento "
+ " WHERE d.id = d1.id ) "
+ " THEN '0' "
+ " ELSE '1' "
+ " END print, di.ayuda, u.id id_persona "
+ "FROM pbank.documento d "
+ "INNER JOIN pbank.documento_inner di ON d.id = di.id_documento "
+ "INNER JOIN pbank.usuario u on u.id_persona = d.id_tipo "
+ "WHERE u.id = @id_persona";
NpgsqlParameter nP = new NpgsqlParameter("id_persona", id_persona);
try
{
using (providersBankContext pBc = new providersBankContext())
{
docs = await pBc.vDocumentosPersona.FromSql(sql, nP).ToListAsync();
ex = "OK";
}
}
catch (Exception e)
{
ex = e.Message;
}
return(docs, ex);
}
