/// <summary>
/// 根据路径获取Excel数据，并转为excel
/// </summary>
/// <param name="hpf"></param>
/// <returns></returns>
private DataSet GetDataFromExcel(HttpPostedFileBase hpf)
{
string uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
string targetPath = Path.Combine(Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH, base.UserInfo.ProjectID.ToString(), Runbow.TWS.Common.Constants.TEMPFOLDER);
if (!Directory.Exists(targetPath))
{
Directory.CreateDirectory(targetPath);
}
string fileName = base.UserInfo.ID.ToString() + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + Path.GetFileName(hpf.FileName);
string fullPath = Path.Combine(targetPath, fileName);
hpf.SaveAs(fullPath);
hpf.InputStream.Close();
Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(fullPath);
DataSet ds = excelHelper.GetAllDataFromAllSheets();
excelHelper.Dispose();
MyFile.Delete(fullPath);
return(ds);
}
public ActionResult SettledCompare(SettledCompareViewModel vm)
{
vm.Customers = ApplicationConfigHelper.GetProjectUserCustomers(base.UserInfo.ProjectID, base.UserInfo.ID)
.Select(c => new SelectListItem()
{
Value = c.CustomerID.ToString(), Text = c.CustomerName
});
vm.DisplyMessage = vm.SettledType == 0 ? "导入应收结算比对(客户运单号|运费|点费|燃油附加费|其他费用)" : "导入应付结算比对(客户运单号|运费|点费|燃油附加费|其他费用)";
if (Request.Files.Count > 0)
{
HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase;
if (hpf.ContentLength > 0)
{
string uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
string targetPath = Path.Combine(Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH, base.UserInfo.ProjectID.ToString(), Runbow.TWS.Common.Constants.TEMPFOLDER);
if (!Directory.Exists(targetPath))
{
Directory.CreateDirectory(targetPath);
}
string fileName = base.UserInfo.ID.ToString() + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + Path.GetFileName(hpf.FileName);
string fullPath = Path.Combine(targetPath, fileName);
hpf.SaveAs(fullPath);
hpf.InputStream.Close();
Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(fullPath);
DataSet ds = excelHelper.GetAllDataFromAllSheets();
excelHelper.Dispose();
MyFile.Delete(fullPath);
if (ds == null || ds.Tables == null || ds.Tables[0].Rows.Count == 0)
{
vm.ErrorMessage = "Excel文件内容有误";
}
else
{
IList <SettledPodCompare> settledPodCompares = new List <SettledPodCompare>();
StringBuilder errorMessage = new StringBuilder();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string customerOrderNumber = string.Empty;
decimal excelShipAmt = 0;
decimal excelBafAmt = 0;
decimal excelPointAmt = 0;
decimal excelOtherAmt = 0;
if (ds.Tables[0].Columns.Contains("客户运单号"))
{
customerOrderNumber = ds.Tables[0].Rows[i]["客户运单号"].ToString();
if (string.IsNullOrEmpty(customerOrderNumber))
{
errorMessage.Append("Excel中第").Append(i + 1).Append("行客户运单号列为空,请重新编辑Excel");
}
}
else
{
errorMessage.Append("Excel中无客户运单号列，请重新编辑").Append("<br />");
}
if (ds.Tables[0].Columns.Contains("运费"))
{
decimal tempShipAmt;
if (decimal.TryParse(ds.Tables[0].Rows[i]["运费"].ToString().Trim(), out tempShipAmt))
{
excelShipAmt = tempShipAmt;
}
}
if (ds.Tables[0].Columns.Contains("点费"))
{
decimal tempPointAmt;
if (decimal.TryParse(ds.Tables[0].Rows[i]["点费"].ToString().Trim(), out tempPointAmt))
{
excelPointAmt = tempPointAmt;
}
}
if (ds.Tables[0].Columns.Contains("燃油附加费"))
{
decimal tempBafAmt;
if (decimal.TryParse(ds.Tables[0].Rows[i]["燃油附加费"].ToString().Trim(), out tempBafAmt))
{
excelBafAmt = tempBafAmt;
}
}
if (ds.Tables[0].Columns.Contains("其他费用"))
{
decimal tempOtherAmt;
if (decimal.TryParse(ds.Tables[0].Rows[i]["其他费用"].ToString().Trim(), out tempOtherAmt))
{
excelOtherAmt = tempOtherAmt;
}
}
settledPodCompares.Add(new SettledPodCompare()
{
CustomerOrderNumber = customerOrderNumber,
CompareShipAmt = excelShipAmt,
CompareBAFAmt = excelBafAmt,
ComparePointAmt = excelPointAmt,
CompareOtherAmt = excelOtherAmt,
CompareTotalAmt = excelShipAmt + excelBafAmt + excelPointAmt + excelOtherAmt,
SettledType = vm.SettledType,
ProjectID = base.UserInfo.ProjectID,
CustomerOrShipperID = vm.SettledType == 0 ? vm.CustomerID : vm.ShipperID,
RelatedCustomerID = vm.CustomerID
});
}
if (errorMessage.Length > 0)
{
vm.ErrorMessage = errorMessage.ToString();
return(View(vm));
}
var Response = new SettledService().GetSettledPodByCondition(new GetSettledPodByConditionRequest()
{
CustomerOrderNumberCollection = settledPodCompares.Select(s => s.CustomerOrderNumber),
SettledType = vm.SettledType,
CustomerID = vm.CustomerID,
ShipperID = vm.ShipperID
});
if (Response.IsSuccess)
{
settledPodCompares.Each((i, s) => {
var tempSettledPod = Response.Result.FirstOrDefault(r => r.CustomerOrderNumber == s.CustomerOrderNumber);
s.ActualDeliveryDate = DateTime.MinValue;
if (tempSettledPod != null)
{
s.SystemNumber = tempSettledPod.SystemNumber;
s.CustomerOrShipperName = tempSettledPod.CustomerOrShipperName;
s.PodID = tempSettledPod.PodID;
s.ID = tempSettledPod.ID;
s.SettledNumber = tempSettledPod.SettledNumber;
s.StartCityID = tempSettledPod.StartCityID;
s.StartCityName = tempSettledPod.StartCityName;
s.EndCityID = tempSettledPod.EndCityID;
s.EndCityName = tempSettledPod.EndCityName;
s.ShipperTypeID = tempSettledPod.ShipperTypeID;
s.ShipperTypeName = tempSettledPod.ShipperTypeName;
s.PODTypeID = tempSettledPod.PODTypeID;
s.PODTypeName = tempSettledPod.PODTypeName;
s.TtlOrTplID = tempSettledPod.TtlOrTplID;
s.TtlOrTplName = tempSettledPod.TtlOrTplName;
s.ActualDeliveryDate = tempSettledPod.ActualDeliveryDate ?? DateTime.MinValue;
s.BoxNumber = tempSettledPod.BoxNumber;
s.Weight = tempSettledPod.Weight;
s.GoodsNumber = tempSettledPod.GoodsNumber;
s.Volume = tempSettledPod.Volume;
s.Remark = tempSettledPod.Remark;
s.ShipAmt = tempSettledPod.ShipAmt;
s.BAFAmt = tempSettledPod.BAFAmt;
s.PointAmt = tempSettledPod.PointAmt;
s.OtherAmt = tempSettledPod.OtherAmt;
s.TotalAmt = tempSettledPod.TotalAmt;
s.Str1 = tempSettledPod.Str1;
s.Str2 = tempSettledPod.Str2;
s.Str3 = tempSettledPod.Str3;
s.Str4 = tempSettledPod.Str4;
s.Str5 = tempSettledPod.Str5;
s.DateTime1 = tempSettledPod.DateTime1;
s.DateTime2 = tempSettledPod.DateTime2;
s.Creator = tempSettledPod.Creator;
s.CreateTime = tempSettledPod.CreateTime;
s.InvoiceID = tempSettledPod.InvoiceID;
s.IsAudit = tempSettledPod.IsAudit;
}
});
vm.SettledPodCompareCollection = settledPodCompares;
}
else
{
vm.ErrorMessage = "获取系统结算信息失败！";
}
}
}
else
{
vm.ErrorMessage = "Excel文件内容为空";
}
}
else
{
vm.ErrorMessage = "请选择excel文件";
}
return(View(vm));
}
public string PodImport(string customer)
{
string uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
string targetPath = Path.Combine(uploadFolderPath, "AMSTempFile");
string url = string.Empty, actualNameInServer = string.Empty, ext = string.Empty;
if (string.IsNullOrEmpty(targetPath) || !Path.IsPathRooted(targetPath))
{
return(new { msg = "程序出错！" }.ToJsonString());
}
if (Request.Files.Count > 0)
{
HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase;
if (hpf.ContentLength > 0)
{
if (!Directory.Exists(targetPath))
{
Directory.CreateDirectory(targetPath);
}
ext = Path.GetExtension(hpf.FileName);
if (!ext.ToLower().Equals(".xls") && !ext.ToLower().Equals(".xlsx"))
{
return(new { msg = "请选择Excel格式的文件" }.ToJsonString());
}
actualNameInServer = DateTime.Now.ToString("yyyyMMddHHmmss") + ext;
url = Path.Combine(targetPath, actualNameInServer);
hpf.SaveAs(url);
hpf.InputStream.Close();
Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(url);
DataSet ds = excelHelper.GetAllDataFromAllSheets();
excelHelper.Dispose();
MyFile.Delete(url);
IEnumerable <Pod> pods = null;
List <Pod> pp = new List <Pod>();
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
StringBuilder results = new StringBuilder();
BaiXingService bxService = new BaiXingService();
string str1, str37, str4, str5, str36, str10, str11, str38, str2, customerOrderNumber;
string systemNumber = "";
long podID = 0;
Double weight = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
//调度运单号,异常收件原因,取件方联系人,取件方联系电话,取件方联系地址,到件方联系人,到件方联系电话,到件方联系地址
//到件方联系电话,货物名称,货物重量,订单备注,委托方联系人,委托方客户卡号
customerOrderNumber = row["委托方联系人"].ToString().Trim(); //客户订单号
if (string.IsNullOrEmpty(customerOrderNumber))
{
continue;
}
else
{
Runbow.TWS.MessageContracts.POD.GetBaiXingRequest bxRequest = new MessageContracts.POD.GetBaiXingRequest()
{
CustomerOrderNumber = customerOrderNumber
};
Response <IEnumerable <Pod> > Podresponse = bxService.GetBaiXingPod(bxRequest);
if (Podresponse.IsSuccess && Podresponse.Result.Count() > 0)
{
#region pod赋值
//快递公司不是顺丰直接过滤
if (!Podresponse.Result.First().Str17.Contains("顺丰"))
{
continue;
}
//对应的快递信息PODID
if (Podresponse.Result.Count() > 1)
{
podID = Podresponse.Result.LastOrDefault().ID;
}
//PodID = Podresponse.Result.First().ID;
systemNumber = Podresponse.Result.First().SystemNumber;
try
{
weight = Convert.ToDouble(row["货物重量"].ToString().Trim()); //重量(公斤)
}
catch
{
}
str1 = row["调度运单号"].ToString().Trim(); //快递单号
str37 = row["异常收件原因"].ToString().Trim(); //异常说明
str4 = row["取件方联系人"].ToString().Trim(); //发货方联系人
str5 = row["取件方联系电话"].ToString().Trim(); //发货方电话
str36 = row["取件方联系地址"].ToString().Trim(); //发货方详细地址
str10 = row["到件方联系人"].ToString().Trim(); //收货方联系人
str11 = row["到件方联系电话"].ToString().Trim(); //收货方电话
str38 = row["到件方联系地址"].ToString().Trim(); //收货方详细地址
str2 = row["货物名称"].ToString().Trim(); //商品品名
//str1 = row["订单备注"].ToString().Trim(); //备注信息
//str1 = row["委托方客户卡号"].ToString().Trim(); //委托方客户卡号
//p.CustomerID = 40;
Pod p = new Pod()
{
ID = podID,
ProjectID = 1,
SystemNumber = systemNumber + "-1",
CustomerOrderNumber = customerOrderNumber,
CustomerID = Podresponse.Result.First().CustomerID, //同一客户，分单机制
CustomerName = Podresponse.Result.First().CustomerName,
ActualDeliveryDate = Podresponse.Result.First().ActualDeliveryDate,
StartCityID = 1, //Podresponse.Result.First().StartCityID,
StartCityName = Podresponse.Result.First().StartCityName,
EndCityID = 2, //Podresponse.Result.First().EndCityID,
EndCityName = Podresponse.Result.First().EndCityName,
PODStateID = Podresponse.Result.First().PODStateID,
PODStateName = Podresponse.Result.First().PODStateName,
ShipperTypeID = 36,
ShipperTypeName = "快递",
BoxNumber = Podresponse.Result.First().BoxNumber,
Weight = weight,
Volume = Podresponse.Result.First().Volume,
GoodsNumber = Podresponse.Result.First().GoodsNumber,
Creator = "bxadmin",
CreateTime = DateTime.Now,
Str1 = str1, //快递运单号
Str2 = str2, //商品品名
Str3 = Podresponse.Result.First().Str3, //交易金额
Str15 = Podresponse.Result.First().Str15, //实际快递费用
Str4 = str4, //发货联系人
Str5 = str5, //发货电话
Str6 = Podresponse.Result.First().Str6, //发货省
Str7 = Podresponse.Result.First().Str7, //发货市
Str8 = Podresponse.Result.First().Str8, //发货区
Str36 = str36, //发货详细地址
Str10 = str10, //收货联系人
Str11 = str11, //收货电话
Str12 = Podresponse.Result.First().Str12, //收货省
Str13 = Podresponse.Result.First().Str13, //收货市
Str14 = Podresponse.Result.First().Str14, //收货区
Str38 = str38, //收货详细地址
Str16 = Podresponse.Result.First().Str16, //投保
Str17 = Podresponse.Result.First().Str17, //podRequest.courier,//快递公司
Str18 = Podresponse.Result.First().Str18, //预估快递费用
Str19 = Podresponse.Result.First().Str19, //来源应用
Str37 = string.IsNullOrEmpty(str37) ? row["订单备注"].ToString().Trim() : str37, //备注信息
PODTypeID = 7,
PODTypeName = "出货运单",
TtlOrTplID = 26,
TtlOrTplName = "LTL",
Type = 1,//分单标识(默认为2)
Str20 = null,
};
#endregion ;
pp.Add(p);
}
}
}
var response = new BaiXingService().AddPods_BX(new AddPodsRequest()
{
Pods = pp
});
if (response.IsSuccess)
{
StringBuilder orderNumbers = new StringBuilder();
foreach (PodKey pk in response.Result)
{
orderNumbers.Append(pk.CustomerOrderNumber + ",");
}
results.Append("成功导入" + response.Result.Count() + "条，百姓运单号：" + orderNumbers.ToString());
}
else
{
results.Append("导入失败，请联系管理员.");
}
return(new { result = results.ToString(), IsSuccess = true }.ToJsonString());
}
}
return(new { result = "文件内容为空", IsSuccess = false }.ToJsonString());
}
return(new { result = "请选择文件", IsSuccess = false }.ToJsonString());
}
public string ExcelCheck(string customer)
{
string uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
string targetPath = Path.Combine(uploadFolderPath, "AMSTempFile");
string url = string.Empty, actualNameInServer = string.Empty, ext = string.Empty;
DateTime amsDate = new DateTime();
if (string.IsNullOrEmpty(targetPath) || !Path.IsPathRooted(targetPath))
{
return(new { msg = "程序出错！" }.ToJsonString());
}
if (Request.Files.Count > 0)
{
HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase;
if (hpf.ContentLength > 0)
{
if (!Directory.Exists(targetPath))
{
Directory.CreateDirectory(targetPath);
}
ext = Path.GetExtension(hpf.FileName);
if (!ext.ToLower().Equals(".xls") && !ext.ToLower().Equals(".xlsx"))
{
return(new { msg = "请选择Excel格式的文件" }.ToJsonString());
}
actualNameInServer = DateTime.Now.ToString("yyyyMMddHHmmss") + ext;
url = Path.Combine(targetPath, actualNameInServer);
hpf.SaveAs(url);
hpf.InputStream.Close();
Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(url);
DataSet ds = excelHelper.GetAllDataFromAllSheets();
excelHelper.Dispose();
MyFile.Delete(url);
AMSUploadService service = new AMSUploadService();
Response <IEnumerable <AMSUpload> > response = service.GetAMSUpload(new AddAMSUploadRequest()
{
amsUpload = null
});
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0 && response.Result.Count() > 0)
{
StringBuilder results = new StringBuilder();
StringBuilder ids = new StringBuilder();
string projectName, fileName;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
projectName = row[0].ToString().Trim().ToUpper();
fileName = row[1].ToString().Trim().ToUpper();
if (projectName == "AMS_永兴东润")
{
amsDate = Convert.ToDateTime(row[2].ToString());
fileName = amsDate.ToString("yyyy-MM-") + fileName;
}
bool isExist = false;
foreach (AMSUpload ams in response.Result)
{
if (projectName == ams.ProjectName.Trim().ToUpper() && fileName == ams.FileName.Trim().ToUpper())
{
ids.Append("'" + ams.ID.ToString() + "',");
isExist = true;
//if (projectName != "AMS_永兴东润") //AMS_永兴东润
//{
// break;
//}
}
}
if (!isExist)
{
results.Append("[" + row[0].ToString() + "]客户中[" + row[1].ToString() + "]运单号 未上传回单图片<br />");
}
}
if (ids.Length > 0)
{
service.UpdateAMSUploadStatus(new AddAMSUploadRequest()
{
Ids = ids.ToString()
});
}
if (results.Length <= 0)
{
results.Append("回单图片都已上传.");
}
return(new { result = results.ToString(), IsSuccess = true }.ToJsonString());
}
}
return(new { result = "文件内容为空", IsSuccess = false }.ToJsonString());
}
return(new { result = "请选择文件", IsSuccess = false }.ToJsonString());
}