static void Main(string[] args)
{
Console.WriteLine();
// create a new customer
Customer cust = new Objects.Customer(11011, "Michael", "Schore", "100 Main St.", "Raleigh", "NC", "27603");
// another way to create a new customer
Customer nextCust = new Customer();
// create 3 different customers
Customer cust2 = new Objects.Customer(11012, "Bob", "Smith", "123 Sesame St.", "Charlotte", "NC", "28105");
Customer cust3 = new Objects.Customer(20821, "Jane", "Doe", "700 Hollywood Rd.", "Richmond", "VA", "23173");
Customer cust4 = new Objects.Customer(37849, "Chloe", "Brown", "289 King Dr.", "Atlanta", "GA", "30301");
// output customer block
cust.PrintBlock();
Console.WriteLine();
cust2.PrintBlock();
Console.WriteLine();
cust3.PrintBlock();
Console.WriteLine();
cust4.PrintBlock();
Console.WriteLine();
}
public static Objects.Customer GetCustomer(int primaryKey)
{
SqlConnection c = GetConnection();
string selectStatement = "SELECT CustName, CustPhone, CustAddress, CustCity, CustState, CustZip " +
"FROM CheathamCustomer " +
"WHERE CustAccountNumber = @CustAccountNumber";
SqlCommand selectCommand = new SqlCommand(selectStatement, c);
selectCommand.Parameters.AddWithValue("@CustAccountNumber", primaryKey);
try
{
c.Open();
SqlDataReader customerReader = selectCommand.ExecuteReader();
if (customerReader.Read())
{
Objects.Customer customer = new Objects.Customer();
customer.AccountNumber = Convert.ToInt32(primaryKey);
customer.Name = customerReader["CustName"].ToString();
customer.PhoneNumber = customerReader["CustPhone"].ToString();
customer.Address = customerReader["CustAddress"].ToString();
customer.City = customerReader["CustCity"].ToString();
customer.State = customerReader["CustState"].ToString();
customer.Zip = Convert.ToInt32(customerReader["CustZip"]);
return(customer);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
c.Close();
}
}
/// <summary>
/// Checks if Id is valid
/// </summary>
/// <returns></returns>
public bool IsValid(Objects.Customer customer)
{
bool isValid;
try
{
//Open Connection
Database.Connection.Open();
if (customer == null)
{
return(false);
}
string query = "SELECT CustomerId FROM Customers WHERE CustomerId=@CustomerId";
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("CustomerId", customer.Id);
MySqlDataReader DataReader = Command.ExecuteReader();
if (!DataReader.HasRows)
{
isValid = false;
}
else
{
isValid = true;
}
//Close Connection
Database.Connection.Close();
}
catch (MySqlException e)
{
Logger.QueryError(e, "Customer", "Verifying Id");
isValid = false;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(isValid);
}
public static bool UpdateCustomer(Objects.Customer customer)
{
SqlConnection c = GetConnection();
string updateStatement = "UPDATE CheathamCustomer SET CustName = @CustName, CustPhone = @CustPhone, CustAddress = @CustAddress, " +
"CustCity = @CustCity, CustZip = @CustZip WHERE CustAccountNumber = @CustAccountNumber";
SqlCommand updateCommand = new SqlCommand(updateStatement, c);
updateCommand.Parameters.AddWithValue("@CustName", customer.Name);
updateCommand.Parameters.AddWithValue("@CustPhone", customer.PhoneNumber);
updateCommand.Parameters.AddWithValue("@CustAddress", customer.Address);
updateCommand.Parameters.AddWithValue("@CustCity", customer.City);
updateCommand.Parameters.AddWithValue("@CustZip", customer.Zip);
updateCommand.Parameters.AddWithValue("@CustAccountNumber", customer.AccountNumber);
try
{
c.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
c.Close();
}
}
/// <summary>
/// Debit Customer StoreCredit (Substract)
/// </summary>
/// <returns></returns>
public Response Debit(Objects.Customer customer, decimal amount)
{
int statusCode = 204;
Response response = new Response();
try
{
Database.Connection.Open();
var query = "UPDATE Customers SET StoreCredit=ROUND(StoreCredit - @Amount, 2) WHERE CustomerId=@CustomerId";
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("CustomerId", customer.Id);
Command.Parameters.AddWithValue("Amount", amount);
Command.ExecuteNonQuery();
Database.Connection.Close();
// OK
statusCode = 204;
//Response
customer.StoreCredit -= amount;
response.StatusCode = statusCode;
response.Data = customer;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Customer", "Debit");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
/// <summary>
/// Delete
/// </summary>
public Response Delete(Objects.Customer customer)
{
//Props
int statusCode = 204;
Response response = new Response();
try
{
Database.Connection.Open();
string Query = "DELETE FROM Customers WHERE CustomerId=@CustomerId ";
//Execute Query With Connection => DataReader
MySqlCommand Command = new MySqlCommand(Query, Database.Connection);
Command.Parameters.AddWithValue("CustomerId", customer.Id);
Command.ExecuteNonQuery();
Database.Connection.Close();
//[HTTP Status Code] OK : Successfully Deleted : Returning No Content
statusCode = 204;
response.StatusCode = statusCode;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Customer", "Delete");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
/// <summary>
/// Create a new Customer
/// </summary>
/// <returns></returns>
public Response Create(Objects.Customer customer)
{
//Props
int statusCode = 201;
Response response = new Response();
//Random Tokens
customer.Id = ServerUtil.GenerateToken(TokenLengths.TokensLength);
try
{
//Open MySqlConnection
Database.Connection.Open();
//Query
string query = " INSERT INTO Customers (CustomerId, Code, FirstName, LastName, Email, PhoneNumber, Website, Address, " +
" PostCode, City, Country, IsFavorite, IsVerified, DateOfBirth, Sex) " +
" VALUES (@CustomerId, @Code, @FirstName, @LastName, @Email, @PhoneNumber, @Website, @Address, " +
" @PostCode, @City, @Country, @IsFavorite, @IsVerified, @DateOfBirth, @Sex) ";
//Execute Query With MySqlConnection
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("CustomerId", customer.Id);
Command.Parameters.AddWithValue("Code", customer.Code);
Command.Parameters.AddWithValue("FirstName", customer.FirstName);
Command.Parameters.AddWithValue("LastName", customer.LastName);
Command.Parameters.AddWithValue("Email", customer.Email);
Command.Parameters.AddWithValue("Sex", customer.Sex);
Command.Parameters.AddWithValue("PhoneNumber", customer.PhoneNumber);
Command.Parameters.AddWithValue("Website", customer.Website);
Command.Parameters.AddWithValue("Address", customer.Address);
Command.Parameters.AddWithValue("PostCode", customer.PostCode);
Command.Parameters.AddWithValue("City", customer.City);
Command.Parameters.AddWithValue("Country", customer.Country);
Command.Parameters.AddWithValue("IsFavorite", Func.ToString(customer.IsFavorite));
Command.Parameters.AddWithValue("IsVerified", Func.ToString(customer.IsVerified));
Command.Parameters.AddWithValue("DateOfBirth", customer.DateOfBirth);
Command.ExecuteNonQuery();
//Close Connection
Database.Connection.Close();
// Status Code
statusCode = 201;
}
catch (MySqlException e)
{
if (e.Message.Contains("Duplicate entry"))
{
//Duplicate Key or Conflict
statusCode = 409;
}
else
{
// Internal Server Error
statusCode = 500;
}
Logger.QueryError(e, "Customer", "Create");
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
//Response
response.StatusCode = statusCode;
response.Data = customer;
return(response);
}
/// <summary>
/// Update
public Response Update(Objects.Customer customer)
{
//Props
int statusCode = 204;
Response response = new Response();
var isFavorite = customer.IsFavorite;
var isVerified = customer.IsVerified;
try
{
//Open Connection
Database.Connection.Open();
//Query
string query = " UPDATE Customers SET GroupId=@GroupId, FirstName=@FirstName, LastName=@LastName, Email=@Email, Sex=@Sex, PhoneNumber=@PhoneNumber, Website=@Website, Address=@Address, " +
" PostCode=@PostCode, City=@City, Country=@Country, IsFavorite=@IsFavorite, IsVerified=@IsVerified, DateOfBirth=@DateOfBirth " +
" WHERE CustomerId=@CustomerId ";
//Execute Query With MySqlConnection
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("CustomerId", customer.Id);
Command.Parameters.AddWithValue("FirstName", customer.FirstName);
Command.Parameters.AddWithValue("LastName", customer.LastName);
Command.Parameters.AddWithValue("Email", customer.Email);
Command.Parameters.AddWithValue("PhoneNumber", customer.PhoneNumber);
Command.Parameters.AddWithValue("Website", customer.Website);
Command.Parameters.AddWithValue("Sex", customer.Sex);
Command.Parameters.AddWithValue("Address", customer.Address);
Command.Parameters.AddWithValue("PostCode", customer.PostCode);
Command.Parameters.AddWithValue("City", customer.City);
Command.Parameters.AddWithValue("Country", customer.Country);
Command.Parameters.AddWithValue("IsFavorite", Func.ToString(customer.IsFavorite));
Command.Parameters.AddWithValue("IsVerified", Func.ToString(customer.IsVerified));
Command.Parameters.AddWithValue("DateOfBirth", customer.DateOfBirth);
if (customer.Group is object)
{
Command.Parameters.AddWithValue("GroupId", customer.Group.Id);
}
Command.ExecuteNonQuery();
//Close Connection
Database.Connection.Close();
// Updated Successfully
statusCode = 204;
//Response
response.StatusCode = statusCode;
}
catch (MySqlException e)
{
if (e.Message.Contains("Duplicate entry"))
{
//Duplicate Key or Conflict
statusCode = 409;
}
else
{
// Internal Server Error
statusCode = 500;
}
Logger.QueryError(e, "Customer", "Update");
// Status Code
response.StatusCode = statusCode;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
/// <summary>
/// Get whole registered customers
/// </summary>
public Response GetCustomers(Objects.CustomerSearchQuery searchQuery, int limit = 0)
{
//Props
int statusCode = 200;
Response response = new Response();
List <object> users = new List <object>();
try
{
Database.Connection.Open();
var q = $"%{searchQuery.Query}%";
var query = GetSearchQuery(searchQuery, limit != 0);
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SearchQuery", q);
Command.Parameters.AddWithValue("GroupId", searchQuery.GroupId);
//Command.Parameters.AddWithValue("Status", searchQuery.Status);
Command.Parameters.AddWithValue("Limit", limit);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
Objects.Customer customer = new Objects.Customer
{
Id = DataReader["CustomerId"].ToString(),
Code = DataReader["Code"].ToString(),
FirstName = DataReader["FirstName"].ToString(),
LastName = DataReader["LastName"].ToString(),
Email = DataReader["Email"].ToString(),
PhoneNumber = DataReader["PhoneNumber"].ToString(),
Website = DataReader["Website"].ToString(),
Address = DataReader["Address"].ToString(),
PostCode = DataReader["PostCode"].ToString(),
City = DataReader["City"].ToString(),
Country = DataReader["Country"].ToString(),
Sex = DataReader["Sex"].ToString(),
StoreCredit = Func.ToDecimal(DataReader["StoreCredit"].ToString()),
IsFavorite = Func.ToBoolean(DataReader["IsFavorite"].ToString()),
IsVerified = Func.ToBoolean(DataReader["IsVerified"].ToString()),
DateOfBirth = Convert.ToDateTime(DataReader["DateOfBirth"].ToString()),
UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString()),
RegisteredAt = Convert.ToDateTime(DataReader["RegisteredAt"].ToString()),
Group = new Objects.CustomerGroup
{
Id = DataReader["GroupId"].ToString(),
Name = DataReader["GroupName"].ToString()
}
};
users.Add(customer);
}
if (!DataReader.HasRows)
{
// Empty
statusCode = 204;
}
else
{
// OK
statusCode = 200;
}
//Close Connection
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.DataArray = users;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Customer", "Getting Customers");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
/// <summary>
/// Get
/// </summary>
/// <returns></returns>
public Response Get(Objects.Customer customer)
{
//Props
int statusCode = 200;
Response response = new Response();
try
{
//Open MySqlConnection
Database.Connection.Open();
//Query
string Query = "SELECT * FROM Customers WHERE CustomerId=@CustomerId";
//Execute Query
MySqlCommand Command = new MySqlCommand(Query, Database.Connection);
Command.Parameters.AddWithValue("CustomerId", customer.Id);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
customer.Id = DataReader["CustomerId"].ToString();
customer.Code = DataReader["Code"].ToString();
customer.FirstName = DataReader["FirstName"].ToString();
customer.LastName = DataReader["LastName"].ToString();
customer.Email = DataReader["Email"].ToString();
customer.PhoneNumber = DataReader["PhoneNumber"].ToString();
customer.Website = DataReader["Website"].ToString();
customer.Address = DataReader["Address"].ToString();
customer.PostCode = DataReader["PostCode"].ToString();
customer.City = DataReader["City"].ToString();
customer.Country = DataReader["Country"].ToString();
customer.Sex = DataReader["Sex"].ToString();
customer.StoreCredit = Func.ToDecimal(DataReader["StoreCredit"].ToString());
customer.IsFavorite = Func.ToBoolean(DataReader["IsFavorite"].ToString());
customer.IsVerified = Func.ToBoolean(DataReader["IsVerified"].ToString());
customer.DateOfBirth = Convert.ToDateTime(DataReader["DateOfBirth"].ToString());
customer.UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString());
customer.RegisteredAt = Convert.ToDateTime(DataReader["RegisteredAt"].ToString());
}
if (!DataReader.HasRows)
{
// Bad Request
statusCode = 400;
}
else
{
// OK
statusCode = 200;
}
//Close Connection
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.Data = customer;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Customer", "Get");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
