====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."AWRWeeks" IS 
{create function 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>