pears.PersonAvailableforPlanIterate

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
}