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