====== pears.CollectionAddChild ======
<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"."CollectionAddChild"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pCollectionID" char(20),in "pItemID" char(25),in "pDragSourceType" char(20),in "pnotes" char(100),in "pstaffid" char(20) ) 
result( "pResult" char(50) ) 
begin
  declare "idtype1" varchar(10);
  declare "ItemID" varchar(21);
  declare "bselect" tinyint;
  declare "pno" char(20);
  declare "loopbrk" tinyint;
  declare "loopcnt" integer;
  set "bselect" = 0;
  set "itemid" = "pitemid";
  set "idtype1" = '';
  if "length"("pDragSourceType") = 1 then
    set "idtype1" = "substr"("pdragsourcetype",1)
  else
    case "pDragSourceType"
    when 'Contact' then set "idtype1" = 'O'
    when 'Invoice' then set "idtype1" = 'I'
    when 'Shift' then set "idtype1" = 'S'
    when 'Timesheet' then
      set "ItemID" = "substr"("pItemId",2);
      if "substr"("pItemId",1,1) = 'T' then
        set "idtype1" = 'T'
      else set "idtype1" = 'Z'
      end if else
      if("length"("pdragsourcetype") = 4) and("substr"("pDragSourceType",2) = 'sel') then
        set "idtype1" = "substr"("pDragSourceType",1,1);
        set "bselect" = 1
      else set "idtype1" = ''
      end if
    end case
  end if;
  set "loopcnt" = 0;
  set "loopbrk" = 0;
  set "pno" = "pcollectionID";
  while("loopbrk" = 0) and("loopcnt" < 10) loop
    select "parent" into "pno" from "collection" where "collectionid" = "pno";
    if "pno" is null then
      set "loopbrk" = 1
    else
      set "loopcnt" = "loopcnt"+1
    end if
  end loop;
  if "loopcnt" > 5 then
    select '3:~Maximum of 5 levels'
  else
    if "isnull"("idtype1",'') = '' then
      select "string"('1:~Unknown ID type ',"idtype1")
    else if not exists(select * from "collection" where "collectionid" = "pcollectionid") then
        select '2:~Collection Parent not found'
      else if "bselect" = 0 then
          insert into "collection"( "CollectionID","Parent","staffid","ID","IDType","Notes","whencreated","whocreated","name" ) values
            ( "uniquekey"("pcollectionID"),"nullif"("pCollectionID",''),
            if "isnull"("pstaffid",'') = '' then "userstaffid" else "pstaffid" endif,"ItemID","string"("idtype1"),"pNotes",current timestamp,"userstaffid","string"("idtype1") ) ;
          select '0:~Success'
        else
          insert into "collection"( "CollectionID","Parent","staffid","IDType","ID","Notes","whencreated","whocreated","name" ) 
            select "uniquekey"("record"),"nullif"("pCollectionID",''),
              if "isnull"("pstaffid",'') = '' then "userstaffid" else "pstaffid" endif,"string"("idtype1"),"record","pNotes",current timestamp,"userstaffid","string"("idtype1")
              from "storedselectionmember" where "storedselectionid" = "itemid"
              and not exists(select * from "collection" where "id" = "record" and "idtype" = "string"("idtype1") and "parent" = "nullif"("pCollectionID",''));
          select '0:~Success'
        end if end if end if end if
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."CollectionAddChild" IS 
{create procedure CollectionAddChild 

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

(  in pCollectionID char(20),  pItemID char(25),  pDragSourceType char(20), pnotes char(100), pstaffid char(20))
result (pResult char(50))
begin
  declare idtype1 varchar(10); 
  declare ItemID varchar(21);
  declare bselect tinyint; 
  declare pno char(20);
  declare loopbrk tinyint; 
  declare loopcnt Integer; 
  set bselect = 0; 
  set itemid = pitemid; 
  set idtype1 = ''; 
  if length(pDragSourceType ) =1 then 
    set  idtype1 = substr(pdragsourcetype,1) 
  else
   case pDragSourceType 
    when  'Contact' then   set  idtype1 = 'O' 
    when  'Invoice' then   set  idtype1 = 'I' 
    when  'Shift' then   set  idtype1 = 'S'
    when  'Timesheet' then  
         set  ItemID  = substr(pItemId, 2);  
          if substr(pItemId, 1,1) = 'T' then 
                set idtype1 = 'T' 
         else SET idtype1 = 'Z'
      end if 
  else 
  if ( length(pdragsourcetype)  = 4) and (substr(pDragSourceType, 2) = 'sel')  then 
       set  idtype1 = substr(pDragSourceType, 1, 1); 
       set  bselect = 1;
   else set idtype1 = ''  
   end if ; 
  end   
  end if; 
  set loopcnt =0; 
  set loopbrk = 0; 
  set pno = pcollectionID; 
  WHILE (loopbrk =0) and (loopcnt<10) LOOP      
    select parent into pno from collection where collectionid = pno;
 	if pno is null  then   
                   set loopbrk =1
               else 
	   SET loopcnt = loopcnt + 1;
    end if 
  END LOOP; 
 if loopcnt >5  then 
    select '3:~Maximum of 5 levels'; 
 else 
  if isnull(idtype1 ,'')= '' then 
    select string( '1:~Unknown ID type ', idtype1);  
 else  if  NOT exists(select * from collection where collectionid =  pcollectionid) then  
   select '2:~Collection Parent not found';  
 else if bselect = 0 then   
   INSERT INTO collection ( CollectionID,Parent,staffid,ID,IDType,Notes, whencreated, whocreated, name ) 
            values(uniquekey(pcollectionID) , nullif(pCollectionID,''),  
             if isnull(pstaffid,'')='' then userstaffid else pstaffid endif,  ItemID, string(idtype1) , pNotes, current timestamp, userstaffid, string(idtype1)); 
   select '0:~Success';  
 else   
    INSERT INTO collection ( CollectionID,Parent,staffid,IDType,ID,Notes, whencreated, whocreated, name )  
       select      uniquekey(record) , nullif(pCollectionID,''),
         if isnull(pstaffid,'')='' then userstaffid else pstaffid endif,  string(idtype1) , record, pNotes, current timestamp, userstaffid, string(idtype1)
    from storedselectionmember where storedselectionid = itemid and 
    not exists(select * from collection where id=record and idtype = string(idtype1) and parent = nullif(pCollectionID,'') ) ; 
                 
 select '0:~Success';
  
end if end if  end if end if; 
end
}
</code>