====== pears.PersonAvailableforPlan ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."PersonAvailableforPlan" IS
{create function 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
}