pears.NetFavourites

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original 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;
 
*/
}