private void SetCriteria()
{
DetachedCriteria criteria = DetachedCriteria.For(typeof(InProcessLocationDetail));
criteria.CreateAlias("OrderLocationTransaction", "olt");
criteria.CreateAlias("InProcessLocation", "ip");
criteria.Add(Expression.Eq("ip.Status", BusinessConstants.CODE_MASTER_STATUS_VALUE_CREATE));
criteria.Add(Expression.Eq("ip.Type", BusinessConstants.CODE_MASTER_INPROCESS_LOCATION_TYPE_VALUE_NORMAL));
if (this.IOType == BusinessConstants.IO_TYPE_IN)
{
DetachedCriteria subCriteria = DetachedCriteria.For(typeof(OrderLocationTransaction));
subCriteria.CreateAlias("OrderDetail", "od");
subCriteria.CreateAlias("od.OrderHead", "oh");
OrderHelper.SetOpenOrderStatusCriteria(subCriteria, "oh.Status");
subCriteria.Add(Expression.Eq("IOType", BusinessConstants.IO_TYPE_IN));
subCriteria.Add(Expression.Eq("Item.Code", this.Item));
subCriteria.Add(Expression.Eq("Location.Code", this.Location));
subCriteria.SetProjection(Projections.ProjectionList().Add(Projections.GroupProperty("OrderDetail.Id")));
criteria.Add(Subqueries.PropertyIn("olt.OrderDetail.Id", subCriteria));
}
else
{
criteria.Add(Expression.Eq("olt.Item.Code", this.Item));
criteria.Add(Expression.Eq("olt.Location.Code", this.Location));
}
DetachedCriteria selectCountCriteria = CloneHelper.DeepClone <DetachedCriteria>(criteria);
selectCountCriteria.SetProjection(Projections.Count("Id"));
this.SetSearchCriteria(criteria, selectCountCriteria);
}
private List <LeanEngine.Entity.InvBalance> GetInspectInvBalance()
{
//报验库存
DetachedCriteria criteria = DetachedCriteria.For(typeof(InspectOrderDetail));
criteria.CreateAlias("InspectOrder", "io");
criteria.CreateAlias("LocationLotDetail", "lld");
criteria.Add(Expression.Eq("io.Status", BusinessConstants.CODE_MASTER_STATUS_VALUE_CREATE));
criteria.Add(Expression.Eq("io.IsSeperated", false));
criteria.SetProjection(Projections.ProjectionList()
.Add(Projections.Sum("InspectQty").As("InspectQty"))
.Add(Projections.Sum("QualifiedQty").As("QualifiedQty"))
.Add(Projections.Sum("RejectedQty").As("RejectedQty"))
.Add(Projections.GroupProperty("LocationFrom.Code").As("LocationFromCode"))
.Add(Projections.GroupProperty("lld.Item.Code").As("ItemCode")));
criteria.SetResultTransformer(Transformers.AliasToBean(typeof(InspectOrderDetail)));
IList <InspectOrderDetail> inspectOrderDetailList = CriteriaMgr.FindAll <InspectOrderDetail>(criteria);
var query = from i in inspectOrderDetailList
select new LeanEngine.Entity.InvBalance
{
Loc = i.LocationFromCode,
Item = i.ItemCode,
Qty = i.InspectedQty,
InvType = Enumerators.InvType.Inspect
};
return(query.Where(q => q.Qty != 0).ToList());
}
public IList <InProcessLocationDetail> GetInProcessLocationDetailOut(IList <string> itemList, IList <string> locList)
{
DetachedCriteria criteria = DetachedCriteria.For(typeof(InProcessLocationDetail));
criteria.CreateAlias("InProcessLocation", "ip");
criteria.Add(Expression.Eq("ip.Status", BusinessConstants.CODE_MASTER_STATUS_VALUE_CREATE));
criteria.Add(Expression.Eq("ip.Type", BusinessConstants.CODE_MASTER_INPROCESS_LOCATION_TYPE_VALUE_NORMAL));
criteria.CreateAlias("OrderLocationTransaction", "olt");
if (itemList.Count == 1)
{
criteria.Add(Expression.Eq("olt.Item.Code", itemList[0]));
}
else
{
criteria.Add(Expression.InG <string>("olt.Item.Code", itemList));
}
if (locList.Count == 1)
{
criteria.Add(Expression.Eq("olt.Location.Code", locList[0]));
}
else
{
criteria.Add(Expression.InG <string>("olt.Location.Code", locList));
}
criteria.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("olt.Location.Code").As("LocationCode"))
.Add(Projections.GroupProperty("olt.Item.Code").As("ItemCode"))
.Add(Projections.Sum("Qty").As("Qty")));
criteria.SetResultTransformer(Transformers.AliasToBean(typeof(InProcessLocationDetail)));
return(this.criteriaMgr.FindAll <InProcessLocationDetail>(criteria));
}
public IList <PurchaseOrderItem> GetMonthlyTransaction(int year, int month)
{
DateTime startDate = new DateTime(year, month, 1);
DateTime endDate = startDate.AddMonths(1);
ICriteria crit = GetCriteria();
ICriteria purchase = crit.CreateCriteria("PurchaseOrder");
purchase.Add(new LtExpression("Date", endDate));
purchase.Add(new GeExpression("Date", startDate));
purchase.Add(new OrExpression(new EqExpression("Location", "01"), new EqExpression("Location", "09")));
crit.SetProjection(Projections.ProjectionList()
.Add(Projections.Sum("QuantityOrdered"))
.Add(Projections.GroupProperty("Product"))
);
crit.SetResultTransformer(
new NHibernate.Transform.AliasToBeanConstructorResultTransformer(typeof(PurchaseOrderItem).GetConstructors()[1])
);
//IList obj = new ArrayList();
//crit.List(obj);
return(crit.List <PurchaseOrderItem>());
}
public void NestedSubqueriesShouldStayInOrder()
{
if (!Dialect.SupportsScalarSubSelects)
{
Assert.Ignore("Dialect does not support scalar sub-select");
}
using (ISession s = OpenSession())
using (ITransaction tx = s.BeginTransaction())
{
DetachedCriteria dc1 = DetachedCriteria.For(typeof(Person))
.Add(Property.ForName("IQ").Eq(40))
.SetProjection(Property.ForName("IQ"));
DetachedCriteria dc2 = DetachedCriteria.For(typeof(Person))
.Add(Subqueries.PropertyEq("ShoeSize", dc1))
.SetProjection(
new SqlFunctionProjection(
"concat",
NHibernateUtil.String,
Projections.GroupProperty("Name"),
new ConstantProjection(" "),
Projections.GroupProperty("Name")));
IList list = s.CreateCriteria(typeof(Person))
.Add(Subqueries.Eq("Fred Fred", dc2))
.List();
Assert.AreEqual(5, list.Count); //yeah, it returns all five results. The key is that it didn't crash
tx.Commit();
}
}
public IDictionary <string, int> getYearlyCompanyMap()
{
Utility.logFile(Constant.METHOD_ENTER + Utility.getCurrentMethodName(1) + "(StatisticsManagerImpl)", null, Constant.LOGTYPE_INFO);
DetachedCriteria criteria = DetachedCriteria.For <Entity_CompanyDetails>();
criteria.Add(Restrictions.Eq("status", Utility.getEnumIndexByValue(Constant.ENUM_COMPANY_STATUS,
EnumData.companyStatus.CONFIRMED.ToString())));
criteria.Add(Restrictions.Eq("myConfigObj.currentDegree", Common.loggedUser.myConfigObj.currentDegree));
ProjectionList projectionList = Projections.ProjectionList();
projectionList.Add(Projections.GroupProperty(Projections.Property("myConfigObj.currentBatch")));
projectionList.Add(Projections.Count(Projections.Property("compDetailsId")));
criteria.SetProjection(projectionList);
IList list = persistence.findByCriteria(criteria);
if (list != null && list.Count > 0)
{
IDictionary <string, int> resultMap = new Dictionary <string, int>();
foreach (Object[] item in list)
{
resultMap.Add(item[0].ToString(), Convert.ToInt32(item[1]));
}
return(resultMap);
}
else
{
return(null);
}
}
public IList getCompanyStatCount()
{
Utility.logFile(Constant.METHOD_ENTER + Utility.getCurrentMethodName(1) + "(StatisticsManagerImpl)", null, Constant.LOGTYPE_INFO);
DetachedCriteria criteria = DetachedCriteria.For <Entity_CompanyDetails>();
criteria.Add(Restrictions.Eq("myConfigObj.currentBatch", Common.loggedUser.myConfigObj.currentBatch));
criteria.Add(Restrictions.Eq("myConfigObj.currentDegree", Common.loggedUser.myConfigObj.currentDegree));
ProjectionList projectionList = Projections.ProjectionList();
projectionList.Add(Projections.Count((Projections.Property("status"))));
projectionList.Add(Projections.GroupProperty(Projections.Property("status")));
criteria.SetProjection(projectionList);
criteria.AddOrder(Order.Desc(Projections.Property("status")));
IList list = persistence.findByCriteria(criteria);
if (list != null && list.Count > 0)
{
return(list);
}
else
{
return(null);
}
}
public void ProdsWDate()
{
ISession session = GetConfiguration().BuildSessionFactory().OpenSession();
Category category = new Category()
{
Id = 1
};
DetachedCriteria criteria = DetachedCriteria.For <Income>();
criteria.CreateAlias("Product", "p");
criteria.CreateAlias("p.Category", "c");
criteria.SetProjection(
Projections.ProjectionList()
.Add(Projections.GroupProperty("Product"))
.Add(Projections.GroupProperty("c.Name"))
.Add(Projections.Max("Date")));
//criteria.Add(Restrictions.Eq("p.Category", category));
criteria.AddOrder(NHibernate.Criterion.Order.Asc("c.Name"));
var list = criteria.GetExecutableCriteria(session).List();
foreach (var VARIABLE in list)
{
}
}
public void HavingUsingSqlFunctions_Concat()
{
using (ISession s = OpenSession())
{
using (ITransaction tx = s.BeginTransaction())
{
IList list = s.CreateCriteria(typeof(Person))
.SetProjection(Projections.ProjectionList().Add(
new SqlFunctionProjection("concat",
NHibernateUtil.String,
Projections.GroupProperty("Name"),
new ConstantProjection(" "),
Projections.GroupProperty("Name")
))
.Add(Projections.Conditional(Restrictions.IsNotNull(Projections.GroupProperty("Id")),
new ConstantProjection("yes"), new ConstantProjection("No")))
)
.Add(Restrictions.Eq(Projections.GroupProperty("Name"), "Fred"))
.Add(Restrictions.Gt("Id", 2)).List();
Assert.AreEqual(2, list.Count);
Assert.AreEqual("Fred Fred", ((object [])list[0])[0]);
tx.Commit();
}
}
}
public static object ObterQuantidadeCategoria()
{
Categoria CategoriaAlias = null;
Receita ReceitaAlias = null;
var query = Session.Current.QueryOver <Receita>(() => ReceitaAlias);
query = query.JoinAlias(() => ReceitaAlias.Categorias, () => CategoriaAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin);
DTODado dto = new DTODado();
var nomeAlias = Projections.Property(() => CategoriaAlias.Valor)
.WithAlias(() => dto.Label);
var categoriaGroup = Projections.GroupProperty(Projections.Property(() => CategoriaAlias.Valor));
var quantAlias = Projections.RowCount().WithAlias(() => dto.Quantidade);
var projections = Projections.ProjectionList()
.Add(nomeAlias)
.Add(quantAlias)
.Add(categoriaGroup)
;
var Dados = query.OrderBy(Projections.RowCount()).Asc()
.ThenBy(() => CategoriaAlias.Valor).Asc()
.Select(projections)
.TransformUsing(Transformers.AliasToBean <DTODado>())
.List <DTODado>();
return(Dados);
}
public void CountAndUnionByState()
{
IList results = session.CreateCriteria(typeof(County))
.AddOrder(Order.Asc("State"))
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("State"))
.Add(Projections.RowCount())
.Add(SpatialProjections.Union("Boundaries"))
)
.List();
Assert.AreEqual(2, results.Count);
object[] resultAA = (object[])results[0];
object[] resultBB = (object[])results[1];
int countAA = (int)resultAA[1];
int countBB = (int)resultBB[1];
IGeometry aggregatedAA = (IGeometry)resultAA[2];
IGeometry aggregatedBB = (IGeometry)resultBB[2];
IGeometry expectedAA = Wkt.Read("POLYGON((1 0, 1 1, 3 1, 3 0, 1 0))");
IGeometry expectedBB = Wkt.Read("POLYGON((1 1, 1 2, 3 2, 3 1, 1 1))");
Assert.AreEqual(2, countAA);
Assert.AreEqual(2, countBB);
Assert.IsTrue(expectedAA.Equals(aggregatedAA));
Assert.IsTrue(expectedBB.Equals(aggregatedBB));
}
public static object ObterRelatorioReceitas(ModeloDeCorteEnum corte, int[] autoresId = null, string dataInicial = null, string dataFinal = null, int TopItens = 5)
{
DTOGrafico dtoGrafico = new DTOGrafico();
try
{
Receita ReceitaAlias = null;
Autor AutorAlias = null;
var query = Session.Current.QueryOver <Receita>(() => ReceitaAlias);
query = query.JoinAlias(() => ReceitaAlias.Autor, () => AutorAlias);
query.Where(Restrictions.IsNull(Projections.Property(() => ReceitaAlias.Parente)));
if (autoresId != null && autoresId.Count() > 0)
{
query.Where(Restrictions.On(() => AutorAlias.Id).IsIn(autoresId));
}
if (dataInicial != null && dataFinal != null)
{
DateTime DataInicial = DateTime.Parse(dataInicial).Date;
DateTime DataFinal = DateTime.Parse(dataFinal).Date.AddHours(23).AddMinutes(59).AddSeconds(59);
query.Where(Restrictions.And(Restrictions.Ge(Projections.Property(() => ReceitaAlias.Data), DataInicial),
Restrictions.Le(Projections.Property(() => ReceitaAlias.Data), DataFinal)));
}
DTODado dto = new DTODado();
var nomeAutorProperty = Projections.Property(() => AutorAlias.Nome);
var nomeAutorGroup = Projections.GroupProperty(nomeAutorProperty);
var nomeAlias = nomeAutorGroup.WithAlias(() => dto.Label);
var quantAlias = Projections.RowCount().WithAlias(() => dto.Quantidade);
var corteAlias = new SQLCorteService().Executar(corte).WithAlias(() => dto.Corte);
var projections = Projections.ProjectionList()
.Add(nomeAlias)
.Add(quantAlias)
.Add(corteAlias)
;
var Dados = query.OrderBy(() => ReceitaAlias.Data).Asc()
.ThenBy(() => AutorAlias.Nome).Asc()
.Select(projections)
.TransformUsing(Transformers.AliasToBean <DTODado>())
.List <DTODado>();
ObterGrafico(dtoGrafico, Dados, TopItens);
}
catch (Exception)
{
throw;
}
return(dtoGrafico);
}
public void GroupByClauseHasParameterSet()
{
if (Dialect is FirebirdDialect)
{
Assert.Ignore("Firebird does not support complex group by expressions");
}
using (var session = OpenSession())
using (session.BeginTransaction())
{
MappingEntity mappingEntity = null;
Assert.DoesNotThrow(
() =>
session.QueryOver <Entity>().SelectList(
builder =>
builder.Select(
Projections.GroupProperty(
Projections.Conditional(
Restrictions.Eq(Projections.Property <Entity>(x => x.Name), ""),
Projections.Constant(1),
Projections.Constant(2)))
.WithAlias(() => mappingEntity.Count))
).TransformUsing(Transformers.AliasToBean <MappingEntity>()).List <MappingEntity>()
);
}
}
protected virtual DetachedCriteria GetCriteria()
{
var regionMask = SecurityContext.Administrator.RegionMask;
if (Region != null)
{
regionMask &= Region.Id;
}
var criteria = DetachedCriteria.For <RejectWaybillLog>();
criteria.CreateCriteria("ForClient", "c", JoinType.LeftOuterJoin)
.Add(Expression.Sql("{alias}.RegionCode & " + regionMask + " > 0"))
.CreateAlias("HomeRegion", "r", JoinType.LeftOuterJoin);
criteria.CreateAlias("Address", "a", JoinType.LeftOuterJoin);
criteria.CreateAlias("FromSupplier", "f", JoinType.LeftOuterJoin);
criteria.SetProjection(Projections.ProjectionList()
.Add(Projections.Count("Id").As("Count"))
.Add(Projections.GroupProperty("c.Id").As("ClientId"))
.Add(Projections.GroupProperty("a.Id").As("AddressId"))
.Add(Projections.GroupProperty("f.Id").As("SupplierId"))
.Add(Projections.GroupProperty("RejectReason").As("RejectReason"))
.Add(Projections.Property("c.Name").As("ClientName"))
.Add(Projections.Property("a.Value").As("AddressName"))
.Add(Projections.Property("r.Name").As("RegionName"))
.Add(Projections.Property("f.Name").As("SupplierName")));
criteria.Add(Expression.Ge("LogTime", Period.Begin.Date))
.Add(Expression.Le("LogTime", Period.End));
if (!string.IsNullOrEmpty(ClientText))
{
criteria.Add(Expression.Like("c.Name", ClientText, MatchMode.Anywhere));
}
return(criteria);
}
public void ProjectionAndGroupTest()
{
using var session = NHibernateHelper.OpenSession();
var actual = session.CreateCriteria <Cat>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.RowCount())
.Add(Projections.Avg("Weight"))
.Add(Projections.Max("Weight"))
.Add(Projections.GroupProperty("Sex")))
.List();
var expected = _cats
.GroupBy(c => c.Sex)
.Select(x => new
{
Count = x.Count(),
Avg = x.Average(c => c.Weight),
Max = x.Max(c => c.Weight),
Sex = x.Key,
})
.ToList();
actual.Count.ShouldBe(expected.Count);
foreach (var item in actual)
{
var row = (item as IList);
var expectedItem = expected.First(c => c.Sex == (char)row[3]);
row[0].ShouldBe(expectedItem.Count);
Math.Round((double)row[1], 3).ShouldBe(Math.Round(expectedItem.Avg, 3));
Math.Round((float)row[2], 3).ShouldBe(Math.Round(expectedItem.Max, 3));
}
}
public List <Transactions> GetTransaction(DateTime startDate, DateTime endDate, int tipo)
{
ICriteria crit = GetCriteria();
if (tipo == 0)
{
crit.Add(new LeExpression("Date", endDate));
crit.Add(new GtExpression("Date", startDate));
crit.Add(new LikeExpression("OrderNumber", "00000", MatchMode.Start));
crit.Add(new OrExpression(new EqExpression("Location", "01"), new EqExpression("Location", "09")));
crit.SetProjection(Projections.ProjectionList()
.Add(Projections.Sum("Quantity"))
.Add(Projections.GroupProperty("Product")));
}
else if (tipo == 1)
{
crit.Add(new LeExpression("Date", endDate));
crit.Add(new GtExpression("Date", startDate));
crit.Add(new LikeExpression("OrderNumber", "0000", MatchMode.Start));
crit.Add(new NotExpression(new LikeExpression("OrderNumber", "00000", MatchMode.Start)));
crit.Add(new OrExpression(new EqExpression("Location", "01"), new EqExpression("Location", "09")));
crit.SetProjection(Projections.ProjectionList()
.Add(Projections.Sum("Quantity"))
.Add(Projections.GroupProperty("Product")));
}
crit.SetResultTransformer(new NHibernate.Transform.AliasToBeanConstructorResultTransformer(typeof(Transactions).GetConstructors()[1]));
return(crit.List <Transactions>() as List <Transactions>);
}
public void GroupByClauseHasParameterSet()
{
if (Dialect is FirebirdDialect)
{
Assert.Ignore("Firebird does not support complex group by expressions");
}
// When not using a named prefix, the driver use positional parameters, causing parameterized
// expression used in group by and select to be not be considered as the same expression.
if (!((DriverBase)Sfi.ConnectionProvider.Driver).UseNamedPrefixInParameter)
{
Assert.Ignore("Cannot group by and select a parameterized expression with positional parameters");
}
using (var session = OpenSession())
using (session.BeginTransaction())
{
MappingEntity mappingEntity = null;
Assert.DoesNotThrow(
() =>
session.QueryOver <Entity>().SelectList(
builder =>
builder.Select(
Projections.GroupProperty(
Projections.Conditional(
Restrictions.Eq(Projections.Property <Entity>(x => x.Name), ""),
Projections.Constant(1),
Projections.Constant(2)))
.WithAlias(() => mappingEntity.Count))
).TransformUsing(Transformers.AliasToBean <MappingEntity>()).List <MappingEntity>()
);
}
}
public IList <AcompanhamentoProducaoM2Consulta> ObterAcompanhamentoProducaoM2PorData(DateTime dataRecebimento, int tipoProcessoId)
{
return(this.Session.CreateCriteria(typeof(Processo))
.CreateAlias("Lote", "Lote")
.CreateAlias("Lote.PacoteProcessado", "PP")
.Add(Restrictions.Between("PP.ArquivoRecebidoEm", dataRecebimento.Date, dataRecebimento.Date.AddDays(1)))
.Add(tipoProcessoId > 0 ? Restrictions.Eq("TipoDeProcesso.Id", tipoProcessoId) : Restrictions.IsNotNull("TipoDeProcesso"))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Alias(Projections.GroupProperty("PP.Id"), "PacoteId"))
.Add(Projections.Alias(Projections.GroupProperty("PP.Arquivo"), "NomeArquivo"))
.Add(Projections.Alias(Projections.Max("PP.ArquivoRecebidoEm"), "DataRecepcao"))
.Add(Projections.Alias(Projections.Max("PP.ArquivoImportadoEm"), "DataImportacao"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimIcr"), "DataOcr"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimIdentificacao"), "DataIdentificacao"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimDaMontagem"), "DataMontagem"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimDigitacao"), "DataDigitacao"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimValidacao"), "DataValidacao"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimProvaZero"), "DataProvaZero"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimFormalistica"), "DataFormalistica"))
.Add(Projections.Alias(Projections.Max("Lote.DataAguardandoAprovacao"), "DataAprovacao"))
.Add(Projections.Alias(Projections.Max("Lote.DataFimExportacao"), "DataExportacao"))
.Add(Projections.Alias(Projections.Max("Lote.DataFinalizacao"), "DataFim")))
.SetResultTransformer(Transformers.AliasToBean(typeof(AcompanhamentoProducaoM2Consulta)))
.AddOrder(Order.Asc("PacoteId"))
.List <AcompanhamentoProducaoM2Consulta>());
}
public IDictionary <string, int> getYearlyStudentMap()
{
Utility.logFile(Constant.METHOD_ENTER + Utility.getCurrentMethodName(1) + "(StatisticsManagerImpl)", null, Constant.LOGTYPE_INFO);
DetachedCriteria criteria = DetachedCriteria.For <Entity_PlacementDetails>();
criteria.CreateAlias("stuObj", "student");
ProjectionList projectionList = Projections.ProjectionList();
projectionList.Add(Projections.GroupProperty(Projections.Property("student.myConfigObj.currentBatch")));
projectionList.Add(Projections.Count(Projections.Property("detailsId")));
criteria.SetProjection(projectionList);
criteria.Add(Restrictions.Eq("student.myConfigObj.currentDegree", Common.loggedUser.myConfigObj.currentDegree));
IList list = persistence.findByCriteria(criteria);
if (list != null && list.Count > 0)
{
IDictionary <string, int> resultMap = new Dictionary <string, int>();
foreach (Object[] item in list)
{
resultMap.Add(item[0].ToString(), Convert.ToInt32(item[1]));
}
return(resultMap);
}
else
{
return(null);
}
}
public static DetachedCriteria[] GetPermissionCriteriaByCategory(User user, string category)
{
DetachedCriteria[] criteria = new DetachedCriteria[2];
DetachedCriteria upSubCriteria = DetachedCriteria.For <UserPermission>();
upSubCriteria.CreateAlias("User", "u");
upSubCriteria.CreateAlias("Permission", "pm");
upSubCriteria.CreateAlias("pm.Category", "pmc");
upSubCriteria.Add(Expression.Eq("pmc.Type", category));
upSubCriteria.Add(Expression.Eq("u.Code", user.Code));
upSubCriteria.SetProjection(Projections.ProjectionList().Add(Projections.GroupProperty("pm.Code")));
DetachedCriteria rpSubCriteria = DetachedCriteria.For <RolePermission>();
rpSubCriteria.CreateAlias("Role", "r");
rpSubCriteria.CreateAlias("Permission", "pm");
rpSubCriteria.CreateAlias("pm.Category", "pmc");
rpSubCriteria.Add(Expression.Eq("pmc.Type", category));
rpSubCriteria.SetProjection(Projections.ProjectionList().Add(Projections.GroupProperty("pm.Code")));
DetachedCriteria urSubCriteria = DetachedCriteria.For <UserRole>();
urSubCriteria.CreateAlias("User", "u");
urSubCriteria.CreateAlias("Role", "r");
urSubCriteria.Add(Expression.Eq("u.Code", user.Code));
urSubCriteria.SetProjection(Projections.ProjectionList().Add(Projections.GroupProperty("r.Code")));
rpSubCriteria.Add(Subqueries.PropertyIn("r.Code", urSubCriteria));
criteria[0] = upSubCriteria;
criteria[1] = rpSubCriteria;
return(criteria);
}
public void ProjectionsExpressions()
{
IProjection p = Projections.Avg("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Count("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.CountDistinct("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.GroupProperty("Name");
NHAssert.IsSerializable(p);
p = Projections.Id();
NHAssert.IsSerializable(p);
p = Projections.Max("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Min("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Property("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.RowCount();
NHAssert.IsSerializable(p);
p = Projections.Sum("StudentNumber");
NHAssert.IsSerializable(p);
p = Projections.Alias(Projections.Count("StudentNumber"), "alias");
NHAssert.IsSerializable(p);
p = Projections.Distinct(Projections.Id());
NHAssert.IsSerializable(p);
p = Projections.ProjectionList().Add(Projections.Max("StudentNumber"));
NHAssert.IsSerializable(p);
}
protected override void DoSearch()
{
if (SearchEvent != null)
{
DateTime startDate = DateTime.Parse(this.tbStartDate.Text.Trim());
DetachedCriteria selectCriteria = DetachedCriteria.For(typeof(RollingForecast));
SecurityHelper.SetPartySearchCriteria(selectCriteria, "Supplier.Code", this.CurrentUser.Code);
if (this.tbSupplier.Text.Trim() != string.Empty)
{
selectCriteria.Add(Expression.Eq("Supplier.Code", this.tbSupplier.Text.Trim()));
}
selectCriteria.Add(Expression.Ge("Date", startDate));
selectCriteria.Add(Expression.Lt("Date", startDate.AddDays(140)));
selectCriteria.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("Supplier").As("Supplier"))
.Add(Projections.GroupProperty("Item").As("Item"))
.Add(Projections.Max("Id").As("Id")));
DetachedCriteria selectCountCriteria = CloneHelper.DeepClone <DetachedCriteria>(selectCriteria);
selectCountCriteria.SetProjection(Projections.Count("Id"));
SearchEvent((new object[] { selectCriteria, selectCountCriteria, startDate, this.IsExport }), null);
}
}
public async Task InvalidOrderByClauseAsync()
{
using (var session = OpenSession())
{
var recipientId = Guid.NewGuid();
await(session.CreateCriteria(typeof(FundingCategory), "fc")
.CreateCriteria("FundingPrograms", "fp")
.CreateCriteria("Projects", "p", JoinType.LeftOuterJoin)
.Add(
Restrictions.Disjunction()
.Add(Restrictions.Eq("fp.Recipient.Id", recipientId))
.Add(Restrictions.Eq("p.Recipient.Id", recipientId))
)
.SetProjection(
Projections.ProjectionList()
.Add(Projections.GroupProperty("fc.Name"), "fcn")
.Add(Projections.Sum("fp.ObligatedAmount"), "fpo")
.Add(Projections.Sum("p.ObligatedAmount"), "po")
)
.AddOrder(Order.Desc("fpo"))
.AddOrder(Order.Desc("po"))
.AddOrder(Order.Asc("fcn"))
.ListAsync <object[]>());
}
}
private IList <LocationTransaction> GetLocationTransaction(int mark)
{
DetachedCriteria criteria = DetachedCriteria.For(typeof(LocationTransaction));
criteria.Add(Expression.Eq("IsSubcontract", false));
criteria.Add(Expression.In("TransactionType", new string[] {
BusinessConstants.CODE_MASTER_LOCATION_TRANSACTION_TYPE_VALUE_RCT_WO,
BusinessConstants.CODE_MASTER_LOCATION_TRANSACTION_TYPE_VALUE_ISS_WO,
BusinessConstants.CODE_MASTER_LOCATION_TRANSACTION_TYPE_VALUE_ISS_WO_BF
}));
criteria.Add(Expression.Gt("Id", mark));
criteria.SetProjection(Projections.ProjectionList()
.Add(Projections.Max("Id").As("Id"))
.Add(Projections.GroupProperty("OrderNo").As("OrderNo"))
.Add(Projections.GroupProperty("OrderDetailId").As("OrderDetailId"))
.Add(Projections.GroupProperty("OrderLocationTransactionId").As("OrderLocationTransactionId"))
.Add(Projections.GroupProperty("TransactionType").As("TransactionType"))
.Add(Projections.GroupProperty("Item").As("Item"))
.Add(Projections.GroupProperty("Location").As("Location"))
.Add(Projections.Sum("Qty").As("Qty"))
.Add(Projections.GroupProperty("EffectiveDate").As("EffectiveDate"))
.Add(Projections.GroupProperty("PartyFrom").As("PartyFrom"))
.Add(Projections.GroupProperty("PartyTo").As("PartyTo"))
.Add(Projections.GroupProperty("RefLocation").As("RefLocation")));
criteria.SetResultTransformer(Transformers.AliasToBean(typeof(LocationTransaction)));
return(criteriaMgr.FindAll <LocationTransaction>(criteria));
}
public void HavingUsingSqlFunctions_Concat()
{
if (TestDialect.HasBrokenTypeInferenceOnSelectedParameters)
{
Assert.Ignore("Current dialect does not support this test");
}
using (ISession s = OpenSession())
using (ITransaction tx = s.BeginTransaction())
{
IList list = s.CreateCriteria(typeof(Person))
.SetProjection(
Projections.ProjectionList()
.Add(
new SqlFunctionProjection(
"concat",
NHibernateUtil.String,
Projections.GroupProperty("Name"),
new ConstantProjection(" "),
Projections.GroupProperty("Name")))
.Add(
Projections.Conditional(
Restrictions.IsNotNull(Projections.GroupProperty("Id")), new ConstantProjection("yes"), new ConstantProjection("No"))))
.Add(Restrictions.Eq(Projections.GroupProperty("Name"), "Fred"))
.Add(Restrictions.Gt("Id", 2))
.List();
Assert.AreEqual(2, list.Count);
Assert.AreEqual("Fred Fred", ((object[])list[0])[0]);
tx.Commit();
}
}
public ActionResult List()
{
DetachedCriteria criteria = DetachedCriteria.For(typeof(User));
criteria.CreateAlias("Orders", "o");
criteria.SetProjection(
Projections.ProjectionList()
.Add(Projections.Count("Orders").As("Quantity"))
.Add(Projections.GroupProperty("Id").As("UserId")));
criteria.SetResultTransformer(Transformers.AliasToBean(typeof(UserOrdersReportRow)));
IList <UserOrdersReportRow> userOrders = daoTemplate.FindByCriteria <UserOrdersReportRow>(criteria);
DetachedCriteria usersCriteria = DetachedCriteria.For(typeof(User)).SetFetchMode("Orders", FetchMode.Select);
IList <User> users = daoTemplate.FindByCriteria <User>(usersCriteria);
IList <UserListItemViewModel> viewModels = new List <UserListItemViewModel>(users.Count);
foreach (User user in users)
{
UserOrdersReportRow row = userOrders.SingleOrDefault(x => x.UserId == user.Id);
int quantity = row == null ? 0 : row.Quantity;
viewModels.Add(new UserListItemViewModel()
{
Quantity = quantity,
User = user,
Profit = user.Profit(),
Summ = user.Orders.Sum(x => x.SummWithDiscount)
});
}
return(View(viewModels));
}
public void SimpleQueries()
{
using (ISession session = SessionFactory.OpenSession())
{
int result = (int)session.CreateCriteria(typeof(Customer)).SetProjection(Projections.RowCount()).UniqueResult();
session.CreateCriteria(typeof(Customer))
.CreateCriteria("Orders", "o")
.Add(Restrictions.Gt("o.Value", 90m))
.List <Customer>();
session.CreateCriteria <Customer>()
.CreateCriteria("Orders", "o")
.SetProjection(Projections.RowCount())
.SetProjection(Projections.GroupProperty("LastName"))
.SetProjection(Projections.Max("o.Value"))
.List();
foreach (var customer in session.CreateCriteria <Customer>().List <Customer>())
{
var orderCound = customer.Orders.Count;
}
Assert.AreEqual(10, result);
}
}
protected void Page_Load(object sender, EventArgs e)
{
this.tbFlowOrLoc.ServiceParameter = "string:" + this.CurrentUser.Code + ",bool:true,bool:true,bool:true,bool:true,bool:true,bool:true,string:" + BusinessConstants.PARTY_AUTHRIZE_OPTION_TO;
this.tbFlowOrLoc.DataBind();
this.tbFlow.ServiceParameter = "string:" + this.CurrentUser.Code + ",bool:true,bool:true,bool:true,bool:true,bool:true,bool:true,string:" + BusinessConstants.PARTY_AUTHRIZE_OPTION_TO;
this.tbWinTime.Attributes.Add("onclick", "WdatePicker({dateFmt:'yyyy-MM-dd HH:mm',lang:'" + this.CurrentUser.UserLanguage + "'})");
this.tbWinTime.Attributes["onchange"] += "setStartTime();";
this.cbIsUrgent.Attributes["onchange"] += "setStartTime();";
if (!IsPostBack)
{
this.ucShift.Date = DateTime.Today;
DetachedCriteria criteria = DetachedCriteria.For <MrpRunLog>();
criteria.SetProjection(Projections.ProjectionList().Add(Projections.GroupProperty("RunDate")));
criteria.AddOrder(Order.Desc("RunDate"));
IList <DateTime> list = TheCriteriaMgr.FindAll <DateTime>(criteria, 0, 30);
List <string> effDate = list.Select(l => l.ToString("yyyy-MM-dd")).ToList();
this.ddlDate.DataSource = effDate;
this.ddlDate.DataBind();
}
}
public void AggregatingHirearchyWithCount()
{
var root = new Key {
Id = 1, Area = 2
};
DetachedCriteria projection = DetachedCriteria.For <TreeNode>("child")
.Add(Restrictions.Eq("Parent.id", root))
.Add(Restrictions.Gt("Key.Id", 0))
.Add(Restrictions.Eq("Type", NodeType.Blue))
.CreateAlias("DirectChildren", "grandchild")
.SetProjection(
Projections.ProjectionList()
.Add(Projections.GroupProperty("child.Key.Id"))
.Add(Projections.GroupProperty("child.Key.Area"))
.Add(Projections.Count(Projections.Property("grandchild.Key.Id")))
);
using (var s = Sfi.OpenSession())
using (var tx = s.BeginTransaction())
{
var criteria = projection.GetExecutableCriteria(s);
var list = criteria.List();
Assert.AreEqual(1, list.Count);
var tuple = (object[])list[0];
Assert.AreEqual(11, tuple[0]);
Assert.AreEqual(2, tuple[1]);
Assert.AreEqual(1, tuple[2]);
tx.Commit();
}
}
public IList <RecentRequestsDto> GetRecentRequests(int userId)
{
SimpleExpression statusActive = Restrictions.Eq(Projections.Property("Request.StatusId"), 1);
SimpleExpression statusUnderReview = Restrictions.Eq(Projections.Property("Request.StatusId"), 3);
ICriteria criteria = Session
.CreateCriteria(typeof(TimeEntry))
.CreateAlias("Request", "Request")
.CreateAlias("Request.WrmsSystem", "RequestWrmsSystem")
.Add(Restrictions.Eq("UserId", userId))
.Add(Restrictions.Or(statusActive, statusUnderReview))
// SELECT
.SetProjection(Projections.ProjectionList()
.Add(
Projections.Distinct(
Projections.GroupProperty(Projections.Property("Request.ShortDesc"))),
"RequestName")
.Add(Projections.Max((Projections.Property("LastUpdateTimeStamp"))))
.Add(Projections.GroupProperty(Projections.Property("RequestWrmsSystem.Code")),
"SystemName")
.Add(Projections.GroupProperty(Projections.Property("Request.Id")), "Id")
);
//GROUP BY, ORDER BY
criteria.AddOrder(Order.Desc(Projections.Max("LastUpdateTimeStamp")))
.SetMaxResults(20)
// CONVERT
.SetResultTransformer(Transformers.AliasToBean(typeof(RecentRequestsDto)));
return(criteria.List <RecentRequestsDto>());
}