private string Get_Department(string department_name)
{
if (department_name.Equals(""))
{
return("" + -1);
}
//MessageBox.Show(department_name);
string query = "select department_id from department where department_name=@Dept;";
string stringDep_id;
try
{
stringDep_id = Database.Query(query, ("@Dept", department_name)).Rows[0].ItemArray.Select(x => x.ToString().Trim()).ToArray()[0];
}
catch
{
//Add in ability to add specified department
if (MessageBox.Show("Could not find the specified department with name: " + department_name + "\nWould you like to add it?", "Warning!", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
query = "select department_id from department;";
int next_id = FindFirstNonIndex(query);
query = "insert into department (department_id, department_name) values(@next, @DeptName);";
Database.NonQuery(query, ("@next", next_id), ("@DeptName", department_name));
MessageBox.Show("Added Department with name: " + department_name);
stringDep_id = "" + next_id;
}
else
{
stringDep_id = "-1";
}
}
//MessageBox.Show(stringDep_id);
return(stringDep_id);
}
private void Btn_emp_add_Click(object sender, RoutedEventArgs e)
{
string query;
if (emp_field_id.Text.Equals(""))
{
if (emp_field_date.Text.Equals("") || emp_field_department.Text.Equals("") || emp_field_email.Text.Equals("") || emp_field_fname.Text.Equals("") || emp_field_lname.Text.Equals("") || emp_field_phone.Text.Equals("") || emp_field_ssn.Password.Equals("") || emp_field_type.Text.Equals(""))
{
MessageBox.Show("Please fill out all fields except for the employee id field");
}
else
{
string get_dep_id = Get_Department(emp_field_department.Text);
query = "select employee_id from employee;";
int next_emp_id = FindFirstNonIndex(query);
query = "insert into employee (employee_id, department_id, first_name, last_name, email, phone_number, employment_type, birthday, ssn) VALUES (" + next_emp_id + ", " + get_dep_id + ", '" + emp_field_fname.Text + "', '" + emp_field_lname.Text + "', '" + emp_field_email.Text + "', '" + emp_field_phone.Text + "', '" + emp_field_type.Text + "', '" + emp_field_date.Text + "', '" + emp_field_ssn.Password + "');";
Database.NonQuery(query);
if (!emp_combo_animal.Text.Equals("") && !emp_combo_animal.Text.Equals("None"))
{
query = "insert into handler (employee_id, animal_id) values (" + next_emp_id + ", " + Get_AnimalId(emp_combo_animal.Text) + ");";
Database.NonQuery(query);
}
MessageBox.Show("Added " + emp_field_fname.Text + " to the database.");
}
}
else
{
MessageBox.Show("You are not allowed to specify the employee id.");
}
}
private void Btn_emp_remove_Click(object sender, RoutedEventArgs e)
{
string query;
if (emp_field_id.Text.Equals(""))
{
MessageBox.Show("Please specifiy the employee to update with their id");
}
else
{
try
{
query = "select * from employee where employee_id=@EID;";
var employee_exists = Database.Query(query).Rows[0].ItemArray.Select(x => x.ToString().Trim()).ToArray()[0];
if (MessageBox.Show("Are you sure you want to remove employee with id: " + emp_field_id.Text + "?", "Warning!", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
query = "delete from employee where employee_id=@EID";
Database.NonQuery(query, ("@EID", emp_field_id.Text));
query = "delete from handler where employee_id=@EID";
Database.NonQuery(query, ("@EID", emp_field_id.Text));
}
}
catch
{
MessageBox.Show("Employee with id: " + emp_field_id + " does not exists.");
}
}
}
private void Btn_species_add_Click(object sender, RoutedEventArgs e)
{
try
{
//First, try to parse all of the entries into usable values.
//If any can't be parsed or are empty, we stop.
int idVal = int.Parse(txt_species_id.Text);
string specName = txt_species_name.Text;
string specClass = txt_species_class.Text;
if (specName != "" && specClass != "")
{
//Make the base of the query
string query = "INSERT INTO SPECIES VALUES(@ID, @SPENAME, @CLASSNAME);";
//Then run the query while replacing the placeholders with our values.
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@SPENAME", specName), ("@CLASSNAME", specClass));
if (valid)
{
MessageBox.Show("Added a Species.");
}
}
else
{
MessageBox.Show("One or more boxes were left blank");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers.");
}
}
private void Btn_animals_delete_Click(object sender, RoutedEventArgs e)
{
//Can only delete one entry at a time. For safety reasons. So you don't accidentally delete tons of records by leaving something filled out.
//Use expert mode to delete more by different parameters.
if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
//First try to parse the id field
int idVal = int.Parse(txt_animal_id.Text);
//Create the query that delete the animal with the
//specified field
string query = "DELETE FROM ANIMAL WHERE animal_id=(@ID);";
//Check if the animal is listed in the handlers table or
//the animal_adoption table
bool notInHandler = Database.Query("SELECT * FROM HANDLER WHERE animal_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;
bool notInAdopt = Database.Query("SELECT * FROM ANIMAL_ADOPTION WHERE animal_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;
//If it is not in those tables, it is safe to delete
if (notInAdopt && notInAdopt)
{
bool validID = Database.NonQuery(query, ("@ID", idVal));
//If the id wasn't valid, report that to the user
if (validID == false)
{
MessageBox.Show("Could not delete because no animal has the ID value");
}
else
{
MessageBox.Show("Deleted the animal.");
}
}
//Report whether the animal is listed in either the animal_adoption or
//Handlers table.
if (notInAdopt == false)
{
MessageBox.Show("This animal is still listed in Animal Adoption and can't be deleted");
}
if (notInHandler == false)
{
MessageBox.Show("This animal is still listed in Handlers and can't be deleted");
}
}
catch
{
MessageBox.Show("The Animal Id is not a number.");
}
}
}
private void Btn_animals_update_Click(object sender, RoutedEventArgs e)
{
try
{
//First try to parse all of the fields
int idVal = int.Parse(txt_animal_id.Text);
int habIdVal = int.Parse(txt_animal_habitat_id.Text);
int speIdVal = int.Parse(txt_animal_species_id.Text);
string animalName = txt_animal_name.Text;
string animalDate = date_animal_birthday.Text;
int wei = int.Parse(txt_animal_weight.Text);
int dietID = int.Parse(txt_animal_diet_id.Text);
if (animalName != "" && animalDate != "")
{
//Create the query to update an animal that has the
//specified id value leaving placeholders.
string query = "UPDATE ANIMAL " +
"SET habitat_id=(@HABID), " +
"species_id=(@SPEID), " +
"animal_name=(@NAME), " +
"birthday=(@BIRTHDAY), " +
"weight=(@WEIGHT), " +
"diet_id=(@DIETID), " +
"WHERE animal_id=(@ID);";
//Replace the placeholders with the users values and
//then run the query.
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABID", habIdVal), ("@SPEID", speIdVal),
("@NAME", animalName), ("@BIRTHDAY", animalDate), ("@WEIGHT", wei),
("@DIETID", dietID));
if (valid)
{
MessageBox.Show("Updated an Animal.");
}
}
else
{
MessageBox.Show("One or more boxes were left blank.");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers");
}
}
private void Btn_diet_delete_Click(object sender, RoutedEventArgs e)
{
//Can only delete one entry at a time. For safety reasons. So you don't accidentally delete tons of records by leaving something filled out.
//Use expert mode to delete more by different parameters.
if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
//First, parse the id value
int idVal = int.Parse(txt_diet_id.Text);
//Create the delete query with a placeholder for the id.
string query = "DELETE FROM DIET WHERE diet_id=(@ID);";
//Check if the diet is used by an animal
bool notInAnimal = Database.Query("SELECT * FROM ANIMAL WHERE diet_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;
//Delete the diet if it isn't used by anything.
if (notInAnimal)
{
//run the query after replacing the placeholder with the
//id value
bool validID = Database.NonQuery(query, ("@ID", idVal));
//If the query did not work, it is due to an invalid id value.
//Report this to the user.
if (validID == false)
{
MessageBox.Show("Could not delete because no diet has the ID value");
}
else
{
MessageBox.Show("Deleted a Diet.");
}
}
//If the diet is used by an animal, do not delete it.
if (notInAnimal == false)
{
MessageBox.Show("This Diet is still listed in Animal and can't be deleted");
}
}
catch
{
MessageBox.Show("The Diet Id is not a number or is invalid.");
}
}
}
private void Submit(string query)
{
// If the query starts with select, we want to perform a normal query
if (query.StartsWith("select", StringComparison.OrdinalIgnoreCase))
{
datagrid_expmode.ItemsSource = Database.Query(query)?.DefaultView;
}
else
{
// otherwise we want to perform what is known as a nonquery. This does not return any rows, only the rows affected
if (Database.NonQuery(query))
{
MessageBox.Show("Query executed successfully!");
}
}
}
private void Btn_habitat_delete_Click(object sender, RoutedEventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
//Parse the id of the habitat from the text boxes
int idVal = int.Parse(txt_habitat_id.Text);
//Next, build the query to delete the habitat at the
//specified id value.
string query = "DELETE FROM HABITAT WHERE habitat_id=(@ID);";
//Determine if the habitat is used by an animal
bool notInAnimal = Database.Query("SELECT * FROM ANIMAL WHERE habitat_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;
//If the habitat is used by an animal, do not delete it
//and report that to the user
if (notInAnimal)
{
//Replace the placeholder, and run the query
bool validID = Database.NonQuery(query, ("@ID", idVal));
//If the query fails, the id was invalid. Report that to the
//user.
if (validID == false)
{
MessageBox.Show("Could not delete because no habitat has the ID value");
}
else
{
MessageBox.Show("Deleted a Habitat.");
}
}
//Report to the user if the habitat is still used by an animal.
if (notInAnimal == false)
{
MessageBox.Show("This Habitat is still listed in Animal and can't be deleted");
}
}
catch
{
MessageBox.Show("The Habitat Id is not a number.");
}
}
}
private void Btn_diet_update_Click(object sender, RoutedEventArgs e)
{
try
{
//First, try to parse the values entered by the user.
int idVal = int.Parse(txt_diet_id.Text);
string dType = txt_diet_type.Text;
string dRestrictions = txt_diet_restrictions.Text;
string dPrimary = txt_diet_primary_food.Text;
string dSecondary = txt_diet_secondary_food.Text;
string dTreats = txt_diet_treats.Text;
//If any of boxes are blank, do not insert and inform the user
if (dType != "" && dRestrictions != "" && dPrimary != "" &&
dSecondary != "" && dTreats != "")
{
//Create the update query with placeholders
string query = "UPDATE DIET " +
"SET dietary_type=(@DTYPE), " +
"restrictions=(@RES), " +
"primary_food=(@PRIM), " +
"secondary_food=(@SECO), " +
"treats=(@TREATS) " +
"WHERE diet_id=(@ID);";
//Replace the placeholders with the user's values and
//run the query.
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@DTYPE", dType), ("@RES", dRestrictions),
("@PRIM", dPrimary), ("@SECO", dSecondary), ("@TREATS", dTreats));
if (valid)
{
MessageBox.Show("Updated a Diet.");
}
}
else
{
MessageBox.Show("One or more boxes are blank.");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers");
}
}
private void Btn_emp_update_Click(object sender, RoutedEventArgs e)
{
string query;
if (emp_field_id.Text.Equals(""))
{
MessageBox.Show("Please specifiy the employee to update with their id");
}
else
{
string get_dep_id = Get_Department(emp_field_department.Text);
query = "select * from employee where employee_id=@EID;";
try
{
var testEmployee = Database.Query(query, ("@EID", emp_field_id.Text)).Rows[0].ItemArray.Select(x => x.ToString().Trim()).ToArray()[0];
query = "update employee set employee_id=" + emp_field_id.Text + ((!get_dep_id.Equals("-1")) ? ", department_id=" + get_dep_id : "") + ((!emp_field_ssn.Password.Equals("")) ? ", ssn=" + emp_field_ssn.Password : "") + ((!emp_field_email.Text.Equals("")) ? ", email='" + emp_field_email.Text + "'" : "") + ((!emp_field_lname.Text.Equals("")) ? ", last_name='" + emp_field_lname.Text + "'" : "") + ((!emp_field_fname.Text.Equals("")) ? ", first_name='" + emp_field_fname.Text + "'" : "") + ((!emp_field_phone.Text.Equals("")) ? ", phone_number='" + emp_field_phone.Text + "'" : "") + ((!emp_field_type.Text.Equals("")) ? ", employment_type='" + emp_field_type.Text + "'" : "") + ((!emp_field_date.Text.Equals("")) ? ", birthday='" + emp_field_date.Text + "'" : "") + " where employee_id=" + emp_field_id.Text + ";";
Database.NonQuery(query);
if (!emp_combo_animal.Text.Equals(""))
{
if (emp_combo_animal.Text.Equals("None"))
{
query = "delete from handler where employee_id=" + emp_field_id.Text;
Database.NonQuery(query);
}
else
{
if (isHandlerOf("", emp_field_id.Text))
{
query = "delete from handler where employee_id=@EID;";
Database.NonQuery(query, ("@EID", emp_field_id.Text));
}
query = "insert into handler (employee_id, animal_id) values (@EID, @Animal);";
Database.NonQuery(query, ("@EID", emp_field_id.Text), ("@Animal", Get_AnimalId(emp_combo_animal.Text)));
}
}
MessageBox.Show("Updated Employee with id: " + emp_field_id.Text);
}
catch
{
//Add in ability to add specified department
MessageBox.Show("Could not find the specified employee with id: " + emp_field_id.Text);
}
}
}
private void Btn_species_delete_Click(object sender, RoutedEventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete this record?", "Warning!", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
{
try
{
//First, parse the id if you can.
int idVal = int.Parse(txt_species_id.Text);
//Create the query to delete that species
string query = "DELETE FROM SPECIES WHERE species_id=(@ID);";
//Lastly, check if the species is used. If it is,
//Don't delete this species.
bool notInAnimal = Database.Query("SELECT * FROM ANIMAL WHERE species_id=(@ID);", ("@ID", idVal)).Rows.Count == 0;
if (notInAnimal)
{
//If the id does not exist, let the user know.
bool validID = Database.NonQuery(query, ("@ID", idVal));
if (validID == false)
{
MessageBox.Show("Could not delete because no species has the ID value");
}
else
{
MessageBox.Show("Deleted a Species");
}
}
//Report to the user that the habitat is still used by
//animals in the zoo.
if (notInAnimal == false)
{
MessageBox.Show("This Habitat is still listed in Animal and can't be deleted");
}
}
catch
{
MessageBox.Show("The species Id is not a number.");
}
}
}
private void Btn_animals_add_Click(object sender, RoutedEventArgs e)
{
try
{
//First, try to parse all of the fields
int idVal = int.Parse(txt_animal_id.Text);
int habIdVal = int.Parse(txt_animal_habitat_id.Text);
int speIdVal = int.Parse(txt_animal_species_id.Text);
string animalName = txt_animal_name.Text;
string animalDate = date_animal_birthday.Text;
int wei = int.Parse(txt_animal_weight.Text);
int dietID = int.Parse(txt_animal_diet_id.Text);
//Prevent adding into the table if the animal has no name
//or birthday.
if (animalName != "" && animalDate != "")
{
//Create the query with placeholders that will insert into the table
string query = "INSERT INTO ANIMAL VALUES(@ID, @HABID, @SPEID, @NAME, @BIRTHDAY, @WEIGHT, @DIETID);";
//Replace the placeholders with the values to insert and run the query
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABID", habIdVal), ("@SPEID", speIdVal),
("@NAME", animalName), ("@BIRTHDAY", animalDate), ("@WEIGHT", wei),
("@DIETID", dietID));
if (valid)
{
MessageBox.Show("Added an Animal.");
}
}
else
{
MessageBox.Show("One or more boxes were left blank.");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers");
}
}
private void Btn_diet_add_Click(object sender, RoutedEventArgs e)
{
try
{
//First, try to parse all of the fields
int idVal = int.Parse(txt_diet_id.Text);
string dType = txt_diet_type.Text;
string dRestrictions = txt_diet_restrictions.Text;
string dPrimary = txt_diet_primary_food.Text;
string dSecondary = txt_diet_secondary_food.Text;
string dTreats = txt_diet_treats.Text;
//If any of boxes are blank, do not insert and inform the user
if (dType != "" && dRestrictions != "" && dPrimary != "" &&
dSecondary != "" && dTreats != "")
{
//Create the query to insert with placeholders
string query = "INSERT INTO DIET VALUES(@ID, @DTYPE, @RES, @PRIM, @SECO, @TREATS); ";
//Replace the placeholders with the user's values and
//run the query
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@DTYPE", dType), ("@RES", dRestrictions),
("@PRIM", dPrimary), ("@SECO", dSecondary), ("@TREATS", dTreats));
if (valid)
{
MessageBox.Show("Added a Diet.");
}
}
else
{
MessageBox.Show("One or more boxes are blank.");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers");
}
}
private void Btn_habitat_update_Click(object sender, RoutedEventArgs e)
{
try
{
//Try to parse the fields
int idVal = int.Parse(txt_habitat_id.Text);
string habName = txt_habitat_name.Text;
int humiVal = int.Parse(txt_habitat_humidity.Text);
int tempVal = int.Parse(txt_habitat_temperature.Text);
//If the habitat_name is blank, do not update the query
//and report to the user why.
if (habName != "")
{
string query = "UPDATE HABITAT " +
"SET habitat_name=(@HABNAME), " +
"humidity=(@HUMIVAL), " +
"temperature=(@TEMP), " +
"WHERE habitat_id=(@ID);";
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABNAME", habName), ("@HUMI", humiVal),
("@TEMP", tempVal));
if (valid)
{
MessageBox.Show("Updated a Habitat.");
}
}
else
{
MessageBox.Show("One or more boxes were left blank.");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers");
}
}
private void Btn_habitat_add_Click(object sender, RoutedEventArgs e)
{
try
{
//First, try to parse all of the fields
int idVal = int.Parse(txt_habitat_id.Text);
string habName = txt_habitat_name.Text;
int humiVal = int.Parse(txt_habitat_humidity.Text);
int tempVal = int.Parse(txt_habitat_temperature.Text);
//If the habitat name is blank, do not insert
//and relay that to the user
if (habName != "")
{
//First create the base query to insert with placeholders
string query = "INSERT INTO HABITAT VALUES(@ID, @HABNAME, @HUMI, @TEMP);";
//Replace the placeholders with the user's values and
//then run the query.
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@HABNAME", habName), ("@HUMI", humiVal),
("@TEMP", tempVal));
if (valid)
{
MessageBox.Show("Added a Habitat.");
}
}
else
{
MessageBox.Show("One or more boxes were left blank.");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers");
}
}
private void Btn_species_update_Click(object sender, RoutedEventArgs e)
{
try
{
//First try to parse all fields
int idVal = int.Parse(txt_species_id.Text);
string specName = txt_species_name.Text;
string specClass = txt_species_class.Text;
//If species_name or species_class are empty,
//do not update. Otherwise create a query to update
//the species at the specified id.
if (specName != "" && specClass != "")
{
string query = "UPDATE SPECIES " +
"SET species_name=(@SPENAME), " +
"class_name=(@CLASSNAME) " +
"WHERE species_id=(@ID);";
bool valid = Database.NonQuery(query, ("@ID", idVal), ("@SPENAME", specName), ("@CLASSNAME", specClass));
if (valid)
{
MessageBox.Show("Update an Species.");
}
}
else
{
MessageBox.Show("One or more boxes were left blank");
}
}
catch
{
MessageBox.Show("One or more boxes are invalid because they are not numbers");
}
}