set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*创建综合评估表[pr_create_quality_board 循环遍历-游标[createALTER ]*/
create PROCEDURE [dbo].[pr_create_Evaluation]
@objectID nvarchar(2000) --/%生成的评估对象%/
,@type int --/%区分是用户ID:1,还是部门ID:2%/
,@reason nvarchar(1000) --/%发启评估理由%/
,@evaType int --/%发启评估的方式1：自动，0：手动 %/
,@endTime datetime --/*评估截止时间*/
,@range int /*发启评估范围1,主管，2，部门经理*/
as
/*声明变量*/
declare @hisPost varchar(50);
declare @curPoset varchar(50);
declare @userId varchar(50);
declare @manageId varchar(50);
declare @autId varchar(50);
declare @evaId int ; /*评估ID*/
declare @count int;
declare @deptName varchar(50);/*部门名称*/
/*分割字符*/
declare @pos int
declare @oldPos int
declare @tempstr varchar(100)
/*创建临时表，存放ID字符串*/
create table #temp_id
(
id int
)
set @pos=1
set @oldPos=1
while @pos<len(@objectID)
begin
set @pos=charindex(',',@objectID,@oldpos)
if @pos>0
begin
set @tempstr=substring(@objectID,@oldpos,@pos-@oldpos)
set @oldpos=@pos+1
end
else
begin
set @tempstr=substring(@objectID,@oldpos,len(@objectID)-@oldpos+1)
set @pos=len(@objectID)
end
insert into #temp_id select @tempstr
end
/*所有的普通员工，生成评估表*/
if @evaType=1 /*自动发启的评估*/
begin
/*声明评估游标*/
declare evaluation_cur scroll cursor for
/*普通员工*/
select a.appointmentId,a.appointmentId as curpost,a.userid,b.fdept_manageid,dbo.autid(a.userid) as autid
from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
order by a.username asc
/*打开游标*/
open evaluation_cur
/*执行第一次取数操作*/
fetch next from evaluation_cur into @hisPost,@curPoset,@userId,@manageId,@autId
/*循环检查*/
while @@FETCH_STATUS = 0
begin
--生成评估表
INSERT INTO [FPCOM].[dbo].[fp_evaluation]
([eval_startTime]
,[eval_endTime]
,[history_post]
,[current_post]
,[salary]
,[total_score]
,[remark]
,[audit_status]
,[type]
,[reason]
,[evaled_userID]
,[eval_userID]
,[audit_userID]
,[IsAutoEvalution]/*1,自动；0，手动*/
,[createTime])
VALUES(getdate(),@endtime ,@hisPost,@curPoset,'',0,'',1,1,'',@userId,@manageId,@autId,1,getdate())
/*生成评估明细表*/
SELECT @evaId = @@IDENTITY -- 评估ID
INSERT INTO [FPCOM].[dbo].[fp_evaluationDetail]
([f_evaId],[name],[type]
,[score_value],[score],[description] ,[sort],[createTime])
select @evaId,[modelName],[type],[score_value],0,'',[sort],getdate()
from fp_evaluationModel
fetch next from evaluation_cur into @hisPost,@curPoset,@userId,@manageId,@autId
end
/*关闭并释放游标*/
close evaluation_cur
deallocate evaluation_cur
------------------------------------------------------------------------------------------------------------------------------------------------------------
/* 向部门经理发送待办事宜 */
INSERT INTO [FPCOM].[dbo].[fp_UndoMission]
([fname],[fuserid],[fstate],[ftotleid]
,[fdate],[ftype],[projroleid],[period])
select convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在15天内完成评估！',
userid,0,'',getdate(),12,null,null from FP_USER where userid in
( select fdept_manageid from dbo.fp_comp_dept where fdeptid not in(1,2,4)) and ftype=0
/* 向部门经理发短信通知 */
INSERT tb_smlog([IO], mobile, message, status,sender,addtime)
SELECT 0,tel,
convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在15天内完成评估！',
0,'system', GETDATE()
FROM dbo.FP_USER
where userid in
(select fdept_manageid from dbo.fp_comp_dept where fdeptid not in(1,2,4)) and ftype=0
/*向部门经理邮件通知*/
insert dbo.mailmsg(address,title,msg,status,sender,toname)
select Email, '公司网站员工综合评估',
convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在15天内完成评估！',
0,'spy',UserName
FROM dbo.FP_USER
where userid in
(select fdept_manageid from dbo.fp_comp_dept where fdeptid not in(1,2,4)) and ftype=0
end
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
/*手动发启评估表*/
else if @evaType=0
begin
if @type=1 /*用户ID*/
begin
/*声明评估游标*/
declare evaluation_cur scroll cursor for
/*普通员工*/
select a.appointmentId,a.appointmentId as curpost,a.userid,b.fdept_manageid,dbo.autid(a.userid) as autid
from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.userid in(select id from #temp_id where id is not null)
order by a.username asc
/*打开游标*/
open evaluation_cur
/*执行第一次取数操作*/
fetch next from evaluation_cur into @hisPost,@curPoset,@userId,@manageId,@autId
/*循环检查*/
while @@FETCH_STATUS = 0
begin
--生成评估表
INSERT INTO [FPCOM].[dbo].[fp_evaluation]
([eval_startTime]
,[eval_endTime]
,[history_post]
,[current_post]
,[salary]
,[total_score]
,[remark]
,[audit_status]
,[type]
,[reason]
,[evaled_userID]
,[eval_userID]
,[audit_userID]
,[IsAutoEvalution]
,[createTime])
VALUES(getdate(),@endTime,@hisPost,@curPoset,'',0,'',1,1,@reason,@userId,@manageId,@autId,@evaType,getdate())
/*生成评估明细表*/
SELECT @evaId = @@IDENTITY -- 评估ID
INSERT INTO [FPCOM].[dbo].[fp_evaluationDetail]
([f_evaId],[name],[type]
,[score_value],[score],[description] ,[sort],[createTime])
select @evaId,[modelName],[type],[score_value],0,'',[sort],getdate()
from fp_evaluationModel
fetch next from evaluation_cur into @hisPost,@curPoset,@userId,@manageId,@autId
end
/*关闭并释放游标*/
close evaluation_cur
deallocate evaluation_cur
------------------------------------------------------------------------------------------------------------------------------------------------------------
/* 向部门经理发送待办事宜 */
INSERT INTO [FPCOM].[dbo].[fp_UndoMission]
([fname],[fuserid],[fstate],[ftotleid]
,[fdate],[ftype],[projroleid],[period])
select convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在'+ convert(varchar(10),Year(@endtime))+'-'+ convert(varchar(10),Month(@endtime))+'-'+convert(varchar(10),DAY(@endtime))+'之前完成评估！',
userid,0,'',getdate(),12,null,null from FP_USER where userid in
(
select userid from fp_user where userid in(
select b.fdept_manageid from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.userid in(select id from #temp_id where id is not null))
) and ftype=0
if @range=2 /*部门经理*/
begin
/*取部门名称*/
select @deptName=fname from fp_comp_dept where fdeptid in
(select deptid from fp_user where userid in(select top 1 id from #temp_id))
/*向主管领导发送待办事宜*/
INSERT INTO [FPCOM].[dbo].[fp_UndoMission]
([fname],[fuserid],[fstate],[ftotleid]
,[fdate],[ftype],[projroleid],[period])
select convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月——'+@deptName+'的综合评估已经产生，请查看！',
userid,0,'',getdate(),12,null,null from FP_USER where userid in
(
select distinct [dbo].[autid](id) as userid from #temp_id
where [dbo].[autid](id) is not null
) and ftype=0
end
/* 向部门经理发短信通知 */
INSERT tb_smlog([IO], mobile, message, status,sender,addtime)
SELECT 0,
tel,
convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在'+ convert(varchar(10),Year(@endtime))+'-'+ convert(varchar(10),Month(@endtime))+'-'+convert(varchar(10),DAY(@endtime))+'之前完成评估！',
0,
'system',
GETDATE()
FROM dbo.FP_USER
where userid in
(
select userid from fp_user where userid in(
select b.fdept_manageid from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.userid in(select id from #temp_id where id is not null))
)and ftype=0
/*向部门经理邮件通知*/
insert dbo.mailmsg(address,title,msg,status,sender,toname)
select Email,
'公司网站员工综合评估',
convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在'+ convert(varchar(10),Year(@endtime))+'-'+ convert(varchar(10),Month(@endtime))+'-'+convert(varchar(10),DAY(@endtime))+'之前完成评估！',
0,
'spy',
UserName
FROM dbo.FP_USER
where userid in
(
select userid from fp_user where userid in(
select b.fdept_manageid from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.userid in(select id from #temp_id where id is not null))
) and ftype=0
drop table #Temp_id
end
else if @type=2 /*部门ID*/
begin
/*声明评估游标*/
declare evaluation_cur scroll cursor for
select a.appointmentId,a.appointmentId as curpost,a.userid,b.fdept_manageid,dbo.autid(a.userid) as autid
from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in( SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.deptid in( select id from #temp_id where id is not null)
order by a.username asc
/*打开游标*/
open evaluation_cur
/*执行第一次取数操作*/
fetch next from evaluation_cur into @hisPost,@curPoset,@userId,@manageId,@autId
/*循环检查*/
while @@FETCH_STATUS = 0
begin
--生成评估表
INSERT INTO [FPCOM].[dbo].[fp_evaluation]
([eval_startTime]
,[eval_endTime]
,[history_post]
,[current_post]
,[salary]
,[total_score]
,[remark]
,[audit_status]
,[type]
,[reason]
,[evaled_userID]
,[eval_userID]
,[audit_userID]
,[IsAutoEvalution]
,[createTime])
VALUES(getdate(),@endTime,@hisPost,@curPoset,'',0,'',1,1,@reason,@userId,@manageId,@autId,@evaType,getdate())
/*生成评估明细表*/
SELECT @evaId = @@IDENTITY -- 评估ID
INSERT INTO [FPCOM].[dbo].[fp_evaluationDetail]
([f_evaId],[name],[type]
,[score_value],[score],[description] ,[sort],[createTime])
select @evaId,[modelName],[type],[score_value],0,'',[sort],getdate()
from fp_evaluationModel
fetch next from evaluation_cur into @hisPost,@curPoset,@userId,@manageId,@autId
end
/*关闭并释放游标*/
close evaluation_cur
deallocate evaluation_cur
------------------------------------------------------------------------------------------------------------------------------------------------------------
/* 向部门经理发送待办事宜 */
INSERT INTO [FPCOM].[dbo].[fp_UndoMission]
([fname],[fuserid],[fstate],[ftotleid]
,[fdate],[ftype],[projroleid],[period])
select convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在'+ convert(varchar(10),Year(@endtime))+'-'+ convert(varchar(10),Month(@endtime))+'-'+convert(varchar(10),DAY(@endtime))+'之前完成评估！',
userid,0,'',getdate(),12,null,null from FP_USER where userid in
(
select userid from fp_user where userid in(
select b.fdept_manageid from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.deptid in(select id from #temp_id where id is not null))
) and ftype=0
if @range=2 /*部门经理发起的评估，向主管领导发送待办事宜*/
begin
/*取部门名称*/
select @deptName=fname from fp_comp_dept where fdeptid in
(select top 1 id from #temp_id)
/*向主管领导发送待办事宜*/
INSERT INTO [FPCOM].[dbo].[fp_UndoMission]
([fname],[fuserid],[fstate],[ftotleid]
,[fdate],[ftype],[projroleid],[period])
select convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月——'+@deptName+'的综合评估已经产生，请查看！',
userid,0,'',getdate(),12,null,null from FP_USER where userid in
(
select distinct [dbo].[autid](userid) as userid from fp_user where userid in(
select b.fdept_manageid from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.deptid in(select id from #temp_id where id is not null)
)
) and ftype=0
end
/* 向部门经理发短信通知 */
INSERT tb_smlog([IO], mobile, message, status,sender,addtime)
SELECT 0,tel,
convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在'+ convert(varchar(10),Year(@endtime))+'-'+ convert(varchar(10),Month(@endtime))+'-'+convert(varchar(10),DAY(@endtime))+'之前完成评估！',
0,'system',GETDATE()
FROM dbo.FP_USER
where userid in
(
select userid from fp_user where userid in(
select b.fdept_manageid from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.deptid in( select id from #temp_id where id is not null))
)and ftype=0
/*向部门经理邮件通知*/
insert dbo.mailmsg(address,title,msg,status,sender,toname)
select Email,
'公司网站员工综合评估',
convert(varchar(10),Year(getdate()))+'年'+ convert(varchar(10),Month(getdate()))+'月'+'员工综合评估已经产生，请在'+ convert(varchar(10),Year(@endtime))+'-'+ convert(varchar(10),Month(@endtime))+'-'+convert(varchar(10),DAY(@endtime))+'之前完成评估！',
0,'spy',UserName
FROM dbo.FP_USER
where userid in
(
select userid from fp_user where userid in(
select b.fdept_manageid from fp_user a , fp_comp_dept b
where a.deptid=b.fdeptid and a.ftype=0 and a.fisassMag=0
and a.userid not in(SELECT fdept_manageid FROM dbo.fp_comp_dept)
and a.deptid in( select id from #temp_id where id is not null ))
) and ftype=0
end
end
