首页 > 代码库 > 迟到没

迟到没

2024-08-05 13:22:38   218人阅读

---得到遲到多少時間或沒有遲到 Geovin Du 塗聚文

declare cursor_select cursor for  select WorkingHoursTime,WorkingHoursUidKey FROM WorkingHoursSet
declare @ntime smalldatetime,@uidkey Uniqueidentifier,@timeint int,@empoyee Uniqueidentifier,@WorkAttendanceUid Uniqueidentifier
--set @uidkey=‘E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A‘
--set @empoyee=‘B8C15A91-D061-4626-8F7A-79D2F16D3A96‘
set @WorkAttendanceUid=‘06335791-F6A3-4B42-B15E-4F3CC711D442‘
 
open cursor_select
fetch cursor_select into @ntime,@uidkey
while @@fetch_status=0
begin
      --select @ntime,@uidkey
     if @uidkey=‘E585A963-58C9-4B8D-A610-9640E8BADFDE‘  --膳入
      begin
         --select @ntime,@uidkey
         select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey
    SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime)) 
    FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
    AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime))<0
          select ISNULL(@timeint,0)
       end
    if @uidkey=‘6E497449-9AEE-4921-8D2D-936C8CE027E0‘ --膳出
    begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime) 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
        select ISNULL(@timeint,0)
        end
    if @uidkey=‘1B823F2E-6EAB-4F3F-80C0-BEC720CE087D‘ --上班  Geovin Du 塗聚文
    begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime)) 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime))<0
        select ISNULL(@timeint,0)
    end
    if @uidkey=‘E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A‘  --下班  Geovin Du 塗聚文
        begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime) 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
        select ISNULL(@timeint,0)
    end  
    fetch cursor_select into @ntime,@uidkey
end
close cursor_select
deallocate cursor_select
 
 
 
declare @ntime smalldatetime,@uidkey Uniqueidentifier,@timeint int,@empoyee Uniqueidentifier,@WorkAttendanceUid Uniqueidentifier,@time smalldatetime
set @uidkey=‘E585A963-58C9-4B8D-A610-9640E8BADFDE‘
--set @empoyee=‘B8C15A91-D061-4626-8F7A-79D2F16D3A96‘  Geovin Du 塗聚文
set @WorkAttendanceUid=‘06335791-F6A3-4B42-B15E-4F3CC711D442‘
 
     if @uidkey=‘E585A963-58C9-4B8D-A610-9640E8BADFDE‘  --膳入
      begin
         --select @ntime,@uidkey
         select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey --規定設定時間
    SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime 
    FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
    AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime))<0
          select ISNULL(@timeint,0)
       end
    if @uidkey=‘6E497449-9AEE-4921-8D2D-936C8CE027E0‘ --膳出
    begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
        select ISNULL(@timeint,0)
        end
    if @uidkey=‘1B823F2E-6EAB-4F3F-80C0-BEC720CE087D‘ --上班
    begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime))<0
        select ISNULL(@timeint,0)
    end
    if @uidkey=‘E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A‘  --下班
        begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime  
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
        select ISNULL(@timeint,0)
    end  
GO
 
 
select [dbo].[GetLateTimeInputTwo] (WorkAttendanceUidKey,WorkAttendanceUid),WorkAttendanceDatetime from dbo.WorkAttendanceReport
 
select [dbo].[GetLateTimeInputTwo] (WorkAttendanceUidKey,WorkAttendanceUid) AS ‘遲到時間‘,WorkAttendanceDatetime,WorkingHoursTypeName,EmployeeName from View_WorkAttendanceLateTime
 
 
 
---算是否考勤遲到
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[GetLateTimeInputTwo]‘) and xtype in (N‘FN‘, N‘IF‘, N‘TF‘))
drop function [dbo].[GetLateTimeInputTwo]
GO
CREATE   function  GetLateTimeInputTwo
( 
   @uidkey Uniqueidentifier,
   @WorkAttendanceUid Uniqueidentifier --参数
)
returns int
as
begin
    declare @ntime smalldatetime,@timeint int,@time smalldatetime
     if @uidkey=‘E585A963-58C9-4B8D-A610-9640E8BADFDE‘  --膳入
      begin
         --select @ntime,@uidkey
            select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey
        SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime))<0
            --return ISNULL(@timeint,0)
       end
    if @uidkey=‘6E497449-9AEE-4921-8D2D-936C8CE027E0‘ --膳出
    begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
        --return ISNULL(@timeint,0)
        end
    if @uidkey=‘1B823F2E-6EAB-4F3F-80C0-BEC720CE087D‘ --上班
    begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime 
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime))<0
        --return ISNULL(@timeint,0)
    end
    if @uidkey=‘E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A‘  --下班
        begin
        select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
        SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime  
        FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
        AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+‘ ‘+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
        --return ISNULL(@timeint,0)
    end
return ISNULL(@timeint,0)
end 
GO

迟到没


联系
我们