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); } }