public JsonResult JsonAdjustOntimeTable(string DepartmentId, string SectionId, string YearId, string MonthId, string MatNameId)
{
ViewBag.DepartmentId = DepartmentId;
ViewBag.SectionId = SectionId;
ViewBag.MatNameId = MatNameId;
ViewBag.YearId = YearId;
ViewBag.MonthId = MonthId;
// add IEnumerable<AdjustAcceptedViewModels>
List <AdjustDeliveredViewModels> viewModel = new List <AdjustDeliveredViewModels>();
//filter department
var q = from d in objBs.ontimeDelayBs.GetByFilter(DepartmentId, SectionId, Convert.ToInt32(MonthId), Convert.ToInt32(YearId))
select d;
//filter matname
if (!String.IsNullOrEmpty(MatNameId))
{
q = q.Where(x => x.MATFRIGRP == MatNameId);
}
//int c = q.Count();
foreach (var item in q)
{
AdjustDeliveredViewModels model = new AdjustDeliveredViewModels();
model.Shipment = item.SHPMNTNO;
model.DeliveryNote = item.DELVNO;
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.ShiptoName = item.TO_SHPG_LOC_NAME;
model.ShippingPoint = item.SHPPOINT;
model.TruckType = item.TRUCK_TYPE;
if (item.REQUESTED_DATE != null)
{
model.RequestedDate = item.REQUESTED_DATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
}
else
{
model.RequestedDate = "";
}
if (item.ORDCMPDATE != null)
{
model.OrderCompDate = item.ORDCMPDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
}
else
{
model.OrderCompDate = "";
}
if (item.SHCRDATE != null)
{
model.ShcrDate = item.SHCRDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
}
else
{
model.ShcrDate = "";
}
model.PlanDelivery = item.PLNONTIMEDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
model.ActualDelivery = item.ACDLVDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
model.ActualGI = item.ACTGIDATE.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 DepartmentId, string SectionId, string YearId, string MonthId, string MatNameId)
{
try
{
DataTable templateData = new DataTable();
// add IEnumerable<AdjustAcceptedViewModels>
List <AdjustDeliveredViewModels> viewModel = new List <AdjustDeliveredViewModels>();
//filter department
var q = from d in objBs.ontimeDelayBs.GetByFilter(DepartmentId, SectionId, Convert.ToInt32(MonthId), Convert.ToInt32(YearId))
select d;
//filter matname
if (!String.IsNullOrEmpty(MatNameId))
{
q = q.Where(x => x.MATFRIGRP == MatNameId);
}
//int c = q.Count();
foreach (var item in q)
{
AdjustDeliveredViewModels model = new AdjustDeliveredViewModels();
model.Shipment = item.SHPMNTNO;
model.DeliveryNote = item.DELVNO;
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.ShiptoName = item.TO_SHPG_LOC_NAME;
model.ShippingPoint = item.SHPPOINT;
model.TruckType = item.TRUCK_TYPE;
if (item.REQUESTED_DATE != null)
{
model.RequestedDate = item.REQUESTED_DATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
}
else
{
model.RequestedDate = "";
}
if (item.ORDCMPDATE != null)
{
model.OrderCompDate = item.ORDCMPDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
}
else
{
model.OrderCompDate = "";
}
if (item.SHCRDATE != null)
{
model.ShcrDate = item.SHCRDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
}
else
{
model.ShcrDate = "";
}
model.PlanDelivery = item.PLNONTIMEDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
model.ActualDelivery = item.ACDLVDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
model.ActualGI = item.ACTGIDATE.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, "ExportedAdjustDelivery_" + DateTime.Now.ToString("yyyyMMddHHmm", new CultureInfo("th-TH")));//dump
return(View());
}
catch (Exception e)
{
TempData["Msg"] = "Error" + e.ToString();
return(View());
}
}
