public ActionResult ExpressDeliveryExeclUpload(string VenderCode) { var model = new ExpressFileUploadJsonModel { VenderCode = VenderCode, SelectOrderNoList = GetWhereTypes(), Status = 10 }; return(View(model)); }
public ActionResult ExpressDeliveryExeclUploadPost(ExpressFileUploadJsonModel filter) { Log.Info("服务器开始运行快递导入账单方法"); HttpContext.Server.ScriptTimeout = 200 * 60; var model = new ExpressFileUploadJsonModel { VenderCode = filter.VenderCode, SelectOrderNo = filter.SelectOrderNo, SelectOrderNoList = GetWhereTypes() }; #region valid the excel HttpFileCollectionBase files = Request.Files; if (files == null || files.Count == 0) { model.Status = 0; model.Info = "上传文件不能为空!"; return(View("ExpressDeliveryExeclUpload", model)); } HttpPostedFileBase excelfile = files[0]; string fileType = excelfile.FileName.Substring(excelfile.FileName.LastIndexOf(".") + 1); if (!_Support_Excel_File_Types.Contains(fileType)) { model.Status = 0; model.Info = "上传文件格式不对,目前只支持.xls,.xlsx格式!"; return(View("ExpressDeliveryExeclUpload", model)); } #endregion var VenderUploadDeliveryFeeList = new List <ExpressDeliveryImportDataModel>(); var passList = new List <ExpressDeliveryImportAccountCheck>(); #region Get data from Excel try { Log.Info("开始保存快递上传的Execl文件"); #region save tempfile string setting = System.Configuration.ConfigurationManager.AppSettings["Upload_Path"]; string tempFilePath = Path.Combine(setting, string.Format("ExpressDeliveryFeeImport_{0}{1}.{2}", _workContext.User.UserUame, DateTime.Now.Ticks.ToString(), fileType)); excelfile.SaveAs(tempFilePath); #endregion Log.Info("完成保存快递上传的Execl文件"); //DataTable dataTable = ExcelHelper.ReadToDataTable(excelfile.InputStream); Log.Info("开始读起execl到datatable"); DataTable dataTable = ExcelHelper.ReadToDataTable(tempFilePath); Log.Info("完成读起execl到datatable"); string venderName = string.Empty; var dorderNumber = new Dictionary <string, string>(); Log.Info("开始验证execl数据"); foreach (var row in dataTable.AsEnumerable()) { var data = new ExpressDeliveryImportDataModel(); if (!string.IsNullOrWhiteSpace(row.Field <string>("订单号/跟踪号"))) { if (dorderNumber.ContainsKey(row.Field <string>("订单号/跟踪号").Trim())) { data.ErrorReason += "订单号/跟踪号,已经存在 上传数据为:" + row.Field <string>("订单号/跟踪号"); } else { dorderNumber.Add(row.Field <string>("订单号/跟踪号").Trim(), row.Field <string>("订单号/跟踪号").Trim()); } data.OrderNumber = row.Field <string>("订单号/跟踪号").Trim(); } else { data.ErrorReason += " [订单号/跟踪号]不能为空; "; } if (!string.IsNullOrWhiteSpace(row.Field <string>("服务商"))) { if (venderName.IsNullOrEmpty()) { venderName = row.Field <string>("服务商").Trim(); } else { if (venderName != row.Field <string>("服务商").Trim()) { data.ErrorReason += " 一次上传只允许一个服务商的数据; "; } } data.VenderName = row.Field <string>("服务商").Trim(); } data.ShippingMethodName = row.Field <string>("运输方式"); data.ShippingMethodName = string.IsNullOrEmpty(data.ShippingMethodName) ? string.Empty : data.ShippingMethodName.Trim(); data.CountryName = row.Field <string>("国家"); data.IncidentalRemark = row.Field <string>("杂费备注"); var receivingDate = row.Field <string>("收货日期"); #region 收货日期 var dtTemp = DateTime.Now; if (!string.IsNullOrEmpty(receivingDate)) { if (DateTime.TryParse(receivingDate, out dtTemp)) { data.ReceivingDate = dtTemp; } else { data.ErrorReason += " 日期格式不对;请采用excel标准日期上传! "; } } #endregion #region 数字列 if (!string.IsNullOrEmpty(row.Field <string>("货物重量kg"))) { try { data.Weight = Convert.ToDecimal(row.Field <string>("货物重量kg")); } catch { data.ErrorReason += "货物重量kg,不为数字,上传数据为:" + row.Field <string>("货物重量kg"); } } if (!string.IsNullOrEmpty(row.Field <string>("计费重量kg"))) { try { data.SettleWeight = Convert.ToDecimal(row.Field <string>("计费重量kg")); } catch { data.ErrorReason += "计费重量kg,不为数字,上传数据为:" + row.Field <string>("计费重量kg"); } } else { data.ErrorReason += " [计费重量kg]不能为空;"; } if (!string.IsNullOrEmpty(row.Field <string>("运费"))) { try { data.Freight = Convert.ToDecimal(row.Field <string>("运费")); } catch { data.ErrorReason += "运费,不为数字,上传数据为:" + row.Field <string>("运费"); } } if (!string.IsNullOrEmpty(row.Field <string>("挂号费"))) { try { data.Register = Convert.ToDecimal(row.Field <string>("挂号费")); } catch { data.ErrorReason += "挂号费,不为数字,上传数据为:" + row.Field <string>("挂号费"); } } if (!string.IsNullOrEmpty(row.Field <string>("燃油费"))) { try { data.FuelCharge = Convert.ToDecimal(row.Field <string>("燃油费")); } catch { data.ErrorReason += "燃油费,不为数字,上传数据为:" + row.Field <string>("燃油费"); } } if (!string.IsNullOrEmpty(row.Field <string>("附加费"))) { try { data.Surcharge = Convert.ToDecimal(row.Field <string>("附加费")); } catch { data.ErrorReason += "附加费,不为数字,上传数据为:" + row.Field <string>("附加费"); } } if (!string.IsNullOrEmpty(row.Field <string>("关税预付服务费"))) { try { data.TariffPrepayFee = Convert.ToDecimal(row.Field <string>("关税预付服务费")); } catch { data.ErrorReason += "关税预付服务费,不为数字,上传数据为:" + row.Field <string>("关税预付服务费"); } } if (!string.IsNullOrEmpty(row.Field <string>("超长超重超周围长费(每件总和)"))) { try { data.OverWeightLengthGirthFee = Convert.ToDecimal(row.Field <string>("超长超重超周围长费(每件总和)")); } catch { data.ErrorReason += "超长超重超周围长费(每件总和),不为数字,上传数据为:" + row.Field <string>("超长超重超周围长费(每件总和)"); } } if (!string.IsNullOrEmpty(row.Field <string>("安全附加费"))) { try { data.SecurityAppendFee = Convert.ToDecimal(row.Field <string>("安全附加费")); } catch { data.ErrorReason += "安全附加费,不为数字,上传数据为:" + row.Field <string>("安全附加费"); } } if (!string.IsNullOrEmpty(row.Field <string>("增值税率费"))) { try { data.AddedTaxFee = Convert.ToDecimal(row.Field <string>("增值税率费")); } catch { data.ErrorReason += "增值税率费,不为数字,上传数据为:" + row.Field <string>("增值税率费"); } } if (!string.IsNullOrEmpty(row.Field <string>("杂费"))) { try { data.Incidentals = Convert.ToDecimal(row.Field <string>("杂费")); } catch { data.ErrorReason += "杂费,不为数字,上传数据为:" + row.Field <string>("杂费"); } } if (!string.IsNullOrEmpty(row.Field <string>("总费用"))) { try { data.TotalFee = Convert.ToDecimal(row.Field <string>("总费用")); } catch { data.ErrorReason += "总费用,不为数字,上传数据为:" + data.TotalFee; } } else { data.ErrorReason += " [总费用]不能为空; "; } #endregion VenderUploadDeliveryFeeList.Add(data); //验证通过 if (data.ErrorReason.IsNullOrWhiteSpace()) { passList.Add(new ExpressDeliveryImportAccountCheck() { AddedTaxFee = data.AddedTaxFee, CountryName = data.CountryName, CreatedBy = _workContext.User.UserUame, CreatedOn = DateTime.Now, Freight = data.Freight, FuelCharge = data.FuelCharge, Incidentals = data.Incidentals, IncidentalRemark = data.IncidentalRemark, OrderNumber = data.OrderNumber, OverWeightLengthGirthFee = data.OverWeightLengthGirthFee, TariffPrepayFee = data.TariffPrepayFee, ReceivingDate = data.ReceivingDate, Register = data.Register, SecurityAppendFee = data.SecurityAppendFee, SettleWeight = data.SettleWeight, ShippingMethodName = data.ShippingMethodName, Status = 0, Surcharge = data.Surcharge, TotalFee = data.TotalFee, UserName = _workContext.User.UserUame, VenderName = data.VenderName, WayBillNumber = data.WayBillNumber, Weight = data.Weight }); } } dorderNumber.Clear(); dataTable.Clear(); Log.Info("完成验证execl数据"); } catch (Exception ex) { Log.Exception(ex); model.Status = 0; model.Info = "读取Excel数据失败"; } #endregion Log.Info("开始保存execl数据到数据库"); model.UploadFailCount = 0; if (VenderUploadDeliveryFeeList.Any(t => !string.IsNullOrEmpty(t.ErrorReason))) { model.ErrorData.AddRange(VenderUploadDeliveryFeeList.Where(t => !string.IsNullOrEmpty(t.ErrorReason)).ToList()); model.UploadFailCount = VenderUploadDeliveryFeeList.Count(t => !string.IsNullOrEmpty(t.ErrorReason)); } try { //写入数据 if (_financialService.SaveDeliveryImportAccountChecks(passList, filter.VenderCode, filter.SelectOrderNo) && passList.Any(t => t.IsTrue == false)) { passList.Where(t => t.IsTrue == false).ToList().ForEach(p => { var errormodel = VenderUploadDeliveryFeeList.FirstOrDefault(e => e.OrderNumber == p.OrderNumber); errormodel.ErrorReason = p.ErrorReason; model.ErrorData.Add(errormodel); }); model.UploadFailCount += passList.Count(t => t.IsTrue == false); } if (model.UploadFailCount == 0) { model.Status = 1; model.UploadSuccessCount = VenderUploadDeliveryFeeList.Count(); } else { model.Status = 3; model.UploadSuccessCount = VenderUploadDeliveryFeeList.Count() - model.UploadFailCount; } } catch (BusinessLogicException ex) { Log.Exception(ex); model.Status = 0; model.Info = ex.Message; } catch (Exception ex) { Log.Error(ex.ToString()); model.Status = 0; model.Info = "保存数据出错"; } Log.Info("完成保存execl数据到数据库"); Log.Info("服务器完成运行快递导入账单方法"); return(View("ExpressDeliveryExeclUpload", model)); }