proc_newtask.sql

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

---------------------------------------------
-- 存储过程(创建新任务，并给相关人员分配该任务，并发送任务分配消息)
-- 返回值   0: 成功；
--        -1: T_Task 表插入记录不成功
--        -2: T_PersonalTask 表插入记录不成功
---------------------------------------------
CREATE PROCEDURE proc_NewTask(@subject varchar(512), @content varchar(7000), @creator int, @DoFrom datetime, @DoEnd datetime, @Executor varchar(1024), @urgency int, @RemindMethod varchar(8), @RemindTime varchar(20))
As
Set NOCOUNT ON
-- define variables
declare @task_serial	int		-- 插入 T_Task 表生成的任务号
declare @emplist	varchar(256)	-- 员工列表
declare @emp_serial	int		-- 员工序列号
declare @pos		int		-- 位置变量
declare @rtn_code	int		-- 返回值
declare @dtRemind	datetime	-- 下次提醒时间
declare @sMsgSubject varchar(512), @sMsgContent varchar(8000), @sCreatorName varchar(20)
declare @nRows	int		-- 返回记录数

select @dtRemind = @RemindTime
select @rtn_code = 0
select @emplist = @Executor
----------------------------------------------------------------------------------------
-- 先向任务表 T_Task 中插入一条记录 ( Create_Date 使用缺省值 GetDate() )
----------------------------------------------------------------------------------------
Begin Tran
insert into T_Task(Subject, Content, Creator, DoFrom, DoEnd, Executor, Urgency, Remind_Method, Remind_Time)
    values( @Subject, @Content, @Creator, @DoFrom, @DoEnd, @Executor, @Urgency, @RemindMethod, @RemindTime)
IF @@ROWCOUNT = 0
    begin
	select @rtn_code = -1 
	GOTO BeforeReturn
    end
select @task_serial = IDENT_CURRENT('T_Task')
select @pos = 0
while LEN(@emplist) > 0
    begin
	select @pos = PATINDEX('%,%', @emplist)
	if @pos > 0								-- 表示找到了一个逗号
	   begin	
		select @emp_serial = SUBSTRING(@emplist, 1, @pos - 1)		-- 取出逗号前的字串
		select @emplist = RIGHT(@emplist, LEN(@emplist) - @pos)		-- 取得新的emplist
		select @emplist = LTRIM(RTRIM(@emplist))			-- 去掉前导和末尾的空格
	   end
	else
	   begin
		select @emp_serial = @emplist					-- emplist中只有最后一个emp_serial
		select @emplist = ''						-- emplist置为空字串，即跳出循环
	   end
	--print @pos
	---------------------------------------------------------------------------------------------------------
	-- 如果 emp_serial 有效，则在 T_PersonalTask 中插入一条记录
	---------------------------------------------------------------------------------------------------------
	select @nRows = count(*) from T_Employee where serial = @emp_serial
	if @nRows > 0
	      begin
		insert into T_PersonalTask(task_serial, emp_serial) values(@task_serial, @emp_serial)
	      end
    end
-- 通知相关人员
select @emplist = @Executor
select @sCreatorName = IsNULL(Name,'') from T_Employee where serial = @Creator
if ( @@ROWCOUNT = 0 or @sCreatorName = '' )
	select @sCreatorName = @Creator
select @sMsgSubject = '新任务：' + @Subject
select @sMsgContent = @sCreatorName + '给您新分配了一个任务《' + @Subject + '》，任务内容如下：' + @Content

EXECUTE proc_NewMessage @MessageType=104, @Subject=@sMsgSubject, @Content=@sMsgContent, @Sender=@Creator, @Receiver=@emplist, @parent=0

Commit Tran
BeforeReturn:
	SET NOCOUNT OFF
	select @rtn_code as 'rtn_code'
GO