fun_att_get_ottime

 

create function fun_att_get_ottime(
@empno as nvarchar(20),
@today as datetime,
@inORout as char(1))

returns varchar(5)

begin

 declare @sch_c int  
 
 declare @ot_in as varchar(5)
 declare @ot_out as varchar(5)

 select  @sch_c = count(*) from HR_ATTsch where convert(varchar(10), from_date, 120) = @today
 and patro_no = (select top 1 att_group from hrempa where empno = @empno)

 if(@sch_c = 0)
 begin
  select @ot_in = ot_in from HR_ATTdaily where convert(varchar(10), d_date, 120) = @today and empno = @empno
  select @ot_out = ot_out from HR_ATTdaily where convert(varchar(10), d_date, 120) = @today and empno = @empno

 end
 else --@sch_c > 0
 begin
  select @ot_in = mor_in from HR_ATTdaily where convert(varchar(10), d_date, 120) = @today and empno = @empno

  declare @mid_out varchar(5)
  select @ot_out = ot_out, @mid_out = mid_out from HR_ATTdaily where convert(varchar(10), d_date, 120) = @today and empno = @empno
  if @ot_out is null or @ot_out = ''
   set @ot_out = @mid_out
 end

 declare @r varchar(5)
 if @inORout = 'i'
  set @r = @ot_in
 else
  set @r = @ot_out

 return @r

end

 

 

 

 

 

 

 

 

CREATE function fun_att_wot_realtime(
@empno as nvarchar(20),
@today as datetime,
@efdate1 as datetime,
 @efdate2 as datetime)

returns varchar(100)
--對比加班時間和考勤時間,得出實際加班時長
begin
 declare @att_ot_in varchar(5)
 declare @att_ot_out varchar(5)
  
 set @att_ot_in = dbo.fun_att_get_ottime(@empno, @today, 'i')
 set @att_ot_out = dbo.fun_att_get_ottime(@empno, @today, 'o')
 
 if @att_ot_in is null and @att_ot_out is null
  return '0'

 declare @d_start datetime
 declare @d_end datetime
 declare @d_len nvarchar(4)
 
 declare @d1 as varchar(10)
 declare @d2 as varchar(10)
 set @d1 = convert(varchar(10), @efdate1, 120)
 set @d2 = convert(varchar(10), @efdate2, 120)

 
 declare @dt_att_ot_in datetime
 declare @dt_att_ot_out datetime
 
 set @dt_att_ot_in = @d1 + ' ' + @att_ot_in
 set @dt_att_ot_out = @d2 + ' ' +  @att_ot_out

 
 
 if @dt_att_ot_in > @efdate1
  set @d_start = @dt_att_ot_in
 else  
  set @d_start = @efdate1

 if @dt_att_ot_out < @efdate2 or @efdate2 = @efdate1
  set @d_end = @dt_att_ot_out
 else  
  set @d_end = @efdate2

 declare @m int
 set @m = DATEDIFF(minute, @d_start, @d_end)
 return cast((cast(@m as float) / cast(60 as float)) as numeric(9,2))
 
end