public void Push() { var confirmDate = DateTime.Now.Date; var settlementID = string.Empty; var diffValue = 0m; var costChangeValue = 0m; var taxRate = 0m; var clearValue = 0m; var taxValue = 0m; var userInfo = FormulaHelper.GetUserInfo(); var sql = string.Format(@"select Detail.*,SupplierContract.ProjectInfo,SupplierContract.TaxRate from ( select * from S_EP_SubContractSettlement_Detail where S_EP_SubContractSettlementID='{0}' ) Detail left join S_SP_SupplierContract SupplierContract on Detail.SubContract=SupplierContract.ID order by SortIndex desc ", ModelDic.GetValue("ID")); var dt = DB.ExecuteDataTable(sql); var list = FormulaHelper.DataTableToListDic(dt); foreach (var item in list) { costChangeValue = Convert.ToDecimal(item.GetValue("CostChangeValue")); #region 计算【税金】【去税金额】 taxRate = string.IsNullOrEmpty(item.GetValue("TaxRate")) ? 0m : Convert.ToDecimal(item.GetValue("TaxRate")); //税率 taxValue = costChangeValue * taxRate / (1 + taxRate); //税金 clearValue = costChangeValue / (1 + taxRate); //去税金额 costChangeValue = Math.Round(costChangeValue, 2); taxValue = Math.Round(taxValue, 2); clearValue = Math.Round(clearValue, 2); #endregion #region 核算成本 sql = string.Format(@"select CBSNode.*,CostUnit.ID as CostUnitID from (select * from S_EP_CBSNode where ID='{0}') CBSNode left join S_EP_CostUnit CostUnit on CBSNode.ID=CostUnit.CBSNodeID ", item.GetValue("ProjectInfo")); dt = DB.ExecuteDataTable(sql); if (dt.Rows.Count == 0) { throw new Formula.Exceptions.BusinessValidationException("没有找到合同对应的CBS节点,无法结算!"); } var unitCBSNode = FormulaHelper.DataRowToDic(dt.Rows[0]); var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", unitCBSNode.GetValue("CBSInfoID")); if (cbsInfoDic == null) { throw new Formula.Exceptions.BusinessValidationException("没有找到对应的核算项目,无法结算!"); } var costState = "Finish"; var costDetailDic = new Dictionary <string, object>(); costDetailDic.SetValue("Name", "采购分包费"); costDetailDic.SetValue("Code", unitCBSNode.GetValue("Code")); costDetailDic.SetValue("SubjectCode", unitCBSNode.GetValue("Code")); costDetailDic.SetValue("CBSFullCode", unitCBSNode.GetValue("FullCode")); costDetailDic.SetValue("CBSNodeID", unitCBSNode.GetValue("ID")); costDetailDic.SetValue("CBSNodeFullID", unitCBSNode.GetValue("FullID")); costDetailDic.SetValue("CostType", SubjectType.SubContractCost.ToString()); costDetailDic.SetValue("CBSInfoID", unitCBSNode.GetValue("CBSInfoID")); costDetailDic.SetValue("CostUnitID", unitCBSNode.GetValue("CostUnitID")); costDetailDic.SetValue("RelateID", item.GetValue("ID")); costDetailDic.SetValue("CostDate", confirmDate); costDetailDic.SetValue("BelongYear", confirmDate.Year); costDetailDic.SetValue("BelongMonth", confirmDate.Month); costDetailDic.SetValue("BelongQuarter", (confirmDate.Month + 2) / 3); costDetailDic.SetValue("TotalPrice", costChangeValue); costDetailDic.SetValue("TaxRate", taxRate); costDetailDic.SetValue("TaxValue", taxValue); costDetailDic.SetValue("ClearValue", clearValue); costDetailDic.SetValue("BelongDept", unitCBSNode.GetValue("ChargerDept")); costDetailDic.SetValue("BelongDeptName", unitCBSNode.GetValue("ChargerDeptName")); costDetailDic.SetValue("BelongUser", unitCBSNode.GetValue("ChargerUser")); costDetailDic.SetValue("BelongUserName", unitCBSNode.GetValue("ChargerUserName")); costDetailDic.SetValue("State", costState); costDetailDic.SetValue("Status", costState); costDetailDic.SetValue("CreateUser", userInfo.UserID); costDetailDic.SetValue("CreateUserName", userInfo.UserName); costDetailDic.SetValue("CreateDate", DateTime.Now); costDetailDic.InsertDB(this.DB, "S_EP_CostInfo"); var cbsInfo = new S_EP_CBSInfo(cbsInfoDic); cbsInfo.SummaryCostValue(); #endregion sql = string.Format(@"update S_EP_SubContractSettlement_Detail set Status='End',BelongDate='{1}',CBSInfoID='{2}',CBSNodeID='{3}' where ID='{0}' ", item.GetValue("ID"), confirmDate.ToString("yyyy-MM-dd"), unitCBSNode.GetValue("CBSInfoID"), unitCBSNode.GetValue("ID")); DB.ExecuteNonQuery(sql); } }
public void Push() { if (!String.IsNullOrEmpty(this.ModelDic.GetValue("ProjectInfo"))) { #region 项目相关报销直接进入项目成本 //根据项目报销,一定是获取的核算项目下的CBS节点 string detailSql = @"select S_EP_CBSNode.SubjectCode, S_EP_ReimbursementApply_Details.*, S_EP_CostUnit.Code as CostUnitCode, S_EP_CBSNode.ID as CBSNodeID, S_EP_CBSNode.Code as CBSNodeCode, S_EP_CBSNode.FullCode as CBSNodeFullCode, S_EP_CBSNode.FullID as CBSNodeFullID, S_EP_CBSNode.CBSInfoID, ExpenseType,SubjectType from S_EP_ReimbursementApply_Details inner join S_EP_CBSNode on S_EP_CBSnode.ID = S_EP_ReimbursementApply_Details.SubjectDetailID inner join S_EP_CostUnit on S_EP_CostUnit.ID = S_EP_ReimbursementApply_Details.CostUnit where S_EP_ReimbursementApplyID = '" + this.ModelDic.GetValue("ID") + "'"; var detailDt = this.DB.ExecuteDataTable(detailSql); var detailDicList = FormulaHelper.DataTableToListDic(detailDt); var user = FormulaHelper.GetUserInfo(); var cbsInfoList = new List <Dictionary <string, object> >(); var dt = this.DB.ExecuteDataTable(" SELECT * FROM S_EP_CostInfo with(nolock) WHERE 1<>1 "); foreach (var detailDic in detailDicList) { if (!cbsInfoList.Exists(c => c.GetValue("ID") == detailDic.GetValue("CBSInfoID"))) { var cbsInfo = this.GetDataDicByID("S_EP_CBSInfo", detailDic.GetValue("CBSInfoID")); if (cbsInfo == null) { throw new Formula.Exceptions.BusinessValidationException("没有找到核算项目信息,无法完成报销"); } cbsInfoList.Add(cbsInfo); } #region var row = dt.NewRow(); row["ID"] = FormulaHelper.CreateGuid(); row["Name"] = detailDic.GetValue("SubjectDetailName"); row["Code"] = detailDic.GetValue("CBSNodeCode"); row["ExpenseType"] = this.ModelDic.GetValue("ReimbursementType"); row["CostType"] = SubjectType.DirectCost.ToString(); row["CBSInfoID"] = detailDic.GetValue("CBSInfoID"); row["CostUnitID"] = detailDic.GetValue("CostUnit"); row["SubjectCode"] = detailDic.GetValue("SubjectCode"); row["CBSFullCode"] = detailDic.GetValue("CBSNodeFullCode"); row["CBSNodeID"] = detailDic.GetValue("CBSNodeID"); row["CBSNodeFullID"] = detailDic.GetValue("CBSNodeFullID"); row["RelateID"] = detailDic.GetValue("ID"); row["CostDate"] = DateTime.Now; row["BelongYear"] = DateTime.Now.Year; row["BelongMonth"] = DateTime.Now.Month; row["BelongQuarter"] = (DateTime.Now.Month - 1) / 3 + 1; row["TotalPrice"] = detailDic.GetValue("ApplyValue"); row["BelongDept"] = detailDic.GetValue("ChargerDept"); row["BelongDeptName"] = detailDic.GetValue("ChargerDeptName"); row["BelongUser"] = ModelDic.GetValue("ChargerUser"); row["BelongUserName"] = ModelDic.GetValue("ChargerUserName"); row["UserID"] = this.ModelDic.GetValue("ApplyUser"); row["UserName"] = this.ModelDic.GetValue("ApplyUserName"); row["UserDept"] = this.ModelDic.GetValue("ApplyDept"); row["UserDeptName"] = this.ModelDic.GetValue("ApplyDeptName"); row["State"] = "Finish"; row["Status"] = "Finish"; row["CreateUser"] = user.UserID; row["CreateUserName"] = user.UserName; row["CreateDate"] = DateTime.Now; row["ModifyUser"] = user.UserID; row["ModifyUserName"] = user.UserName; row["ModifyDate"] = DateTime.Now; #endregion dt.Rows.Add(row); } this.DB.BulkInsertToDB(dt, "S_EP_CostInfo"); foreach (var item in cbsInfoList) { var cbsInfo = new S_EP_CBSInfo(item); cbsInfo.SummaryCostValue(); } #endregion } else { //非项目类报销进入间接费用表中 string detailSql = string.Format(@"select Detail.*,ReimbursementApply.ApplyDate, ReimbursementApply.ApplyUser,ReimbursementApply.ApplyUserName,ReimbursementApply.ApplyDept,ReimbursementApply.ApplyDeptName from (select * from S_EP_ReimbursementApply_Details where S_EP_ReimbursementApplyID = '{0}') Detail left join S_EP_ReimbursementApply ReimbursementApply on Detail.S_EP_ReimbursementApplyID=ReimbursementApply.ID ", this.ModelDic.GetValue("ID")); var detailDt = this.DB.ExecuteDataTable(detailSql); var detailDicList = FormulaHelper.DataTableToListDic(detailDt); var user = FormulaHelper.GetUserInfo(); var applyDate = Convert.ToDateTime(detailDicList[0].GetValue("ApplyDate")); var dic = new Dictionary <string, object>(); var sqlInsert = string.Empty; foreach (var detailDic in detailDicList) { dic.SetValue("CreateDate", DateTime.Now.ToString("yyyy-MM-dd")); dic.SetValue("CreateUserID", user.UserID); dic.SetValue("CreateUser", user.UserName); dic.SetValue("RelateID", detailDic.GetValue("ID")); dic.SetValue("ApplyDate", detailDic.GetValue("ApplyDate")); dic.SetValue("ApplyValue", detailDic.GetValue("ApplyValue")); dic.SetValue("Subject", detailDic.GetValue("SubjectID")); dic.SetValue("SubjectName", detailDic.GetValue("SubjectName")); dic.SetValue("ChargerUser", detailDic.GetValue("ApplyUser")); dic.SetValue("ChargerUserName", detailDic.GetValue("ApplyUserName")); dic.SetValue("ChargerDept", this.ModelDic.GetValue("ApplyDept")); dic.SetValue("ChargerDeptName", this.ModelDic.GetValue("ApplyDeptName")); dic.SetValue("BelongYear", applyDate.Year); dic.SetValue("BelongQuarter", (applyDate.Month + 2) / 3); dic.SetValue("BelongMonth", applyDate.Month); dic.SetValue("Source", "Reimbursement"); sqlInsert += dic.CreateInsertSql(this.DB, "S_EP_IndirectExpenses", FormulaHelper.CreateGuid()); } this.DB.ExecuteNonQuery(sqlInsert); } }