USE
GO
/****** Object: StoredProcedure [dbo].[sp_SendOutEmailForEvalDue1Month] Script Date: 02/01/2011 12:24:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
Author: SP
Create date: 2/5/2009
Description: processes the list of people who have an evaluation due in one month and sends out email notifications
============================================= */
ALTER PROCEDURE [dbo].[sp_SendOutEmailForEvalDue1Month]
AS
BEGIN
Declare @EmpFirstName as nvarchar(100)
Declare @EmpLastName as nvarchar(100)
Declare @EmpEmail as nvarchar(100)
Declare @SupEmail as nvarchar(100)
Declare @EmpBody as nvarchar(max)
Declare @SupBody as nvarchar(max)
Declare @EmpEmailSubject as nvarchar(100)
Declare @SupEmailSubject as nvarchar(100)
Declare @HREmailSubject as nvarchar(100)
Declare @EvalDueDate as nvarchar(100)
Declare @LoopCounter as int
Declare @RecordCount as int
set nocount on
create table #EvalsDueInOneMonth (
firstname nvarchar(100),
lastname nvarchar(100),
midname nvarchar(100),
eval_month int,
eval_day int,
email nvarchar(100),
supervisor int,
KeepOffEvalReport bit,
position int,
super_alt int,
superemail nvarchar(100),
eval_compl_on datetime,
nextevalduedate datetime
)
INSERT INTO #EvalsDueInOneMonth exec sp_GetEvalsDueIn1Month
select @RecordCount = count(position) from #EvalsDueInOneMonth
declare cur cursor
for
select firstname, lastname, email, superemail, convert(char(10), nextevalduedate, 110)
from #EvalsDueInOneMonth
open cur
set @LoopCounter = 0
/* Start loop here */
while @LoopCounter < @RecordCount
begin
fetch next from cur into @EmpFirstName, @EmpLastName, @EmpEmail, @SupEmail, @EvalDueDate;
set @EmpBody = 'Your next annual evaluation is due in one month, on '+@EvalDueDate+'. Your supervisor has been notified of the due date of your evaluation. If you have any questions, please contact your supervisor or HR Person contact info.'
set @SupBody = 'This is a reminder that '+substring(@EmpFirstName, 1, 1)+substring(lower(@EmpFirstName), 2, 50)+' '+substring(@EmpLastName, 1, 1)+substring(lower(@EmpLastName), 2, 50)+' has an evaluation due in one month on '+@EvalDueDate+'. Please complete the evaluation, obtain signatures and meet with this employee within forty-five days of the due date. If you have any questions, please contact HR Person contact info..'
set @EmpEmailSubject = 'Annual Evaluation Due in 1 Month'
set @SupEmailSubject = 'Annual Evaluation Due in 1 Month'
set @HREmailSubject = @EmpFirstName+' '+@EmpLastName+' '+'Annual Evaluation Due in 1 Month'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = @EmpEmail,
@body = @EmpBody,
@subject = @EmpEmailSubject ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = @SupEmail,
@body = @SupBody,
@subject = @SupEmailSubject ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR1@Person.email',
@body = @EmpBody,
@subject = @HREmailSubject ;
/* Email Sending Block for HR2 and HR3, comment out when Beta testing is done
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR2@Person.email',
@body = @EmpBody,
@subject = @EmpEmailSubject ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR2@Person.email',
@body = @SupBody,
@subject = @SupEmailSubject ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR3@Person.email',
@body = @EmpBody,
@subject = @EmpEmailSubject ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR3@Person.email',
@body = @SupBody,
@subject = @SupEmailSubject ;*/
SET @LoopCounter = @LoopCounter + 1;
/* End loop here */
end
close cur
deallocate cur
drop table #EvalsDueInOneMonth
END
