Пример #1
0
        /// <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>());
        }
Пример #2
0
        /// <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)));
        }
Пример #3
0
        /// <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)));
        }
Пример #4
0
        /// <summary>
        /// Get the hours for a business
        /// </summary>
        /// <param name="business"></param>
        /// <returns></returns>
        public static List <BaseInfo> BusinessHours(Business business)
        {
            Select data = new Select();

            data.AddColumns("day, business.businessid, opentime, closetime");
            data.AddFrom($"business left outer join businesshours on business.businessid = businesshours.businessid ");
            data.AddFilter($"business.businessid = '{business.Businessid}'");

            return(CreateList(typeof(BusinessHours), RunQuery(data)));
        }
Пример #5
0
        /// <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)));
        }
Пример #6
0
        /// <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)));
        }
Пример #7
0
        /// <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)));
        }
Пример #8
0
        /// <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)));
        }
Пример #9
0
        /// <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)));
        }
Пример #10
0
        /// <summary>
        /// Inserts a written review into the database
        /// </summary>
        /// <param name="review">Review written</param>
        public static void SubmitReview(Review review)
        {
            string reviewid = GenerateId();
            Select data     = new Select("review");

            data.AddColumns("reviewid");
            data.AddFilter($"reviewid = '{reviewid}'");
            bool duplicate = true;

            // Check to see (however unlikely) if our reviewid generated already exists
            while (duplicate)
            {
                if (RunQuery(data).Rows.Count == 0)
                {
                    duplicate = false;
                }
                else
                {
                    Reinitialize();
                }
            }

            review.Reviewid = reviewid;

            // Reviewid is unique and not a duplicate, start the insert
            Insert insertData = new Insert("review");

            insertData.AddParameter("reviewid", review.Reviewid);
            insertData.AddParameter("userid", review.Userid);
            insertData.AddParameter("businessid", review.Businessid);
            insertData.AddParameter("stars", review.Stars);
            insertData.AddParameter("reviewtext", review.Reviewtext);
            RunNonCommand(insertData);

            // Generate a new ID and clear the old one
            void Reinitialize()
            {
                reviewid = GenerateId();
                data.ClearFilter();
                data.AddFilter($"reviewid = '{reviewid}'");
            }
        }
Пример #11
0
        /// <summary>
        /// Get checkins for a business
        /// </summary>
        /// <param name="business">business</param>
        /// <returns>Dictionary with day and count</returns>
        public static Dictionary <string, int> GetCheckIns(Business business)
        {
            Select data = new Select("businesscheckins");

            data.AddColumns("day, sum(count)");
            data.AddFilter($"businessid = '{business.Businessid}'");
            data.SetGroupBy("day");

            DataTable dt = RunQuery(data);
            Dictionary <string, int> checkins = new Dictionary <string, int>();

            // Parse the DataTable and store them in a dictionary
            foreach (DataRow row in dt.Rows)
            {
                object[] element = row.ItemArray;
                checkins.Add(element[0].ToString(), Convert.ToInt32(element[1]));
            }

            return(checkins);
        }
Пример #12
0
        /// <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>());
        }
Пример #13
0
        /// <summary>
        /// Gets the Attributes, Categories, and Hours for the selected business
        /// </summary>
        /// <param name="business">Selected business</param>
        /// <returns>Dictionary of all the information</returns>
        public static Dictionary <string, string> BusinessInformation(Business business)
        {
            Dictionary <string, string> queryDictionary = new Dictionary <string, string>();
            StringBuilder sb = new StringBuilder();

            Select attributes = new Select("businessattributes");

            attributes.AddColumns("attributename, value");
            attributes.AddFilter($"businessid = '{business.Businessid}'");
            attributes.AddFilter("value <> 'False'");
            attributes.SetOrderBy("attributename");
            DataTable queryResults = RunQuery(attributes);

            // Attributes
            foreach (DataRow row in queryResults.Rows)
            {
                object[] element = row.ItemArray;
                if (element[1].ToString().Trim().ToLower() == "true")
                {
                    sb.AppendLine(element[0].ToString() + ", ");
                }
                else
                {
                    sb.AppendLine(element[0].ToString() + "(" + element[1].ToString() + "), ");
                }
            }

            // Remove the final appendline and comma
            if (sb.Length > 4)
            {
                sb.Remove(sb.Length - 4, 4);
            }

            queryDictionary.Add("attributes", sb.ToString());
            queryResults.Clear();
            sb.Clear();

            Select categories = new Select("businesscategories");

            categories.AddColumns("categoryname");
            categories.AddFilter($"businessid = '{business.Businessid}'");
            categories.SetOrderBy("categoryname");
            queryResults = RunQuery(categories);

            // Categories
            foreach (DataRow row in queryResults.Rows)
            {
                object[] element = row.ItemArray;
                sb.AppendLine(element[0].ToString());
            }
            queryDictionary.Add("categories", sb.ToString());

            queryResults.Clear();
            sb.Clear();

            Select innerHours = new Select("business");

            innerHours.AddColumns("to_char(current_timestamp, 'FMDay'::text) as today, business.businessid");

            Select hours = new Select();

            hours.AddColumns("today, opentime, closetime");
            hours.AddFrom($"({Build(innerHours)}) as tempquery left outer join businesshours on tempquery.businessid = businesshours.businessid and tempquery.today = businesshours.day ");
            hours.AddFilter($"tempquery.businessid = '{business.Businessid}'");

            queryResults = RunQuery(hours);

            // Hours
            foreach (DataRow row in queryResults.Rows)
            {
                object[] element = row.ItemArray;
                sb.AppendLine("Today (" + element[0].ToString() + ")");
                sb.AppendLine($"  Opens: {ConvertTimeSpan(element[1])}");
                sb.AppendLine($"  Closes: {ConvertTimeSpan(element[2])}");
            }
            queryDictionary.Add("hours", sb.ToString());

            return(queryDictionary);
        }
Пример #14
0
        /// <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);
            }
        }