public Int64 GetUIDLocal(ISession session, String labelSystemName, Boolean max)
{
String imapDatabaseField;
Int64 lastDatabaseUID;
switch (labelSystemName)
{
case "Trash":
imapDatabaseField = "UidTrash";
break;
case "Junk":
imapDatabaseField = "UidSpam";
break;
default:
imapDatabaseField = "UidAll";
break;
}
if (max) //maximo
{
lastDatabaseUID = session.CreateCriteria <MailEntity>()
.Add(Restrictions.Eq("MailAccountEntity", this.Entity))
.SetProjection(Projections.Max(imapDatabaseField))
.UniqueResult <Int64>();
}
else //minimo
{
lastDatabaseUID = session.CreateCriteria <MailEntity>()
.Add(Restrictions.Eq("MailAccountEntity", this.Entity))
.SetProjection(Projections.Min(imapDatabaseField))
.UniqueResult <Int64>();
}
return(lastDatabaseUID); //devuelve 0 si no existen mails
}
public IEnumerable <ValoresDiarios> GetAggregatesDiarios(int dispositivo, string sensorCode, DateTime desde, DateTime hasta)
{
var sensorDao = new SensorDAO();
var sensor = sensorDao.FindByCode(dispositivo, sensorCode);
if (sensor == null)
{
return(new List <ValoresDiarios>(0));
}
ValoresDiarios valoresDiarios = null;
var byDate = Projections.SqlFunction("date", NHibernateUtil.Date,
Projections.Group <Medicion>(a => a.FechaMedicion));
return(Session.QueryOver <Medicion>()
.Where(m => m.Dispositivo.Id == dispositivo && m.Sensor.Id == sensor.Id)
.And(m => m.FechaMedicion >= desde && m.FechaMedicion <= hasta)
.SelectList(x => x
.Select(Projections.Max <Medicion>(m => m.ValorNum1).WithAlias(() => valoresDiarios.Max))
.Select(Projections.Min <Medicion>(m => m.ValorNum1).WithAlias(() => valoresDiarios.Min))
.Select(Projections.Avg <Medicion>(m => m.ValorNum1).WithAlias(() => valoresDiarios.Avg))
.Select(Projections.GroupProperty(byDate).WithAlias(() => valoresDiarios.Date))
)
.OrderBy(byDate).Asc
.TransformUsing(Transformers.AliasToBean <ValoresDiarios>())
.List <ValoresDiarios>());
}
/// <summary>
/// Gets aggregate activity for each specified transport company.
/// </summary>
/// <param name="desde"></param>
/// <param name="hasta"></param>
/// <param name="ids"></param>
/// <returns></returns>
public IEnumerable <TransportActivity> GetTransportActivities(DateTime desde, DateTime hasta, List <int> ids)
{
TransportActivity ta = null;
DetachedCriteria dc = GetDatamartDetachedCriteria(ids.ToArray()).FilterBeginBetween(desde, hasta);
ProjectionList pl = Projections.ProjectionList();
pl.Add(Projections.Sum <Datamart>(dm => dm.MovementHours).WithAlias(() => ta.HsActivos));
pl.Add(Projections.Sum <Datamart>(dm => dm.StoppedHours).WithAlias(() => ta.HsInactivos));
pl.Add(Projections.Sum <Datamart>(dm => dm.InfractionMinutes).WithAlias(() => ta.HsInfraccion));
pl.Add(Projections.Sum <Datamart>(dm => dm.Infractions).WithAlias(() => ta.Infracciones));
pl.Add(Projections.Sum <Datamart>(dm => dm.Kilometers).WithAlias(() => ta.Recorrido));
pl.Add(Projections.Max <Datamart>(dm => dm.MaxSpeed).WithAlias(() => ta.VelocidadMaxima));
pl.Add(Projections.Avg <Datamart>(dm => dm.AverageSpeed).WithAlias(() => ta.VelocidadPromedio));
pl.Add(Projections.Group <Datamart>(dm => dm.Vehicle.Id).WithAlias(() => ta.IdVehiculo));
ICriteria crit = GetDatamartCriteria(0, dc, pl, null, typeof(TransportActivity));
IList <TransportActivity> results = crit.List <TransportActivity>();
var cocheDAO = new CocheDAO();
foreach (TransportActivity r in results)
{
Coche coche = cocheDAO.FindById(r.Id);
r.HsInfraccion /= 60;
r.Transport = coche.Transportista.Descripcion;
r.CentroDeCostos = coche.CentroDeCostos != null ? coche.CentroDeCostos.Descripcion : null;
r.TipoVehiculo = coche.TipoCoche != null ? coche.TipoCoche.Descripcion : null;
}
return(results);
}
public int GetMaxIdControl()
{
List <IProjection> lista = new List <IProjection>();
lista.Add(Projections.Max("IdControl"));
return(GetUniqueProjection(lista.ToArray()));
}
public DateTime?GetLastDateClosing()
{
ICriteria criteria = Session.CreateCriteria(typeof(TRecPeriod))
.SetProjection(Projections.Max("PeriodTo"));
object obj = criteria.UniqueResult();
if (obj != null)
{
return(Convert.ToDateTime(obj));
}
else
{
return(null);
}
DateTime dt = criteria.FutureValue <DateTime>().Value;
return(dt);
try
{
}
catch (Exception)
{
return(null);
}
}
private int GetMaxParentDisplayOrder(Document parent, ISession session)
{
return(session.QueryOver <Document>()
.Where(doc => doc.Parent.Id == parent.Id)
.Select(Projections.Max <Document>(d => d.DisplayOrder))
.SingleOrDefault <int>());
}
public virtual void SavePhotoInfo(Photo photo)
{
// Obtain current NHibernate session.
ISession session = this._sessionManager.OpenSession();
// can't seem to get this to work automagically
if (photo.Id == -1)
{
int order;
if (photo.Album.PhotoCount > 0)
{
order = Convert.ToInt32(session.CreateCriteria(typeof(Photo))
.SetProjection(Projections.Max("Id")).List()[0]) + 1;
}
else
{
order = 1;
}
photo.Order = order;
session.Save(photo);
UpdatePhotoCount(photo.Album.Id);
}
else
{
session.SaveOrUpdate(photo);
UpdatePhotoCount(photo.Album.Id);
}
}
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 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);
}
}
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 void TestReturnedOperations()
{
ProjectionExitOperationFactory factory = ProjectionExitOperationFactory.GetFactory();
var sessionFactory = Mock <ISessionFactoryImplementor>();
using (Mocks.Record())
{
}
using (Mocks.Playback())
{
Assert.IsInstanceOf(typeof(RowCountExitOperation),
factory.GetProjectionExitOperation(Projections.RowCount(), sessionFactory));
Assert.IsInstanceOf(typeof(AggregateExitOperation),
factory.GetProjectionExitOperation(Projections.Max("foo"), sessionFactory));
Assert.IsInstanceOf(typeof(AggregateExitOperation),
factory.GetProjectionExitOperation(Projections.Min("foo"), sessionFactory));
Assert.IsInstanceOf(typeof(AggregateExitOperation),
factory.GetProjectionExitOperation(Projections.Sum("foo"), sessionFactory));
try
{
factory.GetProjectionExitOperation(Projections.Avg("foo"), sessionFactory);
Assert.Fail("example of one that we don't yet support");
}
catch (ArgumentException e)
{
// good
}
}
}
private int GetMaxParentDisplayOrder(Document document, ISession session)
{
if (document.Parent != null)
{
return(session.QueryOver <Document>()
.Where(doc => doc.Parent.Id == document.Parent.Id)
.Select(Projections.Max <Document>(d => d.DisplayOrder))
.SingleOrDefault <int>());
}
if (document is MediaCategory)
{
List <MediaCategory> documentsByParent = _getDocumentParents.GetDocumentsByParent <MediaCategory>(null)
.Where(category => category != document).ToList();
return(documentsByParent.Any()
? documentsByParent.Max(category => category.DisplayOrder) + 1
: 0);
}
if (document is Layout)
{
List <Layout> documentsByParent = _getDocumentParents.GetDocumentsByParent <Layout>(null)
.Where(layout => layout != document).ToList();
return(documentsByParent.Any()
? documentsByParent.Max(category => category.DisplayOrder) + 1
: 0);
}
else
{
List <Webpage> documentsByParent = _getDocumentParents.GetDocumentsByParent <Webpage>(null)
.Where(webpage => webpage != document).ToList();
return(documentsByParent.Any()
? documentsByParent.Max(category => category.DisplayOrder) + 1
: 0);
}
}
public List <long> GetTubineBladesWithMaxTempInAVGorMAX()
{
using (ITransaction transaction = session.BeginTransaction())
{
List <long> results = null;
TurbineBlade turbineBladeAlias = null;
QueryOver <TurbineBlade> subqueryAVG =
QueryOver.Of <TurbineBlade>()
.SelectList(list => list.SelectMax(tb => tb.MaxTemp));
QueryOver <TurbineBlade> subqueryMAX =
QueryOver.Of <TurbineBlade>()
.SelectList(list => list.SelectMax(tb => tb.MaxTemp));
QueryOver <TurbineBlade> subqueryAVGMAX =
QueryOver.Of <TurbineBlade>(() => turbineBladeAlias)
.Select(Projections.Avg(Projections.Property(() => turbineBladeAlias.MaxTemp)))
.Select(Projections.Max(Projections.Property(() => turbineBladeAlias.MaxTemp)));
results = session.QueryOver <TurbineBlade>(() => turbineBladeAlias)
//.Where(() => turbineBladeAlias.MaxTemp.IsIn(new object[] {subqueryMAX, subqueryAVG}))
.WithSubquery
.WhereProperty(() => turbineBladeAlias.MaxTemp).In(subqueryAVGMAX)
.Select(tb => tb.Id)
.List <long>().ToList();
transaction.Commit();
return(results);
}
}
private Comment AddPost(string text)
{
Comment newComment = new Comment();
using (ISession session = NHibernateSession.OpenSession())
{
HttpContextWrapper context = (HttpContextWrapper)Request.Properties["MS_HttpContext"];
var max = session.CreateCriteria <Comment>().SetProjection(Projections.Max("rgt")).UniqueResult();
int lft = max == null?0:(int)max;
newComment.Text = text;
newComment.lft = lft + 1;
newComment.rgt = lft + 2;
newComment.indent = 0;
newComment.CreationDate = DateTime.Now;
newComment.Creator = session.QueryOver <UserProfile>().Where(u => u.UserName == context.User.Identity.Name).List()[0];
newComment.CommenterName = context.User.Identity.Name;
using (var tx = session.BeginTransaction())
{
session.SaveOrUpdate(newComment);
tx.Commit();
}
}
return(newComment);
}
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 int PostConsejeriaNew([FromBody] ConsejeriaNewDatos consejeriaNewDatos)
{
var usuaria = this.consejeriaDtoMapper.MapDtoToUsuaria(new UsuariaDto {
Apellido = consejeriaNewDatos.Apellido,
Direccion = consejeriaNewDatos.Direccion,
Edad = consejeriaNewDatos.Edad,
Nombre = consejeriaNewDatos.Nombre,
NacionalidadId = consejeriaNewDatos.NacionalidadId,
FechaNacimiento = consejeriaNewDatos.FechaNacimiento,
Telefono = consejeriaNewDatos.Telefono
});
this.sessionFactory.SaveOrUpdateEntity(usuaria);
var criteria = this.sessionFactory.CreateCriteria <ConsejeriaEntidad>();
criteria.SetProjection(Projections.Max("Numero"));
var numero = (int)criteria.UniqueResult();
var consejeria = this.consejeriaDtoMapper.MapDtoToConsejeria(new ConsejeriaDto {
FechaIngreso = DateTime.Now,
Numero = numero + 1,
Observacion = consejeriaNewDatos.Observacion,
Usuarie1Id = consejeriaNewDatos.Usuarie1Id,
Usuarie2Id = consejeriaNewDatos.Usuarie2Id,
});
consejeria.Usuaria = usuaria;
this.sessionFactory.SaveOrUpdateEntity(consejeria);
return(consejeria.Id);
}
public virtual IList <LatestChangedResourceResult> GetAllLatestChangedResource(IEnumerable <long> projectIdList)
{
ResourceVersion resourceVersionAlias = null;
Resource resourceAlias = null;
Project projectAlias = null;
ResourceVersion resourceVersionAlias2 = null;
Resource resourceAlias2 = null;
LatestChangedResourceResult resultAlias = null;
var result = GetSession().QueryOver(() => resourceVersionAlias)
.JoinAlias(() => resourceVersionAlias.Resource, () => resourceAlias)
.JoinAlias(() => resourceAlias.Project, () => projectAlias)
.WhereRestrictionOn(() => resourceAlias.Project.Id).IsInG(projectIdList)
.And(() => projectAlias.IsRemoved == false)
.And(x => x.Id == resourceAlias.LatestVersion.Id /*&& !resourceAlias.IsRemoved*/)
.WithSubquery.Where(() => resourceVersionAlias.CreateTime == QueryOver.Of(() => resourceAlias2)
.JoinAlias(() => resourceAlias2.LatestVersion, () => resourceVersionAlias2)
.Where(() => resourceAlias2.Project.Id == resourceAlias.Project.Id)
.Select(Projections.Max(() => resourceVersionAlias2.CreateTime))
.Take(1)
.As <DateTime>())
.SelectList(list => list
.SelectGroup(() => resourceAlias.Project.Id).WithAlias(() => resultAlias.ProjectId)
.SelectMax(() => resourceVersionAlias.CreateTime).WithAlias(() => resultAlias.CreateTime)
.SelectMin(() => resourceVersionAlias.CreatedByUser.Id).WithAlias(() => resultAlias.CreatedByUserId))
.TransformUsing(Transformers.AliasToBean <LatestChangedResourceResult>())
.List <LatestChangedResourceResult>();
return(result);
}
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);
}
public async Task CanPageAndSortResultsWithParametersAndFiltersAsync()
{
using (ISession s = OpenSession())
{
s.EnableFilter("ExampleFilter").SetParameter("WeightVal", 100);
DetachedCriteria dc = DetachedCriteria.For <Person>().CreateCriteria("Pets", "pets")
.SetProjection(Projections.Max("pets.Weight"))
.Add(Restrictions.Eq("pets.Weight", 10.0));
ICriteria c = s.CreateCriteria(typeof(Person))
.SetProjection(Projections.ProjectionList()
.Add(Projections.SubQuery(dc), "Weight")
.Add(Projections.Property("Name"), "Name"))
.Add(Restrictions.Eq("Name", "Joe"));
c.SetResultTransformer(Transformers.AliasToBean(typeof(HeaviestPet)));
c.SetMaxResults(1);
c.AddOrder(new Order("Id", true));
IList <HeaviestPet> list = await(c.ListAsync <HeaviestPet>());
Assert.AreEqual(1, list.Count);
foreach (HeaviestPet pet in list)
{
Assert.AreEqual("Joe", pet.Name);
Assert.AreEqual(10.0, pet.Weight);
}
}
}
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 static IList <DriverPosition> GetLastPointForRouteLists(IUnitOfWork uow, int[] routeListsIds, DateTime?beforeTime = null)
{
Track trackAlias = null;
TrackPoint subPoint = null;
DriverPosition result = null;
var lastTimeTrackQuery = QueryOver.Of <TrackPoint>(() => subPoint)
.Where(() => subPoint.Track.Id == trackAlias.Id);
if (beforeTime.HasValue)
{
lastTimeTrackQuery.Where(p => p.TimeStamp <= beforeTime);
}
lastTimeTrackQuery.Select(Projections.Max(() => subPoint.TimeStamp));
return(uow.Session.QueryOver <TrackPoint>()
.JoinAlias(p => p.Track, () => trackAlias)
.Where(() => trackAlias.RouteList.Id.IsIn(routeListsIds))
.WithSubquery.WhereProperty(p => p.TimeStamp).Eq(lastTimeTrackQuery)
.SelectList(list => list
.Select(() => trackAlias.Driver.Id).WithAlias(() => result.DriverId)
.Select(() => trackAlias.RouteList.Id).WithAlias(() => result.RouteListId)
.Select(x => x.TimeStamp).WithAlias(() => result.Time)
.Select(x => x.Latitude).WithAlias(() => result.Latitude)
.Select(x => x.Longitude).WithAlias(() => result.Longitude)
).TransformUsing(Transformers.AliasToBean <DriverPosition>())
.List <DriverPosition>());
}
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 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>());
}
protected override void SetCriteria()
{
DetachedCriteria criteria = DetachedCriteria.For(typeof(CycleCountResult));
criteria.CreateAlias("CycleCount", "cc");
criteria.CreateAlias("cc.Location", "l");
#region Customize
SecurityHelper.SetRegionSearchCriteria(criteria, "l.Region.Code", this.CurrentUser.Code); //区域权限
OrderHelper.SetActiveOrderStatusCriteria(criteria, "cc.Status"); //状态
#endregion
#region Select Parameters
CriteriaHelper.SetPartyCriteria(criteria, "l.Region.Code", this._criteriaParam);
CriteriaHelper.SetLocationCriteria(criteria, "cc.Location.Code", this._criteriaParam);
CriteriaHelper.SetStartDateCriteria(criteria, "cc.EffectiveDate", this._criteriaParam);
CriteriaHelper.SetEndDateCriteria(criteria, "cc.EffectiveDate", this._criteriaParam);
CriteriaHelper.SetStorageBinCriteria(criteria, "StorageBin.Code", this._criteriaParam);
CriteriaHelper.SetItemCriteria(criteria, "Item.Code", this._criteriaParam);
CriteriaHelper.SetOrderNoCriteria(criteria, "CycleCount.Code", this._criteriaParam, MatchMode.Start);
#endregion
#region Projections
ProjectionList projectionList = Projections.ProjectionList()
.Add(Projections.Max("Id").As("Id"))
.Add(Projections.Sum("Qty").As("Qty"))
.Add(Projections.Sum("InvQty").As("InvQty"))
.Add(Projections.Sum("DiffQty").As("DiffQty"))
.Add(Projections.Count("HuId").As("Cartons"))
.Add(Projections.GroupProperty("Item").As("Item"));
if (!this._criteriaParam.ClassifiedOrderNo)
{
projectionList.Add(Projections.GroupProperty("CycleCount").As("CycleCount"));
}
if (!this._criteriaParam.ClassifiedLocation)
{
projectionList.Add(Projections.GroupProperty("cc.Location").As("Location"));
}
if (!this._criteriaParam.ClassifiedBin)
{
projectionList.Add(Projections.GroupProperty("StorageBin").As("StorageBin"));//库格
}
if (!this._criteriaParam.ClassifiedHuId)
{
projectionList.Add(Projections.GroupProperty("HuId").As("HuId")); //条码
projectionList.Add(Projections.GroupProperty("LotNo").As("LotNo")); //批号
projectionList.Add(Projections.GroupProperty("ReferenceLocation").As("ReferenceLocation")); //参考库位
projectionList.Add(Projections.GroupProperty("DiffReason").As("DiffReason")); //差异原因
}
criteria.SetProjection(projectionList);
criteria.SetResultTransformer(Transformers.AliasToBean(typeof(CycleCountResult)));
#endregion
DetachedCriteria selectCountCriteria = CloneHelper.DeepClone <DetachedCriteria>(criteria);
selectCountCriteria.SetProjection(Projections.Count("Id"));
SetSearchCriteria(criteria, selectCountCriteria);
}
public void maximum()
{
var mostItemsInStock = Session.CreateCriteria <Product>()
.SetProjection(Projections.Max("UnitsInStock"))
.UniqueResult <int>();
Assert.AreEqual(_products.Max(p => p.UnitsInStock), mostItemsInStock);
}
public void maximum()
{
var mostItemsInStock = Session.QueryOver <Product>()
.Select(Projections.Max <Product>(p => p.UnitsInStock))
.SingleOrDefault <int>();
Assert.AreEqual(_products.Max(p => p.UnitsInStock), mostItemsInStock);
}
public override DateTime?ObterDataUltimaTransacaoDaConta(int idConta)
{
return(mSessao
.QueryOver <Transacao>()
.Where(x => x.QualConta.Id == idConta)
.Select(Projections.Max <Transacao>(x => x.DataHora))
.SingleOrDefault <DateTime?>());
}
/// <summary>
/// Obtem o útimo codigo usado e retorna ele +1
/// </summary>
/// <returns>
/// int
/// </returns>
/// <example>
/// var Codigo = NextCod();
/// </example>
public int NextCode()
{
int nextCod = session.QueryOver <Despesas>()
.Select(Projections.Max <Despesas>(x => x.CodigoDespesa))
.SingleOrDefault <int>();
return(nextCod + 1);
}
public void TestMax()
{
AggregateExitOperation exitOp = new AggregateExitOperation(Projections.Max("value"));
IList result = exitOp.Apply(data);
Assert.AreEqual(5, ((MyInt)result[0]).Value);
}
public decimal FindMaxDiscountPerList(PriceList pl)
{
ICriteria crit = GetCriteria();
crit.Add(Expression.Eq("PriceList", pl));
crit.Add(Expression.Not(Expression.Eq("DistributorStatus", DistributorStatus.Disable)));
crit.SetProjection(Projections.ProjectionList().Add(Projections.Max("Discount")));
object val = crit.UniqueResult();
return val != null ? Convert.ToDecimal(val) : 0;
}
