# ## # 这是部分存储过程 （太大了只能放这么多 ） 这个存储过程其实就是U8中的客户科目余额表的存储过程
USE [UFDATA_150_2019]
GO
/****** Object: StoredProcedure [dbo].[gl_assReport] Script Date: 2019-06-18 16:09:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[gl_assReport]
@tblname NVARCHAR(60) ,
@iBeginPeriod INT , --月份
@iEndPeriod INT , --月份
@strass NVARCHAR(2000) ,--辅助项,金额9
@whereSql NVARCHAR(2000) ,--过滤条件
@wheresqlcode NVARCHAR(2000) , --科目过滤条件
@bcdc INT ,-- -1 方向 借 1 贷 0
@bVouch BIT, --记账标志11
@citem_class NVARCHAR(10), -- 项目大类
@bDisplayCreditLine bit =0, --信用额度
@sAuth NVARCHAR(4000), --权限字符串
@isshowzeroaccumulation bit=1,
@detailedObject nvarchar(100)='', --交叉项
@subtotalitem nvarchar(100)='', --小计项
@collectitem nvarchar(100)='', --汇总项
@isPhone tinyint=0 --@isPhone:1移动报表按期间汇总
AS
DECLARE @isUseMultiCurrency AS BIT --是否启用多币种核算
SELECT @isUseMultiCurrency=case when UPPER(cvalue)=N'TRUE' then 1 else 0 end from accinformation where cname=N'isUseMultiCurrency' and csysid=N'GL'
--统计项
-- declare @staticsticsItem as nvarchar(100)
-- select @staticsticsItem=enumcode from v_aa_enum where EnumName =@statistics and EnumType ='GL_code'
-- if @staticsticsItem=N'部门'
-- begin
-- set @strass=replace(@strass,'department','')
-- set @strass ='department,' +@strass
--end
-- else if @staticsticsItem=N'项目'
--begin
-- set @strass=replace(@strass,'item','')
-- set @strass ='item,' +@strass
--end
-- else if @staticsticsItem=N'客户'
-- begin
-- set @strass=replace(@strass,'customer','')
-- set @strass ='customer,' +@strass
-- end
-- else if @staticsticsItem=N'供应商'
-- begin
--set @strass=replace(@strass,'vendor','')
--set @strass ='vendor,' +@strass
--end
-- --明细对象
-- declare @detailedObjectItem as nvarchar(100)
-- declare @tempStr as nvarchar(100)
-- select @detailedObjectItem=enumcode from v_aa_enum where EnumName =@detailedObject and EnumType ='gl_code'
--print @detailedObjectItem
--set @tempStr=substring(@strass,CHARINDEX(',',@strass)+1,LEN(@strass))
--if @detailedObjectItem=N'供应商'
-- set @strass=replace(@strass,@tempStr,'vendor')
--else if @detailedObjectItem=N'部门'
-- set @strass=replace(@strass,@tempStr,'department')
--else if @detailedObjectItem=N'项目'
-- set @strass=replace(@strass,@tempStr,'department')
--else if @detailedObjectItem=N'客户'
-- set @strass=replace(@strass,@tempStr,'customer')
--else if @detailedObjectItem=N'科目'
-- set @strass=replace(@strass,@tempStr,'code')
-- --end
--默认币种
declare @accid as char(3)
set @accid=(select SUBSTRING(DB_NAME(),8,3))
declare @RMB as nvarchar(50)
set @RMB=isnull( (select cCurName from ufsystem..UA_Account where cAcc_Id=@accid),N'人民币')
--启用日期
declare @dStartDate as datetime
set @dStartDate=(select cvalue from accinformation where csysid ='gl' and cname = 'dGLStartDate')
declare @AccBeginPeriod as int
declare @AccPeriod as int
declare @startYear as int
if exists(select 1 from ufsystem..ua_period where dbegin <=@dStartDate and dend >= @dStartDate and cacc_id=@accid)
select @AccPeriod=iid,@startYear=iyear from ufsystem..ua_period where dbegin <=@dStartDate and dend >= @dStartDate and cacc_id=@accid
else
select top 1 @AccPeriod=iid,@startYear=iyear from ufsystem..ua_period where cacc_id=@accid order by iyear,iid
set @AccBeginPeriod=@AccPeriod
set @AccPeriod=convert(int,str(@startYear,4)+(case when @AccPeriod>9 then str(@AccPeriod,2) else '0'+str(@AccPeriod,1) end) )
print @AccPeriod --启用期间
--无起始日期
if right(str(@iBeginPeriod),2)='00'
set @iBeginPeriod=@iBeginPeriod+1
declare @regPeriod as int--用来决定取accsum表的期间
IF @bVouch = 1 --如果包含未记账，求最大记账期间
Begin
set @regPeriod=(select isnull(max(iyPeriod),convert(int,str(@startYear,4))*100) from gl_accvouch where ibook=1 and iPeriod 0 )
End
ELSE
set @regPeriod=@iBeginPeriod
set @regPeriod=case when @regPeriod=convert(int,str(@startYear,4))*100 then @AccPeriod else @regPeriod end
set @regPeriod=case when @regPeriod>@iBeginPeriod then @iBeginPeriod else @regPeriod end
--最大使用凭证的期间 可能为 00
if right(str(@regPeriod,9),2)='00'
set @regPeriod=@regPeriod+1
--查询起始日期在第二个年度以后
set @regPeriod=case when @iBeginPeriod/100>@regPeriod/100 then @iBeginPeriod/100*100+1 else @regPeriod end
print '@regPeriod:'+cast(@regPeriod as nvarchar)
DECLARE @withIndex nvarchar(100) --强制使用索引提高效率
set @withIndex=' '
if exists(SELECT * FROM sysindexes WHERE name = N'idx_GL_accvouch_iYPeriod_ibook_iflag_code' and id=object_id('gl_accvouch'))
set @withIndex=' with (index(idx_GL_accvouch_iYPeriod_ibook_iflag_code))'
--DECLARE @tsqltemptable NVARCHAR(60)
DECLARE @sql NVARCHAR(4000)
DECLARE @tblnameA NVARCHAR(60)
DECLARE @tblnameB NVARCHAR(60)
DECLARE @tblnameM NVARCHAR(60)
declare @tmpCode nvarchar(60)
SET @tblnameA = @tblname + 'a'
SET @tblnameB = @tblname + 'b'
SET @tblnameM = @tblname + 'M'
set @tmpCode=@tblname +'code'
set @sql='if not exists(select 1 from tempdb..sysobjects where name='''+@tmpcode+''' and xtype=''u'')
select * into tempdb..'+@tmpcode+' from code '
exec(@sql)
IF CHARINDEX('tempdb..', @tblnameA) > 0
BEGIN
SET @tblnameA = SUBSTRING(@tblnameA, LEN('tempdb..') + 1,
LEN(@tblnameA))
END
--SET @tsqltemptable = 'tempdb..gl_sqltemptable'
SET @sql = '
if exists( select * from tempdb..sysobjects where id=object_id('''
+ 'tempdb..' + @tblnameA + ''') and type=''u'')
drop table tempdb..' + @tblnameA
EXEC(@sql)
SET @sql = '
if exists( select * from tempdb..sysobjects where id=object_id('''
+ 'tempdb..' + @tblnameB + ''') and type=''u'')
drop table tempdb..' + @tblnameB
EXEC(@sql)
SET @sql = '
if exists( select * from tempdb..sysobjects where id=object_id('''
+ 'tempdb..' + @tblnameM + ''') and type=''u'')
drop table tempdb..' + @tblnameM
EXEC(@sql)
set @sql='
if exists( select * from tempdb..sysobjects where id=object_id('''
+ 'tempdb..' + @tblname + ''') and type=''u'')
drop table tempdb..' + @tblname
EXEC(@sql)
DECLARE @OldSelectSql AS NVARCHAR(2000)
DECLARE @selectSql AS NVARCHAR(2000)
DECLARE @selectSqlA AS NVARCHAR(2000)
DECLARE @selectSqlAllA AS NVARCHAR(2000)
--declare @whereSql as nvarchar(2000)
DECLARE @oldGroupbySql AS NVARCHAR(2000)
DECLARE @groupbySql AS NVARCHAR(2000)
DECLARE @joinonSql AS NVARCHAR(2000)
DECLARE @oldjoinonSql AS NVARCHAR(2000)
DECLARE @ibanlanceSql AS NVARCHAR(200)
DECLARE @ass AS NVARCHAR(50)
DECLARE @asscodebegin AS NVARCHAR(50)
DECLARE @asscodeend AS NVARCHAR(50)
--declare @wheresqlcode as nvarchar(2000)
SET @selectSql = ' select '
SET @selectSqlA = ' select '
SET @selectSqlAllA = ''
--SET @whereSql = ' where 1=1 '
SET @groupbySql = ' group by '
SET @joinonSql = ' '
set @oldjoinonSql= ''
set @whereSql=' where 1=1 ' + @whereSql
--set @wheresqlcode=''
declare @leftjoinonSql as nvarchar(2000)
set @leftjoinonSql =' '
declare @assCount int --显示项个数
set @assCount=0
--declare @subTotalSql nvarchar(2000) --小计更新
declare @subtotalSelectSql nvarchar(2000) --小计字段cdept_id|cdept_id,cperson_id
--set @subTotalSql=''
set @subtotalSelectSql=''
DECLARE sqlcursor CURSOR
FOR SELECT ass,asscodeBegin,asscodeEnd FROM f_split(@strass,',')
OPEN sqlcursor
FETCH NEXT FROM sqlcursor INTO @ass,@asscodebegin,@asscodeend ;
WHILE @@FETCH_STATUS = 0
BEGIN
set @assCount=@assCount+1
/*
SET @subTotalSql = @subTotalSql
+ CASE WHEN @ass = 'code' THEN ' a.ccode=Null,'
WHEN @ass = 'customer' THEN ' a.ccus_id=Null,'
WHEN @ass = 'department' THEN ' a.cdept_id=Null,'
WHEN @ass = 'person' THEN 'a.cperson_id=Null,'
WHEN @ass = 'vendor' THEN 'a.csup_id=Null,'
WHEN @ass = ' itemclass ' THEN 'a.citem_class=Null,'
WHEN @ass = 'item' THEN 'a.citem_id=Null,'
WHEN @ass = 'citem' THEN 'citemccode=Null,'
WHEN @ass = 'ccustomer' THEN ' ccccode=Null,'
WHEN @ass = 'cvendor' THEN ' cvccode=Null,'
WHEN @ass = 'dcustomer' THEN ' cdccode=Null,'
WHEN @ass = 'dvendor' THEN ' cdccode=Null,'
WHEN @ass = 'groupcode' THEN ' cgroupcode=Null,'
WHEN @ass = '' or @ass='ibanlance' THEN ''
ELSE 'a.'+@ass+'=Null,'
END
*/
SET @selectSql = @selectSql
+ CASE WHEN @ass = 'code' THEN ' a.ccode,'
WHEN @ass = 'customer' THEN ' a.ccus_id,'
WHEN @ass = 'department' THEN ' a.cdept_id,'
WHEN @ass = 'person' THEN 'a.cperson_id,'
WHEN @ass = 'vendor' THEN 'a.csup_id,'
WHEN @ass = ' itemclass ' THEN 'a.citem_class,'
WHEN @ass = 'item' THEN 'a.citem_id,'
WHEN @ass = 'citem' THEN 'citemccode,'
WHEN @ass = 'ccustomer' THEN ' ccccode,'
WHEN @ass = 'cvendor' THEN ' cvccode,'
WHEN @ass = 'dcustomer' THEN ' cdccode,'
WHEN @ass = 'dvendor' THEN ' cdccode,'
WHEN @ass = 'groupcode' THEN ' cgroupcode,'
WHEN @ass = '' or @ass='ibanlance' THEN ''
ELSE 'a.'+@ass+','
END
--自定义项小计：@subtotalitem=N'cdefine10'
if charindex(','+@ass+',',','+@subtotalitem+',')>0
begin
set @subtotalSelectSql = @subtotalSelectSql + replace(@selectSql,'select ','') + '|'
print '@subtotalSelectSql:'+@subtotalSelectSql
end
SET @selectSqlAllA = @selectSqlAllA
+ CASE WHEN @ass = 'code' THEN ' a.ccode,'
WHEN @ass = 'customer' THEN ' a.ccus_id,'
WHEN @ass = 'department' THEN ' a.cdept_id,'
WHEN @ass = 'person' THEN 'a.cperson_id,'
WHEN @ass = 'vendor' THEN 'a.csup_id,'
WHEN @ass = ' itemclass ' THEN 'a.citem_class,'
WHEN @ass = 'item' THEN 'a.citem_id,'
WHEN @ass = 'citem' THEN 'a.citemccode,'
WHEN @ass = 'ccustomer' THEN ' a.ccccode,'
WHEN @ass = 'cvendor' THEN ' a.cvccode,'
WHEN @ass = 'dcustomer' THEN ' a.cdccode,'
WHEN @ass = 'dvendor' THEN ' a.cdccode,'
WHEN @ass = 'groupcode' THEN ' a.cgroupcode,'
WHEN @ass = '' or @ass='ibanlance' THEN ''
ELSE 'a.'+@ass+','
END
SET @selectSqlA = @selectSqlA
+ CASE WHEN @ass = 'code' THEN ' a.ccode,'
WHEN @ass = 'customer' THEN ' a.ccus_id,'
WHEN @ass = 'department' THEN ' a.cdept_id,'
WHEN @ass = 'person' THEN 'a.cperson_id,'
WHEN @ass = 'vendor' THEN 'a.csup_id,'
WHEN @ass = ' itemclass ' THEN 'a.citem_class,'
WHEN @ass = 'item' THEN 'a.citem_id,'
WHEN @ass = 'citem' THEN (case when @citem_class='ch' then 'cinvccode as citemccode,' else 'citemccode,' end)
WHEN @ass = 'ccustomer' THEN ' ccccode,'
WHEN @ass = 'cvendor' THEN ' cvccode,'
WHEN @ass = 'dcustomer' THEN ' cdccode,'
WHEN @ass = 'dvendor' THEN ' cdccode,'
WHEN @ass = 'groupcode' THEN ' cgroupcode,'
WHEN @ass = '' or @ass='ibanlance' THEN ''
ELSE 'a.'+@ass+','
END
--set @whereSql=@whereSql +
--case
--when @ass='ccode' then (case when @asscodebegin='' then '' else ' and ccode>=''' + @asscodebegin +'''' end)
--+ (case when @asscodeend='' then '' else ' and ccode<=''' + @asscodeend +'''' end ) + ' '
--when @ass='cus' then (case when @asscodebegin='' then '' else ' and ccus_id>=''' + @asscodebegin + '''' end)
--+ (case when @asscodeend='' then '' else ' and ccus_id<=''' + @asscodeend +'''' end ) + ' and ccus_id is not null'
--when @ass='dept' then (case when @asscodebegin='' then '' else ' and cdept_id>=''' + @asscodebegin + '''' end )
--+ (case when @asscodeend='' then '' else ' and cdept_id<=''' + @asscodeend end ) + ' and cdept_id is not null'
--when @ass='person' then (case when @asscodebegin='' then '' else ' and cperson_id>=''' + @asscodebegin +'''' end )
--+ (case when @asscodeend='' then '' else ' and cperson_id<=' + @asscodeend+'''' end ) + ' and cperson_id is not null'
--when @ass='sup' then (case when @asscodebegin='' then '' else ' and csup_id>=' + @asscodebegin+'''' end )
--+ (case when @asscodeend='' then '' else ' and csup_id<=''' + @asscodeend+'''' end ) + ' and csup_id is not null'
--when @ass=' itemclass ' then (case when @asscodebegin='' then '' else ' and citem_class=''' + @asscodebegin+'''' end )
--+' and citem_class is not null'
--when @ass='itemid' then (case when @asscodebegin='' then '' else ' and citem_id>=''' + @asscodebegin+'''' end )
--+ (case when @asscodeend='' then '' else ' and citem_id<=' + @asscodeend+'''' end ) + ' and citem_id is not null'
--else ''
--end
SET @groupbySql = @groupbySql
+ CASE WHEN @ass = 'code' THEN ' a.ccode,'
WHEN @ass = 'customer' THEN ' a.ccus_id,'
WHEN @ass = 'department' THEN ' a.cdept_id,'
WHEN @ass = 'person' THEN 'a.cperson_id,'
WHEN @ass = 'vendor' THEN 'a.csup_id,'
WHEN @ass = ' itemclass ' THEN 'a.citem_class,'
WHEN @ass = 'item' THEN 'a.citem_id,'
WHEN @ass = 'citem' THEN (case when @citem_class='ch' then 'cinvccode,' else 'citemccode,' end)
WHEN @ass = 'ccustomer' THEN ' customer.ccccode,'
WHEN @ass = 'cvendor' THEN ' vendor.cvccode,'
WHEN @ass = 'dcustomer' THEN ' customer.cdccode,'
WHEN @ass = 'dvendor' THEN ' vendor.cdccode,'
WHEN @ass = 'groupcode' THEN ' GF_VgroupStruct.cgroupcode,'
WHEN @ass = '' or @ass='ibanlance' THEN ''
ELSE 'a.'+@ass+','
END
SET @whereSql = @whereSql
+ CASE WHEN @ass = 'customer' THEN ' and not a.ccus_id is Null '
WHEN @ass = 'department' THEN ' and not a.cdept_id is Null '
WHEN @ass = 'person' THEN ' and not a.cperson_id is Null '
WHEN @ass = 'vendor' THEN ' and not a.csup_id is Null '
WHEN @ass = ' itemclass ' THEN ' and not a.citem_class is Null '
WHEN @ass = 'item' or @ass = 'citem' THEN ' and not a.citem_id is Null '
WHEN @ass = 'ccustomer' THEN ' and not customer.ccccode is Null '
WHEN @ass = 'cvendor' THEN ' and not vendor.cvccode is Null '
WHEN @ass = 'dcustomer' THEN ' and not customer.cdccode is Null'
WHEN @ass = 'dvendor' THEN ' and not vendor.cdccode is Null'
WHEN @ass = '' or @ass='ibanlance' or @ass='code' THEN ''
ELSE ' '
END
/*
SET @wheresqlcode = @wheresqlcode
+ CASE WHEN @ass = 'customer' THEN ' and code.bcus=1 '
WHEN @ass = 'department'
THEN ' and code.bdept=1 and code.bperson=0 '
WHEN @ass = 'person'
THEN ' and code.bperson=1 and code.bdept=0 '
WHEN @ass = 'vendor' THEN ' and code.bsup=1 '
WHEN @ass = 'item' THEN ' and code.bitem=1 '
WHEN @ass = 'ccustomer' THEN ' and code.bcus=1 '
WHEN @ass = 'cvendor' THEN ' and code.bsup=1 '
WHEN @ass = 'dcustomer' THEN ' and code.bcus=1 '
WHEN @ass = 'dvendor' THEN ' and code.bsup=1 '
WHEN @ass like 'cdefine%' THEN ' and code.b'+@ass+'=1 '
ELSE ''
END
--个人,部门同时存在,条件冲突,去掉部门条件
if CHARINDEX('and code.bdept=1 and code.bperson=0', @wheresqlcode)>0 and CHARINDEX('and code.bperson=1 and code.bdept=0', @wheresqlcode)>0
SET @wheresqlcode=Replace(@wheresqlcode,'and code.bdept=1 and code.bperson=0','')
*/
SET @joinonSql = @joinonSql
+ CASE WHEN @ass = 'code' THEN ' isnull(a.ccode,'''') = isnull(b.ccode,'''') and'
WHEN @ass = 'customer' THEN ' isnull(a.ccus_id,'''') = isnull(b.ccus_id,'''') and'
WHEN @ass = 'department' THEN ' isnull(a.cdept_id,'''') = isnull(b.cdept_id,'''') and'
WHEN @ass = 'person' THEN ' isnull(a.cperson_id,'''') = isnull(b.cperson_id,'''') and'
WHEN @ass = 'vendor' THEN ' isnull(a.csup_id,'''') = isnull(b.csup_id,'''') and'
WHEN @ass = ' itemclass ' THEN ' isnull(a.citem_class,'''') = isnull(b.citem_class,'''') and'
WHEN @ass = 'item' THEN ' isnull(a.citem_id,'''') = isnull(b.citem_id,'''') and'
WHEN @ass = 'citem' THEN ' isnull(a.citemccode,'''') = isnull(b.citemccode,'''') and'
WHEN @ass = 'groupcode' THEN ' isnull(a.cgroupcode,'''') = isnull(b.cgroupcode,'''') and'
WHEN @ass = 'ccustomer' THEN ' isnull(a.ccccode,'''') = isnull(b.ccccode,'''') and'
WHEN @ass = 'cvendor' THEN ' isnull(a.cvccode,'''') = isnull(b.cvccode,'''') and'
WHEN @ass = 'dcustomer' or @ass = 'dvendor' THEN ' isnull(a.cdccode,'''') = isnull(b.cdccode,'''') and'
WHEN @ass = 'cname' THEN ' isnull(a.cname,'''') = isnull(b.cname,'''') and'
WHEN @ass = '' or @ass='ibanlance' THEN ''
ELSE ' a.' + @ass + '=b.' + @ass + ' and'
END
SET @leftjoinonSql = @leftjoinonSql
+ CASE
WHEN @ass = 'ccustomer' and charindex('left join customer on A.ccus_id=customer.ccuscode',@leftjoinonSql)=0
THEN ' left join customer on A.ccus_id=customer.ccuscode'
WHEN @ass = 'cvendor' and charindex('left join vendor on A.csup_id=vendor.cvencode',@leftjoinonSql)=0
THEN ' left join vendor on A.csup_id=vendor.cvencode'
WHEN @ass = 'dcustomer' and charindex('left join customer on A.ccus_id=customer.ccuscode',@leftjoinonSql)=0
THEN ' left join customer on A.ccus_id=customer.ccuscode '
WHEN @ass = 'dvendor' and charindex('left join vendor on A.csup_id=vendor.cvencode',@leftjoinonSql)=0
THEN ' left join vendor on A.csup_id=vendor.cvencode '
WHEN @ass = 'citem'
THEN ' left join '+(case when @citem_class='ch' then 'inventory f on a.citem_id=f.cinvcode' else 'fitemss'+@citem_class+' f on A.citem_id=f.citemcode ' end)
WHEN @ass = 'groupcode'
THEN ' left join GF_VgroupStruct on A.cdept_id=GF_VgroupStruct.cComCode '
ELSE ''
END
SET @ibanlanceSql = CASE WHEN @ass = 'ibanlance'
THEN ( CASE WHEN @asscodebegin = ''
THEN ''
ELSE ' and abs(me)>='
+ @asscodebegin + ''
END )
+ ( CASE WHEN @asscodeend = ''
THEN ''
ELSE ' and abs(me)<='
+ @asscodeend + ''
END ) + ' '
ELSE ''
END
FETCH NEXT FROM sqlcursor INTO @ass,@asscodebegin,@asscodeend ;
END
CLOSE sqlcursor
DEALLOCATE sqlcursor
print 'selectSql:-'+@selectSql
