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));
        }