/* AccountsTree-sql */
# 4/2/11
#  based on https://lists.gnucash.org/pipermail/gnucash-user/2009-July/030590.html

# hard-coded to 6 levels (0-5) to match my CoA


DELIMITER go
DROP PROCEDURE IF EXISTS acct_tree;
CREATE PROCEDURE acct_tree()
BEGIN
DELETE FROM accounts_tree;
INSERT INTO accounts_tree (guid,dummy,level5,level4,level3,level2,level1,level0)

(select
       leaf.guid AS guid
     , REPLACE(CONCAT_WS  (':',up5.name,up4.name,up3.name,up2.name,up1.name,leaf.name),'Root Account:','') AS dummy
     , leaf.name AS level0
     , up1.name as level1
     , up2.name as level2
     , up3.name as level3
     , up4.name as level4
     , up5.name as level5
  from accounts as leaf
left outer 
  join accounts as up1
    on leaf.parent_guid = up1.guid
left outer 
  join accounts as up2
    on up1.parent_guid = up2.guid
left outer 
  join accounts as up3
    on up2.parent_guid = up3.guid
left outer 
  join accounts as up4
    on up3.parent_guid = up4.guid
left outer 
  join accounts as up5
    on up4.parent_guid = up5.guid
/*
left outer 
  join accounts as up6
    on up5.parent_guid = up6.guid
*/
  where leaf.parent_guid IS NOT NULL
order 
    by  leaf.name 
     , up1.name 
     , up2.name 
     , up3.name
     , up4.name
);

-- move columns over; with 6 levels (0-5), there can be at most 4 null columns (root, and top-level are non-null)
UPDATE accounts_tree  
   SET level0=level1, level1=level2, level2=level3,level3=level4,level4=level5, level5=NULL WHERE level0 IS NULL;
UPDATE accounts_tree  
   SET level0=level1, level1=level2, level2=level3,level3=level4,level4=level5, level5=NULL WHERE level0 IS NULL;
UPDATE accounts_tree  
   SET level0=level1, level1=level2, level2=level3,level3=level4,level4=level5, level5=NULL WHERE level0 IS NULL;
UPDATE accounts_tree  
   SET level0=level1, level1=level2, level2=level3,level3=level4,level4=level5, level5=NULL WHERE level0 IS NULL;


END;
go

DELIMITER ;
-- source AccountsView-sql;
call acct_tree;		-- restores accounts_tree and drops transactions_view
-- call tx_view;			-- restores transactions_view