pears.NetAgencyContactSet [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetAgencyContactSet ======
<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"."NetAgencyContactSet"( in "pWebUserID" char(20),in "pemploymentid" char(20),in "pdirectphone" char(250) default 'NOT_SET',in "pmobile" char(250) default 'NOT_SET',in "pemail" char(250) default 'NOT_SET',in "pdirectfax" char(250) default 'NOT_SET',in "pcascademethod" char(1) default '$',in "pcascadeaddress" char(250) default 'NOT_SET',in "pOLDdirectphone" char(250) default 'NOT_SET',in "pOLDmobile" char(250) default 'NOT_SET',in "pOLDemail" char(250) default 'NOT_SET',in "pOLDdirectfax" char(250) default 'NOT_SET',in "pOLDcascademethod" char(1) default '$',in "pOLDcascadeaddress" char(250) default 'NOT_SET' ) 
result( "pResult" char(250) ) 
//IQXWeb
begin
  if not "pemploymentid" = any(select "employmentid" from "iqxnetuserlink" where "iqxnetuserid" = "pwebuserid") then
    select '99:~Permission denied';
    return
  end if;
  if "isnull"("pcascademethod",'') <> "isnull"("pOLDcascademethod",'') or "isnull"("pcascadeaddress",'') <> "isnull"("pOLDcascadeaddress",'') then
    set "pcascademethod" = "nullif"("pcascademethod",'$');
    set "pcascadeaddress" = "nullif"("pcascadeaddress",'NOT_SET');
    insert into "cascadedeliveryaddress"( "companyid","deliverymethod" ) select distinct "employment"."companyid",'E' from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID"
        and not "employment"."companyid" = any(select "companyid" from "cascadedeliveryaddress");
    update "cascadedeliveryaddress" set "deliverymethod" = "isnull"("ucase"("pcascademethod"),"deliverymethod"),"address" = "isnull"("pcascadeaddress","address") where "companyid"
       = any(select "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
  end if;
  if "isnull"("pdirectphone",'') <> "isnull"("pOLDdirectphone",'') then
    call "IQXNetSetPhones"("pWebUserID",'CP','Direct Telephone',"pdirectphone")
  end if;
  if "isnull"("pmobile",'') <> "isnull"("pOLDmobile",'') then
    call "IQXNetSetPhones"("pWebUserID",'CP','Mobile',"pmobile")
  end if;
  if "isnull"("pemail",'') <> "isnull"("pOLDemail",'') then
    update "iqxnetuserlink" key join "iqxnetuser" set "iqxnetuser"."emailaddress" = "pemail" where "iqxnetuserlink"."employmentid" = "pemploymentid";
    call "IQXNetSetPhones"("pWebUserID",'CP','E-mail',"pemail")
  end if;
  if "isnull"("pdirectfax",'') <> "isnull"("pOLDdirectfax",'') then
    call "IQXNetSetPhones"("pWebUserID",'CP','Direct Fax',"pdirectfax")
  end if;
  select '0:~Success'
end /* DOC
2016-10-10 PC doc and test
2018-04-23 PC revise tests

Tests:
Direct Phone
Mobile
Email
Fax
CascadeMethod,CascadeAddress

*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from phone where phoneid like 'test%';
update iqxnetuser set emailaddress=null where iqxnetuserid='TEST.AGENCY';
delete from CascadeDeliveryAddress where companyid='TEST';
select * from NetAgencyContactSet('test.agency','TEST','0999 999 990',null,null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,'0999 999 991',null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,'test99@test.com',null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,email=test99@test.com;
expect EOF;
select emailaddress from iqxnetuser where iqxnetuserid='TEST.AGENCY';
expect emailaddress=test99@test.com;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,'0999 999 992',null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,email=test99@test.com,df=0999!999!992;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,null,'E','test@test.com',null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,email=test99@test.com,df=0999!999!992;
expect EOF;
select DeliveryMethod,Address from cascadedeliveryaddress where CompanyID='TEST';
expect DeliveryMethod=E,Address=test@test.com;
expect EOF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyContactSet" IS 
{create PROCEDURE pears."NetAgencyContactSet"(in pWebUserID char(20),in pemploymentid char(20),in pdirectphone char(250) default 'NOT_SET',in pmobile char(250) default 'NOT_SET',in pemail char(250) default 'NOT_SET',in pdirectfax char(250) default 'NOT_SET',in pcascademethod char(1) default '$',in pcascadeaddress char(250) default 'NOT_SET',in pOLDdirectphone char(250) default 'NOT_SET',in pOLDmobile char(250) default 'NOT_SET',in pOLDemail char(250) default 'NOT_SET',in pOLDdirectfax char(250) default 'NOT_SET',in pOLDcascademethod char(1) default '$',in pOLDcascadeaddress char(250) default 'NOT_SET')
result(pResult char(250))
//IQXWeb
begin
  if not pemploymentid = any(select employmentid from iqxnetuserlink where iqxnetuserid = pwebuserid) then
    select '99:~Permission denied';
    return
  end if;
  if isnull(pcascademethod,'') <> isnull(pOLDcascademethod,'') or isnull(pcascadeaddress,'') <> isnull(pOLDcascadeaddress,'') then
    set pcascademethod=nullif(pcascademethod,'$');
    set pcascadeaddress=nullif(pcascadeaddress,'NOT_SET');
    insert into cascadedeliveryaddress( companyid,deliverymethod) select distinct employment.companyid,'E' from employment key join iqxnetuserlink where iqxnetuserid = pWebUserID
        and not employment.companyid = any(select companyid from cascadedeliveryaddress);
    update cascadedeliveryaddress set deliverymethod = isnull(ucase(pcascademethod),deliverymethod),address = isnull(pcascadeaddress,address) where companyid
       = any(select employment.companyid from employment key join iqxnetuserlink where iqxnetuserid = pWebUserID)
  end if;
  if isnull(pdirectphone,'') <> isnull(pOLDdirectphone,'') then
    call IQXNetSetPhones(pWebUserID,'CP','Direct Telephone',pdirectphone)
  end if;
  if isnull(pmobile,'') <> isnull(pOLDmobile,'') then
    call IQXNetSetPhones(pWebUserID,'CP','Mobile',pmobile)
  end if;
  if isnull(pemail,'') <> isnull(pOLDemail,'') then
    update iqxnetuserlink key join iqxnetuser set iqxnetuser.emailaddress = pemail where iqxnetuserlink.employmentid = pemploymentid;
    call IQXNetSetPhones(pWebUserID,'CP','E-mail',pemail)
    
  end if;
  if isnull(pdirectfax,'') <> isnull(pOLDdirectfax,'') then
    call IQXNetSetPhones(pWebUserID,'CP','Direct Fax',pdirectfax)
  end if;
  select '0:~Success'
end

/* DOC
2016-10-10 PC doc and test
2018-04-23 PC revise tests

Tests:
    Direct Phone
    Mobile
    Email
    Fax
    CascadeMethod,CascadeAddress

*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from phone where phoneid like 'test%';
update iqxnetuser set emailaddress=null where iqxnetuserid='TEST.AGENCY';
delete from CascadeDeliveryAddress where companyid='TEST';
select * from NetAgencyContactSet('test.agency','TEST','0999 999 990',null,null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,'0999 999 991',null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,'test99@test.com',null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,email=test99@test.com;
expect EOF;
select emailaddress from iqxnetuser where iqxnetuserid='TEST.AGENCY';
expect emailaddress=test99@test.com;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,'0999 999 992',null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,email=test99@test.com,df=0999!999!992;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,null,'E','test@test.com',null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,email=test99@test.com,df=0999!999!992;
expect EOF;
select DeliveryMethod,Address from cascadedeliveryaddress where CompanyID='TEST';
expect DeliveryMethod=E,Address=test@test.com;
expect EOF;
*/
}
</code>