====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."TimesheetDayHours" IS 
{create function 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>