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 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 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 IEnumerable <DE.Recepcion> GetAllByYear(int year)
{
try
{
try
{
var rs = Enumerable.Empty <DTO.Recepcion>();
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";
rs = db.Database.SqlQuery <DTO.Recepcion>(sql,
new OleDbParameter("year", year))
.ToList();
}
var keys = rs.GroupBy(k => new { k.Fecha.Value.Year, k.Albaran.Value })
.ToDictionary(
k => new RecepcionCompositeKey {
Anio = k.Key.Year, Albaran = k.Key.Value
},
v => v.ToList());
return(GenerarRecepciones(keys));
}
catch (FarmaciaContextException)
{
return(Enumerable.Empty <DE.Recepcion>());
}
}
catch (Exception ex) when(ex.Message.Contains(FarmaciaContext.MessageUnderlyngProviderFailed))
{
return(GetAllByYear(year));
}
}