pears.TimesheetDayHours

CREATE FUNCTION "pears"."TimesheetDayHours"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "bprocess" tinyint,IN "bprovisional" tinyint,IN "tsid" CHAR(20),IN "pdate" DATE ) 
RETURNS DOUBLE
BEGIN
  DECLARE "rv" DOUBLE;
  DECLARE "placid" CHAR(20);
  DECLARE "vacid" CHAR(20);
  DECLARE "persid" CHAR(20);
  DECLARE "desktype" CHAR(1);
  DECLARE "b" tinyint;
  DECLARE "d" SMALLINT;
  DECLARE "d1" DATE;
  DECLARE "d2" DATE;
  IF "isnull"("bprocess",0) = 0 OR "isnull"("bprovisional",0) = 0 THEN -- only do provisionals at present
    RETURN NULL
  END IF;
  SELECT FIRST "t"."placementid","t"."vacancyid","t"."personid","d"."desktype" INTO "placid","vacid","persid","desktype" FROM "tempprovtimesheet" AS "t" KEY JOIN "tempdesk" AS "d" WHERE "t"."tempprovtimesheetid" = "tsid";
  IF "desktype" = 'M' THEN
    RETURN NULL
  END IF;
  IF "desktype" = 'S' THEN
    SELECT "sum"("getshiftlength"("timefrom","timeto","breakminutes")) INTO "rv" FROM "tempshift" WHERE "state" IN( 'P','B' ) AND "shiftdate" = "pdate" AND "personid" = "persid" AND "vacancyid" = "vacid"
      AND(("isnull"("placementid",'') = "isnull"("placid",'')) OR("isnull"("placementid",'') = ''));
    RETURN "rv"
  ELSE
    SELECT FIRST "e"."startdate","e"."leavedate" INTO "d1","d2" FROM "placement" AS "p" KEY JOIN "employment" AS "e" WHERE "p"."placementid" = "placid";
    IF "pdate" < "d1" THEN
      RETURN NULL
    END IF;
    IF "pdate" > "d2" THEN
      RETURN NULL
    END IF;
    SELECT FIRST "v"."WorkCancelled","v"."WorkHours" INTO "b","rv" FROM "PlacementDayVariation" AS "v" WHERE "v"."placementid" = "placid" AND "v"."VariationDate" = "pdate";
    IF "b" = 1 THEN
      RETURN NULL
    END IF;
    IF "rv" IS NOT NULL THEN
      RETURN "rv"
    END IF;
    SET "d" = "dow"("pdate")-1;
    SELECT(CASE "d" WHEN 0 THEN "worksunday" WHEN 1 THEN "workmonday" WHEN 2 THEN "worktuesday" WHEN 3 THEN "workwednesday" WHEN 4 THEN "workthursday" WHEN 5 THEN "workfriday" WHEN 6 THEN "worksaturday" END),
      "worknormalhours" INTO "b","rv" FROM "placement" WHERE "placementid" = "placid";
    IF "b" = 1 THEN
      RETURN "rv"
    END IF END IF;
  RETURN NULL
END