//Se utiliza ene el form de busq.
public static Registro ObtenerReg(char pdeleg, int pn_reg)
{
Registro pRegistro = new Registro();
string sql = "select * from registros where delegacion='" + pdeleg + "' and n_reg=" + pn_reg;
using (BDConexion.ObtenerConexion())
{
NpgsqlCommand comando = new NpgsqlCommand(sql, BDConexion.ObtenerConexion());
comando.CommandTimeout = 5 * 60;
NpgsqlDataReader datos = comando.ExecuteReader();
while (datos.Read())
{
pRegistro.delegacion = datos.GetChar(0);
pRegistro.n_reg = datos.GetInt32(1);
pRegistro.fec_ent = datos.GetDateTime(2);
pRegistro.id_cte = datos.GetInt32(3);
pRegistro.id_titular = datos.GetInt32(4);
pRegistro.seccion_int = datos.GetString(5);
pRegistro.seccion = datos.GetString(6);
pRegistro.t_tramite = datos.GetString(7);
pRegistro.matricula = datos.GetString(8);
pRegistro.estado = datos.GetString(9);
pRegistro.factura = datos.GetInt32(10);
pRegistro.fec_fra = datos.GetDateTime(11);
pRegistro.observacion = datos.GetString(12);
pRegistro.honorarios = datos.GetDecimal(13);
pRegistro.p_iva = datos.GetInt16(14);
pRegistro.tasa = datos.GetDecimal(15);
pRegistro.exp_tl = datos.GetString(16);
pRegistro.fec_pre_exp = datos.GetDateTime(17);
pRegistro.et_tasa = datos.GetInt64(18); //(float)datos.GetDecimal(18);
pRegistro.t_tasa = datos.GetString(19);
pRegistro.cambio_serv = datos.GetString(20);
pRegistro.bate_ant = datos.GetString(21);
pRegistro.nif = datos.GetString(22);
pRegistro.dcho_col = datos.GetDecimal(23);
pRegistro.t_cte_fra = datos.GetChar(24);
pRegistro.et_tasa2 = datos.GetInt64(25);
pRegistro.t_tasa2 = datos.GetString(26);
pRegistro.et_tasa3 = datos.GetInt64(27);
pRegistro.t_tasa3 = datos.GetString(28);
pRegistro.et_tasa4 = datos.GetInt64(29);
pRegistro.t_tasa4 = datos.GetString(30);
pRegistro.descripcion = datos.GetString(31);
pRegistro.ruta_pdf = datos.GetString(32);
pRegistro.vehiculo = datos.GetString(33);
pRegistro.descrip1 = datos.GetString(34);
pRegistro.impor1 = datos.GetDecimal(35);
pRegistro.iva1_sn = datos.GetBoolean(36);
pRegistro.descrip2 = datos.GetString(37);
pRegistro.impor2 = datos.GetDecimal(38);
pRegistro.iva2_sn = datos.GetBoolean(39);
}
comando.Connection.Close();
return(pRegistro);
}
}
public Dictionary <String, Product> LoadProductData(NpgsqlConnection connection)
{
Dictionary <String, Product> map = new Dictionary <string, Product>();
String query =
"SELECT" +
" id" +
", description" +
", product_group_id" +
", unit_id" +
", weight" +
", cost" +
", quantity" +
" FROM products";
NpgsqlCommand command = new NpgsqlCommand(query, connection);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
String id = reader.GetString(0);
String description = reader.GetString(1);
String groupId = reader.GetString(2);
String unitId = reader.GetString(3);
Decimal weight = reader.IsDBNull(4) ? 0 : reader.GetDecimal(4);
Decimal cost = reader.GetDecimal(5);
int quantity = reader.GetInt32(6);
Product product = new Product(id, description, groupId, unitId, weight, cost, quantity);
map.Add(id, product);
}
reader.Close();
return(map);
}
public StoreDataProvider()
{
using (NpgsqlConnection conn =
new NpgsqlConnection(
"Server=localhost;Port=5432;UserId=TestAcct;Password=Test1;Database=StoreFinder;"))
{
conn.Open();
var command = new NpgsqlCommand("select \"StoreNumber\", \"Latitude\", \"Longitude\"" +
"from \"StoreZip\" a, \"USZip\" b where a.\"StoreZipCode\" = b.\"Zip\"",
conn);
using (NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
var storeNum = dr.GetDecimal(0);
Decimal latitude = dr.GetDecimal(1);
Decimal longitude = dr.GetDecimal(2);
var spoint = new StoreLocation(storeNum, latitude, longitude);
_storeData.Add(spoint);
}
}
}
}
}//
private Articulo cargaArticulo2(NpgsqlDataReader reader)
{
return(new Articulo
{
Descrip = reader.GetString(0),
Precio_Cost = reader.GetDecimal(1),
Precio_Venta = reader.GetDecimal(2),
Impuesto = reader.GetBoolean(3)
});
}//
/// <summary>
/// Gets an array of geometryCollection objects matching the given criteria.
/// </summary>
/// <param name="includeLargeObject">If true, it also gets the actual object data from the database.</param>
/// <param name="whereClause">The matching criteria for the where clause of the database query.
/// Please refer to geometry_collection table in the database for the definitions.</param>
/// <param name="connectionString">The connectionString to the database. Please refer to .Net Data Provider for Postgresql for format specifications</param>
/// <returns>Returns an array of geometry collection.</returns>
/// <seealso cref="GetObjectsWithIdAndCriteriaMinimal"/>
public static geometryCollection[] GetObjectsWithIdAndCriteria(bool includeLargeObject, string whereClause, string connectionString)
{
NpgsqlConnection con = new NpgsqlConnection(connectionString);
con.Open();
NpgsqlCommand com = new NpgsqlCommand("", con);
com.CommandText = "select id,name,versionTitle,major,minor,format,largeobjectReference,latitude,longitude,pivotX,pivotY,pivotZ,gisId,gistype,lastUpdate from geometryCollection";
if (!string.IsNullOrEmpty(whereClause))
{
com.CommandText += " where " + whereClause;
}
//com.CommandText = com.CommandText.Replace(",", ".");
NpgsqlDataReader reader = com.ExecuteReader();
List <geometryCollection> objectcollection = new List <geometryCollection>();
while (reader.Read())
{
geometryCollection singleObject = new geometryCollection();
singleObject.id = reader.GetInt64(0);
singleObject.name = reader.GetString(1);
singleObject.version = new GaPSlabsVersion()
{
versionTitle = reader.GetString(2), major = reader.GetInt32(3), minor = reader.GetInt32(4)
};
singleObject.format = reader.GetString(5);
if (includeLargeObject && reader.GetInt32(6) != 0)
{
singleObject.largeObject = GetLargeObject((int)reader.GetInt64(6), connectionString);
}
else
{
singleObject.largeObject = null;
}
singleObject.large_object_reference = (int)reader.GetInt64(6);
singleObject.latitude = reader.GetInt32(7);
singleObject.longitude = reader.GetInt32(8);
singleObject.pivot = new Vector3GaPS()
{
x = (float)reader.GetDecimal(9), y = (float)reader.GetDecimal(10), z = (float)reader.GetDecimal(11)
};
singleObject.gisId = reader.GetInt32(12);
singleObject.gisType = reader.GetString(13);
singleObject.lastUpdate = reader.GetTimeStamp(14).ToDateTime();
objectcollection.Add(singleObject);
}
if (!reader.IsClosed)
{
reader.Close();
}
con.Close();
return(objectcollection.ToArray());
}
protected override InvoiceItemEntity FromRow(NpgsqlDataReader resultSet)
{
return(new InvoiceItemEntity(
(ulong)resultSet.GetInt64(IdColumn),
resultSet.GetString(NameColumn),
resultSet.GetDecimal(UnitPriceColumn),
resultSet.GetString(UnitTypeColumn),
resultSet.GetDecimal(DiscountColumn),
resultSet.GetDouble(QuantityColumn)
));
}
/// <summary>
/// Gets the geometry object with the matching id
/// </summary>
/// <param name="id">The id of the object in the geometry_collection table.</param>
/// <param name="includeLargeObject">If true, it also gets the actual object data from the database.</param>
/// <param name="connectionString">The connectionString to the database. Please refer to .Net Data Provider for Postgresql for format specifications</param>
/// <returns>Returns the geometry.</returns>
public static geometryCollection GetSingleObjectWithId(string id, bool includeLargeObject, string connectionString)
{
NpgsqlConnection con = new NpgsqlConnection(connectionString);
con.Open();
NpgsqlCommand com = new NpgsqlCommand("", con);
com.CommandText = "select id,name,version_title,major,minor,format,large_object_Reference,latitude,longitude,pivotx,pivoty,pivotz,gis_id,gis_type,last_update from geometry_collection where id=" + "'" + id + "'";
NpgsqlDataReader reader = com.ExecuteReader();
//List<String> temp = new List<string>();
geometryCollection singleObject = new geometryCollection();
while (reader.Read())
{
//temp.Add(reader[0].ToString());
singleObject.id = reader.GetInt64(0);
singleObject.name = reader.GetString(1);
singleObject.version = new GaPSlabsVersion()
{
versionTitle = reader.GetString(2), major = reader.GetInt32(3), minor = reader.GetInt32(4)
};
singleObject.format = reader.GetString(5);
if (includeLargeObject && reader.GetInt64(6) != 0)
{
singleObject.largeObject = GetLargeObject((int)reader.GetInt64(6), connectionString);
}
else
{
singleObject.largeObject = null;
}
singleObject.large_object_reference = (int)reader.GetInt64(6);
singleObject.latitude = reader.GetInt32(7);
singleObject.longitude = reader.GetInt32(8);
singleObject.pivot = new Vector3GaPS()
{
x = (float)reader.GetDecimal(9), y = (float)reader.GetDecimal(10), z = (float)reader.GetDecimal(11)
};
singleObject.gisId = reader.GetInt32(12);
singleObject.gisType = reader.GetString(13);
singleObject.lastUpdate = reader.GetTimeStamp(14).ToDateTime();
break;
}
if (!reader.IsClosed)
{
reader.Close();
}
con.Close();
return(singleObject);
}
}//
private FacturaPrecio cargarFactPrec(NpgsqlDataReader reader)
{
return(new FacturaPrecio
{
Id_FacArt = reader.GetInt32(0),
Num_Fact = reader.GetString(1),
Cod_Arti = reader.GetString(2),
Descripcion = reader.GetString(3),
Costo = reader.GetDecimal(4),
Utilidad = reader.GetDecimal(5),
PrecioPub = reader.GetDecimal(6),
AplicDesc = reader.GetBoolean(7),
RelFactPrecio = reader.GetInt32(8),
});
}
public List <ContaAPagar> GetListaTodasContasByOrigem(int origem_id)
{
List <ContaAPagar> lista = new List <ContaAPagar>();
string stringSQL = "select " +
"c.conpg_pk, c.conpg_descricao, c.conpg_data, c.conpg_vencimento, " +
"c.conpg_valor, c.org_pk, o.org_descricao, p.pag_valorpago " +
"from contas_pagar c inner join origens_conta o on c.org_pk = o.org_pk " +
"left join pagamentos p on c.conpg_pk = p.conpg_pk " +
"where c.org_pk = @origem";
NpgsqlCommand cmdConsultar = new NpgsqlCommand(stringSQL, this.Conexao);
this.Conexao.Open();
cmdConsultar.Parameters.AddWithValue("@origem", origem_id);
NpgsqlDataReader resultado = cmdConsultar.ExecuteReader();
if (resultado.HasRows)
{
while (resultado.Read())
{
ContaAPagar con = new ContaAPagar();
con.Id = resultado.GetInt32(0);
con.Descricao = resultado.GetString(1);
con.DataLancamento = resultado.GetDateTime(2);
con.Vencimento = resultado.GetDateTime(3);
con.Valor = resultado.GetDecimal(4);
if (!resultado.IsDBNull(7))
{
con.ValorPago = resultado.GetDecimal(7);
}
con.Origem = new OrigemContaAPagar()
{
Id = resultado.GetInt32(5),
Descricao = resultado.GetString(6)
};
lista.Add(con);
}
}
resultado.Close();
this.Conexao.Close();
return(lista);
}
public List <Producto> listarProductosPorProveedor(int idProveedor)
{
List <Producto> productos = new List <Producto>();
using (NpgsqlConnection con = conexion.GetConexion())
{
con.Open();
string sql = "SELECT * FROM products.pa_listarProductosPorProveedor(@idProveedor)";
using (var command = new NpgsqlCommand(sql, con))
{
command.Parameters.AddWithValue(":idProveedor", idProveedor);
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
productos.Add(new Producto(reader.GetInt32(0), reader.GetString(1),
reader.GetDecimal(2), reader.GetString(3),
reader.GetString(4), reader.GetBoolean(5),
reader.GetInt32(6))
);
}
}
}
}
return(productos);
}
public List <Property> GetProperty()
{
//DataAdapter
//DataReader
if (MyConnection.State == System.Data.ConnectionState.Closed)
{
MyConnection.Open();
}
string sql = "Select id, name, price, propertyPicture, location, username, description From property";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
NpgsqlDataReader reader = cmd.ExecuteReader();
List <Property> myResults = new List <Property>();
while (reader.Read())
{
myResults.Add(new Property()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Price = reader.GetDecimal(2),
PropertyPicture = reader.GetString(3),
Location = reader.GetString(4),
Username = reader.GetString(5),
Description = reader.GetString(6)
});
}
return(myResults);
}
public static List <BenhNhan> DanhSachBenhNhan()
{
var connectionstring = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=imbatman;Database=svthuctap;";
var query = "SELECT * FROM current.dmbenhnhan";
List <BenhNhan> list = new List <BenhNhan>();
NpgsqlConnection conn = new NpgsqlConnection(connectionstring);
try
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DateTime ngay = reader.GetDateTime(3);
string ngaysinh = ngay.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
var temp3 = reader.GetDecimal(4);
int gt = Decimal.ToInt32(temp3);
list.Add(new BenhNhan(reader.GetString(0), reader.GetString(1), reader.GetString(2), ngaysinh, gt));
}
conn.Close();
Console.WriteLine("Thành công");
}
catch (Exception)
{
Console.WriteLine("Thất bại");
}
return(list);
}
public Tuple <Dictionary <string, ulong>, bool> GetData(string connection_string)
{
Dictionary <string, ulong> data = new Dictionary <string, ulong>();
bool success = false;
using (NpgsqlConnection conn = new NpgsqlConnection(connection_string))
{
conn.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(Query, conn))
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(0);
ulong sizeInMB = (ulong)reader.GetDecimal(2);
data[name] = sizeInMB;
success = true;
}
}
}
return(Tuple.Create(data, success));
}
public bool Get(string name, ref decimal v)
{
try
{
int ord = reader.GetOrdinal(name);
if (!reader.IsDBNull(ord))
{
v = reader.GetDecimal(ord);
return(true);
}
}
catch
{
}
return(false);
}
public string Sent_PrePro(int vcodpro)
{
string _ValorR = "";
dbSQLConn.ConecDb_Abrir();
NpgsqlDataReader Dr = null;
string Sql = "SELECT prepro FROM productos WHERE codpro = @codpro ORDER BY codpro Desc";
NpgsqlCommand cmd = new NpgsqlCommand(Sql, dbSQLConn.Cnn);
cmd.Parameters.AddWithValue("@codpro", vcodpro);
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
Dr.Read();
_ValorR = Dr.GetDecimal(0).ToString();
Dr.Close();
dbSQLConn.ConecDb_Close();
return _ValorR;
}
else
{
Dr.Close();
dbSQLConn.ConecDb_Close();
return "";
}
}
[WebMethod] //atributo permite que el método pueda ser llamado desde clientes web remotos, en este caso desde la aplicación Android
public Restaurantes[] ListaRestaurant() // método permite recuperar datos desde una BD remota, agregándolos a una lista para luego retornarla como array en formato XML
{
using (var con = new NpgsqlConnection("Server=plop.inf.udec.cl;Port=5432;Database=cristobmunoz;User Id=cristobmunoz;Password=V24qe5;"))
{
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText = "SELECT id_restaurante, nombre, direccion, telefono, valoracion_r, tipo_r, foto, rut_a, id_horario, discapacitados FROM restclopedia.restaurante";
NpgsqlDataReader dataReader = cmd.ExecuteReader();
List <Restaurantes> lista = new List <Restaurantes>();
while (dataReader.Read())
{
lista.Add(
new Restaurantes(dataReader.GetInt32(0),
dataReader.GetString(1),
dataReader.GetString(2),
dataReader.GetInt32(3),
Convert.ToInt32(dataReader.GetDecimal(4)), //parche...
dataReader.GetString(5),
dataReader.GetString(6),
dataReader.GetString(7),
dataReader.GetInt32(8),
Convert.ToInt32(dataReader.GetBoolean(9))
));
}
con.Close();
return(lista.ToArray());
}
}
public decimal CalculateTotalDonationAmount()
{
decimal total = 0;
if (OpenConnection())
{
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
command.CommandText =
"SELECT COALESCE(SUM(donation_amount),0) FROM donation;";
NpgsqlDataReader dataReader = command.ExecuteReader();
dataReader.Read();
total = dataReader.GetDecimal(0);
CloseConnection();
}
return(total);
}
public static List <BenhNhan> ShowBenhNhanFromDB(String mabn)
{
//Lấy connection
BenhNhanRepository getstring = new BenhNhanRepository();
string connectstring = getstring.GetConnectString();
//Câu truy vấn dữ liệu với điều kiện Mã bệnh nhân
var query = "SELECT * FROM current.dmbenhnhan WHERE mabn = '" + mabn + "'";
List <BenhNhan> list = new List <BenhNhan>();
NpgsqlConnection conn = new NpgsqlConnection(connectstring);
try
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DateTime temp = reader.GetDateTime(3);
string temp2 = temp.ToShortDateString();
var temp3 = reader.GetDecimal(4);
int temp4 = Decimal.ToInt32(temp3);
list.Add(new BenhNhan(reader.GetString(0), reader.GetString(1), reader.GetString(2), temp2, temp4));
}
conn.Close();
Console.WriteLine("Thành công");
return(list);
}
catch (Exception)
{
Console.WriteLine("Thất bại");
return(list);
}
}
/// <summary>
/// Samuel Serrano
/// Método que obtiene un producto específico de la base de datos (por ID)
/// </summary>
/// <param name="idProducto"></param>
/// <returns>objeto Producto</returns>
public Producto buscarProducto(int idProducto)
{
Producto producto = new Producto();
using (NpgsqlConnection con = conexion.GetConexion())
{
con.Open();
string sql = "SELECT id_producto,nombre,precio,url_img,detalle,activo,cantidad,id_proveedor FROM products.producto "
+ " WHERE activo<>false AND id_producto = @idProducto";
using (var command = new NpgsqlCommand(sql, con))
{
command.Parameters.AddWithValue("@idProducto", idProducto);
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
producto = new Producto(reader.GetInt32(0), reader.GetString(1),
reader.GetDecimal(2), reader.GetString(3),
reader.GetString(4), reader.GetBoolean(5),
reader.GetInt32(6), proveedorDatos.buscarProveedor(reader.GetInt32(7)));
}
}
}
}
return(producto);
}
public ContaAPagar GetContaById(int conta_id)
{
ContaAPagar conta = null;
string stringSQL = "select conpg_pk, conpg_valor " +
"from contas_pagar " +
"where conpg_pk = @conta";
NpgsqlCommand cmdConsultar = new NpgsqlCommand(stringSQL, this.Conexao);
cmdConsultar.Parameters.AddWithValue("@conta", conta_id);
NpgsqlDataReader resultado = cmdConsultar.ExecuteReader();
if (resultado.HasRows)
{
while (resultado.Read())
{
conta = new ContaAPagar();
conta.Id = resultado.GetInt32(0);
conta.Valor = resultado.GetDecimal(1);
}
}
resultado.Close();
return(conta);
}
//Lista todos los productos que hay en BD, independientemente del estado
public List <Producto> listar_todos_productos()
{
List <Producto> productos = new List <Producto>();
using (NpgsqlConnection con = conexion.GetConexion())
{
con.Open();
string sql = "SELECT id_producto,nombre,precio,url_img,detalle,cantidad,activo FROM products.producto";
using (var command = new NpgsqlCommand(sql, con))
{
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
productos.Add(new Producto(
reader.GetInt32(0),
reader.GetString(1),
reader.GetDecimal(2),
reader.GetString(3),
reader.GetString(4),
reader.GetInt16(5),
reader.GetBoolean(6))
);
}
}
}
}
return(productos);
}
public static long?SafeGetLongNpg(this NpgsqlDataReader rd, int col, long?valorDefecto)
{
if (rd.IsDBNull(col))
{
return(valorDefecto);
}
string tipo = rd.GetDataTypeName(col);
long? regreso = 0;
switch (tipo.ToUpper())
{
case "INT2":
regreso = rd.GetInt16(col);
break;
case "INT4":
regreso = rd.GetInt32(col);
break;
case "INT8":
regreso = rd.GetInt64(col);
break;
case "SERIAL":
regreso = rd.GetInt32(col);
break;
default:
regreso = (long?)rd.GetDecimal(col);
break;
}
return(regreso);
}
public void NumericSupportNpgsqlDbType()
{
_conn.Open();
NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Numeric));
command.Parameters[0].Value = 7.4M;
Int32 rowsAdded = command.ExecuteNonQuery();
Assert.AreEqual(1, rowsAdded);
command.CommandText = "select * from tableb where field_numeric = :a";
NpgsqlDataReader dr = command.ExecuteReader();
dr.Read();
Decimal result = dr.GetDecimal(3);
command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
command.Parameters.Clear();
command.ExecuteNonQuery();
Assert.AreEqual(7.4000000M, result);
}
public Restaurantes[] ListaRestaurant()
{
using (var con = new NpgsqlConnection("Server=plop.inf.udec.cl;Port=5432;Database=cristobmunoz;User Id=cristobmunoz;Password=V24qe5;"))
{
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText = "SELECT id_restaurante, nombre, direccion, telefono, valoracion_r, tipo_r, foto, rut_a, id_horario FROM restclopedia.restaurante";
NpgsqlDataReader dataReader = cmd.ExecuteReader();
List <Restaurantes> lista = new List <Restaurantes>();
while (dataReader.Read())
{
lista.Add(
new Restaurantes(dataReader.GetInt32(0),
dataReader.GetString(1),
dataReader.GetString(2),
dataReader.GetInt32(3),
Convert.ToInt32(dataReader.GetDecimal(4)), //parche...
dataReader.GetString(5),
dataReader.GetString(6),
dataReader.GetString(7),
dataReader.GetInt32(8)
));
}
con.Close();
return(lista.ToArray());
}
}
public List <vwtb_pacientes_para_egreso_Info> Get_List_Paciente(int IdEmpresa, NpgsqlConnection cc)
{
try
{
List <vwtb_pacientes_para_egreso_Info> lista_ = new List <vwtb_pacientes_para_egreso_Info>();
string sql = string.Empty;
sql = "SELECT idempresa,idingreso,idcuenta,pe_cedularuc,pe_nombrecompleto,idplan,nom_plan,idestado,nom_estado,fecha_ingreso,fecha_salida FROM vwtb_pacientes_para_egreso where vwtb_pacientes_para_egreso.idempresa=" + IdEmpresa;
NpgsqlCommand cmd = new NpgsqlCommand(sql, cc);
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.IsDBNull(0) == false)
{
vwtb_pacientes_para_egreso_Info info = new vwtb_pacientes_para_egreso_Info();
info.IdEmpresa = Convert.ToInt32(reader.GetDecimal(0));
if (!reader.IsDBNull(1))
{
info.IdIngreso = Convert.ToDecimal(reader.GetInt32(1));
}
if (!reader.IsDBNull(2))
{
info.IdCuenta = Convert.ToDecimal(reader.GetInt32(2));
}
info.pe_cedulaRuc = reader.GetString(3);
info.pe_nombreCompleto = reader.GetString(4);
info.IdPlan = reader.GetInt32(5);
info.nom_plan = reader.GetString(6);
if (!reader.IsDBNull(7))
{
info.IdEstado = Convert.ToInt32(reader.GetString(7));
}
info.nom_estado = reader.GetString(8);
if (!reader.IsDBNull(9))
{
info.Fecha_ingreso = Convert.ToDateTime(reader.GetTimeStamp(9));
}
if (!reader.IsDBNull(10))
{
info.Fecha_salida = Convert.ToDateTime(reader.GetTimeStamp(10));
}
lista_.Add(info);
}
}
reader.Close();
cc.Close();
return(lista_);
}
catch (Exception ex)
{
string arreglo = ToString();
tb_sis_Log_Error_Vzen_Data oDataLog = new tb_sis_Log_Error_Vzen_Data();
tb_sis_Log_Error_Vzen_Info Log_Error_sis = new tb_sis_Log_Error_Vzen_Info(ex.ToString(), "", arreglo, "", "", "", "", "", DateTime.Now);
oDataLog.Guardar_Log_Error(Log_Error_sis, ref mensaje);
mensaje = ex.InnerException + " " + ex.Message;
throw new Exception(ex.ToString());
}
}
public IHttpActionResult getMaterialsStage(int id)
{
List <Material> materials = new List <Material>();
using (NpgsqlConnection connection = DataBase.getConnection())
{
NpgsqlCommand command = new NpgsqlCommand("obtenermaterialesetapa", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pidrelacion", connection).Value = id;
try
{
connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Material material = new Material();
material._id = reader.GetInt64(0);
material._nombre = reader.GetString(1);
material._precio = reader.GetDecimal(2);
material._cantidadDisponible = reader.GetInt32(3);
materials.Add(material);
}
return(Json(materials));
}
catch (NpgsqlException ex) { return(Json(2)); }
finally { connection.Close(); }
}
}
public IEnumerable <Goal> SelectGoals()
{
var connection = new NpgsqlConnection(connStr);
connection.Open();
var dataAdapter = new NpgsqlDataAdapter();
var command = new NpgsqlCommand("SELECT * FROM smartsaver.goal;", connection);
//dataAdapter.SelectCommand = command;
using NpgsqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
yield return(new Goal
{
Id = rdr.GetInt32(0),
Title = rdr.GetString(1),
Description = rdr.GetString(2),
Amount = rdr.GetDecimal(3),
Deadlinedate = rdr.GetDateTime(4),
Creationdate = rdr.GetDateTime(5)
});
}
connection.Close();
dataAdapter.Dispose();
}
public static decimal?SafeGetDecimalNpg(this NpgsqlDataReader rd, int col, decimal?valorDefecto)
{
if (!rd.IsDBNull(col))
{
return(valorDefecto);
}
return(rd.GetDecimal(col));
}
public void GetLisMOVINVENTARIO(int vcodmov, ListView Lista)
{
dbSQLConn.ConecDb_Abrir();
string[] arr = new string[7];
ListViewItem itm;
Lista.Items.Clear();
NpgsqlDataReader Dr = null;
string strSQL = "SELECT MOVINV.codpro, PRODUCTOS.despro, MOVINV.undunm, MOVINV.canmov, MOVINV.catmov, MOVINV.cosmov, MOVINV.totmov "
+ "FROM MOVINV INNER JOIN PRODUCTOS ON MOVINV.codpro = PRODUCTOS.codpro "
+ "WHERE MOVINV.codmov = @codmov AND MOVINV.tiptid= 'INV' "
+ "ORDER BY MOVINV.codpro ASC ";
NpgsqlCommand cmd = new NpgsqlCommand(strSQL, dbSQLConn.Cnn);
cmd.Parameters.AddWithValue("@codmov", vcodmov);
Dr = cmd.ExecuteReader();
if (Dr.HasRows)
{
int COLC = 0;
while (Dr.Read())
{
arr[0] = Dr.GetInt32(0).ToString().PadLeft(8, '0');
arr[1] = Dr.GetString(1).Trim();
arr[2] = Dr.GetString(2).Trim();
arr[3] = string.Format("{0:0.00}", Dr.GetDecimal(3));
arr[4] = Dr.GetInt32(4).ToString();
arr[5] = string.Format("{0:0.00}", Dr.GetDecimal(5));
arr[6] = string.Format("{0:0.00}", Dr.GetDecimal(6));
itm = new ListViewItem(arr);
Lista.Items.Add(itm);
if (COLC % 2 == 0)
{
Lista.Items[COLC].BackColor = Color.AliceBlue;
}
COLC++;
}
Dr.Close();
dbSQLConn.ConecDb_Close();
}
else
{
Dr.Close();
dbSQLConn.ConecDb_Close();
}
}
}//
private Articulo cargaArticulo3(NpgsqlDataReader reader)
{
try
{
return(new Articulo
{
Cantidad_Invt = reader.GetInt32(0),
Precio_Cost = reader.GetDecimal(1),
Precio_Venta = reader.GetDecimal(2),
Fecha_Compra = reader.GetDateTime(3),
Impuesto = reader.GetBoolean(4)
});
}
catch (Exception ex)
{
throw;
}
}
