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