pears.CollectionAddChild

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
}