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