public override void UpdateNodes()
{
DriverRouteListAddressVMNode resultAlias = null;
Employee driverAlias = null;
RouteList routeListAlias = null;
RouteListItem routeListItemAlias = null;
Order orderAlias = null;
var result = UoW.Session.QueryOver <RouteListItem>(() => routeListItemAlias)
.JoinAlias(rli => rli.RouteList, () => routeListAlias)
.JoinAlias(rli => rli.RouteList.Driver, () => driverAlias)
.JoinAlias(rli => rli.Order, () => orderAlias)
.Where(() => routeListAlias.Status == RouteListStatus.EnRoute)
.Where(() => routeListAlias.Driver.Id == _driverId)
.SelectList(list => list
.Select(() => routeListItemAlias.Id).WithAlias(() => resultAlias.Id)
.Select(Projections.Entity(() => orderAlias)).WithAlias(() => resultAlias.Order)
.Select(Projections.Entity(() => routeListItemAlias)).WithAlias(() => resultAlias.RouteListItem)
.Select(() => routeListAlias.Id).WithAlias(() => resultAlias.RouteListNumber)
.Select(() => routeListItemAlias.Status).WithAlias(() => resultAlias.Status)
.Select(() => orderAlias.DeliverySchedule).WithAlias(() => resultAlias.Time)
.Select(() => orderAlias.DeliveryPoint).WithAlias(() => resultAlias.DeliveryPoint))
.TransformUsing(Transformers.AliasToBean <DriverRouteListAddressVMNode>())
.List <DriverRouteListAddressVMNode>();
SetItemsSource(result);
}
private void UpdateNodes()
{
SelectedOrdersDocumentVMNode resultAlias = null;
Vodovoz.Domain.Orders.Order orderAlias = null;
Counterparty counterpartyAlias = null;
OrderDocument orderDocumentAlias = null;
DeliveryPoint deliveryPointAlias = null;
var query = UoW.Session.QueryOver <OrderDocument>(() => orderDocumentAlias);
Counterparty client = entryreferencevm1.GetSubject <Counterparty>();
if (client != null)
{
query.Where(() => counterpartyAlias.Id == client.Id);
}
int orderId = default(int);
if (int.TryParse(yvalidatedentry1.Text, out orderId))
{
//query.Where(() => orderAlias.Id == orderId);
query.WhereRestrictionOn(() => orderAlias.Id).IsLike(orderId);
}
Documents = query
.JoinAlias(() => orderDocumentAlias.Order, () => orderAlias)
.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
.Where(() =>
orderDocumentAlias.GetType() == typeof(BillDocument) ||
orderDocumentAlias.GetType() == typeof(DoneWorkDocument) ||
orderDocumentAlias.GetType() == typeof(EquipmentTransferDocument) ||
orderDocumentAlias.GetType() == typeof(InvoiceBarterDocument) ||
orderDocumentAlias.GetType() == typeof(InvoiceDocument) ||
orderDocumentAlias.GetType() == typeof(InvoiceContractDoc) ||
orderDocumentAlias.GetType() == typeof(UPDDocument) ||
orderDocumentAlias.GetType() == typeof(DriverTicketDocument) ||
orderDocumentAlias.GetType() == typeof(Torg12Document) ||
orderDocumentAlias.GetType() == typeof(ShetFacturaDocument)
)
.SelectList(list => list
.Select(() => orderAlias.Id).WithAlias(() => resultAlias.OrderId)
.Select(() => orderAlias.DeliveryDate).WithAlias(() => resultAlias.OrderDate)
.Select(() => counterpartyAlias.Id).WithAlias(() => resultAlias.ClientId)
.Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.ClientName)
.Select(() => orderDocumentAlias.Id).WithAlias(() => resultAlias.DocumentId)
.Select(() => orderDocumentAlias.GetType()).WithAlias(() => resultAlias.DocumentTypeString)
.Select(() => deliveryPointAlias.CompiledAddress).WithAlias(() => resultAlias.AddressString)
).OrderBy(() => orderAlias.DeliveryDate).Desc
.TransformUsing(Transformers.AliasToBean <SelectedOrdersDocumentVMNode>())
.List <SelectedOrdersDocumentVMNode>().ToList();
datatreeviewOrderDocuments.SetItemsSource(Documents);
}
public IList <IncomingCallsAnalysisReportNode> GetLastOrderIdAndDeliveryDateByPhone(
IUnitOfWork uow, IEnumerable <string> incomingCallsNumbers)
{
Phone phoneAlias = null;
Counterparty counterpartyAlias = null;
DeliveryPoint deliveryPointAlias = null;
Order orderAlias = null;
IncomingCallsAnalysisReportNode resultAlias = null;
var query = uow.Session.QueryOver(() => phoneAlias)
.Left.JoinAlias(() => phoneAlias.Counterparty, () => counterpartyAlias)
.Left.JoinAlias(() => phoneAlias.DeliveryPoint, () => deliveryPointAlias)
.Where(() => counterpartyAlias.Id != null || deliveryPointAlias.Id != null)
.WhereRestrictionOn(() => phoneAlias.DigitsNumber).IsInG(incomingCallsNumbers);
var lastOrderIdForCounterparty = QueryOver.Of(() => orderAlias)
.Where(() => orderAlias.Client.Id == counterpartyAlias.Id)
.Select(o => o.Id)
.OrderBy(o => o.DeliveryDate).Desc
.Take(1);
var lastOrderIdForDeliveryPoint = QueryOver.Of(() => orderAlias)
.Where(() => orderAlias.DeliveryPoint.Id == deliveryPointAlias.Id)
.Select(o => o.Id)
.OrderBy(o => o.DeliveryDate).Desc
.Take(1);
var lastOrderDeliveryDateForCounterparty = QueryOver.Of(() => orderAlias)
.Where(() => orderAlias.Client.Id == counterpartyAlias.Id)
.Select(o => o.DeliveryDate)
.OrderBy(o => o.DeliveryDate).Desc
.Take(1);
var lastOrderDeliveryDateForDeliveryPoint = QueryOver.Of(() => orderAlias)
.Where(() => orderAlias.DeliveryPoint.Id == deliveryPointAlias.Id)
.Select(o => o.DeliveryDate)
.OrderBy(o => o.DeliveryDate).Desc
.Take(1);
return(query.SelectList(list => list
.Select(() => phoneAlias.DigitsNumber).WithAlias(() => resultAlias.PhoneDigitsNumber)
.Select(() => counterpartyAlias.Id).WithAlias(() => resultAlias.CounterpartyId)
.Select(() => deliveryPointAlias.Id).WithAlias(() => resultAlias.DeliveryPointId)
.Select(Projections.Conditional(
Restrictions.IsNull(Projections.Property(() => counterpartyAlias.Id)),
Projections.SubQuery(lastOrderIdForDeliveryPoint),
Projections.SubQuery(lastOrderIdForCounterparty))).WithAlias(() => resultAlias.LastOrderId)
.Select(Projections.Conditional(
Restrictions.IsNull(Projections.Property(() => counterpartyAlias.Id)),
Projections.SubQuery(lastOrderDeliveryDateForDeliveryPoint),
Projections.SubQuery(lastOrderDeliveryDateForCounterparty))).WithAlias(() => resultAlias.LastOrderDeliveryDate))
.TransformUsing(Transformers.AliasToBean <IncomingCallsAnalysisReportNode>())
.List <IncomingCallsAnalysisReportNode>());
}
public IList <ServiceClaim> GetServiceClaimForOrder(IUnitOfWork uow, Vodovoz.Domain.Orders.Order order)
{
ServiceClaim serviceClaimAlias = null;
Vodovoz.Domain.Orders.Order initialOrderAlias = null, finalOrderAlias = null;
var queryOver = uow.Session.QueryOver(() => serviceClaimAlias)
.JoinAlias(s => s.InitialOrder, () => initialOrderAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.JoinAlias(s => s.FinalOrder, () => finalOrderAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(s => initialOrderAlias.Id == order.Id || finalOrderAlias.Id == order.Id);
return(queryOver.List <ServiceClaim> ());
}
public QueryOver <ServiceClaim> GetDoneClaimsForClient(Vodovoz.Domain.Orders.Order order)
{
ServiceClaim serviceClaimAlias = null;
Counterparty counterpartyAlias = null;
var queryOver = QueryOver.Of(() => serviceClaimAlias)
.JoinAlias(s => s.Counterparty, () => counterpartyAlias)
.Where(s => counterpartyAlias.Id == order.Client.Id &&
s.Status == ServiceClaimStatus.Ready &&
s.FinalOrder == null &&
s.Payment == order.PaymentType);
return(queryOver);
}
public List <BillDocument> GetAllUnsentDocuments(IUnitOfWork uow, DateTime date)
{
VodOrder orderAlias = null;
return(uow.Session.QueryOver <BillDocument>()
.Left.JoinAlias(bdoc => bdoc.Order, () => orderAlias)
.Where(() => orderAlias.CreateDate >= date)
.WithSubquery.WhereNotExists(
QueryOver.Of <StoredEmail>()
.Where(se => se.Order.Id == orderAlias.Id)
.Select(x => x.Id))
.List().Take(1)
.ToList());
}
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 string GetEmployeePushTokenByOrderId(IUnitOfWork uow, int orderId)
{
Vodovoz.Domain.Orders.Order vodovozOrder = null;
RouteListItem routeListAddress = null;
RouteList routeList = null;
Employee employee = null;
return(uow.Session.QueryOver <RouteListItem>(() => routeListAddress)
.Inner.JoinAlias(() => routeListAddress.RouteList, () => routeList)
.Inner.JoinAlias(() => routeListAddress.Order, () => vodovozOrder)
.Inner.JoinAlias(() => routeList.Driver, () => employee)
.Where(Restrictions.Eq(Projections.Property(() => vodovozOrder.Id), orderId))
.And(Restrictions.Not(Restrictions.Eq(Projections.Property(() => routeListAddress.Status), RouteListItemStatus.Transfered)))
.And(Restrictions.IsNull(Projections.Property(() => routeListAddress.TransferedTo)))
.Select(Projections.Property(() => employee.AndroidToken))
.SingleOrDefault <string>());
}
private PointMarker FillAddressMarker(Order order, PointMarkerType type, PointMarkerShape shape, GMapOverlay overlay, RouteList route)
{
var addressMarker = new PointMarker(new PointLatLng((double)order.DeliveryPoint.Latitude, (double)order.DeliveryPoint.Longitude), type, shape)
{
Tag = order
};
string ttText = order.DeliveryPoint.ShortAddress;
if (order.Total19LBottlesToDeliver > 0)
{
ttText += string.Format("\nБутылей 19л: {0}", order.Total19LBottlesToDeliver);
}
if (order.Total6LBottlesToDeliver > 0)
{
ttText += string.Format("\nБутылей 6л: {0}", order.Total6LBottlesToDeliver);
}
if (order.Total600mlBottlesToDeliver > 0)
{
ttText += string.Format("\nБутылей 0,6л: {0}", order.Total600mlBottlesToDeliver);
}
ttText += string.Format($"\nЗабор бутылей: {order.BottlesReturn}");
ttText += string.Format("\nВремя доставки: {0}\nРайон: {1}",
order.DeliverySchedule?.Name ?? "Не назначено",
ViewModel.LogisticanDistricts?.FirstOrDefault(x => x.DistrictBorder.Contains(order.DeliveryPoint.NetTopologyPoint))?.DistrictName);
addressMarker.ToolTipText = ttText;
var identicalPoint = overlay.Markers.Count(g => g.Position.Lat == (double)order.DeliveryPoint.Latitude && g.Position.Lng == (double)order.DeliveryPoint.Longitude);
var pointShift = 5;
if (identicalPoint >= 1)
{
addressMarker.Offset = new System.Drawing.Point(identicalPoint * pointShift, identicalPoint * pointShift);
}
if (route != null)
{
addressMarker.ToolTipText += string.Format(" Везёт: {0}", route.Driver.ShortName);
}
return(addressMarker);
}
private bool NeedPremiumRaskatGAZelleInRouteListDate(IUnitOfWork uow)
{
if (routeList.RecalculatedDistance >= premiumRaskatGAZelleParametersProvider.MinRecalculatedDistanceForPremiumRaskatGAZelle &&
routeList.Car.IsRaskat &&
routeList.Car.TypeOfUse == CarTypeOfUse.DriverCar &&
routeList.Car.RaskatType == RaskatType.RaskatGazelle)
{
RouteListItem routeListAdressesAlias = null;
Order orderAlias = null;
DeliveryPoint deliveryPointAlias = null;
District districtAlias = null;
PremiumItem premiumItemAlias = null;
PremiumRaskatGAZelle premiumRaskatGAZelleAlias = null;
// Ищем премию
var premiumRaskatGAZelleQuery = uow.Session.QueryOver(() => premiumItemAlias)
.JoinAlias(() => premiumItemAlias.Premium, () => premiumRaskatGAZelleAlias)
.Where(() =>
( // Если МЛ переоткрыли в другой день и повторно его закрывают
(premiumRaskatGAZelleAlias.RouteList.Id == routeList.Id) ||
// Если на дату закрытия у водителя уже есть премии
(premiumRaskatGAZelleAlias.Date == DateTime.Today && premiumItemAlias.Employee == routeList.Driver)
) &&
premiumRaskatGAZelleAlias.GetType() == typeof(PremiumRaskatGAZelle)
)
.Take(1).SingleOrDefault();
// Ищем заказ в пригороде
var wageDistrictQuery = uow.Session.QueryOver(() => routeListAdressesAlias)
.JoinAlias(() => routeListAdressesAlias.Order, () => orderAlias)
.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
.JoinAlias(() => deliveryPointAlias.District, () => districtAlias)
.Where(() => districtAlias.WageDistrict.Id == wageParametersProvider.GetSuburbWageDistrictId &&
routeListAdressesAlias.RouteList.Id == routeList.Id)
.Take(1).SingleOrDefault();
return(premiumRaskatGAZelleQuery == null && wageDistrictQuery != null);
}
else
{
return(false);
}
}
private void FillTypeAndShapeMarker(Order order, RouteList route, IEnumerable <int> orderRlsIds, out PointMarkerShape shape, out PointMarkerType type)
{
shape = ViewModel.GetMarkerShapeFromBottleQuantity(order.Total19LBottlesToDeliver);
type = PointMarkerType.black;
if (!orderRlsIds.Any())
{
if ((order.DeliverySchedule.To - order.DeliverySchedule.From).TotalHours <= 1)
{
type = PointMarkerType.black_and_red;
}
else
{
double from = order.DeliverySchedule.From.TotalMinutes;
double to = order.DeliverySchedule.To.TotalMinutes;
if (from >= 1080 && to <= 1439) //>= 18:00, <= 23:59
{
type = PointMarkerType.grey_stripes;
}
else if (from >= 0)
{
if (to <= 720) //<= 12:00
{
type = PointMarkerType.red_stripes;
}
else if (to <= 900) //<=15:00
{
type = PointMarkerType.yellow_stripes;
}
else if (to <= 1080) //<= 18:00
{
type = PointMarkerType.green_stripes;
}
}
}
}
if (route != null)
{
type = ViewModel.GetAddressMarker(ViewModel.RoutesOnDay.IndexOf(route));
}
}
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);
}
public OrderDto convertToAPIOrder(Vodovoz.Domain.Orders.Order vodovozOrder, SmsPaymentStatus?smsPaymentStatus)
{
var pairOfSplitedLists = SplitDeliveryItems(vodovozOrder.OrderEquipments);
var apiOrder = new OrderDto()
{
OrderId = vodovozOrder.Id,
SmsPaymentStatus = _smsPaymentConverter.convertToAPIPaymentStatus(smsPaymentStatus),
DeliveryTime = vodovozOrder.TimeDelivered?.ToString("HH:mm:ss"),
FullBottleCount = vodovozOrder.Total19LBottlesToDeliver,
EmptyBottlesToReturn = vodovozOrder.BottlesReturn ?? 0,
Counterparty = vodovozOrder.Client.FullName,
PhoneNumbers = vodovozOrder.DeliveryPoint.Phones.Concat(vodovozOrder.Client.Phones).Select(x => "+7" + x.DigitsNumber),
PaymentType = _paymentTypeConverter.ConvertToAPIPaymentType(vodovozOrder.PaymentType, vodovozOrder.PaymentByCardFrom),
Address = _deliveryPointConverter.ExtractAPIAddressFromDeliveryPoint(vodovozOrder.DeliveryPoint),
OrderComment = vodovozOrder.Comment,
OrderSum = vodovozOrder.ActualTotalSum,
OrderSaleItems = PrepareSaleItemsList(vodovozOrder.OrderItems),
OrderDeliveryItems = pairOfSplitedLists.orderDeliveryItems,
OrderReceptionItems = pairOfSplitedLists.orderReceptionItems
};
return(apiOrder);
}
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 <Complaint> GetComplaintQuery(IUnitOfWork uow)
{
ComplaintJournalNode resultAlias = null;
Complaint complaintAlias = null;
Employee authorAlias = null;
Counterparty counterpartyAlias = null;
DeliveryPoint deliveryPointAlias = null;
ComplaintGuiltyItem complaintGuiltyItemAlias = null;
Employee guiltyEmployeeAlias = null;
Subdivision guiltySubdivisionAlias = null;
Fine fineAlias = null;
Order orderAlias = null;
ComplaintDiscussion discussionAlias = null;
Subdivision subdivisionAlias = null;
ComplaintKind complaintKindAlias = null;
Subdivision superspecialAlias = null;
var authorProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GET_PERSON_NAME_WITH_INITIALS(?1, ?2, ?3)"),
NHibernateUtil.String,
Projections.Property(() => authorAlias.LastName),
Projections.Property(() => authorAlias.Name),
Projections.Property(() => authorAlias.Patronymic)
);
var workInSubdivisionsSubQuery = QueryOver.Of <Subdivision>(() => subdivisionAlias)
.Where(() => subdivisionAlias.Id == discussionAlias.Subdivision.Id)
.Where(() => discussionAlias.Status == ComplaintStatuses.InProcess)
.Select(Projections.Conditional(
Restrictions.IsNotNull(Projections.Property(() => subdivisionAlias.ShortName)),
Projections.Property(() => subdivisionAlias.ShortName),
Projections.Constant("?")
)
);
var subdivisionsSubqueryProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.SubQuery(workInSubdivisionsSubQuery),
Projections.Constant(", "));
string okkSubdivision = uow.GetById <Subdivision>(subdivisionService.GetOkkId()).ShortName ?? "?";
var workInSubdivisionsProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "CONCAT_WS(',', ?1, IF(?2 = 'Checking',?3, ''))"),
NHibernateUtil.String,
subdivisionsSubqueryProjection,
Projections.Property(() => complaintAlias.Status),
Projections.Constant(okkSubdivision)
);
var plannedCompletionDateProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT DATE_FORMAT(?1, \"%d.%m.%Y\") SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => discussionAlias.PlannedCompletionDate),
Projections.Constant("\n"));
var lastPlannedCompletionDateProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.DateTime, "MAX(DISTINCT ?1)"),
NHibernateUtil.DateTime,
Projections.Property(() => discussionAlias.PlannedCompletionDate));
var counterpartyWithAddressProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "CONCAT_WS('\n', ?1, COMPILE_ADDRESS(?2))"),
NHibernateUtil.String,
Projections.Property(() => counterpartyAlias.Name),
Projections.Property(() => deliveryPointAlias.Id));
var guiltyEmployeeProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GET_PERSON_NAME_WITH_INITIALS(?1, ?2, ?3)"),
NHibernateUtil.String,
Projections.Property(() => guiltyEmployeeAlias.LastName),
Projections.Property(() => guiltyEmployeeAlias.Name),
Projections.Property(() => guiltyEmployeeAlias.Patronymic)
);
var guiltiesProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT " +
"CASE ?1 " +
$"WHEN '{nameof(ComplaintGuiltyTypes.Client)}' THEN 'Клиент' " +
$"WHEN '{nameof(ComplaintGuiltyTypes.None)}' THEN 'Нет' " +
$"WHEN '{nameof(ComplaintGuiltyTypes.Employee)}' THEN CONCAT('(',?5,')', ?2)" +
$"WHEN '{nameof(ComplaintGuiltyTypes.Subdivision)}' THEN ?3 " +
"ELSE '' " +
"END" +
" SEPARATOR ?4)"),
NHibernateUtil.String,
Projections.Property(() => complaintGuiltyItemAlias.GuiltyType),
guiltyEmployeeProjection,
Projections.Property(() => guiltySubdivisionAlias.ShortName),
Projections.Constant("\n"),
Projections.Property(() => superspecialAlias.ShortName));
var finesProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT CONCAT(ROUND(?1, 2), ' р.') SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => fineAlias.TotalMoney),
Projections.Constant("\n"));
var query = uow.Session.QueryOver(() => complaintAlias)
.Left.JoinAlias(() => complaintAlias.CreatedBy, () => authorAlias)
.Left.JoinAlias(() => complaintAlias.Counterparty, () => counterpartyAlias)
.Left.JoinAlias(() => complaintAlias.Order, () => orderAlias)
.Left.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
.Left.JoinAlias(() => complaintAlias.Guilties, () => complaintGuiltyItemAlias)
.Left.JoinAlias(() => complaintAlias.ComplaintKind, () => complaintKindAlias)
.Left.JoinAlias(() => complaintAlias.Fines, () => fineAlias)
.Left.JoinAlias(() => complaintAlias.ComplaintDiscussions, () => discussionAlias)
.Left.JoinAlias(() => discussionAlias.Subdivision, () => subdivisionAlias)
.Left.JoinAlias(() => complaintGuiltyItemAlias.Employee, () => guiltyEmployeeAlias)
.Left.JoinAlias(() => guiltyEmployeeAlias.Subdivision, () => superspecialAlias)
.Left.JoinAlias(() => complaintGuiltyItemAlias.Subdivision, () => guiltySubdivisionAlias);
#region Filter
if (FilterViewModel != null)
{
if (FilterViewModel.IsForRetail != null)
{
query.Where(() => counterpartyAlias.IsForRetail == FilterViewModel.IsForRetail);
}
FilterViewModel.EndDate = FilterViewModel.EndDate.Date.AddHours(23).AddMinutes(59);
if (FilterViewModel.StartDate.HasValue)
{
FilterViewModel.StartDate = FilterViewModel.StartDate.Value.Date;
}
QueryOver <ComplaintDiscussion, ComplaintDiscussion> dicussionQuery = null;
if (FilterViewModel.Subdivision != null)
{
dicussionQuery = QueryOver.Of(() => discussionAlias)
.Select(Projections.Property <ComplaintDiscussion>(p => p.Id))
.Where(() => discussionAlias.Subdivision.Id == FilterViewModel.Subdivision.Id)
.And(() => discussionAlias.Complaint.Id == complaintAlias.Id);
}
if (FilterViewModel.StartDate.HasValue)
{
switch (FilterViewModel.FilterDateType)
{
case DateFilterType.PlannedCompletionDate:
if (dicussionQuery == null)
{
query = query.Where(() => complaintAlias.PlannedCompletionDate <= FilterViewModel.EndDate)
.And(() => FilterViewModel.StartDate == null || complaintAlias.PlannedCompletionDate >= FilterViewModel.StartDate.Value);
}
else
{
dicussionQuery = dicussionQuery
.And(() => FilterViewModel.StartDate == null || discussionAlias.PlannedCompletionDate >= FilterViewModel.StartDate.Value)
.And(() => discussionAlias.PlannedCompletionDate <= FilterViewModel.EndDate);
}
break;
case DateFilterType.ActualCompletionDate:
query = query.Where(() => complaintAlias.ActualCompletionDate <= FilterViewModel.EndDate)
.And(() => FilterViewModel.StartDate == null || complaintAlias.ActualCompletionDate >= FilterViewModel.StartDate.Value);
break;
case DateFilterType.CreationDate:
query = query.Where(() => complaintAlias.CreationDate <= FilterViewModel.EndDate)
.And(() => FilterViewModel.StartDate == null || complaintAlias.CreationDate >= FilterViewModel.StartDate.Value);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
if (dicussionQuery != null)
{
query.WithSubquery.WhereExists(dicussionQuery);
}
if (FilterViewModel.ComplaintType != null)
{
query = query.Where(() => complaintAlias.ComplaintType == FilterViewModel.ComplaintType);
}
if (FilterViewModel.ComplaintStatus != null)
{
query = query.Where(() => complaintAlias.Status == FilterViewModel.ComplaintStatus);
}
if (FilterViewModel.Employee != null)
{
query = query.Where(() => complaintAlias.CreatedBy.Id == FilterViewModel.Employee.Id);
}
if (FilterViewModel.CurrentUserSubdivision != null &&
FilterViewModel.ComplaintDiscussionStatus != null)
{
query = query.Where(() => discussionAlias.Subdivision.Id == FilterViewModel.CurrentUserSubdivision.Id)
.And(() => discussionAlias.Status == FilterViewModel.ComplaintDiscussionStatus);
}
if (FilterViewModel.GuiltyItemVM?.Entity?.GuiltyType != null)
{
var subquery = QueryOver.Of <ComplaintGuiltyItem>()
.Where(g => g.GuiltyType == FilterViewModel.GuiltyItemVM.Entity.GuiltyType.Value);
switch (FilterViewModel.GuiltyItemVM.Entity.GuiltyType)
{
case ComplaintGuiltyTypes.None:
case ComplaintGuiltyTypes.Client:
break;
case ComplaintGuiltyTypes.Employee:
if (FilterViewModel.GuiltyItemVM.Entity.Employee != null)
{
subquery.Where(g => g.Employee.Id == FilterViewModel.GuiltyItemVM.Entity.Employee.Id);
}
break;
case ComplaintGuiltyTypes.Subdivision:
if (FilterViewModel.GuiltyItemVM.Entity.Subdivision != null)
{
subquery.Where(g => g.Subdivision.Id == FilterViewModel.GuiltyItemVM.Entity.Subdivision.Id);
}
break;
default:
break;
}
query.WithSubquery.WhereProperty(x => x.Id).In(subquery.Select(x => x.Complaint));
}
if (FilterViewModel.ComplaintKind != null)
{
query.Where(() => complaintAlias.ComplaintKind.Id == FilterViewModel.ComplaintKind.Id);
}
}
#endregion Filter
query.Where(
GetSearchCriterion(
() => complaintAlias.Id,
() => complaintAlias.ComplaintText,
() => complaintAlias.ResultText,
() => counterpartyAlias.Name,
() => deliveryPointAlias.CompiledAddress
)
);
query.SelectList(list => list
.SelectGroup(() => complaintAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => complaintAlias.CreationDate).WithAlias(() => resultAlias.Date)
.Select(() => complaintAlias.ComplaintType).WithAlias(() => resultAlias.Type)
.Select(() => complaintAlias.Status).WithAlias(() => resultAlias.Status)
.Select(workInSubdivisionsProjection).WithAlias(() => resultAlias.WorkInSubdivision)
.Select(plannedCompletionDateProjection).WithAlias(() => resultAlias.PlannedCompletionDate)
.Select(lastPlannedCompletionDateProjection).WithAlias(() => resultAlias.LastPlannedCompletionDate)
.Select(counterpartyWithAddressProjection).WithAlias(() => resultAlias.ClientNameWithAddress)
.Select(guiltiesProjection).WithAlias(() => resultAlias.Guilties)
.Select(authorProjection).WithAlias(() => resultAlias.Author)
.Select(finesProjection).WithAlias(() => resultAlias.Fines)
.Select(() => complaintAlias.ComplaintText).WithAlias(() => resultAlias.ComplaintText)
.Select(() => complaintKindAlias.Name).WithAlias(() => resultAlias.ComplaintKindString)
.Select(() => complaintKindAlias.IsArchive).WithAlias(() => resultAlias.ComplaintKindIsArchive)
.Select(() => complaintAlias.ResultText).WithAlias(() => resultAlias.ResultText)
.Select(() => complaintAlias.ActualCompletionDate).WithAlias(() => resultAlias.ActualCompletionDate)
);
var result = query.TransformUsing(Transformers.AliasToBean <ComplaintJournalNode>())
.OrderBy(n => n.Id)
.Desc().List <ComplaintJournalNode>()
;
return(query);
}
public int GetEmptyBottlesFromClientByOrder(IUnitOfWork uow, INomenclatureRepository nomenclatureRepository, Order order, int?excludeDocument = null)
{
if (nomenclatureRepository == null)
{
throw new ArgumentNullException(nameof(nomenclatureRepository));
}
var routeListItems = uow.Session.QueryOver <RouteListItem>()
.Where(rli => rli.Order == order)
.List();
if (routeListItems.Any())
{
return(routeListItems.Sum(q => q.BottlesReturned));
}
var defBottle = nomenclatureRepository.GetDefaultBottle(uow);
SelfDeliveryDocument selfDeliveryDocumentAlias = null;
var query = uow.Session.QueryOver <SelfDeliveryDocumentReturned>()
.Left.JoinAlias(d => d.Document, () => selfDeliveryDocumentAlias)
.Where(() => selfDeliveryDocumentAlias.Order == order)
.Where(r => r.Nomenclature == defBottle);
if (excludeDocument.HasValue && excludeDocument.Value > 0)
{
query.Where(() => selfDeliveryDocumentAlias.Id != excludeDocument.Value);
}
var bttls = query.Select(Projections.Sum <SelfDeliveryDocumentReturned>(s => s.Amount))
.SingleOrDefault <decimal>();
return((int)bttls);
}
