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