USE [XFPDM]
GO

/****** Object:  UserDefinedFunction [dbo].[dateAddWork]    Script Date: 2020-01-07 16:55:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--select top(50) * from PDM_WOMFGOrder order by enterdate desc
--select dateadd(second,2*60*60,'2020-01-06 12:10:13.577')
CREATE function [dbo].[dateAddWork] (@datex datetime,@t int)
returns datetime as
begin
declare @date datetime
set @date = @datex--'2020-01-06 18:10:13.577'
declare @dates datetime
set @dates =  SUBSTRING(CONVERT(CHAR(23), @date, 121),0,11)
declare @hour int
declare @day int
declare @astart int
declare @as datetime
declare @aend int
declare @ae datetime
declare @atimes int
declare @pstart int
declare @ps datetime
declare @pend int
declare @pe datetime
declare @btimes int
declare @end int
declare @ee datetime
declare @vt datetime
set @astart = 8*60*60 --上午开始工作日期
set @as =  dateadd(second,@astart,@dates)
set @aend = 12*60*60  --上午结束工作日期
set @ae =  dateadd(second,@aend,@dates)
set @pstart = 14*60*60 --下午开始工作日期
set @ps =  dateadd(second,@pstart,@dates)
set @pend = 18*60*60  --下午结束工作日期
set @pe =  dateadd(second,@pend,@dates)
set @end = 24*60*60 --当天结束日期
set @ee =  dateadd(second,@end,@dates)
--select @dates
set @atimes= @aend -@astart --上午工作时长
set @btimes= @pend - @pstart --下午工作时长
set @hour =@t%((@atimes+@btimes)/60/60)*60*60 --一天工作时长（小时）
set @day = @t / ((@atimes+@btimes)/60/60) --天数
--select @date as '@date',@dates  as '@dates',@as  as '@as',@ae  as '@ae',@ps  as '@ps',@pe  as '@pe',@ee  as '@@ee'
if @date  <=@as
 begin
  --select '0-8'
  if @hour<=@atimes
   set @vt = DATEADD(second,@hour,@as)
  else
  set @vt = DATEADD(second,@hour-@atimes,@ps)
 end
else if @date>@as and @date <=@ae
 begin
  --select '8-12'
  if datediff(second,@date,@ae) >= @hour
   set @vt = DATEADD(second,@hour,@date)
  else
   begin
    if datediff(second,@date,@ae)+@btimes >= @hour
    set @vt = DATEADD(second,@hour-datediff(second,@date,@ae),@ps)
    else
     set @vt = DATEADD(second,@hour-datediff(second,@date,@ae)-@btimes,DATEADD(second,@astart,@ee))
   end
 end
else if @date>@ae and @date <=@ps
 begin
  --select '12-14'
  if @hour<=@btimes
   set @vt = DATEADD(second,@hour,@ps)
   else
    set @vt = DATEADD(second,@hour-@btimes,DATEADD(second,@astart,@ee))
 end
else if @date>@ps and @date <=@pe
 begin
  --select '14-18',@pend
  if datediff(second,@date,@pe) >= @hour
   set @vt = DATEADD(second,@hour,@date)
  else
  begin
   if datediff(second,@date,@pe)+@atimes >= @hour
    set @vt = DATEADD(second,@hour-datediff(second,@date,@pe),DATEADD(second,@astart,@ee))
    else
     set @vt = DATEADD(second,@hour-datediff(second,@date,@pe)-@atimes,DATEADD(second,@pstart,@ee))
  end
 end
else
 begin
  --select '18-24'
  if @hour<=@atimes
    begin
     set @vt = DATEADD(second,@hour,DATEADD(second,@astart,@ee))
    end
  else
  set @vt = DATEADD(second,@hour-@atimes,DATEADD(second,@pstart,@ee))
 end
--select @vt
 return  dateadd(day,@day,@vt)
end
GO