public ActionResult DeliveryFeeVectorExcelUpload(string VenderCode)
{
var model = new FileUploadJsonModel {
VenderCode = VenderCode, SelectOrderNoList = GetWhereTypes(), Status = 10
};
return(View(model));
}
public ActionResult DeliveryFeeVectorExcelUploadPost(FileUploadJsonModel filter)
{
Log.Info("服务器开始运行小包导入账单方法");
HttpContext.Server.ScriptTimeout = 200 * 60;
var model = new FileUploadJsonModel
{
VenderCode = filter.VenderCode,
SelectOrderNo = filter.SelectOrderNo,
SelectOrderNoList = GetWhereTypes()
};
if (filter.VenderCode.IsNullOrWhiteSpace())
{
model.Status = 0;
model.Info = "服务商为空!";
return(View("DeliveryFeeVectorExcelUpload", model));
}
#region valid the excel
HttpFileCollectionBase files = Request.Files;
if (files == null || files.Count == 0)
{
model.Status = 0;
model.Info = "上传文件不能为空!";
return(View("DeliveryFeeVectorExcelUpload", 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("DeliveryFeeVectorExcelUpload", model));
}
#endregion
try
{
Log.Info("开始保存小包上传的Execl文件");
#region save tempfile
string setting = System.Configuration.ConfigurationManager.AppSettings["Upload_Path"];
string tempFilePath = Path.Combine(setting, string.Format("DeliveryFeeImport_{0}{1}.{2}", _workContext.User.UserUame, DateTime.Now.Ticks.ToString(), fileType));
excelfile.SaveAs(tempFilePath);
#endregion
Log.Info("完成保存小包上传的Execl文件");
var VenderUploadDeliveryFeeList = new List <DeliveryImportDataModel>();
#region Get data from Excel
try
{
//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 DeliveryImportDataModel();
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 += " [订单号/跟踪号]不能为空; ";
}
data.ReceivingDateStr = row.Field <string>("收货日期");
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.VenderName = string.IsNullOrEmpty(data.VenderName) ? string.Empty : data.VenderName.Trim();
data.ShippingMethodName = row.Field <string>("运输方式");
data.ShippingMethodName = string.IsNullOrEmpty(data.ShippingMethodName) ? string.Empty : data.ShippingMethodName.Trim();
data.CountryName = row.Field <string>("国家");
#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.TotalFee = Convert.ToDecimal(row.Field <string>("总费用"));
}
catch
{
data.ErrorReason += "总费用,不为数字,上传数据为：" + row.Field <string>("总费用");
}
}
else
{
data.ErrorReason += " [总费用]不能为空; ";
}
#endregion
data.CreatedOn = DateTime.Now;
data.CreatedBy = _workContext.User.UserUame;
data.Status = 0;
VenderUploadDeliveryFeeList.Add(data);
}
//VenderUploadDeliveryFeeList = dataTable.AsEnumerable().Select(row => new DeliveryImportDataModel
//{
// OrderNumber = row.Field<string>("订单号/跟踪号"),
// ReceivingDateStr = row.Field<string>("收货日期"),
// VenderName = row.Field<string>("服务商"),
// ShippingMethodName = row.Field<string>("运输方式"),
// CountryName = row.Field<string>("国家"),
// Weight = Convert.ToDecimal(row.Field<string>("货物重量kg")),
// SettleWeight = Convert.ToDecimal(row.Field<string>("计费重量kg")),
// TotalFee = Convert.ToDecimal(row.Field<string>("总费用")),
// CreatedOn = DateTime.Now,
// CreatedBy = _workContext.User.UserUame,
// Status = 0
//}).ToList();
DateTime dtTemp = DateTime.Now;
foreach (var i in VenderUploadDeliveryFeeList)
{
if (!string.IsNullOrEmpty(i.ReceivingDateStr))
{
if (DateTime.TryParse(i.ReceivingDateStr, out dtTemp))
{
i.ReceivingDate = dtTemp;
}
else
{
i.ErrorReason += " 日期格式不对;请采用excel标准日期上传! ";
}
}
}
Log.Info("完成验证execl数据");
dataTable = null;
}
catch (Exception ex)
{
Log.Exception(ex);
throw new BusinessLogicException("读取Excel数据失败");
}
#endregion
#region check 3 columns
//DeliveryImportDataModel first = VenderUploadDeliveryFeeList.FirstOrDefault();
//foreach (var t in VenderUploadDeliveryFeeList)
//{
// if (first.VenderName != t.VenderName)
// {
// t.ErrorReason += " 一次上传只允许一个服务商的数据; ";
// }
// //if (first.ShippingMethodName != t.ShippingMethodName)
// //{
// // t.ErrorReason += " 一次上传只允许一种运输方式的数据; ";
// //}
// if (!t.TotalFee.HasValue)
// {
// t.ErrorReason += " [总费用]不能为空; ";
// }
// if (string.IsNullOrEmpty(t.OrderNumber))
// {
// t.ErrorReason += " [订单号/跟踪号]不能为空; ";
// }
// if (!t.SettleWeight.HasValue)
// {
// t.ErrorReason += " [计费重量kg]不能为空; ";
// }
//}
//var duplicates = VenderUploadDeliveryFeeList.GroupBy(s => s.OrderNumber).SelectMany(grp => grp.Skip(1));
//foreach (var dup in duplicates)
//{
// foreach (var v in VenderUploadDeliveryFeeList.Where(t => t.OrderNumber == dup.OrderNumber))
// {
// v.ErrorReason += string.Format(" 单号[{0}]发现多条重复数据; ", dup.OrderNumber);
// }
//}
#endregion
#region Save Data
Log.Info("开始保存execl数据到数据库");
int countShouldNotBeEmpty = VenderUploadDeliveryFeeList.Count(t => !string.IsNullOrEmpty(t.ErrorReason));
var dataList = Mapper.Map <List <DeliveryImportDataModel>, List <DeliveryImportAccountCheck> >(
VenderUploadDeliveryFeeList.Where(t => string.IsNullOrEmpty(t.ErrorReason)).ToList());
foreach (var check in dataList)
{
check.UserName = _workContext.User.UserUame; //当前用户
}
model.UploadFailCount = 0;
if (countShouldNotBeEmpty > 0)
{
model.ErrorData.AddRange(VenderUploadDeliveryFeeList.Where(t => !string.IsNullOrEmpty(t.ErrorReason)).ToList());
model.UploadFailCount = countShouldNotBeEmpty;
}
//写入数据
if (_financialService.SaveDeliveryImportAccountChecks(dataList, filter.VenderCode, filter.SelectOrderNo) && dataList.Count(t => t.IsTrue == false) > 0)
{
var output = dataList.Where(t => t.IsTrue == false).ToList();
model.ErrorData.AddRange(Mapper.Map <List <DeliveryImportAccountCheck>, List <DeliveryImportDataModel> >(output));
//统计
model.UploadFailCount += output.Count();
}
if (model.UploadFailCount == 0)
{
model.Status = 1;
model.UploadSuccessCount = VenderUploadDeliveryFeeList.Count();
}
else
{
model.Status = 3;
model.UploadSuccessCount = VenderUploadDeliveryFeeList.Count() - model.UploadFailCount;
}
Log.Info("完成保存execl数据到数据库");
#endregion
}
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("服务器完成运行小包导入账单方法");
return(View("DeliveryFeeVectorExcelUpload", model));
}