remind.asp

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

<%
'************************************************************************************************
' 文件名: remind.asp
' Copyright(c) 2001-2002 上海阿尔卡特网络支援系统有限公司
'
' 　创建人 : 周秋舫
' 　日　期 : 2002-05-13
' 修改历史 :
'   ****年**月**日 ****** 修改内容:**************************************************
' 功能描述 : 任务提醒和例会提醒，检查t_task表和t_routinemeeting表中有提醒的部分，向相关人员发送消息，并重置下次提醒时间
'			DoTaskRemind						'' 任务提醒
'			DoRoutineMeetingRemind		'' 例会提醒
'			NextRemindTime					'' 根据提醒方式和本次提醒时间计算下次提醒时间
' 　版　本 :
'************************************************************************************************
'option explicit
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
%>
<!-- #include file="../include/db.inc" -->
<!-- #include file="../include/datahandle.inc" -->
<!-- #include file="../include/common.inc" -->
<!-- #include file="../include/message.inc" -->

<%
call DoTaskRemind()
call DoRoutineMeetingRemind()
call DoMeetingEffectRemind()


''' 以下为函数实现


''****************************************************************************************************************
'' 函数：DoTaskRemind
'' 功能：任务提醒，向相关人员发送任务提醒消息，并重置该任务的下次提醒时间
'' 输入：(无)
'' 输出：(无) 
''****************************************************************************************************************
sub DoTaskRemind()
	dim sSQL, rs
	dim sRemindMethod, sRemindTime, sNextRemindTime, sSubject, sContent, sDoFrom, sDoEnd, sExecutors
	dim sTaskSerial
	dim dtBaseTime	: dtBaseTime = now

	' modified by zhouqiufang 
	' 已经完成的任务仍然会提醒，原因是remind_method和remind_time只在task中有，在t_personaltask中是没有的
	' 因此对每个有提醒的任务分别去看t_personal表中的progress_percent，如果progress_percent <> 100才提醒
	'sSQL = "select * from t_task where remind_method is not null and remind_time < " & ToSQL(now, "Text")
	sSQL = "select t1.task_serial, t1.remind_method, t1.remind_time, t1.subject, t1.content" & _
			", t1.dofrom, t1.doend, t2.emp_serial" & _
			" from t_task t1, t_personaltask t2" & _
			" where t1.remind_method is not null and t1.remind_time < " & ToSQL(now, "Text") & _
			" and t1.task_serial = t2.task_serial" & _
			" and isnull(progress_percent,0) <> 100"

	'' 获取需要提醒的任务信息
	dim conn	: 	set conn = New CDBConnection
	conn.Connect(dbLocal)
	set rs = conn.m_DBConn.Execute(sSQL)

	' 看每个任务
	while not rs.eof
		sTaskSerial = rs("task_serial")
		sRemindMethod = rs("remind_method")
		sNextRemindTime = rs("remind_time")
		sSubject = rs("subject")
		sContent = rs("content")
		sDoFrom = FormatDT(rs("dofrom"), "yyyy-mm-dd hh:mm")
		sDoEnd = FormatDT(rs("doend"), "yyyy-mm-dd hh:mm")
		'sExecutors = rs("executor")
		sExecutors = rs("emp_serial")

		''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
		'' 获得下次提醒时间，如果下次提醒时间小于当前时间，则继续获取下次提醒时间
		''---------------------------------------------------------------------------------------------------------------------------
		while sNextRemindTime < dtBaseTime
			sNextRemindTime = NextRemindTime(sRemindMethod, sNextRemindTime)
		wend

		'' 更新下次提醒时间
		if left(sRemindMethod,2) = "01" then	'' 一次性提醒
			sSQL = "update t_task set remind_time = null where task_serial = " & ToSQL(sTaskSerial, "Number")
		else
			sSQL = "update t_task set remind_time = " & ToSQL(sNextRemindTime, "Text") & " where task_serial = " & ToSQL(sTaskSerial, "Number")
		end if
		response.write sSQL & "<br><br>"
		conn.m_DBConn.execute(sSQL)


		''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
		'' 发送提醒消息
		''---------------------------------------------------------------------------------------------------------------------------
		sContent = "系统提醒您在 " & sDoFrom & " ～ " & sDoEnd & " 需完成以下任务：" & vbLF & vbLF &_
						"任务名称：" & vbLF & "　　" & sSubject & vbLF & vbLF & _
						"具体内容：" & vbLF & "　　" & sContent & vbLF & vbLF
		'call CommonSendMsg(msg_type, parent_msg_id, subject, content, sender, receiver)
		call CommonSendMsg(MSG_TASK, "", "任务提醒：" & sSubject, sContent, "", sExecutors)

		rs.movenext
	wend
	rs.Close()
	conn.Disconnect()
end sub

''****************************************************************************************************************
'' 函数：DoRoutineMeetingRemind
'' 功能：例会提醒，目前例会只设每周提醒一次，向例会创建者发送提醒安排例会的消息，并重置该例会的下次提醒时间
''			下次提醒时间总为本次提醒时间加7天
'' 输入：(无)
'' 输出：(无) 
''****************************************************************************************************************
sub DoRoutineMeetingRemind()
	dim sSQL, rs
	dim sRemindMethod, sRemindTime, sNextRemindTime
	dim sMeetingSerial, sMeetingName, sMeetingDay, sMeetingTime
	dim sContent

	dim dtBaseTime	: dtBaseTime = now
	dim sReceiver	: sReceiver = GetOfficer		'' 只有办公室主任才能创建例会，因此只向办公室主任发送例会提醒消息

	sSQL = "select * from t_routinemeeting where remind_method is not null and remind_time < " & ToSQL(now, "Text")
'	response.write sSQL : response.end

	'' 获取需要提醒的任务信息
	dim conn	: 	set conn = New CDBConnection
	conn.Connect(dbLocal)
	set rs = conn.m_DBConn.Execute(sSQL)
	while not rs.eof
		sMeetingSerial = rs("meeting_serial")
		sRemindMethod = rs("remind_method")
		sNextRemindTime = rs("remind_time")

		sMeetingName = rs("meeting_name")
		sMeetingDay = rs("meeting_day")
		sMeetingTime = rs("meeting_time")
		
				
		''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
		'' 获得下次提醒时间，如果下次提醒时间小于当前时间，则继续获取下次提醒时间
		''---------------------------------------------------------------------------------------------------------------------------
		while sNextRemindTime < dtBaseTime
			sNextRemindTime = NextRemindTime(sRemindMethod, sNextRemindTime)
		wend

		'' 更新下次提醒时间
		sSQL = "update t_routinemeeting set remind_time = " & ToSQL(sNextRemindTime, "Text") & " where meeting_serial = " & ToSQL(sMeetingSerial, "Number")
		''response.write sSQL & "<br><br>"
		conn.m_DBConn.execute(sSQL)

		''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
		'' 发送提醒消息
		''---------------------------------------------------------------------------------------------------------------------------
		dim wk	: wk = Array("零","一", "二", "三", "四", "五", "六", "日")
		sContent = "系统提醒您及时安排例会：" & vbLF & vbLF &_
						"例会名称：" & sMeetingName & vbLF & vbLF & _
						"例会时间：每周" & wk(sMeetingDay) & " " & sMeetingTime
		'call CommonSendMsg(msg_type, parent_msg_id, subject, content, sender, receiver)
		call CommonSendMsg(MSG_MEETING, "", "例会提醒：" & sMeetingName, sContent, "", sReceiver)

		rs.movenext
	wend
	rs.Close()
	conn.Disconnect()
end sub

''****************************************************************************************************************
'' 函数：DoMeetingEffectRemind
'' 功能：会议落实事项提醒，每天／每周／每月提醒一次，向落实事项相关人员发送提醒消息，并重置该落实事项的下次提醒时间
''			下次提醒时间总为本次提醒时间加1天，1周，1月
'' 输入：(无)
'' 输出：(无) 
''****************************************************************************************************************
sub DoMeetingEffectRemind()
	dim sSQL, rs
	dim sRemindMethod, sRemindTime, sNextRemindTime
	dim sMeetingEffectSerial, sMeetingName, sMeetingEffect, sHandleDeadline
	dim sMsgSubject, sMsgContent

	dim dtBaseTime	: dtBaseTime = now
	dim sReceiver	: sReceiver = GetEmpSerial

	sSQL = "select t1.serial, t1.effect_item, t2.meeting_name, t1.handle_deadline, t1.remind_method, t1.remind_time from t_meetingeffect t1" & _
				" left join t_meetingprepare t2 on t2.meeting_serial = t1.meeting_serial" & _
				" where remind_method is not null and remind_time < " & ToSQL(date, "Text") & _
				" and PATINDEX('%," & sReceiver & ",%', ',' + replace(associate,' ','') + ',') > 0"
	''response.write "<script language=""javascript"">alert('" & sSQL & "</script>" : response.end

	'' 获取需要提醒的落实事项信息
	dim conn	: 	set conn = New CDBConnection
	conn.Connect(dbLocal)
	set rs = conn.m_DBConn.Execute(sSQL)
	while not rs.eof
		sMeetingEffectSerial = rs("serial")
		sRemindMethod = rs("remind_method")
		sNextRemindTime = rs("remind_time")
		sHandleDeadline = rs("handle_deadline")

		sMeetingName = rs("meeting_name")
		sMeetingEffect = rs("effect_item")	
				
		''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
		'' 获得下次提醒时间，如果下次提醒时间小于当前时间，则继续获取下次提醒时间
		''---------------------------------------------------------------------------------------------------------------------------
		while sNextRemindTime < dtBaseTime
			sNextRemindTime = NextRemindTime(sRemindMethod, sNextRemindTime)
		wend

		'' 更新下次提醒时间
		sSQL = "update t_meetingeffect set remind_time = " & ToSQL(sNextRemindTime, "Text") & " where serial = " & ToSQL(sMeetingEffectSerial, "Number")
		conn.m_DBConn.execute(sSQL)

		''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
		'' 发送提醒消息
		''---------------------------------------------------------------------------------------------------------------------------
		sMsgSubject = "请及时处理会议《" & sMeetingName & "》的落实事项"
		sMsgContent = "系统提醒您及时处理：《" & sMeetingName & "》的落实事项" & vbLF & vbLF &_
						"落实事项处理时限：" & sHandleDeadLine & vbLF & vbLF & _
						"落实事项内容：" & vbLF & sMeetingEffect & vbLF & vbLF
		call CommonSendMsg(MSG_MEETING, "", sMsgSubject, sMsgContent, "", sReceiver)

		rs.movenext
	wend
	rs.Close()
	conn.Disconnect()
end sub

''****************************************************************************************************************
'' 函数：NextRemindTime(sRemindMethod, dtBase)
'' 功能：根据提醒方式和本次提醒时间获取下次提醒时间
''			只有每天提醒(04)、每周提醒(08)、每月(16)提醒的提醒方式才有下次提醒时间，并且下次提醒时间分别是本次提醒时间加1天／1周／1月
'' 输入：sRemindMethod	提醒方式
''			dtBase				本次提醒时间
'' 输出：下次提醒时间
''****************************************************************************************************************
function NextRemindTime(sRemindMethod, dtBase)
	NextRemindTime = ""
	if Left(sRemindMethod,2) = "04" then				' 每天提醒一次
		NextRemindTime = DateAdd("d", 1, dtBase)
	elseif Left(sRemindMethod,2) = "08" then		' 每周提醒一次
		NextRemindTime = DateAdd("ww", 1, dtBase)
	elseif Left(sRemindMethod,2) = "16" then		' 每月提醒一次
		NextRemindTime = DateAdd("m", 1, dtBase)
	end if
end function
%>