pears.NetCandidateSetRegistrationComplete

CREATE PROCEDURE "pears"."NetCandidateSetRegistrationComplete"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "pnewstatus" CHAR(1);
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '97:~Unique candidate link required';
    RETURN
  END IF;
  SET "pnewstatus" = "isnull"((SELECT "textvalue" FROM "iqxnetswitch" WHERE "IQXNetSwitchid" = 'DEFAULTCANDSTATE'),(SELECT top 1 "initialapplicantstatus" FROM "params" ORDER BY "dbid" ASC),'C');
  UPDATE "person" SET "status" = "pnewstatus" WHERE "personid" = "pPersonID";
  DELETE FROM "IQXNetWizardState" WHERE "IQXNetUserID" = "pWebUserID";
  SELECT '0:~Success'
END /* DOC
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries. Also modified method of extracting switch value
2019-4-8 MHS added cleanup of IQXNetWizardState
*/
/* TEST
call NetTestSetup('');
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
select isnull(status,'!') as A from person where personid='test';
expect A=C;
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test9');
expect 97*;
select pResult from NetCandidateSetRegistrationComplete('test.candidate1',null);
expect 97*;
update params set initialapplicantstatus=null;
update iqxnetswitch set textvalue=null where iqxnetswitchid='DEFAULTCANDSTATE';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as B from person where personid='test';
expect B=C;
update params set initialapplicantstatus='X';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as D from person where personid='test';
expect D=X;
update iqxnetswitch set textvalue='R' where iqxnetswitchid='DEFAULTCANDSTATE';
call NetCandidateSetRegistrationComplete('test.candidate','test');
select isnull(status,'!') as C from person where personid='test';
expect C=R;
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateSetRegistrationComplete" IS 
{CREATE PROCEDURE pears."NetCandidateSetRegistrationComplete"(pWebUserID CHAR(20),IN pPersonID CHAR(20) DEFAULT NULL)
RESULT(pResult CHAR(250) )
// IQXWeb
BEGIN
  DECLARE pnewstatus CHAR(1);
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  IF pPersonID IS NULL THEN
    SELECT '97:~Unique candidate link required';
    RETURN;
  END IF;  
  SET pnewstatus = isnull((SELECT textvalue FROM iqxnetswitch WHERE IQXNetSwitchid='DEFAULTCANDSTATE'), (SELECT top 1 "initialapplicantstatus" FROM "params" ORDER BY dbid), 'C');
  UPDATE person SET STATUS=pnewstatus WHERE personid=pPersonID;
  DELETE FROM IQXNetWizardState WHERE IQXNetUserID=pWebUserID;
  SELECT '0:~Success';
END
 
 /* DOC
2018-10-22 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries. Also modified method of extracting switch value
2019-4-8 MHS added cleanup of IQXNetWizardState
*/
 
/* TEST
call NetTestSetup('');
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
select isnull(status,'!') as A from person where personid='test';
expect A=C;
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test9');
expect 97*;
select pResult from NetCandidateSetRegistrationComplete('test.candidate1',null);
expect 97*;
update params set initialapplicantstatus=null;
update iqxnetswitch set textvalue=null where iqxnetswitchid='DEFAULTCANDSTATE';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as B from person where personid='test';
expect B=C;
update params set initialapplicantstatus='X';
select pResult from NetCandidateSetRegistrationComplete('test.candidate','test');
expect pResult=0*;
select isnull(status,'!') as D from person where personid='test';
expect D=X;
update iqxnetswitch set textvalue='R' where iqxnetswitchid='DEFAULTCANDSTATE';
call NetCandidateSetRegistrationComplete('test.candidate','test');
select isnull(status,'!') as C from person where personid='test';
expect C=R;
update person set status='C' where personid='test';
update iqxnetswitch set textvalue='C' where iqxnetswitchid='DEFAULTCANDSTATE';
update params set initialapplicantstatus='R';
*/
}