USE Gets;
BEGIN TRANSACTION
ALTER TABLE Entities_Waiters ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_FoodTables ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Customers ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Auth_Groups ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Modifiers ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Auth_Roles ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_ProductGroups ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Taxes ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Auth_Users ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Vouchers ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Kots ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Products ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Inventories ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_InventoryModifiers ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Auth_RoleGroups ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Auth_UserGroups ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_ProductGroupModifiers ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_PrintLocations ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_Reprints ADD ID uniqueidentifier not null DEFAULT newid();
ALTER TABLE Entities_VoucherSettlements ADD ID uniqueidentifier not null DEFAULT newid();
GO
UPDATE Entities_Customers SET ID = '2C716F4B-0736-429A-AD51-610D7C47CB5E' WHERE CustomerID = 1;
UPDATE Entities_Waiters SET ID = '3ECB3BCE-CBBA-461C-BC16-5823FC075AAA' WHERE WaiterID = 1;
DELETE FROM Auth_RoleGroups WHERE RoleID IN (1,2);
DELETE FROM Auth_Roles WHERE RoleID IN (1,2);
UPDATE Auth_Roles SET Name = 'Change Rate' WHERE RoleID = 3;
UPDATE Auth_Roles SET Name = 'Cashier Checkout' WHERE RoleID = 4;
UPDATE Auth_Roles SET Name = 'Customers' WHERE RoleID = 5;
UPDATE Auth_Roles SET Name = 'Discount' WHERE RoleID = 6;
UPDATE Auth_Roles SET Name = 'Edit Printed Bill' WHERE RoleID = 7;
UPDATE Auth_Roles SET Name = 'Edit Printed Product' WHERE RoleID = 8;
UPDATE Auth_Roles SET Name = 'Print Bill' WHERE RoleID = 9;
UPDATE Auth_Roles SET Name = 'Print Kot' WHERE RoleID = 10;
UPDATE Auth_Roles SET Name = 'Sales' WHERE RoleID = 11;
UPDATE Auth_Roles SET Name = 'Sales Detail' WHERE RoleID = 12;
UPDATE Auth_Roles SET Name = 'Void Bill' WHERE RoleID = 13;
UPDATE Auth_Roles SET Name = 'NC Product' WHERE RoleID = 14;
UPDATE Auth_Roles SET Name = 'Settle Bill' WHERE RoleID = 15;
UPDATE Auth_Roles SET Name = 'Products' WHERE RoleID = 16;
UPDATE Auth_Roles SET Name = 'Users' WHERE RoleID = 17;
UPDATE Auth_Roles SET Name = 'Roles' WHERE RoleID = 18;
DELETE FROM Auth_Rolegroups WHERE RoleID = 19;
DELETE FROM Auth_Roles WHERE RoleID = 19;
UPDATE Auth_Roles SET Name = 'Owner' WHERE RoleID = 20;
UPDATE Auth_Roles SET Name = 'Sales Analysis' WHERE RoleID = 21;
UPDATE Auth_Roles SET Name = 'Move Table' WHERE RoleID = 22;
UPDATE Auth_Roles SET Name = 'Merge Tables' WHERE RoleID = 23;
UPDATE Auth_Roles SET Name = 'Merge Kots' WHERE RoleID = 24;
UPDATE Auth_Roles SET Name = 'Move Kot to New Table' WHERE RoleID = 25;
UPDATE Auth_Roles SET Name = 'Bill Details' WHERE RoleID = 26;
UPDATE Auth_Roles SET Name = 'Open Bill' WHERE RoleID = 27;
COMMIT
GO
USE Sales
BEGIN TRANSACTION
INSERT INTO Waiters (WaiterID, Name)
SELECT ID, Name from Gets.dbo.Entities_Waiters;
INSERT INTO Customers (CustomerID, Name, Address, Important, Phone, Remarks)
SELECT ID, Name, Address, Important, Phone, Remarks from Gets.dbo.Entities_Customers;
INSERT INTO Auth_Groups (GroupID, Name)
SELECT ID, Name from Gets.dbo.Auth_Groups;
INSERT INTO Modifiers (ModifierID, Name, ShowInBill, Price)
SELECT ID, Name, 0, 0 from Gets.dbo.Entities_Modifiers;
INSERT INTO Auth_Roles (RoleID, Name)
SELECT ID, Name from Gets.dbo.Auth_Roles;
INSERT INTO ProductGroups (ProductGroupID, Name, DiscountLimit, IsModifierCompulsory, IsActive, SortOrder, GroupType)
SELECT ID, Name, DiscountLimit, IsModifierCompulsory, CASE Discontinued WHEN 0 THEN 1 ELSE 0 END, SortOrder, GroupType from Gets.dbo.Entities_ProductGroups;
INSERT INTO Taxes (TaxID, Name, Rate)
SELECT ID, Name, Rate from Gets.dbo.Entities_Taxes;
INSERT INTO Auth_Users (UserID, Name, MsrString, Password, LockedOut)
SELECT ID, Name, MsrString, Password, LockedOut from Gets.dbo.Auth_Users;

INSERT INTO FoodTables (FoodTableID, Name, IsActive, Location, Status, VoucherID, SortOrder)
SELECT ID, Name, 1, Location, null, null, FoodTableID
from Gets.dbo.Entities_FoodTables as ft;

INSERT INTO Vouchers (VoucherID, Date, Pax, UserID, CreationDate, LastEditDate, BillID, TableID, WaiterID, CustomerID, Narration, Void, VoidReason, Printed, VoucherType, KotID)
SELECT ID, Date, Pax, 
(SELECT u.ID FROM Gets.dbo.Auth_Users u WHERE u.UserID = v.UserID), 
CreationDate, LastEditDate, CASE WHEN BillID LIKE 'K-%' THEN null ELSE CAST(REPLACE(REPLACE(REPLACE(BillID, '-', ''), 'NC', ''), 'ST','') AS int) END, 
(SELECT t.ID FROM Gets.dbo.Entities_FoodTables t WHERE t.Name = v.TableID), 
(SELECT w.ID FROM Gets.dbo.Entities_Waiters w WHERE w.WaiterID = v.WaiterID), 
(SELECT c.ID FROM Gets.dbo.Entities_Customers c WHERE c.CustomerID = v.CustomerID), 
Narration, Void, VoidReason, Printed, VoucherType, CAST(REPLACE(KotID, 'K-', '') AS int)
from Gets.dbo.Entities_Vouchers as V;

INSERT INTO Kots (KotID, VoucherID, Code, TableID, Printed, Date, UserID)
SELECT ID, 
(SELECT v.ID FROM Gets.dbo.Entities_Vouchers v WHERE v.VoucherID = k.VoucherID), 
CAST(REPLACE(Code, 'S-', '') AS int),
(SELECT t.ID FROM Gets.dbo.Entities_FoodTables t WHERE t.Name = k.TableID),
Printed, Date,
(SELECT u.ID FROM Gets.dbo.Auth_Users u WHERE u.UserID = k.UserID)
from Gets.dbo.Entities_Kots AS k;

INSERT INTO Products (ProductID, Code, Name, Units, ProductGroupID, VatID, ServiceTaxID, ServiceCharge, IsScTaxable, Price, FullPrice, IsActive, IsNotAvailable, SortOrder, BaseCode, Quantity)
SELECT ID, Code, Name, Units, 
(SELECT pg.ID FROM Gets.dbo.Entities_ProductGroups pg WHERE pg.ProductGroupID = p.ProductGroupID), 
(SELECT vat.ID FROM Gets.dbo.Entities_Taxes vat WHERE vat.TaxID = p.VatID), 
(SELECT st.ID FROM Gets.dbo.Entities_Taxes st WHERE st.TaxID = p.ServiceTaxID), 
ServiceCharge, IsScTaxable, Price, FullPrice, CASE Discontinued WHEN 0 THEN 1 ELSE 0 END, 0, SortOrder, BaseCode, Quantity
from Gets.dbo.Entities_Products AS p;

INSERT INTO Inventories (InventoryID, KotID, ProductID, SortOrder, Quantity, Price, FullPrice, ServiceCharge, IsScTaxable, ServiceTaxRate, VatRate, ServiceTaxID, VatID, Discount)
SELECT ID, 
(SELECT k.ID FROM Gets.dbo.Entities_Kots k WHERE k.KotID = i.KotID), 
(SELECT p.ID FROM Gets.dbo.Entities_Products p WHERE p.ProductID = i.ProductID), 
0, Quantity, Price, FullPrice, ServiceCharge, IsScTaxable, ServiceTax, Vat, 
(SELECT s.ID FROM Gets.dbo.Entities_Taxes s WHERE s.Rate = i.ServiceTax), 
(SELECT v.ID FROM Gets.dbo.Entities_Taxes v WHERE v.Rate = i.Vat), 
Discount
from Gets.dbo.Entities_Inventories as i;

INSERT INTO InventoryModifiers (InventoryModifierID, InventoryID, ModifierID)
SELECT ID, 
(SELECT i.ID FROM Gets.dbo.Entities_Inventories i WHERE i.InventoryID = im.InventoryID), 
(SELECT m.ID FROM Gets.dbo.Entities_Modifiers m WHERE m.ModifierID = im.ModifierID)
from Gets.dbo.Entities_InventoryModifiers as im;

INSERT INTO Auth_RoleGroups (RoleGroupID, RoleID, GroupID)
SELECT ID, 
(SELECT r.ID FROM Gets.dbo.Auth_Roles r WHERE r.RoleID = rg.RoleID),
(SELECT g.ID FROM Gets.dbo.Auth_Groups g WHERE g.GroupID = rg.GroupID)
from Gets.dbo.Auth_RoleGroups as rg;

INSERT INTO Auth_UserGroups (UserGroupID, UserID, GroupID)
SELECT ID, 
(SELECT u.ID FROM Gets.dbo.Auth_Users u WHERE u.UserID = ug.UserID), 
(SELECT g.ID FROM Gets.dbo.Auth_Groups g WHERE g.GroupID = ug.GroupID)
from Gets.dbo.Auth_UserGroups as ug;

INSERT INTO ProductGroupModifiers (ProductGroupModifierID, ProductGroupID, ModifierID, ShowAutomatically)
SELECT ID, 
(SELECT pg.ID FROM Gets.dbo.Entities_ProductGroups pg WHERE pg.ProductGroupID = pgm.ProductGroupID), 
(SELECT m.ID FROM Gets.dbo.Entities_Modifiers m WHERE m.ModifierID = pgm.ModifierID),
ShowAutomatically
from Gets.dbo.Entities_ProductGroupModifiers as pgm;

INSERT INTO PrintLocations (PrintLocationID, ProductGroupID, Location, Printer, Copies, CutCode)
SELECT ID, 
(SELECT pg.ID FROM Gets.dbo.Entities_ProductGroups pg WHERE pg.ProductGroupID = pl.ProductGroupID), 
Location, Printer, Copies, CutCode
from Gets.dbo.Entities_PrintLocations as pl;

INSERT INTO Reprints (ReprintID, UserID, Date, VoucherID)
SELECT ID, 
(SELECT u.ID FROM Gets.dbo.Auth_Users u WHERE u.UserID = r.UserID),
 Date, 
(SELECT v.ID FROM Gets.dbo.Entities_Vouchers v WHERE v.VoucherID = r.VoucherID)
from Gets.dbo.Entities_Reprints as r;

INSERT INTO VoucherSettlements (VoucherSettlementID, VoucherID, Settled, Amount)
SELECT ID, 
(SELECT v.ID FROM Gets.dbo.Entities_Vouchers v WHERE v.VoucherID = s.VoucherID),
Settled, Amount
from Gets.dbo.Entities_VoucherSettlements as s;

UPDATE FoodTables
SET    Status = ft.Status,
       VoucherID = (SELECT v.ID FROM Gets.dbo.Entities_Vouchers v WHERE v.VoucherID = ft.VoucherID)
FROM   Gets.dbo.Entities_FoodTables as ft
WHERE  FoodTables.FoodTableID = ft.ID;
INSERT INTO Auth_Roles (RoleID, Name) VALUES(Newid(), 'Modifiers')
INSERT INTO Auth_Roles (RoleID, Name) VALUES(Newid(), 'Tables')

COMMIT