====== 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='test9@test.com'
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','test9@test.com','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','test9@test.com','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','test9@test.com','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','test9@test.com','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','test9@test.com','0999 999 997','TestPos','TestDept','0999 999 999','0999 999 998','test9@test.com','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 A=test99@test.com;
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','test70@test.com','0999 999 993','TestPos2','TestDept2','0999 999 996','0999 999 998','test9@test.com','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='test9@test.com';
expect LoginID=test9@test.com;
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='test9@test.com'
*/
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='test9@test.com'
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','test9@test.com','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','test9@test.com','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','test9@test.com','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','test9@test.com','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','test9@test.com','0999 999 997','TestPos','TestDept','0999 999 999','0999 999 998','test9@test.com','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 A=test99@test.com;
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','test70@test.com','0999 999 993','TestPos2','TestDept2','0999 999 996','0999 999 998','test9@test.com','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='test9@test.com';
expect LoginID=test9@test.com;
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='test9@test.com'
*/
}