/// <summary>
/// Obtains location informations from the Database creates LocationTable instances.
/// </summary>
/// <returns>
/// Returns Observable collections which accept LocationTables as their type.
/// </returns>
public ObservableCollection <LocationTable> GetLocations()
{
try
{
ObservableCollection <LocationTable> locations = new ObservableCollection <LocationTable>();
SQLQueryModel queryBuilder = new SQLQueryModel();
string query = string.Format("SELECT locationID, zipCode, locationName FROM location;");
DataTable locDataTable = new DataTable();
locDataTable = queryBuilder.ExecuteMySQLQuery(query);
foreach (DataRow row in locDataTable.Rows)
{
_locationTable = new LocationTable();
_locationTable.LocationID = Convert.ToInt32(row["locationID"]);
_locationTable.LocationName = row["locationName"].ToString();
_locationTable.ZipCode = Convert.ToInt32(row["zipCode"]);
locations.Add(_locationTable);
}
return(locations);
}
catch (ArgumentNullException nullex)
{
Protocol.WriteToProtocol(0, "RegisterNewUser() :" + nullex.Message + nullex.StackTrace, "Location is null Error");
return(null);
}
}
/// <summary>
/// Compares user input to credentials in the database
/// </summary>
/// <param name="username">Username to check/param>
/// <param name="password">password in plaintext</param>
/// <returns></returns>
public bool CheckUser(string email, string password)
{
bool userExists = false;
try
{
Email = email;
LoginPassword = password;
DataTable UserTable = new DataTable();
SQLQueryModel queryBuilder = new SQLQueryModel();
string queryString = string.Format("SELECT email, customerpwd FROM customer WHERE email = '{0}';", this.Email);
UserTable = queryBuilder.ExecuteMySQLQuery(queryString);
if (UserTable.Rows.Count != 0)
{
EncryptionModel encryptionModel = new EncryptionModel();
DbPassword = UserTable.Rows[0]["customerpwd"].ToString();
//LoginPassword = encryptionModel.EncryptPassword(LoginPassword);
userExists = encryptionModel.ValidatePassword(LoginPassword, DbPassword);
//MessageBox.Show(DbPassword);
return(userExists);
}
}
catch (MySqlException mysqlex)
{
Protocol.WriteToProtocol(0, "CheckUser() :" + mysqlex.Message + mysqlex.StackTrace, "Check User Error");
}
catch (ArgumentNullException nullex)
{
Protocol.WriteToProtocol(0, "CheckUser() :" + nullex.Message + nullex.StackTrace, "User Argument Error");
}
return(userExists);
}
public bool CheckMechanic(int personalID)
{
bool userExists = false;
try
{
DataTable mechanicTable = new DataTable();
SQLQueryModel queryBuilder = new SQLQueryModel();
string queryString = string.Format("SELECT personalID FROM mechanic WHERE personalID= '{0}';", personalID);
mechanicTable = queryBuilder.ExecuteMySQLQuery(queryString);
if (mechanicTable.Rows.Count != 0)
{
userExists = true;
}
return(userExists);
}
catch (MySqlException mysqlex)
{
Protocol.WriteToProtocol(0, "CheckUser() :" + mysqlex.Message + mysqlex.StackTrace, "Check User Error");
}
catch (ArgumentNullException nullex)
{
Protocol.WriteToProtocol(0, "CheckUser() :" + nullex.Message + nullex.StackTrace, "User Argument Error");
}
return(userExists);
}
public DataTable LoadParts()
{
PartsTable = new DataTable();
try
{
SQLQueryModel queryBuilder = new SQLQueryModel();
string query = string.Format("SELECT partID, partName, price, categoryID FROM part;");
PartsTable = queryBuilder.ExecuteMySQLQuery(query);
Parts = new ObservableCollection <PartTable>();
foreach (DataRow row in PartsTable.Rows)
{
_partTable = new PartTable();
_partTable.PartID = Convert.ToInt32(row["partID"]);
_partTable.PartName = row["partName"].ToString();
_partTable.Price = Convert.ToInt32(row["price"]);
_partTable.CategoryID = Convert.ToInt32(row["categoryID"]);
//_partTable.RequiredPartID = Convert.ToInt32(row["requiredPartID"]);
}
return(PartsTable);
}
catch (ArgumentNullException nullex)
{
return(null);
}
}
/// <summary>
/// Registers and inserts a new customer into the DB
/// </summary>
/// <param name="firstname">String first name of the customer</param>
/// <param name="lastname">String last name of the customer</param>
/// <param name="password">Password to be hashed</param>
/// <param name="zip">Int Zip code</param>
/// <param name="location">String Location name</param>
/// <param name="streetName">String street name</param>
/// <param name="houseNumber">Int house/ stairs number</param>
/// <param name="locationID">int location ID</param>
/// <returns></returns>
public bool RegisterNewUser(string firstname, string lastname, string email, string password, int zip, string location, string streetName, int houseNumber, int locationID, string birthDate, string phoneNumber) //Date birthDate
{
DataTable userTable = new DataTable();
try
{
// EMAIL VALIDATION
SQLQueryModel queryBuilder = new SQLQueryModel();
string query = string.Format("SELECT firstName FROM customer WHERE firstName = '{0}' AND lastName = '{1}';", firstname, lastname);
userTable = queryBuilder.ExecuteMySQLQuery(query);
if (userTable.Rows.Count != 0)
{
MessageBox.Show("User already exists");
}
else
{
#region Create Address Table
string insertQuery = "";
_addressTable = new AddressTable();
_addressTable.HouseNumber = houseNumber;
_addressTable.LocationID = locationID;
_addressTable.IsValidAddress = true;
_addressTable.StreetName = streetName;
insertQuery = string.Format("INSERT INTO address(locationID, streetName, houseNumber) VALUES('{0}','{1}','{2}');", _addressTable.LocationID, streetName, houseNumber);
queryBuilder.ExecuteMySQLQuery(insertQuery);
#endregion
#region Create Customer Table
insertQuery = "";
DataTable addressTable = new DataTable();
EncryptionModel encryption = new EncryptionModel();
//insertQuery = string.Format("INSERT INTO customer(firstName, lastName, customerpwd, email, addressID, birthDate) VALUES('{0}','{1}','{2}', '{3}',(SELECT LAST_INSERT_ID(),'{4}'));", firstname, lastname, encryption.EncryptPassword(password), email ,birthdate);
insertQuery = string.Format("INSERT INTO customer(firstName, lastName, customerpwd, email, addressID, birthDate, phoneNumber) VALUES('{0}','{1}','{2}','{3}',(SELECT LAST_INSERT_ID()),'{4}','{5}');", firstname, lastname, encryption.EncryptPassword(password), email, birthDate, phoneNumber);
queryBuilder.ExecuteMySQLQuery(insertQuery);
MessageBox.Show("User " + firstname + " " + lastname + " has successfully been created");
#endregion
}
}
catch (ArgumentNullException nullex)
{
Protocol.WriteToProtocol(0, "RegisterNewUser() :" + nullex.Message + nullex.StackTrace, "User already exists Error");
}
return(true);
}
public bool CreateNewPart(string partName, decimal price, int categoryID, int reqPartID)
{
DataTable partTable = new DataTable();
try
{
SQLQueryModel queryBuilder = new SQLQueryModel();
string query = string.Format("INSERT INTO part(partName,categoryID,price,requiredPartID) VALUES('{0}','{1}','{2}','{3}');", partName, categoryID, price, reqPartID);
queryBuilder.ExecuteMySQLQuery(query);
MessageBox.Show("New part has successfully been created");
}
catch (ArgumentNullException nullex)
{
Protocol.WriteToProtocol(0, "CreateNewPart() :" + nullex.Message + nullex.StackTrace, "Part already exists error");
}
return(true);
}
public ObservableCollection <CategoryTable> GetCategory()
{
try
{
ObservableCollection <CategoryTable> categories = new ObservableCollection <CategoryTable>();
SQLQueryModel queryBuilder = new SQLQueryModel();
string query = string.Format("SELECT categoryID, categoryName FROM category");
DataTable categoryTable = new DataTable();
categoryTable = queryBuilder.ExecuteMySQLQuery(query);
foreach (DataRow row in categoryTable.Rows)
{
_categoryTable = new CategoryTable();
_categoryTable.CategoryID = Convert.ToInt32(row["categoryID"]);
_categoryTable.CategoryName = row["categoryName"].ToString();
categories.Add(_categoryTable);
}
return(categories);
}
catch (ArgumentNullException nullex)
{
Protocol.WriteToProtocol(0, "GetCategory() :" + nullex.Message + nullex.StackTrace, "Category null Error");
return(null);
}
}