pears.NetOwnerAddToFavs

CREATE PROCEDURE "pears"."NetOwnerAddToFavs"( IN "pWebUserID" CHAR(20),IN "pParent" CHAR(20),IN "pLinkID" CHAR(20),IN "pType" CHAR(1) ) 
/* RESULT( column_name column_type, ... ) */
BEGIN
  DECLARE "pStaffID" CHAR(20);
  SET "pStaffID" = (SELECT "staffid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "isnull"("pStaffID",'') = '' THEN
    SELECT '99:~invalid staff';
    RETURN
  END IF;
  IF "isnull"("pType",'') = '' THEN
    SELECT '98:~invalid type';
    RETURN
  END IF;
  INSERT INTO "Favourites"( "FavouritesID","Parent","ID","Staffid","Description","Type" ) VALUES
    ( "uniquekey"('x'),"pParent","pLinkID","pStaffID",'',"pType" ) ;
  SELECT '0:~Success'
END /* DOC
2016-12-01 PC Doc and test
2018-08-27 PC improve error handling
 
Tests:
invalid pWebUserID
valid pWebUserID, pParent, pLinkID, invalid pType
valid pWebUserID, pParent, pLinkID, pType
 
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
insert into Favourites (FavouritesID, Parent, ID, Staffid, Description, Type) on existing update values ('TEST', null, null, 'TEST', '', 'H');
select * from NetOwnerAddToFavs(null,null,null,null);
expect 99*;
select * from NetOwnerAddToFavs('test.owner',null,null,null);
expect 98*;
select * from NetOwnerAddToFavs('test.owner','TEST','TEST','P');
expect 0*;
select * from favourites where staffID='TEST' and favouritesid != 'TEST' order by FavouritesID asc;
expect Staffid=TEST,Type=H;
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerAddToFavs" IS 
{CREATE PROCEDURE pears."NetOwnerAddToFavs"( IN "pWebUserID" CHAR(20), IN "pParent" CHAR(20), IN "pLinkID" CHAR(20), IN "pType" CHAR(1) )
/* RESULT( column_name column_type, ... ) */
BEGIN
    DECLARE pStaffID CHAR(20);
    SET pStaffID=(SELECT staffid FROM iqxnetuser WHERE iqxnetuserid=pWebUserID);
    IF isnull(pStaffID,'')='' THEN
        SELECT '99:~invalid staff';
        RETURN;
    END IF;
    IF isnull(pType,'')='' THEN
        SELECT '98:~invalid type';
        RETURN;
    END IF;
    INSERT INTO Favourites (FavouritesID, Parent, ID, Staffid, Description, TYPE) 
    VALUES (uniquekey('x'), pParent, pLinkID, pStaffID, '', pType);
 
    SELECT '0:~Success';
END
 
/* DOC
2016-12-01 PC Doc and test
2018-08-27 PC improve error handling
 
Tests:
    invalid pWebUserID
    valid pWebUserID, pParent, pLinkID, invalid pType
    valid pWebUserID, pParent, pLinkID, pType
 
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
insert into Favourites (FavouritesID, Parent, ID, Staffid, Description, Type) on existing update values ('TEST', null, null, 'TEST', '', 'H');
select * from NetOwnerAddToFavs(null,null,null,null);
expect 99*;
select * from NetOwnerAddToFavs('test.owner',null,null,null);
expect 98*;
select * from NetOwnerAddToFavs('test.owner','TEST','TEST','P');
expect 0*;
select * from favourites where staffID='TEST' and favouritesid != 'TEST' order by FavouritesID asc;
expect Staffid=TEST,Type=H;
 
*/
}