public static void InsertTip(Tip tip)
{
string sqlStr = "INSERT INTO Tip(user_id, business_id, tipDate, text, numLikes) " +
"VALUES ('" + tip.UserID + "', '" + tip.BusinessID + "', current_timestamp, '" + tip.Text + "', " + tip.NumberOfLikes + ");";
YelpDatabaseQueries.ExecuteNonQuery(sqlStr);
}
public static void InsertCheckin(Checkin checkin)
{
string sqlStr = "INSERT INTO Checkins(year, month, day, time, business_id) " +
"VALUES ('" + checkin.Year + "', '" + checkin.Month + "', '" + checkin.Day + "', '" + checkin.Time + "', '" + checkin.BusinessID + "');";
YelpDatabaseQueries.ExecuteNonQuery(sqlStr);
}
public static void GetBusinessAttributes(Action <NpgsqlDataReader> myf, string businessID)
{
string sqlStr = "SELECT attribute_name, value " +
"FROM businessattributes " +
"WHERE business_id = '" + businessID + "'";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetBusinessIDs(Action <NpgsqlDataReader> myf, string businessName)
{
string sqlStr = "SELECT business_id " +
"FROM Business " +
"WHERE business_name = '" + businessName + "'";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetUserIDs(Action <NpgsqlDataReader> myf, string username)
{
string sqlStr = "SELECT user_id " +
"FROM _User " +
"WHERE user_name = '" + username + "'";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void UpdateUser(string user_id, double lat, double longg)
{
string sqlStr = "UPDATE _user " +
" SET latitude = :latitude, longitude = :longitude" +
" WHERE user_id = '" + user_id + "'";
YelpDatabaseQueries.ExecuteUserLatLongUpdate(sqlStr, lat, longg);
}
public static void GetBusinessCategoriesInSelectedZipcode(Action <NpgsqlDataReader> myf, string selectedState, string selectedCity, string selectedZipcode)
{
string sqlStr = "SELECT distinct category_name " +
"FROM Business, Categories " +
"WHERE state = '" + selectedState + "' AND city = '" + selectedCity + "' AND zipcode = '" + selectedZipcode + "' AND Business.business_id = Categories.business_id ";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetUserFriends(Action <NpgsqlDataReader> myf, string userID)
{
string sqlStr = "SELECT user_name, total_tip_likes, avg_stars, join_date " +
" FROM(Select friend_id FROM friends WHERE '" + userID + "' = user_ID) as Temp, _User " +
" WHERE Temp.friend_id = _User.user_id";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetUser(Action <NpgsqlDataReader> myf, string userID)
{
string sqlStr = "SELECT user_id, user_name, join_date, numFans, avg_stars, cool, funny, useful, total_tip_count, total_tip_likes, longitude, latitude " +
"FROM _User " +
"WHERE user_id = '" + userID + "'";
YelpDatabaseQueries.ExecuteQuerySingle(sqlStr, myf);
}
public static void GetBusiness(Action <NpgsqlDataReader> myf, string businessID)
{
string sqlStr = "SELECT business_id, business_name, address, city, state, zipcode, stars, numCheckins, numTips " +
"FROM Business " +
"WHERE business_id = '" + businessID + "'";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetBusinessCategories(Action <NpgsqlDataReader> myf, string businessID)
{
string sqlStr = "SELECT category_name " +
"FROM Categories " +
"WHERE business_id = '" + businessID + "'";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetDistinctStates(Action <NpgsqlDataReader> myf)
{
string sqlStr = "SELECT distinct state " +
"FROM business " +
"ORDER BY state";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetBusinessHours(Action <NpgsqlDataReader> myf, string businessID, string dayOfTheWeek)
{
string sqlStr = "SELECT open, close " +
"FROM Hours " +
"WHERE day_of_the_week = '" + dayOfTheWeek + "' AND business_id = '" + businessID + "'";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void LikeTip(Tip tip)
{
string sqlStr = "UPDATE tip " +
$"SET numlikes = 1 + (SELECT numLikes FROM tip WHERE tip.user_id = '{tip.UserID}' AND tip.business_id = '{tip.BusinessID}' AND tip.tipdate = '{tip.TipDate}') " +
$"WHERE tip.user_id = '{tip.UserID}' AND tip.business_id = '{tip.BusinessID}' AND tip.tipdate = '{tip.TipDate}'";
YelpDatabaseQueries.ExecuteNonQuery(sqlStr);
}
public static void GetBusinessTips(Action <NpgsqlDataReader> myf, string businessID)
{
string sqlStr = "SELECT Tip.user_id, Tip.business_id, Tip.tipDate, Tip.text, Tip.numLikes, _User.user_name " +
"FROM Tip, _User " +
"WHERE Tip.business_id = '" + businessID + "' AND _User.user_id = Tip.user_id " +
"ORDER BY Tip.tipDate DESC";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetZipcodesInSelectedCity(Action <NpgsqlDataReader> myf, string selectedState, string selectedCity)
{
string sqlStr = "SELECT distinct zipcode " +
"FROM business " +
"WHERE state = '" + selectedState + "' AND city = '" + selectedCity + "' " +
"ORDER BY zipcode";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetBusinessLatestTips(Action <NpgsqlDataReader> myf, string businessID)
{
string sqlStr = "SELECT u.user_name, t.tipDate, t.numLikes, t.text " +
"FROM _User as u, Business as b, Tip as t " +
"WHERE b.Business_id = '" + businessID + "'" + " AND b.business_id = t.business_id AND u.user_id = t.user_id " +
"ORDER BY t.tipDate DESC";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetCitiesInSelectedState(Action <NpgsqlDataReader> myf, string selectedState)
{
string sqlStr = "SELECT distinct city " +
"FROM business " +
"WHERE state = '" + selectedState + "' " +
"ORDER BY city";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetRecentCheckinTimes(Action <NpgsqlDataReader> myf, string businessID)
{
string sqlStr = "SELECT month, day, year, time " +
"FROM CheckIns " +
"WHERE business_id = '" + businessID + "' " +
"ORDER BY year DESC, month DESC, day DESC";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetFriendsTips(Action <NpgsqlDataReader> myf, string businessID, string userID)
{
string sqlStr = "SELECT Tip.tipDate, Tip.text, _User.user_name " +
"FROM Tip, _User " +
"WHERE Tip.business_id = '" + businessID + "' AND Tip.user_id IN (SELECT friend_id FROM Friends WHERE Friends.user_id = '" + userID + "') AND _User.user_id = Tip.user_id " +
"ORDER BY Tip.tipDate DESC";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetBusinessCheckin(Action <NpgsqlDataReader> myf, string businessID)
{
string sqlStr = "SELECT month, count(month) AS numCheckins " +
"FROM checkins " +
"WHERE business_id = '" + businessID + "' " +
"GROUP BY month " +
"ORDER BY month ASC";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetUserFriendsLatestTips(Action <NpgsqlDataReader> myf, string userID)
{
string sqlStr = "Select a.user_name, business_name, city, c.text, c.tipdate " +
" FROM (SELECT user_name, _User.user_id " +
" FROM (Select friend_id " +
" FROM friends " +
" WHERE '" + userID + "' = user_ID) as Temp, _User " +
" WHERE Temp.friend_id = _User.user_id) as a, (SELECT tip.user_id, tip.business_id, tip.text, tip.tipdate " +
" FROM tip, (SELECT tip.user_id, max(tipdate) as maxdate " +
" FROM tip " +
" GROUP BY user_id) as b " +
" WHERE tip.user_ID = b.user_ID AND tip.tipdate = b.maxdate) as c, business " +
" WHERE a.user_id = c.user_id AND c.business_id = business.business_ID";
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
public static void GetBusinesses(Action <NpgsqlDataReader> myf, BusinessSearch businessSearch)
{
string sqlStr = "SELECT DISTINCT Business.business_id, Business.business_name, Business.address, Business.city, Business.state, Business.zipcode, Business.longitude, Business.latitude, Business.stars, Business.numCheckins, Business.numTips, ";
if (businessSearch.CurrentUser != null)
{
sqlStr += "Distance(" + businessSearch.CurrentUser.Latitude + ", " + businessSearch.CurrentUser.Longitude + ", Business.latitude, Business.longitude) AS distance ";
}
else
{
sqlStr += "-1 as distance ";
}
sqlStr += "FROM Business";
int temp = 0;
foreach (string category in businessSearch.BusinessCategories)
{
sqlStr += " JOIN Categories AS Category" + temp + " ON Category" + temp++ + ".business_id = Business.business_id";
}
temp = 0;
foreach (string attribute in businessSearch.BusinessAttributes.Keys)
{
sqlStr += " JOIN BusinessAttributes AS Attributes" + temp + " ON Attributes" + temp++ + ".business_id = Business.business_id";
}
sqlStr += " WHERE Business.state = '" + businessSearch.State + "' AND Business.city = '" + businessSearch.City + "' AND Business.zipcode = '" + businessSearch.Zipcode + "'";
temp = 0;
foreach (string category in businessSearch.BusinessCategories)
{
sqlStr += " AND Category" + temp++ + ".category_name = '" + category + "'";
}
temp = 0;
foreach (string attribute in businessSearch.BusinessAttributes.Keys)
{
sqlStr += " AND Attributes" + temp + ".attribute_name = '" + attribute + "' AND Attributes" + temp++ + ".value = '";
string attributeValue = string.Empty;
switch (attribute)
{
case "RestaurantsPriceRange2":
if (businessSearch.BusinessAttributes.TryGetValue(attribute, out attributeValue))
{
sqlStr += attributeValue;
}
break;
case "WiFi":
sqlStr += "free";
break;
default:
sqlStr += "True";
break;
}
sqlStr += "'";
}
sqlStr += " ORDER BY ";
switch (businessSearch.SortingMethod)
{
case "Name (default)":
sqlStr += "Business.business_name ASC";
break;
case "Highest Rating":
sqlStr += "Business.stars DESC";
break;
case "Most Tips":
sqlStr += "Business.numTips DESC";
break;
case "Most Check-ins":
sqlStr += "Business.numCheckins DESC ";
break;
case "Nearest":
if (businessSearch.CurrentUser != null)
{
sqlStr += "distance ASC";
}
else
{
sqlStr += "Business.business_name ASC";
}
break;
}
YelpDatabaseQueries.ExecuteQuery(sqlStr, myf);
}
