138 lines
6.2 KiB
Transact-SQL
138 lines
6.2 KiB
Transact-SQL
BEGIN TRANSACTION
|
|
GO
|
|
ALTER TABLE Auth_Groups ALTER COLUMN Name nvarchar(255) NOT NULL
|
|
GO
|
|
ALTER TABLE dbo.Auth_Groups ADD CONSTRAINT UQ_Auth_Groups__6A3BB341 UNIQUE NONCLUSTERED (Name)ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE dbo.Auth_RoleGroups ADD CONSTRAINT UQ_Auth_RoleGroups UNIQUE NONCLUSTERED (RoleID, GroupID)ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE Auth_Roles ALTER COLUMN Name nvarchar(255) NOT NULL
|
|
GO
|
|
ALTER TABLE dbo.Auth_Roles ADD CONSTRAINT UQ_Auth_Roles UNIQUE NONCLUSTERED (Name)ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE dbo.Auth_UserGroups ADD CONSTRAINT UQ_Auth_UserGroups UNIQUE NONCLUSTERED (UserID, GroupID)ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE Auth_Users ALTER COLUMN Name nvarchar(255) NOT NULL
|
|
GO
|
|
ALTER TABLE dbo.Auth_Users ADD CONSTRAINT UQ_Auth_Users UNIQUE NONCLUSTERED (Name)ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE Entities_FoodTables ALTER COLUMN Name nvarchar(255) NOT NULL
|
|
GO
|
|
ALTER TABLE dbo.Entities_FoodTables ADD CONSTRAINT UQ_Entities_FoodTables UNIQUE NONCLUSTERED (Name)ON [PRIMARY]
|
|
GO
|
|
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FKE67F888598906505]') AND parent_object_id = OBJECT_ID(N'[dbo].[Entities_Vouchers]'))
|
|
ALTER TABLE [dbo].[Entities_Vouchers] DROP CONSTRAINT [FKE67F888598906505]
|
|
GO
|
|
EXECUTE sp_rename N'dbo.Entities_Vouchers.SettledID', N'Settled', 'COLUMN'
|
|
GO
|
|
--delete from entities_inventorymodifiers where inventorymodifierid in (22,23, 39, 103, 104, 174, 175, 177, 229, 272, 441, 487, 488, 364)
|
|
--GO
|
|
--ALTER TABLE dbo.Entities_InventoryModifiers ADD CONSTRAINT UQ_Entities_InventoryModifiers UNIQUE NONCLUSTERED (InventoryID, ModifierID)ON [PRIMARY]
|
|
--GO
|
|
CREATE TABLE [dbo].[Entities_Kots](
|
|
[KotID] [int] IDENTITY(1,1) NOT NULL,
|
|
[Code] [nvarchar](255) NULL,
|
|
[TableID] [nvarchar](255) NULL,
|
|
[Printed] [bit] NULL,
|
|
[Date] [datetime] NULL,
|
|
[VoucherID] [int] NULL,
|
|
[UserID] [int] NULL,
|
|
CONSTRAINT [PK__Entities_Kots] PRIMARY KEY CLUSTERED
|
|
(KotID ASC)ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Kots] WITH CHECK ADD CONSTRAINT [FK7785BB6998E59CD2] FOREIGN KEY([VoucherID])
|
|
REFERENCES [dbo].[Entities_Vouchers] ([VoucherID])
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Kots] CHECK CONSTRAINT [FK7785BB6998E59CD2]
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Kots] WITH CHECK ADD CONSTRAINT [FK7785BB69B9E9332A] FOREIGN KEY([UserID])
|
|
REFERENCES [dbo].[Auth_Users] ([UserID])
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Kots] CHECK CONSTRAINT [FK7785BB69B9E9332A]
|
|
GO
|
|
ALTER TABLE Entities_Modifiers ALTER COLUMN Name nvarchar(255) NOT NULL
|
|
GO
|
|
ALTER TABLE dbo.Entities_Modifiers ADD CONSTRAINT UQ_Entities_Modifiers UNIQUE NONCLUSTERED (Name)ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE Entities_Products ALTER COLUMN Name nvarchar(255) NOT NULL
|
|
GO
|
|
ALTER TABLE Entities_Vouchers DROP COLUMN discriminator
|
|
GO
|
|
ALTER TABLE Entities_Vouchers DROP COLUMN Code
|
|
GO
|
|
ALTER TABLE Entities_Vouchers DROP COLUMN Ref
|
|
GO
|
|
ALTER TABLE Entities_Vouchers DROP COLUMN Type
|
|
GO
|
|
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FKE67F8885A819A402]') AND parent_object_id = OBJECT_ID(N'[dbo].[Entities_Vouchers]'))
|
|
ALTER TABLE [dbo].[Entities_Vouchers] DROP CONSTRAINT [FKE67F8885A819A402]
|
|
GO
|
|
ALTER TABLE Entities_Vouchers DROP COLUMN PaymentGroupID
|
|
GO
|
|
SET IDENTITY_INSERT dbo.Entities_Kots ON
|
|
GO
|
|
INSERT INTO Entities_Kots(KotID, Code, TableID, Printed, Date, VoucherID, UserID)
|
|
SELECT VoucherID, 'S-' + CAST(VoucherID AS nvarchar(200)), TableID, 1, CreationDate, VoucherID, UserID from entities_Vouchers
|
|
GO
|
|
SET IDENTITY_INSERT dbo.Entities_Kots OFF
|
|
GO
|
|
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK695304F8BA29671A]') AND parent_object_id = OBJECT_ID(N'[dbo].[Entities_InventoryModifiers]'))
|
|
ALTER TABLE [dbo].[Entities_InventoryModifiers] DROP CONSTRAINT [FK695304F8BA29671A]
|
|
GO
|
|
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK53E4F7FE3F88CAB6]') AND parent_object_id = OBJECT_ID(N'[dbo].[Entities_Inventories]'))
|
|
ALTER TABLE [dbo].[Entities_Inventories] DROP CONSTRAINT [FK53E4F7FE3F88CAB6]
|
|
GO
|
|
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK53E4F7FEDB70F42]') AND parent_object_id = OBJECT_ID(N'[dbo].[Entities_Inventories]'))
|
|
ALTER TABLE [dbo].[Entities_Inventories] DROP CONSTRAINT [FK53E4F7FEDB70F42]
|
|
GO
|
|
CREATE TABLE [dbo].[Temp_Entities_Inventories](
|
|
[InventoryID] [int] IDENTITY(1,1) NOT NULL,
|
|
[Quantity] [decimal](19, 5) NULL,
|
|
[Rate] [decimal](19, 5) NULL,
|
|
[Tax] [decimal](19, 5) NULL,
|
|
[Discount] [decimal](19, 5) NULL,
|
|
[ServiceCharge] [decimal](19, 5) NULL,
|
|
[Amount] AS (((([Quantity]*[Rate])*((1)-[Discount]))*((1)+[ServiceCharge]))*((1)+[Tax])),
|
|
[KotID] [int] NULL,
|
|
[ProductID] [int] NULL,
|
|
CONSTRAINT [PK__Entities_Inventories] PRIMARY KEY CLUSTERED
|
|
([InventoryID] ASC) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
GO
|
|
SET IDENTITY_INSERT dbo.Temp_Entities_Inventories ON
|
|
GO
|
|
IF EXISTS(SELECT * FROM dbo.Entities_Inventories)
|
|
EXEC('INSERT INTO Temp_Entities_Inventories (InventoryID, Quantity, Rate, Tax, Discount, ServiceCharge, KotID, ProductID)
|
|
SELECT InventoryID, Quantity, Rate, Tax, Discount, ServiceCharge, VoucherID, ProductID FROM Entities_Inventories WITH (HOLDLOCK TABLOCKX)')
|
|
GO
|
|
SET IDENTITY_INSERT dbo.Temp_Entities_Inventories OFF
|
|
GO
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Entities_Inventories]') AND type in (N'U'))
|
|
DROP TABLE [dbo].[Entities_Inventories]
|
|
GO
|
|
EXECUTE sp_rename N'dbo.Temp_Entities_Inventories', N'Entities_Inventories', 'OBJECT'
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Inventories] WITH CHECK ADD CONSTRAINT [FK53E4F7FE3F88CAB6] FOREIGN KEY([ProductID])
|
|
REFERENCES [dbo].[Entities_Products] ([ProductID])
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Inventories] CHECK CONSTRAINT [FK53E4F7FE3F88CAB6]
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Inventories] WITH CHECK ADD CONSTRAINT [FK53E4F7FEDB70F42] FOREIGN KEY([KotID])
|
|
REFERENCES [dbo].[Entities_Kots] ([KotID])
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_Inventories] CHECK CONSTRAINT [FK53E4F7FEDB70F42]
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_InventoryModifiers] WITH CHECK ADD CONSTRAINT [FK695304F8BA29671A] FOREIGN KEY([InventoryID])
|
|
REFERENCES [dbo].[Entities_Inventories] ([InventoryID])
|
|
GO
|
|
ALTER TABLE [dbo].[Entities_InventoryModifiers] CHECK CONSTRAINT [FK695304F8BA29671A]
|
|
GO
|
|
INSERT INTO Auth_Roles(Name) VALUES ('Sales/MergeTable')
|
|
INSERT INTO Auth_Roles(Name) VALUES ('Sales/MergeKot')
|
|
INSERT INTO Auth_Roles(Name) VALUES ('Sales/MoveKot')
|
|
GO
|
|
COMMIT TRANSACTION
|
|
|
|
select * from auth_roles order by roleid |