pears.NetClientVacancySuggestCandidate

CREATE PROCEDURE "pears"."NetClientVacancySuggestCandidate"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pSlice" INTEGER DEFAULT 0,IN "pSliceSize" INTEGER DEFAULT 10,IN "pSearchString" CHAR(50) ) 
RESULT( "pPersonID" CHAR(20),"pName" CHAR(60),"pKeyName" CHAR(60) ) 
// IQXNet
BEGIN
  DECLARE LOCAL TEMPORARY TABLE "PersonAlreadyWorking"(
    "PersonID" CHAR(20) NULL,
    ) NOT transactional;
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  DECLARE "CompID" CHAR(20);
  DECLARE "VacID" CHAR(20);
  SET "x" = "pSliceSize";
  SET "y" = "x"*"pSlice"+1;
  SET "CompID" = "isnull"((SELECT "companyid" FROM "employment" KEY JOIN "vacancy" WHERE "vacancyid" = "pVacancyID"),(SELECT "companyid" FROM "employment" KEY JOIN "vacancy" KEY JOIN "tempshiftplan" WHERE "tempshiftplanid" = "pVacancyID"));
  INSERT INTO "PersonAlreadyWorking"( "PersonID" ) (SELECT DISTINCT
      "ts"."personid"
      FROM "tempshiftplan" AS "tsp"
        JOIN "tempshift" AS "ts" ON "ts"."shiftdate" = "tsp"."shiftdate" AND "ts"."state" <> 'A' AND NOT(("isnull"("ts"."timeto",'23:59:00') <= "tsp"."timefrom") OR("isnull"("ts"."timefrom",'00:00:00') >= "tsp"."timeto"))
        JOIN "person" AS "p"
      WHERE "ts"."vacancyid" = "pVacancyID"
      AND "p"."status" IN( 'C' ) UNION
    SELECT DISTINCT
      "e"."personid"
      FROM "placement" AS "pl"
        KEY JOIN "employment" AS "e"
        KEY JOIN "person" AS "p"
      WHERE "pl"."vacancyid" = "pVacancyID"
      AND "p"."status" IN( 'C' ) );
  SELECT DISTINCT top "x" START at "y"
    "p"."personid","name","p"."keyname"
    FROM "person" AS "p" JOIN "PersonAlreadyWorking" AS "paw" ON "paw"."PersonID" = "p"."personid"
    WHERE "keyname" LIKE '%'+"isnull"("pSearchString",'')+'%'
    AND NOT EXISTS(SELECT * FROM "progress" WHERE "vacancyid" = "pvacancyid" AND "progress"."personid" = "p"."personid")
    ORDER BY "p"."keyname" ASC
END /* DOC
2016-12-28 PC provide paged list of candidates who would be available to work in a particular vacancy
2017-04-20 PC altered to ignore 'Available' shifts
2017-08-01 PC enforce search order and exclude placements
2019-04-13 JB Look for anyone who has worked (shift or placement) and exclude those on he short list
 
Tests:
unable to test invalid slice or sliceSize
invalid pWebUserID
valid pWebUserID, invalid pVacancyID, invalid status
valid pWebUserID, pVacancyID, pSlice, pSliceSize, invalid pSearchString
valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString
valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for today and time wholly before template times
valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for today and time wholly overlapping template times
valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for today and time wholly after template times
valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for tomorrow
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempshiftplan set shiftdate=today(*) where tempshiftplanid = 'TEST';
update tempshift set personid=null,shiftdate=today(*) where vacancyid='TEST';
update person set status='~',keyname='aaatest' where personid='TEST';
delete from progress where vacancyid='test' and personid='test';
select * from NetClientVacancySuggestCandidate(null,null,0,1,null) where pPersonID='TEST';
expect EOF;
update person set status='C' where personid='TEST';
update tempshift set personid='test' where vacancyid='TEST';
select pPersonID from NetClientVacancySuggestCandidate('test.client','TEST',0,1,null) where pPersonID='TEST';
expect pPersonID=TEST;
update tempshiftplan set timefrom='10:00',timeto='12:00' where tempshiftplanid='TEST';
update tempshift set timefrom='07:00',timeto='09:00' where tempshiftid = 'TEST';
select * from NetClientVacancySuggestCandidate('test.client','TEST',0,1,'test');
expect pPersonID=TEST;
 
update tempshift set timefrom='14:00',timeto='15:00' where vacancyid = 'TEST';
select * from NetClientVacancySuggestCandidate('test.client','TEST',0,1,'test');
expect pPersonID=TEST;
update tempshiftplan set shiftdate=dateadd(day,1,today(*)) where vacancyid = 'TEST';
select * from NetClientVacancySuggestCandidate('test.client','TEST',0,1,'test');
expect pPersonID=TEST;
update person set status=null,keyname='test' where personid='TEST';
update tempshiftplan set shiftdate=null where tempshiftplanid = 'TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancySuggestCandidate" IS 
{CREATE PROCEDURE pears."NetClientVacancySuggestCandidate"(IN pWebUserID CHAR(20), IN pVacancyID CHAR(20), IN pSlice INTEGER DEFAULT 0, IN pSliceSize INTEGER DEFAULT 10, IN pSearchString CHAR(50))
RESULT(pPersonID CHAR(20), pName CHAR(60),pKeyName CHAR(60))
// IQXNet
BEGIN
 DECLARE LOCAL TEMPORARY TABLE PersonAlreadyWorking(
    PersonID CHAR(20) NULL,
    ) NOT transactional;
    DECLARE x INTEGER;
    DECLARE y INTEGER;
    DECLARE CompID CHAR(20);
    DECLARE VacID CHAR(20);
 
    SET x=pSliceSize;
    SET y=x*pSlice+1;
    SET CompID = isnull((SELECT companyid FROM employment KEY JOIN vacancy WHERE vacancyid=pVacancyID),(SELECT companyid FROM employment KEY JOIN vacancy KEY JOIN tempshiftplan WHERE tempshiftplanid = pVacancyID ));
    INSERT INTO PersonAlreadyWorking (PersonID) (SELECT DISTINCT
        ts.personid   
    FROM 
        tempshiftplan tsp
        JOIN tempshift ts ON ts.shiftdate = tsp.shiftdate AND ts.state != 'A' AND NOT ((isnull(ts.timeto,'23:59:00') <= tsp.timefrom) OR (isnull(ts.timefrom,'00:00:00') >= tsp.timeto))
        JOIN person p
        WHERE ts.vacancyid = pVacancyID
        AND p.status IN ('C')
 
    UNION SELECT DISTINCT
        e.personid   
    FROM 
        placement pl
        KEY JOIN employment e 
        KEY JOIN person p
        WHERE pl.vacancyid = pVacancyID
        AND p.status IN ('C')
        );
    SELECT DISTINCT top x START at y 
        p.personid,name,p.keyname 
    FROM
        person p JOIN PersonAlreadyWorking paw ON paw.PersonID=p.personid
    WHERE 
        keyname LIKE '%'+isnull(pSearchString,'') + '%'
        AND NOT EXISTS(SELECT * FROM progress WHERE vacancyid = pvacancyid AND progress.personid = p.personid)
    ORDER BY 
        p.keyname ASC;
END
 
/* DOC
2016-12-28 PC provide paged list of candidates who would be available to work in a particular vacancy
2017-04-20 PC altered to ignore 'Available' shifts
2017-08-01 PC enforce search order and exclude placements
2019-04-13 JB Look for anyone who has worked (shift or placement) and exclude those on he short list
 
Tests:
    unable to test invalid slice or sliceSize
    invalid pWebUserID
    valid pWebUserID, invalid pVacancyID, invalid status
    valid pWebUserID, pVacancyID, pSlice, pSliceSize, invalid pSearchString
    valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString
    valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for today and time wholly before template times
    valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for today and time wholly overlapping template times
    valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for today and time wholly after template times
    valid pWebUserID, pVacancyID, pSlice, pSliceSize, pSearchString with shift for tomorrow
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempshiftplan set shiftdate=today(*) where tempshiftplanid = 'TEST';
update tempshift set personid=null,shiftdate=today(*) where vacancyid='TEST';
update person set status='~',keyname='aaatest' where personid='TEST';
delete from progress where vacancyid='test' and personid='test';
select * from NetClientVacancySuggestCandidate(null,null,0,1,null) where pPersonID='TEST';
expect EOF;
update person set status='C' where personid='TEST';
update tempshift set personid='test' where vacancyid='TEST';
select pPersonID from NetClientVacancySuggestCandidate('test.client','TEST',0,1,null) where pPersonID='TEST';
expect pPersonID=TEST;
update tempshiftplan set timefrom='10:00',timeto='12:00' where tempshiftplanid='TEST';
update tempshift set timefrom='07:00',timeto='09:00' where tempshiftid = 'TEST';
select * from NetClientVacancySuggestCandidate('test.client','TEST',0,1,'test');
expect pPersonID=TEST;
 
update tempshift set timefrom='14:00',timeto='15:00' where vacancyid = 'TEST';
select * from NetClientVacancySuggestCandidate('test.client','TEST',0,1,'test');
expect pPersonID=TEST;
update tempshiftplan set shiftdate=dateadd(day,1,today(*)) where vacancyid = 'TEST';
select * from NetClientVacancySuggestCandidate('test.client','TEST',0,1,'test');
expect pPersonID=TEST;
update person set status=null,keyname='test' where personid='TEST';
update tempshiftplan set shiftdate=null where tempshiftplanid = 'TEST';
*/
}