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