private void GetGridView() { Frm用户默认帐套 f = new Frm用户默认帐套(FrameBaseFunction.ClsBaseDataInfo.sUid); f.ShowDialog(); if (f.DialogResult != DialogResult.Yes) { throw new Exception("取消查询"); } try { DataTable dt = f.dt.Copy(); for (int i = gridView1.Columns.Count - 1; i >= 0; i--) { if (gridView1.Columns[i].Caption.Trim() == "科目编码") { continue; } if (gridView1.Columns[i].Caption.Trim() == "科目") { continue; } gridView1.Columns.RemoveAt(i); } for (int i = gridView1.Bands.Count - 1; i >= 0; i--) { if (gridView1.Bands[i].Caption.Trim() == "科目") { continue; } gridView1.Bands.RemoveAt(i); } string sCol = ""; for (int i = 0; i < dt.Rows.Count; i++) { if (Convert.ToBoolean(dt.Rows[i]["选择"])) { string s帐套号 = dt.Rows[i]["帐套号"].ToString().Trim(); string s帐套名称 = dt.Rows[i]["帐套名称"].ToString().Trim(); string sCaption = "[" + s帐套号 + "]" + s帐套名称; sCol = sCol + "," + s帐套号; SetCol(sCaption, s帐套号, "AccID" + s帐套号, i + 6); } } SetCol("累计", "累计", "累计", dt.Rows.Count + 6); SqlConnection con = new SqlConnection(FrameBaseFunction.ClsBaseDataInfo.sConnString); SqlCommand cmd = new SqlCommand(); SqlTransaction trans; con.Open(); cmd.Connection = con; trans = con.BeginTransaction(); cmd.Transaction = trans; try { //创建临时表 string sTableName = "RepTH" + FrameBaseFunction.ClsBaseDataInfo.sUid; //sSQL = @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + sTableName + "]') AND type in (N'U')) DROP TABLE [dbo].[" + sTableName + "]"; sSQL = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[111111]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[111111]"; sSQL = sSQL.Replace("111111", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); string[] sColList = sCol.Split(','); string s组装字段 = ""; for (int i = 0; i < sColList.Length; i++) { string s = sColList[i].Trim(); if (s == "") { continue; } s组装字段 = s组装字段 + ",期初借方" + s + " decimal(16,2)"; s组装字段 = s组装字段 + ",期初贷方" + s + " decimal(16,2)"; s组装字段 = s组装字段 + ",本期借方" + s + " decimal(16,2)"; s组装字段 = s组装字段 + ",本期贷方" + s + " decimal(16,2)"; s组装字段 = s组装字段 + ",期末借方" + s + " decimal(16,2)"; s组装字段 = s组装字段 + ",期末贷方" + s + " decimal(16,2)"; } s组装字段 = s组装字段 + ",期初借方累计" + " decimal(16,2)"; s组装字段 = s组装字段 + ",期初贷方累计" + " decimal(16,2)"; s组装字段 = s组装字段 + ",本期借方累计" + " decimal(16,2)"; s组装字段 = s组装字段 + ",本期贷方累计" + " decimal(16,2)"; s组装字段 = s组装字段 + ",期末借方累计" + " decimal(16,2)"; s组装字段 = s组装字段 + ",期末贷方累计" + " decimal(16,2)"; sSQL = @" create table RepTH( 类型 varchar(50), 科目编码 varchar(50), 科目 varchar(200) 111111 ) "; sSQL = sSQL.Replace("RepTH", sTableName); sSQL = sSQL.Replace("111111", s组装字段); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); string sYear = lookUpEdit年度.Text.Trim(); for (int i = 0; i < sColList.Length; i++) { string s = sColList[i].Trim(); if (s == "") { continue; } string sAccID = sColList[i].Trim(); string sUFDBName = "UFDATA_" + sAccID + "_" + sYear; int i包含未记账 = 1; if (chk包含未记账.Checked) { i包含未记账 = 0; } int i级次1 = ReturnInt(lookUpEdit级次1.EditValue); int i级次2 = ReturnInt(lookUpEdit级次2.EditValue); int i开始月份 = ReturnInt(lookUpEdit月份1.Text.Trim()); int i结束月份 = ReturnInt(lookUpEdit月份2.Text.Trim()); if (FrameBaseFunction.ClsBaseDataInfo.sERPEdition == "u851a") { sSQL = "exec UFDATA..GL_P_FSEYEB 'aaaaaa','bbbbbb','cccccc', NULL, '我', '1', '1', 0, NULL, NULL, NULL, NULL, 'case when cclass =''资产'' then 1 else case when cclass =''负债'' then 2 else case when cclass =''权益'' then 3 else case when cclass =''成本'' then 4 else 5 end end end end as lx', 'YEB98612'"; } else { if (chk末级科目.Checked) { sSQL = "exec UFDATA..GL_P_FSEYEB 'aaaaaa','bbbbbb','cccccc','1','1',NULL,'我',0,0,1,NULL,NULL,NULL,NULL,'case when cclass =''资产'' then 1 else case when cclass =''负债'' then 2 else case when cclass =''权益'' then 3 else case when cclass =''成本'' then 4 else 5 end end end end as lx','YEB58183'"; } else { sSQL = "exec UFDATA..GL_P_FSEYEB 'aaaaaa','bbbbbb','cccccc','1','1',NULL,'我',dddddd,eeeeee,0,NULL,NULL,NULL,NULL,'case when cclass =''资产'' then 1 else case when cclass =''负债'' then 2 else case when cclass =''权益'' then 3 else case when cclass =''成本'' then 4 else 5 end end end end as lx','YEB58183'"; } } sSQL = sSQL.Replace("aaaaaa", i开始月份.ToString().Trim()); sSQL = sSQL.Replace("bbbbbb", i结束月份.ToString().Trim()); sSQL = sSQL.Replace("cccccc", i包含未记账.ToString().Trim()); sSQL = sSQL.Replace("dddddd", i级次1.ToString().Trim()); sSQL = sSQL.Replace("eeeeee", i级次2.ToString().Trim()); sSQL = sSQL.Replace("UFDATA", sUFDBName); DataTable dtTemp = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; if (dtTemp == null || dtTemp.Rows.Count < 1) { continue; } sSQL = "select * from UFDATA..code order by ccode"; sSQL = sSQL.Replace("UFDATA", sUFDBName); DataTable dt科目 = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; for (int j = 0; j < dtTemp.Rows.Count; j++) { string sCode = dtTemp.Rows[j]["cCode"].ToString().Trim(); decimal d期初借方 = ReturnDecimal(dtTemp.Rows[j]["sbb"], 2); decimal d期初贷方 = ReturnDecimal(dtTemp.Rows[j]["sbb1"], 2); decimal d本期借方 = ReturnDecimal(dtTemp.Rows[j]["smd"], 2); decimal d本期贷方 = ReturnDecimal(dtTemp.Rows[j]["smc"], 2); decimal d期末借方 = ReturnDecimal(dtTemp.Rows[j]["smm"], 2); decimal d期末贷方 = ReturnDecimal(dtTemp.Rows[j]["smm1"], 2); string s科目编码 = dtTemp.Rows[j]["ccode"].ToString().Trim(); string s科目 = dtTemp.Rows[j]["ccode_name"].ToString().Trim(); if (s科目编码.Length > 3 && s科目编码.Substring(0, 2) == "zz") { continue; } if (s科目编码 == "zz" && dtTemp.Rows[j]["lx"].ToString().Trim() == "1") { s科目编码 = "资产小计"; } if (s科目编码 == "zz" && dtTemp.Rows[j]["lx"].ToString().Trim() == "2") { s科目编码 = "负债小计"; } if (s科目编码 == "zz" && dtTemp.Rows[j]["lx"].ToString().Trim() == "3") { s科目编码 = "权益小计"; } if (s科目编码 == "zz" && dtTemp.Rows[j]["lx"].ToString().Trim() == "4") { s科目编码 = "成本小计"; } if (s科目编码 == "zz" && dtTemp.Rows[j]["lx"].ToString().Trim() == "5") { s科目编码 = "损益小计"; } if (s科目编码 == "zz" && dtTemp.Rows[j]["lx"].ToString().Trim() == "9") { s科目编码 = "合计"; } sSQL = @" if exists (select * from RepTH where 科目编码 = 's科目编码') update RepTH set 期初借方aaaaaa = d期初借方, 期初贷方aaaaaa = d期初贷方, 本期借方aaaaaa = d本期借方, 本期贷方aaaaaa = d本期贷方, 期末借方aaaaaa = d期末借方, 期末贷方aaaaaa = d期末贷方 where 科目编码 = 's科目编码' else insert into RepTH(类型,科目编码,科目, 期初借方aaaaaa,期初贷方aaaaaa ,本期借方aaaaaa,本期贷方aaaaaa,期末借方aaaaaa,期末贷方aaaaaa) values('s类型','s科目编码','s科目',d期初借方,d期初贷方,d本期借方, d本期贷方,d期末借方, d期末贷方) "; sSQL = sSQL.Replace("RepTH", sTableName); sSQL = sSQL.Replace("aaaaaa", sColList[i].Trim()); sSQL = sSQL.Replace("s科目编码", s科目编码); sSQL = sSQL.Replace("s科目", s科目); sSQL = sSQL.Replace("s类型", dtTemp.Rows[j]["lx"].ToString().Trim()); //d期初借方 =(decimal) 0.00; if (d期初借方 == 0) { sSQL = sSQL.Replace("d期初借方", "null"); } else { sSQL = sSQL.Replace("d期初借方", d期初借方.ToString()); } if (d期初贷方 == 0) { sSQL = sSQL.Replace("d期初贷方", "null"); } else { sSQL = sSQL.Replace("d期初贷方", d期初贷方.ToString()); } if (d本期借方 == 0) { sSQL = sSQL.Replace("d本期借方", "null"); } else { sSQL = sSQL.Replace("d本期借方", d本期借方.ToString()); } if (d本期贷方 == 0) { sSQL = sSQL.Replace("d本期贷方", "null"); } else { sSQL = sSQL.Replace("d本期贷方", d本期贷方.ToString()); } if (d期末借方 == 0) { sSQL = sSQL.Replace("d期末借方", "null"); } else { sSQL = sSQL.Replace("d期末借方", d期末借方.ToString()); } if (d期末贷方 == 0) { sSQL = sSQL.Replace("d期末贷方", "null"); } else { sSQL = sSQL.Replace("d期末贷方", d期末贷方.ToString()); } cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); } } cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = "select * from RepTH order by 类型,科目编码"; sSQL = sSQL.Replace("RepTH", sTableName); DataTable dtRep = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; for (int i = 0; i < dtRep.Rows.Count; i++) { decimal d1 = 0; decimal d2 = 0; decimal d3 = 0; decimal d4 = 0; decimal d5 = 0; decimal d6 = 0; for (int j = 0; j < dtRep.Columns.Count; j++) { string sColC = dtRep.Columns[j].Caption.Trim(); if (sColC.Length > 4 && sColC.Substring(0, 4) == "期初借方" && sColC != "期初借方累计") { d1 = d1 + ReturnDecimal(dtRep.Rows[i][j]); } if (sColC.Length > 4 && sColC.Substring(0, 4) == "期初贷方" && sColC != "期初贷方累计") { d2 = d2 + ReturnDecimal(dtRep.Rows[i][j]); } if (sColC.Length > 4 && sColC.Substring(0, 4) == "本期借方" && sColC != "本期借方累计") { d3 = d3 + ReturnDecimal(dtRep.Rows[i][j]); } if (sColC.Length > 4 && sColC.Substring(0, 4) == "本期贷方" && sColC != "本期贷方累计") { d4 = d4 + ReturnDecimal(dtRep.Rows[i][j]); } if (sColC.Length > 4 && sColC.Substring(0, 4) == "期末借方" && sColC != "期末借方累计") { d5 = d5 + ReturnDecimal(dtRep.Rows[i][j]); } if (sColC.Length > 4 && sColC.Substring(0, 4) == "期末贷方" && sColC != "期末贷方累计") { d6 = d6 + ReturnDecimal(dtRep.Rows[i][j]); } } if (d1 != 0) { dtRep.Rows[i]["期初借方累计"] = d1; } if (d2 != 0) { dtRep.Rows[i]["期初贷方累计"] = d2; } if (d3 != 0) { dtRep.Rows[i]["本期借方累计"] = d3; } if (d4 != 0) { dtRep.Rows[i]["本期贷方累计"] = d4; } if (d5 != 0) { dtRep.Rows[i]["期末借方累计"] = d5; } if (d6 != 0) { dtRep.Rows[i]["期末贷方累计"] = d6; } } gridControl1.DataSource = dtRep; sSQL = @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + sTableName + "]') AND type in (N'U')) DROP TABLE [dbo].[" + sTableName + "]"; sSQL = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[111111]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[111111]"; sSQL = sSQL.Replace("111111", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ee) { trans.Rollback(); throw new Exception(ee.Message); } } catch (Exception ee) { throw new Exception("加载报表列失败:" + ee.Message); } }
private void GetGridView() { string sErr = ""; Frm用户默认帐套 f = new Frm用户默认帐套(FrameBaseFunction.ClsBaseDataInfo.sUid); f.ShowDialog(); if (f.DialogResult != DialogResult.Yes) { throw new Exception("取消查询"); } try { DataTable dt = f.dt.Copy(); for (int i = gridView1.Columns.Count - 1; i >= 0; i--) { if (gridView1.Columns[i].Caption.Trim() == "帐套号") { continue; } if (gridView1.Columns[i].Caption.Trim() == "帐套") { continue; } if (gridView1.Columns[i].Caption.Trim() == "客户/供应商编码") { continue; } if (gridView1.Columns[i].Caption.Trim() == "客户/供应商") { continue; } if (gridView1.Columns[i].Caption.Trim() == "总额") { continue; } if (gridView1.Columns[i].Caption.Trim() == "科目") { continue; } gridView1.Columns.RemoveAt(i); } SqlConnection con = new SqlConnection(FrameBaseFunction.ClsBaseDataInfo.sConnString); SqlCommand cmd = new SqlCommand(); SqlTransaction trans; con.Open(); cmd.Connection = con; trans = con.BeginTransaction(); cmd.Transaction = trans; try { #region 列表字段 sSQL = "select * from dbo._LookUpDate where iType = 1 order by iID"; DataTable dtCol = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; int iLow = 0; string s组装字段 = ""; string s组装字段sum = ""; //string s报表字段 = ""; for (int i = 0; i < dtCol.Rows.Count; i++) { string sColName = "";; if (i == 0) { sColName = " ~ " + dtCol.Rows[i]["iText"].ToString().Trim(); iLow = ReturnInt(dtCol.Rows[i]["iText"]); //s报表字段 = s报表字段 + "aaaa" + i.ToString().Trim() + " <= " + dtCol.Rows[i]["iText"].ToString().Trim(); AddGridCol(sColName, sColName, "d" + i.ToString(), i + 10); s组装字段 = s组装字段 + "," + "d" + i.ToString() + " money"; s组装字段sum = s组装字段sum + ",sum(" + "d" + i.ToString() + ") as " + "d" + i.ToString() + ""; } else { sColName = iLow.ToString().Trim() + " ~ " + dtCol.Rows[i]["iText"].ToString().Trim(); iLow = ReturnInt(dtCol.Rows[i]["iText"]); AddGridCol(sColName, sColName, "d" + i.ToString(), i + 10); s组装字段 = s组装字段 + "," + "d" + i.ToString() + " money"; s组装字段sum = s组装字段sum + ",sum(" + "d" + i.ToString() + ") as " + "d" + i.ToString() + ""; } if (i != 0 && i == dtCol.Rows.Count - 1) { sColName = dtCol.Rows[i]["iText"].ToString().Trim() + " ~ "; AddGridCol(sColName, sColName, "d" + (i + 1).ToString(), i + 11); s组装字段 = s组装字段 + "," + "d" + (i + 1).ToString() + " money"; s组装字段sum = s组装字段sum + ",sum(" + "d" + (i + 1).ToString() + ") as " + "d" + (i + 1).ToString() + ""; } } //创建临时表 string sPublicTableName = "RepPublicCusTH" + FrameBaseFunction.ClsBaseDataInfo.sUid; //sSQL = @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + sPublicTableName + "]') AND type in (N'U')) DROP TABLE [dbo].[" + sPublicTableName + "]"; sSQL = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[111111]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[111111]"; sSQL = sSQL.Replace("111111", sPublicTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = @" create table RepTH( 客户编码 varchar(20), 客户 varchar(200), 科目 varchar(200), 帐套客户编号 varchar(20), 帐套号 varchar(20), 帐套 varchar(200), 总额 money, 单据日期 datetime ) "; sSQL = sSQL.Replace("RepTH", sPublicTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); string sTableName = "RepCusTH" + FrameBaseFunction.ClsBaseDataInfo.sUid; //sSQL = @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + sTableName + "]') AND type in (N'U')) DROP TABLE [dbo].[" + sTableName + "]"; sSQL = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[111111]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[111111]"; sSQL = sSQL.Replace("111111", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = @" create table RepTH( 客户编码 varchar(20), 客户 varchar(200), 科目 varchar(200), 帐套号 varchar(20), 帐套 varchar(200), 来源客户编码 varchar(20), 来源帐套号 varchar(20), 总额 money 111111 ) "; sSQL = sSQL.Replace("RepTH", sTableName); sSQL = sSQL.Replace("111111", s组装字段); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); #endregion DataTable dt应收账款凭证 = null; for (int i = 0; i < f.dt.Rows.Count; i++) { if (!Convert.ToBoolean(dt.Rows[i]["选择"])) { continue; } string sAccID = dt.Rows[i]["帐套号"].ToString().Trim(); sSQL = @" SELECT distinct A.cAcc_Id as 帐套号,A.cAcc_Name as 帐套名称,p.iYear as 年度 FROM UFSystem.dbo.UA_Account A inner join UFSystem.dbo.UA_period P on A.cAcc_Id=P.cAcc_Id AND (P.bIsDelete=0 OR P.bIsDelete IS NULL) WHERE 1=1 and a.cAcc_Id = '111111' order by A.cAcc_Id,p.iYear desc "; sSQL = sSQL.Replace("111111", sAccID); DataTable dtAccYear = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; if (dtAccYear == null || dtAccYear.Rows.Count < 1) { sErr = sErr + "帐套" + sAccID + "不存在\n"; continue; } string sDBName = "UFDATA_" + sAccID + "_" + dtAccYear.Rows[0]["年度"].ToString().Trim(); // or c.ccode like '2131%' if (!chk包含未记账.Checked) { sSQL = @" insert into RepTH(客户编码,客户,科目,帐套号,帐套,总额,来源客户编码,来源帐套号) Select case when isnull(a.cCusDefine1,'') = '' then a.cCusCode else a.cCusDefine1 end as 客户编码,a.cCusAbbName as 客户 ,c.ccode as 科目 ,case when isnull(a.cCusDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cCusDefine1,'') = '' then '333333' else '公共' end as 帐套 ,isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) as 应收账款 ,a.cCusCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Customer a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accass c ON b.cCode=c.cCode) ON a.cCusCode=c.ccus_id where iPeriod <= 111111 and not c.cCode is Null and (c.ccode like '1131%' or c.ccode like '1133%' ) Group By a.cCusCode,a.cCusAbbName,a.cCusDefine1,c.ccode having isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) <> 0 order by a.cCusCode,a.cCusAbbName insert into RepTH(客户编码,客户,科目,帐套号,帐套,总额,来源客户编码,来源帐套号) Select case when isnull(a.cCusDefine1,'') = '' then a.cCusCode else a.cCusDefine1 end as 客户编码,a.cCusAbbName as 客户 ,c.ccode as 科目 ,case when isnull(a.cCusDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cCusDefine1,'') = '' then '333333' else '公共' end as 帐套 ,-isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) as 应收账款 ,a.cCusCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Customer a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accass c ON b.cCode=c.cCode) ON a.cCusCode=c.ccus_id where iPeriod <= 111111 and not c.cCode is Null and (c.ccode like '2131%') Group By a.cCusCode,a.cCusAbbName,a.cCusDefine1,c.ccode having isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'借' then me else -me end) else 0 end),0) <> 0 order by a.cCusCode,a.cCusAbbName insert into RepTH(客户编码,客户,科目,帐套号,帐套,总额,来源客户编码,来源帐套号) Select case when isnull(a.cVenDefine1,'') = '' then a.cVenCode else a.cVenDefine1 end as 客户编码,a.cVenAbbName as 客户 ,c.ccode as 科目 ,case when isnull(a.cVenDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cVenDefine1,'') = '' then '333333' else '公共' end as 帐套 ,isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) as 应收账款 ,a.cVenCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Vendor a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accass c ON b.cCode=c.cCode) ON a.cVenCode=c.csup_id where iPeriod <= 111111 and not c.cCode is Null and (c.ccode like '1151%') Group By a.cVenCode,a.cVenAbbName,a.cVenDefine1,c.ccode having isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) <> 0 order by a.cVenCode,a.cVenAbbName "; sSQL = sSQL.Replace("111111", dateEdit分析日期.DateTime.Month.ToString()); sSQL = sSQL.Replace("222222", sAccID); sSQL = sSQL.Replace("333333", dt.Rows[i]["帐套名称"].ToString().Trim()); sSQL = sSQL.Replace("UFDATA", sDBName); sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); } else { sSQL = @" insert into RepTH(客户编码,客户,科目,帐套号,帐套,总额,来源客户编码,来源帐套号) select 客户编码,客户,科目,帐套号,帐套,sum(应收账款) as 应收账款,来源客户编码,来源帐套号 from ( Select case when isnull(a.cCusDefine1,'') = '' then a.cCusCode else a.cCusDefine1 end as 客户编码,a.cCusAbbName as 客户 ,c.ccode as 科目 ,case when isnull(a.cCusDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cCusDefine1,'') = '' then '333333' else '公共' end as 帐套 ,(Sum(case when iperiod<1 then md-mc else 0 end) + Sum(case when iperiod>=1 then md else 0 end) - Sum(case when iperiod>=1 then mc else 0 end)) as 应收账款 ,a.cCusCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Customer a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accvouch c ON b.cCode=c.cCode) ON a.cCusCode=c.ccus_id where iperiod<= 111111 and not c.cCode is Null and (c.ccode like '1131%' or c.ccode like '1133%') and ibook=0 and (iflag is null or iflag=2) and iperiod>=1 Group By b.cCode,b.ccode_name,a.cCusCode,a.cCusAbbName ,a.cCusDefine1, c.ccode union all Select case when isnull(a.cCusDefine1,'') = '' then a.cCusCode else a.cCusDefine1 end as 客户编码,a.cCusAbbName as 客户 ,c.ccode as 科目 ,case when isnull(a.cCusDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cCusDefine1,'') = '' then '333333' else '公共' end as 帐套 ,isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) as 应收账款 ,a.cCusCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Customer a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accass c ON b.cCode=c.cCode) ON a.cCusCode=c.ccus_id where iPeriod <= 111111 and not c.cCode is Null and (c.ccode like '1131%' or c.ccode like '1133%') Group By a.cCusCode,a.cCusAbbName,a.cCusDefine1,c.ccode )a group by 客户编码,客户,帐套号,帐套,来源客户编码,来源帐套号,科目 having sum(应收账款) <> 0 order by 客户编码 insert into RepTH(客户编码,客户,科目,帐套号,帐套,总额,来源客户编码,来源帐套号) select 客户编码,客户,科目,帐套号,帐套,sum(应收账款) as 应收账款,来源客户编码,来源帐套号 from ( Select case when isnull(a.cCusDefine1,'') = '' then a.cCusCode else a.cCusDefine1 end as 客户编码,a.cCusAbbName as 客户 ,c.ccode as 科目 ,case when isnull(a.cCusDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cCusDefine1,'') = '' then '333333' else '公共' end as 帐套 ,-(Sum(case when iperiod<1 then md-mc else 0 end) + Sum(case when iperiod>=1 then md else 0 end) - Sum(case when iperiod>=1 then mc else 0 end)) as 应收账款 ,a.cCusCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Customer a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accvouch c ON b.cCode=c.cCode) ON a.cCusCode=c.ccus_id where iperiod<= 111111 and not c.cCode is Null and ( c.ccode like '2131%') and ibook=0 and (iflag is null or iflag=2) and iperiod>=1 Group By b.cCode,b.ccode_name,a.cCusCode,a.cCusAbbName ,a.cCusDefine1, c.ccode union all Select case when isnull(a.cCusDefine1,'') = '' then a.cCusCode else a.cCusDefine1 end as 客户编码,a.cCusAbbName as 客户 ,c.ccode as 科目 ,case when isnull(a.cCusDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cCusDefine1,'') = '' then '333333' else '公共' end as 帐套 ,-isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) as 应收账款 ,a.cCusCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Customer a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accass c ON b.cCode=c.cCode) ON a.cCusCode=c.ccus_id where iPeriod <= 111111 and not c.cCode is Null and (c.ccode like '2131%') Group By a.cCusCode,a.cCusAbbName,a.cCusDefine1,c.ccode )a group by 客户编码,客户,帐套号,帐套,来源客户编码,来源帐套号,科目 having sum(应收账款) <> 0 order by 客户编码 insert into RepTH(客户编码,客户,科目,帐套号,帐套,总额,来源客户编码,来源帐套号) select 供应商编码,供应商,科目,帐套号,帐套,sum(预付账款) as 应收账款,来源客户编码,来源帐套号 from ( Select case when isnull(a.cVenDefine1,'') = '' then a.cVenCode else a.cVenDefine1 end as 供应商编码,a.cVenAbbName as 供应商 ,c.ccode as 科目 ,case when isnull(a.cVenDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cVenDefine1,'') = '' then '333333' else '公共' end as 帐套 ,isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) as 预付账款 ,a.cVenCode as 来源客户编码,'222222' as 来源帐套号 from UFDATA..Vendor a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accass c ON b.cCode=c.cCode) ON a.cVenCode=c.csup_id where iPeriod <= 111111 and not c.cCode is Null and (c.ccode like '1151%') Group By a.cVenCode,a.cVenAbbName,a.cVenDefine1,c.ccode having isnull(Sum(case when iperiod=111111 then (case when cendd_c<>'贷' then me else -me end) else 0 end),0) <> 0 union all Select case when isnull(a.cVenDefine1,'') = '' then a.cVenCode else a.cVenDefine1 end as 供应商编码,a.cVenAbbName as 供应商 ,c.ccode as 科目 ,case when isnull(a.cVenDefine1,'') = '' then '222222' else '公共' end as 帐套号 ,case when isnull(a.cVenDefine1,'') = '' then '333333' else '公共' end as 帐套 ,(Sum(case when iperiod<1 then md-mc else 0 end) + Sum(case when iperiod>=1 then md else 0 end) - Sum(case when iperiod>=1 then mc else 0 end)) as 预付账款 ,a.cVenCode as 来源供应商编码,'222222' as 来源帐套号 from UFDATA..Vendor a right JOIN (UFDATA..Code b INNER JOIN UFDATA..GL_accvouch c ON b.cCode=c.cCode) ON a.cVenCode=c.csup_id where iperiod<= 111111 and not c.cCode is Null and (c.ccode like '1151%') and ibook=0 and (iflag is null or iflag=2) and iperiod>=1 Group By a.cVenCode,a.cVenAbbName,a.cVenDefine1,c.ccode )a group by 供应商编码,供应商,帐套号,帐套,来源客户编码,来源帐套号,科目 having sum(预付账款) <> 0 order by 供应商编码 "; sSQL = sSQL.Replace("111111", dateEdit分析日期.DateTime.Month.ToString()); sSQL = sSQL.Replace("222222", sAccID); sSQL = sSQL.Replace("333333", dt.Rows[i]["帐套名称"].ToString().Trim()); sSQL = sSQL.Replace("UFDATA", sDBName); sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); } } sSQL = "select * from RepTH order by 帐套号,客户编码"; sSQL = sSQL.Replace("RepTH", sTableName); dt应收账款凭证 = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; for (int j = 0; j < dt应收账款凭证.Rows.Count; j++) { string sCustomID = dt应收账款凭证.Rows[j]["客户编码"].ToString().Trim(); if (sCustomID == "1001") { } decimal d客户总额 = ReturnDecimal(dt应收账款凭证.Rows[j]["总额"]); string sAccID = dt应收账款凭证.Rows[j]["来源帐套号"].ToString().Trim(); sSQL = @" SELECT distinct A.cAcc_Id as 帐套号,A.cAcc_Name as 帐套名称,p.iYear as 年度 FROM UFSystem.dbo.UA_Account A inner join UFSystem.dbo.UA_period P on A.cAcc_Id=P.cAcc_Id AND (P.bIsDelete=0 OR P.bIsDelete IS NULL) WHERE 1=1 and a.cAcc_Id = '003' order by A.cAcc_Id,p.iYear desc "; sSQL = sSQL.Replace("003", sAccID); DataTable dtAccYear = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; for (int k = 0; k < dtAccYear.Rows.Count; k++) { string s帐套 = sAccID; // dt应收账款凭证.Rows[j]["帐套号"].ToString().Trim(); string sUFDB = "UFDATA_" + sAccID + "_" + dtAccYear.Rows[k]["年度"].ToString().Trim(); string s科目 = dt应收账款凭证.Rows[j]["科目"].ToString().Trim(); #region 非公共客户,供应商 //if (s帐套 != "公共") //{ sSQL = @" select * from UFDATA_003_2013..GL_accvouch where 1=1 and (isnull(md,0) <> 0 or isnull(mc,0) <> 0) and (ccode = '222222') and (isnull(ccus_id,'') = '111111' or isnull(csup_id,'') = '111111') order by dbill_date desc "; sSQL = sSQL.Replace("UFDATA_003_2013", sUFDB); sSQL = sSQL.Replace("111111", sCustomID); sSQL = sSQL.Replace("222222", s科目); if (k != dtAccYear.Rows.Count - 1) { sSQL = sSQL.Replace("1=1", " 1=1 and iperiod <> 0 "); } if (!chk包含未记账.Checked) { sSQL = sSQL.Replace("1=1", "1=1 and isnull(cbook,'') <> '' "); } DataTable dt应收款明细 = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; for (int l = 0; l < dt应收款明细.Rows.Count; l++) { DateTime d单据日期 = Convert.ToDateTime(dt应收款明细.Rows[l]["dbill_date"]); decimal d金额 = ReturnDecimal(dt应收款明细.Rows[l]["md"]); decimal d = ReturnDecimal(dt应收款明细.Rows[l]["mc"]); if (s科目.Substring(0, 4) == "2131") { d金额 = ReturnDecimal(dt应收款明细.Rows[l]["mc"]); d = ReturnDecimal(dt应收款明细.Rows[l]["md"]); if (d < 0) { d金额 = d金额 - d; } } else { if (d < 0) { d金额 = d金额 - d; } } TimeSpan ts = dateEdit分析日期.DateTime - d单据日期; int iDays = ts.Days; for (int ii = 0; ii < gridView1.Columns.Count; ii++) { string sCaption = gridView1.Columns[ii].Caption.Trim(); string sColName = gridView1.Columns[ii].FieldName.Trim(); if (sColName.Substring(0, 1) == "d") { string[] sList = sCaption.Split('~'); if (sList[0].Trim() == "") { sList[0] = "0"; } if (sList[1].Trim() == "") { sList[1] = "65000"; } if (iDays >= ReturnInt(sList[0]) && iDays < ReturnInt(sList[1])) { sSQL = @" update RepTH set 111111 = isnull(111111,0) + 222222 where isnull(客户编码,'') = '333333' and 来源帐套号 = '444444' and 科目 = '555555' "; sSQL = sSQL.Replace("RepTH", sTableName); sSQL = sSQL.Replace("111111", sColName); sSQL = sSQL.Replace("333333", sCustomID); sSQL = sSQL.Replace("444444", sAccID); sSQL = sSQL.Replace("555555", s科目); if (d客户总额 > d金额) { sSQL = sSQL.Replace("222222", d金额.ToString()); } else { sSQL = sSQL.Replace("222222", (d客户总额).ToString()); } cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); d客户总额 = d客户总额 - d金额; if (d客户总额 <= 0) { break; } } } } if (d客户总额 <= 0) { break; } } //} #endregion if (d客户总额 <= 0) { break; } } } sSQL = @" insert into RepTH select null,null,科目 + '小计' as 科目,帐套号,null,null,null,SUM(总额) as 总额 111111 from RepTH where 科目 not like '%小计%' and 科目 not like '%合计%' group by 科目,帐套号 "; sSQL = sSQL.Replace("111111", s组装字段sum); sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = @" insert into RepTH select null,null,科目 + '科目','合计' as 科目,null,null,null,SUM(总额) as 总额 111111 from RepTH where 科目 not like '%小计%' and 科目 not like '%合计%' group by 科目 "; sSQL = sSQL.Replace("111111", s组装字段sum); sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = @" insert into RepTH select null,null,null,帐套号+'合计' as 帐套号,null,null,null,SUM(总额) as 总额 111111 from RepTH where 科目 not like '%小计%' and 科目 not like '%合计%' and 帐套号 not like '%合计%' group by 帐套号,帐套 "; sSQL = sSQL.Replace("111111", s组装字段sum); sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = @" insert into RepTH select null,null,'合计','合计',null,null,null,SUM(总额) as 总额 111111 from RepTH where 科目 not like '%小计%' and 帐套号 not like '%合计%' "; sSQL = sSQL.Replace("111111", s组装字段sum); sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); string sTemp = "总额 "; for (int i = 0; i < dtCol.Rows.Count; i++) { sTemp = sTemp + " - isnull(d" + i.ToString() + ",0)"; } sTemp = "d" + dtCol.Rows.Count.ToString() + " = case when " + sTemp + " <> 0 then " + sTemp + " end "; sSQL = @" update RepTH set 111111 "; sSQL = sSQL.Replace("111111", sTemp); sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = "select * from RepTH order by 帐套号,科目,客户编码"; sSQL = sSQL.Replace("RepTH", sTableName); DataTable dtGrid = SqlHelper.ExecuteDataset(trans, CommandType.Text, sSQL).Tables[0]; gridControl1.DataSource = dtGrid; sSQL = @" drop table RepTH "; sSQL = sSQL.Replace("RepTH", sTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); sSQL = @" drop table RepTH "; sSQL = sSQL.Replace("RepTH", sPublicTableName); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ee) { trans.Rollback(); throw new Exception(ee.Message); } } catch (Exception ee) { throw new Exception("加载报表列失败:" + ee.Message); } }