====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."PersonAvailableforPlanIterate" IS 
{create function 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
  else // This is a night shift going into next day
    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>