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); }