private string GetSearchQuery(Objects.CustomerSearchQuery searchQuery, bool hasLimit = false)
{
var order = " ORDER BY Customers.RegisteredAt DESC ";
var append = (hasLimit) ? order + " LIMIT @Limit" : order;
var q = " SELECT DISTINCT Customers.*, CustomerGroups.Name AS GroupName " +
" FROM Customers " +
" LEFT OUTER JOIN CustomerGroups " +
" ON CustomerGroups.GroupId = Customers.GroupId ";
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(q);
case Enums.SearchQueryType.Id:
return(q + " WHERE Customers.GroupId=@GroupId " + append);
case Enums.SearchQueryType.Query:
return(q + " WHERE (Customers.FirstName LIKE @SearchQuery OR Customers.Code LIKE @SearchQuery OR Customers.Email " +
" LIKE @SearchQuery OR Customers.LastName LIKE @SearchQuery) " + append);
default:
return(q);
}
}
/// <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);
}