====== pears.NetClientRequirementShortListPlaced ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."NetClientRequirementShortListPlaced"( in "pWebUserID" char(20),in "pRowID" char(20),in "IsShift" char(1),in "IsCurrent" char(1) )
result( "PersonName" char(60),"PersonID" char(20),"PersonStatus" char(20),"StartDate" char(20),"LeaveDate" char(20),"CurrentLeave" date )
// IQXWeb
begin
declare "VacID" char(20);
set "VacID" = "pRowID";
if "IsShift" = 1 then
set "VacID" = (select "VacancyID" from "TempShiftPlan" where "tempshiftplanid" = "pRowID")
end if;
if not exists(select * from "placement" as "p" key join "employment" key join "person" where "p"."vacancyid" = "VacID") and not exists(select * from "tempshift" as "s" key join "person" as "pe" where "s"."vacancyid" = "VacID" and "s"."state" in( 'W','B','P' ) ) then
select '','','','','',null;
return
end if;
if "isCurrent" = 'Y' then
if "IsShift" = 1 then
select "pe"."name",
"pe"."personid",
(case "S"."state" when 'B' then 'Confirmed' when 'W' then 'Worked' when 'P' then 'Pending' end) as "PersonStatus",
"s"."shiftdate" as "StartDate",
"s"."shiftdate" as "LeaveDate",
"isnull"("s"."shiftdate",current date) as "currentleave"
from "tempshift" as "s"
key join "person" as "pe"
where "s"."tempshiftplanid" = "pRowID"
and "s"."state" in( 'W','B','P' )
order by "currentleave" desc
else
select "pe"."name" as "PersonName",
"pe"."personid",
'Placed' as "PersonStatus",
"e"."startdate" as "StartDate",
"e"."leavedate" as "LeaveDate",
"isnull"("e"."leavedate",current date) as "currentleave"
from "placement" as "p"
key join "employment" as "e"
key join "person" as "pe"
where "p"."vacancyid" = "VacID" and "currentleave" >= current date
order by "currentleave" desc,"PersonName" asc
end if
else if "IsShift" = 1 then
select distinct
"pe"."name",
"pe"."personid",
'' as "PersonStatus",
(select "min"("sm"."shiftdate") from "tempshift" as "sm" where "sm"."vacancyid" = "s"."vacancyid" and "sm"."personid" = "pe"."personid" and "sm"."state" in( 'W','B','P' ) and "isnull"("s"."shiftdate",current date) < current date and "sm"."state" in( 'W','B','P' ) ),
(select "max"("sm"."shiftdate") from "tempshift" as "sm" where "sm"."vacancyid" = "s"."vacancyid" and "sm"."personid" = "pe"."personid" and "sm"."state" in( 'W','B','P' ) and "isnull"("s"."shiftdate",current date) < current date and "sm"."state" in( 'W','B','P' ) ),
null as "currentleave"
from "tempshift" as "s"
key join "person" as "pe"
where "s"."vacancyid" = "VacID"
and "s"."state" in( 'W','B','P' )
and "isnull"("s"."shiftdate",current date) < current date
else
select "pe"."name" as "PersonName",
"pe"."personid",
'Placed' as "PersonStatus",
"e"."startdate" as "StartDate",
"e"."leavedate" as "LeaveDate",
"isnull"("e"."leavedate",current date) as "currentleave"
from "placement" as "p"
key join "employment" as "e"
key join "person" as "pe"
where "p"."vacancyid" = "VacID"
and "currentleave" < current date
order by "currentleave" desc,"PersonName" asc
end if
end if
end /* DOC
2016-11-30 PC add doc and test
2017-10-17 PC add employment dates
2018-03-23 JB IW-408
2018-05-02 PC,VS IW-408 revise to key on shiftid
2018-05-31 PC IW-565 condense rows into one with max and min dates
2019-01-10 PC resolve schema mismatch
2019-02-18 PC/ET IW-817 removed "no Data"
Tests:
valid pWebUserID, Rowid, IsShift=0
valid pWebUserID, Rowid, IsShift=1
*/
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientRequirementShortListPlaced" IS
{create PROCEDURE pears."NetClientRequirementShortListPlaced"(in pWebUserID char(20),in pRowID char(20),in IsShift char(1),in IsCurrent char(1))
RESULT(PersonName char(60),PersonID char(20),PersonStatus char(20),StartDate char(20),LeaveDate char(20),CurrentLeave date)
// IQXWeb
BEGIN
declare VacID char(20);
set VacID=pRowID;
if IsShift=1 then
set "VacID" = (select VacancyID from "TempShiftPlan" where "tempshiftplanid" = pRowID);
end if;
if not exists( select * from placement p key join employment key join person where p.vacancyid=VacID) and not exists(select * from tempshift s key join person pe where s.vacancyid=VacID and s.state in ('W','B','P')) then
select '','','','','',null;
return;
end if;
if isCurrent='Y' then
if IsShift=1 then
select
pe.name,
pe.personid,
(case S."state" when 'B' then 'Confirmed' when 'W' then 'Worked' when 'P' then 'Pending' end case) as PersonStatus,
s.shiftdate as "StartDate",
s.shiftdate as "LeaveDate",
isnull(s.shiftdate, current date) as currentleave
from
tempshift s
key join person pe
where
s.tempshiftplanid=pRowID
and s.state in ('W','B','P')
order BY
currentleave desc
else
select
pe.name as PersonName,
pe.personid,
'Placed' as PersonStatus,
e.startdate as "StartDate",
e.leavedate as "LeaveDate",
isnull(e.leavedate, current date) as currentleave
from
placement p
key join employment e
key join person pe
where
p.vacancyid=VacID and currentleave >= current date
order BY
currentleave desc, PersonName asc
end if;
else
if IsShift=1 then
select distinct
pe.name,
pe.personid,
'' as PersonStatus,
( select min(sm.shiftdate) from tempshift sm where sm.vacancyid=s.vacancyid and sm.personid=pe.personid and sm.state in ('W','B','P') and isnull(s.shiftdate, current date) < current date and sm.state in ('W','B','P')),
( select max(sm.shiftdate) from tempshift sm where sm.vacancyid=s.vacancyid and sm.personid=pe.personid and sm.state in ('W','B','P') and isnull(s.shiftdate, current date) < current date and sm.state in ('W','B','P')),
null as currentleave
from
tempshift s
key join person pe
where
s.vacancyid=VacID
and s.state in ('W','B','P')
and isnull(s.shiftdate, current date) < current date
else
select
pe.name as PersonName,
pe.personid,
'Placed' as PersonStatus,
e.startdate as "StartDate",
e.leavedate as "LeaveDate",
isnull(e.leavedate, current date) as currentleave
from
placement p
key join employment e
key join person pe
where
p.vacancyid=VacID
and currentleave < current date
order BY
currentleave desc, PersonName asc
end if;
end if;
END
/* DOC
2016-11-30 PC add doc and test
2017-10-17 PC add employment dates
2018-03-23 JB IW-408
2018-05-02 PC,VS IW-408 revise to key on shiftid
2018-05-31 PC IW-565 condense rows into one with max and min dates
2019-01-10 PC resolve schema mismatch
2019-02-18 PC/ET IW-817 removed "no Data"
Tests:
valid pWebUserID, Rowid, IsShift=0
valid pWebUserID, Rowid, IsShift=1
*/
}