欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

产生财务计帐周期

程序员文章站 2022-04-09 19:42:44
先看看实现的结果,可以By月份和季度,可以调整会计开始日期。 前端略去,只分享MS SQL存储过程: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: Insus.NET -- Blog: https://insus.cnb ......

 

先看看实现的结果,可以by月份和季度,可以调整会计开始日期。

 

前端略去,只分享ms sql存储过程:

 

set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- author:      insus.net
-- blog:        https://insus.cnblogs.com
-- create date: 2019-07-02
-- update date: 2019-07-02
-- description: 动态产生会计周期
-- =============================================
create procedure [dbo].[usp_accountingperiod_select_generateperioddate]
(
    @fiscal_year smallint,
    @perioddatetype nvarchar(1),
    @start1 datetime
)
as
begin
    if object_id('#period_date') is not null
        drop table #period_date
    
    create table #period_date(
        [fiscal_year] [smallint] not null,
        [start1] [datetime] null,
        [start2] [datetime] null,
        [start3] [datetime] null,
        [start4] [datetime] null,
        [start5] [datetime] null,
        [start6] [datetime] null,
        [start7] [datetime] null,
        [start8] [datetime] null,
        [start9] [datetime] null,
        [start10] [datetime] null,
        [start11] [datetime] null,
        [start12] [datetime] null,
        [start13] [datetime] null,
        [end1] [datetime] null,
        [end2] [datetime] null,
        [end3] [datetime] null,
        [end4] [datetime] null,
        [end5] [datetime] null,
        [end6] [datetime] null,
        [end7] [datetime] null,
        [end8] [datetime] null,
        [end9] [datetime] null,
        [end10] [datetime] null,
        [end11] [datetime] null,
        [end12] [datetime] null,
        [end13] [datetime] null
    )    


    if @fiscal_year >= year([dbo].[svf_lowdate]()) and @fiscal_year < year([dbo].[svf_highdate]())
    begin
        if @start1 is null        
            set @start1 = convert(datetime, convert(nvarchar(4),@fiscal_year) + '-01-01', 121)           
        insert into  #period_date ([fiscal_year]) values (@fiscal_year)    

        if @perioddatetype = n'm'
        begin
            declare @m tinyint = 1,@ms tinyint = 12
            while @m <= @ms
            begin
                declare @m_start_field nvarchar(128) = n'[start'+ convert(nvarchar(2), @m) +']'
                declare @m_start_value datetime = dateadd(m,@m -1,@start1)    
                
                declare @m_end_field nvarchar(128) = n'[end'+ convert(nvarchar(2), @m) +']'
                declare @m_end_value datetime = dateadd(day,-1, dateadd(m,@m,@start1))    
                
                declare @s_sql nvarchar(4000) = n'
                update #period_date set '+ @m_start_field +' = '''+ convert(nvarchar(40), @m_start_value ) +''',
                                        '+ @m_end_field +' = '''+ convert(nvarchar(40), @m_end_value ) +''' 
                where [fiscal_year] = '''+ convert(nvarchar(4),@fiscal_year) +''''
                execute sp_executesql @s_sql
                set @m = @m + 1
            end
        end

        if  @perioddatetype = n'q'
        begin
        declare @q tinyint = 1,@qs tinyint = 4
            while @q <= @qs
            begin
                declare @q_start_field nvarchar(128) = n'[start'+ convert(nvarchar(2), @q) +']'
                declare @q_start_value datetime = dateadd(quarter,@q -1,@start1)    
                
                declare @q_end_field nvarchar(128) = n'[end'+ convert(nvarchar(2), @q) +']'
                declare @q_end_value datetime = dateadd(day,-1, dateadd(quarter,@q,@start1))    
                
                declare @q_s_sql nvarchar(4000) = n'
                update #period_date set '+ @q_start_field +' = '''+ convert(nvarchar(40), @q_start_value ) +''',
                                        '+ @q_end_field +' = '''+ convert(nvarchar(40), @q_end_value ) +''' 
                where [fiscal_year] = '''+ convert(nvarchar(4),@fiscal_year) +''''
                execute sp_executesql @q_s_sql
                set @q = @q + 1
            end
        end
    end

    select [fiscal_year],
            [start1],[start2],[start3],[start4],
            [start5],[start6],[start7],[start8],
            [start9],[start10],[start11],[start12],
            [start13],
            [end1],[end2],[end3],[end4],
            [end5],[end6],[end7],[end8],
            [end9],[end10],[end11],[end12],
            [end13] 
    from #period_date

end