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