PlotDirector/PlotLine/Sql/109_DropdownListOrderingConsistency.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #ProjectLocationPaths
    (
        LocationID int NOT NULL PRIMARY KEY,
        LocationName nvarchar(200) NOT NULL,
        LocationPath nvarchar(max) NOT NULL
    );

    ;WITH ProjectLocationTree AS
    (
        SELECT LocationID, LocationName, CAST(LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations
        WHERE ProjectID = @ProjectID AND ParentLocationID IS NULL AND IsArchived = 0

        UNION ALL

        SELECT child.LocationID, child.LocationName, CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations child
        INNER JOIN ProjectLocationTree parent ON parent.LocationID = child.ParentLocationID
        WHERE child.ProjectID = @ProjectID AND child.IsArchived = 0
    )
    INSERT #ProjectLocationPaths (LocationID, LocationName, LocationPath)
    SELECT LocationID, LocationName, LocationPath
    FROM ProjectLocationTree;

    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, sa.ShowInQuickAddBar, sa.ImagePath, sa.ThumbnailPath, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    LEFT JOIN #ProjectLocationPaths location ON location.LocationID = sa.CurrentLocationID
    WHERE sa.ProjectID = @ProjectID
        AND sa.IsArchived = 0
    ORDER BY sa.AssetName, sa.StoryAssetID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.AssetTimeline_GetByProject
    @ProjectID int,
    @BookID int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID, sa.IsResolved,
        sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (sa.Importance >= 5 OR sa.IsResolved = 0)
    ORDER BY sa.AssetName, sa.StoryAssetID;

    SELECT ae.AssetEventID, ae.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName,
        ae.SceneID, ae.AssetEventTypeID, aet.TypeName AS AssetEventTypeName, aet.MarkerText,
        ae.FromStateID, fs.StateName AS FromStateName, ae.ToStateID, ts.StateName AS ToStateName,
        ae.EventTitle, ae.EventDescription, b.BookTitle, b.Subtitle AS BookSubtitle,
        c.ChapterNumber, s.SceneNumber, s.SceneTitle, ae.CreatedDate, ae.UpdatedDate
    FROM dbo.AssetEvents ae
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    INNER JOIN dbo.AssetEventTypes aet ON aet.AssetEventTypeID = ae.AssetEventTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = ae.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    LEFT JOIN dbo.AssetStates fs ON fs.AssetStateID = ae.FromStateID
    LEFT JOIN dbo.AssetStates ts ON ts.AssetStateID = ae.ToStateID
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
    ORDER BY b.SortOrder, b.BookNumber, c.SortOrder, c.ChapterNumber, s.SortOrder, s.SceneNumber, sa.AssetName, ae.AssetEventID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Location_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT lp.LocationID, lp.ProjectID, lp.ParentLocationID, lp.LocationName, lp.LocationTypeID, lt.TypeName AS LocationTypeName,
        lp.Description, lp.ShowInQuickAddBar, lp.ExcludeFromCompanionDetection, lp.DetectionPriority,
        lp.LocationPath, lp.Depth, l.CreatedDate, l.UpdatedDate, l.IsArchived
    FROM dbo.LocationPaths lp
    INNER JOIN dbo.Locations l ON l.LocationID = lp.LocationID
    LEFT JOIN dbo.LocationTypes lt ON lt.LocationTypeID = lp.LocationTypeID
    WHERE lp.ProjectID = @ProjectID
    ORDER BY lp.LocationPath, lp.LocationID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.PlotThread_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT pt.PlotThreadID, pt.PlotLineID, pl.ProjectID, pl.PlotLineName, pt.ThreadTitle,
        pt.ThreadTypeID, tt.TypeName AS ThreadTypeName, pt.ThreadStatusID, ts.StatusName AS ThreadStatusName,
        pt.Importance, pt.Summary, pt.IntroducedSceneID, pt.PlannedResolutionSceneID, pt.ActualResolutionSceneID,
        pt.CreatedDate, pt.UpdatedDate, pt.IsArchived
    FROM dbo.PlotThreads pt
    INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
    INNER JOIN dbo.ThreadTypes tt ON tt.ThreadTypeID = pt.ThreadTypeID
    INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
    WHERE pl.ProjectID = @ProjectID AND pl.IsArchived = 0 AND pt.IsArchived = 0
    ORDER BY pt.ThreadTitle, pl.PlotLineName, pt.PlotThreadID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.PlotThread_ListByPlotLine
    @PlotLineID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT pt.PlotThreadID, pt.PlotLineID, pl.ProjectID, pl.PlotLineName, pt.ThreadTitle,
        pt.ThreadTypeID, tt.TypeName AS ThreadTypeName, pt.ThreadStatusID, ts.StatusName AS ThreadStatusName,
        pt.Importance, pt.Summary, pt.IntroducedSceneID, pt.PlannedResolutionSceneID, pt.ActualResolutionSceneID,
        pt.CreatedDate, pt.UpdatedDate, pt.IsArchived
    FROM dbo.PlotThreads pt
    INNER JOIN dbo.PlotLines pl ON pl.PlotLineID = pt.PlotLineID
    INNER JOIN dbo.ThreadTypes tt ON tt.ThreadTypeID = pt.ThreadTypeID
    INNER JOIN dbo.ThreadStatuses ts ON ts.ThreadStatusID = pt.ThreadStatusID
    WHERE pt.PlotLineID = @PlotLineID AND pt.IsArchived = 0
    ORDER BY pt.ThreadTitle, pt.PlotThreadID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.WordCompanion_Book_ListByProject
    @ProjectID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.ProjectUserAccess pua
        INNER JOIN dbo.Projects p ON p.ProjectID = pua.ProjectID
        WHERE pua.ProjectID = @ProjectID
            AND pua.UserID = @UserID
            AND pua.IsActive = 1
            AND p.IsArchived = 0
    )
        RETURN;

    SELECT b.BookID AS BookId,
        b.BookTitle AS Title,
        b.Subtitle,
        b.SortOrder,
        b.UsesExplicitScenes
    FROM dbo.Books b
    WHERE b.ProjectID = @ProjectID
        AND b.IsArchived = 0
    ORDER BY b.SortOrder, b.BookNumber, b.BookTitle, b.Subtitle, b.BookID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.WordCompanion_Book_Structure
    @BookID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.Books b
        INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
        INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
        WHERE b.BookID = @BookID
            AND b.IsArchived = 0
            AND p.IsArchived = 0
            AND pua.UserID = @UserID
            AND pua.IsActive = 1
    )
        RETURN;

    SELECT @BookID AS BookId;

    SELECT c.ChapterID AS ChapterId, c.ChapterTitle AS Title, c.SortOrder
    FROM dbo.Chapters c
    WHERE c.BookID = @BookID
        AND c.IsArchived = 0
    ORDER BY c.SortOrder, c.ChapterNumber, c.ChapterTitle, c.ChapterID;

    SELECT s.ChapterID AS ChapterId, s.SceneID AS SceneId, s.SceneTitle AS Title, s.SortOrder,
        rs.StatusName AS RevisionStatus,
        sw.EstimatedWordCount AS EstimatedWords,
        sw.ActualWordCount AS ActualWords
    FROM dbo.Scenes s
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = s.RevisionStatusID
    LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
    WHERE c.BookID = @BookID
        AND c.IsArchived = 0
        AND s.IsArchived = 0
    ORDER BY c.SortOrder, c.ChapterNumber, s.SortOrder, s.SceneNumber, s.SceneTitle, s.SceneID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.WordCompanion_Chapter_List
    @BookID int,
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.Books b
        INNER JOIN dbo.Projects p ON p.ProjectID = b.ProjectID
        INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
        WHERE b.BookID = @BookID
            AND b.IsArchived = 0
            AND p.IsArchived = 0
            AND pua.UserID = @UserID
            AND pua.IsActive = 1
    )
        RETURN;

    SELECT c.ChapterID AS ChapterId,
        c.ChapterTitle AS Title,
        c.SortOrder
    FROM dbo.Chapters c
    WHERE c.BookID = @BookID
        AND c.IsArchived = 0
    ORDER BY c.SortOrder, c.ChapterNumber, c.ChapterTitle, c.ChapterID;
END;
GO