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);
}
public static ItemInStock ConvertFromDTO(ItemInStockDTO idt)
{
ItemInStock i = new ItemInStock();
i.ItemInStockId = idt.ItemInStockId;
i.ItemId = idt.ItemId;
i.ClientId = idt.ClientId;
return(i);
}
public static ItemInStockDTO ConvertToDTO(ItemInStock i)
{
ItemInStockDTO idt = new ItemInStockDTO();
idt.ItemInStockId = i.ItemInStockId;
idt.ItemId = i.ItemId;
idt.ClientId = i.ClientId;
return(idt);
}
public static List <ItemInStock> ConvertToListFromDTO(List <ItemInStockDTO> lidt)
{
List <ItemInStock> li = new List <ItemInStock>();
for (int i = 0; i < lidt.LongCount(); i++)
{
ItemInStock ii = new ItemInStock();
ii.ItemInStockId = lidt[i].ItemInStockId;
ii.ItemId = lidt[i].ItemId;
ii.ClientId = lidt[i].ClientId;
li.Add(ii);
}
return(li);
}
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);
}