pears.NetFavouriteTitle

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" AS "v" KEY JOIN "employment" KEY JOIN "company" AS "c" WHERE "vacancyid" = "favourites"."id")
    WHEN 'L' THEN(SELECT "pe"."name"+' as '+"v"."position"+' at '+"c"."name" FROM "placement" KEY JOIN "employment" AS "em" KEY JOIN "person" AS "pe","placement" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" KEY JOIN "company" AS "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
*/