ComputerServicesSurveyReminder

USE [Helpdesk]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Leonard Nelson
-- Create date: 09/25/2013
-- Description:	Send a reminder to Help Desk Management with the list of users who need to be surveyed
-- Syntax: EXEC ComputerServicesSurveyReminder 'Yes';
-- =============================================
ALTER PROCEDURE [dbo].[ComputerServicesSurveyReminder] 
(
	@Execute nvarchar(3)
)
AS
SET NOCOUNT ON;
BEGIN
DECLARE @Report_Recipients nvarchar(255);
DECLARE @Report_Name nvarchar(100);
DECLARE @Report_Body nvarchar(max);
DECLARE @Report_Content nvarchar(max);
DECLARE @ReportDay  nvarchar(20);
DECLARE @CurrentDate  nvarchar(20);
DECLARE @StartDate  nvarchar(20);
DECLARE @EndDate  nvarchar(20);
DECLARE @SurveyDayIncrement int;
DECLARE @CountSurveyUsers int;
DECLARE @CountSurveyUsersTotal int;
DECLARE @Owner_Group nvarchar(60);
If (@Execute = 'Yes')
	BEGIN
		-- Set Date and Time
		SET @ReportDay = CONVERT(nvarchar, getDate(), 101);	
		SET @SurveyDayIncrement = 3; --Survey 3 Days After Resolution
		SET @StartDate = Blackbox.dbo.FunctionConvertToEpochTime(CONVERT(datetime, DATEADD(DD, DateDiff(DD, 0, GETDATE())-1, 0), 120));
		SET @EndDate = Blackbox.dbo.FunctionConvertToEpochTime(CONVERT(datetime, DATEADD(DD, DateDiff(DD, 0, GETDATE()), 0) , 120));
		SET @CurrentDate = @StartDate;
	
		-- Set Mail Recipients
		SET @Report_Recipients = 'test.com;';
		-- Set Mail Subject
		SET @Report_Name = 'Computer Services Help Desk Survey Reminder ';
		-- Query Criteria
		-- Status = '4' AND (i.e. Status = Resolved)
		-- Status_Reason = '17000' AND (i.e. Status Reason = No Further Action Required)
		-- Contact_Client_Type <> '960' AND (i.e. Client Type != Member)
		-- Last_Name <> 'Unknown' AND (i.e. Last Name != Unknown)
		-- Corporate_ID IS NOT NULL AND (i.e. TUID != NULL)
		-- Internet_E_mail IS NOT NULL AND (i.e. Email != NULL)
		-- Owner_Group LIKE 'Help Desk%' AND (i.e. Support Group Owner = Help Desk)
		-- VIP = '1' AND (i.e. VIP = Not VIP)
		-- ((Last_Resolved_Date + 259200) > @StartDate AND (Last_Resolved_Date + 259200) < @EndDate ) AND  (i.e. Tickets Resolved in the last 3 Days)
		-- (Last_Resolved_Date >= @StartDate AND Last_Resolved_Date < = @EndDate ) AND (i.e. Tickets Resolved in the last 1 Day)
		-- (Reported_Source <> '20000' OR Reported_Source <> '20001' OR Reported_Source <> '20002');  (i.e. Reported Source != Mark Private or Mark For Deletion or Chat)
						
		SELECT @CountSurveyUsers = COUNT(*) FROM Helpdesk.dbo.ComputerServicesSurvey WHERE SurveyStatus IS NULL;
		SELECT @CountSurveyUsersTotal = COUNT(*) FROM Helpdesk.dbo.ComputerServicesSurvey;
		
		IF (@CountSurveyUsers >= 1)
			BEGIN
				SET @Report_Body = '<table border="0" cellpadding="0" width="100%">';
				SET @Report_Body = @Report_Body + '<tr><td colspan="7">';
				SET @Report_Body = @Report_Body + '<br />'	
				SET @Report_Body = @Report_Body + CAST(@CountSurveyUsers AS nvarchar) + '  users will be surveyed at 9:00 a.m. on ' + @ReportDay + '. ' + REPLACE(CONVERT(varchar, CAST(@CountSurveyUsersTotal AS money), 1), '.00', '') + ' users have been surveyed to date.<p>';
				SET @Report_Body = @Report_Body + '<br />'	
				SET @Report_Body = @Report_Body + '</p></td></tr>';
				SET @Report_Content = 
					CAST(( 
						SELECT 
							Incident_Number AS 'td', '', 
							Submit_Date AS 'td', '', 
							Resolved_Date AS 'td', '', 
							(First_Name + ' ' + Last_Name) AS 'td', '',
							Summary AS 'td', '', 
							Corporate_ID AS 'td', '', 
							Internet_E_mail AS 'td' 
						FROM Helpdesk.dbo.ComputerServicesSurvey 
						WHERE 
							SurveyStatus IS NULL
						FOR XML PATH('tr'), ELEMENTS ) 
					AS nvarchar(max));
			END
		IF (@CountSurveyUsers < 1)
			BEGIN
				SET @Report_Body = '<table border="0" cellpadding="0" width="100%">';
				SET @Report_Body = @Report_Body + '<tr><td colspan="7">';
				SET @Report_Body = @Report_Body + '<br />'	
				SET @Report_Body = @Report_Body + CAST(@CountSurveyUsers AS nvarchar) + '  users will be surveyed at 9:00 a.m. on ' + @ReportDay + '. ' + REPLACE(CONVERT(varchar, CAST(@CountSurveyUsersTotal AS money), 1), '.00', '') + ' users have been surveyed to date.<p>';
				SET @Report_Body = @Report_Body + '<br />'	
				SET @Report_Body = @Report_Body + '</p></td></tr>';
				SET @Report_Content = '<tr><td colspan="7">


No users to survey</td></tr>'	
			END
		SET @Report_Body = @Report_Body + '<thead><tr><th><strong>Incident Number</strong></th><th><strong>Submit Date</strong></th><th><strong>Resolved Date</strong></th><th><strong>Customer Name</strong></th><th><strong>Summary</strong></th><th><strong>TUid</strong></th><th><strong>Email</strong></th></tr></thead>';
		SET @Report_Body = @Report_Body + @Report_Content
		SET @Report_Body = @Report_Body + '<tr><td colspan="7">';			
		SET @Report_Body = @Report_Body + '<br />'
		SET @Report_Body = @Report_Body + 'Last Modified By: [email protected] on September 8, 2015<br />'
		SET @Report_Body = @Report_Body + '<br />'	
		SET @Report_Body = @Report_Body + '</td></tr>'
		SET @Report_Body = @Report_Body + '</table>'
		EXEC dbo.SendMailReport @Report_Recipients, @Report_Name, @Report_Body
	END
END