====== pears.PersonAvailableforPlanIterate ======
<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"."PersonAvailableforPlanIterate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "sPersonid" char(20),in "dShiftDate" date,in "tShiftFrom" time,in "tShiftTo" time,in "iCheckExplicitlyAvailable" smallint,in "MovingShiftID" char(20) ) 
returns char(1)
begin
  declare "foundState" char(1);
  set "foundstate" = null; // If we return null, we have found no explicit unavailability or availability
  if "spersonid" is null then
    return null
  end if;
  if "tshiftfrom" < "tshiftto" then // Whole shift in day
    select first "state" into "foundstate" from "tempshift" where "state" not in( 'A','C' ) and "personid" = "spersonid" and "tempshiftid" <> "movingshiftid" and "shiftdate" = "dshiftdate" and "timefrom" < "tshiftto"
      and("effectivetimeto" > "tshiftfrom" or "effectivetimeto" <= "timefrom");
    if "foundstate" is null then // Check for clashing night shifts starting previous day
      select first "state" into "foundstate" from "tempshift" where "state" not in( 'A','C' ) and "personid" = "spersonid" and "tempshiftid" <> "movingshiftid" and "shiftdate" = "dshiftdate"-1 and "effectivetimeto" <= "timefrom" and "effectivetimeto" > "tshiftfrom"
    end if;
    if "foundstate" is null and "icheckexplicitlyavailable" <> 0 then // Check for explicit availability 
      select first "state" into "foundstate" from "tempshift" where "state" = 'A' and "personid" = "spersonid" and "shiftdate" = "dshiftdate"
        and("timefrom" is null or "effectivetimeto" is null or("timefrom" < "dateadd"("minute",1,"tshiftfrom") and("effectivetimeto" <= "timefrom" or "effectivetimeto" > "dateadd"("minute",-1,"tshiftto"))));
      if "foundstate" is null then // Check for availability starting the previous day
        select first "state" into "foundstate" from "tempshift" where "state" = 'A' and "personid" = "spersonid" and "shiftdate" = "dshiftdate"-1 and "effectivetimeto" <= "timefrom" and "effectivetimeto" > "dateadd"("minute",-1,"tshiftto")
      end if
    end if // This is a night shift going into next day
  else select first "state" into "foundstate" from "tempshift" where "state" not in( 'A','C' ) and "personid" = "spersonid" and "tempshiftid" <> "movingshiftid" and "shiftdate" = "dshiftdate" and("effectivetimeto" <= "timefrom" or "effectivetimeto" > "tshiftfrom");
    if "foundstate" is null then // Check for clashing shifts starting the previous day
      select first "state" into "foundstate" from "tempshift" where "state" not in( 'A','C' ) and "personid" = "spersonid" and "tempshiftid" <> "movingshiftid" and "shiftdate" = "dshiftdate"-1 and "effectivetimeto" <= "timefrom" and "effectivetimeto" > "tshiftfrom"
    end if;
    if "foundstate" is null then // Check for clashing shifts starting the next day
      select first "state" into "foundstate" from "tempshift" where "state" not in( 'A','C' ) and "personid" = "spersonid" and "tempshiftid" <> "movingshiftid" and "shiftdate" = "dshiftdate"+1 and "effectivetimeto" is not null and "timefrom" < "tshiftto"
    end if;
    if "foundstate" is null and "icheckexplicitlyavailable" <> 0 then // Check for explicit availability 
      select first "state" into "foundstate" from "tempshift" where "state" = 'A' and "personid" = "spersonid" and "shiftdate" = "dshiftdate"
        and("timefrom" is null or "effectivetimeto" is null or("timefrom" < "dateadd"("minute",1,"tshiftfrom") and "effectivetimeto" <= "timefrom" and "effectivetimeto" > "dateadd"("minute",-1,"tshiftto")));
      if "foundstate" is null then // Check for 2 shifts of availability, to and from midnight
        select first "state" into "foundstate" from "tempshift" where "state" = 'A' and "personid" = "spersonid" and "shiftdate" = "dshiftdate"
          and "timefrom" < "dateadd"("minute",1,"tshiftfrom") and "effectivetimeto" <= "timefrom";
        if "foundstate" = 'A' then // Look for second half
          set "foundstate" = null;
          select first "state" into "foundstate" from "tempshift" where "state" = 'A' and "personid" = "spersonid" and "shiftdate" = "dshiftdate"+1
            and("effectivetimeto" > "dateadd"("minute",-1,"tshiftto") or "effectivetimeto" <= "timefrom") and "timefrom" < '00:01'
        end if
      end if
    end if end if;
  return "foundstate"
end
</code>