620 lines
21 KiB
Transact-SQL
620 lines
21 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Project_ExistsByNameForUser
|
|
@ProjectName nvarchar(200),
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(1)
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE p.IsArchived = 0
|
|
AND p.ProjectName = @ProjectName
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Scene_UpdatePovCharacter
|
|
@SceneID int,
|
|
@POVCharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.Scenes
|
|
SET POVCharacterID = @POVCharacterID,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE SceneID = @SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Import_LookupIds
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
(SELECT TOP (1) RevisionStatusID FROM dbo.RevisionStatuses WHERE StatusName = N'Planned' ORDER BY SortOrder) AS RevisionStatusID,
|
|
(SELECT TOP (1) TimeModeID FROM dbo.TimeModes WHERE TimeModeName = N'Relative' ORDER BY SortOrder) AS TimeModeID,
|
|
(SELECT TOP (1) TimeConfidenceID FROM dbo.TimeConfidences WHERE TimeConfidenceName = N'Unknown' ORDER BY SortOrder) AS TimeConfidenceID;
|
|
|
|
SELECT LocationTypeID AS LookupID, TypeName FROM dbo.LocationTypes WHERE IsActive = 1;
|
|
SELECT CharacterRoleInSceneTypeID AS LookupID, TypeName FROM dbo.CharacterRoleInSceneTypes WHERE IsActive = 1;
|
|
SELECT PresenceTypeID AS LookupID, TypeName FROM dbo.PresenceTypes WHERE IsActive = 1;
|
|
SELECT PlotLineTypeID AS LookupID, TypeName FROM dbo.PlotLineTypes WHERE IsActive = 1;
|
|
SELECT PlotImportanceID AS LookupID, ImportanceName AS TypeName FROM dbo.PlotImportance WHERE IsActive = 1;
|
|
SELECT ThreadTypeID AS LookupID, TypeName FROM dbo.ThreadTypes WHERE IsActive = 1;
|
|
SELECT ThreadStatusID AS LookupID, StatusName FROM dbo.ThreadStatuses WHERE IsActive = 1;
|
|
SELECT ThreadEventTypeID AS LookupID, TypeName FROM dbo.ThreadEventTypes WHERE IsActive = 1;
|
|
SELECT SceneDependencyTypeID AS LookupID, TypeName FROM dbo.SceneDependencyTypes WHERE IsActive = 1;
|
|
SELECT AssetKindID AS LookupID, KindName AS TypeName FROM dbo.AssetKinds WHERE IsActive = 1;
|
|
SELECT AssetStateID AS LookupID, StateName AS TypeName FROM dbo.AssetStates WHERE IsActive = 1;
|
|
SELECT AssetEventTypeID AS LookupID, TypeName FROM dbo.AssetEventTypes WHERE IsActive = 1;
|
|
SELECT AssetCustodyEventTypeID AS LookupID, TypeName FROM dbo.AssetCustodyEventTypes WHERE IsActive = 1;
|
|
SELECT CustodyRoleID AS LookupID, RoleName AS TypeName FROM dbo.CustodyRoles WHERE IsActive = 1;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectBackup_BookMetadata_List
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT BookID, Subtitle, Tagline, ShortDescription, Status, TargetWordCount, WritingStartDate, TargetCompletionDate, PublicationDate,
|
|
CoverThumbnailPath, CoverStandardPath
|
|
FROM dbo.Books
|
|
WHERE ProjectID = @ProjectID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WordCompanion_Book_ListByProject
|
|
@ProjectID int,
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT b.BookID AS BookId,
|
|
b.BookTitle AS Title,
|
|
b.Subtitle,
|
|
b.SortOrder
|
|
FROM dbo.Books b
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = b.ProjectID
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
|
|
WHERE b.ProjectID = @ProjectID
|
|
AND pua.UserID = @UserID
|
|
AND pua.IsActive = 1
|
|
AND p.IsArchived = 0
|
|
AND b.IsArchived = 0
|
|
ORDER BY b.SortOrder, b.BookNumber, b.BookTitle, b.BookID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ContinuityWarning_ListDashboardUnresolved
|
|
@ProjectID int,
|
|
@Count int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (@Count)
|
|
cw.ContinuityWarningID,
|
|
cw.ValidationRunID,
|
|
cw.ProjectID,
|
|
cw.BookID,
|
|
b.BookTitle,
|
|
b.Subtitle AS BookSubtitle,
|
|
cw.ChapterID,
|
|
c.ChapterNumber,
|
|
c.ChapterTitle,
|
|
cw.SceneID,
|
|
s.SceneNumber,
|
|
s.SceneTitle,
|
|
cw.EntityType,
|
|
cw.EntityID,
|
|
cw.WarningTypeID,
|
|
wt.TypeName AS WarningTypeName,
|
|
cw.WarningSeverityID,
|
|
ws.SeverityName,
|
|
cw.Message,
|
|
cw.Details,
|
|
cw.IsDismissed,
|
|
cw.IsIntentional,
|
|
cw.CreatedDate,
|
|
cw.LastDetectedDate
|
|
FROM dbo.ContinuityWarnings cw
|
|
INNER JOIN dbo.WarningTypes wt ON wt.WarningTypeID = cw.WarningTypeID
|
|
INNER JOIN dbo.WarningSeverities ws ON ws.WarningSeverityID = cw.WarningSeverityID
|
|
LEFT JOIN dbo.Books b ON b.BookID = cw.BookID
|
|
LEFT JOIN dbo.Chapters c ON c.ChapterID = cw.ChapterID
|
|
LEFT JOIN dbo.Scenes s ON s.SceneID = cw.SceneID
|
|
WHERE cw.ProjectID = @ProjectID
|
|
AND cw.IsDismissed = 0
|
|
AND cw.IsIntentional = 0
|
|
ORDER BY ws.SortOrder DESC, cw.LastDetectedDate DESC, cw.ContinuityWarningID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingPlan_ListForManagement
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT wp.WritingPlanID, wp.UserID, wp.ProjectID, p.ProjectName, wp.BookID, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
wp.PlanName, wp.GoalType, wp.StartDate, wp.DeadlineDate, wp.AvailableDaysJson, wp.SessionLengthMinutes, wp.IsActive,
|
|
SUM(CASE WHEN wsi.ScheduleStatus = N'Planned' THEN 1 ELSE 0 END) AS PlannedTaskCount,
|
|
MAX(CASE WHEN wsi.ScheduleStatus = N'Planned' THEN wsi.ScheduledDate ELSE NULL END) AS ProjectedFinishDate
|
|
FROM dbo.WritingPlans wp
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = wp.ProjectID
|
|
LEFT JOIN dbo.Books b ON b.BookID = wp.BookID
|
|
LEFT JOIN dbo.WritingScheduleItems wsi ON wsi.WritingPlanID = wp.WritingPlanID
|
|
WHERE wp.UserID = @UserID
|
|
GROUP BY wp.WritingPlanID, wp.UserID, wp.ProjectID, p.ProjectName, wp.BookID, b.BookTitle, b.Subtitle,
|
|
wp.PlanName, wp.GoalType, wp.StartDate, wp.DeadlineDate, wp.AvailableDaysJson, wp.SessionLengthMinutes, wp.IsActive
|
|
ORDER BY wp.IsActive DESC, wp.DeadlineDate, wp.PlanName, wp.WritingPlanID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingScheduleScene_ListForPlanning
|
|
@ProjectID int,
|
|
@BookID int = NULL,
|
|
@IncludeBlockedScenes bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT s.SceneID, rs.StatusName AS RevisionStatusName, sw.EstimatedWordCount, sw.Priority,
|
|
COALESCE(sw.IsBlocked, 0) AS IsBlocked, b.SortOrder AS BookSortOrder,
|
|
c.SortOrder AS ChapterSortOrder, s.SortOrder AS SceneSortOrder
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.Books b ON b.ProjectID = p.ProjectID AND b.IsArchived = 0
|
|
INNER JOIN dbo.Chapters c ON c.BookID = b.BookID AND c.IsArchived = 0
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
|
|
INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE p.ProjectID = @ProjectID
|
|
AND p.IsArchived = 0
|
|
AND (@BookID IS NULL OR b.BookID = @BookID)
|
|
AND rs.StatusName IN (N'Outlined', N'Needs Work', N'Revised')
|
|
AND (@IncludeBlockedScenes = 1 OR COALESCE(sw.IsBlocked, 0) = 0)
|
|
ORDER BY COALESCE(sw.Priority, 0) DESC, b.SortOrder, c.SortOrder, s.SortOrder, s.SceneID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingScheduleItem_DeleteByPlan
|
|
@WritingPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DELETE FROM dbo.WritingScheduleItems
|
|
WHERE WritingPlanID = @WritingPlanID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingPlanSummary_ListByUser
|
|
@UserID int,
|
|
@ProjectID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT wp.WritingPlanID, wp.UserID, wp.ProjectID, p.ProjectName, wp.BookID, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
wp.PlanName, wp.DeadlineDate, wp.IsActive
|
|
FROM dbo.WritingPlans wp
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = wp.ProjectID
|
|
LEFT JOIN dbo.Books b ON b.BookID = wp.BookID
|
|
WHERE wp.UserID = @UserID
|
|
AND (@ProjectID IS NULL OR wp.ProjectID = @ProjectID)
|
|
ORDER BY wp.IsActive DESC, wp.DeadlineDate, wp.PlanName, wp.WritingPlanID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingSchedulePreview_ListByPlan
|
|
@WritingPlanID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT wsi.WritingScheduleItemID, wsi.WritingPlanID, wsi.SceneID, wsi.ScheduledDate, wsi.TaskType,
|
|
b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterNumber, c.ChapterTitle, s.SceneNumber, s.SceneTitle,
|
|
sw.Priority, wsi.EstimatedMinutes, wsi.TargetWords, wsi.ScheduleStatus, wsi.Notes,
|
|
COALESCE(sw.IsBlocked, 0) AS IsBlocked
|
|
FROM dbo.WritingScheduleItems wsi
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = wsi.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE wsi.WritingPlanID = @WritingPlanID
|
|
ORDER BY wsi.ScheduledDate, wsi.WritingScheduleItemID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingScheduleDashboard_ListActive
|
|
@UserID int,
|
|
@ProjectID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT wsi.WritingScheduleItemID, wsi.WritingPlanID, wp.PlanName, wsi.SceneID, wsi.ScheduledDate, wsi.TaskType,
|
|
b.BookTitle, b.Subtitle AS BookSubtitle, c.ChapterNumber, c.ChapterTitle, c.SortOrder AS ChapterSortOrder,
|
|
s.SceneNumber, s.SceneTitle, s.SortOrder AS SceneSortOrder, sw.Priority, wsi.EstimatedMinutes,
|
|
wsi.TargetWords, wsi.ScheduleStatus, COALESCE(sw.IsBlocked, 0) AS IsBlocked
|
|
FROM dbo.WritingPlans wp
|
|
INNER JOIN dbo.WritingScheduleItems wsi ON wsi.WritingPlanID = wp.WritingPlanID
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = wsi.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE wp.UserID = @UserID
|
|
AND wp.IsActive = 1
|
|
AND (@ProjectID IS NULL OR wp.ProjectID = @ProjectID)
|
|
ORDER BY wsi.ScheduledDate, wsi.WritingScheduleItemID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingScheduleItem_UpdatePlannedStatusForUser
|
|
@WritingScheduleItemID int,
|
|
@UserID int,
|
|
@ScheduleStatus nvarchar(50),
|
|
@CompletedDateUtc datetime2 = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE wsi
|
|
SET ScheduleStatus = @ScheduleStatus,
|
|
CompletedDateUtc = @CompletedDateUtc,
|
|
ModifiedDateUtc = SYSUTCDATETIME()
|
|
FROM dbo.WritingScheduleItems wsi
|
|
INNER JOIN dbo.WritingPlans wp ON wp.WritingPlanID = wsi.WritingPlanID
|
|
WHERE wsi.WritingScheduleItemID = @WritingScheduleItemID
|
|
AND wp.UserID = @UserID
|
|
AND wp.IsActive = 1
|
|
AND wsi.ScheduleStatus = N'Planned';
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingPlanSummary_ListRequiringRebalance
|
|
@UserID int,
|
|
@Today date,
|
|
@ProjectID int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT wp.WritingPlanID, wp.UserID, wp.ProjectID, p.ProjectName, wp.BookID, b.BookTitle, b.Subtitle AS BookSubtitle,
|
|
wp.PlanName, wp.DeadlineDate, wp.IsActive
|
|
FROM dbo.WritingPlans wp
|
|
INNER JOIN dbo.Projects p ON p.ProjectID = wp.ProjectID
|
|
LEFT JOIN dbo.Books b ON b.BookID = wp.BookID
|
|
WHERE wp.UserID = @UserID
|
|
AND wp.IsActive = 1
|
|
AND (@ProjectID IS NULL OR wp.ProjectID = @ProjectID)
|
|
AND EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.WritingScheduleItems wsi
|
|
WHERE wsi.WritingPlanID = wp.WritingPlanID
|
|
AND wsi.ScheduleStatus = N'Planned'
|
|
AND wsi.ScheduledDate < @Today
|
|
)
|
|
ORDER BY wp.DeadlineDate, wp.PlanName, wp.WritingPlanID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.WritingScheduleItem_UpdatePlannedDate
|
|
@WritingPlanID int,
|
|
@WritingScheduleItemID int,
|
|
@ScheduledDate date
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.WritingScheduleItems
|
|
SET ScheduledDate = @ScheduledDate,
|
|
ModifiedDateUtc = SYSUTCDATETIME()
|
|
WHERE WritingPlanID = @WritingPlanID
|
|
AND WritingScheduleItemID = @WritingScheduleItemID
|
|
AND ScheduleStatus = N'Planned';
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectDashboard_Metrics
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
(SELECT COUNT(1) FROM dbo.Characters WHERE ProjectID = @ProjectID AND IsArchived = 0) AS CharacterCount,
|
|
(SELECT COUNT(1) FROM dbo.Locations WHERE ProjectID = @ProjectID AND IsArchived = 0) AS LocationCount,
|
|
(
|
|
SELECT COUNT(1)
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
|
|
INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
|
|
WHERE pl.ProjectID = @ProjectID
|
|
AND pl.IsArchived = 0
|
|
AND pt.IsArchived = 0
|
|
AND ts.IsOpenStatus = 1
|
|
) AS ActivePlotThreadCount,
|
|
(
|
|
SELECT COUNT(1)
|
|
FROM dbo.ContinuityWarnings
|
|
WHERE ProjectID = @ProjectID
|
|
AND IsDismissed = 0
|
|
AND IsIntentional = 0
|
|
) AS ContinuityWarningCount;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectDashboard_AttentionThreads
|
|
@ProjectID int,
|
|
@Count int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (@Count) pt.PlotThreadID, pt.ThreadTitle, pt.PlotLineID, pl.PlotLineName,
|
|
pt.Importance, ts.StatusName AS ThreadStatusName, ts.IsOpenStatus, ts.IsResolvedStatus
|
|
FROM dbo.PlotThreads pt
|
|
INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
|
|
INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
|
|
WHERE pl.ProjectID = @ProjectID
|
|
AND pl.IsArchived = 0
|
|
AND pt.IsArchived = 0
|
|
AND (ts.StatusName = N'Dormant' OR (ts.IsOpenStatus = 1 AND pt.Importance >= 8))
|
|
ORDER BY CASE WHEN ts.StatusName = N'Dormant' THEN 0 ELSE 1 END,
|
|
pt.Importance DESC, pt.UpdatedDate DESC, pt.PlotThreadID DESC;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ProjectActivity_ListRecent
|
|
@ProjectID int,
|
|
@Count int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (@Count) 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.Book_ListByProjects
|
|
@ProjectIDs nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
;WITH ProjectIDs AS
|
|
(
|
|
SELECT DISTINCT TRY_CONVERT(int, value) AS ProjectID
|
|
FROM STRING_SPLIT(COALESCE(@ProjectIDs, N''), N',')
|
|
WHERE TRY_CONVERT(int, value) IS NOT NULL
|
|
)
|
|
SELECT b.BookID, b.ProjectID, b.BookTitle, b.Subtitle, b.Tagline, b.ShortDescription, b.Status, b.BookNumber,
|
|
b.Description, b.TargetWordCount, b.WritingStartDate, b.TargetCompletionDate, b.PublicationDate,
|
|
b.CoverThumbnailPath, b.CoverStandardPath, ISNULL(wordCounts.CurrentWordCount, 0) AS CurrentWordCount,
|
|
ISNULL(counts.ChapterCount, 0) AS ChapterCount, ISNULL(counts.SceneCount, 0) AS SceneCount,
|
|
b.SortOrder, b.CreatedDate, b.UpdatedDate, b.IsArchived, b.ArchivedDate, b.ArchivedReason
|
|
FROM dbo.Books b
|
|
INNER JOIN ProjectIDs ids ON ids.ProjectID = b.ProjectID
|
|
OUTER APPLY
|
|
(
|
|
SELECT COUNT(DISTINCT c.ChapterID) AS ChapterCount, COUNT(s.SceneID) AS SceneCount
|
|
FROM dbo.Chapters c
|
|
LEFT JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
|
|
WHERE c.BookID = b.BookID AND c.IsArchived = 0
|
|
) counts
|
|
OUTER APPLY
|
|
(
|
|
SELECT SUM(ISNULL(sw.ActualWordCount, 0)) AS CurrentWordCount
|
|
FROM dbo.Chapters c
|
|
INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
|
|
LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
|
|
WHERE c.BookID = b.BookID AND c.IsArchived = 0
|
|
) wordCounts
|
|
WHERE b.IsArchived = 0
|
|
ORDER BY b.ProjectID, b.SortOrder, b.BookNumber, b.BookTitle;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Book_GetCoverPaths
|
|
@BookID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT BookID, ProjectID, CoverThumbnailPath, CoverStandardPath
|
|
FROM dbo.Books
|
|
WHERE BookID = @BookID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ThreadEventTarget_ListByEvents
|
|
@ThreadEventIDs nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
;WITH ThreadEventIDs AS
|
|
(
|
|
SELECT DISTINCT TRY_CONVERT(int, value) AS ThreadEventID
|
|
FROM STRING_SPLIT(COALESCE(@ThreadEventIDs, N''), N',')
|
|
WHERE TRY_CONVERT(int, value) IS NOT NULL
|
|
)
|
|
SELECT petpl.PlotEventID, petpl.PlotLineID
|
|
FROM dbo.PlotEventTargetPlotLines petpl
|
|
INNER JOIN ThreadEventIDs ids ON ids.ThreadEventID = petpl.PlotEventID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.ThreadEventTarget_SaveForEvent
|
|
@ThreadEventID int,
|
|
@PlotLineIDs nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DELETE FROM dbo.PlotEventTargetPlotLines
|
|
WHERE PlotEventID = @ThreadEventID;
|
|
|
|
;WITH PlotLineIDs AS
|
|
(
|
|
SELECT DISTINCT TRY_CONVERT(int, value) AS PlotLineID
|
|
FROM STRING_SPLIT(COALESCE(@PlotLineIDs, N''), N',')
|
|
WHERE TRY_CONVERT(int, value) IS NOT NULL
|
|
AND TRY_CONVERT(int, value) > 0
|
|
)
|
|
INSERT dbo.PlotEventTargetPlotLines (PlotEventID, PlotLineID)
|
|
SELECT @ThreadEventID, PlotLineID
|
|
FROM PlotLineIDs;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodyCharacter_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT acec.AssetCustodyEventID, ace.StoryAssetID, ace.SceneID, acec.CharacterID, c.CharacterName,
|
|
acec.CharacterNameText, cr.RoleName, ace.UpdatedDate
|
|
FROM dbo.AssetCustodyEventCharacters acec
|
|
INNER JOIN dbo.AssetCustodyEvents ace ON ace.AssetCustodyEventID = acec.AssetCustodyEventID
|
|
INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ace.StoryAssetID
|
|
LEFT JOIN dbo.Characters c ON c.CharacterID = acec.CharacterID
|
|
LEFT JOIN dbo.CustodyRoles cr ON cr.CustodyRoleID = acec.CustodyRoleID
|
|
WHERE sa.ProjectID = @ProjectID
|
|
AND sa.IsArchived = 0
|
|
AND acec.CharacterID IS NOT NULL
|
|
AND NULLIF(LTRIM(RTRIM(c.CharacterName)), N'') IS NOT NULL;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.AssetCustodySummary_ListByEvents
|
|
@AssetCustodyEventIDs nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
;WITH AssetCustodyEventIDs AS
|
|
(
|
|
SELECT DISTINCT TRY_CONVERT(int, value) AS AssetCustodyEventID
|
|
FROM STRING_SPLIT(COALESCE(@AssetCustodyEventIDs, N''), N',')
|
|
WHERE TRY_CONVERT(int, value) IS NOT NULL
|
|
)
|
|
SELECT acec.AssetCustodyEventID, c.CharacterName, cr.RoleName
|
|
FROM dbo.AssetCustodyEventCharacters acec
|
|
INNER JOIN AssetCustodyEventIDs ids ON ids.AssetCustodyEventID = acec.AssetCustodyEventID
|
|
INNER JOIN dbo.Characters c ON c.CharacterID = acec.CharacterID
|
|
LEFT JOIN dbo.CustodyRoles cr ON cr.CustodyRoleID = acec.CustodyRoleID
|
|
WHERE acec.CharacterID IS NOT NULL
|
|
AND NULLIF(LTRIM(RTRIM(c.CharacterName)), N'') IS NOT NULL;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterSexValue_ListForOwner
|
|
@OwnerUserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterSexValueID, OwnerUserID, SexName, IsSystem, SortOrder, IsActive
|
|
FROM dbo.CharacterSexValues
|
|
WHERE IsActive = 1
|
|
AND (IsSystem = 1 OR OwnerUserID = @OwnerUserID)
|
|
ORDER BY IsSystem DESC, SortOrder, SexName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterSexValue_Get
|
|
@CharacterSexValueID int,
|
|
@OwnerUserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterSexValueID, OwnerUserID, SexName, IsSystem, SortOrder, IsActive
|
|
FROM dbo.CharacterSexValues
|
|
WHERE CharacterSexValueID = @CharacterSexValueID
|
|
AND IsActive = 1
|
|
AND (IsSystem = 1 OR OwnerUserID = @OwnerUserID);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterImage_ListByCharacter
|
|
@CharacterID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterImageID, CharacterID, ProjectID, ImagePath, ThumbnailPath, Caption, SortOrder, UploadedDateUTC, IsDeleted
|
|
FROM dbo.CharacterImages
|
|
WHERE CharacterID = @CharacterID
|
|
AND IsDeleted = 0
|
|
ORDER BY SortOrder, UploadedDateUTC, CharacterImageID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterImage_Get
|
|
@CharacterImageID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CharacterImageID, CharacterID, ProjectID, ImagePath, ThumbnailPath, Caption, SortOrder, UploadedDateUTC, IsDeleted
|
|
FROM dbo.CharacterImages
|
|
WHERE CharacterImageID = @CharacterImageID
|
|
AND IsDeleted = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterImage_Create
|
|
@CharacterID int,
|
|
@ProjectID int,
|
|
@ImagePath nvarchar(500),
|
|
@ThumbnailPath nvarchar(500) = NULL,
|
|
@Caption nvarchar(500) = NULL,
|
|
@InputSortOrder int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @SortOrder int = COALESCE(@InputSortOrder, (SELECT ISNULL(MAX(SortOrder), 0) + 1 FROM dbo.CharacterImages WHERE CharacterID = @CharacterID));
|
|
|
|
INSERT dbo.CharacterImages (CharacterID, ProjectID, ImagePath, ThumbnailPath, Caption, SortOrder)
|
|
VALUES (@CharacterID, @ProjectID, @ImagePath, @ThumbnailPath, @Caption, @SortOrder);
|
|
|
|
SELECT CAST(SCOPE_IDENTITY() AS int);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.CharacterImage_Delete
|
|
@CharacterImageID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.CharacterImages
|
|
SET IsDeleted = 1,
|
|
DeletedDateUTC = SYSUTCDATETIME()
|
|
WHERE CharacterImageID = @CharacterImageID;
|
|
END;
|
|
GO
|