dspConduct
Content WebApp Example: Role Status BY User Views
NOTE: The Unbounded Security view must be created prior to creating these views.
/****** Object: View [dbo].[webRequest_RoleStatusByUser0Sel] Script Date: 3/2/2016 10:47:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[webRequest_RoleStatusByUser0Sel]
AS
SELECT dbo.ttRequest.RequestID, dbo.ttRequestRole.RoleID, dbo.ttRequestRoleSLA.DueDate, dbo.ttRequestRoleSLA.LastEvaluation
FROM dbo.ttRequest INNER JOIN
dbo.ttRequestRole ON dbo.ttRequest.RequestID = dbo.ttRequestRole.RequestID INNER JOIN
dbo.ttRequestRoleSLA ON dbo.ttRequestRole.RequestID = dbo.ttRequestRoleSLA.RequestID AND dbo.ttRequestRole.RoleID = dbo.ttRequestRoleSLA.RoleID
WHERE (dbo.ttRequestRole.DependencyComplete = 1) AND (dbo.ttRequestRole.Finished = 0)
GO
/****** Object: View [dbo].[webRequest_RoleStatusByUser1Sel] Script Date: 3/2/2016 10:47:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[webRequest_RoleStatusByUser1Sel]
AS
SELECT dbo.webSecurity_UnboundedSel.RequestID, dbo.webSecurity_UnboundedSel.RoleID, dbo.webSecurity_UnboundedSel.boaUserID, CASE WHEN SubmittedOn IS NULL
THEN '0' WHEN Finished = '1' THEN '1' WHEN Finished = '0' AND DependencyComplete = '0' THEN '2' WHEN Finished = '0' AND DependencyComplete = '1' AND
NOT COALESCE (webRequest_RoleStatusByUser0Sel.DueDate, GETDATE()) < GETDATE() THEN '3' WHEN Finished = '0' AND DependencyComplete = '1' AND
webRequest_RoleStatusByUser0Sel.DueDate < GETDATE() THEN '4' END AS RoleStatus
FROM dbo.webSecurity_UnboundedSel INNER JOIN
dbo.ttRequestRole ON dbo.webSecurity_UnboundedSel.RequestID = dbo.ttRequestRole.RequestID AND dbo.webSecurity_UnboundedSel.RoleID = dbo.ttRequestRole.RoleID INNER JOIN
dbo.ttRequest ON dbo.ttRequestRole.RequestID = dbo.ttRequest.RequestID LEFT OUTER JOIN
dbo.webRequest_RoleStatusByUser0Sel ON dbo.ttRequestRole.RequestID = dbo.webRequest_RoleStatusByUser0Sel.RequestID AND dbo.ttRequestRole.RoleID = dbo.webRequest_RoleStatusByUser0Sel.RoleID
GO
/****** Object: View [dbo].[webRequest_RoleStatusByUser2Sel] Script Date: 3/2/2016 10:47:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[webRequest_RoleStatusByUser2Sel]
AS
SELECT RequestID, boaUserID, MAX(CAST(RoleStatus AS int)) AS RoleStatus
FROM dbo.webRequest_RoleStatusByUser1Sel
GROUP BY RequestID, boaUserID
GO
/****** Object: View [dbo].[webRequest_RoleStatusByUser3Sel] Script Date: 3/2/2016 10:47:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[webRequest_RoleStatusByUser3Sel]
AS
SELECT RequestID, boaUserID, CASE WHEN RoleStatus = '1' THEN 'BlueCheck' WHEN RoleStatus = '3' THEN 'GreenPlus' WHEN RoleStatus = '4' THEN 'RedX' ELSE 'YellowYieldWithExclamation' END AS RS
FROM dbo.webRequest_RoleStatusByUser2Sel
GO