pears.PersonAvailableforPlanIterate

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