SELECT [EventItemOccurrence].Id as [Value]
,[EventItem].Name + ' (' + ISNULL(CONVERT(VARCHAR, Schedule.EffectiveStartDate, 101), 'No schedule')+ ') (Id: ' + CAST([EventItemOccurrence].Id as VARCHAR) + ')' AS [Text]
FROM [dbo].[EventItemOccurrence]
LEFT JOIN [dbo].[EventItem] ON EventItem.Id = EventItemOccurrence.EventItemId
LEFT JOIN Schedule On EventItemOccurrence.ScheduleId = Schedule.Id
ORDER BY Text ASC
                

Step 2a: Creating the first workflow

The goal of this step is to create a workflow that will add a Room Reservation to the Rock Calendar by either:
(1) Creating a new Event Item and a new Event Item Occurrence with the info from the Room Reservation
(2) Creating a new Event Item Occurrence on an existing Event Item with the info from the Room Reservation

This is the longest and most difficult step.

To begin, create a new workflow type called "Reservation to Calendar". It should not be automatically persisted.

Add 4 workflow attributes:

Under the Start Activity, create a Form action with the following form header. Disable the notification email, and make the Existing Event Item workflow attribute visible and editable. This creates a form which asks whehter you want to attach the reservation to an existing Event Item, or create a new one.

Form Header:

{% assign reservation = Workflow | Attribute:'Reservation','Object' %}

Choose an Event Item to attach {{ reservation.Name }} to, or leave it blank to create a new Event Item.
(Choosing an existing Event Item will create a new event item occurrence with the Reservation info).
                    

Next create a new activity called Create Calendar Occurrence and assign it to the button of the form you created above.

Add a SQL run activity to the Create Calendar Occurrence with the following SQL, and choose "Run If Exisiting Event Item Is Blank". This creates a new Event Item no existing Event Item was selected on the Entry Form. Assign the result of the SQL run to the "New Event Item Id" attribute.

Note: This auto approves the new event. If you need it to be unapproved, you must change the last 1 in this sql to a 0.

{% assign reservation = Workflow | Attribute:'Reservation','Object' %}

INSERT INTO [dbo].[EventItem]
    ([Name]
    ,[IsActive]
    ,[Guid]
    ,[IsApproved])
OUTPUT Inserted.Id
VALUES
    ('{{reservation.Name}}'
    ,1
    ,NEWID()
    ,1)
                

Create another SQL run action under Create Calendar Occurrence. This will be responsible posting our newly created even to the public calendar. Again set "Run If Existing Event Item Is Blank".

Note: This posts the new event to the public claendar. If you wish to post it to another calendar, you need the 1 in the SQL below to the appropriate Calendar ID.

INSERT INTO [dbo].[EventCalendarItem]
    ([EventCalendarId]
    ,[EventItemId]
    ,[Guid])
VALUES
(1,{{Workflow | Attribute:'NewEventItemId'}},NEWID())
                

Add another SQL run action called "Create Event Item Occurrence" with the following SQL. This creates the event item occurrence on the calendar, either attaching it to the pre-existing event item, or the event item just created in the previous step. It also makes a copy of the schedule the reservation uses. Be sure to set the result attribute to the "New Event Item Occurrence Id" attribute.

{% assign reservation = Workflow | Attribute:'Reservation','Object' %}
{% assign eventItem = Workflow | Attribute:'ExistingEventItem','RawValue' %}

{% assign newEventItemId = Workflow | Attribute:'NewEventItemId' %}

{% if newEventItemId != '' %}
    {% assign eventItemId = newEventItemId %}
{% else %}
    {% eventitem where:'Guid == "{{eventItem}}"' %}
        {% for eventitem in eventitemItems %}
            {% assign eventItemId = eventitem.Id %}
        {% endfor %}
    {% endeventitem %}
{% endif %}

DECLARE @id int
DECLARE @table table (id int)
INSERT INTO [dbo].[Schedule]
    (
        [Name]
        ,[Description]
        ,[iCalendarContent]
        ,[EffectiveStartDate]
        ,[EffectiveEndDate]
        ,[CategoryId]
        ,[Guid]
        ,[WeeklyDayOfWeek]
        ,[WeeklyTimeOfDay]
        ,[IsActive]
    )
OUTPUT inserted.id into @table
VALUES
(
    '{{ reservation.Schedule.Name }}'
    ,'{{ reservation.Schedule.Description }}'
    ,'{{ reservation.Schedule.iCalendarContent }}'
    ,'{{ reservation.Schedule.EffectiveStartDate | Date:'yyyyMMdd' }}'
    ,'{{ reservation.Schedule.EffectiveEndDate | Date:'yyyyMMdd' }}'
    ,{% if reservation.Schedule.CategoryId %}{{ reservation.Schedule.CategoryId }}{% else %}NULL{% endif %}
    ,NEWID()
    ,{% if reservation.Schedule.WeeklyDayOfWeek %}{{ reservation.Schedule.WeeklyDayOfWeek }}{% else %}NULL{% endif %}
    ,'{{ reservation.Schedule.WeeklyTimeOfDay }}'
    ,1
)
SELECT @id = id from @table

INSERT INTO [dbo].[EventItemOccurrence]
            ([EventItemId]
            ,[CampusId]
            ,[Location]
            ,[ContactPersonAliasId]
            ,[ContactPhone]
            ,[ContactEmail]
            ,[Note]
            ,[ScheduleId]
            ,[Guid])
        OUTPUT Inserted.Id
        VALUES
            ({{ eventItemId }}
            ,NULL
            ,'{{ reservation.ReservationLocations | Select:'Location' | Select:'Name' | Join:', ' }}'
            ,{{ reservation.EventContactPersonAliasId }}
            ,'{{ reservation.EventContactPhone }}'
            ,'{{ reservation.EventContactEmail }}'
            ,''
            ,@id
            ,NEWID())
                

Now we need to associate the newly created calendar items with the reservation from whence they came. Add an "Entity Attribute Set" action and set the CalendarOccurrence attribute to "New Event Item Occurence Id".

You're almost there! Add one more action; a Redirect with the following URL. Also remember to check "Activity is completed on success" on this action. This will redirect users to the same reservation page they created the calendar event from.

/ReservationDetail?ReservationId={% assign reservation = Workflow | Attribute:'Reservation','Object' %}{{reservation.Id}}

Save the workflow, and make a note of the workflow type ID. You will need this in a future step.

Note: This will be unique to your workflow; don't use 38 from the screenshot below.

On the whole, you should now have a workflow with 4 attributes, 2 activities, and 6 actions. Breathe a big sigh of relief! The hardest part is behind you.

Step 2b: Creating the second workflow

The goal of this step is to create a workflow responsible for updating the Calendar when a Room Reservation changes.

To get started, create a new workflow called "Reservation Change to Calendar". It should not be automatically persisted.

Add 2 workflow attributes:

Add a SQL run action to the Start action titled "Update Schedule", and set it to "Run If Change Equal To Schedule". This action will be responsible for updating the schedule on the calendar to match the reservation. Use the following SQL:

{% assign reservation = Workflow | Attribute:'Reservation','Object' %}
{% assign eventItemOccurrenceId = reservation | Attribute:'CalendarOccurrence','RawValue' %}

{% eventitemoccurrence id:'{{eventItemOccurrenceId}}' %}

{% if eventitemoccurrence.ScheduleId %}

UPDATE [dbo].[Schedule]
    SET [iCalendarContent] = '{{ reservation.Schedule.iCalendarContent }}'
        ,[EffectiveStartDate] = '{{ reservation.Schedule.EffectiveStartDate | Date:'yyyyMMdd' }}'
        ,[EffectiveEndDate] = '{{ reservation.Schedule.EffectiveEndDate | Date:'yyyyMMdd' }}'
        ,[WeeklyDayOfWeek] = {% if reservation.Schedule.WeeklyDayOfWeek %}{{ reservation.Schedule.WeeklyDayOfWeek }}{% else %}NULL{% endif %}
        ,[WeeklyTimeOfDay] = '{{ reservation.Schedule.WeeklyTimeOfDay }}'
WHERE Id = {{ eventitemoccurrence.ScheduleId }}

{% else %}

DECLARE @id int
DECLARE @table table (id int)
INSERT INTO [dbo].[Schedule]
    (
        [Name]
        ,[Description]
        ,[iCalendarContent]
        ,[EffectiveStartDate]
        ,[EffectiveEndDate]
        ,[CategoryId]
        ,[Guid]
        ,[WeeklyDayOfWeek]
        ,[WeeklyTimeOfDay]
        ,[IsActive]
    )
OUTPUT inserted.id into @table
VALUES
(
    '{{ reservation.Schedule.Name }}'
    ,'{{ reservation.Schedule.Description }}'
    ,'{{ reservation.Schedule.iCalendarContent }}'
    ,'{{ reservation.Schedule.EffectiveStartDate | Date:'yyyyMMdd' }}'
    ,'{{ reservation.Schedule.EffectiveEndDate | Date:'yyyyMMdd' }}'
    ,{% if reservation.Schedule.CategoryId %}{{ reservation.Schedule.CategoryId }}{% else %}NULL{% endif %}
    ,NEWID()
    ,{% if reservation.Schedule.WeeklyDayOfWeek %}{{ reservation.Schedule.WeeklyDayOfWeek }}{% else %}NULL{% endif %}
    ,'{{ reservation.Schedule.WeeklyTimeOfDay }}'
    ,1
)
SELECT @id = id from @table

UPDATE [dbo].[EventItemOccurrence]
    SET [ScheduleId] = @id
    WHERE Id = {{eventItemOccurrenceId}}
    
{% endif %}

    
{% endeventitemoccurrence %}
                    

Add another SQL run action to the Start action titled "Update Location", and set it to "Run If Change Equal to Location". This action will be responsible for updating the location on the calendar to match the reservation. Use the following SQL:

{% assign reservation = Workflow | Attribute:'Reservation','Object' %}
{% assign eventItemOccurrenceId = reservation | Attribute:'CalendarOccurrence','RawValue' %}

{% eventitemoccurrence id:'{{eventItemOccurrenceId}}' %}

UPDATE [dbo].[EventItemOccurrence]
    SET [Location] = '{{reservation.ReservationLocations | Select:'Location' | Select:'Name' | Join:', '}}'
    WHERE Id = {{eventItemOccurrenceId}}
    
{% endeventitemoccurrence %}
                    

Add a final SQL run action to the Start action titled "Update Contact", and set it to "Run If Change Equal to Location". This action will be responsible for updating the location on the calendar to match the reservation. Use the following SQL:

{% assign reservation = Workflow | Attribute:'Reservation','Object' %}
{% assign eventItemOccurrenceId = reservation | Attribute:'CalendarOccurrence','RawValue' %}

{% eventitemoccurrence id:'{{eventItemOccurrenceId}}' %}

UPDATE [dbo].[EventItemOccurrence]
    SET [ContactPersonAliasId] = '{{reservation.EventContactPersonAliasId}}'
    ,[ContactPhone] = '{{reservation.EventContactPhone}}'
    ,[ContactEmail] = '{{reservation.EventContactEmail}}'
    WHERE Id = {{eventItemOccurrenceId}}
    
{% endeventitemoccurrence %}
                    

Save the workflow, and make a note of the workflow type ID. You will need this in a future step.

Note: This will be unique to your workflow; don't use 39 from the screenshot below.

On the whole, you should now have a workflow with 2 attributes, 1 activity, and 3 actions.

Step 3: Adding the details panel

The goal of this step is to add a block to the Room Reservation Detail page to indicate whether a reservation is missing from or out of sync with the Rock calendar. The buttons on this block will trigger the workflows you created in step 2.

First, go to the Room Reservation Details page. (Visit the Room Reservation page and click on any reservation to see its details).

On the reservation details page, add a new HTML block to the main zone between the Reservation Detail block and History Log:

Edit the security settings on the block to allow the Rock Entity command:

Edit the HTML block content and paste the following:

{% assign createWorkflowId = 38 %}
{% assign changeWorkflowId = 39 %}

{% if Context.Reservation %}

<div class="panel panel-default" style="margin-bottom: 25px;">

<div class="panel-heading">
    <h3 class="panel-title">Calendar Occurrence</h3>
    </div>
    <div class="panel-body">
    {% assign reservation = Context.Reservation %}
    
    {% assign calendarId = reservation | Attribute:'CalendarOccurrence','RawValue' %}
    
    {% if calendarId == "" %}
        <div class="alert alert-warning">There is no calendar event for this item</div>
        
        <a class="btn btn-default btn-primary pull-right" href="/WorkflowEntry/{{createWorkflowId}}?Reservation={{reservation.Guid}}">Create</a>
    {% else %}
        {% eventitemoccurrence id:'{{calendarId}}' %}
            {% if eventitemoccurrence %}
                {% assign calendarScheduleText = eventitemoccurrence.Schedule.FriendlyScheduleText %}
                
                {% assign iCalUid = eventitemoccurrence.Schedule.iCalendarContent | RegExMatchValue:'UID:\S*' %}
                {% assign iCalDTStamp = eventitemoccurrence.Schedule.iCalendarContent | RegExMatchValue:'DTSTAMP:\S*' %}
                {% assign calendarTrimmedIcal = eventitemoccurrence.Schedule.iCalendarContent | Remove:iCalUid | Remove:iCalDTStamp %}
                
                {% assign iCalUid = reservation.Schedule.iCalendarContent | RegExMatchValue:'UID:\S*' %}
                {% assign iCalDTStamp = reservation.Schedule.iCalendarContent | RegExMatchValue:'DTSTAMP:\S*' %}
                {% assign reservationTrimmedIcal = reservation.Schedule.iCalendarContent | Remove:iCalUid | Remove:iCalDTStamp %}
                
                {% assign locations = reservation.ReservationLocations | Select:'Location' | Select:'Name' | Join:', ' %}
                
                <!-- Schedule difference alert -->
                <div class="alerts" style="margin-top: 25px">
                {% if calendarTrimmedIcal != reservationTrimmedIcal %}
                    <div class="alert alert-danger">The calendar schedule for this event is different from the reservation. <a onclick="if (confirm('Are you sure you want to push the schedule to the calendar?')) { $(this).hide();$.post('/api/Workflows/WorkflowEntry/{{changeWorkflowId}}?Reservation={{reservation.Guid}}&Change=Schedule', function () { location.reload(); }); }" href="#" style="margin-left: 15px;">Push changes to Calendar</a></div>
                {% endif %}
                
                <!-- Location difference alert -->
                {% if locations != eventitemoccurrence.Location %}
                    <div class="alert alert-warning">The locations on the calendar and reservation may not match. <a onclick="if (confirm('Are you sure you want to push the locations to the calendar?')) { $(this).hide();$.post('/api/Workflows/WorkflowEntry/{{changeWorkflowId}}?Reservation={{reservation.Guid}}&Change=Location', function () { location.reload(); }); }" href="#" style="margin-left: 15px;">Push changes to Calendar</a></div>
                {% endif %}
                
                <!-- Contact difference alert -->
                {% if reservation.EventContactEmail != eventitemoccurrence.ContactEmail or reservation.EventContactPhone != eventitemoccurrence.ContactPhone or reservation.EventContactPersonAliasId != eventitemoccurrence.ContactPersonAliasId %}
                    <div class="alert alert-warning">The contact info on the calendar and reservation do not match. <a onclick="if (confirm('Are you sure you want to push the event contact to the calendar?')) { $(this).hide();$.post('/api/Workflows/WorkflowEntry/{{changeWorkflowId}}?Reservation={{reservation.Guid}}&Change=Contact', function () { location.reload(); }); }" href="#" style="margin-left: 15px;">Push changes to Calendar</a></div>
                {% endif %}
                </div>
            
                <div class="row">
                    <div class="col-xs-12 col-md-6">
                        <div class="control-label">Calendar Item:</div>
                        <a href="/page/402?EventItemOccurrenceId={{calendarId}}">{{ eventitemoccurrence.EventItem.Name }}</a><br>
                        
                        <div class="control-label" style="margin-top: 15px;">Calendar Location:</div>
                        {{ eventitemoccurrence.Location }}
                    </div>
                
                    <div class="col-xs-12 col-md-6">
                        <div class="control-label" style="margin-top: 25px;">Calendar Schedule:</div>
                        {{ calendarScheduleText }}<br>
                        
                        <div class="control-label" style="margin-top: 15px;">Calendar Contact:</div>
                        {% if eventitemoccurrence.ContactPersonAliasId %}{% assign eventContact = eventitemoccurrence.ContactPersonAliasId | PersonByAliasId %}{{ eventContact.NickName }} {{ eventContact.LastName }}{% else %}No name{% endif %}<br>
                        {% if eventitemoccurrence.ContactEmail <> '' %}{{ eventitemoccurrence.ContactEmail }}{% else %}No email{% endif %}<br>
                        {% if eventitemoccurrence.ContactPhone <> '' %}{{ eventitemoccurrence.ContactPhone }}{% else %}No phone number{% endif %}
                    </div>
                </div>
            {% else %}
                <div class="alert alert-warning">There is no calendar event for this item</div>
        
                <a class="btn btn-default btn-primary pull-right" href="/WorkflowEntry/{{createWorkflowId}}?Reservation={{reservation.Guid}}">Create</a>
            {% endif %}
        {% endeventitemoccurrence %}
    {% endif %}
    </div>
</div>

{% endif %}
                    

Save the HTML block, and you're all done! The block will now show any changes between this reservation and the Rock calendar. You can create an event item occurrence on the calendar by clicking the "Create" button on this block. If there is already an event item occurrence for this reservation, you can link it by editing the reservation and choosing the existing event item occurrence under the "Calendar Occurrence attribute."

Step 4: Creating the data integrity reports

You're almost there! The goal of this step is to add two reports to Room Management that will show Reservations that are missing or out of sync with the Rock calendar.

To begin, add a new Child Page to the Room Management page titled "Data Integrity":

Click on the new Data Integrity page, and add a new Page Menu block to the main section:

Edit the Page Menu block and change the Template to the following lava:

{% if Page.DisplayChildPages == 'true' and Page.Pages != empty %}
<div class="list-as-blocks">
    <ul>
        {% for childPage in Page.Pages %}
            <li{% if childPage.Current == 'true' %} class="active"{% endif%}>
                <a href="{{ childPage.Url }}">
                    <i class="{{childPage.IconCssClass}}"></i>
                    <h3>{{ childPage.Title }}</h3>
                </a>
            </li>
        {% endfor %}
    </ul>
</div>
{% endif %}
                    

Create a subpage titled "Reservations w/o Calendar Occurrence" and add a Dynamic Data block to this sub page:

Edit the Dynamic Data Block and add the following Query.

DECLARE @attributeId AS INT = 6062 -- << CHANGE ME!!!

declare @tmp table (
    [Id] int,
    [Name] varchar(50)
    ,ApprovalState varchar(15)
    ,[EndDate] date
    ,[EventContactPhone] varchar(30)
    ,[EventContactEmail] varchar(50)
    ,[AdministrativeContactPhone] varchar(30)
    ,[AdministrativeContactEmail] varchar(50)
)

INSERT INTO @tmp
SELECT 
    [Id]
    ,[Name]
    ,CASE [ApprovalState]
        WHEN 5 THEN 'Pending Review'
        WHEN 1 THEN 'Denied'
        WHEN 2 THEN 'Approved'
        END AS ApprovalState
    ,(CASE
        WHEN ISDATE([EndDate]) = 0 THEN CAST('19000101' as Date)
        ELSE [EndDate]
        END) as [EndDate]
    ,[EventContactPhone]
    ,[EventContactEmail]
    ,[AdministrativeContactPhone]
    ,[AdministrativeContactEmail]
FROM
(
SELECT Reservation.[Id]
        ,Reservation.[Name]
        ,[ApprovalState]
        ,(
        SELECT
            CASE
                WHEN [Option1] <> '' THEN SUBSTRING([Option1], 0, 9)
                ELSE SUBSTRING([Option2], 0, 9)
            END
        FROM (
                SELECT
                    REVERSE(SUBSTRING(REVERSE([value]), 0, CHARINDEX(',', REVERSE([value])))) as [Option1],
                    REVERSE(SUBSTRING(REVERSE([value]), 0, CHARINDEX(':', REVERSE([value])))) as [Option2]
                FROM  (
                    SELECT
                        SUBSTRING([value], CHARINDEX('RDATE:', [value]), CHARINDEX('SEQUENCE:', [value]) - CHARINDEX('RDATE:', [value])) as [value]
                    FROM
                        (
                            SELECT
                                REPLACE(
                                    REPLACE(
                                        REPLACE(iCalendarContent,CHAR(13),'')
                                    , CHAR(10),'')
                                , CHAR(32), '') as [value]
                        ) as [RValue]
                    ) as [RDate Segment]
                ) as [EndDate0]
        ) AS [EndDate]
        ,[EventContactPhone]
        ,[EventContactEmail]
        ,[AdministrativeContactPhone]
        ,[AdministrativeContactEmail]
    FROM [dbo].[_com_centralaz_RoomManagement_Reservation] as Reservation
    LEFT JOIN [dbo].[Schedule] ON Reservation.ScheduleId = Schedule.Id
    LEFT JOIN [dbo].AttributeValue ON AttributeValue.EntityId = Reservation.Id AND AttributeValue.AttributeId = @attributeId
    LEFT JOIN [dbo].[EventItemOccurrence] ON AttributeValue.[Value] = EventItemOccurrence.Id
    WHERE EventItemOccurrence.Guid IS NULL AND iCalendarContent LIKE '%RDATE%'

    UNION 

    SELECT Reservation.[Id]
        ,Reservation.[Name]
        ,[ApprovalState]
        ,(
        CASE
            WHEN CHARINDEX('UNTIL', iCalendarContent) > 0 THEN
                (
                    SELECT
                        SUBSTRING([value], 7, 8)
                    FROM
                    (
                        SELECT SUBSTRING(iCalendarContent, CHARINDEX('UNTIL=', iCalendarContent), LEN(iCalendarContent)) as [value]
                    ) as [UNTIL_SUB]
                )
            ELSE ''
        END
        ) AS [EndDate]
        ,[EventContactPhone]
        ,[EventContactEmail]
        ,[AdministrativeContactPhone]
        ,[AdministrativeContactEmail]
    FROM [dbo].[_com_centralaz_RoomManagement_Reservation] as Reservation
    LEFT JOIN [dbo].[Schedule] ON Reservation.ScheduleId = Schedule.Id
    LEFT JOIN [dbo].AttributeValue ON AttributeValue.EntityId = Reservation.Id AND AttributeValue.AttributeId = @attributeId
    LEFT JOIN [dbo].[EventItemOccurrence] ON AttributeValue.[Value] = EventItemOccurrence.Id
    WHERE EventItemOccurrence.Guid IS NULL AND iCalendarContent LIKE '%RRULE%'

    UNION

    SELECT Reservation.[Id]
        ,Reservation.[Name]
        ,[ApprovalState]
        ,CAST([Schedule].EffectiveEndDate as varchar)
        ,[EventContactPhone]
        ,[EventContactEmail]
        ,[AdministrativeContactPhone]
        ,[AdministrativeContactEmail]
    FROM [dbo].[_com_centralaz_RoomManagement_Reservation] as Reservation
    LEFT JOIN [dbo].[Schedule] ON Reservation.ScheduleId = Schedule.Id
    LEFT JOIN [dbo].AttributeValue ON AttributeValue.EntityId = Reservation.Id AND AttributeValue.AttributeId = @attributeId
    LEFT JOIN [dbo].[EventItemOccurrence] ON AttributeValue.[Value] = EventItemOccurrence.Id
    WHERE EventItemOccurrence.Guid IS NULL
    AND iCalendarContent NOT LIKE '%RRULE%' AND iCalendarContent NOT LIKE '%RDATE%'

    ) AS [All Reservations]

    SELECT
    [Id]
    ,[Name]
    ,ApprovalState
    ,CASE WHEN [EndDate] = '1900-01-01' THEN 'None' ELSE CAST([EndDate] as varchar) END as [End Date]
    ,[EventContactPhone]
    ,[EventContactEmail]
    ,[AdministrativeContactPhone]
    ,[AdministrativeContactEmail]
    FROM @tmp
    WHERE EndDate > GETDATE() OR EndDate = '1900-01-01'
    ORDER BY EndDate ASC
                    

Set the Selection URL on the Dynamic Data Block to be /ReservationDetail?ReservationId={Id} and uncheck the box that says "Show Grid Filter". Save the block, and you should see the report in action!

Next, go back to the Data Integrity page. You should see the page you just created. Create a second subpage and title it "Mismatched Information" and add a Dynamic Data block to this sub page:

Edit the Dynamic Data Block and add the following Query.

DECLARE @attributeId AS INT = 5519 -- << CHANGE ME!

SELECT
    [Id],
    [Name],
    CASE
        WHEN (ReservationLocations != CalendarLocation) THEN '<span class="bg-warning">Locations are different</span>'
    END as [Locations],
    CASE
        WHEN (SUBSTRING(ReservationiCal, 0, CHARINDEX('UID:',ReservationiCal)) != SUBSTRING(CalendariCal, 0, CHARINDEX('UID:',ReservationiCal))) THEN '<span class="bg-warning">Schedules are different</span>'
    END as [Schedules],
    CASE
        WHEN ContactMismatches = 1 THEN '<span class="bg-warning">Contact information is different</span>'
    END as [Event Contact]
FROM
    (
        SELECT Reservation.[Id] as Id
            ,Reservation.[Name] as Name
            -- Join Room Management locations by a comma
            ,(SELECT STRING_AGG([Name], ', ') FROM [dbo].Location LEFT JOIN [_com_centralaz_RoomManagement_ReservationLocation] as RLocation ON RLocation.LocationId = Location.Id WHERE RLocation.ReservationId = Reservation.Id ) as ReservationLocations
            ,EventItemOccurrence.Location as CalendarLocation
            ,STUFF([Schedule].iCalendarContent, CHARINDEX('DTSTAMP:', [Schedule].iCalendarContent), 24, '') as [ReservationiCal]
            ,STUFF([CalSchedule].iCalendarContent, CHARINDEX('DTSTAMP:', [Schedule].iCalendarContent), 24, '') as [CalendariCal]
            ,AttributeValue.[Value] AS EventItemOccurrence
            ,CASE
            WHEN EventItemOccurrence.ContactEmail <> Reservation.EventContactEmail THEN 1
            WHEN EventItemOccurrence.ContactPhone <> Reservation.EventContactPhone THEN 1
            WHEN EventItemOccurrence.ContactPersonAliasId <> Reservation.EventContactPersonAliasId THEN 1
            ELSE 0
            END as ContactMismatches
        FROM [dbo].[_com_centralaz_RoomManagement_Reservation] as Reservation
        LEFT JOIN [dbo].[Schedule] ON Reservation.ScheduleId = Schedule.Id
        LEFT JOIN [dbo].AttributeValue ON AttributeValue.EntityId = Reservation.Id AND AttributeValue.AttributeId = @attributeId
        LEFT JOIN [dbo].EventItemOccurrence ON AttributeValue.Value = EventItemOccurrence.Id
        LEFT JOIN [dbo].Schedule as CalSchedule ON EventItemOccurrence.ScheduleId = CalSchedule.Id
        WHERE AttributeValue.[Value] IS NOT NULL
    ) as Reservations
WHERE SUBSTRING(ReservationiCal, 0, CHARINDEX('UID:',ReservationiCal)) != SUBSTRING(CalendariCal, 0, CHARINDEX('UID:',ReservationiCal)) OR ReservationLocations != CalendarLocation OR ContactMismatches = 1
                    

Set the Selection URL on the Dynamic Data Block to be /ReservationDetail?ReservationId={Id} and uncheck the box that says "Show Grid Filter". Save the block, and you should see the report in action!

Next, go back to the Data Integrity page. You should see the two pages you just created.

Congratulations!

You finally made it! Sit back and enjoy the fruit of your labor. If you need to match existing reservations with exisiting event calendar items, edit your reservations and change the "Calendar Occurrence" attribute to be the event item occurrence matching that reservation. Otherwise, you can use your new block to create/update your Rock calendar.