pears.NetClientCandidatesPossibleForShift

CREATE PROCEDURE "pears"."NetClientCandidatesPossibleForShift"( IN "pWebUserID" CHAR(20),IN "pTempShiftID" CHAR(20) ) 
RESULT( "PersonName" CHAR(60),"PersonID" CHAR(20),"PersonStatus" CHAR(20),"StartDate" CHAR(20),"LeaveDate" CHAR(20),"CurrentLeave" DATE,"IsEmployeeAcceptable" CHAR(1),"Availability" CHAR(5) ) 
// IQXWeb
BEGIN
  /* Don't use this. Use NetClientRequirementVerifiedCandidates */
  DECLARE "sID" CHAR(20);
  SET "sID" = (SELECT "employmentid" FROM "iqxnetuser" AS "u" KEY JOIN "iqxnetuserlink" WHERE "u"."iqxnetuserid" = "pWebUserID" AND "iqxnetuserclassid" = 'CLIENT');
  IF "isnull"("sID",'') = '' THEN
    RETURN
  END IF;
  SELECT "pe"."name",
    "pe"."personid",
    "st"."name" AS "PersonStatus",
    "s"."shiftdate" AS "StartDate",
    "s"."shiftdate" AS "LeaveDate",
    "isnull"("s"."shiftdate",CURRENT DATE) AS "currentleave",
    "EmployeeAcceptable"("pe"."personid","e"."companyid",0,"v"."vacancyid",0) AS "IsEmployeeAcceptable",
    "personavailableforplan"("pe"."personid","s"."ShiftDate","s"."TimeFrom","s"."TimeTo","s"."RecoveryHours",0,0,NULL,NULL,NULL,NULL,NULL) AS "Availability"
    FROM "tempshiftplan" AS "s"
      KEY JOIN "vacancy" AS "v"
      KEY JOIN "tempshift" AS "ts"
      KEY JOIN "person" AS "pe"
      LEFT OUTER JOIN "status" AS "st" ON "st"."status" = "pe"."status" AND "type" = 'P'
      ,"vacancy" AS "v"
      KEY JOIN "employment" AS "e"
    WHERE "s"."tempshiftplanid" = "pTempShiftID"
    AND "e"."employmentid" = "sID"
    AND "ts"."state" = 'W'
    AND "pe"."status" NOT IN( '.','P','L' ) 
    AND "IsEmployeeAcceptable" IS NULL
    AND("isnull"("Availability",'A') LIKE '[AM]%')
    ORDER BY "currentleave" DESC
END /* DOC
2019-03-08 PC IW-883 List of people available for a shift
2019-06-08 IW-883 MHS Don't use this. Use NetClientRequirementVerifiedCandidates
*/
/* TEST
call NetTestSetup('');
update tempshift set state='W' where tempshiftid='TEST';
update person set status='C' where personid='TEST';
update employment set leavedate=current date-370 where personid='test' and companyid='test';
select * from NetClientCandidatesPossibleForShift('test.client','test');
expect PersonName=test;
update employment set leavedate=current date-1 where employmentid='test';
select * from NetClientCandidatesPossibleForShift('test.client','test');
expect EOF;
select * from NetClientCandidatesPossibleForShift('test.candidate','test');
expect EOF;
update employment set leavedate=current date where personid='test' and companyid='test';
update tempshiftplan set shiftdate=null where tempshiftplanid='test';
update tempshift set state='[' where tempshiftid='TEST';
update person set status=null where personid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientCandidatesPossibleForShift" IS 
{CREATE PROCEDURE pears."NetClientCandidatesPossibleForShift"(IN pWebUserID CHAR(20), IN pTempShiftID CHAR(20))
RESULT(PersonName CHAR(60),PersonID CHAR(20),PersonStatus CHAR(20),StartDate CHAR(20),LeaveDate CHAR(20),CurrentLeave DATE,IsEmployeeAcceptable CHAR(1), Availability CHAR(5))
// IQXWeb
BEGIN
/* Don't use this. Use NetClientRequirementVerifiedCandidates */
    DECLARE sID CHAR(20);
 
    SET sID = (SELECT employmentid FROM iqxnetuser u KEY JOIN iqxnetuserlink WHERE u.iqxnetuserid=pWebUserID AND iqxnetuserclassid='CLIENT');
    IF isnull(sID,'')='' THEN
        RETURN;
    END IF;
 
	SELECT 
        pe.name,
        pe.personid, 
        st.name AS PersonStatus,
        s.shiftdate AS "StartDate",
        s.shiftdate AS "LeaveDate",
        isnull(s.shiftdate, CURRENT DATE) AS currentleave,
        EmployeeAcceptable(pe.personid,e.companyid,0,v.vacancyid,0) AS IsEmployeeAcceptable,
        personavailableforplan(pe.personid,"s"."ShiftDate","s"."TimeFrom","s"."TimeTo","s"."RecoveryHours",0,0,NULL,NULL,NULL,NULL,NULL)AS Availability 
    FROM 
        tempshiftplan s 
            KEY JOIN vacancy v
            KEY JOIN tempshift ts
            KEY JOIN person pe
            LEFT OUTER JOIN STATUS st ON st.status=pe.status AND TYPE='P',
        vacancy v
            KEY JOIN employment e
    WHERE
        s.tempshiftplanid=pTempShiftID
        AND e.employmentid=sID
        AND ts.state = 'W'
        AND pe.status NOT IN ('.','P','L')
        AND IsEmployeeAcceptable IS NULL
        AND (isnull(Availability,'A') LIKE '[AM]%')
    ORDER BY 
        currentleave DESC
 
END
 
/* DOC
2019-03-08 PC IW-883 List of people available for a shift
2019-06-08 IW-883 MHS Don't use this. Use NetClientRequirementVerifiedCandidates
*/
 
/* TEST
call NetTestSetup('');
update tempshift set state='W' where tempshiftid='TEST';
update person set status='C' where personid='TEST';
update employment set leavedate=current date-370 where personid='test' and companyid='test';
select * from NetClientCandidatesPossibleForShift('test.client','test');
expect PersonName=test;
update employment set leavedate=current date-1 where employmentid='test';
select * from NetClientCandidatesPossibleForShift('test.client','test');
expect EOF;
select * from NetClientCandidatesPossibleForShift('test.candidate','test');
expect EOF;
update employment set leavedate=current date where personid='test' and companyid='test';
update tempshiftplan set shiftdate=null where tempshiftplanid='test';
update tempshift set state='[' where tempshiftid='TEST';
update person set status=null where personid='TEST';
*/
}