pears.TimesheetDayHours

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
}