113 lines
4.5 KiB
Transact-SQL
113 lines
4.5 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.SubscriptionLevel') AND name = N'MonthlyPricePence')
|
|
ALTER TABLE dbo.SubscriptionLevel ADD MonthlyPricePence int NULL;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.SubscriptionLevel') AND name = N'AnnualPricePence')
|
|
ALTER TABLE dbo.SubscriptionLevel ADD AnnualPricePence int NULL;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.SubscriptionLevel') AND name = N'CurrencyCode')
|
|
ALTER TABLE dbo.SubscriptionLevel ADD CurrencyCode nvarchar(3) NOT NULL CONSTRAINT DF_SubscriptionLevel_CurrencyCode DEFAULT N'GBP';
|
|
GO
|
|
|
|
UPDATE dbo.SubscriptionLevel
|
|
SET MonthlyPricePence = CASE Name
|
|
WHEN N'DraftDesk' THEN NULL
|
|
WHEN N'WritingRoom' THEN 499
|
|
WHEN N'StoryForge' THEN 799
|
|
WHEN N'AuthorStudio' THEN 1299
|
|
ELSE MonthlyPricePence
|
|
END,
|
|
AnnualPricePence = CASE Name
|
|
WHEN N'DraftDesk' THEN NULL
|
|
WHEN N'WritingRoom' THEN 4999
|
|
WHEN N'StoryForge' THEN 7999
|
|
WHEN N'AuthorStudio' THEN 12999
|
|
ELSE AnnualPricePence
|
|
END,
|
|
CurrencyCode = N'GBP',
|
|
ModifiedDateUTC = SYSUTCDATETIME()
|
|
WHERE Name IN (N'DraftDesk', N'WritingRoom', N'StoryForge', N'AuthorStudio');
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionLevel_ListActive
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT SubscriptionLevelID, Name, DisplayName, Description, MaxBooks, MaxChaptersPerBook,
|
|
MaxScenesPerBook, MaxCharactersPerProject, MaxStorageMB, MaxCollaborators, IsActive,
|
|
SortOrder, CreatedDateUTC, ModifiedDateUTC, StripeMonthlyPriceId, StripeAnnualPriceId,
|
|
RequiresStripeSubscription, MonthlyPricePence, AnnualPricePence, CurrencyCode
|
|
FROM dbo.SubscriptionLevel
|
|
WHERE IsActive = 1
|
|
ORDER BY SortOrder, DisplayName;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionLevel_Get
|
|
@SubscriptionLevelID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT SubscriptionLevelID, Name, DisplayName, Description, MaxBooks, MaxChaptersPerBook,
|
|
MaxScenesPerBook, MaxCharactersPerProject, MaxStorageMB, MaxCollaborators, IsActive,
|
|
SortOrder, CreatedDateUTC, ModifiedDateUTC, StripeMonthlyPriceId, StripeAnnualPriceId,
|
|
RequiresStripeSubscription, MonthlyPricePence, AnnualPricePence, CurrencyCode
|
|
FROM dbo.SubscriptionLevel
|
|
WHERE SubscriptionLevelID = @SubscriptionLevelID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.SubscriptionLevel_GetByStripePrice
|
|
@StripePriceId nvarchar(100)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT SubscriptionLevelID, Name, DisplayName, Description, MaxBooks, MaxChaptersPerBook,
|
|
MaxScenesPerBook, MaxCharactersPerProject, MaxStorageMB, MaxCollaborators, IsActive,
|
|
SortOrder, CreatedDateUTC, ModifiedDateUTC, StripeMonthlyPriceId, StripeAnnualPriceId,
|
|
RequiresStripeSubscription, MonthlyPricePence, AnnualPricePence, CurrencyCode,
|
|
CASE
|
|
WHEN StripeMonthlyPriceId = @StripePriceId THEN N'Monthly'
|
|
WHEN StripeAnnualPriceId = @StripePriceId THEN N'Annual'
|
|
ELSE NULL
|
|
END AS BillingInterval
|
|
FROM dbo.SubscriptionLevel
|
|
WHERE IsActive = 1
|
|
AND RequiresStripeSubscription = 1
|
|
AND (StripeMonthlyPriceId = @StripePriceId OR StripeAnnualPriceId = @StripePriceId);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserSubscription_GetCurrent
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT us.UserSubscriptionID, us.UserID, us.SubscriptionLevelID, us.StartDateUTC, us.EndDateUTC,
|
|
us.IsActive, us.IsTrial, us.TrialExpiryDateUTC, us.CreatedDateUTC, us.ModifiedDateUTC,
|
|
us.StripeCustomerId, us.StripeSubscriptionId, us.StripePriceId, us.BillingInterval, us.StripeStatus,
|
|
us.CurrentPeriodStartUTC, us.CurrentPeriodEndUTC, us.CancelAtPeriodEnd, us.CancelledDateUTC,
|
|
us.LastPaymentFailureDateUTC, us.LastStripeEventId,
|
|
sl.Name, sl.DisplayName, sl.Description, sl.MaxBooks, sl.MaxChaptersPerBook,
|
|
sl.MaxScenesPerBook, sl.MaxCharactersPerProject, sl.MaxStorageMB, sl.MaxCollaborators,
|
|
sl.IsActive AS SubscriptionLevelIsActive, sl.SortOrder, sl.StripeMonthlyPriceId,
|
|
sl.StripeAnnualPriceId, sl.RequiresStripeSubscription, sl.MonthlyPricePence,
|
|
sl.AnnualPricePence, sl.CurrencyCode
|
|
FROM dbo.UserSubscription us
|
|
INNER JOIN dbo.SubscriptionLevel sl ON sl.SubscriptionLevelID = us.SubscriptionLevelID
|
|
WHERE us.UserID = @UserID
|
|
AND us.IsActive = 1
|
|
AND (us.EndDateUTC IS NULL OR us.EndDateUTC > SYSUTCDATETIME());
|
|
END;
|
|
GO
|