public int GetBottlesAtCounterparty(IUnitOfWork UoW, Counterparty counterparty, DateTime?before = null)
{
BottlesMovementOperation operationAlias = null;
BottlesBalanceQueryResult result = null;
var queryResult = UoW.Session.QueryOver <BottlesMovementOperation>(() => operationAlias)
.Where(() => operationAlias.Counterparty.Id == counterparty.Id);
if (before.HasValue)
{
queryResult.Where(() => operationAlias.OperationTime < before);
}
var bottles = queryResult.SelectList(list => list
.SelectSum(() => operationAlias.Delivered).WithAlias(() => result.Delivered)
.SelectSum(() => operationAlias.Returned).WithAlias(() => result.Returned)
).TransformUsing(Transformers.AliasToBean <BottlesBalanceQueryResult>()).List <BottlesBalanceQueryResult>()
.FirstOrDefault()?.BottlesDebt ?? 0;
return(bottles);
}
public int GetBottleDebtBySelfDelivery(IUnitOfWork UoW, Counterparty counterparty)
{
BottlesMovementOperation operationAlias = null;
BottlesBalanceQueryResult result = null;
Order orderAlias = null;
var queryResult = UoW.Session.QueryOver(() => operationAlias)
.JoinAlias(() => operationAlias.Order, () => orderAlias, NHibernate.SqlCommand.JoinType.RightOuterJoin)
.Where(() => operationAlias.Counterparty == counterparty)
.And(() => orderAlias.SelfDelivery);
var bottles = queryResult.SelectList(list => list
.SelectSum(() => operationAlias.Delivered).WithAlias(() => result.Delivered)
.SelectSum(() => operationAlias.Returned).WithAlias(() => result.Returned)
)
.TransformUsing(Transformers.AliasToBean <BottlesBalanceQueryResult>()).List <BottlesBalanceQueryResult>()
.FirstOrDefault()?
.BottlesDebt ?? 0;
return(bottles);
}
private IQueryOver <RouteListItem> GetQuery(IUnitOfWork uow)
{
BottlesMovementOperation debtBottlesOperationAlias = null;
VodovozOrder orderAlias = null;
RouteListItem routeListItemAlias = null;
RouteList routeListAlias = null;
Employee driverAlias = null;
Phone phoneAlias = null;
DriverMessageJournalNode resultAlias = null;
var bottlesDebtSubquery = QueryOver.Of(() => debtBottlesOperationAlias)
.Where(() => debtBottlesOperationAlias.DeliveryPoint.Id == orderAlias.DeliveryPoint.Id)
.Select(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Int32, "?1 - ?2"),
NHibernateUtil.Int32,
Projections.Sum(Projections.Property(() => debtBottlesOperationAlias.Delivered)),
Projections.Sum(Projections.Property(() => debtBottlesOperationAlias.Returned))
)
);
var query = uow.Session.QueryOver(() => routeListItemAlias)
.Left.JoinAlias(() => routeListItemAlias.Order, () => orderAlias)
.Left.JoinAlias(() => routeListItemAlias.RouteList, () => routeListAlias)
.Left.JoinAlias(() => routeListAlias.Driver, () => driverAlias)
.Left.JoinAlias(() => driverAlias.Phones, () => phoneAlias)
.Where(Restrictions.IsNotNull(Projections.Property(() => orderAlias.DriverMobileAppCommentTime)))
.Where(() => routeListItemAlias.Status != RouteListItemStatus.Transfered)
.Where(Restrictions.Eq(Projections.SqlFunction("DATE", NHibernateUtil.Date, Projections.Property(() => orderAlias.DriverMobileAppCommentTime)), DateTime.Today));
query.Where(
GetSearchCriterion(
() => orderAlias.Id,
() => routeListAlias.Id,
() => driverAlias.LastName,
() => driverAlias.Name,
() => driverAlias.Patronymic,
() => orderAlias.DriverMobileAppComment,
() => phoneAlias.DigitsNumber
)
);
query.SelectList(list => list
.SelectGroup(() => orderAlias.Id).WithAlias(() => resultAlias.OrderId)
.Select(Projections.Property(() => orderAlias.DriverMobileAppCommentTime)).WithAlias(() => resultAlias.CommentDate)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GET_PERSON_NAME_WITH_INITIALS(?1, ?2, ?3)"),
NHibernateUtil.String,
Projections.Property(() => driverAlias.LastName),
Projections.Property(() => driverAlias.Name),
Projections.Property(() => driverAlias.Patronymic)
))
.WithAlias(() => resultAlias.DriverName)
.Select(Projections.Property(() => phoneAlias.Number)).WithAlias(() => resultAlias.DriverPhone)
.Select(Projections.Property(() => routeListAlias.Id)).WithAlias(() => resultAlias.RouteListId)
.Select(Projections.Property(() => orderAlias.BottlesReturn)).WithAlias(() => resultAlias.BottlesReturn)
.Select(Projections.Property(() => routeListItemAlias.DriverBottlesReturned)).WithAlias(() => resultAlias.ActualBottlesReturn)
.Select(Projections.SubQuery(bottlesDebtSubquery)).WithAlias(() => resultAlias.AddressBottlesDebt)
.Select(Projections.Property(() => orderAlias.DriverMobileAppComment)).WithAlias(() => resultAlias.DriverComment)
)
.OrderBy(() => orderAlias.DriverMobileAppCommentTime).Desc()
.TransformUsing(Transformers.AliasToBean <DriverMessageJournalNode>());
return(query);
}
private IQueryOver <ClientTask> GetClientTaskQuery(IUnitOfWork uow)
{
DeliveryPoint deliveryPointAlias = null;
BottlesMovementOperation bottlesMovementAlias = null;
ClientTask callTaskAlias = null;
BusinessTaskJournalNode <ClientTask> resultAlias = null;
Counterparty counterpartyAlias = null;
Employee employeeAlias = null;
Phone deliveryPointPhonesAlias = null;
Phone counterpartyPhonesAlias = null;
Domain.Orders.Order orderAlias = null;
var tasksQuery = UoW.Session.QueryOver(() => callTaskAlias)
.Left.JoinAlias(() => callTaskAlias.DeliveryPoint, () => deliveryPointAlias);
switch (FilterViewModel.DateType)
{
case TaskFilterDateType.CreationTime:
tasksQuery.Where(x => x.CreationDate >= FilterViewModel.StartDate.Date)
.And(x => x.CreationDate <= FilterViewModel.EndDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59));
break;
case TaskFilterDateType.CompleteTaskDate:
tasksQuery.Where(x => x.CompleteDate >= FilterViewModel.StartDate.Date)
.And(x => x.CompleteDate <= FilterViewModel.EndDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59));
break;
default:
tasksQuery.Where(x => x.EndActivePeriod >= FilterViewModel.StartDate.Date)
.And(x => x.EndActivePeriod <= FilterViewModel.EndDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59));
break;
}
if (FilterViewModel.Employee != null)
{
tasksQuery.Where(x => x.AssignedEmployee == FilterViewModel.Employee);
}
else if (FilterViewModel.ShowOnlyWithoutEmployee)
{
tasksQuery.Where(x => x.AssignedEmployee == null);
}
if (FilterViewModel.HideCompleted)
{
tasksQuery.Where(x => !x.IsTaskComplete);
}
if (FilterViewModel.DeliveryPointCategory != null)
{
tasksQuery.Where(() => deliveryPointAlias.Category == FilterViewModel.DeliveryPointCategory);
}
var bottleDebtByAddressQuery = UoW.Session.QueryOver(() => bottlesMovementAlias)
.JoinAlias(() => bottlesMovementAlias.Order, () => orderAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => bottlesMovementAlias.Counterparty.Id == counterpartyAlias.Id)
.And(() => bottlesMovementAlias.DeliveryPoint.Id == deliveryPointAlias.Id || orderAlias.SelfDelivery)
.Select(
Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int32, "( ?2 - ?1 )"),
NHibernateUtil.Int32, new IProjection[] {
Projections.Sum(() => bottlesMovementAlias.Returned),
Projections.Sum(() => bottlesMovementAlias.Delivered)
}
));
var bottleDebtByClientQuery = UoW.Session.QueryOver(() => bottlesMovementAlias)
.Where(() => bottlesMovementAlias.Counterparty.Id == counterpartyAlias.Id)
.Select(
Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int32, "( ?2 - ?1 )"),
NHibernateUtil.Int32, new IProjection[] {
Projections.Sum(() => bottlesMovementAlias.Returned),
Projections.Sum(() => bottlesMovementAlias.Delivered)
}
));
tasksQuery.Where(
GetSearchCriterion(
() => callTaskAlias.Id,
() => deliveryPointAlias.ShortAddress,
() => counterpartyAlias.Name,
() => callTaskAlias.TaskState
)
);
var tasks = tasksQuery
.Left.JoinAlias(() => deliveryPointAlias.Phones, () => deliveryPointPhonesAlias)
.Left.JoinAlias(() => counterpartyAlias.Phones, () => counterpartyPhonesAlias)
.Left.JoinAlias(() => callTaskAlias.Counterparty, () => counterpartyAlias)
.Left.JoinAlias(() => callTaskAlias.AssignedEmployee, () => employeeAlias)
.SelectList(list => list
.SelectGroup(() => callTaskAlias.Id)
.Select(() => deliveryPointAlias.ShortAddress).WithAlias(() => resultAlias.AddressName)
.Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.ClientName)
.Select(() => employeeAlias.Name).WithAlias(() => resultAlias.EmployeeName)
.Select(() => employeeAlias.LastName).WithAlias(() => resultAlias.EmployeeLastName)
.Select(() => employeeAlias.Patronymic).WithAlias(() => resultAlias.EmployeePatronymic)
.Select(() => callTaskAlias.EndActivePeriod).WithAlias(() => resultAlias.Deadline)
.Select(() => callTaskAlias.CreationDate).WithAlias(() => resultAlias.CreationDate)
.Select(() => callTaskAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => callTaskAlias.TaskState).WithAlias(() => resultAlias.TaskStatus)
.Select(() => callTaskAlias.ImportanceDegree).WithAlias(() => resultAlias.ImportanceDegree)
.Select(() => callTaskAlias.IsTaskComplete).WithAlias(() => resultAlias.IsTaskComplete)
.Select(() => callTaskAlias.TareReturn).WithAlias(() => resultAlias.TareReturn)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( CONCAT(?2 , ?1) SEPARATOR ?3 )"),
NHibernateUtil.String,
Projections.Property(() => deliveryPointPhonesAlias.DigitsNumber),
Projections.Constant("+7"),
Projections.Constant("\n"))
).WithAlias(() => resultAlias.DeliveryPointPhones)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( CONCAT(?2 , ?1) SEPARATOR ?3 )"),
NHibernateUtil.String,
Projections.Property(() => counterpartyPhonesAlias.DigitsNumber),
Projections.Constant("+7"),
Projections.Constant("\n"))
).WithAlias(() => resultAlias.CounterpartyPhones)
.SelectSubQuery((QueryOver <BottlesMovementOperation>)bottleDebtByAddressQuery).WithAlias(() => resultAlias.DebtByAddress)
.SelectSubQuery((QueryOver <BottlesMovementOperation>)bottleDebtByClientQuery).WithAlias(() => resultAlias.DebtByClient)
)
.TransformUsing(Transformers.AliasToBean <BusinessTaskJournalNode <ClientTask> >());
return(tasks);
}
public override bool IsValidForOrder(Order order)
{
if (!IsForZeroDebt)
{
return(true);
}
var forfeitId = new BaseParametersProvider().GetForfeitId();
BottlesRepository bottlesRepository = new BottlesRepository();
BottlesMovementOperation bottlesMovementAlias = null;
Order orderAlias = null;
//Долг клиента
var counterpartyDebtQuery = order.UoW.Session.QueryOver <BottlesMovementOperation>(() => bottlesMovementAlias)
.Where(() => bottlesMovementAlias.DeliveryPoint == null)
.Where(() => bottlesMovementAlias.Counterparty.Id == order.Client.Id)
.Select(
Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int32, "( ?2 - ?1 )"),
NHibernateUtil.Int32, new IProjection[] {
Projections.Sum(() => bottlesMovementAlias.Returned),
Projections.Sum(() => bottlesMovementAlias.Delivered)
}
)).SingleOrDefault <int>();
if (counterpartyDebtQuery != 0)
{
return(false);
}
//Долг по точкам доставки
foreach (var deliveryPoint in order.Client.DeliveryPoints)
{
if (bottlesRepository.GetBottlesAtDeliveryPoint(order.UoW, deliveryPoint) != 0)
{
return(false);
}
}
//Возврат бутылей и(ничего или возврат залога или неустойка)
var orders1 = order.UoW.Session.QueryOver(() => orderAlias)
.Where(() => orderAlias.Client.Id == order.Client.Id)
.Where(() => orderAlias.OrderStatus == OrderStatus.Closed)
.Where(() => orderAlias.BottlesReturn != 0)
.List <Order>();
if (orders1.Count == 0)
{
return(false);
}
var orders2 = new List <Order>();
foreach (var o in orders1)
{
if (o.OrderDepositItems != null && o.OrderItems == null)
{
orders2.Add(o);
}
if (o.OrderItems.All(i => i.Nomenclature.Id == forfeitId))
{
orders2.Add(o);
}
if (o.OrderItems == null)
{
orders2.Add(o);
}
}
if (orders2.Count == 0)
{
return(false);
}
//Ввод остатков
foreach (var o in orders2)
{
if (o.DeliveryPoint == null)
{
continue;
}
if (o.DeliveryPoint.HaveResidue.HasValue)
{
if (!o.DeliveryPoint.HaveResidue.Value)
{
return(false);
}
}
}
return(true);
}
