====== pears.NetOwnerAddToFavs ======
<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"."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;

*/
}
</code>