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