public JsonResult OntimeDeliveredTableYearly(string DepartmentId, string SectionId, string MatNameId) { // add IEnumerable<AcceptOntimeMonthlyViewModels> List <DeliveredOntimeYearlyViewModels> viewModel = new List <DeliveredOntimeYearlyViewModels>(); // filter by department var q = objBs.ontimeDeliveryYearBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName) && !String.IsNullOrEmpty(x.SectionName) && !String.IsNullOrEmpty(x.MatName)); //filter Department if (!String.IsNullOrEmpty(DepartmentId)) { q = q.Where(x => x.DepartmentId == DepartmentId); } //filter Section if (!String.IsNullOrEmpty(SectionId)) { q = q.Where(x => x.SectionId == SectionId); } //filter matname if (!String.IsNullOrEmpty(MatNameId)) { q = q.Where(x => x.MatFriGrp == MatNameId); } var results = (from c in q group c by new { c.Year, c.DepartmentName, c.SectionName, c.MatName } into g select new { Year = g.Key.Year, MatName = g.Key.MatName, DepartmentName = g.Key.DepartmentName, SectionName = g.Key.SectionName, SumOfDelivery = g.Sum(x => x.SumOfDelivery), OnTime = g.Sum(x => x.OnTime), Delay = g.Sum(x => x.Delay), AdjustDelivery = g.Sum(x => x.AdjustDelivery) }).OrderBy(x => x.Year).ThenBy(x => x.DepartmentName); foreach (var item in results) { DeliveredOntimeYearlyViewModels model = new DeliveredOntimeYearlyViewModels(); model.Year = item.Year; model.DepartmentName = item.DepartmentName; model.SectionName = item.SectionName; model.MatName = item.MatName; model.SumOfDelivery = item.SumOfDelivery; model.OnTime = item.OnTime; model.Delay = item.Delay; model.AdjustDelivery = item.AdjustDelivery; model.Plan = 98.0; model.Percent = Math.Round(((double)item.OnTime / (double)item.SumOfDelivery) * 100, 2); model.PercentAdjust = Math.Round((((double)item.OnTime + (double)item.AdjustDelivery) / (double)item.SumOfDelivery) * 100, 2); viewModel.Add(model); } return(Json(viewModel, JsonRequestBehavior.AllowGet)); }
public JsonResult OntimeDeliveredTableYearly(string DepartmentId, string SectionId, string MatNameId) { // add IEnumerable<AcceptOntimeMonthlyViewModels> List<DeliveredOntimeYearlyViewModels> viewModel = new List<DeliveredOntimeYearlyViewModels>(); // filter by department var q = objBs.ontimeDeliveryYearBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName) && !String.IsNullOrEmpty(x.SectionName) && !String.IsNullOrEmpty(x.MatName)); //filter Department if (!String.IsNullOrEmpty(DepartmentId)) q = q.Where(x => x.DepartmentId == DepartmentId); //filter Section if (!String.IsNullOrEmpty(SectionId)) q = q.Where(x => x.SectionId == SectionId); //filter matname if (!String.IsNullOrEmpty(MatNameId)) q = q.Where(x => x.MatFriGrp == MatNameId); var results = (from c in q group c by new { c.Year, c.DepartmentName, c.SectionName, c.MatName } into g select new { Year = g.Key.Year, MatName = g.Key.MatName, DepartmentName = g.Key.DepartmentName, SectionName = g.Key.SectionName, SumOfDelivery = g.Sum(x => x.SumOfDelivery), OnTime = g.Sum(x => x.OnTime), Delay = g.Sum(x => x.Delay), AdjustDelivery = g.Sum(x => x.AdjustDelivery) }).OrderBy(x => x.Year).ThenBy(x => x.DepartmentName); foreach (var item in results) { DeliveredOntimeYearlyViewModels model = new DeliveredOntimeYearlyViewModels(); model.Year = item.Year; model.DepartmentName = item.DepartmentName; model.SectionName = item.SectionName; model.MatName = item.MatName; model.SumOfDelivery = item.SumOfDelivery; model.OnTime = item.OnTime; model.Delay = item.Delay; model.AdjustDelivery = item.AdjustDelivery; model.Plan = 98.0; model.Percent = Math.Round(((double)item.OnTime / (double)item.SumOfDelivery) * 100, 2); model.PercentAdjust = Math.Round((((double)item.OnTime + (double)item.AdjustDelivery) / (double)item.SumOfDelivery) * 100, 2); viewModel.Add(model); } return Json(viewModel, JsonRequestBehavior.AllowGet); }