====== pears.NetAgencyCandidateCreate ======
<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"."NetAgencyCandidateCreate"( in "pWebUserID" char(20),in "pForenames" char(50),in "pSurname" char(50) ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "userClass" char(20);
  declare "newId" char(20);
  declare "secAgId" char(20);
  set "userClass" = (select top 1 "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID" order by "iqxnetuserclassid" asc);
  if "userClass" <> 'AGENCY' then
    select '98:~Not an agency login';
    return
  end if;
  set "newId" = "uniquekey"('');
  set "secAgId" = (select top 1 "e"."companyid" from "iqxnetuserlink" as "x" key join "employment" as "e" where "x"."iqxnetuserid" = "pWebUserID" order by "e"."employmentid" asc);
  if "secAgId" is null then
    select '97:~No company link';
    return
  end if;
  insert into "person"( "personid","name","keyname","surname","forenames","status" ) values
    ( "newId","string"("getword"("pForenames",1),' ',"pSurname"),"makekeyname"("string"("pSurname",' ',"pForenames")),"pSurname","pForenames",'A' ) ;
  insert into "pay_employee"( "personid","secondaryagencyid" ) values
    ( "newId","secAgId" ) ;
  select '0:'+"newId"+'~Success'
end /* DOC
25-11-2016 PC Test and doc
17-04-2018 PC/MS modify tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries

Inputs:
pWebUSerID
pForenames
pSurname

Outputs:
pResult

Tests:
valid pWebUserID but no company link
all fields valid

*/
/* TEST
NetTestSetup('');
delete from pay_employee where personid in (select personid from person where surname='Test999');
delete from person where surname='Test999';
select * from NetAgencyCandidateCreate('test.agency','Test999','Test999');
expect pResult=0*;
select count(*) as A from person where surname='Test999';
expect A=1;
select top 1 secondaryagencyid as A from pay_employee where personid=(select top 1 personid from person where surname='Test999');
expect A=TEST;
delete from pay_employee where personid in (select personid from person where surname='Test999');
delete from person where surname='Test999';

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCandidateCreate" IS 
{create PROCEDURE pears."NetAgencyCandidateCreate"( in pWebUserID char(20),in pForenames char(50), in pSurname char(50) ) 
result( pResult char(250) ) 
// IQXWeb
begin
  declare userClass char(20);
  declare newId char(20);
  declare secAgId char(20);
  
  set userClass=(select top 1 iqxnetuserclassid from iqxnetuser where iqxnetuserid = pWebUserID order by iqxnetuserclassid);
  if userClass<>'AGENCY' then
    select '98:~Not an agency login';
    return;
  end if;
  
  set newId=uniquekey('');
  set secAgId=(select top 1 e.companyid from iqxnetuserlink x key join employment e where x.iqxnetuserid=pWebUserID order by e.employmentid);
  if secAgId is null then
    select '97:~No company link';
    return;
  end if;
  
  insert into person (personid, name, keyname, surname, forenames, status) 
   values 
  (newId, string(getword(pForenames,1),' ',pSurname), makekeyname(string(pSurname,' ',pForenames)), pSurname, pForenames, 'A');

  insert into pay_employee (personid,secondaryagencyid)
   values
  (newId, secAgId);
  
  select '0:' + newId + '~Success'
end

/* DOC
25-11-2016 PC Test and doc
17-04-2018 PC/MS modify tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries

Inputs:
    pWebUSerID
    pForenames
    pSurname

Outputs:
    pResult

Tests:
    valid pWebUserID but no company link
    all fields valid

*/

/* TEST
NetTestSetup('');
delete from pay_employee where personid in (select personid from person where surname='Test999');
delete from person where surname='Test999';
select * from NetAgencyCandidateCreate('test.agency','Test999','Test999');
expect pResult=0*;
select count(*) as A from person where surname='Test999';
expect A=1;
select top 1 secondaryagencyid as A from pay_employee where personid=(select top 1 personid from person where surname='Test999');
expect A=TEST;
delete from pay_employee where personid in (select personid from person where surname='Test999');
delete from person where surname='Test999';

*/
}
</code>