public ActionResult ExportExcel(string DepartmentId, string SectionId, string YearId, string MonthId, string MatNameId)
{
try
{
DataTable templateData = new DataTable();
// add IEnumerable<AdjustAcceptedViewModels>
List <AdjustDocReturnedViewModels> viewModel = new List <AdjustDocReturnedViewModels>();
//filter department
var q = from d in objBs.docReturnDelayBs.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();
//int c = q.Count();
foreach (var item in q)
{
AdjustDocReturnedViewModels model = new AdjustDocReturnedViewModels();
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;
model.PlanDocReturn = item.PLNDOCRETDATE_SCGL.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
model.ActualDocReturn = item.DOCRETDATE_SCGL.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, "ExportedAdjustDocReturn_" + DateTime.Now.ToString("yyyyMMddHHmm", new CultureInfo("th-TH")));//dump
return(View());
}
catch (Exception e)
{
TempData["Msg"] = "Error" + e.ToString();
return(View());
}
}
public ActionResult ExportExcel(string DepartmentId, string SectionId, string YearId, string MonthId, string MatNameId)
{
try
{
DataTable templateData = new DataTable();
// add IEnumerable<AdjustAcceptedViewModels>
List<AdjustAcceptedViewModels> viewModel = new List<AdjustAcceptedViewModels>();
//filter department
var q = from d in objBs.acceptedDelayBs.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)
{
AdjustAcceptedViewModels model = new AdjustAcceptedViewModels();
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.LastTender = item.LTNRDDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
model.PlanAccept = item.PLNACPDDATE.Value.ToString("dd/MM/yyyy HH:mm", new CultureInfo("th-TH"));
model.LastAccept = item.LACPDDATE.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, "ExportedAdjustAccept_" + DateTime.Now.ToString("yyyyMMddHHmm", new CultureInfo("th-TH")));//dump
return View();
}
catch (Exception e)
{
TempData["Msg"] = "Error" + e.ToString();
return View();
}
}
public ContentResult UploadReason()
{
using (TransactionScope Trans = new TransactionScope())
{
for (int i = 0; i < Request.Files.Count; i++)
{
string errorRef = "";
string reference = Request.Files.AllKeys[i];
HttpPostedFileBase FileUpload = Request.Files[i]; //Uploaded file
//Use the following properties to get file's name, size and MIMEType
string fileName = reference;
string targetpath = Server.MapPath("~/Content/Docs/docrtn/");
FileUpload.SaveAs(targetpath + DateTime.Now.ToString("yyyyMMddHHmm", new CultureInfo("th-TH")) + "_adjust.xlsx");
string pathToExcelFile = targetpath + DateTime.Now.ToString("yyyyMMddHHmm", new CultureInfo("th-TH")) + "_adjust.xlsx";
var ext = Path.GetExtension(pathToExcelFile);
int countDN = 0;
//if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
if (ext == ".xlsx")
{
DataTable dT = ExcelModels.openExcel(pathToExcelFile, 1);
try
{
foreach (DataRow dr in dT.Rows)
{
//Do record adjust data - send to approval
if (!String.IsNullOrEmpty(dr[0].ToString()))
{
string dn = dr[0].ToString();
int reasonId = Convert.ToInt32(dr[14].ToString());
string remark = dr[15].ToString();
string reasonName = objBs.reasonDocReturnBs.GetByID(reasonId).Name;
bool isadjust = objBs.reasonDocReturnBs.GetByID(Convert.ToInt32(reasonId)).IsAdjust;
DWH_ONTIME_DN ontimeDn = objBs.dWH_ONTIME_DNBs.GetByID(dn);
ontimeDn.SCGL_DOCRET_ADJUST = isadjust ? 0 : 0;
ontimeDn.SCGL_DOCRET_ADJUST_BY = User.Identity.Name;
ontimeDn.SCGL_DOCRET_ADJUST_DATE = DateTime.Now;
ontimeDn.SCGL_DOCRET_REASON = reasonName;
ontimeDn.SCGL_DOCRET_REASON_ID = Convert.ToInt32(reasonId);
ontimeDn.SCGL_DOCRET_REMARK = remark;
objBs.dWH_ONTIME_DNBs.Update(ontimeDn);
DocReturnDelay tmp_adjusted = objBs.docReturnDelayBs.GetByID(dn);
if (tmp_adjusted == null)
{
errorRef = errorRef + dn + " , ";
}
else
{
DocReturnAdjusted tmp_toInsert = new DocReturnAdjusted
{
CARRIER_ID = tmp_adjusted.CARRIER_ID,
DEPARTMENT_ID = tmp_adjusted.DEPARTMENT_ID,
DEPARTMENT_NAME = tmp_adjusted.DEPARTMENT_NAME,
SECTION_ID = tmp_adjusted.SECTION_ID,
SECTION_NAME = tmp_adjusted.SECTION_NAME,
MATFRIGRP = tmp_adjusted.MATFRIGRP,
MATNAME = tmp_adjusted.MATNAME,
REGION_ID = tmp_adjusted.REGION_ID,
REGION_NAME_EN = tmp_adjusted.REGION_NAME_EN,
REGION_NAME_TH = tmp_adjusted.REGION_NAME_TH,
SOLDTO = tmp_adjusted.SOLDTO,
SOLDTO_NAME = tmp_adjusted.SOLDTO_NAME,
SHIPTO = tmp_adjusted.SHIPTO,
TO_SHPG_LOC_NAME = tmp_adjusted.TO_SHPG_LOC_NAME,
VENDOR_CODE = tmp_adjusted.VENDOR_CODE,
VENDOR_NAME = tmp_adjusted.VENDOR_NAME,
PLNDOCRETDATE_SCGL = tmp_adjusted.PLNDOCRETDATE_SCGL,
PLNDOCRETDATE_SCGL_D = tmp_adjusted.PLNDOCRETDATE_SCGL_D,
DOCRETDATE_SCGL = tmp_adjusted.DOCRETDATE_SCGL,
DOCRETDATE_SCGL_D = tmp_adjusted.DOCRETDATE_SCGL_D,
ACTGIDATE = tmp_adjusted.ACTGIDATE,
ACTGIDATE_D = tmp_adjusted.ACTGIDATE_D,
SHPPOINT = tmp_adjusted.SHPPOINT,
TRUCK_TYPE = tmp_adjusted.TRUCK_TYPE,
DELVNO = tmp_adjusted.DELVNO,
SHPMNTNO = tmp_adjusted.SHPMNTNO,
LOADED_DATE = DateTime.Now,
SCGL_DOCRET_ADJUST = isadjust ? 1 : 0,
SCGL_DOCRET_ADJUST_BY = User.Identity.Name,
SCGL_DOCRET_ADJUST_DATE = DateTime.Now,
SCGL_DOCRET_REASON = reasonName,
SCGL_DOCRET_REASON_ID = Convert.ToInt32(reasonId),
SCGL_DOCRET_REMARK = remark
};
//insert for approval
objBs.docReturnAdjustedBs.Insert(tmp_toInsert);
//delete AcceptedDelays
objBs.docReturnDelayBs.Delete(dn);
countDN++;
}
}
}
Trans.Complete();
if (errorRef != "")
{
errorRef = "<div style='overflow:auto'> DN หมายเลข " + errorRef + "ได้ทำการ adjust ไปแล้ว </div>";
}
return(Content("อัพโหลดสำเร็จ " + countDN + " DN" + "<br>" + errorRef));
}
catch (Exception e)
{
return(Content("อัพโหลดไม่สำเร็จ กรอกข้อมูลไม่ถูกต้อง"));
}
}
//deleting excel file from folder
if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
}
}
return(Content("อัพโหลดไม่สำเร็จ ประเภทไฟล์ไม่ถูกต้อง"));
}
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());
}
}
