100 lines
3.1 KiB
Transact-SQL
100 lines
3.1 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_BookCountForOwner
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(DISTINCT b.BookID)
|
|
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 pua.UserID = @UserID
|
|
AND pua.AccessRole = N'Owner'
|
|
AND pua.IsActive = 1
|
|
AND b.IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_CollaboratorCountForOwner
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(DISTINCT collaborator.UserID)
|
|
FROM dbo.ProjectUserAccess ownerAccess
|
|
INNER JOIN dbo.ProjectUserAccess collaborator ON collaborator.ProjectID = ownerAccess.ProjectID
|
|
WHERE ownerAccess.UserID = @UserID
|
|
AND ownerAccess.AccessRole = N'Owner'
|
|
AND ownerAccess.IsActive = 1
|
|
AND collaborator.AccessRole = N'Collaborator'
|
|
AND collaborator.IsActive = 1;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_GetSummaryForOwner
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
WITH OwnedProjects AS
|
|
(
|
|
SELECT DISTINCT p.ProjectID
|
|
FROM dbo.Projects p
|
|
INNER JOIN dbo.ProjectUserAccess pua ON pua.ProjectID = p.ProjectID
|
|
WHERE pua.UserID = @UserID
|
|
AND pua.AccessRole = N'Owner'
|
|
AND pua.IsActive = 1
|
|
),
|
|
OwnedBooks AS
|
|
(
|
|
SELECT b.BookID, b.ProjectID
|
|
FROM dbo.Books b
|
|
INNER JOIN OwnedProjects p ON p.ProjectID = b.ProjectID
|
|
WHERE b.IsArchived = 0
|
|
),
|
|
BookUsage AS
|
|
(
|
|
SELECT b.BookID,
|
|
COUNT(DISTINCT c.ChapterID) AS ChapterCount,
|
|
COUNT(DISTINCT s.SceneID) AS SceneCount
|
|
FROM OwnedBooks b
|
|
LEFT JOIN dbo.Chapters c ON c.BookID = b.BookID
|
|
AND c.IsArchived = 0
|
|
LEFT JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID
|
|
AND s.IsArchived = 0
|
|
GROUP BY b.BookID
|
|
),
|
|
ProjectCharacterUsage AS
|
|
(
|
|
SELECT p.ProjectID,
|
|
COUNT(DISTINCT c.CharacterID) AS CharacterCount
|
|
FROM OwnedProjects p
|
|
LEFT JOIN dbo.Characters c ON c.ProjectID = p.ProjectID
|
|
AND c.IsArchived = 0
|
|
GROUP BY p.ProjectID
|
|
)
|
|
SELECT
|
|
(SELECT COUNT(1) FROM OwnedBooks) AS BooksUsed,
|
|
(
|
|
SELECT COUNT(DISTINCT collaborator.UserID)
|
|
FROM dbo.ProjectUserAccess ownerAccess
|
|
INNER JOIN dbo.ProjectUserAccess collaborator ON collaborator.ProjectID = ownerAccess.ProjectID
|
|
WHERE ownerAccess.UserID = @UserID
|
|
AND ownerAccess.AccessRole = N'Owner'
|
|
AND ownerAccess.IsActive = 1
|
|
AND collaborator.AccessRole = N'Collaborator'
|
|
AND collaborator.IsActive = 1
|
|
) AS CollaboratorsUsed,
|
|
COALESCE((SELECT MAX(ChapterCount) FROM BookUsage), 0) AS HighestChaptersInBook,
|
|
COALESCE((SELECT MAX(SceneCount) FROM BookUsage), 0) AS HighestScenesInBook,
|
|
COALESCE((SELECT MAX(CharacterCount) FROM ProjectCharacterUsage), 0) AS HighestCharactersInProject;
|
|
END;
|
|
GO
|