none
请问有人会分析存储过程吗?这个存储过程是我公司的,计算加班的时候少于半个小时就不会计算,希望能改成计算每一分钟。 RRS feed

  • 问题

  • set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

     

     

     

    ALTER            procedure [dbo].[spGetEmployeeShiftByDate]

    /*

    select * from orgnization where orgcode like '005%'
    select * from workdayrecord where staffno='21741' and datediff(month,fdate,'2009-12-1')=0
    select * from workmonth where staffno='30921' and fdate='2009-10-1'
    update workmonth set finishflag=0 where staffno='31901' and fdate='2009-9-1'
    declare @date smalldatetime,@mDate datetime
    set @date='2009-12-1'
    set @mDate='2009-12-31'
    declare @employeeID uniqueidentifier
    select @employeeID=employeeID from employees where staffno='21741'
    exec spGetEmployeeShiftByDate @employeeID,@date,@mDate,0

    */
    @employeeID uniqueidentifier, --GUID
    @date smalldatetime,  --开始日期
    @eDate smalldatetime,  --结束日期
    @eatFlag bit
    as

    set nocount on
    if not exists(select 1 from employees where employeeid=@employeeID)
     return
    set @date = dateAdd(day,-datePart(day,@date)+1,@date)

    if datediff(month,getdate(),@date)>0
     return
    declare @staffNo varchar(10),  --工号
     @Gender varchar(2),
     @maxDate datetime, --最大日期
     @WX  decimal(10,2), --无薪
     @WorkFlag bit,  --是否不计考勤
     @DutyDate smalldatetime, --入职日期
     @DimissionDate smallDatetime, --离职日期
     @SalaryMode varchar(3),  --薪制
     @OnDuty datetime,  --上班时间
     @OffDuty datetime,  --下班时间
     @OrgCode varchar(30),  --组织机构
     @SalaryFlag tinyint , --工资核算方法
     @ShiftOffDuty smalldatetime,  --班次时间段1下班时间
     @ShiftOnDuty smalldatetime,     --班次时间段2上班时间
     @DutyLevel int,
     @kcc decimal(18,2),
     @defaultShift varchar(10), --默认班次
     @Baoan bit,
     @txMin smalldatetime,
     @txMax smalldatetime,
     @workstation nvarchar(10)

    set @eDate = dateadd(day,-1,dateAdd(month,1,@date))


    --取工号等个人信息
    select
    @staffNo=StaffNo,@DutyDate=DutyDate,
    @WorkFlag=WorkFlag,
    @DimissionDate=b.DimissionDate,
    @SalaryMode=SalaryMode,
    @OrgCode=orgCode,
    @Gender=gender,
    @SalaryFlag=isNull(SalaryFlag,0),
    @DutyLevel = cast(DutyLevelCode as int),
    @workstation=workstation,
    @Baoan=case when (workstation in ('保安','保安班长','宿管员','清洁工','保安队长') or (orgcode like '005004001%' and workstation in ('电工','电梯工','行政助理'))) and salarymode='001' then 1 else 0 end
    from Employees a
    left join EmployeeDimission b on a.EmployeeID=b.EmployeeID
    where a.EmployeeID=@employeeId
    if(@orgcode like '010%')
     set @baoan=0
    select @defaultShift=isnull(fid,'A01') from shift where orgcode=left(@orgCode,3) and isdefault=1

    --------------如果已经审核月报,直接返回
    if exists(select 1 from workmonth where staffno=@staffno and datediff(month,fdate,@date)=0 and finishflag=1)
     return
    --判断是否不计工资

    if @SalaryFlag = 1
    begin
     --如果不计工资则删掉考勤资料
     delete from WorkDayRecord
     where StaffNo=@StaffNo and datediff(month,Fdate,@date)=0

     delete from WorkMonth
     where StaffNo=@StaffNo and datediff(month,FDate,@date)=0
     
     return

    end

     

    -----------------计算餐费---------------
    declare @EatSum decimal(18,2)
    select
    @EatSum = isNull(
    sum(case when FType ='1早餐' then 1 else 0 end) * 0.6 +
    sum(case when FType ='2中餐' then 1 else 0 end) * 3.0 +
    sum(case when FType ='3晚餐' then 1 else 0 end) * 3.0 +
    sum(case when FType ='2中餐' and FKind ='加餐' then 1 else 0 end) * 2,0)
    from eatList a
    left join employees b on a.staffno=b.staffno
    where datediff(month,a.FDate,@Date)=0 and a.StaffNo = @staffNo
    -----------------------------------------------------------------------
    if(@eatflag=1)
    begin
     if exists(select 1 from workmonth where datediff(month,fdate,@date)=0 and staffno=@staffno)
      update workmonth set eattotal=@eatsum where datediff(month,fdate,@date)=0 and staffno=@staffno
     return
    end

    --不计考勤(全勤)
    if @WorkFlag=1
    begin
     declare @st decimal(18,2),@rt decimal(18,2),@kDate smalldatetime
     if(getdate()<@eDate)
      set @kDate=convert(char(10),getdate(),120)
     else
      set @kDate=@eDate
     select @kDate
     select @st=dbo.GetMonthDayByStaffNo(@StaffNo,@date),@rt=dbo.fGetEmployeeRealTime(@StaffNo,@kDate)
     delete from WorkDayRecord where StaffNo=@StaffNo and datediff(month,FDate,@date)=0
     if exists (select 1 from WorkMonth where StaffNo=@Staffno and datediff(month,FDate,@date)=0)
     delete from WorkMonth where StaffNo=@Staffno and datediff(month,FDate,@date)=0
     declare @sql varchar(1000)
     select @sql=[dbo].[fGetEmployeeWorkMonth](@staffno,@kDate)
     print @sql
     exec(@sql)
     
     update workmonth
     set eattotal=@eatsum
     where staffno=@staffno and datediff(month,fdate,@date)=0
     --select @wx=shouldtime-realtime-isnull(sj,0)-isnull(nj,0)-isnull(hj,0) from workmonth where staffno=@staffno and fdate=@date
     --------统计出差

     select @kcc=sum(case wholeday when 0 then hours when 1 then 4 when 2 then 4 when 3 then 8 end) from employeeleave where holidaytype='003' and staffno=@StaffNo and datediff(month,fdate,@date)=0 and status=3
     update workmonth set cc=isnull(@kcc,0)/8 where staffno=@staffno and datediff(month,fdate,@date)=0
     return
    end

    -------------如果月考勤数据为导入数据则只更新应勤及餐费----------------------
    if exists (select 1 from workMonth where StaffNo=@staffNo and datediff(month,FDate,@date)=0 and isNull(ImportFlag,0)=1)
    begin
     --删掉日报表
     /*
     delete WorkDayRecord
     where StaffNo=@StaffNo and datediff(month,FDate,@date)=0
     */
     update WorkMonth
     set ShouldTime = dbo.GetMonthDayByStaffNo(@StaffNo,@date),
     EatTotal = @EatSum
     where StaffNo=@StaffNo and datediff(month,Fdate,@date)=0

     if @SalaryMode='002'
     begin
      --计算无薪
      set @WX =0
      while @date<=@edate
      begin
       if not exists (select 1 from PublicHoliday h
        inner join Employees e on e.OrgCode like h.OrgCode + '%'
        and e.EmployeeID=@EmployeeID and e.SalaryMode=h.SalaryMode
        and h.Gender like '%' + e.Gender + '%'
        and HolidayDate=@date and h.DayPart='0'
        inner join OverTimeType t on h.HolidayType=t.FID
       )
        and ( @date<@DutyDate or @date>=@DimissionDate )
        set @WX = @WX + 1

       set @date=dateadd(day,1,@date)
      end

      set @date=dateadd(day,-1,@date)

      update WorkMonth
      set
      WX=@WX
      where StaffNo=@StaffNo and datediff(month,Fdate,@date)=0
     end
     

     
     
     return
    end

    --------------------------------------------------------------------------
    declare

    @ShiftID varchar(10), --班次编号
    @OnDuty1 datetime, --上班1
    @OffDuty1 datetime, --下班1
    @OnDuty2 datetime, --上班2
    @OffDuty2 datetime, --下班2
    @OnDuty3 datetime, --上班3
    @OffDuty3 datetime, --下班3
    @OnDuty4 datetime, --上班4
    @OffDuty4 datetime, --下班4
    @holidayFlag bit,  --放假标志

    @ShouldTime decimal(10,2), --应勤
    @RealTime decimal(10,4), --实勤
    @LateEarly decimal(10,2), --迟到早退
    @LateNum        smallint,  --迟到早退次数
    @Neglect decimal(10,2), --旷工

    @YeBan  int, --夜班
    @TongXiao int, --通宵
    @BK  smallint, --补卡
    @HolidayType varchar(20),
    @LeaveDayPart smallint, --请假时间段(1上午,2下午,3全天)
    @LeaveHours decimal(18,2), --请假时数
    @LeaveDayType varchar(10), --请假类别

    @ygfj  decimal(10,2), --因公放假
    @DayPart varchar(5),
    @OnDutyBal int,
    @OffDutyBal int,
    @StartTime smalldatetime, --加班开始时间
    @EndTime smalldatetime, --加班结束时间
    @Hours  decimal(18,2), --加班时数
    @OWType  varchar(3), --加班类别
    @OWTime  int,

    @OWMul  decimal(5,2), --加班倍数
    @OW1  decimal(18,2), --1.5倍加班
    @OW2  decimal(18,2), --2倍加班
    @OW3  decimal(18,2), --3倍加班
    @OWApply decimal(18,2), --加班申请时数
    @temp  int,
    @SJ  decimal(10,2), --事假
    @BJ  decimal(10,2), --病假
    @CC  decimal(10,2), --出差
    @NJ  decimal(10,2), --年假
    @HJ  decimal(10,2), --婚假
    @CJ  decimal(10,2), --产假
    @PCJ  decimal(10,2), --陪产假
    @GSJ  decimal(10,2), --工伤假
    @SaJ  decimal(10,2), --丧假
    @BRJ  decimal(18,2), --哺乳假
    @AdjustRest decimal(10,2), --调休
    @AdjustRestS decimal(10,2), --班次中调休时间
    @WXS  decimal(10,2), --班次中无薪时间
    @OShiftID varchar(10), --加班班次
    @RepairFlag bit,  --是否补班
    @NeglectOW decimal(10,2), --“必须”加班产生的旷工
    @OPeriodTime smallint --加班班次时间段
    set @LateNum=0
    --declare @tDate smalldatetime,@pDate smalldatetime
    select @maxDate = LastDate from WorkMonth where staffno=@staffno and datediff(month,fdate,dateadd(month,-1,@date))=0

    if @maxDate is Null
     set @maxDate = dateAdd(day,-1,@date)

    while @date<=@eDate
    begin

     if exists(select 1 from workdayrecord where staffno=@staffno and fdate=@date and updateflag=1)
      select @defaultshift=shiftid from workdayrecord where staffno=@staffno and fdate=@date
     set @RepairFlag=0
     select @RepairFlag=1 from EmployeeLeave a inner join WorkRepair b on a.LeaveID=b.LeaveID where a.staffNo=@staffno and b.FDate=@date and a.status=3
     
     --取昨天、今天及明天的打卡时间
     select @StaffNo as 工号,FDate as 打卡时间 into #t1 from
     (select FDate from WorkTime
     where StaffNo=@StaffNo and abs(datediff(day,@date,FDate)) in (1,0)
     and FDate >@maxDate
     union
     select FDate from WOrkTimeAdd
     where StaffNo=@StaffNo and Status=3
     and abs(datediff(day,@date,FDate)) in (1,0)
     and FDate >@maxDate
     ) a
     order by 打卡时间


     --判断当天是否放假
     select @ShiftID=null,@DayPart=null, @HolidayType=null,@OWMul=null
     while not exists(select 1 from publicholiday where datediff(month,HolidayDate,@date)=0 and OrgCode= @OrgCode and (left(Gender,1)=@Gender or right(Gender,1)=@Gender) and SalaryMode=@SalaryMode)
     begin
      if(len(@orgcode)<=3)
       break;
      set @orgcode=left(@orgcode,len(@orgcode)-3)
      
     end
     
      select @DayPart=h.DayPart,@HolidayType=HolidayType,@OWMul=FMultiple from PublicHoliday h
      inner join Employees e on e.OrgCode like h.OrgCode + '%' and e.EmployeeID=@EmployeeID and e.SalaryMode=h.SalaryMode
      and h.Gender like '%' + e.Gender + '%'
      and HolidayDate=@date
      inner join OverTimeType t on h.HolidayType=t.FID
      where h.orgcode=@Orgcode
     
     select @OWApply=0,@OW1=0,@OW2=0,@OW3=0,@LateEarly=0 ,@LateNum=0,@OWTime=0,@RealTime=0 ,@Neglect=0 ,@NeglectOW=0,@BK=0 ,@YeBan=0 ,@WX=0,@TongXiao=0
     select @AdjustRest =0,@SJ=0,@BJ=0,@CC=0,@NJ=0,@HJ=0,@CJ=0,@PCJ=0,@GSJ=0,@SaJ=0,@BRJ=0,@YGFJ=0,@LeaveDayPart=0,@LeaveHours=0,@LeaveDayType=null
     select @OnDuty = null,@OffDuty = null,@OnDuty1=null,@OnDuty2=null,@OnDuty3=null,@OffDuty1=null,@OffDuty2=null,@OffDuty3=null


     set @OWMul=isNull(@OWMul,1.5)
     --如果当天放假,且不是无薪假 

      
     if (@DayPart='0' and @HolidayType<>'004') 
     and not exists (select 1 from EmployeeLeave a left join WorkRepair b on a.LeaveID=b.LeaveID where a.status=3 and a.Staffno=@Staffno and a.HolidayType='011' and a.WholeDay=3 and b.FDate=@date and a.FDate>='2008-6-1')
     begin
      
      
      if @DutyDate>@date
      begin
       if @HolidayType='001'
       begin
        
        if exists (select 1 from WorkDayRecord where Staffno=@Staffno and FDate=@Date)
         update workDayRecord set ShouldTime=8,RealTime=0,WX=8,OverWork1 =0,OverWork2=0,Overwork3=0,Neglect=0,CC=@Hours,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0 from workDayRecord where Staffno=@Staffno and FDate=@Date
        else
         insert into workDayRecord (StaffNo,Fdate,ShouldTime,RealTime,WX)values(@StaffNo,@Date,8,0,8)
       end
       else
        delete from workDayRecord  where Staffno=@Staffno and FDate=@Date
       goto goon
      end
      --保安的加班-----------------------------------------
      if exists(select 1 from employees where staffno=@staffno and (workstation in ('保安','保安班长','宿管员','清洁工','保安队长') or (orgcode like '005004001%' and workstation in ('电工','电梯工','行政助理'))) and salarymode='001') and @orgcode not like '010%'
      begin
       select @ShiftID=ShiftID from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo and UpdateFlag=1

       create table #owt2(
       ShiftID nvarchar(10),PeriodTime smallint,AheadTime int,AbsenceTimeA int,上班时间 datetime,
       AbsenceTimeL int,下班时间 datetime,DelayTime int,OWTime int,AdjustRest int,WX int)
       
       --班次信息,如果更新过班次,则以设置的班次为准
       
       if isNull(@ShiftID,'') <> ''
       begin
        insert into #owt2
        select
        ParentID,
        PeriodTime,
        AheadTime,
        datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
        --AbsenceTimeA,
        dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
        datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
        --AbsenceTimeL,
        dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
        DelayTime,OWTime,AdjustRest,WX
        from ShiftDetail where ParentID=@ShiftID
       end
       else
       begin --获取该员工所有班次
        
        
        insert into #owt2
        select
        ShiftID,
        PeriodTime,
        AheadTime,
        datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
        --AbsenceTimeA,
        dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
        datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
        --AbsenceTimeL,
        dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
        DelayTime,OWTime,AdjustRest,WX
        from EmployeeShift a
        inner join ShiftDetail b on a.ShiftID=b.ParentID
        where a.EmployeeID=@EmployeeID
       end
       
       select
       a.打卡时间,
       b.ShiftID as 班次编号,
       b.PeriodTime as 时间段,
       isNull(b.OWTime,0) as 加班,
       isNull(b.AdjustRest,0) as 调休,
       isNull(b.WX,0) as 无薪,
       cast(datediff(second,打卡时间,b.上班时间) as decimal(10,2))/60 as 上班时间差,
       cast(datediff(second,b.下班时间,打卡时间) as decimal(10,2))/60 as 下班时间差
       into #owt3
       from #t1 a ,#owt2 b
       where
       cast(datediff(second,a.打卡时间,b.上班时间) as decimal(10,2))/60 between AbsenceTimeA*-1 and AheadTime
       or cast(datediff(second,b.下班时间,a.打卡时间) as decimal(10,2))/60 between AbsenceTimeL*-1 and DelayTime
      
       select top 1 @shiftid=班次编号
        from(
        select 班次编号,时间段,min(abs(case when 上班时间差<0 then 上班时间差-50 else 上班时间差 end)) as 上班时间差,min(abs(case when 下班时间差<0 then 下班时间差-50 else 下班时间差 end)) as 下班时间差
        from #owt3
        group by 班次编号,时间段)a group by 班次编号
        order by sum(上班时间差+下班时间差)
       
       --没有找到匹配的班次,则取默认班次
       IF @ShiftID is null
       begin
        select top 1 @ShiftID=ShiftID from EmployeeShift where EmployeeID=@EmployeeID
        if @ShiftID is null
         set @ShiftID=@defaultshift
       end
       
       --置空
       select @OnDuty=null,@OffDuty=null,@OnDuty1=null,@OffDuty1=null,@OnDuty2=null,@OffDuty2=null,@OnDuty3=null,@OffDuty3=null,
       @ShouldTime=0, @OWTime=0,@StartTime=null

       
        

    2011年11月1日 13:00

答案

  • 改成@temp/(30+0.0)*30 是想计算小数点后面的。因为29/(30+0.0)*30就不会等于0了。

    请问@temp 的数据类型是float吗? 如果是int的话,会忽略小数点后的数据的。

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • 已标记为答案 彭123 2012年10月9日 14:18
    2011年11月9日 10:00
    版主

全部回复

  •    --取上下班时间
      if @shiftID is not null
      begin
       select PeriodTime,cast(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))-RestTime as decimal(10,2))/60 as 应勤,restTime as 休息,
       AdjustRest as 调休,WX as 无薪,OWTime as 加班 into #owt4
       from ShiftDetail where ParentID=@ShiftID

       -------班次的时间段
       declare @owPeriodCount smallint,@j int
       select @j=0,@owPeriodCount=count(ParentID) from ShiftDetail where ParentID=@ShiftID
       
       while @j<@owPeriodCount
       begin
        select @j=@j+1,@OnDutyBal=0,@OffDutyBal=0,@OnDuty=null,@OffDuty=null,@OWTime=0,@WXS=0,@AdjustRestS=0

        if exists (select 1 from #owt2 where ShiftID=@ShiftID and PeriodTime=1 and 上班时间>getdate())
        begin
         
         select @WX = @WX + 应勤 from #owt4 where PeriodTime=@j
         continue
        end
        
        --取上班时间
        
        select top 1 @OnDuty=打卡时间,@OnDutyBal=上班时间差,@temp=加班,@AdjustRestS=调休,@WXS = 无薪 from #owt3 a
        inner join #owt2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
        where b.ShiftID=@shiftID and a.时间段=@j and 上班时间差 between b.absenceTimeA*-1 and b.AheadTime  and 打卡时间>isNull(@maxDate,@date)
        order by 上班时间差 desc--abs(上班时间差)

        if exists (select 1 from #owt2 where ShiftID=@ShiftID and PeriodTime=@j and 下班时间>getdate())
        begin
         select @WX = @WX + 应勤 from #owt4 where PeriodTime=@j
         continue
        end
        
        --取下班时间
      
        if (@OnDuty is not null)
        begin
         
         select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #owt3 a
         inner join #owt2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
         and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) and 打卡时间>@OnDuty
         where b.ShiftID=@shiftID and a.时间段=@j
         order by 下班时间差
        end
        else

         begin
         
         if isNull(@LeaveHours,0)<(select datediff(minute,上班时间,下班时间) from #owt2 where ShiftID=@ShiftID and PeriodTime=@j)
         begin
          
          select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #owt3 a
          inner join #owt2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
          and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) 
          where b.ShiftID=@shiftID and a.时间段=@j
          order by 下班时间差
          
         end
         end
        

     
       
        if @DayPart=cast(@j as varchar(1)) and @OnDuty is not null and @OffDuty is not null
        begin
         set @temp = datediff(minute ,@OnDuty,@OffDuty)
        end

        if @OffDuty is not null or @OnDuty is not null
         set @maxDate=isNull(@OffDuty,@OnDuty)
        if @j=1
         select @OnDuty1=@OnDuty,@OffDuty1=@OffDuty
        if @j=2
         select @OnDuty2=@OnDuty,@OffDuty2=@OffDuty
        if @j=3
         select @OnDuty3=@OnDuty,@OffDuty3=@OffDuty

       end

       if object_id('tempdb..#owt4') is not null
       drop table #owt4
      end
       if object_id('tempdb..#owt2') is not null
       drop table #owt2
       if object_id('tempdb..#owt3') is not null
       drop table #owt3
      --通宵及夜班 
       
       if(@offduty3 is not null)
       begin
        set @txMax=@offduty3
        set @txMin=@Onduty1
       end
       else if(@offduty2 is not null)
       begin
        set @txMax=@offduty2
        set @txMin=@Onduty1
       end
       else
       begin
        set @txmax=@offduty1
        set @txMin=@Onduty1
       end
       if(left(@orgcode,3)<>'002')
       begin
        --if(@txMin<dateadd(day,1,@date)+' 00:00:00' and @txMax>=dateadd(day,1,@date)+' 02:00:00')
        if @txMax>=dateadd(day,1,@date)+' 02:00:00'
         set @TongXiao=1
        else
         set @TongXiao=0
        if(@TongXiao=0)
        begin
         --if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
         if @txMax>=dateadd(day,1,@date)+' 00:00:00'
         set @YeBan=1
        else
         set @YeBan=0
        end
        
        
       end
       else
       begin
        if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 06:30:00')
         set @TongXiao=1
        else
         set @TongXiao=0
        if(@TongXiao=0)
        begin
         if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
          set @YeBan=1
         else
          set @YeBan=0
        end 
        
       end
       declare @owrt decimal(18,2),@owtotal decimal(18,2)
       select @owtotal=cast(sum(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))) as decimal(10,2))/60.0,
       @owrt=sum(RestTime)/60.0
        from ShiftDetail where ParentID=@ShiftID
       set @temp=0
     
       if(@onduty1 is not null and @offduty1 is not null)
       begin
        select @temp=@temp+datediff(minute,@onduty1,@offduty1)
       end
       if(@onduty2 is not null and @offduty2 is not null)
        select @temp=@temp+datediff(minute,@onduty2,@offduty2)
       if(@onduty3 is not null and @offduty3 is not null)
        select @temp=@temp+datediff(minute,@onduty3,@offduty3)
       set @temp=@temp*30/1800

       if @OWMul=2
       begin
        set @ow2=@temp-@owrt
        if(@ow2>@owtotal-@owrt)
         set @ow2=@owtotal-@owrt
        if(@ow2<0)
         set @ow2=0
       end
       if @OWMul=3
       begin
        set @ow3=@temp-@owrt
        
        if(@ow3>@owtotal-@owrt)
         set @ow3=@owtotal-@owrt
        if(@ow3<0)
         set @ow3=0
       end
      end
      
      ----------------------------------------------------
      --判断该员工是否出差,出差则已加班处理,并且没有申请加班
      
      if exists (select 1 from EmployeeLeave where StaffNo=@StaffNo and owflag=1 and datediff(day,Fdate,@date)=0 and Status=3 and HolidayType in ('003','014')) and @baoan<>1
      begin
       select @Hours = case WholeDay when 3 then 8 when 1 then 4 when 2 then 4 else Hours end from EmployeeLeave
       where StaffNo=@StaffNo and datediff(day,FDate,@date)=0

       if @OWMul=2
        set @OW2=@Hours*60

       if @OWMul=3
        set @OW3=@Hours*60

       if @OWMul=1.5
        set @OW1=@Hours*60
       
       if exists (select 1 from WorkDayRecord where StaffNo=@StaffNo and Fdate=@Date)
        update WorkDayRecord set ShouldTime=case @HolidayType when '001' then 8 else null end,RealTime=case @HolidayType when '001' then 8 else null end,
        OverWork1 = @OW1,OverWork2=@OW2,Overwork3=@OW3,Neglect=0,CC=@Hours/60,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=0,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0
        where StaffNo=@StaffNo and FDate=@date
       else
        insert into WorkDayRecord
        (StaffNo,FDate,Overwork1,Overwork2,Overwork3,CC,RealTime,ShouldTime)
        values (@StaffNo,@date,@OW1,@OW2,@OW3,case when @Hours>8 then 8 else @hours end,case @HolidayType when '001' then 8 else null end,case @HolidayType when '001' then 8 else null end)
       
      end
      
      if(@baoan=1)
      begin
        select @BK = count(FDate) from WorkTimeAdd where StaffNo=@StaffNo and DateDiff(Day,FDate,@Date)=0 and FType='001'

        if exists (select 1 from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo)
         update WorkDayRecord set OnDuty1=@OnDuty1,OffDuty1=@OffDuty1,OnDuty2=@OnDuty2,OffDuty2=@OffDuty2,OnDuty3=@OnDuty3,OffDuty3=@OffDuty3,
         OverWork1=@OW1,OverWork2=@OW2,OverWork3=@OW3,
         Neglect=@NegLect,LateEarly=@LateEarly,shiftID=@ShiftID,
         YeBan=@YeBan,TongXiao=@TongXiao,BK=@BK,LateNum=@LateNum,WX=0,ShouldTime=null,RealTime=null,CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,AppOWHours=@OWApply,OWAbnormFlag=0
         where FDate=@Date and StaffNo=@StaffNo
        else
         insert into WorkDayRecord (shiftid,StaffNo,FDate,OnDuty1,OffDuty1,OnDuty2,OffDuty2,OnDuty3,OffDuty3,OverWork1,OverWork2,OverWork3,NegLect,LateEarly,YeBan,TongXiao,BK,LateNum,AppOWHours,OWAbnormFlag)
         Values (@shiftid,@StaffNo,@date,@OnDuty1,@OffDuty1,@OnDuty2,@OffDuty2,@OnDuty3,@OffDuty3,@OW1,@OW2,@OW3,@Neglect,@LateEarly,@YeBan,@TongXiao,@BK,@LateNum,@OWApply,0)
             if @HolidayType='001'

         update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=8 where staffno=@staffNo and FDate=@date
      end
      else
      begin
       
       --判断当天该员工有没有申请加班,如果没有,则该日不用上班删掉当天的数据
       if not exists (select 1 from OverWork where (datediff(day,StartTime,@Date)=0 or abs(datediff(hour,convert(varchar(10),@date,120) + ' 23:59:59',StartTime)) between 0 and 4 ) and StaffNo=@StaffNo and Status=3 and FType in ('001','003') and StartTime>isNull(@MaxDate,@date)) and not exists (select 1 from EmployeeLeave where StaffNo=@StaffNo and datediff(day,Fdate,@date)=0 and Status=3 and HolidayType in ('003','014'))
       begin
        
        if (select Count(staffno) from WorkTime where staffno=@staffno and datediff(day,fdate,@date)=0 and fdate>isNull(@maxdate,@date))  >=2
        begin
         if exists (select 1 from workDayRecord where staffno=@staffno and FDate=@date)
          update WorkDayRecord set ShiftID=null,ShouldTime=null,RealTime=null,OnDuty1=null,OffDuty1=null,OnDuty2=null,OffDuty2=null,OnDuty3=null,OffDuty3=null,LateEarly=null,Neglect=null,OverWork1=null,OverWork2=null,OverWork3=null,
          CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=0,BK=0,BRJ=0,YeBan=0,TongXiao=0,AdjustRest=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0,OWAbnormFlag=1
          where staffno=@staffno and fdate=@date
         else
          insert into workDayRecord (staffno,fdate,OWAbnormFlag) values (@staffno,@date,1)
        end
        else
         delete from WorkDayRecord where StaffNo=@StaffNo and Datediff(day,FDate,@Date)=0

        if @HolidayType='001'
        begin
         if @DutyDate>@date or @date >getdate() or (@DimissionDate is not null and @DimissionDate<=@date)
          select @RealTime=0,@WX = 8
         else
          select @RealTime = 8,@WX=0

         if exists (select 1 from workDayRecord where staffno=@staffno and FDate=@date)
          update workDayRecord
          set ShiftID=@defaultshift,ShouldTime=8,
          RealTime=@RealTime,
          WX=@WX,
          OnDuty1=null,OffDuty1=null,OnDuty2=null,OffDuty2=null,OnDuty3=null,OffDuty3=null,LateEarly=null,Neglect=null,OverWork1=null,OverWork2=null,OverWork3=null,
          CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,BK=0,BRJ=0,YeBan=0,TongXiao=0,AdjustRest=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0,OWAbnormFlag=0
          where staffno=@staffno and fdate=@date
         else
          insert into WorkDayRecord (StaffNo,FDate,ShiftID,ShouldTime,RealTime,WX) values (@StaffNo,@date,@defaultshift,8, @RealTime,@WX)
        end
        goto goOn
       end
       else--------计算加班
       begin
        
        
        select @Neglect = 0,@LateEarly = 0,@LateNum=0,@OWTime=0,@OShiftID = null,@OPeriodTime = 0
        declare @i int
        set @i=1
        --查找加班时间
        declare cur cursor for
        select StartTime,EndTime,Hours,FType,ShiftID,PeriodTime from overwork
        where startTime between @date and dateadd(hour,30,@date) and StaffNo=@StaffNo and Status=3 and FType in ('001','003') and StartTime>isNull(@maxDate,@date)
        
        order by StartTime

        open  cur
        fetch next from cur into @StartTime,@EndTime,@Hours,@OWType,@OShiftID,@OPeriodTime
        while @@FETCH_STATUS=0
        begin

         set @OWApply = @OWApply + @Hours
         
         set @OnDuty = null
         set @OffDuty = null
        
        
        select top 1 @OnDuty = 打卡时间
        from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
        
        and 打卡时间>isNull(@maxDate,@date)
        order by abs(datediff(minute,@StartTime,打卡时间)) asc
         
         if @OnDuty is not null
         begin
          
          select @OffDuty=min(打卡时间) from #t1 where 打卡时间>@OnDuty
          if abs(datediff(minute,@OffDuty,@EndTime))>300 set @OffDuty=null
          if @OffDuty is not null
          begin
           set @temp=datediff(minute,case when @OnDuty< @StartTime then @StartTime else @OnDuty end,@OffDuty)
           select @temp = case when @temp>@Hours then @Hours else @temp end

           if @OShiftID is not null and @OPeriodTime>0
            select @temp = @temp - RestTime from ShiftDetail where ParentID=@OShiftID and PeriodTime=@OPeriodTime
           if @temp < 0
            set @temp =0
           set @OWTime = @OWTime + @temp
          end
         end
         if(@OnDuty is not null and @OffDuty is not null)
          set @maxDate=isNull(@OffDuty,@OnDuty)
         --else
          --set @maxDate=@endTime
      
         if @OWType='003' --如果加班类别为"必须",则计算迟到及旷工
         begin
          if @OnDuty is Null or @OffDuty is Null
           set @NegLect = @NegLect + @Hours
          else
          begin
           set @temp=datediff(minute,@StartTime,@OnDuty)
           if @temp>0
           begin
            if @temp>30
             set @NegLect=@NegLect + @temp/60
             
            else
             select @LateEarly=@LateEarly+@temp, @LateNum=@LateNum+1
           end
          end

         end
         if @i=1
          select @OnDuty1 = @OnDuty,@OffDuty1 = @OffDuty
         if @i=2
          select @OnDuty2 = @OnDuty,@OffDuty2 = @OffDuty
         if @i=3
          select @OnDuty3 = @OnDuty,@OffDuty3 = @OffDuty

         set @i=@i+1
         fetch next from cur into @StartTime,@EndTime,@Hours,@OWType,@OShiftID,@OPeriodTime
        end
        close cur
        deallocate cur
        set @temp = @OwTime
        set @temp = @temp /30 * 30
        set @OWTime = @temp
        
        if @OWMul=1.5
         set @OW1=isNull(@OW1,0) + @OWTime
        if @OWMul=2
         set @OW2=isNull(@OW2,0) + @OWTime
        if @OWMul=3
         set @OW3=isNull(@OW3,0) + @OWTime
        --通宵及夜班 
    /*
       select @onduty1 as fdate into #tx1
        union all
        select @OffDuty1
        union all
        select @onduty2
        union all
        select @OffDuty2
        union all
        select @onduty3
        union all
        select @OffDuty3
        select @txMin=min(fdate),@txMax=max(fdate) from #tx1 where fdate is not null
        drop table #tx1
    */
       if(@offduty3 is not null)
       begin
        set @txMax=@offduty3
        set @txMin=@Onduty1
       end
       else if(@offduty2 is not null)
       begin
        set @txMax=@offduty2
        set @txMin=@Onduty1
       end
       else
       begin
        set @txmax=@offduty1
        set @txMin=@Onduty1
       end
       if(left(@orgcode,3)<>'002')
       begin
        if @txMax>=dateadd(day,1,@date)+' 02:00:00'
         set @TongXiao=1
        else
         set @TongXiao=0
        if(@TongXiao=0) and left(@orgcode,0)<>'002'
        begin
         if @txMax>=dateadd(day,1,@date)+' 00:00:00'
         set @YeBan=1
        else
         set @YeBan=0
        end
        
        
       end
       else
       begin
        if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 06:30:00')
         set @TongXiao=1
        else
         set @TongXiao=0
        if(@TongXiao=0)
        begin
         if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
          set @YeBan=1
         else
          set @YeBan=0
        end 
        
       end
        set @OW1 = @OW1/60 --cast(cast(@OW1 as int)/15*15  as decimal(18,2)) /60
        set @OW2 = @OW2/60 --cast(cast(@OW2 as int)/15*15  as decimal(18,2)) /60
        set @OW3 = @OW3/60 --cast(cast(@OW3 as int)/15*15  as decimal(18,2)) /60
        set @NegLect = @NegLect/60
        
        select @BK = count(FDate) from WorkTimeAdd where StaffNo=@StaffNo and DateDiff(Day,FDate,@Date)=0 and FType='001'

        if exists (select 1 from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo)
         update WorkDayRecord set OnDuty1=@OnDuty1,OffDuty1=@OffDuty1,OnDuty2=@OnDuty2,OffDuty2=@OffDuty2,OnDuty3=@OnDuty3,OffDuty3=@OffDuty3,
         OverWork1=@OW1,OverWork2=@OW2,OverWork3=@OW3,
         Neglect=@NegLect,LateEarly=@LateEarly,shiftID=@ShiftID,
         YeBan=@YeBan,TongXiao=@TongXiao,BK=@BK,LateNum=@LateNum,WX=0,ShouldTime=null,RealTime=null,CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,AppOWHours=@OWApply,OWAbnormFlag=0
         where FDate=@Date and StaffNo=@StaffNo
        else
         insert into WorkDayRecord (shiftid,StaffNo,FDate,OnDuty1,OffDuty1,OnDuty2,OffDuty2,OnDuty3,OffDuty3,OverWork1,OverWork2,OverWork3,NegLect,LateEarly,YeBan,TongXiao,BK,LateNum,AppOWHours,OWAbnormFlag)
         Values (@shiftid,@StaffNo,@date,@OnDuty1,@OffDuty1,@OnDuty2,@OffDuty2,@OnDuty3,@OffDuty3,@OW1,@OW2,@OW3,@Neglect,@LateEarly,@YeBan,@TongXiao,@BK,@LateNum,@OWApply,0)
        
        if @HolidayType='001'

         update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=8 where staffno=@staffNo and FDate=@date
       end
      end
     end
     else
     begin --不放假
      ---------------------------------1210修改
      
      select @OnDuty=null,@OffDuty=null
      select @OnDuty=a.Fdate,@OffDuty=b.Fdate from employeeLeave a left join WorkRepair b on a.LeaveID=b.LeaveID
      where Staffno=@Staffno and a.FDate=@date and WholeDay=3 and HolidayType='011' and a.status=3
      
      if @OnDuty is not null--调休一天
      begin
       
       delete from workDayRecord where staffno=@staffno and fdate=@date
       if @OffDuty is  null--补班
       begin

        select @LeaveHours=480,@LeaveDayType='011',@LeaveDayPart=3,@ShouldTime=8
        insert into workDayRecord (staffno,shiftid,fdate,ShouldTime,AdjustRest) values (@staffno,@defaultshift,@date,@ShouldTime,480)
        
        goto OverWorkCal
       end
       else
        goto goOn
      end
      
      if exists (select 1 from EmployeeLeave a left join WorkRepair b on a.LeaveID=b.LeaveID where a.Staffno=@Staffno and a.HolidayType='011' and a.WholeDay=3 and b.FDate=@date and datediff(month,'2008-6-1',a.fdate)>=0) and @OWMul>1.5
       set @OWMul=1.5
      --如果入职日期大于统计日期或统计日期大于当前日期则以无薪处理
      if @date<@DutyDate or @date>getdate()
      begin
       
       if exists (select 1 from WorkDayRecord where StaffNo=@StaffNo and FDate=@Date)
        update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=0,LateEarly=0,Neglect=0,OverWork1=0,OverWork2=0,OverWork3=0,CC=0,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=8,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=@OWApply
        where Staffno=@StaffNo and FDate=@Date
       else
        insert into WorkDayRecord (StaffNo,FDate,ShiftID,ShouldTime,WX)
        values (@StaffNo,@Date,@defaultshift,8,8)
       goto goOn
      end

     

      --如果离职日期小于等于统计日期则以无薪处理
      if @DimissionDate<=@date
      begin

       if exists (select 1 from WorkDayRecord where StaffNo=@StaffNo and FDate=@Date)
        update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=0,LateEarly=0,Neglect=0,OverWork1=0,OverWork2=0,OverWork3=0,CC=0,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=8,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=@OWApply
        where Staffno=@StaffNo and FDate=@Date
       else
        insert into WorkDayRecord (StaffNo,FDate,ShiftID,ShouldTime,WX)
        values (@StaffNo,@Date,@defaultshift,8,8)
       goto goOn
      end
      
      --获取当天请假数据
      select top 1 @LeaveDayPart=isNull(WholeDay,0),@LeaveDayType=HolidayType,@LeaveHours=case WholeDay when 3 then 480 when 2 then 240 when 1 then 240 else Hours * 60 end from EmployeeLeave where StaffNo=@Staffno and FDate=@date and Status=3 and WholeDay not in (1,2) order by WholeDay desc
      
      
      --判断如果请假一整天
      if @LeaveDayPart=3
      begin
      
       select @LeaveHours=480,@ShiftID=@defaultshift,@OnDuty=null,@OffDuty=null,
       --@OnDuty1=null,@OffDuty1=null,@OnDuty2=null,@OffDuty2=null,@OnDuty3=null,@OffDuty3=null,
       @OWTime=0,@Hours=0,@ShouldTime=8
       if @LeaveDayType in ('003','014')
        set @RealTime=8
       goto OverWorkCal
      end
      --判断是否更新了班次
      select @ShiftID=ShiftID from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo and UpdateFlag=1

      create table #t2(
      ShiftID nvarchar(10),PeriodTime smallint,AheadTime int,AbsenceTimeA int,上班时间 datetime,
      AbsenceTimeL int,下班时间 datetime,DelayTime int,OWTime int,AdjustRest int,WX int)
      
      --班次信息,如果更新过班次,则以设置的班次为准
      
      if isNull(@ShiftID,'') <> ''
      begin
       insert into #t2
       select
       ParentID,
       PeriodTime,
       AheadTime,
       datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
       --AbsenceTimeA,
       dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
       datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
       --AbsenceTimeL,
       dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
       DelayTime,OWTime,AdjustRest,WX
       from ShiftDetail where ParentID=@ShiftID
      end
      else
      begin --获取该员工所有班次
       
       
       insert into #t2
       select
       ShiftID,
       PeriodTime,
       AheadTime,
       datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
       --AbsenceTimeA,
       dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
       datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
       --AbsenceTimeL,
       dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
       DelayTime,OWTime,AdjustRest,WX
       from EmployeeShift a
       inner join ShiftDetail b on a.ShiftID=b.ParentID
       where a.EmployeeID=@EmployeeID
      end
      
      select
      a.打卡时间,
      b.ShiftID as 班次编号,
      b.PeriodTime as 时间段,
      isNull(b.OWTime,0) as 加班,
      isNull(b.AdjustRest,0) as 调休,
      isNull(b.WX,0) as 无薪,
      cast(datediff(second,打卡时间,b.上班时间) as decimal(10,2))/60 as 上班时间差,
      cast(datediff(second,b.下班时间,打卡时间) as decimal(10,2))/60 as 下班时间差
      into #t3
      from #t1 a ,#t2 b
      where
      cast(datediff(second,a.打卡时间,b.上班时间) as decimal(10,2))/60 between AbsenceTimeA*-1 and AheadTime
      or cast(datediff(second,b.下班时间,a.打卡时间) as decimal(10,2))/60 between AbsenceTimeL*-1 and DelayTime
      

      select top 1 @shiftid=班次编号
       from(
       select 班次编号,时间段,min(abs(case when 上班时间差<0 then 上班时间差-50 else 上班时间差 end)) as 上班时间差,min(abs(case when 下班时间差<0 then 下班时间差-50 else 下班时间差 end)) as 下班时间差
       from #t3
       group by 班次编号,时间段)a group by 班次编号
       order by sum(上班时间差+下班时间差)
      /*
      select top 1 @ShiftID=班次编号 from #t3
      group by 班次编号
      order by count(班次编号) desc,sum(abs(上班时间差)),min(打卡时间)
      */
     
      --没有找到匹配的班次,则取默认班次
      IF @ShiftID is null
      begin
       select top 1 @ShiftID=ShiftID from EmployeeShift where EmployeeID=@EmployeeID
       if @ShiftID is null
        set @ShiftID=@defaultshift
      end
      
      --置空
      select @OnDuty=null,@OffDuty=null,@OnDuty1=null,@OffDuty1=null,@OnDuty2=null,@OffDuty2=null,@OnDuty3=null,@OffDuty3=null,
      @ShouldTime=0, @OWTime=0,@StartTime=null
      
      

    2011年11月1日 13:02
  •   --上班前加班 20090310修改,上班前多次加班 
      select top 1 @StartTime=StartTime,@EndTime=EndTime,@Hours=Hours,@OWType=FType from overwork
      where datediff(day,StartTime,@Date)=0 and StartTime>dateadd(hour,7,@date)
      and StartTime<(select 上班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=1)
      and StartTime>isNull(@MaxDate,@date)
      and FType in ('001','003')
      and StaffNo=@StaffNo and Status=3
      and @RepairFlag=0
      
      if @StartTime is Not Null
      begin
       set @OWApply = @OWApply + @Hours
       select @OnDuty = min(打卡时间)
       from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=120
       and 打卡时间>isNull(@maxDate,@date)
       
       if @OnDuty is not null
       begin
        
        select @OffDuty=min(打卡时间) from #t1 where 打卡时间>@OnDuty and abs(datediff(minute,@EndTime,打卡时间))<=180
        
        if @OffDuty is not null
        begin
         set @temp=datediff(minute,@OnDuty,@OffDuty)
         if @temp > @Hours
          set @temp=@Hours

         set @temp = @temp /30 * 30
         if @OWMul=1.5
          set @OW1=@OW1 + @temp
         if @OWMul=2
          set @OW2=@OW2 + @temp
         if @OWMul=3
          set @OW3=@OW3 + @temp
        end
       end
       if @OWType='003' --必须计算迟到及旷工
       begin
        if @OnDuty is Null or @OffDuty is Null
         set @NegLect = @NegLect + @Hours
        else
        begin
         set @temp=datediff(minute,@StartTime,@OnDuty)
         if @temp>0
         begin
          if @temp>30
           set @NegLect=@NegLect + @temp
          else
           select @LateEarly=@LateEarly+@temp,@LateNum=@LateNum+1
         end
        end
       
       end
      end
      
      
      if @OnDuty is not null and @OffDuty is not null
       set @maxDate=@OffDuty

      
     
      --取上下班时间
      if @shiftID is not null
      begin
       select PeriodTime,cast(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))-RestTime as decimal(10,2))/60 as 应勤,
       AdjustRest as 调休,WX as 无薪,OWTime as 加班 into #t4
       from ShiftDetail where ParentID=@ShiftID

       -------班次的时间段
       declare @PeriodCount smallint,@shiftadd bit
       set @shiftadd=0
       select @i=0,@PeriodCount=count(ParentID) from ShiftDetail where ParentID=@ShiftID
       if @PeriodCount=1 and exists(select 1 from employeeleave where staffno=@staffno and wholeday=0 and fdate=@date)
       begin
        
        declare @ons datetime,@offs datetime,@shiftcc int
        select @ons=convert(char(11),dateadd(day,ondutyday,@date),120)+OnDutyTime+':00',@offs=convert(char(11),dateadd(day,offdutyday,@date),120)+OffDutyTime+':00'
         from shiftdetail where parentid=@shiftid
        select @shiftcc=count(1) from #t1
         where 打卡时间 between @ons and @offs
        if(@shiftcc>=2)
         select @PeriodCount=@PeriodCount+1,@shiftadd=1
       end
       while @i<@PeriodCount
       begin
        select @i=@i+1,@OnDutyBal=0,@OffDutyBal=0,@OnDuty=null,@OffDuty=null,@OWTime=0,@WXS=0,@AdjustRestS=0
        
        if exists (select 1 from #t2 where ShiftID=@ShiftID and PeriodTime=1 and 上班时间>getdate())
        begin
         
         select @WX = @WX + 应勤 from #t4 where PeriodTime=@i
         continue
        end
        --请假 上午和下午
        
        if exists (select 1 from EmployeeLeave where staffno=@staffno and fdate=@date and Status=3 and WholeDay=@i)
        begin
         Declare @lType varchar(3),@lHours int
         select @LeaveDayPart = @i,@lType = HolidayType from EmployeeLeave where staffno=@staffno and FDate=@date and status=3 and WholeDay=@i
         if @shiftadd=1
         select @lHours = datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))- RestTime
         from ShiftDetail where PeriodTime=1 and ParentID=@ShiftID
         else
         select @lHours = datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))- RestTime
         from ShiftDetail where PeriodTime=@i and ParentID=@ShiftID
         if @lType='001' --事假
         begin
          select @SJ = @SJ + @lHours/60.0
          continue
         end
      
         if @lType='002' --病假
         begin
          set @BJ = @BJ + @lHours/60.0
          continue
         end
      
         if @lType in ('003','014')--出差/外勤
         begin
          select @CC = @CC + @lHours/60.0,@RealTime = @RealTime + @lHours/60.0

          continue
         end
      
         if @lType='004' --年假
         begin
          set @NJ = @NJ + @lHours /60.0
          continue
         end
      
         if @lType='005' --婚假
         begin
          set @HJ = @NJ + @lHours/60.0
          continue
         end
      
         if @lType='006' --产假
         begin
          set @CJ = @CJ + @lHours/60.0
          continue
         end
      
         if @lType='007' --陪产假
         begin
          set @PCJ = @PCJ + @lHours/60.0
          continue
         end
      
         if @lType='008' --工伤假
         begin
          set @GSJ = @GSJ + @lHours/60.0
          continue
         end
      
         if @lType='009' --丧假
         begin
          set @SaJ = @Saj + @lHours/60.0
          continue
         end
      
         if @lType='010' --因公放假
         begin
          set @YGFJ = @YGFJ + @lHours/60.0
          continue
         end
         
         if @lType = '011' --调休
         begin
          set @AdjustRest = @AdjustRest + @lHours
          continue
         end
      
         if @lType = '012' --无薪
         begin
          set @WX = @WX + @lHours/60.0
          continue
         end
        end
        declare @lhTotal decimal(18,2)
        select @lhTotal=sum(hours) from employeeleave where staffno=@staffno and FDate=@date and status=3 and WholeDay=0
        if(@lhTotal>=8)
         continue
        
        --取上班时间
        if @shiftadd=1
         select top 1 @OnDuty=打卡时间,@OnDutyBal=上班时间差,@temp=加班,@AdjustRestS=调休,@WXS = 无薪 from #t3 a
         inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
         where b.ShiftID=@shiftID and a.时间段=1 and 上班时间差 between b.absenceTimeA*-1 and b.AheadTime  and 打卡时间>isNull(@maxDate,@date)
         order by 上班时间差 desc--abs(上班时间差)
        else
         select top 1 @OnDuty=打卡时间,@OnDutyBal=上班时间差,@temp=加班,@AdjustRestS=调休,@WXS = 无薪 from #t3 a
         inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
         where b.ShiftID=@shiftID and a.时间段=@i and 上班时间差 between b.absenceTimeA*-1 and b.AheadTime  and 打卡时间>isNull(@maxDate,@date)
         order by 上班时间差 desc--abs(上班时间差)

        if exists (select 1 from #t2 where ShiftID=@ShiftID and PeriodTime=@i and 下班时间>getdate())
        begin
         select @WX = @WX + 应勤 from #t4 where PeriodTime=@i
         continue
        end
        
        --取下班时间
        
        if (@OnDuty is not null)
        begin
         if @shiftadd=1
          select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #t3 a
          inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
          and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) and 打卡时间>@OnDuty
          where b.ShiftID=@shiftID and a.时间段=1
          order by 下班时间差
         else
         begin
          
          select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #t3 a
          inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
          and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) and 打卡时间>@OnDuty
          where b.ShiftID=@shiftID and a.时间段=@i
          order by 下班时间差
          
         end
        end
        else

         begin
         if (select count(distinct 打卡时间) from #t3 where 班次编号=@SHiftID and 打卡时间<=(select min(打卡时间) from #t3 where 班次编号=@ShiftID and 打卡时间>(select 下班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i+1)))=2 and @PeriodCount>=2 and @i=1
          goto g1
         if isNull(@LeaveHours,0)<(select datediff(minute,上班时间,下班时间) from #t2 where ShiftID=@ShiftID and PeriodTime=@i)
         begin
          
          select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #t3 a
          inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
          and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) 
          where b.ShiftID=@shiftID and a.时间段=@i
          order by 下班时间差
          if exists(select 1 from overwork where staffno=@staffno and status=3 and abs(datediff(minute,@OffDuty,starttime))<30)
           set @OffDuty=null
         end
         end
        g1:
        set @AdjustRest = @AdjustRest + @AdjustRestS/60
        set @WX = @WX + @WXS/60
        
        if @OnDuty is not null and @OffDuty is not null
        begin
         
         if @temp>=isNUll(@LeaveHours,0)
         set @OWTime = @OWTime + @temp-@LeaveHours
         if(@shiftadd<>1)
         begin
          select
          @LateEarly=@LateEarly + case when @OnDutyBal<0 then ceiling(abs(@OnDutyBal)) else 0 end + case when @OffDutyBal<0 then ceiling(abs(@OffDutyBal)) else 0 end
          
          if(@LateEarly>0 and @HolidayType<>'004')
           set @LateNum=@LateNum+1
         end
       
        if @i=1
         select @OnDuty1=@OnDuty,@OffDuty1=@OffDuty
        if @i=2
         select @OnDuty2=@OnDuty,@OffDuty2=@OffDuty
        if @i=3
         select @OnDuty3=@OnDuty,@OffDuty3=@OffDuty
        
        declare @onTemp datetime,@offTemp datetime
        
     
       select @onTemp=convert(char(11),dateadd(day,ondutyday,@date),120)+Ondutytime+':00',@offTemp=convert(char(11),dateadd(day,offdutyday,@date),120)+offdutytime+':00' from shiftdetail where periodtime=@i and parentid=@shiftid
         if(@shiftadd=1)
         begin
         
          if(@onDuty<@onTemp)
            set @onduty=@onTemp
           if(@offDuty>@offtemp)
            set @offduty=@offtemp
           set @RealTime = @RealTime + datediff(minute,@OnDuty,@OffDuty)/60.0
          --select @onduty,@
         end
         else
          begin
          /*
          if datediff(minute,@OnDuty,@OffDuty)>(select 应勤*60 from #t4 where PeriodTime=@i)
           select @RealTime = @RealTime + 应勤 from #t4 where PeriodTime = @i
          else
          begin
           set @RealTime = @RealTime + datediff(minute,case when @OnDuty<@ontemp then @ontemp else @onduty end,case when @OffDuty>@offtemp then @offtemp else @offduty end)/60.0
          end
          */
          declare @realtemp decimal(18,2)
          set @realtemp=datediff(minute,case when @OnDuty<@ontemp then @ontemp else @onduty end,case when @OffDuty>@offtemp then @offtemp else @offduty end)
          if datediff(minute,@ontemp,@offtemp)-@realtemp<15
          begin
           set @RealTime = @RealTime + datediff(minute,@ontemp,@offtemp)/60.0
           --set @neglect = @neglect +
          end
          else
           
           set @RealTime = @RealTime + @realtemp/60.0
          
         end
         
        end
        else
        begin
     
         if isNull(@DayPart,'')<>cast(@i as varchar(1)) and isNull(@HolidayType,'')<>'001'
         select @Neglect = @Neglect + 应勤 *60  from #t4 where PeriodTime=@i
         
        end
      
        set @Hours = 0
        --前直落处理
        

        select @Hours=Hours,@StartTime=StartTime,@EndTime=EndTime from OverWork
        where StaffNo=@StaffNo and EndTime=(select 上班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i)
        and FType='004' and Status=3

        if @Hours>0
        begin
         set @OWApply = @OWApply + @Hours
         set @temp = isNull(datediff(minute ,@OnDuty,@EndTime),0)
         if @temp>@Hours
          set @temp=@Hours

         set @OWTime = @OWTime + @temp/30*30
         set @Hours=0
        end

        declare @OffDutyTime smalldatetime
        select @OffDutyTime = 下班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i
        --后直落处理
        
         
        select @Hours=Hours,@StartTime=StartTime,@EndTime=EndTime from OverWork
        where StaffNo=@StaffNo and StartTime=@OffDutyTime--(select 下班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i)
        and FType='002' and Status=3
        if @Hours<=0 and @RepairFlag=1
        select @Hours = datediff(Minute,@OffDutyTime,EndTime),@StartTime=@OffDutyTime,@EndTime=EndTime from overwork
        where StaffNo=@Staffno and @OffDutyTime between StartTime and EndTime
        if @Hours>0
        begin
         set @OWApply = @OWApply + @Hours
         set @temp = isNull(datediff(minute ,@StartTime,@OffDuty),0)
         if @temp>@Hours
          set @temp=@Hours
         set @OWTime = @OWTime + @temp/30*30
        end
       
        
        if @DayPart=cast(@i as varchar(1)) and @OnDuty is not null and @OffDuty is not null
        begin
         set @temp = datediff(minute ,@OnDuty,@OffDuty)
         if(@shiftadd=1)
          select @temp=case when @temp>应勤 then 应勤*60 else @temp end from #t4 where periodTime=1
         else
          select @temp=case when @temp>应勤 then 应勤*60 else @temp end from #t4 where periodTime=@i
         set @OWTime= @temp/30*30
         if @OWMul=1.5
          set @OW1=@OW1+isNull(@OWTime,0)
         
         if @OWMul=2
          set @OW2=@OW2+isNull(@OWTime,0)

         if @OWMul=3
          set @OW3=@OW3+isNull(@OWTime,0)
        end
        else
        begin
         
         select @OW1 = @OW1 + isNull(@OWTime ,0)
         
        end


       
        if @OffDuty is not null and @OnDuty is not null
         set @maxDate=isNull(@OffDuty,@OnDuty)
        
        
       end
       
       if object_id('tempdb..#t4') is not null
       drop table #t4
      end

    2011年11月1日 13:03
  •   --计算应勤
      declare @OWShift decimal(18,2),@owrest decimal(18,2),@owshiftFact  decimal(18,2)
      select @ShouldTime=cast(sum(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime)))-sum(RestTime) + sum(AdjustRest) + sum(WX)  as decimal(10,2))/60,
      @OWShift=sum(OWTime),@owshiftFact=sum(OWTime),@owrest=sum(resttime),@realtime=case when @shiftadd=1 then  @realtime-sum(RestTime)/60.0 else @realtime end
      from ShiftDetail where ParentID=@ShiftID
      if(@realtime<0)
       set @realtime=0
      
      
      
      if @DayPart is not null and @HolidayType not in ('001' ,'004')
      select @ShouldTime=@ShouldTime-
      cast(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime)) as decimal(10,2))/60
      from ShiftDetail where ParentID=@ShiftID and PeriodTime=cast(@DayPart as smallint)
      

      if(@lateearly<0)
       set @lateearly=0
      if(@realtime>@shouldtime)
       set @realtime=@shouldtime

      
    OverWorkCal:
      
      if object_id('tempdb..#t2') is not null
       select @OnDuty = min(上班时间) ,@OffDuty=max(下班时间) from #t2 where ShiftID=@ShiftID
      else begin
       set @OffDuty=@date
      end
      
      declare cur cursor for
      
      select StartTime,EndTime,Hours,FType from overwork
      where (datediff(hour,@OffDuty,StartTime)<2 or
      --@OffDuty=StartTime or
      datediff(hour,convert(varchar(10),@date,120) + ' 23:59:59',StartTime)<=4
      )
      and StartTime>isNull(@maxDate,@date)
      and StaffNo=@StaffNo and Status=3 and (shiftid is null or ftype='002')
      order by StartTime

      open cur
      fetch next from cur into @StartTime,@EndTime,@Hours,@OWType
      
      set @OWTime=0
      while @@Fetch_status=0
      begin
       select @OWApply = @OWApply + @Hours,@OnDuty=null,@OffDuty=null
       
      if exists (select 1 打卡时间
       from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
       and 打卡时间<=@StartTime
       and 打卡时间>isNull(@maxDate,@date))
        
       select top 1 @OnDuty = 打卡时间
       from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
       
       and 打卡时间>isNull(@maxDate,@date)
       order by datediff(minute,@StartTime,打卡时间) asc
       
       else
        select top 1 @OnDuty = 打卡时间
         from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
       
         and 打卡时间>isNull(@maxDate,@date)
         order by Abs(datediff(minute,@StartTime,打卡时间)) asc
       
       declare @endTime1 smalldatetime
       select @endTime1=dateadd(minute,@hours,@StartTime)
       
       
        
       if @OnDuty is not null
       begin
        select @maxDate=@OnDuty
        select @OffDuty=min(打卡时间) from #t1 where 打卡时间>@OnDuty and abs(datediff(minute,@EndTime,打卡时间))<=300
        if @OffDuty is not null
        begin
         set @maxDate=@OffDuty
         --20090212修改,加班结束时间不对
         set @temp=datediff(minute,case when @OnDuty< @StartTime then @StartTime else @OnDuty end,case when @OffDuty<@endTime1 then @OffDuty else @endTime1 end)
         if @temp > @Hours
          set @temp=@Hours
         
         set @temp = @temp /30 * 30
         
         if @OWMul=2
          set @OW2=@OW2 + @temp
         else
         begin
          declare @OnDutytemp smalldatetime,@OffDutytemp smalldatetime
          if object_id('tempdb..#t2') is not null
           select @OnDutytemp=上班时间,@OffDutytemp=下班时间 from #t2 where ShiftID=@ShiftID and PeriodTIme=@DayPart
          if @OWMul=3 and (@dayPart='0' or (@StartTIme=@OnDutytemp and @EndTime=@OffDutytemp))
           set @OW3=@OW3 + @temp
          else
           set @OW1=@OW1 + @temp
         end
        end
       end
       if @OWType='003' --必须计算迟到及旷工
       begin
        if @OnDuty is Null or @OffDuty is Null
         set @NegLectOW = @NegLectOW + @Hours
        else

        begin
         set @temp=datediff(minute,@StartTime,@OnDuty)
         if @temp>0
         begin
          if @temp>30
           set @NegLectOW=@NegLectOW + @temp
          else
           select @LateEarly=@LateEarly+@temp ,@LateNum=@LateNum+1
         end
        end
       end
       if @OnDuty is not Null
        select @OnDuty3=@OnDuty
       if @OffDuty is not null
        select @OffDuty3=@OffDuty
       fetch next from cur into @StartTime,@EndTime,@Hours,@OWType
      end
      close cur
      deallocate cur

      if(@OffDuty3 is not null and @OnDuty3 is not null)
      set @maxDate=isNull(@OffDuty3,@OnDuty3)
      if(@offduty3 is not null)
       begin
        set @txMax=@offduty3
        set @txMin=@Onduty1
       end
       else if(@offduty2 is not null)
       begin
        set @txMax=@offduty2
        set @txMin=@Onduty1
       end
       else
       begin
        set @txmax=@offduty1
        set @txMin=@Onduty1
       end
      if(left(@orgcode,3)<>'002')
      begin
       if @txMax>=dateadd(day,1,@date)+' 02:00:00'
        set @TongXiao=1
       else
        set @TongXiao=0
       if(@TongXiao=0)
       begin
        if @txMax>=dateadd(day,1,@date)+' 00:00:00'
         set @YeBan=1
        else
         set @YeBan=0
       end
       
       
      end
      else
      begin
       
       if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 06:30:00')
        set @TongXiao=1
       else
        set @TongXiao=0
       if(@TongXiao=0)
       begin
        if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
         set @YeBan=1
        else
         set @YeBan=0
       end 
      end
      if @HolidayType='004' and @OWshift<=0
      begin
       
       set @WX = @WX+(@Neglect + @LateEarly)/60
       if @LeaveDayTYpe in ('003','014')
        set @WX = @WX - @LeaveHours/60
       if @WX < 0
        set @WX=0
       select @Neglect=0,@LateEarly=0
      end
      

       
      --如果迟到时间大于30分钟则为旷工
      /*
      if @LateEarly>=30
      begin
       set @Neglect = @Neglect + @LateEarly
       select @LateEarly=0, @LateNum  =0
      end
      */
      --如果旷工了并且已请假减去请假时间减
      
      --转换为小时
      
      if @OWShift>0 and @LeaveDayPart=0
      begin
       if @LeaveHours>@OWShift
        set @LeaveHours=@LeaveHours-@OWShift
       else
        select @LeaveHours=0
      end
      set @LeaveHours=@LeaveHours/60

      if exists(select 1 from employeeleave where staffno=@staffno and fdate=@date and wholeday=0 and status=3)
      begin
      select * into #employeeleave from employeeleave where staffno=@staffno and fdate=@date and wholeday=0 and status=3
      declare @ltotal decimal(18,2)
       select @ltotal=sum(hours) from employeeleave where staffno=@staffno and fdate=@date and wholeday=0 and status=3
      if(@ltotal is null)
       set @ltotal=0
      if(@realtime+@ltotal>@shouldtime)
       set @realtime=@shouldtime-@ltotal
     
      while exists(select 1 from #employeeleave)
      begin
          
       declare @leaveid int
       select top 1 @LeaveHours=hours,@leaveid=leaveid,@LeaveDayType=holidaytype from #employeeleave order by leaveid desc
       --如果请假,并且有旷工
      
       if (@lateearly>0 or @neglect>0) and @LeaveHours>0
       begin
        /*
        if @LeaveDayType in('003','013','014')
        begin
         set @realtime=@realtime+@LeaveHours
         if(@realtime>@shouldtime)
          set @realtime=@shouldtime
        end
        else
        */
         if(@realtime+@LeaveHours>@shouldtime)
          set @realtime=@shouldtime
        set @neglect=@neglect-@LeaveHours*60
        
        
        if(@neglect<0)
         set @neglect=0
        
       end

     
       if @lateearly>0 and @LeaveHours>0
       begin
        set @lateearly=@lateearly-@LeaveHours*60.0
        if(@lateearly<0)
         set @lateearly=0
       end
       if @LeaveDayType='001' --事假
       begin
        set @SJ = @SJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@SJ
       end

       if @LeaveDayType='002' --病假

       begin
        set @BJ = @BJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@BJ
       end

       if @LeaveDayType='003' or @LeaveDayType='014'--出差
       begin
        set @CC = @LeaveHours
        if @LeaveDayPart<>3
         set @RealTime = @RealTime + @LeaveHours
        if(@realtime>@shouldtime)
         set @realtime=@shouldtime
       end

       if @LeaveDayType='004' --年假
       begin
        set @NJ = @NJ+@LeaveHours
        
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@NJ
       end

       if @LeaveDayType='005' --婚假
       begin
        set @HJ = @HJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@HJ
       end

       if @LeaveDayType='006' --产假
       begin
        set @CJ = @CJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@CJ
       end

       if @LeaveDayType='007' --陪产假
       begin
        set @PCJ = @PCJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@PCJ
       end

       if @LeaveDayType='008' --工伤假
       begin
        set @GSJ = @GSJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@GSJ
       end

       if @LeaveDayType='009' --丧假
       begin
        set @SaJ = @SaJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@SaJ
       end

       if @LeaveDayType='010' --因公放假
       begin
        
        set @YGFJ = @YGFJ+@LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@YGFJ
       end
       
       if @LeaveDayType = '011' --调休
       begin
        set @AdjustRest = @AdjustRest+@LeaveHours * 60
        
        if(@LeaveHours+@RealTime>@shouldtime)
         set @RealTime=@shouldtime-@LeaveHours
       end

       if @LeaveDayType = '012' --无薪
       begin
        set @WX = @WX + @LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@WX
       end
      
       if @LeaveDayType = '013' --哺乳假
       begin
        set @BRJ = @BRJ+@LeaveHours
        if @LeaveDayPart<>3
        begin
         if @Neglect between 0.1 and 29
         begin
          set @LateEarly=@Neglect
          set @Neglect=0
         end
         
         set @RealTime = @RealTime + @LeaveHours
          if(@realtime>8)
           set @realtime=8
        
        end
       end
       /*
       if(@leavehours>0 and @neglect>0)
         begin
          --set @neglect=@neglect-@leavehours*60
          set @leavehours=0
          if(@neglect<0)
           set @neglect=0
         end
       */
       delete from #employeeleave where leaveid=@leaveid
       
      end
      
      drop table #employeeleave
      end
      else
      begin

       if @Neglect>0 and @LeaveHours>0
       begin
        set @neglect=@neglect-@LeaveHours
        if(@neglect<0)
         set @neglect=0
       end
       if @LeaveDayType='001' --事假
       begin
        set @SJ = @LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@SJ
        goto goUpdate
       end

       if @LeaveDayType='002' --病假

       begin
        set @BJ = @LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@BJ
        goto goUpdate
       end

       if @LeaveDayType='003' or @LeaveDayType='014'--出差
       begin
        set @CC = @LeaveHours
        
        if @LeaveDayPart<>3
         set @RealTime = @RealTime + @LeaveHours

        if(@realtime>@shouldtime)
         set @realtime=@shouldtime
        goto goUpdate

       end

       if @LeaveDayType='004' --年假
       begin
        set @NJ = @LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@NJ
        goto goUpdate

       end

       if @LeaveDayType='005' --婚假
       begin
        set @HJ = @LeaveHours
        goto goUpdate
       end

       if @LeaveDayType='006' --产假
       begin
        set @CJ = @LeaveHours
        goto goUpdate
       end

       if @LeaveDayType='007' --陪产假
       begin
        set @PCJ = @LeaveHours
        goto goUpdate
       end

       if @LeaveDayType='008' --工伤假
       begin
        set @GSJ = @LeaveHours
        goto goUpdate
       end

       if @LeaveDayType='009' --丧假
       begin
        set @SaJ = @LeaveHours
        goto goUpdate
       end

       if @LeaveDayType='010' --因公放假
       begin
        set @YGFJ = @LeaveHours
        goto goUpdate
       end
       
       if @LeaveDayType = '011' --调休
       begin
        set @AdjustRest = @LeaveHours * 60
        goto goUpdate
       end

       if @LeaveDayType = '012' --无薪
       begin
        set @WX = @WX + @LeaveHours
        if(@LeaveHours+@RealTime>8)
         set @RealTime=8-@WX
        goto goUpdate
       end

       if @LeaveDayType = '013' --哺乳假
       begin
        set @BRJ = @LeaveHours
        if @LeaveDayPart<>3
        begin
         if @Neglect between 1 and 29
         begin
          set @LateEarly=@Neglect
          set @Neglect=0
         end

         set @RealTime = @RealTime + @LeaveHours
        end
        goto goUpdate
       end
      end
      if(@lateearly>0 and @owshift>0)
      begin
       if (@lateearly>@owshift)
        select @lateearly=@lateearly-@owshift,@owshift=0
       else
        select @owshift=@owshift-@lateearly,@lateearly=0,@ow1=@owshift-@lateearly
       
      end
      
     
      --迟到的加上实勤
     
      goUpdate:
      select @BK = count(FDate) from WorkTimeAdd where StaffNo=@StaffNo and DateDiff(Day,FDate,@Date)=0 and FType='001'


      if @LeaveDayPart=0
      begin
       
       if @HolidayType='004' and @LeaveDayType='013'
        set @WX = @WX - @LeaveHours
       if @LeaveDayType='004'
        set @WX = @WX - @LeaveHours
       if @WX <0
        set @WX=0
      
       --set @RealTime=@ShouldTime  - @Neglect/60 -@WX
       
       if @LeaveDayType not in ('003','012' ,'013','014','011','010')
       begin
        if(@realtime+@leavehours>@shouldtime)
        set @RealTime = @shouldtime - isNull(@LeaveHours,0)
       end

    2011年11月1日 13:05
  •    --迟到早退并且调休了就用调休减去迟到早退时间
       if @LateEarly>0 and @LeaveHours>0--@AdjustRest>0
       begin
        set @LateEarly=@LateEarly-@LeaveHours*60
        if @LateEarly<0 set @LateEarly=0
        set @LateNum=0
       end

      
      end
      if @LateEarly>=30
      begin
       set @Neglect = @Neglect + @LateEarly
       select @LateEarly=0, @LateNum  =0
      end
      if(@lateearly between 1 and 29)
      begin
       set @realtime=@realtime+@lateearly/60.0
       if(@realtime>@shouldtime)
        set @realtime=@shouldtime
      end
      
      if(@neglect between 0.1 and 29)
      begin
       set @Lateearly=@neglect
       set @realtime=@realtime+@neglect/60.0
       set @neglect=0
       if(@realtime>@shouldtime)
        set @realtime=@shouldtime
      end
      select @Neglect = @Neglect/60,@NegLectOW=@NegLectOW/60.0
      --

      if @LeaveDayPart<>3 and @owshiftFact>0
      begin
       
       select @ShouldTime=@ShouldTime-@owshiftFact/60.0
       
       
       if @RealTime>@ShouldTime
       begin
        select @RealTime=@ShouldTime---@neglect--,@OW1=@OW1-@Neglect*60
        --set @Neglect=0
       end
       if(@neglect>0)
       begin
        if(@OWshift/60>@neglect)
        begin
         select @OW1=@OW1-CEILING(2*@neglect)/2*60
         set @neglect=0
        end
        else
         set @OW1=0
       end
       
       if   @LeaveHours>0
       begin
        set @RealTime=8-@LeaveHours
        
        set @ow1=@owshift-@LeaveHours*60
        set @OWShift=0
       end
       if @realtime<@shouldtime
        set @ow1=0
       if @LateEarly>0
       begin
        set @OW1 = @OW1 - @LateEarly
        select @LateEarly=0, @LateNum=0
       end  
       if(@realtime+@neglect>@shouldtime)
        set @neglect=@shouldtime-@realtime  
      end 

      set @OW1=cast(@OW1/30 as int) *30
      if @OW1<0 set @OW1=0
      select @OW1=@OW1 / 60, @OW2=@OW2 / 60, @OW3=@OW3 / 60,@Neglect=@Neglect+@NeglectOW
     
      if exists(select 1 from WorkDayRecord where StaffNo=@StaffNo and Datediff(day,FDate,@Date)=0)
       update WorkDayRecord set ShiftID=@ShiftID,OnDuty1=@OnDuty1,OffDuty1=@OffDuty1,
       OnDuty2=@OnDuty2,OffDuty2=@OffDuty2,
       OnDuty3=@OnDuty3,OffDuty3=@OffDuty3,
       OverWork1=@OW1,OverWork2=@OW2,OverWork3=@OW3,
       ShouldTime=@ShouldTime,
       RealTime=@Realtime,
       LateEarly=@LateEarly,
       LateNum=@LateNum,
       Neglect=@Neglect,
       BK=@Bk,
       YeBan=@YeBan,
       TongXiao=@TongXiao,
       WX=@WX,
       CC=@cc,
       BJ=@BJ,
       SJ=@SJ,
       HJ=@HJ,
       SaJ=@Saj,
       GSJ=@GSJ,
       NJ=@NJ,
       CJ=@CJ,
       PCJ=@PCJ,
       YGFJ=@YGFJ,
       BRJ=@BRJ,
       AdjustRest = @AdjustRest,
       AppOWHours=@OWApply,OWAbnormFlag=0
       where StaffNo=@StaffNo and Datediff(day,FDate,@Date)=0
      else
       insert into WorkDayRecord(StaffNo,FDate,ShiftID,OnDuty1,OffDuty1,OnDuty2,OffDuty2,OnDuty3,OffDuty3,ShouldTime,RealTime,LateEarly,LateNum,Neglect,BK,YeBan,TongXiao,OverWork1,OverWork2,OverWork3,WX,AdjustRest,
       CC,BJ,SJ,HJ,SaJ,GSJ,NJ,CJ,PCJ,YGFJ,BRJ,AppOWHours,OWAbnormFlag
       )
       values (@StaffNo,@Date,@ShiftID,@OnDuty1,@OffDuty1,@OnDuty2,@OffDuty2,@OnDuty3,@OffDuty3,@ShouldTime,@RealTime,@LateEarly,@LateNum,@Neglect,@BK,@YeBan,@TongXiao,@OW1,@OW2,@OW3,@WX,@AdjustRest,
       @CC,@BJ,@SJ,@HJ,@SaJ,@GSJ,@NJ,@CJ,@PCJ,@YGFJ,@BRJ,@OWApply,0)

      if object_id('tempdb..#t2') is not null
       drop table #t2
      if object_id('tempdb..#t3') is not null
       drop table #t3
     end
     
    goOn:
     drop table #t1
     
     set @date = dateAdd(day,1,@date)
    end
    -----------------更新月报表---------------
    set @date = dateAdd(day,-1,@date)

    select @OW1 = sum(isNull(OverWork1,0)),@OW2=sum(isNull(OverWork2,0)),@OW3=sum(isNull(OverWork3,0)) ,@AdjustRest=sum(IsNull(AdjustRest,0))
    from WorkDayRecord
    where StaffNo=@StaffNo and Datediff(month,@date,FDate)=0
    group by StaffNo

    if @AdjustRest>0 and (left(@OrgCode,3) <>'002' or (left(@OrgCode,3)='002' and @SalaryMode='002') )
    begin
     set @temp = @AdjustRest --* 60
     if  @temp > 0
     begin
      declare curA cursor for
      select FDate from EmployeeLeave where HolidayType='011' and StaffNo=@StaffNo and datediff(month,@date,FDate)=0 and Status=3
      open curA  
      fetch next from curA into @edate
      while @@FETCH_STATUS=0
      begin

       set @i=null
       select @i = AdjustRest from WorkDayRecord where StaffNo=@StaffNo and Fdate=@edate   
      
       if @i is not null --and @temp >= isNull(@i,0)
       and not exists (select 1 from EmployeeLeave a inner join WorkRepair b on a.LeaveID=b.leaveID where staffno=@staffno and a.fdate=@edate and a.WholeDay=3)
       begin
        
        
        update WorkDayRecord set RealTime = case when isnull(RealTime,0) + (AdjustRest/60.0) >8 then 8 else isnull(RealTime,0) + (AdjustRest/60.0) end
         where StaffNo=@StaffNo and Fdate=@edate
        set @temp = @temp - @i
        if @temp<=0
         break
       end
       
       fetch next from curA into @edate   
      end
      close curA
      deallocate curA
     end
     set @AdjustRest = @AdjustRest/60
     --聚能调休先扣2倍加班

     if left(@OrgCode,3) in ('005','010')
     begin
      --先从2倍加班扣调休,如果2倍加班>=调休时间,则将加班减去调休时间,调休置为0
      if @OW2 >= @AdjustRest
      begin
       set @OW2=@OW2-@AdjustRest
       set @AdjustRest=0
      end
      else
      begin--如果2加班时间小于调休时间,则将调休时间减去2倍加班时间,2倍加班时间置0
       set @AdjustRest=@AdjustRest-@OW2
       set @OW2=0
       --判断1.5倍加班时间是否>=调休时间,原则同上 
       if @OW1>=@AdjustRest
       begin
        set @OW1=@OW1-@AdjustRest
        set @AdjustRest=0
       end
       else
       begin
        set @AdjustRest=@AdjustRest-@OW1
        set @OW1=0
        
       end
      end
     end
     else
     begin
      --先从1.5倍加班扣调休,如果1.5倍加班>=调休时间,则将加班减去调休时间,调休置为0
      if @OW1 >= @AdjustRest
      begin
       set @OW1=@OW1-@AdjustRest
       set @AdjustRest=0
      end
      else
      begin--如果1.5加班时间小于调休时间,则将调休时间减去1.5倍加班时间,1.5倍加班时间置0
       set @AdjustRest=@AdjustRest-@OW1
       set @OW1=0
       --判断2倍加班时间是否>=调休时间,原则同上  
       if @OW2>=@AdjustRest
       begin
        set @OW2=@OW2-@AdjustRest
        set @AdjustRest=0
       end
       else
       begin
        set @AdjustRest=@AdjustRest-@OW2
        set @OW2=0
       end
      end

     end
     if @AdjustRest > 0
     begin
      if @OW3>=@AdjustRest
      begin
       set @OW3=@OW3-@AdjustRest
       set @AdjustRest=0
      end
      else
      begin
       set @AdjustRest=@AdjustRest-@OW3
       set @OW3=0
      end
     end

     
    end

     

    select
    @OW1=@OW1 + isNull(sum(isNull(OW1,0)),0),
    @OW2=@OW2 + isNull(sum(isNull(OW2,0)),0),
    @OW3=@OW3 + isNull(sum(isNull(OW3,0)),0),
    @Neglect = @Neglect + isNull(sum(isNull(Neglect,0)),0) from OverworkAdjust

    where staffno=@staffno and datediff(month,fdate,@date)=0 and Status=3


    if @DutyLevel<=3 and @DutyLevel<>0 and @SalaryMode='002'
     select @OW1=0,@OW2=0,@OW3=0

     


    declare cur cursor for

    select a.FDate,b.FDate
    from employeeLeave a
    inner join workRepair b on a.LeaveID=b.LeaveID
    left join employees e on a.staffno=e.staffno
    where a.staffno=@Staffno and datediff(month,a.fdate,@Date)=0
    and a.Status=3
    and exists (select 1 from publicHoliday
    where HolidayType='001'
    and e.OrgCode like OrgCode + '%'
    and e.SalaryMode=SalaryMode
    and charindex(e.Gender,Gender)>0 and HolidayDate=b.FDate)


    open cur
    fetch next from cur into @OnDuty,@OffDuty

    while @@Fetch_status=0
    begin

     insert into workDayRecord(staffno,Fdate,ShouldTime,RealTime)
     select @staffno,@OnDuty,ShouldTime,RealTime from WorkDayRecord where staffno=@Staffno and FDate=@OffDuty


     update WorkDayRecord
     set ShouldTime=8,RealTime=8 where Staffno=@Staffno and FDate=@OffDuty

     fetch next from cur into @OnDuty,@OffDuty
    end
    close cur
    deallocate cur

    if  (@SalaryFlag=2 and @WorkFlag=0)
    begin
     if exists (select 1 from workMonth where staffno=@Staffno and datediff(month,fdate,@date)=0)
     delete from workMonth where staffno=@Staffno and datediff(month,fdate,@date)=0

     return
    end

    select @AdjustRest
    select StaffNo,
    DATEADD(day,-day(@date)+1,@date) as 月份,
    sum(case isNull(ShouldTime,0) when 0 then 0 else 1 end) as 应勤,
    --sum(realtime) as 实勤,

    sum(case isNull(ShouldTime,0) when 0 then 0 else cast( isNull(RealTime,0)/ShouldTime as decimal(18,4)) end)
    -  case when left(@OrgCode,3) ='002' and @SalaryMode='001' then 0 else @AdjustRest/8 end  as 实勤,

    SUM(isNull(LateEarly,0))  as 迟到,
    SUM(isnull(LateNum,0)) as 迟到次数,
    SUM(isNull(Neglect,0))  as 旷工 ,

    @OW1 as OW1,
    @OW2 as OW2,
    @OW3 as OW3,
    SUM(isNull(CC,0))/8 as cc,
    SUM(isNull(BJ,0))/8 as bj,
    SUM(isNull(SJ,0))/8 as sj,
    SUM(isNull(HJ,0))/8 as hj,
    SUM(isNull(SaJ,0))/8 as saj,
    SUM(isNull(GSJ,0))/8 as gsj,
    SUM(isNull(CJ,0))/8 as cj,
    SUM(isNull(PCJ,0))/8 as pcj,
    SUM(isNull(YGFJ,0))/8 as ygfj,
    SUM(isNull(NJ,0))/8 as nj,
    sum(isNull(WX,0)+ case when left(@OrgCode,3) ='002' and @SalaryMode='001' then AdjustRest/60  else 0 end) /8+case when left(@OrgCode,3) ='002' and @SalaryMode='001' then 0  else @AdjustRest/8 end    as 无薪,
    sum(isNull(BK,0)) as 补卡,
    sum(isNull(YeBan,0)) as 夜班,
    sum(isNull(TongXiao,0)) as 通宵,

    @EatSum as 餐费,
    @maxDate as MaxDate
    into #tMonth
    from WorkDayRecord 
    where StaffNo=@StaffNo
    and Datediff(month,@date,FDate)=0
    group by StaffNo

     


    --更新月报表
    if not exists(select 1 from workmonth where staffno=@staffno and datediff(month,fdate,DATEADD(day,-day(@date)+1,@date))=0)
     insert into workmonth (
     StaffNo,FDate,ShouldTime,RealTime,LateEarly,LateNum,Neglect,OverWork1,OverWork2,OverWork3,
     CC,BJ,SJ,HJ,SaJ,GSJ,CJ,PCJ,YGFJ,NJ,WX,BK,YeBan,TongXiao,EatTotal,LastDate)
     select * from #tMonth
    else
     update workmonth
     set shouldtime=a.应勤,realtime=a.实勤,
     lateEarly=a.迟到,lateNum=a.迟到次数,Neglect=a.旷工,OverWork1=OW1,OverWork2=OW2,OverWork3=OW3,
     cc=a.cc,bj=a.bj,sj=a.sj,hj=a.hj,saj=a.saj,gsj=a.gsj,cj=a.cj,pcj=a.pcj,ygfj=a.ygfj,nj=a.nj,wx=a.无薪,
     bk=a.补卡,yeban=a.夜班,tongxiao=a.通宵,EatTotal=a.餐费,LastDate=a.MaxDate
     from #tMonth a inner join workmonth w on a.staffno=w.staffno and a.月份=w.fdate

    drop table #tMonth

    set nocount off

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    2011年11月1日 13:06
  • 你这也太夸张了吧。。。


    it's time to start living the life you are imagined.
    2011年11月7日 2:40
  • 没办法啊,确实有这么多的代码啊。
    2011年11月7日 3:18
  • 你好,

    这条语句,set @temp = @temp /30 * 30 是把结果按30取整来算的,精确到30分钟。当@temp<30 时,@temp = 0; 你可以测试一下下面的代码,来证实一下:

    declare @temp int
    set @temp = 29
    select @temp/30*30,@temp
    set @temp = 31
    select @temp/30*30,@temp

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    2011年11月7日 7:12
    版主
  • 你好,

    这条语句,set @temp = @temp /30 * 30 是把结果按30取整来算的,精确到30分钟。当@temp<30 时,@temp = 0; 你可以测试一下下面的代码,来证实一下:

     

    declare @temp int
    set @temp = 29
    select @temp/30*30,@temp
    set @temp = 31
    select @temp/30*30,@temp

     

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    我试过将所有

    set @temp = @temp /30 * 30
    改为

    set @temp = @temp/(30+0.0)*30

    还是不行。

    2011年11月7日 10:10
  • 请问这条语句  set @temp = @temp/(30+0.0)*30 是想完成什么操作?

    如果是像精确到一分钟的话,试试看用@temp,按照他的模式一分钟公式是这样的:set @temp = @temp/1*1.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    2011年11月8日 5:28
    版主
  • 还是不行。改成@temp/1*1也不行。
    改成@temp/(30+0.0)*30

    是想计算小数点后面的。因为29/(30+0.0)*30就不会等于0了。

    • 已编辑 彭123 2011年11月8日 8:33
    2011年11月8日 8:30
  • 改成@temp/(30+0.0)*30 是想计算小数点后面的。因为29/(30+0.0)*30就不会等于0了。

    请问@temp 的数据类型是float吗? 如果是int的话,会忽略小数点后的数据的。

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • 已标记为答案 彭123 2012年10月9日 14:18
    2011年11月9日 10:00
    版主
  • @temp  int,
    看他的定义应该是整数型的。都不知道该怎么改了。
    2011年11月10日 0:22
  • 真是牛人。。。。

    @Temp/30*30,也就是按每30算一个单位

    用CURSOR来计算,若一个企业有一万多人,那服务器就必须勤劳勇敢了。。。


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2011年11月10日 0:41
  • 真是牛人。。。。

    @Temp/30*30,也就是按每30算一个单位

    用CURSOR来计算,若一个企业有一万多人,那服务器就必须勤劳勇敢了。。。


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    呵呵,加上杂七杂八的人,也才10000左右罢了,很大的运算量吗?
    2011年11月10日 0:50