public IEnumerable <DE.ProveedorHistorico> GetAllHistoricosByFecha(DateTime fecha) { try { var rs = Enumerable.Empty <DTO.ProveedorHistorico>(); using (var db = FarmaciaContext.RecepcionByYear(fecha.Year)) { var sql = $@"SELECT ID_Farmaco as FarmacoId, Proveedor, ID_Fecha as Fecha, PVAlb as PVAlbaran, PC FROM Recepcion WHERE ID_Fecha >= #{fecha.ToString("MM-dd-yyyy HH:mm:ss")}# GROUP BY ID_Farmaco, Proveedor, ID_Fecha, PVAlb, PC ORDER BY ID_Fecha DESC"; rs = db.Database.SqlQuery <DTO.ProveedorHistorico>(sql) .Where(r => r.Fecha.HasValue) .Where(r => r.Proveedor.HasValue) .ToList(); } return(rs.Select(x => new DE.ProveedorHistorico { Id = x.Proveedor.Value, FarmacoId = x.Farmaco, Fecha = x.Fecha.Value, PUC = x.PC > 0 ? x.PC * _factorCentecimal : x.PVAlbaran > 0 ? x.PVAlbaran * _factorCentecimal : 0m })); } catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed)) { return(GetAllHistoricosByFecha(fecha)); } }
public Familia GetSubFamiliaOneOrDefault(long familia, string subFamilia) { var conn = FarmaciaContext.GetConnection(); try { conn.Open(); var sql = $@"select DESCRIPCION from appul.ab_subfamilias where fam_codigo = {familia} AND codigo = '{subFamilia}'"; var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); var descripcion = string.Empty; if (reader.Read()) { descripcion = Convert.ToString(reader["DESCRIPCION"]) ?? string.Empty; } reader.Close(); reader.Dispose(); return(new Familia { Nombre = descripcion }); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
public List <Venta> GetAllByIdGreaterOrEqual(long id, DateTime fecha, string empresa) { var conn = FarmaciaContext.GetConnection(); try { var sql = $@"SELECT * FROM (SELECT * FROM appul.ah_ventas WHERE emp_codigo = '{empresa}' AND situacion = 'N' AND operacion >= {id} AND fecha_venta >= to_date('01/01/{fecha.Year}', 'DD/MM/YYYY') AND fecha_venta >= to_date('{fecha.ToString("dd/MM/yyyy")}', 'DD/MM/YYYY') ORDER BY fecha_venta ASC) WHERE ROWNUM <= 999"; conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); var ventas = new List <Venta>(); while (reader.Read()) { var fechaVenta = Convert.ToDateTime(reader["FECHA_VENTA"]); var fechaFin = !Convert.IsDBNull(reader["FECHA_FIN"]) ? (DateTime?)Convert.ToDateTime(reader["FECHA_FIN"]) : null; var cliCodigo = !Convert.IsDBNull(reader["CLI_CODIGO"]) ? (long)Convert.ToInt32(reader["CLI_CODIGO"]) : 0; var tipoOperacion = Convert.ToString(reader["TIPO_OPERACION"]); var operacion = Convert.ToInt64(reader["OPERACION"]); var puesto = Convert.ToString(reader["PUESTO"]); var usrCodigo = Convert.ToString(reader["USR_CODIGO"]); var importeVentaE = !Convert.IsDBNull(reader["IMPORTE_VTA_E"]) ? Convert.ToDecimal(reader["IMPORTE_VTA_E"]) : default(decimal); var empCodigo = Convert.ToString(reader["EMP_CODIGO"]); ventas.Add(new Venta { ClienteId = cliCodigo, FechaFin = fechaFin, FechaHora = fechaVenta, TipoOperacion = tipoOperacion, Operacion = operacion, Puesto = puesto, VendedorCodigo = usrCodigo, TotalDescuento = importeVentaE, EmpresaCodigo = empCodigo }); } reader.Close(); reader.Dispose(); return(ventas); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
private static void Initialize() { //MessageBox.Show("Inicializando"); try { var dir = ConfigurationManager.AppSettings["Directory.Setup"]; var path = ConfigurationManager.AppSettings["File.Remote.Server"]; var stream = new StreamReader(Path.Combine(dir, path)); //var remoteServer = "https://sisfarma.es/api-cuadromandos";//stream.ReadLine(); //var remoteToken = "f3d0b8171f8b6c1ed0566ca1570c86cc";//stream.ReadLine(); var remoteServer = stream.ReadLine(); var remoteToken = stream.ReadLine(); SisfarmaFactory.Setup(remoteServer, remoteToken); var local = GetConnexionLocal(remoteServer, remoteToken); FarmaciaContext.Setup(local.localServer, local.localUser, local.localPass, local.marketCodeList); } catch (IOException ex) { MessageBox.Show("Error: " + ex.Message); throw new IOException("Ha habido un error en la lectura de algún fichero de configuración. Compruebe que existen dichos ficheros de configuración."); } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); } }
private static void Initialize() { try { var dir = ConfigurationManager.AppSettings["Directory.Setup"]; var path = ConfigurationManager.AppSettings["File.Remote.Server"]; var stream = new StreamReader(Path.Combine(dir, path)); var remoteServer = stream.ReadLine(); remoteServer = remoteServer.Replace("https://sisfarma.es", "https://api.sisfarma.es"); remoteServer = remoteServer.Replace("https://sisfarma.pro", "https://api.sisfarma.pro"); remoteServer = remoteServer.Replace("https://sistemasfarmaceuticos.es", "https://api.sistemasfarmaceuticos.es"); var remoteToken = stream.ReadLine(); SisfarmaFactory.Setup(remoteServer, remoteToken); var local = GetConnexionLocal(remoteServer, remoteToken); FarmaciaContext.Setup(local.pathFicheros, local.password, local.marketCodeList); } catch (IOException) { throw new IOException("Ha habido un error en la lectura de algún fichero de configuración. Compruebe que existen dichos ficheros de configuración."); } }
public List <Venta> GetAllByIdGreaterOrEqual(int year, long value) { try { // Access no handlea long var valueInteger = (int)value; try { using (var db = FarmaciaContext.VentasByYear(year)) { var sql = @"SELECT TOP 999 ID_VENTA as Id, Fecha, NPuesto as Puesto, Cliente, Vendedor, Descuento, Pago, Tipo, Importe FROM ventas WHERE year(fecha) >= @year AND ID_VENTA >= @value ORDER BY ID_VENTA ASC"; return(db.Database.SqlQuery <DTO.Venta>(sql, new OleDbParameter("year", year), new OleDbParameter("value", valueInteger)) .Select(GenerarVentaEncabezado) .ToList()); } } catch (FarmaciaContextException) { return(new List <Venta>()); } } catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed)) { return(GetAllByIdGreaterOrEqual(year, value)); } }
public IEnumerable <DTO.Recepcion> GetAllByDateAsDTO(DateTime fecha) { try { try { using (var db = FarmaciaContext.RecepcionByYear(fecha.Year)) { var sql = $@" SELECT ID_Fecha as Fecha, AlbaranID as Albaran, Proveedor, ID_Farmaco as Farmaco, PVP, PC, PVAlb as PVAlbaran, PCTotal, Recibido, Bonificado, Devuelto FROM Recepcion WHERE AlbaranID IN (SELECT alb.AlbaranID FROM (SELECT TOP 999 AlbaranID, ID_Fecha FROM Recepcion WHERE ID_Fecha > #{fecha.ToString("MM-dd-yyyy HH:mm:ss")}# AND (recibido <> 0 OR devuelto <> 0 OR bonificado <> 0) AND ID_Fecha IS NOT NULL AND AlbaranID IS NOT NULL GROUP BY AlbaranID, ID_Fecha ORDER BY ID_Fecha ASC) AS alb) AND #{fecha.ToString("MM-dd-yyyy HH:mm:ss")}# AND (recibido <> 0 OR devuelto <> 0 OR bonificado <> 0) AND ID_Fecha IS NOT NULL AND AlbaranID IS NOT NULL ORDER BY ID_Fecha ASC"; return(db.Database.SqlQuery <DTO.Recepcion>(sql) .ToList()); } } catch (FarmaciaContextException) { return(Enumerable.Empty <DTO.Recepcion>()); } } catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed)) { return(GetAllByDateAsDTO(fecha)); } }
public List <Venta> GetAllByIdGreaterOrEqual(long id, DateTime fecha) { try { try { using (var db = FarmaciaContext.VentasByYear(fecha.Year)) { var year = fecha.Year; var fechaInicial = fecha.Date.ToString("MM-dd-yyyy HH:mm:ss"); var sql = $@"SELECT ID_VENTA as Id, Fecha, NPuesto as Puesto, Cliente, Vendedor, Descuento, Pago, Tipo, Importe FROM ventas WHERE id_venta >= @id AND year(fecha) = @year AND fecha >= #{fechaInicial}# ORDER BY id_venta ASC"; return(db.Database.SqlQuery <DTO.Venta>(sql, new OleDbParameter("id", (int)id), new OleDbParameter("year", year)) .Select(GenerarVentaEncabezado) .ToList()); } } catch (FarmaciaContextException) { return(new List <Venta>()); } } catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed)) { return(GetAllByIdGreaterOrEqual(id, fecha)); } }
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 <DTO.Recepcion> GetAllByYearAsDTO(int year) { try { try { using (var db = FarmaciaContext.RecepcionByYear(year)) { var sql = $@" SELECT ID_Fecha as Fecha, AlbaranID as Albaran, Proveedor, ID_Farmaco as Farmaco, PVP, PC, PVAlb as PVAlbaran, PCTotal, Recibido, Bonificado, Devuelto FROM Recepcion WHERE AlbaranID IN (SELECT alb.AlbaranID FROM (SELECT TOP 999 AlbaranID, ID_Fecha FROM Recepcion WHERE YEAR(ID_Fecha) >= @year AND (recibido <> 0 OR devuelto <> 0 OR bonificado <> 0) AND ID_Fecha IS NOT NULL AND AlbaranID IS NOT NULL GROUP BY AlbaranID, ID_Fecha ORDER BY ID_Fecha ASC) AS alb) AND YEAR(ID_Fecha) >= @year AND (recibido <> 0 OR devuelto <> 0 OR bonificado <> 0) AND ID_Fecha IS NOT NULL AND AlbaranID IS NOT NULL ORDER BY ID_Fecha ASC"; return(db.Database.SqlQuery <DTO.Recepcion>(sql, new OleDbParameter("year", year)) .ToList()); } } catch (FarmaciaContextException) { return(Enumerable.Empty <DTO.Recepcion>()); } } catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed)) { return(GetAllByYearAsDTO(year)); } }
public Proveedor GetOneOrDefaultByCodigoNacional(string codigoNacional) { var conn = FarmaciaContext.GetConnection(); try { conn.Open(); var sql = $@"SELECT PROVEEDOR FROM ( SELECT PROVEEDOR FROM appul.ad_rec_linped WHERE cant_servida <> 0 AND art_codigo = '{codigoNacional}' ORDER BY fecha_recepcion DESC) WHERE ROWNUM <= 1"; var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); if (reader.Read() && !Convert.IsDBNull(reader["PROVEEDOR"])) { var id = Convert.ToInt64(reader["PROVEEDOR"]); sql = $@"SELECT NOMBRE_AB from appul.ad_proveedores where codigo = {id}"; cmd.CommandText = sql; reader = cmd.ExecuteReader(); if (reader.Read()) { var nombre = Convert.ToString(reader["NOMBRE_AB"]); reader.Close(); reader.Dispose(); return(new Proveedor { Id = id, Nombre = nombre }); } } reader.Close(); reader.Dispose(); return(new Proveedor { Nombre = string.Empty }); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
public Articulo GetOneOrDefaultById(string codigo) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT * FROM articu WHERE IdArticu = @codigo"; return(db.Database.SqlQuery <Articulo>(sql, new SqlParameter("codigo", codigo)) .FirstOrDefault()); } }
public List <Destinatario> GetByCliente(string cliente) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT * FROM Destinatario WHERE fk_Cliente_1 = @idCliente"; return(db.Database.SqlQuery <Destinatario>(sql, new SqlParameter("idCliente", cliente)) .ToList()); } }
public IEnumerable <Recepcion> GetByYear(int year) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT TOP 1000 * From Recep WHERE YEAR(Fecha) >= @year Order by IdRecepcion ASC"; return(db.Database.SqlQuery <Recepcion>(sql, new SqlParameter("year", year)) .ToList()); } }
public IEnumerable <Encargo> GetAllGreaterOrEqualByFecha(DateTime fecha) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT * From Encargo WHERE idFecha >= @fecha AND estado > 0 Order by idFecha DESC"; return(db.Database.SqlQuery <Encargo>(sql, new SqlParameter("fecha", fecha)) .ToList()); } }
public IEnumerable <LineaRecepcion> GetLineasById(int recepcion) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"select * from LINEARECEP where IdRecepcion = @recepcion AND (Recibidas <> 0 OR UDevolver <> 0)"; return(db.Database.SqlQuery <LineaRecepcion>(sql, new SqlParameter("recepcion", recepcion)) .ToList()); } }
public IEnumerable <Encargo> GetAllByContadorGreaterOrEqual(int year, long?contador) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT TOP 1000 * From Encargo WHERE year(idFecha) >= @year AND IdContador >= @contador Order by IdContador ASC"; return(db.Database.SqlQuery <Encargo>(sql, new SqlParameter("year", year), new SqlParameter("contador", contador ?? SqlInt64.Null)) .ToList()); } }
public RecepcionResume GetResumeById(int recepcion) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT ISNULL(COUNT(IdNLinea),0) AS numLineas, ISNULL(SUM(recibidas*ImportePvp),0) AS importePvp, ISNULL(SUM(importe),0) AS importePuc " + @"FROM LINEARECEP WHERE IdRecepcion = @recepcion AND (Recibidas <> 0 OR UDevolver <> 0)"; return(db.Database.SqlQuery <RecepcionResume>(sql, new SqlParameter("recepcion", recepcion)) .Single()); } }
public List <ArticuloWithIva> GetWithStockByIdGreaterOrEqual(string codArticulo) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"select top 1000 a.*, t.Piva AS iva from articu a INNER JOIN Tablaiva t ON t.IdTipoArt = a.XGrup_IdGrupoIva AND t.IdTipoPro = '05' " + @" WHERE a.Descripcion <> 'PENDIENTE DE ASIGNACIÓN' AND a.Descripcion <> 'VENTAS VARIAS' AND a.Descripcion <> ' BASE DE DATOS 3/03/2014' " + @" AND a.IdArticu >= @codArticulo AND a.StockActual > 0 ORDER BY a.IdArticu ASC"; return(db.Database.SqlQuery <ArticuloWithIva>(sql, new SqlParameter("codArticulo", codArticulo)) .ToList()); } }
public IEnumerable <Sinonimo> GetAll() { var conn = FarmaciaContext.GetConnection(); var sinonimos = new List <Sinonimo>(); try { conn.Open(); var sql = $@"SELECT * FROM appul.ad_crelativos"; var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); while (reader.Read()) { var rCodRelativo = Convert.ToString(reader["COD_RELATIVO"]); var rArtCodigo = Convert.ToString(reader["ART_CODIGO"]); sinonimos.Add(new Sinonimo { CodigoBarra = rCodRelativo, CodigoNacional = rArtCodigo }); } reader.Close(); sql = $@"SELECT codigo, ean_13 FROM appul.ab_articulos where not ean_13 is null group by codigo, ean_13"; cmd = conn.CreateCommand(); cmd.CommandText = sql; reader = cmd.ExecuteReader(); while (reader.Read()) { var rCodigo = Convert.ToString(reader["codigo"]); var rEan13 = Convert.ToString(reader["ean_13"]); sinonimos.Add(new Sinonimo { CodigoBarra = rEan13, CodigoNacional = rCodigo }); } reader.Close(); reader.Dispose(); MessageBox.Show("sinonimos cargados"); return(sinonimos); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
public IEnumerable <Recepcion> GetByIdAndYear(int year, long?pedido) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT TOP 1000 * From Recep WHERE IdRecepcion >= @pedido AND YEAR(Fecha) >= @year Order by IdRecepcion ASC"; return(db.Database.SqlQuery <Recepcion>(sql, new SqlParameter("year", year), new SqlParameter("pedido", pedido ?? SqlInt64.Null)) .ToList()); } }
public List <ArticuloWithIva> GetByFechaUltimaEntradaGreaterOrEqual(DateTime?fechaActualizacionStock) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"select top 1000 a.*, t.Piva AS iva from articu a INNER JOIN Tablaiva t ON t.IdTipoArt = a.XGrup_IdGrupoIva AND t.IdTipoPro = '05' " + @"WHERE a.Descripcion <> 'PENDIENTE DE ASIGNACIÓN' AND a.Descripcion <> 'VENTAS VARIAS' AND a.Descripcion <> ' BASE DE DATOS 3/03/2014' " + @"AND FechaUltimaEntrada >= @fechaActualizacion ORDER BY FechaUltimaEntrada ASC"; return(db.Database.SqlQuery <ArticuloWithIva>(sql, new SqlParameter("fechaActualizacion", fechaActualizacionStock ?? SqlDateTime.Null)) .ToList()); } }
public RecepcionTotales GetTotalesByPedidoAsDTO(int anio, long numeroPedido, string empresa) { var conn = FarmaciaContext.GetConnection(); try { var sqlExtra = string.Empty; var sql = $@" select NVL(COUNT(pedido),0) AS numLineas, NVL(SUM(cant_servida*pvp_iva_euros),0) AS importePvp, NVL(SUM(cant_servida*pc_iva_euros),0) AS importePuc from appul.ad_rec_linped where pedido = {numeroPedido} AND cant_servida <> 0 AND emp_codigo = '{empresa}' AND to_char(fecha_recepcion, 'YYYY') = {anio}"; conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); if (reader.Read()) { var rNumLineas = Convert.ToInt32(reader["numLineas"]); var rImportePvp = Convert.ToDecimal(reader["importePvp"]); var rImportePuc = Convert.ToDecimal(reader["importePuc"]); reader.Close(); reader.Dispose(); return(new RecepcionTotales { Lineas = rNumLineas, PVP = rImportePvp, PUC = rImportePuc }); } reader.Close(); reader.Dispose(); return(new RecepcionTotales()); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
public ArticuloWithIva GetControlArticuloSinStockFisrtOrDefault(string articulo) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"select TOP 1 idArticu from articu " + " WHERE Descripcion <> 'PENDIENTE DE ASIGNACIÓN' AND Descripcion <> 'VENTAS VARIAS' AND Descripcion <> ' BASE DE DATOS 3/03/2014' " + " AND IdArticu > @articulo AND StockActual <= 0 ORDER BY IdArticu ASC"; return(db.Database.SqlQuery <ArticuloWithIva>(sql, new SqlParameter("articulo", articulo)) .FirstOrDefault()); } }
public IEnumerable <DE.ProveedorHistorico> GetAllHistoricosByFecha(DateTime fecha) { var historicos = new List <DE.ProveedorHistorico>(); var conn = FarmaciaContext.GetConnection(); try { conn.Open(); var sql = $@" SELECT art_codigo, proveedor, fecha_pedido, pc_iva_euros FROM appul.ad_rec_linped WHERE to_char(fecha_pedido, 'YYYYMMDDHH24MISS') > '{fecha.ToString("yyyyMMddHHmmss")}' GROUP BY art_codigo, proveedor, fecha_pedido, pc_iva_euros ORDER BY fecha_pedido DESC"; var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); while (reader.Read()) { var rArtCodigo = Convert.ToString(reader["art_codigo"]); var rProveedor = !Convert.IsDBNull(reader["proveedor"]) ? Convert.ToInt64(reader["proveedor"]) : 0L; var rFechaPedido = !Convert.IsDBNull(reader["fecha_pedido"]) ? Convert.ToDateTime(reader["fecha_pedido"]) : DateTime.MinValue; var rPcIvaEuros = !Convert.IsDBNull(reader["pc_iva_euros"]) ? Convert.ToDecimal(reader["pc_iva_euros"]) : 0m; var historico = new DE.ProveedorHistorico { Id = rProveedor, FarmacoId = rArtCodigo, Fecha = rFechaPedido, PUC = rPcIvaEuros }; historicos.Add(historico); } reader.Close(); reader.Dispose(); return(historicos); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
public Laboratorio GetOneOrDefaultByCodigo(long codigo, string clase, string claseBot) { var conn = FarmaciaContext.GetConnection(); try { conn.Open(); var sql = $@"SELECT CODIGO, NOMBRE FROM appul.ab_laboratorios WHERE codigo = {codigo} AND clase = '{clase}' AND clase_bot = '{claseBot}'"; var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); var nombre = string.Empty; var numeroLaboratorio = 0L; if (reader.Read()) { numeroLaboratorio = Convert.ToInt64(reader["CODIGO"]); nombre = Convert.ToString(reader["NOMBRE"]) ?? string.Empty; var letraLaboratorio = clase != "1" ? "P" : claseBot == "V" ? "V" : "E"; reader.Close(); reader.Dispose(); return(new Laboratorio { Codigo = letraLaboratorio + $"{numeroLaboratorio}".PadLeft(4, '0'), Nombre = nombre }); } reader.Close(); reader.Dispose(); return(new Laboratorio { Codigo = string.Empty, Nombre = LABORATORIO_DEFAULT }); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
internal IEnumerable <RecepcionGroup> GetGroupGreaterOrEqualByFecha(DateTime fecha) { using (var db = FarmaciaContext.Create(_config)) { var sql = @"SELECT lr.XArt_IdArticu, r.XProv_IdProveedor, r.hora, lr.ImportePuc FROM Recep r " + @"INNER JOIN LineaRecep lr ON lr.IdRecepcion = r.IdRecepcion " + @"WHERE r.hora >= @fecha " + @"GROUP BY lr.XArt_IdArticu, r.XProv_IdProveedor, r.hora, lr.ImportePuc " + @"ORDER BY r.hora DESC"; return(db.Database.SqlQuery <RecepcionGroup>(sql, new SqlParameter("fecha", fecha)) .ToList()); } }
public IEnumerable <Familia> GetByDescripcion() { var conn = FarmaciaContext.GetConnection(); var familias = new List <Familia>(); try { conn.Open(); var sql = $@"select * from appul.ab_subfamilias WHERE descripcion NOT IN ('ESPECIALIDAD', 'EFP', 'SIN FAMILIA') AND descripcion NOT LIKE '%ESPECIALIDADES%' AND descripcion NOT LIKE '%Medicamento%'"; var cmd = conn.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); while (reader.Read()) { var rDescripcion = Convert.ToString(reader["DESCRIPCION"]); var rFamCodigo = Convert.ToString(reader["FAM_CODIGO"]); var rFamEmpCodigo = Convert.ToString(reader["FAM_EMP_CODIGO"]); sql = $@"select * from appul.ab_familias WHERE codigo = '{rFamCodigo}' AND emp_codigo = '{rFamEmpCodigo}'"; cmd = conn.CreateCommand(); cmd.CommandText = sql; var readerFamilia = cmd.ExecuteReader(); var padre = readerFamilia.Read() ? Convert.ToString(readerFamilia["DESCRIPCION"]) : "<SIN PADRE>"; familias.Add(new Familia { Nombre = rDescripcion, Padre = padre }); } reader.Close(); reader.Dispose(); return(familias); } catch (Exception ex) { throw; } finally { conn.Close(); conn.Dispose(); } }
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 IEnumerable <Subcategoria> GetAll() { try { using (var db = FarmaciaContext.Default()) { var sql = "select Nombre from subcategorias"; return(db.Database.SqlQuery <Subcategoria>(sql) .ToList()); } } catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed)) { return(GetAll()); } }