public ReportData PostReportData([FromBody] FilterSettings filterSettings) { Enums.MeasureType measure = filterSettings.Measure; Enums.ReportPeriod period = filterSettings.Period; Enums.FilterType filter = filterSettings.Filter; string filterParam = filterSettings.FilterParam; DateTime startDate; DateTime endDate; Helpers.GetStartEndDates(period, out startDate, out endDate); using (AutosalesContext context = new AutosalesContext()) { var result = new ReportData( GetSalesPerformance(context, measure, filter, filterParam, startDate, endDate), GetSalesPerformance(context, measure, filter, filterParam, startDate.AddYears(-1), endDate.AddYears(-1)), GetProductsPerformance(context, measure, filter, filterParam, startDate, endDate), GetEmployeesPerformance(context, measure, filter, filterParam, startDate, endDate), GetDealershipsPerformance(context, measure, filter, filterParam, startDate, endDate), GetRegionsPerformance(context, measure, filter, filterParam, startDate, endDate) ); result.Period = period; return(result); } }
private List <RegionPerformance> GetRegionsPerformance(AutosalesContext context, Enums.MeasureType measureType, Enums.FilterType filterType, string filterParam, DateTime startDate, DateTime endDate) { DateTime prevYearStart = new DateTime(startDate.Year - 1, 1, 1); DateTime prevYearEnd = new DateTime(startDate.Year - 1, 12, 31); var targetMultiplier = ((endDate - startDate).Days) / 365d; IQueryable <DealershipProductTarget> targets = context.DealershipProductTargets; var sales = context.SalesTransactions .Where(s => s.PurchaseDate >= startDate && s.PurchaseDate < endDate); var prevYearSales = context.SalesTransactions .Where(s => s.PurchaseDate >= prevYearStart && s.PurchaseDate < prevYearEnd); List <RegionPerformance> result = new List <RegionPerformance>(); var prevYearRegionValues = prevYearSales.Join(context.Dealerships, s => s.DealershipID, d => d.ID, (s, d) => new { Region = d.Region, PrevYearProductTotalCost = s.ProductTotalCost, PrevYearProductQuantity = s.ProductQuantity }).GroupBy(s => s.Region, s => s, (key, s) => new { Region = key, PrevYearSalesAmount = s.Sum(g => g.PrevYearProductTotalCost), PrevYearSalesQuantity = s.Sum(g => g.PrevYearProductQuantity) }); var salesAndDealers = sales.Join(context.Dealerships, s => s.DealershipID, d => d.ID, (s, d) => new { Region = d.Region, PurchaseDate = s.PurchaseDate, ProductTotalCost = s.ProductTotalCost, ProductQuantity = s.ProductQuantity }); var targetsAndDealers = targets.Join(context.Dealerships, t => t.DealershipID, d => d.ID, (t, d) => new { Region = d.Region, YearlyRevenueTarget = t.YearlyRevenueTarget, YearlyVolumeTarget = t.YearlyVolumeTarget }); var regionsValues = salesAndDealers.GroupBy(s => s.Region, s => s, (key, s) => new { Region = key, TotalCost = s.Sum(g => g.ProductTotalCost), TotalQuantity = s.Sum(g => g.ProductQuantity) }); var regionsTargets = targetsAndDealers.GroupBy(s => s.Region, t => t, (key, t) => new { Region = key, YearlyRevenueTarget = t.Sum(g => g.YearlyRevenueTarget), YearlyVolumeTarget = t.Sum(g => g.YearlyVolumeTarget) }); var regionsValuesAndTargets = regionsValues.Join(regionsTargets, s => s.Region, t => t.Region, (s, t) => new { Region = s.Region, TotalCost = s.TotalCost, TotalQuantity = s.TotalQuantity, YearlyRevenueTarget = t.YearlyRevenueTarget, YearlyVolumeTarget = t.YearlyVolumeTarget }); var dealersCountPerRegion = context.Dealerships.GroupBy(d => d.Region, d => new { d.ID, d.State }, (key, d) => new { Region = key, DealersCount = d.Count() }); var regionsData = regionsValuesAndTargets.Join( prevYearRegionValues, vt => vt.Region, py => py.Region, (vt, py) => new { Region = vt.Region, TotalCost = vt.TotalCost, TotalQuantity = vt.TotalQuantity, YearlyRevenueTarget = vt.YearlyRevenueTarget, YearlyVolumeTarget = vt.YearlyVolumeTarget, PrevYearSalesAmount = py.PrevYearSalesAmount, PrevYearSalesQuantity = py.PrevYearSalesQuantity }).Join(dealersCountPerRegion, r => r.Region, dc => dc.Region, (r, dc) => new { Region = r.Region, TotalCost = r.TotalCost, TotalQuantity = r.TotalQuantity, YearlyRevenueTarget = r.YearlyRevenueTarget, YearlyVolumeTarget = r.YearlyVolumeTarget, PrevYearSalesAmount = r.PrevYearSalesAmount, PrevYearSalesQuantity = r.PrevYearSalesQuantity, DealersCount = dc.DealersCount }); //Handle measure type switch (measureType) { case Enums.MeasureType.Revenue: { result = context.Regions.Join(regionsData, r => r.Name, rd => rd.Region, (r, rd) => new RegionPerformance { Name = r.Name, Description = r.Description, DirectorName = r.DirectorName, DirectorEmail = r.DirectorEmail, DirectorPhone = r.DirectorPhone, DealersCount = rd.DealersCount, Value = rd.TotalCost, Target = Math.Ceiling((double)rd.YearlyRevenueTarget * targetMultiplier), PrevYearSalesAmount = rd.PrevYearSalesAmount, PrevYearSalesQuantity = rd.PrevYearSalesQuantity }).OrderByDescending(x => x.Value).ToList(); break; } case Enums.MeasureType.Volume: { result = context.Regions.Join(regionsData, r => r.Name, rd => rd.Region, (r, rd) => new RegionPerformance { Name = r.Name, Description = r.Description, DirectorName = r.DirectorName, DirectorEmail = r.DirectorEmail, DirectorPhone = r.DirectorPhone, DealersCount = rd.DealersCount, Value = rd.TotalCost, Target = Math.Ceiling((double)rd.YearlyRevenueTarget * targetMultiplier), PrevYearSalesAmount = rd.PrevYearSalesAmount, PrevYearSalesQuantity = rd.PrevYearSalesQuantity }).OrderByDescending(x => x.Value).ToList(); break; } default: throw new ArgumentOutOfRangeException("measureType"); } if (result.Count > 0) { var max = CalculateScaleMax(result.Max(x => Math.Max( x.Value.HasValue ? x.Value.Value : 0, x.Target.HasValue ? x.Target.Value : 0))); var allSales = result.Sum(x => x.Value); foreach (var boPerformance in result) { boPerformance.Percent = (int)((boPerformance.Value / allSales) * 100); boPerformance.Max = max; boPerformance.IsTargetReached = boPerformance.Value - boPerformance.Target > 0 ? true : false; } } return(result); }
private List <DealershipPerformance> GetDealershipsPerformance(AutosalesContext context, Enums.MeasureType measureType, Enums.FilterType filterType, string filterParam, DateTime startDate, DateTime endDate) { DateTime prevYearStart = new DateTime(startDate.Year - 1, 1, 1); DateTime prevYearEnd = new DateTime(startDate.Year - 1, 12, 31); var targetMultiplier = ((endDate - startDate).Days) / 365d; IQueryable <DealershipProductTarget> targets = context.DealershipProductTargets; var sales = context.SalesTransactions .Where(s => s.PurchaseDate >= startDate && s.PurchaseDate < endDate); var prevYearSales = context.SalesTransactions .Where(s => s.PurchaseDate >= prevYearStart && s.PurchaseDate < prevYearEnd); List <DealershipPerformance> result = new List <DealershipPerformance>(); //Handle filter type switch (filterType) { case Enums.FilterType.All: { break; } case Enums.FilterType.ByRegion: { var byRegion = context.Dealerships.Where(d => d.Region == filterParam); sales = sales.Join(byRegion, s => s.DealershipID, d => d.ID, (s, d) => s); targets = targets.Join(byRegion, t => t.DealershipID, d => d.ID, (t, d) => t); break; } case Enums.FilterType.ByState: { var byState = context.Dealerships.Where(d => d.State == filterParam); sales = sales.Join(byState, s => s.DealershipID, d => d.ID, (s, d) => s); targets = targets.Join(byState, t => t.DealershipID, d => d.ID, (t, d) => t); break; } case Enums.FilterType.ByDealership: { var byDealership = context.Dealerships.Where(d => d.ID == filterParam); sales = sales.Join(byDealership, s => s.DealershipID, d => d.ID, (s, d) => s); targets = targets.Join(byDealership, t => t.DealershipID, d => d.ID, (t, d) => t); break; } default: throw new ArgumentOutOfRangeException("filterType"); } var dealershipsValues = sales.GroupBy(s => s.DealershipID, s => s, (key, s) => new { DealershipID = key, SalesPeopleCount = s.Select(g => g.EmployeeID).Distinct().Count(), TotalCost = s.Sum(g => g.ProductTotalCost), TotalQuantity = s.Sum(g => g.ProductQuantity) }); var dealershipsTargets = targets.GroupBy(t => t.DealershipID, t => t, (key, t) => new { DealershipID = key, YearlyRevenueTarget = t.Sum(g => g.YearlyRevenueTarget), YearlyVolumeTarget = t.Sum(g => g.YearlyVolumeTarget) }); var dealershipsValuesAndTargets = dealershipsValues.Join(dealershipsTargets, s => s.DealershipID, t => t.DealershipID, (s, t) => new { DealershipID = s.DealershipID, SalesPeopleCount = s.SalesPeopleCount, TotalCost = s.TotalCost, TotalQuantity = s.TotalQuantity, YearlyRevenueTarget = t.YearlyRevenueTarget, YearlyVolumeTarget = t.YearlyVolumeTarget }); var prevYearDealershipsValues = prevYearSales.GroupBy(s => s.DealershipID, s => s, (key, s) => new { DealershipID = key, PrevYearSalesAmount = s.Sum(x => x.ProductUnitCost), PrevYearSalesQuantity = s.Sum(x => x.ProductQuantity) }); var dealershipsData = dealershipsValuesAndTargets.Join(prevYearDealershipsValues, d => d.DealershipID, dpy => dpy.DealershipID, (d, dpy) => new { DealershipID = d.DealershipID, SalesPeopleCount = d.SalesPeopleCount, TotalCost = d.TotalCost, TotalQuantity = d.TotalQuantity, YearlyRevenueTarget = d.YearlyRevenueTarget, YearlyVolumeTarget = d.YearlyVolumeTarget, PrevYearSalesAmount = dpy.PrevYearSalesAmount, PrevYearSalesQuantity = dpy.PrevYearSalesQuantity }); //Handle measure type switch (measureType) { case Enums.MeasureType.Revenue: { result = dealershipsData.Join(context.Dealerships, dd => dd.DealershipID, d => d.ID, (dd, d) => new DealershipPerformance { Id = d.ID, Address = d.Address, City = d.City, Country = d.Country, Latitude = d.Latitude, Longitude = d.Longitude, Name = d.Name, Region = d.Region, State = d.State, PostalCode = d.PostalCode, EstablishDate = d.EstablishDate, Image = d.Image, GeneralManager = d.GeneralManager, Email = d.Email, Phone = d.Phone, Awards = d.DealershipAwards.Select(x => x.Award), Services = d.DealershipServices.Select(x => x.Service), SalesPeopleCount = dd.SalesPeopleCount, Value = dd.TotalCost, Target = Math.Ceiling((double)dd.YearlyRevenueTarget * targetMultiplier), PrevYearSalesAmount = dd.PrevYearSalesAmount, PrevYearSalesQuantity = dd.PrevYearSalesQuantity }).OrderByDescending(x => x.Value).ToList(); break; } case Enums.MeasureType.Volume: { result = dealershipsData.Join(context.Dealerships, dd => dd.DealershipID, d => d.ID, (dd, d) => new DealershipPerformance { Id = d.ID, Address = d.Address, City = d.City, Latitude = d.Latitude, Longitude = d.Longitude, Name = d.Name, Region = d.Region, State = d.State, PostalCode = d.PostalCode, EstablishDate = d.EstablishDate, Image = d.Image, GeneralManager = d.GeneralManager, Email = d.Email, Phone = d.Phone, Awards = d.DealershipAwards.Select(x => x.Award), Services = d.DealershipServices.Select(x => x.Service), SalesPeopleCount = dd.SalesPeopleCount, Value = dd.TotalQuantity, Target = Math.Ceiling((double)dd.YearlyVolumeTarget * targetMultiplier), PrevYearSalesAmount = dd.PrevYearSalesAmount, PrevYearSalesQuantity = dd.PrevYearSalesQuantity }).OrderByDescending(x => x.Value).ToList(); break; } default: throw new ArgumentOutOfRangeException("measureType"); } if (result.Count > 0) { var max = CalculateScaleMax(result.Max(x => Math.Max( x.Value.HasValue ? x.Value.Value : 0, x.Target.HasValue ? x.Target.Value : 0))); var allSales = result.Sum(x => x.Value); int i = 1; foreach (var boPerformance in result) { boPerformance.Percent = (int)((boPerformance.Value / allSales) * 100); boPerformance.Max = max; boPerformance.IsTargetReached = boPerformance.Value - boPerformance.Target > 0 ? true : false; boPerformance.PrevYearRank = i + "/" + result.Count; i++; } } return(result); }
private List <PlotPoint> GetSalesPerformance(AutosalesContext context, Enums.MeasureType measureType, Enums.FilterType filterType, string filterParam, DateTime startDate, DateTime endDate) { var sales = context.SalesTransactions .Where(s => s.PurchaseDate >= startDate && s.PurchaseDate < endDate); List <PlotPoint> result = new List <PlotPoint>(); //Handle filter type switch (filterType) { case Enums.FilterType.All: { break; } case Enums.FilterType.ByRegion: { sales = sales.Join(context.Dealerships.Where(d => d.Region == filterParam), s => s.DealershipID, d => d.ID, (s, d) => s); break; } case Enums.FilterType.ByState: { sales = sales.Join(context.Dealerships.Where(d => d.State == filterParam), s => s.DealershipID, d => d.ID, (s, d) => s); break; } case Enums.FilterType.ByDealership: { sales = sales.Join(context.Dealerships.Where(d => d.ID == filterParam), s => s.DealershipID, d => d.ID, (s, d) => s); break; } default: throw new ArgumentOutOfRangeException("filterType"); } var monthlySales = sales.GroupBy(s => new { Year = s.PurchaseDate.Value.Year, Month = s.PurchaseDate.Value.Month }, s => s, (key, s) => new { TotalCost = s.Sum(t => t.ProductTotalCost), TotalQuantity = s.Sum(t => t.ProductQuantity), Year = key.Year, Month = key.Month }); //Handle measure type switch (measureType) { case Enums.MeasureType.Revenue: { result = monthlySales.ToList() .Select(s => new PlotPoint { Value = s.TotalCost, Date = "/Date(" + new DateTime(s.Year, s.Month, DateTime.DaysInMonth(s.Year, s.Month)) .Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)) .TotalMilliseconds.ToString() + ")/" }).OrderBy(s => s.Date).ToList(); break; } case Enums.MeasureType.Volume: { result = monthlySales.ToList() .Select(s => new PlotPoint { Value = s.TotalQuantity, Date = "/Date(" + new DateTime(s.Year, s.Month, DateTime.DaysInMonth(s.Year, s.Month)) .Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)) .TotalMilliseconds.ToString() + ")/" }).OrderBy(s => s.Date).ToList(); break; } default: throw new ArgumentOutOfRangeException("measureType"); } return(result); }
private List <EmployeePerformance> GetEmployeesPerformance(AutosalesContext context, Enums.MeasureType measureType, Enums.FilterType filterType, string filterParam, DateTime startDate, DateTime endDate) { DateTime prevYearStart = new DateTime(startDate.Year - 1, 1, 1); DateTime prevYearEnd = new DateTime(startDate.Year - 1, 12, 31); var targetMultiplier = ((endDate - startDate).Days) / 365d; IQueryable <Employee> employees = context.Employees; var sales = context.SalesTransactions .Where(s => s.PurchaseDate >= startDate && s.PurchaseDate < endDate); var prevYearSales = context.SalesTransactions .Where(s => s.PurchaseDate >= prevYearStart && s.PurchaseDate < prevYearEnd); List <EmployeePerformance> result = new List <EmployeePerformance>(); //Handle filter type switch (filterType) { case Enums.FilterType.All: { break; } case Enums.FilterType.ByRegion: { var byRegion = context.Dealerships.Where(d => d.Region == filterParam); sales = sales.Join(byRegion, s => s.DealershipID, d => d.ID, (s, d) => s); break; } case Enums.FilterType.ByState: { var byState = context.Dealerships.Where(d => d.State == filterParam); sales = sales.Join(byState, s => s.DealershipID, d => d.ID, (s, d) => s); break; } case Enums.FilterType.ByDealership: { var byDealership = context.Dealerships.Where(d => d.ID == filterParam); sales = sales.Join(byDealership, s => s.DealershipID, d => d.ID, (s, d) => s); break; } default: throw new ArgumentOutOfRangeException("filterType"); } var employeesValues = sales.GroupBy(s => s.EmployeeID, s => s, (key, s) => new { EmployeeID = key, TotalCost = s.Sum(g => g.ProductTotalCost), TotalQuantity = s.Sum(g => g.ProductQuantity) }); var prevYearEmployeeValues = prevYearSales.GroupBy(s => s.EmployeeID, s => s, (key, s) => new { EmployeeID = key, PrevYearSalesAmount = s.Sum(x => x.ProductUnitCost), PrevYearSalesQuantity = s.Sum(x => x.ProductQuantity) }); var prevYearMonthlySales = prevYearSales.GroupBy(s => new { s.EmployeeID, s.PurchaseDate.Value.Year, s.PurchaseDate.Value.Month }, s => s, (key, s) => new { EmployeeID = key.EmployeeID, Year = key.Year, Month = key.Month, MonthlySalesAmount = s.Sum(x => x.ProductUnitCost) }).GroupBy(s => s.EmployeeID, s => s, (key, s) => new { EmployeeID = key, MonthlySales = s.Select(x => new { Year = x.Year, Month = x.Month, Value = x.MonthlySalesAmount }) }); var employeeAwards = context.EmployeeAwards.GroupBy( e => e.EmployeeID, e => e, (key, e) => new { EmployeeID = key, Awards = e.Select(g => g.Award) }); var employeesData = employeesValues.Join(prevYearEmployeeValues, e => e.EmployeeID, epy => epy.EmployeeID, (e, epy) => new { EmployeeID = e.EmployeeID, TotalCost = e.TotalCost, TotalQuantity = e.TotalQuantity, PrevYearSalesAmount = epy.PrevYearSalesAmount, PrevYearSalesQuantity = epy.PrevYearSalesQuantity }).Join(employeeAwards, e => e.EmployeeID, ea => ea.EmployeeID, (e, ea) => new { EmployeeID = e.EmployeeID, TotalCost = e.TotalCost, TotalQuantity = e.TotalQuantity, PrevYearSalesAmount = e.PrevYearSalesAmount, PrevYearSalesQuantity = e.PrevYearSalesQuantity, Awards = ea.Awards }).Join(prevYearMonthlySales, ed => ed.EmployeeID, ms => ms.EmployeeID, (ed, ms) => new { EmployeeID = ed.EmployeeID, TotalCost = ed.TotalCost, TotalQuantity = ed.TotalQuantity, PrevYearSalesAmount = ed.PrevYearSalesAmount, PrevYearSalesQuantity = ed.PrevYearSalesQuantity, Awards = ed.Awards, MonthlySales = ms.MonthlySales }); switch (measureType) { case Enums.MeasureType.Revenue: result = employees.ToList().Join(employeesData, e => e.ID, ed => ed.EmployeeID, (e, ed) => new EmployeePerformance { Name = e.FirstName + " " + e.LastName, Gender = e.Gender, HireDate = e.HireDate.Value.ToShortDateString(), Address = e.Address, City = e.City, State = e.State, PostalCode = e.PostalCode, Image = e.Image, Target = Math.Ceiling((double)e.YearlyRevenueTarget * targetMultiplier), Awards = ed.Awards.ToList(), Value = ed.TotalCost, PrevMonthlySales = ed.MonthlySales.Select(y => new PlotPoint { Date = "/Date(" + new DateTime(y.Year, y.Month, DateTime.DaysInMonth(y.Year, y.Month)) .Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)) .TotalMilliseconds.ToString() + ")/", Value = y.Value }).ToList(), PrevYearSalesAmount = ed.PrevYearSalesAmount, PrevYearSalesQuantity = ed.PrevYearSalesQuantity }).OrderByDescending(e => e.Value).ToList(); break; case Enums.MeasureType.Volume: result = employees.ToList().Join(employeesData, e => e.ID, ed => ed.EmployeeID, (e, ed) => new EmployeePerformance { Name = e.FirstName + " " + e.LastName, Gender = e.Gender, HireDate = e.HireDate.Value.ToShortDateString(), Address = e.Address, City = e.City, State = e.State, PostalCode = e.PostalCode, Image = e.Image, Target = Math.Ceiling((double)e.YearlyVolumeTarget * targetMultiplier), Awards = ed.Awards.ToList(), Value = ed.TotalQuantity, PrevMonthlySales = ed.MonthlySales.Select(y => new PlotPoint { Date = "/Date(" + new DateTime(y.Year, y.Month, DateTime.DaysInMonth(y.Year, y.Month)) .Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)) .TotalMilliseconds.ToString() + ")/", Value = y.Value }).ToList(), PrevYearSalesAmount = ed.PrevYearSalesAmount, PrevYearSalesQuantity = ed.PrevYearSalesQuantity }).OrderByDescending(e => e.Value).ToList(); break; default: throw new ArgumentOutOfRangeException("measureType"); } if (result.Count > 0) { var max = CalculateScaleMax(result.Max(x => Math.Max( x.Value.HasValue ? x.Value.Value : 0, x.Target.HasValue ? x.Target.Value : 0))); var allSales = result.Sum(x => x.Value); int i = 1; foreach (var employee in result) { //The number produced here can be 0.xxx that is casted to 0 employee.Percent = (int)((employee.Value / allSales) * 100); employee.Max = max; employee.IsTargetReached = employee.Value - employee.Target > 0 ? true : false; employee.PrevYearRank = i + "/" + result.Count; employee.Phone = "1234567890"; employee.Email = String.Format("{0}@autodealers.com", employee.Name.ToLower().Replace(' ', '.')); i++; } } return(result); }
private List <ProductPerformance> GetProductsPerformance(AutosalesContext context, Enums.MeasureType measureType, Enums.FilterType filterType, string filterParam, DateTime startDate, DateTime endDate) { DateTime prevYearStart = new DateTime(startDate.Year - 1, 1, 1); DateTime prevYearEnd = new DateTime(startDate.Year - 1, 12, 31); var targetMultiplier = ((endDate - startDate).Days) / 365d; var products = context.Products; IQueryable <DealershipProductTarget> targets = context.DealershipProductTargets; var sales = context.SalesTransactions .Where(s => s.PurchaseDate >= startDate && s.PurchaseDate < endDate); var prevYearSales = context.SalesTransactions .Where(s => s.PurchaseDate >= prevYearStart && s.PurchaseDate < prevYearEnd); List <ProductPerformance> result = new List <ProductPerformance>(); //Handle filter type switch (filterType) { case Enums.FilterType.All: { break; } case Enums.FilterType.ByRegion: { var byRegion = context.Dealerships.Where(d => d.Region == filterParam); sales = sales.Join(byRegion, s => s.DealershipID, d => d.ID, (s, d) => s); targets = targets.Join(byRegion, t => t.DealershipID, d => d.ID, (t, d) => t); break; } case Enums.FilterType.ByState: { var byState = context.Dealerships.Where(d => d.State == filterParam); sales = sales.Join(byState, s => s.DealershipID, d => d.ID, (s, d) => s); targets = targets.Join(byState, t => t.DealershipID, d => d.ID, (t, d) => t); break; } case Enums.FilterType.ByDealership: { var byDealership = context.Dealerships.Where(d => d.ID == filterParam); sales = sales.Join(byDealership, s => s.DealershipID, d => d.ID, (s, d) => s); targets = targets.Join(byDealership, t => t.DealershipID, d => d.ID, (t, d) => t); break; } default: throw new ArgumentOutOfRangeException("filterType"); } var productsValues = sales.GroupBy(s => s.ProductSKU, s => s, (key, s) => new { ProductSku = key, TotalCost = s.Sum(g => g.ProductTotalCost), TotalQuantity = s.Sum(g => g.ProductQuantity) }); var productsTargets = targets.GroupBy(t => t.ProductSKU, t => t, (key, t) => new { ProductSku = key, YearlyRevenueTarget = t.Sum(g => g.YearlyRevenueTarget), YearlyVolumeTarget = t.Sum(g => g.YearlyVolumeTarget) }); var productsValuesAndTargets = productsValues.Join(productsTargets, s => s.ProductSku, t => t.ProductSku, (s, t) => new { ProductSku = s.ProductSku, TotalCost = s.TotalCost, TotalQuantity = s.TotalQuantity, YearlyRevenueTarget = t.YearlyRevenueTarget, YearlyVolumeTarget = t.YearlyVolumeTarget }); var prevYearProductValues = prevYearSales.GroupBy(s => s.ProductSKU, s => s, (key, s) => new { ProductSku = key, PrevYearSalesAmount = s.Sum(x => x.ProductUnitCost), PrevYearSalesQuantity = s.Sum(x => x.ProductQuantity) }); var productsData = productsValuesAndTargets.Join(prevYearProductValues, p => p.ProductSku, py => py.ProductSku, (p, py) => new { ProductSku = p.ProductSku, TotalCost = p.TotalCost, TotalQuantity = p.TotalQuantity, YearlyRevenueTarget = p.YearlyRevenueTarget, YearlyVolumeTarget = p.YearlyVolumeTarget, PrevYearSalesAmount = py.PrevYearSalesAmount, PrevYearSalesQuantity = py.PrevYearSalesQuantity }); //Handle measure type switch (measureType) { case Enums.MeasureType.Revenue: { result = productsData.Join(products, pd => pd.ProductSku, p => p.SKU, (pd, p) => new ProductPerformance { ProductSku = p.SKU, Name = p.Name, Description = p.Description, Category = p.Category, Hp = p.HP, DoorsCount = p.Doors, Model = p.Model, ConsumerRating = p.ConsumerRating, Image = p.Image, MilesPerGallon = p.MilesPerGallon, SafetyRanking = p.SafetyRanking, UseCategory = p.UseCategory, Awards = p.ProductAwards.Select(x => x.Award), Value = pd.TotalCost, Target = Math.Ceiling((double)pd.YearlyRevenueTarget * targetMultiplier), PrevYearSalesAmount = pd.PrevYearSalesAmount, PrevYearSalesQuantity = pd.PrevYearSalesQuantity }).OrderByDescending(x => x.Value).ToList(); break; } case Enums.MeasureType.Volume: { result = productsData.Join(products, pd => pd.ProductSku, p => p.SKU, (pd, p) => new ProductPerformance { ProductSku = p.SKU, Name = p.Name, Description = p.Description, Category = p.Category, Hp = p.HP, DoorsCount = p.Doors, Model = p.Model, ConsumerRating = p.ConsumerRating, Image = p.Image, MilesPerGallon = p.MilesPerGallon, SafetyRanking = p.SafetyRanking, UseCategory = p.UseCategory, Awards = p.ProductAwards.Select(x => x.Award), Value = pd.TotalQuantity, Target = Math.Ceiling((double)pd.YearlyVolumeTarget * targetMultiplier), PrevYearSalesAmount = pd.PrevYearSalesAmount, PrevYearSalesQuantity = pd.PrevYearSalesQuantity }).OrderByDescending(x => x.Value).ToList(); break; } default: throw new ArgumentOutOfRangeException("measureType"); } if (result.Count > 0) { var max = CalculateScaleMax(result.Max(x => Math.Max( x.Value.HasValue ? x.Value.Value : 0, x.Value.HasValue ? x.Value.Value : 0))); var allSales = result.Sum(x => x.Value); foreach (var boPerformance in result) { boPerformance.Percent = (int)((boPerformance.Value / allSales) * 100); boPerformance.Max = max; boPerformance.IsTargetReached = boPerformance.Value - boPerformance.Target > 0 ? true : false; } } return(result); }