pears.NetCandidateGetRegistrationComplete

CREATE PROCEDURE "pears"."NetCandidateGetRegistrationComplete"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "isComplete" INTEGER,"wizardID" CHAR(20),"personid" CHAR(20) ) 
// IQXWeb
BEGIN
  DECLARE "unregstat" CHAR(1);
  DECLARE "actualstat" CHAR(1);
  DECLARE "wizID" CHAR(20);
  SET "pPersonID" = "nullif"("trim"("pPersonID"),'');
  IF "pPersonID" IS NULL THEN
    IF(SELECT "count"() FROM "iqxnetuserlink" AS "l" KEY JOIN "person" AS "p"
        WHERE "l"."iqxnetuserid" = "pWebUserID" AND "p"."status" <> '.') > 1 THEN
      SELECT 2,NULL,NULL;
      RETURN
    END IF END IF;
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    select-1,NULL,NULL;
    RETURN
  END IF;
  SELECT "IQXNetSwitchValue"("pwebuserid",'UNREGCANDSTATE'),"person"."status",
    (SELECT FIRST "wizardID" FROM "IQXNetWizardState" WHERE "IQXNetUserID" = "pWebUserID" ORDER BY 1 ASC)
    INTO "unregstat","actualstat","wizID"
    FROM "person" WHERE "personid" = "pPersonID";
  IF "actualstat" = "unregstat" THEN
    SELECT 0,"wizID","pPersonID"
  ELSE
    SELECT 1,"wizID","pPersonID"
  END IF
END /* DOC
2018-10-22 MHS IW-456 multi-cand fix
2018-10-31 MHS return 2 if ambiguous link. Return -1 if no valid link. Tests.
2018-12-19 MHS now exclude archived records
2019-04-10 MHS added look up of uncompleted soft wizard
2019-04-16 GJ Now returns personID when '1' is returned.
*/
/* TEST
call NetTestSetup('');
delete from iqxnetuserlink where iqxnetuserid='test.candidate' and iqxnetuserlinkid='TESTXXX';
select * from NetCandidateGetRegistrationComplete('test.client');
expect isComplete=-1;
select * from NetCandidateGetRegistrationComplete('test.candidate');
expect ROW;
insert into iqxnetuserlink (iqxnetuserlinkid,iqxnetuserid,personid) values ('TESTXXX','test.candidate','TEST');
select * from NetCandidateGetRegistrationComplete('test.candidate');
expect isComplete=2;
delete from iqxnetuserlink where iqxnetuserlinkid='TESTXXX';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateGetRegistrationComplete" IS 
{CREATE PROCEDURE pears."NetCandidateGetRegistrationComplete"(pWebUserID CHAR(20),IN pPersonID CHAR(20) DEFAULT NULL)
RESULT(isComplete INTEGER, wizardID CHAR(20), personid CHAR(20) )
// IQXWeb
BEGIN
  DECLARE unregstat CHAR(1);
  DECLARE actualstat CHAR(1);
  DECLARE wizID CHAR(20);
  SET pPersonID=NULLIF(TRIM(pPersonID),'');
  IF pPersonID IS NULL THEN
    IF (SELECT COUNT(*) FROM iqxnetuserlink l KEY JOIN person p WHERE 
           l.iqxnetuserid=pWebUserID AND p.status<>'.') > 1 THEN
      SELECT 2, NULL, NULL;
      RETURN;
    END IF;
  END IF;
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  IF pPersonID IS NULL THEN
    SELECT -1, NULL, NULL;
    RETURN;
  END IF;
  SELECT IQXNetSwitchValue(pwebuserid,'UNREGCANDSTATE'), person.status, 
   (SELECT FIRST wizardID FROM IQXNetWizardState WHERE IQXNetUserID=pWebUserID ORDER BY 1) 
   INTO unregstat, actualstat, wizID 
   FROM person WHERE personid=pPersonID; 
  IF actualstat=unregstat THEN
     SELECT 0, wizID, pPersonID;
  ELSE
     SELECT 1, wizID, pPersonID;
  END IF;
END
 
 /* DOC
2018-10-22 MHS IW-456 multi-cand fix
2018-10-31 MHS return 2 if ambiguous link. Return -1 if no valid link. Tests.
2018-12-19 MHS now exclude archived records
2019-04-10 MHS added look up of uncompleted soft wizard
2019-04-16 GJ Now returns personID when '1' is returned.
*/
 
/* TEST
call NetTestSetup('');
delete from iqxnetuserlink where iqxnetuserid='test.candidate' and iqxnetuserlinkid='TESTXXX';
select * from NetCandidateGetRegistrationComplete('test.client');
expect isComplete=-1;
select * from NetCandidateGetRegistrationComplete('test.candidate');
expect ROW;
insert into iqxnetuserlink (iqxnetuserlinkid,iqxnetuserid,personid) values ('TESTXXX','test.candidate','TEST');
select * from NetCandidateGetRegistrationComplete('test.candidate');
expect isComplete=2;
delete from iqxnetuserlink where iqxnetuserlinkid='TESTXXX';
*/
}