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); }