if( not exists(select * from sys.databases Where name='emoa2c'))
Begin
 CREATE DATABASE [emoa2c] ON  PRIMARY 
( NAME = N'emoa2c', FILENAME = N'{0}\emoa2c.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'emoa2c_log', FILENAME = N'{1}\emoa2c_log.ldf' , SIZE = 6144KB , FILEGROWTH = 10%)
 COLLATE Polish_CI_AS
   
End
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'emoa2c', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [emoa2c].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [emoa2c] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [emoa2c] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [emoa2c] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [emoa2c] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [emoa2c] SET ARITHABORT OFF 
GO
ALTER DATABASE [emoa2c] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [emoa2c] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [emoa2c] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [emoa2c] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [emoa2c] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [emoa2c] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [emoa2c] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [emoa2c] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [emoa2c] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [emoa2c] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [emoa2c] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [emoa2c] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [emoa2c] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [emoa2c] SET  READ_WRITE 
GO
ALTER DATABASE [emoa2c] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [emoa2c] SET  MULTI_USER 
GO
ALTER DATABASE [emoa2c] SET PAGE_VERIFY CHECKSUM  
GO
USE [emoa2c]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [emoa2c] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
USE [emoa2c]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wersja](
	[w_Id] [int] IDENTITY(1,1) NOT NULL,
	[w_Wersja] [nvarchar](15) NULL,
	[w_Data] [datetime] NULL,
 CONSTRAINT [PK_wersja] PRIMARY KEY CLUSTERED 
(
	[w_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert INTO [wersja] (w_Wersja, w_Data) VALUES ('1.3.3', GETDATE());
GO
USE [emoa2c]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sklepy](
	[s_Id] [int] IDENTITY(1,1) NOT NULL,
	[s_Nazwa] [nvarchar](50) NULL,
	[s_Default] [bit] NULL,
	[s_Webserwis] [nvarchar](255) NULL,
 CONSTRAINT [PK_sklepy] PRIMARY KEY CLUSTERED 
(
	[s_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_sklepy] UNIQUE NONCLUSTERED 
(
	[s_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [emoa2c]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
GO
USE [emoa2c]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kategorie](
	[kat_Id] [int] IDENTITY(1,1) NOT NULL,
	[kat_Sklep] [int] NULL,
	[kat_Dystrybutor] [int] NULL,
	[kat_Symbol] [nvarchar](50) NULL,	
	[kat_Nazwa] [nvarchar](255) NULL,
	[kat_Rodzic] [int] NULL,
	[kat_Poziom] [int] NULL,
	[kat_Marza] [decimal](15,2) NULL,	
	[kat_Eksport] [bit] NULL,
	[kat_Mapa] [int] NULL,	
	[kat_Waga] [decimal](15,2) NULL,
	[kat_Dodanie] [datetime] NULL,
	[kat_Modyfikacja] [datetime] NULL,
 CONSTRAINT [PK_kategorie] PRIMARY KEY CLUSTERED 
(
	[kat_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_kategorie] UNIQUE NONCLUSTERED 
(
	[kat_Dystrybutor] ASC,
    [kat_Symbol] ASC,
	[kat_Sklep] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[kategorie]  WITH CHECK ADD  CONSTRAINT [FK_kategorie_sklepy] FOREIGN KEY([kat_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[kategorie] CHECK CONSTRAINT [FK_kategorie_sklepy];
GO
USE [emoa2c]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sklepy_ustawienia](
	[su_Id] [int] IDENTITY(1,1) NOT NULL,
	[su_Sklep] [int] NULL,
	[su_Name] [nvarchar](255) NULL,
	[su_Path] [nvarchar](255) NULL,
	[su_Value] [nvarchar](255) NULL,
 CONSTRAINT [PK_sklepy_ustawienia] PRIMARY KEY CLUSTERED 
(
	[su_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[sklepy_ustawienia]  WITH CHECK ADD  CONSTRAINT [FK_sklepy_ustawienia_sklepy] FOREIGN KEY([su_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[sklepy_ustawienia] CHECK CONSTRAINT [FK_sklepy_ustawienia_sklepy];
GO
CREATE TABLE [dbo].[sl_dystrybutor](
	[dyst_Id] [int] NOT NULL,
	[dyst_Nazwa] [nvarchar](50) NULL,
 CONSTRAINT [PK_sl_dystrybutor] PRIMARY KEY CLUSTERED 
(
	[dyst_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

GO
ALTER TABLE [dbo].[kategorie]  WITH CHECK ADD  CONSTRAINT [FK_kategorie_dystrybutor] FOREIGN KEY([kat_Dystrybutor])
REFERENCES [dbo].[sl_dystrybutor] ([dyst_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[kategorie] CHECK CONSTRAINT [FK_kategorie_dystrybutor]
GO
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (1, 'Action');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (2, 'Pronox');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (10, 'Incom');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (4, 'AB');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (5, 'Agawa');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (6, 'NTT');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (7, 'Platon');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (8, 'Kontri');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (9, 'Azymut');
INSERT INTO [dbo].[sl_dystrybutor] ([dyst_Id], [dyst_Nazwa]) VALUES  (11, 'Bajtel');
GO
CREATE TABLE [dbo].[marza](
	[mar_Id] [int] IDENTITY(1,1) NOT NULL,
	[mar_Sklep] [int] NULL,
	[mar_CenaOd] [decimal](15, 2) NULL,
	[mar_CenaDo] [decimal](15, 2) NULL,
	[mar_Marza] [decimal](15, 2) NULL,
 CONSTRAINT [PK_marza] PRIMARY KEY CLUSTERED 
(
	[mar_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[marza]  WITH CHECK ADD  CONSTRAINT [FK_marza_sklepy] FOREIGN KEY([mar_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[marza] CHECK CONSTRAINT [FK_marza_sklepy];
GO
CREATE TABLE [dbo].[producent](
	[pro_Id] [int] IDENTITY(1,1) NOT NULL,
	[pro_Sklep] [int] NULL,
	[pro_Symbol] [nvarchar](50) NULL,
	[pro_Nazwa] [nvarchar](255) NULL,	
	[pro_Eksport] [bit] NULL,	
	[pro_Dodanie] [datetime] NULL,
	[pro_Modyfikacja] [datetime] NULL,
 CONSTRAINT [PK_producent] PRIMARY KEY CLUSTERED 
(
	[pro_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[producent]  WITH CHECK ADD  CONSTRAINT [FK_producent_sklepy] FOREIGN KEY([pro_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[producent] CHECK CONSTRAINT [FK_producent_sklepy];
GO
/****** Object:  Table [dbo].[produkty]    Script Date: 06/15/2009 13:15:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[produkty](
	[p_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[p_Sklep] [int] NULL,
	[p_PN] [nvarchar](50) NULL,
	[p_Nazwa] [nvarchar](255) NULL,
	[p_Producent] [int] NULL,
	[p_Gwarancja] [nvarchar](50) NULL,	
	[p_Nowosc] [bit] NOT NULL,
	[p_Promocja] [bit] NULL,
	[p_BestSeller] [bit] NULL,
	[p_Zapowiedz] [bit] NULL,
	[p_Dodanie] [datetime] NULL,
	[p_Modyfikacja] [datetime] NULL,
  [p_Aktualizacja] [datetime] NULL,
  [p_Mapa] [int] NULL,
  [p_VAT] [decimal](15,2) NULL,
  [p_CenaSRP] [decimal](15,4) NULL,
  [p_Waga] [decimal](15,3) NULL,
  [p_Marza] [decimal](15,2) NULL,
  [p_Oplata] [decimal](15,2) NULL,
  [p_Eksport] [bit] NULL,
  [p_RegEx] [bit] NULL,
 CONSTRAINT [PK_produkty] PRIMARY KEY CLUSTERED 
(
	[p_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_produkty] UNIQUE NONCLUSTERED 
(
	[p_Sklep] asc,
	[p_PN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[produkty]  WITH CHECK ADD  CONSTRAINT [FK_produkty_producent] FOREIGN KEY([p_Producent])
REFERENCES [dbo].[producent] ([pro_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[produkty] CHECK CONSTRAINT [FK_produkty_producent]
GO
ALTER TABLE [dbo].[produkty]  WITH CHECK ADD  CONSTRAINT [FK_produkty_sklepy] FOREIGN KEY([p_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[produkty] CHECK CONSTRAINT [FK_produkty_sklepy];
GO
CREATE TABLE [dbo].[produkty_Ceny](
	[pc_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[pc_Dystrybutor] [int] NULL,
	[pc_KodDostawcy] [nvarchar](50) NULL,
	[pc_Cena] [decimal](18, 4) NULL,
	[pc_Dostepnosc] [decimal](18, 4) NULL,
	[pc_Dodanie] [datetime] NULL,
	[pc_Modyfikacja] [datetime] NULL,
	[pc_Produkt] [bigint] NULL,
 CONSTRAINT [PK_p_Ceny] PRIMARY KEY CLUSTERED 
(
	[pc_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_p_Ceny] UNIQUE NONCLUSTERED 
(
	[pc_Produkt] ASC,
	[pc_Dystrybutor] ASC,
	[pc_KodDostawcy] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[produkty_Ceny]  WITH CHECK ADD  CONSTRAINT [FK_p_Ceny_produkty] FOREIGN KEY([pc_Produkt])
REFERENCES [dbo].[produkty] ([p_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[produkty_Ceny] CHECK CONSTRAINT [FK_p_Ceny_produkty]
GO
ALTER TABLE [dbo].[produkty_Ceny]  WITH CHECK ADD  CONSTRAINT [FK_p_Ceny_sl_dystrybutor] FOREIGN KEY([pc_Dystrybutor])
REFERENCES [dbo].[sl_dystrybutor] ([dyst_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[produkty_Ceny] CHECK CONSTRAINT [FK_p_Ceny_sl_dystrybutor];
GO
CREATE TABLE [dbo].[produkty_Kategoria](
	[pk_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[pk_Produkt] [bigint] NULL,
	[pk_Kategoria] [int] NULL,
 CONSTRAINT [PK_p_Kategoria] PRIMARY KEY CLUSTERED 
(
	[pk_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_p_Kategoria] UNIQUE NONCLUSTERED 
(
	[pk_Kategoria] ASC,
	[pk_Produkt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[produkty_Kategoria]  WITH CHECK ADD  CONSTRAINT [FK_p_Kategoria_kategoria] FOREIGN KEY([pk_Kategoria])
REFERENCES [dbo].[kategorie] ([kat_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[produkty_Kategoria] CHECK CONSTRAINT [FK_p_Kategoria_kategoria]
GO
ALTER TABLE [dbo].[produkty_Kategoria]  WITH CHECK ADD  CONSTRAINT [FK_p_Kategoria_produkty] FOREIGN KEY([pk_Produkt])
REFERENCES [dbo].[produkty] ([p_Id])
ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[produkty_Kategoria] CHECK CONSTRAINT [FK_p_Kategoria_produkty];
GO
CREATE TABLE [dbo].[parametry](
	[par_Id] [int] IDENTITY(1,1) NOT NULL,
	[par_Nazwa] [nvarchar](100) NULL,
	[par_Sklep] [int] NULL,
	[par_Widok] [bit] NULL,
  [par_SK] [char](32) NULL,
 CONSTRAINT [PK_parametry] PRIMARY KEY CLUSTERED 
(
	[par_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_parametry] UNIQUE NONCLUSTERED 
(
	[par_Sklep] ASC,
	[par_SK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[parametry]  WITH CHECK ADD  CONSTRAINT [FK_parametry_sklepy] FOREIGN KEY([par_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[parametry] CHECK CONSTRAINT [FK_parametry_sklepy];
GO
CREATE TABLE [dbo].[parametry_Wartosci](
	[parw_Id] [int] IDENTITY(1,1) NOT NULL,
	[parw_Parametr] [int] NULL,
	[parw_Wartosc] [text] NULL,
	[parw_SK] [char](32) NULL,
	[parw_JM] [nvarchar](50) NOT NULL,
	[parw_Typ] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_par_Wartosci] PRIMARY KEY CLUSTERED 
(
	[parw_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_parametry_Wartosci] UNIQUE NONCLUSTERED 
(
	[parw_Parametr] ASC,
	[parw_SK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[parametry_Wartosci]  WITH CHECK ADD  CONSTRAINT [FK_par_Wartosci_parametry] FOREIGN KEY([parw_Parametr])
REFERENCES [dbo].[parametry] ([par_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[parametry_Wartosci] CHECK CONSTRAINT [FK_par_Wartosci_parametry];
GO
CREATE TABLE [dbo].[produkty_Parametr](
	[pp_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[pp_Produkt] [bigint] NULL,
	[pp_Parametr] [int] NULL,
	[pp_Wartosc] [int] NULL,
 CONSTRAINT [PK_produkty_Parametr_1] PRIMARY KEY CLUSTERED 
(
	[pp_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[produkty_Parametr]  WITH CHECK ADD  CONSTRAINT [FK_produkty_Parametr_parametry_Wartosci] FOREIGN KEY([pp_Wartosc])
REFERENCES [dbo].[parametry_Wartosci] ([parw_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[produkty_Parametr] CHECK CONSTRAINT [FK_produkty_Parametr_parametry_Wartosci];
GO
CREATE TABLE [dbo].[produkty_Zdjecia](
	[pz_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[pz_Produkt] [bigint] NULL,
	[pz_Domyslne] [bit] NULL,
	[pz_Data] [datetime] NULL,
	[pz_Plik] [nvarchar](255) NULL,
	[pz_SK] [char](32) NULL,
 CONSTRAINT [PK_p_Zdjecia] PRIMARY KEY CLUSTERED 
(
	[pz_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_p_Zdjecia] UNIQUE NONCLUSTERED 
(
	[pz_Produkt] ASC,
	[pz_SK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[produkty_Zdjecia]  WITH CHECK ADD  CONSTRAINT [FK_p_Zdjecia_produkty] FOREIGN KEY([pz_Produkt])
REFERENCES [dbo].[produkty] ([p_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[produkty_Zdjecia] CHECK CONSTRAINT [FK_p_Zdjecia_produkty];
GO
CREATE TABLE [dbo].[pliki](
	[pl_Id] [int] IDENTITY(1,1) NOT NULL,
	[pl_Nazwa] [nvarchar](50) NULL,
	[pl_Plik] [nvarchar](50) NULL,
	[pl_Dystrybutor] [int] NULL,
	[pl_Sklep] [int] NULL,
	[pl_Przetworzony] [bit] NULL,
 CONSTRAINT [PK_pliki] PRIMARY KEY CLUSTERED 
(
	[pl_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[pliki]  WITH CHECK ADD  CONSTRAINT [FK_pliki_sklepy] FOREIGN KEY([pl_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pliki] CHECK CONSTRAINT [FK_pliki_sklepy]
GO
ALTER TABLE [dbo].[pliki]  WITH CHECK ADD  CONSTRAINT [FK_pliki_sl_dystrybutor] FOREIGN KEY([pl_Dystrybutor])
REFERENCES [dbo].[sl_dystrybutor] ([dyst_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pliki] CHECK CONSTRAINT [FK_pliki_sl_dystrybutor];
GO
CREATE TABLE [dbo].[wykluczenia](
	[wyk_Id] [int] IDENTITY(1,1) NOT NULL,
	[wyk_Sklep] [int] NULL,
	[wyk_Slowo] [varchar](250) COLLATE Polish_CI_AS NULL,
	[wyk_Wyklucz] [bit] NULL,
	[wyk_Wyjatek] [bit] NULL,
 CONSTRAINT [PK_wykluczenia] PRIMARY KEY CLUSTERED 
(
	[wyk_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[wykluczenia]  WITH CHECK ADD  CONSTRAINT [FK_wykluczenia_sklepy] FOREIGN KEY([wyk_Sklep])
REFERENCES [dbo].[sklepy] ([s_Id])
ON UPDATE CASCADE
ON DELETE CASCADE   
GO
ALTER TABLE [dbo].[wykluczenia] CHECK CONSTRAINT [FK_wykluczenia_sklepy];
GO
ALTER TABLE dbo.producent ADD CONSTRAINT
	IX_producent UNIQUE NONCLUSTERED 
	(
	pro_Symbol,
	pro_Sklep
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[error](
	[e_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[e_UNIQUE] [bigint] NULL,
	[e_Query] [text] NULL,
	[e_Nr] [nvarchar](50) NULL,
	[e_Proc] [nvarchar](255) NULL,
	[e_State] [nvarchar](50) NULL,
	[e_Message] [nvarchar](255) NULL,
	[e_Line] [nvarchar](50) NULL,
 CONSTRAINT [PK_error] PRIMARY KEY CLUSTERED 
(
	[e_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE PROCEDURE proc_GetErrorInfo
	@query text,
	@unique int
AS
BEGIN
	
	INSERT INTO [dbo].[error]
           ([e_UNIQUE],[e_Query],[e_Nr],[e_Proc],[e_State],[e_Message], [e_Line])
     VALUES
           (@unique, @query,  ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_MESSAGE(), ERROR_LINE());

END
GO
Create PROCEDURE dbo.proc_dodaj_par 
	@nazwa nvarchar(100),
	@sklep int,	
	@widok bit,
	@SK char(32),
	@id int OUT 
AS
BEGIN	
	SET NOCOUNT ON;  	
	SET @id = (Select TOP 1 par_id from parametry  Where par_sk=@SK and par_sklep=@sklep)

	if(ISNULL(@id,0)=0)
	Begin		
		INSERT INTO [dbo].[parametry] ([par_Nazwa],[par_Sklep],[par_Widok], [par_SK]) VALUES  (@nazwa, @sklep, @widok , @SK);
		Set @id = (select @@IDENTITY);
	End

END
GO
CREATE PROCEDURE dbo.proc_dodaj_wartosc
	
	@wartosc text,
	@parametr int,
	@SK char (32),
	@jm nvarchar (50),
	@typ nvarchar (50),
	@id int OUT
AS
BEGIN
	   
	SET NOCOUNT ON;  	
	SET @id = (Select TOP 1 parw_Id from parametry_Wartosci  Where parw_SK=@SK and parw_Parametr=@parametr)

	if(ISNULL(@id,0)=0)
	Begin		
		INSERT INTO [dbo].[parametry_Wartosci]
           ([parw_Parametr],[parw_Wartosc],[parw_SK],[parw_JM],[parw_Typ])
		VALUES (@parametr, @wartosc, @SK, @jm ,@typ)
		Set @id = (select @@IDENTITY);
	End
END
GO
Create PROCEDURE [dbo].[proc_dodaj_parwar] 
	@nazwa nvarchar(100),
	@sklep int,	
	@widok bit,
	@SK char(32),
	@id int,
	@wartosc text,
	@SK2 char (32),
	@typ nvarchar (50),
	@jm nvarchar (50)
AS
BEGIN	
	SET NOCOUNT ON;  	
	SET @id = (Select TOP 1 par_id from parametry  Where par_sk=@SK and par_sklep=@sklep)

	if(ISNULL(@id,0)=0)
	Begin		
		INSERT INTO [dbo].[parametry] ([par_Nazwa],[par_Sklep],[par_Widok], [par_SK]) VALUES  (@nazwa, @sklep, @widok , @SK);
		Set @id = (select @@IDENTITY);
	End
	Declare @wartoscId int;

	SET @wartoscId = (Select TOP 1 parw_Id from parametry_Wartosci Where parw_SK=@SK2 and parw_Parametr=@id)

	if(ISNULL(@wartoscId,0)=0)
	Begin		
		INSERT INTO [dbo].[parametry_Wartosci]
           ([parw_Parametr],[parw_Wartosc],[parw_SK],[parw_JM],[parw_Typ])
		VALUES (@id, @wartosc, @SK2, @jm ,@typ);		
	End

END
GO
Create PROCEDURE [dbo].[proc_dodaj_produkt] 
	@IdProdukt int OUT,
	@p_pn nvarchar(50), 
    @p_nazwa nvarchar(255), 
    @p_producent nvarchar(255), 
    @p_gwarancja nvarchar(50), 
    @p_dodanie datetime, 
    @p_modyfikacja datetime, 
    @p_nowosc bit, 
    @p_promocja bit ,
    @p_zapowiedz bit,
    @p_bestseller bit,
    @p_sklep int,
    @p_vat decimal(15,2),
    @p_cenaSRP decimal(15,4),
	@p_waga decimal(15,3),
	@p_oplata decimal(15,2)
AS
BEGIN	
	SET NOCOUNT ON;  
	Declare @producent int;	
	set @producent =(Select TOP 1 pro_Id from producent Where pro_Symbol=@p_producent and pro_Sklep=@p_sklep);

	SET @IdProdukt = (Select TOP 1 p_Id from produkty  Where p_PN=@p_pn and p_Producent=@producent and p_sklep=@p_sklep)

	if(ISNULL(@IdProdukt,0)=0)
		Begin				
			Insert Into produkty (p_PN,p_Nazwa,p_Producent,p_Gwarancja,p_Dodanie,p_Modyfikacja,p_Nowosc,p_Promocja, p_Sklep, p_Zapowiedz,p_BestSeller, p_VAT, p_CenaSRP, p_Waga, p_Oplata, p_Eksport, p_RegEx) 
			Values (@p_pn, @p_nazwa, @producent, @p_gwarancja, @p_dodanie, @p_modyfikacja, @p_nowosc, @p_promocja, @p_sklep, @p_zapowiedz, @p_bestseller, @p_vat, @p_cenaSRP, @p_waga, @p_oplata, '1' ,'0')
			Set @IdProdukt = (select @@IDENTITY);
		End
	else
		Begin
			Update produkty set p_nazwa=@p_nazwa,p_producent=@producent,p_gwarancja=@p_gwarancja,p_modyfikacja=@p_modyfikacja,p_nowosc=@p_nowosc,p_promocja=@p_promocja,p_zapowiedz=@p_zapowiedz,p_bestseller=@p_bestseller, p_VAT=@p_vat, p_CenaSRP=@p_cenaSRP, p_Waga=@p_waga,p_Oplata=@p_oplata Where p_id=@IdProdukt
		End

END
GO
Create PROCEDURE [dbo].[proc_dodaj_produkt_cena] 
	@IdProdukt int OUT, 
  @pc_dystrybutor int, 
	@pc_koddostawcy nvarchar(50),
  @pc_cena decimal (15,2),
  @pc_dostepnosc decimal(15,2),
  @pc_dodanie datetime,
  @pc_modyfikacja datetime	
AS
BEGIN	
	SET NOCOUNT ON;  	
	Declare @id int;
	Declare @query nvarchar(255);
		  
	SET @id = (Select  TOP 1 pc_Id from produkty_Ceny Where pc_Produkt=@IdProdukt and pc_Dystrybutor=@pc_dystrybutor and pc_KodDostawcy=@pc_koddostawcy)
	SET @query = 'Insert INTO produkty_Ceny (pc_Dystrybutor,pc_KodDostawcy,pc_Cena,pc_Dostepnosc,pc_Dodanie,pc_Modyfikacja, pc_Produkt)	Values (' + Convert(nvarchar(255),@pc_dystrybutor) + ', ' + Convert(nvarchar(255), @pc_koddostawcy) + ', ' + Convert(nvarchar(255), @pc_cena) + ', ' + Convert(nvarchar(255), @pc_dostepnosc) + ',' + Convert(nvarchar(255), @pc_dodanie) + ',' + Convert(nvarchar(255), @pc_modyfikacja) + ',' + Convert(nvarchar(255),@IdProdukt) +')'; 

	if(@IdProdukt is not null and ISNULL(@id,0)=0)
		Begin
			BEGIN TRY	
				Insert INTO produkty_Ceny (pc_Dystrybutor,pc_KodDostawcy,pc_Cena,pc_Dostepnosc,pc_Dodanie,pc_Modyfikacja, pc_Produkt) 
				Values (@pc_dystrybutor,@pc_koddostawcy,@pc_cena,@pc_dostepnosc,@pc_dodanie,@pc_modyfikacja, @IdProdukt); 
			END TRY
			BEGIN CATCH
					EXECUTE proc_GetErrorInfo @query, @id;
			END CATCH
		End
	else
		Begin
			Update produkty_Ceny set pc_cena=@pc_cena,pc_dostepnosc=@pc_dostepnosc,pc_modyfikacja=@pc_modyfikacja Where pc_id=@id;
		End

END
GO
Create PROCEDURE [dbo].[proc_dodaj_produkt_zdjecie] 
	@IdProdukt int OUT,
	@pz_domyslne bit,
  @pz_data datetime,
  @pz_plik nvarchar(255),
  @pz_sk char(32)
AS
BEGIN	
	SET NOCOUNT ON;  	
	Declare @id int;
	SET @id = (Select TOP 1 pz_id from produkty_Zdjecia Where pz_Produkt=@IdProdukt and pz_SK=@pz_sk)

	if(@IdProdukt is not null and ISNULL(@id,0)=0)
	Begin		
		Insert INTO produkty_Zdjecia (pz_Produkt,pz_Domyslne,pz_Data,pz_Plik,pz_SK) 
		Values (@IdProdukt,@pz_domyslne,@pz_data,@pz_plik,@pz_sk);
	End

END
GO
Create  PROCEDURE [dbo].[proc_dodaj_prokat] 
	@IdProdukt int OUT,
	@pk_kategoria nvarchar(50),
  @pk_dystrybutor int,
  @pk_sklep int
AS
BEGIN	
	SET NOCOUNT ON;  	
	
	Declare @Kategoria int; 
	Declare @id int;
	DECLARE @query nvarchar(255)
	Set @Kategoria=(Select TOP 1 kat_Id from kategorie Where kat_symbol=@pk_kategoria and kat_dystrybutor=@pk_dystrybutor and kat_Sklep=@pk_Sklep);
	Set @id = (Select TOP 1 pk_Kategoria from dbo.produkty_Kategoria Where pk_Produkt=@IdProdukt and pk_Kategoria=@Kategoria)	
	set @query = 'Insert Into produkty_Kategoria (pk_Produkt,pk_Kategoria) Values (' + Convert(nvarchar(255) , @IdProdukt) + ', '+ Convert(nvarchar(255) , @Kategoria) + ')';
	
	if(@IdProdukt is not null and @Kategoria is not null and @id is null) 
	Begin 
	    BEGIN TRY
			Insert Into produkty_Kategoria (pk_Produkt,pk_Kategoria) Values (@IdProdukt,@Kategoria)
		END TRY
		BEGIN CATCH
				EXECUTE proc_GetErrorInfo @query, @Kategoria;
		END CATCH
	End

END
GO
Create PROCEDURE [dbo].[proc_dodaj_produkt_parametr] 
	@IdProdukt int OUT,
	@sklep int,	
	@SK char(32)	
AS
BEGIN	
	SET NOCOUNT ON; 
	Declare @parametr int; 
	Declare @wartosc int; 	
	Declare @id int;
	Declare @query nvarchar(255);
	Select TOP 1 @wartosc = parw_id, @parametr=par_id from dbo.parametry_Wartosci Inner JOIN parametry on (parw_Parametr=par_Id and par_Sklep=@sklep)  Where parw_SK=@SK
	SET @query = 'INSERT INTO [dbo].[produkty_Parametr] ([pp_Produkt] ,[pp_Parametr]  ,[pp_Wartosc]) VALUES  (' + Convert(nvarchar(255),@IdProdukt) +',' + convert(nvarchar(255), @parametr) + ',' + convert(nvarchar(255),@wartosc) +')';
	
	Set @id = (Select TOP 1 pp_Id from dbo.produkty_Parametr Where pp_Parametr=@parametr and pp_Wartosc=@wartosc and pp_Produkt=@IdProdukt);

	if(ISNULL(@id,0)=0 and @wartosc is not null and @parametr is not null and @IdProdukt is not null )
	Begin		
		BEGIN TRY
			INSERT INTO [dbo].[produkty_Parametr] ([pp_Produkt] ,[pp_Parametr]  ,[pp_Wartosc])
			VALUES  (@IdProdukt,@parametr,@wartosc);
		END TRY
		BEGIN CATCH
				EXECUTE proc_GetErrorInfo @query, @id;
		END CATCH;
	End

END
GO
CREATE TABLE [dbo].[sl_pliki](
	[plt_Id] [int] NOT NULL,
	[plt_Nazwa] [nvarchar](50) COLLATE Polish_CI_AS NULL,
 CONSTRAINT [PK_sl_pliki] PRIMARY KEY CLUSTERED 
(
	[plt_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (1, 'Duży XML');
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (2, 'Mały XML');
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (3, 'Opisy XML');
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (4, 'Zdjęcia XML');
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (5, 'Ceny XML');
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (6, 'Dostępność XML');
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (7, 'Technika XML');
Insert INTO [sl_pliki] (plt_Id, plt_Nazwa) VALUES (8, 'Grupy XML');
GO
ALTER TABLE dbo.pliki ADD	pl_Typ int NULL;
GO
ALTER TABLE dbo.pliki ADD CONSTRAINT
	FK_pliki_sl_pliki FOREIGN KEY
	(
	pl_Typ
	) REFERENCES dbo.sl_pliki
	(
	plt_Id
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
Create PROCEDURE [dbo].[proc_zmien_produkt_cena] 
  @pc_dystrybutor int, 
  @pc_koddostawcy nvarchar(50),
  @pc_cena decimal (15,2),
  @pc_dostepnosc decimal(15,2),  
  @pc_modyfikacja datetime,
  @sklep int
AS
BEGIN	
	SET NOCOUNT ON;  	
	Declare @id int;

	Update produkty_Ceny 
	set pc_cena=@pc_cena,pc_dostepnosc=@pc_dostepnosc,pc_modyfikacja=@pc_modyfikacja 
	from produkty_Ceny
	Inner JOIN produkty on (p_Id=pc_Produkt and p_Sklep=@sklep)
	Where pc_koddostawcy=@pc_koddostawcy and pc_dystrybutor=@pc_dystrybutor;
	
	
END
GO
CREATE NONCLUSTERED INDEX [dta_ip_K4_K2] ON [dbo].[parametry_Wartosci] 
(
	[parw_SK] ASC,
	[parw_Parametr] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE STATISTICS [dta_stat_3_4_2] ON [dbo].[produkty_Parametr]([pp_Parametr], [pp_Wartosc], [pp_Produkt])
GO
CREATE STATISTICS [dta_stat_2_3] ON [dbo].[produkty_Parametr]([pp_Produkt], [pp_Parametr])
GO
CREATE NONCLUSTERED INDEX [dta_ip_K4_K2_K3_1] ON [dbo].[produkty_Parametr] 
(
	[pp_Wartosc] ASC,
	[pp_Produkt] ASC,
	[pp_Parametr] ASC
)
INCLUDE ( [pp_Id]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
Create PROCEDURE [dbo].[proc_dodaj_producenta] 
	@pro_sklep int,
	@pro_symbol nvarchar(50),	
	@pro_nazwa nvarchar(255),
	@pro_eksport bit,
	@pro_dodanie datetime,
	@pro_modyfikacja datetime 
AS
BEGIN	
	SET NOCOUNT ON;  	
	Declare @id int;
	SET @id = (Select TOP 1 pro_id from dbo.producent  Where pro_symbol=@pro_symbol and pro_sklep=@pro_sklep)

	if(ISNULL(@id,0)=0)
	Begin		
		INSERT INTO [dbo].[producent]
           ([pro_Sklep],[pro_Symbol],[pro_Nazwa],[pro_Eksport],[pro_Dodanie],[pro_Modyfikacja])
		VALUES
           (@pro_sklep,@pro_symbol,@pro_nazwa,@pro_eksport,@pro_dodanie,@pro_modyfikacja);         
	End

END
GO
Create PROCEDURE [dbo].[proc_zmien_produkt_cena_azymut] 
  @pc_dystrybutor int, 
  @pc_koddostawcy nvarchar(50),
  @pc_cena decimal (15,2),
  @p_vat decimal(15,2),  
  @pc_modyfikacja datetime,
  @sklep int,
  @p_SRP decimal(15,4)
AS
BEGIN	
	SET NOCOUNT ON;  	
	Declare @produktId int;
	
		
	Update produkty_Ceny 
	set pc_cena=@pc_cena,pc_modyfikacja=@pc_modyfikacja 
	from produkty_Ceny
	Inner JOIN produkty on (p_Id=pc_Produkt and p_Sklep=@sklep)
	Where pc_koddostawcy=@pc_koddostawcy and pc_dystrybutor=@pc_dystrybutor;
	
	Select @produktId = pc_Produkt 
	from produkty_Ceny
	Inner JOIN produkty on (p_Id=pc_Produkt and p_Sklep=@sklep)
	Where pc_koddostawcy=@pc_koddostawcy and pc_dystrybutor=@pc_dystrybutor;
	
	if(@produktId is not null)
	Begin
		Update produkty set p_CenaSRP=@p_SRP, p_VAT=@p_vat, p_Modyfikacja=@pc_modyfikacja Where p_Id= @produktId
	End
	
END
GO
Create PROCEDURE [dbo].[proc_zmien_produkt_dostepnosc] 
  @pc_dystrybutor int, 
  @pc_koddostawcy nvarchar(50),
  @pc_dostepnosc decimal (15,2),
  @pc_modyfikacja datetime,
  @sklep int
AS
BEGIN	
	SET NOCOUNT ON;  			
		
	Update produkty_Ceny 
	set pc_dostepnosc=@pc_dostepnosc,pc_modyfikacja=@pc_modyfikacja 
	from produkty_Ceny
	Inner JOIN produkty on (p_Id=pc_Produkt and p_Sklep=@sklep)
	Where pc_koddostawcy=@pc_koddostawcy and pc_dystrybutor=@pc_dystrybutor;	
	
END
GO
Create proc proc_parametry_produktu @Id int
AS
Begin
	Select par_Nazwa, parw_Wartosc from produkty_parametr
	Inner JOIN produkty on (p_Id=pp_Produkt)
	inner JOIN parametry on (par_Id=pp_Parametr)
	inner JOIN parametry_wartosci on (parw_Id=pp_Wartosc)
	Where p_Id=@Id
END
GO
Create proc proc_GetKategoria 
	@sklep int, 
	@dystrybutor int, 
	@symbol nvarchar(50), 
	@rodzic int OUT,
	@poziom int OUT
AS
Begin

Select @rodzic= kat_Id, @poziom = (kat_Poziom+1) from kategorie
Where kat_Sklep=@sklep and kat_Dystrybutor=@dystrybutor and kat_Symbol=@symbol

END
GO
GO
CREATE TABLE [dbo].[produkty_Rozmiary](
	[pr_Id] [int] IDENTITY(1,1) NOT NULL,
	[pr_Produkt] [bigint] NULL,
	[pr_Kolor] [nvarchar](50) NULL,
	[pr_Rozmiar] [nvarchar](50) NULL,
	[pr_Ilosc] [decimal](15, 2) NULL,
	[pr_Dodanie] [datetime] NULL,
	[pr_Modyfikacja] [datetime] NULL,
 CONSTRAINT [PK_produkty_rozmiary] PRIMARY KEY CLUSTERED 
(
	[pr_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_produkty_rozmiary] UNIQUE NONCLUSTERED 
(
	[pr_Produkt] ASC,
	[pr_Kolor] ASC,
	[pr_Rozmiar] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[produkty_Rozmiary]  WITH CHECK ADD  CONSTRAINT [FK_produkty_rozmiary_produkty] FOREIGN KEY([pr_Produkt])
REFERENCES [dbo].[produkty] ([p_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[produkty_Rozmiary] CHECK CONSTRAINT [FK_produkty_rozmiary_produkty]
GO
Create proc proc_UsunRozmiary
	@sklep int
AS
Begin
		Delete from dbo.produkty_Rozmiary
		Where pr_Produkt in (Select p_Id from produkty Where p_Sklep=@sklep)
End
GO
Create proc proc_ZerujRozmiary
	@sklep int
AS
Begin

		Update dbo.produkty_Rozmiary set pr_Ilosc=0
		Where pr_Produkt in (Select p_Id from produkty Where p_Sklep=@sklep)
End
GO
Create PROCEDURE [dbo].[proc_dodaj_produkt_rozmiary] 
	@IdProdukt int OUT, 
	@pr_kolor nvarchar(50),
	@pr_rozmiar nvarchar(50), 	
	@pr_ilosc decimal (15,2),
	@pr_dodanie datetime,
	@pr_modyfikacja datetime	
AS
BEGIN	
	SET NOCOUNT ON;  	
	Declare @id int;
	Declare @query nvarchar(255);
		  
	SET @id = (Select  TOP 1 pr_Id from produkty_rozmiary Where pr_Produkt=@IdProdukt and pr_Kolor=@pr_kolor and pr_Rozmiar=@pr_rozmiar)
	
	if(ISNULL(@id,0)=0)
		Begin
			BEGIN TRY	
				Insert INTO produkty_Rozmiary(pr_Produkt, pr_Kolor, pr_Rozmiar, pr_Ilosc, pr_Dodanie, pr_Modyfikacja) 
				Values (@IdProdukt, @pr_kolor, @pr_rozmiar, @pr_ilosc, @pr_dodanie, @pr_modyfikacja); 
			END TRY
			BEGIN CATCH
					EXECUTE proc_GetErrorInfo @query, @id;
			END CATCH
		End
	else
		Begin
			Update produkty_Rozmiary set pr_ilosc=@pr_ilosc,pr_modyfikacja=@pr_modyfikacja Where pr_id=@id;
		End

END
GO
Create  proc [dbo].[proc_GetPN] 
	@IdProdukt bigint OUT,
	@pn nvarchar(50),
	@sklep int,
	@dystrybutor int 
AS
BEGIN

	Select @IdProdukt = p_Id from produkty 
	Inner Join produkty_Ceny on (pc_Produkt=p_Id and pc_Dystrybutor=@dystrybutor)
	Where p_PN=@pn and p_Sklep=@sklep;
	   
	if( @IdProdukt is null)
	Begin
		Select @IdProdukt = p_Id from produkty 
		Inner Join produkty_Ceny on (pc_Produkt=p_Id and pc_KodDostawcy=@pn and pc_Dystrybutor=@dystrybutor)
		Where p_Sklep=@sklep;
	End

End
GO
CREATE TABLE [dbo].[kategorie_Marza](
	[katm_Id] [int] IDENTITY(1,1) NOT NULL,
	[katm_Kategoria] [int] NULL,
	[katm_Od] [decimal](15, 2) NULL,
	[katm_Do] [decimal](15, 2) NULL,
	[katm_Marza] [decimal](15, 2) NULL,
 CONSTRAINT [PK_kategoria_Marza] PRIMARY KEY CLUSTERED 
(
	[katm_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[kategorie_Marza]  WITH CHECK ADD  CONSTRAINT [FK_kategorie_Marza_kategorie] FOREIGN KEY([katm_Kategoria])
REFERENCES [dbo].[kategorie] ([kat_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[kategorie_Marza] CHECK CONSTRAINT [FK_kategorie_Marza_kategorie]
GO
CREATE TABLE [dbo].[log_kategorie](
	[lk_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[lk_Kategoria] [int] NULL,
	[lk_Wyslano] [bit] NULL,
	[lk_Aktualizacja] [bit] NULL,
	[lk_Czas] [datetime] NULL,
	[lk_Paczka] [nvarchar](50) NULL,
	[lk_Error] [bit] NULL,
	[lk_ErrMsg] [text] NULL,
 CONSTRAINT [PK_log_kategorie] PRIMARY KEY CLUSTERED 
(
	[lk_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[log_kategorie]  WITH CHECK ADD  CONSTRAINT [FK_log_kategorie_kategorie] FOREIGN KEY([lk_Kategoria])
REFERENCES [dbo].[kategorie] ([kat_Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[log_kategorie] CHECK CONSTRAINT [FK_log_kategorie_kategorie]
GO
GO
CREATE TABLE [dbo].[log_produkty](
	[lpro_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[lpro_Produkt] [bigint] NULL,
	[lpro_Wyslano] [bit] NULL,
	[lpro_Aktualizacja] [bit] NULL,
	[lpro_Czas] [datetime] NULL,
	[lpro_Paczka] [nvarchar](50) NULL,
	[lpro_Error] [bit] NULL,
	[lpro_ErrMsg] [text] NULL,
 CONSTRAINT [PK_log_produkty] PRIMARY KEY CLUSTERED 
(
	[lpro_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[log_produkty]  WITH CHECK ADD  CONSTRAINT [FK_log_produkty_log_produkty] FOREIGN KEY([lpro_Produkt])
REFERENCES [dbo].[produkty] ([p_Id])
GO
ALTER TABLE [dbo].[log_produkty] CHECK CONSTRAINT [FK_log_produkty_log_produkty]
GO
CREATE FUNCTION dbo.funkcja_GetRodzicKat 
(	
	@Rodzic int
)
RETURNS nvarchar(50)
AS
BEGIN
	
	DECLARE @zwrot nvarchar(50)

	
	SELECT @zwrot = kat_Symbol from kategorie Where kat_Id=@Rodzic

	
	RETURN @zwrot;

END
GO
Create proc proc_LogiKategorii
	@Paczka nvarchar(50)
AS
BEGIN
	Select kat_Nazwa [Nazwa], kat_Symbol [Symbol], lk_Wyslano [Wysłano], lk_Aktualizacja [Zaktualizowano], lk_Error [Błąd], lk_ErrMsg [Komunikat], lk_Czas [Czas] from log_kategorie
	Inner JOIN kategorie on (kat_Id=lk_Kategoria)
	Where lk_Paczka=@Paczka

End
GO
GO

CREATE FUNCTION [dbo].[funkcja_GetPrice] 
(
	@cena decimal (15,4),
	@kategoria int,
	@sklep int,
	@marza decimal (15,2),
	@marza_kat decimal (15,2),
	@vat decimal(15,2),
	@round bit,
	@grosik bit,
	@large bit,
	@oplata decimal(15,2)
)
RETURNS  decimal(15,4)
AS
BEGIN
	
	DECLARE @zwrot decimal(15,4)
	
	--marza na produkt
	if(IsNULL(@marza,0) > 0)
		Begin
			SET @zwrot = 1;--(1 + @marza/100) * @cena;
		End
	--marza na kategorie
	else if(ISNULL(@marza_kat,0)>0)
		Begin
			 SET @zwrot = (1 + @marza_kat/100) * @cena;
		End
	--marza wartosciowa na kategorie
	else if exists(Select 1 from kategorie_Marza Where katm_Kategoria=@kategoria and katm_Od<=@cena and katm_Do>@cena)
		Begin
			Select TOP 1 @zwrot = (1 + katm_Marza/100) * @cena from kategorie_Marza Where katm_Kategoria=@kategoria and katm_Od<=@cena and katm_Do>@cena;
		End
	--wartosciowa na kwote
	else if exists(Select 1 from marza Where mar_Sklep=@sklep and mar_CenaOd<=@cena and mar_CenaDo>@cena)
		Begin
			Select TOP 1 @zwrot = (1 + mar_Marza/100) * @cena from marza Where mar_Sklep=@sklep and mar_CenaOd<=@cena and mar_CenaDo>@cena;
		End
	else
		Begin
				SET @zwrot = 1.2 * @cena;
		End
	
	if @large=1
	Begin
		SET @zwrot =  @zwrot + @oplata
	End	
		   
	if(@round=1 and @grosik=0)
		Begin
			SET @zwrot = ROUND(@zwrot * (1 + @vat/100), 0)  / (1 + @vat/100);	
		End	
	else if (@round=1 and @grosik=1)
		Begin
			SET @zwrot = (ROUND(@zwrot * (1 + @vat/100), 0) - 0.01)  / (1 + @vat/100);	
		End	
	
	return @zwrot;
END

GO
Create proc [dbo].[proc_Produkty] 
	@sklep int,
	@round bit,
	@grosik bit,
	@large bit
as
Begin
	Select	
			p_Id [Id], 
			p_Eksport [Eksport],
			p_RegEx [Wykluczenie],					 
			p_PN [PartNo], 
			p_Nazwa [Nazwa],
			dyst_Nazwa [Dystrybutor], 
			dyst_Id [DystrybutorId], 
			pc_KodDostawcy [Symbol],
			p_Marza [Marża],
			pc_Cena [Cena zakupu],
			dbo.funkcja_GetPrice(pc_Cena, a.kat_Id, p_Sklep, NULL, a.kat_Marza, p_VAT, @round, @grosik, @large, p_Oplata) [Cena sprzedaży],
			p_CenaSRP [Cena SRP],
			p_VAT [VAT%],
			dbo.funkcja_GetPriceBrutto(pc_Cena, a.kat_Id, p_Sklep, NULL, a.kat_Marza, p_VAT, @round, @grosik, @large, p_Oplata) [Cena brutto],
			pc_Dostepnosc [Dostępność],
			pro_Nazwa [Producent],
			pro_Symbol [Kod producenta],
			a.kat_Nazwa [Kategoria],
			a.kat_Symbol [Symbol kategorii],
			isnull(b.kat_Symbol,0) [Rodzic],
			isnull(c.kat_Symbol,0) [Dziadek],
			case when ISNULL(p_Waga,0) > 0 then p_Waga else a.kat_Waga end [Waga],		
			p_Gwarancja [Gwarancja],
			ISNULL(a.kat_Mapa,0) [Mapa kategorii],
			ISNULL(b.kat_Mapa,0) [Mapa rodzica],
			ISNULL(c.kat_Mapa,0) [Mapa dziadka]
	from produkty 
		Inner JOIN produkty_kategoria on (pk_Produkt=p_Id)
		Inner JOIN kategorie a on (pk_Kategoria=a.kat_Id and a.kat_Eksport=1)
		Inner JOIN produkty_ceny on (p_Id=pc_Produkt)
		Inner JOIN sl_Dystrybutor on (dyst_Id=pc_Dystrybutor)
		Inner JOIN producent on (pro_Id=p_Producent and pro_Eksport=1)
		Left JOIN kategorie b on (b.kat_Id=a.kat_Rodzic)
		Left JOIN kategorie c on (c.kat_Id=b.kat_Rodzic)
	Where p_Sklep=@sklep and pc_Cena > 0
	Order by p_Id
	
END
GO
Create FUNCTION [dbo].[funkcja_GetPriceBrutto] 
(
	@cena decimal (15,4),
	@kategoria int,
	@sklep int,
	@marza decimal (15,2),
	@marza_kat decimal (15,2),
	@vat decimal(15,2),
	@round bit,
	@grosik bit,
	@large bit,
	@oplata decimal(15,2)
)
RETURNS  decimal(15,4)
AS
BEGIN
	
	DECLARE @zwrot decimal(15,4)
	
	--marza na produkt
	if(IsNULL(@marza,0) > 0)
		Begin
			SET @zwrot = (1 + @marza/100) * @cena;
		End
	--marza na kategorie
	else if(ISNULL(@marza_kat,0)>0)
		Begin
			 SET @zwrot = (1 + @marza_kat/100) * @cena;
		End
	--marza wartosciowa na kategorie
	else if exists(Select 1 from kategorie_Marza Where katm_Kategoria=@kategoria and katm_Od<=@cena and katm_Do>@cena)
		Begin
			Select TOP 1 @zwrot = (1 + katm_Marza/100) * @cena from kategorie_Marza Where katm_Kategoria=@kategoria and katm_Od<=@cena and katm_Do>@cena;
		End
	--wartosciowa na kwote
	else if exists(Select 1 from marza Where mar_Sklep=@sklep and mar_CenaOd<=@cena and mar_CenaDo>@cena)
		Begin
			Select TOP 1 @zwrot = (1 + mar_Marza/100) * @cena from marza Where mar_Sklep=@sklep and mar_CenaOd<=@cena and mar_CenaDo>@cena;
		End
	else
		Begin
				SET @zwrot = 1.2 * @cena;
		End
	
	if @large=1
	Begin
		SET @zwrot =  @zwrot + @oplata
	End		   
		   
		 
	if(@round=1 and @grosik=0)
		Begin
			SET @zwrot = ROUND(@zwrot * (1 + @vat/100), 0);			
		End	
	else if (@round=1 and @grosik=1)
		Begin
			SET @zwrot = ROUND(@zwrot * (1 + @vat/100), 0) - 0.01;
		End
	else 
		Begin
			SET @zwrot = ROUND(@zwrot * (1 + @vat/100), 2);	
		end	

	RETURN @zwrot;

END
GO
Create proc proc_GetImage 
		@produkt int
As
Begin
	Select * from produkty_Zdjecia Where pz_Produkt=@produkt
End

GO

Create proc proc_Technika
	@produkt int
As
Begin
	Select pp_Produkt, par_Nazwa, parw_Wartosc, parw_SK, parw_JM, parw_Typ from produkty_Parametr 
	Inner JOIN dbo.parametry on (pp_Parametr=par_Id)
	Inner JOIN dbo.parametry_Wartosci on (pp_Wartosc=parw_Id)
	Where pp_Produkt=@produkt and par_Widok=1
End
GO

Create proc proc_Rozmiary
	@produkt int
As
Begin
	Select * from dbo.produkty_Rozmiary Where pr_Produkt=@produkt
End
GO
Create proc proc_ListaProducentow
	@sklep int,
	@szukaj nvarchar(255)
As
Begin
	if(LEN(@szukaj)=0)
		Begin
			Select pro_Id [Id], 
				   pro_Symbol [Symbol], 
				   pro_Nazwa [Nazwa], 
				   pro_Eksport [Eksport],
				   pro_Dodanie [Utworzono],
				   pro_Modyfikacja [Zmodyfikowano]
			from dbo.producent
			Where pro_Sklep=@sklep
		End
	Else 
		Begin
			Select pro_Id [Id], 
				   pro_Symbol [Symbol], 
				   pro_Nazwa [Nazwa], 
				   pro_Eksport [Eksport],
				   pro_Dodanie [Utworzono],
				   pro_Modyfikacja [Zmodyfikowano]
			from dbo.producent
			Where pro_Sklep=@sklep and pro_Nazwa like @szukaj
		End
End
GO
Create proc proc_ListaParametrow
	@sklep int,
	@szukaj nvarchar(255)
As
Begin
	if(LEN(@szukaj)=0)
		Begin
			Select par_Id [Id], 
				   par_SK [Symbol], 
				   par_Nazwa [Nazwa], 
				   par_Widok [Eksport]				   
			from dbo.parametry
			Where par_Sklep=@sklep
		End
	Else 
		Begin
			Select par_Id [Id], 
				   par_SK [Symbol], 
				   par_Nazwa [Nazwa], 
				   par_Widok [Eksport]				   
			from dbo.parametry
			Where par_Sklep=@sklep and par_Nazwa like @szukaj
		End
End
GO
Create PROCEDURE [dbo].[proc_dodaj_parwarProdukt] 
	@nazwa nvarchar(100),
	@sklep int,	
	@widok bit,
	@SK char(32),
	@id int,
	@wartosc text,
	@SK2 char (32),
	@typ nvarchar (50),
	@jm nvarchar (50),
	@IdProdukt int
AS
BEGIN	
	SET NOCOUNT ON;  	
	SET @id = (Select TOP 1 par_id from parametry  Where par_sk=@SK and par_sklep=@sklep)

	if(ISNULL(@id,0)=0)
	Begin		
		INSERT INTO [dbo].[parametry] ([par_Nazwa],[par_Sklep],[par_Widok], [par_SK]) VALUES  (@nazwa, @sklep, @widok , @SK);
		Set @id = (select @@IDENTITY);
	End
	Declare @wartoscId int;

	SET @wartoscId = (Select TOP 1 parw_Id from parametry_Wartosci Where parw_SK=@SK2 and parw_Parametr=@id)

	if(ISNULL(@wartoscId,0)=0)
	Begin		
		INSERT INTO [dbo].[parametry_Wartosci]
           ([parw_Parametr],[parw_Wartosc],[parw_SK],[parw_JM],[parw_Typ])
		VALUES (@id, @wartosc, @SK2, @jm ,@typ);	
		
		Select @wartoscId = @@IDENTITY;	
	End
	
	
	Declare @wpis bigint;	
	Select @wpis=pp_Id from produkty_Parametr 
		Where pp_Produkt=@IdProdukt and pp_Parametr=@id and pp_Wartosc=@wartoscId
	
	if(@IdProdukt is not null 
		and @id is not null 
		and @wartoscId is not null
		and @wpis is null
		)
	Begin		
		INSERT INTO [dbo].[produkty_Parametr]
           ([pp_Produkt],[pp_Parametr],[pp_Wartosc])
		VALUES (@IdProdukt, @id, @wartoscId)
	End
END
GO
Create proc proc_ListaWykluczen
	@sklep int,
	@szukaj nvarchar(255)
As
Begin
	if(LEN(@szukaj)=0)
		Begin
			Select wyk_Id [Id], 
				   wyk_Slowo [Słowo],
				   wyk_Wyklucz [Wyklucz], 
				   wyk_Wyjatek [Wyjątek]		   
			from dbo.wykluczenia
			Where wyk_Sklep=@sklep 
		End
	Else 
		Begin
			Select wyk_Id [Id], 
				   wyk_Slowo [Słowo],
				   wyk_Wyklucz [Wyklucz], 
				   wyk_Wyjatek [Wyjątek]				   
			from dbo.wykluczenia
			Where wyk_Sklep=@sklep and wyk_Slowo like @szukaj
		End
End
GO
Create proc proc_SklepProdukty
	@sklep int
AS
Begin
		Select p_Id as [Id] from produkty Where p_Sklep=@sklep
End
GO
Create proc proc_UsunSklep 
		 @sklep int
As
BEGIN
	Delete from dbo.sklepy Where s_Id=@sklep;
END
GO
Create proc proc_UsunProdukt
	@id bigint
As
BEGIN
	
	Delete from dbo.produkty_Kategoria Where pk_Produkt=@id;
	Delete from dbo.produkty Where p_Id=@id;
	
END
GO
Create proc proc_PobierzDystrybutora
	@sklep int,
	@dystrybutor int
AS
BEGIN
	
	Select p_Id [Id], pc_KodDostawcy [Symbol], p_PN [Indeks]
	from produkty_Ceny
	Inner JOIN produkty on (pc_Produkt=p_Id and p_Sklep=@sklep)
	Where pc_Dystrybutor=@dystrybutor


END
GO
Create proc proc_zmienSRP 
		@produkt int,
		@SRP decimal(15,4)
AS
BEGIN
		Update produkty set p_CenaSRP=@SRP Where p_Id=@produkt
END
GO
Create proc proc_zmienWage 
		@produkt int,
		@waga decimal(15,4)
AS
BEGIN
		Update produkty set p_Waga=@waga Where p_Id=@produkt
END