pears.wtd_holiday_stat

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."wtd_holiday_stat" IS 
{CREATE FUNCTION pears.wtd_holiday_stat(IN pid CHAR(20),IN yr INTEGER,IN wk INTEGER,IN Stat INTEGER)
RETURNS REAL
/* Stat parameter values- 
0: 1=qualifies for hol pay (i.e. Stat 1 >= 13), 0=does not qualify
1: Weeks worked this holiday year (if still working or stopped >= last week)  
2: As 1 but pre 23/11/99 (3 weeks holiday allowed per annum)
3: As 1 but post 23/11/99 (4 weeks holiday allowed per annum)
11: Average hours worked in last 12 weeks
12: Average weekly pay in last 12 weeks
21: Weeks holiday earned
22: Weeks holiday taken
23: Weeks holiday outstanding
24: Pay in lieu outstanding  */
BEGIN
  DECLARE sdate DATE;
  DECLARE wedate DATE;
  DECLARE wksworked REAL;
  DECLARE qualifyingwksworked REAL;
  DECLARE wksworkedpre1199 REAL;
  DECLARE wksworkedpost1199 REAL;
  DECLARE lastweek INTEGER;
  DECLARE twelveweeks INTEGER;
  DECLARE begyrweek INTEGER;
  DECLARE avgpay REAL;
  DECLARE avghrs REAL;
  DECLARE wksholearned REAL;
  DECLARE wksholtaken REAL;
  DECLARE wksholoutst REAL;
  DECLARE payinlieu REAL;
  DECLARE wksholintwelvewks REAL;
  DECLARE i INTEGER;
  DECLARE d DATE;
  DECLARE qualified SMALLINT;
  SET wedate="date"(weekenddate(yr,wk));
  IF wedate IS NULL THEN -- Off the range of known weeks
    RETURN 0
  END IF;
  SELECT MIN(startdate) INTO sdate FROM pay_employment WHERE personid = pid AND
    (enddate IS NULL OR enddate >= wedate-13); --(still working or stopped >= last week)
  IF sdate IS NULL THEN
    RETURN 0
  END IF;
  SET qualifyingwksworked=CEILING(((wedate-6)-sdate)/7);
  SET sdate=getholidayyear(sdate,wedate-7); --Does 1/10/1998 threshold, and rolls forward if > 1 year ago
  SET wksworked=CEILING(((wedate-6)-sdate)/7);
  IF wksworked <= 0 THEN
    RETURN 0
  END IF;
  IF qualifyingwksworked >= 13 THEN
    SET qualified=1
  ELSE
    SET qualified=0
  END IF;
  IF stat = 0 THEN
    RETURN qualified
  END IF;
  IF stat = 1 THEN
    RETURN wksworked
  END IF;
  SET d=wedate-6;
  IF d > "date"('1999-11-20') THEN
    SET d="date"('1999-11-20')
  END IF;
  SET wksworkedpre1199=CEILING((d-sdate)/7);
  IF wksworkedpre1199 < 0 THEN
    SET wksworkedpre1199=0
  END IF;
  SET wksworkedpost1199=wksworked-wksworkedpre1199;
  IF stat = 2 THEN
    RETURN wksworkedpre1199
  END IF;
  IF stat = 3 THEN
    RETURN wksworkedpost1199
  END IF;
  SET lastweek=tempgetweekago(yr,wk,1);
  SET twelveweeks=tempgetweekago(yr,wk,12);
  IF qualified = 1 THEN -- Lots of stuff irrelevant if not past the 13 week threshold
    SELECT isnull(SUM(weekstaken),0) INTO wksholintwelvewks FROM pay_holiday WHERE -- Wks hol taken in last 12 wks
      personid = pid AND yearnumber*100+weeknumber BETWEEN twelveweeks AND lastweek;
    IF wksholintwelvewks > 11 THEN -- Cannot do the 12 week averaging so give up
      RETURN 0
    END IF;
    SELECT SUM(isnull(hours,0)+isnull(nighthours,0)+isnull(othours,0))/(12-wksholintwelvewks) INTO avghrs
      FROM tempslot WHERE personid = pid AND yearnumber*100+weeknumber BETWEEN twelveweeks AND lastweek;
    SET avghrs=isnull(avghrs,0);
    IF stat = 11 THEN
      RETURN avghrs
    END IF;
    SELECT SUM(isnull(hours*pay,0)+isnull(nighthours*nightpay,0)+isnull(othours*otpay,0))/(12-wksholintwelvewks) INTO avgpay
      FROM tempslot WHERE personid = pid AND yearnumber*100+weeknumber BETWEEN twelveweeks AND lastweek;
    SET avgpay=isnull(avgpay,0);
    IF stat = 12 THEN
      RETURN avgpay
    END IF;
    SET wksholearned=3*wksworkedpre1199/52+4*wksworkedpost1199/52;
    IF stat = 21 THEN
      RETURN wksholearned
    END IF
  ELSE -- Not Qualified but still want wksholtaken and wksholoutst in case of negative balances
    SET wksholearned=0;
    SET avghrs=0;
    SET avgpay=0
  END IF;
  SELECT MAX(yearnumber) INTO i FROM payrollyear WHERE(week1friday-6) <= sdate;
  SELECT week1friday-6 INTO d FROM payrollyear WHERE yearnumber = i;
  SET begyrweek=i*100+MOD(sdate-d,7)+1; -- The temp week no of the start of this temp''s hol year
  SELECT isnull(SUM(weekstaken),0) INTO wksholtaken FROM pay_holiday WHERE -- Weeks hol taken this hol year
    personid = pid AND yearnumber*100+weeknumber BETWEEN begyrweek AND lastweek;
  IF stat = 22 THEN
    RETURN wksholtaken
  END IF;
  SET wksholoutst=wksholearned-wksholtaken;
  IF stat = 23 THEN
    RETURN wksholoutst
  END IF;
  SET payinlieu=wksholoutst*avgpay;
  IF stat = 24 THEN
    RETURN payinlieu
  END IF;
  RETURN 0
END
}