====== pears.NetCandidateGetRegistrationComplete ======
<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"."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';
*/
}
</code>