DB Migration For Vinoth - MuraliM/Utils GitHub Wiki

Application Users

Source Tables

[dbo].[user_Users]

---

1. [uuId] [INT, NOT NULL] - Identity column for user Id
2. [uuUsername] [nvarchar][60] NULL - User name column- allows NULL
3. [uuFName] [nvarchar][60] NULL - First Name column - allows NULL
4. [uuLName] [nvarchar][60] NULL - Last Name column - allows NULL
5. [uuEmail] [nvarchar][60] NULL - Email , allows NULL
6. [uuIsDoctor] [smallint] NULL - Indicates whether a user is a doctor
7. [uuIsActive] [smallint] NULL - Its a active user
8. [uuCode] [nvarchar][50] NULL - Code for the user. Not sure where it is used
9. [uuSuspended] [int] NULL - Indicates user is deactivated
10. [uuSort] [int] NULL - Not sure what is the use of this
11. [uuHidden] [int] NULL - Not sure what is the use of this
12. [uuShowOverview] [int] NULL - Show overview dashboard to user

[dbo].[user_Logins]

1. [ulId] [int] IDENTITY(1,1) NOT NULL - Identity column
2. [uuId] [int] NULL - Refers the uuId column in user_Users table
3. [ulIsAdmin] [smallint] NULL - Indicates user is admin user
4. [ulPassword] nvarchar(60) NULL - Password for the user

---

Target Tables

[dbo].[Person]

[PersonId] [bigint] IDENTITY(1,1) NOT NULL,
[TenantId] [bigint] NOT NULL,
[FirstName] [nvarchar](100) NOT NULL,
[LastName] [nvarchar](100) NOT NULL,
[Gender] [nvarchar](15) NULL,
[PersonNumber] [nvarchar](20) NULL,
[DOB] [smalldatetime] NULL,
[MobileNumber] [nvarchar](20) NULL,
[EmailId] [nvarchar](100) NULL,
[Address] [nvarchar](255) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[ZipCode] [nvarchar](20) NULL,
[Country] [nvarchar](50) NULL,
[IsDeleted] [bit] NULL,
[AddedBy] [bigint] NULL,
[AddedDateTime] [smalldatetime] NULL,
[UpdatedBy] [bigint] NULL,
[UpdatedDateTime] [smalldatetime] NULL,
[PersonImage] [nvarchar](255) NULL,

[dbo].[Employee]

[EmployeeId] [bigint] IDENTITY(1,1) NOT NULL,
[RoleId] [int] NOT NULL,
[TenantId] [bigint] NOT NULL,
[PersonId] [bigint] NOT NULL,
[Designation] [nvarchar](50) NOT NULL,
[Description] [nvarchar](255) NULL,
[IsDeleted] [bit] NULL,
[AddedBy] [bigint] NOT NULL,
[AddedDateTime] [smalldatetime] NOT NULL,
[UpdatedBy] [bigint] NULL,
[UpdatedDateTime] [smalldatetime] NULL,
[EmployeeType] [tinyint] NULL,
[ShowInDashboard] [bit] NULL,
[CanViewOthers] [bit] NULL,

[dbo].[ApplicationUser]

[UserId] [bigint] IDENTITY(1,1) NOT NULL,
[TenantId] [bigint] NOT NULL,
[PersonId] [bigint] NULL,
[Username] [nvarchar](100) NOT NULL,
[Password] [nvarchar](200) NOT NULL,
[EmailId] [nvarchar](100) NOT NULL,
[LastLoginDate] [smalldatetime] NULL,
[FailedLoginDate] [smalldatetime] NULL,
[IsActive] [bit] NULL,
[IsAccountLocked] [bit] NULL,
[FailedLoginAttempt] [int] NULL,
[IsDeleted] [bit] NULL,
[AddedBy] [bigint] NULL,
[AddedDateTime] [smalldatetime] NULL,
[UpdatedBy] [bigint] NULL,
[UpdatedDateTime] [smalldatetime] NULL,
[UserType] [tinyint] NOT NULL,
[NeedPasswordReset] [bit] NOT NULL,

[dbo].[UserXRole]

[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[RoleId] [int] NOT NULL,

Migration Details

[dbo].[user_Users] to [dbo].[Person]

PersonId -> Auto generated

TenantId -> 1

FirstName -> uuFName from user_Users if is NULL insert empty string

LastName ->uuLName from user_Users if is NULL insert empty string

Gender ->Male

PersonNumber->NULL

DOB->NULL

MobileNumber->NULL

EmailId->uuEmail from user_Users

Address->NULL

City->NULL

State->NULL

ZipCode->NULL

Country->NULL

IsDeleted->0

AddedBy->1

AddedDateTime->GetDate()

UpdatedBy->NULL

UpdatedDateTime->NULL

PersonImage->NULL

[dbo].[user_Users] to [dbo].[Employee]

Insert in this table only if the record has uuIsDoctor=1

RoleId -> 3 for doctor

TenantId=1,

PersonId -> Retreive from Person table inserted

Designation -> Doctor

AddedBy=1

AddedDateTime=GetDate()

EmployeeType=0

ShowInDashboard=1

CanViewOthers=1

[dbo].[ApplicationUser] from [dbo].[user_Users], [dbo].[user_Logins]

TenantId ->1

PersonId -> From person table inserted

Username -> uuUsername in [dbo].[user_Users]

Password -> ulPassword in [dbo].[user_Logins], Is it possible to call a c# function to encrypt and save?

EmailId -> uuEmail in [dbo].[user_Users]

LastLoginDate -> GetDate()

FailedLoginDate -> NULL

IsActive -> uuIsActive from [dbo].[user_Users]

IsAccountLocked -> 0

FailedLoginAttempt -> 0

IsDeleted -> uuSuspended from [dbo].[user_Users]

AddedBy ->1

AddedDateTime -> GetDate()

UpdatedBy -> NULL

UpdatedDateTime -> NULL

UserType -> if ulIsAdmin=1 in [dbo].[user_Logins] then insert 1, else 0

NeedPasswordReset -> 0

[dbo].[UserXRole]

Id -> Autogenerated

UserId -> from ApplicationUser table inserted

RoleId -> if ulIsAdmin=1 in [dbo].[user_Logins] then insert 2, else if(uuIsDoctor in user_Users=1 then insert 3) else insert 5