/// <summary>
/// Оборудование заказа от клиента
/// </summary>
/// <returns>Список оборудования от клиенту</returns>
/// <param name="uow">Uow.</param>
/// <param name="order">Заказ</param>
public IList <ClientEquipmentNode> GetEquipmentFromClientForOrder(IUnitOfWork uow, VodovozOrder order)
{
OrderEquipment orderEquipmentAlias = null;
Nomenclature nomenclatureAlias = null;
ClientEquipmentNode resultAlias = null;
var equipFromClient = uow.Session.QueryOver(() => orderEquipmentAlias)
.Where(() => orderEquipmentAlias.Order.Id == order.Id)
.Where(() => orderEquipmentAlias.Direction == Direction.PickUp)
.Left.JoinQueryOver(i => i.Nomenclature, () => nomenclatureAlias)
.SelectList(list => list
.Select(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.Name)
.Select(() => nomenclatureAlias.ShortName).WithAlias(() => resultAlias.ShortName)
.Select(() => orderEquipmentAlias.Count).WithAlias(() => resultAlias.Count)
)
.TransformUsing(Transformers.AliasToBean <ClientEquipmentNode>())
.List <ClientEquipmentNode>();
return(equipFromClient);
}
public IEnumerable <ReceiptForOrderNode> GetOrdersForCashReceiptServiceToSend(
IUnitOfWork uow,
IOrderParametersProvider orderParametersProvider,
IOrganizationParametersProvider organizationParametersProvider,
ISalesReceiptsParametersProvider salesReceiptsParametersProvider,
DateTime?startDate = null)
{
#region Aliases Restrictions Projections
var paymentByCardFromNotToSendSalesReceipts = orderParametersProvider.PaymentsByCardFromNotToSendSalesReceipts;
var vodovozSouthOrganizationId = organizationParametersProvider.VodovozSouthOrganizationId;
ExtendedReceiptForOrderNode extendedReceiptForOrderNodeAlias = null;
OrderItem orderItemAlias = null;
VodovozOrder orderAlias = null;
CashReceipt cashReceiptAlias = null;
Nomenclature nomenclatureAlias = null;
ProductGroup productGroupAlias = null;
Counterparty counterpartyAlias = null;
CounterpartyContract counterpartyContractAlias = null;
Organization organizationAlias = null;
var orderSumProjection = Projections.Sum(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "CAST(IFNULL(?1 * ?2 - ?3, 0) AS DECIMAL(14,2))"),
NHibernateUtil.Decimal,
Projections.Property(() => orderItemAlias.Count),
Projections.Property(() => orderItemAlias.Price),
Projections.Property(() => orderItemAlias.DiscountMoney)
)
);
var orderSumRestriction = Restrictions.Gt(orderSumProjection, 0);
var alwaysSendOrdersRestriction = Restrictions.Disjunction()
.Add(() => productGroupAlias.OnlineStore != null)
.Add(() => counterpartyAlias.AlwaysSendReceitps)
.Add(() => orderAlias.SelfDelivery)
.Add(Restrictions.In(Projections.Property(() => orderAlias.PaymentType),
new[] { PaymentType.ByCard, PaymentType.Terminal }.ToArray()));
var orderDeliveredStatuses = Restrictions.In(Projections.Property(() => orderAlias.OrderStatus),
new[] { OrderStatus.Shipped, OrderStatus.UnloadingOnStock }.ToArray());
var orderPaymentTypesRestriction = Restrictions.In(Projections.Property(() => orderAlias.PaymentType),
new[] { PaymentType.cash, PaymentType.Terminal, PaymentType.ByCard }.ToArray());
var paidByCardRestriction = Restrictions.Disjunction()
.Add(() => orderAlias.PaymentType != PaymentType.ByCard)
.Add(() => organizationAlias.Id != vodovozSouthOrganizationId)
.Add(Restrictions.On(() => orderAlias.PaymentByCardFrom.Id)
.Not.IsIn(paymentByCardFromNotToSendSalesReceipts));
#endregion
#region AlwaysSendOrders
var alwaysSendOrdersQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias)
.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Left.JoinAlias(() => nomenclatureAlias.ProductGroup, () => productGroupAlias)
.Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias)
.Left.JoinAlias(() => counterpartyContractAlias.Organization, () => organizationAlias)
.Where(alwaysSendOrdersRestriction)
.And(paidByCardRestriction)
.And(Restrictions.Disjunction()
.Add(orderDeliveredStatuses)
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.SelfDelivery)
.Add(() => orderAlias.IsSelfDeliveryPaid)))
.And(orderSumRestriction)
.And(orderPaymentTypesRestriction)
.And(Restrictions.Disjunction()
.Add(Restrictions.IsNull(Projections.Property(() => cashReceiptAlias.Id)))
.Add(() => !cashReceiptAlias.Sent));
if (startDate.HasValue)
{
alwaysSendOrdersQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value);
}
var alwaysSendOrders = alwaysSendOrdersQuery
.SelectList(list => list
.SelectGroup(() => orderAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderId)
.Select(() => orderAlias.PaymentType).WithAlias(() => extendedReceiptForOrderNodeAlias.PaymentType)
.Select(orderSumProjection).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderSum)
.Select(() => cashReceiptAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.ReceiptId)
.Select(() => cashReceiptAlias.Sent).WithAlias(() => extendedReceiptForOrderNodeAlias.WasSent))
.TransformUsing(Transformers.AliasToBean <ExtendedReceiptForOrderNode>())
.Future <ExtendedReceiptForOrderNode>();
//Здесь фильтрация идёт не на уровне запроса, т.к. не NHibernate упорно не хочет клась сложное условие в HAVING
var result = alwaysSendOrders
.Where(x =>
x.PaymentType != PaymentType.cash ||
x.PaymentType == PaymentType.cash && x.OrderSum < 20000)
.Select(x => new ReceiptForOrderNode
{
OrderId = x.OrderId,
ReceiptId = x.ReceiptId,
WasSent = x.WasSent
});
#endregion
#region UniqueOrderSumOrders
if (salesReceiptsParametersProvider.SendUniqueOrderSumOrders)
{
var uniqueOrderSumSendOrdersQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias)
.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Left.JoinAlias(() => nomenclatureAlias.ProductGroup, () => productGroupAlias)
.Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias)
.Left.JoinAlias(() => counterpartyContractAlias.Organization, () => organizationAlias)
.Where(Restrictions.Not(alwaysSendOrdersRestriction))
.And(paidByCardRestriction)
.And(orderDeliveredStatuses)
.And(orderSumRestriction)
.And(orderPaymentTypesRestriction);
if (startDate.HasValue)
{
uniqueOrderSumSendOrdersQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value);
}
var notUniqueOrderSumSendOrdersTemp = uniqueOrderSumSendOrdersQuery
.SelectList(list => list
.SelectGroup(() => orderAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderId)
.Select(() => orderAlias.PaymentType).WithAlias(() => extendedReceiptForOrderNodeAlias.PaymentType)
.Select(orderSumProjection).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderSum)
.Select(CustomProjections.Date(() => orderAlias.DeliveryDate))
.WithAlias(() => extendedReceiptForOrderNodeAlias.DeliveryDate)
.Select(() => cashReceiptAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.ReceiptId)
.Select(() => cashReceiptAlias.Sent).WithAlias(() => extendedReceiptForOrderNodeAlias.WasSent))
.TransformUsing(Transformers.AliasToBean <ExtendedReceiptForOrderNode>())
.Future <ExtendedReceiptForOrderNode>();
var notUniqueOrderSumSendOrders = notUniqueOrderSumSendOrdersTemp.Where(x =>
x.PaymentType != PaymentType.cash ||
x.PaymentType == PaymentType.cash && x.OrderSum < 20000).ToList();
var alreadySentOrders =
new List <ExtendedReceiptForOrderNode>(notUniqueOrderSumSendOrders.Where(x => x.WasSent.HasValue && x.WasSent.Value));
var uniqueOrderSumSendNodes = new List <ExtendedReceiptForOrderNode>();
foreach (var node in notUniqueOrderSumSendOrders.Where(x => !x.WasSent.HasValue || !x.WasSent.Value))
{
if (alreadySentOrders.All(x => x.OrderSum != node.OrderSum || x.DeliveryDate != node.DeliveryDate) &&
uniqueOrderSumSendNodes.All(x => x.OrderSum != node.OrderSum || x.DeliveryDate != node.DeliveryDate))
{
uniqueOrderSumSendNodes.Add(node);
}
}
var uniqueOrderSumSendOrderNodes = uniqueOrderSumSendNodes.Select(x => new ReceiptForOrderNode
{
OrderId = x.OrderId, ReceiptId = x.ReceiptId, WasSent = x.WasSent
});
result = result.Union(uniqueOrderSumSendOrderNodes);
}
#endregion
return(result);
}
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());
}
/// <summary>
/// Первый заказ контрагента, который можно считать выполненым.
/// </summary>
/// <returns>Первый заказ</returns>
/// <param name="uow">UoW</param>
/// <param name="counterparty">Контрагент</param>
public VodovozOrder GetFirstRealOrderForClientForActionBottle(IUnitOfWork uow, VodovozOrder order, Counterparty counterparty)
{
if (uow == null)
{
throw new ArgumentNullException(nameof(uow));
}
if (order == null)
{
throw new ArgumentNullException(nameof(order));
}
if (counterparty == null)
{
throw new ArgumentNullException(nameof(counterparty));
}
if (counterparty?.FirstOrder != null && GetValidStatusesToUseActionBottle().Contains(counterparty.FirstOrder.OrderStatus))
{
return(counterparty.FirstOrder);
}
var query = uow.Session.QueryOver <VodovozOrder>()
.Where(o => o.Id != order.Id)
.Where(o => o.Client == counterparty)
.Where(o => o.OrderStatus.IsIn(GetValidStatusesToUseActionBottle()))
.OrderBy(o => o.DeliveryDate).Asc
.Take(1)
;
return(query.List().FirstOrDefault());
}
private IQueryOver <OrderWithoutShipmentForPayment> GetOrdersWithoutShipmentForPaymentQuery(IUnitOfWork uow)
{
RetailOrderJournalNode resultAlias = null;
OrderWithoutShipmentForPayment orderWSPAlias = null;
OrderWithoutShipmentForPaymentItem orderWSPItemAlias = null;
VodovozOrder orderAlias = null;
Nomenclature nomenclatureAlias = null;
OrderItem orderItemAlias = null;
Counterparty counterpartyAlias = null;
Employee authorAlias = null;
var query = uow.Session.QueryOver(() => orderWSPAlias)
.Left.JoinAlias(o => o.Author, () => authorAlias);
if (FilterViewModel.ViewTypes != ViewTypes.OrderWSFP && FilterViewModel.ViewTypes != ViewTypes.All ||
FilterViewModel.RestrictStatus != null && FilterViewModel.RestrictStatus != OrderStatus.Closed ||
FilterViewModel.RestrictPaymentType != null ||
FilterViewModel.DeliveryPoint != null ||
FilterViewModel.RestrictOnlyService != null ||
FilterViewModel.RestrictOnlySelfDelivery != null ||
FilterViewModel.RestrictLessThreeHours == true ||
FilterViewModel.OrderPaymentStatus != null ||
FilterViewModel.Organisation != null ||
FilterViewModel.PaymentByCardFrom != null)
{
query.Where(o => o.Id == -1);
}
if (FilterViewModel != null && FilterViewModel.IsForRetail != null)
{
query.JoinEntityAlias(
() => counterpartyAlias,
() => counterpartyAlias.Id == orderWSPAlias.Client.Id &&
counterpartyAlias.IsForRetail == FilterViewModel.IsForRetail,
NHibernate.SqlCommand.JoinType.InnerJoin);
}
else
{
query.Left.JoinAlias(o => o.Client, () => counterpartyAlias);
}
if (FilterViewModel.RestrictStartDate != null)
{
query.Where(o => o.CreateDate >= FilterViewModel.RestrictStartDate);
}
if (FilterViewModel.RestrictEndDate != null)
{
query.Where(o => o.CreateDate <= FilterViewModel.RestrictEndDate.Value.AddDays(1).AddTicks(-1));
}
if (FilterViewModel.RestrictCounterparty != null)
{
query.Where(o => o.Client == FilterViewModel.RestrictCounterparty);
}
var bottleCountSubquery = QueryOver.Of(() => orderWSPItemAlias)
.Where(() => orderWSPAlias.Id == orderWSPItemAlias.OrderWithoutDeliveryForPayment.Id)
.Left.JoinAlias(() => orderWSPItemAlias.Order, () => orderAlias)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => nomenclatureAlias.Category == NomenclatureCategory.water && nomenclatureAlias.TareVolume == TareVolume.Vol19L)
.Select(Projections.Sum(() => orderItemAlias.Count));
var orderSumSubquery = QueryOver.Of(() => orderWSPItemAlias)
.Where(() => orderWSPAlias.Id == orderWSPItemAlias.OrderWithoutDeliveryForPayment.Id)
.Left.JoinAlias(() => orderWSPItemAlias.Order, () => orderAlias)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Select(
Projections.Sum(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 * ?2 - IF(?3 IS NULL OR ?3 = 0, IFNULL(?4, 0), ?3)"),
NHibernateUtil.Decimal,
Projections.Property(() => orderItemAlias.Count),
Projections.Property(() => orderItemAlias.Price),
Projections.Property(() => orderItemAlias.DiscountMoney),
Projections.Property(() => orderItemAlias.OriginalDiscountMoney)
)
)
);
query.Where(GetSearchCriterion(
() => orderWSPAlias.Id,
() => counterpartyAlias.Name,
() => authorAlias.LastName
));
var resultQuery = query
.SelectList(list => list
.Select(() => orderWSPAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => orderWSPAlias.CreateDate).WithAlias(() => resultAlias.CreateDate)
.Select(() => orderWSPAlias.CreateDate).WithAlias(() => resultAlias.Date)
.Select(() => authorAlias.LastName).WithAlias(() => resultAlias.AuthorLastName)
.Select(() => authorAlias.Name).WithAlias(() => resultAlias.AuthorName)
.Select(() => authorAlias.Patronymic).WithAlias(() => resultAlias.AuthorPatronymic)
.Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.Counterparty)
.SelectSubQuery(orderSumSubquery).WithAlias(() => resultAlias.Sum)
.SelectSubQuery(bottleCountSubquery).WithAlias(() => resultAlias.BottleAmount)
)
.OrderBy(x => x.CreateDate).Desc
.SetTimeout(60)
.TransformUsing(Transformers.AliasToBean <RetailOrderJournalNode <OrderWithoutShipmentForPayment> >());
return(resultQuery);
}
protected override IQueryOver <Payment> ItemsQuery(IUnitOfWork uow)
{
UnallocatedBalancesJournalNode resultAlias = null;
VodOrder orderAlias = null;
VodOrder orderAlias2 = null;
OrderItem orderItemAlias = null;
PaymentItem paymentItemAlias = null;
Counterparty counterpartyAlias = null;
Organization organizationAlias = null;
CounterpartyContract counterpartyContractAlias = null;
Organization orderOrganizationAlias = null;
CashlessMovementOperation cashlessMovementOperationAlias = null;
var query = uow.Session.QueryOver <Payment>()
.Inner.JoinAlias(cmo => cmo.Counterparty, () => counterpartyAlias)
.Inner.JoinAlias(cmo => cmo.Organization, () => organizationAlias);
var income = QueryOver.Of <CashlessMovementOperation>()
.Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id)
.And(cmo => cmo.Organization.Id == organizationAlias.Id)
.And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled)
.Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Income));
var expense = QueryOver.Of <CashlessMovementOperation>()
.Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id)
.And(cmo => cmo.Organization.Id == organizationAlias.Id)
.And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled)
.Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Expense));
var balanceProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - ?2"),
NHibernateUtil.Decimal,
Projections.SubQuery(income),
Projections.SubQuery(expense));
var orderSumProjection = OrderRepository.GetOrderSumProjection(orderItemAlias);
var totalNotPaidOrders = QueryOver.Of(() => orderAlias)
.Inner.JoinAlias(o => o.OrderItems, () => orderItemAlias)
.Inner.JoinAlias(o => o.Contract, () => counterpartyContractAlias)
.Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias)
.Where(() => orderAlias.Client.Id == counterpartyAlias.Id)
.And(() => orderOrganizationAlias.Id == organizationAlias.Id)
.And(() => orderAlias.PaymentType == PaymentType.cashless)
.And(() => orderAlias.OrderPaymentStatus != OrderPaymentStatus.Paid)
.Select(orderSumProjection)
.Where(Restrictions.Gt(orderSumProjection, 0));
var totalPayPartiallyPaidOrders = QueryOver.Of(() => paymentItemAlias)
.JoinEntityAlias(() => orderAlias2, () => paymentItemAlias.Order.Id == orderAlias2.Id, JoinType.InnerJoin)
.Inner.JoinAlias(() => orderAlias2.Contract, () => counterpartyContractAlias)
.Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias)
.Inner.JoinAlias(() => paymentItemAlias.CashlessMovementOperation, () => cashlessMovementOperationAlias)
.Where(() => orderAlias2.Client.Id == counterpartyAlias.Id)
.And(() => orderOrganizationAlias.Id == organizationAlias.Id)
.And(() => cashlessMovementOperationAlias.CashlessMovementOperationStatus != AllocationStatus.Cancelled)
.And(() => orderAlias2.PaymentType == PaymentType.cashless)
.And(() => orderAlias2.OrderPaymentStatus == OrderPaymentStatus.PartiallyPaid)
.Select(Projections.Sum(() => cashlessMovementOperationAlias.Expense));
var counterpartyDebtProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - IFNULL(?2, ?3)"),
NHibernateUtil.Decimal,
Projections.SubQuery(totalNotPaidOrders),
Projections.SubQuery(totalPayPartiallyPaidOrders),
Projections.Constant(0));
query.Where(GetSearchCriterion(
() => counterpartyAlias.Id,
() => counterpartyAlias.Name,
() => balanceProjection,
() => counterpartyDebtProjection));
#region filter
if (_filterViewModel.Counterparty != null)
{
query.Where(() => counterpartyAlias.Id == _filterViewModel.Counterparty.Id);
}
if (_filterViewModel.Organization != null)
{
query.Where(() => organizationAlias.Id == _filterViewModel.Organization.Id);
}
#endregion
return(query.SelectList(list => list
.SelectGroup(() => counterpartyAlias.Id).WithAlias(() => resultAlias.CounterpartyId)
.SelectGroup(() => organizationAlias.Id).WithAlias(() => resultAlias.OrganizationId)
.Select(p => counterpartyAlias.INN).WithAlias(() => resultAlias.CounterpartyINN)
.Select(p => counterpartyAlias.Name).WithAlias(() => resultAlias.CounterpartyName)
.Select(p => organizationAlias.Name).WithAlias(() => resultAlias.OrganizationName)
.Select(balanceProjection).WithAlias(() => resultAlias.CounterpartyBalance)
.Select(counterpartyDebtProjection).WithAlias(() => resultAlias.CounterpartyDebt))
.Where(Restrictions.Gt(balanceProjection, 0))
.And(Restrictions.Gt(counterpartyDebtProjection, 0))
.OrderBy(balanceProjection).Desc
.TransformUsing(Transformers.AliasToBean <UnallocatedBalancesJournalNode>())
.SetTimeout(120));
}
public FastDeliveryAvailabilityHistory GetRouteListsForFastDelivery(
IUnitOfWork uow,
double latitude,
double longitude,
bool isGetClosestByRoute,
IDeliveryRulesParametersProvider deliveryRulesParametersProvider,
IEnumerable <NomenclatureAmountNode> nomenclatureNodes,
Order fastDeliveryOrder = null)
{
var maxDistanceToTrackPoint = deliveryRulesParametersProvider.MaxDistanceToLatestTrackPointKm;
var driverGoodWeightLiftPerHand = deliveryRulesParametersProvider.DriverGoodWeightLiftPerHandInKg;
var maxFastOrdersPerSpecificTime = deliveryRulesParametersProvider.MaxFastOrdersPerSpecificTime;
var maxTimeForFastDeliveryTimespan = deliveryRulesParametersProvider.MaxTimeForFastDelivery;
//Переводим всё в минуты
var trackPointTimeOffset = (int)deliveryRulesParametersProvider.MaxTimeOffsetForLatestTrackPoint.TotalMinutes;
var maxTimeForFastDelivery = (int)maxTimeForFastDeliveryTimespan.TotalMinutes;
var minTimeForNewOrder = (int)deliveryRulesParametersProvider.MinTimeForNewFastDeliveryOrder.TotalMinutes;
var driverUnloadTime = (int)deliveryRulesParametersProvider.DriverUnloadTime.TotalMinutes;
var specificTimeForFastOrdersCount = (int)deliveryRulesParametersProvider.SpecificTimeForMaxFastOrdersCount.TotalMinutes;
var fastDeliveryAvailabilityHistory = new FastDeliveryAvailabilityHistory
{
IsGetClosestByRoute = isGetClosestByRoute,
Order = fastDeliveryOrder,
MaxDistanceToLatestTrackPointKm = maxDistanceToTrackPoint,
DriverGoodWeightLiftPerHandInKg = driverGoodWeightLiftPerHand,
MaxFastOrdersPerSpecificTime = maxFastOrdersPerSpecificTime,
MaxTimeForFastDelivery = maxTimeForFastDeliveryTimespan,
MinTimeForNewFastDeliveryOrder = deliveryRulesParametersProvider.MinTimeForNewFastDeliveryOrder,
DriverUnloadTime = deliveryRulesParametersProvider.DriverUnloadTime,
SpecificTimeForMaxFastOrdersCount = deliveryRulesParametersProvider.SpecificTimeForMaxFastOrdersCount,
};
var order = fastDeliveryAvailabilityHistory.Order;
if (order != null)
{
fastDeliveryAvailabilityHistory.Order = order.Id == 0 ? null : order;
fastDeliveryAvailabilityHistory.Author = order.Author;
fastDeliveryAvailabilityHistory.DeliveryPoint = order.DeliveryPoint;
fastDeliveryAvailabilityHistory.District = order.DeliveryPoint.District;
fastDeliveryAvailabilityHistory.Counterparty = order.Client;
}
var fastDeliveryHistoryConverter = new FastDeliveryHistoryConverter();
if (nomenclatureNodes != null)
{
fastDeliveryAvailabilityHistory.OrderItemsHistory =
fastDeliveryHistoryConverter.ConvertNomenclatureAmountNodesToOrderItemsHistory(nomenclatureNodes, fastDeliveryAvailabilityHistory);
}
var distributions = uow.GetAll <AdditionalLoadingNomenclatureDistribution>();
fastDeliveryAvailabilityHistory.NomenclatureDistributionHistoryItems =
fastDeliveryHistoryConverter.ConvertNomenclatureDistributionToDistributionHistory(distributions, fastDeliveryAvailabilityHistory);
var district = GetDistrict(uow, (decimal)latitude, (decimal)longitude);
if (district?.TariffZone == null || !district.TariffZone.IsFastDeliveryAvailableAtCurrentTime)
{
fastDeliveryAvailabilityHistory.AdditionalInformation =
new List <string> {
"Не найден район, у района отсутствует тарифная зона, либо недоступна экспресс-доставка в текущее время."
};
return(fastDeliveryAvailabilityHistory);
}
var neededNomenclatures = nomenclatureNodes.ToDictionary(x => x.NomenclatureId, x => x.Amount);
Track t = null;
TrackPoint tp = null;
RouteList rl = null;
TrackPoint tpInner = null;
FastDeliveryVerificationDetailsNode result = null;
Employee e = null;
RouteListItem rla = null;
RouteListItem rlaTransfered = null;
Order o = null;
OrderItem oi = null;
OrderEquipment oe = null;
CarLoadDocument scld = null;
CarLoadDocumentItem scldi = null;
CountUnclosedFastDeliveryAddressesNode countUnclosedFastDeliveryAddressesAlias = null;
RouteListNomenclatureAmount ordersAmountAlias = null;
RouteListNomenclatureAmount loadDocumentsAmountAlias = null;
var lastTimeTrackQuery = QueryOver.Of(() => tpInner)
.Where(() => tpInner.Track.Id == t.Id)
.Select(Projections.Max(() => tpInner.TimeStamp));
//МЛ только в пути и с погруженным запасом
var routeListNodes = uow.Session.QueryOver(() => rl)
.JoinEntityAlias(() => t, () => t.RouteList.Id == rl.Id)
.Inner.JoinAlias(() => t.TrackPoints, () => tp)
.Inner.JoinAlias(() => rl.Driver, () => e)
.WithSubquery.WhereProperty(() => tp.TimeStamp).Eq(lastTimeTrackQuery)
.And(() => rl.Status == RouteListStatus.EnRoute)
.And(() => rl.AdditionalLoadingDocument.Id != null) // только с погруженным запасом
.SelectList(list => list
.Select(() => tp.TimeStamp).WithAlias(() => result.TimeStamp)
.Select(() => tp.Latitude).WithAlias(() => result.Latitude)
.Select(() => tp.Longitude).WithAlias(() => result.Longitude)
.Select(Projections.Entity(() => rl)).WithAlias(() => result.RouteList))
.TransformUsing(Transformers.AliasToBean <FastDeliveryVerificationDetailsNode>())
.List <FastDeliveryVerificationDetailsNode>();
//Последняя координата в указанном радиусе
foreach (var node in routeListNodes)
{
var distance = DistanceHelper.GetDistanceKm(node.Latitude, node.Longitude, latitude, longitude);
var deliveryPoint = new PointOnEarth(latitude, longitude);
var proposedRoute = OsrmClientFactory.Instance
.GetRoute(new List <PointOnEarth> {
new PointOnEarth(node.Latitude, node.Longitude), deliveryPoint
}, false, GeometryOverview.False, _globalSettings.ExcludeToll)?.Routes?
.FirstOrDefault();
node.DistanceByLineToClient.ParameterValue = (decimal)distance;
node.DistanceByRoadToClient.ParameterValue = decimal.Round((decimal)(proposedRoute?.TotalDistance ?? int.MaxValue) / 1000, 2);
if (distance < maxDistanceToTrackPoint)
{
node.DistanceByLineToClient.IsValidParameter = node.DistanceByRoadToClient.IsValidParameter = true;
}
else
{
node.DistanceByLineToClient.IsValidParameter = node.DistanceByRoadToClient.IsValidParameter = false;
node.IsValidRLToFastDelivery = false;
}
//Выставляем время последней координаты
var timeSpan = DateTime.Now - node.TimeStamp;
node.LastCoordinateTime.ParameterValue = timeSpan.TotalHours > 838 ? new TimeSpan(838, 0, 0) : timeSpan;
if (node.LastCoordinateTime.ParameterValue.TotalMinutes <= trackPointTimeOffset)
{
node.LastCoordinateTime.IsValidParameter = true;
}
else
{
node.LastCoordinateTime.IsValidParameter = false;
node.IsValidRLToFastDelivery = false;
}
}
routeListNodes = routeListNodes
.OrderBy(x => isGetClosestByRoute ? x.DistanceByRoadToClient.ParameterValue : x.DistanceByLineToClient.ParameterValue)
.ToList();
//Не более определённого кол-ва заказов с быстрой доставкой в определённый промежуток времени
var addressCountSubquery = QueryOver.Of(() => rla)
.Inner.JoinAlias(() => rla.Order, () => o)
.Where(() => rla.RouteList.Id == rl.Id)
.And(() => rla.Status == RouteListItemStatus.EnRoute)
.And(() => o.IsFastDelivery)
.And(Restrictions.GtProperty(
Projections.Property(() => rla.CreationDate),
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.DateTime,
$"TIMESTAMPADD(MINUTE, -{specificTimeForFastOrdersCount}, CURRENT_TIMESTAMP)"),
NHibernateUtil.DateTime)))
.Select(Projections.Count(() => rla.Id));
var routeListsWithCountUnclosedFastDeliveries = uow.Session.QueryOver(() => rl)
.WhereRestrictionOn(() => rl.Id).IsInG(routeListNodes.Select(x => x.RouteList.Id))
.SelectList(list => list
.Select(() => rl.Id).WithAlias(() => countUnclosedFastDeliveryAddressesAlias.RouteListId)
.SelectSubQuery(addressCountSubquery).WithAlias(() => countUnclosedFastDeliveryAddressesAlias.UnclosedFastDeliveryAddresses))
.TransformUsing(Transformers.AliasToBean <CountUnclosedFastDeliveryAddressesNode>())
.List <CountUnclosedFastDeliveryAddressesNode>();
var rlsWithCountUnclosedFastDeliveries =
routeListsWithCountUnclosedFastDeliveries.ToDictionary(x => x.RouteListId, x => x.UnclosedFastDeliveryAddresses);
foreach (var node in routeListNodes)
{
var countUnclosedFastDeliveryAddresses = rlsWithCountUnclosedFastDeliveries[node.RouteList.Id];
node.UnClosedFastDeliveries.ParameterValue = countUnclosedFastDeliveryAddresses;
if (countUnclosedFastDeliveryAddresses < maxFastOrdersPerSpecificTime)
{
node.UnClosedFastDeliveries.IsValidParameter = true;
}
else
{
node.UnClosedFastDeliveries.IsValidParameter = false;
node.IsValidRLToFastDelivery = false;
}
}
//Время доставки следующего (текущего) заказа позволяет взять быструю доставку
foreach (var routeListNode in routeListNodes)
{
RouteListItem latestAddress = null;
var orderedEnRouteAddresses = routeListNode.RouteList.Addresses
.Where(x => x.Status == RouteListItemStatus.EnRoute).OrderBy(x => x.IndexInRoute).ToList();
var orderedCompletedAddresses = routeListNode.RouteList.Addresses
.Where(x => x.Status == RouteListItemStatus.Completed).OrderBy(x => x.IndexInRoute).ToList();
var latestCompletedAddress = orderedCompletedAddresses.OrderByDescending(x => x.StatusLastUpdate).FirstOrDefault();
if (latestCompletedAddress != null)
{
latestAddress = orderedEnRouteAddresses.FirstOrDefault(x => x.IndexInRoute > latestCompletedAddress.IndexInRoute);
}
if (latestAddress == null)
{
latestAddress = orderedEnRouteAddresses.FirstOrDefault();
}
if (latestAddress != null)
{
var neededTime1 = maxTimeForFastDelivery - latestAddress.Order.DeliveryPoint.MinutesToUnload;
if (neededTime1 < minTimeForNewOrder)
{
routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = new TimeSpan(0, neededTime1, 0);
routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = false;
routeListNode.IsValidRLToFastDelivery = false;
continue;
}
var water19Count = latestAddress.Order.OrderItems
.Where(x => x.Nomenclature.TareVolume == TareVolume.Vol19L && x.Nomenclature.Category == NomenclatureCategory.water)
.Sum(x => x.Count);
var orderItemsSummaryWeight = latestAddress.Order.OrderItems
.Where(x => x.Nomenclature.TareVolume != TareVolume.Vol19L || x.Nomenclature.Category != NomenclatureCategory.water)
.Sum(x => x.Nomenclature.Weight * x.Count);
var orderEquipmentsSummaryWeight = latestAddress.Order.OrderEquipments
.Where(x => x.Direction == Direction.Deliver)
.Sum(x => x.Nomenclature.Weight * x.Count);
var goodsSummaryWeight = orderItemsSummaryWeight + orderEquipmentsSummaryWeight;
//Время выгрузки след. заказа:
//(Суммарный вес прочих товаров / кол-во кг, которое водитель может унести в одной руке + кол-во 19л) / 2 руки * время выгрузки в 2 руках 2 бутылей или товара
var unloadTime = (goodsSummaryWeight / driverGoodWeightLiftPerHand + water19Count) / 2 * driverUnloadTime;
var neededTime2 = maxTimeForFastDelivery - (int)unloadTime;
if (neededTime2 < minTimeForNewOrder)
{
routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = new TimeSpan(0, neededTime2, 0);
routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = false;
routeListNode.IsValidRLToFastDelivery = false;
}
else
{
routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = new TimeSpan(0, neededTime2, 0);
routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = true;
}
}
else
{
routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = maxTimeForFastDeliveryTimespan;
routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = true;
}
}
var rlIds = routeListNodes.Select(x => x.RouteList.Id).ToArray();
//OrderItems
var orderItemsToDeliver = uow.Session.QueryOver <RouteListItem>(() => rla)
.Inner.JoinAlias(() => rla.Order, () => o)
.Inner.JoinAlias(() => o.OrderItems, () => oi)
.Left.JoinAlias(() => rla.TransferedTo, () => rlaTransfered)
.WhereRestrictionOn(() => rla.RouteList.Id).IsIn(rlIds)
.WhereRestrictionOn(() => oi.Nomenclature.Id).IsIn(neededNomenclatures.Keys)
.Where(() =>
//не отменённые и не недовозы
rla.Status != RouteListItemStatus.Canceled && rla.Status != RouteListItemStatus.Overdue
// и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки)
&& (!rla.WasTransfered || rla.NeedToReload || o.IsFastDelivery)
// и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся)
&& (rla.Status != RouteListItemStatus.Transfered || (!rlaTransfered.NeedToReload && !o.IsFastDelivery)))
.SelectList(list => list
.SelectGroup(() => rla.RouteList.Id).WithAlias(() => ordersAmountAlias.RouteListId)
.SelectGroup(() => oi.Nomenclature.Id).WithAlias(() => ordersAmountAlias.NomenclatureId)
.SelectSum(() => oi.Count).WithAlias(() => ordersAmountAlias.Amount))
.TransformUsing(Transformers.AliasToBean <RouteListNomenclatureAmount>())
.Future <RouteListNomenclatureAmount>();
//OrderEquipments
var orderEquipmentsToDeliver = uow.Session.QueryOver <RouteListItem>(() => rla)
.Inner.JoinAlias(() => rla.Order, () => o)
.Inner.JoinAlias(() => o.OrderEquipments, () => oe)
.Left.JoinAlias(() => rla.TransferedTo, () => rlaTransfered)
.WhereRestrictionOn(() => rla.RouteList.Id).IsIn(rlIds)
.WhereRestrictionOn(() => oe.Nomenclature.Id).IsIn(neededNomenclatures.Keys)
.Where(() =>
//не отменённые и не недовозы
rla.Status != RouteListItemStatus.Canceled && rla.Status != RouteListItemStatus.Overdue
// и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки)
&& (!rla.WasTransfered || rla.NeedToReload || o.IsFastDelivery)
// и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся)
&& (rla.Status != RouteListItemStatus.Transfered || (!rlaTransfered.NeedToReload && !o.IsFastDelivery)))
.And(() => oe.Direction == Direction.Deliver)
.SelectList(list => list
.SelectGroup(() => rla.RouteList.Id).WithAlias(() => ordersAmountAlias.RouteListId)
.SelectGroup(() => oe.Nomenclature.Id).WithAlias(() => ordersAmountAlias.NomenclatureId)
.Select(Projections.Sum(Projections.Cast(NHibernateUtil.Decimal, Projections.Property(() => oe.Count)))
).WithAlias(() => ordersAmountAlias.Amount))
.TransformUsing(Transformers.AliasToBean <RouteListNomenclatureAmount>())
.Future <RouteListNomenclatureAmount>();
//CarLoadDocuments
var allLoaded = uow.Session.QueryOver <CarLoadDocument>(() => scld)
.Inner.JoinAlias(() => scld.Items, () => scldi)
.WhereRestrictionOn(() => scld.RouteList.Id).IsIn(rlIds)
.WhereRestrictionOn(() => scldi.Nomenclature.Id).IsIn(neededNomenclatures.Keys)
.SelectList(list => list
.SelectGroup(() => scld.RouteList.Id).WithAlias(() => loadDocumentsAmountAlias.RouteListId)
.SelectGroup(() => scldi.Nomenclature.Id).WithAlias(() => loadDocumentsAmountAlias.NomenclatureId)
.SelectSum(() => scldi.Amount).WithAlias(() => loadDocumentsAmountAlias.Amount))
.TransformUsing(Transformers.AliasToBean <RouteListNomenclatureAmount>())
.Future <RouteListNomenclatureAmount>();
var allToDeliver = orderItemsToDeliver
.Union(orderEquipmentsToDeliver)
.GroupBy(x => new { x.RouteListId, x.NomenclatureId })
.Select(group => new RouteListNomenclatureAmount
{
RouteListId = group.Key.RouteListId,
NomenclatureId = group.Key.NomenclatureId,
Amount = group.Sum(x => x.Amount)
})
.ToList();
//Выбираем МЛ, в котором хватает запаса номенклатур на поступивший быстрый заказ
foreach (var routeListNode in routeListNodes)
{
var toDeliverForRL = allToDeliver.Where(x => x.RouteListId == routeListNode.RouteList.Id).ToList();
var loadedForRL = allLoaded.Where(x => x.RouteListId == routeListNode.RouteList.Id).ToList();
foreach (var need in neededNomenclatures)
{
var toDeliver = toDeliverForRL.FirstOrDefault(x => x.NomenclatureId == need.Key)?.Amount ?? 0;
var loaded = loadedForRL.FirstOrDefault(x => x.NomenclatureId == need.Key)?.Amount ?? 0;
var onBoard = loaded - toDeliver;
if (onBoard < need.Value)
{
routeListNode.IsGoodsEnough.ParameterValue = false;
routeListNode.IsGoodsEnough.IsValidParameter = false;
routeListNode.IsValidRLToFastDelivery = false;
break;
}
}
}
if (routeListNodes != null)
{
fastDeliveryAvailabilityHistory.Items = fastDeliveryHistoryConverter
.ConvertVerificationDetailsNodesToAvailabilityHistoryItems(routeListNodes, fastDeliveryAvailabilityHistory);
}
return(fastDeliveryAvailabilityHistory);
}
public void SynchronizePaymentStatuses()
{
try
{
_logger.Info("Запущен процесс синхронизации статусов платежей");
using (var uow = UnitOfWorkFactory.CreateWithoutRoot())
{
RouteListItem routeListItemAlias = null;
Order orderAlias = null;
SmsPayment smsPaymentAlias = null;
RouteList routeListAlias = null;
var payments = uow.Session.QueryOver <SmsPayment>(() => smsPaymentAlias)
.Inner.JoinAlias(() => smsPaymentAlias.Order, () => orderAlias)
.JoinEntityAlias(() => routeListItemAlias, () => routeListItemAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Left.JoinAlias(() => routeListItemAlias.RouteList, () => routeListAlias)
.Where(() => smsPaymentAlias.SmsPaymentStatus == SmsPaymentStatus.WaitingForPayment)
.And(Restrictions.Disjunction()
.Add(Restrictions.Eq(Projections.Property(() => routeListAlias.Id), null))
.Add(() => routeListAlias.Status != RouteListStatus.Closed))
.List <SmsPayment>();
int count = 0;
foreach (var payment in payments)
{
var actualStatus = _paymentController.GetPaymentStatus(payment.ExternalId);
if (actualStatus == null || actualStatus == payment.SmsPaymentStatus)
{
continue;
}
switch (actualStatus.Value)
{
case SmsPaymentStatus.WaitingForPayment:
payment.SetWaitingForPayment();
break;
case SmsPaymentStatus.Paid:
payment.SetPaid(uow, DateTime.Now, uow.GetById <PaymentFrom>(_orderParametersProvider.PaymentByCardFromSmsId));
break;
case SmsPaymentStatus.Cancelled:
payment.SetCancelled();
break;
default:
throw new ArgumentOutOfRangeException();
}
uow.Save(payment);
uow.Commit();
count++;
}
_logger.Info(payments.Count == 0
? $"Не найдено ни одного платежа для синхронизации"
: $"Синхронизировано {count} статусов платежей");
}
}
catch (Exception ex) {
_logger.Error(ex, "При синхронизации произошла ошибка");
}
}
/// <summary>
/// Кол-во 19л. воды в заказе
/// </summary>
/// <returns>Кол-во 19л. воды в заказе</returns>
/// <param name="uow">Uow.</param>
/// <param name="order">Заказ</param>
public static int Get19LWatterQtyForOrder(IUnitOfWork uow, VodovozOrder order)
{
return(OrderSingletonRepository.GetInstance().Get19LWatterQtyForOrder(uow, order));
}
/// <summary>
/// Оборудование заказа от клиента
/// </summary>
/// <returns>Список оборудования от клиенту</returns>
/// <param name="uow">Uow.</param>
/// <param name="order">Заказ</param>
public static IList <ClientEquipmentNode> GetEquipmentFromClientForOrder(IUnitOfWork uow, VodovozOrder order)
{
return(OrderSingletonRepository.GetInstance().GetEquipmentFromClientForOrder(uow, order));
}
/// <summary>
/// Первый заказ контрагента, который можно считать выполненым.
/// </summary>
/// <returns>Первый заказ</returns>
/// <param name="uow">UoW</param>
/// <param name="counterparty">Контрагент</param>
public static VodovozOrder GetFirstRealOrderForClientForActionBottle(IUnitOfWork uow, VodovozOrder order, Counterparty counterparty)
{
return(OrderSingletonRepository.GetInstance().GetFirstRealOrderForClientForActionBottle(uow, order, counterparty));
}
public static OrderStatus[] GetStatusesForActualCount(VodovozOrder order)
{
return(OrderSingletonRepository.GetInstance().GetStatusesForActualCount(order));
}
/// <summary>
/// Список МЛ для заказа, отсортированный в порядке владения этим заказом, в случае переносов
/// </summary>
/// <returns>Список МЛ</returns>
/// <param name="UoW">UoW</param>
/// <param name="order">Заказ</param>
public static IList <RouteList> GetAllRLForOrder(IUnitOfWork UoW, VodovozOrder order)
{
return(OrderSingletonRepository.GetInstance().GetAllRLForOrder(UoW, order));
}
public IList <UndeliveredOrder> GetListOfUndeliveriesForOrder(IUnitOfWork uow, int orderId)
{
Order order = uow.GetById <Order>(orderId);
return(GetListOfUndeliveriesForOrder(uow, order));
}
public bool HasEnoughQuantityForFastDelivery(IUnitOfWork uow, RouteListItem routeListItemFrom, RouteList routeListTo)
{
RouteListItem routeListItemAlias = null;
RouteListItem routeListItemTransferedAlias = null;
OrderItem orderItemAlias = null;
NomenclatureAmountNode nomenclatureAmountAlias = null;
Order orderAlias = null;
OrderEquipment orderEquipmentAlias = null;
CarLoadDocument carLoadDocumentAlias = null;
CarLoadDocumentItem carLoadDocumentItemAlias = null;
var nomenclaturesToDeliver = routeListItemFrom.Order.GetAllGoodsToDeliver();
var neededIds = nomenclaturesToDeliver.Select(x => x.NomenclatureId).ToArray();
var orderItemsToDeliver = uow.Session.QueryOver <RouteListItem>(() => routeListItemAlias)
.Inner.JoinAlias(() => routeListItemAlias.Order, () => orderAlias)
.Inner.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => routeListItemAlias.TransferedTo, () => routeListItemTransferedAlias)
.Where(() => routeListItemAlias.RouteList.Id == routeListTo.Id)
.WhereRestrictionOn(() => orderItemAlias.Nomenclature.Id).IsIn(neededIds)
.Where(() =>
//не отменённые и не недовозы
routeListItemAlias.Status != RouteListItemStatus.Canceled && routeListItemAlias.Status != RouteListItemStatus.Overdue
// и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки)
&& (!routeListItemAlias.WasTransfered || routeListItemAlias.NeedToReload || orderAlias.IsFastDelivery)
// и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся)
&& (routeListItemAlias.Status != RouteListItemStatus.Transfered || (!routeListItemTransferedAlias.NeedToReload && !orderAlias.IsFastDelivery)))
.SelectList(list => list
.SelectGroup(() => orderItemAlias.Nomenclature.Id).WithAlias(() => nomenclatureAmountAlias.NomenclatureId)
.SelectSum(() => orderItemAlias.Count).WithAlias(() => nomenclatureAmountAlias.Amount)
).TransformUsing(Transformers.AliasToBean <NomenclatureAmountNode>())
.Future <NomenclatureAmountNode>();
var orderEquipmentsToDeliver = uow.Session.QueryOver <RouteListItem>(() => routeListItemAlias)
.Inner.JoinAlias(() => routeListItemAlias.Order, () => orderAlias)
.Inner.JoinAlias(() => orderAlias.OrderEquipments, () => orderEquipmentAlias)
.Left.JoinAlias(() => routeListItemAlias.TransferedTo, () => routeListItemTransferedAlias)
.Where(() => routeListItemAlias.RouteList.Id == routeListTo.Id)
.WhereRestrictionOn(() => orderEquipmentAlias.Nomenclature.Id).IsIn(neededIds)
.Where(() =>
//не отменённые и не недовозы
routeListItemAlias.Status != RouteListItemStatus.Canceled && routeListItemAlias.Status != RouteListItemStatus.Overdue
// и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки)
&& (!routeListItemAlias.WasTransfered || routeListItemAlias.NeedToReload || orderAlias.IsFastDelivery)
// и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся)
&& (routeListItemAlias.Status != RouteListItemStatus.Transfered || (!routeListItemTransferedAlias.NeedToReload && !orderAlias.IsFastDelivery)))
.And(() => orderEquipmentAlias.Direction == Domain.Orders.Direction.Deliver)
.SelectList(list => list
.SelectGroup(() => orderEquipmentAlias.Nomenclature.Id).WithAlias(() => nomenclatureAmountAlias.NomenclatureId)
.Select(Projections.Cast(NHibernateUtil.Decimal, Projections.Sum(Projections.Property(() => orderEquipmentAlias.Count)))).WithAlias(() => nomenclatureAmountAlias.Amount)
).TransformUsing(Transformers.AliasToBean <NomenclatureAmountNode>())
.Future <NomenclatureAmountNode>();
var allToDeliver = orderItemsToDeliver
.Union(orderEquipmentsToDeliver)
.GroupBy(x => new { x.NomenclatureId })
.Select(group => new NomenclatureAmountNode()
{
NomenclatureId = group.Key.NomenclatureId,
Amount = group.Sum(x => x.Amount)
})
.ToList();
var allLoaded = uow.Session.QueryOver <CarLoadDocument>(() => carLoadDocumentAlias)
.Inner.JoinAlias(() => carLoadDocumentAlias.Items, () => carLoadDocumentItemAlias)
.Where(() => carLoadDocumentAlias.RouteList.Id == routeListTo.Id)
.WhereRestrictionOn(() => carLoadDocumentItemAlias.Nomenclature.Id).IsIn(neededIds)
.SelectList(list => list
.SelectGroup(() => carLoadDocumentItemAlias.Nomenclature.Id).WithAlias(() => nomenclatureAmountAlias.NomenclatureId)
.SelectSum(() => carLoadDocumentItemAlias.Amount).WithAlias(() => nomenclatureAmountAlias.Amount)
).TransformUsing(Transformers.AliasToBean <NomenclatureAmountNode>())
.List <NomenclatureAmountNode>();
foreach (var need in nomenclaturesToDeliver)
{
var toDeliver = allToDeliver.FirstOrDefault(x => x.NomenclatureId == need.NomenclatureId)?.Amount ?? 0;
var loaded = allLoaded.FirstOrDefault(x => x.NomenclatureId == need.NomenclatureId)?.Amount ?? 0;
if (loaded - toDeliver < need.Amount)
{
return(false);
}
}
return(true);
}
public IList <UndeliveredOrder> GetListOfUndeliveriesForOrder(IUnitOfWork uow, Order order)
{
var query = uow.Session.QueryOver <UndeliveredOrder>()
.Where(u => u.OldOrder == order)
.List <UndeliveredOrder>();
return(query);
}
/// <summary>
/// Возврат словаря, у которого ключ это <see cref="PromotionalSet.Id"/>,
/// а значение - массив с <see cref="VodovozOrder.Id"/>, для всех точек доставок
/// похожих по полям <see cref="DeliveryPoint.City"/>,
/// <see cref="DeliveryPoint.Street"/>, <see cref="DeliveryPoint.Building"/>,
/// <see cref="DeliveryPoint.Room"/>
/// </summary>
/// <returns>Словарь</returns>
/// <param name="uow">Unit Of Work</param>
/// <param name="currOrder">Заказ, из которого берётся точка доставки</param>
/// <param name="ignoreCurrentOrder">Если <c>true</c>, то в выборке будет
/// игнорироваться заказ передаваемы в качестве параметра <paramref name="currOrder"/></param>
public Dictionary <int, int[]> GetPromotionalSetsAndCorrespondingOrdersForDeliveryPoint(IUnitOfWork uow, VodovozOrder currOrder, bool ignoreCurrentOrder = false)
{
if (GetPromotionalSetsAndCorrespondingOrdersForDeliveryPointTestGap != null)
{
return(GetPromotionalSetsAndCorrespondingOrdersForDeliveryPointTestGap(uow, currOrder, ignoreCurrentOrder));
}
VodovozOrder ordersAlias = null;
PromotionalSet promotionalSetAlias = null;
DeliveryPoint deliveryPointAlias = null;
var dp = currOrder.DeliveryPoint;
var oId = !ignoreCurrentOrder ? -1 : currOrder.Id;
var acceptableStatuses = new[] {
OrderStatus.Accepted,
OrderStatus.InTravelList,
OrderStatus.WaitForPayment,
OrderStatus.OnLoading,
OrderStatus.OnTheWay,
OrderStatus.Shipped,
OrderStatus.UnloadingOnStock,
OrderStatus.Closed
};
var subQuerySimilarDP = QueryOver.Of(() => deliveryPointAlias)
.Where(p => p.City == dp.City)
.Where(p => p.Street == dp.Street)
.Where(p => p.Building == dp.Building)
.Where(p => p.Room == dp.Room)
.Select(Projections.Property(() => deliveryPointAlias.Id))
;
var result = uow.Session.QueryOver(() => promotionalSetAlias)
.JoinAlias(() => promotionalSetAlias.Orders, () => ordersAlias)
.JoinAlias(() => ordersAlias.DeliveryPoint, () => deliveryPointAlias)
.Where(() => ordersAlias.Id != oId)
.Where(() => ordersAlias.OrderStatus.IsIn(acceptableStatuses))
.WithSubquery.WhereProperty(() => deliveryPointAlias.Id).In(subQuerySimilarDP)
.SelectList(list => list.Select(() => promotionalSetAlias.Id)
.Select(() => ordersAlias.Id))
.List <object[]>()
.GroupBy(x => (int)x[0])
.ToDictionary(g => g.Key, g => g.Select(x => (int)x[1]).ToArray());
return(result);
}
private IQueryOver <VodovozOrder> GetOrdersQuery(IUnitOfWork uow)
{
RetailOrderJournalNode resultAlias = null;
VodovozOrder orderAlias = null;
Nomenclature nomenclatureAlias = null;
OrderItem orderItemAlias = null;
Counterparty counterpartyAlias = null;
DeliveryPoint deliveryPointAlias = null;
DeliverySchedule deliveryScheduleAlias = null;
Employee authorAlias = null;
Employee lastEditorAlias = null;
District districtAlias = null;
CounterpartyContract contractAlias = null;
Nomenclature sanitizationNomenclature = _nomenclatureRepository.GetSanitisationNomenclature(uow);
var query = uow.Session.QueryOver <VodovozOrder>(() => orderAlias);
if (FilterViewModel != null && FilterViewModel.IsForRetail != null)
{
query.JoinEntityAlias(
() => counterpartyAlias,
() => counterpartyAlias.Id == orderAlias.Client.Id &&
counterpartyAlias.IsForRetail == FilterViewModel.IsForRetail,
NHibernate.SqlCommand.JoinType.InnerJoin);
}
else
{
query.Left.JoinAlias(o => o.Client, () => counterpartyAlias);
}
if (FilterViewModel.ViewTypes != ViewTypes.Order && FilterViewModel.ViewTypes != ViewTypes.All)
{
query.Where(o => o.Id == -1);
}
if (FilterViewModel.RestrictStatus != null)
{
query.Where(o => o.OrderStatus == FilterViewModel.RestrictStatus);
}
if (FilterViewModel.RestrictPaymentType != null)
{
query.Where(o => o.PaymentType == FilterViewModel.RestrictPaymentType);
}
if (FilterViewModel.HideStatuses != null)
{
query.WhereRestrictionOn(o => o.OrderStatus).Not.IsIn(FilterViewModel.HideStatuses);
}
if (FilterViewModel.RestrictOnlySelfDelivery != null)
{
query.Where(o => o.SelfDelivery == FilterViewModel.RestrictOnlySelfDelivery);
}
if (FilterViewModel.RestrictWithoutSelfDelivery != null)
{
query.Where(o => o.SelfDelivery != FilterViewModel.RestrictWithoutSelfDelivery);
}
if (FilterViewModel.RestrictCounterparty != null)
{
query.Where(o => o.Client == FilterViewModel.RestrictCounterparty);
}
if (FilterViewModel.DeliveryPoint != null)
{
query.Where(o => o.DeliveryPoint == FilterViewModel.DeliveryPoint);
}
if (FilterViewModel.RestrictStartDate != null)
{
query.Where(o => o.DeliveryDate >= FilterViewModel.RestrictStartDate);
}
if (FilterViewModel.RestrictEndDate != null)
{
query.Where(o => o.DeliveryDate <= FilterViewModel.RestrictEndDate.Value.AddDays(1).AddTicks(-1));
}
if (FilterViewModel.RestrictLessThreeHours == true)
{
query.Where(Restrictions
.GtProperty(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Time, "ADDTIME(?1, ?2)"),
NHibernateUtil.Time,
Projections.Property(() => deliveryScheduleAlias.From),
Projections.Constant("3:0:0")),
Projections.Property(() => deliveryScheduleAlias.To)));
}
if (FilterViewModel.RestrictHideService != null)
{
if (FilterViewModel.RestrictHideService.Value)
{
query.Where(o => o.OrderAddressType != OrderAddressType.Service);
}
else
{
query.Where(o => o.OrderAddressType == OrderAddressType.Service);
}
}
if (FilterViewModel.RestrictOnlyService != null)
{
if (FilterViewModel.RestrictOnlyService.Value)
{
query.Where(o => o.OrderAddressType == OrderAddressType.Service);
}
else
{
query.Where(o => o.OrderAddressType != OrderAddressType.Service);
}
}
if (FilterViewModel.OrderPaymentStatus != null)
{
query.Where(o => o.OrderPaymentStatus == FilterViewModel.OrderPaymentStatus);
}
if (FilterViewModel.Organisation != null)
{
query.Where(() => contractAlias.Organization.Id == FilterViewModel.Organisation.Id);
}
if (FilterViewModel.PaymentByCardFrom != null)
{
query.Where(o => o.PaymentByCardFrom.Id == FilterViewModel.PaymentByCardFrom.Id);
}
var bottleCountSubquery = QueryOver.Of <OrderItem>(() => orderItemAlias)
.Where(() => orderAlias.Id == orderItemAlias.Order.Id)
.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => nomenclatureAlias.Category == NomenclatureCategory.water && nomenclatureAlias.TareVolume == TareVolume.Vol19L)
.Select(Projections.Sum(() => orderItemAlias.Count));
var sanitisationCountSubquery = QueryOver.Of <OrderItem>(() => orderItemAlias)
.Where(() => orderAlias.Id == orderItemAlias.Order.Id)
.Where(() => orderItemAlias.Nomenclature.Id == sanitizationNomenclature.Id)
.Select(Projections.Sum(() => orderItemAlias.Count));
var orderSumSubquery = QueryOver.Of <OrderItem>(() => orderItemAlias)
.Where(() => orderItemAlias.Order.Id == orderAlias.Id)
.Select(
Projections.Sum(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "ROUND(IFNULL(?1, ?2) * ?3 - ?4, 2)"),
NHibernateUtil.Decimal,
Projections.Property <OrderItem>(x => x.ActualCount),
Projections.Property <OrderItem>(x => x.Count),
Projections.Property <OrderItem>(x => x.Price),
Projections.Property <OrderItem>(x => x.DiscountMoney)
)
)
);
query.Left.JoinAlias(o => o.DeliveryPoint, () => deliveryPointAlias)
.Left.JoinAlias(o => o.DeliverySchedule, () => deliveryScheduleAlias)
.Left.JoinAlias(o => o.Author, () => authorAlias)
.Left.JoinAlias(o => o.LastEditor, () => lastEditorAlias)
.Left.JoinAlias(() => deliveryPointAlias.District, () => districtAlias)
.Left.JoinAlias(o => o.Contract, () => contractAlias);
query.Where(GetSearchCriterion(
() => orderAlias.Id,
() => counterpartyAlias.Name,
() => deliveryPointAlias.CompiledAddress,
() => authorAlias.LastName,
() => orderAlias.DriverCallId,
() => orderAlias.OnlineOrder,
() => orderAlias.EShopOrder,
() => orderAlias.OrderPaymentStatus
));
var resultQuery = query
.SelectList(list => list
.Select(() => orderAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => orderAlias.SelfDelivery).WithAlias(() => resultAlias.IsSelfDelivery)
.Select(() => orderAlias.DeliveryDate).WithAlias(() => resultAlias.Date)
.Select(() => orderAlias.CreateDate).WithAlias(() => resultAlias.CreateDate)
.Select(() => deliveryScheduleAlias.Name).WithAlias(() => resultAlias.DeliveryTime)
.Select(() => orderAlias.OrderStatus).WithAlias(() => resultAlias.StatusEnum)
.Select(() => orderAlias.Address1c).WithAlias(() => resultAlias.Address1c)
.Select(() => authorAlias.LastName).WithAlias(() => resultAlias.AuthorLastName)
.Select(() => authorAlias.Name).WithAlias(() => resultAlias.AuthorName)
.Select(() => authorAlias.Patronymic).WithAlias(() => resultAlias.AuthorPatronymic)
.Select(() => lastEditorAlias.LastName).WithAlias(() => resultAlias.LastEditorLastName)
.Select(() => lastEditorAlias.Name).WithAlias(() => resultAlias.LastEditorName)
.Select(() => lastEditorAlias.Patronymic).WithAlias(() => resultAlias.LastEditorPatronymic)
.Select(() => orderAlias.LastEditedTime).WithAlias(() => resultAlias.LastEditedTime)
.Select(() => orderAlias.DriverCallId).WithAlias(() => resultAlias.DriverCallId)
.Select(() => orderAlias.OnlineOrder).WithAlias(() => resultAlias.OnlineOrder)
.Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.Counterparty)
.Select(() => districtAlias.DistrictName).WithAlias(() => resultAlias.DistrictName)
.Select(() => deliveryPointAlias.CompiledAddress).WithAlias(() => resultAlias.CompilledAddress)
.Select(() => deliveryPointAlias.City).WithAlias(() => resultAlias.City)
.Select(() => deliveryPointAlias.Street).WithAlias(() => resultAlias.Street)
.Select(() => deliveryPointAlias.Building).WithAlias(() => resultAlias.Building)
.Select(() => orderAlias.EShopOrder).WithAlias(() => resultAlias.EShopOrder)
.Select(() => orderAlias.OrderPaymentStatus).WithAlias(() => resultAlias.OrderPaymentStatus)
.SelectSubQuery(orderSumSubquery).WithAlias(() => resultAlias.Sum)
.SelectSubQuery(bottleCountSubquery).WithAlias(() => resultAlias.BottleAmount)
.SelectSubQuery(sanitisationCountSubquery).WithAlias(() => resultAlias.SanitisationAmount)
)
.OrderBy(x => x.CreateDate).Desc
.SetTimeout(60)
.TransformUsing(Transformers.AliasToBean <RetailOrderJournalNode <VodovozOrder> >());
return(resultQuery);
}
public IEnumerable <ReceiptForOrderNode> GetOrdersForCashReceiptServiceToSend(
IUnitOfWork uow,
IOrderParametersProvider orderParametersProvider,
DateTime?startDate = null)
{
ReceiptForOrderNode resultAlias = null;
OrderItem orderItemAlias = null;
VodovozOrder orderAlias = null;
CashReceipt cashReceiptAlias = null;
Counterparty counterpartyAlias = null;
var orderSumProjection = Projections.Sum(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "CAST(IFNULL(?1 * ?2 - ?3, 0) AS DECIMAL(14,2))"),
NHibernateUtil.Decimal,
Projections.Property(() => orderItemAlias.Count),
Projections.Property(() => orderItemAlias.Price),
Projections.Property(() => orderItemAlias.DiscountMoney)
)
);
var paymentTypeRestriction = Restrictions.Disjunction()
.Add(() => orderAlias.PaymentType == PaymentType.Terminal)
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.PaymentType == PaymentType.cash)
.Add(() => counterpartyAlias.AlwaysSendReceipts))
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.PaymentType == PaymentType.ByCard)
.Add(Restrictions.Disjunction()
.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.PaymentFromTerminalId)
.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.GetPaymentByCardFromFastPaymentServiceId)
.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.GetPaymentByCardFromAvangardId)
//Не выбираем данный источник отправки для чеков, пока нет оплаты на сайте по QR
/*.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.GetPaymentByCardFromSiteByQrCode)*/));
var statusRestriction = Restrictions.Disjunction()
.Add(Restrictions.In(Projections.Property(() => orderAlias.OrderStatus),
new[] { OrderStatus.Shipped, OrderStatus.UnloadingOnStock }))
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.SelfDelivery)
.Add(() => orderAlias.IsSelfDeliveryPaid));
var positiveSumRestriction = Restrictions.Gt(orderSumProjection, 0);
var notSentRestriction = Restrictions.Disjunction()
.Add(Restrictions.IsNull(Projections.Property(() => cashReceiptAlias.Id)))
.Add(() => !cashReceiptAlias.Sent);
var ordersToSendQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias)
.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Inner.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Where(paymentTypeRestriction)
.And(statusRestriction)
.And(positiveSumRestriction)
.And(notSentRestriction);
if (startDate.HasValue)
{
ordersToSendQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value);
}
var ordersToSend = ordersToSendQuery
.SelectList(list => list
.SelectGroup(() => orderAlias.Id).WithAlias(() => resultAlias.OrderId)
.Select(() => cashReceiptAlias.Id).WithAlias(() => resultAlias.ReceiptId)
.Select(() => cashReceiptAlias.Sent).WithAlias(() => resultAlias.WasSent))
.TransformUsing(Transformers.AliasToBean <ReceiptForOrderNode>())
.List <ReceiptForOrderNode>();
return(ordersToSend);
}
/// <summary>
/// Запрос выбирающий количество добавленное на склад, отгруженное со склада
/// и зарезервированное в заказах каждой номенклатуры по выбранному типу оборудования
/// </summary>
public QueryOver <Nomenclature, Nomenclature> QueryAvailableNonSerialEquipmentForRent(EquipmentKind kind)
{
Nomenclature nomenclatureAlias = null;
WarehouseMovementOperation operationAddAlias = null;
//Подзапрос выбирающий по номенклатуре количество добавленное на склад
var subqueryAdded = QueryOver.Of(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.IncomingWarehouse)))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
//Подзапрос выбирающий по номенклатуре количество отгруженное со склада
var subqueryRemoved = QueryOver.Of(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.WriteoffWarehouse)))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
//Подзапрос выбирающий по номенклатуре количество зарезервированное в заказах до отгрузки со склада
Vodovoz.Domain.Orders.Order localOrderAlias = null;
OrderEquipment localOrderEquipmentAlias = null;
Equipment localEquipmentAlias = null;
var subqueryReserved = QueryOver.Of(() => localOrderAlias)
.JoinAlias(() => localOrderAlias.OrderEquipments, () => localOrderEquipmentAlias)
.JoinAlias(() => localOrderEquipmentAlias.Equipment, () => localEquipmentAlias)
.Where(() => localEquipmentAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Where(() => localOrderEquipmentAlias.Direction == Direction.Deliver)
.Where(() => localOrderAlias.OrderStatus == OrderStatus.Accepted ||
localOrderAlias.OrderStatus == OrderStatus.InTravelList ||
localOrderAlias.OrderStatus == OrderStatus.OnLoading)
.Select(Projections.Sum(() => localOrderEquipmentAlias.Count));
NomenclatureForRentNode resultAlias = null;
MeasurementUnits unitAlias = null;
EquipmentKind equipmentKindAlias = null;
//Запрос выбирающий количество добавленное на склад, отгруженное со склада
//и зарезервированное в заказах каждой номенклатуры по выбранному типу оборудования
var query = QueryOver.Of(() => nomenclatureAlias)
.JoinAlias(() => nomenclatureAlias.Unit, () => unitAlias)
.JoinAlias(() => nomenclatureAlias.Kind, () => equipmentKindAlias);
if (kind != null)
{
query = query.Where(() => equipmentKindAlias.Id == kind.Id);
}
query = query.SelectList(
list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.NomenclatureName)
.Select(() => equipmentKindAlias.Id).WithAlias(() => resultAlias.TypeId)
.Select(() => equipmentKindAlias.Name).WithAlias(() => resultAlias.EquipmentKindName)
.Select(() => unitAlias.Name).WithAlias(() => resultAlias.UnitName)
.Select(() => unitAlias.Digits).WithAlias(() => resultAlias.UnitDigits)
.SelectSubQuery(subqueryAdded).WithAlias(() => resultAlias.Added)
.SelectSubQuery(subqueryRemoved).WithAlias(() => resultAlias.Removed)
.SelectSubQuery(subqueryReserved).WithAlias(() => resultAlias.Reserved))
.OrderBy(x => x.Name).Asc
.TransformUsing(Transformers.AliasToBean <NomenclatureForRentNode>());
return(query);
}
public IQueryOver <Payment, Payment> GetAllUnallocatedBalances(IUnitOfWork uow, int closingDocumentDeliveryScheduleId)
{
UnallocatedBalancesJournalNode resultAlias = null;
Order orderAlias = null;
Order orderAlias2 = null;
OrderItem orderItemAlias = null;
PaymentItem paymentItemAlias = null;
Counterparty counterpartyAlias = null;
Organization organizationAlias = null;
CounterpartyContract counterpartyContractAlias = null;
Organization orderOrganizationAlias = null;
DeliverySchedule deliveryScheduleAlias = null;
DeliverySchedule deliveryScheduleAlias2 = null;
CashlessMovementOperation cashlessMovementOperationAlias = null;
var query = uow.Session.QueryOver <Payment>()
.Inner.JoinAlias(cmo => cmo.Counterparty, () => counterpartyAlias)
.Inner.JoinAlias(cmo => cmo.Organization, () => organizationAlias);
var income = QueryOver.Of <CashlessMovementOperation>()
.Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id)
.And(cmo => cmo.Organization.Id == organizationAlias.Id)
.And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled)
.Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Income));
var expense = QueryOver.Of <CashlessMovementOperation>()
.Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id)
.And(cmo => cmo.Organization.Id == organizationAlias.Id)
.And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled)
.Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Expense));
var balanceProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - ?2"),
NHibernateUtil.Decimal,
Projections.SubQuery(income),
Projections.SubQuery(expense));
var orderSumProjection = OrderRepository.GetOrderSumProjection(orderItemAlias);
var totalNotPaidOrders = QueryOver.Of(() => orderAlias)
.Inner.JoinAlias(o => o.OrderItems, () => orderItemAlias)
.Inner.JoinAlias(o => o.Contract, () => counterpartyContractAlias)
.Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias)
.Inner.JoinAlias(o => o.DeliverySchedule, () => deliveryScheduleAlias)
.Where(() => orderAlias.Client.Id == counterpartyAlias.Id)
.And(() => orderOrganizationAlias.Id == organizationAlias.Id)
.And(() => orderAlias.OrderStatus == OrderStatus.Shipped ||
orderAlias.OrderStatus == OrderStatus.UnloadingOnStock ||
orderAlias.OrderStatus == OrderStatus.Closed)
.And(() => orderAlias.PaymentType == PaymentType.cashless)
.And(() => orderAlias.OrderPaymentStatus != OrderPaymentStatus.Paid)
.And(() => deliveryScheduleAlias.Id != closingDocumentDeliveryScheduleId)
.Select(orderSumProjection)
.Where(Restrictions.Gt(orderSumProjection, 0));
var totalPayPartiallyPaidOrders = QueryOver.Of(() => paymentItemAlias)
.JoinEntityAlias(() => orderAlias2, () => paymentItemAlias.Order.Id == orderAlias2.Id, JoinType.InnerJoin)
.Inner.JoinAlias(() => orderAlias2.Contract, () => counterpartyContractAlias)
.Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias)
.Inner.JoinAlias(() => paymentItemAlias.CashlessMovementOperation, () => cashlessMovementOperationAlias)
.Inner.JoinAlias(() => orderAlias2.DeliverySchedule, () => deliveryScheduleAlias2)
.Where(() => orderAlias2.Client.Id == counterpartyAlias.Id)
.And(() => orderOrganizationAlias.Id == organizationAlias.Id)
.And(() => cashlessMovementOperationAlias.CashlessMovementOperationStatus != AllocationStatus.Cancelled)
.And(() => orderAlias2.OrderStatus == OrderStatus.Shipped ||
orderAlias2.OrderStatus == OrderStatus.UnloadingOnStock ||
orderAlias2.OrderStatus == OrderStatus.Closed)
.And(() => orderAlias2.PaymentType == PaymentType.cashless)
.And(() => orderAlias2.OrderPaymentStatus == OrderPaymentStatus.PartiallyPaid)
.And(() => deliveryScheduleAlias2.Id != closingDocumentDeliveryScheduleId)
.Select(Projections.Sum(() => cashlessMovementOperationAlias.Expense));
var counterpartyDebtProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - IFNULL(?2, ?3)"),
NHibernateUtil.Decimal,
Projections.SubQuery(totalNotPaidOrders),
Projections.SubQuery(totalPayPartiallyPaidOrders),
Projections.Constant(0));
return(query.SelectList(list => list
.SelectGroup(() => counterpartyAlias.Id).WithAlias(() => resultAlias.CounterpartyId)
.SelectGroup(() => organizationAlias.Id).WithAlias(() => resultAlias.OrganizationId)
.Select(p => counterpartyAlias.INN).WithAlias(() => resultAlias.CounterpartyINN)
.Select(p => counterpartyAlias.Name).WithAlias(() => resultAlias.CounterpartyName)
.Select(p => organizationAlias.Name).WithAlias(() => resultAlias.OrganizationName)
.Select(balanceProjection).WithAlias(() => resultAlias.CounterpartyBalance)
.Select(counterpartyDebtProjection).WithAlias(() => resultAlias.CounterpartyDebt))
.Where(Restrictions.Gt(balanceProjection, 0))
.And(Restrictions.Gt(counterpartyDebtProjection, 0))
.OrderBy(balanceProjection).Desc
.TransformUsing(Transformers.AliasToBean <UnallocatedBalancesJournalNode>())
.SetTimeout(180));
}