pears.NetAgencyCandidateCreate

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';
 
*/
}