private void SetRelationNoContract() { try { int index; DataRow[] rowArray = this.tbContract.Select("RecordType = 'NoContract'"); int length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbContract.Rows.Remove(rowArray[index]); } rowArray = this.tbPayout.Select("RecordType = 'NoContract'"); length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbPayout.Rows.Remove(rowArray[index]); } rowArray = this.tbApportion.Select("RecordType = 'NoContract'"); length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbApportion.Rows.Remove(rowArray[index]); } QueryAgent agent = new QueryAgent(); try { string queryString = "select 'Payment_' + a.PaymentCode as ContractCode, '' as ContractID, '非合同请款' + b.PaymentID as ContractName, c.SubjectCode as CostCode, c.SubjectCode as FullCode, b.SupplyCode as SupplierCode, b.SupplyName as SupplierName, a.ItemMoney as Money, a.CostBudgetSetCode, cb.PBSType, cb.PBSCode from PaymentItem a left join CBS c on c.CostCode = a.CostCode left join CostBudgetSet cb on cb.CostBudgetSetCode = a.CostBudgetSetCode, Payment b, PayoutItem oi, Voucher v, VoucherDetail vd where a.PaymentCode = b.PaymentCode and b.ProjectCode = '" + this.ProjectCode + "' and isnull(b.IsContract, 0) = 0 and a.PaymentItemCode = oi.PaymentItemCode and vd.RelaType = '付款' and vd.RelaCode = oi.PayoutCode and v.VoucherCode = vd.VoucherCode and v.Status in (1, 2)"; if ((this.PBSType != "") || (this.PBSCode != "")) { string text4 = queryString; queryString = text4 + " and cb.PBSType = '" + this.PBSType + "' and cb.PBSCode = '" + this.PBSCode + "'"; } DataTable tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "NoContract", "ContractMoney"); foreach (DataRow row in this.tbContract.Rows) { string text2 = ConvertRule.ToString(row["ContractCode"]).Replace("Payment_", ""); string text3 = ConvertRule.ToString(row["CostCode"]); queryString = "select b.VoucherCode as PayoutCode, b.CheckDate as PayoutDate, a.DebitMoney as Money, 'Payment_' + pi.PaymentCode as ContractCode, a.SubjectCode as CostCode, a.SubjectCode as FullCode, '' as CostBudgetSetCode, '' as PBSType, '' as PBSCode from VoucherDetail a inner join Voucher b on b.VoucherCode = a.VoucherCode and b.Status in (1, 2), PayoutItem oi, PaymentItem pi where b.ProjectCode = '" + this.ProjectCode + "' and a.RelaType = '付款' and a.RelaCode = oi.PayoutCode and oi.PaymentItemCode = pi.PaymentItemCode and pi.PaymentCode = '" + text2 + "'"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddPayout(tbSrc, "Contract"); foreach (DataRow row2 in tbSrc.Rows) { row["ContractPayReal"] = ConvertRule.ToDecimal(row["ContractPayReal"]) + ConvertRule.ToDecimal(row2["Money"]); } } CostBudgetPageRule.CostBudgetDtlCalcAllRow(this.tbContract, CostBudgetPageRule.m_DynamicRowType.Contract, null, 0, 0); } finally { agent.Dispose(); } } catch (Exception exception) { throw exception; } }
private void SetRelationBalance() { try { int index; DataRow[] rowArray = this.tbContract.Select("RecordType = 'Balance'"); int length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbContract.Rows.Remove(rowArray[index]); } rowArray = this.tbPlan.Select("RecordType = 'Balance'"); length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbPlan.Rows.Remove(rowArray[index]); } QueryAgent agent = new QueryAgent(); try { DataTable tbSrc; string queryString; if (this.CostBudgetCode != "") { queryString = "select c.FullCode, a.CostCode, 'Balance' as RecordType, 'Balance_' + a.CostCode as ContractCode, '' as ContractID, '预留金额' as ContractName, a.BudgetMoney as Money from CostBudgetDtl a left join CBS c on c.CostCode = a.CostCode where a.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetCode = '" + this.CostBudgetCode + "' and not exists(select * from CBS CBSCount where CBSCount.ParentCode = a.CostCode)"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "Balance", "ContractMoney"); } if (this.CostBudgetCode != "") { queryString = "select c.FullCode, a.CostCode, 'Balance' as RecordType, 'Balance_' + a.CostCode as ContractCode, right('0000' + cast(b.IYear as varchar), 4) + right('00' + cast(b.IMonth as varchar), 2) as PlanYm, b.BudgetMoney as PlanMoney from CostBudgetDtl a left join CBS c on c.CostCode = a.CostCode, CostBudgetMonth b where a.CostBudgetDtlCode = b.CostBudgetDtlCode and a.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetCode = '" + this.CostBudgetCode + "' and right('0000' + cast(b.IYear as varchar), 4) + right('00' + cast(b.IMonth as varchar), 2) between '" + this.StartYm + "' and '" + this.EndYm + "'"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddPlan(tbSrc, ""); } CostBudgetPageRule.CostBudgetDtlCalcAllRow(this.tbContract, CostBudgetPageRule.m_DynamicRowType.Contract, null, 0, 0); } finally { agent.Dispose(); } } catch (Exception exception) { throw exception; } }
private void SetRelationBidding() { try { int index; DataRow[] rowArray = this.tbContract.Select("RecordType = 'Bidding'"); int length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbContract.Rows.Remove(rowArray[index]); } rowArray = this.tbPlan.Select("RecordType = 'Bidding'"); length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbPlan.Rows.Remove(rowArray[index]); } QueryAgent agent = new QueryAgent(); try { string queryString = "select c.FullCode, 'Bidding' as RecordType, 'Bidding_' + b.BiddingCode + '#' + b.BiddingDtlCode as ContractCode, '' as ContractID, a.title + '(' + b.title + ')' as ContractName, b.* from Bidding a , BiddingDtl b left join CBS c on c.CostCode = b.CostCode where a.ProjectCode = '" + this.ProjectCode + "' and b.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and isnull(a.state, 0) = 0 and a.BiddingCode = b.BiddingCode"; DataTable tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "Bidding", "ContractMoney"); queryString = "select c.FullCode, 'Bidding' as RecordType, 'Bidding_' + b.BiddingCode + '#' + a.BiddingDtlCode as ContractCode, a.CostCode, convert(varchar(6), p.PlanningPayDate, 112) as PlanYm, sum(isnull(p.Money, 0)) as PlanMoney from BiddingDtl a left join CBS c on c.CostCode = a.CostCode, Bidding b, BiddingDtlPlan p where a.BiddingCode = b.BiddingCode and a.BiddingDtlCode = p.BiddingDtlCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and convert(varchar(6), p.PlanningPayDate, 112) between '" + this.StartYm + "' and '" + this.EndYm + "' group by c.FullCode, a.CostCode, 'Bidding_' + b.BiddingCode + '#' + a.BiddingDtlCode, convert(varchar(6), p.PlanningPayDate, 112)"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddPlan(tbSrc, ""); CostBudgetPageRule.CostBudgetDtlCalcAllRow(this.tbContract, CostBudgetPageRule.m_DynamicRowType.Contract, null, 0, 0); } finally { agent.Dispose(); } } catch (Exception exception) { throw exception; } }
private void SetRelationNoContract() { try { int index; DataRow[] rowArray = this.tbContract.Select("RecordType = 'NoContract'"); int length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbContract.Rows.Remove(rowArray[index]); } rowArray = this.tbPayout.Select("RecordType = 'NoContract'"); length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbPayout.Rows.Remove(rowArray[index]); } rowArray = this.tbApportion.Select("RecordType = 'NoContract'"); length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbApportion.Rows.Remove(rowArray[index]); } QueryAgent agent = new QueryAgent(); try { string text4; string queryString = "select 'Payment_' + a.PaymentCode as ContractCode, '' as ContractID, '非合同请款' + b.PaymentID as ContractName, a.CostCode, c.FullCode, b.SupplyCode as SupplierCode, b.SupplyName as SupplierName, a.ItemMoney as Money, a.CostBudgetSetCode, cb.PBSType, cb.PBSCode from PaymentItem a left join CBS c on c.CostCode = a.CostCode left join CostBudgetSet cb on cb.CostBudgetSetCode = a.CostBudgetSetCode, Payment b where a.PaymentCode = b.PaymentCode and b.ProjectCode = '" + this.ProjectCode + "' and isnull(b.IsContract, 0) = 0"; if ((this.PBSType != "") || (this.PBSCode != "")) { text4 = queryString; queryString = text4 + " and cb.PBSType = '" + this.PBSType + "' and cb.PBSCode = '" + this.PBSCode + "'"; } DataTable tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "NoContract", "ContractMoney"); queryString = "select 'Payment_' + a.PaymentCode as ContractCode, '' as ContractID, '非合同请款' + b.PaymentID as ContractName, a.CostCode, c.FullCode, b.SupplyCode as SupplierCode, b.SupplyName as SupplierName, a.ItemMoney as Money, a.CostBudgetSetCode, cb.PBSType, cb.PBSCode from PaymentItem a left join CBS c on c.CostCode = a.CostCode left join CostBudgetSet cb on cb.CostBudgetSetCode = a.CostBudgetSetCode, Payment b where a.PaymentCode = b.PaymentCode and b.ProjectCode = '" + this.ProjectCode + "' and b.Status in (1, 2) and isnull(b.IsContract, 0) = 0"; if ((this.PBSType != "") || (this.PBSCode != "")) { text4 = queryString; queryString = text4 + " and cb.PBSType = '" + this.PBSType + "' and cb.PBSCode = '" + this.PBSCode + "'"; } tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "NoContract", "ContractPay"); queryString = "select mi.CostCode, c.FullCode, 'Payment_' + m.PaymentCode as ContractCode, a.PayoutCode, b.PayoutDate, a.PayoutMoney as Money, mi.CostBudgetSetCode, cb.PBSType, cb.PBSCode from PayoutItem a inner join Payout b on b.PayoutCode = a.PayoutCode, PaymentItem mi left join CBS c on c.CostCode = mi.CostCode left join CostBudgetSet cb on cb.CostBudgetSetCode = mi.CostBudgetSetCode, Payment m where a.PaymentItemCode = mi.PaymentItemCode and mi.PaymentCode = m.PaymentCode and m.ProjectCode = '" + this.ProjectCode + "' and isnull(m.IsContract, 0) = 0"; if ((this.PBSType != "") || (this.PBSCode != "")) { text4 = queryString; queryString = text4 + " and cb.PBSType = '" + this.PBSType + "' and cb.PBSCode = '" + this.PBSCode + "'"; } if (PaymentRule.IsPayoutMoneyIncludeNotCheck == 0) { queryString = queryString + " and b.Status in (1)"; } DataTable table2 = agent.ExecSqlForDataSet(queryString).Tables[0]; DataView view = new DataView(this.tbContract, "RecordType = 'NoContract'", "", DataViewRowState.CurrentRows); foreach (DataRowView view2 in view) { DataRow row = view2.Row; string costCode = ConvertRule.ToString(row["CostCode"]); string contractCode = ConvertRule.ToString(row["ContractCode"]); DataRow[] drsSrc = table2.Select("CostCode = '" + costCode + "' and ContractCode = '" + contractCode + "'"); this.AddPayout(drsSrc, "Contract"); foreach (DataRow row2 in drsSrc) { row["ContractPayReal"] = ConvertRule.ToDecimal(row["ContractPayReal"]) + ConvertRule.ToDecimal(row2["Money"]); this.DoApportion(contractCode, costCode, ConvertRule.ToString(row2["CostBudgetSetCode"]), ConvertRule.ToString(row["RecordType"]), "ContractPayReal", ConvertRule.ToDecimal(row2["Money"])); } } CostBudgetPageRule.CostBudgetDtlCalcAllRow(this.tbContract, CostBudgetPageRule.m_DynamicRowType.Contract, null, 0, 0); } finally { agent.Dispose(); } } catch (Exception exception) { throw exception; } }
private void SetRelationContract() { try { int index; DataRow[] rowArray = this.tbContract.Select("RecordType = 'Contract'"); int length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbContract.Rows.Remove(rowArray[index]); } rowArray = this.tbPlan.Select("RecordType = 'Contract'"); length = rowArray.Length; for (index = length - 1; index >= 0; index--) { this.tbPlan.Rows.Remove(rowArray[index]); } QueryAgent agent = new QueryAgent(); try { string queryString = "select c.FullCode, s.SupplierName, a.*, b.* from ContractCost a left join CBS c on c.CostCode = a.CostCode, Contract b left join Supplier s on s.SupplierCode = b.SupplierCode where a.ContractCode = b.ContractCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and b.Status in (0, 2) and not exists (select * from ContractChange g where g.ContractCode = b.ContractCode)"; DataTable tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "Contract", "ContractMoney"); queryString = "select c.FullCode, a.Money as Money, s.SupplierName, a.*, b.* from ContractCostChange a left join CBS c on c.CostCode = a.CostCode, Contract b left join Supplier s on s.SupplierCode = b.SupplierCode where a.ContractCode = b.ContractCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and b.Status in (0, 2, 4) and a.ContractChangeCode = (select min(ContractChangeCode) from ContractChange g where g.ContractCode = b.ContractCode) and exists (select * from ContractChange g where g.ContractCode = b.ContractCode)"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "Contract", "ContractMoney"); queryString = "select c.FullCode, a.ChangeMoney as Money, s.SupplierName, a.*, b.* from ContractCostChange a left join CBS c on c.CostCode = a.CostCode, Contract b left join Supplier s on s.SupplierCode = b.SupplierCode, ContractChange g where a.ContractCode = b.ContractCode and b.ContractCode = g.ContractCode and a.ContractChangeCode = g.ContractChangeCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and b.Status not in (3) and g.Status in (0) and a.ChangeMoney <> 0"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "Contract", "ContractChangeMoney"); queryString = "select c.FullCode, s.SupplierName, a.*, b.* from ContractCost a left join CBS c on c.CostCode = a.CostCode, Contract b left join Supplier s on s.SupplierCode = b.SupplierCode where a.ContractCode = b.ContractCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and b.Status in (1, 7)"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "Contract", "ContractApplyMoney"); queryString = "select c.FullCode, s.SupplierName, a.ChangeMoney as Money, a.*, b.* from ContractCostChange a left join CBS c on c.CostCode = a.CostCode, Contract b left join Supplier s on s.SupplierCode = b.SupplierCode, ContractChange g where a.ContractCode = b.ContractCode and b.ContractCode = g.ContractCode and a.ContractChangeCode = g.ContractChangeCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and b.Status not in (3) and g.Status in (1, 2) and a.ChangeMoney <> 0"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddRelationContractRow(this.tbContract, tbSrc, "Contract", "ContractApplyMoney"); queryString = "select c.FullCode, 'Contract' as RecordType, a.CostCode, a.ContractCode, convert(varchar(6), p.PlanningPayDate, 112) as PlanYm, sum(isnull(p.Money, 0)) as PlanMoney from ContractCost a left join CBS c on c.CostCode = a.CostCode, Contract b, ContractCostPlan p where a.ContractCode = b.ContractCode and a.ContractCostCode = p.ContractCostCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and convert(varchar(6), p.PlanningPayDate, 112) between '" + this.StartYm + "' and '" + this.EndYm + "' group by c.FullCode, a.CostCode, a.ContractCode, convert(varchar(6), p.PlanningPayDate, 112)"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; this.AddPlan(tbSrc, ""); foreach (DataRow row in this.tbContract.Rows) { string text2 = ConvertRule.ToString(row["ContractCode"]); if (ConvertRule.ToString(row["AllContractCode"]) != "") { queryString = "select sum(isnull(a.ItemMoney, 0)) from PaymentItem a, Payment b where a.PaymentCode = b.PaymentCode and b.ProjectCode = '" + this.ProjectCode + "' and a.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and b.Status in (1, 2) and b.ContractCode in (" + ConvertRule.ToString(row["AllContractCode"]) + ") and a.CostCode = '" + ConvertRule.ToString(row["CostCode"]) + "'"; row["ContractPay"] = ConvertRule.ToDecimal(agent.ExecuteScalar(queryString)); queryString = "select a.PayoutCode, b.PayoutDate, a.PayoutMoney as Money, '" + text2 + "' as ContractCode, mi.CostCode, c.FullCode from PayoutItem a inner join Payout b on b.PayoutCode = a.PayoutCode, PaymentItem mi left join CBS c on c.CostCode = mi.CostCode, Payment m where a.PaymentItemCode = mi.PaymentItemCode and mi.PaymentCode = m.PaymentCode and m.ProjectCode = '" + this.ProjectCode + "' and mi.CostBudgetSetCode = '" + this.CostBudgetSetCode + "' and m.ContractCode in (" + ConvertRule.ToString(row["AllContractCode"]) + ") and mi.CostCode = '" + ConvertRule.ToString(row["CostCode"]) + "'"; tbSrc = agent.ExecSqlForDataSet(queryString).Tables[0]; row["ContractPayReal"] = MathRule.SumColumn(tbSrc, "Money"); } } CostBudgetPageRule.CostBudgetDtlCalcAllRow(this.tbContract, CostBudgetPageRule.m_DynamicRowType.Contract, null, 0, 0); } finally { agent.Dispose(); } } catch (Exception exception) { throw exception; } }