pears.NetContactRightsSet

CREATE PROCEDURE "pears"."NetContactRightsSet"( IN "pWebUserID" CHAR(20),IN "pEmploymentID" CHAR(20),IN "pRightsList" long VARCHAR ) 
RESULT( "pResult" CHAR(250) ) 
//IQXWeb
BEGIN
  DECLARE "cid" CHAR(20);
  DECLARE "pRequestedWebUserID" CHAR(20);
  DECLARE "i" SMALLINT;
  DECLARE "j" SMALLINT;
  DECLARE "q" SMALLINT;
  DECLARE "dummy" SMALLINT;
  DECLARE "s" CHAR(30);
  DECLARE "t" CHAR(30);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID" ) 
    SELECT DISTINCT "e"."companyid" FROM "employment" AS "e" KEY JOIN "iqxnetuserlink" AS "i" WHERE "i"."iqxnetuserid" = "pWebUserID";
  IF NOT "pEmploymentID" = any(SELECT "e"."employmentid" FROM "employment" AS "e" JOIN "IDs" ON "e"."companyid" = "IDs"."ID") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "IQXNetHasPermission"("pWebUserID",'MAINTAINCONTACTS') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "pRequestedWebUserID" = (SELECT FIRST "IQXNetUserID" FROM "IQXNetUserLink" WHERE "employmentid" = "pEmploymentID");
  DELETE FROM "iqxnetrightassigned" WHERE "iqxnetuserid" = "pRequestedWebUserID" AND "iqxnetrightid"
     = any(SELECT "a"."iqxnetrightid" FROM "iqxnetrightallowed" AS "a" KEY JOIN "iqxnetuserclass" KEY JOIN "iqxnetuser" AS "u" WHERE "a"."maintainonweb" = 1 AND "u"."iqxnetuserid" = "prequestedwebuserid");
  SET "pRightsList" = "replace"("pRightsList",'Right_','');
  "looplabel": loop
    SET "i" = "charindex"(',',"pRightsList");
    IF "i" = 0 THEN
      SET "s" = "prightslist"
    ELSE
      SET "s" = "left"("prightslist","i"-1);
      SET "prightslist" = "right"("prightslist","length"("prightslist")-"i")
    END IF;
    SET "s" = "trim"("s");
    SET "q" = 0;
    SET "j" = "charindex"('=',"s");
    IF "j" > 0 THEN
      SET "t" = "trim"("right"("s","length"("s")-"j"));
      SET "s" = "trim"("left"("s","j"-1));
      CASE "t"
      WHEN 'Allow' THEN SET "q" = 1
      WHEN 'Deny' THEN SET "q" = -1
      WHEN '1' THEN SET "q" = 1
      WHEN '-1' THEN SET "q" = -1
      END CASE
    END IF;
    IF "s" <> '' AND "q" <> 0 THEN
      BEGIN
        INSERT INTO "iqxnetrightassigned"( "iqxnetuserid","iqxnetrightid","assigned" ) VALUES( "pRequestedWebUserID","s","q" ) 
      exception
        WHEN others THEN
          SET "dummy" = 0
      END END IF;
    IF "i" = 0 THEN
      leave "looplabel"
    END IF
  END loop "looplabel";
  SELECT '0:~Success'
END /* DOC
2016-08-26 PC test and doc
2018-08-27 PC improve error handler
 
test:
null pWebUserID, null pEmploymentId, null pRightslist
correct pWebUserID, correct pEmploymentID, correct pRightsList but no Maintaincontacts right
set an incorrect, allowed right
deny a correct, allowed right
set a correct, denied right
deny a correct, allowed right and allow a correct, denied right
 
*/
/* TEST
 
call NetTestSetup('');
delete from iqxnetrightallowed where iqxnetrightid='maintaincontacts' and iqxnetuserclassid='client';
delete from iqxnetrightallowed where iqxnetrightid='managementinfo' and iqxnetuserclassid='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 NetContactRightsSet(null,null,null);
expect 99*;
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=0');
expect 99*;
select * from NetContactRightsSet('test.client','test','right_zz=1');
expect 99*;
insert into iqxnetrightallowed values ('maintaincontacts','CLIENT','*',null);
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=0');
expect 0*;
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect EOF;
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=1');
expect 0*;
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect EOF;
update iqxnetrightallowed set maintainonweb=1 where iqxnetrightid='maintaincontacts' and iqxnetuserclassid='CLIENT';
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect rightassigned=1;
insert into iqxnetrightallowed values ('managementinfo','CLIENT','*',1);
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=0,right_managementinfo=1');
expect 0*;
select * from NetContactRights('test.client','test') where rightid='managementinfo';
expect rightassigned=1;
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect rightassigned=0;
update iqxnetrightallowed set maintainonweb=1 where iqxnetrightid='managementinfo' and iqxnetuserclassid='client';
delete from iqxnetrightassigned where iqxnetuserid='test.client';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetContactRightsSet" IS 
{CREATE PROCEDURE pears."NetContactRightsSet"(IN pWebUserID CHAR(20),IN pEmploymentID CHAR(20),IN pRightsList long VARCHAR)
RESULT(pResult CHAR(250))
//IQXWeb
BEGIN
  DECLARE cid CHAR(20);
  DECLARE pRequestedWebUserID CHAR(20);
  DECLARE i SMALLINT;
  DECLARE j SMALLINT;
  DECLARE q SMALLINT;
  DECLARE dummy SMALLINT;
  DECLARE s CHAR(30);
  DECLARE t CHAR(30);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO IDs( ID) 
    SELECT DISTINCT e.companyid FROM employment AS e KEY JOIN iqxnetuserlink AS i WHERE i.iqxnetuserid = pWebUserID;
  IF NOT pEmploymentID = any(SELECT e.employmentid FROM employment AS e JOIN IDs ON e.companyid = IDs.ID) THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF IQXNetHasPermission(pWebUserID,'MAINTAINCONTACTS') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET pRequestedWebUserID=(SELECT FIRST IQXNetUserID FROM IQXNetUserLink WHERE employmentid = pEmploymentID);
  DELETE FROM iqxnetrightassigned WHERE iqxnetuserid = pRequestedWebUserID AND iqxnetrightid
     = any(SELECT a.iqxnetrightid FROM iqxnetrightallowed AS a KEY JOIN iqxnetuserclass KEY JOIN iqxnetuser AS u WHERE a.maintainonweb = 1 AND u.iqxnetuserid = prequestedwebuserid);
  SET pRightsList=REPLACE(pRightsList,'Right_','');
  looplabel: loop
    SET i=charindex(',',pRightsList);
    IF i = 0 THEN
      SET s=prightslist
    ELSE
      SET s="left"(prightslist,i-1);
      SET prightslist="right"(prightslist,LENGTH(prightslist)-i)
    END IF;
    SET s=TRIM(s);
    SET q=0;
    SET j=charindex('=',s);
    IF j > 0 THEN
      SET t=TRIM("right"(s,LENGTH(s)-j));
      SET s=TRIM("left"(s,j-1));
      CASE t
      WHEN 'Allow' THEN SET q=1
      WHEN 'Deny' THEN SET q=-1
      WHEN '1' THEN SET q=1
      WHEN '-1' THEN SET q=-1
      END CASE
    END IF;
    IF s <> '' AND q <> 0 THEN
      BEGIN
        INSERT INTO iqxnetrightassigned( iqxnetuserid,iqxnetrightid,assigned) VALUES( pRequestedWebUserID,s,q) 
      exception
        WHEN others THEN
          SET dummy=0
      END
    END IF;
    IF i = 0 THEN
      leave looplabel
    END IF
  END loop looplabel;
  SELECT '0:~Success'
END
 
/* DOC
2016-08-26 PC test and doc
2018-08-27 PC improve error handler
 
test:
    null pWebUserID, null pEmploymentId, null pRightslist
    correct pWebUserID, correct pEmploymentID, correct pRightsList but no Maintaincontacts right
    set an incorrect, allowed right
    deny a correct, allowed right
    set a correct, denied right
    deny a correct, allowed right and allow a correct, denied right
 
*/
 
/* TEST
 
call NetTestSetup('');
delete from iqxnetrightallowed where iqxnetrightid='maintaincontacts' and iqxnetuserclassid='client';
delete from iqxnetrightallowed where iqxnetrightid='managementinfo' and iqxnetuserclassid='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 NetContactRightsSet(null,null,null);
expect 99*;
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=0');
expect 99*;
select * from NetContactRightsSet('test.client','test','right_zz=1');
expect 99*;
insert into iqxnetrightallowed values ('maintaincontacts','CLIENT','*',null);
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=0');
expect 0*;
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect EOF;
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=1');
expect 0*;
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect EOF;
update iqxnetrightallowed set maintainonweb=1 where iqxnetrightid='maintaincontacts' and iqxnetuserclassid='CLIENT';
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect rightassigned=1;
insert into iqxnetrightallowed values ('managementinfo','CLIENT','*',1);
select * from NetContactRightsSet('test.client','test','right_maintaincontacts=0,right_managementinfo=1');
expect 0*;
select * from NetContactRights('test.client','test') where rightid='managementinfo';
expect rightassigned=1;
select * from NetContactRights('test.client','test') where rightid='maintaincontacts';
expect rightassigned=0;
update iqxnetrightallowed set maintainonweb=1 where iqxnetrightid='managementinfo' and iqxnetuserclassid='client';
delete from iqxnetrightassigned where iqxnetuserid='test.client';
*/
}