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