考勤处理

 --   GetCardRecord 
 
 
alter          procedure  GetCardRecord 
--@begindate varchar(10), 
--@enddate varchar(10) 
as 
 
 
 
 
 
 
declare   
@begindate varchar(10), 
@enddate varchar(10), 
@adate varchar(10) , 
@adate2 varchar(10),  
@ecode varchar(5), 
@ename varchar(10), 
@btime varchar(5), 
@etime2 varchar(5), 
@xbtime varchar(5), 
@etime  varchar(5), 
@bWorkTime varchar(5), 
@ccount int, 
@card varchar(5), 
@i int, 
@j int, 
@str varchar(8000), 
@str2 varchar(8000), 
@str3 varchar(8000), 
@atime1  varchar(5), 
@atime2  varchar(5), 
@atime3  varchar(5), 
@atime4  varchar(5), 
@atime5  varchar(5), 
@atime6  varchar(5), 
@flag varchar(1), 
@addhour float 
 
 
set @begindate =convert(varchar(10),dateadd(day,-3,Getdate()),120) 
print(@begindate) 
set @enddate =@begindate 
 
 
 
--set @begindate ='2010-09-06' 
--set @enddate ='2010-09-06' 
 
 
 
begin tran 
 
 
delete from cardrecord   where carddate between @begindate and @enddate  
and  ecode in (select ecode  from HR_MTD..emploees ) 
 
set @adate= @begindate 
 
while  @adate <= @enddate --- 一天天处理 
begin 
 print(@adate) 
 
set @adate2= Convert(varchar(10),dateadd(day,1,@adate),120) 
 
 
declare cursor_ecode cursor for  
select ecode,ename from  HR_MTD..emploees   
open  cursor_ecode  
fetch next from cursor_ecode into @ecode,@ename 
while @@fetch_status=0  
begin  
 
 
 
 
select @btime=b.cftime,@etime2=cmtime,@xbtime=cetime,@bWorkTime=b.bWorkTime,@etime=cltime  
from empclass A   inner join  HR_MTD..CLASSTYPE  b on left(a.cday,1) = b.cno where workid =  @ecode and prodate = @adate  
 
 
if @btime<='13:00' 
begin  
select   @atime1=atime1,@atime2=atime2,@atime3=atime3,@atime4=atime4,@atime5=atime5,@atime6=atime6 ,  
@addhour= (case when addhour- cast( addhour as int) >=0.5 then cast( addhour as int)+0.5 else  cast( addhour as int) end) 
 
from  
(select ecode,carddate, min(atime1)atime1,min(atime2)atime2,min(atime3)atime3,min(atime4)atime4,min(atime5)atime5,max(atime6)atime6,max(addhour)as addhour 
from  
(select   case when len(b.remark)=7 then right(b.remark,5)  else right(b.remark,4) end as ecode  , a.empname as ename, @adate as  carddate,   
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,-120,@btime)                                                                                                                    
and a.workdate = @adate  and convert(varchar(5),a.worktime,108)<dateadd(mi,15,@btime)  then convert(varchar(5),a.worktime,108) else null end  as atime1,--上午上班卡 
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,-15,@etime2)                                                                                                                    
and a.workdate = @adate  and convert(varchar(5),a.worktime,108)<=dateadd(mi,45,@etime2)  then convert(varchar(5),a.worktime,108) else null end as atime2,--上午下班卡 
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,-15,@etime2)                                                                                                                    
and a.workdate = @adate  and convert(varchar(5),a.worktime,108)<dateadd(mi,45,@etime2)  then  
 convert(varchar(5),dateadd(mi,29,convert(varchar(5),a.worktime,108)),108) else null end as atime3,--下午上班卡 
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,-15,@etime)                                                                                                                    
and a.workdate = @adate  and convert(varchar(5),a.worktime,108)<=dateadd(mi,15,@etime)  then convert(varchar(5),a.worktime,108) else null end as atime4 ,--下午下班卡 
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,-15,@etime)                                                                                                                    
and a.workdate = @adate  and convert(varchar(5),a.worktime,108)<=dateadd(mi,15,@etime)  then  
 convert(varchar(5),dateadd(mi,29,convert(varchar(5),a.worktime,108)),108)  else null end as atime5 ,--加班上班卡 
--convert(varchar(5),a.worktime,108) 
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,16,@bworktime)  and datediff(mi,@bworktime,convert(varchar(5),a.worktime,108))<=60  then convert(varchar(5),a.worktime,108) 
     when   convert(varchar(5),a.worktime,108)>=dateadd(mi,16,@bworktime)  and datediff(mi,@bworktime ,convert(varchar(5),a.worktime,108))>60  then  
convert(varchar(5),dateadd(mi,datediff(mi,@adate+' '+@bworktime, workdate+' '+ convert(varchar(5),a.worktime,108))%30 ,
convert(varchar(5),dateadd(hour,1,@adate+' '+@bworktime),108)),108)

 else null end  as atime6 ,  --加班下班卡 
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,16,@bworktime) and datediff(mi,@bworktime ,convert(varchar(5),a.worktime,108))<= 60 then  
  round(datediff(mi,@bworktime,convert(varchar(5),a.worktime,108))/60.0,1) 
when   convert(varchar(5),a.worktime,108)>=dateadd(mi,16,@bworktime) and datediff(mi,@bworktime ,convert(varchar(5),a.worktime,108))>60  then 1 
else  0  end as addhour 
 from dlcpro..kq_workrecord a  
inner join dlcpro..hr_employee b on a.empno= b.empno 
 where   a.workdate = @adate and   
a.empno like 'da%' and b.remark= 'da'+@ecode )aa 
group by  ecode,carddate)bb  
 
insert into cardrecord(copid,ecode,ename,carddate,card1,card2,card3,card4,card5,card6,addtime) values('002',@ecode,@ename,@adate,@atime1,@atime2,@atime3,@atime4,@atime5,@atime6,@addhour) 
end 
 
-- select top 2 * from dlcpro..kq_workrecord 
 
--   select top 2 * from dlcpro..hr_employee 
 
 --select   round(datediff(mi,'17:30',convert(varchar(5),'19:30',108))/60.0,1) 
 
if @btime>'13:00' --晚班 
begin  
 
select   @atime1=atime1,@atime2=atime2,@atime3=atime3,@atime4=atime4,@atime5=atime5,@atime6=atime6, 
@addhour= (case when addhour- cast( addhour as int) >=0.5 then cast( addhour as int)+0.5 else  cast( addhour as int) end) 
from  
(select ecode,carddate,min(atime1)as atime1,min(atime2)as atime2,min(atime3)as atime3,min(atime4)as atime4,min(atime5)as atime5,max(atime6)as atime6,max(addhour)addhour 
from  
(select case when len(b.remark)=7 then right(b.remark,5)  else right(b.remark,4) end as ecode  ,a.empname as ename,   @adate as  carddate,    
(case  when   workdate+' '+ convert(varchar(5),a.worktime,108)>=dateadd(mi,-30,@adate+' '+@btime ) 
and workdate+' '+ convert(varchar(5),a.worktime,108)<@adate+' '+dateadd(mi,15,@adate+' '+@btime ) then  convert(varchar(5),a.worktime,108) else null end ) as atime1, --上午上班卡 
(case  when   workdate+' '+ convert(varchar(5),a.worktime,108)>=@adate+' '+dateadd(mi,-15,@etime2) 
and workdate+' '+ convert(varchar(5),a.worktime,108)<=@adate+' '+dateadd(mi,45,@etime2) then  convert(varchar(5),a.worktime,108) else null end ) as atime2,--上午下班卡 
(case  when   workdate+' '+ convert(varchar(5),a.worktime,108)>=@adate+' '+dateadd(mi,-15,@etime2) 
and workdate+' '+ convert(varchar(5),a.worktime,108)<=@adate+' '+dateadd(mi,45,@etime2) then   
convert(varchar(5), dateadd(mi,29,workdate+' '+convert(varchar(5),a.worktime,108)),108) else null end ) as atime3 ,--下午上班卡 
(case  when   workdate+' '+ convert(varchar(5),a.worktime,108)>@adate2+' '+dateadd(mi,-15,@etime) 
and workdate+' '+ convert(varchar(5),a.worktime,108)<@adate2+' '+dateadd(mi,15,@etime)  then   workdate+' '+ convert(varchar(5),a.worktime,108) else null end ) as atime4, --下午下班卡 
(case  when   workdate+' '+ convert(varchar(5),a.worktime,108)>=@adate2+' '+dateadd(mi,-15,@etime) 
and workdate+' '+ convert(varchar(5),a.worktime,108)<@adate2+' '+dateadd(mi,15,@etime)  then    
convert(varchar(5), dateadd(mi,29,workdate+' '+convert(varchar(5),a.worktime,108)),108) 
 else null end ) as atime5 ,--加班上班卡 
(case  when   workdate+' '+ convert(varchar(5),a.worktime,108)>@adate2+' '+dateadd(mi,15,@etime)  and   
              datediff(mi,(@adate2+' '+@bworktime),workdate+' '+ convert(varchar(5),a.worktime,108))<=60    then   convert(varchar(5),a.worktime,108)   
       when   workdate+' '+ convert(varchar(5),a.worktime,108)>@adate2+' '+dateadd(mi,15,@etime)  and   
              datediff(mi,@adate2+' '+@bworktime, workdate+' '+ convert(varchar(5),a.worktime,108))>60 then  
convert(varchar(5),dateadd(mi,datediff(mi,@adate2+' '+@bworktime, workdate+' '+ convert(varchar(5),a.worktime,108))%30 ,
convert(varchar(5),dateadd(hour,1,@adate2+' '+@bworktime),108)),108)
else  null  end ) as atime6,--加班下班卡 
 
case when   convert(varchar(5),a.worktime,108)>=dateadd(mi,16,@bworktime) and  datediff(mi,@adate2+' '+@bworktime, @adate2+' '+ convert(varchar(5),a.worktime,108))<=60  then  
   round(datediff(mi,@bworktime,convert(varchar(5),a.worktime,108))/60.0,1) 
   when   convert(varchar(5),a.worktime,108)>=dateadd(mi,16,@bworktime) and  datediff(mi,@adate2+' '+@bworktime, @adate2+' '+ convert(varchar(5),a.worktime,108))>60  then 1 
else  0  end as addhour 
 
from dlcpro..kq_workrecord a 
inner join dlcpro..hr_employee b on a.empno= b.empno 
 where a.empno like 'da%' and b.remark= 'da'+@ecode 
and ((a.workdate = @adate  and  convert(varchar(5),worktime,108)>'12:00' ) or (a.workdate = @adate2  and  convert(varchar(5),worktime,108)<='12:00' ) )   
) aa  
group by ecode,carddate)bb 
 
insert into cardrecord(copid,ecode,ename,carddate,card1,card2,card3,card4,card5,card6,addtime) values('002',@ecode,@ename,@adate,@atime1,@atime2,@atime3,@atime4,@atime5,@atime6,@addhour) 
end  
 
 
fetch next from cursor_ecode into @ecode,@ename 
end 
 
close  cursor_ecode 
deallocate  cursor_ecode 
 
 set @adate= Convert(varchar(10),dateadd(day,1,@adate),120) 
 
end 
 
        if @@error<>0      
        begin     
          rollback tran     
          raiserror('出错！',16,1)     
        end     
        else     
          commit tran   
 
select a.*,b.cday,bworktime from cardrecord  a  
inner join empclass  b on  carddate =  prodate and  ecode =  workid 
where carddate between @begindate and @enddate  order by ecode 
 
--select * from cardrecord where carddate between '2010-08-11' and '2010-08-11'