推荐资料自学网址:http://www./study.asp?vip=10241777 ALTER proc [dbo].[HN_Kq_TimeTest] (
@userid as varchar(50), --员工代号
@begindate as datetime , -- 开始时间
@enddate as datetime, --截止时间
@type as int --0 标示请假 1 标示加班 2 标示补卡
)
as
declare @begin as datetime --开始时间
declare @end as datetime
set @begin=@begindate
set @end=@enddate
declare @begintime as varchar(20) --开始时间
declare @endtime as varchar(20) --截止时间
declare @Depno as varchar(20) --部门代号
declare @morring1 as varchar(20) --早上上班时间
declare @morring2 as varchar(20) --早上下班时间
declare @afternoon1 as varchar(20) --下午上班时间
declare @afternoon2 as varchar(20) --下午下班时间
declare @night1 as varchar(20) --晚上上班时间
declare @night2 as varchar(20) --晚上下班时间
declare @morring1B as varchar(20) --早上上班刷卡起
declare @morring2E as varchar(20) --早上下班刷卡止
declare @afternoon1B as varchar(20) --下午上班刷卡起
declare @afternoon2E as varchar(20) --下午下班刷卡止
declare @night1B as varchar(20) --晚上上班刷卡起
declare @night2E as varchar(20) --晚上上班刷卡止
declare @column as varchar(200) --列名
declare @ErrorTitle as varchar(80)
declare @bz as bit
declare @rowtimecount as int
select @Depno=cardbranchid from [HN_Employee] where userID=@userid
set @rowtimecount=(select COUNT(*) as con from [HN_KqTimeSet] where branchid=@Depno and years=year(@begin) and months=month(@begin))
if @rowtimecount>0
begin
select
@morring1= CONVERT(varchar(100),MB, 24) ,
@morring2=CONVERT(varchar(100),ME, 24) ,
@afternoon1=CONVERT(varchar(100),AB, 24),
@afternoon2=CONVERT(varchar(100),AE, 24) ,
@night1=CONVERT(varchar(100),NB, 24) ,
@night2=CONVERT(varchar(100),NE, 24),
@morring1B= CONVERT(varchar(100),MBup, 24) ,
@morring2E=CONVERT(varchar(100),MEdown, 24) ,
@afternoon1B=CONVERT(varchar(100),ABup, 24),
@afternoon2E=CONVERT(varchar(100),AEdown, 24) ,
@night1B=CONVERT(varchar(100),NBup, 24) ,
@night2E=CONVERT(varchar(100),NEdown, 24)
from [HN_KqTimeSet] where branchid=@Depno and years=year(@begin) and months=month(@begin)
set @begintime =CONVERT(varchar(100),@begin ,24) --取时间部分 08:00
set @endtime =CONVERT(varchar(100),@end ,24) --取时间部分 17:00
----***********------------- 0 标示请假
if @type=0
begin
set @ErrorTitle='正常'
set @bz=1
--判断请假开始时间是否在上班时间段内 自动规范请假时间
if ( (@begintime >= @morring1 and @begintime <@morring2) or (@begintime >= @afternoon1 and @begintime<@afternoon2) or (@begintime >= @night1 and @begintime< @night2) )
begin
set @begintime=@begintime
end
else
begin
set @ErrorTitle= '请假起始时间不在在正常上班时间段内,请更正!'
set @bz=0
end
if @bz=1
begin
if ( (@endtime > @morring1 and @endtime< =@morring2) or (@endtime>@afternoon1 and @endtime<= @afternoon2) or (@endtime> @night1 and @endtime<=@night2))
begin
set @endtime=@endtime
end
else
begin
set @ErrorTitle= '请假截止时间不在正常上班时间段内,请更正!'
set @bz=0
end
end
end
----------------------------------------------------------------------------------
----***********-------------1 标示加班
if @type=1
begin
set @ErrorTitle='正常'
set @begintime =CONVERT(varchar(100),@begin ,23) --取日期部分 2014-08-08
set @column='D'+convert(varchar(20),day(@begintime))
CREATE TABLE #KqDayJ(KqDayJ INT)
set @column=' INSERT INTO #KqDayJ(KqDayJ) select count(*) AS KqDayJ from [HN_KqDaySet] where branchid='+@Depno+' and years=year('''+@begintime+''') and months=month('''+@begintime+''') and '+@column+'=1'
exec (@column)
declare @KqDayJ as INT
SET @KqDayJ=(SELECT TOP 1 KqDayJ FROM #KqDayJ)
if @KqDayJ>0
begin
---正常上班的加班
set @bz=1
--判断加班开始时间是否在上班时间段内 自动规范请假时间
if ( ( @begintime <@morring1) or ( @begintime >=@morring2) or ( @begintime>=@afternoon2) or ( @begintime>= @night2) )
begin
set @begintime=@begintime
end
else
begin
--select @begintime
-- select @morring2
set @ErrorTitle= '加班起始时间不在在正常下班时间段内,请更正!'
set @bz=0
end
if @bz=1
begin
if ( ( @endtime <@morring1) or(@endtime>@morring2) or ( @endtime> @afternoon2) or (@endtime>@night2))
begin
set @endtime=@endtime
end
else
begin
set @ErrorTitle= '加班截止时间不在正常下班时间段内,请更正!'
set @bz=0
end
end
if @bz=1
begin
-- --判断加班开始时间是否在上班时间内
if ( (@begintime <@morring1 and @endtime>'00:00:00' and @endtime <= @morring1 ) or
(@begintime >=@morring2 and @begintime<@afternoon1 and @endtime >@morring2 and @endtime<=@afternoon1) or
(@begintime >=@afternoon2 and @begintime<@night1 and @endtime>@afternoon2 and @endtime<=@night1) or
(@begintime >=@night2 and @begintime<'23:59:59' and @endtime >@night2) )
begin
set @begintime=@begintime
set @endtime=@endtime
end
else
begin
set @ErrorTitle= '加班时间段不在下班时间段内,请更正!'
set @bz=0
end
end
end
end
DROP TABLE #KqDayJ
----------------------------------------------------------------------------------
----***********-------------2 标示补卡
if @type=2
begin
set @ErrorTitle='正常'
set @begintime =CONVERT(varchar(100),@begin ,23) --取日期部分 2014-08-08
set @endtime =CONVERT(varchar(100),@end ,24) --取时间部分 17:00
set @column='D'+convert(varchar(20),day(@begintime))
CREATE TABLE #KqDay(KqDay INT)
set @column=' INSERT INTO #KqDay(KqDay) select count(*) AS KqDay from [HN_KqDaySet] where branchid='+@Depno+' and years=year('''+@begintime+''') and months=month('''+@begintime+''') and '+@column+'=1'
exec (@column)
declare @KqDay as INT
SET @KqDay=(SELECT TOP 1 KqDay FROM #KqDay)
DROP TABLE #KqDay
--if @@error >0 or @@rowcount=1
if @KqDay>0
begin
if (@endtime between @morring1B and @morring1)
or (@endtime between @morring2 and @afternoon2E)
or (@endtime between @afternoon1B and @afternoon1)
or (@endtime between @afternoon2 and @afternoon2E)
or (@endtime between @night1B and @night1)
or (@endtime between @night2 and @night2E)
begin
set @begintime=@begintime
set @endtime=@endtime
end
else
begin
set @ErrorTitle='补卡时间 '+@endtime+' 不在正常刷卡时间范围内!'
end
end
else
begin
set @ErrorTitle='当前补卡日期 '+@begintime+'考勤天数表设置不用出勤,无需补卡,请更正!'
end
end
--------------------------------------------------------------------------------------
end
else
begin
set @ErrorTitle='当前时间年月,员工刷卡部门未设置有效考勤时间,请设置!'
end
select @ErrorTitle as ErrorTitle
