public JsonResult OntimeAcceptTableDaily(string DepartmentId, string SectionId, DateTime? FromDateSearch, DateTime? ToDateSearch, string MatNameId) { // add IEnumerable<OntimeAccept> List<AcceptOntimeViewModels> viewModel = new List<AcceptOntimeViewModels>(); // filter by department var q = objBs.ontimeAcceptBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName)); //var q = objBs.ontimeAcceptBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName) // && !String.IsNullOrEmpty(x.SectionName) // && !String.IsNullOrEmpty(x.MatName)); //&& x.DepartmentId == DepartmentId); //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); } //filter from date, to date if (FromDateSearch != null && ToDateSearch != null) { if (FromDateSearch == ToDateSearch) { q = q.Where(x => x.ActualGiDate == FromDateSearch); } else { q = q.Where(x => x.ActualGiDate >= FromDateSearch && x.ActualGiDate <= ToDateSearch); } } if (FromDateSearch != null && ToDateSearch == null) { q = q.Where(x => x.ActualGiDate == FromDateSearch); } if (FromDateSearch == null && ToDateSearch != null) { q = q.Where(x => x.ActualGiDate == ToDateSearch); } var results = (from c in q group c by new { c.ActualGiDate, c.DepartmentName, c.SectionName, c.MatName } into g select new { ActualGiDate = g.Key.ActualGiDate, DepartmentName = g.Key.DepartmentName, SectionName = g.Key.SectionName, MatName = g.Key.MatName, Plan = 98.0, SumOfAccept = ((int)g.Sum(x => x.SumOfAccept)), OnTime = ((int)g.Sum(x => x.OnTime)), Delay = ((int)g.Sum(x => x.Delay)), AdjustAccept = ((int)g.Sum(x => x.AdjustAccept)), }).OrderBy(x => x.ActualGiDate).ToList(); foreach (var item in results) { AcceptOntimeViewModels model = new AcceptOntimeViewModels(); string dd = item.ActualGiDate.Value.Day.ToString(); string mm = item.ActualGiDate.Value.Month.ToString(); string yyyy = item.ActualGiDate.Value.Year.ToString(); model.ActualGiDate = item.ActualGiDate.Value.ToString("dd/MM/yyyy"); model.DepartmentName = item.DepartmentName; model.SectionName = item.SectionName; model.MatName = item.MatName; model.SumOfAccept = item.SumOfAccept; model.OnTime = item.OnTime; model.Delay = item.Delay; model.AdjustAccept = item.AdjustAccept; model.Plan = 98.0; model.Percent = Math.Round(((double)item.OnTime / (double)item.SumOfAccept) * 100, 2); model.PercentAdjust = Math.Round((((double)item.OnTime + (double)item.AdjustAccept) / (double)item.SumOfAccept) * 100, 2); viewModel.Add(model); } return Json(viewModel, JsonRequestBehavior.AllowGet); }
public JsonResult OntimeAcceptTableDaily(string DepartmentId, string SectionId, DateTime?FromDateSearch, DateTime?ToDateSearch, string MatNameId) { // add IEnumerable<OntimeAccept> List <AcceptOntimeViewModels> viewModel = new List <AcceptOntimeViewModels>(); // filter by department var q = objBs.ontimeAcceptBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName)); //var q = objBs.ontimeAcceptBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName) // && !String.IsNullOrEmpty(x.SectionName) // && !String.IsNullOrEmpty(x.MatName)); //&& x.DepartmentId == DepartmentId); //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); } //filter from date, to date if (FromDateSearch != null && ToDateSearch != null) { if (FromDateSearch == ToDateSearch) { q = q.Where(x => x.ActualGiDate == FromDateSearch); } else { q = q.Where(x => x.ActualGiDate >= FromDateSearch && x.ActualGiDate <= ToDateSearch); } } if (FromDateSearch != null && ToDateSearch == null) { q = q.Where(x => x.ActualGiDate == FromDateSearch); } if (FromDateSearch == null && ToDateSearch != null) { q = q.Where(x => x.ActualGiDate == ToDateSearch); } var results = (from c in q group c by new { c.ActualGiDate, c.DepartmentName, c.SectionName, c.MatName } into g select new { ActualGiDate = g.Key.ActualGiDate, DepartmentName = g.Key.DepartmentName, SectionName = g.Key.SectionName, MatName = g.Key.MatName, Plan = 98.0, SumOfAccept = ((int)g.Sum(x => x.SumOfAccept)), OnTime = ((int)g.Sum(x => x.OnTime)), Delay = ((int)g.Sum(x => x.Delay)), AdjustAccept = ((int)g.Sum(x => x.AdjustAccept)), }).OrderBy(x => x.ActualGiDate).ToList(); foreach (var item in results) { AcceptOntimeViewModels model = new AcceptOntimeViewModels(); string dd = item.ActualGiDate.Value.Day.ToString(); string mm = item.ActualGiDate.Value.Month.ToString(); string yyyy = item.ActualGiDate.Value.Year.ToString(); model.ActualGiDate = item.ActualGiDate.Value.ToString("dd/MM/yyyy"); model.DepartmentName = item.DepartmentName; model.SectionName = item.SectionName; model.MatName = item.MatName; model.SumOfAccept = item.SumOfAccept; model.OnTime = item.OnTime; model.Delay = item.Delay; model.AdjustAccept = item.AdjustAccept; model.Plan = 98.0; model.Percent = Math.Round(((double)item.OnTime / (double)item.SumOfAccept) * 100, 2); model.PercentAdjust = Math.Round((((double)item.OnTime + (double)item.AdjustAccept) / (double)item.SumOfAccept) * 100, 2); viewModel.Add(model); } return(Json(viewModel, JsonRequestBehavior.AllowGet)); }