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>());
}
