database:procedures:pears_netclientcontactset



pears.NetClientContactSet

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE PROCEDURE "pears"."NetClientContactSet"( IN "pWebUserID" CHAR(20),IN "pemploymentid" CHAR(20),IN "pdirectphone" CHAR(250) DEFAULT NULL,IN "pmobile" CHAR(250) DEFAULT NULL,IN "pemail" CHAR(250) DEFAULT NULL,IN "pdirectfax" CHAR(250) DEFAULT NULL,IN "pposition" CHAR(250) DEFAULT NULL,IN "pdepartment" CHAR(250) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
//IQXWeb
BEGIN
  DECLARE "cid" CHAR(20);
  DECLARE "ssql" CHAR(250);
  DECLARE "scomma" CHAR(1);
  IF NOT "pemploymentid" = any(SELECT "employmentid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF("pemail" IS NOT NULL AND "pemail" = (SELECT "iqxnetuser"."emailaddress" FROM "iqxnetuser" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID")) THEN
    SET "pemail" = NULL
  END IF;
  IF("pemail" IS NOT NULL AND(SELECT "count"() FROM "iqxnetuser" WHERE "emailaddress" = "pemail") > 0) THEN
    SELECT '100:~that email address already exists. Please check it or contact your consultant'
  END IF;
  IF "pdirectphone" IS NOT NULL THEN
    CALL "IQXNetSetPhones"("pWebUserID",'CP','Direct Telephone',"pdirectphone")
  END IF;
  IF "pmobile" IS NOT NULL THEN
    CALL "IQXNetSetPhones"("pWebUserID",'CP','Mobile',"pmobile")
  END IF;
  IF "pemail" IS NOT NULL THEN
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."employmentid" = "pemploymentid" AND "iqxnetuser"."iqxnetuserid" <> 'ADMINUSER';
    CALL "IQXNetSetPhones"("pWebUserID",'CP','E-mail',"pemail")
  END IF;
  IF "pdirectfax" IS NOT NULL THEN
    CALL "IQXNetSetPhones"("pWebUserID",'CP','Direct Fax',"pdirectfax")
  END IF;
  SET "ssql" = '';
  SET "scomma" = '';
  IF "pposition" IS NOT NULL THEN
    SET "ssql" = "string"("ssql","scomma",'position=pposition');
    SET "scomma" = ','
  END IF;
  IF "pdepartment" IS NOT NULL THEN
    SET "ssql" = "string"("ssql","scomma",'department=pdepartment');
    SET "scomma" = ','
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE "string"('update employment set ',"ssql",' where employmentid in (select employmentid from iqxnetuserlink where iqxnetuserid=''',"pWebUserID",''')')
  END IF;
  SELECT '0:~Success'
END /* TEST
call NetTestSetup('');
insert into employment (employmentid,companyid,personid,position) on existing update defaults off values ('TEST2','TEST','TEST','TestPosition2');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST2' where IQXNetUserLinkID='test.client';
select * from NetClientContactSet('test.client9','TEST2','0999 999 999',null,null,null,null,null,null,null,null,null,null,null);
expect 99:*;
select * from NetClientContactSet('test.client','TEST2','0999 999 999',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,'0999 999 998',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,'[email protected]',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected];
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,'0999 999 997',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected],df=0999!999!997;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,'TestPosition',null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected],df=0999!999!997,Position=TestPosition;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,'TEST',null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected],df=0999!999!997,Position=TestPosition,Department=TEST;
expect EOF;
 
select * from NetClientContactSet('test.client','TEST2','0999 999 996',null,null,null,null,null,'0999 999 999',null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,'0999 999 995',null,null,null,null,null,'0999 999 998',null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,'[email protected]',null,null,null,null,null,'[email protected]',null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected];
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,'0999 999 994',null,null,null,null,null,'0999 999 997',null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected],df=0999!999!994;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,'TestPosition2',null,null,null,null,null,'TestPosition',null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected],df=0999!999!994,Position=TestPosition2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,'TEST2',null,null,null,null,null,'TEST');
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected],df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
 
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,'0999 999 999',null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect mob=0999!999!995,[email protected],df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,'0999 999 998',null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect [email protected],df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,'[email protected]',null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,null,'0999 999 997',null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,null,null,'TestPosition',null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,null,null,null,'TEST');
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect;
expect EOF;
delete from employment where employmentid='TEST2';
*/
/* DOC
2016-08-23 PC add doc and test
2018-01-30 PC check that email does not already exist and avoid updating iqxnetuserlink for records linked to adminuser
2018-08-22 PC improve error handling
2020-07-09 GJ/ET/JHG IW2-182 Changed to make it simpler and modified to work with web v2
 
tests:
invalid pWebUserID
valid pWebUserID with no employment
pdirectphone is not null and pOldDirectPhone is null
pMobile is not null and pOldMobile is null
pEmail is not null and pOldEmail is null
pDirectfax is not null and pOldDirectFax is null
pPosition is not null and pOldPosition is null
pDepartment is not null and pOldDepartment is null
pdirectphone is different and pOldDirectPhone is not null
pMobile is different and pOldMobile is not null
pEmail is different and pOldEmail is not null
pDirectfax is different and pOldDirectFax is not null
pPosition is different and pOldPosition is not null
pDepartment is different and pOldDepartment is not null
pdirectphone is null and pOldDirectPhone is not null
pMobile is null and pOldMobile is not null
pEmail is null and pOldEmail is not null
pDirectfax is null and pOldDirectFax is not null
pPosition is null and pOldPosition is not null
pDepartment is null and pOldDepartment is not null
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientContactSet" IS 
{CREATE PROCEDURE pears."NetClientContactSet"(IN pWebUserID CHAR(20),IN pemploymentid CHAR(20),IN pdirectphone CHAR(250) DEFAULT NULL,IN pmobile CHAR(250) DEFAULT NULL,IN pemail CHAR(250) DEFAULT NULL,IN pdirectfax CHAR(250) DEFAULT NULL,IN pposition CHAR(250) DEFAULT NULL,IN pdepartment CHAR(250) DEFAULT NULL)
RESULT(pResult CHAR(250))
//IQXWeb
BEGIN
  DECLARE cid CHAR(20);
  DECLARE ssql CHAR(250);
  DECLARE scomma CHAR(1);
  IF NOT pemploymentid = any(SELECT employmentid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid) THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF(pemail IS NOT NULL AND pemail = (SELECT iqxnetuser.emailaddress FROM iqxnetuser WHERE iqxnetuser.iqxnetuserid = pWebUserID)) THEN
    SET pemail = NULL;
  END IF;
  IF (pemail IS NOT NULL AND (SELECT COUNT(*) FROM iqxnetuser WHERE emailaddress=pemail) > 0) THEN
    SELECT '100:~that email address already exists. Please check it or contact your consultant';
  END IF;
  IF pdirectphone IS NOT NULL THEN
    CALL IQXNetSetPhones(pWebUserID,'CP','Direct Telephone',pdirectphone)
  END IF;
  IF pmobile IS NOT NULL THEN
    CALL IQXNetSetPhones(pWebUserID,'CP','Mobile',pmobile)
  END IF;
 
  IF pemail IS NOT NULL THEN
    UPDATE iqxnetuserlink KEY JOIN iqxnetuser SET iqxnetuser.emailaddress = pemail WHERE iqxnetuserlink.employmentid = pemploymentid AND iqxnetuser.iqxnetuserid !='ADMINUSER';
    CALL IQXNetSetPhones(pWebUserID,'CP','E-mail',pemail)
  END IF;
 
  IF pdirectfax IS NOT NULL THEN
    CALL IQXNetSetPhones(pWebUserID,'CP','Direct Fax',pdirectfax)
  END IF;
  SET ssql='';
  SET scomma='';
  IF pposition IS NOT NULL THEN
    SET ssql=string(ssql,scomma,'position=pposition');
    SET scomma=','
  END IF;
  IF pdepartment IS NOT NULL THEN
    SET ssql=string(ssql,scomma,'department=pdepartment');
    SET scomma=','
  END IF;
  IF ssql <> '' THEN
    EXECUTE IMMEDIATE string('update employment set ',ssql,' where employmentid in (select employmentid from iqxnetuserlink where iqxnetuserid=''',pWebUserID,''')')
  END IF;
  SELECT '0:~Success'
END
 
/* TEST
call NetTestSetup('');
insert into employment (employmentid,companyid,personid,position) on existing update defaults off values ('TEST2','TEST','TEST','TestPosition2');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST2' where IQXNetUserLinkID='test.client';
select * from NetClientContactSet('test.client9','TEST2','0999 999 999',null,null,null,null,null,null,null,null,null,null,null);
expect 99:*;
select * from NetClientContactSet('test.client','TEST2','0999 999 999',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,'0999 999 998',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,'[email protected]',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected];
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,'0999 999 997',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,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected],df=0999!999!997;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,'TestPosition',null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected],df=0999!999!997,Position=TestPosition;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,'TEST',null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!999,mob=0999!999!998,[email protected],df=0999!999!997,Position=TestPosition,Department=TEST;
expect EOF;
 
select * from NetClientContactSet('test.client','TEST2','0999 999 996',null,null,null,null,null,'0999 999 999',null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,'0999 999 995',null,null,null,null,null,'0999 999 998',null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,'[email protected]',null,null,null,null,null,'[email protected]',null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected];
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,'0999 999 994',null,null,null,null,null,'0999 999 997',null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected],df=0999!999!994;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,'TestPosition2',null,null,null,null,null,'TestPosition',null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected],df=0999!999!994,Position=TestPosition2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,'TEST2',null,null,null,null,null,'TEST');
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect DP=0999!999!996,mob=0999!999!995,[email protected],df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
 
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,'0999 999 999',null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect mob=0999!999!995,[email protected],df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,'0999 999 998',null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect [email protected],df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,'[email protected]',null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect df=0999!999!994,Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,null,'0999 999 997',null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect Position=TestPosition2,Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,null,null,'TestPosition',null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect Department=TEST2;
expect EOF;
select * from NetClientContactSet('test.client','TEST2',null,null,null,null,null,null,null,null,null,null,null,'TEST');
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df,position,department from NetClientContact('test.client');
expect;
expect EOF;
delete from employment where employmentid='TEST2';
*/
 
/* DOC
2016-08-23 PC add doc and test
2018-01-30 PC check that email does not already exist and avoid updating iqxnetuserlink for records linked to adminuser
2018-08-22 PC improve error handling
2020-07-09 GJ/ET/JHG IW2-182 Changed to make it simpler and modified to work with web v2
 
tests:
    invalid pWebUserID
    valid pWebUserID with no employment
    pdirectphone is not null and pOldDirectPhone is null
    pMobile is not null and pOldMobile is null
    pEmail is not null and pOldEmail is null
    pDirectfax is not null and pOldDirectFax is null
    pPosition is not null and pOldPosition is null
    pDepartment is not null and pOldDepartment is null
    pdirectphone is different and pOldDirectPhone is not null
    pMobile is different and pOldMobile is not null
    pEmail is different and pOldEmail is not null
    pDirectfax is different and pOldDirectFax is not null
    pPosition is different and pOldPosition is not null
    pDepartment is different and pOldDepartment is not null
    pdirectphone is null and pOldDirectPhone is not null
    pMobile is null and pOldMobile is not null
    pEmail is null and pOldEmail is not null
    pDirectfax is null and pOldDirectFax is not null
    pPosition is null and pOldPosition is not null
    pDepartment is null and pOldDepartment is not null
 
*/
}
  • database/procedures/pears_netclientcontactset.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1