====== pears.NetClientCandidatesPossibleForShift ======
<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"."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';
*/
}
</code>