/// <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(); }