database:procedures:pears_netcontactset



pears.NetContactSet

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

Original SQL

CREATE PROCEDURE "pears"."NetContactSet"( 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 "pposition" CHAR(250) DEFAULT 'NOT_SET',IN "pdepartment" 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 "pOLDposition" CHAR(250) DEFAULT 'NOT_SET',IN "pOLDdepartment" CHAR(250) DEFAULT 'NOT_SET',IN "pForenames" CHAR(50) DEFAULT NULL,IN "pSurname" CHAR(50) DEFAULT NULL,IN "pBaseEmploymentID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
//IQXWeb
BEGIN
  -- This proc is for maintenance updates of any company contact by master contacts with suitable rights
  DECLARE "scoid" CHAR(20);
  DECLARE "sdivid" CHAR(20);
  DECLARE "ssql" CHAR(250);
  DECLARE "scomma" CHAR(1);
  DECLARE "userClass" CHAR(20);
  DECLARE "baseUserID" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  IF "pemploymentid" = 'NEW' THEN
    IF "pemail" = 'NOT_SET' OR "pForenames" IS NULL OR "pSurname" IS NULL OR "pBaseEmploymentID" IS NULL THEN
      SELECT '91:~Missing surname, forenames, email address or template contact ID';
      RETURN
    END IF;
    IF EXISTS(SELECT * FROM "IQXNetUser" WHERE("EmailAddress" = "pemail" OR "LoginID" = "pemail")) THEN
      SELECT '90:~Email address already in use';
      RETURN
    END IF;
    SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
    SET "baseUserID" = (SELECT FIRST "iqxnetuserid" FROM "iqxnetuserlink" WHERE "employmentid" = "pBaseEmploymentID" AND "iqxnetuserid" <> 'ADMINUSER');
    SELECT "c"."companyid","c"."divisionid" INTO "scoid","sdivid" FROM "employment" AS "e" KEY JOIN "company" AS "c" WHERE "e"."employmentid" = "pBaseEmploymentID";
    SET "pEmploymentID" = "uniquekey"('');
    INSERT INTO "person"( "personid","staffid","name","keyname","status","forenames","surname","salutation","divisionid" ) VALUES
      ( "pEmploymentID","userstaffid","string"("getword"("pForenames",1),' ',"pSurname"),"makekeyname"("string"("pSurname",' ',"pForenames")),'L',"pForenames","pSurname","getword"("pforenames",1),"sdivid" ) ;
    INSERT INTO "employment"( "employmentid","companyid","personid" ) VALUES( "pEmploymentID","scoid","pEmploymentID" ) ;
    INSERT INTO "iqxnetuser"( "IQXNetUserID","IQXNetUserClassID","Name","LoginID","EmailAddress" ) VALUES( "pEmploymentID","userClass","string"("getword"("pForenames",1),' ',"pSurname"),"pemail","pemail" ) ;
    INSERT INTO "iqxnetuserlink"( "IQXNetUserLinkID","IQXNetUserID","PersonID","EmploymentID" ) VALUES( "pEmploymentID","pEmploymentID","pEmploymentID","pEmploymentID" ) ;
    INSERT INTO "iqxnetrightassigned"( "IQXNetUserID","IQXNetRightID","Assigned" ) SELECT "pEmploymentID","IQXNetRightID","Assigned" FROM "iqxnetrightassigned" WHERE "IQXNetUserID" = "baseUserID";
    INSERT INTO "VacancyRoleAllocation"( "VacancyID","EmploymentID","VacancyRoleID" ) SELECT "VacancyID","pEmploymentID","VacancyRoleID" FROM "VacancyRoleAllocation" WHERE "EmploymentID" = "pBaseEmploymentID"
  ELSE
    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 END IF;
  IF "isnull"("pdirectphone",'') <> "isnull"("pOLDdirectphone",'') THEN
    CALL "setphone"('CP','Direct Telephone',"pemploymentid","pdirectphone")
  END IF;
  IF "isnull"("pmobile",'') <> "isnull"("pOLDmobile",'') THEN
    CALL "setphone"('CP','Mobile',"pemploymentid","pmobile")
  END IF;
  IF "isnull"("pemail",'') <> "isnull"("pOLDemail",'') THEN
    CALL "setphone"('CP','E-mail',"pemploymentid","pemail");
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."employmentid" = "pemploymentid"
  END IF;
  IF "isnull"("pdirectfax",'') <> "isnull"("pOLDdirectfax",'') THEN
    CALL "setphone"('CP','Direct Fax',"pemploymentid","pdirectfax")
  END IF;
  SET "ssql" = '';
  SET "scomma" = '';
  IF "isnull"("pposition",'') <> "isnull"("pOLDposition",'') THEN
    SET "ssql" = "string"("ssql","scomma",'position=pposition');
    SET "scomma" = ','
  END IF;
  IF "isnull"("pdepartment",'') <> "isnull"("pOLDdepartment",'') THEN
    SET "ssql" = "string"("ssql","scomma",'department=pdepartment');
    SET "scomma" = ','
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE "string"('update employment set ',"ssql",' where employmentid=''',"pemploymentid",'''')
  END IF;
  SELECT '0:~Success'
END /* DOC
2016-08-29 PC Test and doc
2018-08-28 PC improved error handler
 
test:
create new with missing surname
create new
create new with existing email
create new with incorrect employmentid
update one field
update remaining fields at same time
*/
/* TEST
call NetTestSetup('');
delete from iqxnetuser where loginid='[email protected]'
 
 
delete from iqxnetrightassigned where iqxnetuserid='TEST.client' and iqxnetrightid='test';
delete from vacancyroleallocation where vacancyid='TEST';
delete from vacancyrole where vacancyroleid='TEST';
delete from iqxnetright where iqxnetrightid='TEST';
delete from employment where employmentid='TEST2';
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.client','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST2' where IQXNetUserLinkID='test.client';
insert into IQXNetRight values ('TEST','test',999);
insert into vacancyrole values ('TEST','TestRole','X',99,null);
insert into vacancyroleallocation values ('TEST','TEST','TEST');
 
select * from NetContactSet('test.client','NEW','0999 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename',null,'test');
expect pResult=91*;
select * from NetContactSet('test.client','NEW','0899 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename','TestSurname','test');
expect presult=0*;
select * from NetContactSet('test.client','NEW','0799 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename','TestSurname','test');
expect presult=90*;
select * from NetContactSet('test.client','T99','0699 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename','TestSurname','test');
expect presult=99*;
select * from NetContactSet('test.client','TEST','0599 999 996','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept','0999 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept','TestForename','TestSurname','test');
expect presult=0*;
select replace(number,' ','!') as A from phone where whoid='TEST' order by phonetypeid;
expect [email protected];
expect A=0999!999!992;
expect A=0999!999!991;
expect A=0599!999!996;
select * from NetContactSet('test.client','TEST','0499 999 995','0999 999 994','[email protected]','0999 999 993','TestPos2','TestDept2','0999 999 996','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept','TestForename2','TestSurname2','test');
expect presult=0*;
select numberdigits from phone where whoid='TEST';
expect numberdigits=0499999995;
expect numberdigits=0999999994;
expect numberdigits=70;
expect numberdigits=0999999993;
expect EOF;
select loginid from iqxnetuser where emailaddress='[email protected]';
expect [email protected];
select surname from person where forenames='testforename';
expect surname=Testsurname;
 
delete from iqxnetrightassigned where iqxnetuserid='TEST.client' and iqxnetrightid='test';
delete from vacancyroleallocation where vacancyid='TEST';
delete from vacancyrole where vacancyroleid='TEST';
delete from iqxnetright where iqxnetrightid='TEST';
delete from iqxnetrightassigned where iqxnetuserid='TEST.client' and iqxnetrightid='test';
delete from employment where employmentid='TEST2';
delete from iqxnetuser where loginid='[email protected]'
 
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetContactSet" IS 
{CREATE PROCEDURE pears."NetContactSet"(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 pposition CHAR(250) DEFAULT 'NOT_SET',IN pdepartment 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 pOLDposition CHAR(250) DEFAULT 'NOT_SET',IN pOLDdepartment CHAR(250) DEFAULT 'NOT_SET', IN pForenames CHAR(50) DEFAULT NULL, IN pSurname CHAR(50) DEFAULT NULL, IN pBaseEmploymentID CHAR(20) DEFAULT NULL)
RESULT(pResult CHAR(250))
//IQXWeb
BEGIN
  -- This proc is for maintenance updates of any company contact by master contacts with suitable rights
  DECLARE scoid CHAR(20);
  DECLARE sdivid CHAR(20);
  DECLARE ssql CHAR(250);
  DECLARE scomma CHAR(1);
  DECLARE userClass CHAR(20);
  DECLARE baseUserID CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  IF pemploymentid='NEW' THEN
    IF pemail='NOT_SET' OR pForenames IS NULL OR pSurname IS NULL OR pBaseEmploymentID IS NULL THEN
      SELECT '91:~Missing surname, forenames, email address or template contact ID';
      RETURN
    END IF;
    IF EXISTS(SELECT * FROM IQXNetUser WHERE EmailAddress=pemail OR LoginID=pemail) THEN 
      SELECT '90:~Email address already in use';
      RETURN
    END IF;
    SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
    SET baseUserID=(SELECT FIRST iqxnetuserid FROM iqxnetuserlink WHERE employmentid=pBaseEmploymentID AND iqxnetuserid<>'ADMINUSER');
    SELECT c.companyid, c.divisionid INTO scoid, sdivid FROM employment e KEY JOIN company c WHERE e.employmentid=pBaseEmploymentID;
    SET pEmploymentID=uniquekey('');
    INSERT INTO person (personid,staffid,name,keyname,STATUS,forenames,surname,salutation,divisionid) VALUES
      (pEmploymentID,userstaffid,string(getword(pForenames,1),' ',pSurname), makekeyname(string(pSurname,' ',pForenames)), 'L', pForenames, pSurname,getword(pforenames,1), sdivid);
    INSERT INTO employment (employmentid, companyid, personid) VALUES (pEmploymentID, scoid, pEmploymentID);
    INSERT INTO iqxnetuser (IQXNetUserID,IQXNetUserClassID,Name,LoginID,EmailAddress) VALUES (pEmploymentID, userClass, string(getword(pForenames,1),' ',pSurname), pemail, pemail);
    INSERT INTO iqxnetuserlink (IQXNetUserLinkID,IQXNetUserID,PersonID,EmploymentID) VALUES (pEmploymentID, pEmploymentID, pEmploymentID, pEmploymentID);
    INSERT INTO iqxnetrightassigned (IQXNetUserID, IQXNetRightID, Assigned) SELECT pEmploymentID, IQXNetRightID, Assigned FROM iqxnetrightassigned WHERE IQXNetUserID=baseUserID;
    INSERT INTO VacancyRoleAllocation (VacancyID, EmploymentID, VacancyRoleID) SELECT VacancyID, pEmploymentID, VacancyRoleID FROM VacancyRoleAllocation WHERE EmploymentID=pBaseEmploymentID;
  ELSE
    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;
  END IF;
  IF isnull(pdirectphone,'') <> isnull(pOLDdirectphone,'') THEN
    CALL setphone('CP','Direct Telephone',pemploymentid,pdirectphone)
  END IF;
  IF isnull(pmobile,'') <> isnull(pOLDmobile,'') THEN
    CALL setphone('CP','Mobile',pemploymentid,pmobile)
  END IF;
  IF isnull(pemail,'') <> isnull(pOLDemail,'') THEN
    CALL setphone('CP','E-mail',pemploymentid,pemail);
    UPDATE iqxnetuserlink KEY JOIN iqxnetuser SET iqxnetuser.emailaddress = pemail WHERE iqxnetuserlink.employmentid = pemploymentid
  END IF;
  IF isnull(pdirectfax,'') <> isnull(pOLDdirectfax,'') THEN
    CALL setphone('CP','Direct Fax',pemploymentid,pdirectfax)
  END IF;
  SET ssql='';
  SET scomma='';
  IF isnull(pposition,'') <> isnull(pOLDposition,'') THEN
    SET ssql=string(ssql,scomma,'position=pposition');
    SET scomma=','
  END IF;
  IF isnull(pdepartment,'') <> isnull(pOLDdepartment,'') THEN
    SET ssql=string(ssql,scomma,'department=pdepartment');
    SET scomma=','
  END IF;
  IF ssql <> '' THEN
    EXECUTE IMMEDIATE string('update employment set ',ssql,' where employmentid=''',pemploymentid,'''')
  END IF;
  SELECT '0:~Success'
END
 
/* DOC
2016-08-29 PC Test and doc
2018-08-28 PC improved error handler
 
test:
    create new with missing surname
    create new
    create new with existing email
    create new with incorrect employmentid
    update one field
    update remaining fields at same time
*/
 
/* TEST
call NetTestSetup('');
delete from iqxnetuser where loginid='[email protected]'
 
 
delete from iqxnetrightassigned where iqxnetuserid='TEST.client' and iqxnetrightid='test';
delete from vacancyroleallocation where vacancyid='TEST';
delete from vacancyrole where vacancyroleid='TEST';
delete from iqxnetright where iqxnetrightid='TEST';
delete from employment where employmentid='TEST2';
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.client','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST2' where IQXNetUserLinkID='test.client';
insert into IQXNetRight values ('TEST','test',999);
insert into vacancyrole values ('TEST','TestRole','X',99,null);
insert into vacancyroleallocation values ('TEST','TEST','TEST');
 
select * from NetContactSet('test.client','NEW','0999 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename',null,'test');
expect pResult=91*;
select * from NetContactSet('test.client','NEW','0899 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename','TestSurname','test');
expect presult=0*;
select * from NetContactSet('test.client','NEW','0799 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename','TestSurname','test');
expect presult=90*;
select * from NetContactSet('test.client','T99','0699 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept',null,null,null,null,null,null,'TestForename','TestSurname','test');
expect presult=99*;
select * from NetContactSet('test.client','TEST','0599 999 996','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept','0999 999 999','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept','TestForename','TestSurname','test');
expect presult=0*;
select replace(number,' ','!') as A from phone where whoid='TEST' order by phonetypeid;
expect [email protected];
expect A=0999!999!992;
expect A=0999!999!991;
expect A=0599!999!996;
select * from NetContactSet('test.client','TEST','0499 999 995','0999 999 994','[email protected]','0999 999 993','TestPos2','TestDept2','0999 999 996','0999 999 998','[email protected]','0999 999 997','TestPos','TestDept','TestForename2','TestSurname2','test');
expect presult=0*;
select numberdigits from phone where whoid='TEST';
expect numberdigits=0499999995;
expect numberdigits=0999999994;
expect numberdigits=70;
expect numberdigits=0999999993;
expect EOF;
select loginid from iqxnetuser where emailaddress='[email protected]';
expect [email protected];
select surname from person where forenames='testforename';
expect surname=Testsurname;
 
delete from iqxnetrightassigned where iqxnetuserid='TEST.client' and iqxnetrightid='test';
delete from vacancyroleallocation where vacancyid='TEST';
delete from vacancyrole where vacancyroleid='TEST';
delete from iqxnetright where iqxnetrightid='TEST';
delete from iqxnetrightassigned where iqxnetuserid='TEST.client' and iqxnetrightid='test';
delete from employment where employmentid='TEST2';
delete from iqxnetuser where loginid='[email protected]'
 
 
*/
}
  • database/procedures/pears_netcontactset.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1