public bool HasSexoField()
{
using (var ctx = new FarmaticContext())
{
var existFieldSexo = false;
// Chekeamos si existen los campos
var connection = ctx.Database.Connection;
var sql = "SELECT TOP 1 * FROM ClienteAux";
var command = connection.CreateCommand();
command.CommandText = sql;
connection.Open();
var reader = command.ExecuteReader();
var schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
if (row[schemaTable.Columns["ColumnName"]].ToString()
.Equals("sexo", StringComparison.CurrentCultureIgnoreCase))
{
existFieldSexo = true;
break;
}
}
connection.Close();
return(existFieldSexo);
}
}
public IEnumerable <Familia> Get()
{
using (var db = FarmaticContext.Create(_config))
{
var sql = "select * from familia";
return(db.Database.SqlQuery <Familia>(sql)
.ToList());
}
}
public List <Sinonimos> GetAll()
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM Sinonimo";
return(db.Database.SqlQuery <Sinonimos>(sql)
.ToList());
}
}
public void Update(int lista)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"UPDATE ListaArticu SET fecha = DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) WHERE idLista = @lista";
db.Database.ExecuteSqlCommand(sql,
new SqlParameter("lista", @lista));
}
}
public List <Familia> GetByDescripcion()
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"select * from familia WHERE descripcion NOT IN ('ESPECIALIDAD', 'EFP', 'SIN FAMILIA') AND descripcion NOT LIKE '%ESPECIALIDADES%' AND descripcion NOT LIKE '%Medicamento%'";
return(db.Database.SqlQuery <Familia>(sql)
.ToList());
}
}
public IEnumerable <Proveedor> GetAll()
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM proveedor";
return(db.Database.SqlQuery <Proveedor>(sql)
.ToList());
}
}
public IEnumerable <LineaPedido> GetLineasByPedido(int pedido)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"select * from lineaPedido where IdPedido = @pedido";
return(db.Database.SqlQuery <LineaPedido>(sql,
new SqlParameter("pedido", pedido))
.ToList());
}
}
public IEnumerable <Pedido> GetByFechaGreaterOrEqual(DateTime fecha)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * From pedido WHERE Fecha >= @fecha Order by IdPedido ASC";
return(db.Database.SqlQuery <Pedido>(sql,
new SqlParameter("fecha", fecha))
.ToList());
}
}
public IEnumerable <Pedido> GetByIdGreaterOrEqual(long?pedido)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * From pedido WHERE IdPedido >= @pedido Order by IdPedido ASC";
return(db.Database.SqlQuery <Pedido>(sql,
new SqlParameter("pedido", pedido ?? SqlInt64.Null))
.ToList());
}
}
public IEnumerable <LineaRecepcion> GetLineasById(int recepcion)
{
using (var db = FarmaticContext.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 <Recepcion> GetByYear(int year)
{
using (var db = FarmaticContext.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 List <ItemListaArticulo> GetArticulosByLista(int lista)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM ItemListaArticu WHERE XItem_IdLista = @lista GROUP BY XItem_IdLista, XItem_IdArticu";
return(db.Database.SqlQuery <ItemListaArticulo>(sql,
new SqlParameter("lista", lista))
.ToList());
}
}
public List <ListaArticulo> GetByFechaExceptList(int lista)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM ListaArticu WHERE fecha >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND idLista <> @lista";
return(db.Database.SqlQuery <ListaArticulo>(sql,
new SqlParameter("lista", lista))
.ToList());
}
}
public IEnumerable <ListaArticulo> GetByIdGreaterThan(int lista)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM ListaArticu WHERE idLista > @lista";
return(db.Database.SqlQuery <ListaArticulo>(sql,
new SqlParameter("lista", lista))
.ToList());
}
}
public IEnumerable <Encargo> GetAllGreaterOrEqualByFecha(DateTime fecha)
{
using (var db = FarmaticContext.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 Vendedor GetOneOrDefaultById(short?idVendedor)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM vendedor WHERE IdVendedor = @idVendedor";
return(db.Database.SqlQuery <Vendedor>(sql,
new SqlParameter("idVendedor", idVendedor ?? SqlInt16.Null))
.FirstOrDefault());
}
}
public Sinonimos GetOneOrDefaultByArticulo(string codigo)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM sinonimo WHERE IdArticu = @codigo";
return(db.Database.SqlQuery <Sinonimos>(sql,
new SqlParameter("codigo", codigo))
.FirstOrDefault());
}
}
public Laboratorio GetById(string codigo)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM laboratorio WHERE codigo = @codigo";
return(db.Database.SqlQuery <Laboratorio>(sql,
new SqlParameter("codigo", codigo))
.FirstOrDefault());
}
}
public Familia GetById(short id)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM familia WHERE IdFamilia = @id";
return(db.Database.SqlQuery <Familia>(sql,
new SqlParameter("id", id))
.FirstOrDefault());
}
}
public Cliente GetOneOrDefaulById(int dni)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM Cliente WHERE Idcliente = @dni";
return(db.Database.SqlQuery <Cliente>(sql,
new SqlParameter("dni", dni))
.FirstOrDefault());
}
}
public List <Destinatario> GetByCliente(string cliente)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM Destinatario WHERE fk_Cliente_1 = @idCliente";
return(db.Database.SqlQuery <Destinatario>(sql,
new SqlParameter("idCliente", cliente))
.ToList());
}
}
public T GetAuxiliarById <T>(string cliente) where T : ClienteAux
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM ClienteAux WHERE idCliente = @idCliente";
return(db.Database.SqlQuery <T>(sql,
new SqlParameter("idCliente", cliente))
.FirstOrDefault());
}
}
public decimal GetTotalPuntosById(string idCliente)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT ISNULL(SUM(cantidad), 0) AS puntos FROM HistoOferta WHERE IdCliente = @idCliente AND TipoAcumulacion = 'P'";
return(db.Database.SqlQuery <decimal>(sql,
new SqlParameter("idCliente", idCliente))
.FirstOrDefault());
}
}
public Proveedor GetOneOrDefault(string id)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = "SELECT * FROM Proveedor WHERE IDProveedor = @id";
return(db.Database.SqlQuery <Proveedor>(sql,
new SqlParameter("id", id))
.FirstOrDefault());
}
}
public List <LineaVenta> GetLineasVentaByVenta(int venta)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM lineaventa WHERE IdVenta = @idVenta";
return(db.Database.SqlQuery <LineaVenta>(sql,
new SqlParameter("idVenta", venta))
.ToList());
}
}
public Venta GetOneOrDefaultById(long venta)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT * FROM venta WHERE IdVenta = @venta ORDER BY IdVenta ASC";
return(db.Database.SqlQuery <Venta>(sql,
new SqlParameter("venta", venta))
.FirstOrDefault());
}
}
public List <Cliente> GetGreatThanId(int id)
{
using (var db = FarmaticContext.Create(_config))
{
var sql =
@"SELECT TOP 1000 * FROM cliente WHERE Idcliente > @ultimoCliente ORDER BY CAST(Idcliente AS DECIMAL(20)) ASC";
return(db.Database.SqlQuery <Cliente>(sql,
new SqlParameter("ultimoCliente", id))
.ToList());
}
}
public List <Venta> GetByIdGreaterOrEqual(int year, long value)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT TOP 1000 * FROM venta WHERE ejercicio >= @year AND IdVenta >= @value ORDER BY IdVenta ASC";
return(db.Database.SqlQuery <Venta>(sql,
new SqlParameter("year", year),
new SqlParameter("value", value))
.ToList());
}
}
public List <LineaVentaVirtual> GetLineasVirtualesByVenta(int venta)
{
using (var db = FarmaticContext.Create(_config))
{
var sql =
@"SELECT * FROM lineaventavirtual WHERE IdVenta = @venta AND (codigo = 'Pago' OR codigo = 'A Cuenta')";
return(db.Database.SqlQuery <LineaVentaVirtual>(sql,
new SqlParameter("venta", venta))
.ToList());
}
}
public List <Venta> GetVirtualesLessThanId(long venta)
{
using (var db = FarmaticContext.Create(_config))
{
var sql = @"SELECT v.* FROM venta v INNER JOIN lineaventavirtual lvv ON lvv.idventa = v.idventa AND (lvv.codigo = 'Pago' OR lvv.codigo = 'A Cuenta') " +
@"WHERE v.ejercicio >= 2015 AND v.IdVenta < @venta ORDER BY v.IdVenta DESC";
return(db.Database.SqlQuery <Venta>(sql,
new SqlParameter("venta", venta))
.ToList());
}
}
