www.gusucode.com > 精典源码Delphi130：顺某指纹考勤管理系统 > 精典源码Delphi130：顺某指纹考勤管理系统/11065顺某指纹考勤管理系统delphi/SP_NoMiMa/wbSP_KQ_ZhengChang.txt
/****** Object: Stored Procedure dbo.wbSP_KQ_ZhengChang Script Date: 2000-03-22 12:27:47 ******/
/****** Object: Stored Procedure dbo.wbSP_KQ_ZhengChang Script Date: 2000-02-24 11:48:00 ******/
CREATE PROCEDURE wbSP_KQ_ZhengChang
@KQNO char(5),
@KQDate DateTime,--得到考勤当日的日期（无时间部分）
@isRGong bit, -- 是否人工补考勤
@RGKQDate DateTime,
@Result Int Output
AS
begin
Declare @tmpXBJieGuo char(20)
Declare @tmpZTTime Int
Declare @KQTime DateTime
Declare @KQYear int
Declare @KQMonth int
Declare @KQDay int
Declare @KQWeekDay int
Declare @KQHour int
Declare @KQMinute int
Declare @KQSecond int
declare @BMcode char(4) -- 部门代码
Declare @ZCBorDB Int -- 0:正常班 1:倒班
Declare @BCType char(6) -- 正常班,倒班
Declare @LXCode char(2) -- 班次类型代码
Declare @BCXuHao int -- 班次序号
Declare @YingDXS Int --应到时间
declare @YingDFZ int
Declare @CDTime Int --迟到分钟
Declare @ZTTime Int --早退分钟
Declare @KQJieGuo Char(20) --考勤结果
Declare @tmpSBKQJieGuo Char(20)
Declare @isNotKQ bit --当前班次是否考勤
declare @HaveorNot bit --倒班是否排班
--迟到、早退、旷工规定
Declare @gdCDFZ Int
Declare @isgdCD bit
declare @isgdSBKG bit
Declare @gdSBKGFZ int
Declare @gdZTFZ int
Declare @isgdZT bit
declare @isgdXBKG bit
Declare @gdXBKGFZ int
Declare @SBorXB Int --0: 上班、1：下班
Declare @isCRiXB bit -- 是否次日下班
Declare @WhatDayLX char(1) -- '0' 上日,'1' 当日, '2'下日的班次类型
Declare @QianRLXCode char(2) -- 前日排班类型
Declare @XiaRLXCode char(2) -- 下日排班类型
Declare @TmpRQ DateTime
Declare @tmpYear int
Declare @tmpMonth int
Declare @tmpDay int
Declare @isXBKQ bit --如下上班不考勤，则上班考勤时自动填上下班信息
Declare @XBTime Decimal(6,2) --下班应签到时间
Declare @isSBKQ bit --如上班不考勤，则下班不能认为旷工
Declare @SBTime decimal(6,2) --上班应签到时间
Declare @ShangR bit
Declare @CiR bit
Declare @DangRisCiR_XB bit
Declare @DangRisShangR_SB bit
Set @TmpRQ=@KQDate
Set @Result=0 -- 0 考勤成功
set @ShangR=0
set @CiR=0
--EEEEEnd 变量定义
--BBBBBegin 得到考勤日期
-- Get KQ Time
if @isRGong=1
begin
Set @KQTime=@RGKQDate
Set @KQYear=datepart(yy,@RGKQDate)
Set @KQMonth=datepart(mm,@RGKQDate)
Set @KQDay=datepart(dd,@RGKQDate)
Set @KQWeekDay=datepart(dw,@RGKQDate)
Set @KQHour=datepart(hh,@RGKQDate)
Set @KQMinute=datepart(mi,@RGKQDate)
Set @KQSecond=datepart(ss,@RGKQDate)
end
else
begin
Set @KQTime=GetDate()
Set @KQYear=datepart(yy,GetDate())
Set @KQMonth=datepart(mm,GetDate())
Set @KQDay=datepart(dd,GetDate())
Set @KQWeekDay=datepart(dw,GetDate())
Set @KQHour=datepart(hh,GetDate())
Set @KQMinute=datepart(mi,GetDate())
Set @KQSecond=datepart(ss,GetDate())
end
--EEEEEEEen 得到考勤日期
-- Get This Person BMCode
Set @BMcode=(Select BMCode from bYGongXX where KQNO=@KQNO)
--1. 判断是正常班或倒班
if Exists(Select KQNO from bYGongXX where KQNO=@KQNO and ZCBorDB="倒班")
Set @ZCBorDB=1
else
Set @ZCBorDB=0
set @WhatDayLX='1' -- 当日班次类型
set @DangRisCiR_XB=0
Set @DangRisShangR_SB=0
--BBBBBegin 一、************得到正常班的班次类型代码
--1. 如是正常班 判断是否公修日
if @ZCBorDB=0
begin
if Exists(Select BMCode from bGXR Where BMcode=@BMCode and WeekOrder=@KQWeekDay and isGXR=1) --是公休日
begin
-- 是否公休日列外
if not Exists(Select BMcode from bGXRLieWai where BMcode=@BMCode and DatePart(yy,LWaiRQ)=@KQYear and DatePart(mm,LWaiRQ)=@KQMonth and DatePart(dd,LWaiRQ)=@KQDay)
begin
Set @Result=1 -- 1. 公休日
return
end
else -- 是公休日列外 得到考勤班次类型代码
begin
-- 判断类型代码字段是否为空
if Exists (Select LXCode from bGXRLieWai where BMcode=@BMCode and DatePart(yy,LWaiRQ)=@KQYear and DatePart(mm,LWaiRQ)=@KQMonth and DatePart(dd,LWaiRQ)=@KQDay and LXCode is Not Null)
Set @LXCode=(Select LXCode from bGXRLieWai where BMcode=@BMCode and DatePart(yy,LWaiRQ)=@KQYear and DatePart(mm,LWaiRQ)=@KQMonth and DatePart(dd,LWaiRQ)=@KQDay)
else
begin
Set @Result=2 -- 2. 当日未排班
return
end
end end
else --不是公休日
begin
--3. 是否节假日
if Exists (Select JJRcode from bJJR where JJRYear=@KQYear and (@KQTime>= BeginDate) and (@KQTime<= EndDate+0.99999999))
begin
Set @Result=3 -- 3. 节假日
return
end
--不是公休和节假如 得到考勤班次类型代码
else
begin
-- 判断类型代码字段是否为空
if Exists (Select BCLXCode from bGXR where BMcode=@BMCode and WeekOrder=@KQWeekDay and BCLXCode is Not NUll)
Set @LXCode=(Select BCLXCode from bGXR where BMcode=@BMCode and WeekOrder=@KQWeekDay)
else
begin
Set @Result=2 -- 2. 当日未排班
return
end
end
end
end
--EEEEEnd 一、************得到正常班的班次类型代码
else
-- 二、************得到倒班的 "上日,当日,下日"的班次类型代码
begin
Set @QianRLXCode=''
Set @LXCode=''
Set @XiaRLXCode=''
-- 得到当日的班次类型代码
exec wbSp_GetDBRenY_BCLXCode @KQNO,@KQYear,@KQMonth,@KQDay,@HaveorNot output,@LXcode output
-- 得到前日的班次类型代码
Set @TmpRQ=@KQDate-1
Set @tmpYear=DatePart(yy,@TmpRQ)
Set @TmpMonth=Datepart(mm,@TmpRQ)
Set @TmpDay=datepart(dd,@TmpRQ)
exec wbSp_GetDBRenY_BCLXCode @KQNO,@tmpYear,@tmpMonth,@tmpDay,@HaveorNot output,@QianRLXCode output
-- 得到下日的班次类型代码
Set @TmpRQ=@KQDate+1
Set @tmpYear=DatePart(yy,@TmpRQ)
Set @TmpMonth=Datepart(mm,@TmpRQ)
Set @TmpDay=datepart(dd,@TmpRQ)
exec wbSp_GetDBRenY_BCLXCode @KQNO,@tmpYear,@tmpMonth,@tmpDay,@HaveorNot output,@XiaRLXCode output
-- 如前日,当日,下日都没有排班 退出
if @QianRLXCode='' and @LXCode='' and @XiaRLXCode=''
begin
Set @Result=2 -- 2. 当日未排班
return
end
end
--************结束得到倒班的班次类型代码
--四、判断是否有考勤班次或是否在考勤时间段内
--********正常班班次判断开始
if @ZCBorDB=0 -- 正常班
begin
--1. 先判断上班时间段
if Exists(Select BCXhuHao from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)
begin -- 得到该班次上班信息
Set @isNotKQ=(Select isBeginKQ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)
if @isNotKQ=0
begin
Set @Result=4 -- 4. 该班次上班不考勤
return
end
Set @SBorXB=0 --上班
Set @isXBKQ=(Select isEndKQ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)
Set @XBTime=(Select XBXS from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)
+(Select XBFZ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)/100.0
Set @BCXuHao=(Select BCXhuHao from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)
Set @YingDXS=(Select SBXS from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)
Set @YingDFZ=(Select SBFZ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and BCXhuHao is Not Null)
end
--2.判断下班时间段
else
begin
if Exists(Select BCXhuHao from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
begin -- 得到该班次下班信息
Set @isSBKQ=(Select isBeginKQ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
Set @SBTime=(Select SBXS from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
+(Select SBFZ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)/100.00
Set @isCRiXB=(Select isCiR from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
Set @isNotKQ=(Select isEndKQ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
if @isNotKQ=0
begin
Set @Result=6 -- 4. 该班次下班不考勤
return
end
Set @SBorXB=1 --下班
Set @BCXuHao=(Select BCXhuHao from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
Set @YingDXS=(Select XBXS from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
Set @YingDFZ=(Select XBFZ from bZCBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and BCXhuHao is Not Null)
end
else --找不到合适的班次
begin
Set @Result=5 -- 5. 此时不考勤或班次设置错误
return
end
end
end --********正常班班次判断结束
--********倒班班次班次判断开始
else
begin
set @WhatDayLX='1' -- 当日班次类型
Set @BCXuHao=-1 -- 不存在的班次序号
Set @SBorXB=0 --上班
-- 1. 先判断当日班次类型
if @LXCode<>''
begin
----上班时间段
--当日上班,且考勤开始时间在当日 ((SBXS*60+SBFZ) >= SBQFenZ) and isShangR=0
if Exists(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and ((SBXS*60+SBFZ) >= SBQFenZ) and isShangR=0 and BCXhuHao is Not Null)
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and ((SBXS*60+SBFZ) >= SBQFenZ) and isShangR=0 and BCXhuHao is Not Null)
--当日上班,且考勤开始时间在上日 ((SBXS*60+SBFZ) < SBQFenZ) and isShangR=0
else if Exists(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) < (SBXS*60+SBFZ+SBHFenZ) and ((SBXS*60+SBFZ) < SBQFenZ) and isShangR=0 and BCXhuHao is Not Null)
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) < (SBXS*60+SBFZ+SBHFenZ) and ((SBXS*60+SBFZ) < SBQFenZ) and isShangR=0 and BCXhuHao is Not Null)
--上日上班,截止考勤时间在当日 (24*60-(SBXS*60+SBFZ+SBHFenZ)) >=0 and isShangR=1
else if Exists(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) < ((SBXS*60+SBFZ+SBHFenZ)-24*60) and isShangR=1 and BCXhuHao is Not Null)
begin
Set @DangRisShangR_SB=1
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) < ((SBXS*60+SBFZ+SBHFenZ)-24*60) and isShangR=1 and BCXhuHao is Not Null)
end
---- 下班时间段
-- 当日下班,且下班截止时间在当日((XBXS*60+XBFZ+XBHFenZ)<24*60) and isCiR=0
else if Exists(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and ((XBXS*60+XBFZ+XBHFenZ)<24*60) and isCiR=0 and BCXhuHao is Not Null)
begin
Set @SBorXB=1 --下班
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and (XBXS*60+XBFZ+XBHFenZ) and ((XBXS*60+XBFZ+XBHFenZ)<24*60) and isCiR=0 and BCXhuHao is Not Null)
end
-- 当日下班,且下班截止时间在次日((XBXS*60+XBFZ+XBHFenZ)>=24*60)>=24*60) and isCiR=0
else if Exists(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and 24*60 and ((XBXS*60+XBFZ+XBHFenZ)>=24*60) and isCiR=0 and BCXhuHao is Not Null)
begin
Set @SBorXB=1 --下班
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and 24*60 and ((XBXS*60+XBFZ+XBHFenZ)>=24*60) and isCiR=0 and BCXhuHao is Not Null)
end
-- 次日下班,且下班开始时间在当日 isCiR=1 and XBXS*60+XBFZ<XBQFenZ
else if Exists(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (24*60-(XBQFenZ-(XBXS*60+XBFZ))) and 24*60 and (XBQFenZ-(XBXS*60+XBFZ))>0 and isCiR=1 and BCXhuHao is Not Null)
begin
set @DangRisCiR_XB=1
Set @SBorXB=1 --下班
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@LXcode and (@KQHour*60+@KQMinute) Between (XBXS*60+XBFZ-XBQFenZ) and 24*60 and isCiR=1 and BCXhuHao is Not Null)
end
else
Set @BCXuHao=-1 -- 不存在的班次序号
end
-- 2. 其次判断下日班次类型的上班
if @XiaRLXCode<>'' and @BCXuHao=-1
begin
set @WhatDayLX='2' -- 次日的班次类型
----上班时间段
--当日上班,且考勤开始时间在上日 ((SBXS*60+SBFZ) < SBQFenZ) and isShangR=0
if Exists(Select BCXhuHao from bDBBC where LXcode=@XiaRLXCode and (@KQHour*60+@KQMinute) >=(24*60-(SBQFenZ-(SBXS*60+SBFZ))) and ((SBXS*60+SBFZ) < SBQFenZ) and isShangR=0 and BCXhuHao is Not Null)
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@XiaRLXCode and (@KQHour*60+@KQMinute) >=(24*60-(SBQFenZ-(SBXS*60+SBFZ))) and ((SBXS*60+SBFZ) < SBQFenZ) and isShangR=0 and BCXhuHao is Not Null)
--上日上班,考勤截止时间在当日 ((SBXS*60+SBFZ+SBHFenZ)>=24*60) and isShangR=1
else if Exists(Select BCXhuHao from bDBBC where LXcode=@XiaRLXCode and (@KQHour*60+@KQMinute) >=(SBXS*60+SBFZ-SBQFenZ) and ((SBXS*60+SBFZ+SBHFenZ)>=24*60) and isShangR=1 and BCXhuHao is Not Null)
begin
set @ShangR=1
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@XiaRLXCode and (@KQHour*60+@KQMinute) >=(SBXS*60+SBFZ-SBQFenZ) and ((SBXS*60+SBFZ+SBHFenZ)>=24*60) and isShangR=1 and BCXhuHao is Not Null)
end
--上日上班,考勤截止时间在上日 ((SBXS*60+SBFZ+SBHFenZ)<24*60) and isShangR=1
else if Exists(Select BCXhuHao from bDBBC where LXcode=@XiaRLXCode and (@KQHour*60+@KQMinute) between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and ((SBXS*60+SBFZ+SBHFenZ)<24*60) and isShangR=1 and BCXhuHao is Not Null)
begin
set @ShangR=1
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@XiaRLXCode and (@KQHour*60+@KQMinute) between (SBXS*60+SBFZ-SBQFenZ) and (SBXS*60+SBFZ+SBHFenZ) and ((SBXS*60+SBFZ+SBHFenZ)<24*60) and isShangR=1 and BCXhuHao is Not Null)
end
else
Set @BCXuHao=-1 -- 不存在的班次序号
end
-- 3. 最后判断上日班次类型的下班
if @QianRLXCode<>'' and @BCXuHao=-1
begin
set @WhatDayLX='0' -- 上日的班次类型
Set @SBorXB=1 --下班
---- 下班时间段
-- 当日下班,且下班截止时间在次日((XBXS*60+XBFZ+XBHFenZ)>=24*60) and isCiR=0
if Exists(Select BCXhuHao from bDBBC where LXcode=@QianRLXCode and (@KQHour*60+@KQMinute) <=((XBXS*60+XBFZ+XBHFenZ)-24*60) and ((XBXS*60+XBFZ+XBHFenZ)>=24*60) and isCiR=0 and BCXhuHao is Not Null)
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@QianRLXCode and (@KQHour*60+@KQMinute) <=((XBXS*60+XBFZ+XBHFenZ)-24*60) and ((XBXS*60+XBFZ+XBHFenZ)>=24*60) and isCiR=0 and BCXhuHao is Not Null)
-- 次日下班 isCiR=1
else if Exists(Select BCXhuHao from bDBBC where LXcode=@QianRLXCode and (@KQHour*60+@KQMinute) <=(XBXS*60+XBFZ+XBHFenZ) and isCiR=1 and BCXhuHao is Not Null)
begin
Set @CiR=1
Set @BCXuHao=(Select BCXhuHao from bDBBC where LXcode=@QianRLXCode and (@KQHour*60+@KQMinute) <=(XBXS*60+XBFZ+XBHFenZ) and isCiR=1 and BCXhuHao is Not Null)
end
else
Set @BCXuHao=-1 -- 不存在的班次序号
end
if @BCXuHao=-1 --找不到合适的班次
begin
Set @Result=5 -- 5. 此时不考勤或班次设置错误
return
end
if @WhatDayLX='0'
set @LXCode=@QianRLXCode
if @WhatDayLX='2'
set @LXCode=@XiaRLXCode
if @SBorXB=0 --上班 -- 得到该班次上班信息
begin
Set @isNotKQ=(Select isBeginKQ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
if @isNotKQ=0
begin
Set @Result=4 -- 4. 该班次上班不考勤
return
end
Set @isXBKQ=(Select isEndKQ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
Set @XBTime=(Select XBXS from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
+(Select XBFZ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)/100.00
Set @YingDXS=(Select SBXS from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
Set @YingDFZ=(Select SBFZ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
end
else --得到该班次下班信息
begin
Set @isSBKQ=(Select isBeginKQ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
Set @SBTime=(Select SBXS from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
+(Select SBFZ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)/100.00
Set @isNotKQ=(Select isEndKQ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
if @isNotKQ=0
begin
Set @Result=6 -- 6. 该班次下班不考勤
return
end
Set @YingDXS=(Select XBXS from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
Set @YingDFZ=(Select XBFZ from bDBBC where LXcode=@LXcode and BCXhuHao=@BCXuHao)
end
end --********倒班班次班次判断结束
--五、得到各种上班、下班、旷工规定
Set @gdCDFZ=(Select ShuZ from bCD_ZT_GuiD where GDCode='01')
set @isgdCD=(Select isSheZ from bCD_ZT_GuiD where GDCode='01')
set @isgdSBKG=(Select isSheZ from bCD_ZT_GuiD where GDCode='02')
set @gdSBKGFZ=(Select ShuZ from bCD_ZT_GuiD where GDCode='02')
set @gdZTFZ=(Select ShuZ from bCD_ZT_GuiD where GDCode='03')
set @isgdZT=(Select isSheZ from bCD_ZT_GuiD where GDCode='03')
set @isgdXBKG=(Select isSheZ from bCD_ZT_GuiD where GDCode='04')
Set @gdXBKGFZ=(Select ShuZ from bCD_ZT_GuiD where GDCode='04')
if @WhatDayLX='2' and @ShangR=0
begin
set @gdCDFZ=@gdCDFZ+24*60
Set @gdSBKGFZ=@gdSBKGFZ+24*60
end
if @WhatDayLX='0' and @CiR=0
begin
set @gdZTFZ=@gdztFZ+24*60
Set @gdxbKGFZ=@gdxBKGFZ+24*60
end
if @WhatDayLX='1' and @DangRisCiR_XB=1
begin
set @gdZTFZ=@gdztFZ-24*60
Set @gdxbKGFZ=@gdxBKGFZ-24*60
end
if @WhatDayLX='1' and @DangRisShangR_SB=1
begin
set @gdCDFZ=@gdCDFZ-24*60
Set @gdSBKGFZ=@gdSBKGFZ-24*60
end
--六、处理在考勤原始记录表(bKQJL)
--1.得到表各字段的数值
if @ZCBorDB=0
set @BCType='固定班'
else
set @BCType='倒班'
-- 得到实际建立考勤记录的日期
if @WhatDayLX='0'
set @KQDate=@KQDate-1
if @WhatDayLX='2'
set @KQDate=@KQDate+1
if @SBorXB=0 --上班
begin
--1. 判断是否存在此班次记录
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao) and @isRgong=0
begin
Set @Result=7 --7: 该班次上班已经考勤
return
end
-- 人工补考勤
else if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao and YQDTime is null) and @isRgong=1
begin
--???????????
-- 得到考勤结果
Set @KQJieGuo='正常出勤'
if @isgdCD=1 -- 有迟到规定
begin
if (@KQHour*60+@KQMinute)<=(@YingDXS*60+@YingDFZ+@gdCDFZ)
Set @KQJieGuo='正常出勤'
else
begin
Set @KQJieGuo='迟到'
Set @CDTime=(@KQHour*60+@KQMinute)-(@YingDXS*60+@YingDFZ+@gdCDFZ)
Set @Result=8 --8:迟到
end
end -- end 有迟到规定
if @isgdSBKG=1 -- 有上班旷工规定
begin
if (@KQHour*60+@KQMinute)>=(@YingDXS*60+@YingDFZ+@gdSBKGFZ)
begin
Set @KQJieGuo='旷工'
Set @CDTime=(@KQHour*60+@KQMinute)-(@YingDXS*60+@YingDFZ+@gdCDFZ)
Set @Result=9 --9:'上班旷工'
end
end -- end 有上班旷工规定
Set @tmpXBJieGuo=(Select BCJGcode from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao and YQDTime is null)
Set @tmpZTTime=(Select ZTTime from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao and YQDTime is null)
if @tmpZTTime=null
begin
if @KQJieGuo='正常出勤'
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=NULL,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
else -- 下班为有早退时间
begin
if @KQJieGuo='旷工'
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else if @KQJieGuo='迟到'
begin
if @isgdXBKG=1 and @tmpZTTime>=@gdXBKGFZ -- 下班旷工
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else if @isgdZT=1 and @tmpZTTime>=@gdZTFZ -- 下班早退
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime,BCJGcode='迟到、早退' where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime,BCJGcode='迟到' where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
else -- 上班正常
begin
if @isgdXBKG=1 and @tmpZTTime>=@gdXBKGFZ -- 下班旷工
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else if @isgdZT=1 and @tmpZTTime>=@gdZTFZ -- 下班早退
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime,BCJGcode='早退' where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQDtime=@YingDXS+@YingDFZ/100.00,SJQDtime=NULL,CDTime=@CDTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
end
--??????????
end
else if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao and YQDTime is not null) and @isRgong=1
begin
Set @Result=7 --7: 该班次上班已经考勤
return
end
else
begin
--2. 得到考勤结果
Set @KQJieGuo='正常出勤'
if @isgdCD=1 -- 有迟到规定
begin
if (@KQHour*60+@KQMinute)<=(@YingDXS*60+@YingDFZ+@gdCDFZ)
Set @KQJieGuo='正常出勤'
else
begin
Set @KQJieGuo='迟到'
Set @CDTime=(@KQHour*60+@KQMinute)-(@YingDXS*60+@YingDFZ+@gdCDFZ)
Set @Result=8 --8:迟到
end
end -- end 有迟到规定
if @isgdSBKG=1 -- 有上班旷工规定
begin
if (@KQHour*60+@KQMinute)>=(@YingDXS*60+@YingDFZ+@gdSBKGFZ)
begin
Set @KQJieGuo='旷工'
Set @CDTime=(@KQHour*60+@KQMinute)-(@YingDXS*60+@YingDFZ+@gdCDFZ)
Set @Result=9 --9:'上班旷工'
end
end -- end 有上班旷工规定
--3.新增加上班考勤记录
if @KQJieGuo<>'正常出勤' --有迟到时间
begin
if @isXBKQ=1
begin
if @isRGong=1 -- set SJQDtime=NULL 便于注释和考勤分开
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,CDTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,NULL,@CDTime,@KQJieGuo)
else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,CDTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@CDTime,@KQJieGuo)
end
else -- 下班不考勤，把下班时间天上
begin
if @isRGong=1 -- set SJQDtime=NULL 便于注释和考勤分开 Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,YQTTime,SJQTTime,CDTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,NULL,@XBTime,@XBTime,@CDTime,@KQJieGuo)
else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,YQTTime,SJQTTime,CDTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@XBTime,@XBTime,@CDTime,@KQJieGuo) end
end
else
begin
if @isXBKQ=1
begin
if @isRGong=1 -- set SJQDtime=NULL 便于注释和考勤分开
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,NULL,@KQJieGuo) else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@KQJieGuo)
end
else
begin
if @isRGong=1 -- set SJQDtime=NULL 便于注释和考勤分开
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,YQTTime,SJQTTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,NULL,@XBTime,@XBTime,@KQJieGuo)
else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDtime,SJQDtime,YQTTime,SJQTTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@XBTime,@XBTime,@KQJieGuo)
end
end
Return -- 结束考勤
end
end --end 上班
else
begin -- 下班
--1. 判断是否存在此班次下班记录
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao and YQTTime is not null)
begin
Set @Result=12 --12: 该班次下班已经考勤
return
end
else
begin
--2. 得到考勤结果
Set @KQJieGuo='正常出勤'
if @isgdZT=1 -- 有早退规定
begin
if (@KQHour*60+@KQMinute)>=(@YingDXS*60+@YingDFZ-@gdZTFZ)
Set @KQJieGuo='正常出勤'
else
begin
Set @KQJieGuo='早退'
Set @ZTTime=(@YingDXS*60+@YingDFZ-@gdZTFZ)-(@KQHour*60+@KQMinute)
Set @Result=10 --10:迟到
end
end -- end 有早退规定
if @isgdXBKG=1 -- 有下班旷工规定
begin
if (@KQHour*60+@KQMinute)<=(@YingDXS*60+@YingDFZ-@gdXBKGFZ)
begin
Set @KQJieGuo='旷工'
Set @ZTTime=(@YingDXS*60+@YingDFZ-@gdZTFZ)-(@KQHour*60+@KQMinute)
Set @Result=11 --11:下班旷工'
end
end -- end 有下班旷工规定
--3.如无上班记录新增加下班考勤记录
if not Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao)
begin
IF @isSBKQ=1 -- 上班要考勤
begin
Set @KQJieGuo='旷工'
if @KQJieGuo<>'正常出勤' --有早退时间
begin
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQTTime,SJQTtime,ZTTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,NULL,@ZTtime,@KQJieGuo)
else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQTTime,SJQTtime,ZTTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@ZTtime,@KQJieGuo)
end
else
begin
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,SJQDTime,YQTtime,SJQTtime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@SBTime,@SBTime,@YingDXS+@YingDFZ/100.00,NULL,@KQJieGuo) else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,SJQDTime,YQTtime,SJQTtime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@SBTime,@SBTime,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@KQJieGuo)
end
end
else --上班不考勤
begin
if @KQJieGuo<>'正常出勤' --有早退时间
begin
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,SJQDTime,YQTtime,SJQTtime,ZTTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@SBTime,@SBTime,@YingDXS+@YingDFZ/100.00,NULL,@ZTtime,@KQJieGuo)
else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,SJQDTime,YQTtime,SJQTtime,ZTTime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@SBTime,@SBTime,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@ZTtime,@KQJieGuo)
end
else
begin
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,SJQDTime,YQTtime,SJQTtime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@SBTime,@SBTime,@YingDXS+@YingDFZ/100.00,NULL,@KQJieGuo)
else
Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,SJQDTime,YQTtime,SJQTtime,BCJGCode) values(@KQNO,@KQDate,@BCType,@LXcode,@BCXuHao,@SBTime,@SBTime,@YingDXS+@YingDFZ/100.00,@KQHour+@KQMinute/100.00,@KQJieGuo)
end
end
end
else --更新此班次的下班考勤信息
begin
if @KQJieGuo<>'正常出勤' --有早退时间或下班旷工
begin
if @KQJieGuo='旷工'
begin
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=NULL,ZTTime=@ZTTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=@KQHour+@KQMinute/100.00,ZTTime=@ZTTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
else --根据上班考勤结果定
begin Set @tmpSBKQJieGuo=(Select BCJGcode from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao)
if @tmpSBKQJieGuo='旷工' --上班旷工显示为旷工
begin if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=NULL,ZTTime=@ZTTime where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=@KQHour+@KQMinute/100.00,ZTTime=@ZTTime where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
else if @tmpSBKQJieGuo='迟到' --为迟到、早退
begin
Set @KQJieGuo='迟到、早退'
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=NULL,ZTTime=@ZTTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=@KQHour+@KQMinute/100.00,ZTTime=@ZTTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
else --为早退
begin
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=NULL,ZTTime=@ZTTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=@KQHour+@KQMinute/100.00,ZTTime=@ZTTime,BCJGcode=@KQJieGuo where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
end
end
else
begin
if @isRGong=1 -- set SJQTtime=NULL 便于注释和考勤分开
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=NULL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
else
Update bKQJL Set YQTtime=@YingDXS+@YingDFZ/100.00,SJQTtime=@KQHour+@KQMinute/100.00 where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXuHao
end
end
end
end -- end 下班
-- @KQJieGuo--考勤结果
return
end