public IList <AdditionalLoadingDocumentItem> CreateAdditionalLoadingItems(IUnitOfWork uow, decimal availableWeight, decimal availableVolume) { var distributions = uow.GetAll <AdditionalLoadingNomenclatureDistribution>().ToList(); IList <AdditionalLoadingDocumentItem> items = new List <AdditionalLoadingDocumentItem>(); //Сначала расчитывам по грузоподъёмности foreach (var distribution in distributions) { if (distribution.Nomenclature.Weight <= 0 || distribution.Nomenclature.Volume <= 0) { continue; } var item = new AdditionalLoadingDocumentItem { Nomenclature = distribution.Nomenclature, //Доступный вес в МЛ * процент распределения номенклатуры / вес номенклатуры Amount = Math.Floor(availableWeight * distribution.Percent / 100 / distribution.Nomenclature.Weight) }; items.Add(item); } RemoveZeroAmountItemsIfNeededByWeight(items, availableWeight); AddFlyers(items, uow); var hasVolumeExcess = availableVolume < items.Sum(x => x.Amount * x.Nomenclature.Volume); if (!hasVolumeExcess) { _activeFlyers = null; _flyersInStock = null; return(items); } //Если расчитали по грузоподъёмности, но не хватило места по объёму, то расчитываем по объёму items.Clear(); foreach (var distribution in distributions) { if (distribution.Nomenclature.Weight <= 0 || distribution.Nomenclature.Volume <= 0) { continue; } var item = new AdditionalLoadingDocumentItem { Nomenclature = distribution.Nomenclature, Amount = Math.Floor(availableVolume * distribution.Percent / 100 / distribution.Nomenclature.Volume) }; items.Add(item); } RemoveZeroAmountItemsIfNeededByVolume(items, availableVolume); AddFlyers(items, uow); _activeFlyers = null; _flyersInStock = null; return(items); }
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 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); }