www.gusucode.com > 精典源码Delphi130：顺某指纹考勤管理系统 > 精典源码Delphi130：顺某指纹考勤管理系统/11065顺某指纹考勤管理系统delphi/SP_NoMiMa/wbSP_GetQueBanData.txt

    
/****** Object:  Stored Procedure dbo.wbSP_GetQueBanData    Script Date: 2000-03-22 12:27:47 ******/
CREATE PROCEDURE wbSP_GetQueBanData  
 @KQNO Char(5),
 @KQDate DateTime
AS
 Declare @BMcode Char(4) -- 部门代码
 Declare @ZCBorDB Int -- 0:正常班  1:倒班
 Declare @BCType char(6)
 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

 Declare @LXCode char(2)
 Declare @LXName varChar(30)
 Declare @BCJGCode varChar(30) 
 Declare @BCXH Int
 Declare @BCName varChar(30)
 Declare @InsertFlag Bit
 Declare @YGName Char(20) 
  
begin

 
 Set @KQYear=datepart(yy,@KQDate)
 Set @KQMonth=datepart(mm,@KQDate)
 Set @KQDay=datepart(dd,@KQDate)
 Set @KQWeekDay=datepart(dw,@KQDate)
-- Get This Person BMCode 
 Set @BMcode=(Select BMCode from bYGongXX where KQNO=@KQNO) 
 Set @YGName=(Select YGName from bYGongXX where KQNO=@KQNO) 
 if Exists(Select KQNO from bYGongXX where KQNO=@KQNO and KQBeginDate<=@KQDate) -- 是否在考勤日期后
   Set @InsertFlag=1
 else
   Set @InsertFlag=0
--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) 
     Set @InsertFlag=0
   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)
     begin
        Set @LXCode=(Select LXCode from bGXRLieWai where BMcode=@BMCode and DatePart(yy,LWaiRQ)=@KQYear and DatePart(mm,LWaiRQ)=@KQMonth and DatePart(dd,LWaiRQ)=@KQDay) 
        Set @LXName=(Select LXName from bZCBLX where LXCode=@LXCode)
     end
     else
     
       Set @InsertFlag=0
     
   end
  end
  else --不是公休日 
  begin
  --3. 是否节假日 
   if Exists (Select JJRcode from bJJR where JJRYear=@KQYear and (@KQDate>= BeginDate) and (@KQDate<= EndDate+0.99999999))    
     Set @InsertFlag=0
  --不是公休和节假 得到考勤班次类型代码
   else
   begin
     -- 判断类型代码字段是否为空
     if Exists (Select BCLXCode from bGXR where BMcode=@BMCode and WeekOrder=@KQWeekDay and BCLXCode is Not NUll)
     begin
        Set @LXCode=(Select BCLXCode from bGXR where BMcode=@BMCode and WeekOrder=@KQWeekDay)
        Set @LXName=(Select LXName from bZCBLX where LXCode=@LXCode)
     end
     else
       Set @InsertFlag=0
   end
  end
 end 
--************结束得到正常班的班次类型代码
 else

-- 二、************得到倒班的班次类型代码
 begin
   exec wbSp_GetDBRenY_BCLXCode  @KQNO,@KQYear,@KQMonth,@KQDay,@HaveorNot output,@LXcode output
   if @HaveorNot=0 
     Set @InsertFlag=0
   else
     Set @LXName=(Select LXName from bDBLX where LXCode=@LXCode)
 end     
-- 创建临时表 
 Select @KQNO as KQNO,@YGName as YGName,@BCType as BCType,@LXCode as LXCode,@LXName as LXName,@BCXH as BCXH,@BCName as BCName,
    @ShangBXS+@ShangBFZ/100.00 as YQDTime,@XiaBXS+@XiaBFZ/100.00 as YQTTime,@BCJGCode as BCJGcode into #tmpQueB
 Delete  from #tmpQueB
--************结束得到倒班的班次类型代码

--3.对各班次排班是否缺班处理
 if @InsertFlag=1 
 begin   
--/////处理正常班
  if @ZCBorDB=1
  begin
    --定义正常班各班次光标
    Declare ZCBEveryBCXH CURSOR FOR
    Select BCXhuHao,BCName,isBeginKQ,isEndKQ,SBXS,SBFZ,XBXS,XBFZ from bZCBBC where LXCode =@LXCode
    OPEN ZCBEveryBCXH 
    Fetch Next From ZCBEveryBCXH INTO  @BCXH,@BCName,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ
    while (@@FETCH_STATUS=0)
    begin
      Set @InsertFlag=1
      if @isSBKQ=1 or @isXBKQ=1 --上下班有要考勤
      begin
        -- 该班次无考勤原始记录
        if not Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXH) 
        begin
          --有无请假记录
            if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @KQDate Between BeginTime and EndTime and DWei='天')
              Set @InsertFlag=0
            else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @KQDate =BeginTime and @KQDate=EndTime and DWei='班次' and LXCode=@LXCode and ( @BCXH between BeginBCXH  and EndBCXH))--请假在同一天结束
              Set @InsertFlag=0
            else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @KQDate =BeginTime and DWei='班次' and LXCode=@LXCode and @BCXH>=BeginBCXH) --请假第一天
              Set @InsertFlag=0
            else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @KQDate =EndTime and DWei='班次' and LXCode=@LXCode and @BCXH<=EndBCXH) --请假最后一天
              Set @InsertFlag=0
            else if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @KQDate >BeginTime and @KQDate<EndTime and DWei='班次' and LXCode=@LXCode)  --请假中间秒天
              Set @InsertFlag=0
           --有无外出记录  
            else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @KQDate Between BeginTime and EndTime and DWei='天')
              Set @InsertFlag=0
            else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @KQDate =BeginTime and @KQDate=EndTime and DWei='班次' and LXCode=@LXCode and ( @BCXH between BeginBCXH  and EndBCXH))--外出在同一天结束
              Set @InsertFlag=0
            else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @KQDate =BeginTime and DWei='班次' and LXCode=@LXCode and @BCXH>=BeginBCXH) --外出第一天
              Set @InsertFlag=0
            else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @KQDate =EndTime and DWei='班次' and LXCode=@LXCode and @BCXH<=EndBCXH) --外出最后一天
              Set @InsertFlag=0
            else if Exists( Select KQNO from bWaiChu where KQNO=@KQNO and @KQDate >BeginTime and @KQDate<EndTime and DWei='班次' and LXCode=@LXCode)  --外出中间天
              Set @InsertFlag=0
            else 
              Set @InsertFlag=1
            -- Add Que Ban Record
            if @InsertFlag=1 
            Insert into #tmpQueB(KQNO,YGName,BCType,LXCode,LXName,BCXH,BCName,YQDTime,YQTTime) 
                  Values(@KQNO,@YGName,@BCType,@LXCode,@LXName,@BCXH,@BCName,@ShangBXS+@ShangBFZ/100.00,@XiaBXS+@XiaBFZ/100.00)      
        end -- 该班次无考勤原始记录
      end -- end if @isSBKQ=1 or @isXBKQ=1 --上下班有要考勤
      Fetch Next From ZCBEveryBCXH INTO  @BCXH,@BCName,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ
    end -- end Curror While (@@FETCH_STATUS=0)
    Close ZCBEveryBCXH
    Deallocate ZCBEveryBCXH
  end -- end if @ZCBorDB=1
--/////处理倒班
  else
  begin
--?????

    --定义倒班各班次光标
    Declare DBEveryBCXH CURSOR FOR
      Select BCXhuHao,BCName,isBeginKQ,isEndKQ,SBXS,SBFZ,XBXS,XBFZ from bDBBC where LXCode =@LXCode
    OPEN DBEveryBCXH 
    Fetch Next From DBEveryBCXH INTO  @BCXH,@BCName,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ
    while (@@FETCH_STATUS=0)
    begin
      Set @InsertFlag=1
      if @isSBKQ=1 or @isXBKQ=1 --上下班有要考勤
      begin
        -- 该班次无考勤原始记录
        if not Exists(Select KQNO from bKQJL where KQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXH) 
        begin
          --有无请假记录
            if Exists( Select KQNO from bQingJ where KQNO=@KQNO and @KQDate Between BeginTime and EndTime and DWei='天')
              Set @InsertFlag=0
           
           --有无外出记录  
            else if Exists(Select KQNO from bWaiChu where KQNO=@KQNO and @KQDate Between BeginTime and EndTime and DWei='天')
              Set @InsertFlag=0
           -- 有无调班
            else if Exists(Select BeiKQNO from bTiaoBanJL where BeiKQNO=@KQNO and KQDate=@KQDate and BCCode=@LXCode and BCXH=@BCXH)
              Set @InsertFlag=0
            else 
              Set @InsertFlag=1
            -- Add Que Ban Record
            if @InsertFlag=1 
            Insert into #tmpQueB(KQNO,YGName,BCType,LXCode,LXName,BCXH,BCName,YQDTime,YQTTime) 
                  Values(@KQNO,@YGName,@BCType,@LXCode,@LXName,@BCXH,@BCName,@ShangBXS+@ShangBFZ/100.00,@XiaBXS+@XiaBFZ/100.00)      
        end -- 该班次无考勤原始记录
      end -- end if @isSBKQ=1 or @isXBKQ=1 --上下班有要考勤
      Fetch Next From DBEveryBCXH INTO  @BCXH,@BCName,@isSBKQ,@isXBKQ,@ShangBXS,@ShangBFZ,@XiaBXS,@XiaBFZ
    end -- end Curror While (@@FETCH_STATUS=0)
    Close DBEveryBCXH
    Deallocate DBEveryBCXH

--????  
  end 

-- end  /////处理倒班
 end -- end if  @InsertFlag=1 

-- 返回结果 
 Select * from #tmpQueB
end