====== pears.NetContactRights ======
<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"."NetContactRights"( in "pWebUserID" char(20),in "pEmploymentID" char(20) ) 
result( "RightID" char(20),"RightDescription" char(100),"RightDefault" char(100),"RightAssigned" integer,"RightAssignedLabel" char(100) ) 
//IQXWeb
begin
  declare "cid" char(20);
  declare "pRequestedWebUserID" char(20);
  declare "pUserClassID" char(20);
  set "cid" = (select "companyid" from "employment" where "employmentid" = "pEmploymentID");
  if not "cid" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") then
    select "IQXNetRightID","Description",'' as "RightDefault",0 as "RightAssigned",'' as "RightAssignedLabel" from "IQXNetRight" where "IQXNetRightID" is null
  else
    set "pRequestedWebUserID" = (select first "IQXNetUserID" from "IQXNetUserLink" where "employmentid" = "pEmploymentID");
    set "pUserClassID" = (select first "IQXNetUserClassID" from "IQXNetUser" where "IQXNetUserID" = "pRequestedWebUserID");
    select "IQXNetRight"."IQXNetRightID" as "RightID",
      "IQXNetRight"."Description" as "RightDescription",
      if exists(select * from "IQXNetRightDefault" where "IQXNetRightID" = "IQXNetRight"."IQXNetRightID" and "IQXNetUserClassID" = "pUserClassID" and "ID" = '*') then 'Allowed'
      else 'Denied'
      endif as "RightDefault","isnull"("IQXNetRightAssigned"."Assigned",0) as "RightAssigned",
      (case "RightAssigned" when-1 then 'Deny' when 0 then '' when 1 then 'Allow' end) as "RightAssignedLabel"
      from "IQXNetRightAllowed" left outer join "IQXNetRightAssigned" on "IQXNetRightAllowed"."IQXNetRightID" = "IQXNetRightAssigned"."IQXNetRightID" and "IQXNetRightAssigned"."IQXNetUserID" = "pRequestedWebUserID"
        ,"IQXNetRightAllowed" key join "IQXNetRight"
      where "IQXNetRightAllowed"."IQXNetUserClassID" = "pUserClassID" and "IQXNetRightAllowed"."ID" = '*' and "IQXNetRightAllowed"."MaintainOnWeb" = 1
      order by "IQXNetRight"."sortorder" asc,"IQXNetRight"."IQXNetRightID" asc
  end if
end /* DOC
2016-08-26 PC Test and doc

inputs:
pWebUserID
pEmploymentID

outputs:
RightID - uppercase text for Right PK
RightDescription - text description of right
RightDefault - 'Allowed' or 'Denied'

RightAssigned - 0 or 1
RightAssignedLabel 'Deny' or 'Allow'


tests:
null pebUserID and null pEmploymentID
valid pWebUserID and invalid pEmplomentID
invalid pWebUserID and valid pEmploymentID
valid pWebUserID and valid pEmploymentID with a valid company check for 'Denied' (empty value in rights table)
valid pWebUserID and valid pEmploymentID with a valid company check for 'Allowed' (* in rights table)
valid pWebUserID and valid pEmploymentID with a valid company check for 'deny' (empty value in default table)
valid pWebUserID and valid pEmploymentID with a valid company check for 'allow' (* in default table)

*/
/* TEST
call NetTestSetup('');
insert into iqxnetright on existing update values ('TEST','TestRight',-999);
insert into iqxnetrightallowed on existing update values ('TEST','CLIENT','',1);
insert into iqxnetrightdefault on existing update values ('TEST','CLIENT','');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
select * from NetContactRights(null,null);
expect EOF;
select * from NetContactRights('test.client',null);
expect EOF;
select * from NetContactRights('test.x','test');
expect EOF;
select * from NetContactRights('test.client','test') where rightid='test';
expect EOF;
update iqxnetrightallowed set id='*' where iqxnetrightid='test';
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Denied,rightassigned=0,RAL=x;
update iqxnetrightdefault set id='*' where iqxnetrightid='test';
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Allowed,rightassigned=0,RAL=x;
insert into iqxnetrightassigned values ('TEST.client','TEST',-1);
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Allowed,rightassigned=-1,RAL=Denyx;
update iqxnetrightassigned set assigned=1 where iqxnetuserid='test.client';
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Allowed,rightassigned=1,RAL=Allowx;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetContactRights" IS 
{create PROCEDURE pears."NetContactRights"(in pWebUserID char(20),in pEmploymentID char(20))
result(RightID char(20),RightDescription char(100),RightDefault char(100),RightAssigned integer,RightAssignedLabel char(100))
//IQXWeb
begin
  declare cid char(20);
  declare pRequestedWebUserID char(20);
  declare pUserClassID char(20);
  set cid=(select companyid from employment where employmentid = pEmploymentID);
  if not cid = any(select companyid from employment key join iqxnetuserlink where iqxnetuserid = pWebUserID) then
    select IQXNetRightID,Description,'' as RightDefault,0 as RightAssigned,'' as RightAssignedLabel from IQXNetRight where IQXNetRightID is null
  else
    set pRequestedWebUserID=(select first IQXNetUserID from IQXNetUserLink where employmentid = pEmploymentID);
    set pUserClassID=(select first IQXNetUserClassID from IQXNetUser where IQXNetUserID = pRequestedWebUserID);
    select IQXNetRight.IQXNetRightID as RightID,
      IQXNetRight.Description as RightDescription,
      if exists(select * from IQXNetRightDefault where IQXNetRightID = IQXNetRight.IQXNetRightID and IQXNetUserClassID = pUserClassID and ID = '*') then 'Allowed'
      else 'Denied'
      endif as RightDefault,isnull(IQXNetRightAssigned.Assigned,0) as RightAssigned,
      (case RightAssigned when-1 then 'Deny' when 0 then '' when 1 then 'Allow'
      end) as RightAssignedLabel from IQXNetRightAllowed left outer join IQXNetRightAssigned on IQXNetRightAllowed.IQXNetRightID = IQXNetRightAssigned.IQXNetRightID and IQXNetRightAssigned.IQXNetUserID = pRequestedWebUserID
      ,IQXNetRightAllowed key join IQXNetRight
      where IQXNetRightAllowed.IQXNetUserClassID = pUserClassID and IQXNetRightAllowed.ID = '*' and IQXNetRightAllowed.MaintainOnWeb = 1 order by
      IQXNetRight.sortorder asc,IQXNetRight.IQXNetRightID asc
  end if
end

/* DOC
2016-08-26 PC Test and doc

inputs:
    pWebUserID
    pEmploymentID

outputs:
    RightID - uppercase text for Right PK
    RightDescription - text description of right
    RightDefault - 'Allowed' or 'Denied'

    RightAssigned - 0 or 1
    RightAssignedLabel 'Deny' or 'Allow'


tests:
    null pebUserID and null pEmploymentID
    valid pWebUserID and invalid pEmplomentID
    invalid pWebUserID and valid pEmploymentID
    valid pWebUserID and valid pEmploymentID with a valid company check for 'Denied' (empty value in rights table)
    valid pWebUserID and valid pEmploymentID with a valid company check for 'Allowed' (* in rights table)
    valid pWebUserID and valid pEmploymentID with a valid company check for 'deny' (empty value in default table)
    valid pWebUserID and valid pEmploymentID with a valid company check for 'allow' (* in default table)
    
*/

/* TEST
call NetTestSetup('');
insert into iqxnetright on existing update values ('TEST','TestRight',-999);
insert into iqxnetrightallowed on existing update values ('TEST','CLIENT','',1);
insert into iqxnetrightdefault on existing update values ('TEST','CLIENT','');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
select * from NetContactRights(null,null);
expect EOF;
select * from NetContactRights('test.client',null);
expect EOF;
select * from NetContactRights('test.x','test');
expect EOF;
select * from NetContactRights('test.client','test') where rightid='test';
expect EOF;
update iqxnetrightallowed set id='*' where iqxnetrightid='test';
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Denied,rightassigned=0,RAL=x;
update iqxnetrightdefault set id='*' where iqxnetrightid='test';
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Allowed,rightassigned=0,RAL=x;
insert into iqxnetrightassigned values ('TEST.client','TEST',-1);
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Allowed,rightassigned=-1,RAL=Denyx;
update iqxnetrightassigned set assigned=1 where iqxnetuserid='test.client';
select rightid,rightdescription,rightdefault,rightassigned,rightassignedLabel+'x' as RAL from NetContactRights('test.client','test') where rightid='test';
expect rightid=TEST,rightdescription=TestRight,rightdefault=Allowed,rightassigned=1,RAL=Allowx;
*/
}
</code>