/****** Object:  UserDefinedFunction [_SMDBA_].[CALCWOAGE]    Script Date: 07/16/2008 11:47:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create function [_SMDBA_].[CALCWOAGE] (@seq int, @now datetime) returns int  
as  
begin    
-- function to return total seconds of incident age based on work schedule and clock stoppage    
declare @holidayset int, @dateopen datetime, @dateclose datetime, @currdate datetime, @currdatenotime datetime, @nownotime datetime, @retval int, @maxseconds int  
    
-- get holiday set from severity link in incident    
select @holidayset = null    
select @holidayset = SEQUENCE from dbo.HOLIDAYSET where NAME = (SELECT SCHEDULE from _SMDBA_._SEVERITY_ where SEQUENCE = (select [SEQ_SEVERITY:] from _SMDBA_._WORKORD_ where SEQUENCE = @seq))    
select @dateopen = [DATE OPEN], @dateclose = [CLOSED ON] from _SMDBA_._WORKORD_ where SEQUENCE = @seq    
    
-- check the closed date, make the aging date for this incident the close date if closed    
if @dateclose is not null    
 if @dateclose < @now    
  select @now = @dateclose    
    
-- make sure the aging date > the open date    
if @now <= @dateopen return 0    
    
-- assume all minutes are workable    
select @maxseconds = datediff(ss, @dateopen, @now)    
select @retval = @maxseconds    
  
-- no need to do this if holidayset is null    
if @holidayset is not null select @retval = [_SMDBA_].CALCWORKINGSECONDS(@holidayset, @dateopen, @now)    
    
select @currdate = @dateopen    
select @currdatenotime = convert(datetime, convert(varchar, @currdate, 101), 101)    
select @nownotime = convert(datetime, convert(varchar, @now, 101), 101)    

select @retval = @retval - [_SMDBA_].CALCHOLIDAYSECONDS(@holidayset, @dateopen, @now)

if exists (select NULL from [_SMDBA_].[_WORKDET_] where [TTNUM] = @seq and ACTION IN (59,60) and PDATE < @now)  
begin  
  -- cursor for all stop/start clock records in incident details for this incident    
  declare @pdate datetime, @action int, @state int, @laststoptime datetime    
  select @state = 0 --0 = expect stop, 1 = expect start; starting state for this FSM is expect stop    
  -- note in SQL that we only get start/stop clock events before the aging date (allows arbitrary aging date)    
  declare clockdetail_curs cursor for select PDATE, ACTION from [_SMDBA_].[_WORKDET_] where [TTNUM] = @seq and ACTION IN (59,60) and PDATE < @now order by [TTNUM], PDATE, ACTION
  open clockdetail_curs     
  fetch next from clockdetail_curs into @pdate, @action    
  while @@fetch_status = 0    
  begin    
   if @state = 0 --expect stop    
   begin    
    if @action = 59 -- found a stop clock    
    begin    
     -- set last stop time holder to this datetime, now state is expecting a start    
     select @laststoptime = @pdate, @state = 1    
    end    
   end    
   else --expect start    
   begin    
    if @action = 60 -- found a start clock event    
    begin    
     -- see if any of the minutes expired while the clock was stopped were working minutes    
     -- if so, deduct them from the total; if no work schedule, all minutes are workable    
     select @retval = @retval - ([_SMDBA_].CALCWORKINGSECONDS(@holidayset, @laststoptime, @pdate) - [_SMDBA_].CALCHOLIDAYSECONDS(@holidayset, @laststoptime, @pdate)), 
            @state = 0    
    end    
   end    
   fetch next from clockdetail_curs into @pdate, @action    
  end    
  close clockdetail_curs     
  deallocate clockdetail_curs     
    
  -- see if the last action had the clock stopped    
  if @state = 1    
  begin    
   -- deduct any working minutes from the last stop time to the aging date    
   select @retval = @retval - ([_SMDBA_].CALCWORKINGSECONDS(@holidayset, @laststoptime, @now) - [_SMDBA_].CALCHOLIDAYSECONDS(@holidayset, @laststoptime, @now))
  end    
  
end  
    
exit_return:    
if @retval < 0 select @retval = 0    
return @retval    
    
end