厦门用友畅捷通T+ 下级科目汇总不到一级科目

备份账套。数据库执行语句；

declare @currentyear int

set @currentyear=2022

update aa_account set idparent=(select id from (select id,substring(code,1,4) code,accountingyear from aa_account where code in

(select substring(code,1,4) from aa_account where LEN(code)=6 ) and accountingyear=@currentyear

) as a where aa_account.depth=2 and aa_account.accountingyear=@currentyear and substring(aa_account.code,1,4)=a.code)

where depth=2 and accountingyear=@currentyear

update aa_account set idparent=(select id from (select id,substring(code,1,6) code,accountingyear from aa_account where code in

(select substring(code,1,6) from aa_account where LEN(code)=8) and accountingyear=@currentyear

) as a where aa_account.depth=3 and aa_account.accountingyear=@currentyear and substring(aa_account.code,1,6)=a.code)

where depth=3 and accountingyear=@currentyear

update aa_account set idparent=(select id from (select id,substring(code,1,8) code,accountingyear from aa_account where code in

(select substring(code,1,8) from aa_account where LEN(code)=10) and accountingyear=@currentyear

) as a where aa_account.depth=4 and aa_account.accountingyear=@currentyear and substring(aa_account.code,1,8)=a.code)

where depth=4 and accountingyear=@currentyear

declare @MaxDepthValue int

declare @CurrentDetphValue int

set @maxDepthValue=(select MAX(depth) from AA_Account where accountingyear=@currentyear)

set @CurrentDetphValue=1

while(@CurrentDetphValue<=@MaxDepthValue)

begin

if(@CurrentDetphValue=1)

begin

update AA_Account set inId = CONVERT(varchar(36),id) where depth=1 and accountingyear=@currentyear

end

else if (@CurrentDetphValue=2)

begin

update AA_Account set inId =CONVERT(varchar(36),idParent) + '_' + CONVERT(varchar(36),id) where depth=2 and accountingyear=@currentyear

end

else if (@CurrentDetphValue >2)

begin

update AA_Account set inId =(select distinct CONVERT(varchar(max),inid) from AA_Account A where A.id=AA_Account.idParent and A.depth=AA_Account.depth-1 ) + '_' + CONVERT(varchar(36),id)

from AA_Account where depth=@CurrentDetphValue and accountingyear=@currentyear

end

set @CurrentDetphValue=@CurrentDetphValue+1

end

truncate table AA_ParentAccount

insert into AA_ParentAccount (idParent,idSon,Depth,isEndNode)

select p.id as idParent,s.id as idSon,p.depth-1 as Depth,p.IsEndNode as isEndNode from AA_Account p

left join AA_Account s

on s.code like p.code+'%' and s.isEndNode =1 and s.accountingyear=p.accountingyear

GO

下级科目汇总不到一级科目.txt