/// <summary>
/// 修改B2CPreAlertLogs
/// </summary>
/// <param name="model"></param>
public static void UpdateB2CPreAlertLogs(B2CPreAlertLog model)
{
string sql = "";
if (model.Status == 2)
{
sql = @"UPDATE dbo.B2CPreAlertLogs
SET ShippingMethod='" + model.ShippingMethod + "',PreAlertID='" + model.PreAlertID + @"',Status=2
,PreAlertBatchNo='" + model.PreAlertBatchNo + @"',LastUpdatedOn=GETDATE(),LastUpdatedBy='system'
WHERE WayBillNumber='" + model.WayBillNumber + "' AND PreAlertBatchNo IS NULL";
}
else if (model.Status == 3)
{
sql = @"UPDATE dbo.B2CPreAlertLogs
SET ShippingMethod='" + model.ShippingMethod + "',ErrorMsg=N'" + model.ErrorMsg.Replace("/r/n", "").StripXML() + "',ErrorCode='" + model.ErrorCode + "',ErrorDetails=N'" + model.ErrorDetails.Replace("/r/n", "").StripXML() + @"',Status=3
,PreAlertBatchNo='" + model.PreAlertBatchNo + @"',LastUpdatedOn=GETDATE(),LastUpdatedBy='system'
WHERE WayBillNumber='" + model.WayBillNumber + "'";
}
if (sql.Length > 0)
{
try
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
dbUtility.ExecuteNonQuery(sql);
}
catch (Exception ex)
{
Log.Exception(ex);
}
}
}
/// <summary>
/// 插入备份数据库
/// </summary>
/// <param name="bill"></param>
/// <param name="list"></param>
/// <returns></returns>
public static bool InsertBackUp(ReceivingBill bill, List <BillModel> list)
{
var result = false;
Log.Info("LMS开始插入备份数据库账单号为:{0}".FormatWith(bill.ReceivingBillID));
const string insertReceivingBills = @"INSERT INTO [LMS_DB_Financial].[dbo].[ReceivingBills]
([ReceivingBillID],[CustomerCode],[CustomerName]
,[ReceivingBillDate],[ReceivingBillAuditor],[BillStartTime],[BillEndTime]) VALUES({0},{1},{2},{3},{4},{5},{6})";
const string insertReceivingBillInfos = @"INSERT INTO [ReceivingBillInfos]
([WayBillNumber],[ReceivingBillID],[CustomerOrderNumber],[CustomerName]
,[CustomerCode],[ReceivingDate],[TrackingNumber],[CountryName]
,[ShippingMethodName],[SettleWeight],[Weight],[PackageNumber]
,[FeeDetail],[TotalFee])
VALUES({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13})";
const string xml = "<FeeDetails><Freight>{0}</Freight><FuelCharge>{1}</FuelCharge><Register>{2}</Register><Surcharge>{3}</Surcharge><TariffPrepayFee>{4}</TariffPrepayFee><SpecialFee>{5}</SpecialFee><RemoteAreaFee>{6}</RemoteAreaFee></FeeDetails>";
try
{
DbUtility dbUtility = new SqlDbUtility(_lmsfinCon);
using (var transaction = new TransactionScope())
{
dbUtility.ExecuteNonQuery(insertReceivingBills, bill.ReceivingBillID, bill.CustomerCode,
bill.CustomerName, bill.ReceivingBillDate, bill.ReceivingBillAuditor,
bill.BillStartTime, bill.BillEndTime);
list.ForEach(
p => dbUtility.ExecuteNonQuery(insertReceivingBillInfos, p.WayBillNumber,
bill.ReceivingBillID,
p.CustomerOrderNumber, bill.CustomerName, bill.CustomerCode,
p.InStorageCreatedOn, p.TrackingNumber, p.ChineseName,
p.InShippingMethodName, p.SettleWeight, p.Weight,
p.CountNumber,
xml.FormatWith(p.Freight, p.FuelCharge, p.Register,
p.Surcharge, p.TariffPrepayFee, p.SpecialFee, p.RemoteAreaFee),
p.Freight + p.FuelCharge + p.Register + p.Surcharge +
p.TariffPrepayFee + p.SpecialFee));
transaction.Complete();
}
result = true;
}
catch (Exception ex)
{
Log.Exception(ex);
}
Log.Info("LMS完成插入备份数据库账单号为:{0}".FormatWith(bill.ReceivingBillID));
return(result);
}
/// <summary>
/// 插入B2C预报记录表
/// </summary>
/// <param name="totalPackageNumber">总包号</param>
/// <param name="registeredShippingMethodId">欧洲专线挂号运输方式ID</param>
/// <param name="mailShippingMethodId">欧洲专线平邮运输方式ID</param>
public static void InsertB2CForeCastLogs(string totalPackageNumber, int registeredShippingMethodId,
int mailShippingMethodId)
{
Log.Info("LMS开始插入总包号为:{0} 的B2C预报记录".FormatWith(totalPackageNumber));
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
dbUtility.ExecuteNonQuery("exec P_TotalPackageNumberCreatedLog {0},{1},{2}", totalPackageNumber,
registeredShippingMethodId, mailShippingMethodId);
Log.Info("LMS完成插入总包号为:{0} 的B2C预报记录".FormatWith(totalPackageNumber));
}
/// <summary>
/// 当配置
/// </summary>
/// <param name="model"></param>
public static void InTotalPackageTraceInfo(TotalPackageTraceInfoModel model)
{
Log.Info("LMS开始插入总包号为:{0} 的总包号跟踪信息,事件码:{1}".FormatWith(model.TotalPackageNumber, model.TraceEventCode));
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
dbUtility.ExecuteNonQuery(@"INSERT INTO TotalPackageTraceInfos
([TotalPackageNumber],[TraceEventCode],[TraceEventTime],[TraceEventAddress]
,[CreatedBy],[LastUpdatedBy],[TraceEventContent])
VALUES({0},{1},{2},{3},{4},{5},{6})", model.TotalPackageNumber, model.TraceEventCode, model.TraceEventTime,
model.TraceEventAddress, model.CreatedBy, model.CreatedBy, "");
Log.Info("LMS完成插入总包号为:{0} 的总包号跟踪信息,事件码:{1}".FormatWith(model.TotalPackageNumber, model.TraceEventCode));
}
/// <summary>
/// 同步运输方式
/// </summary>
/// <param name="shippingMethodId"></param>
/// <param name="shippingMethodTypeId"></param>
/// <param name="enabled"></param>
public static void SynchronousShippingMethod(int shippingMethodId, int shippingMethodTypeId, bool enabled)
{
Log.Info(string.Format("开始同步运输方式ID：{0}", shippingMethodId));
try
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
dbUtility.ExecuteNonQuery("exec P_SynchronousShippingMethod {0},{1},{2}", shippingMethodId, shippingMethodTypeId, enabled?1:0);
}
catch (Exception ex)
{
Log.Exception(ex);
}
Log.Info(string.Format("完成同步运输方式ID：{0}", shippingMethodId));
}
/// <summary>
/// 修改运单表没有指定发货渠道的荷兰小包
/// </summary>
/// <returns></returns>
public static void UpdateOutShippingMethod()
{
Log.Info("开始指定顺丰荷兰小包出仓渠道");
string sql = @"UPDATE w
SET w.OutShippingMethodID='" + _NLPOSTShippingMethodID + @"',w.VenderCode='" + _NLPOSTVenderCode + @"',
w.LastUpdatedOn=GETDATE(),w.LastUpdatedBy='system'
FROM dbo.WayBillInfos w
WHERE w.VenderCode IS NULL AND OutShippingMethodID IS NULL
AND EXISTS( SELECT 1 FROM dbo.NetherlandsParcelResponses n WHERE w.WayBillNumber=n.WayBillNumber AND n.Status=2)" ;
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
dbUtility.ExecuteNonQuery(sql);
Log.Info("完成指定顺丰荷兰小包出仓渠道");
}
/// <summary>
/// 修改运单表没有指定发货渠道的俄罗斯
/// </summary>
public static void UpdateOutShippingMethod()
{
Log.Info("开始指定顺E宝出仓渠道");
string sql = @"UPDATE w
SET w.OutShippingMethodID=w.InShippingMethodID,w.VenderCode='" + _LithuaniaVenderCode + @"',
w.LastUpdatedOn=GETDATE(),w.LastUpdatedBy='system'
FROM dbo.WayBillInfos w
WHERE w.VenderCode IS NULL AND OutShippingMethodID IS NULL
AND EXISTS( SELECT 1 FROM dbo.LithuaniaInfos n WHERE w.WayBillNumber=n.WayBillNumber AND n.Status=2)" ;
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
dbUtility.ExecuteNonQuery(sql);
Log.Info("完成指定顺E宝出仓渠道");
}
/// <summary>
/// B2CForeCastLogs外部信息插入LMS的InTrackingLogInfos
/// </summary>
public static void TrackInfoInsertLms()
{
Log.Info("开始插入LMS的InTrackingLogInfos");
try
{
DbUtility dbUtility = new SqlDbUtility(TrackCon);
dbUtility.ExecuteNonQuery("P_B2CInsertLMSInTrackingLogInfos");
}
catch (Exception ee)
{
Log.Exception(ee);
}
Log.Info("完成插入LMS的InTrackingLogInfos");
}
/// <summary>
/// 插入内部跟踪信息
/// </summary>
/// <param name="model"></param>
public static bool InsertInTrackingLogInfo(TotalPackageTraceInfoModel model, string shippingMethodConfig)
{
var result = false;
Log.Info("LMS开始插入总包号为:{0} 的内部信息".FormatWith(model.TotalPackageNumber));
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
var obj = dbUtility.ExecuteScalar("exec P_TotalPackageNumberJob {0},{1}", model.ID, shippingMethodConfig);
if (obj != null && obj.ToString() == "1")
{
dbUtility.ExecuteNonQuery(
"UPDATE TotalPackageTraceInfos SET IsJob=1,LastUpdatedOn=GETDATE() WHERE ID={0}", model.ID);
result = true;
}
Log.Info("LMS完成插入总包号为:{0} 的内部信息".FormatWith(model.TotalPackageNumber));
return(result);
}
/// <summary>
/// 更新错误日记
/// </summary>
/// <param name="waybillnumber"></param>
/// <param name="errorbody"></param>
public static void UpdateJobErrorLog(string waybillnumber, string errorbody)
{
try
{
var iscorrect = 0;
if (string.IsNullOrWhiteSpace(errorbody))
{
iscorrect = 1;
}
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
dbUtility.ExecuteNonQuery("exec P_JobErrorLog {0},{1},{2},{3},{4},{5}", waybillnumber, jobtype,
errortype, errorbody ?? "", "system", iscorrect);
}
catch (Exception ex)
{
Log.Exception(ex);
}
}
/// <summary>
/// 修改已生成订单状态
/// </summary>
/// <param name="receivingBillId"></param>
/// <returns></returns>
public static bool UpdateReceivingBillStatus(string receivingBillId)
{
Log.Info("LMS开始修改账单号为：{0}出账单状态".FormatWith(receivingBillId));
var result = false;
try
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
int obj =
dbUtility.ExecuteNonQuery("update ReceivingBills set Status=2 where ReceivingBillID='" +
receivingBillId + "' and Status=1");
result = obj > 0;
}
catch (Exception ex)
{
Log.Exception(ex);
}
Log.Info("LMS完成修改账单号为：{0}出账单状态".FormatWith(receivingBillId));
return(result);
}
/// <summary>
/// 更新欧洲专线平邮自定义跟踪信息单号的状态为完成
/// </summary>
public static void UpdateCustomeIsOkJob()
{
Log.Info("开始更新欧洲专线平邮自定义跟踪信息单号的状态为完成");
string sql = @"WITH kk AS
(
SELECT WayBillNumber,IsOkJob,LastUpdatedOn
FROM dbo.B2CForeCastLogs
WHERE IsOkJob=4
),hh AS
(
SELECT WayBillNumber
FROM B2CForeCastLogs d
WHERE EXISTS(SELECT 1 FROM kk WHERE kk.WayBillNumber=d.WayBillNumber) AND EventCode=11
)
UPDATE kk
SET IsOkJob=1,LastUpdatedOn=GETDATE()
FROM kk
WHERE EXISTS(SELECT 1 FROM hh WHERE hh.WayBillNumber=kk.WayBillNumber)" ;
DbUtility dbUtility = new SqlDbUtility(_TrackCon);
dbUtility.ExecuteNonQuery(sql);
Log.Info("完成更新欧洲专线平邮自定义跟踪信息单号的状态为完成");
}
/// <summary>
/// 插入客户资金记录表
/// </summary>
/// <param name="bill"></param>
/// <param name="list"></param>
/// <returns></returns>
public static bool CreateCustomerAmountRecords(ReceivingBill bill, List <BillModel> list)
{
var result = false;
Log.Info("LMS开始插入客户资金记录客户为：{1}，账单号为:{0}".FormatWith(bill.ReceivingBillID, bill.CustomerCode));
try
{
DbUtility dbUtility = new SqlDbUtility(_lmsCon);
using (var transaction = new TransactionScope())
{
list.ForEach(p =>
{
object obj;
if (p.Freight > 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 2, 3, p.Freight,
"运单号：{0}扣运费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成运费扣费记录失败！".FormatWith(p.WayBillNumber));
}
}
else if (p.Freight < 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 3, 3, p.Freight * -1,
"运单号：{0}退运费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成运费退费记录失败！".FormatWith(p.WayBillNumber));
}
}
if (p.Register > 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 2, 4, p.Register,
"运单号：{0}扣挂号费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成挂号费扣费记录失败！".FormatWith(p.WayBillNumber));
}
}
else if (p.Register < 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 3, 4, p.Register * -1,
"运单号：{0}退挂号费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成挂号费退费记录失败！".FormatWith(p.WayBillNumber));
}
}
if (p.FuelCharge > 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 2, 5, p.FuelCharge,
"运单号：{0}扣燃油费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成燃油费扣费记录失败！".FormatWith(p.WayBillNumber));
}
}
else if (p.FuelCharge < 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 3, 5, p.FuelCharge * -1,
"运单号：{0}退燃油费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成燃油费退费记录失败！".FormatWith(p.WayBillNumber));
}
}
if (p.Surcharge + p.SpecialFee > 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 2, 2, p.Surcharge + p.SpecialFee,
"运单号：{0}扣附加费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成附加费扣费记录失败！".FormatWith(p.WayBillNumber));
}
}
else if (p.Surcharge + p.SpecialFee < 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 3, 2, (p.Surcharge + p.SpecialFee) * -1,
"运单号：{0}退附加费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成附加费退费记录失败！".FormatWith(p.WayBillNumber));
}
}
if (p.TariffPrepayFee > 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 2, 6, p.TariffPrepayFee,
"运单号：{0}扣关税预付服务费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成关税预付服务费扣费记录失败！".FormatWith(p.WayBillNumber));
}
}
else if (p.TariffPrepayFee < 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 3, 6, p.TariffPrepayFee * -1,
"运单号：{0}退关税预付服务费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成关税预付服务费退费记录失败！".FormatWith(p.WayBillNumber));
}
}
if (p.RemoteAreaFee > 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 2, 12, p.RemoteAreaFee,
"运单号：{0}扣偏远附加服务费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成偏远附加服务费扣费记录失败！".FormatWith(p.WayBillNumber));
}
}
else if (p.RemoteAreaFee < 0)
{
obj =
dbUtility.ExecuteScalar(
"Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
bill.CustomerCode,
p.WayBillNumber, p.InStorageID, 3, 12, p.RemoteAreaFee * -1,
"运单号：{0}退偏远附加服务费".FormatWith(p.WayBillNumber), "system");
if (obj.ToString() != "1")
{
throw new ArgumentException("该运单号\"{0}\"生成偏远附加服务费退费记录失败！".FormatWith(p.WayBillNumber));
}
}
//if (p.SpecialFee <= 0) return;
//obj =
// dbUtility.ExecuteScalar(
// "Exec P_JobCustomerAmountRecord {0},{1},{2},{3},{4},{5},{6},{7}",
// bill.CustomerCode,
// p.WayBillNumber, p.InStorageID, 2, 7, p.SpecialFee,
// "运单号：{0}扣特殊费".FormatWith(p.WayBillNumber), "system");
//if (obj.ToString() != "1")
//{
// throw new ArgumentException("该运单号\"{0}\"生成特殊费扣费记录失败！".FormatWith(p.WayBillNumber));
//}
});
dbUtility.ExecuteNonQuery("update ReceivingBills set Search=100 where ReceivingBillID='" +
bill.ReceivingBillID + "' and Status=1");//限制重复扣费
transaction.Complete();
}
result = true;
}
catch (Exception ex)
{
Log.Exception(ex);
}
Log.Info("LMS完成插入客户资金记录客户为：{1}，账单号为:{0}".FormatWith(bill.ReceivingBillID, bill.CustomerCode));
return(result);
}