====== pears.NetCandidateAddToShortlist ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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';
*/
}
</code>