8 Add 'My Reservation Approvals' To Dashboard Shared by David Turner, Pillars 3 years ago 9.0 Operations Intermediate If you are using the Room Management plugin from Central Christian Church you can use this recipe to add a new "My Reservation Approvals" section to the dashboard page. This will list any reservations that still require approval by the person viewing their dashboard and specifically what needs to be approved by them.To add this section, edit the Zones on the Dashboard page and add a new Dynamic Data block to the Main zone. After adding the new block, edit the settings and set the following values: Query: DECLARE @PersonId int = {{ CurrentPerson.Id }} DECLARE @LocationEntityTypeId int = ( SELECT TOP 1 [Id] FROM [EntityType] WHERE [Name] = 'Rock.Model.Location' ) DECLARE @LocationApprovalGroupAttrId int = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @LocationEntityTypeId AND [Key] = 'ApprovalGroup' ) ;WITH CTE AS ( SELECT R.[Id], U.[Order], U.[ApprovalsNeeded] FROM [_com_centralaz_RoomManagement_Reservation] R INNER JOIN [_com_centralaz_RoomManagement_ReservationType] T ON T.[Id] = R.[ReservationTypeId] LEFT OUTER JOIN [GroupMember] SAG ON SAG.[GroupId] = T.[SuperAdminGroupId] AND SAG.[PersonId] = @PersonId CROSS APPLY ( SELECT 0 AS [Order], CONCAT( L.[Name], ' (Location)' ) AS [ApprovalsNeeded] FROM [_com_centralaz_RoomManagement_ReservationLocation] RL INNER JOIN [Location] L ON L.[Id] = RL.[LocationId] LEFT OUTER JOIN [AttributeValue] V ON V.[AttributeId] = @LocationApprovalGroupAttrId AND V.[EntityId] = RL.[LocationId] AND V.[Value] IS NOT NULL AND V.[Value] <> '' LEFT OUTER JOIN [Group] G ON G.[Guid] = V.[Value] LEFT OUTER JOIN [GroupMember] M ON M.[GroupId] = G.[Id] AND M.[GroupMemberStatus] = 1 AND M.[PersonId] = @PersonId WHERE RL.[ReservationId] = R.[Id] AND RL.[ApprovalState] NOT IN (2,3) -- Approved,Denied AND ( SAG.[Id] IS NOT NULL OR M.[Id] IS NOT NULL ) UNION ALL SELECT 1, CONCAT( R.[Name], ' (Resource)' ) FROM [_com_centralaz_RoomManagement_ReservationResource] RR INNER JOIN [_com_centralaz_RoomManagement_Resource] RRR ON RRR.[Id] = RR.[ResourceId] LEFT OUTER JOIN [GroupMember] M ON M.[GroupId] = RRR.[ApprovalGroupId] AND M.[GroupMemberStatus] = 1 AND M.[PersonId] = @PersonId WHERE RR.[ReservationId] = R.[Id] AND RR.[ApprovalState] NOT IN (2,3) -- Approved,Denied AND ( SAG.[Id] IS NOT NULL OR M.[Id] IS NOT NULL ) UNION ALL SELECT 2, 'Final Approval' FROM [_com_centralaz_RoomManagement_ReservationType] T LEFT OUTER JOIN [GroupMember] M ON M.[GroupId] = T.[FinalApprovalGroupId] AND M.[GroupMemberStatus] = 1 AND M.[PersonId] = @PersonId WHERE T.[Id] = R.[ReservationTypeId] AND R.[ApprovalState] NOT IN (2,3) -- Approved,Denied AND ( SAG.[Id] IS NOT NULL OR M.[Id] IS NOT NULL ) AND NOT EXISTS ( SELECT [Id] FROM [_com_centralaz_RoomManagement_ReservationLocation] WHERE [ReservationId] = R.[Id] AND [ApprovalState] <> 2 ) AND NOT EXISTS ( SELECT [Id] FROM [_com_centralaz_RoomManagement_ReservationResource] WHERE [ReservationId] = R.[Id] AND [ApprovalState] <> 2 ) ) U WHERE U.[Order] IS NOT NULL ) SELECT R.[Id], R.[Name], R.[FirstOccurrenceStartDateTime], T.[Name] AS [Type], AP.[Id] AS [AdministrativeContactId], CONCAT( AP.[NickName] + ' ', AP.[LastName] ) AS [AdministrativeContact], EP.[Id] AS [EventContactId], CONCAT( EP.[NickName] + ' ', EP.[LastName] ) AS [EventContact], R.[Note], REPLACE(G.[ApprovalsNeeded],'|','') AS [ApprovalsNeeded] FROM ( SELECT T1.[Id], STUFF ( ( SELECT '|' + [ApprovalsNeeded] FROM CTE WHERE CTE.[Id] = T1.[Id] FOR XML PATH('') ),1,1,'' ) AS [ApprovalsNeeded] FROM CTE T1 GROUP BY T1.[Id] ) G INNER JOIN [_com_centralaz_RoomManagement_Reservation] R ON R.[Id] = G.[Id] INNER JOIN [_com_centralaz_RoomManagement_ReservationType] T ON T.[Id] = R.[ReservationTypeId] LEFT OUTER JOIN [Schedule] S ON S.[Id] = R.[ScheduleId] LEFT OUTER JOIN [PersonAlias] APA ON APA.[Id] = R.[AdministrativeContactPersonAliasId] LEFT OUTER JOIN [Person] AP ON AP.[Id] = APA.[PersonId] LEFT OUTER JOIN [PersonAlias] EPA ON EPA.[Id] = R.[EventContactPersonAliasId] LEFT OUTER JOIN [Person] EP ON EP.[Id] = EPA.[PersonId] ORDER BY R.[FirstOccurrenceStartDateTime] Formatted Output: My Room Reservation Approvals {% assign count = rows | Size %} {% if count and count > 0 %} Name When Type Admin Contact Event Contact Note Approvals Needed {% for row in rows %} {{ row.Name }} {{ row.FirstOccurrenceStartDateTime | Date:'MM/dd/yy hh:mm tt' }} {{ row.Type }} {{ row.AdministrativeContact }} {{ row.EventContact }} {{ row.Note }} {{ row.ApprovalsNeeded }} {% endfor %} {% else %} There are no reservations requiring approval. {% endif %}