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