www.gusucode.com > 精典源码Delphi130:顺某指纹考勤管理系统 > 精典源码Delphi130:顺某指纹考勤管理系统/11065顺某指纹考勤管理系统delphi/newNoMiMa_SP/wbSP_KQ_HuiZhong.txt
/****** Object: Stored Procedure dbo.wbSP_KQ_HuiZhong Script Date: 2000-10-05 16:13:24 ******/ /****** 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 exec wbSP_AddorUpdateKQJieGuo @KQNO,@HZdate,@HZYear,@HZMonth,@CirCount,@ZCBorDB,'WP',9,@SBtime,@XBtime,@CDTime,@ZTTime,@KQJieGuo,@KQJieGuoBJ,@isUpdate 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