pears.NetCandidateCreateRegistration

CREATE PROCEDURE "pears"."NetCandidateCreateRegistration"( IN "pWebUserID" CHAR(20),IN "pForenames" CHAR(60),IN "pSurname" CHAR(60),IN "pAddr1" CHAR(60),IN "pAddr2" CHAR(60) DEFAULT NULL,IN "pAddr3" CHAR(60) DEFAULT NULL,IN "pTown" CHAR(60) DEFAULT NULL,IN "pCounty" CHAR(60) DEFAULT NULL,IN "pCountry" CHAR(60) DEFAULT NULL,IN "pPostcode" CHAR(60),IN "pDOB" CHAR(60),IN "pEmail" CHAR(60),IN "pUsername" CHAR(60),IN "pPassword" CHAR(60),IN "pRepeatpassword" CHAR(60),IN "pDepartment" CHAR(5),IN "pDivision" CHAR(20),IN "pDeptQs" CHAR(2048),IN "pPersonQs" CHAR(2048) ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "dateofbirth" DATE;
  DECLARE "ppersonid" CHAR(20);
  DECLARE "pfirstname" CHAR(50);
  DECLARE "pnewappnumber" INTEGER;
  DECLARE "pnewstatus" CHAR(1);
  DECLARE "pnewwebuserid" CHAR(20);
  DECLARE "chained" CHAR(3);
  DECLARE "pKey" CHAR(255);
  SET "pKey" = 'TheCro55Key5-L111e51eaf';
  SET "dateofbirth" = "IQXNetStringToDate"("pdob");
  IF "trim"("isnull"("pdob",'')) <> '' AND "dateofbirth" IS NULL THEN
    SELECT '1:~Invalid date of birth';
    RETURN
  END IF;
  IF "dateofbirth" IS NOT NULL AND "ppostcode" IS NOT NULL THEN
    IF EXISTS(SELECT * FROM "person" WHERE "postcode" = "ppostcode" AND "dob" = "dateofbirth" AND "similar"("string"("pforenames",' ',"psurname"),"string"("forenames",' ',"surname")) >= 90) THEN
      SELECT '2:~You may already be registered - please contact the agency';
      RETURN
    END IF END IF;
  SET "pnewwebuserid" = "uniquekey"('');
  SET "chained" = (SELECT "connection_property"('chained'));
  SET "ppersonid" = "uniquekey"('');
  SET "pfirstname" = "getword"("pforenames",1);
  IF "chained" = 'On' THEN
    BEGIN atomic
      UPDATE "params" SET "nextappnumber" = "isnull"("nextappnumber",0)+1 WHERE "autoappnumber" = 1;
      IF @@rowcount > 0 THEN
        SET "pnewappnumber" = (SELECT top 1 "nextappnumber" FROM "params" ORDER BY "dbid" ASC)
      ELSE
        SET "pnewappnumber" = NULL
      END IF
    END
  ELSE UPDATE "params" SET "nextappnumber" = "isnull"("nextappnumber",0)+1 WHERE "autoappnumber" = 1;
    IF @@rowcount > 0 THEN
      SET "pnewappnumber" = (SELECT top 1 "nextappnumber" FROM "params" ORDER BY "dbid" ASC)
    ELSE
      SET "pnewappnumber" = NULL
    END IF END IF;
  SET "pnewstatus" = (SELECT top 1 "initialapplicantstatus" FROM "params" ORDER BY "dbid" ASC);
  SET "pnewstatus" = "isnull"("pnewstatus",'C');
  INSERT INTO "person"( "personid","forenames","surname","name","keyname","salutation","dob","addr1","addr2","addr3","town","county","country","postcode","appnumber","status","registrationdate","divisionid" ) VALUES
    ( "ppersonid","pforenames","psurname","string"("pfirstname",' ',"psurname"),"makekeyname"("string"("psurname",' ',"pforenames")),
    "pfirstname","dateofbirth","paddr1","paddr2","paddr3","ptown","pcounty","pcountry","ucase"("ppostcode"),"pnewappnumber","pnewstatus",CURRENT DATE,"pDivision" ) ;
  CALL "personrecordupdated"("ppersonid");
  INSERT INTO "iqxnetuser"( "iqxnetuserid","iqxnetuserclassid","name","loginid","password","emailaddress","passwordset" ) VALUES( "pnewwebuserid",'CANDIDATE',"string"("pfirstname",' ',"psurname"),"pEmail","encrypt"("pPassword","pKey"),"pEmail",1 ) ;
  INSERT INTO "iqxnetuserlink"( "iqxnetuserlinkid","iqxnetuserid","personid" ) VALUES( "ppersonid","pnewwebuserid","ppersonid" ) ;
  IF "pemail" IS NOT NULL THEN
    CALL "setphone"('P','E-mail',"ppersonid","pemail")
  END IF;
  CALL "IQXNetSaveQuestionnaire"("ppersonid","pPersonQs");
  CALL "IQXNetSaveQuestionnaire"("ppersonid","pDeptQs");
  INSERT INTO "search"( "searchid","temp","permanent","personid","departmentid" ) VALUES( "uniquekey"(''),1,0,"ppersonid","pdepartment" ) ;
  INSERT INTO "IQXNetAction"( "IQXNetActionID","IQXNetUserID","ActionType","TargetType","ActionState","WhenRequested","TargetID","ActionAddress" ) VALUES( "uniquekey"(''),"pnewwebuserid",'NotifyWebLoginCreated','P',0,CURRENT TIMESTAMP,"ppersonid","pEmail" ) ;
  SELECT '0:~Success'
END /* DOC 
20170823 PC create candidate registration custom
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateCreateRegistration" IS 
{CREATE PROCEDURE pears."NetCandidateCreateRegistration"(IN pWebUserID CHAR(20),IN pForenames CHAR(60),IN pSurname CHAR(60),IN pAddr1 CHAR(60),IN pAddr2 CHAR(60) DEFAULT NULL,IN pAddr3 CHAR(60) DEFAULT NULL,IN pTown CHAR(60) DEFAULT NULL,IN pCounty CHAR(60) DEFAULT NULL,IN pCountry CHAR(60) DEFAULT NULL,IN pPostcode CHAR(60),IN pDOB CHAR(60),IN pEmail CHAR(60),IN pUsername CHAR(60),IN pPassword CHAR(60),IN pRepeatpassword CHAR(60),IN pDepartment CHAR(5),IN pDivision CHAR(20),IN pDeptQs CHAR(2048),IN pPersonQs CHAR(2048))
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE dateofbirth DATE;
  DECLARE "ppersonid" CHAR(20);
  DECLARE "pfirstname" CHAR(50);
  DECLARE "pnewappnumber" INTEGER;
  DECLARE "pnewstatus" CHAR(1);
  DECLARE "pnewwebuserid" CHAR(20);
  DECLARE "chained" CHAR(3);
  DECLARE pKey CHAR(255);
 
 SET pKey = 'TheCro55Key5-L111e51eaf';
  SET dateofbirth=IQXNetStringToDate(pdob);
  IF TRIM(isnull(pdob,'')) <> '' AND dateofbirth IS NULL THEN
    SELECT '1:~Invalid date of birth';
    RETURN
  END IF;
  IF dateofbirth IS NOT NULL AND ppostcode IS NOT NULL THEN
    IF EXISTS(SELECT * FROM person WHERE postcode = ppostcode AND dob = dateofbirth AND SIMILAR(string(pforenames,' ',psurname),string(forenames,' ',surname)) >= 90) THEN
      SELECT '2:~You may already be registered - please contact the agency';
      RETURN
    END IF
  END IF;
  SET pnewwebuserid =  "uniquekey"('');
  SET "chained" = (SELECT "connection_property"('chained'));
  SET "ppersonid" = "uniquekey"('');
  SET "pfirstname" = "getword"("pforenames",1);
  IF "chained" = 'On' THEN
    BEGIN atomic
      UPDATE "params" SET "nextappnumber" = "isnull"("nextappnumber",0)+1 WHERE "autoappnumber" = 1;
      IF @@rowcount > 0 THEN
        SET "pnewappnumber" = (SELECT top 1 "nextappnumber" FROM "params" ORDER BY dbid)
      ELSE
        SET "pnewappnumber" = NULL
      END IF
    END
  ELSE UPDATE "params" SET "nextappnumber" = "isnull"("nextappnumber",0)+1 WHERE "autoappnumber" = 1;
    IF @@rowcount > 0 THEN
      SET "pnewappnumber" = (SELECT top 1 "nextappnumber" FROM "params" ORDER BY dbid)
    ELSE
      SET "pnewappnumber" = NULL
    END IF END IF;
  SET "pnewstatus" = (SELECT top 1 "initialapplicantstatus" FROM "params" ORDER BY dbid);
  SET "pnewstatus" = "isnull"("pnewstatus",'C');
  INSERT INTO "person"( "personid","forenames","surname","name","keyname","salutation","dob","addr1","addr2","addr3","town","county","country","postcode","appnumber","status","registrationdate","divisionid" ) VALUES
    ( "ppersonid","pforenames","psurname","string"("pfirstname",' ',"psurname"),"makekeyname"("string"("psurname",' ',"pforenames")),
    "pfirstname","dateofbirth","paddr1","paddr2","paddr3","ptown","pcounty","pcountry","ucase"("ppostcode"),"pnewappnumber","pnewstatus",CURRENT DATE,pDivision ) ;
  CALL "personrecordupdated"("ppersonid");
 
 INSERT INTO iqxnetuser (iqxnetuserid,iqxnetuserclassid,name,loginid,password,emailaddress,passwordset) VALUES (pnewwebuserid,'CANDIDATE',"string"("pfirstname",' ',"psurname"),pEmail,encrypt(pPassword,pKey),pEmail,1);
  INSERT INTO "iqxnetuserlink"( "iqxnetuserlinkid","iqxnetuserid","personid" ) VALUES( "ppersonid","pnewwebuserid","ppersonid" ) ;
 
 IF "pemail" IS NOT NULL THEN
    CALL "setphone"('P','E-mail',"ppersonid","pemail")
  END IF;
  CALL "IQXNetSaveQuestionnaire"("ppersonid","pPersonQs");
  CALL "IQXNetSaveQuestionnaire"("ppersonid","pDeptQs");
  INSERT INTO SEARCH (searchid,temp,permanent,personid,departmentid) VALUES ("uniquekey"(''),1,0,ppersonid,pdepartment);
  INSERT INTO IQXNetAction (IQXNetActionID,IQXNetUserID,ActionType,TargetType,ActionState,WhenRequested,TargetID,ActionAddress) VALUES (uniquekey(''),pnewwebuserid,'NotifyWebLoginCreated','P',0,CURRENT TIMESTAMP,ppersonid,pEmail);
  SELECT '0:~Success'
 
END
 
/* DOC 
20170823 PC create candidate registration custom
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
*/
}