public JsonResult OntimeDocReturnedTableYearly(string DepartmentId, string SectionId, string MatNameId) { // add IEnumerable<AcceptOntimeMonthlyViewModels> List <DocReturnedOntimeYearlyViewModels> viewModel = new List <DocReturnedOntimeYearlyViewModels>(); // filter by department var q = objBs.ontimeDocReturnYearBs.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, SumOfDocReturn = g.Sum(x => x.SumOfDocReturn), OnTime = g.Sum(x => x.OnTime), Delay = g.Sum(x => x.Delay), AdjustDocReturn = g.Sum(x => x.AdjustDocReturn) }).OrderBy(x => x.Year).ThenBy(x => x.DepartmentName); foreach (var item in results) { DocReturnedOntimeYearlyViewModels model = new DocReturnedOntimeYearlyViewModels(); model.Year = item.Year; model.DepartmentName = item.DepartmentName; model.SectionName = item.SectionName; model.MatName = item.MatName; model.SumOfDocReturn = item.SumOfDocReturn; model.OnTime = item.OnTime; model.Delay = item.Delay; model.AdjustDocReturn = item.AdjustDocReturn; model.Plan = 98.0; model.Percent = Math.Round(((double)item.OnTime / (double)item.SumOfDocReturn) * 100, 2); model.PercentAdjust = Math.Round((((double)item.OnTime + (double)item.AdjustDocReturn) / (double)item.SumOfDocReturn) * 100, 2); viewModel.Add(model); } return(Json(viewModel, JsonRequestBehavior.AllowGet)); }
public JsonResult OntimeDocReturnedTableYearly(string DepartmentId, string SectionId, string MatNameId) { // add IEnumerable<AcceptOntimeMonthlyViewModels> List<DocReturnedOntimeYearlyViewModels> viewModel = new List<DocReturnedOntimeYearlyViewModels>(); // filter by department var q = objBs.ontimeDocReturnYearBs.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, SumOfDocReturn = g.Sum(x => x.SumOfDocReturn), OnTime = g.Sum(x => x.OnTime), Delay = g.Sum(x => x.Delay), AdjustDocReturn = g.Sum(x => x.AdjustDocReturn) }).OrderBy(x => x.Year).ThenBy(x => x.DepartmentName); foreach (var item in results) { DocReturnedOntimeYearlyViewModels model = new DocReturnedOntimeYearlyViewModels(); model.Year = item.Year; model.DepartmentName = item.DepartmentName; model.SectionName = item.SectionName; model.MatName = item.MatName; model.SumOfDocReturn = item.SumOfDocReturn; model.OnTime = item.OnTime; model.Delay = item.Delay; model.AdjustDocReturn = item.AdjustDocReturn; model.Plan = 98.0; model.Percent = Math.Round(((double)item.OnTime / (double)item.SumOfDocReturn) * 100, 2); model.PercentAdjust = Math.Round((((double)item.OnTime + (double)item.AdjustDocReturn) / (double)item.SumOfDocReturn) * 100, 2); viewModel.Add(model); } return Json(viewModel, JsonRequestBehavior.AllowGet); }