pears.NetOwnerIsFav

CREATE PROCEDURE "pears"."NetOwnerIsFav"( IN "pWebUserID" CHAR(20),IN "pLinkID" CHAR(20) ) 
RESULT( "favid" CHAR(100) ) 
BEGIN
  IF((SELECT "count"() FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID") = 0) THEN
    SELECT '99:~invalid user';
    RETURN
  END IF;
  SELECT "FavouritesID" AS "favid"
    FROM "Favourites"
    WHERE "ID" = "pLinkID"
END /* DOC
2016-12-01 PC test and doc
added favid as result, previously undefined
 
Inputs:
pWebUserID
pLinkID
 
Outputs:
undefined
 
Tests:
invalid pWebUserID
valid pWebUserID invalid pLinkID
valid pWebUserID, pLinkID
 
*/
/* 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 skip values ('TEST', null, null, 'TEST', '', 'H');
insert into Favourites (FavouritesID, Parent, ID, Staffid, Description, Type) on existing skip values ('TEST2', 'TEST', 'TEST', 'TEST', 'test', 'P');
select * from NetOwnerIsFav(null,null);
expect 99*;
select * from NetOwnerIsFav('test.owner',null);
expect EOF;
select * from NetOwnerIsFav('test.owner','TEST');
expect favid=TEST2;
expect EOF;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerIsFav" IS 
{CREATE PROCEDURE pears."NetOwnerIsFav"( IN "pWebUserID" CHAR(20),IN "pLinkID" CHAR(20) )
RESULT(favid CHAR(100))
BEGIN
    IF ((SELECT COUNT(*) FROM iqxnetuser WHERE iqxnetuserid=pWebUserID)=0) THEN
        SELECT '99:~invalid user';
        RETURN;
    END IF;
    SELECT FavouritesID AS favid
    FROM Favourites 
    WHERE ID=pLinkID
END
 
/* DOC
2016-12-01 PC test and doc
    added favid as result, previously undefined
 
Inputs:
    pWebUserID
    pLinkID
 
Outputs:
    undefined
 
Tests:
    invalid pWebUserID
    valid pWebUserID invalid pLinkID
    valid pWebUserID, pLinkID
 
*/
 
/* 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 skip values ('TEST', null, null, 'TEST', '', 'H');
insert into Favourites (FavouritesID, Parent, ID, Staffid, Description, Type) on existing skip values ('TEST2', 'TEST', 'TEST', 'TEST', 'test', 'P');
select * from NetOwnerIsFav(null,null);
expect 99*;
select * from NetOwnerIsFav('test.owner',null);
expect EOF;
select * from NetOwnerIsFav('test.owner','TEST');
expect favid=TEST2;
expect EOF;
*/
}