====== pears.NetOwnerCandidateSubmit ======
<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"."NetOwnerCandidateSubmit"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pLineID" char(20),in "pIsShift" integer ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "sname" char(100);
  declare "RefCode" char(20);
  declare "VacID" char(20);
  declare "CreatePopup" char(1);
  declare "DefaultStatus" char(20);
  if "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set "sname" = (select "name" from "person" where "personid" = "pPersonID");
  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);
  if "pIsShift" = 1 then
    if(select "count"() from "tempshiftprogress" where "tempshiftplanid" = "pLineID" and "PersonID" = "pPersonID") = 0 then
      insert into "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status" ) values( "uniquekey"("ppersonid"+"plineid"),"plineid","ppersonid","userstaffid","isnull"("DefaultStatus",'A') ) ;
      set "RefCode" = (select "tsp"."ReferenceCode" from "TempShiftPlan" as "tsp" where "tsp"."TempShiftPlanID" = "pLineID");
      set "VacID" = (select "vacancyid" from "TempShiftPlan" where "TempShiftPlanID" = "pLineID")
    else
      select '1:~This person is already in the short list';
      return
    end if end if;
  if "pIsShift" = 0 then
    if(select "count"() from "progress" where "vacancyid" = "pLineID" and "PersonID" = "pPersonID") = 0 then
      insert into "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) values( "uniquekey"("ppersonid"+"plineid"),"pLineid","ppersonid","userstaffid","isnull"("DefaultStatus",'A'),current date ) ;
      set "RefCode" = (select "refcode" from "vacancy" where "vacancyid" = "pLineID");
      set "VacID" = "pLineID"
    else
      select '2:~This person is already in the short list';
      return
    end if end if;
  if "isnull"("RefCode",'') <> '' then
    set "RefCode" = '('+"RefCode"+')'
  end if;
  set "CreatePopup" = (select top 1 "isnull"("value",0) from "IQXNetSwitch" where "IQXNetSwitchID" = 'CANDSHORTLISTPOPUP' and "value" = 1 order by "IQXNetSwitchID" asc);
  if "CreatePopup" = 1 then
    call "IQXNetPopup"("pWebUserID","pPersonID",null,"VacID",null,null,'SUBMITCANDIDATE','Candidate submitted by client '+"RefCode","sname")
  end if;
  select '0:~Success'
end /* DOC
2017-01-05 PC receive suggested candidate for shortlist of vacancy
2018-01-23 PC IW-368 parameterise whether a popup notification should be used, and insert default status from lookup
2018-08-27 PC improve error handler

Tests:
all invalid
pWebUserID valid
all valid for vacancy but no permission
all valid for vacancy with permission
all valid for vacancy repeated
all valid for shift with permission
all valid for shift repeated

*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.owner','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
update tempshiftplan set referencecode='TestRefPlan' where tempshiftplanid='TEST';
delete from tempshiftprogress where tempshiftplanid='test';
delete from iqxnetrightallowed where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserclassid='OWNER';
delete from iqxnetrightassigned where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserid='test.owner';
select * from NetOwnerCandidateSubmit(null,null,null,null);
expect pResult=99*;
select * from NetOwnerCandidateSubmit('test.owner',null,null,null);
expect pResult=99*;
select * from NetOwnerCandidateSubmit('test.owner','TEST','TEST',0);
expect pResult=99*;
INSERT INTO iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,MaintainOnWeb) values ('SUBMITCANDIDATES','OWNER','*',1);
insert into iqxnetrightassigned (iqxnetuserid,iqxnetrightid,Assigned) values ('test.owner','SUBMITCANDIDATES',1);
INSERT INTO iqxnetswitch on existing update values ('CANDSHORTLISTPOPUP','Candidate list popul','1',null);
select * from NetOwnerCandidateSubmit('test.owner','TEST','TEST',0);
expect pResult=0*;
select * from progress where vacancyid='test';
expect personid=TEST;
select * from NetOwnerCandidateSubmit('test.owner','TEST','TEST',1);
expect pResult=0*;
select * from tempshiftprogress where tempshiftplanid='test';
expect personid=TEST;
select top 1 *,replace(description,' ','_') as B from diary order by whenentered desc;
expect staffid=TEST,personid=TEST,vacancyid=TEST,B=Candidate_submitted_by_client_(TestRefPlan),notes=test;
update tempshiftplan set referencecode=null where tempshiftplanid='TEST';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCandidateSubmit" IS 
{create PROCEDURE pears."NetOwnerCandidateSubmit"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pLineID" char(20),in "pIsShift" integer) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare sname char(100);
  declare RefCode char(20);
  declare VacID char(20);
  declare CreatePopup char(1);
  declare DefaultStatus char(20);

  if "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 then
    select '99:~Permission denied';
    return
  end if;
  set sname=(select name from person where personid = pPersonID);
  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);
  
  if pIsShift = 1 then
    if (select count(*) from tempshiftprogress where tempshiftplanid=pLineID and PersonID=pPersonID) = 0 then
        insert into "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status" ) values( "uniquekey"("ppersonid"+"plineid"),"plineid","ppersonid","userstaffid",isnull(DefaultStatus,'A')); 
        set RefCode = (select "tsp"."ReferenceCode" from "TempShiftPlan" as "tsp" where "tsp"."TempShiftPlanID" = "pLineID");
        set VacID = (select vacancyid from "TempShiftPlan" where "TempShiftPlanID" = "pLineID");
    else
        select '1:~This person is already in the short list';
        return;
    end if;
  end if;
  if pIsShift = 0 then
    if (select count(*) from progress where vacancyid=pLineID and PersonID=pPersonID) = 0 then
        insert into "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) values( "uniquekey"("ppersonid"+"plineid"),"pLineid","ppersonid","userstaffid",isnull(DefaultStatus,'A'),current date ); 
        set RefCode = (select refcode from vacancy where vacancyid = pLineID);
        set VacID = pLineID;
    else
      select '2:~This person is already in the short list';
      return;
    end if;
  end if;
  
  if "isnull"(RefCode,'') <> '' then 
    set RefCode = '('+RefCode+')'
  end if;
  set CreatePopup=(select top 1 isnull(value,0) from IQXNetSwitch where IQXNetSwitchID='CANDSHORTLISTPOPUP' and value=1 order by IQXNetSwitchID asc);
  if CreatePopup=1 then
    call "IQXNetPopup"("pWebUserID","pPersonID",null,"VacID",null,null,'SUBMITCANDIDATE','Candidate submitted by client '+RefCode,sname);
  end if;
  select '0:~Success';
end

/* DOC
2017-01-05 PC receive suggested candidate for shortlist of vacancy
2018-01-23 PC IW-368 parameterise whether a popup notification should be used, and insert default status from lookup
2018-08-27 PC improve error handler

Tests:
    all invalid
    pWebUserID valid
    all valid for vacancy but no permission
    all valid for vacancy with permission
    all valid for vacancy repeated
    all valid for shift with permission
    all valid for shift repeated

*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.owner','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
update tempshiftplan set referencecode='TestRefPlan' where tempshiftplanid='TEST';
delete from tempshiftprogress where tempshiftplanid='test';
delete from iqxnetrightallowed where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserclassid='OWNER';
delete from iqxnetrightassigned where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserid='test.owner';
select * from NetOwnerCandidateSubmit(null,null,null,null);
expect pResult=99*;
select * from NetOwnerCandidateSubmit('test.owner',null,null,null);
expect pResult=99*;
select * from NetOwnerCandidateSubmit('test.owner','TEST','TEST',0);
expect pResult=99*;
INSERT INTO iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,MaintainOnWeb) values ('SUBMITCANDIDATES','OWNER','*',1);
insert into iqxnetrightassigned (iqxnetuserid,iqxnetrightid,Assigned) values ('test.owner','SUBMITCANDIDATES',1);
INSERT INTO iqxnetswitch on existing update values ('CANDSHORTLISTPOPUP','Candidate list popul','1',null);
select * from NetOwnerCandidateSubmit('test.owner','TEST','TEST',0);
expect pResult=0*;
select * from progress where vacancyid='test';
expect personid=TEST;
select * from NetOwnerCandidateSubmit('test.owner','TEST','TEST',1);
expect pResult=0*;
select * from tempshiftprogress where tempshiftplanid='test';
expect personid=TEST;
select top 1 *,replace(description,' ','_') as B from diary order by whenentered desc;
expect staffid=TEST,personid=TEST,vacancyid=TEST,B=Candidate_submitted_by_client_(TestRefPlan),notes=test;
update tempshiftplan set referencecode=null where tempshiftplanid='TEST';
*/
}
</code>