public bool DeleteCustomer(Customer customer)
{
if (customer.internalID.Equals(-1))
{
throw new Exception("Customer to remove had no ID!");
}
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "DELETE FROM customer WHERE customer.idcustomer = '" + customer.internalID.ToString() + "'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
if (command.ExecuteNonQuery() <= 0)
{
return false;
}
else
{
return true;
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
private void btnAddCustomer_Click(object sender, RoutedEventArgs e)
{
string firstName = tbFirstName.Text;
string lastName = tbLastName.Text;
string phoneNumber = tbPhoneNumber.Text;
string street = tbStreet.Text;
string streetNumber = tbHouseNumber.Text;
string town = tbTown.Text;
if (lastName.Equals(""))
{
MessageBox.Show(this, "Een achternaam is minimaal verplicht!", "Te weinig gegevens", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
Customer c = new Customer(firstName, lastName, phoneNumber, street, streetNumber, town);
if (access.AddCustomer(c))
{
MessageBox.Show(this, "Klant succesvol toegevoegd!", "Succes", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show(this, "Klant kon niet toegevoegd worden!", "Fout", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public bool AddCustomer(Customer customer)
{
if (customer.internalID.Equals(-1) == false)
{
throw new Exception("Customer to insert into database already has database id!");
}
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
string query = "INSERT INTO customer (firstname, lastname, phonenumber, street, housenumber, town) VALUES "
+ "('" + customer.firstName + "', '" + customer.lastName + "', '" + customer.phoneNumber + "', '" + customer.street + "', '" + customer.houseNumber + "', '" + customer.town + "')";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
return false;
}
}
string queryID = "SELECT LAST_INSERT_ID();";
using (IDbCommand commandID = MySQLDAOFactory.GetDatabase().CreateCommand(queryID, connection))
{
using (IDataReader reader = commandID.ExecuteReader())
{
if (reader.Read())
{
customer.internalID = Convert.ToInt32(reader[0]);
}
}
}
if (customer.internalID.Equals(-1))
{
transaction.Rollback();
throw new Exception("Customer inserted succesfully, but could not retrieve ID afterwards. Rollback.");
}
else
{
transaction.Commit();
return true;
}
}
catch (MySqlException ex)
{
transaction.Rollback();
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public Order(Customer customer, DateTime dateTimeOrdered, DateTime dateTimePickup, Employee employee, string description, int internalID)
{
this.customer = customer;
this.dateTimeOrdered = dateTimeOrdered;
this.dateTimePickup = dateTimePickup;
this.employee = employee;
this.description = description;
this.internalID = internalID;
}
public Order(Customer customer, DateTime dateTimeOrdered, DateTime dateTimePickup, Employee employee, string description, Order_Entry[] order_entries)
{
this.customer = customer;
this.dateTimeOrdered = dateTimeOrdered;
this.dateTimePickup = dateTimePickup;
this.employee = employee;
this.description = description;
this.order_entries = order_entries;
this.internalID = -1;
}
public bool AddCustomer(Customer customer)
{
CustomerDAO dao = daoFactory.GetCustomerDAO();
try
{
return dao.AddCustomer(customer);
}
catch (DatabaseException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetOrdersByCustomer(Customer customer, out Order[] orders)
{
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM " + MySQLDAOFactory.DatabaseName + ".order WHERE order.customerid = '" + customer.internalID.ToString() + "'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List<Order> ordersList = new List<Order>();
while (reader.Read())
{
DateTime ordered_datetime;
DateTime pickup_datetime;
if (DateTime.TryParseExact(reader["ordered_datetime"].ToString(), dateFormatReading, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.AssumeLocal, out ordered_datetime) &&
DateTime.TryParseExact(reader["pickup_datetime"].ToString(), dateFormatReading, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.AssumeLocal, out pickup_datetime))
{
Order o = new Order(
customer,
ordered_datetime,
pickup_datetime,
new Employee("", Convert.ToInt32(reader["employee_id"])),
reader["description"].ToString(),
Convert.ToInt32(reader["idorder"])
);
ordersList.Add(o);
}
else
{
throw new Exception("Couldn't cast to datetime!");
}
}
orders = ordersList.ToArray();
if (orders.Length > 0)
{
return true;
}
return false;
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetOrdersByCustomer(Customer customer, out Order[] orders)
{
OrderDAO dao = daoFactory.GetOrderDAO();
try
{
return dao.GetOrdersByCustomer(customer, out orders);
}
catch (DatabaseException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetCustomersByName(string customerName, out Customer[] customers)
{
CustomerDAO dao = daoFactory.GetCustomerDAO();
try
{
return dao.GetCustomersByName(customerName, out customers);
}
catch (DatabaseException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetAllCustomers(out Customer[] customers)
{
CustomerDAO dao = daoFactory.GetCustomerDAO();
try
{
return dao.GetAllCustomers(out customers);
}
catch (DatabaseException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public bool UpdateCustomer(Customer customer)
{
throw new NotImplementedException();
}
public bool GetCustomersByName(string customerName, out Customer[] customers)
{
string searchName = customerName.Trim().ToLower();
if (searchName.Equals(""))
{
customers = new Customer[0];
return false;
}
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM customer where customer.firstname LIKE '%" + searchName + "%' OR customer.lastname LIKE '%" + searchName + "%'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List<Customer> customerList = new List<Customer>();
while (reader.Read())
{
Customer c = new Customer(
reader["firstname"].ToString(),
reader["lastname"].ToString(),
reader["phonenumber"].ToString(),
reader["street"].ToString(),
reader["housenumber"].ToString(),
reader["town"].ToString()
);
customerList.Add(c);
}
customers = customerList.ToArray();
if (customers.Length > 0)
{
return true;
}
return false;
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetCustomerByID(int customerID, out Customer customer)
{
if (customerID.Equals(-1))
{
customer = null;
return false;
}
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM customer where customer.idcustomer = '" + customerID.ToString() + "'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
customer = new Customer(
reader["firstname"].ToString(),
reader["lastname"].ToString(),
reader["phonenumber"].ToString(),
reader["street"].ToString(),
reader["housenumber"].ToString(),
reader["town"].ToString(),
customerID
);
return true;
}
customer = null;
return false;
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetAllCustomers(out Customer[] customers)
{
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM customer";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List<Customer> customerList = new List<Customer>();
while (reader.Read())
{
Customer c = new Customer(
reader["firstname"].ToString(),
reader["lastname"].ToString(),
reader["phonenumber"].ToString(),
reader["street"].ToString(),
reader["housenumber"].ToString(),
reader["town"].ToString(),
Convert.ToInt32(reader["idcustomer"])
);
customerList.Add(c);
}
customers = customerList.ToArray();
if (customers.Length > 0)
{
return true;
}
return false;
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
