====== pears.NetCandidateProfile ======
<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"."NetCandidateProfile"( in "pWebUserID" char(20),in "pPersonID" char(20) default null ) 
result( "PersonID" char(20),"Forenames" char(50),"Surname" char(50),"Gender" char(1),"DOB" date,"HomePhone" char(250),"DayPhone" char(250),"Mobile" char(250),"Email" char(250),"Addr1" char(50),"Addr2" char(50),"Addr3" char(50),"Town" char(50),"County" char(50),"Country" char(50),"Postcode" char(50),"NINumber" char(20),"SecondaryAgencyRef" char(20),"SecondaryAgencyCandidateStatus" char(1),"OptInToMarketing" tinyint ) 
// IQXWeb
begin
  if "trim"("isnull"("pPersonID",'')) = '' then
    set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID",null)
  else if "NetHasPermission"("pWebUserID","pPersonID",null) = 0 then
      set "pPersonID" = null
    end if end if;
  select "person"."personid","person"."forenames","person"."surname","person"."sex","person"."dob",
    "getphone"('P','Home Telephone',"person"."personid") as "HomePhone","getphone"('P','Day Telephone',"person"."personid") as "DayPhone",
    "getphone"('P','Mobile',"person"."personid") as "Mobile","netgetemail"('P',"person"."personid") as "Email",
    "person"."addr1","person"."addr2","person"."addr3","person"."town","person"."county","person"."country","person"."postcode","person"."ni","pay_employee"."SecondaryAgencyRef","pay_employee"."SecondaryAgencyCandidateStatus",if "isnull"("UnsubscribeToMarketing",0) = 0 then 1 else 0 endif as "OptInToMarketing"
    from "person" key left outer join "pay_employee" where "person"."personid" = "pPersonID"
end /* DOC
2018-01-25 PC BR-13 and IW-371 add NI to registration details tab and remove loginid
2018-02-14 PC OP-30 allow consultants to view and edit details
2018-04-23 MHS IW-426 add SecondaryAgencyCandidateStatus
2018-05-17 GJ Added UnsubscribeToMarketing Support
2018-10-22 MHS IW-456 multi-cand fix and tests
*/
/* TEST
call NetTestSetup('');
def test select * from NetCandidateProfile('TEST.CANDIDATE');
do test;
expect forenames=test,surname=test; 
call NetCandidateProfileSet(pWebUserID='TEST.CANDIDATE', pPersonID='TEST', pForenames='Herbert', pSurname='Smith');
do test;
expect forenames=Herbert, surname=Smith; 
expect EOF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateProfile" IS 
{create PROCEDURE pears."NetCandidateProfile"( in pWebUserID char(20), in pPersonID char(20) default null ) 
result( PersonID char(20),Forenames char(50),Surname char(50),Gender char(1),DOB date,HomePhone char(250),DayPhone char(250),Mobile char(250),Email char(250),Addr1 char(50),Addr2 char(50),Addr3 char(50),Town char(50),County char(50),Country char(50),Postcode char(50),NINumber char(20), SecondaryAgencyRef char(20), SecondaryAgencyCandidateStatus char(1), OptInToMarketing tinyint ) 
// IQXWeb
begin
  if trim(isnull(pPersonID,''))='' then
     set pPersonID=NetCandidateValidPersonID(pWebUserID,null)
  else if NetHasPermission(pWebUserID,pPersonID,null)=0 then
     set pPersonID=null
  end if;
  end if;
  select person.personid,person.forenames,person.surname,person.sex,person.dob,
    getphone('P','Home Telephone',person.personid) as HomePhone,getphone('P','Day Telephone',person.personid) as DayPhone,
    getphone('P','Mobile',person.personid) as Mobile,netgetemail('P',person.personid) as Email,
    person.addr1,person.addr2,person.addr3,person.town,person.county,person.country,person.postcode,person.ni, pay_employee.SecondaryAgencyRef, pay_employee.SecondaryAgencyCandidateStatus, if isnull(UnsubscribeToMarketing,0)=0 then 1 else 0 endif as OptInToMarketing
    from person key left outer join pay_employee where person.personid=pPersonID
end

/* DOC
2018-01-25 PC BR-13 and IW-371 add NI to registration details tab and remove loginid
2018-02-14 PC OP-30 allow consultants to view and edit details
2018-04-23 MHS IW-426 add SecondaryAgencyCandidateStatus
2018-05-17 GJ Added UnsubscribeToMarketing Support
2018-10-22 MHS IW-456 multi-cand fix and tests
*/

/* TEST
call NetTestSetup('');
def test select * from NetCandidateProfile('TEST.CANDIDATE');
do test;
expect forenames=test,surname=test; 
call NetCandidateProfileSet(pWebUserID='TEST.CANDIDATE', pPersonID='TEST', pForenames='Herbert', pSurname='Smith');
do test;
expect forenames=Herbert, surname=Smith; 
expect EOF;
*/
}
</code>