-- 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'