====== pears.NetClientRequirementVerifiedCandidates ======
<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"."NetClientRequirementVerifiedCandidates"( in "pWebUserID" char(20),in "pRowID" char(20),in "IsShift" char(1) ) 
result( "PersonName" char(60),"PersonID" char(20),"PersonStatus" char(20),"StartDate" char(20),"LeaveDate" char(20),"CurrentLeave" date ) 
// IQXWeb
begin
  declare "currentStates" char(100);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  insert into "Cos"( "CompanyID" ) 
    select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  set "currentStates" = (select first "isnull"("personcurrentstates",'C') from "params" order by "dbid" asc);
  if "IsShift" = '1' then
    select "pe"."name",
      "pe"."personid",
      "st"."name" as "PersonStatus",
      (select "min"("shiftdate") from "tempshift" where "vacancyid" = "v"."vacancyid" and "personid" = "pe"."personid" and "state" <> 'C') as "StartDate",
      (select "max"("shiftdate") from "tempshift" where "vacancyid" = "v"."vacancyid" and "personid" = "pe"."personid" and "state" <> 'C') as "LeaveDate",
      "LeaveDate" as "currentleave"
      from "tempshiftplan" as "s"
        key join "vacancy" as "v"
        key join "employment" as "e"
        ,"person" as "pe"
        join "status" as "st" on "st"."status" = "pe"."status" and "st"."type" = 'P'
      where "e"."companyid" = any(select "companyid" from "cos")
      and "s"."tempshiftplanid" = "pRowID"
      and "pe"."personid" = any(select "personid" from "tempshift" where "vacancyid" = "v"."vacancyid" and "state" <> 'C')
      and "locate"("currentStates","pe"."status") > 0
      and "EmployeeAcceptable"("pe"."personid","e"."companyid",1,"v"."vacancyid",0) is null
      and "isnull"("personavailableforplan"("pe"."personid","s"."ShiftDate","s"."TimeFrom","s"."TimeTo","s"."RecoveryHours","s"."minutes",0,"v"."departmentid","s"."essentialskill","s"."essentialskillgradeid","s"."essentialskillchoicelist",null),'A') like '[AM]%'
      order by "currentleave" desc,
      "pe"."KeyName" asc
  else
    select "pe"."name",
      "pe"."personid",
      "st"."name" as "PersonStatus",
      (select "max"("startdate") from "employment" where "personid" = "pe"."personid") as "StartDate",
      (select "max"("leavedate") from "employment" where "personid" = "pe"."personid") as "LeaveDate",
      "isnull"("e"."leavedate",current date) as "currentleave"
      from "vacancy" as "v"
        key join "employment" as "e"
        ,"person" as "pe"
        join "status" as "st" on "st"."status" = "pe"."status" and "st"."type" = 'P'
      where "e"."companyid" = any(select "companyid" from "cos")
      and "v"."vacancyid" = "pRowID"
      and "pe"."personid" = any(select "emp"."personid" from "employment" as "emp" key join "placement" as "pl" where "pl"."vacancyid" = "v"."vacancyid")
      and "locate"("currentStates","pe"."status") > 0
      and "EmployeeAcceptable"("pe"."personid","e"."companyid",1,"v"."vacancyid",0) is null
      order by "currentleave" desc,
      "pe"."keyName" asc
  end if
end /* DOC
2019-01-18 PC IW-794 (IW-788) Client - Current Requirements - Shifts ability to book previous temp
2019-06-19 MHS IW-883
*/
/* TEST
call NetTestSetup('');
def test select * from NetClientRequirementVerifiedCandidates('TEST.CLIENT','TEST1','1');
update employment set startdate=current date - 2, leavedate=current date - 1 where personid='TEST';
update person set status='C' where personid='TEST';
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST','TEST','t1',current date,'09:00','17:00');
insert into tempshift (tempshiftid,vacancyid,shiftdate,timefrom,timeto,state,tempshiftplanid) on existing update defaults off values ('TEST','TEST',current date,'09:00','17:00','B','TEST');
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST1','TEST','t2',current date,'09:00','17:00');
do test;
expect EOF;
update tempshiftplan set shiftdate=current date + 1 where tempshiftplanid='TEST1';
do test;
expect PersonID=TEST;
select * from NetClientRequirementVerifiedCandidates('TEST.CLIENT','TEST','0');
// Just testing execution of non-shift - not result;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientRequirementVerifiedCandidates" IS 
{create PROCEDURE pears."NetClientRequirementVerifiedCandidates"(in pWebUserID char(20),in pRowID char(20),in IsShift char(1))
RESULT(PersonName char(60),PersonID char(20),PersonStatus char(20),StartDate char(20),LeaveDate char(20),CurrentLeave date)
// IQXWeb
begin
declare currentStates char(100);
declare local temporary table Cos(
    CompanyID char(20) null,
    ) not transactional;
insert into Cos( CompanyID ) 
    select companyid from employment key join iqxnetuserlink where iqxnetuserid = pWebUserID;
set currentStates=(select first isnull(personcurrentstates,'C') from params order by dbid);
if IsShift='1'  then
  select 
      pe.name,
      pe.personid, 
      st.name as PersonStatus,
      (select min(shiftdate) from tempshift where vacancyid=v.vacancyid and personid=pe.personid and state<>'C') as StartDate,
      (select max(shiftdate) from tempshift where vacancyid=v.vacancyid and personid=pe.personid and state<>'C') as LeaveDate,
      LeaveDate as currentleave
  from 
      tempshiftplan s 
      key join vacancy v
      key join employment e,
      person pe
      join status st on st.status=pe.status and st.type='P'
  where
      e.companyid in (select companyid from cos)
      and s.tempshiftplanid=pRowID
      and pe.personid in (select personid from tempshift where vacancyid=v.vacancyid and state<>'C')
      and locate(currentStates,pe.status)>0
      and EmployeeAcceptable(pe.personid,e.companyid,1,v.vacancyid,0) is null
      and isnull(personavailableforplan(pe.personid,s.ShiftDate,s.TimeFrom,s.TimeTo,s.RecoveryHours,s.minutes,0,v.departmentid,s.essentialskill,s.essentialskillgradeid,s.essentialskillchoicelist,null),'A') like '[AM]%'
  order BY 
      currentleave desc,
      pe.KeyName asc
else
  select
      pe.name,
      pe.personid, 
      st.name as PersonStatus,
      (select max(startdate) from employment where personid=pe.personid) as StartDate,
      (select max(leavedate) from employment where personid=pe.personid) as "LeaveDate",
      isnull(e.leavedate, current date) as currentleave
  from 
      vacancy v
      key join employment e,
      person pe
      join status st on st.status=pe.status and st.type='P'
  where
      e.companyid in (select companyid from cos)
      and v.vacancyid=pRowID 
      and pe.personid in (select emp.personid from employment emp key join placement pl where pl.vacancyid=v.vacancyid)
      and locate(currentStates,pe.status)>0
      and EmployeeAcceptable(pe.personid,e.companyid,1,v.vacancyid,0) is null
  order by 
      currentleave desc,
      pe.keyName asc
end if;
end

/* DOC
2019-01-18 PC IW-794 (IW-788) Client - Current Requirements - Shifts ability to book previous temp
2019-06-19 MHS IW-883
*/

/* TEST
call NetTestSetup('');
def test select * from NetClientRequirementVerifiedCandidates('TEST.CLIENT','TEST1','1');
update employment set startdate=current date - 2, leavedate=current date - 1 where personid='TEST';
update person set status='C' where personid='TEST';
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST','TEST','t1',current date,'09:00','17:00');
insert into tempshift (tempshiftid,vacancyid,shiftdate,timefrom,timeto,state,tempshiftplanid) on existing update defaults off values ('TEST','TEST',current date,'09:00','17:00','B','TEST');
insert into tempshiftplan (tempshiftplanid,vacancyid,description,shiftdate,timefrom,timeto) on existing update defaults off values ('TEST1','TEST','t2',current date,'09:00','17:00');
do test;
expect EOF;
update tempshiftplan set shiftdate=current date + 1 where tempshiftplanid='TEST1';
do test;
expect PersonID=TEST;
select * from NetClientRequirementVerifiedCandidates('TEST.CLIENT','TEST','0');
// Just testing execution of non-shift - not result;
*/
}
</code>