====== pears.AWRWeeks ======
<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"."AWRWeeks"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @JobMasterID char(20),in @WeekNo integer,in @VacID char(20),in @PersID char(20),in @PlacID char(20),in @ShiftID char(20) ) 
returns integer
begin
  declare @AWRStat integer;
  declare @AWRLink char(20);
  declare @PersonID char(20);
  declare @DirectlyEmployed smallint;
  declare @AWRStartPeriod integer;
  declare @WkCount integer;
  declare @GapCount smallint;
  declare @FunctionTimeStamp "datetime";
  declare @LastPeriod integer;
  declare @OverRide smallint;
  declare @OverRideDate date;
  declare @ThisPeriodGap smallint;
  declare @FirstOne smallint;
  -- parameters @VacID, @PersID, @PlacID, @ShiftID not currently used
  -- check override AWR start date not set (mainly a testing facility)
  if "varexists"('AWRStartPeriod') = 1 then
    set @AWRStartPeriod = "AWRStartPeriod"
  else set @AWRStartPeriod = "isnull"("weekcontaining"("dateadd"("year",-5,current date)),(select "min"("period") from "payrollcalendar"))
  end if;
  set @WkCount = 0;
  -- check role qualified 
  if "nullif"(@JobMasterID,'') is not null then
    select "AWRStatus","AWRLinkCode","personid","isnull"("OverrideSystem",1),"OverrideDate",
      (if exists(select * from "person" as "p" where "p"."personid" = "AWRJobMaster"."Personid" and "isnull"("directlyemployed",0) >= 1) then 1 else 0 endif)
      into @AWRStat,@AWRLink,@PersonID,@OverRide,@OverRideDate,@DirectlyEmployed
      from "AWRJobMaster" where "AWRJobMasterID" = @JobMasterID;
    -- finish if role not qualified or Directly Employed
    if @AWRStat = 3 or @DirectlyEmployed = 1 then
      return 0
    end if;
    -- look for override
    if @OverRideDate is not null then // nested ifs for performance - prevents function call if not necessary
      if @WeekNo >= "weekcontaining"(@OverRideDate) then
        if @OverRide = 3 then return 0
        elseif @OverRide = 2 then return 15
        end if end if end if;
    -- count weeks since 6 week gap add 1 for continue 0 for pause stop at 15
    set @FunctionTimeStamp = current timestamp;
    set @WkCount = 0;
    set @GapCount = 0;
    set @LastPeriod = @WeekNo;
    set @ThisPeriodGap = 0;
    set @FirstOne = 1;
    -- populate temporary table 
    insert into "AWRWeekFind"( "AWRJobMasterID","DataGenerated","ThisPeriod","Qual" ) 
      select @JobMasterID,@FunctionTimeStamp,"period","isnull"("qualtype",0)
        from "AWRWeeklyDetail"
        where "AWRJobmasterid" = any(select "AWRJobMasterID" from "AWRJobMaster" where "AWRLinkCode" = @AWRLink)
        and("disregard" = 0 or "disregard" is null) and "period" between @AWRStartPeriod and @WeekNo union all
      select @JobMasterID,@FunctionTimeStamp,"period","isnull"("qualtype",0)
        from "AWRWeeklyDetail"
        where "personid" = @PersonID and("disregard" = 0 or "disregard" is null) and "period" between @AWRStartPeriod and @WeekNo union all
      select @JobMasterID,@FunctionTimeStamp,"period","isnull"("qualtype",0)
        from "AWRWeeklyDetail"
        where "companyid" = any(select "companyid" from "AWRJobMaster" key join "vacancy" key join "employment" where "AWRLinkCode" = @AWRLink) and("disregard" = 0 or "disregard" is null) and "period" between @AWRStartPeriod and @WeekNo;
    -- now check results
    for "forlab" as "curs" no scroll cursor for
      select "ThisPeriod","Qual"
        from "AWRWeekFind" where "AWRJobMasterID" = @JobMasterID and "DataGenerated" = @FunctionTimeStamp
        order by "ThisPeriod" desc,"Qual" desc for read only
    do -- check if we have moved on to a new period, if not we can skip to next record 
      if "ThisPeriod" <> @LastPeriod or @FirstOne = 1 then
        -- Work out the gap since the last record
        if "truncnum"(@LastPeriod,-2)/100 = "truncnum"("ThisPeriod",-2)/100 then //same year
          set @ThisPeriodGap = @LastPeriod-"ThisPeriod"-1
        else //different year   
          set @ThisPeriodGap = ("datediff"("day","weekmonthenddate"("ThisPeriod",'W'),"weekmonthenddate"(@LastPeriod,'W'))/7)-1
        end if;
        -- check for 6 week gap since last record and finish if exceeded
        if @FirstOne = 1 then
          set @ThisPeriodGap = @ThisPeriodGap+1;
          set @FirstOne = 0
        end if;
        if(@GapCount+@ThisPeriodGap >= 6) then
          delete from "AWRWeekFind" where "DataGenerated" = @FunctionTimeStamp;
          return @WkCount
        end if;
        -- check the record status
        if "Qual" = 2 then //timesheet, other agency work or other continue record found
          set @WkCount = @WkCount+1;
          set @GapCount = 0
        elseif "Qual" = 1 then //pause record found
          set @GapCount = @GapCount+@ThisPeriodGap
        end if;
        -- check for 6 week gap and finish if exceeded
        if @GapCount >= 6 then
          delete from "AWRWeekFind" where "DataGenerated" = @FunctionTimeStamp;
          return @WkCount
        end if;
        -- stop counting at 15 weeks since qualification
        if @WkCount >= 15 then
          delete from "AWRWeekFind" where "DataGenerated" = @FunctionTimeStamp;
          return @WkCount
        end if;
        -- set the @lastPeriod ready for moving to next row
        set @LastPeriod = "ThisPeriod"
      end if end for end if;
  delete from "AWRWeekFind" where "DataGenerated" = @FunctionTimeStamp;
  return @WkCount
end
</code>