public IList <MountDTO> GetSelectedProductMounts(int productId)
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Component c = null;
Mount m = null;
Product p = null;
Measure me = null;
MountDTO dto = null;
var mounts = session.QueryOver(() => c)
.Left.JoinAlias(() => c.Mounts, () => m)
.Left.JoinAlias(() => m.Product, () => p)
.Left.JoinAlias(() => m.Measure, () => me)
.SelectList(l => l
.Select(Projections.Group((() => c.Id)).WithAlias(() => dto.ComponentId))
.Select(Projections.Group((() => c.Name)).WithAlias(() => dto.ComponentName))
.Select(Projections.Max(Projections.Conditional(Restrictions.Eq("p.Id", productId), Projections.Constant(true), Projections.Constant(false)))
.WithAlias(() => dto.IsComponentMounted))
.Select(Projections.Max(Projections.Conditional(Restrictions.Eq("p.Id", productId), Projections.Property("me.Id"), Projections.Constant(1))))
.WithAlias(() => dto.MeasureId)
.Select(Projections.Max(Projections.Conditional(Restrictions.Eq("p.Id", productId), Projections.Property("m.MeasureCount"), Projections.Constant(0M, NHibernateUtil.Decimal))))
.WithAlias(() => dto.MeasureCount)
.Select(Projections.Max(Projections.Conditional(Restrictions.Eq("p.Id", productId), Projections.Property("m.ItemCount"), Projections.Constant(0M, NHibernateUtil.Decimal))))
.WithAlias(() => dto.ItemCount))
.OrderByAlias(() => dto.IsComponentMounted).Desc
.OrderByAlias(() => dto.ComponentName).Asc
.TransformUsing(Transformers.AliasToBean <MountDTO>())
.List <MountDTO>();
return(mounts);
#region Upper Sql example for 965 ProductId
/*
* SELECT
* c.Id AS ComponentId,
* c.Name AS ComponentName,
* MAX(CASE WHEN p.id = 965 THEN p.Id ELSE NULL END) AS ProductId,
* MAX(CASE WHEN p.id = 965 THEN me.Id ELSE NULL END) AS MeasureId,
* MAX(CASE WHEN p.id = 965 THEN mnt.MeasureCount ELSE NULL END) AS MountMeasureCount,
* MAX(CASE WHEN p.id = 965 THEN mnt.ItemCount ELSE NULL END) AS MountItemCount
* FROM `Component` c
* LEFT JOIN `Mount` mnt ON c.Id = mnt.Component_id
* LEFT JOIN `Measure` me ON mnt.Measure_id = me.Id
* LEFT JOIN `Product` p ON mnt.Product_id = p.Id
* GROUP BY c.Id
* ORDER BY ProductId DESC, ComponentName ASC;
*/
#endregion
}
}
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);
}
}
