private string GetInventorySearchQuery(Objects.InventorySearchQuery searchQuery)
{
var query = " SELECT * FROM Products ";
var append = " ORDER BY CreatedAt DESC ";
var defaultQuery = query + append;
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(defaultQuery);
case Enums.SearchQueryType.Query:
return(query + " WHERE (ProductId LIKE @SearchQuery OR Barcode LIKE @SearchQuery OR Name LIKE @SearchQuery OR Description LIKE @SearchQuery ) " + append);
case Enums.SearchQueryType.Value:
// OnStock
if (searchQuery.Stock >= ReportConstants.OnStockValue)
{
return(query + $" WHERE ( Stock >= {ReportConstants.OnStockValue} )" + append);
}
// LowStock
else if (searchQuery.Stock < ReportConstants.OnStockValue && searchQuery.Stock > ReportConstants.NoStockValue)
{
return(query + $" WHERE ( Stock < {ReportConstants.OnStockValue} AND Stock > {ReportConstants.NoStockValue} )" + append);
}
// NoStock
else
{
return(query + $" WHERE ( Stock <= {ReportConstants.NoStockValue} ) " + append);
}
case Enums.SearchQueryType.DateRange:
return(query + " WHERE CreatedAt BETWEEN @DateFrom AND @DateTo " + append);
default:
return(defaultQuery);
}
}
/// <summary>
/// Get Inventory Products
/// </summary>
/// <returns></returns>
public Response GetInventoryProducts(Objects.InventorySearchQuery searchQuery, Objects.Pagination pagination)
{
int statusCode = 200;
Response response = new Response();
List <object> products = new List <object>();
try
{
Database.Connection.Open();
var q = $"%{searchQuery.Query}%";
var query = GetInventorySearchQuery(searchQuery) + Func.GetPaginationLimit(pagination);
//Execute Query
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SearchQuery", q);
Command.Parameters.AddWithValue("StockQuery", searchQuery.StockQuery);
Command.Parameters.AddWithValue("DateFrom", searchQuery.DateFrom);
Command.Parameters.AddWithValue("DateTo", searchQuery.DateTo);
Command.Parameters.AddWithValue("Status", searchQuery.Status);
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()),
IsBalanceRequired = Func.ToBoolean(DataReader["IsBalanceRequired"].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()),
UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString()),
CreatedAt = Convert.ToDateTime(DataReader["CreatedAt"].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;
response.Pagination = pagination;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Product", "Getting Products");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
