/// <summary>
/// 获取需要提交到顺丰的运单
/// </summary>
/// <returns></returns>
public static List <string> GetNlPostWayBillNumberList()
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData("exec P_GetNlPostWayBillNumberList N'" + _NLPOSTShippingMethodID + "',N'" + SubmitStatus + "'");
var list = new List <string>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS获取需要提交到顺丰的运单总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName != "WayBillNumber")
{
continue;
}
if (!string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
{
list.Add(dt.Rows[i][j].ToString().Trim());
}
}
}
}
Log.Info("LMS获取需要提交到顺丰的运单完毕！");
return(list);
}
/// <summary>
/// 获取需要提交到顺E宝的运单
/// --已提交或者已收货
/// </summary>
/// <returns></returns>
public static List <string> GetLithuaniaWayBillNumberList()
{
var shippingMethodId = _LithuaniaShippingMethodID.Replace(",", "','");
string sql = @"SELECT WayBillNumber FROM dbo.WayBillInfos w
WHERE InShippingMethodID IN('" + shippingMethodId + @"')
AND Status IN(3,4)
AND w.IsHold=0
AND NOT EXISTS ( SELECT 1
FROM LithuaniaInfos n
WHERE w.WayBillNumber = n.WayBillNumber )";
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData(sql);
var list = new List <string>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS获取需要提交到顺E宝的运单总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName != "WayBillNumber")
{
continue;
}
if (!string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
{
list.Add(dt.Rows[i][j].ToString().Trim());
}
}
}
}
Log.Info("LMS获取需要提交到顺E宝的运单完毕！");
return(list);
}
/// <summary>
/// 获取欧洲专线平邮需要自定义跟踪信息的单号
/// </summary>
/// <returns></returns>
public static List <B2CForeCastLogs> GetB2CForeCastLogs()
{
Log.Info("开始获取欧洲专线平邮需要自定义跟踪信息的单号");
string sql = @"WITH kk AS
(
SELECT WayBillNumber,EventLocation,EventContent
FROM dbo.B2CForeCastLogs
WHERE IsOkJob=4
), ll AS
(
SELECT b.WayBillNumber,b.EventDate
FROM dbo.B2CForeCastLogs b
WHERE EXISTS(SELECT 1 FROM kk WHERE kk.WayBillNumber=b.WayBillNumber) AND EventCode=10
),hh AS
(
SELECT WayBillNumber
FROM B2CForeCastLogs d
WHERE EXISTS(SELECT 1 FROM kk WHERE kk.WayBillNumber=d.WayBillNumber) AND EventCode=11
)
SELECT kk.WayBillNumber,kk.EventLocation,ll.EventDate,kk.EventContent
FROM kk LEFT JOIN ll ON ll.WayBillNumber = kk.WayBillNumber
WHERE ll.EventDate IS NOT NULL AND NOT EXISTS(SELECT 1 FROM hh WHERE hh.WayBillNumber=kk.WayBillNumber)" ;
DbUtility dbUtility = new SqlDbUtility(_TrackCon);
var list = new List <B2CForeCastLogs>();
DataTable dt = dbUtility.ExecuteData(sql);
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("获取欧洲专线平邮需要自定义跟踪信息的单号记录总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
var v = new B2CForeCastLogs();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "WayBillNumber":
v.WayBillNumber = dt.Rows[i][j].ToString();
break;
case "EventLocation":
v.EventLocation = dt.Rows[i][j].ToString();
break;
case "EventDate":
v.EventDate = DateTime.Parse(dt.Rows[i][j].ToString());
break;
case "EventContent":
v.EventContent = dt.Rows[i][j].ToString();
break;
}
}
list.Add(v);
}
}
Log.Info("完成获取欧洲专线平邮需要自定义跟踪信息的单号");
return(list);
}
const int errortype = 2; //-运费接口错误
/// <summary>
/// 获取需要更新的运单
/// </summary>
/// <returns></returns>
public static List <VenderInfoPackageRequest> GetUpdatePriceWayBillList(string lastUpdateTime)
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData("exec P_GetBatchFreightCostWayBillNumberList N'" + _status + "',N'" + lastUpdateTime + "'");
var list = new List <VenderInfoPackageRequest>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS需要更新成本价运单总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
var v = new VenderInfoPackageRequest();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "VenderCode":
v.VenderCode = dt.Rows[i][j].ToString();
break;
case "CountryCode":
v.CountryCode = dt.Rows[i][j].ToString();
break;
case "WayBillNumber":
v.WayBillNumber = dt.Rows[i][j].ToString();
break;
case "PackageRequest":
if (string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
{
continue;
}
v.Packages = SerializeUtil.DeserializeFromXml <List <VenderPackageRequest> >(dt.Rows[i][j].ToString());
break;
case "OutShippingMethodID":
v.ShippingMethodId = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "GoodsTypeID":
v.ShippingTypeId = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "CustomerID":
v.CustomerId = Guid.Parse(dt.Rows[i][j].ToString());
break;
case "EnableTariffPrepay":
v.EnableTariffPrepay = bool.Parse(dt.Rows[i][j].ToString());
break;
}
}
list.Add(v);
}
}
Log.Info("LMS需要更新成本价运单获取完毕！");
return(list);
}
/// <summary>
/// 获取需要生成出账单的账单号
/// </summary>
/// <returns></returns>
public static List <ReceivingBill> GetCreateOutBillList()
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData("select ReceivingBillID,CustomerCode,CustomerName,ReceivingBillDate,ReceivingBillAuditor,BillStartTime,BillEndTime,ISNULL(Search,0) Search from ReceivingBills where Status=1");
var list = new List <ReceivingBill>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS需要生成账单的账单号总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
var v = new ReceivingBill();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "ReceivingBillID":
v.ReceivingBillID = dt.Rows[i][j].ToString();
break;
case "CustomerCode":
v.CustomerCode = dt.Rows[i][j].ToString();
break;
case "CustomerName":
v.CustomerName = dt.Rows[i][j].ToString();
break;
case "ReceivingBillDate":
v.ReceivingBillDate = dt.Rows[i][j].ToString();
break;
case "ReceivingBillAuditor":
v.ReceivingBillAuditor = dt.Rows[i][j].ToString();
break;
case "BillStartTime":
v.BillStartTime = dt.Rows[i][j].ToString();
break;
case "BillEndTime":
v.BillEndTime = dt.Rows[i][j].ToString();
break;
case "Search":
v.Search = Int32.Parse(dt.Rows[i][j].ToString());
break;
}
}
list.Add(v);
}
}
Log.Info("LMS需要生成账单的账单号获取完毕！");
return(list);
}
/// <summary>
/// 获取需要运行增加跟踪信息的总单号
/// </summary>
/// <returns></returns>
public static List <TotalPackageTraceInfoModel> GetTotalPackageTraceInfo()
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt =
dbUtility.ExecuteData(
@" select ID,TotalPackageNumber,TraceEventCode,TraceEventTime,TraceEventAddress,CreatedBy
from TotalPackageTraceInfos tp where IsJob=0 and TraceEventTime<=GETDATE()
AND EXISTS(SELECT 1 FROM TotalPackageInfos ti WHERE ti.TotalPackageNumber=tp.TotalPackageNumber AND ti.CreatedOn>'" + StartDate + "')");
var list = new List <TotalPackageTraceInfoModel>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS总包号编辑时间记录总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
var v = new TotalPackageTraceInfoModel();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "TotalPackageNumber":
v.TotalPackageNumber = dt.Rows[i][j].ToString();
break;
case "TraceEventCode":
v.TraceEventCode = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "TraceEventTime":
v.TraceEventTime = DateTime.Parse(dt.Rows[i][j].ToString());
break;
case "TraceEventAddress":
v.TraceEventAddress = dt.Rows[i][j].ToString();
break;
case "CreatedBy":
v.CreatedBy = dt.Rows[i][j].ToString();
break;
case "ID":
v.ID = Int32.Parse(dt.Rows[i][j].ToString());
break;
}
}
list.Add(v);
}
}
Log.Info("LMS总包号编辑时间记录获取完毕！");
return(list);
}
/// <summary>
/// 获取需要提交到B2C的运单号
/// </summary>
/// <returns></returns>
public static List <string> GetSubmitWayBillNumber()
{
var list = new List <string>();
try
{
string sql = @"SELECT WayBillNumber FROM dbo.B2CPreAlertLogs WHERE Status=1";
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData(sql);
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS获取需要提交到B2C的运单总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName != "WayBillNumber")
{
continue;
}
if (!string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
{
list.Add(dt.Rows[i][j].ToString().Trim());
}
}
}
}
Log.Info("LMS获取需要提交到B2C的运单完毕！");
}
catch (Exception ex)
{
Log.Exception(ex);
}
return(list);
}
/// <summary>
/// 根据出账单号获取账单信息
/// </summary>
/// <param name="receivingBillId"></param>
/// <returns></returns>
public static List <BillModel> GetExportReceivingBill(string receivingBillId)
{
Log.Info("LMS开始获取账单号为:{0}的账单信息".FormatWith(receivingBillId));
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData("exec P_ExportReceivingBill N'" + receivingBillId + "'");
var list = new List <BillModel>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS开始获取账单号为:{0}的账单信息总数是{1}".FormatWith(receivingBillId, dt.Rows.Count));
for (int i = 0; i < dt.Rows.Count; i++)
{
var v = new BillModel();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "ReceivingExpenseId":
v.ReceivingExpenseId = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "CustomerOrderNumber":
v.CustomerOrderNumber = dt.Rows[i][j].ToString();
break;
case "WayBillNumber":
v.WayBillNumber = dt.Rows[i][j].ToString();
break;
case "CreatedOn":
v.CreatedOn = DateTime.Parse(dt.Rows[i][j].ToString());
break;
case "InStorageCreatedOn":
v.InStorageCreatedOn = DateTime.Parse(dt.Rows[i][j].ToString());
break;
case "TrackingNumber":
v.TrackingNumber = dt.Rows[i][j].ToString();
break;
case "ChineseName":
v.ChineseName = dt.Rows[i][j].ToString();
break;
case "InShippingMethodName":
v.InShippingMethodName = dt.Rows[i][j].ToString();
break;
case "InShippingMethodId":
v.InShippingMethodId = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "InStorageID":
v.InStorageID = dt.Rows[i][j].ToString();
break;
case "SettleWeight":
v.SettleWeight = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "Weight":
v.Weight = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "CountNumber":
v.CountNumber = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "Freight":
v.Freight = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "FuelCharge":
v.FuelCharge = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "Register":
v.Register = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "Surcharge":
v.Surcharge = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "TariffPrepayFee":
v.TariffPrepayFee = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "SpecialFee":
v.SpecialFee = decimal.Parse(dt.Rows[i][j].ToString());
break;
case "RemoteAreaFee":
v.RemoteAreaFee = decimal.Parse(dt.Rows[i][j].ToString());
break;
}
}
list.Add(v);
}
}
Log.Info("LMS完成获取账单号为:{0}的账单信息".FormatWith(receivingBillId));
return(list);
}
/// <summary>
/// 查询该运单号的数据
/// </summary>
/// <param name="wayBillNumber"></param>
/// <returns></returns>
public static WayBillInfoModel GetWayBillInfoModel(string wayBillNumber)
{
Log.Info("开始获取运单号为{0}的数据".FormatWith(wayBillNumber));
var model = new WayBillInfoModel();
string sql =
@"SELECT w.WayBillNumber,w.CustomerOrderNumber, ISNULL(s.ShippingFirstName,'')+' '+ ISNULL(s.ShippingLastName,'') ConsigneeName,w.OutShippingMethodID ShippingMethodID
,ISNULL(s.ShippingCompany,'') ShippingCompany,s.ShippingAddress,s.ShippingCity,s.ShippingState,s.ShippingZip,s.ShippingPhone,c.ThreeLetterISOCode CountryCode,
ISNULL(w.Weight,0) Weight,ISNULL(w.Length,1) Length,ISNULL(w.Width,1) Width,ISNULL(w.Height,1) Height
FROM dbo.WayBillInfos w LEFT JOIN dbo.ShippingInfos s ON s.ShippingInfoID = w.ShippingInfoID
LEFT JOIN dbo.Countries c ON c.CountryCode = w.CountryCode
WHERE w.WayBillNumber='" + wayBillNumber + "'";
try
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "WayBillNumber":
model.WayBillNumber = dt.Rows[0][j].ToString();
break;
case "CustomerOrderNumber":
model.CustomerOrderNumber = dt.Rows[0][j].ToString();
break;
case "ConsigneeName":
model.ConsigneeName = dt.Rows[0][j].ToString();
break;
case "ShippingCompany":
model.CompanyName = dt.Rows[0][j].ToString();
break;
case "ShippingAddress":
model.Street = dt.Rows[0][j].ToString();
break;
case "ShippingCity":
model.CityOrTown = dt.Rows[0][j].ToString();
break;
case "ShippingState":
model.StateOrProvince = dt.Rows[0][j].ToString();
break;
case "ShippingZip":
model.ZIPCode = dt.Rows[0][j].ToString();
break;
case "ShippingPhone":
model.PhoneNumber = dt.Rows[0][j].ToString();
break;
case "CountryCode":
model.CountryCode = dt.Rows[0][j].ToString();
break;
case "Weight":
model.Weight = decimal.Parse(dt.Rows[0][j].ToString());
break;
case "Length":
model.Length = decimal.Parse(dt.Rows[0][j].ToString());
break;
case "Width":
model.Width = decimal.Parse(dt.Rows[0][j].ToString());
break;
case "Height":
model.Height = decimal.Parse(dt.Rows[0][j].ToString());
break;
case "ShippingMethodID":
model.ShippingMethodID = Int32.Parse(dt.Rows[0][j].ToString());
break;
//case "ApplicationInfos":
// model.ApplicationInfos = SerializeUtil.DeserializeFromXml<List<ApplicationInfoModel>>(dt.Rows[0][j].ToString());
// break;
}
}
sql =
@"SELECT ISNULL(a.Remark,'') SKUCode,a.ApplicationName SKUDescription,a.HSCode,
ISNULL(a.Qty,1) Quantity,ISNULL(a.UnitPrice,0)*ISNULL(a.Qty,1) Price,a.ProductUrl ImageUrl
FROM dbo.ApplicationInfos a
WHERE a.WayBillNumber='" + wayBillNumber + "' AND a.IsDelete=0";
DataTable adt = dbUtility.ExecuteData(sql);
if (adt != null || adt.Rows.Count > 0)
{
for (int i = 0; i < adt.Rows.Count; i++)
{
var app = new ApplicationInfoModel();
for (int j = 0; j < adt.Columns.Count; j++)
{
switch (adt.Columns[j].ColumnName)
{
case "SKUCode":
app.SKUCode = adt.Rows[i][j].ToString();
break;
case "SKUDescription":
app.SKUDescription = adt.Rows[i][j].ToString();
break;
case "HSCode":
app.HSCode = adt.Rows[i][j].ToString();
break;
case "Quantity":
app.Quantity = Int32.Parse(adt.Rows[i][j].ToString());
break;
case "Price":
app.Price = decimal.Parse(adt.Rows[i][j].ToString());
break;
case "ImageUrl":
app.ImageUrl = adt.Rows[i][j].ToString();
break;
}
}
model.ApplicationInfos.Add(app);
}
}
}
catch (Exception ex)
{
Log.Exception(ex);
}
Log.Info("完成获取运单号为{0}的数据".FormatWith(wayBillNumber));
return(model);
}
/// <summary>
/// 获取需要提交到顺丰的运单信息
/// </summary>
/// <param name="wayBillNumbers">运单号集合</param>
/// <returns></returns>
public static List <WayBillSfModel> GetWayBillSfModelList(List <string> wayBillNumbers)
{
if (!wayBillNumbers.Any())
{
return(null);
}
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData("exec P_GetNlPostWayBillNumberInfoList N'" + string.Join(",", wayBillNumbers) + "'");
var list = new List <WayBillSfModel>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS获取需要提交到顺丰的运单信息总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
var model = new WayBillSfModel();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "WayBillNumber":
model.WayBillNumber = dt.Rows[i][j].ToString();
break;
case "ShippingCompany":
model.ShippingCompany = dt.Rows[i][j].ToString();
break;
case "ShippingName":
model.ShippingName = dt.Rows[i][j].ToString();
break;
case "ShippingPhone":
model.ShippingPhone = dt.Rows[i][j].ToString();
break;
case "ShippingAddress":
model.ShippingAddress = dt.Rows[i][j].ToString();
break;
case "PackageNumber":
model.PackageNumber = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "CountryCode":
model.CountryCode = dt.Rows[i][j].ToString();
break;
case "ShippingZip":
model.ShippingZip = dt.Rows[i][j].ToString();
break;
case "ShippingState":
model.ShippingState = dt.Rows[i][j].ToString();
break;
case "ShippingCity":
model.ShippingCity = dt.Rows[i][j].ToString();
break;
case "IsReturn":
model.IsReturn = bool.Parse(dt.Rows[i][j].ToString());
break;
case "ApplicationInfo":
if (string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
{
continue;
}
model.ApplicationInfo = SerializeUtil.DeserializeFromXml <List <ApplicationInfoSfModel> >(dt.Rows[i][j].ToString());
break;
}
}
list.Add(model);
}
}
Log.Info("LMS获取需要提交到顺丰的运单信息完毕！");
return(list);
}
/// <summary>
/// 获取需要提交到顺E宝的运单信息
/// </summary>
/// <param name="wayBillNumbers">运单号集合</param>
/// <returns></returns>
public static List <SebModel> GetWayBillSfModelList(List <string> wayBillNumbers)
{
if (!wayBillNumbers.Any())
{
return(null);
}
string sql = @"SELECT w.WayBillNumber ,
s.ShippingCompany ,
ISNULL(s.ShippingFirstName,'') + ' ' + ISNULL(s.ShippingLastName,'') ShippingName ,
s.ShippingPhone ,
ISNULL(s.ShippingAddress,'') + ' ' + ISNULL(s.ShippingAddress1,'') + ' '
+ ISNULL(s.ShippingAddress2,'') ShippingAddress ,
ISNULL(c.PackageNumber,1) PackageNumber ,
w.CountryCode ,
s.ShippingZip ,
s.ShippingState ,
s.ShippingCity ,
w.InShippingMethodID ShippingMethodID ,
CONVERT(XML, ( SELECT a.ApplicationName ,
a.HSCode HsCode,
a.Qty ,
a.UnitWeight ,
a.UnitPrice
FROM dbo.ApplicationInfos a
WHERE a.WayBillNumber = w.WayBillNumber
AND a.CustomerOrderID = c.CustomerOrderID
AND a.IsDelete = 0
FOR
XML PATH('ApplicationInfoSfModel') ,
ROOT('ArrayOfApplicationInfoSfModel')
)) AS ApplicationInfo
FROM dbo.WayBillInfos w
LEFT JOIN dbo.ShippingInfos s ON w.ShippingInfoID = s.ShippingInfoID
LEFT JOIN dbo.CustomerOrderInfos c ON w.CustomerOrderID = c.CustomerOrderID
WHERE w.WayBillNumber IN('" + string.Join("','", wayBillNumbers) + "')";
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData(sql);
var list = new List <SebModel>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS获取需要提交到顺E宝的运单信息总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
var model = new SebModel();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "WayBillNumber":
model.WayBillNumber = dt.Rows[i][j].ToString();
break;
case "ShippingCompany":
model.ShippingCompany = dt.Rows[i][j].ToString();
break;
case "ShippingName":
model.ShippingName = dt.Rows[i][j].ToString();
break;
case "ShippingPhone":
model.ShippingPhone = dt.Rows[i][j].ToString();
break;
case "ShippingAddress":
model.ShippingAddress = dt.Rows[i][j].ToString();
break;
case "PackageNumber":
model.PackageNumber = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "CountryCode":
model.CountryCode = dt.Rows[i][j].ToString();
break;
case "ShippingZip":
model.ShippingZip = dt.Rows[i][j].ToString();
break;
case "ShippingState":
model.ShippingState = dt.Rows[i][j].ToString();
break;
case "ShippingCity":
model.ShippingCity = dt.Rows[i][j].ToString();
break;
case "ShippingMethodID":
model.ShippingMethodId = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "ApplicationInfo":
if (string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
{
continue;
}
model.ApplicationInfo = SerializeUtil.DeserializeFromXml <List <ApplicationInfoSfModel> >(dt.Rows[i][j].ToString());
break;
}
}
list.Add(model);
}
}
Log.Info("LMS获取需要提交到顺E宝的运单信息完毕！");
return(list);
}
/// <summary>
/// 获取需要更新收货运费的运单
/// </summary>
/// <returns></returns>
public static List <WayBillPriceModel> GetUpdatePriceWayBillList()
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
DataTable dt = dbUtility.ExecuteData(@"SELECT r.WayBillNumber,r.ReceivingExpenseID,w.EnableTariffPrepay,c.CustomerID,c.CustomerTypeID,w.GoodsTypeID,
w.CountryCode,w.InShippingMethodID,ISNULL(s.ShippingCity,'') ShippingCity,ISNULL(s.ShippingState,'') ShippingState,
ISNULL(s.ShippingZip,'') ShippingZip,CONVERT(XML, (SELECT Length, Width,Height,Weight
FROM WaybillPackageDetails PackageRequest
WHERE PackageRequest.WayBillNumber=w.WayBillNumber
FOR XML Path('PackageRequest'),ROOT('ArrayOfPackageRequest')))AS PackageRequest
FROM dbo.ReceivingExpenses r LEFT JOIN dbo.WayBillInfos w ON r.WayBillNumber=w.WayBillNumber
LEFT JOIN dbo.Customers c ON w.CustomerCode=c.CustomerCode
LEFT JOIN dbo.ShippingInfos s ON w.ShippingInfoID=s.ShippingInfoID
WHERE IsNoGet=0 AND DATEDIFF(DAY,w.CreatedOn,GETDATE())>=1
AND EXISTS(SELECT 1 FROM WaybillPackageDetails wp WHERE w.WayBillNumber=wp.WayBillNumber)");
var list = new List <WayBillPriceModel>();
if (dt != null && dt.Rows.Count > 0)
{
Log.Info("LMS收货需要更新运费运单总数是" + dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
var v = new WayBillPriceModel();
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Columns[j].ColumnName)
{
case "EnableTariffPrepay":
v.EnableTariffPrepay = bool.Parse(dt.Rows[i][j].ToString());
break;
case "CountryCode":
v.CountryCode = dt.Rows[i][j].ToString();
break;
case "WayBillNumber":
v.WayBillNumber = dt.Rows[i][j].ToString();
break;
case "CustomerID":
v.CustomerId = Guid.Parse(dt.Rows[i][j].ToString());
break;
//case "CustomerTypeID":
// v.CustomerTypeID = Int32.Parse(dt.Rows[i][j].ToString());
// break;
case "PackageRequest":
v.Packages = SerializeUtil.DeserializeFromXml <List <PackageRequest> >(dt.Rows[i][j].ToString());
break;
case "InShippingMethodID":
v.ShippingMethodId = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "GoodsTypeID":
v.ShippingTypeId = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "ReceivingExpenseID":
v.ReceivingExpenseID = Int32.Parse(dt.Rows[i][j].ToString());
break;
case "ShippingCity":
v.ShippingInfo.ShippingCity = dt.Rows[i][j].ToString();
break;
case "ShippingState":
v.ShippingInfo.ShippingState = dt.Rows[i][j].ToString();
break;
case "ShippingZip":
v.ShippingInfo.ShippingZip = dt.Rows[i][j].ToString();
break;
}
}
list.Add(v);
}
}
Log.Info("LMS收货需要更新运费运单获取完毕！");
return(list);
}