K3BOS万能报表工具包-电子表格报表中的ACCT公式逻辑
参考文档https://wenku.my7c.com/link/s/MkC3T
K3BOS万能报表工具包中配置报表的易用性较差，但是又希望能用到里面的公式；之前有客户ACCT函数的取值逻辑到底是什么样的呢，如果知道其取数逻辑，转成SQL报表的话，二开起来就方便多了；
因此制作了这个辅助工具；方便报表开发人员理解、开发sql报表
步骤一:
首先执行下面的这段脚本
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE name ='TAcctFunctionItem' AND xtype='u')
begin
drop table TAcctFunctionItem
end
Go
Create Table TAcctFunctionItem(FID int identity(1,1),FNumber nvarchar(20),FName nvarchar(20),FRule nvarchar(10))
--不变N
--ITEM_AMTDR, ITEM_AMTCR, ITEM_QTY_AMTDR, ITEM_QTY_AMTCR, ITEM_PNL, ITEM_BUDGETDR, ITEM_BUDGETCR, ITEM_BUDGET_YT, ITEM_BASE_AMTDR, ITEM_BASE_AMTCR, ITEM_BASE_PNL
--强制修改结束期间为起始期间S
--ITEM_BEGBAL, ITEM_BEGBALDR, ITEM_BEGBALCR, ITEM_BEGBALABS, ITEM_QTY_BEGBAL, ITEM_BASE_BEGBAL, ITEM_BASE_BEGBALDR, ITEM_BASE_BEGBALCR, ITEM_BASE_BEGBALABS
--强制修改起始期间为结束期间E
--ITEM_ENDBAL, ITEM_ENDBALDR, ITEM_ENDBALCR, ITEM_ENDBALABS, ITEM_QTY_ENDBAL, ITEM_BASE_ENDBAL, ITEM_BASE_ENDBALDR, ITEM_BASE_ENDBALCR, ITEM_BASE_ENDBALABS, ITEM_BUDGET, ITEM_MINBUDGET
--强制修改起始期间为结束期间E
--ITEM_YTDDR, ITEM_YTDCR, ITEM_PNLYTD, ITEM_QTY_YTDDR, ITEM_QTY_YTDCR, ITEM_BASE_YTDDR, ITEM_BASE_YTDCR, ITEM_BASE_PNLYTD
--必须按顺序插入
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BEGBAL','期初余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BEGBALDR','期初借方余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BEGBALCR','期初贷方余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BEGBALABS','期初绝对余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_AMTDR','借方发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_AMTCR','贷方发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_YTDDR','借方累计发生额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_YTDCR','贷方累计发生额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_ENDBAL','期末余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_ENDBALDR','借方期末余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_ENDBALCR','贷方期末余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_ENDBALABS','期末绝对余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_PNL','损益表本期实际发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_PNLYTD','损益表本年实际发生额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BUDGET','预算','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_MINBUDGET','最小预算','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_BEGBAL','折合本位币期初余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_BEGBALDR','折合本位币期初借方余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_BEGBALCR','折合本位币期初贷方余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_BEGBALABS','折合本位币期初绝对余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_AMTDR','折合本位币借方发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_AMTCR','折合本位币贷方发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_YTDDR','折合本位币借方累计发生额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_YTDCR','折合本位币贷方累计发生额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_ENDBAL','折合本位币期末余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_ENDBALDR','折合本位币期末借方余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_ENDBALCR','折合本位币期末贷方余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_ENDBALABS','折合本位币期末绝对余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_PNL','折合本位币损益表本期数','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_PNLYTD','折合本位币损益表本年累计数','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BASE_BUDGET','折合本位币预算数',''
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_QTY_BEGBAL','数量期初余额','S'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_QTY_AMTDR','数量借方发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_QTY_AMTCR','数量贷方发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_QTY_YTDDR','数量借方累计发生额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_QTY_YTDCR','数量贷方累计发生额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_QTY_ENDBAL','数量期末余额','E'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_ACCUMU','累计数',''
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BUDGET_YT','预算累计数','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BUDGETCR','本期最高预算贷方发生额','N'
Insert into TAcctFunctionItem(FNumber,FName,FRule)
select 'ITEM_BUDGETDR','本期最高预算借方发生额','N'
这个表包含了ACCT函数支持的查询类型：（ITEM_BASE_BUDGET、ITEM_ACCUMU这2个不支持）
第二步：请仔细研读下面这段脚本，这段脚本为ACCT函数的取数逻辑，我将其改造为针对单一个科目、核算项目；
--Acct公式取数 逻辑分析
declare @iStartYear as int --报表过滤范围：起始年份
declare @iStartPeriod as int--报表过滤范围：起始期间
declare @iEndYear as int--报表过滤范围：结束年份
declare @iEndPeriod as int--报表过滤范围：结束期间
declare @msg as nvarchar(50)--返回错误消息
declare @AccItemTypeNumber as nvarchar(20)--查询类别
declare @AcctNumber nvarchar(50) --科目Number:SELECT * FROM t_Account
declare @CurrencyID int
declare @clsName as nvarchar(10) --核算项目类别：物料、供应商等
declare @clsItemNumber as nvarchar(50) --要查的核算项目编码：物料编码等
declare @ContainVoucher as int --是否包含未过账凭证 0否，1是
--过滤条件设置
set @iStartYear = 2013 --2014
set @iStartPeriod = 1
set @iEndYear = 2013 --2014
set @iEndPeriod = 1 --2
set @AccItemTypeNumber = N'ITEM_BASE_ENDBALDR'--ITEM_BASE_ENDBALDR,ITEM_AMTCR
set @AcctNumber = '1403'--原材料
set @CurrencyID = 0 --本位币
set @clsName = '物料'
set @clsItemNumber = '1.01.000.00000'
set @ContainVoucher = 1--0
declare @iCurrYear int
declare @iCurrPeriod int
--获得当前年份
select @iCurrYear = Convert(int,Fvalue) from t_SystemProfile where FKey='CurrentYear' and FCategory ='GL'
--获取当前期间
select @iCurrPeriod = Convert(int,Fvalue) from t_SystemProfile where FKey='CurrentPeriod' and FCategory ='GL'
--获取最大期间值
declare @v as int
declare @MaxPeriod int
select @v = Convert(int,Fvalue) from t_SystemProfile where FKey='AdjustPeriodCheck' and FCategory ='GL'
if @v = 1
begin
select @MaxPeriod = Convert(int,Fvalue)from t_SystemProfile where FKey='PeriodCount' and FCategory ='GL'
end
else
begin
SELECT @MaxPeriod = isnull(max(fnumber),0) FROM t_GlAdjustPeriod
end
if @iStartPeriod<=0 or @iStartPeriod > @MaxPeriod or @iEndPeriod<=0 or @iEndPeriod > @MaxPeriod
begin
set @msg=N'期间设置错误，期间值必须在[1,'+ Convert(nvarchar(2),@MaxPeriod) +N']内'
raiserror(@msg,18,18)
return
end
if @iEndPeriod<@iStartPeriod
begin
raiserror('开始期间不能大于结束期间',18,18)
return
end
if @iStartYear <> @iEndYear
begin
raiserror('起始年份和结束年份不是同一年',18,18)
return
end
declare @AccItemTypeID int--查询类别ID
declare @AccItemTypeRule nvarchar(10)--查询类别规则
Select @AccItemTypeID = FID,@AccItemTypeRule = FRule from TAcctFunctionItem where FNumber = @AccItemTypeNumber
if isnull(@AccItemTypeID,0) = 0
begin
raiserror('ACCT函数查询类别参数输入错误，请在TAcctFunctionItem表中确认',18,18)
return
end
--N保持原样
if(@AccItemTypeRule='S')
begin
set @iEndPeriod = @iStartPeriod
end
if(@AccItemTypeRule='E')
begin
set @iStartPeriod = @iEndPeriod
end
declare @AccID int
declare @AccParentID int
declare @AccLevel int
declare @AccRootID int
declare @AccFDC int
declare @AccDetailID int
declare @AccDetial int
--核算项目查找
SELECT @AccID = FAccountID,@AccParentID = FParentID,@AccLevel = FLevel,@AccRootID = FRootID,@AccFDC =FDC,@AccDetailID= FDetailID, @AccDetial=FDetail
FROM t_Account Where FNumber = @AcctNumber
if isnull(@AccID,0) = 0
begin
raiserror('科目编号不存在，请在t_Account表中确认',18,18)
return
end
--核算项目分析
--colCls As New Collection, colStartItem As New Collection, colEndItem As New Collection
declare @ItemClassID int
select @ItemClassID = FItemClassID from t_ItemClass where fname = @clsName
if isnull(@ItemClassID,0) = 0
begin
raiserror('核算项目不存在，请在t_ItemClass表中确认',18,18)
return
end
declare @iItemID int
declare @iItemLevel int
declare @iItemParentID int
Select @iItemID = FItemID,@iItemLevel = FLevel,@iItemParentID = FParentID From t_Item Where FNumber = @clsItemNumber And FItemClassID = @ItemClassID
if @AccDetial=1
begin
if not exists(Select * From t_ItemDetailV Where FDetailID = @AccDetailID And FItemID = -1 And FItemClassID = @ItemClassID)
begin
raiserror('#科目无此核算项目，请在t_ItemDetailV表中确认',18,18)
return
end
end
declare @bEndBalDCByAcct as int --取余额的设置方式
select @bEndBalDCByAcct = Convert(int,Fvalue) from t_SystemProfile where FKey='EndBalDCFormat' and FCategory ='GL'
declare @strCriteria nvarchar(300)
declare @groupby nvarchar(100)
declare @strSql nvarchar(4000)
declare @operatorSign as nvarchar(4)
--凭证、凭证调整数据
declare @strTableB as nvarchar(1000)
declare @strSql2 nvarchar(4000)
declare @bIncludeUnpost as int
declare @bIncludeUnpostAdjust as int
if not (@ContainVoucher = 1
and ((@iStartYear < @iCurrYear) or (@iStartYear = @iCurrYear And @iEndPeriod<@iCurrPeriod)) And exists(select 1 from TAcctFunctionItem where FNumber = @AccItemTypeNumber And FNumber in('ITEM_ENDBALDR','ITEM_ENDBALCR','ITEM_BASE_ENDBALDR','ITEM_BASE_ENDBALCR')))
begin
set @strCriteria = N't_ItemDetail d,t_Account a where b.FDetailID=d.FDetailID And b.FAccountID=a.FAccountID and a.FRootID=' + Convert(nvarchar(10),@AccRootID)
set @strCriteria = '(Select FItemID From t_Item where FDetail=1 AND FItemClassID=' + Convert(nvarchar(10),@ItemClassID) +' And FNumber='''+ Convert(nvarchar(50),@clsItemNumber) +''') e,' + @strCriteria
set @strCriteria = @strCriteria + N' And d.F'+Convert(nvarchar(10),@ItemClassID)+ '=e.FItemID'
set @strCriteria = @strCriteria + N' And b.FPeriod>=' + Convert(nvarchar(10),@iStartPeriod) + N' And b.FPeriod<='+ Convert(nvarchar(10),@iEndPeriod)
set @strCriteria = @strCriteria + N' And a.FNumber = ''' + Convert(nvarchar(10),@AcctNumber) + ''''
set @strCriteria = @strCriteria + N' And b.FCurrencyID= ' + Convert(nvarchar(10),@CurrencyID)
set @groupby = N' Group by d.F' +Convert(nvarchar(10),@ItemClassID)
select @strSql = case @AccItemTypeNumber
when 'ITEM_BUDGET' then 'Select' + (Select case @CurrencyID when 0 then 'Sum(b.FBudget) FBudget' else 'Sum(b.FBudgetFor) FBudgetFor' end) + ' from t_Budget b '
when 'ITEM_MINBUDGET' then 'Select'+(Select case @CurrencyID when 0 then 'Sum(b.FMinBudget) FMinBudget' else 'Sum(b.FMinBudgetFor) FMinBudgetFor' end) +' from t_Budget b '
when 'ITEM_BUDGETDR' then 'Select' +(Select case @CurrencyID when 0 then 'Sum(b.FBudgetDebit) FBudget' else 'Sum(b.FBudgetCreditFor) FBudgetFor' end) +' from t_Budget b '
when 'ITEM_BUDGETCR' then 'Select' +(Select case @CurrencyID when 0 then ' Sum(b.FBudgetCredit) FBudget' else 'Sum(b.FBudgetCreditFor) FBudgetFor' end)+' from t_Budget b '
when 'ITEM_PNL' then 'Select Sum(b.FAmountFor) FAmountFor from t_ProfitAndLoss b'
when 'ITEM_BASE_PNL' then 'Select Sum(b.FAmount) FAmount from t_ProfitAndLoss b '
when 'ITEM_PNLYTD' then 'Select Sum(b.FYtdAmountFor) FYtdAmountFor from t_ProfitAndLoss b '
when 'ITEM_BASE_PNLYTD' then 'Select Sum(b.FYtdAmount) FYtdAmount from t_ProfitAndLoss b'
when 'ITEM_QTY_BEGBAL' then 'Select Sum(b.FBeginQty) FBeginQty from t_QuantityBalance b '
when 'ITEM_QTY_AMTDR' then 'Select Sum(b.FDebitQty) FDebitQty from t_QuantityBalance b '
when 'ITEM_QTY_AMTCR' then 'Select Sum(b.FCreditQty) FCreditQty from t_QuantityBalance b'
when 'ITEM_QTY_YTDDR' then 'Select Sum(b.FYtdDebitQty) FYtdDebitQty from t_QuantityBalance b '
when 'ITEM_QTY_YTDCR' then 'Select Sum(b.FYtdCreditQty) FYtdCreditQty from t_QuantityBalance b'
when 'ITEM_QTY_ENDBAL' then 'Select Sum(b.FEndQty) FEndQty from t_QuantityBalance b '
--
when 'ITEM_BEGBALDR' then 'Select Sum(x.FBeginBalanceFor) FBeginBalanceFor From (Select Sum(b.FBeginBalanceFor) FBeginBalanceFor from t_Balance b '
when 'ITEM_BEGBALCR' then 'Select Sum(x.FBeginBalanceFor) FBeginBalanceFor From (Select Sum(b.FBeginBalanceFor) FBeginBalanceFor from t_Balance b '
when 'ITEM_BASE_BEGBALDR' then 'Select Sum(x.FBeginBalance) FBeginBalance From (Select Sum(b.FBeginBalance) FBeginBalance from t_Balance b '
when 'ITEM_BASE_BEGBALCR' then 'Select Sum(x.FBeginBalance) FBeginBalance From (Select Sum(b.FBeginBalance) FBeginBalance from t_Balance b '
when 'ITEM_ENDBALDR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select Sum(b.FBeginBalanceFor) FBeginBalanceFor from t_Balance b '
else 'Select Sum(x.FEndBalanceFor) FEndBalanceFor From (Select Sum(b.FEndBalanceFor) FEndBalanceFor from t_Balance b' end
when 'ITEM_ENDBALCR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select Sum(b.FBeginBalanceFor) FBeginBalanceFor from t_Balance b '
else 'Select Sum(x.FEndBalanceFor) FEndBalanceFor From (Select Sum(b.FEndBalanceFor) FEndBalanceFor from t_Balance b' end
when 'ITEM_BASE_ENDBALDR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select Sum(b.FBeginBalance) FBeginBalance from t_Balance b '
else 'Select Sum(x.FEndBalance) FEndBalance From (Select Sum(b.FEndBalance) FEndBalance from t_Balance b' end
when 'ITEM_BASE_ENDBALCR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select Sum(b.FBeginBalance) FBeginBalance from t_Balance b '
else 'Select Sum(x.FEndBalance) FEndBalance From (Select Sum(b.FEndBalance) FEndBalance from t_Balance b' end
when 'ITEM_BEGBAL' then 'Select Sum(b.FBeginBalanceFor) FBeginBalanceFor from t_Balance b'
when 'ITEM_BEGBALABS' then 'Select Sum(b.FBeginBalanceFor) FBeginBalanceFor from t_Balance b'
when 'ITEM_BASE_BEGBAL' then 'Select Sum(b.FBeginBalance) FBeginBalance from t_Balance b '
when 'ITEM_BASE_BEGBALABS' then 'Select Sum(b.FBeginBalance) FBeginBalance from t_Balance b '
when 'ITEM_AMTDR' then 'Select Sum(b.FDebitFor) FDebitFor from t_Balance b '
when 'ITEM_BASE_AMTDR' then 'Select Sum(b.FDebit) FDebit from t_Balance b'
when 'ITEM_AMTCR' then 'Select Sum(b.FCreditFor) FCreditFor from t_Balance b'
when 'ITEM_BASE_AMTCR' then 'Select Sum(b.FCredit) FCredit from t_Balance b '
when 'ITEM_YTDDR' then 'Select Sum(b.FYtdDebitFor) FYtdDebitFor from t_Balance b '
when 'ITEM_BASE_YTDDR' then 'Select Sum(b.FYtdDebit) FYtdDebit from t_Balance b'
when 'ITEM_YTDCR' then 'Select Sum(b.FYtdCreditFor) FYtdCreditFor from t_Balance b '
when 'ITEM_BASE_YTDCR' then 'Select Sum(b.FYtdCredit) FYtdCredit from t_Balance b'
when 'ITEM_ENDBAL' then 'Select Sum(b.FEndBalanceFor) FEndBalanceFor from t_Balance b'
when 'ITEM_ENDBALABS' then 'Select Sum(b.FEndBalanceFor) FEndBalanceFor from t_Balance b '
when 'ITEM_BASE_ENDBAL' then 'Select Sum(b.FEndBalance) FEndBalance from t_Balance b '
when 'ITEM_BASE_ENDBALABS' then 'Select Sum(b.FEndBalance) FEndBalance from t_Balance b'
else '' end
if len(@strSql) = 0
begin
raiserror('#不支持的类别',18,18)
return
end
set @strSql = @strSql + ','+ @strCriteria
set @strSql = @strSql + (select case @AccItemTypeNumber
when 'ITEM_BEGBALDR' then @groupby + ') x Where x.FBeginBalanceFor>0 '
when 'ITEM_BEGBALCR' then @groupby + ') x Where x.FBeginBalanceFor<0 '
when 'ITEM_BASE_BEGBALDR' then @groupby + ') x Where x.FBeginBalance>0 '
when 'ITEM_BASE_BEGBALCR' then @groupby + ') x Where x.FBeginBalance<0 '
when 'ITEM_ENDBALDR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then ''
else @groupby + ') x Where x.FEndBalanceFor>0' end
when 'ITEM_ENDBALCR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then ''
else @groupby + ') x Where x.FEndBalanceFor>0' end
when 'ITEM_BASE_ENDBALDR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then ''
else @groupby + ') x Where x.FEndBalance>0' end
when 'ITEM_BASE_ENDBALCR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then ''
else @groupby + ') x Where x.FEndBalance<0' end
else '' End)
--原始结果表
--print @strSql
set @operatorSign = ''
if @AccFDC = -1 And exists(select 1 from TAcctFunctionItem where FNumber = @AccItemTypeNumber And FNumber in('ITEM_BEGBAL','ITEM_ENDBAL','ITEM_PNL','ITEM_PNLYTD','ITEM_BASE_BEGBAL','ITEM_BASE_ENDBAL','ITEM_BASE_PNL','ITEM_BASE_PNLYTD'))
begin
set @operatorSign = '-'
end
set @strSql = (select case @AccItemTypeNumber
when 'ITEM_BEGBALDR' then 'Select '+@operatorSign+'sum(FBeginBalanceFor) From ('+ @strSql +') as S'
when 'ITEM_BEGBALCR' then 'Select '+@operatorSign+'sum(-FBeginBalanceFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_BEGBALDR' then 'Select '+@operatorSign+'sum(FBeginBalance) From ('+ @strSql +') as S'
when 'ITEM_BASE_BEGBALCR' then 'Select '+@operatorSign+'sum(-FBeginBalance) From ('+ @strSql +') as S'
when 'ITEM_ENDBALDR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select '+@operatorSign+'sum(FBeginBalanceFor) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(FEndBalanceFor) From ('+ @strSql +') as S' end
when 'ITEM_ENDBALCR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select '+@operatorSign+'sum(FBeginBalanceFor) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(-FEndBalanceFor) From ('+ @strSql +') as S' end
when 'ITEM_BASE_ENDBALDR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select '+@operatorSign+'sum(FBeginBalance) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(FEndBalance) From ('+ @strSql +') as S' end
when 'ITEM_BASE_ENDBALCR' then
case when @ContainVoucher = 1 and @iCurrYear = @iStartYear and @iCurrPeriod = @iEndPeriod then 'Select '+@operatorSign+'sum(FBeginBalance) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(-FEndBalance) From ('+ @strSql +') as S' end
when 'ITEM_BEGBAL' then 'Select '+@operatorSign+'sum(FBeginBalanceFor) From ('+ @strSql +') as S'
when 'ITEM_BEGBALABS' then 'Select '+@operatorSign+'sum(FBeginBalanceFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_BEGBAL' then 'Select '+@operatorSign+'sum(FBeginBalance) From ('+ @strSql +') as S'
when 'ITEM_BASE_BEGBALABS' then 'Select '+@operatorSign+'sum(FBeginBalance) From ('+ @strSql +') as S'
when 'ITEM_AMTDR' then 'Select '+@operatorSign+'sum(FDebitFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_AMTDR' then 'Select '+@operatorSign+'sum(FDebit) From ('+ @strSql +') as S'
when 'ITEM_AMTCR' then 'Select '+@operatorSign+'sum(FCreditFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_AMTCR' then 'Select '+@operatorSign+'sum(FCredit) From ('+ @strSql +') as S'
when 'ITEM_YTDDR' then 'Select '+@operatorSign+'sum(FYtdDebitFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_YTDDR' then 'Select '+@operatorSign+'sum(FYtdDebit) From ('+ @strSql +') as S'
when 'ITEM_YTDCR' then 'Select '+@operatorSign+'sum(FYtdCreditFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_YTDCR' then 'Select '+@operatorSign+'sum(FYtdCredit) From ('+ @strSql +') as S'
when 'ITEM_ENDBAL' then 'Select '+@operatorSign+'sum(FEndBalanceFor) From ('+ @strSql +') as S'
when 'ITEM_ENDBALABS' then 'Select '+@operatorSign+'sum(FEndBalanceFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_ENDBAL' then 'Select '+@operatorSign+'sum(FEndBalance) From ('+ @strSql +') as S'
when 'ITEM_BASE_ENDBALABS' then 'Select '+@operatorSign+'sum(FEndBalance) From ('+ @strSql +') as S'
when 'ITEM_PNL' then 'Select '+@operatorSign+'sum(FAmountFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_PNL' then 'Select '+@operatorSign+'sum(FAmount) From ('+ @strSql +') as S'
when 'ITEM_PNLYTD' then 'Select '+@operatorSign+'sum(FYtdAmountFor) From ('+ @strSql +') as S'
when 'ITEM_BASE_PNLYTD' then 'Select '+@operatorSign+'sum(FYtdAmount) From ('+ @strSql +') as S'
when 'ITEM_BUDGET' then
case @CurrencyID when 0 then 'Select '+@operatorSign+'sum(FBudget) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(FBudgetFor) From ('+ @strSql +') as S' end
when 'ITEM_BUDGETDR' then
case @CurrencyID when 0 then 'Select '+@operatorSign+'sum(FBudget) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(FBudgetFor) From ('+ @strSql +') as S' end
when 'ITEM_BUDGETCR' then
case @CurrencyID when 0 then 'Select '+@operatorSign+'sum(FBudget) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(FBudgetFor) From ('+ @strSql +') as S' end
when 'ITEM_MINBUDGET' then
case @CurrencyID when 0 then 'Select '+@operatorSign+'sum(FMinBudget) From ('+ @strSql +') as S'
else 'Select '+@operatorSign+'sum(FMinBudgetFor) From ('+ @strSql +') as S' end
when 'ITEM_QTY_BEGBAL' then 'Select '+@operatorSign+'sum(FBeginQty) From ('+ @strSql +') as S'
when 'ITEM_QTY_AMTDR' then 'Select '+@operatorSign+'sum(FDebitQty) From ('+ @strSql +') as S'
when 'ITEM_QTY_AMTCR' then 'Select '+@operatorSign+'sum(FCreditQty) From ('+ @strSql +') as S'
when 'ITEM_QTY_YTDDR' then 'Select '+@operatorSign+'sum(FYtdDebitQty) From ('+ @strSql +') as S'
when 'ITEM_QTY_YTDCR' then 'Select '+@operatorSign+'sum(FYtdCreditQty) From ('+ @strSql +') as S'
when 'ITEM_QTY_YTDCR' then 'Select '+@operatorSign+'sum(FYtdCreditQty) From ('+ @strSql +') as S'
when 'ITEM_QTY_ENDBAL' then 'Select '+@operatorSign+'sum(FEndQty) From ('+ @strSql +') as S'
else '' end )
--汇总表
print @strSql
set @bIncludeUnpost = 0
set @bIncludeUnpostAdjust = 0
--取未过账的内容：有些查询时强制要查询未过账的内容
if @iCurrYear = @iEndYear And @iCurrPeriod = @iEndPeriod
begin
set @bIncludeUnpost = 1
end
if @iEndYear <@iCurrYear
begin
set @bIncludeUnpostAdjust = 1
end
if @bIncludeUnpost = 1 And @bIncludeUnpostAdjust = 0
begin
set @strTableB =
'(Select u.FYear, u.FPeriod , e.FAccountID, e.FDetailID, e.FCurrencyID, e.FDC,e.FAmountFor, e.FAmount, e.FQuantity, e.FMeasureUnitID
From t_Voucher u
Inner Join t_VoucherEntry e On u.FVoucherID=e.FVoucherID
Where u.FPosted =0 And u.FYear='+ Convert(nvarchar(10),@iEndYear) +' And u.FPeriod='+ Convert(nvarchar(10),@iEndPeriod) +') as B'
end
else if @bIncludeUnpost =1 And @bIncludeUnpostAdjust = 1--？？这种情况如何可能发生？？
begin
set @strTableB = '(Select u.FYear, u.FPeriod , e.FAccountID, e.FDetailID, e.FCurrencyID, e.FDC,
e.FAmountFor, e.FAmount, e.FQuantity, e.FMeasureUnitID
From t_Voucher u
Inner Join t_VoucherEntry e On u.FVoucherID=e.FVoucherID
Where u.FPosted =0 And u.FYear='+Convert(nvarchar(10),@iEndYear)+' And u.FPeriod='+ Convert(nvarchar(10),@iEndPeriod)+
'Union All
Select u.FYear, u.FPeriod , e.FAccountID, e.FDetailID, e.FCurrencyID, e.FDC,
e.FAmountFor, e.FAmount, e.FQuantity, e.FMeasureUnitID
From t_VoucherAdjust u
Inner Join t_VoucherEntry e On u.FVoucherID=e.FVoucherID
Where u.FPosted =0 And u.FYear='+Convert(nvarchar(10),@iEndYear)+' And u.FPeriod>= '+Convert(nvarchar(10),@iStartPeriod)+' And u.FPeriod<= '+Convert(nvarchar(10),@iEndPeriod)+
') AS B'
end
else if @bIncludeUnpost = 0 and @bIncludeUnpostAdjust = 1
begin
set @strTableB = '(Select u.FYear, u.FPeriod , e.FAccountID, e.FDetailID, e.FCurrencyID, e.FDC, e.FAmountFor, e.FAmount, e.FQuantity, e.FMeasureUnitID
From t_VoucherAdjust u
Inner Join t_VoucherEntry e On u.FVoucherID=e.FVoucherID
Where u.FPosted =0 And u.FYear='+Convert(nvarchar(10),@iEndYear)+' And u.FPeriod>= '+Convert(nvarchar(10),@iStartPeriod)+' And u.FPeriod<= '+Convert(nvarchar(10),@iEndPeriod)+' ) AS B'
end
else
begin
set @strSql2 = 'Select 0 as FValue1'
--这种情况下，不用再去拼接后续脚本了
end
if len(@strTableB) > 0
begin
set @groupby = ' Group By '
set @groupby = @groupby + 'd.F'+Convert(nvarchar(10),@ItemClassID)
set @strCriteria = N' t_ItemDetail d, t_Account a where b.FDetailID = d.FDetailID and b.FAccountID = a.FAccountID and a.FRootID=' + Convert(nvarchar(10),@AccRootID)
set @strCriteria = @strCriteria + N' And d.F'+Convert(nvarchar(10),@ItemClassID)+ '=' + Convert(nvarchar(10),@iItemID)
if @iStartYear = @iEndYear
begin
set @strCriteria = @strCriteria + ' and b.FYear= ' + Convert(nvarchar(10),@iStartYear)
if @iStartPeriod = @iEndPeriod
begin
set @strCriteria = @strCriteria + ' and b.FPeriod= ' + Convert(nvarchar(10),@iEndPeriod)
end
else
begin
set @strCriteria = @strCriteria + ' and (b.FPeriod Between '+Convert(nvarchar(10),@iStartPeriod)+' And '+Convert(nvarchar(10),@iEndPeriod) +')'
end
end
else
begin
set @strCriteria = @strCriteria + ' and (b.FYear> '+Convert(nvarchar(10),@iStartYear)+' or (b.FYear= '+Convert(nvarchar(10),@iStartYear)+' and b.FPeriod>='+
Convert(nvarchar(10),@iStartPeriod)+')) And (b.FYear<'+Convert(nvarchar(10),@iEndYear)+' or (b.FYear='+Convert(nvarchar(10),@iEndYear)+
' and b.FPeriod<='+Convert(nvarchar(10),@iEndPeriod)+'))'
end
set @strCriteria = @strCriteria + N' And a.FNumber = ''' + Convert(nvarchar(10),@AcctNumber) + ''''
if @CurrencyID > 0
begin
set @strCriteria = @strCriteria + N' And b.FCurrencyID= ' + Convert(nvarchar(10),@CurrencyID)
end
select @strSql2 = case @AccItemTypeNumber
when 'ITEM_PNL' then 'Select Sum(' +
(case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) + N'*2*(b.FDC-0.5)*a.FDC) FValue ' +
' From ' + @strTableB+',' + @strCriteria + ' And a.FGroupID >= 400 AND a.FGroupID <= 600 '
when 'ITEM_BASE_PNL' then 'Select Sum(b.FAmount*2*(b.FDC-0.5)*a.FDC) FValue '+
' From ' + @strTableB +','+ @strCriteria + ' And a.FGroupID >= 400 AND a.FGroupID <= 600 '
when 'ITEM_PNLYTD' then 'Select Sum(' +
(case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end)+N'*2*(b.FDC-0.5)*a.FDC) FValue'+
' From ' + @strTableB +','+ @strCriteria + ' And a.FGroupID >= 400 AND a.FGroupID <= 600 '
when 'ITEM_BASE_PNLYTD' then 'Select Sum(b.FAmount*2*(b.FDC-0.5)*a.FDC) FValue'+
' From ' + @strTableB+',' + @strCriteria + ' And a.FGroupID >= 400 AND a.FGroupID <= 600 '
when 'ITEM_QTY_AMTDR' then 'Select Sum(b.FQuantity*m.FCoefficient) FValue From' + @strTableB + ' , t_MeasureUnit m, ' + @strCriteria
+' And b.FMeasureUnitID=m.FMeasureUnitID And b.FDC=0'
when 'ITEM_QTY_AMTCR' then 'Select Sum(b.FQuantity*m.FCoefficient) FValue From '+ @strTableB + ' , t_MeasureUnit m, ' + @strCriteria
+' And b.FMeasureUnitID=m.FMeasureUnitID And b.FDC=0'
when 'ITEM_QTY_YTDDR' then 'Select Sum(b.FQuantity*m.FCoefficient) FValue From '+ @strTableB + ' , t_MeasureUnit m, ' + @strCriteria
+' And b.FMeasureUnitID=m.FMeasureUnitID And b.FDC=1'
when 'ITEM_QTY_YTDCR' then ' Select Sum(b.FQuantity*m.FCoefficient) FValue From'+ @strTableB + ' , t_MeasureUnit m, ' + @strCriteria
+' And b.FMeasureUnitID=m.FMeasureUnitID And b.FDC=0'
when 'ITEM_QTY_ENDBAL' then 'Select Sum(b.FQuantity*m.FCoefficient*2*(b.FDC-0.5)*a.FDC) FValue From ' + @strTableB + ' , t_MeasureUnit m, ' + @strCriteria
+' And b.FMeasureUnitID=m.FMeasureUnitID'
when 'ITEM_ENDBALDR' then 'Select Sum(x.FAmountFor) FValue From (Select Sum(' +
(case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end)+N'*b.FDC)-Sum('+
(case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end)+N'*(1-b.FDC)) FAmountFor'+
' From ' + @strTableB +',' + @strCriteria + @groupby +') x'
when 'ITEM_ENDBALCR' then 'Select Sum(x.FAmountFor) FValue From (Select Sum(' +
(case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end)+N'*b.FDC)-Sum('+
(case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end)+N'*(1-b.FDC)) FAmountFor'+
' From ' + @strTableB +',' + @strCriteria + @groupby +') x'
when 'ITEM_BASE_ENDBALDR' then 'Select Sum(x.FAmount) FValue From (Select Sum(b.FAmount*b.FDC)-Sum(b.FAmount*(1-b.FDC)) FAmount'+
' From ' + @strTableB +',' + @strCriteria + @groupby +') x'
when 'ITEM_BASE_ENDBALCR' then 'Select Sum(x.FAmount) FValue From (Select Sum(b.FAmount*b.FDC)-Sum(b.FAmount*(1-b.FDC)) FAmount'+
' From ' + @strTableB +',' + @strCriteria + @groupby +') x'
when 'ITEM_ENDBAL' then 'Select Sum(' + (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) + '*2*(b.FDC-0.5)*a.FDC) FValue'+
' From ' + @strTableB +',' + @strCriteria
when 'ITEM_ENDBALABS' then 'Select Sum(' + (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) + '*2*(b.FDC-0.5)*a.FDC) FValue'+
' From ' + @strTableB +',' + @strCriteria
when 'ITEM_BASE_ENDBAL' then 'Select Sum(b.FAmount*2*(b.FDC-0.5)*a.FDC) FValue'+
' From ' + @strTableB +',' + @strCriteria
when 'ITEM_BASE_ENDBALABS' then 'Select Sum(b.FAmount*2*(b.FDC-0.5)) FValue'+
' From ' + @strTableB +',' + @strCriteria
when 'ITEM_AMTDR' then 'Select Sum(' + (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) + ') FValue '+
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=1 '
when 'ITEM_BASE_AMTDR' then 'Select Sum(b.FAmount) FValue '+
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=1 '
when 'ITEM_AMTCR' then 'Select Sum(' + (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) + ') FValue '+
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=0 '
when 'ITEM_BASE_AMTCR' then 'Select Sum(b.FAmount) FValue '+
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=0 '
when 'ITEM_YTDDR' then 'Select Sum(' + (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) + ') FValue '+
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=1 '
when 'ITEM_BASE_YTDDR' then 'Select Sum(b.FAmount) FValue '+
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=1 '
when 'ITEM_YTDCR' then 'Select Sum(' + (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) + ') FValue '+
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=0 '
when 'ITEM_BASE_YTDCR' then 'Select Sum(b.FAmount) FValue ' +
' From ' +@strTableB +',' + @strCriteria +' And b.FDC=0 '
else
'select 0 as FValue'
end
end
print @strSql2
print '2段脚本查询结果相加为本次查询的结果'
end
else
begin
set @strCriteria = 'INNER JOIN t_Account a ON b.FAccountID = a.FAccountID INNER JOIN t_ItemDetail d ON b.FDetailID = d.FDetailID '
set @strCriteria = @strCriteria + ' WHERE a.FRootID= ' + Convert(nvarchar(10),@AccRootID) +N' And d.F'+Convert(nvarchar(10),@ItemClassID)+ '=' + Convert(nvarchar(10),@iItemID)
If @iStartYear = @iEndYear
begin
set @strCriteria = @strCriteria + ' and b.FYear='+convert(nvarchar(4), @iStartYear)
If @iStartPeriod = @iEndPeriod
begin
set @strCriteria = @strCriteria + ' And b.FPeriod = ' + convert(nvarchar(4),@iStartPeriod)
end
Else
begin
set @strCriteria = @strCriteria + ' And b.FPeriod >= ' + convert(nvarchar(4),@iStartPeriod) + ' And b.FPeriod<='+convert(nvarchar(4),@iEndPeriod)
end
end
else
begin
set @strCriteria = @strCriteria + ' and (b.FYear> '+Convert(nvarchar(10),@iStartYear)+' or (b.FYear= '+Convert(nvarchar(10),@iStartYear)+' and b.FPeriod>='+
Convert(nvarchar(10),@iStartPeriod)+')) And (b.FYear<'+Convert(nvarchar(10),@iEndYear)+' or (b.FYear='+Convert(nvarchar(10),@iEndYear)+
' and b.FPeriod<='+Convert(nvarchar(10),@iEndPeriod)+'))'
end
set @strCriteria = @strCriteria + N' And a.FNumber = ''' + Convert(nvarchar(10),@AcctNumber) + ''''
if @CurrencyID > 0
begin
set @strCriteria = @strCriteria + N' And b.FCurrencyID= ' + Convert(nvarchar(10),@CurrencyID)
end
set @groupby = ' Group By d.F'+Convert(nvarchar(10),@ItemClassID)
select @strSql = Case @AccItemTypeNumber
when 'ITEM_ENDBALDR' then
'Select SUM(CASE WHEN FEndBalanceFor > 0 THEN FEndBalanceFor ELSE 0 END) AS FValue'
when 'ITEM_ENDBALCR' then
'Select SUM(CASE WHEN FEndBalanceFor < 0 THEN -FEndBalanceFor ELSE 0 END) AS FValue'
when 'ITEM_BASE_ENDBALDR' then
'SELECT SUM(CASE WHEN FEndBalance > 0 then FEndBalance ELSE 0 END) AS FValue'
when 'ITEM_BASE_ENDBALCR' then
'Select SUM(CASE WHEN FEndBalance < 0 THEN -FEndBalance ELSE 0 END) AS FValue'
else
'select 0 as FValue'
end
set @strSql = @strSql +
' FROM (SELECT SUM(FEndBalance) AS FEndBalance,SUM(FEndBalanceFor) AS FEndBalanceFor'+
' FROM (SELECT d.F'+Convert(nvarchar(10),@ItemClassID)+',SUM(b.FEndBalance) AS FEndBalance,SUM(b.FEndBalanceFor) AS FEndBalanceFor'+
' FROM t_Balance AS b '+@strCriteria + @groupby
if @iCurrYear = @iEndYear and @iCurrPeriod = @iEndPeriod
begin
select @strSql = @strSql + ' Union All ' +
' SELECT d.F'+Convert(nvarchar(10),@ItemClassID) +',SUM(b.famount) AS famount,SUM('+ (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) +') AS FAmountFor'+
' FROM (SELECT u.FYear, u.FPeriod, e.FAccountID, e.FDetailID, e.FCurrencyID, e.FDC, CASE WHEN e.FDC = 1 THEN e.FAmountFor ELSE - e.FAmountFor END AS FAmountFor,'+
' CASE WHEN e.FDC = 1 THEN e.FAmount ELSE - e.FAmount END AS famount, e.FQuantity, e.FMeasureUnitID'+
' FROM t_Voucher AS u '+
' INNER JOIN t_VoucherEntry AS e ON u.FVoucherID = e.FVoucherID'+
' WHERE u.FPosted = 0 ) b '+@strCriteria + @groupby
end
if @iEndYear<@iCurrYear
begin
select @strSql = @strSql + ' Union All ' +
' SELECT d.F'+Convert(nvarchar(10),@ItemClassID) +',SUM(b.famount) AS famount,SUM('+ (case @CurrencyID when 0 then 'b.FAmount' else 'b.FAmountFor' end) +') AS FAmountFor'+
' FROM (SELECT u.FYear, u.FPeriod, e.FAccountID, e.FDetailID, e.FCurrencyID, e.FDC, CASE WHEN e.FDC = 1 THEN e.FAmountFor ELSE - e.FAmountFor END AS FAmountFor,'+
' CASE WHEN e.FDC = 1 THEN e.FAmount ELSE - e.FAmount END AS famount, e.FQuantity, e.FMeasureUnitID'+
' FROM t_VoucherAdjust AS u'+
' INNER JOIN t_VoucherEntry AS e ON u.FVoucherID = e.FVoucherID'+
' WHERE u.FPosted = 0'+
' And u.FYear= '+ Convert(nvarchar(4),@iEndYear) +' And u.Fperiod>= '+ Convert(nvarchar(4),@iCurrPeriod) +' And u.Fperiod<=' + Convert(nvarchar(4),@iEndPeriod) +
') as b ' + @strCriteria + @groupby
end
select @strSql = @strSql +' ) AS a'+
' ) AS x'
print @strSql
end
根据自己的账套自己修改这些过滤条件，然后按F5执行；
会输出2个段或1段脚本；如果输出的是2段脚本则最终结果是2段脚本查询结果之和
根据这段脚本就能够快速构建出多核算项目、多科目的报表了。
过滤条件如何设置：
--过滤条件设置
set @iStartYear = 2013 --2014
set @iStartPeriod = 1
set @iEndYear = 2013 --2014
set @iEndPeriod = 1 --2
set @AccItemTypeNumber = N'ITEM_BASE_ENDBALDR'--ITEM_BASE_ENDBALDR,ITEM_AMTCR
set @AcctNumber = '1403'--原材料
set @CurrencyID = 0 --本位币
set @clsName = '物料' --核算项目的名称
set @clsItemNumber = '1.01.000.00000'--输入具体核算项目的长代码
set @ContainVoucher = 1--输入0或者1,1表示包含
已蓝海演示账套为例：
--获得当前年份
select Convert(int,Fvalue) from t_SystemProfile where FKey='CurrentYear' and FCategory ='GL'
--获取当前期间
select Convert(int,Fvalue) from t_SystemProfile where FKey='CurrentPeriod' and FCategory ='GL'
【期间】你输入的期间不能大于2014-2期间（根据自己账套实际来）
关于不同【查询类别】@AccItemTypeNumber ，对期间的输入是有严格的要求，并会再函数中自动进行调整；
【查询类别】@AccItemTypeNumberr 表TAcctFunctionItem中的所有类别（除了ITEM_BASE_BUDGET、ITEM_ACCUMU这2个），填写时务必填写为大写（我脚本里没有自动转为大写）
【科目编号】@AcctNumber t_account表的Fnumber字段
【币别】@CurrencyID t_currency 的FCurrencyID;填0表示本位币；
【核算项目类别名称】@clsName t_itemClass的FName字段
【具体核算项目编码】@clsItemNumber
首先查询出核算项目类别ID，入上上图中的物料的FitemClassID=4。
select FFullNumber,* from t_Item where FItemClassID = 4
然后再t_item表中查找出这个核算项目的明细项
完
K3BOS万能报表工具包-电子表格报表中的ACCT公式逻辑
本文2024-09-16 15:28:44发表“k3wise知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3wise-3051.html
