/// <summary>Gets random ID from array of IDs.</summary>
/// <param name="ids"> The array of IDs to pick from.</param>
/// <returns>A random ID from the array.</returns>
private static int GetRandomID(int[] ids)
{
int randIndex = NumberRandomizer.GetRandomNumber(0, ids.Length - 1);
return(ids[randIndex]);
}
/// <summary>Initializes the database by creating tables and stored procedures,
/// then seeding with data for testing.</summary>
/// <param name="context">The DB context.</param>
public static void Initialize(PurchaseAdminContext context)
{
context.Database.EnsureCreated();
string fileFoldername = @"DAL\";
string triggerFoldername = @"SQLTriggers\";
string procedureFoldername = @"SQLProcedures\";
string dropTablesFilename = "DropAllTables.sql";
string addTablesFilename = "AddAllTables.sql";
string mockDataFilename = "AddMockData.sql";
int userIDSeed = 1000; // DB IDs are seeded to begin at 1000
int staffRoleID = 1000;
int facultyRoleID = 1001;
int studentRoleID = 1002;
int sharedTypeID = 1001;
double staffPercent = .3; // percent of users we want to be staff
double facultyPercent = .2; // percent of users we want to be faculty
double staffStudentPercent = .05; // percent of staff we want to also be students
int userQty = Convert.ToInt32(context.Users.Count()) - 1; // exclude system user
int staffQty = Convert.ToInt32(staffPercent * userQty); // quantity of users to make staff
int facultyQty = Convert.ToInt32(facultyPercent * userQty); // quantity of users to make faculty
int staffStudentQty = Convert.ToInt32(staffStudentPercent * userQty); // quantity of staff to make students
int extraMgrQty = 8; // quantity of users we want to manage multiple accounts
// additional count of users to grant access to shared account
// may be additional account(s) for fac-staff or for workstudy students
int extraUserAccountQty = 32;
const int START_AT_ZERO = 0;
try
{
// uncomment to completely reset database
//RunSQLFile(context, fileFoldername, dropTablesFilename);
// Create tables if not exist
RunSQLFile(context, fileFoldername, addTablesFilename);
// Look for any users.
if (context.Users.Any())
{
return; // DB has been seeded
}
// create triggers, stored procedures, and seed with mock data
RunAllSQLFromFolder(context, fileFoldername + triggerFoldername);
RunAllSQLFromFolder(context, fileFoldername + procedureFoldername);
RunSQLFile(context, fileFoldername, mockDataFilename);
// get IDs and count for all shared accounts
int[] allSharedAccounts = context.Accounts
.Where(a => a.AccountTypeID == sharedTypeID)
.Select(i => i.ID)
.ToArray();
int accountCount = allSharedAccounts.Length;
// seed BillingCode table
var billingCodes = new BillingCode[accountCount];
// generate unique 10-digit codes
int[] codes = NumberRandomizer.GetUniqueNumArray(accountCount, 90000000, 99999999);
for (int i = 0; i < accountCount; i++)
{
billingCodes[i] = new BillingCode {
Code = codes[i], AccountID = allSharedAccounts[i]
};
}
foreach (BillingCode c in billingCodes)
{
context.BillingCodes.Add(c);
}
context.SaveChanges();
// seed UserRole table
var userRoles = new UserRole[userQty + staffStudentQty];
// lowerInclusive should start at 1001 to exclude the system user
var randUsers = NumberRandomizer.GetUniqueNumArray(userQty, 1 + userIDSeed, userQty + userIDSeed);
//assign staff roles
for (int i = 0; i < staffQty; i++)
{
userRoles[i] = new UserRole {
UserID = randUsers[i], RoleID = staffRoleID
};
}
//assign faculty roles
for (int i = staffQty; i < facultyQty + staffQty; i++)
{
userRoles[i] = new UserRole {
UserID = randUsers[i], RoleID = facultyRoleID
};
}
//assign student roles
for (int i = facultyQty + staffQty; i < userQty; i++)
{
userRoles[i] = new UserRole {
UserID = randUsers[i], RoleID = studentRoleID
};
}
// make some staff also students by randomizing the index numbers of randUsers within the staff range
var randStaff = NumberRandomizer.GetUniqueNumArray(staffStudentQty, START_AT_ZERO, staffQty - 1);
for (int i = userQty; i < userRoles.Length; i++)
{
userRoles[i] = new UserRole {
UserID = randUsers[randStaff[i - userQty]], RoleID = studentRoleID
}
}
;
foreach (UserRole r in userRoles)
{
context.UserRoles.Add(r);
}
context.SaveChanges();
// subarrays by role to help comprehension in subsequent actions
var allStaff = new int[staffQty];
Array.Copy(randUsers, START_AT_ZERO, allStaff, START_AT_ZERO, staffQty);
var allFaculty = new int[facultyQty];
Array.Copy(randUsers, staffQty, allFaculty, START_AT_ZERO, facultyQty);
// seed Manager table
var managers = new List <Manager>();
// dictionary ensures no duplicate pairs
var managerSelection = new Dictionary <int, HashSet <int> >();
// assign a random manager to every shared account
GetIDPairs(managerSelection, allSharedAccounts, allStaff);
// assign additional managers to some shared accounts (user cannot manage any one account more than once)
AddUniqueIDPairs(managerSelection, extraMgrQty, allStaff, allSharedAccounts);
foreach (KeyValuePair <int, HashSet <int> > set in managerSelection)
{
foreach (int i in set.Value)
{
managers.Add(new Manager {
UserID = set.Key, AccountID = i
});
}
}
foreach (Manager m in managers)
{
context.Managers.Add(m);
}
context.SaveChanges();
// seed UserAccount table
var userAccounts = new List <UserAccount>();
// dictionary ensures no duplicate pairs
var accountSelection = new Dictionary <int, HashSet <int> >();
// assign all staff to a random shared account
GetIDPairs(accountSelection, allStaff, allSharedAccounts);
// assign all faculty to a random shared account
GetIDPairs(accountSelection, allFaculty, allSharedAccounts);
// assign additional accounts to some users irrespective of their role
AddUniqueIDPairs(accountSelection, extraUserAccountQty, randUsers, allSharedAccounts);
foreach (KeyValuePair <int, HashSet <int> > set in accountSelection)
{
foreach (int i in set.Value)
{
userAccounts.Add(new UserAccount {
UserID = i, AccountID = set.Key
});
}
}
foreach (UserAccount a in userAccounts)
{
context.UserAccounts.Add(a);
}
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Database seeding failed.");
}
}
