gusucode.com > 精典源码Delphi044:指纹考勤管理系统源码程序 > 精典源码Delphi044:指纹考勤管理系统源码程序/精典源码Delphi044:指纹考勤管理系统/顺发指纹考勤管理系统/指纹考勤系统/SP_NoMiMa/wbSP_ZhuS_QueBan.txt
CREATE PROCEDURE wbSP_ZhuS_QueBan @KQNO Char(5), @ZSDate DateTime, @ZSJieGuo Varchar(20), @Result int Output -- @Result 0: 注释成功,1: 公休日 2. 当日未排班 3. 节假日 4.当日已有考勤记录或所在班次不考勤 AS Declare @BMcode Char(4) -- 部门代码 Declare @ZCBorDB Int -- 0:正常班 1:倒班 Declare @BCType char(6) -- 正常班,倒班 Declare @LXCode char(2) -- 班次类型代码 Declare @BCXH int -- 班次序号 Declare @isSBKQ bit -- 是否上班考勤 Declare @isXBKQ bit -- 是否下班考勤 Declare @ShangBXS Int --上班时间 declare @ShangBFZ int Declare @XiaBXS Int --下班时间 declare @XiaBFZ int Declare @KQYear int Declare @KQMonth int Declare @KQDay int Declare @KQWeekDay int Declare @HaveorNot bit begin Set @KQYear=datepart(yy,@ZSDate) Set @KQMonth=datepart(mm,@ZSDate) Set @KQDay=datepart(dd,@ZSDate) Set @KQWeekDay=datepart(dw,@ZSDate) -- 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="固定班") begin Set @ZCBorDB=1 Set @BCType='固定班' end else begin Set @ZCBorDB=0 Set @BCType='倒班' end -- 一、************得到正常班的班次类型代码 --2. 如是正常班 判断是否公修日 if @ZCBorDB=1 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 (@ZSDate>= BeginDate) and (@ZSDate<= 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 --************结束得到正常班的班次类型代码 else -- 二、************得到倒班的班次类型代码 begin exec wbSp_GetDBRenY_BCLXCode @KQNO,@KQYear,@KQMonth,@KQDay,@HaveorNot output,@LXcode output if @HaveorNot=0 begin Set @Result=2 -- 2. 当日未排班 return end end --************结束得到倒班的班次类型代码 --- ----------------- 开始处理正常班 if @ZCBorDB=1 begin Set @Result=4 --定义正常班各班次光标 Declare ZCBEveryBCXH CURSOR FOR Select BCXhuHao,isBeginKQ,isEndKQ,SBXS,SBFZ,XBXS,XBFZ from bZCBBC where LXCode =@LXCode OPEN ZCBEveryBCXH Fetch Next From ZCBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ while (@@FETCH_STATUS=0) begin -- 该班次是否有考勤原始记录 if Not Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@ZSDate and BCCode=@LXCode and BCXH=@BCXH) begin -- 上下班是否考勤 if ((@isSBKQ=1) or (@isXBKQ=1)) begin Set @Result=0 Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,YQTTime,BCJGCode) Values(@KQNO,@ZSDate,@BCType,@LXCode,@BCXH,@ShangBXS+@ShangBFZ/100.00,@XiaBXS+@XiaBFZ/100.00,@ZSJieGuo) end end Fetch Next From ZCBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ end -- end Curror While (@@FETCH_STATUS=0) Close ZCBEveryBCXH Deallocate ZCBEveryBCXH end --------------------- -- 结束正常班 else ----------------- -- 开始处理倒班 begin Set @Result=4 --定义正常班各班次光标 Declare DBEveryBCXH CURSOR FOR Select BCXhuHao,isBeginKQ,isEndKQ,SBXS,SBFZ,XBXS,XBFZ from bDBBC where LXCode =@LXCode OPEN DBEveryBCXH Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ while (@@FETCH_STATUS=0) begin -- 该班次是否有考勤原始记录 if Not Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@ZSDate and BCCode=@LXCode and BCXH=@BCXH) begin -- 上下班是否考勤 if ((@isSBKQ=1) or (@isXBKQ=1)) begin Set @Result=0 Insert into bKQJL(KQNO,KQDate,BCType,BCCode,BCXH,YQDTime,YQTTime,BCJGCode) Values(@KQNO,@ZSDate,@BCType,@LXCode,@BCXH,@ShangBXS+@ShangBFZ/100.00,@XiaBXS+@XiaBFZ/100.00,@ZSJieGuo) end end Fetch Next From DBEveryBCXH INTO @BCXH,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ end -- end Curror While (@@FETCH_STATUS=0) Close DBEveryBCXH Deallocate DBEveryBCXH end ---------------- -- 结束处理倒班 Return 0 end