public ActionResult Index(UserReservationSearch search, int?page, string offerName, string hotelName, string dateFrom,
string dateTo, string peopleFrom, string peopleTo, string priceFrom, string priceTo)
{
ViewBag.CurrentSearch = new UserReservationSearch();
ViewBag.empty = null;
if (search != null)
{
page = 1;
ViewBag.CurrentSearch = search;
}
else
{
ViewBag.CurrentSearch.offerName = offerName;
ViewBag.CurrentSearch.hotelName = hotelName;
ViewBag.CurrentSearch.dateFrom = dateFrom;
ViewBag.CurrentSearch.dateTo = dateTo;
ViewBag.CurrentSearch.peopleFrom = peopleFrom;
ViewBag.CurrentSearch.peopleTo = peopleTo;
ViewBag.CurrentSearch.priceFrom = priceFrom;
ViewBag.CurrentSearch.priceTo = priceTo;
search = ViewBag.CurrentSearch;
}
int pageSize = 10;
int pageNumber = (page ?? 1);
var user = HttpContext.User.Identity;
var idUser = _accountSqlExecutor.GetUserId(user.Name);
var reservations = _reservationsSqlExecutor.FilterUserReservations(idUser, search);
UserReservationModel model = new UserReservationModel {
Reservations = reservations.ToPagedList <UserReservationFullData>(pageNumber, pageSize), Search = search
};
return(View(model));
}
public List <UserReservationFullData> FilterUserReservations(int idUser, UserReservationSearch search)
{
search.dateFrom = string.IsNullOrEmpty(search.dateFrom) ? "1999-01-01" : search.dateFrom;
search.dateTo = string.IsNullOrEmpty(search.dateTo) ? "2100-01-01" : search.dateTo;
search.guestsFrom = string.IsNullOrEmpty(search.guestsFrom) ? "1" : search.guestsFrom;
search.guestsTo = string.IsNullOrEmpty(search.guestsTo) ? "20" : search.guestsTo;
search.priceFrom = string.IsNullOrEmpty(search.priceFrom) ? "0" : search.priceFrom;
search.priceTo = string.IsNullOrEmpty(search.priceTo) ? "100000" : search.priceTo;
var sqlQuery = $"SELECT USER_RESERVATIONS.*, OFFERS.*, HOTELS.NAME AS HOTEL_NAME FROM USER_RESERVATIONS INNER JOIN OFFERS ON USER_RESERVATIONS.ID_OFFER = OFFERS.ID_OFFER " +
$" INNER JOIN HOTELS ON OFFERS.ID_HOTEL = HOTELS.ID_HOTEL" +
$" WHERE OFFERS.NAME LIKE '%" + search.offerName + "%' AND HOTELS.NAME LIKE '%" + search.hotelName + "%'" +
$" AND OFFERS.DATE_START BETWEEN @dateFrom AND @dateTo AND OFFERS.DATE_END BETWEEN @dateFrom AND @dateTo AND OFFERS.PRICE BETWEEN @priceFrom AND @priceTo " +
$" AND USER_RESERVATIONS.GUESTS >= @guestsFrom and USER_RESERVATIONS.GUESTS <= @guestsTo AND USER_RESERVATIONS.ID_USER = '******'";
List <UserReservationFullData> reservations = db.Query <UserReservationFullData>(sqlQuery, search).ToList();
foreach (var r in reservations)
{
r.GuestsList = GetGuestsToReservation(r.ID_USER_RESERVATION);
}
return(reservations);
}
