pears.NetUpdatePermissions

CREATE PROCEDURE "pears"."NetUpdatePermissions"( IN "pWebUserID" CHAR(20) ) 
RESULT( "State" VARCHAR(5),"Rights" long VARCHAR,"Switches" long VARCHAR ) 
BEGIN
  SELECT(SELECT "p"."status"
      FROM "PERSON" AS "p"
      WHERE "l"."personid" = "p"."personid") AS "state",
    (SELECT "LIST"("a"."IQXNetRightID")
      FROM "IQXNetRightAllowed" AS "a"
      WHERE "isnull"((SELECT FIRST "Assigned" FROM "IQXNetRightAssigned" WHERE "IQXNetRightID" = "a"."IQXNetRightID" AND "IQXNetUserID" = "u"."IQXNetUserID"),0)
      +"isnull"((SELECT FIRST 1 FROM "IQXNetRightDefault" WHERE "IQXNetRightID" = "a"."IQXNetRightID" AND "IQXNetUserClassID" = "a"."IQXNetUserClassID" AND "ID" = "a"."ID"),0) > 0
      AND "a"."IQXNetUserClassID" = "u"."IQXNetUserClassID" AND "a"."ID" = '*') AS "Rights",
    (SELECT "LIST"("IQXNetSwitchID")
      FROM "IQXNetSwitch"
      WHERE "IQXNetOverrideSwitchValue"("u"."IQXNetUserClassID","u"."IQXNetUserID","IQXNetSwitchID","Value") > 0) AS "Switches"
    FROM "IQXNetUser" AS "u"
      KEY JOIN "IQXNetUserLink" AS "l"
      KEY JOIN "IQXNetUserClass" AS "c"
    WHERE "u"."IQXNetUserID" = "pWebUserID"
END /* DOC
2020-01-31 GJ AW ET Fixed State
2020-01-07 GJ Added State
2019-07-29 GJ Created
 
This will re-fetch the current logged in users State, Rights & Switches to ensure they are kept up-to date.
The state is also used to to check that the candidate is not set to DNU
*/
/* TEST
call NetTestSetup('');
SELECT * FROM NetUpdatePermissions('');
EXPECT EOF;
SELECT * FROM NetUpdatePermissions('TEST.CANDIDATE');
EXPECT ROW;
EXPECT EOF;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetUpdatePermissions" IS 
{CREATE PROCEDURE pears."NetUpdatePermissions"(IN "pWebUserID" CHAR(20))
RESULT("State" VARCHAR(5), "Rights" long VARCHAR, "Switches" long VARCHAR)
BEGIN
    SELECT
        (SELECT 
            p.status 
         FROM 
            PERSON p 
         WHERE 
            l.personid = p.personid) AS state,
        (SELECT 
            LIST(a.IQXNetRightID) 
         FROM
            IQXNetRightAllowed AS a 
         WHERE
            isnull((SELECT FIRST Assigned FROM IQXNetRightAssigned WHERE IQXNetRightID = a.IQXNetRightID AND IQXNetUserID = u.IQXNetUserID),0)+
            isnull((SELECT FIRST 1 FROM IQXNetRightDefault WHERE IQXNetRightID = a.IQXNetRightID AND IQXNetUserClassID = a.IQXNetUserClassID AND ID = a.ID),0) > 0 AND
            a.IQXNetUserClassID = u.IQXNetUserClassID AND a.ID = '*') AS Rights,
        (SELECT 
            LIST(IQXNetSwitchID) 
         FROM 
            IQXNetSwitch 
         WHERE 
            IQXNetOverrideSwitchValue(u.IQXNetUserClassID,u.IQXNetUserID,IQXNetSwitchID,VALUE)>0) AS Switches
    FROM 
        IQXNetUser AS u 
        KEY JOIN
            IQXNetUserLink AS l
        KEY JOIN 
            IQXNetUserClass AS c
    WHERE
        u.IQXNetUserID = pWebUserID
END
/* DOC
    2020-01-31 GJ AW ET Fixed State
    2020-01-07 GJ Added State
    2019-07-29 GJ Created
 
    This will re-fetch the current logged in users State, Rights & Switches to ensure they are kept up-to date.
    The state is also used to to check that the candidate is not set to DNU
*/
 
/* TEST
call NetTestSetup('');
SELECT * FROM NetUpdatePermissions('');
EXPECT EOF;
SELECT * FROM NetUpdatePermissions('TEST.CANDIDATE');
EXPECT ROW;
EXPECT EOF;
*/
}