====== pears.NetUpdatePermissions ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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;
*/
}
</code>