proc_remindmachine.sql

物业管理和办公自动化系统

----------------------------------------------------------------
-- 存储过程 proc_RemindMachine(提醒机)
-- 每隔一秒钟查询表T_PersonalTask
-- 无返回值
----------------------------------------------------------------
CREATE PROCEDURE proc_RemindMachine
AS
SET NOCOUNT ON
-- define variables
declare @nSerial int, @sEmpId varchar(20), @sRemindMethod varchar(8), @dRemindTime datetime
declare @nInterval	int
declare @dNextRemindTime datetime
declare @sSQL	varchar(512)
SET ROWCOUNT 1
declare @nTrue int
select @nTrue = 1
while @nTrue = 1
begin
	WAITFOR DELAY '00:00:00.800'
	-- 检查T_PersonalTask表的提醒内容
	SELECT @nSerial = Serial, @sEmpId = Emp_Id, @sRemindMethod = Remind_Method, @dRemindTime = Remind_Time 
	FROM T_PersonalTask 
	WHERE Emp_Id is not null 
	AND ( Remind_Method is not null and len(Remind_Method) = 8 )
	AND ( Remind_Time is not null and Remind_Time <= GetDate() )
	AND Progress_Percent < 100
	ORDER BY Remind_Time asc
	if @@ROWCOUNT = 0
	begin
		continue
	end
	select @nInterval = cast(Right(Left(@sRemindMethod,4),2) as int)
	select @sRemindMethod = Left(@sRemindMethod,2)
	if (@sRemindMethod = '01' or @sRemindMethod = '04' or @sRemindMethod = '08' or @sRemindMethod = '16' )	
	begin
		if @sRemindMethod = '04'
			select @dNextRemindTime = dateadd(Day, 1, @dRemindTime)
		if @sRemindMethod = '08'
			select @dNextRemindTime = dateadd(Week, 1, @dRemindTime)
		if @sRemindMethod = '16'
			select @dNextRemindTime = dateadd(Month, 1, @dRemindTime)
		--print '提醒机：工号'  + @sEmpId + '，提醒方式' + @sRemindMethod + '，本次提醒时间' + convert(varchar(20),@dRemindTime) + '，下次提醒时间' + convert(varchar(20),@dNextRemindTime)
		EXECUTE proc_RemindTask @EmpId=@sEmpId, @PSerial=@nSerial, @NextRemindTime=@dNextRemindTime
	end
	
	-- 检查T_RoutineMeeting表的提醒内容
	SELECT @nSerial = Meeting_id, @sEmpId = Creator, @sRemindMethod = Remind_Method, @dRemindTime = Remind_Time
	FROM T_RoutineMeeting
	WHERE Creator is not null 
	AND ( Remind_Method is not null and len(Remind_Method) = 8 )
	AND ( Remind_Time is not null and Remind_Time <= GetDate() )
	ORDER BY Remind_Time asc
	if @@ROWCOUNT = 0 
	begin
		continue
	end
	select @nInterval = cast(Right(Left(@sRemindMethod,4),2) as int)
	-- 例会目前只有每周提醒
	select @sRemindMethod = Left(@sRemindMethod,2)		
	if (@sRemindMethod = '08')
	begin
		select @dNextRemindTime = dateadd(Week, 1, @dRemindTime)
		EXECUTE proc_RemindRM @EmpId=@sEmpId, @MeetingId=@nSerial, @NextRemindTime=@dNextRemindTime
	end
end
return

GO