//public IList<SuOrganization_SuOrganizationLang> FindByLanguageId(int languageId)
//{
// StringBuilder strQuery = new StringBuilder();
// strQuery.AppendLine(" SELECT org.OrganizationID,orgLang.OrganizationName FROM SuOrganization org ");
// strQuery.AppendLine(" INNER JOIN SuOrganizationLang orgLang ");
// strQuery.AppendLine(" ON org.OrganizationID = orgLang.OrganizationID ");
// strQuery.AppendLine(" WHERE orgLang.LanguageID = :LanguageID ");
// strQuery.AppendLine(" ORDER BY orgLang.OrganizationName ");
// return GetCurrentSession().CreateSQLQuery(strQuery.ToString())
// .AddEntity(typeof(SuOrganization_SuOrganizationLang))
// .SetInt32("LanguageID", languageId)
// .List<SuOrganization_SuOrganizationLang>();
//}
//public IList<SuOrganization_SuOrganizationLang> FindByLanguageId(int languageId)
//{
// StringBuilder strQuery = new StringBuilder();
// strQuery.AppendLine(" SELECT org.*,orgLang.* FROM SuOrganization org ");
// strQuery.AppendLine(" INNER JOIN SuOrganizationLang orgLang ");
// strQuery.AppendLine(" ON org.OrganizationID = orgLang.OrganizationID ");
// strQuery.AppendLine(" WHERE orgLang.LanguageID = :LanguageID ");
// strQuery.AppendLine(" ORDER BY orgLang.OrganizationName ");
// return GetCurrentSession().CreateSQLQuery(strQuery.ToString())
// .AddEntity(typeof(SuOrganization_SuOrganizationLang))
// .SetInt32("LanguageID", languageId)
// .List<SuOrganization_SuOrganizationLang>();
//}
#endregion
#region ISuOrganizationLangQuery Members
public IList <SuOrganizationWithLang> FindByLanguageId(short languageId)
{
//throw new NotImplementedException();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT org.OrganizationID, org.OrganizationName ");
sqlBuilder.Append(" FROM SuOrganizationLang org where org.LanguageID = :LanguageId ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageId", typeof(short), languageId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("OrganizationID", NHibernateUtil.Int16)
.AddScalar("OrganizationName", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SuOrganizationWithLang))).List <SuOrganizationWithLang>());
}
public int GetBottlesAtCounterparty(IUnitOfWork UoW, Counterparty counterparty, DateTime?before = null)
{
BottlesMovementOperation operationAlias = null;
BottlesBalanceQueryResult result = null;
var queryResult = UoW.Session.QueryOver <BottlesMovementOperation>(() => operationAlias)
.Where(() => operationAlias.Counterparty.Id == counterparty.Id);
if (before.HasValue)
{
queryResult.Where(() => operationAlias.OperationTime < before);
}
var bottles = queryResult.SelectList(list => list
.SelectSum(() => operationAlias.Delivered).WithAlias(() => result.Delivered)
.SelectSum(() => operationAlias.Returned).WithAlias(() => result.Returned)
).TransformUsing(Transformers.AliasToBean <BottlesBalanceQueryResult>()).List <BottlesBalanceQueryResult>()
.FirstOrDefault()?.BottlesDebt ?? 0;
return(bottles);
}
public IList <ModuleChange> GetByDate(int year, int month, int?moduleId)
{
using (var session = sessionFactory.OpenSession())
{
var query = string.Format(@"SELECT * FROM ModuleChange Where YEAR(StartDate) = :param1 AND MONTH(StartDate) = :param2 ");
if (moduleId != null)
{
query += string.Join(query, "AND ModuleId = :param3");
}
var result = session.CreateSQLQuery(query).
SetParameter("param1", year).
SetParameter("param2", month);
if (moduleId != null)
{
result.SetParameter("param3", moduleId);
}
return(result.SetResultTransformer(Transformers.AliasToBean <ModuleChange>()).
List <ModuleChange>());
}
}
public IList <OperationNode> GetCashBalanceForOrganizations(IUnitOfWork uow)
{
Organization organizationAlias = null;
OrganisationCashMovementOperation operationAlias = null;
OperationNode resultAlias = null;
var query = uow.Session.QueryOver(() => organizationAlias)
.JoinEntityAlias(() => operationAlias,
() => organizationAlias.Id == operationAlias.Organisation.Id,
JoinType.LeftOuterJoin
)
.SelectList(list => list
.SelectGroup(() => organizationAlias.Id)
.Select(Projections.Sum(() => operationAlias.Amount)).WithAlias(() => resultAlias.Balance)
.Select(() => organizationAlias.Name).WithAlias(() => resultAlias.Name))
.TransformUsing(Transformers.AliasToBean <OperationNode>())
.List <OperationNode>();
return(query);
}
protected override IQueryOver <PECMailLog, PECMailLog> MappingProjection(IQueryOver <PECMailLog, PECMailLog> queryOver)
{
PECMail pec = null;
PECMailLogHeader header = null;
queryOver
.JoinAlias(o => o.Mail, () => pec)
.SelectList(list => list
.Select(x => x.Id).WithAlias(() => header.Id)
.Select(x => x.Description).WithAlias(() => header.Description)
.Select(x => x.Type).WithAlias(() => header.Type)
.Select(x => x.Date).WithAlias(() => header.Date)
.Select(x => x.SystemComputer).WithAlias(() => header.SystemComputer)
.Select(x => x.SystemUser).WithAlias(() => header.SystemUser)
.Select(x => pec.Id).WithAlias(() => header.MailId)
.Select(x => pec.MailSubject).WithAlias(() => header.MailSubject)
)
.TransformUsing(Transformers.AliasToBean <PECMailLogHeader>());
return(queryOver);
}
public IList <ProgramRole> FindSuProgramRoleByProgramCode(string programCode)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" SELECT SuProgramRole.RoleId ");
sqlBuilder.AppendLine(" FROM SuProgramRole INNER JOIN SuProgram ON SuProgram.ProgramID = SuProgramRole.ProgramID ");
sqlBuilder.AppendLine(" WHERE SuProgram.ProgramCode = :ProgramCode ");
sqlBuilder.AppendLine(" AND SuProgramRole.Active = 1 ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("ProgramCode", typeof(string), programCode);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("RoleId", NHibernateUtil.Int16);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.ProgramRole))).List <ProgramRole>());
}
public IList <Language> FindByDbLanguageCriteria(DbLanguage language)
{
StringBuilder sql = new StringBuilder();
sql.Append("Select l.LanguageName ");
sql.Append("from DbLanguage as l ");
sql.Append("where l.LanguageName = :LanguageName ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LanguageName", typeof(String), language.LanguageName);
queryParameterBuilder.FillParameters(query);
query.AddScalar("LanguageName", NHibernateUtil.String);;
IList <SS.DB.DTO.ValueObject.Language> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(SS.DB.DTO.ValueObject.Language)))
.List <SS.DB.DTO.ValueObject.Language>();
return(list);
}
public IList <PlaylistDto> GetCreatedPlaylists(int userId, PageData pageData)
{
PlaylistDto dto = null;
Playlist playlist = null;
User creator = null;
return(Session.QueryOver(() => playlist)
.Where(p => p.Creator.Id == userId)
.JoinAlias(() => playlist.Creator, () => creator)
.OrderBy(p => p.DateCreated).Desc
.SelectList(l => l
.Select(() => creator.UserName).WithAlias(() => dto.CreatorUserName)
.Select(() => creator.Alias).WithAlias(() => dto.CreatorAlias)
.Select(() => playlist.Name).WithAlias(() => dto.Name)
.Select(() => playlist.Id).WithAlias(() => dto.PlaylistId)
.Select(() => playlist.UrlId).WithAlias(() => dto.UrlId))
.TransformUsing(Transformers.AliasToBean <PlaylistDto>())
.Paginate(pageData)
.List <PlaylistDto>());
}
public async Task OrderBySpecifiedPropertyWithQueryOverAsync()
{
using (var session = OpenSession())
using (session.BeginTransaction())
{
PersonDto dto = null;
var people = await(session.QueryOver <Person>()
.SelectList(b => b.Select(p => p.Id).WithAlias(() => dto.Id)
.Select(p => p.Name).WithAlias(() => dto.Name)
.Select(p => p.Address).WithAlias(() => dto.Address)
.Select(p => p.Age).WithAlias(() => dto.Age))
.OrderBy(p => p.Age)
.Desc
.TransformUsing(Transformers.AliasToBean <PersonDto>())
.ListAsync <PersonDto>());
Assert.That(people.Count, Is.EqualTo(2));
Assert.That(people, Is.Ordered.By("Age").Descending);
}
}
public IList <TranslatedListItem> GetTranslatedList(short languageID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT div.DivisionID as Id, divLang.DivisionName as Text FROM SuDivision div ");
strQuery.AppendLine(" INNER JOIN SuDivisionLang divLang ");
strQuery.AppendLine(" ON div.DivisionID = divLang.DivisionID ");
strQuery.AppendLine(" WHERE divLang.LanguageID = :LanguageID ");
strQuery.AppendLine(" ORDER BY divLang.DivisionName ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetInt16("LanguageID", languageID == short.Parse("0") ? short.Parse("1") : languageID);
query.AddScalar("Id", NHibernateUtil.Int16);
query.AddScalar("Text", NHibernateUtil.String);
IList <TranslatedListItem> list = query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem))).List <TranslatedListItem>();
return(list);
}
public IList <ClosedComplaintResultNode> GetComplaintsResultsOfEmployees(IUnitOfWork uow, DateTime?start = null, DateTime?end = null)
{
ComplaintResultOfEmployees resultOfEmployeesAlias = null;
ClosedComplaintResultNode resultAlias = null;
var query = uow.Session.QueryOver <Complaint>()
.Left.JoinAlias(c => c.ComplaintResultOfEmployees, () => resultOfEmployeesAlias)
.Where(c => c.Status == ComplaintStatuses.Closed);
AddDateRestriction(start, end, query);
return(query.SelectList(list => list
.SelectGroup(() => resultOfEmployeesAlias.Name).WithAlias(() => resultAlias.Name)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Int32, "COUNT(IFNULL(?1, 0))"),
NHibernateUtil.Int32,
Projections.Property(() => resultOfEmployeesAlias.Id))).WithAlias(() => resultAlias.Count))
.TransformUsing(Transformers.AliasToBean <ClosedComplaintResultNode>())
.List <ClosedComplaintResultNode>());
}
protected override Dictionary <Product, IEnumerable <string> > GetValues(List <Product> objs)
{
ProductVariant productVariantAlias = null;
OptionValueData data = null;
IList <OptionValueData> values = _session.QueryOver <ProductOptionValue>()
.JoinAlias(value => value.ProductVariant, () => productVariantAlias)
.SelectList(builder => builder
.Select(value => value.Value).WithAlias(() => data.Value)
.Select(value => value.ProductOption.Id).WithAlias(() => data.OptionId)
.Select(() => productVariantAlias.Product.Id).WithAlias(() => data.ProductId)
).TransformUsing(Transformers.AliasToBean <OptionValueData>())
.List <OptionValueData>();
Dictionary <int, IEnumerable <string> > dictionary =
values.GroupBy(valueData => valueData.ProductId)
.ToDictionary(datas => datas.Key, datas => datas.Select(valueData => valueData.TermValue));
return(objs.ToDictionary(product => product,
product => dictionary.ContainsKey(product.Id) ? dictionary[product.Id] : Enumerable.Empty <string>()));
}
public List <SelectListItem> GetOptions()
{
BrandInfo info = null;
return(_session.QueryOver <Brand>()
.OrderBy(x => x.Name).Asc
.SelectList(builder =>
{
builder.Select(x => x.Id).WithAlias(() => info.Id);
builder.Select(x => x.Name).WithAlias(() => info.Name);
return builder;
}).TransformUsing(Transformers.AliasToBean <BrandInfo>())
.Cacheable()
.List <BrandInfo>()
.BuildSelectItemList(item => item.Name, item => item.Id.ToString(), null, new SelectListItem
{
Text = "Please select...",
Value = "0"
}));
}
public IList <DbPBCurrency> FindPBLocalCurrencyByPBID(long pbID, string currencyType)
{
string sql = @"select PBCurrency.ID,PBCurrency.PBID,Currency.CurrencyID,Currency.Symbol,PBCurrency.UpdBy,PBCurrency.UpdDate,PBCurrency.CreBy,PBCurrency.CreDate,PBCurrency.UpdPgm from DbPBCurrency as PBCurrency inner join DbCurrency as Currency on PBCurrency.CurrencyID = Currency.CurrencyID
where PBCurrency.PBID = :pbID order by PBCurrency.ID";
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql);
query.SetInt64("pbID", pbID);
query.AddScalar("ID", NHibernateUtil.Int64);
query.AddScalar("PBID", NHibernateUtil.Int64);
query.AddScalar("CurrencyID", NHibernateUtil.Int16);
query.AddScalar("Symbol", NHibernateUtil.String);
query.AddScalar("UpdBy", NHibernateUtil.Int64);
query.AddScalar("UpdDate", NHibernateUtil.DateTime);
query.AddScalar("CreBy", NHibernateUtil.Int64);
query.AddScalar("CreDate", NHibernateUtil.DateTime);
query.AddScalar("UpdPgm", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPBCurrency)));
return(query.List <DbPBCurrency>());;
}
public IList <DbPaymentMethod> FindPaymentMethodActive(short ComID)
{
StringBuilder sqlBuilder = new StringBuilder();
ISQLQuery query = null;
sqlBuilder.Append(" SELECT B.PaymentMethodID , '[' + B.PaymentMethodCode + '] ' + B.PaymentMethodName AS PaymentMethodName ");
sqlBuilder.Append(" FROM DbCompanyPaymentMethod AS A , DbPaymentMethod AS B ");
sqlBuilder.Append(" WHERE A.PaymentMethodID = B.PaymentMethodID AND ");
sqlBuilder.Append(" A.CompanyID = :CompanyID ");
sqlBuilder.Append(" ORDER BY B.PaymentMethodCode ");
query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
query.SetParameter("CompanyID", ComID);
query.AddScalar("PaymentMethodID", NHibernateUtil.Int64);
query.AddScalar("PaymentMethodName", NHibernateUtil.String);
IList <DbPaymentMethod> dbPyamentMethodList = query.SetResultTransformer(Transformers.AliasToBean(typeof(DbPaymentMethod))).List <DbPaymentMethod>();
return(dbPyamentMethodList);
}
public override void UpdateNodes()
{
CommentsTemplatesVMNode resultAlias = null;
CommentsTemplates commentsTemplatesAlias = null;
var query = UoW.Session.QueryOver <CommentsTemplates>(() => commentsTemplatesAlias);
//if(Filter.RestrictionFineDateStart.HasValue) {
// query.Where(() => fineAlias.Date >= Filter.RestrictionFineDateStart.Value);
//}
var result = query.SelectList(list => list
.Select(() => commentsTemplatesAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => commentsTemplatesAlias.CommentTemplate).WithAlias(() => resultAlias.CommentTmp)
).TransformUsing(Transformers.AliasToBean <CommentsTemplatesVMNode>())
.List <CommentsTemplatesVMNode>();
SetItemsSource(result);
}
public static CounterpartyDebtQueryResult GetCounterpartyBalanceResult(IUnitOfWork UoW, Counterparty counterparty, DateTime?before = null)
{
MoneyMovementOperation operationAlias = null;
CounterpartyDebtQueryResult result = null;
var queryResult = UoW.Session.QueryOver <MoneyMovementOperation>(() => operationAlias)
.Where(() => operationAlias.Counterparty.Id == counterparty.Id);
if (before.HasValue)
{
queryResult.Where(() => operationAlias.OperationTime < before);
}
var debt = queryResult
.SelectList(list => list
.SelectSum(() => operationAlias.Debt).WithAlias(() => result.Charged)
.SelectSum(() => operationAlias.Money).WithAlias(() => result.Payed)
.SelectSum(() => operationAlias.Deposit).WithAlias(() => result.Deposit)
).TransformUsing(Transformers.AliasToBean <CounterpartyDebtQueryResult>()).List <CounterpartyDebtQueryResult>();
return(debt.FirstOrDefault());
}
public List <KodIsim> StokKods(string stokKodIsim, Sube sube)
{
//ICriteria criteria = Session.CreateCriteria(typeof(Stok)).SetProjection(Projections.Property("Id"))
// .Add(Expression.Like("Id", stokKod, MatchMode.Start)).SetMaxResults(GetMaxResult);
//ICriteria subeCriteria = criteria.CreateCriteria("Sube");
//subeCriteria.Add(NHibernate.Criterion.Expression.Eq("Id", sube.Id));
//return criteria.List<string>() as List<string>;
//IQuery query = Session.CreateQuery("select st.Id from Stok st where st.Sube=:sube and st.Id like :st").SetString("st", stokKod + "%").SetEntity("sube", sube).SetMaxResults(10);
//return (List<string>)query.List<string>();
KodIsim kodIsim = null;
return(Session.QueryOver <Stok>().Where(x => (x.Sube == sube || x.SubelerdeOrtak == true) &&
(x.Id.IsLike(stokKodIsim, MatchMode.Anywhere) || x.StokAdi.IsLike(stokKodIsim, MatchMode.Anywhere)))
.SelectList(liste =>
liste.SelectGroup(c => c.Id).WithAlias(() => kodIsim.Kod)
.SelectGroup(c => c.StokAdi).WithAlias(() => kodIsim.Isim)
).TransformUsing(Transformers.AliasToBean <KodIsim>())
.Take(GetMaxResult).List <KodIsim>() as List <KodIsim>);
}
public ActionResult Conclave()
{
// Get data for existing Types and pass to View
using (ISession _S = MvcApplication.SF.GetCurrentSession())
{
Conclave clv = null;
var conclaveList = _S.QueryOver(() => clv)
.SelectList(l => l
.Select(x => x.Typepk).WithAlias(() => clv.Typepk)
.Select(x => x.Typeid).WithAlias(() => clv.Typeid)
.Select(x => x.Name).WithAlias(() => clv.Name)
.Select(x => x.Customflg).WithAlias(() => clv.Customflg)
)
.TransformUsing(Transformers.AliasToBean <Conclave>())
.List <Conclave>();
return(View(conclaveList));
}
}
private void DeleteTransformers()
{
var tasks =
Transformers.Select(transformerDefinition => DatabaseCommands.DeleteTransformerAsync(transformerDefinition.Name))
.ToList();
TaskEx.WhenAll(tasks)
.ContinueOnUIThread(t =>
{
if (t.IsFaulted)
{
ApplicationModel.Current.AddErrorNotification(t.Exception, "not all transformers could be deleted");
}
else
{
ApplicationModel.Current.AddInfoNotification("all transformers were successfully deleted");
UrlUtil.Navigate("/transformers");
}
});
}
public IList <DbTax> GetTaxCodeActiveByCompany(long companyID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select DbTax.TaxCode,DbTax.TaxID ");
sqlBuilder.Append(" from DbTax ");
sqlBuilder.Append(" left join DbCompanyTax on DbTax.TaxID = DbCompanyTax.TaxID and DbCompanyTax.CompanyID = :companyID");
sqlBuilder.Append(" Where DbTax.Active = 1 ");
sqlBuilder.Append(" and (ISNULL(DbCompanyTax.Disable,0) <> 1)");
sqlBuilder.Append(" or (DbTax.ApplyAllCompany = 1 and DbTax.Active = 1)");
sqlBuilder.Append(" order by DbTax.TaxCode ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("companyID", typeof(string), companyID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("TaxCode", NHibernateUtil.String)
.AddScalar("TaxID", NHibernateUtil.Int64);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(DbTax))).List <DbTax>());
}
public IList <SupportTeamDto> GetSupportTeamSummaries()
{
var criteria = Session.CreateCriteria(typeof(SupportTeam));
criteria
.CreateAlias("Supervisor", "Supervisor", JoinType.LeftOuterJoin)
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("Id"), "Id")
.Add(Projections.Property("TeamName"), "TeamName")
.Add(Projections.Property("PhoneNumber"), "PhoneNumber")
.Add(Projections.Property("Supervisor.FirstName"), "SupervisorFirstName")
.Add(Projections.Property("Supervisor.LastName"), "SupervisorLastName")
//.Add(Projections.Property("SupervisorId"), "SupervisorId");
);
criteria
.SetResultTransformer(Transformers.AliasToBean <SupportTeamDto>());
return(criteria.List <SupportTeamDto>());
}
public void CountWithConditionalDoesNotThrowAsync()
{
using (var session = OpenSession())
using (session.BeginTransaction())
{
MappingEntity mappingEntity = null;
Assert.DoesNotThrowAsync(
() =>
session.QueryOver <Entity>().SelectList(
builder =>
builder.Select(
Projections.Count(
Projections.Conditional(
Restrictions.Eq(Projections.Property <Entity>(x => x.Name), "FOO"),
Projections.Constant("", NHibernateUtil.String),
Projections.Constant(null, NHibernateUtil.String))).WithAlias(() => mappingEntity.Count))
).TransformUsing(Transformers.AliasToBean <MappingEntity>()).ListAsync <MappingEntity>()
);
}
}
public IEnumerable <CategoryContactDTO> GetAllByCategoriesIds(List <int> categoriesIds)
{
Contact contact = null;
Category category = null;
CategoryContactDTO dto = null;
var query =
new DefaultQueryOver <Contact, int>().GetQueryOver(() => contact)
.JoinQueryOver(() => contact.Categories, () => category)
.WhereRestrictionOn(() => category.Id)
.IsIn(categoriesIds)
.SelectList(
l =>
l.Select(() => contact.Id)
.WithAlias(() => dto.contactId)
.Select(() => category.Id)
.WithAlias(() => dto.categoryId))
.TransformUsing(Transformers.AliasToBean <CategoryContactDTO>());
return(this.Repository.FindAll <CategoryContactDTO>(query));
}
public IList <TrackThumbnailDto> GetUploadedTracks(int userId, PageData pageData)
{
User uploader = null;
TrackThumbnailDto dto = null;
return(Session.QueryOver <Track>()
.Where(t => t.Uploader.Id == userId)
.OrderBy(t => t.DateUploaded).Desc
.JoinAlias(t => t.Uploader, () => uploader)
.SelectList(l => l
.Select(t => t.Id).WithAlias(() => dto.Id)
.Select(t => t.Title).WithAlias(() => dto.Title)
.Select(t => t.UrlId).WithAlias(() => dto.UrlId)
.Select(t => t.Artwork).WithAlias(() => dto.Artwork)
.Select(() => uploader.Alias).WithAlias(() => dto.UserAlias)
.Select(() => uploader.UserName).WithAlias(() => dto.UserName))
.TransformUsing(Transformers.AliasToBean <TrackThumbnailDto>())
.Paginate(pageData)
.List <TrackThumbnailDto>());
}
public void ForceRefreshComponentsDictionary()
{
using (var session = Hibernate.SessionFactory.OpenSession())
{
Component c = null;
Mount m = null;
ComponentWithCountsDTO dto = null;
components = session
.QueryOver(() => c)
.Left.JoinAlias(() => c.Mounts, () => m)
.SelectList(l => l
.SelectGroup(() => c.Id).WithAlias(() => dto.Id)
.SelectGroup(() => c.Name).WithAlias(() => dto.Name)
.SelectCount(() => m.Id).WithAlias(() => dto.MountsCount))
.OrderBy(() => c.Name).Asc
.TransformUsing(Transformers.AliasToBean <ComponentWithCountsDTO>())
.List <ComponentWithCountsDTO>();
}
}
public IList <FnExpenseDocument> GetTotalExpense(long remittanceID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select FnExpenseDocument.TotalExpense as TotalExpense ,FnExpenseDocument.PaymentType");
sqlBuilder.Append(" from FnRemittance inner join ");
sqlBuilder.Append(" FnExpenseDocument on FnRemittance.documentID = FnExpenseDocument.DocumentID ");
sqlBuilder.Append(" where FnRemittance.RemittanceID = :remittanceID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("remittanceID", typeof(long), remittanceID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("TotalExpense", NHibernateUtil.Double);
query.AddScalar("PaymentType", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(FnExpenseDocument))).List <FnExpenseDocument>());
}
private int GetNumberBought(IList <ProductVariant> variants)
{
if (!variants.Any())
{
return(0);
}
List <int> values = variants.Select(variant => variant.Id).ToList();
var numberBoughtCount = new NumberBoughtCount();
var singleOrDefault = _session.QueryOver <OrderLine>()
.Where(line => line.ProductVariant.Id.IsIn(values))
.SelectList(
builder =>
builder.SelectSum(line => line.Quantity)
.WithAlias(() => numberBoughtCount.Count))
.TransformUsing(Transformers.AliasToBean <NumberBoughtCount>())
.Cacheable()
.SingleOrDefault <NumberBoughtCount>();
return(singleOrDefault != null ? singleOrDefault.Count : 0);
}
public void ProjectionsShouldWorkWithHqlAndFutures()
{
using (var session = OpenSession())
using (session.BeginTransaction())
{
var query1 =
session.CreateQuery("select e.Id as EntityId, e.Name as EntityName from TestEntity e").SetResultTransformer(
Transformers.AliasToBean(typeof(TestEntityDto)))
.List <TestEntityDto>();
Assert.AreEqual(2, query1.Count());
var query2 =
session.CreateQuery("select e.Id as EntityId, e.Name as EntityName from TestEntity e").SetResultTransformer(
Transformers.AliasToBean(typeof(TestEntityDto)))
.Future <TestEntityDto>();
Assert.AreEqual(2, query2.Count());
}
}
/// <summary>
/// SQL分页
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="utilityPager">分页类</param>
/// <param name="querysql">SQL语句</param>
/// <returns></returns>
public IList <T> PagerList <T>(UtilityPager utilityPager, string querysql, List <NHParameter> parameters, string orderBy = "", string ascOrDesc = "", bool isDTO = false) where T : class
{
if (utilityPager == null)
{
utilityPager = new UtilityPager();
}
string _querysql = querysql;
if (!string.IsNullOrEmpty(orderBy) && !string.IsNullOrEmpty(ascOrDesc))
{
_querysql = querysql + " order by " + orderBy + " " + ascOrDesc;
}
var queryList = ICreateSQLQuery(_querysql);
if (parameters != null && parameters.Count > 0)
{
foreach (NHParameter para in parameters)
{
queryList.SetParameter(para.ParameterName, para.ParameterValue, para.DbType);
}
}
if (!isDTO)
{
queryList.AddEntity(typeof(T));
}
else
{
queryList.SetResultTransformer(Transformers.AliasToBean(typeof(T)));
}
if (!utilityPager.IsGetCount)
{
return(queryList.SetFirstResult(GetPageIndex(utilityPager))
.SetMaxResults(utilityPager.PageSize)
.List <T>());
}
int count = Convert.ToInt32(GetCustomSQLQueryUniqueResult <object>("select count(*) from (" + querysql + ") as countTable", parameters));
return(queryList.SetFirstResult(GetPageIndex(utilityPager, count))
.SetMaxResults(utilityPager.PageSize)
.List <T>());
}