private IQueryOver <Premium> GetPremiumsQuery(IUnitOfWork uow)
{
PremiumJournalNode resultAlias = null;
Premium premiumAlias = null;
PremiumItem premiumItemAlias = null;
Employee employeeAlias = null;
var query = uow.Session.QueryOver <Premium>(() => premiumAlias)
.JoinAlias(f => f.Items, () => premiumItemAlias)
.JoinAlias(() => premiumItemAlias.Employee, () => employeeAlias);
if (FilterViewModel.Subdivision != null)
{
query.Where(() => employeeAlias.Subdivision.Id == FilterViewModel.Subdivision.Id);
}
if (FilterViewModel.StartDate.HasValue)
{
query.Where(() => premiumAlias.Date >= FilterViewModel.StartDate.Value);
}
if (FilterViewModel.EndDate.HasValue)
{
query.Where(() => premiumAlias.Date <= FilterViewModel.EndDate.Value);
}
var employeeProjection = CustomProjections.Concat_WS(
" ",
() => employeeAlias.LastName,
() => employeeAlias.Name,
() => employeeAlias.Patronymic
);
query.Where(
GetSearchCriterion(
() => premiumAlias.Id,
() => premiumAlias.PremiumReasonString,
() => employeeProjection,
() => premiumAlias.TotalMoney
)
);
var resultQuery = query
.SelectList(list => list
.SelectGroup(() => premiumAlias.Id).WithAlias(() => resultAlias.Id)
.Select(() => premiumAlias.Date).WithAlias(() => resultAlias.Date)
.Select(CustomProjections.GroupConcat(
employeeProjection,
false,
employeeProjection,
OrderByDirection.Asc,
"\n")
).WithAlias(() => resultAlias.EmployeesName)
.Select(() => premiumAlias.PremiumReasonString).WithAlias(() => resultAlias.PremiumReason)
.Select(() => premiumAlias.TotalMoney).WithAlias(() => resultAlias.PremiumSum)
).OrderBy(o => o.Date).Desc
.TransformUsing(Transformers.AliasToBean <PremiumJournalNode <Premium> >());
return(resultQuery);
}
protected override IQueryOver <EmployeeCard> ItemsQuery(IUnitOfWork uow)
{
EmployeeProcessingJournalNode resultAlias = null;
Post postAlias = null;
Subdivision subdivisionAlias = null;
EmployeeCard employeeAlias = null;
Norm normAlias = null;
var employees = uow.Session.QueryOver <EmployeeCard>(() => employeeAlias);
if (Filter.ShowOnlyWork)
{
employees.Where(x => x.DismissDate == null);
}
if (Filter.Subdivision != null)
{
employees.Where(x => x.Subdivision.Id == Filter.Subdivision.Id);
}
if (Filter.Department != null)
{
employees.Where(x => x.Department.Id == Filter.Department.Id);
}
var normProjection = CustomProjections.GroupConcat(Projections.SqlFunction("coalesce", NHibernateUtil.String, Projections.Property(() => normAlias.Name), Projections.Property(() => normAlias.Id)), separator: "\n");
return(employees
.Where(GetSearchCriterion(
() => employeeAlias.Id,
() => employeeAlias.CardNumber,
() => employeeAlias.PersonnelNumber,
() => employeeAlias.LastName,
() => employeeAlias.FirstName,
() => employeeAlias.Patronymic,
() => postAlias.Name,
() => subdivisionAlias.Name
))
.JoinAlias(() => employeeAlias.Post, () => postAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.JoinAlias(() => employeeAlias.Subdivision, () => subdivisionAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Left.JoinAlias(() => employeeAlias.UsedNorms, () => normAlias)
.SelectList((list) => list
.SelectGroup(x => x.Id).WithAlias(() => resultAlias.Id)
.Select(x => x.CardNumber).WithAlias(() => resultAlias.CardNumber)
.Select(x => x.PersonnelNumber).WithAlias(() => resultAlias.PersonnelNumber)
.Select(x => x.FirstName).WithAlias(() => resultAlias.FirstName)
.Select(x => x.LastName).WithAlias(() => resultAlias.LastName)
.Select(x => x.Patronymic).WithAlias(() => resultAlias.Patronymic)
.Select(() => employeeAlias.DismissDate).WithAlias(() => resultAlias.DismissDate)
.Select(() => postAlias.Name).WithAlias(() => resultAlias.Post)
.Select(() => subdivisionAlias.Name).WithAlias(() => resultAlias.Subdivision)
.Select(normProjection).WithAlias(() => resultAlias.Norms)
)
.OrderBy(() => employeeAlias.LastName).Asc
.ThenBy(() => employeeAlias.FirstName).Asc
.ThenBy(() => employeeAlias.Patronymic).Asc
.TransformUsing(Transformers.AliasToBean <EmployeeProcessingJournalNode>()));
}
protected override IQueryOver <IssuanceSheet> ItemsQuery(IUnitOfWork uow)
{
IssuanceSheetJournalNode resultAlias = null;
IssuanceSheet issuanceSheetAlias = null;
IssuanceSheetItem issuanceSheetItemAlias = null;
Organization organizationAlias = null;
Subdivision subdivisionAlias = null;
EmployeeCard employeeCardAlias = null;
var employeesSubquery = QueryOver.Of <IssuanceSheetItem>(() => issuanceSheetItemAlias)
.Where(() => issuanceSheetItemAlias.IssuanceSheet.Id == issuanceSheetAlias.Id)
.JoinQueryOver(x => x.Employee, () => employeeCardAlias)
.Select(CustomProjections
.GroupConcat(Projections
.Property(() => employeeCardAlias.LastName), useDistinct: true, separator: ", "));
var query = uow.Session.QueryOver <IssuanceSheet>(() => issuanceSheetAlias);
if (Filter.StartDate != null)
{
query.Where(x => x.Date >= Filter.StartDate);
}
if (Filter.EndDate != null)
{
query.Where(x => x.Date <= Filter.EndDate);
}
return(query
.Where(GetSearchCriterion(
() => issuanceSheetAlias.Id,
() => issuanceSheetAlias.Expense.Id,
() => issuanceSheetAlias.CollectiveExpense.Id,
() => organizationAlias.Name,
() => subdivisionAlias.Name,
() => subdivisionAlias.Code
))
.Left.JoinAlias(s => s.Organization, () => organizationAlias)
.Left.JoinAlias(s => s.Subdivision, () => subdivisionAlias)
.SelectList((list) => list
.Select(x => x.Id).WithAlias(() => resultAlias.Id)
.Select(x => x.Date).WithAlias(() => resultAlias.Date)
.Select(() => organizationAlias.Name).WithAlias(() => resultAlias.Organigation)
.Select(() => subdivisionAlias.Name).WithAlias(() => resultAlias.Subdivision)
.Select(() => subdivisionAlias.Code).WithAlias(() => resultAlias.SubdivisionCode)
.Select(x => x.Expense.Id).WithAlias(() => resultAlias.DocExpenseId)
.Select(x => x.CollectiveExpense.Id).WithAlias(() => resultAlias.DocCollectiveExpenseId)
.SelectSubQuery(employeesSubquery).WithAlias(() => resultAlias.Employees)
)
.OrderBy(() => issuanceSheetAlias.Date).Desc
.ThenBy(() => issuanceSheetAlias.Id).Desc
.TransformUsing(Transformers.AliasToBean <IssuanceSheetJournalNode>()));
}
