pears.NetOwnerCandidateProfileSet

CREATE PROCEDURE "pears"."NetOwnerCandidateProfileSet"( 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 "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 "qanswers" long VARCHAR DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "dateofbirth" DATE;
  DECLARE "bnamechanged" SMALLINT;
  DECLARE "ssql" CHAR(255);
  DECLARE "DivId" CHAR(20);
  DECLARE "PersID" CHAR(20);
  DECLARE @StaffID CHAR(20);
  DECLARE "PayrollCoID" CHAR(20);
  SET "bnamechanged" = 0;
  SET "ssql" = '';
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  IF "IQXNetHasPermission"("pWebUserID",'ADDCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "isnull"("pForenames",'') = '' OR "isnull"("pSurname",'') = '' THEN
    SELECT '100:~Please enter both a forename and a surname';
    RETURN
  END IF;
  IF((SELECT "count"() FROM "person" WHERE "person"."personid" = "ppersonid") = 0) THEN
    SET @StaffID = (SELECT FIRST "staffid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
    SET "PersID" = "uniquekey"("pForenames"+"pSurname");
    SET "PayrollCoID" = (SELECT "DefPayrollCoID" FROM "Division" WHERE "divisionid" = "DivID");
    INSERT INTO "person"( "personid","staffid","divisionid","name","keyname","status","forenames","surname","salutation","registrationdate","payrollnumber","PayrollIdentifier" ) VALUES( "PersID",@StaffID,"DivID","pForenames"+' '+"pSurname","ucase"("pSurname")+' '+"ucase"("pForenames"),'C',"pForenames","pSurname","pForenames","now"(),'NEW',"PayrollCoID" ) 
  ELSE
    SET "PersID" = "ppersonid";
    IF "isnull"("pforenames",'') <> "isnull"("poldforenames",'') THEN
      SET "ssql" = "ssql"+',forenames=pforenames';
      SET "ssql" = "ssql"+',salutation=getword(pforenames,1)';
      SET "bnamechanged" = 1
    END IF;
    IF "isnull"("psurname",'') <> "isnull"("poldsurname",'') THEN
      SET "ssql" = "ssql"+',surname=psurname';
      SET "bnamechanged" = 1
    END IF;
    IF "bnamechanged" = 1 THEN
      SET "ssql" = "ssql"+',name=string(getword(pforenames,1),'' '',psurname)';
      SET "ssql" = "ssql"+',keyname=makekeyname(string(psurname,'' '',pforenames))'
    END IF 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",'') THEN
    SET "ssql" = "ssql"+',county=pcounty'
  END IF;
  IF "isnull"("pcountry",'') <> "isnull"("poldcountry",'') THEN
    SET "ssql" = "ssql"+',country=pcountry'
  END IF;
  IF "isnull"("ppostcode",'') <> "isnull"("poldpostcode",'') THEN
    SET "ssql" = "ssql"+',postcode=ucase(ppostcode)'
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE 'update person set '+"stuff"("ssql",1,1,'')+' where personid=PersID'
  END IF;
  IF "isnull"("phomephone",'') <> "isnull"("pOLDhomephone",'') THEN
    CALL "setphone"('P','Home Telephone',"PersID","phomephone")
  END IF;
  IF "isnull"("pdayphone",'') <> "isnull"("pOLDdayphone",'') THEN
    CALL "setphone"('P','Day Telephone',"PersID","pdayphone")
  END IF;
  IF "isnull"("pmobile",'') <> "isnull"("pOLDmobile",'') THEN
    CALL "setphone"('P','Mobile',"PersID","pmobile")
  END IF;
  IF "isnull"("pemail",'') <> "isnull"("pOLDemail",'') THEN
    CALL "setphone"('P','E-mail',"PersID","pemail");
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."personid" = "PersID"
  END IF;
  CALL "IQXNetSaveQuestionnaire"("PersID","qanswers");
  CALL "personrecordupdated"("PersID");
  SELECT '0:~Success'
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCandidateProfileSet" IS 
{CREATE PROCEDURE pears."NetOwnerCandidateProfileSet"( 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 "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 "qanswers" long VARCHAR DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "dateofbirth" DATE;
  DECLARE "bnamechanged" SMALLINT;
  DECLARE "ssql" CHAR(255);
  DECLARE "DivId" CHAR(20);
  DECLARE "PersID" CHAR(20);
  DECLARE @StaffID CHAR(20);
  DECLARE "PayrollCoID" CHAR(20);
  SET "bnamechanged" = 0;
  SET "ssql" = '';
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  IF "IQXNetHasPermission"("pWebUserID",'ADDCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "isnull"("pForenames",'') = '' OR "isnull"("pSurname",'') = '' THEN
    SELECT '100:~Please enter both a forename and a surname';
    RETURN
  END IF;
  IF((SELECT "count"() FROM "person" WHERE "person"."personid" = "ppersonid") = 0) THEN
    SET @StaffID = (SELECT FIRST "staffid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
    SET "PersID" = "uniquekey"("pForenames"+"pSurname");
    SET "PayrollCoID" = (SELECT "DefPayrollCoID" FROM "Division" WHERE "divisionid" = "DivID");
    INSERT INTO "person"( "personid","staffid","divisionid","name","keyname","status","forenames","surname","salutation","registrationdate","payrollnumber","PayrollIdentifier" ) VALUES( "PersID",@StaffID,"DivID","pForenames"+' '+"pSurname","ucase"("pSurname")+' '+"ucase"("pForenames"),'C',"pForenames","pSurname","pForenames","now"(),'NEW',"PayrollCoID" ) 
  ELSE
    SET "PersID" = "ppersonid";
    IF "isnull"("pforenames",'') <> "isnull"("poldforenames",'') THEN
      SET "ssql" = "ssql"+',forenames=pforenames';
      SET "ssql" = "ssql"+',salutation=getword(pforenames,1)';
      SET "bnamechanged" = 1
    END IF;
    IF "isnull"("psurname",'') <> "isnull"("poldsurname",'') THEN
      SET "ssql" = "ssql"+',surname=psurname';
      SET "bnamechanged" = 1
    END IF;
    IF "bnamechanged" = 1 THEN
      SET "ssql" = "ssql"+',name=string(getword(pforenames,1),'' '',psurname)';
      SET "ssql" = "ssql"+',keyname=makekeyname(string(psurname,'' '',pforenames))'
    END IF 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",'') THEN
    SET "ssql" = "ssql"+',county=pcounty'
  END IF;
  IF "isnull"("pcountry",'') <> "isnull"("poldcountry",'') THEN
    SET "ssql" = "ssql"+',country=pcountry'
  END IF;
  IF "isnull"("ppostcode",'') <> "isnull"("poldpostcode",'') THEN
    SET "ssql" = "ssql"+',postcode=ucase(ppostcode)'
  END IF;
  IF "ssql" <> '' THEN
    EXECUTE IMMEDIATE 'update person set '+"stuff"("ssql",1,1,'')+' where personid=PersID'
  END IF;
  IF "isnull"("phomephone",'') <> "isnull"("pOLDhomephone",'') THEN
    CALL "setphone"('P','Home Telephone',"PersID","phomephone")
  END IF;
  IF "isnull"("pdayphone",'') <> "isnull"("pOLDdayphone",'') THEN
    CALL "setphone"('P','Day Telephone',"PersID","pdayphone")
  END IF;
  IF "isnull"("pmobile",'') <> "isnull"("pOLDmobile",'') THEN
    CALL "setphone"('P','Mobile',"PersID","pmobile")
  END IF;
  IF "isnull"("pemail",'') <> "isnull"("pOLDemail",'') THEN
    CALL "setphone"('P','E-mail',"PersID","pemail");
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."personid" = "PersID"
  END IF;
  CALL "IQXNetSaveQuestionnaire"("PersID","qanswers");
  CALL "personrecordupdated"("PersID");
  SELECT '0:~Success'
END
}