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 < 1--@CountSurveyUsers
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