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