public IEnumerable <Lista> GetAllByIdGreaterThan(int id)
{
try
{
using (var db = FarmaciaContext.Proveedores())
{
var sql = @"SELECT ID_Bolsa as Id, Descripcion FROM DescrBolsas WHERE id_bolsa > @id";
var rs = db.Database.SqlQuery <Lista>(sql,
new OleDbParameter("id", id))
//.Take(1000)
.ToList();
foreach (var item in rs)
{
var sqlFarmacos = @"SELECT id_bolsa as ListaId, id_farmaco as FarmacoId FROM Bolsas WHERE id_bolsa = @id GROUP BY id_bolsa, id_farmaco";
var farmacos = db.Database.SqlQuery <ListaDetalle>(sqlFarmacos,
new OleDbParameter("id", item.Id))
.ToList();
item.Farmacos = farmacos;
}
return(rs);
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllByIdGreaterThan(id));
}
}
public IEnumerable <Proveedor> GetAll()
{
try
{
using (var db = FarmaciaContext.Proveedores())
{
var sql = "SELECT ID_Proveedor as Id, Nombre FROM proveedores";
return(db.Database.SqlQuery <Proveedor>(sql)
.ToList());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAll());
}
}
public Proveedor GetOneOrDefaultById(long id)
{
try
{
var idInteger = (int)id;
using (var db = FarmaciaContext.Proveedores())
{
var sql = "SELECT ID_Proveedor as Id, Nombre FROM Proveedores WHERE ID_Proveedor = @id";
return(db.Database.SqlQuery <Proveedor>(sql,
new OleDbParameter("id", idInteger))
.FirstOrDefault());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetOneOrDefaultById(id));
}
}
public IEnumerable <Encargo> GetAllByIdGreaterOrEqual(int year, long encargo)
{
try
{
var rs = Enumerable.Empty <DTO.Encargo>();
using (var db = FarmaciaContext.Proveedores())
{
var sql = @"SELECT ID_Encargo as Id, ID_Farmaco as Farmaco, ID_Cliente as Cliente, ID_Vendedor as Vendedor, Fecha_Hora as FechaHora, Fecha_Hora_Entrega as FechaHoraEntrega, Cantidad, Observaciones, Id_Proveedor as Proveedor From Encargos WHERE year(Fecha_Hora) >= @year AND Id_Encargo >= @encargo Order by Id_Encargo ASC";
rs = db.Database.SqlQuery <DTO.Encargo>(sql,
new OleDbParameter("year", year),
new OleDbParameter("encargos", (int)encargo))
.Take(10)
.ToList();
}
return(rs.Select(GenerarEncargo));
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllByIdGreaterOrEqual(year, encargo));
}
}
public IEnumerable <Pedido> GetAllByIdGreaterOrEqual(long pedido)
{
try
{
var rs = Enumerable.Empty <DTO.Pedido>();
using (var db = FarmaciaContext.Proveedores())
{
var sql = @"SELECT ID_NumPedido as Id, ID_Proveedor as Proveedor, ID_Farmaco as Farmaco, CantInicial, Fecha From recibir WHERE ID_NumPedido >= @pedido Order by ID_NumPedido ASC";
rs = db.Database.SqlQuery <DTO.Pedido>(sql,
new OleDbParameter("pedido", (int)pedido))
.Take(10)
.ToList();
}
var keys = rs.GroupBy(k => new PedidoCompositeKey {
Id = k.Id, Proveedor = k.Proveedor
});
return(GenerarPedidos(keys));
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllByIdGreaterOrEqual(pedido));
}
}
