USE [FSNEPv2]
GO
/****** Object:  StoredProcedure [dbo].[usp_NotifyUsersOfSemiAnnualCertification]    Script Date: 05/12/2010 10:47:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[usp_NotifyUsersOfSemiAnnualCertification]

AS

DECLARE @dayInt int
DECLARE @monthInt int

SET @dayInt = (SELECT DATEPART(DAY, GETDATE()))
SET @monthInt = (SELECT DATEPART(month, GETDATE()))

IF (@dayInt = 13) AND (@monthInt = 4 OR @monthInt = 1)
BEGIN

	DECLARE @MailList CURSOR
	SET @MailList = CURSOR FOR

	--Get all of the 1.0 FTE TimeSheet Users
	SELECT     aspnet_Membership.Email, Users.FirstName + ' ' + Users.LastName as FullName
	FROM         aspnet_Membership INNER JOIN
						  aspnet_UsersInRoles ON aspnet_Membership.UserId = aspnet_UsersInRoles.UserId INNER JOIN
						  aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId AND aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId INNER JOIN
						  Users ON aspnet_Membership.UserId = Users.UserId
	WHERE     (aspnet_Roles.RoleName = N'Timesheet User') AND (FTE = 1)

	OPEN @MailList

	DECLARE @Email varchar(50), @FullName varchar(100)
	FETCH NEXT FROM @MailList INTO @Email, @FullName

	WHILE (@@FETCH_STATUS = 0)
		BEGIN
			--Send emails to each matching user
			DECLARE @bodyText varchar(MAX)

			SET @bodyText = 'This email was generated by the FSNEP Online Time Record System.
*** Please do not respond to this email address ***

This is a reminder that your FSNEP Semi-Annual Certification record is due by the 15th of this month.  Please print and then complete the attached certification and obtain the necessary signatures and dates.  Send a PDF copy of the certification to your State Office Analyst by the 15th of the month.

If you have any questions regarding this message, or about time records in general, please contact your State Office analyst:

Corinne Gould ( cgould@ucdavis.edu ) or
Susan Padgett ( sdpadgett@ucdavis.edu ) or
Yolanda Cortez (yCortez@ucdavis.edu ).
'

			EXEC msdb.dbo.sp_send_dbmail
			@recipients=@Email,
			@subject='UC-FSNEP Semi-Annual Certification Record Due',
			@body=@bodyText

			FETCH NEXT FROM @MailList INTO @Email, @FullName
		END

	CLOSE @MailList
	DEALLOCATE @MailList

END