public IEnumerable <Familia> GetByDescripcion()
{
try
{
using (var db = FarmaciaContext.Default())
{
var sql = @"select nombre from familias WHERE nombre NOT IN ('ESPECIALIDAD', 'EFP', 'SIN FAMILIA') AND nombre NOT LIKE '%ESPECIALIDADES%' AND nombre NOT LIKE '%Medicamento%'";
return(db.Database.SqlQuery <Familia>(sql)
.ToList());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetByDescripcion());
}
}
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());
}
}
public string GetSubCategoriaById(string id)
{
try
{
using (var db = FarmaciaContext.Default())
{
var sql = @"SELECT nombre FROM Subcategorias WHERE IdCategoria = @id";
return(db.Database.SqlQuery <string>(sql,
new OleDbParameter("id", int.Parse(id)))
.FirstOrDefault());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetSubCategoriaById(id));
}
}
public IEnumerable <string> GetAllNombreSubcategoriaByCategoriaId(long id)
{
try
{
using (var db = FarmaciaContext.Default())
{
var sql = @"SELECT nombre FROM Subcategorias WHERE IdCategoria = @id";
return(db.Database.SqlQuery <string>(sql,
new OleDbParameter("id", (int)id))
.ToList());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllNombreSubcategoriaByCategoriaId(id));
}
}
public string GetOneByFarmacoId(long farmaco)
{
try
{
var farmacoInteger = (int)farmaco;
using (var db = FarmaciaContext.Default())
{
var sql = @"select Cod_Barra from codigo_barra where ID_farmaco = @farmaco";
return(db.Database.SqlQuery <string>(sql,
new OleDbParameter("farmaco", farmacoInteger))
.FirstOrDefault());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetOneByFarmacoId(farmaco));
}
}
public Categoria GetOneOrDefaultById(long id)
{
try
{
var idInteger = (int)id;
using (var db = FarmaciaContext.Default())
{
var sql = @"SELECT Nombre FROM categorias WHERE IDCategoria = @id";
return(db.Database.SqlQuery <Categoria>(sql,
new OleDbParameter("id", idInteger))
.FirstOrDefault());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetOneOrDefaultById(id));
}
}
public IEnumerable <Sinonimo> GetAll()
{
try
{
using (var db = FarmaciaContext.Default())
{
var sql = @"SELECT Cod_Barra as Serial, ID_Farmaco as Farmaco FROM codigo_barra";
return(db.Database.SqlQuery <DTO.CodigoBarra>(sql)
//.Take(1000)
.ToList()
.Select(x => new Sinonimo {
CodigoBarra = x.Serial, CodigoNacional = x.Farmaco.ToString()
}));
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAll());
}
}
public Laboratorio GetOneOrDefaultByCodigo(string codigo)
{
try
{
using (var db = FarmaciaContext.Default())
{
var sql = "SELECT ID_Laboratorio as Codigo, Nombre FROM Laboratorios WHERE ID_Laboratorio = @codigo";
return(db.Database.SqlQuery <Laboratorio>(sql,
new OleDbParameter("codigo", codigo))
.FirstOrDefault()
?? new Laboratorio {
Codigo = codigo, Nombre = LABORATORIO_DEFAULT
});
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetOneOrDefaultByCodigo(codigo));
}
}
public Subcategoria GetSubcategoriaOneOrDefaultByKey(long categoria, long id)
{
try
{
var categInteger = (int)categoria;
var idInteger = (int)id;
using (var db = FarmaciaContext.Default())
{
var sql = "SELECT Nombre FROM subcategorias WHERE IdSubCategoria = @id AND IdCategoria = @categoria";
return(db.Database.SqlQuery <Subcategoria>(sql,
new OleDbParameter("id", idInteger),
new OleDbParameter("categoria", categInteger))
.FirstOrDefault());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetSubcategoriaOneOrDefaultByKey(categoria, id));
}
}
public IEnumerable <Categoria> GetAllByDescripcion()
{
try
{
var rs = new List <Categoria>();
using (var db = FarmaciaContext.Default())
{
var sql = @"select IDCategoria as Id, Nombre from categorias WHERE nombre NOT IN ('ESPECIALIDAD', 'EFP', 'SIN FAMILIA') AND nombre NOT LIKE '%ESPECIALIDADES%' AND nombre NOT LIKE '%Medicamento%'";
rs = db.Database.SqlQuery <DTO.Categoria>(sql)
.Select(x => new Categoria {
Id = x.Id, Nombre = x.Nombre
})
.ToList();
}
rs.ForEach(item =>
item.Subcategorias = GetAllNombreSubcategoriaByCategoriaId(item.Id));
return(rs);
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllByDescripcion());
}
}