/// <summary>
/// Gets the business categories that exist within the businesses at the said state, city, and zipcode
/// </summary>
/// <param name="business">Selected business</param>
/// <returns>List of categories</returns>
public static List <BaseInfo> BusinessCategories(List <BaseInfo> businesses)
{
if (businesses.Count > 0)
{
if (businesses[0] is Business business)
{
Select data = new Select("businesscategories");
data.AddColumns("categoryname, Count(categoryname) as total");
data.AddInnerJoin("business", "businessid");
data.SetGroupBy("categoryname");
data.AddFilter($"state = '{business.State}'");
data.AddFilter($"city = '{business.City}'");
data.AddFilter($"zipcode = '{business.Zipcode}'");
OrFilter orfilter = new OrFilter();
foreach (Business b in businesses)
{
orfilter.Add($"business.businessid = '{b.Businessid}'");
}
data.AddOrFilter(orfilter);
return(CreateList(typeof(BusinessCategory), RunQuery(data)));
}
}
return(new List <BaseInfo>());
}
/// <summary>
/// Reinitialize the Attributes list with the given businesses remaining
/// </summary>
/// <param name="businesses">List of businesses remaining</param>
/// <returns>List of attributes found</returns>
public static List <BaseInfo> BusinessAttributes(List <BaseInfo> businesses)
{
List <string> qualifiedAttributes = new List <string>(MainData.GetQualifiedAttributes().Keys);
OrFilter orfilter = new OrFilter();
foreach (string atr in qualifiedAttributes)
{
orfilter.Add($"attributename like '{atr}'");
}
if (businesses.Count > 0)
{
if (businesses[0] is Business business)
{
Select data = new Select("businessattributes");
data.AddColumns("attributename, Count(attributename) as total");
data.AddInnerJoin("business", "businessid");
data.SetGroupBy("attributename");
data.AddFilter($"state = '{business.State}'");
data.AddFilter($"city = '{business.City}'");
data.AddFilter($"zipcode = '{business.Zipcode}'");
data.AddFilter($"value <> 'False'");
data.AddFilter($"value <> 'no'");
data.AddOrFilter(orfilter);
OrFilter orfilter2 = new OrFilter();
foreach (Business b in businesses)
{
orfilter2.Add($"business.businessid = '{b.Businessid}'");
}
data.AddOrFilter(orfilter2);
return(CreateList(typeof(BusinessAttribute), RunQuery(data)));
}
}
return(new List <BaseInfo>());
}
/// <summary>
/// Query which price ranges exist among the current list of businesses
/// </summary>
/// <param name="businesses">List of businesses on the businessGrid</param>
/// <returns>bool of prices and meals that exist</returns>
public static (bool[] priceArray, Dictionary <string, bool> mealsList) BusinessPriceMealsList(List <BaseInfo> businesses)
{
return(Prices(), Meals());
// Find what priceranges exist
bool[] Prices()
{
bool[] prices = new bool[4];
Select priceQuery = new Select("businessattributes");
priceQuery.AddColumns("distinct value");
priceQuery.AddFilter($"lower(attributename) = 'restaurantspricerange2'");
priceQuery.AddOrFilter(AddBusinessOrFilter());
DataTable priceResult = RunQuery(priceQuery);
foreach (DataRow row in priceResult.Rows)
{
object[] element = row.ItemArray;
if (int.TryParse(element[0].ToString(), out int index))
{
prices[index - 1] = true;
}
}
return(prices);
}
// Find which meals are offered
Dictionary <string, bool> Meals()
{
Dictionary <string, bool> mealFilters = new Dictionary <string, bool>
{
{ "breakfast", false },
{ "brunch", false },
{ "dessert", false },
{ "dinner", false },
{ "latenight", false },
{ "lunch", false }
};
Select mealsQuery = new Select("businessattributes");
mealsQuery.AddColumns("distinct attributename, value");
mealsQuery.AddFilter($"lower(value) = 'true'");
OrFilter orfilter = new OrFilter();
foreach (string meal in mealFilters.Keys)
{
orfilter.Add($"lower(attributename) = '{meal}'");
}
mealsQuery.AddOrFilter(AddBusinessOrFilter());
mealsQuery.AddOrFilter(orfilter);
DataTable mealsResult = RunQuery(mealsQuery);
foreach (DataRow row in mealsResult.Rows)
{
object[] element = row.ItemArray;
if (element.Length > 0)
{
if (element[1].ToString() == "True")
{
mealFilters[element[0].ToString()] = true;
}
}
}
return(mealFilters);
}
// Creates a big OrFilter for this query
OrFilter AddBusinessOrFilter()
{
OrFilter returnorfilter = new OrFilter();
foreach (Business business in businesses)
{
returnorfilter.Add($"businessid = '{business.Businessid}'");
}
return(returnorfilter);
}
}
