====== pears.TimesheetDayHours ======
<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"."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
</code>