pears.PersonAvailableforPlan [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.PersonAvailableforPlan ======
<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 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
</code>