public async Task <SearchResult <Classifier> > Search(ClassifierSearchRequest request, CancellationToken cancellationToken)
{
var type = await _classifierTypeService.Get(request.TypeCode, cancellationToken);
using (var db = _dbContextFactory.Create())
{
var result = await SearchInternal(db, type, request, cancellationToken);
// search in data by Uid is not effective - O(n), but ok for small collections
if (request.FocusUid.HasValue && result.Rows.Any(x => x.Uid == request.FocusUid) == false)
{
// todo: add test
var focus = await SearchInternal(db, type, new ClassifierSearchRequest
{
Uid = request.FocusUid,
SkipPaging = true
}, cancellationToken);
for (var i = focus.Rows.Count - 1; i >= 0; i--)
{
result.Rows.Insert(0, focus.Rows[i]);
}
}
// todo: preload fields for multiple items or (?) store fields in the same table?
if (request.IncludeFields)
{
var metadata = await _metadataService.GetMetadata(type, cancellationToken);
foreach (var item in result.Rows)
{
var fields = await _fieldDataRepository.Search(new FieldDataSearchRequest
{
Metadata = metadata,
EntityTypeCode = Classifier.TypeCode,
// ReSharper disable once PossibleInvalidOperationException
EntityUids = new[] { item.Uid.Value }
}, cancellationToken);
item.Fields = fields.Rows.SingleOrDefault();
}
}
return(result);
}
}
protected IQueryable <DbClassifier> BuildQuery(DbContext db, ClassifierType type, ClassifierSearchRequest request)
{
IQueryable <DbClassifier> query = null;
if (type.HierarchyType == HierarchyType.Groups)
{
if (request.GroupUid != null)
{
if (request.Depth == null || request.Depth == "0") // todo: use constant
{
query = from trees in db.GetTable <DbClassifierTree>()
join childrenGroups in db.GetTable <DbClassifierGroup>() on trees.Uid equals childrenGroups.TreeUid
join links in db.GetTable <DbClassifierLink>() on childrenGroups.Uid equals links.GroupUid
join c in db.GetTable <DbClassifier>() on links.ItemUid equals c.Uid
where trees.TypeUid == type.Uid &&
trees.Uid == request.TreeUid &&
childrenGroups.Uid == request.GroupUid
select c;
}
else
{
query = from trees in db.GetTable <DbClassifierTree>()
join parentGroups in db.GetTable <DbClassifierGroup>() on trees.Uid equals parentGroups.TreeUid
join closures in db.GetTable <DbClassifierClosure>() on parentGroups.Uid equals closures.ParentUid
join childrenGroups in db.GetTable <DbClassifierGroup>() on closures.ChildUid equals childrenGroups
.Uid
join links in db.GetTable <DbClassifierLink>() on childrenGroups.Uid equals links.GroupUid
join c in db.GetTable <DbClassifier>() on links.ItemUid equals c.Uid
where trees.TypeUid == type.Uid &&
trees.Uid == request.TreeUid &&
parentGroups.Uid == request.GroupUid
select c;
}
}
}
else if (type.HierarchyType == HierarchyType.Items)
{
if (request.GroupUid != null)
{
if (request.Depth == null || request.Depth == "0") // todo: use enum or constant
{
query = from parent in db.GetTable <DbClassifier>()
join @class in db.GetTable <DbClassifier>() on parent.Uid equals @class.ParentUid
where parent.TypeUid == type.Uid && parent.Uid == request.GroupUid
select @class;
}
else
{
query = from parent in db.GetTable <DbClassifier>()
join closures in db.GetTable <DbClassifierClosure>() on parent.Uid equals closures.ParentUid
join @class in db.GetTable <DbClassifier>() on closures.ChildUid equals @class.Uid
where parent.TypeUid == type.Uid && parent.Uid == request.GroupUid && closures.Level > 0
select @class;
}
}
}
if (query == null)
{
query = from c in db.GetTable <DbClassifier>()
where c.TypeUid == type.Uid
select c;
}
if (request.Uid != null)
{
query = query.Where(x => x.Uid == request.Uid);
}
if (request.Uids != null)
{
query = query.Where(x => request.Uids.Contains(x.Uid));
}
if (request.Code != null)
{
query = query.Where(x => x.Code == request.Code);
}
if (request.SearchTerm != null)
{
query = query.Where(x => SqlExpr.ILike(x.Name, "%" + request.SearchTerm + "%"));
// query = query.Where(x => Sql.Like(x.Name, "%" + request.SearchTerm + "%"));
// query = query.Where(x => x.Name.Contains(request.SearchTerm));
}
return(query);
}
/// <summary>
/// Filter by all parameters except FocusUid
/// </summary>
protected virtual async Task <SearchResult <Classifier> > SearchInternal(DbContext db,
ClassifierType type, ClassifierSearchRequest request, CancellationToken cancellationToken)
{
var query = BuildQuery(db, type, request);
var data = await query
.Apply(request, x => x.Code)
.Select(x => Materialize(type, x))
.Cast <Classifier>()
.ToListAsync(cancellationToken);
return(new SearchResult <Classifier>
{
TotalCount = query.GetTotalCount(request),
Rows = data
});
}
protected override async Task <SearchResult <Classifier> > SearchInternal(DbContext db,
ClassifierType type, ClassifierSearchRequest request, CancellationToken cancellationToken)
{
var classifiers = BuildQuery(db, type, request);
var dbMessageTemplates = db.GetTable <DbMessageTemplate>().AsQueryable();
var joined = from classifier in classifiers
join dbMessageTemplate in dbMessageTemplates on classifier.Uid equals dbMessageTemplate.Uid
select new DbItem {
Classifier = classifier, MessageTemplate = dbMessageTemplate
};
// todo: fix paging - map column to expression
request.SortColumn ??= nameof(Classifier.Code);
request.SortColumn = nameof(DbItem.Classifier) + "." + request.SortColumn;
var data = await joined
.Apply(request, x => x.Classifier.Code)
.Select(x => Materialize(type, x))
.Cast <Classifier>()
.ToListAsync(cancellationToken);
return(new SearchResult <Classifier>
{
TotalCount = joined.GetTotalCount(request),
Rows = data
});
}
protected override async Task <SearchResult <Classifier> > SearchInternal(DbContext db,
ClassifierType type, ClassifierSearchRequest request, CancellationToken cancellationToken)
{
var classifiers = BuildQuery(db, type, request);
var numerators = db.GetTable <DbNumerator>().AsQueryable();
var numeratorRequest = request as NumeratorSearchRequest;
if (numeratorRequest?.EntityTypeCode != null && numeratorRequest.EntityTypeUid != null)
{
var numeratorEntities = db.GetTable <DbNumeratorEntity>().Where(x => x.EntityUid == numeratorRequest.EntityTypeUid);
if (numeratorRequest.IsAutoNumbering != null)
{
numeratorEntities = numeratorEntities.Where(x => x.IsAutoNumbering == numeratorRequest.IsAutoNumbering);
}
numerators = from n in numerators.Where(x => x.EntityTypeCode == numeratorRequest.EntityTypeCode)
join ne in numeratorEntities on n.Uid equals ne.NumeratorUid
select n;
}
var joined = from classifier in classifiers
join numerator in numerators on classifier.Uid equals numerator.Uid
select new DbItem {
Classifier = classifier, Numerator = numerator
};
// todo: fix paging - map column to expression
request.SortColumn ??= nameof(Classifier.Code);
request.SortColumn = nameof(DbItem.Classifier) + "." + request.SortColumn;
var data = await joined
.Apply(request, x => x.Classifier.Code)
.Select(x => Materialize(type, x))
.Cast <Classifier>()
.ToListAsync(cancellationToken);
return(new SearchResult <Classifier>
{
TotalCount = joined.GetTotalCount(request),
Rows = data
});
}
protected override async Task <SearchResult <Classifier> > SearchInternal(DbContext db,
ClassifierType type, ClassifierSearchRequest request, CancellationToken cancellationToken)
{
var classifiers = BuildQuery(db, type, request);
var users = db.GetTable <DbUser>().AsQueryable();
if (request is UserSearchRequest userRequest)
{
if (userRequest.UserName != null)
{
users = users.Where(x => x.UserName == userRequest.UserName);
}
}
var joined = from classifier in classifiers
join user in users on classifier.Uid equals user.Id
select new DbItem {
Classifier = classifier, User = user
};
// todo: fix paging - map column to expression
request.SortColumn ??= nameof(Classifier.Code);
request.SortColumn = nameof(DbItem.Classifier) + "." + request.SortColumn;
var data = await joined
.Apply(request, x => x.Classifier.Code)
.Select(x => Materialize(type, x))
.Cast <Classifier>()
.ToListAsync(cancellationToken);
return(new SearchResult <Classifier>
{
TotalCount = joined.GetTotalCount(request),
Rows = data
});
}