procedure

 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER PROCEDURE [dbo].[INIT_VACATION]
as
begin

declare @empId varchar(20)
declare @OutExprYear datetime
declare @workDate datetime
declare @tempworkDate datetime
set @tempworkDate=cast('2999/12/31' as datetime)
declare @vacationDay int
declare @approval1 int
declare @approval2 int
declare cr_employee  cursor for
select empId,OutExprYear from dbo.employee where TuipinFlg<>1

open cr_employee

fetch next from cr_employee  into @empId,@OutExprYear
--begin cr_employee
while @@fetch_status = 0
begin
set @vacationDay=0
set @approval1=0
set @approval2=0
--begin cr_workinfolist
--####休假计算#######
declare cr_workinfolist cursor for

select workDate from dbo.workInfoList
where datepart(year,workDate)=datepart(year,getdate())
and isnull(vacation,0)=2
and empID=@empId
order by workDate asc

open cr_workinfolist
fetch next from cr_workinfolist into @workDate
while @@fetch_status = 0
begin

if @tempworkDate=cast('2999/12/31' as datetime)
begin
 set @tempworkDate=@workDate
 set @vacationDay=@vacationDay+1
 --print('chushi @workDate:'+cast(@workDate as varchar))
 fetch next from cr_workinfolist into @workDate
    continue
end
else
begin
    --print ('0001msg:'+cast(datediff(day,@tempworkDate,@workDate) as varchar))--test
 if datediff(day,@tempworkDate,@workDate)=1--连续的日期
 begin
  set @vacationDay=@vacationDay+1
  set @tempworkDate=@workDate
  fetch next from cr_workinfolist into @workDate
  continue
 end
 else --间隔一天以上的日期
    begin
  declare @i int
  declare @countday int
  set @i=0
  set @countday=0
        while  @i<datediff(day,@tempworkDate,@workDate)-1
  begin
   set @tempworkDate=dateadd(day,1,@tempworkDate)
   declare @inTimeflg int
   set @inTimeflg=0
   --是否有打卡
   select @inTimeflg =case when isnull(workInTime,0)=0 then 0 else 1 end
   from dbo.workInfoList
            where workDate=@tempworkDate
   and empID=@empId
--    print ('0002msg:'+cast(@inTimeflg as varchar))--test
--             print ('0002msg@vacationDay :'+cast(@vacationDay as varchar))--test
            if @inTimeflg=1
            begin
    break
   end
   else
            begin
--    --是否是周末
--    if datepart(weekday,@tempworkDate)=1 or datepart(weekday,@tempworkDate)=7
--    begin
--     set @inSunSatflg=1
--    end     
--    --是否是节假日
--    select @inHolidayflg=count(hdate) from  dbo.holiday
--    where hdate=cast(convert(varchar,@inHolidayflg,111)as datetime) 
--    if @inSunSatflg=1 or @inHolidayflg>0
--    begin
--    set @countday=@countday+1
--    end
    --是否排班
    declare @inworkSetList decimal(6,0)
    set @inworkSetList=0
    
    select @inworkSetList=case when count(workDate)=0 then 0 else 1 end from dbo.workSetList
    where workDate=@tempworkDate
    and empId=@empId
--    print ('--- @tempworkDate:'+cast(@tempworkDate as varchar))--test
--    print ('--- @inworkSetList:'+cast(@inworkSetList as varchar))--test
    if  @inworkSetList=0
    begin
     set @countday=@countday+1
                    --print ('----count++----')--test
    end
    else
    begin
     break
    end
   end
   set @i=@i+1
  end
  if datediff(day,@tempworkDate,@workDate)=1
  begin
   set @vacationDay=@vacationDay+@countday
   --print ('----@vacationDay++----')--test
   --print ('@vacationDay:'+cast(@vacationDay as varchar))
   --print ('@countday:'+cast(@countday as varchar))
  end
--  else
--  begin
--   set @vacationDay=@vacationDay+1
--  end
    end
    set @vacationDay=@vacationDay+1
 set @tempworkDate=@workDate
--    print ('@countday0006msg:'+cast(@tempworkDate as varchar))--test
 fetch next from cr_workinfolist into @workDate
end
end --end cr_workinfolist.while
close cr_workinfolist
deallocate cr_workinfolist
--####迟到早退计算#######

select @approval1=isnull(sum(lateM),0)
,@approval2=isnull(sum(leaveM),0)
from
(
 select approval1,approval2
 ,case when lateflg=2 then approval1 else 0 end lateM
 ,case when leaveEarlyflg=2 then approval2 else 0  end leaveM
 from dbo.workInfoList
 where datepart(year,workDate)=datepart(year,getdate())
 and empID=@empId
 and (lateflg=2
 or leaveEarlyflg=2)
)Temp
 declare @AllDays int
 set @AllDays=cast((@approval1+@approval2)/480 as decimal(5,0))+@vacationDay
 declare @joinyear decimal
 set @joinyear=datediff(day,@OutExprYear,cast(cast(datepart(year,getdate())as varchar)+'/12/31' as datetime))/365.00

--    print ('社员ID：'+@empId)
-- print ('基准日：'+convert(varchar,@OutExprYear, 111 ))  
-- print ('入职年限：'+cast(@joinyear as varchar))
-- print ('休假数：'+cast(@vacationDay as varchar))
-- print('总休假天数：'+cast(@AllDays as varchar))
-- print ('@approval1:'+cast(@approval1 as varchar)) 
-- print ('@approval2:'+cast(@approval1 as varchar)) 
 if @joinyear<1
    begin
 insert dbo.vacation select @empId,cast(datepart(year,dateadd(year,1,getdate())) as varchar),0,0
 end
    else if @joinyear>=1.00 and @joinyear<10.00
 begin
  if @AllDays>=60
  begin
   insert dbo.vacation select @empId,cast(datepart(year,dateadd(year,1,getdate())) as varchar),0,0
  end
        else
        begin
   insert dbo.vacation select @empId,cast(datepart(year,dateadd(year,1,getdate())) as varchar),5,0
  end
 end
    else if @joinyear>=10.00 and @joinyear<20.00
 begin
  if @AllDays>=90
  begin
   insert dbo.vacation select @empId,cast(datepart(year,dateadd(year,1,getdate())) as varchar),0,0
  end
        else
        begin
   insert dbo.vacation select @empId,cast(datepart(year,dateadd(year,1,getdate())) as varchar),10,0
  end
 end
    else if @joinyear>=20.00
 begin
  if @AllDays>=120
  begin
   insert dbo.vacation select @empId,cast(datepart(year,dateadd(year,1,getdate())) as varchar),0,0
  end
        else
        begin
   insert dbo.vacation select @empId,cast(datepart(year,dateadd(year,1,getdate())) as varchar),15,0
  end
 end

 fetch next  from cr_employee  into @empId,@OutExprYear
end --end cr_employee.while
close cr_employee
deallocate cr_employee

end --end PROCEDURE