private IList <LookupValueDto> GetLookupDto(Type lookupType)
{
var criteria = Session.CreateCriteria(lookupType);
criteria.SetProjection(
Projections.ProjectionList()
.Add <LookupBase, LookupValueDto, long>(p => p.Key, pDto => pDto.Key)
.Add <LookupBase, LookupValueDto, string>(
p => p.WellKnownName,
pDto => pDto.WellKnownName)
.Add <LookupBase, LookupValueDto, string>(p => p.Name, pDto => pDto.Name)
.Add <LookupBase, LookupValueDto, string>(p => p.ShortName, pDto => pDto.ShortName)
.Add <LookupBase, LookupValueDto, int?>(
p => p.SortOrderNumber,
pDto => pDto.SortOrderNumber));
criteria.SetResultTransformer(Transformers.AliasToBean(typeof(LookupValueDto)));
criteria.AddOrder(Order.Asc(Projections.Property <LookupBase>(p => p.SortOrderNumber)))
.AddOrder(Order.Asc(Projections.Property <LookupBase>(p => p.Name)));
return(criteria.List <LookupValueDto>());
}
public SourceDTO GetSourceDTO(int sourceId)
{
string sql = @"
SELECT s.SourceID, s.SourceName, s.FullReference, s.SourcePath, s.SourceDate, s.FileExtension, s.IsRestricted, s.FileDateTimeStamp, s.Archive,
s.IsReadOnly, s.Notes, s.IsPublic, DATALENGTH(s.FileData) AS FileSize, j.CaseNumber AS JhroCaseNumber, j.JhroCaseID,
(CASE WHEN s.OriginalFileData IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END) AS HasOcrText,
au.UserID AS UploadedByUserID
FROM PRF_Source s LEFT JOIN PRF_JhroCase j ON s.JhroCaseID = j.JhroCaseID
LEFT JOIN PRF_FeedingSource fs ON s.SourceID = fs.SourceID
LEFT JOIN PRF_AdminUser au ON fs.UploadedByID = au.AdminUserID
WHERE s.SourceID = :sourceId
";
IList <SourceDTO> results = Session.CreateSQLQuery(sql)
.SetParameter("sourceId", sourceId, NHibernateUtil.Int32)
.SetResultTransformer(Transformers.AliasToBean(typeof(SourceDTO)))
.List <SourceDTO>(); // returning list, as the join with PRF_FeedingsSource can possibly result in more than one row returned
if (results != null && results.Count > 0)
{
return(results[0]);
}
return(null);
}
public void UpdateItem(int id, [FromBody] Item value)
{
var query = "SELECT * FROM ITEM WHERE ITEMID=" + id;
Item item = null;
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
var sqlquery = session.CreateSQLQuery(query)
.SetResultTransformer(Transformers.AliasToBean <Item>());
item = sqlquery.List <Item>().FirstOrDefault();
item.Category = value.Category;
item.Description = value.Description;
item.ImageID = value.ImageID;
item.Phone = value.Phone;
item.Price = value.Price;
item.Title = value.Title;
session.SaveOrUpdate(item);
transaction.Commit();
}
}
}
public IList <ExpenseCA> FindByCADocumentID(long CADocmentID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("Select distinct doc.DocumentNo, doc.Subject, doc.DocumentDate, caDoc.CADocumentID ");
sqlBuilder.AppendLine(" from Document AS doc ");
sqlBuilder.AppendLine(" LEFT JOIN CADocument AS caDoc ");
sqlBuilder.AppendLine(" ON doc.DocumentID = caDoc.DocumentID ");
sqlBuilder.AppendLine(" WHERE caDoc.CADocumentID = :CADocmentID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
//query.SetParameterList("MPADocmentID", Convert.ToString(MPADocmentID));
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("CADocmentID", typeof(long), CADocmentID);
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
query.AddScalar("Subject", NHibernateUtil.String);
query.AddScalar("DocumentDate", NHibernateUtil.Date);
query.AddScalar("CADocumentID", NHibernateUtil.Int64);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ExpenseCA))).List <ExpenseCA>());
}
public IList <DriverDetailsDto> EmployeeDate()
{
DriverDetailsDto driverDetailsDto = null;
Employee employeeAlias = null;
// Driver driverAlias = null;
using (ITransaction transaction = _session.BeginTransaction())
{
var employeeData = _session.QueryOver(() => employeeAlias)
.SelectList(
list => list.Select(() => employeeAlias.FirstName)
.WithAlias(() => driverDetailsDto.FirstName)
.Select(() => employeeAlias.LastName)
.WithAlias(() => driverDetailsDto.LastName)
.Select(() => employeeAlias.Salary)
.WithAlias(() => driverDetailsDto.Salary)
).TransformUsing(Transformers.AliasToBean <DriverDetailsDto>()).List <DriverDetailsDto>();
return(employeeData);
}
}
public IList <WorkFlowStateEvent> FindWorkFlowStateEvent(string WorkFlowStateName, string WorkFlowStateEventName, int WorkFlowTypeID)
{
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine(" SELECT t2.WorkFlowStateEventID AS WorkFlowStateEventID ");
strQuery.AppendLine(" FROM WorkFlowState t1 ");
strQuery.AppendLine(" INNER JOIN WorkFlowStateEvent t2 ");
strQuery.AppendLine(" ON t1.WorkFlowStateID = t2.WorkFlowStateID ");
strQuery.AppendLine(" WHERE t1.[Name] = :WorkFlowStateEventName ");
strQuery.AppendLine(" AND t2.[Name] = :WorkFlowStateName ");
strQuery.AppendLine(" AND t1.WorkFlowTypeID = :WorkFlowTypeID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(strQuery.ToString());
query.SetString("WorkFlowStateName", WorkFlowStateName);
query.SetString("WorkFlowStateEventName", WorkFlowStateEventName);
query.SetInt32("WorkFlowTypeID", WorkFlowTypeID);
query.AddScalar("WorkFlowStateEventID", NHibernateUtil.Int32);
query.SetResultTransformer(Transformers.AliasToBean(typeof(WorkFlowStateEvent)));
return(query.List <WorkFlowStateEvent>());
}
public IList <VORejectReasonLang> FindRejectReasonByDocumentTypeIDAndLanguageID(int documentTypeID, short languageID)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT r.ReasonID AS ReasonID , r.ReasonCode AS ReasonCode , rl.ReasonDetail AS ReasonDetail ");
sql.Append("FROM DbRejectReason AS r ");
sql.Append("INNER JOIN DbRejectReasonLang AS rl ON rl.ReasonID = r.ReasonID ");
sql.Append("WHERE r.DocumentTypeID =:documentTypeID AND rl.LanguageID =:languageID AND r.Active = 1 ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString());
QueryParameterBuilder queryParameter = new QueryParameterBuilder();
queryParameter.AddParameterData("documentTypeID", typeof(int), documentTypeID);
queryParameter.AddParameterData("languageID", typeof(int), languageID);
queryParameter.FillParameters(query);
query.AddScalar("ReasonID", NHibernateUtil.Int16);
query.AddScalar("ReasonCode", NHibernateUtil.String);
query.AddScalar("ReasonDetail", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(VORejectReasonLang))).List <VORejectReasonLang>());
}
public IList <TranslatedListItem> FindCompanyCriteriaShowIDJoinName()
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT ");
sqlBuilder.Append(" CompanyID AS ID, ");
sqlBuilder.Append(" '[ ' + CONVERT(VARCHAR,CompanyCode) + ' ] ' + CompanyName AS Symbol ");
sqlBuilder.Append(" FROM ");
sqlBuilder.Append(" DbCompany ");
sqlBuilder.Append(" WHERE Active = 1 ORDER BY CompanyCode");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.FillParameters(query);
query.AddScalar("ID", NHibernateUtil.Int16);
query.AddScalar("Symbol", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(TranslatedListItem)));
return(query.List <TranslatedListItem>());
}
public IList <LocationLangResult> FindLocationLangByLocationID(long locationId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("Select lang.LanguageID as LanguageID,lang.LanguageName as LanguageName "); //, ll.Description as LocationName, ll.Comment as Comment, ll.Active as Active ");
sqlBuilder.Append("FROM DbLanguage as lang ");
//sqlBuilder.Append("LEFT JOIN DbLocationLang as ll on lang.LanguageID = ll.LanguageID AND ll.LocationID =:LocationID ");
sqlBuilder.Append("Where lang.LanguageID = :LanguageID ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("LocationID", typeof(long), locationId);
queryParameterBuilder.FillParameters(query);
query.AddScalar("LanguageID", NHibernateUtil.Int16);
query.AddScalar("LanguageName", NHibernateUtil.String);
//query.AddScalar("LocationName", NHibernateUtil.String);
//query.AddScalar("Comment", NHibernateUtil.String);
//query.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(LocationLangResult))).List <LocationLangResult>());
}
public IList <SnapshotAggregatedInfo> GetSnapshotsResourcesCount(long[] snapshotIds)
{
ResourceVersion resourceVersionAlias = null;
Resource resourceAlias = null;
Project projectAlias = null;
SnapshotAggregatedInfo contract = null;
var result = GetSession().QueryOver <Snapshot>()
.JoinAlias(x => x.ResourceVersions, () => resourceVersionAlias)
.JoinAlias(() => resourceVersionAlias.Resource, () => resourceAlias)
.JoinAlias(() => resourceAlias.Project, () => projectAlias)
.Where(x => x.Id.IsIn(snapshotIds))
.And(() => projectAlias.IsRemoved == false)
.SelectList(list =>
list.SelectGroup(x => x.Id).WithAlias(() => contract.Id)
.SelectGroup(() => resourceAlias.ResourceType).WithAlias(() => contract.Type)
.SelectCount(() => resourceVersionAlias.Id).WithAlias(() => contract.ResourcesCount))
.TransformUsing(Transformers.AliasToBean <SnapshotAggregatedInfo>())
.List <SnapshotAggregatedInfo>();
return(result);
}
public ValoresAgregados GetAggregates(int dispositivo, string sensorCode, DateTime desde, DateTime hasta)
{
var sensorDao = new SensorDAO();
var sensor = sensorDao.FindByCode(dispositivo, sensorCode);
if (sensor == null)
{
return(null);
}
ValoresAgregados valoresAgregados = null;
return(Session.QueryOver <Medicion>()
.Where(m => m.Dispositivo.Id == dispositivo && m.Sensor.Id == sensor.Id)
.And(m => m.FechaMedicion >= desde && m.FechaMedicion <= hasta)
.Select(Projections.Max <Medicion>(m => m.ValorNum1).WithAlias(() => valoresAgregados.Max),
Projections.Min <Medicion>(m => m.ValorNum1).WithAlias(() => valoresAgregados.Min),
Projections.Avg <Medicion>(m => m.ValorNum1).WithAlias(() => valoresAgregados.Avg)
)
.TransformUsing(Transformers.AliasToBean <ValoresAgregados>())
.List <ValoresAgregados>()
.FirstOrDefault());
}
//isso aqui deveria estar no CategoriesDAO, mas é só um exemplo :)
public ICollection <ProdutosPorCategoria> BuscaQtdeProdutosPorCategoria()
{
//Forma 1 - manipulando lista de Object[]
//var query = _session.CreateQuery("select p.Category, count(p) from Product p group by p.Category");
//var resultados = query.List<Object[]>();
//foreach (var r in resultados)
//{
// var p = new ProdutosPorCategoria()
// {
// Categoria = (Category)r[0],
// NumeroDeProdutos = (long)r[1]
// };
// yield return p; //yield return não está debugando =/
//}
//Forma 2 - Transformers.AliasToBean
var query = _session.CreateQuery("select p.Category as Categoria, count(p) as NumeroDeProdutos from Product p group by p.Category");
query.SetResultTransformer(Transformers.AliasToBean <ProdutosPorCategoria>());
return(query.List <ProdutosPorCategoria>());
}
public IList <Competition> GetByPlayer(long id)
{
Competition dto = null;
Competition comp = null;
Game game = null;
PlayerStats ps = null;
using (ISession session = NHibernateHelper.OpenSession())
{
var comps = session.QueryOver <PlayerStats>(() => ps)
.JoinAlias(() => ps.Game, () => game)
.JoinAlias(() => game.Competition, () => comp)
.Where(e => e.Player.Id == id)
.SelectList(l => l
.Select(() => comp.Id).WithAlias(() => dto.Id)
.Select(() => comp.Name).WithAlias(() => dto.Name)
.Select(() => comp.Team).WithAlias(() => dto.Team)
)
.TransformUsing(Transformers.AliasToBean <Competition>())
.List <Competition>();
return(comps as IList <Competition>);
}
}
public IList <TotalDocumentoPorFaseConsulta> ObterTotalDeDocumentosPorFase()
{
var statusConsulta = new List <DocumentoStatus>
{
DocumentoStatus.StatusValidado,
DocumentoStatus.StatusParaReconhecimento,
DocumentoStatus.StatusParaProvaZero,
DocumentoStatus.AguardandoMontagem,
DocumentoStatus.AguardandoAprovacao,
DocumentoStatus.ParaDigitacao,
DocumentoStatus.ParaValidacao,
DocumentoStatus.StatusParaExportacao,
DocumentoStatus.StatusFinalizado
};
return(this.Session.CreateCriteria(typeof(Documento))
.Add(Restrictions.In("Status", statusConsulta))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Alias(Projections.GroupProperty("Status"), "Status"))
.Add(Projections.Alias(Projections.Count("Id"), "QuantidadeDocumentos")))
.SetResultTransformer(Transformers.AliasToBean(typeof(TotalDocumentoPorFaseConsulta)))
.List <TotalDocumentoPorFaseConsulta>());
}
public IList <SuRTEContentSearchResult> FindSuRTEContentByContentIdLanguageId(short contentId, short languageId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" select c.LanguageId as LanguageId, c.Id as contentId, c.Header as Header, c.Content as Content, c.Comment as Comment, c.Active as Active");
sqlBuilder.AppendLine(" from SuRTEContent c where c.Id = :ContentId and c.LanguageId = :LanguageId");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("LanguageId", typeof(short), languageId);
parameterBuilder.AddParameterData("ContentId", typeof(short), contentId);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("LanguageId", NHibernateUtil.Int16)
.AddScalar("ContentId", NHibernateUtil.Int16)
.AddScalar("Header", NHibernateUtil.String)
.AddScalar("Content", NHibernateUtil.String)
.AddScalar("Comment", NHibernateUtil.String)
.AddScalar("Active", NHibernateUtil.Boolean);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(SU.DTO.ValueObject.SuRTEContentSearchResult))).List <SuRTEContentSearchResult>());
}
public ValidateMilage GetMileageItemForValidationCheckLength(long RequesterId, string CarLicenseNo, DateTime travelDate, long expId, Double meter)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select a.DocumentNo from Document a ");
sqlBuilder.Append(" inner join [dbo].[FnExpenseDocument] b on a.DocumentID = b.DocumentID ");
sqlBuilder.Append(" inner join [dbo].FnExpenseMileage c on b.ExpenseID = c.ExpenseID ");
sqlBuilder.Append(" inner join [dbo].FnExpenseMileageItem d on c.ExpenseMileageID = d.ExpenseMileageID ");
sqlBuilder.Append(" where a.CacheCurrentStateName <> 'Cancel' and c.[Owner] = 'EMP' ");
sqlBuilder.Append(" and a.RequesterID = :RequesterID and c.CarLicenseNo = :CarLicenseNo and d.TravelDate = :travelDate and b.ExpenseID <> :expId ");
sqlBuilder.Append(" and (d.CarMeterStart <= :meter and d.CarMeterEnd > :meter) ");
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
QueryParameterBuilder queryParameterBuilder = new QueryParameterBuilder();
queryParameterBuilder.AddParameterData("RequesterID", typeof(long), RequesterId);
queryParameterBuilder.AddParameterData("CarLicenseNo", typeof(string), CarLicenseNo);
queryParameterBuilder.AddParameterData("travelDate", typeof(DateTime), travelDate);
queryParameterBuilder.AddParameterData("expId", typeof(long), expId);
queryParameterBuilder.AddParameterData("meter", typeof(Double), meter);
queryParameterBuilder.FillParameters(query);
query.AddScalar("DocumentNo", NHibernateUtil.String);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(ValidateMilage))).List <ValidateMilage>().FirstOrDefault());
}
public IList <RoleDto> FindUserRoles(string userName)
{
var s = EntityManager.CurrentSession;
string hql = @"
SELECT
r.Id as Id,
r.RoleName as RoleName
FROM B_UserInRoles uir
JOIN uir.Role r
WHERE
uir.User.Login = :userName AND
uir.Status = :status
";
var list = s.CreateQuery(hql)
.SetString("userName", userName)
.SetInt32("status", (int)EntityStatus.Active)
.SetResultTransformer(Transformers.AliasToBean <RoleDto>())
.List <RoleDto>();
return(list);
}
/// <summary>
/// query for bind ddl (PaymentType)
/// </summary>
/// <param name="groupStatus"></param>
/// <param name="languageID"></param>
/// <returns></returns>
public IList <PaymentTypeListItem> GetPbListItem(long companyID, short languageID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT PB.PBID as ID, PB.PBCode as Code , PBL.Description as Text");
sqlBuilder.Append(" FROM DbPB as PB");
sqlBuilder.Append(" LEFT JOIN DBPBLang as PBL on PB.PBID = PBL.PBID");
sqlBuilder.Append(" WHERE PB.CompanyID = :companyID AND PBL.LanguageID =:languageID AND PB.Active = 1 AND PBL.Active = 1 ");
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
parameterBuilder.AddParameterData("companyID", typeof(long), companyID);
parameterBuilder.AddParameterData("languageID", typeof(string), languageID);
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
query.AddScalar("Code", NHibernateUtil.String)
.AddScalar("Text", NHibernateUtil.String)
.AddScalar("ID", NHibernateUtil.Int64);
return(query.SetResultTransformer(Transformers.AliasToBean(typeof(PaymentTypeListItem))).List <PaymentTypeListItem>());
}
public int GetBottlesAtCouterpartyAndDeliveryPoint(IUnitOfWork UoW, Counterparty counterparty, DeliveryPoint deliveryPoint, DateTime?before = null)
{
BottlesMovementOperation operationAlias = null;
BottlesBalanceQueryResult result = null;
var queryResult = UoW.Session.QueryOver <BottlesMovementOperation>(() => operationAlias)
.Where(() => operationAlias.Counterparty == counterparty)
.Where(() => operationAlias.DeliveryPoint == deliveryPoint);
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 List <DatabaseItemDto> GetByClass(string itemClass)
{
using (ISession session = SessionCreator.OpenSession()) {
using (ITransaction transaction = session.BeginTransaction()) {
var subquery = $"SELECT {DatabaseItemStatTable.Item} FROM {DatabaseItemStatTable.Table} " +
$" WHERE {DatabaseItemStatTable.Stat} = 'Class' AND {DatabaseItemStatTable.TextValue} = :itemClass";
var sql = string.Join(" ",
$" SELECT {DatabaseItemTable.Record} as Record, ",
$" {DatabaseItemTable.Name} as Name, ",
$" {DatabaseItemStatTable.Stat} as Stat, {DatabaseItemStatTable.Value} as Value, {DatabaseItemStatTable.TextValue} AS TextValue",
$" FROM {DatabaseItemTable.Table} i, {DatabaseItemStatTable.Table} s ",
$" WHERE i.{DatabaseItemTable.Id} = s.{DatabaseItemStatTable.Item} ",
$" AND i.{DatabaseItemTable.Id} IN ({subquery})"
);
IQuery query = session.CreateSQLQuery(sql)
.SetParameter("itemClass", itemClass)
.SetResultTransformer(Transformers.AliasToBean <InteralRowStat>());
return(ToDto(query.List <InteralRowStat>()));
}
}
}
private void FillDrivers()
{
DriverNode resultAlias = null;
Employee employeeAlias = null;
_driversList = UoW.Session.QueryOver <Employee>(() => employeeAlias)
.Where(
() => employeeAlias.Status != EmployeeStatus.IsFired &&
(employeeAlias.Category == EmployeeCategory.driver || employeeAlias.Category == EmployeeCategory.forwarder) &&
!employeeAlias.VisitingMaster &&
employeeAlias.Status != EmployeeStatus.OnCalculation
)
.SelectList(list => list
.Select(() => employeeAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => employeeAlias.Name).WithAlias(() => resultAlias.Name)
.Select(() => employeeAlias.LastName).WithAlias(() => resultAlias.LastName)
.Select(() => employeeAlias.Patronymic).WithAlias(() => resultAlias.Patronymic)
.Select(() => employeeAlias.Category).WithAlias(() => resultAlias.Category)
.Select(() => employeeAlias.FirstWorkDay).WithAlias(() => resultAlias.FirstWorkDay))
.OrderBy(e => e.LastName).Asc.ThenBy(x => x.Name).Asc
.TransformUsing(Transformers.AliasToBean <DriverNode>())
.List <DriverNode>();
}
public List <ImageSortItem> GetFilesToSort(MediaCategory category = null)
{
IQueryOver <MediaFile, MediaFile> query = _session.QueryOver <MediaFile>();
query = category != null
? query.Where(file => file.MediaCategory.Id == category.Id)
: query.Where(file => file.MediaCategory == null);
query = query.OrderBy(x => x.DisplayOrder).Asc;
ImageSortItem item = null;
return(query.SelectList(builder =>
{
builder.Select(file => file.FileName).WithAlias(() => item.Name);
builder.Select(file => file.Id).WithAlias(() => item.Id);
builder.Select(file => file.DisplayOrder).WithAlias(() => item.Order);
builder.Select(file => file.FileExtension).WithAlias(() => item.FileExtension);
builder.Select(file => file.FileUrl).WithAlias(() => item.ImageUrl);
return builder;
}).TransformUsing(Transformers.AliasToBean <ImageSortItem>())
.List <ImageSortItem>().ToList());
}
public IList <TDto> GetBySql <TDto>(Pager pager, String countSql, String sql, IDictionary <string, Object> namedParameters)
{
Check.Require(pager != null, "pager may not be null!");
CheckSqlInjection(sql);
ISQLQuery q = Session.CreateSQLQuery(sql);
ISQLQuery cq = Session.CreateSQLQuery(countSql);
if (namedParameters != null)
{
foreach (var namedParameter in namedParameters)
{
q.SetParameter(namedParameter.Key, namedParameter.Value);
cq.SetParameter(namedParameter.Key, namedParameter.Value);;
}
}
pager.ItemCount = (int)cq.UniqueResult();
if (pager.PageSize > 0)
{
q.SetFirstResult(pager.FromRowIndex).SetMaxResults(pager.PageSize);
}
return(q.SetResultTransformer(
Transformers.AliasToBean <TDto>()).List <TDto>());
}
public IList <NotificationModel> GetNotifications()
{
var user = CurrentRequestData.CurrentUser;
var queryOver = _session.QueryOver <Notification>();
if (user.LastNotificationReadDate.HasValue)
{
queryOver = queryOver.Where(notification => notification.CreatedOn >= user.LastNotificationReadDate);
}
NotificationModel notificationModelAlias = null;
return(queryOver.SelectList(
builder =>
builder.Select(notification => notification.Message)
.WithAlias(() => notificationModelAlias.Message)
.Select(notification => notification.CreatedOn)
.WithAlias(() => notificationModelAlias.DateValue))
.OrderBy(notification => notification.CreatedOn).Desc
.TransformUsing(Transformers.AliasToBean <NotificationModel>())
.Take(15)
.List <NotificationModel>());
}
public async Task <IList <ProductSummary> > GetSaleSummariesAsync(CancellationToken cancellationToken = default(CancellationToken))
{
using (var session = OpenSession())
using (var tx = session.BeginTransaction())
{
var criteria = session
.CreateCriteria <Order>("O")
.CreateCriteria("O.OrderLines", "OI", SqlCommand.JoinType.InnerJoin)
.CreateCriteria("OI.Product", "P", SqlCommand.JoinType.InnerJoin);
var summaeries = await(criteria
.SetProjection(Projections.ProjectionList().Add(Projections.Property("P.ProductId"), "ProductId")
.Add(Projections.Property("P.Name"), "Name")
.Add(Projections.Sum(Projections.Cast(NHibernateUtil.Int32, Projections.Property("OI.Quantity"))), "TotalQuantity")
.Add(Projections.Sum("OI.UnitPrice"), "TotalPrice")
.Add(Projections.GroupProperty("P.ProductId"))
.Add(Projections.GroupProperty("P.Name")))
.SetResultTransformer(Transformers.AliasToBean(typeof(ProductSummary)))
.ListAsync <ProductSummary>(cancellationToken));
await(tx.CommitAsync(cancellationToken));
return(summaeries);
}
}
public async Task ProjectTransformToDtoAsync()
{
using (var s = OpenSession())
using (s.BeginTransaction())
{
PersonSummary summary = null;
var actual = await(s.QueryOver <Person>()
.SelectList(list => list
.SelectGroup(p => p.Name).WithAlias(() => summary.Name)
//GH1985: DateTime.xxxx are not supported in SelectGroup
.SelectGroup(p => p.BirthDate.Year).WithAlias(() => summary.BirthYear)
.Select(Projections.RowCount()).WithAlias(() => summary.Count))
.OrderByAlias(() => summary.Name).Asc
.TransformUsing(Transformers.AliasToBean <PersonSummary>())
.ListAsync <PersonSummary>());
Assert.That(actual.Count, Is.EqualTo(2));
Assert.That(actual[0].Name, Is.EqualTo("test person 1"));
Assert.That(actual[0].Count, Is.EqualTo(2));
Assert.That(actual[1].Name, Is.EqualTo("test person 2"));
Assert.That(actual[1].Count, Is.EqualTo(1));
}
}
public PagedList <DemoArticle> GetArticleByTags(IEnumerable <Tag> tags, ArticleCriteria cr)
{
using (var session = sessionFactory.OpenSession())
{
ICriteria filter = session.CreateCriteria(typeof(Article));
if (cr.LastId > 0)
{
filter.Add(Restrictions.Lt("Id", cr.LastId));
}
else
{
filter.SetFirstResult(cr.StartFrom);
}
filter.CreateAlias("Tags", "tag");
filter.Add(Expression.In("tag.Id", tags.Select(m => m.Id).ToArray()));
//criteria.SetProjection(Projections.Distinct(Projections.Property("Id")));
var result = new PagedList <DemoArticle>();
var countCreteria = (ICriteria)filter.Clone();
result.AddRange(
filter
.SetProjection(Projections.Distinct(Projections.ProjectionList()
.Add(Projections.Id(), "Id")
.Add(Projections.Property("Title"), "Title")
.Add(Projections.Property("Image"), "Image")
.Add(Projections.Property("ShortDescription"), "ShortDescription")
.Add(Projections.Property("CreateDate"), "CreateDate")
.Add(Projections.Property("LastUpdateDate"), "LastUpdateDate")))
.AddOrder(Order.Desc("Id"))
.SetResultTransformer(Transformers.AliasToBean <DemoArticle>())
.List <DemoArticle>());
result.LinesCount = countCreteria.SetProjection(Projections.RowCount()).UniqueResult <int>();
result.PageCount = (int)Math.Ceiling(result.LinesCount / (double)cr.Count);
return(result);
}
}
public IEnumerable <NomanclatureStockNode> GetWarehouseNomenclatureStock(IUnitOfWork uow, int warehouseId)
{
NomanclatureStockNode resultAlias = null;
Nomenclature nomenclatureAlias = null;
WarehouseMovementOperation warehouseOperation = null;
IProjection incomeAmount = Projections.Sum(
Projections.Conditional(
Restrictions.Eq(Projections.Property(() => warehouseOperation.IncomingWarehouse.Id), warehouseId),
Projections.Property(() => warehouseOperation.Amount),
Projections.Constant(0M)
)
);
IProjection writeoffAmount = Projections.Sum(
Projections.Conditional(
Restrictions.Eq(Projections.Property(() => warehouseOperation.WriteoffWarehouse.Id), warehouseId),
Projections.Property(() => warehouseOperation.Amount),
Projections.Constant(0M)
)
);
IProjection stockProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "( IFNULL(?1, 0) - IFNULL(?2, 0) )"),
NHibernateUtil.Int32,
incomeAmount,
writeoffAmount
);
return(uow.Session.QueryOver(() => warehouseOperation)
.Left.JoinAlias(() => warehouseOperation.Nomenclature, () => nomenclatureAlias)
.SelectList(list => list
.SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.NomenclatureId)
.Select(stockProjection).WithAlias(() => resultAlias.Stock)
)
.TransformUsing(Transformers.AliasToBean <NomanclatureStockNode>())
.List <NomanclatureStockNode>());
}
public override void UpdateNodes()
{
ServiceClaimVMNode resultAlias = null;
ServiceClaim serviceClaimAlias = null;
Counterparty counterpartyAlias = null;
Nomenclature nomenclatureAlias = null;
var query = UoW.Session.QueryOver <ServiceClaim> (() => serviceClaimAlias);
if (Filter.RestrictServiceClaimStatus != null)
{
query.Where(c => c.Status == Filter.RestrictServiceClaimStatus);
}
if (Filter.RestrictServiceClaimType != null)
{
query.Where(c => c.ServiceClaimType == Filter.RestrictServiceClaimType);
}
var result = query
.JoinAlias(sc => sc.Counterparty, () => counterpartyAlias)
.JoinAlias(sc => sc.Nomenclature, () => nomenclatureAlias)
.SelectList(list => list
.Select(() => serviceClaimAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => serviceClaimAlias.Status).WithAlias(() => resultAlias.Status)
.Select(() => serviceClaimAlias.ServiceStartDate).WithAlias(() => resultAlias.StartDate)
.Select(() => serviceClaimAlias.ServiceClaimType).WithAlias(() => resultAlias.Type)
.Select(() => counterpartyAlias.FullName).WithAlias(() => resultAlias.Counterparty)
.Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.Nomenclature)
)
.OrderBy(x => x.ServiceStartDate).Desc
.TransformUsing(Transformers.AliasToBean <ServiceClaimVMNode> ())
.List <ServiceClaimVMNode> ();
SetItemsSource(result);
}
public IEnumerable <StatisticsPoint> CrashesPerWeek()
{
return(_sessionProvider.Session.CreateSQLQuery(@"
;with errors as (
select
datediff(day, Timestamp, GetDate()) / 1 dateKey,
count(*) Mark,
0 isFake
from crash e
where
e.LogLevel = 'Fatal' and
Timestamp > '2015-10-01'
group by
datediff(day, Timestamp, GetDate()) / 1
),
extendedErrors as (
select * from errors
union all
select 0 dateKey, 0 Mark, 1 isFake
),
alignedErrors as (
select
*,
row_number() over (partition by dateKey order by isFake) idx
from extendedErrors
)
select
-dateKey Date,
alignedErrors.Mark Value
from alignedErrors
where alignedErrors.idx = 1
order by alignedErrors.dateKey desc
" )
.SetResultTransformer(Transformers.AliasToBean(typeof(StatisticsPoint)))
.List <StatisticsPoint>());
}