public override void Up()
{
AddColumn("dbo.ReservationOrders", "Table_Id", c => c.Int());
CreateIndex("dbo.ReservationOrders", "Table_Id");
AddForeignKey("dbo.ReservationOrders", "Table_Id", "dbo.Tables", "Id");
Sql(DbMigrationExtensions.CreateViewQuery("ReservationsView",
@"SELECT r.Id AS ReservationId, r.UnlockCode, u.Login AS ClientLogin, r.Created, r.ReservationTime, r.Status, r.Comment, rmiq.Quantity, mi.Title AS MenuItem, r.Table_Id AS TableId, t.Title AS TableTitle
FROM ReservationOrders r
LEFT JOIN WebClients wc ON wc.Id = r.Client_Id
LEFT JOIN Users u ON u.Id = wc.User_Id
LEFT JOIN ReservationMenuItemQuantities rmiq ON rmiq.ReservationOrder_Id=r.Id
LEFT JOIN MenuItems mi ON mi.Id = rmiq.Item_Id
LEFT JOIN Tables t ON t.Id = r.Table_Id"));
}
public override void Up()
{
Sql(DbMigrationExtensions.CreateViewQuery("AuthenticatedUsersView", string.Format(
@"SELECT u.Id AS [UserId], u.Login AS [Login], {0} AS [Type], a.UserToken AS [Token]
FROM dbo.Tables t
JOIN Users u ON t.User_Id = u.Id
JOIN activeUsers a ON a.UserId = u.CommonId
WHERE u.IsNewest=1 AND u.IsDeleted=0 AND a.TokenCreation >= DateAdd(DAY,-1,GETDATE())
UNION
SELECT u.Id AS [UserId], u.Login AS [Login], {1} AS [Type], a.UserToken AS [Token]
FROM dbo.Waiters w
JOIN Users u ON w.User_Id = u.Id
JOIN activeUsers a ON a.UserId = u.CommonId
WHERE u.IsNewest=1 AND u.IsDeleted=0 AND a.TokenCreation >= DateAdd(DAY,-1,GETDATE())"
, (int)UserType.Table, (int)UserType.Waiter)));
}
public override void Up()
{
AddColumn("dbo.Orders", "Waiter_Id", c => c.Int());
CreateIndex("dbo.Orders", "Waiter_Id");
AddForeignKey("dbo.Orders", "Waiter_Id", "dbo.Waiters", "Id");
Sql(DbMigrationExtensions.CreateViewQuery("OrdersView", @"
SELECT o.Id AS OrderId, o.Created, o.Status, o.Comment, SUM(miq.Quantity) AS Quantity,
mi.Title AS MenuItem, t.Id as TableId, t.Title as TableTitle, w.Id as WaiterId, u.Login AS WaiterLogin
FROM Orders o JOIN MenuItemsQuantities miq ON o.Id = miq.Order_Id
JOIN MenuItems mi ON miq.Item_Id = mi.Id
JOIN Tables t ON o.Id = t.Id
LEFT JOIN Waiters w ON o.Id = w.Id
LEFT JOIN Users u ON w.User_Id = u.Id
GROUP BY o.Id, o.Id, o.Created, o.Status, o.Comment, mi.Title, t.Id, t.Title, w.Id, u.Login"));
}
public override void Down()
{
AddColumn("dbo.Waiters", "Login", c => c.String());
AddColumn("dbo.Users", "UserId", c => c.Guid(nullable: false));
AddColumn("dbo.Tables", "Login", c => c.String());
DropForeignKey("dbo.Waiters", "User_Id", "dbo.Users");
DropForeignKey("dbo.Tables", "User_Id", "dbo.Users");
DropIndex("dbo.Waiters", new[] { "User_Id" });
DropIndex("dbo.Tables", new[] { "User_Id" });
DropColumn("dbo.Waiters", "User_Id");
DropColumn("dbo.Users", "Login");
DropColumn("dbo.Tables", "User_Id");
Sql(DbMigrationExtensions.CreateViewQuery("UsersView",
"SELECT [Id], [CommonId], [UserId], [SecondHash] From Users WHERE IsNewest=1 AND IsDeleted=0"));
Sql(DbMigrationExtensions.AlterViewQuery("TablesView",
"SELECT [Id] as TableId, [CommonId] as TableGuid, [Title], [Description], [Login] From Tables WHERE IsNewest=1 AND IsDeleted=0"));
Sql(DbMigrationExtensions.AlterViewQuery("WaitersView",
"SELECT [Id] as WaiterId, [CommonId] as WaiterGuid, [FirstName], [LastName], [Login] From Waiters WHERE IsNewest=1 AND IsDeleted=0"));
}
public override void Up()
{
CreateTable(
"dbo.Waiters",
c => new
{
Id = c.Int(nullable: false, identity: true),
FirstName = c.String(),
LastName = c.String(),
CommonId = c.Guid(nullable: false),
Created = c.DateTime(nullable: false),
Modified = c.DateTime(),
Version = c.Int(nullable: false),
IsNewest = c.Boolean(nullable: false),
IsDeleted = c.Boolean(nullable: false),
})
.PrimaryKey(t => t.Id);
Sql(DbMigrationExtensions.CreateViewQuery("WaitersView",
"SELECT [Id] as WaiterId, [CommonId] as WaiterGuid, [FirstName], [LastName] From Waiters WHERE IsNewest=1 AND IsDeleted=0"));
}
public override void Up()
{
Sql(DbMigrationExtensions.AlterViewQuery("AuthenticatedUsersView",
@"SELECT u.Id AS [UserId], u.Login AS [Login], 1 AS [Type], a.UserToken AS [Token]
FROM dbo.Tables t JOIN
Users u ON t .User_Id = u.Id JOIN
activeUsers a ON a.UserId = u.CommonId
WHERE u.IsNewest = 1 AND u.IsDeleted = 0 AND a.TokenCreation >= DateAdd(DAY, - 1, GETDATE())
UNION
SELECT u.Id AS [UserId], u.Login AS [Login], 2 AS [Type], a.UserToken AS [Token]
FROM dbo.Waiters w JOIN
Users u ON w.User_Id = u.Id JOIN
activeUsers a ON a.UserId = u.CommonId
WHERE u.IsNewest = 1 AND u.IsDeleted = 0 AND a.TokenCreation >= DateAdd(DAY, - 1, GETDATE())
UNION
SELECT u.Id AS [UserId], u.Login AS [Login], 3 AS [Type], a.UserToken AS [Token]
FROM dbo.WebClients w JOIN
Users u ON w.User_Id = u.Id JOIN
activeUsers a ON a.UserId = u.CommonId
WHERE u.IsNewest = 1 AND u.IsDeleted = 0 AND a.TokenCreation >= DateAdd(DAY, - 1, GETDATE())"));
}
public override void Up()
{
AddColumn("dbo.Tables", "User_Id", c => c.Int());
AddColumn("dbo.Users", "Login", c => c.String());
AddColumn("dbo.Waiters", "User_Id", c => c.Int());
CreateIndex("dbo.Tables", "User_Id");
CreateIndex("dbo.Waiters", "User_Id");
AddForeignKey("dbo.Tables", "User_Id", "dbo.Users", "Id");
AddForeignKey("dbo.Waiters", "User_Id", "dbo.Users", "Id");
DropColumn("dbo.Tables", "Login");
DropColumn("dbo.Users", "UserId");
DropColumn("dbo.Waiters", "Login");
Sql(DbMigrationExtensions.DropViewQuery("dbo.UsersView"));
Sql(DbMigrationExtensions.AlterViewQuery("dbo.TablesView",
@"SELECT t.[Id] AS [TableId], t.[CommonId] AS [TableGuid], [Title], [Description], u.[Login], u.[SecondHash], u.[CommonId] AS [UserId]
FROM [dbo].[Tables] t join [dbo].[Users] u on t.[User_Id] = u.[Id] WHERE t.[IsNewest]=1 AND t.[IsDeleted]=0" ));
Sql(DbMigrationExtensions.AlterViewQuery("dbo.WaitersView",
"SELECT w.[Id] AS [WaiterId], w.[CommonId] AS [WaiterGuid], [FirstName], [LastName], u.[Login], u.[SecondHash], u.[CommonId] AS [UserId] FROM [dbo].[Waiters] w join [dbo].[Users] u on w.User_Id = u.Id WHERE w.[IsNewest]=1 AND w.[IsDeleted]=0"));
}
public override void Up()
{
CreateTable(
"dbo.MenuItems",
c => new
{
Id = c.Int(nullable: false, identity: true),
Title = c.String(),
Description = c.String(),
CommonId = c.Guid(nullable: false),
Created = c.DateTime(nullable: false),
Modified = c.DateTime(),
Version = c.Int(nullable: false),
IsNewest = c.Boolean(nullable: false),
IsDeleted = c.Boolean(nullable: false),
Category_Id = c.Int(),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Categories", t => t.Category_Id)
.Index(t => t.Category_Id);
Sql(DbMigrationExtensions.CreateViewQuery("MenuItemsView",
"SELECT m.[Id] as MenuItemId, m.[CommonId] as MenuItemGuid, m.[Title], m.[Description], c.[Id] as CategoryId, c.[Title] as CategoryTitle From MenuItems m JOIN Categories c ON m.Category_Id = c.Id WHERE m.IsNewest=1 AND m.IsDeleted=0 AND c.IsDeleted=0"));
}
public override void Down()
{
DropTable("dbo.Waiters");
Sql(DbMigrationExtensions.DropViewQuery("WaitersView"));
}
public override void Down()
{
Sql(DbMigrationExtensions.DropViewQuery("AuthenticatedUsersView"));
}
public override void Down()
{
Sql(DbMigrationExtensions.DropViewQuery("TablesView"));
Sql(DbMigrationExtensions.DropViewQuery("WaitersView"));
}
public override void Down()
{
Sql(DbMigrationExtensions.DropViewQuery("WebClientsView"));
}
public override void Up()
{
Sql(DbMigrationExtensions.CreateViewQuery("CategoriesView",
"SELECT [Id] as CategoryId, [CommonId] as CategoryGuid, [Title], [Description] From Categories WHERE IsNewest=1 AND IsDeleted=0"));
}
public override void Down()
{
Sql(DbMigrationExtensions.DropViewQuery("CategoriesView"));
}
