public static QueryOver <Equipment, Equipment> AvailableEquipmentQuery()
{
Vodovoz.Domain.Orders.Order orderAlias = null;
Equipment equipmentAlias = null;
WarehouseMovementOperation operationAddAlias = null;
OrderEquipment orderEquipmentAlias = null;
var equipmentInStockCriterion = Subqueries.IsNotNull(
QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.OrderBy(() => operationAddAlias.OperationTime).Desc
.Where(() => equipmentAlias.Id == operationAddAlias.Equipment.Id)
.Select(op => op.IncomingWarehouse)
.Take(1).DetachedCriteria
);
var subqueryAllReservedEquipment = QueryOver.Of <Vodovoz.Domain.Orders.Order>(() => orderAlias)
.Where(() => orderAlias.OrderStatus == OrderStatus.Accepted ||
orderAlias.OrderStatus == OrderStatus.InTravelList ||
orderAlias.OrderStatus == OrderStatus.OnLoading) // чтобы не добавить в доп соглашение оборудование добавленное в уже созданный заказ.
.JoinAlias(() => orderAlias.OrderEquipments, () => orderEquipmentAlias)
.Where(() => orderEquipmentAlias.Direction == Direction.Deliver)
.Select(Projections.Property(() => orderEquipmentAlias.Equipment.Id));
return(QueryOver.Of <Equipment>(() => equipmentAlias)
.Where(equipmentInStockCriterion)
.Where(e => e.AssignedToClient == null)
.WithSubquery.WhereProperty(() => equipmentAlias.Id).NotIn(subqueryAllReservedEquipment));
}
public static Dictionary <int, decimal> EquipmentInStock(IUnitOfWork UoW, int warehouseId, int[] equipmentIds)
{
Equipment equipmentAlias = null;
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
var subqueryAdd = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.Where(() => operationAddAlias.Equipment.Id == equipmentAlias.Id)
.And(Restrictions.Eq(Projections.Property <WarehouseMovementOperation>(o => o.IncomingWarehouse.Id), warehouseId))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var subqueryRemove = QueryOver.Of <WarehouseMovementOperation>(() => operationRemoveAlias)
.Where(() => operationRemoveAlias.Equipment.Id == equipmentAlias.Id)
.And(Restrictions.Eq(Projections.Property <WarehouseMovementOperation>(o => o.WriteoffWarehouse.Id), warehouseId))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
ItemInStock inStock = null;
var stocklist = UoW.Session.QueryOver <Equipment>(() => equipmentAlias)
.Where(() => equipmentAlias.Id.IsIn(equipmentIds))
.SelectList(list => list
.SelectGroup(() => equipmentAlias.Id).WithAlias(() => inStock.Id)
.SelectSubQuery(subqueryAdd).WithAlias(() => inStock.Added)
.SelectSubQuery(subqueryRemove).WithAlias(() => inStock.Removed)
).TransformUsing(Transformers.AliasToBean <ItemInStock>()).List <ItemInStock>();
var result = new Dictionary <int, decimal>();
foreach (var nomenclatureInStock in stocklist)
{
result.Add(nomenclatureInStock.Id, nomenclatureInStock.Amount);
}
return(result);
}
public static int GetStockForNomenclature(IUnitOfWork uow, int nomenclatureId)
{
Nomenclature nomenclatureAlias = null;
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
var subqueryAdd = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var subqueryRemove = QueryOver.Of <WarehouseMovementOperation>(() => operationRemoveAlias)
.Where(() => operationRemoveAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var amountProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int32, "( ?1 - ?2 )"),
NHibernateUtil.Int32, new IProjection[] {
Projections.SubQuery(subqueryAdd),
Projections.SubQuery(subqueryRemove)
}
);
ItemInStock inStock = null;
var queryResult = uow.Session.QueryOver <Nomenclature>(() => nomenclatureAlias)
.Where(() => nomenclatureAlias.Id == nomenclatureId)
.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id)
.Select(amountProjection)
).SingleOrDefault <object[]>();
return((int)queryResult[1]);
}
public Dictionary <int, decimal> NomenclatureInStock(IUnitOfWork uow, int[] nomenclatureIds, int?warehouseId = null, DateTime?onDate = null)
{
Nomenclature nomenclatureAlias = null;
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
var subqueryAdd = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id);
if (onDate.HasValue)
{
subqueryAdd.Where(x => x.OperationTime < onDate.Value);
}
if (warehouseId.HasValue)
{
subqueryAdd.And(() => operationAddAlias.IncomingWarehouse.Id == warehouseId);
}
else
{
subqueryAdd.And(() => operationAddAlias.IncomingWarehouse != null);
}
subqueryAdd.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var subqueryRemove = QueryOver.Of <WarehouseMovementOperation>(() => operationRemoveAlias)
.Where(() => operationRemoveAlias.Nomenclature.Id == nomenclatureAlias.Id);
if (onDate.HasValue)
{
subqueryRemove.Where(x => x.OperationTime < onDate.Value);
}
if (warehouseId.HasValue)
{
subqueryRemove.And(() => operationRemoveAlias.WriteoffWarehouse.Id == warehouseId);
}
else
{
subqueryRemove.And(() => operationRemoveAlias.WriteoffWarehouse != null);
}
subqueryRemove.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
ItemInStock inStock = null;
var stocklist = uow.Session.QueryOver <Nomenclature>(() => nomenclatureAlias)
.Where(() => nomenclatureAlias.Id.IsIn(nomenclatureIds))
.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => inStock.Id)
.SelectSubQuery(subqueryAdd).WithAlias(() => inStock.Added)
.SelectSubQuery(subqueryRemove).WithAlias(() => inStock.Removed)
).TransformUsing(Transformers.AliasToBean <ItemInStock>()).List <ItemInStock>();
var result = new Dictionary <int, decimal>();
foreach (var nomenclatureInStock in stocklist)
{
result.Add(nomenclatureInStock.Id, nomenclatureInStock.Amount);
}
return(result);
}
/// <summary>
/// Запрос выбирающий количество добавленное на склад, отгруженное со склада
/// и зарезервированное в заказах каждой номенклатуры по выбранному типу оборудования
/// </summary>
public static QueryOver <Nomenclature, Nomenclature> QueryAvailableNonSerialEquipmentForRent(EquipmentType type)
{
Nomenclature nomenclatureAlias = null;
WarehouseMovementOperation operationAddAlias = null;
//Подзапрос выбирающий по номенклатуре количество добавленное на склад
var subqueryAdded = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.IncomingWarehouse)))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
//Подзапрос выбирающий по номенклатуре количество отгруженное со склада
var subqueryRemoved = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.WriteoffWarehouse)))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
//Подзапрос выбирающий по номенклатуре количество зарезервированное в заказах до отгрузки со склада
Vodovoz.Domain.Orders.Order localOrderAlias = null;
OrderEquipment localOrderEquipmentAlias = null;
Equipment localEquipmentAlias = null;
var subqueryReserved = QueryOver.Of <Vodovoz.Domain.Orders.Order>(() => localOrderAlias)
.JoinAlias(() => localOrderAlias.OrderEquipments, () => localOrderEquipmentAlias)
.JoinAlias(() => localOrderEquipmentAlias.Equipment, () => localEquipmentAlias)
.Where(() => localEquipmentAlias.Nomenclature.Id == nomenclatureAlias.Id)
.Where(() => localOrderEquipmentAlias.Direction == Direction.Deliver)
.Where(() => localOrderAlias.OrderStatus == OrderStatus.Accepted ||
localOrderAlias.OrderStatus == OrderStatus.InTravelList ||
localOrderAlias.OrderStatus == OrderStatus.OnLoading)
.Select(Projections.Sum(() => localOrderEquipmentAlias.Count));
NomenclatureForRentVMNode resultAlias = null;
MeasurementUnits unitAlias = null;
EquipmentType equipmentType = null;
//Запрос выбирающий количество добавленное на склад, отгруженное со склада
//и зарезервированное в заказах каждой номенклатуры по выбранному типу оборудования
var query = QueryOver.Of <Nomenclature>(() => nomenclatureAlias)
.JoinAlias(() => nomenclatureAlias.Unit, () => unitAlias)
.JoinAlias(() => nomenclatureAlias.Type, () => equipmentType);
if (type != null)
{
query = query.Where(() => nomenclatureAlias.Type.Id == type.Id);
}
query = query.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => equipmentType.Id).WithAlias(() => resultAlias.TypeId)
.Select(() => unitAlias.Name).WithAlias(() => resultAlias.UnitName)
.Select(() => unitAlias.Digits).WithAlias(() => resultAlias.UnitDigits)
.SelectSubQuery(subqueryAdded).WithAlias(() => resultAlias.Added)
.SelectSubQuery(subqueryRemoved).WithAlias(() => resultAlias.Removed)
.SelectSubQuery(subqueryReserved).WithAlias(() => resultAlias.Reserved)
)
.TransformUsing(Transformers.AliasToBean <NomenclatureForRentVMNode>());
return(query);
}
public virtual void CreateOperation(Warehouse warehouseSrc, DateTime time)
{
ConsumptionMaterialOperation = new WarehouseMovementOperation {
WriteoffWarehouse = warehouseSrc,
Amount = Amount,
OperationTime = time,
Nomenclature = Nomenclature
};
}
public virtual void CreateOperation(Warehouse warehouse, DateTime time)
{
WarehouseMovementOperation = new WarehouseMovementOperation
{
WriteoffWarehouse = warehouse,
Amount = Amount,
OperationTime = time,
Nomenclature = Nomenclature,
Equipment = Equipment
};
}
public int BottlesUnloadedByCarUnloadedDocuments(IUnitOfWork uow, int emptyBottleId, int routeListId, params int[] exceptDocumentIds)
{
WarehouseMovementOperation movementOperationAlias = null;
var returnableQuery = OrderItemsReturnedToAllWarehouses(uow, routeListId, emptyBottleId).GetExecutableQueryOver(uow.Session);
var result = returnableQuery.WhereNot(x => x.Id.IsIn(exceptDocumentIds))
.SelectList(list => list.SelectSum(() => movementOperationAlias.Amount))
.SingleOrDefault <decimal>()
;
return((int)result);
}
public static IList <Equipment> GetEquipmentUnloadedTo(IUnitOfWork uow, RouteList routeList)
{
CarUnloadDocumentItem unloadItemAlias = null;
WarehouseMovementOperation operationAlias = null;
Equipment equipmentAlias = null;
var unloadedEquipmentIdsQuery = QueryOver.Of <CarUnloadDocument>().Where(doc => doc.RouteList.Id == routeList.Id)
.JoinAlias(doc => doc.Items, () => unloadItemAlias)
.JoinAlias(() => unloadItemAlias.WarehouseMovementOperation, () => operationAlias)
.JoinAlias(() => operationAlias.Equipment, () => equipmentAlias)
.Select(op => equipmentAlias.Id);
return(uow.Session.QueryOver <Equipment>(() => equipmentAlias).WithSubquery.WhereProperty(() => equipmentAlias.Id).In(unloadedEquipmentIdsQuery).List());
}
public override void UpdateNodes()
{
Nomenclature nomenclatureAlias = null;
EquipmentType equipmentTypeAlias = null;
NomenclatureForSaleVMNode resultAlias = null;
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
Vodovoz.Domain.Orders.Order orderAlias = null;
OrderEquipment orderEquipmentAlias = null;
Equipment equipmentAlias = null;
var subqueryAdded = QueryOver.Of <WarehouseMovementOperation> (() => operationAddAlias)
.JoinAlias(() => operationAddAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => nomenclatureAlias.Type.Id == equipmentTypeAlias.Id)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation> (o => o.IncomingWarehouse)))
.Select(Projections.Sum <WarehouseMovementOperation> (o => o.Amount));
var subqueryRemoved = QueryOver.Of <WarehouseMovementOperation>(() => operationRemoveAlias)
.JoinAlias(() => operationRemoveAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => nomenclatureAlias.Type.Id == equipmentTypeAlias.Id)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation> (o => o.WriteoffWarehouse)))
.Select(Projections.Sum <WarehouseMovementOperation> (o => o.Amount));
var subqueryReserved = QueryOver.Of <Vodovoz.Domain.Orders.Order> (() => orderAlias)
.JoinAlias(() => orderAlias.OrderEquipments, () => orderEquipmentAlias)
.JoinAlias(() => orderEquipmentAlias.Equipment, () => equipmentAlias)
.JoinAlias(() => equipmentAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => nomenclatureAlias.Type.Id == equipmentTypeAlias.Id)
.Where(() => orderEquipmentAlias.Direction == Direction.Deliver)
.Where(() => orderAlias.OrderStatus == OrderStatus.Accepted ||
orderAlias.OrderStatus == OrderStatus.InTravelList ||
orderAlias.OrderStatus == OrderStatus.OnLoading)
.Select(Projections.Count(() => orderEquipmentAlias.Id));
var equipment = UoW.Session.QueryOver <EquipmentType>(() => equipmentTypeAlias)
.SelectList(list => list
.SelectGroup(() => equipmentTypeAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => equipmentTypeAlias.Name).WithAlias(() => resultAlias.Name)
.SelectSubQuery(subqueryAdded).WithAlias(() => resultAlias.Added)
.SelectSubQuery(subqueryRemoved).WithAlias(() => resultAlias.Removed)
.SelectSubQuery(subqueryReserved).WithAlias(() => resultAlias.Reserved)
)
.TransformUsing(Transformers.AliasToBean <EquipmentTypesForRentVMNode>())
.List <EquipmentTypesForRentVMNode>();
SetItemsSource(equipment);
}
public decimal UnloadedTerminalAmount(IUnitOfWork uow, int routelistId, int terminalId)
{
CarUnloadDocument docAlias = null;
CarUnloadDocumentItem docItemsAlias = null;
WarehouseMovementOperation warehouseMovementOperationAlias = null;
var query = uow.Session.QueryOver(() => docAlias)
.JoinAlias(d => d.Items, () => docItemsAlias)
.JoinAlias(() => docItemsAlias.WarehouseMovementOperation, () => warehouseMovementOperationAlias)
.Where(() => docAlias.RouteList.Id == routelistId)
.And(() => warehouseMovementOperationAlias.Nomenclature.Id == terminalId)
.Select(Projections.Sum(() => warehouseMovementOperationAlias.Amount))
.SingleOrDefault <decimal>();
return(query);
}
public virtual void CreateOperation(Warehouse warehouse, DateTime time)
{
WarehouseWriteOffOperation = new WarehouseMovementOperation
{
WriteoffWarehouse = warehouse,
Amount = Amount,
OperationTime = time,
Nomenclature = NomenclatureOld
};
WarehouseIncomeOperation = new WarehouseMovementOperation
{
IncomingWarehouse = warehouse,
Amount = Amount,
OperationTime = time,
Nomenclature = NomenclatureNew
};
}
public Dictionary <int, decimal> NomenclatureUnloaded(IUnitOfWork UoW, RouteList routeList, Warehouse warehouse, CarUnloadDocument excludeDoc)
{
CarUnloadDocument docAlias = null;
CarUnloadDocumentItem docItemsAlias = null;
WarehouseMovementOperation movementOperationAlias = null;
var unloadedlist = UoW.Session.QueryOver <CarUnloadDocument>(() => docAlias)
.Where(d => d.RouteList.Id == routeList.Id)
.Where(d => d.Warehouse.Id == warehouse.Id)
.Where(d => d.Id != excludeDoc.Id)
.JoinAlias(d => d.Items, () => docItemsAlias)
.JoinAlias(() => docItemsAlias.WarehouseMovementOperation, () => movementOperationAlias)
.SelectList(list => list
.SelectGroup(() => movementOperationAlias.Nomenclature.Id)
.SelectSum(() => movementOperationAlias.Amount)
).List <object[]>();
return(unloadedlist.ToDictionary(r => (int)r[0], r => (decimal)r[1]));
}
public bool CanAddFlyerToOrder(
IUnitOfWork uow, IRouteListParametersProvider routeListParametersProvider, int flyerId, int geographicGroupId)
{
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
VodovozOrder orderAlias = null;
DeliveryPoint deliveryPointAlias = null;
District districtAlias = null;
OrderEquipment orderEquipmentAlias = null;
var warehouseId = geographicGroupId == routeListParametersProvider.SouthGeographicGroupId
? routeListParametersProvider.WarehouseSofiiskayaId
: routeListParametersProvider.WarehouseParnasId;
var subqueryAdded = uow.Session.QueryOver(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == flyerId)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.IncomingWarehouse)))
.Where(o => o.IncomingWarehouse.Id == warehouseId)
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount))
.SingleOrDefault <decimal>();
var subqueryRemoved = uow.Session.QueryOver(() => operationRemoveAlias)
.Where(() => operationRemoveAlias.Nomenclature.Id == flyerId)
.Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.WriteoffWarehouse)))
.Where(o => o.WriteoffWarehouse.Id == warehouseId)
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount))
.SingleOrDefault <decimal>();
var subqueryReserved = uow.Session.QueryOver(() => orderAlias)
.JoinAlias(() => orderAlias.OrderEquipments, () => orderEquipmentAlias)
.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
.JoinAlias(() => deliveryPointAlias.District, () => districtAlias)
.Where(() => orderEquipmentAlias.Nomenclature.Id == flyerId)
.Where(() => districtAlias.GeographicGroup.Id == geographicGroupId)
.Where(() => orderAlias.OrderStatus == OrderStatus.NewOrder ||
orderAlias.OrderStatus == OrderStatus.Accepted ||
orderAlias.OrderStatus == OrderStatus.InTravelList ||
orderAlias.OrderStatus == OrderStatus.OnLoading)
.Select(Projections.Sum(() => orderEquipmentAlias.Count))
.SingleOrDefault <int>();
return(subqueryAdded - subqueryRemoved - subqueryReserved > 0);
}
public override void CreateMovementOperations(Warehouse writeoffWarehouse, Nomenclature terminal)
{
WarehouseMovementOperation = new WarehouseMovementOperation
{
WriteoffWarehouse = writeoffWarehouse,
IncomingWarehouse = null,
Amount = 1,
Equipment = null,
Nomenclature = terminal,
OperationTime = CreationDate
};
EmployeeNomenclatureMovementOperation = new EmployeeNomenclatureMovementOperation
{
Amount = 1,
Employee = Driver,
Nomenclature = terminal,
OperationTime = CreationDate
};
}
public QueryOver <CarUnloadDocument> OrderItemsReturnedToAllWarehouses(IUnitOfWork uow, int routeListId, params int[] nomenclatureIds)
{
List <ReturnsNode> result = new List <ReturnsNode>();
Nomenclature nomenclatureAlias = null;
CarUnloadDocumentItem carUnloadItemsAlias = null;
WarehouseMovementOperation movementOperationAlias = null;
CarUnloadDocument carUnloadAlias = null;
var returnableQuery = QueryOver.Of <CarUnloadDocument>()
.Where(doc => doc.RouteList.Id == routeListId)
.JoinAlias(doc => doc.Items, () => carUnloadItemsAlias)
.JoinAlias(() => carUnloadItemsAlias.WarehouseMovementOperation, () => movementOperationAlias)
.Where(Restrictions.IsNotNull(Projections.Property(() => movementOperationAlias.IncomingWarehouse)))
.JoinAlias(() => movementOperationAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => !nomenclatureAlias.IsSerial)
.Where(() => nomenclatureAlias.Id.IsIn(nomenclatureIds));
return(returnableQuery);
}
public IEnumerable <NomanclatureStockNode> GetWarehouseNomenclatureStock(IUnitOfWork uow, int warehouseId, IEnumerable <int> nomenclatureIds)
{
NomanclatureStockNode resultAlias = null;
Nomenclature nomenclatureAlias = null;
WarehouseMovementOperation warehouseOperation = null;
IProjection incomeAmount = Projections.Sum(
Projections.Conditional(
Restrictions.Eq(Projections.Property(() => warehouseOperation.IncomingWarehouse.Id), warehouseId),
Projections.Property(() => warehouseOperation.Amount),
Projections.Constant(0M)
)
);
IProjection writeoffAmount = Projections.Sum(
Projections.Conditional(
Restrictions.Eq(Projections.Property(() => warehouseOperation.WriteoffWarehouse.Id), warehouseId),
Projections.Property(() => warehouseOperation.Amount),
Projections.Constant(0M)
)
);
IProjection stockProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "( IFNULL(?1, 0) - IFNULL(?2, 0) )"),
NHibernateUtil.Int32,
incomeAmount,
writeoffAmount
);
return(uow.Session.QueryOver(() => warehouseOperation)
.Left.JoinAlias(() => warehouseOperation.Nomenclature, () => nomenclatureAlias)
.Where(Restrictions.In(Projections.Property(() => warehouseOperation.Nomenclature.Id), nomenclatureIds.ToArray()))
.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.NomenclatureId)
.Select(stockProjection).WithAlias(() => resultAlias.Stock)
)
.TransformUsing(Transformers.AliasToBean <NomanclatureStockNode>())
.List <NomanclatureStockNode>());
}
public virtual void CreateOperation(Warehouse warehouse, DateTime time)
{
if (Difference < 0)
{
WarehouseChangeOperation = new WarehouseMovementOperation
{
WriteoffWarehouse = warehouse,
Amount = Math.Abs(Difference),
OperationTime = time,
Nomenclature = Nomenclature
};
}
if (Difference > 0)
{
WarehouseChangeOperation = new WarehouseMovementOperation
{
IncomingWarehouse = warehouse,
Amount = Math.Abs(Difference),
OperationTime = time,
Nomenclature = Nomenclature
};
}
}
public List <ReturnsNode> GetReturnsToWarehouse(IUnitOfWork uow, int routeListId, NomenclatureCategory[] categories = null, int[] excludeNomenclatureIds = null)
{
Nomenclature nomenclatureAlias = null;
ReturnsNode resultAlias = null;
CarUnloadDocumentItem carUnloadItemsAlias = null;
WarehouseMovementOperation movementOperationAlias = null;
var returnableQuery = uow.Session.QueryOver <CarUnloadDocument>().Where(doc => doc.RouteList.Id == routeListId)
.JoinAlias(doc => doc.Items, () => carUnloadItemsAlias)
.JoinAlias(() => carUnloadItemsAlias.WarehouseMovementOperation, () => movementOperationAlias)
.Where(Restrictions.IsNotNull(Projections.Property(() => movementOperationAlias.IncomingWarehouse)))
.JoinAlias(() => movementOperationAlias.Nomenclature, () => nomenclatureAlias)
.Where(() => !nomenclatureAlias.IsSerial);
if (categories != null)
{
returnableQuery.Where(() => nomenclatureAlias.Category.IsIn(categories));
}
if (excludeNomenclatureIds != null)
{
returnableQuery.Where(() => !nomenclatureAlias.Id.IsIn(excludeNomenclatureIds));
}
var result = returnableQuery.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.NomenclatureId)
.Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.Name)
.Select(() => false).WithAlias(() => resultAlias.Trackable)
.Select(() => nomenclatureAlias.Category).WithAlias(() => resultAlias.NomenclatureCategory)
.SelectSum(() => movementOperationAlias.Amount).WithAlias(() => resultAlias.Amount))
.TransformUsing(Transformers.AliasToBean <ReturnsNode>())
.List <ReturnsNode>();
DomainHelper.FillPropertyByEntity <ReturnsNode, Nomenclature>(uow, result, x => x.NomenclatureId, (node, nom) => node.Nomenclature = nom);
return(result.ToList());
}
public static IList <MovementWagon> UsedWagonsByPeriod(IUnitOfWork uow, DateTime start, DateTime end, Warehouse warehouse)
{
WarehouseMovementOperation shipOpAlias = null;
WarehouseMovementOperation deliverOpAlias = null;
MovementDocumentItem itemAlias = null;
MovementDocument docAlias = null;
MovementWagon wagonAlias = null;
end = end.Date.AddDays(1);
var docSubqery = QueryOver.Of <MovementDocument>(() => docAlias)
.JoinAlias(d => d.Items, () => itemAlias)
.JoinAlias(() => itemAlias.WarehouseWriteoffOperation, () => shipOpAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.JoinAlias(() => itemAlias.WarehouseIncomeOperation, () => deliverOpAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(d => d.MovementWagon.Id == wagonAlias.Id)
.Where(() => ((shipOpAlias.IncomingWarehouse == warehouse || shipOpAlias.WriteoffWarehouse == warehouse) &&
shipOpAlias.OperationTime >= start && shipOpAlias.OperationTime < end) ||
((deliverOpAlias.IncomingWarehouse == warehouse || deliverOpAlias.WriteoffWarehouse == warehouse) &&
deliverOpAlias.OperationTime >= start && deliverOpAlias.OperationTime < end))
.Select(d => d.Id);
return(uow.Session.QueryOver <MovementWagon>(() => wagonAlias)
.WithSubquery.WhereExists(docSubqery).List());
}
public virtual void CreateOperation(Warehouse warehouse, DateTime time)
{
if(Difference < 0)
{
WarehouseChangeOperation = new WarehouseMovementOperation
{
WriteoffWarehouse = warehouse,
Amount = Math.Abs(Difference),
OperationTime = time,
Nomenclature = Nomenclature
};
}
if(Difference > 0)
{
WarehouseChangeOperation = new WarehouseMovementOperation
{
IncomingWarehouse = warehouse,
Amount = Math.Abs(Difference),
OperationTime = time,
Nomenclature = Nomenclature
};
}
}
public virtual void CreateOperation(Warehouse warehouseSrc, DateTime time)
{
ConsumptionMaterialOperation = new WarehouseMovementOperation
{
WriteoffWarehouse = warehouseSrc,
Amount = Amount,
OperationTime = time,
Nomenclature = Nomenclature
};
}
/// <summary>
/// Создание операции доставки при транспортировке
/// </summary>
public virtual void CreateOperation(Warehouse warehouseDst, DateTime deliveredTime)
{
DeliveryMovementOperation = new WarehouseMovementOperation
{
IncomingWarehouse = warehouseDst,
Amount = Amount,
OperationTime = deliveredTime,
Nomenclature = Nomenclature,
Equipment = Equipment
};
WarehouseMovementOperation.IncomingWarehouse = null;
}
public virtual void CreateOperation(Warehouse warehouseSrc, Warehouse warehouseDst, DateTime time, TransportationStatus status)
{
CounterpartyMovementOperation = null;
WarehouseMovementOperation = new WarehouseMovementOperation
{
WriteoffWarehouse = warehouseSrc,
IncomingWarehouse = status == TransportationStatus.WithoutTransportation ? warehouseDst : null,
Amount = Amount,
OperationTime = time,
Nomenclature = Nomenclature,
Equipment = Equipment
};
if (status == TransportationStatus.Delivered)
CreateOperation(warehouseDst, Document.DeliveredTime.Value);
}
public virtual void CreateOperation(Counterparty counterpartySrc, DeliveryPoint pointSrc, Counterparty counterpartyDst, DeliveryPoint pointDst, DateTime time)
{
WarehouseMovementOperation = null;
CounterpartyMovementOperation = new CounterpartyMovementOperation
{
IncomingCounterparty = counterpartyDst,
IncomingDeliveryPoint = pointDst,
WriteoffCounterparty = counterpartySrc,
WriteoffDeliveryPoint = pointSrc,
Amount = Amount,
OperationTime = time,
Nomenclature = Nomenclature,
Equipment = Equipment
};
}
public virtual void CreateOperation(Warehouse warehouse, DateTime time)
{
WarehouseMovementOperation = new WarehouseMovementOperation
{
IncomingWarehouse = warehouse,
Amount = Amount,
OperationTime = time,
Nomenclature = Nomenclature,
Equipment = Equipment
};
}
public static Dictionary <int, decimal> NomenclatureInStock(
IUnitOfWork UoW, int warehouseId,
int[] nomenclaturesToInclude,
int[] nomenclaturesToExclude,
string[] nomenclatureTypeToInclude,
string[] nomenclatureTypeToExclude,
int[] productGroupToInclude,
int[] productGroupToExclude,
DateTime?onDate = null)
{
Nomenclature nomenclatureAlias = null;
Nomenclature nomenclatureAddOperationAlias = null;
Nomenclature nomenclatureRemoveOperationAlias = null;
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
var subqueryAdd = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.JoinAlias(() => operationAddAlias.Nomenclature, () => nomenclatureAddOperationAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id);
if (nomenclatureTypeToInclude.Length > 0)
{
List <NomenclatureCategory> parsedCategories = new List <NomenclatureCategory>();
foreach (var categoryName in nomenclatureTypeToInclude)
{
parsedCategories.Add((NomenclatureCategory)Enum.Parse(typeof(NomenclatureCategory), categoryName));
}
subqueryAdd = subqueryAdd.Where(() => nomenclatureAddOperationAlias.Category.IsIn(parsedCategories));
}
if (productGroupToInclude != null && productGroupToInclude.Any())
{
subqueryAdd = subqueryAdd.Where(() => nomenclatureAddOperationAlias.ProductGroup.Id.IsIn(productGroupToInclude));
}
if (nomenclaturesToInclude != null && nomenclaturesToInclude.Any())
{
subqueryAdd = subqueryAdd.Where(() => nomenclatureAddOperationAlias.Id.IsIn(nomenclaturesToInclude));
}
if (nomenclatureTypeToExclude.Length > 0)
{
List <NomenclatureCategory> parsedCategories = new List <NomenclatureCategory>();
foreach (var categoryName in nomenclatureTypeToExclude)
{
parsedCategories.Add((NomenclatureCategory)Enum.Parse(typeof(NomenclatureCategory), categoryName));
}
subqueryAdd = subqueryAdd.Where(() => nomenclatureAddOperationAlias.Category.IsIn(parsedCategories));
}
if (productGroupToExclude != null && productGroupToExclude.Any())
{
subqueryAdd = subqueryAdd.Where(() => nomenclatureAddOperationAlias.ProductGroup.IsIn(productGroupToExclude));
}
if (nomenclaturesToExclude != null && nomenclaturesToExclude.Any())
{
subqueryAdd = subqueryAdd.Where(() => nomenclatureAddOperationAlias.Id.IsIn(nomenclaturesToExclude));
}
if (onDate.HasValue)
{
subqueryAdd = subqueryAdd.Where(x => x.OperationTime < onDate.Value);
}
subqueryAdd.And(Restrictions.Eq(Projections.Property <WarehouseMovementOperation>(o => o.IncomingWarehouse.Id), warehouseId))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var subqueryRemove = QueryOver.Of(() => operationRemoveAlias)
.JoinAlias(() => operationRemoveAlias.Nomenclature, () => nomenclatureRemoveOperationAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => operationRemoveAlias.Nomenclature.Id == nomenclatureAlias.Id);
if (nomenclatureTypeToInclude.Length > 0)
{
List <NomenclatureCategory> parsedCategories = new List <NomenclatureCategory>();
foreach (var categoryName in nomenclatureTypeToInclude)
{
parsedCategories.Add((NomenclatureCategory)Enum.Parse(typeof(NomenclatureCategory), categoryName));
}
subqueryRemove = subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.Category.IsIn(parsedCategories));
}
if (productGroupToInclude != null && productGroupToInclude.Any())
{
subqueryRemove = subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.ProductGroup.Id.IsIn(productGroupToInclude));
}
if (nomenclaturesToInclude != null && nomenclaturesToInclude.Any())
{
subqueryRemove = subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.Id.IsIn(nomenclaturesToInclude));
}
if (nomenclatureTypeToExclude.Length > 0)
{
List <NomenclatureCategory> parsedCategories = new List <NomenclatureCategory>();
foreach (var categoryName in nomenclatureTypeToExclude)
{
parsedCategories.Add((NomenclatureCategory)Enum.Parse(typeof(NomenclatureCategory), categoryName));
}
subqueryRemove = subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.Category.IsIn(parsedCategories));
}
if (productGroupToExclude != null && productGroupToExclude.Any())
{
subqueryRemove = subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.ProductGroup.IsIn(productGroupToExclude));
}
if (nomenclaturesToExclude != null && nomenclaturesToExclude.Any())
{
subqueryRemove = subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.Id.IsIn(nomenclaturesToExclude));
}
if (onDate.HasValue)
{
subqueryRemove = subqueryRemove.Where(x => x.OperationTime < onDate.Value);
}
subqueryRemove.And(Restrictions.Eq(Projections.Property <WarehouseMovementOperation>(o => o.WriteoffWarehouse.Id), warehouseId))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
ItemInStock inStock = null;
var stocklist = UoW.Session.QueryOver <Nomenclature>(() => nomenclatureAlias)
.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => inStock.Id)
.SelectSubQuery(subqueryAdd).WithAlias(() => inStock.Added)
.SelectSubQuery(subqueryRemove).WithAlias(() => inStock.Removed)
).TransformUsing(Transformers.AliasToBean <ItemInStock>()).List <ItemInStock>();
var result = new Dictionary <int, decimal>();
foreach (var nomenclatureInStock in stocklist.Where(x => x.Amount != 0))
{
result.Add(nomenclatureInStock.Id, nomenclatureInStock.Amount);
}
return(result);
}
public Dictionary <int, decimal> NomenclatureInStock(IUnitOfWork uow, int warehouseId, DateTime?onDate = null, NomenclatureCategory?nomenclatureCategory = null, ProductGroup nomenclatureType = null, Nomenclature nomenclature = null)
{
Nomenclature nomenclatureAlias = null;
Nomenclature nomenclatureAddOperationAlias = null;
Nomenclature nomenclatureRemoveOperationAlias = null;
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
var subqueryAdd = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.JoinAlias(() => operationAddAlias.Nomenclature, () => nomenclatureAddOperationAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id);
if (nomenclatureCategory != null)
{
subqueryAdd.Where(() => nomenclatureAddOperationAlias.Category == nomenclatureCategory.Value);
}
if (nomenclatureType != null)
{
subqueryAdd.Where(() => nomenclatureAddOperationAlias.ProductGroup.Id == nomenclatureType.Id);
}
if (nomenclature != null)
{
subqueryAdd.Where(() => nomenclatureAddOperationAlias.Id == nomenclature.Id);
}
if (onDate.HasValue)
{
subqueryAdd.Where(x => x.OperationTime < onDate.Value);
}
subqueryAdd.And(Restrictions.Eq(Projections.Property <WarehouseMovementOperation>(o => o.IncomingWarehouse.Id), warehouseId))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var subqueryRemove = QueryOver.Of(() => operationRemoveAlias)
.JoinAlias(() => operationRemoveAlias.Nomenclature, () => nomenclatureRemoveOperationAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => operationRemoveAlias.Nomenclature.Id == nomenclatureAlias.Id);
if (nomenclatureCategory != null)
{
subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.Category == nomenclatureCategory.Value);
}
if (nomenclatureType != null)
{
subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.ProductGroup == nomenclatureType);
}
if (nomenclature != null)
{
subqueryRemove.Where(() => nomenclatureRemoveOperationAlias.Id == nomenclature.Id);
}
if (onDate.HasValue)
{
subqueryRemove.Where(x => x.OperationTime < onDate.Value);
}
subqueryRemove.And(Restrictions.Eq(Projections.Property <WarehouseMovementOperation>(o => o.WriteoffWarehouse.Id), warehouseId))
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
ItemInStock inStock = null;
var stocklist = uow.Session.QueryOver <Nomenclature>(() => nomenclatureAlias)
.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => inStock.Id)
.SelectSubQuery(subqueryAdd).WithAlias(() => inStock.Added)
.SelectSubQuery(subqueryRemove).WithAlias(() => inStock.Removed)
).TransformUsing(Transformers.AliasToBean <ItemInStock>()).List <ItemInStock>();
var result = new Dictionary <int, decimal>();
foreach (var nomenclatureInStock in stocklist.Where(x => x.Amount != 0))
{
result.Add(nomenclatureInStock.Id, nomenclatureInStock.Amount);
}
return(result);
}
/// <summary>
/// Возвращает список товаров возвращенного на склад по номенклатурам
/// </summary>
public List <ReturnsNode> GetReturnsToWarehouse(IUnitOfWork uow, int routeListId, params int[] nomenclatureIds)
{
List <ReturnsNode> result = new List <ReturnsNode>();
Nomenclature nomenclatureAlias = null;
ReturnsNode resultAlias = null;
Equipment equipmentAlias = null;
CarUnloadDocument carUnloadAlias = null;
CarUnloadDocumentItem carUnloadItemsAlias = null;
WarehouseMovementOperation movementOperationAlias = null;
var returnableQuery = QueryOver.Of <CarUnloadDocument>(() => carUnloadAlias)
.JoinAlias(() => carUnloadAlias.Items, () => carUnloadItemsAlias)
.JoinAlias(() => carUnloadItemsAlias.WarehouseMovementOperation, () => movementOperationAlias)
.JoinAlias(() => movementOperationAlias.Nomenclature, () => nomenclatureAlias)
.Where(Restrictions.IsNotNull(Projections.Property(() => movementOperationAlias.IncomingWarehouse)))
.Where(() => !nomenclatureAlias.IsSerial)
.Where(() => carUnloadAlias.RouteList.Id == routeListId)
.Where(() => nomenclatureAlias.Id.IsIn(nomenclatureIds))
.GetExecutableQueryOver(uow.Session);
var returnableItems = returnableQuery.SelectList
(
list => list.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.NomenclatureId)
.Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.Name)
.Select(() => false).WithAlias(() => resultAlias.Trackable)
.Select(() => nomenclatureAlias.Category).WithAlias(() => resultAlias.NomenclatureCategory)
.Select(() => carUnloadItemsAlias.DefectSource).WithAlias(() => resultAlias.DefectSource)
.SelectSum(() => movementOperationAlias.Amount).WithAlias(() => resultAlias.Amount)
)
.TransformUsing(Transformers.AliasToBean <ReturnsNode>())
.List <ReturnsNode>();
var returnableQueryEquipment = uow.Session.QueryOver <CarUnloadDocument>(() => carUnloadAlias)
.JoinAlias(() => carUnloadAlias.Items, () => carUnloadItemsAlias)
.JoinAlias(() => carUnloadItemsAlias.WarehouseMovementOperation, () => movementOperationAlias)
.JoinAlias(() => movementOperationAlias.Equipment, () => equipmentAlias)
.JoinAlias(() => equipmentAlias.Nomenclature, () => nomenclatureAlias)
.Where(Restrictions.IsNotNull(Projections.Property(() => movementOperationAlias.IncomingWarehouse)))
.Where(() => carUnloadAlias.RouteList.Id == routeListId)
.Where(() => nomenclatureAlias.Id.IsIn(nomenclatureIds))
;
var returnableEquipment =
returnableQueryEquipment.SelectList(list => list
.Select(() => equipmentAlias.Id).WithAlias(() => resultAlias.Id)
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.NomenclatureId)
.Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.Name)
.Select(() => nomenclatureAlias.IsSerial).WithAlias(() => resultAlias.Trackable)
.Select(() => nomenclatureAlias.Category).WithAlias(() => resultAlias.NomenclatureCategory)
.SelectSum(() => movementOperationAlias.Amount).WithAlias(() => resultAlias.Amount)
.Select(() => nomenclatureAlias.Type).WithAlias(() => resultAlias.EquipmentType)
.Select(() => carUnloadItemsAlias.DefectSource).WithAlias(() => resultAlias.DefectSource)
)
.TransformUsing(Transformers.AliasToBean <ReturnsNode>())
.List <ReturnsNode>();
result.AddRange(returnableItems);
result.AddRange(returnableEquipment);
DomainHelper.FillPropertyByEntity <ReturnsNode, Nomenclature>(uow, result, x => x.NomenclatureId, (node, nom) => node.Nomenclature = nom);
return(result);
}
