pears.NetAgencyCurrentRequirementCandidates

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';
 
*/
}