pears.NetCandidateProfileSet

CREATE PROCEDURE "pears"."NetCandidateProfileSet"( IN "pWebUserID" CHAR(20),IN "ppersonid" CHAR(20),IN "pForenames" CHAR(50) DEFAULT 'NOT-SET',IN "pSurname" CHAR(50) DEFAULT 'NOT-SET',IN "pGender" CHAR(20) DEFAULT 'NOT-SET',IN "pdob" CHAR(20) DEFAULT 'NOT-SET',IN "phomephone" CHAR(250) DEFAULT 'NOT-SET',IN "pDayPhone" CHAR(250) DEFAULT 'NOT-SET',IN "pMobile" CHAR(250) DEFAULT 'NOT-SET',IN "pemail" CHAR(250) DEFAULT 'NOT-SET',IN "pAddr1" CHAR(50) DEFAULT 'NOT-SET',IN "pAddr2" CHAR(50) DEFAULT 'NOT-SET',IN "pAddr3" CHAR(50) DEFAULT 'NOT-SET',IN "pTown" CHAR(50) DEFAULT 'NOT-SET',IN "pCounty" CHAR(50) DEFAULT 'NOT-SET',IN "pCountry" CHAR(50) DEFAULT 'NOT-SET',IN "pPostcode" CHAR(50) DEFAULT 'NOT-SET',IN "pNINumber" CHAR(20) DEFAULT 'NOT-SET',IN "pLoginID" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDForenames" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDSurname" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDGender" CHAR(20) DEFAULT 'NOT-SET',IN "pOLDdob" CHAR(20) DEFAULT 'NOT-SET',IN "pOLDhomephone" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDDayPhone" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDMobile" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDemail" CHAR(250) DEFAULT 'NOT-SET',IN "pOLDAddr1" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDAddr2" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDAddr3" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDTown" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDCounty" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDCountry" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDPostcode" CHAR(50) DEFAULT 'NOT-SET',IN "pOLDNINumber" CHAR(20) DEFAULT 'NOT-SET',IN "pOLDLoginID" CHAR(50) DEFAULT 'NOT-SET',
  IN "qanswers" long VARCHAR DEFAULT NULL,
  IN "pSecondaryAgencyRef" CHAR(100) DEFAULT NULL,IN "pSecondaryAgencyCandidateStatus" CHAR(1) DEFAULT NULL,IN "pOptInToMarketing" tinyint DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "dateofbirth" DATE;
  DECLARE "bnamechanged" SMALLINT;
  DECLARE "sname1" CHAR(50);
  DECLARE "fname1" CHAR(50);
  DECLARE "ssql" CHAR(2048);
  DECLARE "CompressedNI" CHAR(20);
  SET "bnamechanged" = 0;
  SET "sname1" = 'surname';
  SET "fname1" = 'forenames';
  SET "CompressedNI" = "replace"("pNINumber",' ','');
  SET "ssql" = '';
  IF("NetHasPermission"("pWebUserID","pPersonID",NULL)+(SELECT "count"() FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid" AND "iqxnetuserclassid" = 'OWNER')) = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "isnull"("pforenames",'') <> "isnull"("poldforenames",'') THEN
    SET "ssql" = "ssql"+',forenames=pforenames';
    SET "ssql" = "ssql"+',salutation=getword(pforenames,1)';
    SET "bnamechanged" = 1;
    SET "fname1" = 'pforenames'
  END IF;
  IF "isnull"("psurname",'') <> "isnull"("poldsurname",'') THEN
    SET "ssql" = "ssql"+',surname=psurname';
    SET "bnamechanged" = 1;
    SET "sname1" = 'psurname'
  END IF;
  IF "bnamechanged" = 1 THEN
    SET "ssql" = "ssql"+',name=string(getword('+"fname1"+',1),'' '','+"sname1"+')';
    SET "ssql" = "ssql"+',keyname=makekeyname(string('+"sname1"+','' '','+"fname1"+'))'
  END IF;
  IF "isnull"("pgender",'') <> "isnull"("poldgender",'') THEN
    SET "ssql" = "ssql"+',sex=pgender'
  END IF;
  IF "isnull"("pdob",'') <> "isnull"("pOLDdob",'') THEN
    SET "dateofbirth" = "IQXNetStringToDate"("pdob");
    IF "trim"("isnull"("pdob",'')) <> '' AND "dateofbirth" IS NULL THEN
      SELECT '1:~Invalid date of birth';
      RETURN
    END IF;
    SET "ssql" = "ssql"+',dob = dateofbirth'
  END IF;
  IF "isnull"("paddr1",'') <> "isnull"("poldaddr1",'') THEN
    SET "ssql" = "ssql"+',addr1=paddr1'
  END IF;
  IF "isnull"("paddr2",'') <> "isnull"("poldaddr2",'') THEN
    SET "ssql" = "ssql"+',addr2=paddr2'
  END IF;
  IF "isnull"("paddr3",'') <> "isnull"("poldaddr3",'') THEN
    SET "ssql" = "ssql"+',addr3=paddr3'
  END IF;
  IF "isnull"("ptown",'') <> "isnull"("poldtown",'') THEN
    SET "ssql" = "ssql"+',town=ptown'
  END IF;
  IF "isnull"("pcounty",'') <> "isnull"("poldcounty",'') AND "isnull"("WPKMaintainGetSwitchValue"('INCCOUNTY','','L'),'N') <> 'N' THEN
    SET "ssql" = "ssql"+',county=pcounty'
  END IF;
  IF "isnull"("pcountry",'') <> "isnull"("poldcountry",'') AND "isnull"("WPKMaintainGetSwitchValue"('INCCOUNTRY','','L'),'N') <> 'N' THEN
    SET "ssql" = "ssql"+',country=pcountry'
  END IF;
  IF "isnull"("ppostcode",'') <> "isnull"("poldpostcode",'') THEN
    SET "ssql" = "ssql"+',postcode=ucase(ppostcode)'
  END IF;
  IF "isnull"("pNINumber",'') <> "isnull"("poldNINumber",'') THEN
    SET "ssql" = "ssql"+',ni=ucase(CompressedNI)'
  END IF;
  IF "pOptInToMarketing" IS NOT NULL THEN
    SET "ssql" = "ssql"+',UnsubscribeToMarketing=(if isnull(pOptInToMarketing,0)=0 then 1 else 0 endif)'
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE 'update person set '+"stuff"("ssql",1,1,'')+' where personid=ppersonid'
  END IF;
  IF "isnull"("phomephone",'') <> "isnull"("pOLDhomephone",'') THEN
    CALL "setphone"('P','Home Telephone',"ppersonid","phomephone")
  END IF;
  IF "isnull"("pdayphone",'') <> "isnull"("pOLDdayphone",'') THEN
    CALL "setphone"('P','Day Telephone',"ppersonid","pdayphone")
  END IF;
  IF "isnull"("pmobile",'') <> "isnull"("pOLDmobile",'') THEN
    CALL "setphone"('P','Mobile',"ppersonid","pmobile")
  END IF;
  IF "isnull"("pemail",'') <> "isnull"("pOLDemail",'') THEN
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."personid" = "ppersonid";
    CALL "setphone"('P','E-mail',"ppersonid","pemail")
  END IF;
  IF "pSecondaryAgencyRef" IS NOT NULL THEN
    UPDATE "pay_employee" SET "SecondaryAgencyRef" = "ucase"("pSecondaryAgencyRef") WHERE "personid" = "pPersonID"
  END IF;
  IF "pSecondaryAgencyCandidateStatus" IS NOT NULL THEN
    UPDATE "pay_employee" SET "SecondaryAgencyCandidateStatus" = "pSecondaryAgencyCandidateStatus" WHERE "personid" = "pPersonID"
  END IF;
  CALL "IQXNetSaveQuestionnaire"("ppersonid","qanswers");
  CALL "personrecordupdated"("ppersonid");
  SELECT '0:~Success'
END /* DOC
2016-12-02 PC test and doc
2018-01-25 PC BR-13 and IW-371 add NI number to details and remove login details
2018-02-14 PC OP-30 allow consultants to view and edit details
2018-02-16 PC BR-21 remove spaces from NI number
2018-04-23 MHS IW-426 Added SecondaryAgencyCandidateStatus, changed param to pSecondaryAgencyRef and simplified tests
2018-05-17 GJ Added UnsubscribeToMarketing Support
2018-07-24 PC include improved error handler
2020-03-02 GJ Changed order of updating the email address so an error can be triggered before its saved.
2020-04-02 GJ Check the switch values in general settings for include county and include country
 
Inputs:
pWebUserID
ppersonid
pForenames
pSurname
pGender
pdob
phomephone
pDayPhone
pMobile
pemail
pAddr1
pAddr2
pAddr3
pTown
pCounty
pCountry
pPostcode
pLoginID
pOLDForenames
pOLDSurname
pOLDGender
pOLDdob
pOLDhomephone
pOLDDayPhone
pOLDMobile
pOLDemail
pOLDAddr1
pOLDAddr2
pOLDAddr3
pOLDTown
pOLDCounty
pOLDCountry
pOLDPostcode
pOLDLoginID
qanswers
pSecondaryAgencyRef
pSecondaryAgencyCandidateStatus
pUnsubscribeToMarketing
 
Outputs:
pResult
 
Tests:
 
*/
/* TEST
call NetTestSetup('');
call NetCandidateProfileSet(pWebUserID='test.candidate',pPersonID='TEST',pForenames='Fred',pSurname='Bloggs');
select * from NetCandidateProfile('test.candidate');
expect Forenames=Fred, Surname=Bloggs;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateProfileSet" IS 
{CREATE PROCEDURE pears."NetCandidateProfileSet"( IN pWebUserID CHAR(20),IN ppersonid CHAR(20),IN pForenames CHAR(50) DEFAULT 'NOT-SET',IN pSurname CHAR(50) DEFAULT 'NOT-SET',IN pGender CHAR(20) DEFAULT 'NOT-SET',IN pdob CHAR(20) DEFAULT 'NOT-SET',IN phomephone CHAR(250) DEFAULT 'NOT-SET',IN pDayPhone CHAR(250) DEFAULT 'NOT-SET',IN pMobile CHAR(250) DEFAULT 'NOT-SET',IN pemail CHAR(250) DEFAULT 'NOT-SET',IN pAddr1 CHAR(50) DEFAULT 'NOT-SET',IN pAddr2 CHAR(50) DEFAULT 'NOT-SET',IN pAddr3 CHAR(50) DEFAULT 'NOT-SET',IN pTown CHAR(50) DEFAULT 'NOT-SET',IN pCounty CHAR(50) DEFAULT 'NOT-SET',IN pCountry CHAR(50) DEFAULT 'NOT-SET',IN pPostcode CHAR(50) DEFAULT 'NOT-SET',IN pNINumber CHAR(20) DEFAULT 'NOT-SET',IN pLoginID CHAR(50) DEFAULT 'NOT-SET',IN pOLDForenames CHAR(50) DEFAULT 'NOT-SET',IN pOLDSurname CHAR(50) DEFAULT 'NOT-SET',IN pOLDGender CHAR(20) DEFAULT 'NOT-SET',IN pOLDdob CHAR(20) DEFAULT 'NOT-SET',IN pOLDhomephone CHAR(250) DEFAULT 'NOT-SET',IN pOLDDayPhone CHAR(250) DEFAULT 'NOT-SET',IN pOLDMobile CHAR(250) DEFAULT 'NOT-SET',IN pOLDemail CHAR(250) DEFAULT 'NOT-SET',IN pOLDAddr1 CHAR(50) DEFAULT 'NOT-SET',IN pOLDAddr2 CHAR(50) DEFAULT 'NOT-SET',IN pOLDAddr3 CHAR(50) DEFAULT 'NOT-SET',IN pOLDTown CHAR(50) DEFAULT 'NOT-SET',IN pOLDCounty CHAR(50) DEFAULT 'NOT-SET',IN pOLDCountry CHAR(50) DEFAULT 'NOT-SET',IN pOLDPostcode CHAR(50) DEFAULT 'NOT-SET',IN pOLDNINumber CHAR(20) DEFAULT 'NOT-SET',IN pOLDLoginID CHAR(50) DEFAULT 'NOT-SET',
  IN qanswers long VARCHAR DEFAULT NULL, 
  IN pSecondaryAgencyRef CHAR(100) DEFAULT NULL, IN pSecondaryAgencyCandidateStatus CHAR(1) DEFAULT NULL, pOptInToMarketing tinyint DEFAULT NULL ) 
RESULT( pResult CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE dateofbirth DATE;
  DECLARE bnamechanged SMALLINT;
  DECLARE sname1 CHAR(50);
  DECLARE fname1 CHAR(50);
  DECLARE ssql CHAR(2048);
  DECLARE CompressedNI CHAR(20);
  SET bnamechanged = 0;
  SET sname1 = 'surname';
  SET fname1 = 'forenames';
  SET CompressedNI=REPLACE("pNINumber",' ','');
  SET ssql = '';
  IF (NetHasPermission(pWebUserID,pPersonID,NULL)+ (SELECT COUNT() FROM iqxnetuser WHERE iqxnetuserid=pwebuserid AND iqxnetuserclassid='OWNER'))=0  THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF isnull(pforenames,'') <> isnull(poldforenames,'') THEN
    SET ssql = ssql+',forenames=pforenames';
    SET ssql = ssql+',salutation=getword(pforenames,1)';
    SET bnamechanged = 1;
    SET fname1 = 'pforenames'
  END IF;
  IF isnull(psurname,'') <> isnull(poldsurname,'') THEN
    SET ssql = ssql+',surname=psurname';
    SET bnamechanged = 1;
    SET sname1 = 'psurname'
  END IF;
  IF bnamechanged = 1 THEN
    SET ssql = ssql+',name=string(getword('+fname1+',1),'' '','+sname1+')';
    SET ssql = ssql+',keyname=makekeyname(string('+sname1+','' '','+fname1+'))'
  END IF;
  IF isnull(pgender,'') <> isnull(poldgender,'') THEN
    SET ssql = ssql+',sex=pgender'
  END IF;
  IF isnull(pdob,'') <> isnull(pOLDdob,'') THEN
    SET dateofbirth = IQXNetStringToDate(pdob);
    IF TRIM(isnull(pdob,'')) <> '' AND dateofbirth IS NULL THEN
      SELECT '1:~Invalid date of birth';
      RETURN
    END IF;
    SET ssql = ssql+',dob = dateofbirth'
  END IF;
  IF isnull(paddr1,'') <> isnull(poldaddr1,'') THEN
    SET ssql = ssql+',addr1=paddr1'
  END IF;
  IF isnull(paddr2,'') <> isnull(poldaddr2,'') THEN
    SET ssql = ssql+',addr2=paddr2'
  END IF;
  IF isnull(paddr3,'') <> isnull(poldaddr3,'') THEN
    SET ssql = ssql+',addr3=paddr3'
  END IF;
  IF isnull(ptown,'') <> isnull(poldtown,'') THEN
    SET ssql = ssql+',town=ptown'
  END IF;
  IF isnull(pcounty,'') <> isnull(poldcounty,'') AND isnull(WPKMaintainGetSwitchValue('INCCOUNTY', '', 'L'), 'N') <> 'N' THEN
    SET ssql = ssql+',county=pcounty'
  END IF;
  IF isnull(pcountry,'') <> isnull(poldcountry,'') AND isnull(WPKMaintainGetSwitchValue('INCCOUNTRY', '', 'L'), 'N') <> 'N' THEN
    SET ssql = ssql+',country=pcountry'
  END IF;
  IF isnull(ppostcode,'') <> isnull(poldpostcode,'') THEN
    SET ssql = ssql+',postcode=ucase(ppostcode)'
  END IF;
  IF isnull(pNINumber,'') <> isnull(poldNINumber,'') THEN
    SET ssql = ssql+',ni=ucase(CompressedNI)'
  END IF;
  IF pOptInToMarketing IS NOT NULL THEN
    SET ssql = ssql+',UnsubscribeToMarketing=(if isnull(pOptInToMarketing,0)=0 then 1 else 0 endif)'
  END IF;
  IF ssql <> '' THEN
    EXECUTE IMMEDIATE 'update person set '+stuff(ssql,1,1,'')+' where personid=ppersonid'
  END IF;
  IF isnull(phomephone,'') <> isnull(pOLDhomephone,'') THEN
    CALL setphone('P','Home Telephone',ppersonid,phomephone)
  END IF;
  IF isnull(pdayphone,'') <> isnull(pOLDdayphone,'') THEN
    CALL setphone('P','Day Telephone',ppersonid,pdayphone)
  END IF;
  IF isnull(pmobile,'') <> isnull(pOLDmobile,'') THEN
    CALL setphone('P','Mobile',ppersonid,pmobile)
  END IF;
  IF isnull(pemail,'') <> isnull(pOLDemail,'') THEN
    UPDATE iqxnetuserlink KEY JOIN iqxnetuser SET iqxnetuser.emailaddress = pemail WHERE iqxnetuserlink.personid = ppersonid;
    CALL setphone('P','E-mail',ppersonid,pemail)
  END IF;
  IF pSecondaryAgencyRef IS NOT NULL THEN
    UPDATE pay_employee SET SecondaryAgencyRef=ucase(pSecondaryAgencyRef) WHERE personid=pPersonID;
  END IF;
  IF pSecondaryAgencyCandidateStatus IS NOT NULL THEN
    UPDATE pay_employee SET SecondaryAgencyCandidateStatus=pSecondaryAgencyCandidateStatus WHERE personid=pPersonID;
  END IF;
  CALL IQXNetSaveQuestionnaire(ppersonid,qanswers);
  CALL personrecordupdated(ppersonid);
  SELECT '0:~Success'
END
 
/* DOC
2016-12-02 PC test and doc
2018-01-25 PC BR-13 and IW-371 add NI number to details and remove login details
2018-02-14 PC OP-30 allow consultants to view and edit details
2018-02-16 PC BR-21 remove spaces from NI number
2018-04-23 MHS IW-426 Added SecondaryAgencyCandidateStatus, changed param to pSecondaryAgencyRef and simplified tests
2018-05-17 GJ Added UnsubscribeToMarketing Support
2018-07-24 PC include improved error handler
2020-03-02 GJ Changed order of updating the email address so an error can be triggered before its saved.
2020-04-02 GJ Check the switch values in general settings for include county and include country
 
Inputs:
    pWebUserID
    ppersonid
    pForenames
    pSurname
    pGender
    pdob
    phomephone
    pDayPhone
    pMobile
    pemail
    pAddr1
    pAddr2
    pAddr3
    pTown
    pCounty
    pCountry
    pPostcode
    pLoginID
    pOLDForenames
    pOLDSurname
    pOLDGender
    pOLDdob
    pOLDhomephone
    pOLDDayPhone
    pOLDMobile
    pOLDemail
    pOLDAddr1
    pOLDAddr2
    pOLDAddr3
    pOLDTown
    pOLDCounty
    pOLDCountry
    pOLDPostcode
    pOLDLoginID
    qanswers
    pSecondaryAgencyRef
    pSecondaryAgencyCandidateStatus
    pUnsubscribeToMarketing
 
Outputs:
    pResult
 
Tests:
 
*/
 
/* TEST
call NetTestSetup('');
call NetCandidateProfileSet(pWebUserID='test.candidate',pPersonID='TEST',pForenames='Fred',pSurname='Bloggs');
select * from NetCandidateProfile('test.candidate');
expect Forenames=Fred, Surname=Bloggs;
*/
}