pears.NetHasPermission

CREATE FUNCTION "pears"."NetHasPermission"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pCompanyID" CHAR(20) ) 
RETURNS INTEGER
// IQXWeb
BEGIN
  DECLARE "rv" INTEGER;
  DECLARE "userclass" CHAR(20);
  DECLARE "divid" CHAR(20);
  SET "rv" = 0;
  SELECT "iqxnetuserclassid" INTO "userclass" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid";
  IF "userclass" = 'CANDIDATE' THEN
    SET "rv" = (SELECT FIRST 1 FROM "iqxnetuserlink" WHERE "personid" = "ppersonid" AND "iqxnetuserid" = "pwebuserid")
  ELSE IF "userclass" = 'CLIENT' THEN
      SET "rv" = (SELECT FIRST 1 FROM "employment" AS "e" KEY JOIN "iqxnetuserlink" AS "x" WHERE "e"."companyid" = "pcompanyid" AND "x"."iqxnetuserid" = "pwebuserid")
    ELSE IF "userclass" = 'AGENCY' THEN
        SET "rv" = (SELECT FIRST 1 FROM "pay_employee" AS "pe" KEY JOIN "company" KEY JOIN "employment" KEY JOIN "iqxnetuserlink" AS "x" WHERE "pe"."personid" = "ppersonid" AND "x"."iqxnetuserid" = "pwebuserid")
      ELSE IF "userclass" = 'OWNER' THEN
          SET "divid" = "isnull"((SELECT FIRST "divisionid" FROM "person" WHERE "personid" = "pPersonID" ORDER BY 1 ASC),(SELECT FIRST "divisionid" FROM "company" WHERE "companyid" = "pCompanyID" ORDER BY 1 ASC),'');
          IF "divid" = any(SELECT "DivisionID" FROM "DashboardStaffDivisions"()) THEN
            SET "rv" = 1
          END IF
        END IF END IF END IF END IF;
  RETURN "isnull"("rv",0)
END /* DOC
2016-08-30 PC doc and test
2018-10-22 MHS improved OWNER logic to properly respect staff division rights
 
inputs:
pWebUserID
pPersonID
pCompanyID
 
outputs:
(integer) - 0 or 1
 
tests:
test all null
test invalid pWebUserID
test valid pWebUserID as person and invalid pPersonID
test valid pWebUserID as person and valid pPersonID
test valid pWebUserID as client and pPersonID and valid pCompanyID
test valid pWebUserID as agency and pPersonID and valid pCompanyID
test valid pWebUserID as owner and pPersonID and valid pCompanyID
 
*/
/* TEST
call NetTestSetup('','NetHasPermission');
update company set divisionid='test' where companyid='test';
update person set divisionid='TEST' where personid='test';
update staff set divisionid='TEST' where staffid='TEST';
insert into pay_employee (personid, secondaryagencyid) on existing update values ('test','test');
select NetHasPermission(null,null,null);
expect 0;
select NetHasPermission('test.candidate','T9',null);
expect 0;
select NetHasPermission('test.candidate','Test',null);
expect 1;
select NetHasPermission('TEST.client','test','test');
expect 1;
select NetHasPermission('TEST.agency','test','test');
expect 1;
select NetHasPermission('TEST.owner','test','test');
expect 1;
 
*/