pears.NetCandidateProfile

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