public EditWindow(Constants.Tablename_PKs tablename_PKs, List <Util.Header_Datatype> Headers_Datatypes, string[] ItemData)
{
InitializeComponent();
this.tablename_PKs = tablename_PKs;
this.Headers_Datatypes = Headers_Datatypes;
this.oldItemData = ItemData;
this.textboxes = new List <TextBox>();
controller = new Controller();
modifiedItem = new Util.ModifiedItem
{
TableName = tablename_PKs.TableName,
ChangesCount = 0,
PrimaryKeysHeaders = tablename_PKs.PrimaryKeys,
PrimaryKeysValues = new List <string>(new string[tablename_PKs.PrimaryKeys.Count]),
DeletedHeaders = new List <string>(),
ChangedHeaders = new List <string>(),
ChangedValues = new List <string>()
};
//get primary keys values
modifiedItem = Util.GetPKValues(modifiedItem, Headers_Datatypes, oldItemData);
Add_Labels_Textboxes();
}
public AddWindow(Constants.Tablename_PKs tablename_PKs, List <Util.Header_Datatype> Headers_Datatypes)
{
InitializeComponent();
this.tablename_PKs = tablename_PKs;
this.Headers_Datatypes = Headers_Datatypes;
this.textboxes = new List <TextBox>();
roleComboBox.ItemsSource = new string[] { "admin", "employee", "client" }; //user types
controller = new Controller();
addedItem = new Util.ModifiedItem
{
TableName = tablename_PKs.TableName,
ChangesCount = 0,
PrimaryKeysHeaders = tablename_PKs.PrimaryKeys,
PrimaryKeysValues = new List <string>(new string[tablename_PKs.PrimaryKeys.Count]),
ChangedHeaders = new List <string>(),
ChangedValues = new List <string>()
};
//if the the table has an associated user
if (Constants.tablesWithUsers.Contains(tablename_PKs.TableName))
{
this.NextButton.Visibility = Visibility.Visible;
}
else
{
this.CommitNoUserButton.Visibility = Visibility.Visible;
}
Add_Labels_Textboxes();
}
//add an item
public async Task <int> AddItem(Util.ModifiedItem addedItem)
{
int changesCount = 0;
string query = "INSERT INTO " + addedItem.TableName + " (";
for (int i = 0; i < addedItem.ChangedHeaders.Count; i++)
{
query += addedItem.ChangedHeaders[i];
changesCount++;
if (changesCount < addedItem.ChangesCount)
{
query += ", ";
}
}
query += ") VALUES (";
changesCount = 0;
for (int i = 0; i < addedItem.ChangedHeaders.Count; i++)
{
query += "'" + addedItem.ChangedValues[i] + "'";
changesCount++;
if (changesCount < addedItem.ChangesCount)
{
query += ", ";
}
}
query += ");";
Console.WriteLine(query);
int result = await db_manager.ExecuteNonQueryAsync(query);
this.errorMessage = db_manager.errorMessage;
return(result);
}
//update an item
public async Task <int> UpdateItem(Util.ModifiedItem modifiedItem)
{
int changesCount = 0;
string query = "UPDATE " + modifiedItem.TableName + " SET ";
//SET deleted columns to null
foreach (string header in modifiedItem.DeletedHeaders)
{
query += header + " = null ";
changesCount++;
if (changesCount < modifiedItem.ChangesCount)
{
query += ", ";
}
}
//SET modified columns to their values
for (int i = 0; i < modifiedItem.ChangedHeaders.Count; i++)
{
query += modifiedItem.ChangedHeaders[i] + " = '" + modifiedItem.ChangedValues[i] + "' ";
changesCount++;
if (changesCount < modifiedItem.ChangesCount)
{
query += ", ";
}
}
//use the primary keys to identify the columns
query += "WHERE ";
for (int i = 0; i < modifiedItem.PrimaryKeysHeaders.Count; i++)
{
query += modifiedItem.PrimaryKeysHeaders[i] + " = '" + modifiedItem.PrimaryKeysValues[i] + "'";
if ((i + 1) < modifiedItem.PrimaryKeysHeaders.Count)
{
query += " AND ";
}
}
query += ";";
Console.WriteLine(query);
int result = await db_manager.ExecuteNonQueryAsync(query);
this.errorMessage = db_manager.errorMessage;
return(result);
}
private async void DeletePaButton_Click(object sender, RoutedEventArgs e)
{
//if no row is selected
if (PartsDataGrid.SelectedCells.Count == 0)
{
MessageBox.Show("No Part is Selected", "Select a Part");
return;
}
Util.ModifiedItem deletedItem = new Util.ModifiedItem();
deletedItem.TableName = "Part";
deletedItem.PrimaryKeysHeaders = Util.GetTablePk(deletedItem.TableName).PrimaryKeys;
deletedItem = Util.GetPKValues(deletedItem, Parts_Datatypes, partsSelectedCells); //get values of primary keys (deletedItem.PrimaryKeysValues)
if (MessageBox.Show("Are you sure you want to delete the Part?", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question, MessageBoxResult.Yes) == MessageBoxResult.Yes)
{
await controller.DeleteItem(deletedItem);
LoadParts();
}
}
private async void DeleteEmpButton_Click(object sender, RoutedEventArgs e)
{
//if no row is selected
if (EmployeesDataGrid.SelectedCells.Count == 0)
{
MessageBox.Show("No Employee is Selected", "Select an Employee");
return;
}
Util.ModifiedItem deletedItem = new Util.ModifiedItem();
deletedItem.TableName = "Employee";
deletedItem.PrimaryKeysHeaders = Util.GetTablePk(deletedItem.TableName).PrimaryKeys;
deletedItem = Util.GetPKValues(deletedItem, Employees_Datatypes, employeesSelectedCells); //get values of primary keys (deletedItem.PrimaryKeysValues)
if (MessageBox.Show("Are you sure you want to delete the Employee? (Deleting the Employee will also delete the user associated)", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question, MessageBoxResult.Yes) == MessageBoxResult.Yes)
{
await controller.DeleteItem(deletedItem);
LoadEmployees();
}
}
//Delete an item (a row)
public async Task <int> DeleteItem(Util.ModifiedItem deletedItem)
{
string query = "DELETE FROM " + deletedItem.TableName + " WHERE ";
//use the primary keys to identify the columns
for (int i = 0; i < deletedItem.PrimaryKeysHeaders.Count; i++)
{
query += deletedItem.PrimaryKeysHeaders[i] + " = '" + deletedItem.PrimaryKeysValues[i] + "'";
if ((i + 1) < deletedItem.PrimaryKeysHeaders.Count)
{
query += " AND ";
}
}
query += ";";
Console.WriteLine(query);
int result = await db_manager.ExecuteNonQueryAsync(query);
this.errorMessage = db_manager.errorMessage;
return(result);
}
private async void CommitWithUserButton_Click(object sender, RoutedEventArgs e)
{
addedUser = new Util.ModifiedItem
{
TableName = "AppUser",
ChangesCount = 0,
ChangedHeaders = new List <string>(),
ChangedValues = new List <string>()
};
string username = usernameTextbox.Text;
if (username.Equals(""))
{
MessageBox.Show("Username can't be empty", "Add Username");
return;
}
else if ((await controller.CheckUsername(username)) == 1)
{
MessageBox.Show("This username is already used", "Change Username");
return;
}
string password = passwordTextbox.Text;
if (password.Length < 8)
{
MessageBox.Show("Password can't be less than 8 charachters", "Modify Password");
return;
}
var role_ = roleComboBox.SelectedItem;
if (!(role_ != null))
{
MessageBox.Show("Please choose a role for this user", "Choose a role");
return;
}
string role = role_.ToString();
if (role.Equals("client") && !(tablename_PKs.TableName.Equals("Client")))
{
MessageBox.Show("client role is only for clients", "Choose a role");
return;
}
else if (role.Equals("admin") && !(tablename_PKs.TableName.Equals("Employee")))
{
MessageBox.Show("admin role is only for employees", "Choose a role");
return;
}
else if (role.Equals("employee") && !(tablename_PKs.TableName.Equals("Employee")))
{
MessageBox.Show("employee role is only for employees", "Choose a role");
return;
}
addedUser.ChangedHeaders.Add("username");
addedUser.ChangedHeaders.Add("password");
addedUser.ChangedHeaders.Add("role");
addedUser.ChangedValues.Add(username);
addedUser.ChangedValues.Add(password);
addedUser.ChangedValues.Add(role);
addedUser.ChangesCount += 3;
if (tablename_PKs.TableName.Equals("Employee"))
{
string ID_ = (await controller.GetNextIncrement("Employee")).ToString();
int ID = Convert.ToInt32(ID_) - 1;
addedUser.ChangedHeaders.Add("EID");
addedUser.ChangedValues.Add(ID.ToString());
addedUser.ChangesCount += 1;
}
else if (tablename_PKs.TableName.Equals("Client"))
{
string ID_ = (await controller.GetNextIncrement("Client")).ToString();
int ID = Convert.ToInt32(ID_) - 1;
addedUser.ChangedHeaders.Add("CID");
addedUser.ChangedValues.Add(ID.ToString());
addedUser.ChangesCount += 1;
}
int success = 0;
success = await controller.AddItem(addedUser);
if (success == 0)
{
//print sql error in a message box)
MessageBox.Show(controller.errorMessage, "Database Error");
return;
}
//raise an event to update the employee in the main database
OnRefreshTable(new RefreshDataEventArgs(tablename_PKs.TableName, true));
this.Close();
}
