====== pears.NetContactRightsSet ======
<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"."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';
*/
}
</code>