public JsonResult jsonData(string SegmentId, string CustomerId, string FromDateSearch, string ToDateSearch, string MatNameId) { //add summary data List<ReportSalesViewModels> viewSummaryModel = new List<ReportSalesViewModels>(); if (FromDateSearch != null && ToDateSearch == null) { ToDateSearch = FromDateSearch; } if (FromDateSearch == null && ToDateSearch != null) { FromDateSearch = ToDateSearch; } DateTime DateFrom = Convert.ToDateTime(FromDateSearch, new System.Globalization.CultureInfo("en-US", false).DateTimeFormat); DateTime DateTo = Convert.ToDateTime(ToDateSearch, new System.Globalization.CultureInfo("en-US", false).DateTimeFormat); IEnumerable<dynamic> DN = objBs.dWH_ONTIME_DNBs.GetByDate(DateFrom, DateTo).ToList(); IEnumerable<dynamic> SH = objBs.dWH_ONTIME_SHIPMENTBs.GetByDate(DateFrom, DateTo).ToList(); DN = DN.Where(x => x.DATA_SUBGRP == SegmentId && x.SOLDTO == CustomerId); //filter matname if (!String.IsNullOrEmpty(MatNameId)) DN = DN.Where(x => x.MATFRIGRP == MatNameId); SH = SH.Where(x => x.DATA_SUBGRP == SegmentId && x.SOLDTO == CustomerId); //filter matname if (!String.IsNullOrEmpty(MatNameId)) SH = SH.Where(x => x.MATFRIGRP == MatNameId); IEnumerable<dynamic> q = (from dn in DN join sh in SH on dn.DELVNO equals sh.DELVNO select new { DELVNO = dn.DELVNO, SHPMNTNO = sh.SHPMNTNO, SUB_SEGMENT = sh.DATA_SUBGRP, MATFRIGRP = dn.MATFRIGRP, SOLDTO = dn.SOLDTO, SOLDTO_NAME = dn.SOLDTO_NAME, ACPD_ONTIME = sh.ACPD_ONTIME == null ? 0 : sh.ACPD_ONTIME, ACPD_ADJUST = sh.ACPD_ADJUST == null ? 0 : sh.ACPD_ADJUST, ACPD_COUNT = sh.ACPD_COUNT == null ? 0 : sh.ACPD_COUNT, TNRD_ONTIME = sh.TNRD_ONTIME == null ? 0 : sh.TNRD_ONTIME, TNRD_ADJUST = sh.TNRD_ADJUST == null ? 0 : sh.TNRD_ADJUST, TNRD_COUNT = sh.TNRD_COUNT == null ? 0 : sh.TNRD_COUNT, INB_ONTIME = dn.INB_ONTIME_FLAG == null ? 0 : dn.INB_ONTIME_FLAG, INB_ADJUST = dn.INB_ADJUST == null ? 0 : dn.INB_ADJUST, INB_COUNT = dn.INB_COUNT == null ? 0 : dn.INB_COUNT, OUTB_ONTIME = dn.OUTB_ONTIME_FLAG == null ? 0 : dn.OUTB_ONTIME_FLAG, OUTB_ADJUST = dn.OUTB_ADJUST == null ? 0 : dn.OUTB_ADJUST, OUTB_COUNT = dn.OUTB_COUNT == null ? 0 : dn.OUTB_COUNT, ONTIME_ONTIME = dn.ON_TIME_FLAG == null ? 0 : dn.ON_TIME_FLAG, ONTIME_ADJUST = dn.ON_TIME_ADJUST == null ? 0 : dn.ON_TIME_ADJUST, ONTIME_COUNT = dn.ON_TIME_COUNT == null ? 0 : dn.ON_TIME_COUNT, SCGL_DOCRTN_ONTIME = dn.SCGL_DOCRET_ONTIME_FLAG == null ? 0 : dn.SCGL_DOCRET_ONTIME_FLAG, SCGL_DOCRTN_ADJUST = dn.SCGL_DOCRET_ADJUST == null ? 0 : dn.SCGL_DOCRET_ADJUST, SCGL_DOCRTN_COUNT = dn.SCGL_DOCRET_COUNT == null ? 0 : dn.SCGL_DOCRET_COUNT, ACTGIDATE = dn.ACTGIDATE_D }).ToList(); var results = (from c in q group c by new { c.ACTGIDATE, c.SOLDTO_NAME, c.MATFRIGRP, c.SUB_SEGMENT } into g select new { ActualGiDate = g.Key.ACTGIDATE, Segment = g.Key.SUB_SEGMENT, SoldToName = g.Key.SOLDTO_NAME, MatFreight = g.Key.MATFRIGRP, Plan = 98.0, SumOfAccept = (int)g.Sum(x => x.ACPD_COUNT == null ? 0 : x.ACPD_COUNT), PcAccept_Ontime = (double)g.Sum(x => x.ACPD_COUNT) == 0 ? 0 : ((double)g.Sum(x => x.ACPD_ONTIME) / (double)g.Sum(x => x.ACPD_COUNT)) * 100, PcAccept_Adjust = (double)g.Sum(x => x.ACPD_COUNT) == 0 ? 0 : (((double)g.Sum(x => x.ACPD_ADJUST) + (double)g.Sum(x => x.ACPD_ONTIME)) / (double)g.Sum(x => x.ACPD_COUNT)) * 100, SumOfTender = (int)g.Sum(x => x.TNRD_COUNT == null ? 0 : x.TNRD_COUNT), PcTender_Ontime = (double)g.Sum(x => x.TNRD_COUNT) == 0 ? 0 : ((double)g.Sum(x => x.TNRD_ONTIME) / (double)g.Sum(x => x.TNRD_COUNT)) * 100, PcTender_Adjust = (double)g.Sum(x => x.TNRD_COUNT) == 0 ? 0 : (((double)g.Sum(x => x.TNRD_ADJUST) + (double)g.Sum(x => x.TNRD_ONTIME)) / (double)g.Sum(x => x.TNRD_COUNT)) * 100, SumOfInbound = (int)g.Sum(x => x.INB_COUNT == null ? 0 : x.INB_COUNT), PcInbound_Ontime = (double)g.Sum(x => x.INB_COUNT) == 0 ? 0 : ((double)g.Sum(x => x.INB_ONTIME) / (double)g.Sum(x => x.INB_COUNT)) * 100, PcInbound_Adjust = (double)g.Sum(x => x.INB_COUNT) == 0 ? 0 : (((double)g.Sum(x => x.INB_ADJUST) + (double)g.Sum(x => x.INB_ONTIME)) / (double)g.Sum(x => x.INB_COUNT)) * 100, SumOfOutbound = (int)g.Sum(x => x.OUTB_COUNT == null ? 0 : x.OUTB_COUNT), PcOutbound_Ontime = (double)g.Sum(x => x.OUTB_COUNT) == 0 ? 0 : ((double)g.Sum(x => x.OUTB_ONTIME) / (double)g.Sum(x => x.OUTB_COUNT)) * 100, PcOutbound_Adjust = (double)g.Sum(x => x.OUTB_COUNT) == 0 ? 0 : (((double)g.Sum(x => x.OUTB_ADJUST) + (double)g.Sum(x => x.OUTB_ONTIME)) / (double)g.Sum(x => x.OUTB_COUNT)) * 100, SumOfDocReturn = (int)g.Sum(x => x.SCGL_DOCRTN_COUNT == null ? 0 : x.SCGL_DOCRTN_COUNT), PcDocReturn_Ontime = (double)g.Sum(x => x.SCGL_DOCRTN_COUNT) == 0 ? 0 : ((double)g.Sum(x => x.SCGL_DOCRTN_ONTIME) / (double)g.Sum(x => x.SCGL_DOCRTN_COUNT)) * 100, PcDocReturn_Adjust = (double)g.Sum(x => x.SCGL_DOCRTN_COUNT) == 0 ? 0 : (((double)g.Sum(x => x.SCGL_DOCRTN_ADJUST) + (double)g.Sum(x => x.SCGL_DOCRTN_ONTIME)) / (double)g.Sum(x => x.SCGL_DOCRTN_COUNT)) * 100, SumOfOntime = (int)g.Sum(x => x.ONTIME_COUNT == null ? 0 : x.ONTIME_COUNT), PcOntime_Ontime = (double)g.Sum(x => x.ONTIME_COUNT) == 0 ? 0 : ((double)g.Sum(x => x.ONTIME_ONTIME) / (double)g.Sum(x => x.ONTIME_COUNT)) * 100, PcOntime_Adjust = (double)g.Sum(x => x.ONTIME_COUNT) == 0 ? 0 : (((double)g.Sum(x => x.ONTIME_ADJUST) + (double)g.Sum(x => x.ONTIME_ONTIME)) / (double)g.Sum(x => x.ONTIME_COUNT)) * 100, }).OrderBy(x => x.ActualGiDate).ToList(); foreach (var item in results) { ReportSalesViewModels model = new ReportSalesViewModels(); model.Segment = item.Segment; model.Customer = item.SoldToName; model.MatName = item.MatFreight; model.ActualGiDate = item.ActualGiDate.ToString("dd/MM/yyyy"); model.Plan = item.Plan; model.OnTimeTender = Math.Round(item.PcTender_Ontime, 2); model.AdjustTender = Math.Round(item.PcTender_Adjust, 2); model.SumOfTender = item.SumOfTender; model.OnTimeAccept = Math.Round(item.PcAccept_Ontime, 2); model.AdjustAccept = Math.Round(item.PcAccept_Adjust, 2); model.SumOfAccept = item.SumOfAccept; model.OnTimeInbound = Math.Round(item.PcInbound_Ontime, 2); model.AdjustInbound = Math.Round(item.PcInbound_Adjust, 2); model.SumOfInbound = item.SumOfInbound; model.OnTimeOutbound = Math.Round(item.PcOutbound_Ontime, 2); model.AdjustOutbound = Math.Round(item.PcOutbound_Adjust, 2); model.SumOfOutbound = item.SumOfOutbound; model.OnTimeOntime = Math.Round(item.PcOntime_Ontime, 2); model.AdjustOntime = Math.Round(item.PcOntime_Adjust, 2); model.SumOfOntime = item.SumOfOntime; model.OnTimeDocReturn = Math.Round(item.PcDocReturn_Ontime, 2); model.AdjustDocReturn = Math.Round(item.PcDocReturn_Adjust, 2); model.SumOfDocReturn = item.SumOfDocReturn; viewSummaryModel.Add(model); } return Json(viewSummaryModel, JsonRequestBehavior.AllowGet); }
public JsonResult jsonData(string SegmentId, string CustomerId, string FromDateSearch, string ToDateSearch, string MatNameId) { //add summary data List <ReportSalesViewModels> viewSummaryModel = new List <ReportSalesViewModels>(); if (FromDateSearch != null && ToDateSearch == null) { ToDateSearch = FromDateSearch; } if (FromDateSearch == null && ToDateSearch != null) { FromDateSearch = ToDateSearch; } DateTime DateFrom = Convert.ToDateTime(FromDateSearch, new System.Globalization.CultureInfo("en-US", false).DateTimeFormat); DateTime DateTo = Convert.ToDateTime(ToDateSearch, new System.Globalization.CultureInfo("en-US", false).DateTimeFormat); var q = (from c in objBs.SaleDailyBs.GetByDate(DateFrom, DateTo) select c); if (!String.IsNullOrEmpty(SegmentId)) { q = q.Where(x => x.SegmentId == SegmentId); } if (!String.IsNullOrEmpty(MatNameId)) { q = q.Where(x => x.MatFriGrp == MatNameId); } if (!String.IsNullOrEmpty(CustomerId) && (CustomerId != "0")) { q = q.Where(x => x.CustomerId == CustomerId); } if (CustomerId != "0") { var results = (from c in q group c by new { c.ActualGiDate, c.CustomerName, c.SegmentName } into g select new { ActualGiDate = g.Key.ActualGiDate, CustomerName = g.Key.CustomerName, SegmentName = g.Key.SegmentName, OnTimeTender = g.Sum(x => x.OnTimeTender), AdjustedTender = g.Sum(x => x.AdjustedTender), SumOfTender = g.Sum(x => x.SumOfTender), OnTimeAccept = g.Sum(x => x.OnTimeAccept), AdjustedAccept = g.Sum(x => x.AdjustedAccept), SumOfAccept = g.Sum(x => x.SumOfAccept), OnTimeInbound = g.Sum(x => x.OnTimeInbound), AdjustedInbound = g.Sum(x => x.AdjustedInbound), SumOfInbound = g.Sum(x => x.SumOfInbound), OnTimeOutbound = g.Sum(x => x.OnTimeOutbound), AdjustedOutbound = g.Sum(x => x.AdjustedOutbound), SumOfOutbound = g.Sum(x => x.SumOfOutbound), OnTimeDelivery = g.Sum(x => x.OnTimeDelivery), AdjustedDelivery = g.Sum(x => x.AdjustedDelivery), SumOfDelivery = g.Sum(x => x.SumOfDelivery), OnTimeDocreturn = g.Sum(x => x.OnTimeDocreturn), AdjustedDocreturn = g.Sum(x => x.AdjustedDocreturn), SumOfDocreturn = g.Sum(x => x.SumOfDocreturn) }).OrderBy(x => x.ActualGiDate); foreach (var item in results) { ReportSalesViewModels model = new ReportSalesViewModels(); model.Segment = item.SegmentName; model.Customer = item.CustomerName; //model.MatName = item.MatFreight; model.ActualGiDate = item.ActualGiDate.ToString("dd/MM/yyyy"); model.Plan = 98.0; model.OnTimeTender = item.OnTimeTender; model.AdjustTender = item.AdjustedTender; model.SumOfTender = item.SumOfTender; model.OnTimeAccept = item.OnTimeAccept; model.AdjustAccept = item.AdjustedAccept; model.SumOfAccept = item.SumOfAccept; model.OnTimeInbound = item.OnTimeInbound; model.AdjustInbound = item.AdjustedInbound; model.SumOfInbound = item.SumOfInbound; model.OnTimeOutbound = item.OnTimeOutbound; model.AdjustOutbound = item.AdjustedOutbound; model.SumOfOutbound = item.SumOfOutbound; model.OnTimeOntime = item.OnTimeDelivery; model.AdjustOntime = item.AdjustedDelivery; model.SumOfOntime = item.SumOfDelivery; model.OnTimeDocReturn = item.OnTimeDocreturn; model.AdjustDocReturn = item.AdjustedDocreturn; model.SumOfDocReturn = item.SumOfDocreturn; viewSummaryModel.Add(model); } } else { var results = (from c in q group c by new { c.ActualGiDate, c.SegmentName } into g select new { ActualGiDate = g.Key.ActualGiDate, CustomerName = "SCG Customer", SegmentName = g.Key.SegmentName, OnTimeTender = g.Sum(x => x.OnTimeTender), AdjustedTender = g.Sum(x => x.AdjustedTender), SumOfTender = g.Sum(x => x.SumOfTender), OnTimeAccept = g.Sum(x => x.OnTimeAccept), AdjustedAccept = g.Sum(x => x.AdjustedAccept), SumOfAccept = g.Sum(x => x.SumOfAccept), OnTimeInbound = g.Sum(x => x.OnTimeInbound), AdjustedInbound = g.Sum(x => x.AdjustedInbound), SumOfInbound = g.Sum(x => x.SumOfInbound), OnTimeOutbound = g.Sum(x => x.OnTimeOutbound), AdjustedOutbound = g.Sum(x => x.AdjustedOutbound), SumOfOutbound = g.Sum(x => x.SumOfOutbound), OnTimeDelivery = g.Sum(x => x.OnTimeDelivery), AdjustedDelivery = g.Sum(x => x.AdjustedDelivery), SumOfDelivery = g.Sum(x => x.SumOfDelivery), OnTimeDocreturn = g.Sum(x => x.OnTimeDocreturn), AdjustedDocreturn = g.Sum(x => x.AdjustedDocreturn), SumOfDocreturn = g.Sum(x => x.SumOfDocreturn) }).OrderBy(x => x.ActualGiDate); foreach (var item in results) { ReportSalesViewModels model = new ReportSalesViewModels(); model.Segment = item.SegmentName; model.Customer = item.CustomerName; //model.MatName = item.MatFreight; model.ActualGiDate = item.ActualGiDate.ToString("dd/MM/yyyy"); model.Plan = 98.0; model.OnTimeTender = item.OnTimeTender; model.AdjustTender = item.AdjustedTender; model.SumOfTender = item.SumOfTender; model.OnTimeAccept = item.OnTimeAccept; model.AdjustAccept = item.AdjustedAccept; model.SumOfAccept = item.SumOfAccept; model.OnTimeInbound = item.OnTimeInbound; model.AdjustInbound = item.AdjustedInbound; model.SumOfInbound = item.SumOfInbound; model.OnTimeOutbound = item.OnTimeOutbound; model.AdjustOutbound = item.AdjustedOutbound; model.SumOfOutbound = item.SumOfOutbound; model.OnTimeOntime = item.OnTimeDelivery; model.AdjustOntime = item.AdjustedDelivery; model.SumOfOntime = item.SumOfDelivery; model.OnTimeDocReturn = item.OnTimeDocreturn; model.AdjustDocReturn = item.AdjustedDocreturn; model.SumOfDocReturn = item.SumOfDocreturn; viewSummaryModel.Add(model); } } return(Json(viewSummaryModel, JsonRequestBehavior.AllowGet)); }