Portal - Change Calendar

--use a GUID generator to create some new guids for your view panel and navigation node.  NOTE: All guids must be completely lower case.
--you can run this query three times in SQL to generate the guids you need.
-- SELECT LOWER(CONVERT(nvarchar(39),NEWID()))
DECLARE @NavNodeGuid nvarchar(255)       = '4f962bc3-61ba-4b95-a311-fa00b2c61e30';
DECLARE @ViewPanelGuid nvarchar(255)     = '6de60ec7-9b5d-4aad-9da9-3c77b5ac0e6f';
DECLARE @DataSourceGuid nvarchar(255)    = 'f177dd61-5cd9-4f78-95c9-f8987a69edca';

--this query selects all the active Change Requests in the WorkItem table
DECLARE @Query nvarchar(max) = 
'DECLARE @SystemWorkItemChangeRequest uniqueidentifier = ''e6c9cf6e-d7fe-1b5d-216c-c3f5d2c7670c'';
 DECLARE @ChangeStatus_Cancelled uniqueidentifier = ''877defb6-0d21-7d19-89d5-a1107d621270'';
 DECLARE @ChangeStatus_Failed uniqueidentifier = ''85f00ead-2603-6c68-dfec-531c83bf900f'';
 DECLARE @ChangeStatus_Completed uniqueidentifier = ''68277330-a0d3-cfdd-298d-d5c31d1d126f'';
 DECLARE @ChangeStatus_Closed uniqueidentifier = ''f228d50b-2b5a-010f-b1a4-5c7d95703a9b'';
 SELECT w.Id, w.WorkItemId, w.WorkItemId+'' - ''+w.Title as Title, w.Description, w.ScheduledStartDate, w.ScheduledEndDate
   FROM [WorkItem] w
  WHERE w.ClassId = @SystemWorkItemChangeRequest AND w.StatusId NOT IN (@ChangeStatus_Cancelled, @ChangeStatus_Failed, @ChangeStatus_Completed, @ChangeStatus_Closed)';
--this is the text which will be shown in the portal menu
DECLARE @MenuTitle nvarchar(255)         = 'Change Request Calendar';
DECLARE @Locale nvarchar(3)              = 'ENU';                                                             

--this just deletes it if it already exists so that you can iteratively tweak it and recreate it easily
DELETE FROM DisplayString WHERE ElementID = @NavNodeGuid and LocaleID = @Locale;
DELETE FROM NavigationNode WHERE Id = @NavNodeGuid;
DELETE FROM ViewPanel WHERE id = @ViewPanelGuid;
DELETE FROM DataSource WHERE id = @DataSourceGuid;

--this creates the query which will be used as the data source for the calendar
INSERT INTO dbo.[DataSource] (Id, ConnectionString, Query)
VALUES ( @DataSourceGuid, NULL, @Query);

--this creates the navigation node display string
INSERT INTO [dbo].[DisplayString]  (ElementID, LocaleID, DisplayString) 
VALUES (@NavNodeGuid, @Locale, @MenuTitle);

--Create the navigation node.
INSERT INTO NavigationNode(Id, [Definition], Ordinal, Sealed, IsPublic, IsVisible, LicenseRequired, IconClass, ParentId, Location)
VALUES (
@NavNodeGuid,
'{"Id":"' + @NavNodeGuid+ '","layoutType":"full","view":{"header":{"title":"' + @MenuTitle + '","subTitle":""},"body":{"content":{"rows":[{"columns":[{"ColSpan":12,"type":"viewPanel","ViewPanelId":"' + @ViewPanelGuid + '"}]}]}}}}', 
2,0,0,0,'DASHBOARD', NULL, 'E99B6A93-E536-442E-839F-67A99B439297','6CFC4D00-D8EB-4F51-B63D-F776576CF25A');

--Create the view panel
INSERT INTO ViewPanel(Id, [Definition], TypeId)
VALUES (
@ViewPanelGuid,
'{"Id":"' + @ViewPanelGuid + '","type":"calendar","content":{"queryId":"' + @DataSourceGuid + '", "views":["day","week","month","agenda"],"id":"WorkItemId","title":"Title","description":"Description","startDate":"ScheduledStartDate","endDate":"ScheduledEndDate"}}',
'calendar'
);