public List<Ranking> GetRanking(RankingFilter filter) { using (BubisDEntities db = new BubisDEntities()) { var shipments = db.Orders_Shipments.Include("Orders").Include("ArticlesByCompanies").Where(l => l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active)); if (filter.ORDR_DateFrom.HasValue) shipments = shipments.Where(l => l.Orders.ORDR_Date >= filter.ORDR_DateFrom.Value); if (filter.ORDR_DateTo.HasValue) shipments = shipments.Where(l => l.Orders.ORDR_Date <= filter.ORDR_DateTo.Value); if (filter.ART_Id.HasValue) shipments = shipments.Where(l => l.ArticlesByCompanies.ART_Id == filter.ART_Id.Value); if (filter.COMP_Id.HasValue) shipments = shipments.Where(l => l.ArticlesByCompanies.COMP_Id == filter.COMP_Id.Value); if (filter.CLT_Id.HasValue) shipments = shipments.Where(l => l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active && v.CLT_Id == filter.CLT_Id.Value)); var collRanking = shipments.Select(l => new Ranking() { ORDR_Code = l.Orders.ORDR_Code, ORDR_Date = l.Orders.ORDR_Date, ART_Code = l.ArticlesByCompanies.Articles.ART_Code, ART_Name = l.ArticlesByCompanies.Articles.Articles_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.ART_Active).ART_Name, ORDR_IsShipped = l.Orders.Orders_Versions.Any(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active && v.ORDR_IsShipped.Value), ORDR_SHIP_QuantityShipped = l.ORDR_SHIP_QuantityShipped, ORDR_SHIP_QuantityOrdered = l.ORDR_SHIP_QuantityOrdered, ART_UM_Desc = l.ArticlesByCompanies.Articles.Articles_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.ART_Active).Articles_UnitMeasure.ART_UM_Desc, ORDR_SHIP_Price = l.ORDR_SHIP_Price, ORDR_SHIP_AMLA = l.ORDR_SHIP_AMLA, CLT_Name = l.Orders.Orders_Versions.FirstOrDefault(v => v.VERS_IsCurrent && v.VERS_IsVerify && v.VERS_Active).Clients.CLT_Name, COMP_Name = l.ArticlesByCompanies.Companies.COMP_Name, ORDR_SHIP_OPE = l.ORDR_SHIP_OPE } ).ToList(); foreach (var l in collRanking) l.Dollars = (l.Quantity * l.ORDR_SHIP_Price) + (l.Quantity * (l.ORDR_SHIP_OPE.HasValue ? l.ORDR_SHIP_OPE.Value : 0)); return collRanking.OrderByDescending(l => l.Dollars).ToList(); } }
public List<RankingGroupBy> GetRankingGroupBy(RankingFilter rankingFilter) { var rankings = this.GetRanking(rankingFilter); IEnumerable<IGrouping<string, Ranking>> groupBy = null; switch (rankingFilter.GroupBy) { case "Article": groupBy = rankings.GroupBy(l => l.ART_Code); break; case "Company": groupBy = rankings.GroupBy(l => l.COMP_Name); break; case "Client": groupBy = rankings.GroupBy(l => l.CLT_Name); break; case "ALL": default: break; } var select = groupBy.Select(l => new RankingGroupBy(l.Key) { ART_UM_Desc = l.FirstOrDefault().ART_UM_Desc, AMLA = l.Sum(v => v.AMLA), Dollars = l.Sum(v => v.Dollars), Quantity = l.Sum(v => v.Quantity) }).OrderByDescending(l=>l.Dollars).ToList(); int index = 1; foreach (var l in select) { l.Position = index; index++; } return select.OrderByDescending(l => l.Dollars).ToList(); }