pears.NetContactRights

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;
*/
}