K3BOS万能报表工具包-电子表格报表中的ACCT公式逻辑

栏目:k3wise知识作者:金蝶来源:金蝶云社区发布:2024-09-16浏览:1

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个不支持)

image.webp

第二步:请仔细研读下面这段脚本,这段脚本为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执行;

image.webp

image.webp

会输出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'

image.webp


【期间】你输入的期间不能大于2014-2期间(根据自己账套实际来)

关于不同【查询类别】@AccItemTypeNumber ,对期间的输入是有严格的要求,并会再函数中自动进行调整;

查询类别】@AccItemTypeNumberr 表TAcctFunctionItem中的所有类别(除了ITEM_BASE_BUDGET、ITEM_ACCUMU这2个),填写时务必填写为大写(我脚本里没有自动转为大写)

 【科目编号】@AcctNumber t_account表的Fnumber字段

image.webp

【币别】@CurrencyID t_currency 的FCurrencyID;填0表示本位币;

image.webp

【核算项目类别名称】@clsName t_itemClass的FName字段

image.webp

具体核算项目编码@clsItemNumber

首先查询出核算项目类别ID,入上上图中的物料的FitemClassID=4。

select FFullNumber,* from t_Item where FItemClassID = 4

然后再t_item表中查找出这个核算项目的明细项

image.webp




K3BOS万能报表工具包-电子表格报表中的ACCT公式逻辑

参考文档https://wenku.my7c.com/link/s/MkC3T K3BOS万能报表工具包中配置报表的易用性较差,但是又希望能用到里面的公式;之前有客户ACCT...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息