public JsonResult JsonAdjustTenderTable(string TenderUser, string SegmentId, string YearId, string MonthId, string ShipPoint, string ShipTo, string TruckType) { //ViewBag.SegmentId = SegmentId; ViewBag.YearId = YearId; ViewBag.MonthId = MonthId; ViewBag.ShipPoint = ShipPoint; ViewBag.ShipTo = ShipTo; ViewBag.TruckType = TruckType; // add IEnumerable<AdjustAcceptedViewModels> List <AdjustTenderedViewModels> viewModel = new List <AdjustTenderedViewModels>(); //filter department //var q = from d in objBs.tenderedDelayBs.GetByFilter(SegmentId, Convert.ToInt32(YearId), Convert.ToInt32(MonthId)) // select d; var month = Convert.ToInt16(MonthId); var year = Convert.ToInt16(YearId); var q = from d in objBs.tenderedDelayBs.GetAll().Where(x => x.CRTD_USR_CD == TenderUser && x.PLNTNRDDATE_D.Value.Year == year && x.PLNTNRDDATE_D.Value.Month == month) select d; q.ToList(); ////filter Shipping Point //if (!String.IsNullOrEmpty(ShipPoint)) // q = q.Where(x => x.SHPPOINT == ShipPoint); ////filter Shipping To //if (!String.IsNullOrEmpty(ShipTo)) // q = q.Where(x => x.SHIPTO == ShipTo); ////filter Truck Type //if (!String.IsNullOrEmpty(TruckType)) // q = q.Where(x => x.TRUCK_TYPE == TruckType); //int c = q.Count(); foreach (var item in q) { AdjustTenderedViewModels model = new AdjustTenderedViewModels(); model.TenderUser = item.CRTD_USR_CD; model.Shipment = item.SHPMNTNO; model.CarrierId = item.CARRIER_ID; model.RegionId = item.REGION_ID; model.RegionName = item.REGION_NAME_TH; model.Soldto = item.SOLDTO; model.SoldtoName = item.SOLDTO_NAME; model.Shipto = item.SHIPTO; model.Segment = item.SEGMENT; model.SubSegment = item.SUBSEGMENT; model.ShiptoName = item.LAST_SHPG_LOC_NAME; model.ShippingPoint = item.SHPPOINT; model.TruckType = item.TRUCK_TYPE; model.ShcrDate = item.SHCRDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH")); model.PlanTender = item.PLNTNRDDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH")); model.FirstTender = item.FTNRDDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH")); viewModel.Add(model); } var ddlReason = (from r in objBs.reasonOntimeBs.GetAll() select new { Id = r.Id, Name = r.Name }).Distinct().OrderBy(x => x.Name); ViewBag.ReasonId = new SelectList(ddlReason.ToList(), "Id", "Name"); var jsonResult = Json(viewModel, JsonRequestBehavior.AllowGet); jsonResult.MaxJsonLength = int.MaxValue; return(jsonResult); }
public ActionResult ExportExcel(string TenderUser, string SegmentId, string YearId, string MonthId, string ShipPoint, string ShipTo, string TruckType) { try { DataTable templateData = new DataTable(); // add IEnumerable<AdjustAcceptedViewModels> List <AdjustTenderedViewModels> viewModel = new List <AdjustTenderedViewModels>(); //filter department //var q = from d in objBs.tenderedDelayBs.GetByFilter(SegmentId, Convert.ToInt32(YearId), Convert.ToInt32(MonthId)) // select d; var month = Convert.ToInt16(MonthId); var year = Convert.ToInt16(YearId); var q = from d in objBs.tenderedDelayBs.GetAll().Where(x => x.CRTD_USR_CD == TenderUser && x.PLNTNRDDATE_D.Value.Year == year && x.PLNTNRDDATE_D.Value.Month == month) select d; q.ToList(); ////filter Shipping Point //if (!String.IsNullOrEmpty(ShipPoint)) // q = q.Where(x => x.SHPPOINT == ShipPoint); ////filter Shipping To //if (!String.IsNullOrEmpty(ShipTo)) // q = q.Where(x => x.SHIPTO == ShipTo); ////filter Truck Type //if (!String.IsNullOrEmpty(TruckType)) // q = q.Where(x => x.TRUCK_TYPE == TruckType); //int c = q.Count(); foreach (var item in q) { AdjustTenderedViewModels model = new AdjustTenderedViewModels(); model.TenderUser = item.CRTD_USR_CD; model.Shipment = item.SHPMNTNO; model.CarrierId = item.CARRIER_ID; model.RegionId = item.REGION_ID; model.RegionName = item.REGION_NAME_TH; model.Soldto = item.SOLDTO; model.SoldtoName = item.SOLDTO_NAME; model.Shipto = item.SHIPTO; model.Segment = item.SEGMENT; model.SubSegment = item.SUBSEGMENT; model.ShiptoName = item.LAST_SHPG_LOC_NAME; model.ShippingPoint = item.SHPPOINT; model.TruckType = item.TRUCK_TYPE; model.ShcrDate = item.SHCRDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH")); model.PlanTender = item.PLNTNRDDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH")); model.FirstTender = item.FTNRDDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH")); viewModel.Add(model); } templateData.Merge(ExcelModels.ToDataTable(viewModel)); templateData.Columns.Add("Remark", typeof(string)); ExcelModels ex = new ExcelModels(); ex.DumpExcel(templateData, "ExportedAdjustTender_" + DateTime.Now.ToString("yyyyMMddHHmm", new CultureInfo("th-TH")));//dump return(View()); } catch (Exception e) { TempData["Msg"] = "Error" + e.ToString(); return(View()); } }