public IList BookingSearch(ICriterion criterion, string customer, int blocked, int pageSize, int pageIndex, Order order)
{
ICriteria criteria = _session.OpenSession().CreateCriteria(typeof(Booking), "booking");
if (criterion != null)
{
criteria.Add(criterion);
}
criteria.CreateAlias("Trip", "trip");
if (!string.IsNullOrEmpty(customer))
{
DetachedCriteria detached = DetachedCriteria.For(typeof(Booking))
.CreateAlias("Customers", "customer")
.SetProjection(Projections.ProjectionList()
.Add(Projections.RowCount()))
.Add(Property.ForName("Id").EqProperty("booking.Id"))
.Add(Expression.Like("customer.Fullname", customer, MatchMode.Anywhere));
criteria.CreateAlias("Agency", "agent", JoinType.LeftOuterJoin);
ICriterion crit2 = Expression.Like("agent.Name", customer, MatchMode.Anywhere);
criteria.Add(Expression.Or(Subqueries.Le(1, detached), crit2));
}
if (blocked > 0)
{
// Như vậy, bản thân booking đó không được là charter
criteria.Add(Expression.Eq("IsCharter", false));
DetachedCriteria detached = DetachedCriteria.For(typeof(Booking))
.SetProjection(Projections.ProjectionList()
.Add(Projections.RowCount()))
.Add(Property.ForName("StartDate").GeProperty("booking.StartDate"))
.Add(Property.ForName("StartDate").LtProperty("booking.EndDate"))
.Add(Property.ForName("EndDate").GtProperty("booking.StartDate"))
.Add(Property.ForName("EndDate").LeProperty("booking.EndDate"))
.Add(Property.ForName("Cruise").EqProperty("booking.Cruise"))
.Add(Expression.Eq("IsCharter", true))
.Add(Expression.Eq("Status", StatusType.Approved));
// Nếu trong khoảng trùng ngày có một booking charter đã approved
criteria.Add(Subqueries.Le(1, detached));
}
if (order != null)
{
criteria.AddOrder(order);
}
if (pageSize > 0)
{
if (pageIndex < 0)
{
pageIndex = 0;
}
criteria.SetMaxResults(pageSize);
criteria.SetFirstResult(pageIndex * pageSize);
}
return(criteria.List());
}
public void ValueCriterion()
{
ICriteria expected =
CreateTestCriteria(typeof(Person))
.Add(Subqueries.Eq("Name", DetachedCriteriaName))
.Add(Subqueries.Ge("Age", DetachedCriteriaAge))
.Add(Subqueries.Gt("Age", DetachedCriteriaAge))
.Add(Subqueries.In("Name", DetachedCriteriaName))
.Add(Subqueries.Le("Age", DetachedCriteriaAge))
.Add(Subqueries.Lt("Age", DetachedCriteriaAge))
.Add(Subqueries.Ne("Name", DetachedCriteriaName))
.Add(Subqueries.NotIn("Name", DetachedCriteriaName));
var actual =
CreateTestQueryOver <Person>()
.And(Subqueries.WhereValue("Name").Eq(DetachedQueryOverName))
.And(Subqueries.WhereValue("Age").Ge(DetachedQueryOverAge))
.And(Subqueries.WhereValue("Age").Gt(DetachedQueryOverAge))
.And(Subqueries.WhereValue("Name").In(DetachedQueryOverName))
.And(Subqueries.WhereValue("Age").Le(DetachedQueryOverAge))
.And(Subqueries.WhereValue("Age").Lt(DetachedQueryOverAge))
.And(Subqueries.WhereValue("Name").Ne(DetachedQueryOverName))
.And(Subqueries.WhereValue("Name").NotIn(DetachedQueryOverName));
AssertCriteriaAreEqual(expected, actual);
}
public void SubqueriesExpressions()
{
DetachedCriteria dc = DetachedCriteria.For(typeof(Student))
.Add(Expression.Eq("Name", "Gavin King"));
ICriterion c = Subqueries.Eq("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.EqAll("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.Exists(dc);
NHAssert.IsSerializable(c);
c = Subqueries.Ge("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.GeAll("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.GeSome("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.Gt("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.GtAll("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.GtSome("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.In("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.Le("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.LeAll("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.LeSome("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.Lt("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.LtAll("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.LtSome("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.Ne("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.NotExists(dc);
NHAssert.IsSerializable(c);
c = Subqueries.NotIn("Gavin King", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyEq("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyEqAll("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyGe("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyGeAll("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyGeSome("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyGt("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyGtAll("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyGtSome("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyIn("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyLe("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyLeAll("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyLeSome("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyLt("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyLtAll("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyLtSome("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyNe("Name", dc);
NHAssert.IsSerializable(c);
c = Subqueries.PropertyNotIn("Name", dc);
NHAssert.IsSerializable(c);
}
public IList <VodovozOrder> GetOrdersToExport1c8(
IUnitOfWork uow,
IOrderParametersProvider orderParametersProvider,
Export1cMode mode,
DateTime startDate,
DateTime endDate,
int?organizationId = null)
{
VodovozOrder orderAlias = null;
OrderItem orderItemAlias = null;
var export1CSubquerySum = QueryOver.Of(() => orderItemAlias)
.Where(() => orderItemAlias.Order.Id == orderAlias.Id)
.Select(Projections.Sum(
Projections.SqlFunction(new VarArgsSQLFunction("", " * ", ""),
NHibernateUtil.Decimal,
Projections.Conditional(
Restrictions.IsNotNull(Projections.Property <OrderItem>(x => x.ActualCount)),
Projections.Property <OrderItem>(x => x.ActualCount),
Projections.Property <OrderItem>(x => x.Count)
),
Projections.Property <OrderItem>(x => x.Price),
Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "( 1 - ?1 / 100 )"),
NHibernateUtil.Decimal,
Projections.Property <OrderItem>(x => x.Discount)
)
)
))
;
var query = uow.Session.QueryOver(() => orderAlias)
.Where(() => orderAlias.OrderStatus.IsIn(VodovozOrder.StatusesToExport1c))
.Where(() => startDate <= orderAlias.DeliveryDate && orderAlias.DeliveryDate <= endDate);
if (organizationId.HasValue)
{
CounterpartyContract counterpartyContractAlias = null;
query.Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias)
.Where(() => counterpartyContractAlias.Organization.Id == organizationId);
}
switch (mode)
{
case Export1cMode.BuhgalteriaOOO:
query.Where(o => o.PaymentType == PaymentType.cashless)
.And(Subqueries.Le(0.01, export1CSubquerySum.DetachedCriteria));
break;
case Export1cMode.BuhgalteriaOOONew:
CashReceipt cashReceiptAlias = null;
query.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Where(Restrictions.Disjunction()
.Add(() => orderAlias.PaymentType == PaymentType.cashless)
.Add(Restrictions.Conjunction()
.Add(Restrictions.On(() => orderAlias.PaymentType)
.IsIn(new[] { PaymentType.Terminal, PaymentType.cash }))
.Add(Restrictions.IsNotNull(Projections.Property(() => cashReceiptAlias.Id))))
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.PaymentType == PaymentType.ByCard)
.Add(Restrictions.Disjunction()
.Add(Restrictions.On(() => orderAlias.PaymentByCardFrom.Id)
.IsIn(orderParametersProvider.PaymentsByCardFromNotToSendSalesReceipts))
.Add(Restrictions.IsNotNull(Projections.Property(() => cashReceiptAlias.Id))))
)
);
break;
case Export1cMode.IPForTinkoff:
query.Where(o => o.PaymentType == PaymentType.ByCard)
.And(o => o.OnlineOrder != null)
.And(Subqueries.Le(0.01, export1CSubquerySum.DetachedCriteria));
break;
default:
throw new ArgumentOutOfRangeException(nameof(mode), mode, null);
}
return(query.List());
}
