ComputerServicesSurveyNotifyGoogle

USE [Helpdesk]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Leonard Nelson
-- Create date: 09/25/2013
-- Description:	Send Computer Services Survey using Google Docs
-- Syntax: EXEC ComputerServicesSurveyNotifyGoogle 'Yes';
-- =============================================
CREATE PROCEDURE [dbo].[ComputerServicesSurveyNotifyGoogle] 
(
	@Execute nvarchar(3)
)
AS
SET NOCOUNT ON;
BEGIN
DECLARE @Survey_Recipients nvarchar(100), @Survey_Subject nvarchar(100), @Survey_Body nvarchar(max), @Survey_Content nvarchar(max), @CountSurveyUsers int, @CountRecord int, @SurveyRecord int, @IncidentNumber varchar(15), @FirstName varchar(30), @LastName varchar(30), @IncidentSummary varchar(100);
If (@Execute = 'Yes')
	BEGIN
		
		SELECT TOP 1 @SurveyRecord = SurveyID FROM Helpdesk.dbo.ComputerServicesSurvey WHERE SurveyStatus IS NULL;
		SELECT @CountSurveyUsers = COUNT(*) FROM Helpdesk.dbo.ComputerServicesSurvey WHERE SurveyStatus IS NULL;
		IF (@CountSurveyUsers >= 1)
		BEGIN
			SET @CountRecord = 0;
			WHILE @CountRecord < [email protected]
			BEGIN
				SET @CountRecord = @CountRecord + 1
				SELECT TOP 1 @SurveyRecord = SurveyID, @IncidentNumber = Incident_Number, @FirstName = First_Name, @LastName = Last_Name, @IncidentSummary = Summary FROM  Helpdesk.dbo.ComputerServicesSurvey WHERE SurveyStatus IS NULL;
				-- Set Mail Recipients
				SET @Survey_Recipients = '[email protected]';
				-- Set Mail Subject
				SET @Survey_Subject = 'Computer Services Survey';
		
				SET @Survey_Body = '<table border="0" cellpadding="0" width="100%">';
				SET @Survey_Body = @Survey_Body + '<tr><td>';
				SET @Survey_Content = 'https://docs.google.com/forms/d/1H_oZQtsdaxSEQOhasdasdaVdX8MxsTasdasdzQJrCcHSMY_YlhsdasdEfe8ELxzI/viewform?entry.821459209=' + @IncidentNumber + '&entry.984224030=' + @IncidentSummary + '&entry.583394915'
				SET @Survey_Body = @Survey_Body + '<p>Dear ' + @FirstName + ' ' + @LastName + ',</p>'
				SET @Survey_Body = @Survey_Body + '<p>You recently contacted Computer Services for assistance. The summary of request ' + @IncidentNumber + ' is as follows:</p>';
				SET @Survey_Body = @Survey_Body + '<p>' + @IncidentSummary + '</p>';
				SET @Survey_Body = @Survey_Body + '<p>Please take a moment and let us know how we did by completing this <a href="' + @Survey_Content + '">survey</a>.</p>'
				SET @Survey_Body = @Survey_Body + '<p>Sincerely,<br />'
				SET @Survey_Body = @Survey_Body + '</p><p>Computer Services<br />'
				SET @Survey_Body = @Survey_Body + 'Phone: (123) 456-7890<br />'
				SET @Survey_Body = @Survey_Body + 'Website: http://www.test.com/<br /></p>'
				SET @Survey_Body = @Survey_Body + '</td></tr>'
				SET @Survey_Body = @Survey_Body + ''
				EXEC dbo.SendSurveyHTML @Survey_Recipients, @Survey_Subject, @Survey_Body
			END			
		END
	END
END
GO