pears.wtd_holiday_stat

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" -- Wks hol taken in last 12 wks
      WHERE "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"
    -- Not Qualified but still want wksholtaken and wksholoutst in case of negative balances
    END IF
  ELSE 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" -- Weeks hol taken this hol year
    WHERE "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