/// <summary>获取对账的实际总金额
/// </summary>
/// <param name="checkId"></param>
/// <returns></returns>
public IList <ExceptionReckoningInfo> GetTotalMoney(Guid checkId)
{
var parm = new SqlParameter(PARM_CHECKID, SqlDbType.UniqueIdentifier)
{
Value = checkId
};
IList <ExceptionReckoningInfo> exceptionReckoningList = new List <ExceptionReckoningInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, SQL_SELECT_TOTAL, parm))
{
while (rdr.Read())
{
var reckoning = new ExceptionReckoningInfo
{
FilialeId = rdr["FilialeId"] == DBNull.Value ? Guid.Empty : new Guid(rdr["FilialeId"].ToString()),
FilialeName = rdr["FilialeName"] == DBNull.Value ? String.Empty : rdr["FilialeName"].ToString(),
DiffMoney = 0,
SumConfirmMoney = rdr["SumConfirmMoney"] == DBNull.Value ? 0 : Convert.ToDecimal(rdr["SumConfirmMoney"]),
SumFinanceConfirmMoney = rdr["SumFinanceConfirmMoney"] == DBNull.Value ? 0 : Convert.ToDecimal(rdr["SumFinanceConfirmMoney"]),
IsOut = rdr["IsOut"] != DBNull.Value && Convert.ToBoolean(rdr["IsOut"])
};
exceptionReckoningList.Add(reckoning);
}
}
return(exceptionReckoningList);
}
/// <summary>获取公司异常总调账金额
/// </summary>
/// <param name="checkId"></param>
/// <param name="isReceivable">是否应收，(true：应收；false：应付) </param>
/// <returns></returns>
public IList <ExceptionReckoningInfo> GetAccountReceivableByFilialeId(Guid checkId, Boolean isReceivable)
{
var SQL = string.Format(@"
WITH TEMP AS
(
SELECT ISNULL(SUM(DiffMoney),0) DiffMoney,FilialeId FROM lmShop_TempReckoning AS T
WHERE
CheckId=@CheckId
AND IsOut=1
AND DiffMoney{0}0
GROUP BY FilialeId
)
SELECT T.FilialeId,F.Name AS FilialeName,T.DiffMoney FROM TEMP AS T
LEFT JOIN Filiale F on T.FilialeId=F.ID", isReceivable ? ">" : "<");
var parm = new SqlParameter(PARM_CHECKID, SqlDbType.UniqueIdentifier)
{
Value = checkId
};
IList <ExceptionReckoningInfo> exceptionReckoningList = new List <ExceptionReckoningInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, SQL, parm))
{
while (rdr.Read())
{
var reckoning = new ExceptionReckoningInfo
{
FilialeId = rdr["FilialeId"] == DBNull.Value ? Guid.Empty : new Guid(rdr["FilialeId"].ToString()),
FilialeName = rdr["FilialeName"] == DBNull.Value ? String.Empty : rdr["FilialeName"].ToString(),
DiffMoney = rdr["DiffMoney"] == DBNull.Value ? 0 : Convert.ToDecimal(rdr["DiffMoney"]),
IsOut = true
};
exceptionReckoningList.Add(reckoning);
}
}
return(exceptionReckoningList);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
// ReSharper disable once FunctionComplexityOverflow
protected void OnFinishClick(object sender, EventArgs e)
{
if (!_submitController.Enabled)
{
RAM.Alert("请不要重复提交!");
return;
}
if (rgCheckHistory.SelectedItems.Count == 0)
{
RAM.Alert("请选择待完成帐!");
return;
}
var checkList = new List <CheckDataRecordInfo>();
var dictionary = new Dictionary <Guid, int>();
foreach (GridDataItem item in rgCheckHistory.SelectedItems)
{
var checkId = new Guid(item.GetDataKeyValue("CheckId").ToString());
var info = _checkDataManagerWrite.GetCheckDataInfoById(checkId);
if (info != null)
{
switch (info.CheckDataState)
{
case (int)CheckDataState.Finished:
checkList.Add(info);
dictionary.Add(info.CheckId, info.CheckType);
break;
default:
RAM.Alert("系统提示：选项中存在已完成或处理中对账记录！");
return;
}
}
}
var companyList = checkList.GroupBy(act => act.CheckCompanyId).Select(act => act.Key).ToList();
if (companyList.Count > 1)
{
RAM.Alert("系统提示：多条记录对账只能在同快递公司之间！");
return;
}
IList <CompanyFundReceiptInfo> receiptList = new List <CompanyFundReceiptInfo>();
var personnelInfo = CurrentSession.Personnel.Get();
IList <ExceptionReckoningInfo> newList = new BindingList <ExceptionReckoningInfo>();
#region [计算当前选择的对账记录需要生成的各公司的收付款记录]
//计算当前选择的对账记录需要生成的各公司的收付款记录
foreach (var key in dictionary.Keys)
{
var list = _checkDataManagerWrite.GetTotalMoney(key); //实际对账金额 (实收金额累计)
foreach (var item in list)
{
decimal totalMoney = 0;
decimal serverMoney = 0;
decimal exceptionMoney;
if (dictionary[key] == (int)ReckoningCheckType.Carriage)
{
totalMoney -= item.SumFinanceConfirmMoney;
serverMoney -= item.SumConfirmMoney;
exceptionMoney = item.SumConfirmMoney - item.SumFinanceConfirmMoney;
}
else
{
totalMoney += item.SumFinanceConfirmMoney;
serverMoney += item.SumConfirmMoney;
exceptionMoney = item.SumFinanceConfirmMoney - item.SumConfirmMoney;
}
if (totalMoney == 0)
{
continue;
}
//公司收付款
if (item.IsOut)
{
var tempInfo = newList.FirstOrDefault(ent => ent.FilialeId == item.FilialeId && ent.IsOut);
if (tempInfo != null)
{
/*------公司账务合并------||-------------------------收款----------------------------||-------------------------付款----------------------------*/
//zal 2015-11-23
if (CKB_Merger.Checked || (totalMoney > 0 && tempInfo.SumFinanceConfirmMoney > 0) || (totalMoney < 0 && tempInfo.SumFinanceConfirmMoney < 0))
{
tempInfo.FilialeId = item.FilialeId;
tempInfo.FilialeName = item.FilialeName;
tempInfo.DiffMoney += exceptionMoney;
tempInfo.SumConfirmMoney += serverMoney;
tempInfo.SumFinanceConfirmMoney += totalMoney;
tempInfo.IsOut = true;
}
else
{
var tempInfo1 = new ExceptionReckoningInfo
{
FilialeId = item.FilialeId,
FilialeName = item.FilialeName,
DiffMoney = exceptionMoney,
SumConfirmMoney = serverMoney,
SumFinanceConfirmMoney = totalMoney,
IsOut = true
};
newList.Add(tempInfo1);
}
}
else
{
var tempInfo1 = new ExceptionReckoningInfo
{
FilialeId = item.FilialeId,
FilialeName = item.FilialeName,
DiffMoney = exceptionMoney,
SumConfirmMoney = serverMoney,
SumFinanceConfirmMoney = totalMoney,
IsOut = true
};
newList.Add(tempInfo1);
}
}
else
{
var tempInfo = newList.FirstOrDefault(ent => ent.FilialeId == _reckoningElseFilialeid && ent.IsOut == false);
if (tempInfo != null)
{
/*------公司账务合并------||-------------------------收款----------------------------||-------------------------付款----------------------------*/
//zal 2015-11-23
if (CKB_Merger.Checked || (totalMoney > 0 && tempInfo.SumFinanceConfirmMoney > 0) || (totalMoney < 0 && tempInfo.SumFinanceConfirmMoney < 0))
{
tempInfo.FilialeId = _reckoningElseFilialeid;
tempInfo.FilialeName = "ERP";
tempInfo.DiffMoney += exceptionMoney;
tempInfo.SumConfirmMoney += serverMoney;
tempInfo.SumFinanceConfirmMoney += totalMoney;
tempInfo.IsOut = false;
}
else
{
var tempInfo1 = new ExceptionReckoningInfo
{
FilialeId = _reckoningElseFilialeid,
FilialeName = "ERP",
DiffMoney = exceptionMoney,
SumConfirmMoney = serverMoney,
SumFinanceConfirmMoney = totalMoney,
IsOut = false
};
newList.Add(tempInfo1);
}
}
else
{
var tempInfo1 = new ExceptionReckoningInfo
{
FilialeId = _reckoningElseFilialeid,
FilialeName = "ERP",
DiffMoney = exceptionMoney,
SumConfirmMoney = serverMoney,
SumFinanceConfirmMoney = totalMoney,
IsOut = false
};
newList.Add(tempInfo1);
}
}
}
}
#endregion
#region [生成个公司收付款单据]
//生成个公司收付款单据
foreach (var info in newList)
{
if (info.IsOut)
{
//生成往来单位收付款
var receipt = new CompanyFundReceiptInfo
{
ReceiptNo = _codeManager.GetCode(info.SumFinanceConfirmMoney > 0 ? CodeType.GT : CodeType.PY),
ReceiptType = Convert.ToInt32(info.SumFinanceConfirmMoney > 0 ? CompanyFundReceiptType.Receive : CompanyFundReceiptType.Payment),
ApplyDateTime = DateTime.Now,
ApplicantID = personnelInfo.PersonnelId,
PurchaseOrderNo = string.Empty,
CompanyID = companyList[0]
};
string msg = "{0} 应{1}总额：{2},我方总额：{3}，异常总额：{4}"; //确认金额，系统金额，异常金额
msg = string.Format(msg, info.FilialeName, info.SumFinanceConfirmMoney > 0 ? "收" : "付", Math.Abs(info.SumFinanceConfirmMoney).ToString("f2"),
Math.Abs(info.SumConfirmMoney).ToString("f2"), Math.Abs(info.DiffMoney).ToString("f2"));
receipt.HasInvoice = true;
receipt.SettleStartDate = Convert.ToDateTime("1999-09-09");
receipt.SettleEndDate = Convert.ToDateTime("1999-09-09");
receipt.ExpectBalance = Math.Abs(info.SumFinanceConfirmMoney);
receipt.RealityBalance = Math.Abs(info.SumFinanceConfirmMoney);
receipt.DiscountMoney = 0;
receipt.DiscountCaption = string.Empty;
receipt.OtherDiscountCaption = "[快递对账生成的收付款]";
receipt.FilialeId = info.FilialeId;
receipt.ReceiptStatus = info.SumFinanceConfirmMoney < 0
? Convert.ToInt32(CompanyFundReceiptState.WaitAuditing)
: Convert.ToInt32(CompanyFundReceiptState.WaitInvoice);
receipt.StockOrderNos = string.Empty;
receipt.Remark = WebControl.RetrunUserAndTime(msg);
receipt.IsOut = true;
receipt.PaymentDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM"));
receiptList.Add(receipt);
}
else
{
var receipt = new CompanyFundReceiptInfo
{
ReceiptNo = _codeManager.GetCode(info.SumFinanceConfirmMoney > 0 ? CodeType.GT : CodeType.PY),
ReceiptType = Convert.ToInt32(info.SumFinanceConfirmMoney > 0 ? CompanyFundReceiptType.Receive : CompanyFundReceiptType.Payment),
ApplyDateTime = DateTime.Now,
ApplicantID = personnelInfo.PersonnelId,
PurchaseOrderNo = string.Empty,
CompanyID = companyList[0]
};
string msg = "{0} 应{1}总额：{2},我方总额：{3}，异常总额：{4}"; //确认金额，系统金额，异常金额
msg = string.Format(msg, "其他公司", info.SumFinanceConfirmMoney > 0 ? "收" : "付", Math.Abs(info.SumFinanceConfirmMoney).ToString("f2"), Math.Abs(info.SumConfirmMoney).ToString("f2"), Math.Abs(info.DiffMoney).ToString("f2"));
receipt.HasInvoice = false;
receipt.SettleStartDate = Convert.ToDateTime("1999-09-09");
receipt.SettleEndDate = Convert.ToDateTime("1999-09-09");
receipt.ExpectBalance = Math.Abs(info.SumFinanceConfirmMoney);
receipt.RealityBalance = Math.Abs(info.SumFinanceConfirmMoney);
receipt.DiscountMoney = 0;
receipt.DiscountCaption = string.Empty;
receipt.OtherDiscountCaption = "[快递对账生成的收付款]";
receipt.FilialeId = _reckoningElseFilialeid;
receipt.ReceiptStatus = info.SumFinanceConfirmMoney < 0
? Convert.ToInt32(CompanyFundReceiptState.WaitAuditing)
: Convert.ToInt32(CompanyFundReceiptState.Audited);
receipt.StockOrderNos = string.Empty;
receipt.Remark = WebControl.RetrunUserAndTime(msg);
receipt.IsOut = false;
receipt.PaymentDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM"));
receiptList.Add(receipt);
}
}
#endregion
#region [最初处理方式暂注释]
//foreach (var key in dictionary.Keys)
//{
// //decimal money = checkDataManager.GetTotalMoney(key);
// //decimal server = checkDataManager.GetServerMoney(key);
// var list = checkDataManager.GetTotalMoney(key); //实际对账金额 (实收金额累计)
// IList<ExceptionReckoningInfo> filialeExceptionReckoningInfoList = new BindingList<ExceptionReckoningInfo>();
// IList<ExceptionReckoningInfo> elseExceptionReckoningInfoList = new BindingList<ExceptionReckoningInfo>();
// foreach (var item in list)
// {
// if (item.IsOut)
// filialeExceptionReckoningInfoList.Add(item);
// else
// elseExceptionReckoningInfoList.Add(item);
// }
// //生成公司的往来单位收付款
// foreach (var item in filialeExceptionReckoningInfoList)
// {
// decimal totalMoney = 0;
// decimal serverMoney = 0;
// decimal exceptionMoney;
// if (dictionary[key] == (int)ReckoningCheckType.Carriage)
// {
// totalMoney -= item.SumFinanceConfirmMoney;
// serverMoney -= item.SumConfirmMoney;
// exceptionMoney = item.SumConfirmMoney - item.SumFinanceConfirmMoney;
// }
// else
// {
// totalMoney += item.SumFinanceConfirmMoney;
// serverMoney += item.SumConfirmMoney;
// exceptionMoney = item.SumFinanceConfirmMoney - item.SumConfirmMoney;
// }
// if (totalMoney == 0) continue;
// //生成往来单位收付款
// var receipt = new CompanyFundReceiptInfo
// {
// ReceiptNo = codeManager.GetCode(totalMoney > 0 ? CodeType.GT : CodeType.PY),
// ReceiptType = Convert.ToInt32(totalMoney > 0 ? CompanyFundReceiptType.Receive : CompanyFundReceiptType.Payment),
// ApplyDateTime = DateTime.Now,
// ApplicantID = personnelInfo.PersonnelId,
// PurchaseOrderNo = string.Empty,
// CompanyID = companyList[0]
// };
// string msg = "{0} 应{1}总额：{2},我方总额：{3}，异常总额：{4}"; //确认金额，系统金额，异常金额
// msg = string.Format(msg, item.FilialeName, totalMoney > 0 ? "收" : "付", Math.Abs(totalMoney).ToString("f2"),
// Math.Abs(serverMoney).ToString("f2"), Math.Abs(exceptionMoney).ToString("f2"));
// receipt.HasInvoice = item.IsOut;
// receipt.SettleStartDate = Convert.ToDateTime("1999-09-09");
// receipt.SettleEndDate = Convert.ToDateTime("1999-09-09");
// receipt.ExpectBalance = Math.Abs(totalMoney);
// receipt.RealityBalance = Math.Abs(totalMoney);
// receipt.DiscountMoney = 0;
// receipt.DiscountCaption = string.Empty;
// receipt.OtherDiscountCaption = "[快递对账生成的收付款]";
// receipt.FilialeId = item.FilialeId;
// receipt.ReceiptStatus = totalMoney < 0
// ? Convert.ToInt32(CompanyFundReceiptState.WaitAuditing)
// : Convert.ToInt32(CompanyFundReceiptState.WaitInvoice);
// receipt.StockOrderNos = string.Empty;
// receipt.Remark = WebControl.RetrunUserAndTime(msg);
// receipt.IsOut = item.IsOut;
// receiptList.Add(receipt);
// }
// if (elseExceptionReckoningInfoList.Count > 0)
// {
// decimal totalMoney = 0;
// decimal serverMoney = 0;
// decimal exceptionMoney;
// var sumFinanceConfirmMoney = elseExceptionReckoningInfoList.Sum(ent => ent.SumFinanceConfirmMoney);
// var sumConfirmMoney = elseExceptionReckoningInfoList.Sum(ent => ent.SumConfirmMoney);
// if (dictionary[key] == (int)ReckoningCheckType.Carriage)
// {
// totalMoney -= sumFinanceConfirmMoney;
// serverMoney -= sumConfirmMoney;
// exceptionMoney = sumConfirmMoney - sumFinanceConfirmMoney;
// }
// else
// {
// totalMoney += sumFinanceConfirmMoney;
// serverMoney += sumConfirmMoney;
// exceptionMoney = sumFinanceConfirmMoney - sumConfirmMoney;
// }
// if (totalMoney == 0) continue;
// //生成往来单位收付款
// var receipt = new CompanyFundReceiptInfo
// {
// ReceiptNo = codeManager.GetCode(totalMoney > 0 ? CodeType.GT : CodeType.PY),
// ReceiptType = Convert.ToInt32(totalMoney > 0 ? CompanyFundReceiptType.Receive : CompanyFundReceiptType.Payment),
// ApplyDateTime = DateTime.Now,
// ApplicantID = personnelInfo.PersonnelId,
// PurchaseOrderNo = string.Empty,
// CompanyID = companyList[0]
// };
// string msg = "{0} 应{1}总额：{2},我方总额：{3}，异常总额：{4}"; //确认金额，系统金额，异常金额
// msg = string.Format(msg, "其他公司", totalMoney > 0 ? "收" : "付", Math.Abs(totalMoney).ToString("f2"), Math.Abs(serverMoney).ToString("f2"), Math.Abs(exceptionMoney).ToString("f2"));
// receipt.HasInvoice = false;
// receipt.SettleStartDate = Convert.ToDateTime("1999-09-09");
// receipt.SettleEndDate = Convert.ToDateTime("1999-09-09");
// receipt.ExpectBalance = Math.Abs(totalMoney);
// receipt.RealityBalance = Math.Abs(totalMoney);
// receipt.DiscountMoney = 0;
// receipt.DiscountCaption = string.Empty;
// receipt.OtherDiscountCaption = "[快递对账生成的收付款]";
// receipt.FilialeId = _reckoningElseFilialeid;
// receipt.ReceiptStatus = totalMoney < 0
// ? Convert.ToInt32(CompanyFundReceiptState.WaitAuditing)
// : Convert.ToInt32(CompanyFundReceiptState.Audited);
// receipt.StockOrderNos = string.Empty;
// receipt.Remark = WebControl.RetrunUserAndTime(msg);
// receipt.IsOut = false;
// receiptList.Add(receipt);
// }
//}
#endregion
#region 添加往来账，修改往来账
try
{
using (var scop = new TransactionScope())
{
if (receiptList.Count > 0)
{
ICompanyFundReceipt companyFundReceipt = new DAL.Implement.Inventory.CompanyFundReceipt(GlobalConfig.DB.FromType.Write);
foreach (var item in receiptList)
{
bool isInsert = companyFundReceipt.Insert(item);
var info = companyFundReceipt.GetFundReceiptInfoByReceiptNo(item.ReceiptNo);
if (isInsert && info.ReceiptID != Guid.Empty)
{
WebControl.AddOperationLog(personnelInfo.PersonnelId, personnelInfo.RealName,
info.ReceiptID, info.ReceiptNo, item.RealityBalance > 0
? OperationPoint.CurrentReceivedPayment.FillBill.GetBusinessInfo()
: OperationPoint.CurrentReceivedPayment.FillPayment.GetBusinessInfo(), string.Empty);
WebControl.AddOperationLog(personnelInfo.PersonnelId, personnelInfo.RealName, info.ReceiptID, info.ReceiptNo, OperationPoint.CurrentReceivedPayment.Remarks.GetBusinessInfo(), string.Empty);
}
}
}
var mediumReckoning = MediumReckoning.WriteInstance;
foreach (var key in dictionary.Keys)
{
_checkDataManagerWrite.UpdateState(key, (int)CheckDataState.Checked);
mediumReckoning.DeleteTempReckoning(key);
}
scop.Complete();
}
_submitController.Submit();
}
catch (Exception ex)
{
RAM.Alert("往来账处理异常：" + ex.Message);
return;
}
//if (!string.IsNullOrWhiteSpace(receiptNo))
//{
// RAM.ResponseScripts.Add(string.Format("return ShowCheckHistoryForm('{0}')", receiptNo));
//}
//else
//{
RAM.Alert("往来账收付款生成完毕!");
// }
#endregion
rgCheckHistory.Rebind();
}
