/// <summary>
/// Delete
/// </summary>
public Response Delete(Objects.ProductSupplier supplier)
{
//Props
int statusCode = 204;
Response response = new Response();
try
{
Database.Connection.Open();
string Query = "DELETE FROM ProductSuppliers WHERE SupplierId=@SupplierId ";
//Execute Query With Connection => DataReader
MySqlCommand Command = new MySqlCommand(Query, Database.Connection);
Command.Parameters.AddWithValue("SupplierId", supplier.Id);
//Execute Command
Command.ExecuteNonQuery();
//Close Connection
Database.Connection.Close();
//[HTTP Status Code] OK : Successfully Deleted : Returning No Content
statusCode = 204;
response.StatusCode = statusCode;
}
catch (MySqlException e)
{
Logger.QueryError(e, "ProductSupplier", "Delete ");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
/// <summary>
/// Checks if Id is valid
/// </summary>
/// <returns></returns>
public bool IsValid(Objects.ProductSupplier supplier)
{
bool isValid;
try
{
//Open Connection
Database.Connection.Open();
string query = "SELECT SupplierId FROM ProductSuppliers WHERE SupplierId=@SupplierId";
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SupplierId", supplier.Id);
MySqlDataReader DataReader = Command.ExecuteReader();
if (!DataReader.HasRows)
{
isValid = false;
}
else
{
isValid = true;
}
//Close Connection
Database.Connection.Close();
}
catch (MySqlException e)
{
Logger.QueryError(e, "ProductSupplier", "Verifying Id");
isValid = false;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(isValid);
}
/// <summary>
/// Create a new Supplier
/// </summary>
/// <returns></returns>
public Response Create(Objects.ProductSupplier supplier)
{
//Props
int statusCode = 201;
Response response = new Response();
//Random Tokens
supplier.Id = ServerUtil.GenerateToken(TokenLengths.TokensLength);
try
{
Database.Connection.Open();
string query = " INSERT INTO ProductSuppliers (SupplierId, Name, Markup, Description, FirstName, LastName, Company, " +
" Email, PhoneNumber, Website, Address, PostCode, City, Country ) " +
" VALUES (@SupplierId, @Name, @Markup, @Description, @FirstName, @LastName, @Company, " +
" @Email, @PhoneNumber, @Website, @Address, @PostCode, @City, @Country ) ";
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SupplierId", supplier.Id);
Command.Parameters.AddWithValue("Name", supplier.Name);
Command.Parameters.AddWithValue("Markup", supplier.Markup);
Command.Parameters.AddWithValue("Description", supplier.Description);
Command.Parameters.AddWithValue("FirstName", supplier.FirstName);
Command.Parameters.AddWithValue("LastName", supplier.LastName);
Command.Parameters.AddWithValue("Company", supplier.Company);
Command.Parameters.AddWithValue("Email", supplier.Email);
Command.Parameters.AddWithValue("PhoneNumber", supplier.PhoneNumber);
Command.Parameters.AddWithValue("Website", supplier.Website);
Command.Parameters.AddWithValue("Address", supplier.Address);
Command.Parameters.AddWithValue("PostCode", supplier.PostCode);
Command.Parameters.AddWithValue("City", supplier.City);
Command.Parameters.AddWithValue("Country", supplier.Country);
Command.ExecuteNonQuery();
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, "ProductSupplier", "Create");
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
//Response
response.StatusCode = statusCode;
response.Data = supplier;
return(response);
}
/// <summary>
/// Update
public Response Update(Objects.ProductSupplier supplier)
{
int statusCode = 200;
Response response = new Response();
try
{
Database.Connection.Open();
string query = " UPDATE ProductSuppliers " +
" SET Name=@Name, Markup=@Markup, Description=@Description, FirstName=@FirstName, LastName=@LastName, Company=@Company, Email=@Email," +
" PhoneNumber=@PhoneNumber, Website=@Website, Address=@Address, PostCode=@PostCode, City=@City, Country=@Country " +
" WHERE SupplierId=@SupplierId ";
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SupplierId", supplier.Id);
Command.Parameters.AddWithValue("Name", supplier.Name);
Command.Parameters.AddWithValue("Markup", supplier.Markup);
Command.Parameters.AddWithValue("Description", supplier.Description);
Command.Parameters.AddWithValue("FirstName", supplier.FirstName);
Command.Parameters.AddWithValue("LastName", supplier.LastName);
Command.Parameters.AddWithValue("Company", supplier.Company);
Command.Parameters.AddWithValue("Email", supplier.Email);
Command.Parameters.AddWithValue("PhoneNumber", supplier.PhoneNumber);
Command.Parameters.AddWithValue("Website", supplier.Website);
Command.Parameters.AddWithValue("Address", supplier.Address);
Command.Parameters.AddWithValue("PostCode", supplier.PostCode);
Command.Parameters.AddWithValue("City", supplier.City);
Command.Parameters.AddWithValue("Country", supplier.Country);
Command.ExecuteNonQuery();
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, "ProductSupplier", "Update");
// Status Code
response.StatusCode = statusCode;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
/// <summary>
/// Get Suppliers
/// </summary>
/// <returns></returns>
public Response GetSuppliers()
{
int statusCode = 200;
Response response = new Response();
List <object> suppliers = new List <object>();
try
{
Database.Connection.Open();
string query = " SELECT DISTINCT ProductSuppliers.*, " +
" (SELECT COUNT(*) FROM Products WHERE Products.SupplierId = ProductSuppliers.SupplierId ) " +
" AS ProductsCount " +
" FROM ProductSuppliers LEFT OUTER JOIN Products " +
" ON ( (SELECT COUNT(*) FROM Products) != 0)" +
" ORDER BY ProductsCount DESC ";
//Execute Query
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
Objects.ProductSupplier supplier = new Objects.ProductSupplier
{
Id = DataReader["SupplierId"].ToString(),
Name = DataReader["Name"].ToString(),
Description = DataReader["Description"].ToString(),
FirstName = DataReader["FirstName"].ToString(),
LastName = DataReader["LastName"].ToString(),
Company = DataReader["Company"].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(),
Markup = decimal.Parse(DataReader["Markup"].ToString()),
UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString()),
CreatedAt = Convert.ToDateTime(DataReader["CreatedAt"].ToString()),
ProductsCount = Convert.ToDouble(DataReader["ProductsCount"].ToString()),
};
suppliers.Add(supplier);
}
if (!DataReader.HasRows)
{
// Nothing
statusCode = 204;
}
else
{
// OK
statusCode = 200;
}
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.DataArray = suppliers;
}
catch (MySqlException e)
{
Logger.QueryError(e, "ProductSupplier", "Getting Suppliers");
// 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.ProductSupplier supplier)
{
int statusCode = 200;
Response response = new Response();
try
{
Database.Connection.Open();
string Query = "SELECT * FROM ProductSuppliers WHERE SupplierId=@SupplierId";
//Execute Query
MySqlCommand Command = new MySqlCommand(Query, Database.Connection);
Command.Parameters.AddWithValue("SupplierId", supplier.Id);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
supplier.Id = DataReader["SupplierId"].ToString();
supplier.Name = DataReader["Name"].ToString();
supplier.Description = DataReader["Description"].ToString();
supplier.FirstName = DataReader["FirstName"].ToString();
supplier.LastName = DataReader["LastName"].ToString();
supplier.Company = DataReader["Company"].ToString();
supplier.Email = DataReader["Email"].ToString();
supplier.PhoneNumber = DataReader["PhoneNumber"].ToString();
supplier.Website = DataReader["Website"].ToString();
supplier.Address = DataReader["Address"].ToString();
supplier.PostCode = DataReader["PostCode"].ToString();
supplier.City = DataReader["City"].ToString();
supplier.Country = DataReader["Country"].ToString();
supplier.Markup = decimal.Parse(DataReader["Markup"].ToString());
supplier.UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString());
supplier.CreatedAt = Convert.ToDateTime(DataReader["CreatedAt"].ToString());
}
if (!DataReader.HasRows)
{
// Bad Request
statusCode = 400;
}
else
{
// OK
statusCode = 200;
}
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.Data = supplier;
}
catch (MySqlException e)
{
Logger.QueryError(e, "ProductSupplier", "Get");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
