pears.NetCandidateAddToShortlist

CREATE PROCEDURE "pears"."NetCandidateAddToShortlist"( IN "pWebUserID" CHAR(20),IN "pRefCode" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL,IN "pNotes" long VARCHAR DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "pvacancyid" CHAR(20);
  DECLARE "pProgressID" CHAR(20);
  DECLARE "pUserStaffID" CHAR(20);
  DECLARE "DefaultStatus" CHAR(20);
  DECLARE "CreatePopup" CHAR(1);
  IF "pRefCode" IS NULL THEN
    SELECT '132:~RefCode required';
    RETURN
  END IF;
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '97:~There is an error with the linking on your profile';
    RETURN
  END IF;
  IF EXISTS(SELECT * FROM "progress" WHERE "personid" = "pPersonID" AND "vacancyid" = "pRefCode") THEN
    SELECT '135:~You have already applied for this job';
    RETURN
  END IF;
  SET "pUserStaffID" = (SELECT "staffid" FROM "vacancy" WHERE "vacancyid" = "pRefCode");
  SET "DefaultStatus" = (SELECT top 1 "trim"("right"("description","length"("description")-"charindex"(':',"description"))) AS "DefaultState" FROM "IQXNetSwitch" WHERE "IQXNetSwitchID" = 'DEFAPPLYVACSTATE' AND "value" = 1 ORDER BY "IQXNetSwitchID" ASC);
  SET "CreatePopup" = (SELECT top 1 "isnull"("value",0) FROM "IQXNetSwitch" WHERE "IQXNetSwitchID" = 'CANDSHORTLISTPOPUP' AND "value" = 1 ORDER BY "IQXNetSwitchID" ASC);
  SET "pProgressID" = (SELECT "uniquekey"('X'));
  INSERT INTO "progress"( "progressid","vacancyid","actiondate","status","personid","staffId","note" ) VALUES( "pProgressID","pRefCode",CURRENT DATE,"isnull"("DefaultStatus",'H'),"pPersonID","pUserStaffID","pNotes" ) ;
  IF "CreatePopup" = 1 THEN
    CALL "IQXNetPopup"("pWebUserID","pPersonID",NULL,"pRefCode",NULL,"pProgressID",'JOBAPPLICATION','Web vacancy application',(SELECT "name" FROM "person" WHERE "personid" = "pPersonID"))
  END IF;
  SELECT '0:~Success'
END /* DOC
2016-11-30 PC Test and doc
2017-12-13 PC modify to allow default state to be drawn from IQXWeb Switches DEFAPPLYVACSTATE
2018-01-23 PC IW-368 allow or suppress popup
2018-06-14 PC modify for extended error handler
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2019-03-05 PC IW-873 revised
2020-01-17 AW IW-12 added notes
 
Inputs:
pWebUserID
pRefCode
 
Outputs:
pResult
 
Tests:
invalid pWebUserID
Valid pWebUser=Candidate, invalid pRefCode
Valid pWebUser=Candidate, pRefCode
Valid pWebUser=client, pRefCode
 
*/
/* TEST
call NetTestSetup('');
update person set staffid='TEST' where personid='test';
update vacancy set staffid='TEST' where vacancyid='TEST';
delete from progress where personid='test';
select * from NetCandidateAddToShortlist(null,null);
expect pResult=132:~RefCode*;
select * from NetCandidateAddToShortlist('test.candidate',null);
expect pResult=132:~RefCode*;
select * from NetCandidateAddToShortlist('test.candidate','TEST');
expect pResult=0*;
select * from progress where personid='TEST';
expect vacancyid=TEST,status=A;  
select * from NetCandidateAddToShortlist('test.candidate','TEST');
expect pResult=135:~*;
select * from NetCandidateAddToShortlist('test.client','TEST');
expect pResult=97:~There*;
delete from progress where personid='test';
update person set staffid=null where personid='test';
update vacancy set staffid=null where vacancyid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateAddToShortlist" IS 
{CREATE PROCEDURE pears."NetCandidateAddToShortlist"(IN pWebUserID CHAR(20), IN pRefCode CHAR(20), IN pPersonID CHAR(20) DEFAULT NULL, IN pNotes long VARCHAR DEFAULT NULL) 
RESULT( pResult CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE pvacancyid CHAR(20);
  DECLARE pProgressID CHAR(20);
  DECLARE pUserStaffID CHAR(20);
  DECLARE DefaultStatus CHAR(20);
  DECLARE CreatePopup CHAR(1);
 
  IF pRefCode IS NULL THEN
    SELECT '132:~RefCode required';
    RETURN;
  END IF;
 
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  IF pPersonID IS NULL THEN
    SELECT '97:~There is an error with the linking on your profile';
    RETURN;
  END IF;
 
  IF EXISTS(SELECT * FROM progress WHERE personid=pPersonID AND vacancyid=pRefCode) THEN
     SELECT '135:~You have already applied for this job';
     RETURN;
  END IF;
 
  SET pUserStaffID=(SELECT staffid FROM vacancy WHERE vacancyid=pRefCode);
 
  SET DefaultStatus=(SELECT top 1 TRIM(RIGHT(description,LENGTH(description)-charindex(':',description))) AS DefaultState FROM IQXNetSwitch WHERE IQXNetSwitchID='DEFAPPLYVACSTATE' AND VALUE=1 ORDER BY IQXNetSwitchID ASC);
  SET CreatePopup=(SELECT top 1 isnull(VALUE,0) FROM IQXNetSwitch WHERE IQXNetSwitchID='CANDSHORTLISTPOPUP' AND VALUE=1 ORDER BY IQXNetSwitchID ASC);
 
  SET pProgressID=(SELECT uniquekey('X'));
  INSERT INTO progress( progressid,vacancyid,actiondate,STATUS,personid,staffId, note ) VALUES( pProgressID,pRefCode,CURRENT DATE,isnull(DefaultStatus,'H'),pPersonID,pUserStaffID,pNotes ) ;
  IF CreatePopup = 1 THEN
    CALL IQXNetPopup(pWebUserID,pPersonID,NULL,pRefCode,NULL,pProgressID,'JOBAPPLICATION', 'Web vacancy application',(SELECT name FROM person WHERE personid=pPersonID));
  END IF;
 
  SELECT '0:~Success';
END
 
/* DOC
2016-11-30 PC Test and doc
2017-12-13 PC modify to allow default state to be drawn from IQXWeb Switches DEFAPPLYVACSTATE
2018-01-23 PC IW-368 allow or suppress popup
2018-06-14 PC modify for extended error handler
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2019-03-05 PC IW-873 revised
2020-01-17 AW IW-12 added notes
 
Inputs:
    pWebUserID
    pRefCode
 
Outputs:
    pResult
 
Tests:
    invalid pWebUserID
    Valid pWebUser=Candidate, invalid pRefCode
    Valid pWebUser=Candidate, pRefCode
    Valid pWebUser=client, pRefCode
 
*/
 
/* TEST
call NetTestSetup('');
update person set staffid='TEST' where personid='test';
update vacancy set staffid='TEST' where vacancyid='TEST';
delete from progress where personid='test';
select * from NetCandidateAddToShortlist(null,null);
expect pResult=132:~RefCode*;
select * from NetCandidateAddToShortlist('test.candidate',null);
expect pResult=132:~RefCode*;
select * from NetCandidateAddToShortlist('test.candidate','TEST');
expect pResult=0*;
select * from progress where personid='TEST';
expect vacancyid=TEST,status=A;  
select * from NetCandidateAddToShortlist('test.candidate','TEST');
expect pResult=135:~*;
select * from NetCandidateAddToShortlist('test.client','TEST');
expect pResult=97:~There*;
delete from progress where personid='test';
update person set staffid=null where personid='test';
update vacancy set staffid=null where vacancyid='TEST';
*/
}