private string GetSellSearchQuery(Objects.ProductSearchQuery searchQuery, bool hasLimit = false)
{
var query = " SELECT DISTINCT Products.*, ProductDiscounts.DiscountId, " +
" IF(ProductDiscounts.DiscountId IS NOT NULL AND " +
" ( NOW() BETWEEN( SELECT Discounts.StartDate FROM Discounts " +
" WHERE Discounts.DiscountId = ProductDiscounts.DiscountId ) " +
" AND( SELECT Discounts.EndDate FROM Discounts WHERE Discounts.DiscountId = ProductDiscounts.DiscountId))," +
" TRUE, FALSE) " +
" AS IsDiscounted FROM Products " +
" LEFT JOIN ProductDiscounts " +
" ON(Products.ProductId = ProductDiscounts.ProductId) " +
" LEFT JOIN Discounts " +
" ON(Discounts.DiscountId = ProductDiscounts.DiscountId)";
/** Old Query
* var oldQuery = "SELECT DISTINCT Products.*, ProductDiscounts.DiscountId, " +
* "IF(ProductDiscounts.DiscountId IS NULL, FALSE, TRUE) AS IsDiscounted FROM Products " +
* "LEFT JOIN ProductDiscounts " +
* "ON(Products.ProductId = ProductDiscounts.ProductId) ";*/
var append = " ORDER BY CreatedAt DESC ";
append += (hasLimit) ? " LIMIT @Limit" : null;
var defaultQuery = query + append;
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(defaultQuery);
case Enums.SearchQueryType.Query:
return(query + " WHERE (Products.Barcode LIKE @SearchQuery OR Products.Name LIKE @SearchQuery OR Products.Description LIKE @SearchQuery ) " + append);
case Enums.SearchQueryType.Barcode:
return(query + " WHERE Barcode=@Barcode " + append);
default:
return(defaultQuery);
}
}
private string GetSearchQuery(Objects.ProductSearchQuery searchQuery, bool hasLimit = false)
{
var append = (hasLimit) ? " LIMIT @Limit" : null;
var defaultQuery = " SELECT * FROM Products ORDER BY CreatedAt DESC " + append;
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(defaultQuery);
case Enums.SearchQueryType.Query:
return(" SELECT DISTINCT * FROM Products WHERE (Name LIKE @SearchQuery OR Description LIKE @SearchQuery ) ORDER BY CreatedAt DESC" + append);
case Enums.SearchQueryType.Id:
return(" SELECT DISTINCT * FROM Products WHERE (SupplierId=@SupplierId OR BrandId=@BrandId OR TypeId=@TypeId) " + append);
default:
return(defaultQuery);
}
}
/// <summary>
/// Get Sell Products
/// </summary>
/// <returns></returns>
public Response GetSellProducts(Objects.ProductSearchQuery searchQuery, int limit = 0)
{
int statusCode = 200;
Response response = new Response();
List <object> products = new List <object>();
try
{
Database.Connection.Open();
var q = $"%{searchQuery.Query}%";
var query = GetSellSearchQuery(searchQuery, limit != 0);
//Execute Query
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SearchQuery", q);
Command.Parameters.AddWithValue("Barcode", searchQuery.Barcode);
Command.Parameters.AddWithValue("Limit", limit);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
Objects.Product product = new Objects.Product
{
Id = DataReader["ProductId"].ToString(),
Barcode = DataReader["Barcode"].ToString(),
Name = DataReader["Name"].ToString(),
Description = DataReader["Description"].ToString(),
Stock = Func.ToDecimal(DataReader["Stock"].ToString()),
SupplyPrice = Func.ToDecimal(DataReader["SupplyPrice"].ToString()),
RetailPrice = Func.ToDecimal(DataReader["RetailPrice"].ToString()),
Markup = Func.ToDecimal(DataReader["Markup"].ToString()),
ImageBlobId = DataReader["ImageBlobId"].ToString(),
Color = ColorUtil.HEXToColor(DataReader["Color"].ToString()),
IsBalanceRequired = Func.ToBoolean(DataReader["IsBalanceRequired"].ToString()),
IsDiscounted = Func.ToBoolean(DataReader["IsDiscounted"].ToString()),
UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString()),
CreatedAt = Convert.ToDateTime(DataReader["CreatedAt"].ToString()),
Discount = new Objects.DiscountProduct
{
DiscountId = DataReader["DiscountId"].ToString(),
ProductId = DataReader["ProductId"].ToString(),
},
Tax = new Objects.Tax {
Id = DataReader["TaxId"].ToString()
},
Brand = new Objects.ProductBrand {
Id = DataReader["BrandId"].ToString()
},
Supplier = new Objects.ProductSupplier {
Id = DataReader["SupplierId"].ToString()
},
Type = new Objects.ProductType {
Id = DataReader["TypeId"].ToString()
},
IsSearchItem = true,
};
products.Add(product);
}
if (!DataReader.HasRows)
{
// Nothing
statusCode = 204;
}
else
{
// OK
statusCode = 200;
}
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.DataArray = products;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Product", "Getting Sell Products");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
