332 lines
12 KiB
Transact-SQL
332 lines
12 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.FeatureRequests', N'UserLastViewedUtc') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.FeatureRequests ADD UserLastViewedUtc datetime2 NULL;
|
|
END;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.FeatureRequests', N'UserAttentionUtc') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.FeatureRequests ADD UserAttentionUtc datetime2 NULL;
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeatureRequests_UserAttention' AND object_id = OBJECT_ID(N'dbo.FeatureRequests'))
|
|
BEGIN
|
|
CREATE INDEX IX_FeatureRequests_UserAttention
|
|
ON dbo.FeatureRequests(UserID, UserAttentionUtc)
|
|
WHERE UserAttentionUtc IS NOT NULL;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_GetForUser
|
|
@FeatureRequestID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT fr.FeatureRequestID, fr.UserID, au.Email AS UserEmail, au.DisplayName AS UserDisplayName,
|
|
fr.Title, fr.Description, fr.AppArea, fr.Importance, fr.Status, fr.IsPublicCandidate,
|
|
fr.AdminNotes, fr.CreatedUtc, fr.UpdatedUtc, fr.ClosedUtc, fr.ShippedUtc,
|
|
COALESCE(latest.CreatedUtc, fr.UpdatedUtc, fr.CreatedUtc) AS LatestActivityUtc,
|
|
latest.IsAdmin AS LatestMessageIsAdmin,
|
|
latest.MessageBody AS LatestMessagePreview,
|
|
messageCounts.MessageCount,
|
|
fr.UserLastViewedUtc,
|
|
fr.UserAttentionUtc,
|
|
CAST(CASE WHEN fr.UserAttentionUtc IS NOT NULL THEN 1 ELSE 0 END AS bit) AS UserNeedsAttention
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) frm.IsAdmin, frm.MessageBody, frm.CreatedUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
ORDER BY frm.CreatedUtc DESC, frm.FeatureRequestMessageID DESC
|
|
) latest
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(1) AS MessageCount
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) messageCounts
|
|
WHERE fr.FeatureRequestID = @FeatureRequestID
|
|
AND fr.UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_GetForAdmin
|
|
@FeatureRequestID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT fr.FeatureRequestID, fr.UserID, au.Email AS UserEmail, au.DisplayName AS UserDisplayName,
|
|
fr.Title, fr.Description, fr.AppArea, fr.Importance, fr.Status, fr.IsPublicCandidate,
|
|
fr.AdminNotes, fr.CreatedUtc, fr.UpdatedUtc, fr.ClosedUtc, fr.ShippedUtc,
|
|
COALESCE(latest.CreatedUtc, fr.UpdatedUtc, fr.CreatedUtc) AS LatestActivityUtc,
|
|
latest.IsAdmin AS LatestMessageIsAdmin,
|
|
latest.MessageBody AS LatestMessagePreview,
|
|
messageCounts.MessageCount,
|
|
fr.UserLastViewedUtc,
|
|
fr.UserAttentionUtc,
|
|
CAST(CASE WHEN fr.UserAttentionUtc IS NOT NULL THEN 1 ELSE 0 END AS bit) AS UserNeedsAttention
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) frm.IsAdmin, frm.MessageBody, frm.CreatedUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
ORDER BY frm.CreatedUtc DESC, frm.FeatureRequestMessageID DESC
|
|
) latest
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(1) AS MessageCount
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) messageCounts
|
|
WHERE fr.FeatureRequestID = @FeatureRequestID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_ListForUser
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT fr.FeatureRequestID, fr.UserID, au.Email AS UserEmail, au.DisplayName AS UserDisplayName,
|
|
fr.Title, fr.Description, fr.AppArea, fr.Importance, fr.Status, fr.IsPublicCandidate,
|
|
CAST(NULL AS nvarchar(max)) AS AdminNotes, fr.CreatedUtc, fr.UpdatedUtc, fr.ClosedUtc, fr.ShippedUtc,
|
|
COALESCE(latest.CreatedUtc, fr.UpdatedUtc, fr.CreatedUtc) AS LatestActivityUtc,
|
|
latest.IsAdmin AS LatestMessageIsAdmin,
|
|
latest.MessageBody AS LatestMessagePreview,
|
|
messageCounts.MessageCount,
|
|
fr.UserLastViewedUtc,
|
|
fr.UserAttentionUtc,
|
|
CAST(CASE WHEN fr.UserAttentionUtc IS NOT NULL THEN 1 ELSE 0 END AS bit) AS UserNeedsAttention
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) frm.IsAdmin, frm.MessageBody, frm.CreatedUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
ORDER BY frm.CreatedUtc DESC, frm.FeatureRequestMessageID DESC
|
|
) latest
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(1) AS MessageCount
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) messageCounts
|
|
WHERE fr.UserID = @UserID
|
|
ORDER BY COALESCE(fr.UserAttentionUtc, latest.CreatedUtc, fr.UpdatedUtc, fr.CreatedUtc) DESC, fr.FeatureRequestID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_ListForAdmin
|
|
@Status nvarchar(50) = NULL,
|
|
@AppArea nvarchar(100) = NULL,
|
|
@Importance nvarchar(50) = NULL,
|
|
@Search nvarchar(200) = NULL,
|
|
@Sort nvarchar(50) = N'LatestActivity'
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @CleanSearch nvarchar(200) = NULLIF(LTRIM(RTRIM(@Search)), N'');
|
|
|
|
SELECT fr.FeatureRequestID, fr.UserID, au.Email AS UserEmail, au.DisplayName AS UserDisplayName,
|
|
fr.Title, fr.Description, fr.AppArea, fr.Importance, fr.Status, fr.IsPublicCandidate,
|
|
fr.AdminNotes, fr.CreatedUtc, fr.UpdatedUtc, fr.ClosedUtc, fr.ShippedUtc,
|
|
COALESCE(latest.CreatedUtc, fr.UpdatedUtc, fr.CreatedUtc) AS LatestActivityUtc,
|
|
latest.IsAdmin AS LatestMessageIsAdmin,
|
|
latest.MessageBody AS LatestMessagePreview,
|
|
messageCounts.MessageCount,
|
|
fr.UserLastViewedUtc,
|
|
fr.UserAttentionUtc,
|
|
CAST(CASE WHEN fr.UserAttentionUtc IS NOT NULL THEN 1 ELSE 0 END AS bit) AS UserNeedsAttention
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) frm.IsAdmin, frm.MessageBody, frm.CreatedUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
ORDER BY frm.CreatedUtc DESC, frm.FeatureRequestMessageID DESC
|
|
) latest
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(1) AS MessageCount
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) messageCounts
|
|
WHERE (@Status IS NULL OR fr.Status = @Status)
|
|
AND (@AppArea IS NULL OR fr.AppArea = @AppArea)
|
|
AND (@Importance IS NULL OR fr.Importance = @Importance)
|
|
AND
|
|
(
|
|
@CleanSearch IS NULL
|
|
OR fr.Title LIKE N'%' + @CleanSearch + N'%'
|
|
OR fr.Description LIKE N'%' + @CleanSearch + N'%'
|
|
OR au.DisplayName LIKE N'%' + @CleanSearch + N'%'
|
|
OR au.Email LIKE N'%' + @CleanSearch + N'%'
|
|
)
|
|
ORDER BY
|
|
CASE WHEN @Sort = N'OldestFirst' THEN fr.CreatedUtc END ASC,
|
|
CASE WHEN @Sort = N'NewestFirst' THEN fr.CreatedUtc END DESC,
|
|
CASE WHEN @Sort IS NULL OR @Sort = N'LatestActivity' THEN COALESCE(latest.CreatedUtc, fr.UpdatedUtc, fr.CreatedUtc) END DESC,
|
|
fr.FeatureRequestID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_UpdateStatus
|
|
@FeatureRequestID int,
|
|
@Status nvarchar(50),
|
|
@IsPublicCandidate bit = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.FeatureRequests
|
|
SET Status = @Status,
|
|
IsPublicCandidate = COALESCE(@IsPublicCandidate, IsPublicCandidate),
|
|
UpdatedUtc = sysutcdatetime(),
|
|
UserAttentionUtc = sysutcdatetime(),
|
|
ClosedUtc = CASE WHEN @Status IN (N'Shipped', N'Not Planned') THEN COALESCE(ClosedUtc, sysutcdatetime()) ELSE NULL END,
|
|
ShippedUtc = CASE WHEN @Status = N'Shipped' THEN COALESCE(ShippedUtc, sysutcdatetime()) ELSE NULL END
|
|
WHERE FeatureRequestID = @FeatureRequestID;
|
|
|
|
EXEC dbo.FeatureRequest_GetForAdmin @FeatureRequestID = @FeatureRequestID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_UpdateRoadmapVisibility
|
|
@FeatureRequestID int,
|
|
@IsPublicCandidate bit
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.FeatureRequests
|
|
SET IsPublicCandidate = @IsPublicCandidate,
|
|
UpdatedUtc = sysutcdatetime()
|
|
WHERE FeatureRequestID = @FeatureRequestID;
|
|
|
|
EXEC dbo.FeatureRequest_GetForAdmin @FeatureRequestID = @FeatureRequestID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequestMessage_Add
|
|
@FeatureRequestID int,
|
|
@UserID int = NULL,
|
|
@IsAdmin bit = 0,
|
|
@MessageBody nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM dbo.FeatureRequests WHERE FeatureRequestID = @FeatureRequestID)
|
|
RETURN;
|
|
|
|
INSERT INTO dbo.FeatureRequestMessages (FeatureRequestID, UserID, IsAdmin, MessageBody)
|
|
VALUES (@FeatureRequestID, @UserID, @IsAdmin, @MessageBody);
|
|
|
|
UPDATE dbo.FeatureRequests
|
|
SET UpdatedUtc = sysutcdatetime(),
|
|
UserAttentionUtc = CASE WHEN @IsAdmin = 1 THEN sysutcdatetime() ELSE UserAttentionUtc END
|
|
WHERE FeatureRequestID = @FeatureRequestID;
|
|
|
|
SELECT frm.FeatureRequestMessageID, frm.FeatureRequestID, frm.UserID, au.Email AS UserEmail,
|
|
au.DisplayName AS UserDisplayName, frm.IsAdmin, frm.MessageBody, frm.CreatedUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
LEFT JOIN dbo.AppUser au ON au.UserID = frm.UserID
|
|
WHERE frm.FeatureRequestMessageID = SCOPE_IDENTITY();
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_MarkViewedForUser
|
|
@FeatureRequestID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.FeatureRequests
|
|
SET UserLastViewedUtc = sysutcdatetime(),
|
|
UserAttentionUtc = NULL
|
|
WHERE FeatureRequestID = @FeatureRequestID
|
|
AND UserID = @UserID;
|
|
|
|
SELECT CAST(@@ROWCOUNT AS bit) AS Succeeded;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_CountAttentionForUser
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(1)
|
|
FROM dbo.FeatureRequests
|
|
WHERE UserID = @UserID
|
|
AND UserAttentionUtc IS NOT NULL;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_CountWaitingForAdmin
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(1)
|
|
FROM dbo.FeatureRequests fr
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) frm.IsAdmin
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
ORDER BY frm.CreatedUtc DESC, frm.FeatureRequestMessageID DESC
|
|
) latest
|
|
WHERE fr.Status NOT IN (N'Shipped', N'Not Planned')
|
|
AND (latest.IsAdmin IS NULL OR latest.IsAdmin = 0);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_ListRoadmap
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT FeatureRequestID, Title, CAST(NULL AS int) AS UserID, CAST(NULL AS nvarchar(256)) AS UserEmail,
|
|
CAST(NULL AS nvarchar(200)) AS UserDisplayName, Description, AppArea, Importance, Status,
|
|
IsPublicCandidate, CAST(NULL AS nvarchar(max)) AS AdminNotes, CreatedUtc, UpdatedUtc,
|
|
ClosedUtc, ShippedUtc, UpdatedUtc AS LatestActivityUtc,
|
|
CAST(NULL AS bit) AS LatestMessageIsAdmin, CAST(NULL AS nvarchar(max)) AS LatestMessagePreview,
|
|
0 AS MessageCount, CAST(NULL AS datetime2) AS UserLastViewedUtc, CAST(NULL AS datetime2) AS UserAttentionUtc,
|
|
CAST(0 AS bit) AS UserNeedsAttention
|
|
FROM dbo.FeatureRequests
|
|
WHERE IsPublicCandidate = 1
|
|
AND Status IN (N'Candidate', N'Planned', N'In Progress', N'Shipped')
|
|
ORDER BY
|
|
CASE Status
|
|
WHEN N'Candidate' THEN 1
|
|
WHEN N'Planned' THEN 2
|
|
WHEN N'In Progress' THEN 3
|
|
WHEN N'Shipped' THEN 4
|
|
ELSE 5
|
|
END,
|
|
CASE WHEN Status = N'Shipped' THEN ShippedUtc END DESC,
|
|
UpdatedUtc DESC,
|
|
FeatureRequestID DESC;
|
|
END;
|
|
GO
|