====== pears.NetAgencyCurrentRequirementCandidates ======
<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"."NetAgencyCurrentRequirementCandidates"( in "pWebUserID" char(20),in "pLineID" char(22) ) 
result( "PersonID" char(20),"CandidateName" char(100),"CandidateReference" char(100),"ProgressID" char(20),"AlreadySubmitted" char(5),"SkillsMatch" char(30),"Keyname" char(50),"AlreadyWorking" integer ) 
// IQXNet
begin
  declare "SecAgID" char(20);
  declare "ProgID" char(20);
  declare "LineID" char(20);
  declare "LineType" char(1);
  declare "VacID" char(20);
  declare "pShiftDate" char(15);
  set "SecAgID" = (select top 1 "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" order by "isnull"("employment"."leavedate",current date) asc);
  set "LineType" = "left"("pLineID",1);
  set "LineID" = "right"("pLineID",20);
  set "VacID" = "isnull"((select "vacancyid" from "TempShiftPlan" as "pl" where "tempshiftplanid" = "LineID"),"LineID");
  set "pShiftDate" = (select "shiftdate" from "TempShiftPlan" as "pl" where "tempshiftplanid" = "LineID");
  select "p"."personid",
    "p"."name",
    "pe"."secondaryagencyref",
    (select top 1 "progressid" from "progress" where "personid" = "p"."personid" and "vacancyid" = "VacID" order by(select "sortorder" from "status" where "status"."status" = "progress"."status" and "status"."type" = 'R') desc) as "ProgID",
    "ifnull"("ProgID",0,1) as "alreadysubmitted",
    (select "IsCand" from "NetDoSkillsMatch"("pWebUserID","VacID","p"."personid")) as "SkillsMatch",
    "p"."keyname",
    (select "count"() from "tempshift" where "personid" = "p"."personid" and "shiftdate" = "pShiftDate" and "state" <> 'A')+(select "count"() from "employment" where "personid" = "p"."personid" and "pShiftDate" between "startdate" and "leavedate" and "concurrent" = 0) as "AlreadyWorking"
    from "person" as "p"
      key join "pay_employee" as "pe"
    where "pe"."secondaryagencyid" = "SecAgId"
    and "SkillsMatch" = 1
    and "alreadysubmitted" = 0
    and "AlreadyWorking" = 0
    order by "p"."keyname" asc
end /* DOC
2016-11-28 PC Doc and test
2017-01-19 PC modify to include only candidates who have worked for the secondary agency
2017-04-05 PC modify to include perm posts
2018-04-26 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries

Tests:
all valid but no secondary agency
all valid vacancy but no pay_employee
all valid vacancy and pay_employee

*/
/* TEST
call NetTestSetup('');
delete from pay_employee where personid='TEST';
delete from tempshift where tempshiftid='TEST';
delete from criterion where id='TESTTESTTESTTESTTEST';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='TEST.agency';
select * from NetAgencyCurrentRequirementCandidates('test.agency','VTEST');
expect EOF;
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='TEST.agency';
select replace(CandidateName,' ','!') as A,AlreadySubmitted from NetAgencyCurrentRequirementCandidates('test.agency','VTESTTESTTESTTESTTEST');
expect EOF;
delete from pay_employee where personid='TEST';
delete from tempshift where tempshiftid='TEST';
delete from criterion where id='TESTTESTTESTTESTTEST';

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCurrentRequirementCandidates" IS 
{create PROCEDURE pears."NetAgencyCurrentRequirementCandidates"( in "pWebUserID" char(20),in "pLineID" char(22)) 
result( "PersonID" char(20),"CandidateName" char(100),"CandidateReference" char(100),"ProgressID" char(20),"AlreadySubmitted" char(5),SkillsMatch char(30),Keyname char(50),AlreadyWorking integer ) 
// IQXNet
begin
  declare SecAgID char(20);
  declare ProgID char(20);
  declare LineID char(20);
  declare LineType char(1);
  declare VacID char(20);
  declare pShiftDate char(15);
  set SecAgID = (select top 1 "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" order by isnull(employment.leavedate,current date));
  set LineType=left(pLineID,1);
  set LineID = right(pLineID,20);
  set VacID = isnull((select vacancyid from TempShiftPlan pl where tempshiftplanid = LineID),LineID);
  set pShiftDate = (select shiftdate from TempShiftPlan pl where tempshiftplanid = LineID);
  
  select p.personid,
    p.name,
    pe.secondaryagencyref,
    (select top 1 progressid  from progress where personid="p"."personid" and vacancyid=VacID order by (select sortorder from status where status.status=progress.status and status.type='R') desc) as ProgID,
    ifnull(ProgID,0,1) as "alreadysubmitted",
    (select IsCand from NetDoSkillsMatch(pWebUserID,VacID,p.personid)) as SkillsMatch,
    p.keyname,
    (select count(*) from tempshift where personid=p.personid and shiftdate=pShiftDate and state !='A') + (select count(*) from employment where personid=p.personid and pShiftDate between startdate and leavedate and concurrent=0)as AlreadyWorking
  from person p
    key join pay_employee pe
  where 
    pe.secondaryagencyid = SecAgId
    and SkillsMatch=1
    and alreadysubmitted = 0
    and AlreadyWorking = 0
  order by
    p.keyname asc
end

/* DOC
2016-11-28 PC Doc and test
2017-01-19 PC modify to include only candidates who have worked for the secondary agency
2017-04-05 PC modify to include perm posts
2018-04-26 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries

Tests:
    all valid but no secondary agency
    all valid vacancy but no pay_employee
    all valid vacancy and pay_employee

*/

/* TEST
call NetTestSetup('');
delete from pay_employee where personid='TEST';
delete from tempshift where tempshiftid='TEST';
delete from criterion where id='TESTTESTTESTTESTTEST';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='TEST.agency';
select * from NetAgencyCurrentRequirementCandidates('test.agency','VTEST');
expect EOF;
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='TEST.agency';
select replace(CandidateName,' ','!') as A,AlreadySubmitted from NetAgencyCurrentRequirementCandidates('test.agency','VTESTTESTTESTTESTTEST');
expect EOF;
delete from pay_employee where personid='TEST';
delete from tempshift where tempshiftid='TEST';
delete from criterion where id='TESTTESTTESTTESTTEST';

*/
}
</code>