136 lines
3.7 KiB
Transact-SQL
136 lines
3.7 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Books', N'CoverThumbnailPath') IS NULL
|
|
ALTER TABLE dbo.Books ADD CoverThumbnailPath nvarchar(1000) NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.Books', N'CoverStandardPath') IS NULL
|
|
ALTER TABLE dbo.Books ADD CoverStandardPath nvarchar(1000) NULL;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Book_ListByProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
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
|
|
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.ProjectID = @ProjectID AND b.IsArchived = 0
|
|
ORDER BY b.SortOrder, b.BookNumber, b.BookTitle;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Book_Get
|
|
@BookID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
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
|
|
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.BookID = @BookID AND b.IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Book_UpdateCover
|
|
@BookID int,
|
|
@CoverThumbnailPath nvarchar(1000) = NULL,
|
|
@CoverStandardPath nvarchar(1000) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.Books
|
|
SET CoverThumbnailPath = @CoverThumbnailPath,
|
|
CoverStandardPath = @CoverStandardPath,
|
|
UpdatedDate = SYSUTCDATETIME()
|
|
WHERE BookID = @BookID;
|
|
END;
|
|
GO
|