102 lines
2.8 KiB
Transact-SQL
102 lines
2.8 KiB
Transact-SQL
BEGIN TRANSACTION
|
|
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,
|
|
FullPrice decimal(19, 5) 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 (case when [IsScTaxable]=(1) then ((([Quantity]*[Price])*((1)-[Discount]))*((1)+[ServiceCharge]))*(((1)+[ServiceTaxRate])+[VatRate]) else (([Quantity]*[Price])*((1)-[Discount]))*((((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, FullPrice, ServiceCharge, IsScTaxable, ServiceTaxRate, VatRate, ServiceTaxID, VatID, Discount)
|
|
SELECT InventoryID, KotID, ProductID, 0, Quantity, Price, FullPrice, 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
|
|
COMMIT
|