narsil/Sql/2016.04.11 Has Happy Hour.sql

BEGIN TRANSACTION
GO
ALTER TABLE dbo.Inventories	DROP CONSTRAINT FK8C0CFB221DECC269
GO
ALTER TABLE dbo.Inventories	DROP CONSTRAINT FK8C0CFB22EB4DE5BC
GO
ALTER TABLE dbo.Inventories	DROP CONSTRAINT FK8C0CFB223F88CAB6
GO
ALTER TABLE dbo.Inventories	DROP CONSTRAINT FK8C0CFB22DB70F42
GO
CREATE TABLE dbo.Tmp_Inventories
	(
	InventoryID uniqueidentifier NOT NULL,
	KotID uniqueidentifier NOT NULL,
	ProductID uniqueidentifier NOT NULL,
	SortOrder int NOT NULL,
	Quantity decimal(19, 5) NOT NULL,
	Price decimal(19, 5) NOT NULL,
	IsHappyHour bit NOT NULL,
	ServiceCharge decimal(19, 5) NOT NULL,
	IsScTaxable bit NOT NULL,
	ServiceTaxRate decimal(19, 5) NOT NULL,
	VatRate decimal(19, 5) NOT NULL,
	ServiceTaxID uniqueidentifier NOT NULL,
	VatID uniqueidentifier NOT NULL,
	Discount decimal(19, 5) NOT NULL,
	Amount  AS (Quantity * CASE WHEN IsHappyHour = 1 THEN 0 ELSE Price END * (1 - Discount) * CASE WHEN IsScTaxable = 1 THEN  (1 + ServiceCharge) * (1 + ServiceTaxRate + VatRate) ELSE (1 + ServiceCharge + ServiceTaxRate + VatRate) END)
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Inventories)
	 EXEC('INSERT INTO dbo.Tmp_Inventories (InventoryID, KotID, ProductID, SortOrder, Quantity, Price, IsHappyHour, ServiceCharge, IsScTaxable, ServiceTaxRate, VatRate, ServiceTaxID, VatID, Discount)
		SELECT InventoryID, KotID, ProductID, SortOrder, Quantity, CASE WHEN Price = 0 THEN FullPrice ELSE Price END, CASE WHEN Price = 0 THEN 1 ELSE 0 END, ServiceCharge, IsScTaxable, ServiceTaxRate, VatRate, ServiceTaxID, VatID, Discount FROM dbo.Inventories WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.InventoryModifiers DROP CONSTRAINT FK80820FB4BA29671A
GO
DROP TABLE dbo.Inventories
GO
EXECUTE sp_rename N'dbo.Tmp_Inventories', N'Inventories', 'OBJECT' 
GO
ALTER TABLE dbo.Inventories ADD CONSTRAINT
	PK__Inventories__0DAF0CB0 PRIMARY KEY CLUSTERED 
	(
	InventoryID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Inventories ADD CONSTRAINT
	FK8C0CFB22DB70F42 FOREIGN KEY
	(
	KotID
	) REFERENCES dbo.Kots
	(
	KotID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Inventories ADD CONSTRAINT
	FK8C0CFB223F88CAB6 FOREIGN KEY
	(
	ProductID
	) REFERENCES dbo.Products
	(
	ProductID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Inventories ADD CONSTRAINT
	FK8C0CFB221DECC269 FOREIGN KEY
	(
	ServiceTaxID
	) REFERENCES dbo.Taxes
	(
	TaxID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Inventories ADD CONSTRAINT
	FK8C0CFB22EB4DE5BC FOREIGN KEY
	(
	VatID
	) REFERENCES dbo.Taxes
	(
	TaxID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.InventoryModifiers ADD CONSTRAINT
	FK80820FB4BA29671A FOREIGN KEY
	(
	InventoryID
	) REFERENCES dbo.Inventories
	(
	InventoryID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Products	DROP CONSTRAINT FK4065562AEB4DE5BC
GO
ALTER TABLE dbo.Products	DROP CONSTRAINT FK4065562A1DECC269
GO
ALTER TABLE dbo.Products	DROP CONSTRAINT FK4065562A95976D16
GO
CREATE TABLE dbo.Tmp_Products	(
	ProductID uniqueidentifier NOT NULL,
	Code int NULL,
	Name nvarchar(255) NOT NULL,
	Units nvarchar(255) NOT NULL,
	ProductGroupID uniqueidentifier NULL,
	VatID uniqueidentifier NULL,
	ServiceTaxID uniqueidentifier NULL,
	ServiceCharge decimal(19, 5) NOT NULL,
	IsScTaxable bit NOT NULL,
	Price decimal(19, 5) NOT NULL,
	HasHappyHour bit NOT NULL,
	IsActive bit NOT NULL,
	IsNotAvailable bit NOT NULL,
	SortOrder int NOT NULL,
	Quantity decimal(19, 5) NOT NULL
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Products)
	 EXEC('INSERT INTO dbo.Tmp_Products (ProductID, Code, Name, Units, ProductGroupID, VatID, ServiceTaxID, ServiceCharge, IsScTaxable, Price, HasHappyHour, IsActive, IsNotAvailable, SortOrder, Quantity)
		SELECT ProductID, Code, Name, Units, ProductGroupID, VatID, ServiceTaxID, ServiceCharge, IsScTaxable, Price, CASE WHEN Price = 0 AND FullPrice != 0 THEN 1 ELSE 0 END, IsActive, IsNotAvailable, SortOrder, Quantity FROM dbo.Products WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.Inventories	DROP CONSTRAINT FK8C0CFB223F88CAB6
GO
DROP TABLE dbo.Products
GO
EXECUTE sp_rename N'dbo.Tmp_Products', N'Products', 'OBJECT' 
GO
ALTER TABLE dbo.Products ADD CONSTRAINT
	PK__Products__164452B1 PRIMARY KEY CLUSTERED 
	(
	ProductID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Products ADD CONSTRAINT
	UQ__Products__173876EA UNIQUE NONCLUSTERED 
	(
	Name,
	Units
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Products ADD CONSTRAINT
	FK4065562A95976D16 FOREIGN KEY
	(
	ProductGroupID
	) REFERENCES dbo.ProductGroups
	(
	ProductGroupID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Products ADD CONSTRAINT
	FK4065562AEB4DE5BC FOREIGN KEY
	(
	VatID
	) REFERENCES dbo.Taxes
	(
	TaxID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Products ADD CONSTRAINT
	FK4065562A1DECC269 FOREIGN KEY
	(
	ServiceTaxID
	) REFERENCES dbo.Taxes
	(
	TaxID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Inventories ADD CONSTRAINT
	FK8C0CFB223F88CAB6 FOREIGN KEY
	(
	ProductID
	) REFERENCES dbo.Products
	(
	ProductID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT