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); }