pears.NetOwnerFavourites

CREATE PROCEDURE "pears"."NetOwnerFavourites"( IN "pWebUserID" CHAR(20),IN "pStaffID" CHAR(20) DEFAULT NULL ) 
RESULT( "favouriteID" CHAR(20),"favouriteName" CHAR(100),"favouriteDescription" long VARCHAR,"favouriteType" CHAR(10),"objectID" CHAR(20),"parentFavouriteID" CHAR(20),"isNode" INTEGER ) 
BEGIN
  SET "pStaffID" = "isnull"("nullif"("pStaffID",''),"userStaffID");
  SELECT "f"."FavouritesID" AS "FavouriteID",
    "NetFavouriteTitle"("FavouriteID") AS "ObjTitle",
    "isnull"("Description",'') AS "ObjDescription",
    (CASE "Type"
    WHEN 'H' THEN 'Heading'
    WHEN 'C' THEN 'Company'
    WHEN 'E' THEN 'Contact'
    WHEN 'P' THEN 'Person'
    WHEN 'V' THEN 'Vacancy'
    WHEN 'L' THEN 'Placement' END) AS "ObjType",
    "ID" AS "ObjID",
    "isnull"("parent",'') AS "ParentID",
    (SELECT(IF "count"() > 0 THEN 1 ELSE 0 endif) FROM "favourites" AS "f" WHERE "FavouriteID" = "f"."parent") AS "IsNode"
    FROM "Favourites" AS "f"
      KEY JOIN "staff" AS "s"
    WHERE "f"."staffid" = "pStaffID" AND "s"."divisionid" = any(SELECT "divisionid" FROM "dashboardstaffdivisions"())
END /* DOC
2016-08-11 PC RC1 -  create child list of any item
2016-08-23 PC Doc and test
2019-07-09 ET Changed procedure to return all favourite records for a given staffID (default to userStaffID if not specified)
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerFavourites" IS 
{CREATE PROCEDURE pears."NetOwnerFavourites"(IN pWebUserID CHAR(20), IN pStaffID CHAR(20) DEFAULT NULL)
RESULT(favouriteID CHAR(20),favouriteName CHAR(100),favouriteDescription long VARCHAR,favouriteType CHAR(10),objectID CHAR(20),parentFavouriteID CHAR(20),isNode INTEGER)
BEGIN
    SET pStaffID = isnull(NULLIF(pStaffID, ''), userStaffID);
 
    SELECT 
        f.FavouritesID AS FavouriteID,
        NetFavouriteTitle(FavouriteID) AS ObjTitle,
        isnull(Description,'') AS ObjDescription,
        (CASE TYPE
         WHEN 'H' THEN 'Heading'
         WHEN 'C' THEN 'Company'
         WHEN 'E' THEN 'Contact'
         WHEN 'P' THEN 'Person'
         WHEN 'V' THEN 'Vacancy'
         WHEN 'L' THEN 'Placement'
        END) AS ObjType,
        ID AS ObjID,
        isnull(parent,'') AS ParentID,
        (SELECT (IF COUNT(*) > 0 THEN 1 ELSE 0 endif)FROM favourites f WHERE FavouriteID=f.parent) AS IsNode
    FROM Favourites f
        KEY JOIN staff s
    WHERE f.staffid = pStaffID AND s.divisionid IN (SELECT divisionid FROM dashboardstaffdivisions()) 
END
 
/* DOC
  2016-08-11 PC RC1 -  create child list of any item
  2016-08-23 PC Doc and test
  2019-07-09 ET Changed procedure to return all favourite records for a given staffID (default to userStaffID if not specified)
*/
}