pears.NetContactSet [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.NetContactSet ======
<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"."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'


*/
}
</code>