public IList <RecipeDTO> GetRecipesOfOrder(int orderId)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Recipe r = null;
Recipes_Model.Entities.Order o = null;
Product p = null;
Measure m = null;
RecipeDTO dto = null;
var recipes = session.QueryOver(() => r)
.SelectList(list => list
.Select(() => r.Id).WithAlias(() => dto.Id)
.Select(() => p.Id).WithAlias(() => dto.ProductId)
.Select(() => p.Name).WithAlias(() => dto.ProductName)
.Select(() => m.Id).WithAlias(() => dto.MeasureId)
.Select(() => m.Name).WithAlias(() => dto.MeasureName)
.Select(() => r.MeasureCount).WithAlias(() => dto.MeasureCount))
.JoinAlias(() => r.Order, () => o)
.JoinAlias(() => r.Product, () => p)
.JoinAlias(() => r.Measure, () => m)
.Where(() => o.Id == orderId)
.OrderBy(() => p.Name).Asc
.TransformUsing(Transformers.AliasToBean <RecipeDTO>())
.List <RecipeDTO>();
return(recipes);
}
}
public IList <OrderDTO> GetOrders(int pageNo, int pageSize, OrderSearchCriteriaDTO criteria = null)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Recipes_Model.Entities.Order o = null;
OrderDTO dto = null;
var orders = session.QueryOver(() => o)
.SelectList(list => list
.Select(() => o.Id).WithAlias(() => dto.Id)
.Select(() => o.Name).WithAlias(() => dto.Name)
.Select(() => o.OrderNo).WithAlias(() => dto.OrderNo)
.Select(() => o.Comments).WithAlias(() => dto.Comments)
.Select(() => o.ItemCount).WithAlias(() => dto.ItemCount)
.Select(() => o.Date).WithAlias(() => dto.Date));
if (criteria != null)
{
if (criteria.OrderName.IsNotNull())
{
orders.Where(Restrictions.InsensitiveLike(nameof(o.Name), criteria.OrderName, MatchMode.Anywhere));
}
if (criteria.OrderNo.IsNotNull())
{
orders.Where(Restrictions.InsensitiveLike(nameof(o.OrderNo), criteria.OrderNo, MatchMode.Anywhere));
}
if (criteria.OrderDate.IsNotNull())
{
orders.Where(() => o.Date.Date == criteria.OrderDate.Value.Date);
}
if (criteria.ComponentName.IsNotNull())
{
Recipe r = null;
Product p = null;
Mount m = null;
Component c = null;
orders
.JoinAlias(() => o.Recipes, () => r)
.JoinAlias(() => r.Product, () => p)
.JoinAlias(() => p.Mounts, () => m)
.JoinAlias(() => m.Component, () => c)
.Where(Restrictions.InsensitiveLike("c.Name", criteria.ComponentName, MatchMode.Anywhere));
}
}
return(orders
.OrderBy(() => o.Name).Asc
.TransformUsing(Transformers.AliasToBean <OrderDTO>())
.Skip((pageNo - 1) * pageSize).Take(pageSize)
.List <OrderDTO>());
}
}
public IList <MountDTO> GetComponentsOfOrder(int orderId)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Recipes_Model.Entities.Order o = null;
Recipe r = null;
Product p = null;
Mount m = null;
Measure me = null;
Component c = null;
MountDTO dto = null;
var mounts = session.QueryOver(() => o)
.JoinAlias(() => o.Recipes, () => r)
.JoinAlias(() => r.Product, () => p)
.JoinAlias(() => p.Mounts, () => m)
.JoinAlias(() => m.Component, () => c)
.JoinAlias(() => m.Measure, () => me)
.SelectList(list => list
.Select(Projections.Group(() => c.Id)).WithAlias(() => dto.ComponentId)
.Select(Projections.Group(() => c.Name)).WithAlias(() => dto.ComponentName)
.Select(Projections.Group(() => me.Id)).WithAlias(() => dto.MeasureId)
.Select(Projections.Group(() => me.Name)).WithAlias(() => dto.MeasureName)
.Select(Projections.Sum(
Projections.SqlFunction(
new VarArgsSQLFunction("(", "*", ")"), NHibernateUtil.Decimal,
new[]
{
Projections.Property("m.MeasureCount"), Projections.Property("r.MeasureCount")
}
))).WithAlias(() => dto.MeasureCount)
.Select(Projections.Sum(
Projections.SqlFunction(
new VarArgsSQLFunction("(", "*", ")"), NHibernateUtil.Decimal,
new[]
{
Projections.Property("m.ItemCount"), Projections.Property("r.MeasureCount")
}
)).WithAlias(() => dto.ItemCount)))
.Where(() => o.Id == orderId)
.OrderBy(() => o.Name).Asc
.TransformUsing(Transformers.AliasToBean <MountDTO>())
.List <MountDTO>();
return(mounts);
}
}
public int GetOrdersCount(int pageNo, int pageSize, OrderSearchCriteriaDTO criteria = null)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Recipes_Model.Entities.Order o = null;
var count = session.QueryOver(() => o);
if (criteria != null)
{
if (criteria.OrderName.IsNotNull())
{
count.Where(Restrictions.InsensitiveLike(nameof(o.Name), criteria.OrderName, MatchMode.Anywhere));
}
if (criteria.OrderNo.IsNotNull())
{
count.Where(Restrictions.InsensitiveLike(nameof(o.OrderNo), criteria.OrderNo, MatchMode.Anywhere));
}
if (criteria.OrderDate.IsNotNull())
{
count.Where(() => o.Date.Date == criteria.OrderDate.Value.Date);
}
if (criteria.ComponentName.IsNotNull())
{
Recipe r = null;
Product p = null;
Mount m = null;
Component c = null;
count
.JoinAlias(() => o.Recipes, () => r)
.JoinAlias(() => r.Product, () => p)
.JoinAlias(() => p.Mounts, () => m)
.JoinAlias(() => m.Component, () => c)
.Where(Restrictions.InsensitiveLike("c.Name", criteria.ComponentName, MatchMode.Anywhere));
}
}
var result = Convert.ToInt32(Math.Ceiling((1.0 * count.RowCount()) / pageSize));
return(result == 0 ? 1 : result);
}
}