gusucode.com > 精典源码Delphi044：指纹考勤管理系统源码程序 > 精典源码Delphi044：指纹考勤管理系统源码程序/精典源码Delphi044：指纹考勤管理系统/顺发指纹考勤管理系统/指纹考勤系统/SP_NoMiMa/wbSP_KQ_HuiZhong.txt
/****** Object: Stored Procedure dbo.wbSP_KQ_HuiZhong Script Date: 2000-03-22 12:27:47 ******/
/****** Object: Stored Procedure dbo.wbSP_KQ_HuiZhong Script Date: 2000-02-24 11:48:00 ******/
CREATE PROCEDURE wbSP_KQ_HuiZhong
@KQNO Char(5),
@HZYear Int,
@HZMonth Int,
@HZBeginDay int,
@HZEndDay int,
@HZBeginDate DateTime,
@isUpdate bit
AS
begin
Declare @HZDate DateTime
Declare @CirCount Int
Declare @ZCBorDB Char(6) -- 正常班 或倒班
Declare @BCLXCode char(2)
Declare @BCXH int
Declare @isSBKQ bit
Declare @isXBKQ bit
Declare @SBTime Decimal(6,2)
Declare @XBTime Decimal(6,2)
Declare @CDTime int
Declare @ZTTime int
Declare @KQJieGuo char(20)
Declare @KQJieGuoBJ Char(2)
Declare @QJCode char(2)
Declare @WQCode Char(2)
Declare @BMCode char(4)
Declare @WeekDay int
Declare @BeginKQDate DateTime
Declare @isDBPaiB bit
Declare @dbPBLXcode char(2)
--开始日期循环
Set @CirCount=@HZBeginDay
Set @HZDate=@HZBeginDate
Set @BMCode=(Select BMCode from bYGongXX where KQNO=@KQNO)
while @CirCount <=@HZEndDay
begin
Set @WeekDay=DatePart(dw,@HZDate)
Set @SBTime =null
set @XBTime =NUll
Set @CDTime =Null
Set @ZTTime =Null
Set @KQJieGuo =Null
Set @KQJieGuoBJ =Null
---^^^^^^^^^ insert into bbb(KQNO,KQDate) values(@KQNO,@HZDate)
-- 一.考勤原始记录中是否存在当日此人的考勤记录
if Exists (Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate)
--????????????????************ 有 考勤原始记录中是否存在当日此人的考勤记录
begin
--1. 得到其当日班次类型
Set @BCLXCode=(Select Top 1 BCCode from bKQJL where KQNO=@KQNO and KQDate=@HZDate order by BCXH)
Set @ZCBorDB=(Select Top 1 BCType from bKQJL where KQNO=@KQNO and KQDate=@HZDate order by BCXH)
--2. 判断各班次当日是否有考勤记录
-- &&&&&&&&&&&正常班
if @ZCBorDB='固定班'
begin
--定义正常班各班次光标
Declare ZCBEveryBCXH CURSOR FOR
Select BCXhuHao,isBeginKQ,isEndKQ from bZCBBC where LXCode =@BCLXCode
OPEN ZCBEveryBCXH
Fetch Next From ZCBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
while (@@FETCH_STATUS=0)
begin
Set @SBTime =null
set @XBTime =NUll
Set @CDTime =Null
Set @ZTTime =Null
Set @KQJieGuo =Null
Set @KQJieGuoBJ =Null
-- 该班次是否有考勤原始记录
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
begin --有
-- 得到上班、下班时间
Set @SBTime=(Select top 1 SJQDTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @XBTime=(Select top 1 SJQTTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @CDTime=(Select top 1 CDTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @ZTTime=(Select top 1 ZTTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
--得到考勤结果和考勤结果标记
-- 是否有缺时
if Not Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Null) or (YQTTime is Null)))
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
--上班缺时
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Null) and (YQTTime is Not Null)))
begin
if @isSBKQ=1
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin Set @KQJieGuo=(Select top 1 BCJGCode from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
--下班缺时
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Not Null) and (YQTTime is Null)))
begin
if @isXBKQ=1
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
end
end -- end 有
else
-- 该班次无考勤原始记录
begin -- 无
--++++++++++++++
-- 是否公休日
if Exists(Select BMCode from bGXR where WeekOrder=@WeekDay and BMCode=@BMCode and isGXR=1 )
begin
if Exists(Select BMCode from bGXRLieWai where BMCode=@BMCode and LWaiRQ=@HZDate)
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
--有无请假跨越节假日记录
if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and QJcode in(Select QJcode from bQJLX where isKYJJR=0))
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出跨越节假日记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and WQcode in(Select WQcode from bWQLX where isKYJJR=0))
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
else
begin
Set @KQJieGuo='公休日'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
end
--是否节假日
else if Exists(Select JJRCode from bJJR where JJRYear= @HZYear and @HZDate Between BeginDate and EndDate)
begin
--有无请假跨越节假日记录
if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and QJcode in(Select QJcode from bQJLX where isKYJJR=0))
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出跨越节假日记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and WQcode in(Select WQcode from bWQLX where isKYJJR=0))
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
else
begin
Set @KQJieGuo='节假日'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
--++++++++++++++
--有无请假记录
-- 按天为单位
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
-- 按班次为单位
--1.请假在同一天结束
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--2.时间段中间
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime)
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--3.请假第一天
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH) --请假第一天
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--4.--请假最后一天
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate =EndTime and @HZDate> BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate =EndTime and @HZDate> BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
-- 按班次维护单位
--1.中间天
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime )
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--2.外出在同一天结束
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--3.外出第一天
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZdate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH)
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZdate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--4.外出最后一天
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate =EndTime and @HZDate>BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate =EndTime and @HZDate>BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--++++++++++++++
else
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end --eND 无
-- End 该班次无考勤原始记录
-- Insert or Update 考勤结果表
exec wbSP_AddorUpdateKQJieGuo @KQNO,@HZdate,@HZYear,@HZMonth,@CirCount,@ZCBorDB,@BCLXCode,@BCXH,@SBtime,@XBtime,@CDTime,@ZTTime,@KQJieGuo,@KQJieGuoBJ,@isUpdate
Fetch Next From ZCBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
end -- end Curror While (@@FETCH_STATUS=0)
Close ZCBEveryBCXH
Deallocate ZCBEveryBCXH
end -- end 正常班
-- &&&&&&&&&&& end 正常班
else
-- %%%%%%%% Begin 倒班
begin --定义倒班各班次光标
Declare DBEveryBCXH CURSOR FOR
Select BCXhuHao,isBeginKQ,isEndKQ from bDBBC where LXCode =@BCLXCode
OPEN DBEveryBCXH
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
while (@@FETCH_STATUS=0)
begin
Set @SBTime =null
set @XBTime =NUll
Set @CDTime =Null
Set @ZTTime =Null
Set @KQJieGuo =Null
Set @KQJieGuoBJ =Null
-- 该班次是否有考勤原始记录
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
begin --有
-- 得到上班、下班时间
Set @SBTime=(Select top 1 SJQDTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @XBTime=(Select top 1 SJQTTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @CDTime=(Select top 1 CDTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @ZTTime=(Select top 1 ZTTime from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
--得到考勤结果和考勤结果标记
-- 是否有缺时
if Not Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Null) or (YQTTime is Null)))
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
--上班缺时
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Null) and (YQTTime is Not Null)))
begin
if @isSBKQ=1
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
--下班缺时
if Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Not Null) and (YQTTime is Null)))
begin
if @isXBKQ=1
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bKQJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
end
end -- end 有
else
-- 该班次无考勤原始记录
begin -- 无
--是否有调班
if Exists(Select BeiKQNO from bTiaoBanJL where BeiKQNO=@KQNO and KQDate=@HZDate and BCcode=@BCLXcode and BCXH=@BCXH)
begin
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
Continue
end
--有无请假记录
if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--是否节假日
else if Exists(Select JJRCode from bJJR where JJRYear= @HZYear and @HZDate Between BeginDate and EndDate)
begin
Set @KQJieGuo='节假日' exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end -- 当日是否排班
else
begin
exec wbSp_GetDBRenY_BCLXCode @KQNO,@HZYear,@HZMonth,@CirCount,@isDBPaiB Output,@dbPBLXcode Output
if @isDBPaiB=1
begin
--有无请假记录
if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
else
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
else
begin
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
Continue
end
end
end --eND 无
-- End 该班次无考勤原始记录
-- Insert or Update 考勤结果表
exec wbSP_AddorUpdateKQJieGuo @KQNO,@HZdate,@HZYear,@HZMonth,@CirCount,@ZCBorDB,@BCLXCode,@BCXH,@SBtime,@XBtime,@CDTime,@ZTTime,@KQJieGuo,@KQJieGuoBJ,@isUpdate
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
end -- end Curror While (@@FETCH_STATUS=0)
Close DBEveryBCXH
Deallocate DBEveryBCXH end -- end 倒班
-- %%%%%%%% end 倒班
end -- end 有 考勤原始记录中是否存在当日此人的考勤记录
--????????????***********end 有 考勤原始记录中是否存在当日此人的考勤记录
else
--########### 无 考勤原始记录中是否存在当日此人的考勤记录
begin
--是否开始考勤时间后
Set @BeginKQDate=(Select KQBeginDate from bYGongXX Where KQNO=@KQNO)
if @BeginKQDate>@HZDate
begin
Set @CirCount=@CirCount+1
Set @HZDate=@HZDate+1
continue -- 结束此日的处理
end
--2. 判断此人是正常班或倒班
Set @ZCBorDB=(Select ZCBorDB from bYGongXX where KQNO=@KQNO)
Set @BMCode=(Select BMCode from bYGongXX where KQNO=@KQNO)
--3 处理倒班人员
if @ZCBorDB='倒班'
begin
-- 是否排班
exec wbSp_GetDBRenY_BCLXCode @KQNO,@HZYear,@HZMonth,@CirCount,@isDBPaiB Output,@BCLXcode Output
if @isDBPaiB=1
begin
--判断各班次是否免勤
--定义倒班各班次光标
Declare DBEveryBCXH CURSOR FOR
Select BCXhuHao,isBeginKQ,isEndKQ from bDBBC where LXCode =@BCLXCode
OPEN DBEveryBCXH
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
while (@@FETCH_STATUS=0)
begin
Set @SBTime =null
set @XBTime =NUll
Set @CDTime =Null
Set @ZTTime =Null
Set @KQJieGuo =Null
Set @KQJieGuoBJ =Null
--1. 判断此人是否有调班记录
if Exists(Select BeiKQNO from bTiaoBanJL where BeiKQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
begin
Set @KQJieGuo='被调班'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else if (@isSBKQ=0) and (@isXBKQ=0)
begin
Set @KQJieGuo='免勤'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
--------
--有无请假记录
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--------
else
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
-- Insert or Update 考勤结果表
exec wbSP_AddorUpdateKQJieGuo @KQNO,@HZdate,@HZYear,@HZMonth,@CirCount,@ZCBorDB,@BCLXCode,@BCXH,@SBtime,@XBtime,@CDTime,@ZTTime,@KQJieGuo,@KQJieGuoBJ,@isUpdate
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
end -- end Curror While (@@FETCH_STATUS=0)
Close DBEveryBCXH
Deallocate DBEveryBCXH
end
else --未排班
begin
--1. 判断此人是否有调班记录
if Exists(Select BeiKQNO from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate)
begin
set @BCLXCode=(Select top 1 BCCode from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate)
--定义倒班各班次光标
Declare DBEveryBCXH CURSOR FOR Select BCXhuHao,isBeginKQ,isEndKQ from bDBBC where LXCode =@BCLXCode
OPEN DBEveryBCXH
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
while (@@FETCH_STATUS=0)
begin
Set @SBTime =null
set @XBTime =NUll
Set @CDTime =Null
Set @ZTTime =Null
Set @KQJieGuo =Null
Set @KQJieGuoBJ =Null
-- 该班次是否有考勤原始记录
if Exists(Select KQNO from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
begin --有
-- 得到上班、下班时间
Set @SBTime=(Select top 1 SJQDTime from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @XBTime=(Select top 1 SJQTTime from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @CDTime=(Select top 1 CDTime from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
Set @ZTTime=(Select top 1 ZTTime from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
--得到考勤结果和考勤结果标记
-- 是否有缺时
if Not Exists(Select KQNO from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Null) or (YQTTime is Null)))
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
--上班缺时
if Exists(Select KQNO from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Null) and (YQTTime is Not Null)))
begin
if @isSBKQ=1
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
--下班缺时
if Exists(Select KQNO from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH and ((YQDTime is Not Null) and (YQTTime is Null)))
begin
if @isXBKQ=1
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
Set @KQJieGuo=(Select top 1 BCJGCode from bTiaoBanJL where KQNO=@KQNO and KQDate=@HZDate and BCCode=@BCLXCode and BCXH=@BCXH)
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
end
end -- end 有
-- Insert or Update 考勤结果表
exec wbSP_AddorUpdateKQJieGuo @KQNO,@HZdate,@HZYear,@HZMonth,@CirCount,@ZCBorDB,@BCLXCode,@BCXH,@SBtime,@XBtime,@CDTime,@ZTTime,@KQJieGuo,@KQJieGuoBJ,@isUpdate
Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
end -- end Curror While (@@FETCH_STATUS=0)
Close DBEveryBCXH
Deallocate DBEveryBCXH
end
else
begin
Set @KQJieGuo='未排班'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
end -- end 处理倒班人员
--4 处理正常班人员
else
begin
-- 得到该部门当日排班
if not Exists (Select BCLXCode from bGXR where BMcode=@BMCode and WeekOrder=@WeekDay and ((BCLXcode is not null) or(BCLXCode='')))
begin
Set @CirCount=@CirCount+1
Set @HZDate=@HZDate+1
Continue -- 如未排班结束此日处理
end
else
begin
Set @BCLXCode=(Select top 1 BCLXCode from bGXR where BMcode=@BMCode and WeekOrder=@WeekDay and BCLXcode is not null)
--定义正常班各班次光标
Declare ZCBEveryBCXH CURSOR FOR
Select BCXhuHao,isBeginKQ,isEndKQ from bZCBBC where LXCode =@BCLXCode
OPEN ZCBEveryBCXH
Fetch Next From ZCBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
while (@@FETCH_STATUS=0)
begin
Set @SBTime =null
set @XBTime =NUll
Set @CDTime =Null
Set @ZTTime =Null
Set @KQJieGuo =Null
Set @KQJieGuoBJ =Null
-- 是否公休日
if Exists(Select BMCode from bGXR where WeekOrder=@WeekDay and BMCode=@BMCode and isGXR=1 )
begin
if Exists(Select BMCode from bGXRLieWai where BMCode=@BMCode and LWaiRQ=@HZDate)
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
--有无请假跨越节假日记录
if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and QJcode in(Select QJcode from bQJLX where isKYJJR=0))
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出跨越节假日记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and WQcode in(Select WQcode from bWQLX where isKYJJR=0))
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
else
begin
Set @KQJieGuo='公休日'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
end
--是否节假日
else if Exists(Select JJRCode from bJJR where JJRYear= @HZYear and @HZDate Between BeginDate and EndDate)
begin
--有无请假跨越节假日记录
if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and QJcode in(Select QJcode from bQJLX where isKYJJR=0))
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出跨越节假日记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and WQcode in(Select WQcode from bWQLX where isKYJJR=0))
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
else
begin
Set @KQJieGuo='节假日'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
--++++++++++++++
--有无请假记录
-- 按天为单位
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
-- 按班次为单位
--1.请假在同一天结束
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--2.时间段中间
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime)
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--3.请假第一天
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH) --请假第一天
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate =BeginTime and @HZDate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--4.--请假最后一天
else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @HZDate =EndTime and @HZDate> BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
begin
Set @QJCode=(Select top 1 QJCode from bQingJ where KQNO=@KQNO and @HZDate =EndTime and @HZDate> BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
Set @KQJieGuo=(Select QJName from bQJLX where QJCode= @QJCode)
Set @KQJieGuoBJ=(Select KQBJ from bQJLX where QJCode= @QJCode)
end
--有无外出记录
else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime and DWei='天')
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate Between BeginTime and EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
-- 按班次维护单位
--1.中间天
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime )
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate >BeginTime and @HZDate<EndTime)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--2.外出在同一天结束
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZDate=EndTime and DWei='班次' and LXCode=@BCLXCode and ( @BCXH between BeginBCXH and EndBCXH))
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--3.外出第一天
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZdate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH)
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate =BeginTime and @HZdate<EndTime and DWei='班次' and LXCode=@BCLXCode and @BCXH>=BeginBCXH)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--4.外出最后一天
else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @HZDate =EndTime and @HZDate>BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
begin
Set @WQCode=(Select top 1 WQCode from bWaiChu where KQNO=@KQNO and @HZDate =EndTime and @HZDate>BeginTime and DWei='班次' and EndLXCode=@BCLXCode and @BCXH<=EndBCXH)
Set @KQJieGuo=(Select WQName from bWQLX where WQCode= @WQCode)
Set @KQJieGuoBJ=(Select KQBJ from bWQLX where WQCode= @WQCode)
end
--++++++++++++++ -- 是否免勤
else
begin
if (@isSBKQ=0) and (@isXBKQ=0)
begin
Set @KQJieGuo='免勤'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
else
begin
Set @KQJieGuo='旷工'
exec wbSp_GetTSKQBiaoJ @KQJieGuo,@KQJieGuoBJ output
end
end
-- Insert or Update 考勤结果表
exec wbSP_AddorUpdateKQJieGuo @KQNO,@HZdate,@HZYear,@HZMonth,@CirCount,@ZCBorDB,@BCLXCode,@BCXH,@SBtime,@XBtime,@CDTime,@ZTTime,@KQJieGuo,@KQJieGuoBJ,@isUpdate
Fetch Next From ZCBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ
end -- end Curror While (@@FETCH_STATUS=0)
Close ZCBEveryBCXH
Deallocate ZCBEveryBCXH
end
end --end 处理正常班人员
end -- end 无
--############End 一.考勤原始记录中是否存在当日此人的考勤记录
Set @CirCount=@CirCount+1
Set @HZDate=@HZDate+1
end
--结束日期循环
return
end