public int CustomerGetNumberOfCustomersInMonth(int month, int year, User user) { var firstDateOfMonth = new DateTime(year, month, 1); var lastDateOfMonth = firstDateOfMonth.AddMonths(1).AddDays(-1); var query = _session.QueryOver <Customer>(); query = query.Where(x => x.Type == CustomerType.Adult || x.Type == CustomerType.Children); BookingRoom bookingRoomAlias = null; query = query.JoinAlias(x => x.BookingRooms, () => bookingRoomAlias); Booking bookingAlias = null; query = query.JoinAlias(() => bookingRoomAlias.Book, () => bookingAlias); BookingSale bookingSalesAlias = null; query = query.JoinAlias(() => bookingAlias.BookingSale, () => bookingSalesAlias); if (user != null) { query = query.Where(() => bookingSalesAlias.Sale == user); } query = query.Where(() => bookingAlias.Deleted == false); query = query.Where(() => bookingAlias.StartDate >= firstDateOfMonth && bookingAlias.StartDate <= lastDateOfMonth); query = query.Where(() => bookingAlias.Status == StatusType.Approved); query = query.Select(Projections.RowCount()); return(query.FutureValue <int>().Value); }
public double BookingGetTotalRevenueInMonth(int month, int year, User user) { var firstDateOfMonth = new DateTime(year, month, 1); var lastDateOfMonth = firstDateOfMonth.AddMonths(1).AddDays(-1); var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false); BookingSale bookingSalesAlias = null; query = query.JoinAlias(x => x.BookingSale, () => bookingSalesAlias); if (user != null) { query = query.Where(() => bookingSalesAlias.Sale == user); } query = query.Where(x => x.StartDate >= firstDateOfMonth && x.StartDate <= lastDateOfMonth); query = query.Where(x => x.Status == StatusType.Approved); query = query.Select( Projections.Sum( Projections.Conditional( Restrictions.Where <Booking>(x => x.IsTotalUsd), Projections.SqlFunction( new VarArgsSQLFunction("(", "*", ")"), NHibernateUtil.Double, Projections.Property <Booking>(x => x.Total), Projections.Constant(23000) ), Projections.Property <Booking>(x => x.Total) ) )); return(query.Take(1).SingleOrDefault <double>()); }
public ICollection <Booking> BookigBookingGetAllTodayBookings(User user) { var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false); BookingSale bookingSalesAlias = null; query = query.JoinAlias(x => x.BookingSale, () => bookingSalesAlias); query = query.Where(() => bookingSalesAlias.Sale == user); query = query.Where(x => x.StartDate == DateTime.Today); query = query.Where(x => x.Status == StatusType.Approved); return(query.Future().ToList()); }
public IEnumerable <Booking> BookingGetAllBookingsInMonth(User user) { var firstDateOfMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); var lastDateOfMonth = firstDateOfMonth.AddMonths(1).AddDays(-1); var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false); BookingSale bookingSalesAlias = null; query = query.JoinAlias(x => x.BookingSale, () => bookingSalesAlias); query = query.Where(() => bookingSalesAlias.Sale == user); query = query.Where(x => x.StartDate >= firstDateOfMonth && x.StartDate <= lastDateOfMonth); query = query.Where(x => x.Status == StatusType.Approved); return(query.Future().ToList()); }
public IEnumerable <Booking> BookingGetAllNewBookings(User user) { var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false); BookingSale bookingSalesAlias = null; query = query.JoinAlias(x => x.BookingSale, () => bookingSalesAlias); if (user != null) { query = query.Where(() => bookingSalesAlias.Sale == user); } //Kiểm tra điều kiện booking được tạo trong ngày hôm nay query = query.Where(x => x.CreatedDate >= DateTime.Today && x.CreatedDate <= DateTime.Today.Add(new TimeSpan(23, 59, 59))); //-- query = query.Where(x => x.Status == StatusType.Pending); return(query.Future().ToList()); }
public int BookingGetNumberOfBookingsInMonth(int month, int year, User user) { var firstDateOfMonth = new DateTime(year, month, 1); var lastDateOfMonth = firstDateOfMonth.AddMonths(1).AddDays(-1); var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false); BookingSale bookingSalesAlias = null; query = query.JoinAlias(x => x.BookingSale, () => bookingSalesAlias); if (user != null) { query = query.Where(() => bookingSalesAlias.Sale == user); } query = query.Where(x => x.StartDate >= firstDateOfMonth && x.StartDate <= lastDateOfMonth); query = query.Where(x => x.Status == StatusType.Approved); query = query.Select(Projections.RowCount()); return(query.FutureValue <int>().Value); }
public object AgencyGetTop10(User user) { var firstDateOfMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); var lastDateOfMonth = firstDateOfMonth.AddMonths(1).AddDays(-1); var query = _session.QueryOver <Customer>(); query = query.Where(x => x.Type == CustomerType.Adult || x.Type == CustomerType.Children); BookingRoom bookingRoomAlias = null; query = query.JoinAlias(x => x.BookingRooms, () => bookingRoomAlias); Booking bookingAlias = null; query = query.JoinAlias(() => bookingRoomAlias.Book, () => bookingAlias); BookingSale bookingSalesAlias = null; query = query.JoinAlias(() => bookingAlias.BookingSale, () => bookingSalesAlias); if (user != null) { query = query.Where(() => bookingSalesAlias.Sale == user); } query = query.Where(() => bookingAlias.Deleted == false); query = query.Where(() => bookingAlias.StartDate >= firstDateOfMonth && bookingAlias.StartDate <= lastDateOfMonth); query = query.Where(() => bookingAlias.Status == StatusType.Approved); Agency agencyAlias = null; query = query.JoinAlias(() => bookingAlias.Agency, () => agencyAlias); query = query.Select( Projections.Property(() => agencyAlias.Id), Projections.Property(() => agencyAlias.Name), Projections.RowCount(), Projections.Group(() => agencyAlias.Id), Projections.Group(() => agencyAlias.Name) ); return(query.OrderBy(Projections.RowCount()).Desc.Take(10).Future <object[]>().ToList() .Select(x => new { AgencyId = x[0], AgencyName = x[1], NumberOfPax = x[2] })); }
public IList <Booking> PaymentReportBLL_BookingSearchBy(DateTime?from, DateTime?to, string agencyName, int cruiseId, int tripId, int agencyId, int bookingId, int salesId) { var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false) .And(Restrictions.Or(Restrictions.Where <Booking>(x => x.Status == StatusType.Approved), Restrictions.Where <Booking>(x => x.Status == StatusType.Cancelled && x.CancelPay > 0))); if (from != null) { query = query.Where(x => x.StartDate >= from); } if (to != null) { query = query.Where(x => x.StartDate <= to); } Agency agencyAlias = null; query.JoinAlias(x => x.Agency, () => agencyAlias); if (!string.IsNullOrEmpty(agencyName)) { query = query.Where(x => agencyAlias.Name.IsLike(agencyName, MatchMode.Anywhere)); } BookingSale bookingSaleAlias = null; query.JoinAlias(x => x.BookingSale, () => bookingSaleAlias); if (salesId > -1) { if (salesId > 0) { query = query.Where(x => bookingSaleAlias.Sale.Id == salesId); } if (salesId == 0) { query = query.Where(x => agencyAlias.Sale == null); } } if (cruiseId > -1) { query = query.Where(x => x.Cruise.Id == cruiseId); } if (bookingId > -1) { query = query.Where(x => x.Id == bookingId); } if (tripId > -1) { query = query.Where(x => x.Trip.Id == tripId); } if (agencyId > -1) { query = query.Where(x => x.Agency.Id == agencyId); } return(query.OrderBy(x => x.StartDate).Asc.List <Booking>()); }