public bool addRest(Restaurant rest)
{
bool price = false;
bool url = false;
string select = "'" + rest.Name + "', city.id, 0";
if (rest.PriceRange != null)
{
select += ", '" + rest.PriceRange + "'";
price = true;
}
select += ", 0";
if (!rest.URL.Equals(""))
{
select += ", '" + rest.URL + "'";
url = true;
}
select += ", '" + username + "' from city";
string where = "city.name = '" + rest.City + "'";
string values = "name, city_id,rating ,";
if (price)
{
values += "price_range,";
}
values += "numbers_of_reviews,";
if (url)
{
values += "url_ta,";
}
values += "owner ";
// insert restaurant
bool result = dBConnect.InsertSelect(select, where, "restaurants", values);
if (result == false)
{
return(false);
}
int id = dBConnect.select_last_inserted_id();
if (id == -1)
{
return(false);
}
// insert styles
if (rest.Types.Count != 0)
{
select = id.ToString() + ", style.id from style ";
foreach (string type in rest.Types)
{
where = " style.style = \'" + type + "\';";
result = dBConnect.InsertSelect(select, where, "style_rest", null);
if (result == false)
{
return(false);
}
}
}
int new_id = dBConnect.select_last_inserted_id();
if (new_id == -1)
{
return(false);
}
restsResults[RestsResults.Count - 1].ID = new_id.ToString();
return(true);
}
public List <Restaurant> getRestByFilter()
{
// create sql query
string select = "restaurants.ID_TA, restaurants.name, city.Name as city, country.Name as country, restaurants.rating, restaurants.owner";
string from = "restaurants join city on restaurants.city_id = city.id join country on city.countrycode = country.code";
string where;
if (isClient == true)
{
if (RestName == null)
{
where = "restaurants.city_id = (select city.id from city where city.name = '" + CitiesFilter[0] + "')";
if (TypesFilter.Count != 0)
{
if (TypesFilter[0] != null)
{
where += " And restaurants.ID_TA IN (select ID_TA from style_rest join style on style_rest.styleid = style.id where style.style='" + TypesFilter[0] + "'";
for (int i = 1; i < TypesFilter.Count; i++)
{
where += " OR style.style='" + TypesFilter[i] + "'";
}
where += ")";
}
}
List <string> ranges = calculatePriceRange();
if (ranges.Count != 0)
{
where += " And (restaurants.Price_Range = '" + ranges[0] + "'";
for (int i = 1; i < ranges.Count; i++)
{
where += " OR Price_Range = '" + ranges[i] + "'";
}
where += ")";
}
if (RateFilter.Count != 0)
{
where += " And restaurants.rating Between " + RateFilter[0].ToString() + " AND " + RateFilter[1].ToString();
}
}
else
{
where = " restaurants.Name LIKE '%" + RestName + "%'";
}
}
else
{
where = "restaurants.owner = '" + UserName + "'";
}
/*if (top5RestValue[0] != null)
* {
* //add filter of 5 top rest
* where = top5RestValue[0] + where + top5RestValue[1];
* }*/
string orderByValue = null;
string order = null;
if (Order != null)
{
orderByValue = Order;
if (Asc == true)
{
order = "Asc";
}
else
{
order = "Desc";
}
}
bool endReading = false;
List <Restaurant> all_rest = new List <Restaurant>();
List <string>[] rest = dBConnect.SelectRest(from, where, orderByValue, order, select, 10, queryContinue, ref endReading);
if (endReading == true)
{
EndOfRests = true;
}
else
{
EndOfRests = false;
}
if (rest == null)
{
return(null);
}
select = "restaurants.ID_TA, style.style";
from = "restaurants join style_rest on restaurants.ID_TA = style_rest.ID_TA join style on style.id = style_rest.styleid";
for (int i = 0; i < rest[0].Count; i++)
{
Restaurant new_rest = new Restaurant(rest[0][i], rest[1][i], rest[4][i], rest[2][i], null, calculateRate(Convert.ToDouble(rest[3][i])), null, -1, null, null, rest[5][i]);
// condition for specific id -restaurant-styles
string id = rest[0][i];
where = " restaurants.ID_TA='" + id + "'";
List <string>[] dbStyles = dBConnect.Select(from, where, null, null, select, 1);
if (dbStyles == null)
{
return(null);
}
List <string> styles = new List <string>();
if (dbStyles[9].Count != 0)
{
styles.Add(dbStyles[9][0]);
new_rest.Types = styles;
}
all_rest.Add(new_rest);
}
return(all_rest);
}
public Restaurant restDetails()
{
Restaurant currRest = RestsResults[restsResults.FindIndex(x => x.ID == RestID)];
// create sql query
string select = "restaurants.price_range, restaurants.Numbers_of_reviews, restaurants.url_TA ";
string from = "restaurants";
string where = "restaurants.ID_TA = " + currRest.ID;
List <string>[] rest = dBConnect.Select(from, where, null, null, select, -1);
if (rest == null)
{
return(null);
}
select = "restaurants.ID_TA, style.style";
from = "restaurants inner join style_rest on restaurants.ID_TA = style_rest.ID_TA inner join style on style.id = style_rest.styleid";
for (int i = 0; i < rest[0].Count; i++)
{
currRest.PriceRange = rest[4][i];
currRest.NumOfReviews = Convert.ToInt32(rest[7][i]);
currRest.URL = rest[5][i];
// condition for specific id -restaurant-styles
;
where = " restaurants.ID_TA='" + currRest.ID + "' ";
List <string>[] dbStyles = dBConnect.Select(from, where, null, null, select, 3);
if (dbStyles == null)
{
return(null);
}
List <string> styles = new List <string>();
for (int j = 0; j < dbStyles[0].Count; j++)
{
styles.Add(dbStyles[9][j]);
}
// get -restaurant -reviews
List <string>[] dbReviews = dBConnect.Select("reviews", "ID_TA= " + currRest.ID, null, null, null, -1);
if (dbReviews == null)
{
return(null);
}
List <UserReview> listReviews = new List <UserReview>();
for (int j = 0; j < dbReviews[0].Count; j++)
{
double rate;
if (Convert.ToDouble(dbReviews[12][j]) == -1)
{
rate = calculateRate(currRest.Rate);
}
else
{
rate = calculateRate(Convert.ToDouble(dbReviews[12][j]));
}
UserReview newReview = new UserReview(currRest.ID, dbReviews[10][j], dbReviews[11][j], rate);
listReviews.Add(newReview);
}
currRest.Types = styles;
currRest.Reviews = listReviews;
}
return(currRest);
}
public bool updateRestaurant(Restaurant rest)
{
string set = "name='" + rest.Name + "' ,URL_TA= '" + rest.URL + "' ,price_range= '" + rest.PriceRange + "' ,restaurants.city_id = (SELECT city.id FROM city WHERE city.name = '" + rest.City + "')";
return(dBConnect.Update("restaurants", set, "ID_TA = " + rest.ID));
}
