Ticket #184: 0_Subscription_update.sql

USE BAZA_REKLAM
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/****** Object:  Table [dbo].[SubscriptionType]    Script Date: 06/17/2009 15:39:34 ******/
CREATE TABLE [dbo].[SubscriptionType](
        [Id] [int] NOT NULL,
        [Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1250_CI_AS NOT NULL,
        [PricePln] [decimal](9, 2) NULL,
        [PriceEur] [decimal](9, 2) NULL,
        [TitleId] [int] NULL,
        [AmtMounth] [int] NULL,
 CONSTRAINT [PK_SubscriptionType] PRIMARY KEY CLUSTERED 
(
        [Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO [SubscriptionType] VALUES(1, 'Abonament 3M', 550, 129, 41, 3);
INSERT INTO [SubscriptionType] VALUES(2, 'Abonament 6M', 505, 119, 41, 6);
INSERT INTO [SubscriptionType] VALUES(3, 'Abonament 12M', 420, 99, 41, 12);

/****** Object:  Table [dbo].[Subscription]    Script Date: 07/21/2009 15:00:06 ******/
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Subscription](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Guid] [uniqueidentifier] NOT NULL,
        [CustomerId] [int] NOT NULL,
        [SubscriptionTypeId] [int] NOT NULL,
        [BasePrice] [decimal](9, 2) NULL,
        [Discount] [decimal](9, 2) NULL,
        [TotalPrice] [decimal](9, 2) NULL,
        [Currency] [varchar](3) COLLATE SQL_Latin1_General_CP1250_CI_AS NULL,
        [OrderId] [int] NULL,
        [CreatedOn] [datetime] NULL,
        [CreatedBy] [int] NULL,
        [UpdatedOn] [datetime] NULL,
        [UpdatedBy] [int] NULL,
        [SubscriptionItems] [int] NULL,
        [StartDate] [datetime] NULL,
        [Vat] [decimal](9, 2) NULL,
        [InvoiceId] [int] NULL
 CONSTRAINT [PK_Subscription] PRIMARY KEY CLUSTERED 
(
        [Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[SubscriptionDetail]    Script Date: 07/21/2009 15:02:50 ******/
CREATE TABLE [dbo].[SubscriptionDetail](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [SubscriptionId] [int] NOT NULL,
        [Price] [decimal](9, 2) NULL,
        [PricePLN] [decimal](9, 2) NULL,
        [Year] [int] NULL,
        [Month] [int] NULL,
        [InvoiceId] [int] NULL,
 CONSTRAINT [PK_SubscriptionDetails] PRIMARY KEY CLUSTERED 
(
        [Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO


/****** Object:  StoredProcedure [dbo].[sp_GetProductsWithOrder]    Script Date: 06/17/2009 15:38:52 ******/
-- =============================================
-- Create date: 2009-11-23
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetProductsWithOrder]
        @clientId int
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here

DECLARE @ProductsWithOrder TABLE
(
        Id int,
        TitleId int,
        Type varchar(20),
        ProductType int,
        ShortName varchar(20),
        StartDate datetime,
        Price decimal(9,2),
        TotalPrice decimal(9,2),
        Vat decimal(9,2),
        Currency varchar(3),
        IsActivated bit,
        OrderId int,
        HasInvoice bit,
        DetailCount int,
        Discount decimal(9,2)
)

INSERT INTO @ProductsWithOrder
SELECT R.ReklamaID, T.Id, 'Reklama', 2, R.[ID REKLAMY], 
E.Data1Emisji, R.NETTO, R.NETTO * (1+R.VAT) AS Brutto, R.VAT, 
Currency = CASE 
                        WHEN Brutto_Euro_Miano IS NULL OR Brutto_Euro_Miano='' THEN 'PLN'
                        ELSE R.Brutto_Euro_Miano
                   END, 
R.[ZATWIERDZONO DO DRUKU],
R.idZamowienia,
HasInvoices = 
        CASE 
                WHEN I.invoiceCount IS NULL OR I.invoiceCount < 1 THEN 0
                ELSE 1
        END,
I.InvoiceCount,
R.RABAT
FROM REKLAMA AS R 
LEFT OUTER JOIN (SELECT ReklamaId, MIN(DataEmisji) AS Data1Emisji FROM EmisjeReklamy GROUP BY ReklamaId) AS E ON E.ReklamaId = R.ReklamaID
LEFT OUTER JOIN (SELECT ReklamaId, InvoiceCount = count(*) FROM [uka¿e siê w nr] WHERE zafakturowana=1 GROUP BY Reklamaid) I ON I.ReklamaId=R.reklamaid
INNER JOIN dbo.Zamowienia Z ON Z.idZamowienia = R.idZamowienia
INNER JOIN dbo.[Lista Tytu³ów] T ON T.Symb = R.[Tytu³]
WHERE 
Z.idKlienta = @clientId AND R.idZamowienia IS NOT NULL
UNION
SELECT A.Id, AT.TitleId, 'Abonament', 1, 'Abo-' + CAST(A.Id AS varchar),
A.StartDate, A.BasePrice, A.TotalPrice, A.Vat, A.Currency, 0, A.OrderId, 
HasInvoices = 
        CASE 
                WHEN SD.InvoiceCount IS NULL OR SD.InvoiceCount < 1 THEN 0
                ELSE 1
        END,
A.SubscriptionItems,
A.Discount
FROM Subscription AS A
LEFT OUTER JOIN (SELECT SubscriptionId, InvoiceCount = COUNT(InvoiceId) FROM SubscriptionDetail WHERE InvoiceId IS NOT NULL GROUP BY SubscriptionId) SD ON SD.SubscriptionId=A.ID
INNER JOIN SubscriptionType AS AT ON AT.Id = A.SubscriptionTypeId
WHERE
A.CustomerId = @clientId AND A.OrderId IS NOT NULL

SELECT * FROM @ProductsWithOrder

END
GO


/****** Object:  StoredProcedure [dbo].[sp_GetProductsWithoutOrder]    Script Date: 07/21/2009 14:55:02 ******/
-- =============================================
-- Create date: 2009-11-23
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetProductsWithoutOrder]
        @customerId int
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here

DECLARE @ProductsWithoutOrder TABLE
(
        Id int,
        TitleId int,
        Type varchar(20),
        ProductType int,
        ShortName varchar(20),
        StartDate datetime,
        Price decimal(9,2),
        TotalPrice decimal(9,2),
        Vat decimal(9,2),
        Currency varchar(3),
        IsActivated bit,
        HasInvoice bit,
        DetailCount int,
        Discount decimal(9,2)
)

INSERT INTO @ProductsWithoutOrder
SELECT     R.ReklamaID, T.Id, 'Reklama', 2, R.[ID REKLAMY], E.Data1Emisji, R.NETTO, R.NETTO * (1+R.VAT) AS Brutto, R.VAT, 
Currency = CASE 
                        WHEN Brutto_Euro_Miano IS NULL OR Brutto_Euro_Miano='' THEN 'PLN'
                        ELSE R.Brutto_Euro_Miano
                   END, 
R.[ZATWIERDZONO DO DRUKU],
HasInvoices = 
        CASE 
                WHEN I.invoiceCount IS NULL OR I.invoiceCount < 1 THEN 0
                ELSE 1
        END,
I.InvoiceCount,
R.RABAT
FROM REKLAMA AS R 
LEFT OUTER JOIN (SELECT     ReklamaId, MIN(DataEmisji) AS Data1Emisji FROM EmisjeReklamy GROUP BY ReklamaId) AS E ON E.ReklamaId = R.ReklamaID
LEFT OUTER JOIN (SELECT ReklamaId, InvoiceCount = count(*) FROM [uka¿e siê w nr] WHERE zafakturowana=1 GROUP BY Reklamaid) I ON I.ReklamaId=R.reklamaid
INNER JOIN dbo.[Lista Tytu³ów] T ON T.Symb=R.[Tytu³]
WHERE 
(R.CustomerID = @customerId) 
AND (R.idZamowienia IS NULL) 
AND (R.[FAKTURA WYSTAWIONO] = 0) 
AND 
(
        (YEAR(R.[DATA ZAMÓWIENIA]) = 2008 AND R.[ZATWIERDZONO DO DRUKU] = 1)
OR 
        (YEAR(R.[DATA ZAMÓWIENIA]) >= 2009)
)
UNION
SELECT A.Id, AT.TitleId, 'Abonament', 1, 'Abo-' + CAST(A.Id AS varchar),
A.StartDate, A.BasePrice, A.TotalPrice, A.Vat, A.Currency, 0,
HasInvoices = 
        CASE 
                WHEN SD.InvoiceCount IS NULL OR SD.InvoiceCount < 1 THEN 0
                ELSE 1
        END,
A.SubscriptionItems,
A.Discount
FROM Subscription AS A
LEFT OUTER JOIN (SELECT SubscriptionId, InvoiceCount = COUNT(InvoiceId) FROM SubscriptionDetail WHERE InvoiceId IS NOT NULL GROUP BY SubscriptionId) SD ON SD.SubscriptionId=A.ID
INNER JOIN SubscriptionType AS AT ON AT.Id = A.SubscriptionTypeId
WHERE
A.CustomerId = @customerId AND A.OrderId IS NULL

SELECT * FROM @ProductsWithoutOrder

END
GO



/****** Object:  StoredProcedure [dbo].[sp_AddProductToOrder]    Script Date: 06/17/2009 15:40:25 ******/
-- =============================================
-- Create date: 2009-11-23
-- =============================================
CREATE PROCEDURE [dbo].[sp_AddProductToOrder]
        -- Add the parameters for the stored procedure here
        @productId int, 
        @productType int,
        @orderId int
        
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    IF(@productType=2) -- Reklama
                BEGIN
                        UPDATE dbo.Reklama SET idZamowienia=@orderId WHERE ReklamaId=@productId
                        RETURN
                END
        IF(@productType=1)
                BEGIN
                        UPDATE dbo.Subscription SET OrderId=@orderId WHERE Id=@productId
                        RETURN
                END
END
GO


/****** Object:  StoredProcedure [dbo].[sp_RemoveProductFromOrder]    Script Date: 06/17/2009 15:40:43 ******/
-- =============================================
-- Create date: 2009-11-23
-- =============================================
CREATE PROCEDURE [dbo].[sp_RemoveProductFromOrder]
        -- Add the parameters for the stored procedure here
        @productId int, 
        @productType int
        
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    IF(@productType=2) -- Reklama
                BEGIN
                        UPDATE dbo.Reklama SET idZamowienia=NULL WHERE ReklamaId=@productId
                        RETURN
                END
        IF(@productType=1)
                BEGIN
                        UPDATE dbo.Subscription SET OrderId=NULL WHERE Id=@productId
                        RETURN
                END
END