pears.NetFavouriteTitle

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetFavouriteTitle" IS 
{CREATE FUNCTION pears."NetFavouriteTitle"( IN "pNodeID" CHAR(20) ) 
RETURNS CHAR(250)
BEGIN
  DECLARE "rv" CHAR(250);
  SELECT(CASE "type" 
    WHEN 'C' THEN (SELECT "string"("name",', ',"addr1",', ',"postcode") FROM "company" WHERE "companyid" = "favourites"."id") 
    WHEN 'E' THEN (SELECT "string"("p"."name",', ',"c"."name") FROM "employment" AS "e" KEY JOIN("company" AS "c","person" AS "p") WHERE "e"."employmentid" = "favourites"."id") 
    WHEN 'P' THEN (SELECT "name" FROM "person" WHERE "personid" = "favourites"."id")
    WHEN 'V' THEN (SELECT v.position+' - '+ c.name FROM vacancy v KEY JOIN employment KEY JOIN company c WHERE vacancyid=favourites.id)
    WHEN 'L' THEN (SELECT pe.name+' as '+v.position+' at '+c.name FROM placement KEY JOIN employment em KEY JOIN person pe,placement KEY JOIN vacancy v KEY JOIN employment KEY JOIN company c WHERE placementid=favourites.id)
    ELSE "Description"
    END) INTO "rv" FROM "Favourites" WHERE "FavouritesID" = "pNodeID";
  RETURN "rv"
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 favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F3','F1','TEST','TEST','TestPerson','C');
insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F4','F1','TEST','TEST','TestPerson','E');
update company set addr1='AddressLine',postcode='XX1 1XX' where companyid='TEST';
select NetFavouriteTitle('F1');
expect Favourites;
expect EOF;
select NetFavouriteTitle('F2');
expect test;
expect EOF;
select replace(NetFavouriteTitle('F3'),',','!');
expect test! AddressLine! XX1 1XX;
expect EOF;
select replace(NetFavouriteTitle('F4'),',','!');
expect test! test;
expect EOF;
select replace(NetFavouriteTitle('F5'),',','!');
expect;
expect EOF;
update company set addr1='',postcode='' where companyid='TEST';
*/
 
/* DOC
2016-08-11 V1.2 Vacancy line and Placement line added
*/
}