/// <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);
}
public static void AfterCreateCustomerAmountRecords(ReceivingBill bill, List <BillModel> list)
{
ExeclCreateController.ExeclCreate(bill, list); //生成Execl
if (OutBillController.UpdateReceivingBillStatus(bill.ReceivingBillID))
{
Log.Info("账单号为:{0},修改出账单状态成功！".FormatWith(bill.ReceivingBillID));
Log.Info(OutBillController.InsertBackUp(bill, list)
? "账单号为:{0},插入备份数据库成功！".FormatWith(bill.ReceivingBillID)
: "账单号为:{0},插入备份数据库失败！".FormatWith(bill.ReceivingBillID));
}
else
{
Log.Error("账单号为:{0},修改出账单状态失败！".FormatWith(bill.ReceivingBillID));
}
}
/// <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>
/// 插入客户资金记录表
/// </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);
}
/// <summary>
/// 导入收款单据
/// </summary>
/// <param name="easSysNo"></param>
/// <param name="model"></param>
/// <param name="receivingType">收款单类型(5:商品收款单;10:服务收款单)</param>
/// <param name="customer">送货客户(商城客户:3003999997(如果为升舱订单 客服编号就要对应))</param>
/// <param name="flowIdentify"></param>
/// <param name="dataMd5"></param>
/// <param name="isAgain">是否开始同步</param>
/// <param name="description"></param>
/// <param name="enableEas">是否启用Eas</param>
/// <param name="isData">是否只取xml数据</param>
/// <returns>Result</returns>
/// <remarks>2013-9-25 杨浩 创建</remarks>
public static Result <string> ReceivingBillFacade(int easSysNo, List <ReceivingInfo> model, 收款单类型 receivingType, string customer, string description, string flowIdentify, string dataMd5 = null, bool isAgain = false, bool enableEas = true, bool isData = false)
{
var name = (receivingType == 收款单类型.退销售回款 ? "导入付款单" : "导入收款单");
var watch = new Stopwatch();
watch.Start();
var result = new Eas.ReceivingBillFacade.Result();
string sessionId = "";
#region 数据初始
var datajson = new ReceivingInfoWraper
{
Model = model,
Description = description,
Customer = customer,
ReceivingType = receivingType
}.ToJson();
var firstModel = model.FirstOrDefault();
var organizationCode = firstModel != null ? firstModel.OrganizationCode : string.Empty; //组织架构
var payeeAccount = firstModel != null ? firstModel.PayeeAccount : string.Empty; //收款科目
int warehouseSysNo = firstModel != null ? firstModel.WarehouseSysNo : 0; //仓库编号
decimal voucherAmount = model.Sum(q => q.Amount);
if (receivingType == 收款单类型.退销售回款)
{
voucherAmount = -voucherAmount;
}
var billHead = new Model.Receiving.BillHead
{
number = "SK" + Number,
company = "30", //品牌管理有限公司
creator = _userName,
createtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
lastUpdateUser = _userName,
lastUpdateTime = DateTime.Now.ToString(dateFormater),
bizdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
handler = _userName,
description = description,
auditor = _userName,
//recbilltype = 100, //销售回款
//paymenttype = "002", //赊销
fundtype = (receivingType == 收款单类型.商品收款单 || receivingType == 收款单类型.退销售回款) ? "100" : "101", //现金=100,银行=101,其他=102
rectype = (receivingType == 收款单类型.商品收款单) ? "100" : ((receivingType == 收款单类型.退销售回款) ? "102" : "999"), //收款类型 销售回款(应收系统)=100,,其他(出纳系统)=999
currency = "RMB",
actrecamt = 0, //0表头自动计算
actreclocamt = 0, //0表头自动计算
exchangerate = 1,
payeeaccount = payeeAccount, //收款科目
payeeaccountbank = firstModel != null ? firstModel.PayeeAccountBank : string.Empty, //收款账户，支付宝，网银需传入
settlementtype = firstModel != null ? firstModel.SettlementType : string.Empty, //结算方式 需hyt对应(对应)01:现金
biztype = string.Empty,
payeebank = string.Empty,
settlementnumber = string.Empty,
adminorgunit = GetOrganizeCode(organizationCode), //组织架构
person = firstModel != null ? firstModel.WarehouseNumber : string.Empty, //仓库编码
oppaccount = string.Empty,
costcenter = string.Empty,
oppbgitemnumber = string.Empty,
payertype = "00001", //客户
payernumber = customer, //商城客户
payerbank = string.Empty,
payeraccountbank = string.Empty,
sourcebillid = string.Empty,
sourcefunction = string.Empty,
auditdate = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"),
billstatus = "10", //保存=10,已提交=11,已审批=12,已收款=14,已付款=15,审批中=6,已审核=8
sourcebilltype = string.Empty,
sourcesystype = (receivingType == 收款单类型.商品收款单 || receivingType == 收款单类型.退销售回款) ? "100" : "103", //100:应收系统,103:出纳系统
sourcetype = (receivingType == 收款单类型.商品收款单 || receivingType == 收款单类型.退销售回款) ? "100" : "103", //100:应收系统,103:出纳系统
baseStatus = 14 //保存=10,已提交=11(提交即审核) 14=收款状态
};
var billEntries = model.Select((q, i) => new Model.Receiving.entry
{
seq = 1 + i,
recbilltype = (receivingType == 收款单类型.商品收款单) ? 100 : 0, //100:销售回款(应收系统需要)
amount = (receivingType == 收款单类型.退销售回款) ? -q.Amount : q.Amount,
localamt = (receivingType == 收款单类型.退销售回款) ? -q.Amount : q.Amount,
rebate = 0,
rebatelocamt = 0,
actualamt = (receivingType == 收款单类型.退销售回款) ? -q.Amount : q.Amount,
actuallocamt = (receivingType == 收款单类型.退销售回款) ? -q.Amount : q.Amount,
remark = q.Remark,
corebilltype = (receivingType == 收款单类型.商品收款单) ? 102 : 0, //102:销售出库单(应收系统需要)
corebillnumber = string.Empty,
corebillentryseq = string.Empty,
project = string.Empty,
tracknumber = string.Empty,
contractnum = string.Empty,
contractentryseq = string.Empty,
lockamt = 0, //结算状态为收款金额
//unlockamt = q.Amount, //未结算状态为收款金额
amountvc = 0,
localamtvc = 0,
//unvcamount = q.Amount, //未结算状态为收款金额
//unvclocamount = q.Amount, //未结算状态为收款金额
matchedamount = 0,
matchedamountloc = 0,
oppaccount = string.Empty,
oppbgitemname = string.Empty,
ck = (payeeAccount == EasConstant.PayeeAccount?EasConstant.HytWharehouse: q.WarehouseNumber), //仓库Eas编号
}).ToList();
var data = new ReceivingBill
{
billHead = billHead,
billEntries = billEntries,
thirdSysBillID = dataMd5 ?? Helper.MD5Encrypt(datajson),
checkDuplicate = "true"
};
//按照Eas格式，去掉xmlns和空格
string xml = Helper.XmlSerialize(data)
.Replace(xsi, "")
.Replace(xsd, "")
.Replace("utf-16", "UTF-8")
.Trim();
if (isData)
{
return(new Result <string>
{
Data = xml
});
}
#endregion
#region 调用外部Eas接口
#region 检查基础资料是否完备
string message = EasConstant.Information + ":";
bool isFull = true;
if (string.IsNullOrEmpty(billHead.payeeaccount))
{
message += " 收款科目为空";
isFull = false;
}
//仅在收款科目为1002开头时才是必录项
if (!string.IsNullOrEmpty(billHead.payeeaccount) && billHead.payeeaccount.StartsWith("1002"))
{
if (string.IsNullOrEmpty(billHead.payeeaccountbank))
{
message += " 收款账户为空";
isFull = false;
}
}
if (string.IsNullOrEmpty(billHead.adminorgunit))
{
message += " 部门为空";
isFull = false;
}
if (string.IsNullOrEmpty(billHead.person))
{
message += " 人员为空";
isFull = false;
}
#endregion
步状态 status;
if (enableEas && isAgain && isFull)
{
try
{
var context = ErpLogin();
sessionId = context.sessionId;
var client = new Eas.ReceivingBillFacade.WSReceivingBillFacadeSrvProxyService
{
Timeout = Settings.Default.Timeout,
SessionId = new SoapUnknownHeader {
Element = GetSessionHeader(sessionId)
}
};
if (sessionId != null)
{
result = client.importData(xml);
}
status = result.status ? 步状态.成功 : 步状态.失败;
}
catch (Exception e)
{
result.message = e.Message;
result.status = false;
status = 步状态.失败;
}
}
else
{
if (enableEas)
{
result.message = isFull ? Model.EasConstant.EAS_WAIT : message;
}
else
{
result.message = Model.EasConstant.EAS_MESSAGE_CLOSE;
}
result.status = false;
status = 步状态.等待同步;
}
#endregion
watch.Stop();
var resultData = new Result <string>
{
Data = (result.data != null ? result.data.ToString() : ""), //返回单据号
Status = result.status,
StatusCode = result.statusCode,
Message = result.message
};
//记录日志
var sysno = WriteLog(datajson, resultData, watch, name, 接口类型.收款单据导入, warehouseSysNo, description, flowIdentify, voucherAmount, status, isAgain);
easSysNo = sysno == 0 ? easSysNo : sysno;
//临时记录 检查是否重复
var content = " 订单编号：" + description + " 会话ID：" + sessionId + " 外部编号：" + data.thirdSysBillID + " 请求时间:" + billHead.lastUpdateTime +
" 返回结果：" + (result.status ? result.data : result.message) + " 返回状态：" + result.status + "," + result.statusCode + " ElapseTime:" + watch.ElapsedMilliseconds;
WriteLog(easSysNo, content, billHead.createtime, isAgain);
return(resultData);
}
/// <summary>
/// 根据收货出账单号生成Execl
/// </summary>
/// <param name="bill"></param>
/// <param name="list"></param>
public static void ExeclCreate(ReceivingBill bill, List <BillModel> list)
{
Log.Info("LMS开始生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
var groupShipping = list.GroupBy(p => p.InShippingMethodName).Select(g => new
{
ShippingMethodName = g.Key,
TotalFee = g.Sum(p => p.Freight + p.FuelCharge +
p.Register + p.Surcharge +
p.TariffPrepayFee +
p.SpecialFee + p.RemoteAreaFee)
}).ToList();
using (
var file = new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate/FinancialCreditBill.xls", FileMode.Open,
FileAccess.Read))
{
IWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheetNew = hssfworkbook.CloneSheet(0);
sheetNew.GetRow(1).GetCell(0).SetCellValue("客户名称：{0}".FormatWith(bill.CustomerName));
sheetNew.GetRow(1).GetCell(1).SetCellValue("账单结算期间：{0}至{1}".FormatWith(DateTime.Parse(bill.BillStartTime).ToString("yyyy-MM-dd"), DateTime.Parse(bill.BillEndTime).ToString("yyyy-MM-dd")));
IRow sourceRow = sheetNew.GetRow(2);
int startRow = 2;
foreach (var t in groupShipping)
{
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow row = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref row);
row.GetCell(0).SetCellValue(t.ShippingMethodName);
row.GetCell(1).SetCellValue("总费用：{0}".FormatWith(t.TotalFee.ToString("F2")));
}
startRow++;
sheetNew.ShiftRows(startRow, sheetNew.LastRowNum, 1, true, true);
IRow srow = sheetNew.CreateRow(startRow);
CopyRow(sourceRow, ref srow);
srow.GetCell(0).SetCellValue("结算人：{0}".FormatWith(bill.ReceivingBillAuditor));
startRow = startRow + 3;
int startR = startRow;
foreach (var b in list)
{
IRow row = sheetNew.CreateRow(startRow);
row.CreateCell(0).SetCellValue(b.WayBillNumber);
row.CreateCell(1).SetCellValue(b.CustomerOrderNumber ?? "");
row.CreateCell(2).SetCellValue(b.CreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(3).SetCellValue(b.InStorageCreatedOn.ToString("yyyy-MM-dd HH:mm"));
row.CreateCell(4).SetCellValue(b.TrackingNumber ?? "");
row.CreateCell(5).SetCellValue(b.ChineseName);
row.CreateCell(6).SetCellValue(b.InShippingMethodName);
row.CreateCell(7).SetCellValue(b.SettleWeight.ToString("F4"));
row.CreateCell(8).SetCellValue(b.Weight.ToString("F4"));
row.CreateCell(9).SetCellValue(b.CountNumber);
row.CreateCell(10).SetCellValue(double.Parse(b.Freight.ToString("F2")));
row.CreateCell(11).SetCellValue(double.Parse(b.Register.ToString("F2")));
row.CreateCell(12).SetCellValue(double.Parse(b.FuelCharge.ToString("F2")));
row.CreateCell(13).SetCellValue(double.Parse(b.Surcharge.ToString("F2")));
row.CreateCell(14).SetCellValue(double.Parse(b.TariffPrepayFee.ToString("F2")));
row.CreateCell(15).SetCellValue(double.Parse(b.SpecialFee.ToString("F2")));
row.CreateCell(16).SetCellValue(double.Parse(b.RemoteAreaFee.ToString("F2")));
startRow++;
row.CreateCell(17).SetCellFormula("sum(K" + startRow + ":Q" + startRow + ")");
}
IRow totalRow = sheetNew.CreateRow(startRow);
for (int i = 0; i < 18; i++)
{
totalRow.CreateCell(i).SetCellValue("");
}
startR++;
totalRow.Cells[9].SetCellValue("各项计费小计：");
totalRow.Cells[10].SetCellFormula("sum(K" + startR + ":K" + startRow + ")");
totalRow.Cells[11].SetCellFormula("sum(L" + startR + ":L" + startRow + ")");
totalRow.Cells[12].SetCellFormula("sum(M" + startR + ":M" + startRow + ")");
totalRow.Cells[13].SetCellFormula("sum(N" + startR + ":N" + startRow + ")");
totalRow.Cells[14].SetCellFormula("sum(O" + startR + ":O" + startRow + ")");
totalRow.Cells[15].SetCellFormula("sum(P" + startR + ":P" + startRow + ")");
totalRow.Cells[16].SetCellFormula("sum(Q" + startR + ":Q" + startRow + ")");
totalRow.Cells[17].SetCellFormula("sum(R" + startR + ":R" + startRow + ")");
hssfworkbook.SetSheetName(hssfworkbook.GetSheetIndex(sheetNew), bill.ReceivingBillID);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
if (File.Exists(Path.Combine(path, bill.ReceivingBillID + ".xls")))
{
File.Delete(Path.Combine(path, bill.ReceivingBillID + ".xls"));
}
var savefile = new FileStream(Path.Combine(path, bill.ReceivingBillID + ".xls"), FileMode.Create);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.Write(savefile);
file.Close();
}
Log.Info("LMS完成生成账单号为：{0}execl表格".FormatWith(bill.ReceivingBillID));
}
