public JsonResult DeleteCostInfo(string listJson)
{
var dicList = JsonHelper.ToList(listJson);
string delSql = "";
foreach (var dic in dicList)
{
var dateTime = Convert.ToDateTime(string.Format("{0}-{1}-01", dic.GetValue("BelongYear"), dic.GetValue("BelongMonth")));
CostFO.ValidatePeriodIsClosed(dateTime);
delSql += string.Format(" delete S_EP_CostInfo where ID = '{0}' ", dic.GetValue("ID"));
}
this.SQLDB.ExecuteNonQuery(delSql);
var distinctCBSInfoList = dicList.Select(a => a.GetValue("CBSInfoID")).Distinct();
var sqlCBSInfo = "select * from S_EP_CBSInfo where ID ='{0}' ";
foreach (var cbsInfoID in distinctCBSInfoList)
{
var cbsDt = SQLDB.ExecuteDataTable(string.Format(sqlCBSInfo, cbsInfoID));
if (cbsDt.Rows.Count > 0)
{
var cbsInfo = new S_EP_CBSInfo(FormulaHelper.DataRowToDic(cbsDt.Rows[0]));
cbsInfo.SummaryCostValue();
}
}
return(Json("true"));
}
public JsonResult SettlementRevert(string id)
{
var sql = string.Format(@"select top 1 * from S_EP_SubContractSettlement order by CreateDate desc ", "");
var dt = SQLDB.ExecuteDataTable(sql);
if (dt.Rows.Count == 0)
{
throw new Formula.Exceptions.BusinessValidationException("无结算单，请确认！");
}
var dicLast = FormulaHelper.DataRowToDic(dt.Rows[0]);
CostFO.ValidatePeriodIsClosed(Convert.ToDateTime(dicLast.GetValue("SettlementDate")));
if (dicLast.GetValue("FlowPhase") == "Start")
{
sql = string.Format(@"delete from S_EP_SubContractSettlement where ID='{0}' ", id);
SQLDB.ExecuteNonQuery(sql);
return(Json(""));
}
if (dicLast.GetValue("FlowPhase") == "Processing")
{
throw new Formula.Exceptions.BusinessValidationException("此结算单在【流程中】，无法撤销！");
}
sql = string.Format(@"select * from S_EP_SubContractSettlement_Detail where S_EP_SubContractSettlementID='{0}' ", id);
dt = SQLDB.ExecuteDataTable(sql);
var settlementDetail = FormulaHelper.DataTableToListDic(dt);
var cbsInfoIDs = new List <string>();
var revertID = FormulaHelper.CreateGuid();
dicLast.SetValue("RevertReason", string.Empty);
var sqlUpdate = dicLast.CreateInsertSql(SQLDB, "S_EP_SubContractSettlementRevert", revertID);
sqlUpdate += string.Format(@"delete from S_EP_SubContractSettlement where ID='{0}' ", id);
foreach (var item in settlementDetail)
{
cbsInfoIDs.Add(item.GetValue("CBSInfoID"));
item.SetValue("S_EP_SubContractSettlementRevertID", revertID);
sqlUpdate += item.CreateInsertSql(SQLDB, "S_EP_SubContractSettlementRevert_Detail", FormulaHelper.CreateGuid());
sqlUpdate += string.Format(@"delete from S_EP_CostInfo where RelateID='{0}' ", item.GetValue("ID"));
}
SQLDB.ExecuteNonQuery(sqlUpdate);
foreach (var cbsInfoID in cbsInfoIDs.Distinct())
{
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", cbsInfoID);
if (cbsInfoDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的核算项目，无法撤销！");
}
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
}
return(Json(""));
}
public void Push()
{
//判断是否是最后确认节点
var dt = this.DB.ExecuteDataTable(string.Format("select top 1 ID,CBSInfoID,ConfirmDate from S_EP_InvoiceConfirm where InvoiceID='{0}' order by ID desc ", this.ModelDic.GetValue("InvoiceID")));
if (dt.Rows.Count == 0)
{
throw new Formula.Exceptions.BusinessValidationException("无确认信息，无需撤销！");
}
var dicConfirm = FormulaHelper.DataRowToDic(dt.Rows[0]);
if (this.ModelDic.GetValue("InvoiceConfirmID") != dicConfirm.GetValue("ID"))
{
throw new Formula.Exceptions.BusinessValidationException("只能逐步撤销节点");
}
CostFO.ValidatePeriodIsClosed(Convert.ToDateTime(dicConfirm.GetValue("ConfirmDate")));
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", dicConfirm.GetValue("CBSInfoID"));
if (cbsInfoDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的核算项目，无法撤销！");
}
string sql = string.Format(@"delete from S_EP_InvoiceConfirm where ID='{0}' ", this.ModelDic.GetValue("InvoiceConfirmID"));
sql += string.Format(@"delete from S_EP_CostInfo where RelateID='{0}' ", this.ModelDic.GetValue("InvoiceConfirmID"));
this.DB.ExecuteNonQuery(sql);
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
}
protected override void AfterSave(Dictionary <string, string> dic, S_UI_Form formInfo, bool isNew)
{
var sqlCBSInfo = "select * from S_EP_CBSInfo where ID ='{0}' ";
var cbsDt = SQLDB.ExecuteDataTable(string.Format(sqlCBSInfo, dic["CBSInfoID"]));
if (cbsDt.Rows.Count > 0)
{
var cbsInfo = new S_EP_CBSInfo(FormulaHelper.DataRowToDic(cbsDt.Rows[0]));
cbsInfo.SummaryCostValue();
}
}
public void Revert()
{
var costUnitDic = this.GetDataDicByID("S_EP_CostUnit", this.ModelDic["CostUnitInfo"].ToString());
if (costUnitDic == null)
{
return;
}
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", costUnitDic.GetValue("CBSInfoID"));
if (cbsInfoDic == null)
{
return;
}
this.DB.ExecuteNonQuery(String.Format("delete from S_EP_CostInfo where RelateID='{0}'", this.ID));
this.DB.ExecuteNonQuery(String.Format("delete from S_EP_WorkHourUser where ID='{0}'", this.ID));
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
}
public void Push()
{
#region 判断节点是否是最后确认节点
var sql = string.Format(@"select top 1 ID,CBSInfoID,ConfirmDate,FlowPhase as ConfirmFlowPhase from S_EP_SupplierContractConfirm
where SubContractDetailID='{0}' order by ID desc ", this.ModelDic.GetValue("SubContractDetailID"));
var dt = this.DB.ExecuteDataTable(sql);
if (dt.Rows.Count == 0)
{
throw new Formula.Exceptions.BusinessValidationException("要撤销的节点不存在！");
}
var dicConfirm = FormulaHelper.DataRowToDic(dt.Rows[0]);
if (this.ModelDic.GetValue("SubContractConfirmID") != dicConfirm.GetValue("ID"))
{
throw new Formula.Exceptions.BusinessValidationException("只能逐步撤销节点");
}
#endregion
CostFO.ValidatePeriodIsClosed(Convert.ToDateTime(dicConfirm.GetValue("ConfirmDate")));
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", dicConfirm.GetValue("CBSInfoID"));
if (cbsInfoDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的核算项目，无法撤销！");
}
sql = string.Format(@"delete from S_EP_SupplierContractConfirm where ID='{0}' ", this.ModelDic.GetValue("SubContractConfirmID"));
sql += string.Format(@"delete from S_EP_CostInfo where RelateID='{0}' ", this.ModelDic.GetValue("SubContractConfirmID"));
sql += string.Format(@"update S_SP_SupplierContract_ContractSplit
set MinContractValue=(select ISNULL(sum(CurrentValue),0) from S_EP_SupplierContractConfirm where SubContractDetailID='{0}')
where ID='{0}' ", this.ModelDic.GetValue("SubContractDetailID"));
this.DB.ExecuteNonQuery(sql);
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
}
private void ToCostInfo(Dictionary <string, string> entity)
{
if (string.IsNullOrEmpty(entity.GetValue("PaymentDate")))
{
throw new Formula.Exceptions.BusinessValidationException("请填写付款日期");
}
CostFO.ValidatePeriodIsClosed(Convert.ToDateTime(entity.GetValue("PaymentDate")));
var sql = string.Format(@"select PaymentCostInfo.ID,
PaymentCostInfo.CostUnitID as CostUnit,
ISNULL(PaymentCostInfo.CostValue,0) as CostValue,
Payment.PaymentDate,
Payment.PaymentUser,
Payment.PaymentUserName,
isnull(SupplierContract.TaxRate,0) as TaxRate
from (select * from S_SP_Payment where ID='{0}') as Payment
inner join S_SP_Payment_CostInfo PaymentCostInfo on Payment.ID=PaymentCostInfo.S_SP_PaymentID
left join S_SP_SupplierContract SupplierContract on Payment.Contract = SupplierContract.ID ", entity.GetValue("ID"));
var dicList = FormulaHelper.DataTableToListDic(MarketSQLDB.ExecuteDataTable(sql));
foreach (var dic in dicList)
{
var dt = MarketSQLDB.ExecuteDataTable(string.Format(@"select * from S_EP_CostUnit where ID='{0}' ", dic.GetValue("CostUnit")));
if (dt.Rows.Count == 0)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的成本单元，无法确认成本");
}
var unitDic = FormulaHelper.DataRowToDic(dt.Rows[0]);
dt = MarketSQLDB.ExecuteDataTable(string.Format(@"select * from S_EP_CBSNode where ID='{0}' ", unitDic.GetValue("CBSNodeID")));
if (dt.Rows.Count == 0)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的CBS节点，无法确认成本");
}
var unitCBSNode = FormulaHelper.DataRowToDic(dt.Rows[0]);
dt = MarketSQLDB.ExecuteDataTable(string.Format(@"select * from S_EP_CBSInfo where ID='{0}' ", unitDic.GetValue("CBSInfoID")));
if (dt.Rows.Count == 0)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的核算项目，无法确认成本");
}
var cbsInfoDic = FormulaHelper.DataRowToDic(dt.Rows[0]);
var costValue = string.IsNullOrEmpty(dic.GetValue("CostValue")) ? 0m : Convert.ToDecimal(dic.GetValue("CostValue"));
var taxRate = string.IsNullOrEmpty(dic.GetValue("TaxRate")) ? 0m : Convert.ToDecimal(dic.GetValue("TaxRate"));
var taxValue = costValue / (1 + taxRate) * taxRate;
var clearCostValue = costValue - taxValue;
var costDetailDic = new Dictionary <string, object>();
var subjectDt = this.MarketSQLDB.ExecuteDataTable(String.Format(@"select * from S_EP_CBSNode with(nolock)
where FullID like '{0}%' order by FullID", unitCBSNode.GetValue("FullID")));
var subjectNode = subjectDt.AsEnumerable().FirstOrDefault(c => c["SubjectType"] != null && c["SubjectType"] != DBNull.Value &&
c["SubjectType"].ToString() == SubjectType.SubContractCost.ToString());
if (subjectNode == null)
{
throw new BusinessException("未找到SubjectType为【" + SubjectType.SubContractCost.ToString() + "】的节点");
}
var costState = "Finish";
costDetailDic.SetValue("Name", subjectNode["Name"]);
costDetailDic.SetValue("CBSFullCode", subjectNode["FullCode"]);
costDetailDic.SetValue("CBSNodeID", subjectNode["ID"]);
costDetailDic.SetValue("CBSNodeFullID", subjectNode["FullID"]);
if (String.IsNullOrEmpty(costDetailDic.GetValue("Name")))
{
costDetailDic.SetValue("Name", "采购分包费");
}
costDetailDic.SetValue("Code", subjectNode["Code"]);
if (String.IsNullOrEmpty(costDetailDic.GetValue("Code")))
{
costDetailDic.SetValue("Code", unitCBSNode.GetValue("Code"));
}
costDetailDic.SetValue("CostType", SubjectType.SubContractCost.ToString());
costDetailDic.SetValue("CBSInfoID", unitDic.GetValue("CBSInfoID"));
costDetailDic.SetValue("CostUnitID", unitDic.GetValue("ID"));
costDetailDic.SetValue("SubjectCode", subjectNode["SubjectCode"]);
costDetailDic.SetValue("RelateID", dic.GetValue("ID"));
var costDate = String.IsNullOrEmpty(dic.GetValue("PaymentDate")) ? DateTime.Now : Convert.ToDateTime(dic.GetValue("PaymentDate"));
costDetailDic.SetValue("CostDate", costDate);
costDetailDic.SetValue("BelongYear", costDate.Year);
costDetailDic.SetValue("BelongMonth", costDate.Month);
costDetailDic.SetValue("BelongQuarter", (costDate.Month - 1) / 3 + 1);
costDetailDic.SetValue("TotalPrice", costValue);
costDetailDic.SetValue("TaxRate", taxRate);
costDetailDic.SetValue("TaxValue", taxValue);
costDetailDic.SetValue("ClearValue", clearCostValue);
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", dic.GetValue("PaymentUser"));
costDetailDic.SetValue("CreateUserName", dic.GetValue("PaymentUserName"));
costDetailDic.SetValue("CreateDate", DateTime.Now);
sql = string.Format(@"delete from S_EP_CostInfo where RelateID='{0}' ", dic.GetValue("ID"));
MarketSQLDB.ExecuteNonQuery(sql);
costDetailDic.InsertDB(this.MarketSQLDB, "S_EP_CostInfo");
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
}
}
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);
}
}
public void Push()
{
var dateTime = String.IsNullOrEmpty(this.ModelDic.GetValue("ConfirmDate")) ? DateTime.Now : Convert.ToDateTime(this.ModelDic.GetValue("ConfirmDate"));
CostFO.ValidatePeriodIsClosed(dateTime);
var taskEntity = this.GetDataDicByID("S_EP_SubContractTask_CostUnitDetail", this.ModelDic.GetValue("TaskDetailID"));
var unitDic = this.GetDataDicByID("S_EP_CostUnit", this.ModelDic.GetValue("CostUnitID"));
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", unitDic.GetValue("CBSInfoID"));
if (taskEntity == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的分包外委任务，无法确认外委进度");
}
if (unitDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的成本单元，无法确认外委进度");
}
var unitCBSNode = this.GetDataDicByID("S_EP_CBSNode", unitDic.GetValue("CBSNodeID"), ConnEnum.Market, true);
if (unitCBSNode == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的CBS节点，无法确认外委进度");
}
if (cbsInfoDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的核算项目，无法确认外委进度");
}
var contractValue = Convert.ToDecimal(taskEntity.GetValue("SubContractValue"));
var scale = String.IsNullOrEmpty(this.ModelDic.GetValue("TotalProgress")) ? 0 : Convert.ToDecimal(this.ModelDic.GetValue("TotalProgress"));
var totalValue = contractValue * scale;
var sumCostValue = Convert.ToDecimal(taskEntity.GetValue("TotalSubContractValue"));
var currentValue = totalValue - sumCostValue;
//税金计算
var taxRate = String.IsNullOrEmpty(this.ModelDic.GetValue("TaxRate")) ? 0m : Convert.ToDecimal(this.ModelDic.GetValue("TaxRate"));
var taxValue = currentValue / (1 + taxRate) * taxRate;
var clearCostValue = currentValue - taxValue;
var costDetailDic = new Dictionary <string, object>();
var subjectDt = this.DB.ExecuteDataTable(String.Format(@"select * from S_EP_CBSNode with(nolock)
where FullID like '{0}%' order by FullID", unitCBSNode.GetValue("FullID")));
var costState = String.IsNullOrEmpty(this.ModelDic.GetValue("CostState")) ? "Finish" : this.ModelDic.GetValue("CostState");
var subjectCode = this.ModelDic.GetValue("SubjectCode");
var subjectNode = subjectDt.AsEnumerable().FirstOrDefault(c => c["SubjectCode"] != null && c["SubjectCode"] != DBNull.Value &&
c["SubjectCode"].ToString() == subjectCode);
if (subjectNode == null)
{
subjectNode = subjectDt.AsEnumerable().FirstOrDefault(c => c["SubjectType"] != null && c["SubjectType"] != DBNull.Value &&
c["SubjectType"].ToString() == SubjectType.SubContractCost.ToString());
if (subjectNode == null)
{
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"));
}
else
{
costDetailDic.SetValue("Name", subjectNode["Name"]);
costDetailDic.SetValue("Code", subjectNode["Code"]);
costDetailDic.SetValue("SubjectCode", subjectNode["SubjectCode"]);
costDetailDic.SetValue("CBSFullCode", subjectNode["FullCode"]);
costDetailDic.SetValue("CBSNodeID", subjectNode["ID"]);
costDetailDic.SetValue("CBSNodeFullID", subjectNode["FullID"]);
}
}
else
{
costDetailDic.SetValue("Name", subjectNode["Name"]);
costDetailDic.SetValue("Code", subjectNode["Code"]);
costDetailDic.SetValue("SubjectCode", subjectNode["SubjectCode"]);
costDetailDic.SetValue("CBSFullCode", subjectNode["FullCode"]);
costDetailDic.SetValue("CBSNodeID", subjectNode["ID"]);
costDetailDic.SetValue("CBSNodeFullID", subjectNode["FullID"]);
}
costDetailDic.SetValue("CostType", SubjectType.SubContractCost.ToString());
costDetailDic.SetValue("CBSInfoID", unitDic.GetValue("CBSInfoID"));
costDetailDic.SetValue("CostUnitID", unitDic.GetValue("ID"));
costDetailDic.SetValue("RelateID", this.ID);
var costDate = String.IsNullOrEmpty(this.ModelDic.GetValue("ApplyDate")) ? DateTime.Now : Convert.ToDateTime(this.ModelDic.GetValue("ApplyDate"));
costDetailDic.SetValue("CostDate", costDate);
costDetailDic.SetValue("BelongYear", costDate.Year);
costDetailDic.SetValue("BelongMonth", costDate.Month);
costDetailDic.SetValue("BelongQuarter", (costDate.Month - 1) / 3 + 1);
costDetailDic.SetValue("TotalPrice", currentValue);
costDetailDic.SetValue("TaxRate", taxRate);
costDetailDic.SetValue("TaxValue", taxValue);
costDetailDic.SetValue("ClearValue", clearCostValue);
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", this.ModelDic.GetValue("CreateUserID"));
costDetailDic.SetValue("CreateUserName", this.ModelDic.GetValue("CreateUser"));
costDetailDic.SetValue("CreateDate", DateTime.Now);
costDetailDic.InsertDB(this.DB, "S_EP_CostInfo");
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
this.DB.ExecuteNonQuery(String.Format(@"UPDATE S_EP_SubContractTask_CostUnitDetail
SET SubContractProgress={0},
TotalSubContractValue='{1}'
WHERE ID='{2}'", scale, totalValue, taskEntity.GetValue("ID")));
}
public static void SynCBSInfo(Dictionary <string, object> paramDic, Expenses.Logic.SetCBSOpportunity opp)
{
S_EP_CBSInfo newCBSInfo = null;
var db = SQLHelper.CreateSqlHelper(ConnEnum.Market);
var infrasDB = SQLHelper.CreateSqlHelper(ConnEnum.InfrasBaseConfig);
var defineDt = infrasDB.ExecuteDataTable(
String.Format("SELECT * FROM S_EP_DefineCBSInfo with(nolock) WHERE AutoCreateCBSInfo='{0}' AND AutoCreateMethod='{1}'",
true.ToString().ToLower(), opp.ToString()));
foreach (DataRow defineRow in defineDt.Rows)
{
var define = new Expenses.Logic.Domain.S_EP_DefineCBSInfo(FormulaHelper.DataRowToDic(defineRow));
if (define.ValidateAutoSet(paramDic))
{
newCBSInfo = define.AutoSetCBSInfo(paramDic, opp);
break;
}
}
if (newCBSInfo == null)
{
//由于上面没有触发自动立项（自动立项时会去做自动同步CBS节点的数据动作)
//这里需要再次搜索所有的符合自动更新节点数据的动作
string sql = String.Format(@"select * from S_EP_DefineCBSNode where SynData like '%{0}%'", opp.ToString());
var dt = infrasDB.ExecuteDataTable(sql);
foreach (DataRow defineNode in dt.Rows)
{
if (defineNode["DynamicSettings"] == DBNull.Value || String.IsNullOrEmpty(defineNode["DynamicSettings"].ToString()))
{
continue;
}
var list = Formula.Helper.JsonHelper.ToList(defineNode["DynamicSettings"].ToString());
var item = list.FirstOrDefault(c => c.GetValue("SynDataMethod") == opp.ToString());
if (item == null || String.IsNullOrEmpty(item.GetValue("SynDataMethodSettings")))
{
continue;
}
var settings = Formula.Helper.JsonHelper.ToObject(item.GetValue("SynDataMethodSettings"));
//如果没有定位核算项目的条件，则默认不做任何操作
//同步节点一定需要有定位到核算项目的条件设置
if (String.IsNullOrEmpty(settings.GetValue("LocationCondition")))
{
continue;
}
var locationCondition = Formula.Helper.JsonHelper.ToList(settings.GetValue("LocationCondition"));
if (locationCondition.Count == 0)
{
continue;
}
var locationSQL = "SELECT TOP 1 * FROM S_EP_CBSInfo WITH(NOLOCK) WHERE CBSDefineInfoID='" + defineNode["DefineID"] + "' ";
foreach (var condition in locationCondition)
{
var fieldValue = "";
if (!String.IsNullOrEmpty(condition.GetValue("Value")))
{
Regex reg = new Regex("\\{[0-9a-zA-Z_\\.]*\\}");
fieldValue = reg.Replace(condition.GetValue("Value"), (Match m) =>
{
#region 处理定义配置信息
var value = m.Value.Trim('{', '}');
var fields = value.Split('.');
if (fields.Length == 1)
{
return(paramDic.GetValue(fields[0]));
}
else
{
if (fields[0].ToLower() == "param" && paramDic != null)
{
return(paramDic.GetValue(fields[1]));
}
else
{
return("");
}
}
#endregion
});
}
locationSQL += getQueryMode(condition.GetValue("Field"), condition.GetValue("QueryMode"), fieldValue);
}
var cbsInfoDt = db.ExecuteDataTable(locationSQL);
if (cbsInfoDt.Rows.Count == 0)
{
continue;
}
newCBSInfo = new S_EP_CBSInfo(FormulaHelper.DataRowToDic(cbsInfoDt.Rows[0]));
var cbsNodeDt = db.ExecuteDataTable(String.Format("SELECT * FROM S_EP_CBSNode WHERE CBSInfoID='{0}'", newCBSInfo.ID));
var defineNodesList = newCBSInfo.DefineCBSNodes.AsEnumerable().ToList();
newCBSInfo.SynNodeWithDefineNode(opp, defineNode, cbsNodeDt, defineNodesList, paramDic);
var sumDataDefine = settings.GetValue("SummaryData").Split(',');
if (sumDataDefine.Contains("ContractValue"))
{
newCBSInfo.SummaryContractValue();
}
if (sumDataDefine.Contains("BudgetValue"))
{
newCBSInfo.SummaryBudgetValue();
}
if (sumDataDefine.Contains("ProductionValue"))
{
newCBSInfo.SummaryPlanProductionValue();
}
}
}
}
public JsonResult SaveExcelData()
{
var param = Request["param"].Split(new char[] { ',' });
var belongYear = Convert.ToInt32(param[0]);
var belongMonth = Convert.ToInt32(param[1]);
var createDate = DateTime.Now;
var reader = new System.IO.StreamReader(HttpContext.Request.InputStream);
string data = reader.ReadToEnd();
var tempdata = JsonConvert.DeserializeObject <Dictionary <string, string> >(data);
var list = JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(tempdata["data"]);
var BaseSQLDB = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var sqlUser = "******";
var sqlSubject = "select * from S_EP_DefineSubject where Code='{0}' ";
var sqlCBSUnitNode = @"select S_EP_CBSNode.*,S_EP_CostUnit.ID CostUnitID from S_EP_CostUnit inner join S_EP_CBSNode
on S_EP_CostUnit.CBSNodeID = S_EP_CBSNode.ID where S_EP_CostUnit.Code = '{0}'";
var sqlSubjectCBSNode = @"select * from S_EP_CBSNode where FullID like '%{0}%' and Code = '{1}'";
var sqlCBSInfo = "select * from S_EP_CBSInfo where ID ='{0}' ";
var sqlInsert = string.Empty;
var sqlUpdate = string.Empty;
DataTable dt = new DataTable();
var dic = new Dictionary <string, object>();
var cbsInfoDicList = new List <Dictionary <string, object> >();
foreach (var item in list)
{
dic.Clear();
try
{
dt = this.SQLDB.ExecuteDataTable(string.Format(sqlSubject, item.GetValue("SubjectCode")));
dic.SetValue("SubjectCode", dt.Rows[0]["Code"]);
dic.SetValue("Name", dt.Rows[0]["Name"]);
dic.SetValue("ExpenseType", dt.Rows[0]["ExpenseType"]);
dt = BaseSQLDB.ExecuteDataTable(string.Format(sqlUser, item.GetValue("UserCode")));
dic.SetValue("UserID", dt.Rows[0]["ID"]);
dic.SetValue("UserName", dt.Rows[0]["Name"]);
dic.SetValue("UserDept", dt.Rows[0]["DeptID"]);
dic.SetValue("UserDeptName", dt.Rows[0]["DeptName"]);
dt = SQLDB.ExecuteDataTable(string.Format(sqlCBSUnitNode, item.GetValue("UnitCode")));
var subjectCbsNodeDt = SQLDB.ExecuteDataTable(string.Format(sqlSubjectCBSNode, dt.Rows[0]["FullID"], item.GetValue("SubjectCode")));
dic.SetValue("Code", dt.Rows[0]["Code"]);
dic.SetValue("CBSInfoID", dt.Rows[0]["CBSInfoID"]);
dic.SetValue("CostUnitID", dt.Rows[0]["CostUnitID"]);
dic.SetValue("CBSFullCode", subjectCbsNodeDt.Rows[0]["FullCode"]);
dic.SetValue("CBSNodeID", subjectCbsNodeDt.Rows[0]["ID"]);
dic.SetValue("CBSNodeFullID", subjectCbsNodeDt.Rows[0]["FullID"]);
dic.SetValue("BelongDept", subjectCbsNodeDt.Rows[0]["ChargerDept"]);
dic.SetValue("BelongDeptName", subjectCbsNodeDt.Rows[0]["ChargerDeptName"]);
dic.SetValue("BelongUser", subjectCbsNodeDt.Rows[0]["ChargerUser"]);
dic.SetValue("BelongUserName", subjectCbsNodeDt.Rows[0]["ChargerUserName"]);
var cbsDt = SQLDB.ExecuteDataTable(string.Format(sqlCBSInfo, dt.Rows[0]["CBSInfoID"]));
cbsInfoDicList.Add(FormulaHelper.DataRowToDic(cbsDt.Rows[0]));
}
catch (Exception)
{
throw new Formula.Exceptions.BusinessValidationException("信息有误，无法导入！");
}
dic.SetValue("ID", FormulaHelper.CreateGuid());
dic.SetValue("CreateDate", createDate.ToString("yyyy-MM-dd"));
dic.SetValue("CreateUserID", CurrentUserInfo.UserID);
dic.SetValue("CreateUser", CurrentUserInfo.UserName);
dic.SetValue("ModifyDate", createDate.ToString("yyyy-MM-dd"));
dic.SetValue("ModifyUserID", CurrentUserInfo.UserID);
dic.SetValue("ModifyUser", CurrentUserInfo.UserName);
dic.SetValue("BelongYear", belongYear);
dic.SetValue("BelongQuarter", (belongMonth + 2) / 3);
dic.SetValue("BelongMonth", belongMonth);
dic.SetValue("State", "Finish");
dic.SetValue("Status", "Finish");
dic.SetValue("CostType", "DirectCost");
dic.SetValue("TotalPrice", item.GetValue("TotalPrice"));
dic.SetValue("Quantity", 1);
dic.SetValue("UnitPrice", item.GetValue("TotalPrice"));
dic.SetValue("CostDate", DateTime.Now.ToString("yyyy-MM-dd"));
sqlInsert += dic.CreateInsertSql(SQLDB, "S_EP_CostInfo", dic.GetValue("ID"));
}
string allSql = sqlInsert + " " + sqlUpdate;
if (!string.IsNullOrEmpty(allSql))
{
SQLDB.ExecuteNonQuery(allSql);
}
foreach (var item in cbsInfoDicList)
{
var cbsInfo = new S_EP_CBSInfo(item);
cbsInfo.SummaryCostValue();
}
return(Json("Success"));
}
public S_EP_CBSInfo AutoSetCBSInfo(Dictionary <string, object> paramDic, SetCBSOpportunity opp)
{
#region 校验数据
if (this.ModelDic.GetValue("AutoCreateCBSInfo").ToLower() != true.ToString().ToLower())
{
throw new Formula.Exceptions.BusinessValidationException("配置定义未设置自动立项属性，无法自理设置核算项目信息");
}
if (String.IsNullOrEmpty(this.ModelDic.GetValue("AutoCreateMethod")))
{
throw new Formula.Exceptions.BusinessValidationException("自动设置核算项目必须指定立项依据，请检查配置项是否设置完成");
}
if (!paramDic.ContainsKey("ID"))
{
throw new Formula.Exceptions.BusinessValidationException("输入对象的ID不能为空，RelateID绑定不允许为空");
}
var dataSourceDefine = new List <Dictionary <string, object> >();
if (!String.IsNullOrEmpty(this.ModelDic.GetValue("AutoCreateDataSource")))
{
dataSourceDefine = JsonHelper.ToList(this.ModelDic.GetValue("AutoCreateDataSource"));
}
#endregion
var dataSourceList = new Dictionary <string, DataTable>();
foreach (var item in dataSourceDefine)
{
var key = item.GetValue("Code");
if (String.IsNullOrEmpty(item.GetValue("SQL")))
{
continue;
}
var value = SQLHelper.CreateSqlHelper(item.GetValue("ConnName")).
ExecuteDataTable(this.ReplaceString(item.GetValue("SQL"), null, paramDic));
dataSourceList.Add(key, value);
}
string sql = "select * from S_EP_CBSInfo where CBSDefineInfoID='" + this.ModelDic.GetValue("ID") + "' ";
if (!String.IsNullOrEmpty(this.ModelDic.GetValue("LocationCondition")))
{
var locationCondition = JsonHelper.ToList(this.ModelDic.GetValue("LocationCondition"));
if (locationCondition.Count == 0)
{
sql += " AND RelateID='" + paramDic.GetValue("ID") + "'";
}
else
{
foreach (var condition in locationCondition)
{
var fieldValue = "";
if (!String.IsNullOrEmpty(condition.GetValue("Value")))
{
Regex reg = new Regex("\\{[0-9a-zA-Z_\\.]*\\}");
fieldValue = reg.Replace(condition.GetValue("Value"), (Match m) =>
{
#region 处理定义配置信息
var value = m.Value.Trim('{', '}');
var fields = value.Split('.');
if (fields.Length == 1)
{
return(paramDic.GetValue(fields[0]));
}
else
{
if (fields[0].ToLower() == "param" && paramDic != null)
{
return(paramDic.GetValue(fields[1]));
}
else
{
return("");
}
}
#endregion
});
}
sql += getQueryMode(condition.GetValue("Field"), condition.GetValue("QueryMode"), fieldValue);
}
}
}
var cbsInfoDt = this.DB.ExecuteDataTable(sql);
S_EP_CBSInfo cbsInfo;
if (cbsInfoDt.Rows.Count == 0)
{
//新建核算项目
var cbsInfoDic = new Dictionary <string, object>();
_setAdaptertValue(cbsInfoDic, paramDic, dataSourceList);
if (String.IsNullOrEmpty(cbsInfoDic.GetValue("RelateID")))
{
cbsInfoDic.SetValue("RelateID", paramDic.GetValue("ID"));
}
if (String.IsNullOrEmpty(cbsInfoDic.GetValue("RelateID")))
{
throw new Formula.Exceptions.BusinessValidationException("必须指定业务关联ID，否则无法立项");
}
cbsInfoDic.SetValue("ID", FormulaHelper.CreateGuid());
cbsInfoDic.SetValue("Type", CBSDefineType.Produce.ToString());
cbsInfoDic.SetValue("BuildBasis", this.ModelDic.GetValue("AutoCreateMethod"));
cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.Build(this);
cbsInfo.SynNodeData(opp, paramDic);
}
else
{
//更新核算项目
var cbsInfoDic = Formula.FormulaHelper.DataRowToDic(cbsInfoDt.Rows[0]);
_setAdaptertValue(cbsInfoDic, paramDic, dataSourceList);
cbsInfoDic.UpdateDB(this.DB, "S_EP_CBSInfo", cbsInfoDic.GetValue("ID"));
cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SynNodeData(opp, paramDic);
}
var sumDataDefine = this.ModelDic.GetValue("SummaryData").Split(',');
if (sumDataDefine.Contains("ContractValue"))
{
cbsInfo.SummaryContractValue();
}
if (sumDataDefine.Contains("BudgetValue"))
{
cbsInfo.SummaryBudgetValue();
}
if (sumDataDefine.Contains("ProductionValue"))
{
cbsInfo.SummaryPlanProductionValue();
}
return(cbsInfo);
}
/// <summary>
/// 成本核算
/// </summary>
/// <param name="dic"></param>
private void ToCostInfo(Dictionary <string, string> dic)
{
var invoiceEntity = this.GetDataDicByID("S_SP_Invoice", dic.GetValue("InvoiceID"));
if (invoiceEntity == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的委外开票信息，无法进行委外开票确认");
}
var unitDic = this.GetDataDicByID("S_EP_CostUnit", dic.GetValue("CostUnitID"));
if (unitDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的成本单元，无法确认成本");
}
var unitCBSNode = this.GetDataDicByID("S_EP_CBSNode", unitDic.GetValue("CBSNodeID"), Config.ConnEnum.Market, true);
if (unitCBSNode == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的CBS节点，无法确认成本");
}
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", unitDic.GetValue("CBSInfoID"));
if (cbsInfoDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的核算项目，无法确认成本");
}
var costDetailDic = new Dictionary <string, object>();
var subjectDt = this.SQLDB.ExecuteDataTable(String.Format(@"select * from S_EP_CBSNode with(nolock)
where FullID like '{0}%' order by FullID", unitCBSNode.GetValue("FullID")));
var costState = "Finish";
var subjectNode = subjectDt.AsEnumerable().FirstOrDefault(c => c["SubjectType"] != null && c["SubjectType"] != DBNull.Value &&
c["SubjectType"].ToString() == SubjectType.SubContractCost.ToString());
if (subjectNode == null)
{
throw new BusinessException("未找到SubjectType为【" + SubjectType.SubContractCost.ToString() + "】的节点");
}
costDetailDic.SetValue("Name", subjectNode["Name"]);
costDetailDic.SetValue("CBSFullCode", subjectNode["FullCode"]);
costDetailDic.SetValue("CBSNodeID", subjectNode["ID"]);
costDetailDic.SetValue("CBSNodeFullID", subjectNode["FullID"]);
if (String.IsNullOrEmpty(costDetailDic.GetValue("Name")))
{
costDetailDic.SetValue("Name", "采购分包费");
}
costDetailDic.SetValue("Code", subjectNode["Code"]);
if (String.IsNullOrEmpty(costDetailDic.GetValue("Code")))
{
costDetailDic.SetValue("Code", unitCBSNode.GetValue("Code"));
}
costDetailDic.SetValue("CostType", SubjectType.SubContractCost.ToString());
costDetailDic.SetValue("CBSInfoID", unitDic.GetValue("CBSInfoID"));
costDetailDic.SetValue("CostUnitID", unitDic.GetValue("ID"));
costDetailDic.SetValue("SubjectCode", subjectNode["SubjectCode"]);
costDetailDic.SetValue("RelateID", dic.GetValue("ID"));
var confirmDate = Convert.ToDateTime(dic.GetValue("ConfirmDate"));
costDetailDic.SetValue("CostDate", confirmDate);
costDetailDic.SetValue("BelongYear", confirmDate.Year);
costDetailDic.SetValue("BelongMonth", confirmDate.Month);
costDetailDic.SetValue("BelongQuarter", (confirmDate.Month + 2) / 3);
costDetailDic.SetValue("TotalPrice", dic.GetValue("CurrentValue"));
costDetailDic.SetValue("TaxRate", dic.GetValue("TaxRate"));
costDetailDic.SetValue("TaxValue", dic.GetValue("TaxValue"));
costDetailDic.SetValue("ClearValue", dic.GetValue("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", dic.GetValue("ApplyUser"));
costDetailDic.SetValue("CreateUserName", dic.GetValue("ApplyUserName"));
costDetailDic.SetValue("CreateDate", DateTime.Now);
costDetailDic.InsertDB(this.SQLDB, "S_EP_CostInfo");
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
}
//批量确认开票
public JsonResult BatchConfirm()
{
var totalProgress = 0m;
decimal.TryParse(Request["ConfirmProgress"], out totalProgress);
if (totalProgress <= 0)
{
throw new Formula.Exceptions.BusinessValidationException("确认比例必须大于0！");
}
var confirmDate = DateTime.Now.Date;
if (!DateTime.TryParse(Request["ConfirmDate"], out confirmDate))
{
throw new Formula.Exceptions.BusinessValidationException("请选择确认日期！");
}
CostFO.ValidatePeriodIsClosed(confirmDate);
var invoiceValue = 0m;
var lastProgress = 0m;
var lastValue = 0m;
var totalValue = 0m;
var currentValue = 0m;
var taxRate = 0m;
var clearValue = 0m;
var taxValue = 0m;
var invoiceConfirmID = string.Empty;
var list = JsonHelper.ToList(Request["ListData"]);
foreach (var item in list)
{
#region 验证数据，变量赋值
//上期开票确认信息
var sql = string.Format(@"select Invoice.ID,
ISNULL(Invoice.Amount,0) as InvoiceValue,
Invoice.InvoiceReciever as ChargerUser,
Invoice.InvoiceRecieverName as ChargerUserName,
Invoice.Supplier,
Invoice.SupplierName,
SupplierContract.SupplierDept as ChargerDept,
SupplierContract.SupplierDeptName as ChargerDeptName,
SupplierContract.ProjectInfo,
SupplierContract.ProjectInfoName,
SupplierContract.Name as SubContracName,
SupplierContract.SerialNumber as SubContracCode,
ISNULL(SupplierContract.TaxRate,0) as TaxRate,
ConfirmInfo.ID as InvoiceConfirmID,
ISNULL(ConfirmInfo.TotalProgress,0) as LastProgress,
ISNULL(ConfirmInfo.TotalValue,0) as LastValue,
S_EP_CostUnit.ID as CostUnitID,
S_EP_CostUnit.Name,
S_EP_CostUnit.Code,
S_EP_CostUnit.CBSNodeID,
S_EP_CostUnit.CBSInfoID,
S_EP_CBSInfo.Name as CBSInfoName,
S_EP_CBSInfo.Code as CBSInfoCode
from (select * from S_SP_Invoice where ID='{0}') as Invoice
left join S_SP_SupplierContract SupplierContract on Invoice.SupplierContract = SupplierContract.ID
outer apply(select top 1 * from S_EP_InvoiceConfirm a where Invoice.ID=a.InvoiceID order by a.ID desc) ConfirmInfo
left join S_EP_CostUnit on S_EP_CostUnit.ID=Invoice.CostUnit
left join S_EP_CBSInfo on S_EP_CostUnit.CBSInfoID=S_EP_CBSInfo.ID", item.GetValue("ID"));
var dt = SQLDB.ExecuteDataTable(sql);
if (dt.Rows.Count == 0)
{
throw new Formula.Exceptions.BusinessValidationException(string.Format("项目【{0}】没有找到指定的委外开票信息，无法进行确认", item.GetValue("Name")));
}
var dic = FormulaHelper.DataRowToDic(dt.Rows[0]);
lastProgress = 0m;
lastValue = 0m;
//非首次确认
if (!string.IsNullOrEmpty(dic.GetValue("InvoiceConfirmID")))
{
lastProgress = string.IsNullOrEmpty(dic.GetValue("LastProgress")) ? 0m : Convert.ToDecimal(dic.GetValue("LastProgress"));
//是否确认至100%
if (lastProgress >= 1)
{
continue;
}
lastValue = string.IsNullOrEmpty(dic.GetValue("LastValue")) ? 0m : Convert.ToDecimal(dic.GetValue("LastValue"));
}
//【本期末累计确认比例】是否大于 【上期末累计确认比例】
if (totalProgress <= lastProgress)
{
throw new Formula.Exceptions.BusinessValidationException("【本期末累计确认比例】必须大于【上期末累计确认比例】");
}
//【发票金额】不能为0
invoiceValue = string.IsNullOrEmpty(dic.GetValue("InvoiceValue")) ? 0m : Convert.ToDecimal(dic.GetValue("InvoiceValue"));
if (invoiceValue == 0)
{
throw new Formula.Exceptions.BusinessValidationException(string.Format("项目【{0}】的【开票金额】为0，不能确认！", item.GetValue("Name")));
}
#endregion
#region 计算【本期末累计确认金额】【本期确认金额】【税金】【去税金额】
totalValue = invoiceValue * totalProgress; //本期末累计确认金额
currentValue = totalValue - lastValue; //本期确认金额
taxRate = string.IsNullOrEmpty(dic.GetValue("TaxRate")) ? 0m : Convert.ToDecimal(dic.GetValue("TaxRate")); //税率
taxValue = currentValue * taxRate / (1 + taxRate); //税金
clearValue = currentValue / (1 + taxRate); //去税金额
totalValue = Math.Round(totalValue, 2);
currentValue = Math.Round(currentValue, 2);
taxValue = Math.Round(taxValue, 2);
clearValue = Math.Round(clearValue, 2);
#endregion
#region 保存开票确认信息
var dicConfirm = new Dictionary <string, object>();
dicConfirm.SetValue("InvoiceID", item.GetValue("ID"));
dicConfirm.SetValue("InvoiceValue", invoiceValue);
dicConfirm.SetValue("LastProgress", lastProgress);
dicConfirm.SetValue("LastValue", lastValue);
dicConfirm.SetValue("TotalProgress", totalProgress);
dicConfirm.SetValue("TotalValue", totalValue);
dicConfirm.SetValue("CurrentValue", currentValue);
dicConfirm.SetValue("ApplyUser", CurrentUserInfo.UserID);
dicConfirm.SetValue("ApplyUserName", CurrentUserInfo.UserName);
dicConfirm.SetValue("ApplyDate", DateTime.Now);
dicConfirm.SetValue("TaxRate", taxRate);
dicConfirm.SetValue("TaxValue", taxValue);
dicConfirm.SetValue("ClearValue", clearValue);
dicConfirm.SetValue("ConfirmDate", confirmDate);
dicConfirm.SetValue("CBSInfoID", item.GetValue("CBSInfoID"));
dicConfirm.SetValue("CBSInfoName", dic.GetValue("CBSInfoName"));
dicConfirm.SetValue("CBSInfoCode", dic.GetValue("CBSInfoCode"));
dicConfirm.SetValue("CBSNodeID", item.GetValue("CBSNodeID"));
dicConfirm.SetValue("CostUnitID", item.GetValue("CostUnitID"));
//dicConfirm.SetValue("InvoiceValue", invoiceValue);
invoiceConfirmID = dicConfirm.InsertDB(SQLDB, "S_EP_InvoiceConfirm");
#endregion
//continue;
#region 核算成本
var unitDic = this.GetDataDicByID("S_EP_CostUnit", item.GetValue("CostUnitID"));
if (unitDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到指定的成本单元，无法确认成本！");
}
var unitCBSNode = this.GetDataDicByID("S_EP_CBSNode", unitDic.GetValue("CBSNodeID"));
if (unitCBSNode == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的CBS节点，无法确认成本！");
}
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", unitDic.GetValue("CBSInfoID"));
if (cbsInfoDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到成本单元对应的核算项目，无法确认成本！");
}
var costDetailDic = new Dictionary <string, object>();
var subjectDt = this.SQLDB.ExecuteDataTable(String.Format(@"select * from S_EP_CBSNode with(nolock)
where FullID like '{0}%' order by FullID", unitCBSNode.GetValue("FullID")));
var costState = "Finish";
var subjectNode = subjectDt.AsEnumerable().FirstOrDefault(c => c["SubjectType"] != null && c["SubjectType"] != DBNull.Value &&
c["SubjectType"].ToString() == SubjectType.SubContractCost.ToString());
if (subjectNode == null)
{
throw new BusinessException("未找到SubjectType为【" + SubjectType.SubContractCost.ToString() + "】的节点");
}
costDetailDic.SetValue("Name", subjectNode["Name"]);
costDetailDic.SetValue("CBSFullCode", subjectNode["FullCode"]);
costDetailDic.SetValue("CBSNodeID", subjectNode["ID"]);
costDetailDic.SetValue("CBSNodeFullID", subjectNode["FullID"]);
if (String.IsNullOrEmpty(costDetailDic.GetValue("Name")))
{
costDetailDic.SetValue("Name", "采购分包费");
}
costDetailDic.SetValue("Code", subjectNode["Code"]);
if (String.IsNullOrEmpty(costDetailDic.GetValue("Code")))
{
costDetailDic.SetValue("Code", unitCBSNode.GetValue("Code"));
}
costDetailDic.SetValue("CostType", SubjectType.SubContractCost.ToString());
costDetailDic.SetValue("CBSInfoID", unitDic.GetValue("CBSInfoID"));
costDetailDic.SetValue("CostUnitID", unitDic.GetValue("ID"));
costDetailDic.SetValue("SubjectCode", subjectNode["SubjectCode"]);
costDetailDic.SetValue("RelateID", invoiceConfirmID);
costDetailDic.SetValue("CostDate", confirmDate);
costDetailDic.SetValue("BelongYear", confirmDate.Year);
costDetailDic.SetValue("BelongMonth", confirmDate.Month);
costDetailDic.SetValue("BelongQuarter", (confirmDate.Month + 2) / 3);
costDetailDic.SetValue("TotalPrice", currentValue);
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", CurrentUserInfo.UserID);
costDetailDic.SetValue("CreateUserName", CurrentUserInfo.UserName);
costDetailDic.SetValue("CreateDate", DateTime.Now);
costDetailDic.InsertDB(this.SQLDB, "S_EP_CostInfo");
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
cbsInfo.SummaryCostValue();
#endregion
}
return(Json(""));
}
public void Push()
{
var cbsInfoDic = this.GetDataDicByID("S_EP_CBSInfo", this.ModelDic.GetValue("CBSInfoID"));
if (cbsInfoDic == null)
{
throw new Formula.Exceptions.BusinessValidationException("没有找到核算项目，无法变更");
}
var cbsInfo = new S_EP_CBSInfo(cbsInfoDic);
var defineNodeDt = this.InfrasDB.ExecuteDataTable(String.Format(@"SELECT * FROM S_EP_DefineCBSNode WHERE DefineID='{0}' ",
cbsInfoDic.GetValue("CBSDefineInfoID")));
defineNodeDt.PrimaryKey = new DataColumn[] { defineNodeDt.Columns["ID"] };
var detailDt = this.DB.ExecuteDataTable(@"SELECT * FROM S_EP_CBSInfoSchema_CBSNodeInfo
WHERE S_EP_CBSInfoSchemaID='" + this.ModelDic.GetValue("ID") + "'");
var sqlCommand = new StringBuilder();
var addedList = new List <Dictionary <string, object> >();
foreach (DataRow row in detailDt.Rows)
{
if (row["ModifyState"] == null || row["ModifyState"] == DBNull.Value)
{
continue;
}
if (row["ModifyState"].ToString() == ModifyState.Added.ToString())
{
var cbsNode = this.GetDataDicByID("S_EP_CBSNode", row["CBSNodeID"].ToString());
if (cbsNode != null)
{
Formula.LogWriter.Error("新增节点时候，CBS节点ID已存在，记录ID 为【" + row["ID"] + "】");
continue;
}
var defineNodeRow = defineNodeDt.Rows.Find(row["CBSDefineID"].ToString());
if (defineNodeRow == null)
{
Formula.LogWriter.Error("新增节点时候，没有找到CBS定义节点，记录ID 为【" + row["ID"] + "】");
continue;
}
var cbsNodeDic = FormulaHelper.DataRowToDic(row);
cbsNodeDic.SetValue("ID", row["CBSNodeID"].ToString());
cbsNodeDic.SetValue("CBSInfoID", cbsInfoDic.GetValue("ID"));
cbsNodeDic.SetValue("ParentID", row["CBSParentID"].ToString());
cbsNodeDic.SetValue("DefineID", row["CBSDefineID"].ToString());
cbsNodeDic.SetValue("FullID", row["CBSNodeFullID"].ToString());
cbsNodeDic.SetValue("NodeType", row["NodeType"].ToString());
cbsNodeDic.SetValue("CBSType", cbsInfoDic.GetValue("Type"));
if (String.IsNullOrEmpty(cbsNodeDic.GetValue("Code")))
{
cbsNodeDic.SetValue("Code", cbsInfoDic.GetValue("Code"));
}
cbsNodeDic.InsertDB(this.DB, "S_EP_CBSNode", cbsNodeDic.GetValue("ID"));
addedList.Add(cbsNodeDic);
cbsInfo.setUnit(cbsNodeDic, row, defineNodeRow);
#region 自动增加静态节点
var staticDefineNodeList = defineNodeDt.AsEnumerable().Where(c => c["FullID"] != DBNull.Value &&
c["FullID"].ToString().StartsWith(defineNodeRow["FullID"].ToString()) && c["IsDynamic"].ToString() != "true").
OrderBy(c => c["FullID"].ToString()).ToList();
foreach (var staticDefineNodeRow in staticDefineNodeList)
{
var parentCBSNodeRows = addedList.Where(c => c["DefineID"] != DBNull.Value &&
c["DefineID"].ToString() == staticDefineNodeRow["ParentID"].ToString()).ToList();
foreach (var parentNodeRow in parentCBSNodeRows)
{
var staticNode = addedList.AsEnumerable().FirstOrDefault(c => c["ParentID"] != DBNull.Value &&
c["ParentID"].ToString() == parentNodeRow["ID"].ToString() && c["DefineID"] != DBNull.Value &&
c["DefineID"].ToString() == staticDefineNodeRow["ID"].ToString());
if (staticNode == null)
{
staticNode = new Dictionary <string, object>();
var staticDefineNodeDic = FormulaHelper.DataRowToDic(staticDefineNodeRow);
var parentCBS = new S_EP_CBSNode(parentNodeRow);
staticNode.SetValue("ID", FormulaHelper.CreateGuid());
staticNode.SetValue("DefineID", staticDefineNodeRow["ID"]);
staticNode.SetValue("Name", staticDefineNodeRow["Name"]);
staticNode.SetValue("Code", staticDefineNodeRow["Code"]);
staticNode.SetValue("NodeType", staticDefineNodeRow["NodeType"]);
if (staticNode.GetValue("NodeType") == CBSNodeType.Subject.ToString())
{
staticNode.SetValue("SubjectID", staticDefineNodeRow["SubjectID"]);
staticNode.SetValue("SubjectType", staticDefineNodeRow["SubjectType"]);
staticNode.SetValue("Code", staticDefineNodeRow["SubjectCode"]);
staticNode.SetValue("SubjectFullCode", staticDefineNodeRow["SubjectFullCode"]);
}
staticNode.SetValue("CBSType", cbsInfoDic.GetValue("Type"));
staticNode.SetValue("ChargerUser", cbsInfoDic.GetValue("ChargerUser"));
staticNode.SetValue("ChargerUserName", cbsInfoDic.GetValue("ChargerUserName"));
staticNode.SetValue("ChargerDept", cbsInfoDic.GetValue("ChargerDept"));
staticNode.SetValue("ChargerDeptName", cbsInfoDic.GetValue("ChargerDeptName"));
staticNode.SetValue("ContractInfoID", cbsInfoDic.GetValue("ContractInfoID"));
staticNode.SetValue("ProjectInfoID", cbsInfoDic.GetValue("ProjectInfoID"));
staticNode.SetValue("EngineeringInfoID", cbsInfoDic.GetValue("EngineeringInfoID"));
staticNode.SetValue("ClueInfoID", cbsInfoDic.GetValue("ClueInfoID"));
parentCBS.AddChild(staticNode);
addedList.Add(staticNode);
}
}
}
#endregion
}
else if (row["ModifyState"].ToString() == ModifyState.Modified.ToString())
{
#region
var cbsNode = this.GetDataDicByID("S_EP_CBSNode", row["CBSNodeID"].ToString());
if (cbsNode == null)
{
continue;
}
cbsNode.SetValue("Name", row["Name"]);
cbsNode.SetValue("Code", row["Code"]);
cbsNode.SetValue("ChargerUser", row["ChargerUser"]);
cbsNode.SetValue("ChargerUserName", row["ChargerUserName"]);
cbsNode.SetValue("ChargerDept", row["ChargerDept"]);
cbsNode.SetValue("ChargerDeptName", row["ChargerDeptName"]);
cbsNode.SetValue("ContractValue", row["ContractValue"]);
cbsNode.SetValue("ContractTaxValue", row["ContractTaxValue"]);
cbsNode.SetValue("ContractClearValue", row["ContractClearValue"]);
cbsNode.SetValue("TaxRate", row["TaxRate"]);
cbsNode.SetValue("DefineID", row["CBSDefineID"]);
cbsNode.SetValue("NodeType", row["NodeType"]);
sqlCommand.AppendLine(cbsNode.CreateUpdateSql(this.DB, "S_EP_CBSNode", cbsNode.GetValue("ID")));
#endregion
var defineNodeRow = defineNodeDt.Rows.Find(row["CBSDefineID"].ToString());
if (defineNodeRow == null)
{
Formula.LogWriter.Error("新增节点时候，没有找到CBS定义节点，记录ID 为【" + row["ID"] + "】");
continue;
}
cbsInfo.setUnit(cbsNode, row, defineNodeRow);
}
else if (row["ModifyState"].ToString() == ModifyState.Removed.ToString())
{
var cbsNode = this.GetDataDicByID("S_EP_CBSNode", row["CBSNodeID"].ToString());
if (cbsNode == null)
{
continue;
}
var node = new S_EP_CBSNode(cbsNode);
node.Delete();
}
}
if (!String.IsNullOrEmpty(sqlCommand.ToString()))
{
this.DB.ExecuteNonQuery(sqlCommand.ToString());
}
}
