356 lines
18 KiB
Transact-SQL
356 lines
18 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.ProjectActivity', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.ProjectActivity
|
|
(
|
|
ProjectActivityID bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProjectActivity PRIMARY KEY,
|
|
ProjectID int NOT NULL,
|
|
UserID int NULL,
|
|
ActivityType nvarchar(50) NOT NULL,
|
|
EntityType nvarchar(100) NOT NULL,
|
|
EntityID int NULL,
|
|
EntityName nvarchar(300) NULL,
|
|
Description nvarchar(1000) NULL,
|
|
CreatedDateUTC datetime2 NOT NULL CONSTRAINT DF_ProjectActivity_CreatedDateUTC DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_ProjectActivity_Project FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT FK_ProjectActivity_User FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID),
|
|
CONSTRAINT CK_ProjectActivity_ActivityType CHECK (ActivityType IN (N'Created', N'Updated', N'Deleted', N'Archived', N'Restored', N'Shared', N'CollaboratorRemoved'))
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_ProjectActivity_Project_Created' AND object_id = OBJECT_ID(N'dbo.ProjectActivity'))
|
|
CREATE INDEX IX_ProjectActivity_Project_Created ON dbo.ProjectActivity(ProjectID, CreatedDateUTC DESC) INCLUDE (UserID, ActivityType, EntityType, EntityID, EntityName);
|
|
GO
|
|
|
|
DECLARE @AuditColumns TABLE (TableName sysname NOT NULL, ColumnName sysname NOT NULL, Definition nvarchar(200) NOT NULL);
|
|
|
|
INSERT INTO @AuditColumns (TableName, ColumnName, Definition)
|
|
VALUES
|
|
(N'Projects', N'CreatedByUserID', N'int NULL'),
|
|
(N'Projects', N'ModifiedByUserID', N'int NULL'),
|
|
(N'Projects', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'Projects', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'Books', N'CreatedByUserID', N'int NULL'),
|
|
(N'Books', N'ModifiedByUserID', N'int NULL'),
|
|
(N'Books', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'Books', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'Chapters', N'CreatedByUserID', N'int NULL'),
|
|
(N'Chapters', N'ModifiedByUserID', N'int NULL'),
|
|
(N'Chapters', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'Chapters', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'Scenes', N'CreatedByUserID', N'int NULL'),
|
|
(N'Scenes', N'ModifiedByUserID', N'int NULL'),
|
|
(N'Scenes', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'Scenes', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'Characters', N'CreatedByUserID', N'int NULL'),
|
|
(N'Characters', N'ModifiedByUserID', N'int NULL'),
|
|
(N'Characters', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'Characters', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'StoryAssets', N'CreatedByUserID', N'int NULL'),
|
|
(N'StoryAssets', N'ModifiedByUserID', N'int NULL'),
|
|
(N'StoryAssets', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'StoryAssets', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'Locations', N'CreatedByUserID', N'int NULL'),
|
|
(N'Locations', N'ModifiedByUserID', N'int NULL'),
|
|
(N'Locations', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'Locations', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'PlotLines', N'CreatedByUserID', N'int NULL'),
|
|
(N'PlotLines', N'ModifiedByUserID', N'int NULL'),
|
|
(N'PlotLines', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'PlotLines', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'PlotThreads', N'CreatedByUserID', N'int NULL'),
|
|
(N'PlotThreads', N'ModifiedByUserID', N'int NULL'),
|
|
(N'PlotThreads', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'PlotThreads', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'CharacterRelationships', N'CreatedByUserID', N'int NULL'),
|
|
(N'CharacterRelationships', N'ModifiedByUserID', N'int NULL'),
|
|
(N'CharacterRelationships', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'CharacterRelationships', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'RelationshipEvents', N'CreatedByUserID', N'int NULL'),
|
|
(N'RelationshipEvents', N'ModifiedByUserID', N'int NULL'),
|
|
(N'RelationshipEvents', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'RelationshipEvents', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'ThreadEvents', N'CreatedByUserID', N'int NULL'),
|
|
(N'ThreadEvents', N'ModifiedByUserID', N'int NULL'),
|
|
(N'ThreadEvents', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'ThreadEvents', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'AssetEvents', N'CreatedByUserID', N'int NULL'),
|
|
(N'AssetEvents', N'ModifiedByUserID', N'int NULL'),
|
|
(N'AssetEvents', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'AssetEvents', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneWorkflow', N'CreatedByUserID', N'int NULL'),
|
|
(N'SceneWorkflow', N'ModifiedByUserID', N'int NULL'),
|
|
(N'SceneWorkflow', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneWorkflow', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneNotes', N'CreatedByUserID', N'int NULL'),
|
|
(N'SceneNotes', N'ModifiedByUserID', N'int NULL'),
|
|
(N'SceneNotes', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneNotes', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneChecklistItems', N'CreatedByUserID', N'int NULL'),
|
|
(N'SceneChecklistItems', N'ModifiedByUserID', N'int NULL'),
|
|
(N'SceneChecklistItems', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneChecklistItems', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneAttachments', N'CreatedByUserID', N'int NULL'),
|
|
(N'SceneAttachments', N'ModifiedByUserID', N'int NULL'),
|
|
(N'SceneAttachments', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'SceneAttachments', N'ModifiedDateUTC', N'datetime2 NULL'),
|
|
(N'ChapterGoals', N'CreatedByUserID', N'int NULL'),
|
|
(N'ChapterGoals', N'ModifiedByUserID', N'int NULL'),
|
|
(N'ChapterGoals', N'CreatedDateUTC', N'datetime2 NULL'),
|
|
(N'ChapterGoals', N'ModifiedDateUTC', N'datetime2 NULL');
|
|
|
|
DECLARE @TableName sysname;
|
|
DECLARE @ColumnName sysname;
|
|
DECLARE @Definition nvarchar(200);
|
|
DECLARE @Sql nvarchar(max);
|
|
DECLARE audit_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT TableName, ColumnName, Definition FROM @AuditColumns;
|
|
|
|
OPEN audit_cursor;
|
|
FETCH NEXT FROM audit_cursor INTO @TableName, @ColumnName, @Definition;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
IF OBJECT_ID(N'dbo.' + @TableName, N'U') IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.' + @TableName) AND name = @ColumnName)
|
|
BEGIN
|
|
SET @Sql = N'ALTER TABLE dbo.' + QUOTENAME(@TableName) + N' ADD ' + QUOTENAME(@ColumnName) + N' ' + @Definition + N';';
|
|
EXEC sys.sp_executesql @Sql;
|
|
END;
|
|
|
|
FETCH NEXT FROM audit_cursor INTO @TableName, @ColumnName, @Definition;
|
|
END;
|
|
CLOSE audit_cursor;
|
|
DEALLOCATE audit_cursor;
|
|
GO
|
|
|
|
UPDATE p
|
|
SET CreatedDateUTC = COALESCE(p.CreatedDateUTC, p.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(p.ModifiedDateUTC, p.UpdatedDate, p.CreatedDate),
|
|
CreatedByUserID = COALESCE(p.CreatedByUserID, ownerAccess.UserID),
|
|
ModifiedByUserID = COALESCE(p.ModifiedByUserID, ownerAccess.UserID)
|
|
FROM dbo.Projects p
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) UserID
|
|
FROM dbo.ProjectUserAccess
|
|
WHERE ProjectID = p.ProjectID AND AccessRole = N'Owner' AND IsActive = 1
|
|
ORDER BY ProjectUserAccessID
|
|
) ownerAccess;
|
|
GO
|
|
|
|
UPDATE b
|
|
SET CreatedDateUTC = COALESCE(b.CreatedDateUTC, b.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(b.ModifiedDateUTC, b.UpdatedDate, b.CreatedDate),
|
|
CreatedByUserID = COALESCE(b.CreatedByUserID, p.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(b.ModifiedByUserID, p.ModifiedByUserID)
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID;
|
|
GO
|
|
|
|
UPDATE c
|
|
SET CreatedDateUTC = COALESCE(c.CreatedDateUTC, c.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(c.ModifiedDateUTC, c.UpdatedDate, c.CreatedDate),
|
|
CreatedByUserID = COALESCE(c.CreatedByUserID, b.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(c.ModifiedByUserID, b.ModifiedByUserID)
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID;
|
|
GO
|
|
|
|
UPDATE s
|
|
SET CreatedDateUTC = COALESCE(s.CreatedDateUTC, s.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(s.ModifiedDateUTC, s.UpdatedDate, s.CreatedDate),
|
|
CreatedByUserID = COALESCE(s.CreatedByUserID, c.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(s.ModifiedByUserID, c.ModifiedByUserID)
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID;
|
|
GO
|
|
|
|
UPDATE child
|
|
SET CreatedDateUTC = COALESCE(child.CreatedDateUTC, child.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(child.ModifiedDateUTC, child.UpdatedDate, child.CreatedDate),
|
|
CreatedByUserID = COALESCE(child.CreatedByUserID, p.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(child.ModifiedByUserID, p.ModifiedByUserID)
|
|
FROM dbo.Characters child
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = child.ProjectID;
|
|
GO
|
|
|
|
UPDATE child
|
|
SET CreatedDateUTC = COALESCE(child.CreatedDateUTC, child.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(child.ModifiedDateUTC, child.UpdatedDate, child.CreatedDate),
|
|
CreatedByUserID = COALESCE(child.CreatedByUserID, p.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(child.ModifiedByUserID, p.ModifiedByUserID)
|
|
FROM dbo.StoryAssets child
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = child.ProjectID;
|
|
GO
|
|
|
|
UPDATE child
|
|
SET CreatedDateUTC = COALESCE(child.CreatedDateUTC, child.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(child.ModifiedDateUTC, child.UpdatedDate, child.CreatedDate),
|
|
CreatedByUserID = COALESCE(child.CreatedByUserID, p.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(child.ModifiedByUserID, p.ModifiedByUserID)
|
|
FROM dbo.Locations child
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = child.ProjectID;
|
|
GO
|
|
|
|
UPDATE child
|
|
SET CreatedDateUTC = COALESCE(child.CreatedDateUTC, child.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(child.ModifiedDateUTC, child.UpdatedDate, child.CreatedDate),
|
|
CreatedByUserID = COALESCE(child.CreatedByUserID, p.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(child.ModifiedByUserID, p.ModifiedByUserID)
|
|
FROM dbo.PlotLines child
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = child.ProjectID;
|
|
GO
|
|
|
|
UPDATE pt
|
|
SET CreatedDateUTC = COALESCE(pt.CreatedDateUTC, pt.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(pt.ModifiedDateUTC, pt.UpdatedDate, pt.CreatedDate),
|
|
CreatedByUserID = COALESCE(pt.CreatedByUserID, pl.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(pt.ModifiedByUserID, pl.ModifiedByUserID)
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID;
|
|
GO
|
|
|
|
UPDATE cr
|
|
SET CreatedDateUTC = COALESCE(cr.CreatedDateUTC, cr.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(cr.ModifiedDateUTC, cr.UpdatedDate, cr.CreatedDate),
|
|
CreatedByUserID = COALESCE(cr.CreatedByUserID, p.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(cr.ModifiedByUserID, p.ModifiedByUserID)
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = cr.ProjectID;
|
|
GO
|
|
|
|
UPDATE re
|
|
SET CreatedDateUTC = COALESCE(re.CreatedDateUTC, re.CreatedDate),
|
|
ModifiedDateUTC = COALESCE(re.ModifiedDateUTC, re.UpdatedDate, re.CreatedDate),
|
|
CreatedByUserID = COALESCE(re.CreatedByUserID, cr.CreatedByUserID),
|
|
ModifiedByUserID = COALESCE(re.ModifiedByUserID, cr.ModifiedByUserID)
|
|
FROM dbo.RelationshipEvents re
|
|
INNER JOIN dbo.CharacterRelationships cr ON cr.CharacterRelationshipID = re.CharacterRelationshipID;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectActivity_Create
|
|
@ProjectID int,
|
|
@UserID int = NULL,
|
|
@ActivityType nvarchar(50),
|
|
@EntityType nvarchar(100),
|
|
@EntityID int = NULL,
|
|
@EntityName nvarchar(300) = NULL,
|
|
@Description nvarchar(1000) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT dbo.ProjectActivity (ProjectID, UserID, ActivityType, EntityType, EntityID, EntityName, Description)
|
|
VALUES (@ProjectID, @UserID, @ActivityType, @EntityType, @EntityID, @EntityName, @Description);
|
|
|
|
SELECT CAST(SCOPE_IDENTITY() AS bigint);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectAudit_Record
|
|
@ProjectID int,
|
|
@UserID int = NULL,
|
|
@ActivityType nvarchar(50),
|
|
@EntityType nvarchar(100),
|
|
@EntityID int = NULL,
|
|
@EntityName nvarchar(300) = NULL,
|
|
@Description nvarchar(1000) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @Now datetime2 = SYSUTCDATETIME();
|
|
|
|
EXEC dbo.ProjectActivity_Create @ProjectID, @UserID, @ActivityType, @EntityType, @EntityID, @EntityName, @Description;
|
|
|
|
IF @UserID IS NULL OR @EntityID IS NULL
|
|
RETURN;
|
|
|
|
IF @EntityType = N'Project'
|
|
UPDATE dbo.Projects SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE ProjectID = @EntityID;
|
|
ELSE IF @EntityType = N'Book'
|
|
UPDATE dbo.Books SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE BookID = @EntityID;
|
|
ELSE IF @EntityType = N'Chapter'
|
|
UPDATE dbo.Chapters SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE ChapterID = @EntityID;
|
|
ELSE IF @EntityType = N'Scene'
|
|
UPDATE dbo.Scenes SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE SceneID = @EntityID;
|
|
ELSE IF @EntityType = N'Character'
|
|
UPDATE dbo.Characters SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE CharacterID = @EntityID;
|
|
ELSE IF @EntityType = N'Asset'
|
|
UPDATE dbo.StoryAssets SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE StoryAssetID = @EntityID;
|
|
ELSE IF @EntityType = N'Location'
|
|
UPDATE dbo.Locations SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE LocationID = @EntityID;
|
|
ELSE IF @EntityType = N'Plot Line'
|
|
UPDATE dbo.PlotLines SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE PlotLineID = @EntityID;
|
|
ELSE IF @EntityType = N'Thread'
|
|
UPDATE dbo.PlotThreads SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE PlotThreadID = @EntityID;
|
|
ELSE IF @EntityType = N'Relationship'
|
|
UPDATE dbo.CharacterRelationships SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE CharacterRelationshipID = @EntityID;
|
|
ELSE IF @EntityType = N'Relationship Event'
|
|
UPDATE dbo.RelationshipEvents SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE RelationshipEventID = @EntityID;
|
|
ELSE IF @EntityType = N'Thread Event'
|
|
UPDATE dbo.ThreadEvents SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE ThreadEventID = @EntityID;
|
|
ELSE IF @EntityType = N'Asset Event'
|
|
UPDATE dbo.AssetEvents SET CreatedByUserID = CASE WHEN @ActivityType = N'Created' THEN COALESCE(CreatedByUserID, @UserID) ELSE CreatedByUserID END,
|
|
CreatedDateUTC = COALESCE(CreatedDateUTC, CreatedDate, @Now), ModifiedByUserID = @UserID, ModifiedDateUTC = @Now WHERE AssetEventID = @EntityID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectActivity_ListForProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (200)
|
|
pa.ProjectActivityID,
|
|
pa.ProjectID,
|
|
pa.UserID,
|
|
u.DisplayName,
|
|
u.Email,
|
|
pa.ActivityType,
|
|
pa.EntityType,
|
|
pa.EntityID,
|
|
pa.EntityName,
|
|
pa.Description,
|
|
pa.CreatedDateUTC
|
|
FROM dbo.ProjectActivity pa
|
|
LEFT JOIN dbo.AppUser u ON u.UserID = pa.UserID
|
|
WHERE pa.ProjectID = @ProjectID
|
|
ORDER BY pa.CreatedDateUTC DESC, pa.ProjectActivityID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Relationship_Get
|
|
@CharacterRelationshipID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT cr.CharacterRelationshipID, cr.ProjectID, cr.CharacterAID, ca.CharacterName AS CharacterAName,
|
|
cr.CharacterBID, cb.CharacterName AS CharacterBName, cr.RelationshipTypeID, rt.TypeName AS RelationshipTypeName,
|
|
cr.IsPermanent, cr.StartSceneID, cr.EndSceneID, cr.IsKnownToReader, cr.Notes, cr.CreatedDate, cr.UpdatedDate, cr.IsArchived
|
|
FROM dbo.CharacterRelationships cr
|
|
INNER JOIN dbo.Characters ca ON ca.CharacterID = cr.CharacterAID
|
|
INNER JOIN dbo.Characters cb ON cb.CharacterID = cr.CharacterBID
|
|
INNER JOIN dbo.RelationshipTypes rt ON rt.RelationshipTypeID = cr.RelationshipTypeID
|
|
WHERE cr.CharacterRelationshipID = @CharacterRelationshipID;
|
|
END;
|
|
GO
|