public override void UpdateNodes()
{
DeliveryPoint deliveryPointAlias = null;
Counterparty counterpartyAlias = null;
ClientDeliveryPointVMNode resultAlias = null;
ContactAndPhonesView contactAlias = null;
var deliveryPointslist = UoW.Session.QueryOver <DeliveryPoint> (() => deliveryPointAlias)
.JoinAlias(c => c.Counterparty, () => counterpartyAlias)
.JoinAlias(c => c.ContactAndPhones, () => contactAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => counterpartyAlias.Id == Counterparty.Id)
.SelectList(list => list
.SelectGroup(() => deliveryPointAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => deliveryPointAlias.CompiledAddress).WithAlias(() => resultAlias.CompiledAddress)
.Select(() => deliveryPointAlias.Comment).WithAlias(() => resultAlias.Comment)
.Select(() => deliveryPointAlias.IsActive).WithAlias(() => resultAlias.IsActive)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => contactAlias.PostNameAndPhones),
Projections.Constant("\n"))
).WithAlias(() => resultAlias.Contacts)
)
.TransformUsing(Transformers.AliasToBean <ClientDeliveryPointVMNode> ())
.List <ClientDeliveryPointVMNode> ();
SetItemsSource(deliveryPointslist);
}
public IList <Booking> BookingReportBLL_BookingSearchBy(DateTime?startDate, int cruiseId, int bookingStatus)
{
var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false);
if (cruiseId > -1)
{
query = query.Where(x => x.Cruise.Id == cruiseId);
}
if (bookingStatus > -1)
{
query = query.Where(x => x.Status == (StatusType)bookingStatus);
}
if (startDate != null)
{
query = query.Where(Restrictions.Eq(
Projections.SqlFunction("date",
NHibernateUtil.Date,
Projections.Property <Booking>(x => x.StartDate)
), startDate.Value.Date));
}
return(query.Future <Booking>().ToList());
}
private static IProjection ProcessIfNull(MethodCallExpression mce)
{
var arg0 = ExpressionProcessor.FindMemberProjection(mce.Arguments[0]).AsProjection();
var arg1 = ExpressionProcessor.FindMemberProjection(mce.Arguments[1]).AsProjection();
return(Projections.SqlFunction("coalesce", NHibernateUtil.Object, arg0, arg1));
}
public UkolVedeni GetLatestLopThisYear()
{
return(Session.CreateCriteria <UkolVedeni>().SetMaxResults(1)
.AddOrder(Order.Desc("DateStart"))
.Add(Restrictions.Eq(Projections.SqlFunction("year", NHibernateUtil.DateTime, Projections.Property("DateStart")), DateTime.Today.Year))
.UniqueResult <UkolVedeni>());
}
public int GetStockForNomenclature(IUnitOfWork uow, int nomenclatureId)
{
Nomenclature nomenclatureAlias = null;
WarehouseMovementOperation operationAddAlias = null;
WarehouseMovementOperation operationRemoveAlias = null;
var subqueryAdd = QueryOver.Of <WarehouseMovementOperation>(() => operationAddAlias)
.Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id)
.And(() => operationAddAlias.IncomingWarehouse.Id != null)
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var subqueryRemove = QueryOver.Of <WarehouseMovementOperation>(() => operationRemoveAlias)
.Where(() => operationRemoveAlias.Nomenclature.Id == nomenclatureAlias.Id)
.And(() => operationRemoveAlias.WriteoffWarehouse.Id != null)
.Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount));
var amountProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int32, "( ?1 - ?2 )"),
NHibernateUtil.Int32, new IProjection[] {
Projections.SubQuery(subqueryAdd),
Projections.SubQuery(subqueryRemove)
}
);
ItemInStock inStock = null;
var queryResult = uow.Session.QueryOver <Nomenclature>(() => nomenclatureAlias)
.Where(() => nomenclatureAlias.Id == nomenclatureId)
.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id)
.Select(amountProjection)
).SingleOrDefault <object[]>();
return((int)queryResult[1]);
}
protected IQueryOver <Writeoff> QueryWriteoffDoc(IUnitOfWork uow, bool isCounting)
{
if (Filter.StokDocumentType != null && Filter.StokDocumentType != StokDocumentType.WriteoffDoc)
{
return(null);
}
WriteoffItem writeoffItemAlias = null;
Writeoff writeoffAlias = null;
var writeoffQuery = uow.Session.QueryOver <Writeoff>(() => writeoffAlias);
if (Filter.StartDate.HasValue)
{
writeoffQuery.Where(o => o.Date >= Filter.StartDate.Value);
}
if (Filter.EndDate.HasValue)
{
writeoffQuery.Where(o => o.Date < Filter.EndDate.Value.AddDays(1));
}
if (Filter.Warehouse != null)
{
writeoffQuery.Where(() => writeoffItemAlias.Warehouse == Filter.Warehouse);
}
writeoffQuery.Where(GetSearchCriterion(
() => writeoffAlias.Id,
() => authorAlias.Name
));
var concatProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => warehouseExpenseAlias.Name),
Projections.Constant(", "));
if (!isCounting)
{
writeoffQuery
.JoinAlias(() => writeoffAlias.Items, () => writeoffItemAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.JoinAlias(() => writeoffItemAlias.Warehouse, () => warehouseExpenseAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin);
}
writeoffQuery
.JoinAlias(() => writeoffAlias.CreatedbyUser, () => authorAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.SelectList(list => list
.SelectGroup(() => writeoffAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => writeoffAlias.Date).WithAlias(() => resultAlias.Date)
.Select(() => authorAlias.Name).WithAlias(() => resultAlias.Author)
.Select(concatProjection).WithAlias(() => resultAlias.ExpenseWarehouse)
.Select(() => StokDocumentType.WriteoffDoc).WithAlias(() => resultAlias.DocTypeEnum)
.Select(() => writeoffAlias.Comment).WithAlias(() => resultAlias.Comment)
.Select(() => writeoffAlias.CreationDate).WithAlias(() => resultAlias.CreationDate)
)
.OrderBy(() => writeoffAlias.Date).Desc
.ThenBy(() => writeoffAlias.CreationDate).Desc
.TransformUsing(Transformers.AliasToBean <StockDocumentsJournalNode>());
return(writeoffQuery);
}
public static async Task <double> GetTotalHours(Dictionary <string, object> filters)
{
ISession se = NHibernateHelper.CurrentSession;
ICriteria cr = se.CreateCriteria <Attendance>();
IProjection yearProjection = Projections.SqlFunction("year", NHibernateUtil.Int32, Projections.Property("Workdate"));
cr.Add(Restrictions.Eq(yearProjection, filters["year"]));
IProjection monthProjection = Projections.SqlFunction("month", NHibernateUtil.Int32, Projections.Property("Workdate"));
cr.Add(Restrictions.Eq(monthProjection, filters["month"]));
if (filters.ContainsKey("staff_id"))
{
cr.Add(Restrictions.Eq("Staffid", filters["staff_id"]));
}
IList <Attendance> list = await Task.Run(() => { return(cr.List <Attendance>()); });
double total_hours = 0;
foreach (Attendance o in list)
{
DateTime to = o.Timeout.GetValueOrDefault();
DateTime ti = o.Timein.GetValueOrDefault();
total_hours += (to - ti).TotalSeconds / 3600.0;
}
return(total_hours);
}
public void CriteriaTrimFunctionsWithParameters()
{
AddObjects();
ISession s = OpenSession();
try
{
ICriteria criteria = s.CreateCriteria(typeof(A)).Add(
Restrictions.Eq(
Projections.SqlFunction(
"trim",
NHibernateUtil.String,
Projections.Constant("f"),
Projections.SqlProjection("from", null, null), // Silly hack to get "from" as a second argument.
Projections.Property("Name")),
"irst"));
IList <A> items = criteria.List <A>();
Assert.AreEqual(1, items.Count);
Assert.AreEqual("first", items[0].Name);
}
finally
{
s.Close();
}
}
public virtual IList <SingleUserGroup> FindSingleUserGroups(int start, int count, string queryString, bool includeSearchInUsers)
{
User userAlias = null;
ICriterion restrictions = Restrictions.Like(Projections.Property <SingleUserGroup>(x => x.Name), queryString, MatchMode.Start);
if (includeSearchInUsers)
{
restrictions = Restrictions.Or(
restrictions,
Restrictions.Like(Projections.SqlFunction("concat",
NHibernateUtil.String,
Projections.Property(() => userAlias.ExtFirstName),
Projections.Constant(" "),
Projections.Property(() => userAlias.ExtLastName)),
queryString, MatchMode.Anywhere)
);
}
var result = GetSession().QueryOver <SingleUserGroup>()
.JoinAlias(x => x.User, () => userAlias)
.Where(restrictions)
.Fetch(SelectMode.Fetch, x => x.User)
.OrderBy(x => x.Name).Asc
.Skip(start)
.Take(count)
.List();
return(result);
}
public void project_into_dto()
{
Customer customer = null;
Employee employee = null;
var orderHeaders = Session.QueryOver <Order>()
.JoinAlias(o => o.Customer, () => customer)
.JoinAlias(o => o.Employee, () => employee)
.Select(Projections.ProjectionList()
.Add(Projections.Property <Order>(o => o.OrderedOn).As("OrderedOn"))
.Add(Projections.Property(() => customer.Name).As("CustomerName"))
.Add(Projections.SqlFunction("concat", NHibernateUtil.String,
new[]
{
Projections.Property(() => employee.FirstName),
Projections.Constant(" "),
Projections.Property(() => employee.LastName)
}), "EmployeeName"))
.TransformUsing(new AliasToBeanResultTransformer(typeof(OrderHeader)))
.List <OrderHeader>();
Assert.Greater(orderHeaders.Count(), 0);
foreach (var orderHeader in orderHeaders)
{
Assert.IsNotNull(orderHeader.CustomerName);
Assert.IsNotNull(orderHeader.EmployeeName);
Assert.Greater(orderHeader.OrderedOn, DateTime.MinValue);
}
}
public bool DoesUserExist(string email = null, string username = null)
{
int count = 0;
if (email == null)
{
count = Session.QueryOver <User>()
.Where(u => u.Username == username)
.RowCount();
}
else if (username == null)
{
count = Session.QueryOver <User>()
.WhereEqualInsensitive(u => u.Email, email)
.RowCount();
}
else
{
count = Session.QueryOver <User>()
.Where(Restrictions.And(
Restrictions.Eq(
Projections.SqlFunction("lower", NHibernateUtil.String, Projections.Property("Email")),
email?.ToLower()),
Restrictions.Where <User>(u => u.Username == username)))
.RowCount();
}
return(count > 0);
}
private static IProjection ProjectionOperacaoAtivoGroupingQueryModel()
{
OperacaoAtivoGroupingQueryModel queryModel = null;
IProjection projectionQuantidade = Projections.Property <Operacao>(operacao => operacao.Quantidade);
IProjection projectionPreco = Projections.Property <Operacao>(operacao => operacao.Preco);
IProjection projectionQuantidadeVezesPreco = Projections.SqlFunction
(
function: new VarArgsSQLFunction("(", "*", ")"),
type: NHibernateUtil.Double,
projectionQuantidade, projectionPreco
);
IProjection projectionSumOfQuantidadeVezesPreco = Projections.Sum(projectionQuantidadeVezesPreco);
IProjection projectionSumOfQuantidade = Projections.Sum(projectionQuantidade);
IProjection projectionPrecoMedio = Projections.SqlFunction
(
function: new VarArgsSQLFunction("(", "/", ")"),
type: NHibernateUtil.Double,
projectionSumOfQuantidadeVezesPreco, projectionSumOfQuantidade
);
return(Projections
.ProjectionList()
.Add(Projections.Group <Operacao>(operacao => operacao.Ativo).WithAlias(() => queryModel.Ativo))
.Add(projectionSumOfQuantidade.WithAlias(() => queryModel.SomaDasQuantidades))
.Add(projectionPrecoMedio.WithAlias(() => queryModel.PrecoMedio))
);
}
public override void UpdateNodes()
{
Counterparty counterpartyAlias = null;
Contact contactAlias = null;
ContactsVMNode resultAlias = null;
Post postAlias = null;
Phone phoneAlias = null;
var contactslist = UoW.Session.QueryOver <Contact> (() => contactAlias)
.JoinAlias(c => c.Counterparty, () => counterpartyAlias)
.JoinAlias(c => c.Post, () => postAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.JoinAlias(c => c.Phones, () => phoneAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => counterpartyAlias.Id == Counterparty.Id)
.SelectList(list => list
.SelectGroup(() => contactAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => contactAlias.Name).WithAlias(() => resultAlias.Name)
.Select(() => contactAlias.Patronymic).WithAlias(() => resultAlias.Lastname)
.Select(() => contactAlias.Surname).WithAlias(() => resultAlias.Surname)
.Select(() => contactAlias.Comment).WithAlias(() => resultAlias.Comment)
.Select(() => postAlias.Name).WithAlias(() => resultAlias.Post)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => phoneAlias.Number),
Projections.Constant("\n"))).WithAlias(() => resultAlias.Phones)
)
.TransformUsing(Transformers.AliasToBean <ContactsVMNode> ())
.List <ContactsVMNode> ();
SetItemsSource(contactslist);
}
protected static IProjection ProcessEnd <T>(MemberExpression memberExpression)
{
var parentExpression = memberExpression.Expression;
var projection = ProjectionWrapper.FindMemberProjection(parentExpression);
return(Projections.SqlFunction("upper", NHibernateUtil.GuessType(typeof(T)), projection));
}
public override IQueryOver <Contractor, Contractor> Filtering(
IQueryOver <Contractor, Contractor> query,
UserFilter filter)
{
query.FindByRn(filter.Rn);
if (!string.IsNullOrWhiteSpace(filter.TableNumber))
{
query.IsLike(x => x.ClockNumber, filter.TableNumber.ReplaceStar(string.Empty));
}
if (filter.TypeCatalog > 0)
{
query.Where(x => x.Catalog.Rn == (long)filter.TypeCatalog);
}
if (filter.IsWorker)
{
query.Where(Restrictions.Eq(Projections.SqlFunction("length", NHibernateUtil.String, Projections.Property <Contractor>(x => x.ClockNumber)), 6));
}
query.IsLike(x => x.Family, filter.Lastname.ReplaceStar(string.Empty));
query.IsLike(x => x.Firstname, filter.Firstname.ReplaceStar(string.Empty));
query.IsLike(x => x.Name, filter.OrganizationName.ReplaceStar(string.Empty));
query.IsLike(x => x.NameShort, filter.NameShort.ReplaceStar(string.Empty));
return(query);
}
public virtual ListWithTotalCountResult <User> GetUsersByGroup(int groupId, int start, int count, string filterByName)
{
UserGroup userGroupAlias = null;
var query = GetSession().QueryOver <User>()
.JoinAlias(x => x.Groups, () => userGroupAlias)
.Where(() => userGroupAlias.Id == groupId);
if (!string.IsNullOrEmpty(filterByName))
{
query.And(Restrictions.Like(Projections.SqlFunction("concat",
NHibernateUtil.String,
Projections.Property <User>(x => x.ExtFirstName),
Projections.Constant(" "),
Projections.Property <User>(x => x.ExtLastName)),
filterByName, MatchMode.Anywhere));
}
var result = query
.OrderBy(x => x.ExtFirstName).Asc
.ThenBy(x => x.ExtLastName).Asc
.Skip(start)
.Take(count)
.Future();
var resultCount = query
.ToRowCountQuery()
.FutureValue <int>();
return(new ListWithTotalCountResult <User>
{
List = result.ToList(),
Count = resultCount.Value,
});
}
public IList <CounterpartyTo1CNode> GetCounterpartiesWithInnAndAnyContact(IUnitOfWork uow)
{
Email emailAlias = null;
Counterparty counterpartyAlias = null;
CounterpartyTo1CNode resultAlias = null;
var query = uow.Session.QueryOver <Counterparty>(() => counterpartyAlias)
.Left.JoinAlias(() => counterpartyAlias.Emails, () => emailAlias)
.Where(c => c.INN != "")
.SelectList(list => list
.SelectGroup(x => x.Id).WithAlias(() => resultAlias.Id)
.Select(x => x.FullName).WithAlias(() => resultAlias.Name)
.Select(x => x.INN).WithAlias(() => resultAlias.Inn)
.Select(x => x.RingUpPhone).WithAlias(() => resultAlias.Phones)
.Select(
Projections.SqlFunction(
new SQLFunctionTemplate(
NHibernateUtil.String,
"GROUP_CONCAT(?1 SEPARATOR ';')"
),
NHibernateUtil.String,
Projections.Property(() => emailAlias.Address))
).WithAlias(() => resultAlias.EMails)
)
.TransformUsing(Transformers.AliasToBean <CounterpartyTo1CNode>())
.List <CounterpartyTo1CNode>();
return(query.Where(x => !String.IsNullOrEmpty(x.EMails) || !String.IsNullOrEmpty(x.Phones)).ToList());
}
public IList <Booking> SeriesViewBLL_BookingSearchBy(int seriesId, string taCode, int bookingCode, DateTime?startDate)
{
var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false);
if (seriesId > -1)
{
query = query.Where(x => x.Series.Id == seriesId);
}
if (!String.IsNullOrEmpty(taCode))
{
query = query.Where(x => x.AgencyCode == taCode);
}
if (bookingCode > -1)
{
query = query.Where(x => x.Id == bookingCode);
}
if (startDate != null)
{
query = query.Where(Restrictions.Eq(
Projections.SqlFunction("date",
NHibernateUtil.Date,
Projections.Property <Booking>(x => x.StartDate)
), startDate.Value.Date));
}
return(query.Future <Booking>().ToList());
}
//IList<NewsEntity.Models.CodeUmagf> IRepository<NewsEntity.Models.CodeUmagf>.GetAll()
//{
// using (ISession session = NHibernateHelper.OpenSession())
// {
// ICriteria criteria = session.CreateCriteria(typeof(NewsEntity.Models.CodeUmagf));
// criteria.AddOrder(Order.Desc("ID"));
// criteria.AddOrder(Order.Asc("MI"));
// return criteria.List<NewsEntity.Models.CodeUmagf>();
// }
//}
public IList <NewsEntity.Models.CodeUmagf> GetByPeriod(int station, int startYYYY, int startMM, int startDD, int endYYYY, int endMM, int endDD)
{
using (ISession session = NHibernateHelper.OpenSession())
{
ICriteria criteria = session.CreateCriteria(typeof(NewsEntity.Models.CodeUmagf));
criteria.AddOrder(Order.Desc("ID"));
criteria.Add(Restrictions.Eq("Station_ID", station));
System.DateTime startDate = new DateTime(startYYYY, startMM, startDD);
System.DateTime endDate = new DateTime(endYYYY, endMM, endDD);
var strYYYY = Projections.Cast(NHibernateUtil.String, Projections.Property("YYYY"));
var strMM = Projections.Cast(NHibernateUtil.String, Projections.Property("MM"));
var strDD = Projections.Cast(NHibernateUtil.String, Projections.Property("DD"));
var sl = Projections.Cast(NHibernateUtil.String, Projections.Constant("/"));
var projDate = Projections.SqlFunction("concat", NHibernateUtil.String, strDD, sl,
strMM, sl,
strYYYY);
projDate = Projections.Cast(NHibernateUtil.DateTime, projDate);
criteria.Add(Restrictions.Between(projDate, startDate, endDate));
criteria.AddOrder(Order.Asc("MI"));
return(criteria.List <NewsEntity.Models.CodeUmagf>());
}
}
public double BookingGetTotalRevenueInMonth(int month, int year, User user)
{
var firstDateOfMonth = new DateTime(year, month, 1);
var lastDateOfMonth = firstDateOfMonth.AddMonths(1).AddDays(-1);
var query = _session.QueryOver <Booking>().Where(x => x.Deleted == false);
BookingSale bookingSalesAlias = null;
query = query.JoinAlias(x => x.BookingSale, () => bookingSalesAlias);
if (user != null)
{
query = query.Where(() => bookingSalesAlias.Sale == user);
}
query = query.Where(x => x.StartDate >= firstDateOfMonth && x.StartDate <= lastDateOfMonth);
query = query.Where(x => x.Status == StatusType.Approved);
query = query.Select(
Projections.Sum(
Projections.Conditional(
Restrictions.Where <Booking>(x => x.IsTotalUsd),
Projections.SqlFunction(
new VarArgsSQLFunction("(", "*", ")"),
NHibernateUtil.Double,
Projections.Property <Booking>(x => x.Total),
Projections.Constant(23000)
),
Projections.Property <Booking>(x => x.Total)
)
));
return(query.Take(1).SingleOrDefault <double>());
}
private static IProjection CreateEntryNameProjection <T>(string prefix, Expression <Func <T, object> > keyCol)
{
return(Projections.SqlFunction("concat", NHibernateUtil.String,
Projections.Constant(prefix),
Projections.Cast(NHibernateUtil.String, Projections.Property(keyCol)),
Projections.Constant(".json")));
}
public override void UpdateNodes()
{
Proxy proxyAlias = null;
Counterparty counterpartyAlias = null;
ProxiesVMNode resultAlias = null;
Person personAlias = null;
DeliveryPoint deliveryPointAlias = null;
var proxieslist = UoW.Session.QueryOver <Proxy> (() => proxyAlias)
.JoinAlias(c => c.Counterparty, () => counterpartyAlias)
.JoinAlias(c => c.Persons, () => personAlias)
.JoinAlias(c => c.DeliveryPoints, () => deliveryPointAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Where(() => counterpartyAlias.Id == CounterpartyUoW.Root.Id)
.SelectList(list => list
.SelectGroup(() => proxyAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => proxyAlias.Number).WithAlias(() => resultAlias.Number)
.Select(() => proxyAlias.IssueDate).WithAlias(() => resultAlias.IssueDate)
.Select(() => proxyAlias.StartDate).WithAlias(() => resultAlias.StartDate)
.Select(() => proxyAlias.ExpirationDate).WithAlias(() => resultAlias.EndDate)
.SelectCountDistinct(() => personAlias.Id).WithAlias(() => resultAlias.PeopleCount)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => deliveryPointAlias.ShortAddress),
Projections.Constant("\n"))).WithAlias(() => resultAlias.DeliveryPoints)
)
.TransformUsing(Transformers.AliasToBean <ProxiesVMNode>())
.List <ProxiesVMNode>();
SetItemsSource(proxieslist);
}
public IEnumerable <OperationNode> CurrentCashForGivenSubdivisions(IUnitOfWork uow, int[] subdivisionIds)
{
Subdivision subdivisionAlias = null;
Income incomeAlias = null;
Expense expenseAlias = null;
OperationNode resultAlias = null;
var expenseSub = QueryOver.Of(() => expenseAlias)
.Where(x => x.RelatedToSubdivision.Id == subdivisionAlias.Id)
.Select(Projections.Sum <Expense>(o => o.Money));
var incomeSub = QueryOver.Of(() => incomeAlias)
.Where(x => x.RelatedToSubdivision.Id == subdivisionAlias.Id)
.Select(Projections.Sum <Income>(o => o.Money));
var projection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "( IFNULL(?1, 0) - IFNULL(?2, 0) )"),
NHibernateUtil.Decimal,
Projections.SubQuery(incomeSub),
Projections.SubQuery(expenseSub)
);
var results = uow.Session
.QueryOver(() => subdivisionAlias)
.Where(() => subdivisionAlias.Id.IsIn(subdivisionIds)).SelectList(list => list
.Select(() => subdivisionAlias.Name).WithAlias(() => resultAlias.Name)
.Select(projection).WithAlias(() => resultAlias.Balance)
)
.TransformUsing(Transformers.AliasToBean <OperationNode>())
.List <OperationNode>();
return(results);
}
public void InSqlFunctionTest()
{
using (var session = factory.OpenSession())
{
CreateObjects(typeof(Simple), session);
var inExpression = Restrictions.In(
Projections.SqlFunction(
"substring",
NHibernateUtil.String,
Projections.Property("Name"),
Projections.Constant(1),
Projections.Constant(1)),
new object[] { "A", "B" });
var sql = inExpression.ToSqlString(criteria, criteriaQuery);
// Allow some dialectal differences in function name and parameter style.
Assert.That(sql.ToString(),
Does.StartWith("substring(sql_alias.Name").Or.StartsWith("substr(sql_alias.Name"));
Assert.That(sql.ToString(), Does.EndWith(") in (?, ?)"));
// Ensure no parameters are duplicated.
var parameters = criteriaQuery.CollectedParameters.ToList();
Assert.That(parameters.Count, Is.EqualTo(4));
Assert.That(parameters[0].Value, Is.EqualTo(1));
Assert.That(parameters[1].Value, Is.EqualTo(1));
Assert.That(parameters[2].Value, Is.EqualTo("A"));
Assert.That(parameters[3].Value, Is.EqualTo("B"));
}
}
public IList <object[]> GetComplaintsResults(IUnitOfWork uow, DateTime?start = null, DateTime?end = null)
{
ComplaintResult complaintResultAlias = null;
var query = uow.Session.QueryOver <Complaint>()
.Left.JoinAlias(c => c.ComplaintResult, () => complaintResultAlias)
.Where(c => c.Status == ComplaintStatuses.Closed)
;
if (start != null && end != null)
{
query.Where(c => c.CreationDate >= start)
.Where(c => c.CreationDate <= end);
}
var result = query.SelectList(list => list
.SelectGroup(() => complaintResultAlias.Name)
.Select(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Int32, "COUNT(IFNULL(?1, 0))"),
NHibernateUtil.Int32,
Projections.Property(() => complaintResultAlias.Id)
)
))
.List <object[]>()
;
return(result);
}
//Get exact age of a person as of today
public static IProjection Age(string datepart, IProjection startDate, DateTime?endDate)
{
IProjection myAge = DateDiff("yy",
startDate, endDate);
IProjection ageMinusOne = Projections.SqlFunction(
new VarArgsSQLFunction("(", "-", ")"), NHibernateUtil.Int32, myAge,
Projections.Constant(1));
IProjection datePartMonthBirthdate = Projections.SqlFunction("month", NHibernateUtil.Int32,
startDate);
IProjection datePartDayBirthdate = Projections.SqlFunction("day", NHibernateUtil.Int32,
startDate);
IProjection datePartMonthCurrentDate = Projections.SqlFunction("month", NHibernateUtil.Int32,
Projections.Constant(endDate));
IProjection datePartDayCurrentDate = Projections.SqlFunction("day", NHibernateUtil.Int32,
Projections.Constant(endDate));
IProjection myRealAge = Projections.Conditional(
Restrictions.Or(
Restrictions.GtProperty(datePartMonthBirthdate, datePartMonthCurrentDate),
Restrictions.GtProperty(datePartDayBirthdate, datePartDayCurrentDate) &&
Restrictions.EqProperty(datePartMonthBirthdate, datePartMonthCurrentDate)),
ageMinusOne,
myAge);
return(myRealAge);
}
public PagingResult <TagInfo> SearchTag(string keyword, int page, int rows)
{
keyword = keyword ?? string.Empty;
var list = BaseRepository.GetEntityPagingList <Tag>(
string.IsNullOrEmpty(keyword) ? new List <ICriterion>() :
new List <ICriterion>
{
Restrictions.Or(
Restrictions.Gt(Projections.SqlFunction("LOCATE",
NHibernateUtil.Int32,
Projections.Constant(keyword),
Projections.Property(Tag.COL_NAME)), 0),
Restrictions.Gt(Projections.SqlFunction("LOCATE",
NHibernateUtil.Int32,
Projections.Constant(keyword),
Projections.Property(Tag.COL_DESCRIPTION)), 0))
}, (page - 1) * rows, rows);
return(new PagingResult <TagInfo>
{
Data = list.Data.Select(i => new TagInfo
{
TagId = i.TagId.GetValueOrDefault(),
Name = i.Name
}).ToList(),
TotalCount = list.TotalCount,
Count = list.Count
});
}
public void project_into_dto()
{
var orderHeaders = Session.CreateCriteria <Order>("order")
.CreateCriteria("order.Employee", "employee")
.CreateCriteria("order.Customer", "customer")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("order.OrderedOn"), "OrderedOn")
.Add(Projections.SqlFunction("concat", NHibernateUtil.String,
new[]
{
Projections.Property("employee.FirstName"),
Projections.Constant(" "),
Projections.Property("employee.LastName")
}), "EmployeeName")
.Add(Projections.Property("customer.Name"), "CustomerName")
)
.SetResultTransformer(new AliasToBeanResultTransformer(typeof(OrderHeader)))
.List <OrderHeader>();
Assert.Greater(orderHeaders.Count, 0);
foreach (var orderHeader in orderHeaders)
{
Assert.IsNotNull(orderHeader.CustomerName);
Assert.IsNotNull(orderHeader.EmployeeName);
Assert.Greater(orderHeader.OrderedOn, DateTime.MinValue);
}
}
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>());
}
public override IQueryOver <EmployeeDto, EmployeeDto> Filtering(IQueryOver <EmployeeDto, EmployeeDto> query, EmployeeFilter filter)
{
var tmp = filter.Fullname.Replace("*", "%").ToUpper();
query.Where(Restrictions.Like(Projections.SqlFunction("upper", NHibernateUtil.String, Projections.Property <EmployeeDto>(x => x.Fullname)), tmp));
return(query);
}