/// <summary>
/// Constructor
/// </summary>
/// <param name="pUserName">userName</param>
/// <param name="pPassword">password</param>
public UserLogin(string pUserName, string pPassword)
{
KeyManagerHelper.Hash hasher = new KeyManagerHelper.Hash();
userName = pUserName;
password = hasher.getHash(pPassword);
// replaced the placeholders in the sql.
sql = sql.Replace("_userName", "'" + userName + "'");
sql = sql.Replace("_password", "'" + password + "'");
}
/// <summary>
/// If database exists, adds some sample data to it.
/// </summary>
/// <returns></returns>
public static bool PopulateSampleData()
{
try {
KeyManagerHelper.Hash hasher = new KeyManagerHelper.Hash();
SQLiteConnection conn = GetConnection();
String[] insertStatements =
{
// three personnel
"INSERT INTO personnel ('Username', 'Password', 'First Name', 'Last Name', 'IsAdministrator') VALUES ('papa', '" + hasher.getHash("smurf") + "', 'Papa', 'Smurf', 1)", // admin user
"INSERT INTO personnel ('Username', 'Password', 'First Name', 'Last Name', 'IsAdministrator') VALUES ('brainy', '" + hasher.getHash("smurf") + "', 'Brainy', 'Smurf', 0)", // normal user
"INSERT INTO personnel ('First Name', 'Last Name', 'IsAdministrator') VALUES ('Handy', 'Smurf', 0)", // non-user
// 7 locks, only have ID
"INSERT INTO lock DEFAULT VALUES",
"INSERT INTO lock DEFAULT VALUES",
"INSERT INTO lock DEFAULT VALUES",
"INSERT INTO lock DEFAULT VALUES",
"INSERT INTO lock DEFAULT VALUES",
"INSERT INTO lock DEFAULT VALUES",
"INSERT INTO lock DEFAULT VALUES",
// doors - three offices share a lock.
"INSERT INTO door ('room_number', 'lock') VALUES ('A100', 1)",
"INSERT INTO door ('room_number', 'lock') VALUES ('A101', 1)",
"INSERT INTO door ('room_number', 'lock') VALUES ('A102', 1)",
// doors - three offices with distinct locks.
"INSERT INTO door ('room_number', 'lock') VALUES ('B100', 2)",
"INSERT INTO door ('room_number', 'lock') VALUES ('B101', 3)",
"INSERT INTO door ('room_number', 'lock') VALUES ('B102', 4)",
// doors - two bathrooms share a lock.
"INSERT INTO door ('room_number', 'lock') VALUES ('A Bathroom', 5)",
"INSERT INTO door ('room_number', 'lock') VALUES ('B Bathroom', 5)",
// doors - two entrances with distinct locks.
"INSERT INTO door ('room_number', 'lock') VALUES ('Front Entrance', 6)",
"INSERT INTO door ('room_number', 'lock') VALUES ('Back Entrance', 7)",
// groups, aka locations
"INSERT INTO location ('Name') VALUES ('A Wing')",
"INSERT INTO location ('Name') VALUES ('B Wing')",
"INSERT INTO location ('Name') VALUES ('Offices')",
"INSERT INTO location ('Name') VALUES ('Entrances')",
"INSERT INTO location ('Name') VALUES ('All Doors')",
// join doors to groups - A Wing
"INSERT INTO door_to_location ('Door', 'Location') VALUES (1, 1)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (2, 1)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (3, 1)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (7, 1)",
// join doors to groups - B Wing
"INSERT INTO door_to_location ('Door', 'Location') VALUES (4, 2)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (5, 2)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (6, 2)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (8, 2)",
// join doors to groups - Offices
"INSERT INTO door_to_location ('Door', 'Location') VALUES (1, 3)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (2, 3)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (3, 3)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (4, 3)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (5, 3)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (6, 3)",
// join doors to groups - Entrances
"INSERT INTO door_to_location ('Door', 'Location') VALUES (9, 4)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (10, 4)",
// join doors to groups - All Doors
"INSERT INTO door_to_location ('Door', 'Location') VALUES (1, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (2, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (3, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (4, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (5, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (6, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (7, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (8, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (9, 5)",
"INSERT INTO door_to_location ('Door', 'Location') VALUES (10, 5)",
// keytypes
"INSERT INTO keytype ('Name') VALUES ('A Offices')",
"INSERT INTO keytype ('Name') VALUES ('B Offices')",
"INSERT INTO keytype ('Name') VALUES ('B100')",
"INSERT INTO keytype ('Name') VALUES ('B101')",
"INSERT INTO keytype ('Name') VALUES ('B102')",
"INSERT INTO keytype ('Name') VALUES ('Bathrooms')",
"INSERT INTO keytype ('Name') VALUES ('Entrances')",
"INSERT INTO keytype ('Name') VALUES ('Maintenance')", // can open bathrooms and entrances
"INSERT INTO keytype ('Name') VALUES ('All Offices')",
"INSERT INTO keytype ('Name') VALUES ('Master')",
// join keytypes to locks
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (1, 1)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (2, 2)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (2, 3)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (2, 4)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (3, 2)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (4, 3)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (5, 4)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (6, 5)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (7, 6)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (7, 7)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (8, 6)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (8, 7)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (8, 5)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (9, 1)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (9, 2)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (9, 3)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (9, 4)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (10, 1)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (10, 2)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (10, 3)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (10, 4)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (10, 5)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (10, 6)",
"INSERT INTO keytype_to_lock ('Keytype', 'Lock') VALUES (10, 7)",
// create some keyrings
"INSERT INTO keyring ('Name', 'owner') VALUES ('Master Set', 3)",
"INSERT INTO keyring ('Name', 'owner') VALUES ('Janitor Set', 3)",
"INSERT INTO keyring ('Name', 'owner') VALUES ('Prof Smurf Set', 1)",
// three copies of every key, some added to keyrings.
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('A999a', 1, 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('A999b', 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('A999c', 1)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('B999a', 2, 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('B999b', 2)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('B999c', 2)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('B100a', 3, 1)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('B100b', 3, 3)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('B100c', 3)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('B101a', 4, 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('B101b', 4)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('B101c', 4)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('B102a', 5, 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('B102b', 5)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('B102c', 5)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('BATHa', 6, 1)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('BATHb', 6, 2)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('BATHc', 6)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('ENTRa', 7, 1)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('ENTRb', 7, 3)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('ENTRc', 7, 2)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('MNTa', 8, 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('MNTb', 8)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('MNTc', 8)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('OFFCa', 9, 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('OFFCb', 9)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('OFFCc', 9)",
"INSERT INTO key ('Serial', 'Keytype', 'Keyring') VALUES ('MSTRa', 10, 1)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('MSTRb', 10)",
"INSERT INTO key ('Serial', 'Keytype') VALUES ('MSTRc', 10)",
// let papa smurf have his keys checked out
"INSERT INTO checkout ('Person', 'Keyring', 'Date') VALUES (1, 3, '2016-01-01')",
"INSERT INTO checkout ('Person', 'Key', 'Date') VALUES (1, 2, '2016-01-01')",
"INSERT INTO checkout ('Person', 'Key', 'Date') VALUES (1, 3, '2016-01-01')",
"INSERT INTO checkout ('Person', 'Key', 'Date') VALUES (1, 5, '2016-01-01')",
"INSERT INTO checkout ('Person', 'Key', 'Date') VALUES (1, 6, '2016-01-01')"
};
SQLiteCommand command;
foreach (String sql in insertStatements)
{
command = new SQLiteCommand(sql, conn);
command.ExecuteNonQuery();
}
conn.Close();
return true;
}
catch(Exception)
{
return false;
}
}
