public static IUnitOfWorkGeneric<NonfreeRentAgreement> Create(CounterpartyContract contract)
{
var uow = UnitOfWorkFactory.CreateWithNewRoot<NonfreeRentAgreement> ();
uow.Root.Contract = uow.GetById<CounterpartyContract>(contract.Id);
uow.Root.AgreementNumber = AdditionalAgreement.GetNumber (uow.Root.Contract);
return uow;
}
void RunAgreementCreateDialog(CounterpartyContract contract)
{
ITdiTab dlg;
string paymentTypeString = "";
switch (UoWGeneric.Root.Payment)
{
case PaymentType.cash:
paymentTypeString = "наличной";
break;
case PaymentType.cashless:
paymentTypeString = "безналичной";
break;
case PaymentType.barter:
paymentTypeString = "бартерной";
break;
}
string question = "Отсутствует доп. соглашение сервиса с клиентом в договоре для " +
paymentTypeString +
" формы оплаты. Создать?";
if (MessageDialogWorks.RunQuestionDialog(question))
{
dlg = new RepairAgreementDlg(contract);
(dlg as IAgreementSaved).AgreementSaved += (sender, e) => {
UoWGeneric.Root.InitialOrder?.CreateOrderAgreementDocument(e.Agreement);
};
TabParent.AddSlaveTab(this, dlg);
}
}
public WaterAgreementDlg(CounterpartyContract contract, DeliveryPoint deliveryPoint, DateTime?date) : this(contract, deliveryPoint)
{
if (date.HasValue)
{
UoWGeneric.Root.IssueDate = UoWGeneric.Root.StartDate = date.Value;
}
ConfigureDlg();
Entity.FillFixedPricesFromDeliveryPoint(UoW);
}
public FreeRentAgreementDlg(CounterpartyContract contract, DeliveryPoint point, DateTime?IssueDate)
{
this.Build();
UoWGeneric = FreeRentAgreement.Create(contract);
UoWGeneric.Root.DeliveryPoint = point;
if (IssueDate.HasValue)
{
UoWGeneric.Root.IssueDate = UoWGeneric.Root.StartDate = IssueDate.Value;
}
ConfigureDlg();
}
public DailyRentAgreementDlg(CounterpartyContract contract, DeliveryPoint point, DateTime?IssueDate, PaidRentPackage paidRentPackage) // : this (contract)
{
this.Build();
UoWGeneric = DailyRentAgreement.Create(contract);
UoWGeneric.Root.DeliveryPoint = point;
if (IssueDate.HasValue)
{
UoWGeneric.Root.IssueDate = UoWGeneric.Root.StartDate = IssueDate.Value;
}
ConfigureDlg();
PaidRentPackage = paidRentPackage;
dailyrentpackagesview1.PaidRentPackage = PaidRentPackage;
dailyrentpackagesview1.AddEquipment(PaidRentPackage);
}
public FreeRentAgreementDlg(CounterpartyContract contract, DeliveryPoint point, DateTime?IssueDate, FreeRentPackage freeRentPackage)
{
this.Build();
UoWGeneric = FreeRentAgreement.Create(contract);
UoWGeneric.Root.DeliveryPoint = point;
if (IssueDate.HasValue)
{
UoWGeneric.Root.IssueDate = UoWGeneric.Root.StartDate = IssueDate.Value;
}
ConfigureDlg();
FreeRentPackage = freeRentPackage;
freerentpackagesview1.FreeRentPackage = FreeRentPackage;
freerentpackagesview1.AddEquipment(FreeRentPackage);
}
public EquipSalesAgreementDlg(CounterpartyContract contract, DeliveryPoint point, DateTime?IssueDate, Nomenclature nomenclature = null)
{
this.Build();
UoWGeneric = SalesEquipmentAgreement.Create(contract);
UoWGeneric.Root.DeliveryPoint = point;
if (IssueDate.HasValue)
{
UoWGeneric.Root.IssueDate = UoWGeneric.Root.StartDate = IssueDate.Value;
}
if (nomenclature != null)
{
Entity.AddEquipment(nomenclature);
}
ConfigureDlg();
}
public IList <NotFullyPaidOrderNode> GetAllNotFullyPaidOrdersByClientAndOrg(
IUnitOfWork uow, int counterpartyId, int organizationId, int closingDocumentDeliveryScheduleId)
{
VodovozOrder orderAlias = null;
OrderItem orderItemAlias = null;
PaymentItem paymentItemAlias = null;
CounterpartyContract counterpartyContractAlias = null;
Organization orderOrganizationAlias = null;
DeliverySchedule deliveryScheduleAlias = null;
CashlessMovementOperation cashlessMovementOperationAlias = null;
NotFullyPaidOrderNode resultAlias = null;
var orderSumProjection = GetOrderSumProjection(orderItemAlias);
var allocatedSumProjection = QueryOver.Of(() => paymentItemAlias)
.JoinAlias(pi => pi.CashlessMovementOperation, () => cashlessMovementOperationAlias)
.Where(pi => pi.Order.Id == orderAlias.Id)
.Where(pi => pi.PaymentItemStatus != AllocationStatus.Cancelled)
.Select(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "IFNULL(?1, ?2)"),
NHibernateUtil.Decimal,
Projections.Sum(() => cashlessMovementOperationAlias.Expense),
Projections.Constant(0)));
return(uow.Session.QueryOver(() => 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 == counterpartyId)
.And(() => orderOrganizationAlias.Id == organizationId)
.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)
.SelectList(list =>
list.SelectGroup(o => o.Id).WithAlias(() => resultAlias.Id)
.Select(o => o.DeliveryDate).WithAlias(() => resultAlias.OrderDeliveryDate)
.Select(o => o.CreateDate).WithAlias(() => resultAlias.OrderCreationDate)
.Select(orderSumProjection).WithAlias(() => resultAlias.OrderSum)
.SelectSubQuery(allocatedSumProjection).WithAlias(() => resultAlias.AllocatedSum))
.Where(Restrictions.Gt(orderSumProjection, 0))
.TransformUsing(Transformers.AliasToBean <NotFullyPaidOrderNode>())
.OrderBy(o => o.DeliveryDate).Asc
.OrderBy(o => o.CreateDate).Asc
.List <NotFullyPaidOrderNode>());
}
public override bool Save()
{
var valid = new QSValidator <ServiceClaim> (UoWGeneric.Root);
if (valid.RunDlgIfNotValid((Window)this.Toplevel))
{
return(false);
}
CounterpartyContract contract = CounterpartyContractRepository.GetCounterpartyContractByPaymentType
(UoWGeneric, UoWGeneric.Root.Counterparty, UoWGeneric.Root.Counterparty.PersonType, UoWGeneric.Root.Payment);
if (contract == null)
{
RunContractCreateDialog();
return(false);
}
UoWGeneric.Session.Refresh(contract);
if (!contract.RepairAgreementExists())
{
RunAgreementCreateDialog(contract);
return(false);
}
if (UoWGeneric.Root.InitialOrder != null)
{
UoWGeneric.Root.InitialOrder.AddServiceClaimAsInitial(UoWGeneric.Root);
}
if (UoWGeneric.Root.FinalOrder != null)
{
UoWGeneric.Root.FinalOrder.AddServiceClaimAsFinal(UoWGeneric.Root);
}
if (UoWGeneric.IsNew)
{
UoWGeneric.Root.AddHistoryRecord(UoWGeneric.Root.Status,
String.IsNullOrWhiteSpace(textComment.Buffer.Text) ? "Заявка зарегистрирована" : textComment.Buffer.Text);
}
logger.Info("Сохраняем заявку на обслуживание...");
UoWGeneric.Save();
logger.Info("Ok");
return(true);
}
private void LoadData()
{
CounterpartyContract contractAlias = null;
//получаем список контрактов
var contracts = UoW.Session.QueryOver <CounterpartyContract>(() => contractAlias)
.Where(() => contractAlias.Counterparty.Id == Counterparty.Id)
.List();
foreach (var contract in contracts)
{
CounterpartyDocumentNode contractNode = new CounterpartyDocumentNode();
contractNode.Document = contract;
contractNode.Documents = new List <CounterpartyDocumentNode>();
CounterpartyDocs.Add(contractNode);
}
ytreeDocuments.YTreeModel = new RecursiveTreeModel <CounterpartyDocumentNode>(CounterpartyDocs, x => x.Parent, x => x.Documents);
}
public CounterpartyContract CreateContract(IUnitOfWork uow, Order order, DateTime?issueDate)
{
var contractType = counterpartyContractRepository.GetContractTypeForPaymentType(order.Client.PersonType, order.PaymentType);
var org = organizationProvider.GetOrganization(uow, order);
var contractSubNumber = CounterpartyContract.GenerateSubNumber(order.Client);
CounterpartyContract contract = new CounterpartyContract {
Counterparty = order.Client,
ContractSubNumber = contractSubNumber,
Organization = org,
IsArchive = false,
ContractType = contractType
};
if (issueDate.HasValue)
{
contract.IssueDate = issueDate.Value;
}
return(contract);
}
public FreeRentAgreementDlg(CounterpartyContract contract)
{
this.Build();
UoWGeneric = FreeRentAgreement.Create(contract);
ConfigureDlg();
}
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 override void UpdateNodes()
{
Counterparty counterpartyAlias = null;
Counterparty counterpartyAliasForSubquery = null;
CounterpartyContract contractAlias = null;
CounterpartyVMNode resultAlias = null;
Vodovoz.Domain.Contacts.Phone phoneAlias = null;
DeliveryPoint addressAlias = null;
Tag tagAliasForSubquery = null;
var query = UoW.Session.QueryOver <Counterparty>(() => counterpartyAlias);
if (Filter != null && !Filter.RestrictIncludeArhive)
{
query.Where(c => !c.IsArchive);
}
if (Filter?.CounterpartyType != null)
{
query.Where(t => t.CounterpartyType == Filter.CounterpartyType);
}
var contractsSubquery = QueryOver.Of <CounterpartyContract>(() => contractAlias)
.Left.JoinAlias(c => c.Counterparty, () => counterpartyAliasForSubquery)
.Where(() => counterpartyAlias.Id == counterpartyAliasForSubquery.Id)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( CONCAT(?2,' - ',?1) SEPARATOR ?3)"),
NHibernateUtil.String,
Projections.Property(() => contractAlias.ContractSubNumber),
Projections.Property(() => counterpartyAliasForSubquery.VodovozInternalId),
Projections.Constant("\n")));
var addressSubquery = QueryOver.Of <DeliveryPoint>(() => addressAlias)
.Where(d => d.Counterparty.Id == counterpartyAlias.Id)
.Where(() => addressAlias.IsActive)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => addressAlias.CompiledAddress),
Projections.Constant("\n")));
var tagsSubquery = QueryOver.Of <Counterparty>(() => counterpartyAliasForSubquery)
.Where(() => counterpartyAlias.Id == counterpartyAliasForSubquery.Id)
.JoinAlias(c => c.Tags, () => tagAliasForSubquery)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( CONCAT(' <span foreground=\"', ?1, '\"> ♥</span>', ?2) SEPARATOR '\n')"),
NHibernateUtil.String,
Projections.Property(() => tagAliasForSubquery.ColorText),
Projections.Property(() => tagAliasForSubquery.Name)
));
if (Filter != null && Filter.Tag != null)
{
query.JoinAlias(c => c.Tags, () => tagAliasForSubquery)
.Where(() => tagAliasForSubquery.Id == Filter.Tag.Id);
}
var counterpartyList = query
.JoinAlias(c => c.Phones, () => phoneAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.SelectList(list => list
.SelectGroup(c => c.Id).WithAlias(() => resultAlias.Id)
.SelectGroup(c => c.VodovozInternalId).WithAlias(() => resultAlias.InternalId)
.Select(c => c.Name).WithAlias(() => resultAlias.Name)
.Select(c => c.INN).WithAlias(() => resultAlias.INN)
.Select(c => c.IsArchive).WithAlias(() => resultAlias.IsArhive)
.SelectSubQuery(contractsSubquery).WithAlias(() => resultAlias.Contracts)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => phoneAlias.Number),
Projections.Constant("\n"))
).WithAlias(() => resultAlias.Phones)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => phoneAlias.DigitsNumber),
Projections.Constant("\n"))
).WithAlias(() => resultAlias.PhonesDigits)
.SelectSubQuery(addressSubquery).WithAlias(() => resultAlias.Addresses)
.SelectSubQuery(tagsSubquery).WithAlias(() => resultAlias.Tags)
)
.TransformUsing(Transformers.AliasToBean <CounterpartyVMNode>())
.List <CounterpartyVMNode>();
SetItemsSource(counterpartyList);
}
public EquipSalesAgreementDlg(CounterpartyContract contract)
{
this.Build();
UoWGeneric = SalesEquipmentAgreement.Create(contract);
ConfigureDlg();
}
public WaterAgreementDlg(CounterpartyContract contract, DeliveryPoint deliveryPoint) : this(contract)
{
UoWGeneric.Root.DeliveryPoint = UoW.GetById <DeliveryPoint>(deliveryPoint.Id);
ConfigureDlg();
}
public WaterAgreementDlg(CounterpartyContract contract)
{
this.Build();
UoWGeneric = WaterSalesAgreement.Create(contract);
ConfigureDlg();
}
public ContractSavedEventArgs(CounterpartyContract contract)
{
Contract = contract;
}
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());
}
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 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));
}
