public List <Hotel> FilterHotels(HotelsSearch search, bool canRate, int idUser)
{
var sqlQuery = "";
search.googleRateFrom = string.IsNullOrEmpty(search.googleRateFrom) ? "0" : search.googleRateFrom;
search.googleRateTo = string.IsNullOrEmpty(search.googleRateTo) ? "5" : search.googleRateTo;
search.usersRateFrom = string.IsNullOrEmpty(search.usersRateFrom) ? "0" : search.usersRateFrom;
search.usersRateTo = string.IsNullOrEmpty(search.usersRateTo) ? "5" : search.usersRateTo;
search.distance = string.IsNullOrEmpty(search.distance) ? "1000000" : search.distance;
search.lat = string.IsNullOrEmpty(search.lat) ? "54.353556" : search.lat;
search.lng = string.IsNullOrEmpty(search.lng) ? "18.65836200000001" : search.lng;
if (canRate)
{
search.yourRateFrom = string.IsNullOrEmpty(search.yourRateFrom) ? "0" : search.yourRateFrom;
search.yourRateTo = string.IsNullOrEmpty(search.yourRateTo) ? "5" : search.yourRateTo;
sqlQuery = $"DECLARE @SOURCE GEOGRAPHY = 'POINT(" + search.lat + " " + search.lng + ")'; DECLARE @radius int = " + search.distance + "; " +
$"SELECT HOTELS.*, (SELECT coalesce(AVG(Cast(HOTEL_RATES.RATE as Float)), 0) FROM HOTEL_RATES WHERE HOTEL_RATES.ID_HOTEL = HOTELS.ID_HOTEL) AS AVG_RATE," +
$"(select coalesce(rate, 0) from HOTEL_RATES where ID_HOTEL = HOTELS.ID_HOTEL and ID_USER = "******" ) as USER_RATE, (SELECT COUNT(*) from HOTEL_RATES where ID_HOTEL = HOTELS.ID_HOTEL) as RATESCOUNT FROM HOTELS " +
$"WHERE HOTELS.NAME LIKE '%" + search.nameSearch + "%' AND HOTELS.FULLADDRESS LIKE '%" + search.addressSearch + "%' AND HOTELS.GOOGLE_RATE BETWEEN " + search.googleRateFrom + " AND " + search.googleRateTo + " " +
$"AND (SELECT coalesce(AVG(Cast(HOTEL_RATES.RATE as Float)), 0) FROM HOTEL_RATES WHERE HOTEL_RATES.ID_HOTEL = HOTELS.ID_HOTEL) BETWEEN " + search.usersRateFrom + " AND " + search.usersRateTo + " " +
$"AND (select coalesce(AVG(rate), 0) from HOTEL_RATES where ID_HOTEL = HOTELS.ID_HOTEL and ID_USER = "******" ) BETWEEN " + search.yourRateFrom + " AND " + search.yourRateTo +
$" AND @SOURCE.STDistance('POINT(' + CONVERT(NVARCHAR(50), LAT) + ' ' + CONVERT(NVARCHAR(50),LNG) + ')') <= @radius";
}
else
{
sqlQuery = $"DECLARE @SOURCE GEOGRAPHY = 'POINT({search.lat} {search.lng})'; DECLARE @radius int = '{search.distance}'; " +
$"SELECT HOTELS.*, (SELECT coalesce(AVG(Cast(HOTEL_RATES.RATE as Float)), 0) FROM HOTEL_RATES WHERE HOTEL_RATES.ID_HOTEL = HOTELS.ID_HOTEL) AS AVG_RATE, (SELECT COUNT(*) from HOTEL_RATES where ID_HOTEL = HOTELS.ID_HOTEL) as RATESCOUNT FROM HOTELS " +
$"WHERE HOTELS.NAME LIKE '%" + search.nameSearch + "%' AND HOTELS.FULLADDRESS LIKE '%" + search.addressSearch + "%' AND HOTELS.GOOGLE_RATE BETWEEN " + search.googleRateFrom + " AND " + search.googleRateTo + " " +
$"AND (SELECT coalesce(AVG(Cast(HOTEL_RATES.RATE as Float)), 0) FROM HOTEL_RATES WHERE HOTEL_RATES.ID_HOTEL = HOTELS.ID_HOTEL) BETWEEN " + search.usersRateFrom + " AND " + search.usersRateTo +
$" AND @SOURCE.STDistance('POINT(' + CONVERT(NVARCHAR(50), LAT) + ' ' + CONVERT(NVARCHAR(50),LNG) + ')') <= @radius";
}
List <Hotel> hotels = db.Query <Hotel>(sqlQuery).ToList();
return(hotels);
}
public ActionResult Index(HotelsSearch search, string name, string address, string userRateFrom, string userRateTo,
string yourRateFrom, string yourRateTo, string distance, string lat, string lng, int?page)
{
ViewBag.CurrentSearch = new HotelsSearch();
ViewBag.Empty = null;
if (search != null)
{
page = 1;
ViewBag.CurrentSearch = search;
search.lat = lat;
search.lng = lng;
}
else
{
ViewBag.CurrentSearch.nameSearch = name;
ViewBag.CurrentSearch.addressSearch = address;
ViewBag.CurrentSearch.usersRateFrom = userRateFrom;
ViewBag.CurrentSearch.usersRateTo = userRateTo;
ViewBag.CurrentSearch.yourRateFrom = yourRateFrom;
ViewBag.CurrentSearch.yourRateTo = yourRateTo;
ViewBag.CurrentSearch.distance = distance;
ViewBag.CurrentSearch.lat = lat;
ViewBag.CurrentSearch.lng = lng;
search = ViewBag.CurrentSearch;
}
int pageSize = 10;
int pageNumber = (page ?? 1);
bool canRate = User.Identity.IsAuthenticated;
var user = HttpContext.User.Identity;
var idUser = _accountSqlExecutor.GetUserId(user.Name);
var canEdit = false;
if (user.Name == "Admin")
{
canEdit = true;
}
var hotels = _hotelsSqlExecutor.FilterHotels(search, canRate, idUser);
HotelsModel model = new HotelsModel {
Hotels = hotels.ToPagedList <Hotel>(pageNumber, pageSize), CanEdit = canEdit, CanRate = canRate, Search = search
};
return(View(model));
}
