pears.CollectionAllChildrenJsonTree

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'