SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/***********************************************************************************************
***
**会计账簿(各类总账明细账及科目余额汇总表)关于发生额余额过滤条件
**返回SubCode:
**程序员:zjw
**修改日期:2013-01-25
**输入参数:
@IncludeUnBindData:0->只提取已登账凭证的科目数据,1->包含未登账凭证
@calctype 辅助核算类型:0科目(总分类帐、科目余额汇总表),1项目,2部门,3往来单位,4货品,5员工, 6数量金额
@calcid 辅助核算类型id
@FilterType integer, 0全部,1有发生额,2有余额,3无发生额,4无余额,5有发生额或有余额,6有发生额且有余额,
7有发生额且无余额,8无发生额或无余额,9无发生额且无余额,10无发生额且有余额
**********************************************************************************************/
ALTER function ar_get_subcode_0(
@bdate datetime='01/01/1900',
@edate datetime='01/01/1900',
@bsubcode varchar(32),
@esubcode varchar(32),
@bsublevels integer,
@esublevels integer,
@calctype integer,
@calcid integer,
@nmoneyid integer,
@shopid integer,
@IncludeUnBindData integer,
@FilterType integer,
@isdetail integer
)
returns @local_tab table(subcode varchar(32))
as
begin
declare @sc1length integer,@sc2length integer,@sc3length integer,@sc4length integer,@sc5length integer,
@sc6length integer,@sc7length integer,@sc8length integer,@sc9length integer,@sc10length integer
declare @lastssubcode varchar(32), @ssubcode varchar(32), @subcode varchar(32)
declare @temp table(subcode varchar(32),FilterType integer)
declare @nyear integer, @nmonth integer, @moneyid integer, @isdebit t_bool, @bmbala t_amount, @tmsumdebit t_amount, @tmsumcredit t_amount
declare @byear integer, @bmonth integer, @eyear integer, @emonth integer
declare @sub_bala_tab table(nyear integer, nmonth integer,subid integer, moneyid integer, bmbala numeric(38,8),
tmsumdebit numeric(38,8), tmsumcredit numeric(38,8),subcode varchar(32))
declare @sub_bala_tab2 table(subcode varchar(32), isdebit integer, moneyid integer, bmbala numeric(38,8),
tmsumdebit numeric(38,8), tmsumcredit numeric(38,8))
set @byear = year(@bdate)
set @bmonth = month(@bdate)
set @eyear = year(@edate)
set @emonth = month(@edate)
set @lastssubcode=''
if( @bsubcode is null or @bsubcode ='') set @bsubcode='1'
if( @esubcode is null or @esubcode ='') set @esubcode='9'
select @sc1length=sc1length,@sc2length=sc2length,@sc3length=sc3length,@sc4length=sc4length,@sc5length=sc5length,
@sc6length=sc6length,@sc7length=sc7length,@sc8length=sc8length,@sc9length=sc9length,@sc10length=sc10length
from dbo.ag_getsubjectalllength()
if(@calctype=0)
insert into @sub_bala_tab
select nyear, nmonth, subid, moneyid, bmbala, tmsumdebit, tmsumcredit, subcode
from dbo.ar_get_mssub_includeunbind(@byear,@bmonth,@eyear,@emonth,@bsubcode,@esubcode,@bsublevels,@esublevels,@nmoneyid,@shopid,@IncludeUnBindData)
else if(@calctype=1)
insert into @sub_bala_tab
select nyear, nmonth, subid, m.moneyid, bmbala, tmsumdebit, tmsumcredit, m.subcode
from dbo.ar_get_aproj_msproj_includeunbind(@byear,@bmonth,@eyear,@emonth,@bsubcode,@esubcode,@bsublevels,@esublevels,@calcid,@nmoneyid,@shopid,@IncludeUnBindData) m
left join al_subject s on s.subjectid = m.subid where s.toproject=1
else if(@calctype=2)
insert into @sub_bala_tab
select nyear, nmonth, subid, m.moneyid, bmbala, tmsumdebit, tmsumcredit, m.subcode
from dbo.ar_get_adep_msdep_includeunbind(@byear,@bmonth,@eyear,@emonth,@bsubcode,@esubcode,@bsublevels,@esublevels,@calcid,@nmoneyid,@shopid,@IncludeUnBindData) m
left join al_subject s on s.subjectid = m.subid where s.todepartment=1
else if(@calctype=3)
insert into @sub_bala_tab
select nyear, nmonth, subid, m.moneyid, bmbala, tmsumdebit, tmsumcredit, m.subcode
from dbo.ar_get_atrader_mstrader_includeunbind(@byear,@bmonth,@eyear,@emonth,@bsubcode,@esubcode,@bsublevels,@esublevels,@calcid,@nmoneyid,@shopid,@IncludeUnBindData) m
left join al_subject s on s.subjectid = m.subid where s.totrader=1
else if(@calctype=4)
insert into @sub_bala_tab
select nyear, nmonth, subid, m.moneyid, bmbala, tmsumdebit, tmsumcredit, m.subcode
from dbo.ar_get_agoods_msgoods_includeunbind(@byear,@bmonth,@eyear,@emonth,@bsubcode,@esubcode,@bsublevels,@esublevels,@calcid,@nmoneyid,@shopid,@IncludeUnBindData) m
left join al_subject s on s.subjectid = m.subid where s.togoods=1
else if(@calctype=5)
insert into @sub_bala_tab
select nyear, nmonth, subid, m.moneyid, bmbala, tmsumdebit, tmsumcredit, m.subcode
from dbo.ar_get_aemp_msemp_includeunbind(@byear,@bmonth,@eyear,@emonth,@bsubcode,@esubcode,@bsublevels,@esublevels,@calcid,@nmoneyid,@shopid,@IncludeUnBindData) m
left join al_subject s on s.subjectid = m.subid where s.toemp=1
else if(@calctype=6)
insert into @sub_bala_tab
select nyear, nmonth, subid, m.moneyid, bmbala, tmsumdebit, tmsumcredit, m.subcode
from dbo.ar_get_msqty_includeunbind(@byear,@bmonth,@eyear,@emonth,@bsubcode,@esubcode,@bsublevels,@esublevels,@nmoneyid,@shopid,@IncludeUnBindData) m
left join al_subject s on s.subjectid = m.subid where s.toqty=1 --and s.togoods=1
--统计@bdate~@edate科目的余额及发生额记录
declare local_cursor cursor local forward_only static read_only
for select s.subcode, s.isdebit, m.nyear, m.nmonth, m.moneyid, m.bmbala, m.tmsumdebit, m.tmsumcredit
from @sub_bala_tab m
left join al_subject s on s.subjectid=m.subid
order by s.subcode, m.nyear, m.nmonth, m.moneyid
open local_cursor
fetch next from local_cursor into @ssubcode, @isdebit, @nyear, @nmonth, @moneyid, @bmbala, @tmsumdebit, @tmsumcredit
while @@fetch_status = 0
begin
if (@bmbala is null) set @bmbala = 0
if (@tmsumdebit is null) set @tmsumdebit = 0
if (@tmsumcredit is null) set @tmsumcredit = 0
if (@ssubcode<>@lastssubcode)
begin
--插入初始记录
insert into @sub_bala_tab2 values(@ssubcode, @isdebit, @moneyid, @bmbala, 0, 0)
set @lastssubcode=@ssubcode
end
if(@isdebit=1)
update @sub_bala_tab2 set bmbala=bmbala+@tmsumdebit-@tmsumcredit --bmbala<>0 标明有余额
where subcode=@ssubcode and moneyid=@moneyid
else
update @sub_bala_tab2 set bmbala=bmbala-@tmsumdebit+@tmsumcredit --bmbala<>0 标明有余额
where subcode=@ssubcode and moneyid=@moneyid
if(@tmsumdebit<>0) --<>0标明有发生额
update @sub_bala_tab2 set tmsumdebit=@tmsumdebit
where subcode=@ssubcode and moneyid=@moneyid
if(@tmsumcredit<>0) --<>0标明有发生额
update @sub_bala_tab2 set tmsumcredit=@tmsumcredit
where subcode=@ssubcode and moneyid=@moneyid
fetch next from local_cursor into @ssubcode, @isdebit, @nyear, @nmonth, @moneyid, @bmbala, @tmsumdebit, @tmsumcredit
end
close local_cursor
deallocate local_cursor
--标志各科目是否有余额及发生额
declare local_cursor cursor local forward_only static read_only
for select * from @sub_bala_tab2 order by subcode
open local_cursor
fetch next from local_cursor into @ssubcode, @isdebit, @moneyid, @bmbala, @tmsumdebit, @tmsumcredit
while @@fetch_status = 0
begin
if (@isdetail=1) --明细科目
begin
set @subcode = @ssubcode
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
else --科目为非明细科目,有下设科目时
begin
if (len(@ssubcode)>=@sc10length)
begin
set @subcode=substring(@ssubcode,1,@sc10length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc9length)
begin
set @subcode=substring(@ssubcode,1,@sc9length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc8length)
begin
set @subcode=substring(@ssubcode,1,@sc8length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc7length)
begin
set @subcode=substring(@ssubcode,1,@sc7length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc6length)
begin
set @subcode=substring(@ssubcode,1,@sc6length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc5length)
begin
set @subcode=substring(@ssubcode,1,@sc5length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc4length)
begin
set @subcode=substring(@ssubcode,1,@sc4length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc3length)
begin
set @subcode=substring(@ssubcode,1,@sc3length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc2length)
begin
set @subcode=substring(@ssubcode,1,@sc2length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
if (len(@ssubcode)>=@sc1length)
begin
set @subcode=substring(@ssubcode,1,@sc1length)
if (@tmsumdebit=0 and @tmsumcredit=0)
insert into @temp values(@subcode,3) --无发生额
else
insert into @temp values(@subcode,1) --有发生额
if (@bmbala=0)
insert into @temp values(@subcode,4) --无余额
else
insert into @temp values(@subcode,2) --有余额
end
end --科目为非明细科目,有下设科目时
fetch next from local_cursor into @ssubcode, @isdebit, @moneyid, @bmbala, @tmsumdebit, @tmsumcredit
end
close local_cursor
deallocate local_cursor
--筛选出对应过滤条件的结果集
declare @FType integer
declare local_cursor cursor local forward_only static read_only
for select distinct subcode, FilterType from @temp order by subcode
open local_cursor
fetch next from local_cursor into @subcode, @FType
while @@fetch_status = 0
begin
if exists(select 1 from @local_tab where subcode=@subcode)
begin
fetch next from local_cursor into @subcode, @FType
continue
end
if (@FilterType=0) --全部
begin
insert into @local_tab values(@subcode)
end else
if (@FilterType=1) --有发生额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=1)
insert into @local_tab values(@subcode)
end else
if (@FilterType=2) --有余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=2)
insert into @local_tab values(@subcode)
end else
if (@FilterType=3) --无发生额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=3)
insert into @local_tab values(@subcode)
end else
if (@FilterType=4) --无余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=4)
insert into @local_tab values(@subcode)
end else
if (@FilterType=5) --有发生额或有余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=1) or
exists(select 1 from @temp where subcode=@subcode and FilterType=2)
insert into @local_tab values(@subcode)
end else
if (@FilterType=6) --有发生额且有余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=1) and
exists(select 1 from @temp where subcode=@subcode and FilterType=2)
insert into @local_tab values(@subcode)
end else
if (@FilterType=7) --有发生额且无余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=1) and
exists(select 1 from @temp where subcode=@subcode and FilterType=4)
insert into @local_tab values(@subcode)
end else
if (@FilterType=8) --无发生额或无余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=3) or
exists(select 1 from @temp where subcode=@subcode and FilterType=4)
insert into @local_tab values(@subcode)
end else
if (@FilterType=9) --无发生额且无余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=3) and
exists(select 1 from @temp where subcode=@subcode and FilterType=4)
insert into @local_tab values(@subcode)
end else
if (@FilterType=10) --无发生额且有余额
begin
if exists(select 1 from @temp where subcode=@subcode and FilterType=3) and
exists(select 1 from @temp where subcode=@subcode and FilterType=2)
insert into @local_tab values(@subcode)
end
fetch next from local_cursor into @subcode, @FType
end
close local_cursor
deallocate local_cursor
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
上一篇:升到6.47及后续版型本,分支序号不对脚本修正 | 下一篇:请问速达3000是否支撑无单退货 |