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