print 'creazione tabella gruppi taglie'
IF NOT EXISTS (select * from Information_SCHEMA.columns where Table_name='GruppiTaglie')
CREATE TABLE [dbo].[GruppiTaglie](
[NomeGruppo] [nvarchar](50) NOT NULL,
[Taglie] [nvarchar](250) NOT NULL,
[FiltriJanus] [nvarchar](250) NULL,
[Negozio] [nvarchar](10) NULL,
[Stagione] [nvarchar](10) NULL,
[FiltroSQL] [nvarchar](250) NULL,
[UserIns] [nvarchar](50) NOT NULL,
[DataIns] [datetime] NOT NULL,
CONSTRAINT [PK_GruppiTaglie] PRIMARY KEY CLUSTERED 
(
[NomeGruppo] 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

print 'modifica tabella cliente'
IF NOT EXISTS (select * from Information_SCHEMA.columns where Table_name='Cliente' and column_name='Tipo')
ALTER TABLE dbo.Cliente ADD	Tipo nvarchar(5) NULL
GO
IF NOT EXISTS (select * from Information_SCHEMA.columns where Table_name='Cliente' and column_name='Codice') BEGIN
EXECUTE sp_rename N'dbo.Cliente.CodiceCard', N'Codice', 'COLUMN' 
END
GO
UPDATE [dbo].[Cliente]   SET [Tipo] = 'C' 
--UPDATE [dbo].[Cliente]   SET [Tipo] = 'F' WHERE [RagioneSociale] like 'BENCOM%'
--UPDATE [dbo].[Cliente]   SET [Tipo] = 'C' WHERE [RagioneSociale] not like 'BENCOM%'
--UPDATE [dbo].[Cliente]   SET [Codice] = 'B' WHERE [RagioneSociale] like 'BENCOM%'
GO

print 'creazione tabelle DocumentiT e DocumentiD'
IF NOT EXISTS (select * from Information_SCHEMA.columns where Table_name='DocumentiT') BEGIN
CREATE TABLE [dbo].[DocumentiT](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IDNegozio] [nvarchar](10) NOT NULL,
	[TipoDocumento] [nvarchar](5) NOT NULL,
	[DataDocumento] [datetime] NOT NULL,
	[NumeroDocumento] [nvarchar](10) NOT NULL,
	[NumeroViaggio] [nvarchar](10) NULL,
	[Fornitore] [nvarchar](5) NULL,
	[Anno] [nvarchar](5) NULL,
	[Stagione] [nvarchar](5) NULL,
	[Stato] [nvarchar](5) NULL,
	[UserIns] [nvarchar](50) NOT NULL,
	[DataIns] [datetime] NOT NULL,
	[UserMod] [nvarchar](50) NULL,
	[DataMod] [datetime] NULL,
 CONSTRAINT [PK_tmpDocumentiT] PRIMARY KEY CLUSTERED 
(
	[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]
END
GO
IF NOT EXISTS (select * from Information_SCHEMA.columns where Table_name='DocumentiD') BEGIN
CREATE TABLE [dbo].[DocumentiD](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IDT] [int] NOT NULL,
	[Articolo] [nvarchar](20) NOT NULL,
	[Taglia] [nvarchar](5) NOT NULL,
	[Colore] [nvarchar](5) NOT NULL CONSTRAINT [DF_DocumentiD_Colore]  DEFAULT (''),
	[Quantita] [decimal](18, 4) NOT NULL CONSTRAINT [DF_DocumentiD_Quantita]  DEFAULT ((0)),
	[Prezzo] [decimal](18, 4) NOT NULL CONSTRAINT [DF_DocumentiD_Prezzo]  DEFAULT ((0)),
	[Costo] [decimal](18, 4) NOT NULL CONSTRAINT [DF_DocumentiD_Costo]  DEFAULT ((0)),
	[Descrizione] [nvarchar](100) NULL,
	[BarCode] [nvarchar](20) NULL,
	[Marchio] [nvarchar](5) NULL,
	[Reparto] [nvarchar](5) NULL,
	[Gruppo] [nvarchar](5) NULL,
	[Famiglia] [nvarchar](5) NULL,
	[Sesso] [nvarchar](5) NULL,
	[FasciaEta] [nvarchar](5) NULL,
	[Continuativo] [nvarchar](5) NULL,
	[Anno] [nvarchar](5) NULL CONSTRAINT [DF_DocumentiD_Anno]  DEFAULT (''),
	[Stagione] [nvarchar](5) NULL,
	[UserIns] [nvarchar](50) NOT NULL,
	[DataIns] [datetime] NOT NULL,
	[UserMod] [nvarchar](50) NULL,
	[DataMod] [datetime] NULL,
 CONSTRAINT [PK_DocumentiD] PRIMARY KEY CLUSTERED 
(
	[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]
ALTER TABLE [dbo].[DocumentiD]  WITH CHECK ADD  CONSTRAINT [FK_DocumentiD_DocumentiT] FOREIGN KEY([IDT])
REFERENCES [dbo].[DocumentiT] ([ID])
ALTER TABLE [dbo].[DocumentiD] CHECK CONSTRAINT [FK_DocumentiD_DocumentiT]
END
GO

print 'modifica tabella operatore per inserimento campo Codice'
IF NOT EXISTS (select * from Information_SCHEMA.columns where Table_name='Operatore' and column_name='Codice')
ALTER TABLE dbo.Operatore ADD Codice nvarchar(50) NULL
GO

print 'modifica tabella MovimentiInventario vecchia'
IF EXISTS (select * from Information_SCHEMA.columns where Table_name='MovimentiInventario' and column_name='IDTerminale')BEGIN
EXECUTE sp_rename N'dbo.MovimentiInventario', N'MovimentiInventarioOLD'
print 'rinomino tabella MovimentiInventario'
END
GO
IF NOT EXISTS (select * from Information_SCHEMA.columns where Table_name='MovimentiInventario') BEGIN
CREATE TABLE [dbo].[MovimentiInventario](
	[IDMovInv] [int] IDENTITY(1,1) NOT NULL,
	[IDNegozio] [nvarchar](10) NOT NULL,
	[IDInventario] [int] NOT NULL,
	[DescInv] [nvarchar](255) NOT NULL CONSTRAINT [DF_MovimentiInventario_DescInv]  DEFAULT (''),
	[BarCode] [nvarchar](50) NULL,
	[Codice] [nvarchar](50) NULL,
	[TG] [nvarchar](5) NULL,
	[CL] [nvarchar](5) NULL,
	[Descrizione] [nvarchar](100) NULL,
	[Prezzo] [decimal](18, 4) NULL,
	[Quantita] [int] NULL,
	[GiacenzaInData] [int] NULL,
	[SyncCS] [nvarchar](10) NOT NULL CONSTRAINT [DF_MovimentiInventario_SyncCS]  DEFAULT (N'NEW'),
	[StagioneAcq] [nvarchar](50) NULL,
	[chkQtaOK] [bit] NOT NULL CONSTRAINT [DF_MovimentiInventario_chkQtaOK_1]  DEFAULT ((0)),
 CONSTRAINT [PK_MovimentiInventario_1] PRIMARY KEY CLUSTERED 
(
	[IDMovInv] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
print 'creo tabella MovimentiInventario'
END
GO
