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