cwfx_accountstrua.txt

财务分析 财财务分析务分析

/*功能说明 给出参数：@ccode科目代码，@AccountType及科目分析方向
--@CY int,@CM1 int,@CM2 int 为当前时间段年月   @CmpY int,@CmpM1 int,@CmpM2 int为比较期时间段年月
结果输出该科目下明细科目在分析期间及比较期间的科目分析方向的金额
如果是余额则为对应期间末的余额（VB程序中来实现参数月的变换）
*/
CREATE PROCEDURE dbo.cwfx_AccountStruA @Ccode varchar(15),@AccountType varchar(10),@CY int,@CM1 int,@CM2 int,@CmpY int=0,@CmpM1 int=0,@CmpM2 int=0
AS
    declare @CcodeChild varchar(15),@Cname varchar(40),@Curr_V money,@Comp_V money
--@Curr_V money, 本期发生,@Comp_V money 比较期发生

BEGIN
if exists (select 1 from tempdb.dbo.sysobjects where name='##temp')
	drop table ##temp

create table ##temp(Ccode varchar(15),Cname varchar(40),Curr_V money,Comp_V money)
declare Analysis_Cursor cursor for
    Select CCode From cwzz_AccCode where ParentCode=@Ccode order by ccode
open Analysis_Cursor
fetch next from Analysis_Cursor into @CcodeChild
while  (@@FETCH_STATUS = 0)
begin

    select @Cname=Cname from cwzz_Acccode where ccode=@CcodeChild
    set @Curr_V=0
    set @Comp_V=0
    SELECT   @Curr_V=CASE @AccountType
	WHEN '借方发生额' THEN sum(mjje)
	WHEN '贷方发生额' THEN sum(mdje)
	WHEN '余额' THEN sum(qmye)
	 END  
	FROM cwzz_AccSum
	WHERE ccode=@CcodeChild and year=@cy and period between @cm1 and @cm2
    
    SELECT   @Comp_V=CASE @AccountType
	WHEN '借方发生额' THEN sum(mjje)
	WHEN '贷方发生额' THEN sum(mdje)
	WHEN '余额' THEN sum(qmye)
	 END  
	FROM cwzz_AccSum
	WHERE ccode=@CcodeChild and year=@CmpY and period between @CmpM1 and @CmpM2

    insert into ##temp values(@CcodeChild,@Cname,@Curr_V,@Comp_V)
    Fetch next from Analysis_Cursor into @CcodeChild
end
close Analysis_Cursor
deallocate  Analysis_Cursor
--select * from ##temp 
--drop table ##temp
END
GO