====== pears.CollectionAllChildrenJsonTree ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."CollectionAllChildrenJsonTree"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pcollection" char(20) ) 
result( "jstr" long varchar ) 
begin
  declare "loopcnt" integer;
  declare "rv" long varchar;
  declare local temporary table "collrecords"(
    "collectionid" char(20) null,
    "parentid" char(20) null,
    "Indent" integer null,
    "pstr" long varchar not null,
    "ino" integer null,
    "pno" integer null,
    primary key("pstr"),) not transactional;insert into "Collrecords"( "CollectionID","Parentid","Indent","pstr" ) select "CollectionID","Parent",0,'0001' from "collection" where "collectionid" = "pcollection";
  set "loopcnt" = 0;
  while "loopcnt" < 20 loop
    insert into "Collrecords"( "CollectionID","Parentid","Indent","pstr" ) 
      select "C"."CollectionID","C"."Parent","loopcnt"+1,"string"("cc"."pstr","right"("string"('0000',"number"()),4))
        from "collection" as "c" join "Collrecords" as "cc" on("cc"."collectionid" = "c"."parent") and "indent" = "loopcnt" order by "c"."sortorder" asc;
    set "loopcnt" = "loopcnt"+1
  end loop;
  update "Collrecords" set "ino" = "number"() from "Collrecords" order by "pstr" asc;
  select "pstr" into "rv" from "collrecords" where "indent" = 0;
  update "Collrecords" as "cc"
    set "pstr"
     = (select "c"."CollectionID","c"."ParentID","ccc"."Name","ccc"."StaffID","ccc"."IDType","ccc"."ID","ccc"."Notes","ccc"."SortOrder",
      if "ccc"."idtype" = 'I' then
        "Isnull"((select "documenttype" from "iqacdocument" where "documentid" = "ccc"."id"),'Invoice')
      else "CollectionGetItemTypeName"("ccc"."IDType")
      endif as "ItemTypeDescription","CollectionItemDescription"("c"."collectionid","ccc"."idtype","ccc"."id") as "ItemDescription",
      case "ccc"."idtype"
      when 'E' then(select "companyid" from "employment" where "employmentid" = "ccc"."id")
      when 'I' then(select "ourref" from "iqacdocument" where "documentid" = "ccc"."id")
      when 'T' then(select "serialnumber" from "temptimesheet" where "temptimesheetid" = "ccc"."id")
      else ''
      end as "ID2","Indent",
      null as "nodes"
      from "collrecords" as "c" join "collection" as "ccc" on("ccc"."collectionid" = "c"."collectionid") where "c"."ino" = "cc"."ino" for json raw);
  update "collrecords" set "pstr" = "replace"("replace"("pstr",'[{','{'),'}]','}');
  while "loopcnt" >= 0 loop
    update "collrecords" as "cc"
      set "pstr"
       = "replace"("pstr",'"nodes":null',"string"('"nodes":',"isnull"("nullif"("string"('[',(select "list"("pstr",',') from "collrecords" as "c" where "c"."parentID" = "cc"."collectionid"),']'),'[]'),'null'))) where "indent" = "loopcnt";
    set "loopcnt" = "loopcnt"-1
  end loop;
  select "string"('[',"pstr",']') from "collrecords" where "indent" = 1
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."CollectionAllChildrenJsonTree" IS 
'create procedure CollectionAllChildrenJsonTree 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in pcollection char(20))
result (jstr long varchar)
begin
  declare loopcnt integer;
  declare rv long varchar;
  declare local temporary table collrecords(collectionid char(20), parentid char(20), Indent integer, pstr long varchar, ino Integer, pno Integer,  primary key(pstr))  not transactional;
  Insert into Collrecords (CollectionID,Parentid,Indent, pstr)select CollectionID,Parent, 0, ''0001''  from collection where collectionid =pcollection;
  set loopcnt =0;
  while loopcnt < 20  LOOP
     Insert into Collrecords (CollectionID,Parentid,  Indent, pstr)
      select C.CollectionID,C.Parent,loopcnt +1, string(cc.pstr,  right(string(''0000'', number()),4))
      from collection c join Collrecords cc on (cc.collectionid = c.parent) and indent = loopcnt order by c.sortorder;
     SET loopcnt = loopcnt + 1;
  end LOOP;
  update Collrecords  set ino = number()  from Collrecords order by pstr;
  select pstr into rv from collrecords where indent = 0;
  update Collrecords  cc  set pstr =(
  select c.CollectionID,c.ParentID,ccc.Name,ccc.StaffID,ccc.IDType,ccc.ID,ccc.Notes,ccc.SortOrder,
    if ccc.idtype = ''I'' then
         Isnull( (select documenttype from iqacdocument where documentid = ccc.id) , ''Invoice'')
    else CollectionGetItemTypeName (ccc.IDType) end if  as ItemTypeDescription ,
    CollectionItemDescription (c.collectionid, ccc.idtype, ccc.id) as ItemDescription,
    case ccc.idtype
           when ''E'' then (select companyid from employment where employmentid = ccc.id) 
           when ''I'' then (select ourref from iqacdocument where documentid = ccc.id) 
           when ''T'' then (select serialnumber from temptimesheet where temptimesheetid = ccc.id) 
           else ''''
    end case as ID2 , Indent,
    null as nodes

   from collrecords c join collection ccc on (ccc.collectionid = c.collectionid)  where c.ino = cc.ino for json raw) ;
   update collrecords set pstr = replace( replace(pstr,''[{'',''{''),''}]'',''}'');

  while loopcnt >=0  LOOP
     update collrecords cc set pstr = 

  replace(pstr,''"nodes":null'' , string(''"nodes":'',  isnull(  nullif(string(''['',(select list(pstr,'','') from collrecords c  where c.parentID = cc.collectionid),'']''),''[]'')   , ''null'')))    where indent = loopcnt;  SET loopcnt = loopcnt - 1;
end LOOP;
  select string(''['', pstr,'']'')  from collrecords where indent  =1;
end'
</code>