Scheduled SQL Qry/SP send to email (ScriptRunner)

As a support team member I want to be able to upload a SQL Query/Stored Proc that can be scheduled to email results on a daily basis so that I don't have to run it manually

Notes

• There are often times when we run into an issue, and the summer team have to execute a SQL query/Stored Proc on a daily basis and share the results
  • Long term we address the issues that result in this type of activity, but there is an interim period that should really require a user to get manually get the results on a daily basis
  • This could be achieve by using a SQL Agent task, but ops prefer that we don't follow this route
  • As such, we are looking for a way within the application (InFORM or Server Manager) to upload the query, schedule it, and have the results be emailed out
• In this particular case, the query below is being executed on a daily basis and the results are manually sent out each day

• --Find the number of forecasts imported, and the days that were imported
  SELECT 
  COUNT(1) AS ForecastCount,
  SUBSTRING(a.AuditDetails, 117, 10) AS BusinessDay 
  FROM tbAudit a WITH (NOLOCK) 
  WHERE a.AuditDateTime >= CONVERT(VARCHAR(10), getdate(), 121)
  AND a.AuditDateTime < CONVERT(VARCHAR(10), getdate()+1, 121)
  AND a.AuditCategoryID = 23 
  AND a.AuditDetails LIKE '%demand%'
  GROUP BY SUBSTRING(a.AuditDetails, 117, 10)
  
  --Stores in zone, that did not get a forecast for a given date
  --Get the stores that didn't import the Full Forecast - Today + 15 days
  select distinct ez.entityid,e.Entity,e.Status from tbentityzone ez with(nolock)
  join tbentity e with(nolock) on ez.entityid = e.entityid
  where ez.ZoneID = 770
  AND e.Status != 'Closed'
  and e.TempCloseDate is Null
  AND ez.EntityID not in (
  	select EntityID from Forecasting.forecast with(nolock)
  	where
  	BusinessDay = CONVERT(VARCHAR(10), getdate()+15, 121)
  	and GenerationDate > CONVERT(VARCHAR(10), getdate(), 121)
  	)
  --Get the stores that didn't import the Summary Forecast - Today + 20 days
  select distinct ez.entityid,e.Entity,e.Status from tbentityzone ez with(nolock)
  join tbentity e with(nolock) on ez.entityid = e.entityid
  where ez.ZoneID = 770
  AND e.Status != 'Closed'
  and e.TempCloseDate is Null
  AND ez.EntityID not in (
  	select EntityID from Forecasting.forecast with(nolock)
  	where
  	BusinessDay = CONVERT(VARCHAR(10), getdate()+20, 121)
  	and GenerationDate > CONVERT(VARCHAR(10), getdate(), 121)
  	)
  

• Corey suggested that adding something to Server Manager might by a good option, there is already task scheduling available

Acceptance Criteria

1. Confirm that a sql query/stored procedure can be added to the system outside of the release cycle
2. Confirm that the sql query/stored procedure can be updated if needed
3. Confirm that there is a mechanism to schedule the sql query/stored procedure to be run on a daily basis
4. Confirm that there is a way to configure a list of email addresses, that will be used as the recipient list of the results of the sql query/stored procedure
5. Confirm that a sql query/stored procedure is delivered to identify those restaurants that were not imported for the day
   • Typically, for each Open restaurant, InFORM will import a Demand and a Summary file every day
6. Ensure that this is read only, so as to prevent abuse
7. Implement a timeout for long running queries (60 seconds)
8. Confirm that when the task runs on schedule that the results are delivered by email to the recipient list
9. Confirm that the task is added to SM and the tab is displayed for the correct instance (#1), via deployment