public int CreateCustomer(Customer c)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewCustomer";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("CompanyName", c.Name);
cmd.Parameters.AddWithValue("AddressID", c.Address_ID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
//// GET: Customer
//public ActionResult GetCustomers()
//{
// // If logged in
// bool state = (bool)Session["loggedInState"];
// if (state == true)
// {
// // Creates models
// var addressModel = new AddressModel();
// var customerModel = new CustomerModel();
// // Gets the complete list
// var customerList = customerModel.ListCustomers();
// // Attaches associated address to customer
// foreach (var customer in customerList)
// {
// Address address = null;
// if (customer.Address_ID != 0)
// {
// address = addressModel.SearchAddress(customer.Address_ID);
// }
// // Appends object to address
// customer.Address = address;
// }
// // Return the CustomerList
// return View(customerList);
// }
// else
// {
// return Redirect("/403.html");
// }
//}
// Creates a new customer
public int create(String name, int addressID)
{
// Establishes models
CustomerModel customerModel = new CustomerModel();
// Holds object placeholders
Customer newCustomer = new Customer();
// Stored details for the customer
newCustomer.Name = name;
newCustomer.Address_ID = addressID;
// Creates the customer
int customerID = customerModel.CreateCustomer(newCustomer);
// Return created department to view
return customerID;
}
public Bank SearchBanking(Customer c)
{
var bank = new Bank();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "SearchBankingCustomer";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("CustomerID", c.ID);
cmd.Parameters.AddWithValue("CustomerName", c.Name);
cmd.Parameters.AddWithValue("AddressID", c.Address_ID);
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
bank.ID = int.Parse(reader["Banking_ID"].ToString());
bank.Address_ID = int.Parse(reader["Address_ID"].ToString());
bank.SortCode = reader["Sort_Code"].ToString();
bank.AccountNumber = int.Parse(reader["Account_Number"].ToString());
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return bank;
}
}
// Finds Transations with the specifed details in the customer object.
public List<Transaction> SearchTransactions(Customer c)
{
throw new NotImplementedException();
}
// View individual customer details
public ActionResult view()
{
// Null handling
if (Session["loggedInState"] == null)
{
return Redirect("/403.html");
}
// If logged in
bool state = (bool)Session["loggedInState"];
if (state == true)
{
// Establishes models
AccountModel clientModel = new AccountModel();
CustomerModel customerModel = new CustomerModel();
List<Account> accountList = new List<Account>();
// Get the ID requested
int theID = int.Parse(Url.RequestContext.RouteData.Values["id"].ToString());
Customer customer = new Customer();
customer.ID = theID;
accountList = clientModel.SearchAccounts(customer);
foreach(var account in accountList)
{
account.Customer = customerModel.SearchCustomers(account.CustomerID);
}
return View(accountList);
}
else
{
// If not logged in
return Redirect("/login.html");
}
}
// Allows editing of a customer
public ActionResult edit()
{
// Null handling
if (Session["loggedInState"] == null)
{
return Redirect("/403.html");
}
// Checks if logged in
bool state = (bool)Session["loggedInState"];
if (state == true)
{
// Creates an department placeholder
Customer customer = new Customer();
// Establish customer model
CustomerModel customerModel = new CustomerModel();
// Setup address edit
customer.ID = int.Parse(Request.Form["id"]);
customer.Address_ID = int.Parse(Request.Form["addressID"]);
customer.Name = Request.Form["customerName"];
// Conduct edit
customerModel.EditCustomer(customer);
// Passes back to the view
return Redirect("/Customer/Customer");
}
else
{
// If not logged in
return Redirect("/login.html");
}
}
public ActionResult createCustomer()
{
if (Session["loggedInState"] == null)
{
return Redirect("/403.html");
}
else
{
int id = int.Parse(Request.Form["newAddress"]);
String name = Request.Form["newClientName"];
Customer client = new Customer();
client.Name = name;
client.Address_ID = id;
var cm = new CustomerModel();
cm.CreateCustomer(client);
return Redirect("Customer");
}
}
// Changes the customer specifed by the customer object account type to the Account_Type object.
public void ChangeAccountType(Customer c, Account_Type type)
{
throw new NotImplementedException();
}
// Calls main method for getting a customer
public Customer SearchCustomers(Customer c)
{
return SearchCustomers(c.ID);
}
// Main method for getting a customer
public Customer SearchCustomers(int ID)
{
var cust = new Customer();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "GetCustomer";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("CustomerID", ID);
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
cust.ID = (int)reader["Customer_ID"];
cust.Name = reader["Company_name"].ToString();
cust.Address_ID = (int)reader["Address_ID"];
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return cust;
}
}
// List all customers
public List<Customer> ListCustomers()
{
var cl = new List<Customer>();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "ListCustomer";
var cmd = new MySqlCommand(query, connect) {CommandType = CommandType.StoredProcedure};
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var c = new Customer();
c.ID = (int) reader["Customer_ID"];
c.Name = reader["Company_name"].ToString();
c.Address_ID = (int) reader["Address_ID"];
cl.Add(c);
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return cl;
}
}
public void EditCustomer(Customer c)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "EditCustomer";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("CustomerID", c.ID);
cmd.Parameters.AddWithValue("CustomerName", c.Name);
cmd.Parameters.AddWithValue("AddressID", c.Address_ID);
cmd.ExecuteNonQuery();
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
}
// Gets a list of accounts for a customer speicifed in the customer object.
public List<Account> SearchAccounts(Customer c)
{
var accountList = new List<Account>();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "SearchAccount";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("CustomerID", c.ID);
cmd.Parameters.AddWithValue("AccountID", null);
cmd.Parameters.AddWithValue("ContactID", null);
cmd.Parameters.AddWithValue("AccountTypeID", null);
cmd.Parameters.AddWithValue("BankingID", null);
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var a = new Account();
a.ID = int.Parse(reader["Account_ID"].ToString());
a.ContactID = int.Parse(reader["Contact_ID"].ToString());
a.CustomerID = int.Parse(reader["Customer_ID"].ToString());
a.AccountTypeID = int.Parse(reader["Account_Type_ID"].ToString());
a.BankID = int.Parse(reader["Banking_ID"].ToString());
accountList.Add(a);
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return accountList;
}
}
