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

• ### 问题

• 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.
• 已标记为答案 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了。

• 已编辑 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.
• 已标记为答案 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