252 lines
9.3 KiB
Transact-SQL
252 lines
9.3 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = N'FeatureRequests' AND schema_id = SCHEMA_ID(N'dbo'))
|
|
BEGIN
|
|
CREATE TABLE dbo.FeatureRequests
|
|
(
|
|
FeatureRequestID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_FeatureRequests PRIMARY KEY,
|
|
UserID int NOT NULL,
|
|
Title nvarchar(200) NOT NULL,
|
|
Description nvarchar(max) NOT NULL,
|
|
AppArea nvarchar(100) NULL,
|
|
Importance nvarchar(50) NULL,
|
|
Status nvarchar(50) NOT NULL CONSTRAINT DF_FeatureRequests_Status DEFAULT N'New',
|
|
IsPublicCandidate bit NOT NULL CONSTRAINT DF_FeatureRequests_IsPublicCandidate DEFAULT 0,
|
|
AdminNotes nvarchar(max) NULL,
|
|
CreatedUtc datetime2 NOT NULL CONSTRAINT DF_FeatureRequests_CreatedUtc DEFAULT sysutcdatetime(),
|
|
UpdatedUtc datetime2 NOT NULL CONSTRAINT DF_FeatureRequests_UpdatedUtc DEFAULT sysutcdatetime(),
|
|
ClosedUtc datetime2 NULL,
|
|
ShippedUtc datetime2 NULL,
|
|
CONSTRAINT FK_FeatureRequests_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = N'FeatureRequestMessages' AND schema_id = SCHEMA_ID(N'dbo'))
|
|
BEGIN
|
|
CREATE TABLE dbo.FeatureRequestMessages
|
|
(
|
|
FeatureRequestMessageID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_FeatureRequestMessages PRIMARY KEY,
|
|
FeatureRequestID int NOT NULL,
|
|
UserID int NULL,
|
|
IsAdmin bit NOT NULL CONSTRAINT DF_FeatureRequestMessages_IsAdmin DEFAULT 0,
|
|
MessageBody nvarchar(max) NOT NULL,
|
|
CreatedUtc datetime2 NOT NULL CONSTRAINT DF_FeatureRequestMessages_CreatedUtc DEFAULT sysutcdatetime(),
|
|
CONSTRAINT FK_FeatureRequestMessages_FeatureRequests FOREIGN KEY (FeatureRequestID) REFERENCES dbo.FeatureRequests(FeatureRequestID),
|
|
CONSTRAINT FK_FeatureRequestMessages_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeatureRequests_UserID' AND object_id = OBJECT_ID(N'dbo.FeatureRequests'))
|
|
CREATE INDEX IX_FeatureRequests_UserID ON dbo.FeatureRequests(UserID);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeatureRequests_Status' AND object_id = OBJECT_ID(N'dbo.FeatureRequests'))
|
|
CREATE INDEX IX_FeatureRequests_Status ON dbo.FeatureRequests(Status);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeatureRequests_CreatedUtc' AND object_id = OBJECT_ID(N'dbo.FeatureRequests'))
|
|
CREATE INDEX IX_FeatureRequests_CreatedUtc ON dbo.FeatureRequests(CreatedUtc);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeatureRequestMessages_FeatureRequestID' AND object_id = OBJECT_ID(N'dbo.FeatureRequestMessages'))
|
|
CREATE INDEX IX_FeatureRequestMessages_FeatureRequestID ON dbo.FeatureRequestMessages(FeatureRequestID);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeatureRequestMessages_CreatedUtc' AND object_id = OBJECT_ID(N'dbo.FeatureRequestMessages'))
|
|
CREATE INDEX IX_FeatureRequestMessages_CreatedUtc ON dbo.FeatureRequestMessages(CreatedUtc);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_Create
|
|
@UserID int,
|
|
@Title nvarchar(200),
|
|
@Description nvarchar(max),
|
|
@AppArea nvarchar(100) = NULL,
|
|
@Importance nvarchar(50) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT INTO dbo.FeatureRequests (UserID, Title, Description, AppArea, Importance)
|
|
VALUES (@UserID, @Title, @Description, NULLIF(@AppArea, N''), NULLIF(@Importance, N''));
|
|
|
|
DECLARE @FeatureRequestID int = SCOPE_IDENTITY();
|
|
|
|
EXEC dbo.FeatureRequest_GetForUser @FeatureRequestID = @FeatureRequestID, @UserID = @UserID;
|
|
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,
|
|
latest.LatestActivityUtc
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT MAX(frm.CreatedUtc) AS LatestActivityUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) latest
|
|
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,
|
|
latest.LatestActivityUtc
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT MAX(frm.CreatedUtc) AS LatestActivityUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) latest
|
|
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,
|
|
latest.LatestActivityUtc
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT MAX(frm.CreatedUtc) AS LatestActivityUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) latest
|
|
WHERE fr.UserID = @UserID
|
|
ORDER BY fr.UpdatedUtc DESC, fr.FeatureRequestID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.FeatureRequest_ListForAdmin
|
|
@Status nvarchar(50) = NULL
|
|
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,
|
|
latest.LatestActivityUtc
|
|
FROM dbo.FeatureRequests fr
|
|
INNER JOIN dbo.AppUser au ON au.UserID = fr.UserID
|
|
OUTER APPLY
|
|
(
|
|
SELECT MAX(frm.CreatedUtc) AS LatestActivityUtc
|
|
FROM dbo.FeatureRequestMessages frm
|
|
WHERE frm.FeatureRequestID = fr.FeatureRequestID
|
|
) latest
|
|
WHERE @Status IS NULL OR fr.Status = @Status
|
|
ORDER BY fr.UpdatedUtc 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(),
|
|
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_UpdateAdminNotes
|
|
@FeatureRequestID int,
|
|
@AdminNotes nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.FeatureRequests
|
|
SET AdminNotes = NULLIF(@AdminNotes, N''),
|
|
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()
|
|
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.FeatureRequestMessage_List
|
|
@FeatureRequestID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
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.FeatureRequestID = @FeatureRequestID
|
|
ORDER BY frm.CreatedUtc ASC, frm.FeatureRequestMessageID ASC;
|
|
END;
|
|
GO
|