private IList <FastDeliveryAdditionalLoadingReportRow> GenerateReportRows()
{
if (!IsHasDates)
{
return(new List <FastDeliveryAdditionalLoadingReportRow>());
}
RouteList routeListAlias = null;
RouteListItem routeListItemAlias = null;
Order orderAlias = null;
Nomenclature nomenclatureAlias = null;
FastDeliveryAdditionalLoadingReportRow resultAlias = null;
AdditionalLoadingDocumentItem additionalLoadingDocumentItemAlias = null;
AdditionalLoadingDocument additionalLoadingDocumentAlias = null;
var itemsQuery = UoW.Session.QueryOver(() => additionalLoadingDocumentItemAlias)
.JoinAlias(() => additionalLoadingDocumentItemAlias.AdditionalLoadingDocument, () => additionalLoadingDocumentAlias)
.JoinEntityAlias(() => routeListAlias, () => routeListAlias.AdditionalLoadingDocument.Id == additionalLoadingDocumentAlias.Id)
.JoinAlias(() => additionalLoadingDocumentItemAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => routeListAlias.Date >= CreateDateFrom.Value.Date &&
routeListAlias.Date <= CreateDateTo.Value.Date.Add(new TimeSpan(0, 23, 59, 59)));
var ownOrdersAmountSubquery = QueryOver.Of(() => routeListItemAlias)
.JoinAlias(() => routeListItemAlias.Order, () => orderAlias)
.WhereRestrictionOn(() => routeListItemAlias.Status).Not.IsIn(new RouteListItemStatus[]
{
RouteListItemStatus.Canceled,
RouteListItemStatus.Overdue,
RouteListItemStatus.Transfered
})
.And(() => routeListItemAlias.RouteList.Id == routeListAlias.Id)
.And(() => !orderAlias.IsFastDelivery)
.Select(Projections.Count(Projections.Id()));
return(itemsQuery
.SelectList(list => list
.Select(() => routeListAlias.Date).WithAlias(() => resultAlias.RouteListDate)
.Select(() => routeListAlias.Id).WithAlias(() => resultAlias.RouteListId)
.SelectSubQuery(ownOrdersAmountSubquery).WithAlias(() => resultAlias.OwnOrdersCount)
.Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.AdditionaLoadingNomenclature)
.Select(() => additionalLoadingDocumentItemAlias.Amount).WithAlias(() => resultAlias.AdditionaLoadingAmount)
).OrderBy(() => routeListAlias.Date).Desc
.ThenBy(() => routeListAlias.Id).Desc
.TransformUsing(Transformers.AliasToBean <FastDeliveryAdditionalLoadingReportRow>())
.List <FastDeliveryAdditionalLoadingReportRow>());
}
public AdditionalLoadingDocument CreateAdditionLoadingDocument(IUnitOfWork uow, RouteList routeList)
{
if (uow == null)
{
throw new ArgumentNullException(nameof(uow));
}
if (routeList == null)
{
throw new ArgumentNullException(nameof(routeList));
}
if (routeList.AdditionalLoadingDocument != null)
{
throw new InvalidOperationException("Может быть создан только один документ запаса");
}
var availableWeight = routeList.Car.CarModel.MaxWeight - routeList.GetTotalWeight();
var availableVolume = routeList.Car.CarModel.MaxVolume - routeList.GetTotalVolume();
if (availableWeight < 0 || availableVolume < 0)
{
return(null);
}
var document = new AdditionalLoadingDocument
{
Author = _employeeRepository.GetEmployeeForCurrentUser(uow),
CreationDate = DateTime.Now,
};
foreach (var item in CreateAdditionalLoadingItems(uow, availableWeight, availableVolume))
{
item.AdditionalLoadingDocument = document;
document.Items.Add(item);
}
return(document.Items.Any() ? document : null);
}
public override void UpdateNodes()
{
WorkingDriverVMNode resultAlias = null;
Employee driverAlias = null;
RouteList routeListAlias = null;
Car carAlias = null;
CarVersion carVersionAlias = null;
Domain.Orders.Order orderAlias = null;
OrderItem ordItemsAlias = null;
Nomenclature nomenclatureAlias = null;
var completedSubquery = QueryOver.Of <RouteListItem>()
.Where(i => i.RouteList.Id == routeListAlias.Id)
.Where(i => i.Status != RouteListItemStatus.EnRoute)
.Select(Projections.RowCount());
var addressesSubquery = QueryOver.Of <RouteListItem>()
.Where(i => i.RouteList.Id == routeListAlias.Id)
.Select(Projections.RowCount());
var uncompletedBottlesSubquery = QueryOver.Of <RouteListItem>() // Запрашивает количество ещё не доставленных бутылей.
.Where(i => i.RouteList.Id == routeListAlias.Id)
.Where(i => i.Status == RouteListItemStatus.EnRoute)
.JoinAlias(rli => rli.Order, () => orderAlias)
.JoinAlias(() => orderAlias.OrderItems, () => ordItemsAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.JoinAlias(() => ordItemsAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => nomenclatureAlias.Category == NomenclatureCategory.water && nomenclatureAlias.TareVolume == TareVolume.Vol19L)
.Select(Projections.Sum(() => ordItemsAlias.Count));
var trackSubquery = QueryOver.Of <Track>()
.Where(x => x.RouteList.Id == routeListAlias.Id)
.Select(x => x.Id);
var isCompanyHavingProjection = Projections.Conditional(
Restrictions.Eq(Projections.Property(() => carVersionAlias.CarOwnType), CarOwnType.Company),
Projections.Constant(true),
Projections.Constant(false));
#region Water19LReserve
RouteListItem routeListItemAlias = null;
RouteListItem transferedToAlias = null;
OrderItem orderItemAlias = null;
AdditionalLoadingDocumentItem additionalLoadingDocumentItemAlias = null;
AdditionalLoadingDocument additionalLoadingDocumentAlias = null;
var ownOrdersSubquery = QueryOver.Of <RouteListItem>(() => routeListItemAlias)
.JoinAlias(() => routeListItemAlias.Order, () => orderAlias)
.JoinEntityAlias(() => orderItemAlias, () => orderItemAlias.Order.Id == orderAlias.Id)
.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => !orderAlias.IsFastDelivery && !routeListItemAlias.WasTransfered)
.And(() => nomenclatureAlias.Category == NomenclatureCategory.water &&
nomenclatureAlias.TareVolume == TareVolume.Vol19L)
.And(() => routeListItemAlias.RouteList.Id == routeListAlias.Id)
.Select(Projections.Sum(() => orderItemAlias.Count));
var additionalBalanceSubquery = QueryOver.Of <AdditionalLoadingDocumentItem>(() => additionalLoadingDocumentItemAlias)
.JoinAlias(() => additionalLoadingDocumentItemAlias.Nomenclature, () => nomenclatureAlias)
.JoinAlias(() => additionalLoadingDocumentItemAlias.AdditionalLoadingDocument, () => additionalLoadingDocumentAlias)
.Where(() => nomenclatureAlias.Category == NomenclatureCategory.water &&
nomenclatureAlias.TareVolume == TareVolume.Vol19L)
.And(() => routeListAlias.AdditionalLoadingDocument.Id == additionalLoadingDocumentAlias.Id)
.Select(Projections.Sum(() => additionalLoadingDocumentItemAlias.Amount));
var deliveredOrdersSubquery = QueryOver.Of <RouteListItem>(() => routeListItemAlias)
.JoinAlias(() => routeListItemAlias.Order, () => orderAlias)
.JoinEntityAlias(() => orderItemAlias, () => orderItemAlias.Order.Id == orderAlias.Id)
.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Left.JoinAlias(() => routeListItemAlias.TransferedTo, () => transferedToAlias)
.Where(() =>
//не отменённые и не недовозы
routeListItemAlias.Status != RouteListItemStatus.Canceled && routeListItemAlias.Status != RouteListItemStatus.Overdue
// и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки)
&& (!routeListItemAlias.WasTransfered || routeListItemAlias.NeedToReload || orderAlias.IsFastDelivery)
// и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся)
&& (routeListItemAlias.Status != RouteListItemStatus.Transfered || (!transferedToAlias.NeedToReload && !orderAlias.IsFastDelivery)))
.And(() => nomenclatureAlias.Category == NomenclatureCategory.water &&
nomenclatureAlias.TareVolume == TareVolume.Vol19L)
.And(() => routeListItemAlias.RouteList.Id == routeListAlias.Id)
.Select(Projections.Sum(() => orderItemAlias.Count));
var water19LReserveProjection =
Projections.Conditional(Restrictions.Eq(Projections.Property(() => routeListAlias.AdditionalLoadingDocument), null),
Projections.Constant(0m),
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "IFNULL(?1, 0) + IFNULL(?2, 0) - IFNULL(?3, 0)"), //
NHibernateUtil.Decimal,
Projections.SubQuery(ownOrdersSubquery),
Projections.SubQuery(additionalBalanceSubquery),
Projections.SubQuery(deliveredOrdersSubquery)));
#endregion
var query = UoW.Session.QueryOver <RouteList>(() => routeListAlias);
if (Filter.IsFastDeliveryOnly)
{
query.Where(() => routeListAlias.AdditionalLoadingDocument != null);
}
var result = query
.JoinAlias(rl => rl.Driver, () => driverAlias)
.JoinAlias(rl => rl.Car, () => carAlias)
.JoinEntityAlias(() => carVersionAlias,
() => carVersionAlias.Car.Id == carAlias.Id &&
carVersionAlias.StartDate <= routeListAlias.Date &&
(carVersionAlias.EndDate == null || carVersionAlias.EndDate >= routeListAlias.Date))
.Where(rl => rl.Status == RouteListStatus.EnRoute)
.Where(rl => rl.Driver != null)
.Where(rl => rl.Car != null)
.SelectList(list => list
.Select(() => driverAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => driverAlias.Name).WithAlias(() => resultAlias.Name)
.Select(() => driverAlias.LastName).WithAlias(() => resultAlias.LastName)
.Select(() => driverAlias.Patronymic).WithAlias(() => resultAlias.Patronymic)
.Select(() => carAlias.RegistrationNumber).WithAlias(() => resultAlias.CarNumber)
.Select(isCompanyHavingProjection).WithAlias(() => resultAlias.IsVodovozAuto)
.Select(() => routeListAlias.Id).WithAlias(() => resultAlias.RouteListNumber)
.SelectSubQuery(addressesSubquery).WithAlias(() => resultAlias.AddressesAll)
.SelectSubQuery(completedSubquery).WithAlias(() => resultAlias.AddressesCompleted)
.SelectSubQuery(trackSubquery).WithAlias(() => resultAlias.TrackId)
.SelectSubQuery(uncompletedBottlesSubquery).WithAlias(() => resultAlias.BottlesLeft)
.Select(water19LReserveProjection).WithAlias(() => resultAlias.Water19LReserve)
)
.TransformUsing(Transformers.AliasToBean <WorkingDriverVMNode>())
.List <WorkingDriverVMNode>();
var summaryResult = new List <WorkingDriverVMNode>();
int rowNum = 0;
foreach (var driver in result.GroupBy(x => x.Id).OrderBy(x => x.First().ShortName))
{
var savedRow = driver.First();
savedRow.RouteListsText = String.Join("; ", driver.Select(x => x.TrackId != null ? String.Format("<span foreground=\"green\"><b>{0}</b></span>", x.RouteListNumber) : x.RouteListNumber.ToString()));
savedRow.RouteListsIds = driver.ToDictionary(x => x.RouteListNumber, x => x.TrackId);
savedRow.AddressesAll = driver.Sum(x => x.AddressesAll);
savedRow.AddressesCompleted = driver.Sum(x => x.AddressesCompleted);
savedRow.Water19LReserve = driver.Sum(x => x.Water19LReserve);
savedRow.RowNumber = ++rowNum;
summaryResult.Add(savedRow);
}
SetItemsSource(summaryResult);
}
