====== 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>
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
}
</code>