public JsonResult OntimeDeliveredTableMonthly(string DepartmentId, string SectionId, string YearId, string MonthId, string MatNameId)
{
// add IEnumerable<AcceptOntimeMonthlyViewModels>
List<DeliveredOntimeMonthlyViewModels> viewModel = new List<DeliveredOntimeMonthlyViewModels>();
// filter by department
var q = objBs.ontimeDeliveryMonthBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName)
&& !String.IsNullOrEmpty(x.SectionName)
&& !String.IsNullOrEmpty(x.MatName)
&& x.Year == YearId);
//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 month
if (!String.IsNullOrEmpty(MonthId))
q = q.Where(x => x.Month == MonthId);
var results = (from c in q
group c by new { c.Year, c.Month, c.DepartmentName, c.SectionName, c.MatName } into g
select new
{
Year = g.Key.Year,
Month = g.Key.Month,
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.Month).ThenBy(x => x.DepartmentName);
foreach (var item in results)
{
DeliveredOntimeMonthlyViewModels model = new DeliveredOntimeMonthlyViewModels();
model.Month = DateTimeFormatInfo.CurrentInfo.GetAbbreviatedMonthName(Convert.ToInt32(item.Month)).ToString();
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 OntimeDeliveredTableMonthly(string DepartmentId, string SectionId, string YearId, string MonthId, string MatNameId)
{
// add IEnumerable<AcceptOntimeMonthlyViewModels>
List <DeliveredOntimeMonthlyViewModels> viewModel = new List <DeliveredOntimeMonthlyViewModels>();
// filter by department
var q = objBs.ontimeDeliveryMonthBs.GetAll().Where(x => !String.IsNullOrEmpty(x.DepartmentName) &&
!String.IsNullOrEmpty(x.SectionName) &&
!String.IsNullOrEmpty(x.MatName) &&
x.Year == YearId);
//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 month
if (!String.IsNullOrEmpty(MonthId))
{
q = q.Where(x => x.Month == MonthId);
}
var results = (from c in q
group c by new { c.Year, c.Month, c.DepartmentName, c.SectionName, c.MatName } into g
select new
{
Year = g.Key.Year,
Month = g.Key.Month,
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.Month).ThenBy(x => x.DepartmentName);
foreach (var item in results)
{
DeliveredOntimeMonthlyViewModels model = new DeliveredOntimeMonthlyViewModels();
model.Month = DateTimeFormatInfo.CurrentInfo.GetAbbreviatedMonthName(Convert.ToInt32(item.Month)).ToString();
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));
}
