English

速达软件
     
当前位置:速达软件 >> 技术支持 >> 浏览文章

数量金额总帐和明细帐没数据的脚本

发布日期:2019年01月20日 浏览次数: 作者:佚名  

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是否支撑无单退货