USE [xinjuren]
GO
/****** Object: StoredProcedure [dbo].[proc_uf_kqtable] Script. Date: 01/09/2013 16:19:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_uf_kqtable](@startdate char(32), @enddate varchar(32))
as
begin
set NOCOUNT on
declare @count int=0
declare @table table(rowNum int,humresid varchar(32),date1 varchar(32),startdktime varchar(32))
insert into @table select row_number() over (order by humresid) as rowNum,humresid,date1,startdktime from uf_kqtable h where date1 between @startdate and @enddate and (chidao=1 or zaotui=1 or wdkcount=1)
select @count=COUNT(humresid) from @table
declare @tableadd table(nextqdtime varchar(32),workname varchar(32))
insert into @tableadd select nextqdtime ,workname from uf_workadd where requestid in(select id from requestbase where isdelete=0 and isfinished=1)
and (nextqdtime like SUBSTRING(@startdate,0,8)+'%')
declare @tableleave table(requestid varchar(32),reqname varchar(32),reqbegindate varchar(32),reqenddate varchar(32))
insert into @tableleave select requestid,reqname, reqbegindate,reqenddate from uf_leave where requestid in(select id from requestbase where isdelete=0 and isfinished=1)
declare @tableleaveapp table(requestid varchar(32),leavename varchar(32),stratdate varchar(32),enddate varchar(32))
insert into @tableleaveapp select requestid,leavename,stratdate,enddate from uf_leaveapp where requestid in(select id from requestbase where isdelete=0 and isfinished=1)
declare @number int=1
declare @leavecount varchar(32)
declare @leaveappcount varchar(32)
while(@count>=@number)
begin
set @leavecount=''
set @leaveappcount=''
print @number
declare @humresid varchar(32)
select @humresid=humresid from @table where
rowNum=@number declare @date1 varchar(10)
select @date1=date1 from @table where
rowNum=@number declare @diff float
declare @nextqdtime varchar(32)
declare @startdktime varchar(32)
select @startdktime=startdktime from @table where
rowNum=@number select @nextqdtime=nextqdtime from @tableadd where workname
=@humresid and nextqdtime is not null and @date1 like '%'+nextqdtime+'%'
print @nextqdtime
if(LEN(@nextqdtime)=10)
begin
print @nextqdtime+'nextqdtime'
update uf_kqtable set chidao=0,chidaomin=0 ,zaotui=0 ,zaotuimin=0 ,wdkcount=0,jiaban=0 where humresid=(select humresid from @table where
rowNum=@number) and date1=(select date1 from @table where
rowNum=@number)
end
else if(LEN(@nextqdtime)>10)-
begin
select @diff=DATEDIFF(MINUTE,@startdktime,@nextqdtime)
if(@diff>0)
begin
print @nextqdtime
update uf_kqtable set chidao=0,chidaomin=0 ,zaotui=0 ,zaotuimin=0 ,wdkcount=0,jiaban=0 where humresid=(select humresid from @table where
rowNum=@number) and date1=(select date1 from @table where
rowNum=@number)
end
else
begin
print @nextqdtime
update uf_kqtable set chidao=1,chidaomin=@diff ,zaotui=0 ,zaotuimin=0 ,wdkcount=0,jiaban=0 where humresid=(select humresid from @table where
rowNum=@number) and date1=(select date1 from @table where
rowNum=@number)
end
end
select @leavecount=requestid from @tableleave where
reqname=@humresid and @date1 between reqbegindate and reqenddate
print
'2'+@leavecount if(@leavecount!='')
begin
update uf_kqtable set chidao=0,chidaomin=0 ,zaotui=0 ,zaotuimin=0 ,wdkcount=0,jiaban=0 where
humresid=@humresid and
date1=@date1 end
select @leaveappcount=requestid from @tableleaveapp where
leavename=@humresid and @date1 between stratdate and enddate
print
'leappcount'+@leaveappcount if(@leaveappcount!='')
begin
update uf_kqtable set chidao=0,chidaomin=0 ,zaotui=0 ,zaotuimin=0 ,wdkcount=0,jiaban=0 where
humresid=@humresid and
date1=@date1 end
set @number=@number+1
end
end
