====== pears.NetFavourites ======
<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"."NetFavourites"( in "pWebUserID" char(20),in "pNodeID" char(20) default '' ) 
result( "Name" char(250),"Note" char(250),"Type" char(2),"NodeID" char(250),"ParentName" char(250),"LineID" char(20) ) 
begin
  declare "userClass" char(20);
  declare "pName" char(250);
  declare "RecID" char(20);
  declare "CompID" char(20);
  declare "RecType" char(1);
  declare "userstaffid" char(20);
  set "userstaffid" = (select first "staffid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  set "userClass" = (select first "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  if "userClass" <> 'OWNER' then
    select '','','','','','' from "dummy" where 1 = 2;
    return
  end if;
  if "nullif"("trim"("pNodeID"),'') is null then
    select first "FavouritesID" into "pNodeID" from "Favourites" where "description" = 'Favourites' and "parent" is null and "staffid" = "userstaffid" and "type" = 'H'
  end if;
  set "pName" = "NetFavouriteTitle"("pNodeID");
  select "id","type" into "RecID","RecType" from "Favourites" where "FavouritesID" = "pNodeID";
  set "compid" = "recid";
  if "rectype" = 'E' then
    select "companyid" into "compid" from "employment" where "employmentid" = "recid"
  end if;
  select "NetFavouriteTitle"("FavouritesID") as "Name",
    (if "type" <> 'H' then "Description" else null endif) as "Note",
    'N',
    "FavouritesID" as "NodeID",
    "pName" as "ParentName",
    "id" as "LineID"
    from "Favourites" where "Parent" = "pNodeID" and "staffid" = "userstaffid" union
  select 'Dial',"phone"."number",'PH',"digitsonly"("phone"."number"),"pName","phoneid"
    from "phone" key join "phonetype"
    where "rectype" in( 'P','C','E' ) and "phonetype"."capabilities" like '%D%' and "phone"."who" = (if "rectype" = 'E' then 'CP' else "rectype" endif)
    and "phone"."whoid" = "recid" union
  select 'Text Message',"phone"."number",'TX',"digitsonly"("phone"."number"),"pName","phoneid"
    from "phone" key join "phonetype"
    where "rectype" in( 'P','C','E' ) and "phonetype"."capabilities" like '%S%' and "phone"."who" = (if "rectype" = 'E' then 'CP' else "rectype" endif)
    and "phone"."whoid" = "recid" union
  select 'Email',"phone"."number",'EM',"urlsafe"("phone"."number"),"pName","phoneid"
    from "phone" key join "phonetype"
    where "rectype" in( 'P','C','E' ) and "phonetype"."capabilities" like '%E%' and "phone"."who" = (if "rectype" = 'E' then 'CP' else "rectype" endif)
    and "phone"."whoid" = "recid" union
  select 'Map',(if "rectype" = 'P' then "getpersonaddressonline"("recid") else "getcompanyaddressonline"("compid") endif),
    'MP',
    (if "rectype" = 'P' then(select "urlsafe"("string"("postcode",'(',"addr1",')')) from "person" where "personid" = "recid" and "trim"("isnull"("postcode",'')) <> '')
    else(select "string"("postcode",'(',"addr1",')') from "company" where "companyid" = "compid" and "trim"("isnull"("postcode",'')) <> '')
    endif) as "mapquery","pName",''
    from "dummy"
    where "rectype" in( 'P','C','E' ) and "mapquery" is not null
end /* TEST
call NetTestSetup('');

insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F1',null,null,'TEST','Favourites','H');
insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F2','F1','test','TEST','TestPerson','P');
insert into phone (phoneid,phonetypeid,who,whoid,number) values ('TESTPHONE','PH','P','TEST','favsPhone');
select * from NetFavourites('test.owner','');
expect name=test;
select * from NetFavourites('test.owner','F2') order by name;
expect name=Dial,note=favsPhone;
expect EOF;
select * from NetFavourites('');
expect EOF;

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetFavourites" IS 
{create PROCEDURE pears."NetFavourites"( in "pWebUserID" char(20),in "pNodeID" char(20) default '' ) 
result( "Name" char(250),"Note" char(250),"Type" char(2),"NodeID" char(250),"ParentName" char(250),LineID char(20) ) 
begin
  declare "userClass" char(20);
  declare "pName" char(250);
  declare "RecID" char(20);
  declare "CompID" char(20);
  declare "RecType" char(1);
  declare userstaffid char(20);
  set userstaffid=(select first "staffid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  set "userClass" = (select first "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  if "userClass" <> 'OWNER' then
    select '','','','','','' from "dummy" where 1 = 2;
    return
  end if;
  if "nullif"("trim"("pNodeID"),'') is null then
    select first "FavouritesID" into "pNodeID" from "Favourites" where "description" = 'Favourites' and "parent" is null and "staffid" = "userstaffid" and "type" = 'H'
  end if;
  set "pName" = "NetFavouriteTitle"("pNodeID");
  select "id","type" into "RecID","RecType" from "Favourites" where "FavouritesID" = "pNodeID";
  set "compid" = "recid";
  if "rectype" = 'E' then
    select "companyid" into "compid" from "employment" where "employmentid" = "recid"
  end if;
  select "NetFavouriteTitle"("FavouritesID") as "Name",
    (if "type" <> 'H' then "Description" else null endif) as "Note",
    'N',
    "FavouritesID" as "NodeID",
    "pName" as "ParentName",
    "id" as LineID
    from "Favourites" where "Parent" = "pNodeID" and "staffid" = "userstaffid" union
  select 'Dial',"phone"."number",'PH',"digitsonly"("phone"."number"),"pName",phoneid
    from "phone" key join "phonetype"
    where "rectype" in( 'P','C','E' ) and "phonetype"."capabilities" like '%D%' and "phone"."who" = (if "rectype" = 'E' then 'CP' else "rectype" endif)
    and "phone"."whoid" = "recid" union
  select 'Text Message',"phone"."number",'TX',"digitsonly"("phone"."number"),"pName",phoneid
    from "phone" key join "phonetype"
    where "rectype" in( 'P','C','E' ) and "phonetype"."capabilities" like '%S%' and "phone"."who" = (if "rectype" = 'E' then 'CP' else "rectype" endif)
    and "phone"."whoid" = "recid" union
  select 'Email',"phone"."number",'EM',"urlsafe"("phone"."number"),"pName",phoneid
    from "phone" key join "phonetype"
    where "rectype" in( 'P','C','E' ) and "phonetype"."capabilities" like '%E%' and "phone"."who" = (if "rectype" = 'E' then 'CP' else "rectype" endif)
    and "phone"."whoid" = "recid" union
  select 'Map',(if "rectype" = 'P' then "getpersonaddressonline"("recid") else "getcompanyaddressonline"("compid") endif),
    'MP',
    (if "rectype" = 'P' then(select "urlsafe"("string"("postcode",'(',"addr1",')')) from "person" where "personid" = "recid" and "trim"("isnull"("postcode",'')) <> '')
    else(select "string"("postcode",'(',"addr1",')') from "company" where "companyid" = "compid" and "trim"("isnull"("postcode",'')) <> '')
    endif) as "mapquery","pName",''
    from "dummy"
    where "rectype" in( 'P','C','E' ) and "mapquery" is not null
end

/* TEST
call NetTestSetup('');

insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F1',null,null,'TEST','Favourites','H');
insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F2','F1','test','TEST','TestPerson','P');
insert into phone (phoneid,phonetypeid,who,whoid,number) values ('TESTPHONE','PH','P','TEST','favsPhone');
select * from NetFavourites('test.owner','');
expect name=test;
select * from NetFavourites('test.owner','F2') order by name;
expect name=Dial,note=favsPhone;
expect EOF;
select * from NetFavourites('');
expect EOF;

*/
}
</code>