SQL to Google Calendar

--****************************************************************************************
-- This script returns a (graphical) timeline for data specified in the Job Runtime creation table
--****************************************************************************************
SET nocount ON

DECLARE @DT datetime
DECLARE @StartDT datetime
DECLARE @EndDT datetime
DECLARE @MinRuntimeInSec INT
DECLARE @SendMail INT
DECLARE @ReturnRecocordset INT
DECLARE @Emailprofilename VARCHAR(50)
DECLARE @EmailRecipients VARCHAR(50)

--***************************************************************************************
-- Set variables
--***************************************************************************************
SET @StartDT = getdate() - 1
SET @EndDT = getdate()
SET @MinRuntimeInSec = 60 --Ignore jobs with runtime smaller then this

SET @ReturnRecocordset = 0
SET @SendMail = 1
SET @Emailprofilename = 'YourEmailProfile'
SET @EmailRecipients = 'YourEmail'

--***************************************************************************************
-- Pre-run cleanup (just in case)
--***************************************************************************************
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..##GoogleGraph') IS NOT NULL DROP TABLE ##GoogleGraph;

--***************************************************************************************
-- Create a table for HTML assembly
--***************************************************************************************
CREATE TABLE ##GoogleGraph ([ID] [INT] IDENTITY(1,1) NOT NULL,
                            [HTML] [VARCHAR](8000) NULL);

--***************************************************************************************
-- Create the Job Runtime information table
--***************************************************************************************


-- PUT A SELECT STATEMENT HERE THAT RETURNS TWO COLUMNS
-- COLUMN [dt] (Datetime) and COLUMN [ItemCount] (Integer)
-- SELECT exampleDate AS 'dt'
    , SUM(*) AS 'ItemCount'
INTO #JobRuntime
-- FROM exampleTable
IF NOT EXISTS (SELECT 1 FROM #JobRuntime)
    GOTO NothingToDo

--***************************************************************************************
-- Format for google graph - Header
-- (Split into multiple inserts because the default text result setting is 256 chars)
--***************************************************************************************
INSERT INTO ##GoogleGraph (HTML)
SELECT '<html>
    <head>
    <!--<META HTTP-EQUIV="refresh" CONTENT="3">-->
    <script type="text/javascript" src="https://www.google.com/jsapi?autoload={''modules'':[{''name'':''visualization'', ''version'':''1'',''packages'':[''calendar'']}]}"></script>'
INSERT INTO ##GoogleGraph (HTML)
SELECT '    <script type="text/javascript">
    google.setOnLoadCallback(drawChart);
    function drawChart() {'
INSERT INTO ##GoogleGraph (HTML)
SELECT '    var container = document.getElementById(''JobTimeline'');
    var chart = new google.visualization.Calendar(container);
    var dataTable = new google.visualization.DataTable();'
INSERT INTO ##GoogleGraph (HTML)
SELECT '    dataTable.addColumn({ type: ''date'', id: ''Date'' });
       dataTable.addColumn({ type: ''number'', id: ''Number of Tickets'' });
       dataTable.addRows(['

--***************************************************************************************
-- Format for google graph - Data
--***************************************************************************************
INSERT INTO ##GoogleGraph (HTML)
SELECT  '       [ '
        +'new Date('
        +     CAST(DATEPART(YEAR,   dt) AS VARCHAR(4))
        +', '+CAST(DATEPART(MONTH,  dt) -1 AS VARCHAR(4)) --Java months count from 0
        +', '+CAST(DATEPART(DAY,    dt) AS VARCHAR(4))
        + ') , '
        + ItemCount
        +  ' ] ,'
FROM    #JobRuntime

SELECT *
FROM #JobRuntime
--***************************************************************************************
-- Format for google graph - Footer
--***************************************************************************************
INSERT INTO ##GoogleGraph (HTML)
SELECT '    ]);

    var options =
    {
        title: "Items by Day",
        height: 900,
        calendar: { cellSize: 30 }, --MODIFY THIS NUMBER TO SIZE YOUR CALENDAR
    };

    chart.draw(dataTable, options);

}'
INSERT INTO ##GoogleGraph (HTML)
SELECT '
    </script>
    </head>
    <body>'
    +'<font face="Helvetica" size="3" >'
    +'Job timeline on: '+@@servername
    +' from '+CONVERT(VARCHAR(20), @StartDT, 120)
    +' until '+CONVERT(VARCHAR(20), @EndDT, 120)
    +CASE WHEN @MinRuntimeInSec = 0 THEN '' ELSE ' (hiding jobs with runtime < '+CAST(@MinRuntimeInSec AS VARCHAR(10))+' seconds)' END
    +'</font>
        <div id="JobTimeline" style="width: 1870px; height: 900px;"></div>
    </body>
</html>'

--***************************************************************************************
-- Output HTML page - copy output & paste to a .HTML file and open with google chrome
--***************************************************************************************
IF @ReturnRecocordset = 1
    SELECT html FROM ##GoogleGraph ORDER BY ID

--***************************************************************************************
-- Send Email -
--***************************************************************************************
IF @SendMail = 1
    EXECUTE msdb.dbo.sp_send_dbmail
         @profile_name = @Emailprofilename
        ,@recipients = @EmailRecipients
        ,@subject = 'JobTimeline'
        ,@body = 'See attachment for JobTimeline, open with Google Chrome!'
        ,@body_format = 'HTML' -- or TEXT
        ,@importance = 'Normal' --Low Normal High
        ,@sensitivity = 'Normal' --Normal Personal Private Confidential
        ,@execute_query_database = 'master'
        ,@query_result_header = 1
        ,@query = 'set nocount on; SELECT HTML FROM ##GoogleGraph'
        ,@query_result_no_padding = 1  -- prevent SQL adding padding spaces in the result
        --,@query_no_truncate= 1       -- mutually exclusive with @query_result_no_padding
        ,@attach_query_result_as_file = 1
        ,@query_attachment_filename= 'JobTimeline.HTML'


GOTO Cleanup

--***************************************************************************************
-- Just in case....
--***************************************************************************************
NothingToDo:

print 'No job runtime info found....'

--***************************************************************************************
-- Cleanup
--***************************************************************************************
Cleanup:
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..##GoogleGraph') IS NOT NULL DROP TABLE ##GoogleGraph;