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 '+@operatorSi