====== pears.PersonAvailableforPlan ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create function "pears"."PersonAvailableforPlan"(
/* Application Maintained Function / Procedure - DO NOT EDIT*/
in "sPersonid" char(20),in "dShiftDate" date,in "tShiftFrom" time,in "tShiftTo" time,in "iRecovery" smallint,in "iMinutes" smallint,
in "iCheckExplicitlyAvailable" smallint,in "departmentid" char(2),in "EssentialSkill" char(15),in "EssentialSkillGradeID" char(10),in "EssentialSkillChoiceList" char(100),in "MovingShiftID" char(20) )
returns char(20)
begin
declare "foundState" char(20);
declare "founddigit" char(1);
declare "slotfound" tinyint;
declare "MustBeKnownAvailable" tinyint;
declare "RequiredGradeValue" integer;
declare "UpperGradeValue" integer;
declare "bExactGrade" integer;
declare "MaxGradeValue" integer;
declare "checktime" time;
declare "tloc" char(3);
declare "tid" char(3);
declare "gtid" char(3);
declare "tcid" char(4);
declare "i" integer;
set "foundstate" = null;
if "spersonid" is null then
return null
end if;
set "maxgradevalue" = 1000000000; -- 1 billion should be safe
if "left"("essentialskillgradeid",2) = '==' then
set "bExactGrade" = 1;
set "essentialskillgradeid" = "substring"("essentialskillgradeid",3)
else
set "bExactGrade" = 0
end if;
if "icheckexplicitlyavailable" < 0 then
set "mustbeknownavailable" = 0;
if "icheckexplicitlyavailable" = -1 then
set "icheckexplicitlyavailable" = 1
else
set "icheckexplicitlyavailable" = 0
end if
else select "onlymatchifknownavailable" into "mustbeknownavailable" from "person" where "personid" = "spersonid";
set "mustbeknownavailable" = "isnull"("mustbeknownavailable",0);
if "mustbeknownavailable" = 1 then
set "icheckexplicitlyavailable" = 1
end if end if;
if "length"("isnull"("essentialskill",'')) > 2 then
set "i" = "charindex"(';',"essentialskill");
if "i" > 0 then
set "tloc" = "left"("essentialskill","i"-1);
set "essentialskill" = "right"("essentialskill","length"("essentialskill")-"i")
end if;
set "i" = "charindex"(';',"essentialskill");
if "i" > 0 then
set "tid" = "left"("essentialskill","i"-1);
set "essentialskill" = "right"("essentialskill","length"("essentialskill")-"i")
end if;
set "i" = "charindex"(';',"essentialskill");
if "i" > 0 then
set "tcid" = "left"("essentialskill","i"-1);
set "gtid" = "right"("essentialskill","length"("essentialskill")-"i");
if "trim"("gtid") = '' then
set "gtid" = "tid"
end if
else set "tcid" = "essentialskill";
set "gtid" = "tid"
end if;
if "tloc" = 'A' then
set "tloc" = "string"('A',"departmentid")
end if;
if "gtid" <> "tid" then // gtid contains a single select grade question. The choice value (or sortorder if value is null) is used to rate the grade
select "isnull"("value","sortorder") into "requiredgradevalue" from "tagchoice" where "taglocation" = "tloc" and "tagid" = "gtid" and "tagchoiceid" = "essentialskillgradeid" and "isnull"("subchoice",0) = 0;
if "requiredgradevalue" is null then
set "requiredgradevalue" = 1;
set "uppergradevalue" = "maxgradevalue"
else
if "bExactGrade" = 1 then
set "uppergradevalue" = "requiredgradevalue"
else
set "uppergradevalue" = "maxgradevalue"
end if end if;
if not exists(select * from "tagvalue" as "v" key join "tagchoice" as "c" where "v"."taglocation" = "tloc" and "v"."tagid" = "gtid" and "v"."id" = "spersonid" and "isnull"("c"."value","c"."sortorder") between "requiredgradevalue" and "uppergradevalue") then
return('Q') // Not qualified
end if;
set "requiredgradevalue" = 1; // Grade check done. The skill selection may be multiple choice - any positive value will do
set "uppergradevalue" = "maxgradevalue"
else
select "value" into "requiredgradevalue" from "tagchoice" where "taglocation" = "tloc" and "tagid" = "tid" and "tagchoiceid" = "essentialskillgradeid" and "subchoice" = 1;
if "requiredgradevalue" is null then
set "requiredgradevalue" = 1;
set "uppergradevalue" = "maxgradevalue"
else
if "bExactGrade" = 1 then
set "uppergradevalue" = "requiredgradevalue"
else
set "uppergradevalue" = "maxgradevalue"
end if
end if end if;
if "tcid" <> '' then
set "essentialskillchoicelist" = "tcid"
else
set "essentialskillchoicelist" = "isnull"("essentialskillchoicelist",'')
end if;
while "essentialskillchoicelist" <> '' loop
set "i" = "charindex"(';',"essentialskillchoicelist");
if "i" = 0 then
set "tcid" = "essentialskillchoicelist";
set "essentialskillchoicelist" = ''
else
set "tcid" = "left"("essentialskillchoicelist","i"-1);
set "essentialskillchoicelist" = "right"("essentialskillchoicelist","length"("essentialskillchoicelist")-"i")
end if;
if not exists(select * from "tagvalue" where "taglocation" = "tloc" and "tagid" = "tid" and "tagchoiceid" like "tcid"
and "id" = "spersonid" and "value" between "requiredgradevalue" and "uppergradevalue") then
return('Q') // Not qualified
end if
end loop end if;
set "movingshiftid" = "isnull"("movingshiftid",'');
// First check for all day unavailability
select first "state" into "foundstate" from "tempshift" where "state" not in( 'A','C' ) and "personid" = "spersonid" and "tempshiftid" <> "movingshiftid" and "shiftdate" = "dshiftdate" and("timefrom" is null or "timeto" is null);
if "foundstate" is null then // Look for non-concurrent employments
select first 'W' into "foundstate" from "employment" where "isnull"("concurrent",0) = 0 and "personid" = "spersonid" and "startdate" <= "dshiftdate" and("leavedate" is null or "leavedate" >= "dshiftdate")
end if;
if "foundstate" is null then
if "isnull"("iminutes",0) = 0 or "iminutes" > 480 or "tshiftto" <= "tshiftfrom" then // Do not do iterative match for overnight shifts
set "foundstate" = "personavailableforplaniterate"("spersonid","dshiftdate","tshiftfrom","tshiftto","icheckexplicitlyavailable","movingshiftid");
set "foundstate" = "personavailableforrecovery"("foundstate","sPersonid","dShiftDate","tShiftFrom","tShiftTo","iRecovery","MovingShiftID")
else
set "slotfound" = 0;
set "checktime" = "tshiftto";
set "tshiftto" = cast("dateadd"("minute","iminutes","tshiftfrom") as time);
"iterator": loop
set "founddigit" = "personavailableforplaniterate"("spersonid","dshiftdate","tshiftfrom","tshiftto","icheckexplicitlyavailable","movingshiftid");
set "founddigit" = "personavailableforrecovery"("founddigit","sPersonid","dShiftDate","tShiftFrom","tShiftTo","iRecovery","MovingShiftID");
if "founddigit" is null and "slotfound" = 0 then
set "foundstate" = "string"('M',"dateformat"("dshiftdate",'yyyymmdd'),"dateformat"("tshiftfrom",'hhnn'),"dateformat"("tshiftto",'hhnn'));
set "slotfound" = 1;
if "icheckexplicitlyavailable" = 0 then
leave "iterator"
end if
else if "founddigit" = 'A' then
set "foundstate" = "string"('A',"dateformat"("dshiftdate",'yyyymmdd'),"dateformat"("tshiftfrom",'hhnn'),"dateformat"("tshiftto",'hhnn'));
leave "iterator"
else if "foundstate" is null and "founddigit" in( 'H','U','P','B','W' ) then
set "foundstate" = "founddigit"
end if end if end if;
set "tshiftfrom" = cast("dateadd"("minute",30,"tshiftfrom") as time);
set "tshiftto" = cast("dateadd"("minute",30,"tshiftto") as time);
if "tshiftto" > "checktime" or "tshiftto" <= "tshiftfrom" then
leave "iterator"
end if
end loop "iterator" end if end if;
if("foundstate" is null or "foundstate" like 'M%') and "mustbeknownavailable" = 1 then
set "foundstate" = 'U'
end if;
return "foundstate"
end