83 lines
1.9 KiB
Transact-SQL
83 lines
1.9 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 p.IsArchived = 0
|
|
AND b.IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_ChapterCountForBook
|
|
@BookID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(1)
|
|
FROM dbo.Chapters
|
|
WHERE BookID = @BookID
|
|
AND IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_SceneCountForBook
|
|
@BookID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(1)
|
|
FROM dbo.Scenes s
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
WHERE c.BookID = @BookID
|
|
AND c.IsArchived = 0
|
|
AND s.IsArchived = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionUsage_CharacterCountForProject
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT COUNT(1)
|
|
FROM dbo.Characters
|
|
WHERE ProjectID = @ProjectID
|
|
AND 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.Projects p ON p.ProjectID = ownerAccess.ProjectID
|
|
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
|
|
AND p.IsArchived = 0;
|
|
END;
|
|
GO
|