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 <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 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 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 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 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 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 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 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 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());
}
}
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 <Encargo> GetAllGreaterOrEqualByFecha(DateTime fecha)
{
try
{
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());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllGreaterOrEqualByFecha(fecha));
}
}
public List <Destinatario> GetByCliente(string cliente)
{
try
{
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());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetByCliente(cliente));
}
}
public IEnumerable <Encargo> GetAllByContadorGreaterOrEqual(int year, long?contador)
{
try
{
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());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllByContadorGreaterOrEqual(year, contador));
}
}