132 lines
3.2 KiB
C#
132 lines
3.2 KiB
C#
using System;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using MileageTraker.Web.DAL;
|
|
using MileageTraker.Web.Models;
|
|
|
|
namespace MileageTraker.Web.Migrations
|
|
{
|
|
using System.Data.Entity.Migrations;
|
|
|
|
public partial class AddLogUser : DbMigration
|
|
{
|
|
/// <summary>
|
|
/// 0 - UserId
|
|
/// 1 - Username
|
|
/// 2 - Email
|
|
/// 3 - FullName
|
|
/// 4 - IsApproved
|
|
/// </summary>
|
|
private const string InsertUserTemplate = @"INSERT INTO [User]
|
|
([UserId]
|
|
,[Username]
|
|
,[Email]
|
|
,[FullName]
|
|
,[Password]
|
|
,[Comment]
|
|
,[IsApproved]
|
|
,[PasswordFailuresSinceLastSuccess]
|
|
,[LastPasswordFailureDate]
|
|
,[LastActivityDate]
|
|
,[LastLockoutDate]
|
|
,[LastLoginDate]
|
|
,[Created]
|
|
,[IsLockedOut]
|
|
,[LastPasswordChangedDate]
|
|
,[PasswordResetToken])
|
|
VALUES
|
|
('{0}'
|
|
,'{1}'
|
|
,'{2}'
|
|
,'{3}'
|
|
,'uninitialized_state'
|
|
,NULL
|
|
,{4}
|
|
,0
|
|
,'1753-1-1'
|
|
,'1753-1-1'
|
|
,'1753-1-1'
|
|
,'1753-1-1'
|
|
,GETDATE()
|
|
,0
|
|
,'1753-1-1'
|
|
,NULL)";
|
|
|
|
private const string InsertUserRoleDriverTemplate =
|
|
@"INSERT INTO [RoleUser]
|
|
([Role_RoleId]
|
|
,[User_UserId])
|
|
VALUES
|
|
('C070ADF5-CCA3-42B0-92E4-9E38472940DF'
|
|
,'{0}')";
|
|
|
|
private static string GetUserInsertSql(User user)
|
|
{
|
|
return string.Format(InsertUserTemplate,
|
|
user.UserId,
|
|
user.Username,
|
|
user.Email,
|
|
user.FullName,
|
|
user.IsApproved ? 1 : 0);
|
|
}
|
|
|
|
private static string GetUserInsertRoleDriverSql(User user)
|
|
{
|
|
return string.Format(InsertUserRoleDriverTemplate,
|
|
user.UserId);
|
|
}
|
|
|
|
public override void Up()
|
|
{
|
|
AddColumn("Log", "User_UserId", c => c.Guid());
|
|
AddForeignKey("Log", "User_UserId", "User", "UserId");
|
|
CreateIndex("Log", "User_UserId");
|
|
|
|
Sql(@"INSERT INTO [Role]
|
|
([RoleId]
|
|
,[RoleName]
|
|
,[Description])
|
|
VALUES
|
|
('C070ADF5-CCA3-42B0-92E4-9E38472940DF'
|
|
,'Driver'
|
|
,'Driver')");
|
|
|
|
var existingUsers = new [] {"dsloan", "ccecil"};
|
|
var appDomain = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data");
|
|
var users = UserImporter.Import(Path.Combine(appDomain, "UserEmails.xls"));
|
|
users = (from u in users
|
|
where !existingUsers.Contains(u.Username)
|
|
select u).ToList();
|
|
foreach (var user in users)
|
|
{
|
|
user.UserId = Guid.NewGuid();
|
|
Sql(GetUserInsertSql(user));
|
|
Sql(GetUserInsertRoleDriverSql(user));
|
|
}
|
|
|
|
Sql(@"UPDATE [Log]
|
|
SET [Log].User_UserId = [User].UserId
|
|
FROM [Log]
|
|
INNER JOIN [User] ON
|
|
[User].FullName = [Log].EmployeeName");
|
|
}
|
|
|
|
public override void Down()
|
|
{
|
|
DropIndex("Log", new[] { "User_UserId" });
|
|
DropForeignKey("Log", "User_UserId", "User");
|
|
DropColumn("Log", "User_UserId");
|
|
|
|
Sql(@"DELETE FROM [User]
|
|
WHERE UserId IN (
|
|
SELECT RoleUser.User_UserId
|
|
FROM RoleUser
|
|
INNER JOIN Role On RoleUser.Role_RoleId = Role.RoleId
|
|
WHERE RoleName = 'Driver'
|
|
)");
|
|
|
|
Sql(@"DELETE FROM ROLE WHERE RoleName = 'Driver'");
|
|
}
|
|
}
|
|
}
|