/// <summary>
/// Updates the business grid checking all filters in the GUI
/// </summary>
/// <param name="business">Business to get state, city, and zip</param>
/// <param name="categories">Selected categories</param>
/// <param name="price">Price checkmarked</param>
/// <param name="meals">Meals checkmarked</param>
/// <param name="attributes">Selected attributes</param>
/// <returns>List of businesses remaining</returns>
public static List <BaseInfo> UpdateBusinessGrid(Business business, List <BusinessCategory> categories, string price, List <string> meals, List <BusinessAttribute> attributes, Users users, string orderby, List <string> additionalFilters = null)
{
Select innerData = new Select("businesscategories");
innerData.AddColumns($"name, address, city, state, zipcode, reviewcount, reviewrating, numcheckins, stars, business.businessid, categoryname, latitude, longitude, distance('{users.Latitude}', '{users.Longitude}', business.latitude, business.longitude)");
innerData.AddInnerJoin("business", "businessid");
innerData.AddFilter($"state = '{business.State}'");
innerData.AddFilter($"city = '{business.City}'");
innerData.AddFilter($"zipcode = '{business.Zipcode}'");
foreach (BusinessCategory category in categories)
{
innerData.AddFilter("exists (select * from businesscategories as b1 where b1.businessid = businesscategories.businessid and b1.categoryname = '" + category.Categoryname.Replace("'", "''") + "')");
}
foreach (BusinessAttribute attribute in attributes)
{
if (attribute.Attributename.Equals("WiFi"))
{
innerData.AddFilter("exists (select * from businessattributes as b2 where b2.businessid = businessattributes.businessid and b2.attributename = '" + attribute.Attributename.Replace("'", "''") + "' and b2.value <> 'False' and b2.value <> 'no')");
}
else
{
innerData.AddFilter("exists (select * from businessattributes as b2 where b2.businessid = businessattributes.businessid and b2.attributename = '" + attribute.Attributename.Replace("'", "''") + "')");
}
}
if (!string.IsNullOrEmpty(price) || meals.Count > 0 || attributes.Count > 0)
{
innerData.AddInnerJoin("businessattributes", "businessid");
if (!string.IsNullOrEmpty(price))
{
innerData.AddFilter($"(businessattributes.attributename = 'RestaurantsPriceRange2' and businessattributes.value = '{price}')");
}
if (meals.Count > 0)
{
foreach (string meal in meals)
{
innerData.AddFilter($"exists (select attributename, value from businessattributes as ba1 where ba1.businessid = businesscategories.businessid and (ba1.attributename = '{meal.ToLower()}' and ba1.value = 'True'))");
}
}
}
Select outerData = new Select($"({Build(innerData)}) as temp");
outerData.AddColumns("distinct name, address, city, state, zipcode, reviewcount, reviewrating, numcheckins, stars, businessid, latitude, longitude, distance");
if (additionalFilters != null)
{
foreach (string filter in additionalFilters)
{
outerData.AddFilter(filter);
}
}
outerData.SetOrderBy(string.IsNullOrEmpty(orderby) ? "name" : orderby);
return(CreateList(typeof(Business), RunQuery(outerData)));
}
/// <summary>
/// Gets the reviews for a selected business that were left by friends of the user
/// </summary>
/// <param name="business">Selected business</param>
/// <param name="users">Current user</param>
/// <returns>List of reviews</returns>
public static List <BaseInfo> BusinessFriendsReviews(Business business, Users users)
{
Select data = new Select("review");
data.AddColumns("reviewid, review.userid, businessid, reviewtext, stars, reviewdate, review.funny, review.useful, review.cool, name as username");
data.AddFilter($"review.businessid = '{business.Businessid}'");
data.AddFilter($"friends.userid = '{users.Userid}'");
data.AddInnerJoin("users", "userid");
data.AddInnerJoin("friends", "userid", "isfriend");
data.SetOrderBy("reviewdate");
return(CreateList(typeof(Review), RunQuery(data)));
}
/// <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>
/// Parses the array of query results and stores them into a list of Review instances
/// </summary>
/// <param name="currentUser">Selected User</param>
/// <returns>List of Reviews</returns>
public static List <BaseInfo> FriendsReviews(Users currentUser)
{
// Friend Reviews
Select data = new Select("review");
data.AddColumns("review.userid, users.name as uname, business.name as bname, review.stars, review.reviewdate, review.reviewtext, (row_number() over(partition by review.userid, users.name order by reviewdate desc)) as rn");
data.AddFilter($"friends.userid = '{currentUser.Userid}'");
data.AddInnerJoin("friends", "userid", "isfriend");
data.AddInnerJoin("business", "businessid");
data.AddInnerJoin("users", "userid");
Select outerData = new Select($"({Build(data)}) as p3");
outerData.AddColumns("p3.uname as username, p3.bname as businessname, p3.stars, p3.reviewtext, p3.reviewdate");
outerData.AddFilter("rn = 1");
outerData.SetOrderBy("p3.reviewdate desc");
return(CreateList(typeof(Review), RunQuery(outerData)));
}
/// <summary>
/// Parses the array of query results and stores them into a list of Users instances
/// </summary>
/// <param name="currentUser">Selected user</param>
/// <returns>List of Users</returns>
public static List <BaseInfo> FriendsInformation(Users currentUser)
{
Select data = new Select("friends");
data.AddColumns("users.userid, name, yelpingsince, reviewcount, fans, averagestars, funny, useful cool, latitude, longitude");
data.AddInnerJoin("users", "isfriend", "userid");
data.AddFilter($"friends.userid = '{currentUser.Userid}'");
data.SetOrderBy("name");
return(CreateList(typeof(Users), RunQuery(data)));
}
/// <summary>
/// Gets most recent reviews of a business
/// </summary>
/// <param name="business"></param>
/// <returns></returns>
public static List <BaseInfo> RecentBusinessReviews(Business business)
{
Select data = new Select("review");
data.AddColumns("name as username, stars, reviewdate, reviewtext");
data.AddInnerJoin("users", "userid");
data.AddFilter($"businessid = '{business.Businessid}'");
data.SetOrderBy("reviewdate desc");
return(CreateList(typeof(Review), RunQuery(data)));
}
/// <summary>
/// Parses the array of query results and stores them into a list of Review instances
/// </summary>
/// <param name="currentUser">Selected user</param>
/// <returns>List of Reviews</returns>
public static List <BaseInfo> RecentReviews(Users currentUser)
{
Select data = new Select("review");
data.AddColumns("reviewdate, business.name as businessname, review.stars, review.reviewtext, review.funny, review.useful, review.cool");
data.AddInnerJoin("business", "businessid");
data.AddFilter($"userid = '{currentUser.Userid}'");
data.SetOrderBy("reviewdate desc");
return(CreateList(typeof(Review), RunQuery(data)));
}
/// <summary>
/// Parses the array of query results and stores them into a list of Business instances
/// </summary>
/// <param name="currentUser">Selected user</param>
/// <returns>List of Businesses</returns>
public static List <BaseInfo> FavoriteBusiness(Users currentUser)
{
Select data = new Select("favorite");
data.AddColumns("business.businessid, name, address, city, state, zipcode, reviewrating, reviewcount, numcheckins, latitude, longitude");
data.AddInnerJoin("business", "businessid");
data.AddFilter($"userid = '{currentUser.Userid}'");
return(CreateList(typeof(Business), RunQuery(data)));
}
/// <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>());
}