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