USE [ProcessManager]
GO

/****** Object:  UserDefinedFunction [dbo].[GetNewAge]    Script Date: 12/7/2017 9:31:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE function [dbo].[GetNewAge] (@startDate DateTime, @endDate DateTime, @useBusinessHours bit =0, @useWorkDays bit =0, @sessionid nvarchar(40) ='00000000-0000-0000-0000-010000000000')
returns int
as
begin

declare @bStartTime DateTime
declare @bEndTime DateTime
-- select @bStartTime = BusinessDayStart, @bEndTime = BusinessDayEnd from MasterSettings


select @bStartTime = StartTime, @bEndTime = EndTime from dbo.GetNewBusinessHours(@sessionid)

declare @startSec int
set @startSec = datediff(s,dateadd(d, 0, datediff(d, 0, @startDate)),@startDate)

declare @endSec int
set @endSec = datediff(s,dateadd(d, 0, datediff(d, 0, @endDate)),@endDate)

declare @bStartSec int
set @bStartSec = datediff(s,dateadd(d, 0, datediff(d, 0, @bStartTime)),@bStartTime)

declare @bEndSec int
set @bEndSec = datediff(s,dateadd(d, 0, datediff(d, 0, @bEndTime)),@bEndTime)

if(@useBusinessHours = 0)
begin
	set @bStartSec = 0
	set @bEndSec = 24*3600
end

declare @res int
set @res=0

if @useWorkDays=0 OR (not exists(select top(1) * from Holiday where DATEDIFF(d,@startDate,[Date])=0) AND
	not exists( select top(1) * from Weekend where DATEPART(DW,@startDate)=[Weekday]))
begin
	declare @dayStartSec int
	if @startSec > @bStartSec
		set @dayStartSec = @startSec
	else
		set @dayStartSec = @bStartSec

	declare @dayEndSec int
	
	if datediff(d,0,@startDate) < datediff(d,0,@endDate)
		set @dayEndSec=@bEndSec
	else
		if datediff(d,0,@startDate) = datediff(d,0,@endDate)
			if @bEndSec < @endSec
				set @dayEndSec = @bEndSec
			else
				set @dayEndSec = @endSec
		else
			set @dayEndSec=@dayStartSec
	
	if( @dayEndSec>@dayStartSec)
		set @res=@res+@dayEndSec-@dayStartSec
end


while datediff(d,0,@startDate)+1 < datediff(d,0,@endDate)
begin
	set @startDate=@startDate+1

	if @useWorkDays=0 OR (not exists( select top(1) * from Holiday where DATEDIFF(d,@startDate,Date)=0 ) AND
		not exists( select top(1) * from Weekend where DATEPART(DW,@startDate)=[Weekday]))
		set @res = @res + @bEndSec - @bStartSec

end

set @startDate=@startDate+1

if datediff(d,0,@startDate) = datediff(d,0,@endDate) AND ( @useWorkDays=0 OR
	(not exists( select top(1) * from Holiday where DATEDIFF(d,@startDate,Date)=0 ) AND
	not exists( select top(1) * from Weekend where DATEPART(DW,@startDate)=[Weekday])) )
begin
	set @dayStartSec = @bStartSec
	
	if @bEndSec < @endSec
		set @dayEndSec = @bEndSec
	else
		set @dayEndSec = @endSec

	if( @dayEndSec>@dayStartSec)
		set @res=@res+@dayEndSec-@dayStartSec
	
end
return @res
end

GO