ALTER Proc [dbo].[GL_P_JZA](@iPer tinyint = 11, @ccash tinyint =1, @tcond varchar(5000)= '') AS
SET NOCOUNT On
declare @ss varchar(8000)
--ll Tong2005B
declare @Checkflag varchar(20)
declare @trnd bigint
set @trnd=(select convert(bigint,rand()*100000000000))
declare @temptable varchar(50)
declare @temptable2 varchar(50)
set @temptable='GL_jztmp'+ convert(varchar(30),@trnd)
set @temptable2='gl_bookB'+ convert(varchar(30),@trnd)
set @Checkflag = (SELECT cvalue FROM AccInformation WHERE cSysID='GL' AND cName='bNotCheckJz')
------------------------------
Delete From GL_mpostcond1
if @ccash=0
begin
--ll Tong2005B
if @Checkflag = 'False'
set @ss='SELECT iperiod,isignseq,ino_id From gl_accvouch where ibook=0 and iperiod= '+convert(char,@iPer,0)
+(case when @tcond='' then '' else ' and (' + @tcond + ')' end)+' and (not (ccheck is null) and iflag is null or ccheck is null and iflag=1) GROUP BY iperiod,isignseq,ino_id'
else
set @ss='SELECT iperiod,isignseq,ino_id From gl_accvouch where ibook=0 and iperiod= '+convert(char,@iPer,0)
+(case when @tcond='' then '' else ' and (' + @tcond + ')' end)+' and (iflag is null or ccheck is null and iflag=1) GROUP BY iperiod,isignseq,ino_id'
-----------------------------
Insert GL_mpostcond1 exec (@ss)
End
Else
begin
if not object_id('tempdb..'+@temptable) is null
exec('drop table tempdb..'+ @temptable)
--ll Tong2005B
if @Checkflag = 'False'
set @ss='SELECT isignseq,ino_id,1 as zt into tempdb..'+@temptable+' FROM gl_accvouch inner join code ON gl_accvouch.ccode = code.ccode
where ibook=0 and iperiod= '+convert(char,@iPer,0)+(case when @tcond='' then '' else ' and (' + @tcond + ')' end)
+' and ((code.bbank ^ code.bcash) =1) and ((not (ccheck is null)) and (ccashier is null)) GROUP BY isignseq,ino_id
Union SELECT isignseq,ino_id,0 as zt From gl_accvouch where ibook=0 and iperiod= '+convert(char,@iPer,0)
+(case when @tcond='' then '' else ' and (' + @tcond + ')' end)+' and (not (ccheck is null) and iflag is null or ccheck is null and iflag=1) GROUP BY isignseq,ino_id'
else
set @ss='SELECT isignseq,ino_id,1 as zt into tempdb..'+@temptable+' FROM gl_accvouch inner join code ON gl_accvouch.ccode = code.ccode
where ibook=0 and iperiod= '+convert(char,@iPer,0)+(case when @tcond='' then '' else ' and (' + @tcond + ')' end)
+' and ((code.bbank ^ code.bcash) =1) and (ccashier is null) GROUP BY isignseq,ino_id
Union SELECT isignseq,ino_id,0 as zt From gl_accvouch where ibook=0 and iperiod= '+convert(char,@iPer,0)
+(case when @tcond='' then '' else ' and (' + @tcond + ')' end)+' and (iflag is null or ccheck is null and iflag=1) GROUP BY isignseq,ino_id'
------------------------------
exec (@ss)
exec('SELECT isignseq,ino_id,sum(zt) as zt1 into tempdb..' + @temptable2 + ' from tempdb..'+@temptable+' group by isignseq,ino_id')
set @ss='select '+convert(char,@iPer,0)+' as iperiod,isignseq,ino_id from tempdb..' + @temptable2 + ' Where zt1 = 0 '+(case when @tcond='' then '' else ' and (' + @tcond + ')' end)