public PagedResult<SystemPermission> GetPermissionPaging(int companyId, string keyword, bool? isDeleted, PagingInput pagingInput)
{
var query = GetPermissions(companyId, keyword, isDeleted);
var result = new PagedResult<SystemPermission>(query);
result.SetPaging(pagingInput);
return result;
}
public ActionResult Index(string id, ActivitiesQueryInput activityInput, PagingInput pagingInput)
{
string userId = VerbosifyId <User>(id);
if (!_permissionManager.DoesExist <User>(userId))
{
return(HttpNotFound());
}
var userResult = _documentSession
.Query <All_Users.Result, All_Users>()
.AsProjection <All_Users.Result>()
.Where(x => x.UserId == userId)
.First();
dynamic viewModel = new ExpandoObject();
viewModel.User = _userViewModelQuery.BuildUser(userId);
//viewModel.Activities = _activityViewModelQuery.BuildGroupActivityList(userResult.User.UserProject.Id, activityInput, pagingInput);
viewModel.Activities = _activityViewModelQuery.BuildUserActivityList(userResult.User.Id, activityInput, pagingInput);
//viewModel.IsMember = _userContext.IsUserAuthenticated() ? _userContext.HasGroupPermission<Project>(PermissionNames.CreateObservation, projectId) : false;
viewModel.SightingCountDescription = "Sighting" + (userResult.SightingCount == 1 ? string.Empty : "s");
viewModel.ProjectCountDescription = "Project" + (userResult.Projects.Count() == 1 ? string.Empty : "s");
viewModel.OrganisationCountDescription = "Organisation" + (userResult.Organisations.Count() == 1 ? string.Empty : "s");
viewModel.ShowActivities = true;
return(RestfulResult(
viewModel,
"users",
"index"));
}
/// <summary>
/// 分页查询文章列表
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task <PagedResultDto <QueryPostDto> > QueryPosts(PagingInput input)
{
var posts = await _postRepository.GetAllListAsync();
var count = posts.Count;
var list = posts.OrderByDescending(x => x.CreationTime).AsQueryable().PageByIndex(input.Page, input.Limit).ToList();
var dtos = list.MapTo <IList <PostBriefDto> >().ToList();
dtos.ForEach(x =>
{
x.CreationTime = Convert.ToDateTime(x.CreationTime).ToString("MMMM dd, yyyy HH:mm:ss", new CultureInfo("en-us"));
x.Year = Convert.ToDateTime(x.CreationTime).Year;
});
var result = new List <QueryPostDto>();
var group = dtos.GroupBy(x => x.Year).ToList();
group.ForEach(x =>
{
result.Add(new QueryPostDto
{
Year = x.Key,
Posts = x.ToList()
});
});
return(new PagedResultDto <QueryPostDto>(count, result));
}
public List<DicReader> GetPlistVisitingLogTrs(string key, DateTime? leftVisitOn, DateTime? rightVisitOn, PagingInput paging)
{
paging.Valid();
if (key != null)
{
key = key.Trim();
}
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
var filterString = @" where a.LoginName like @key ";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("VisitingLog", filter, paging);
}
public PagedResult<Company> GetCompaniesPaging(string keyword, bool? isActived, bool? isRootSite, bool? isDeleted, PagingInput pagingInput)
{
var query = GetCompanies(keyword, isActived, isRootSite, isDeleted);
var result = new PagedResult<Company>(query);
result.SetPaging(pagingInput);
return result;
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filterCallback"></param>
/// <param name="paging"></param>
/// <returns></returns>
protected IList <T> GetPlist <T>(Func <ObjectFilter> filterCallback, PagingInput paging) where T : class, IEntity <Guid>
{
paging.Valid();
string setName = typeof(T).Name + "s";
ObjectFilter filter = ObjectFilter.Empty;
if (filterCallback != null)
{
filter = filterCallback();
}
var queryString =
@"select value a from " + setName + " as a " + filter.FilterString + " order by a." + paging.SortField + " " + paging.SortOrder;
var countQs =
@"select value a from " + setName + " as a " + filter.FilterString;
IQueryable <T> countQuery;
IQueryable <T> query;
if (filter.Parameters != null)
{
countQuery = this.DbContext.CreateQuery <T>(countQs, filter.Parameters);
query = this.DbContext.CreateQuery <T>(queryString, filter.Parameters)
.Skip(paging.SkipCount).Take(paging.PageSize);
}
else
{
countQuery = this.DbContext.CreateQuery <T>(countQs);
query = this.DbContext.CreateQuery <T>(queryString)
.Skip(paging.SkipCount).Take(paging.PageSize);
}
paging.Total = countQuery.Count();
return(query.ToList <T>());
}
public ActionResult Index(string id, ActivitiesQueryInput activityInput, PagingInput pagingInput)
{
string organisationId = VerbosifyId <Organisation>(id);
if (!_permissionManager.DoesExist <Organisation>(organisationId))
{
return(HttpNotFound());
}
var organisationResult = _documentSession
.Query <All_Groups.Result, All_Groups>()
.AsProjection <All_Groups.Result>()
.First(x => x.GroupId == organisationId);
dynamic viewModel = new ExpandoObject();
viewModel.Organisation = _organisationViewModelQuery.BuildOrganisation(organisationId);
viewModel.Activities = _activityViewModelQuery.BuildGroupActivityList(organisationId, activityInput, pagingInput);
viewModel.IsMember = _userContext.IsUserAuthenticated() ? _userContext.HasGroupPermission <Organisation>(PermissionNames.CreateObservation, organisationId) : false;
viewModel.UserCountDescription = "Member" + (organisationResult.UserCount == 1 ? string.Empty : "s");
viewModel.PostCountDescription = "Post" + (organisationResult.PostCount == 1 ? string.Empty : "s");
viewModel.ShowActivities = true;
return(RestfulResult(
viewModel,
"organisations",
"index"));
}
public async Task <PagedResponse <QueryPostDto> > QueryPostsAsync([FromQuery] PagingInput input)
{
var posts = await _context.Posts.ToListAsync();
var count = posts.Count;
var result = posts.OrderByDescending(x => x.CreationTime)
.Skip((input.Page - 1) * input.Limit)
.Take(input.Limit)
.Select(x => new PostBriefDto
{
Title = x.Title,
Url = x.Url,
Year = Convert.ToDateTime(x.CreationTime).Year,
CreationTime = Convert.ToDateTime(x.CreationTime).ToString("MMMM dd, yyyy HH:mm:ss", new CultureInfo("en-us"))
})
.GroupBy(x => x.Year)
.Select(x => new QueryPostDto
{
Year = x.Key,
Posts = x.ToList()
}).ToList();
return(new PagedResponse <QueryPostDto>(count, result));
}
public IList <ExceptionLog> GetPlistExceptionLogs(List <FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService <ISqlFilterStringBuilder>();
RdbDescriptor db = GetExceptionLogDb();
List <DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString;
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from ExceptionLog as t" + filterString;
var exceptionLogs = new List <ExceptionLog>();
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "ExceptionLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
exceptionLogs.Add(ExceptionLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray());
reader.Close();
return(exceptionLogs);
}
public List <DicReader> GetPlistCatalogAccountTrs(string key, string catalogCode
, bool includeDescendants, PagingInput paging)
{
paging.Valid();
if (string.IsNullOrEmpty(catalogCode))
{
throw new ArgumentNullException("catalogCode");
}
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
var filterString = " where (a.Name like @key or a.Code like @key or a.LoginName like @key)";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (!includeDescendants)
{
parameters.Add(CreateParameter("CatalogCode", catalogCode, DbType.String));
filterString += " and a.CatalogCode=@CatalogCode";
}
else
{
parameters.Add(CreateParameter("CatalogCode", catalogCode + "%", DbType.String));
filterString += " and a.CatalogCode like @CatalogCode";
}
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("CatalogAccountTr", filter, paging));
}
public List<DicReader> GetPlistCatalogAccountTrs(string key, string catalogCode
, bool includeDescendants, PagingInput paging)
{
paging.Valid();
if (string.IsNullOrEmpty(catalogCode))
{
throw new ArgumentNullException("catalogCode");
}
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
var filterString = " where (a.Name like @key or a.Code like @key or a.LoginName like @key)";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (!includeDescendants)
{
parameters.Add(CreateParameter("CatalogCode", catalogCode, DbType.String));
filterString += " and a.CatalogCode=@CatalogCode";
}
else
{
parameters.Add(CreateParameter("CatalogCode", catalogCode + "%", DbType.String));
filterString += " and a.CatalogCode like @CatalogCode";
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("CatalogAccountTr", filter, paging);
}
public void Filter_Order_Paging_WhenDynamicQueryNetInputIsPassed_ShouldReturnOrderedQueryable()
{
var filterInput = new List <FilterInput> {
new FilterInput
{
Operation = OperationTypeEnum.GreaterThanOrEqual,
Property = "Date",
Value = "2017/04/07",
Type = InputTypeEnum.String
},
new FilterInput
{
Operation = OperationTypeEnum.LessThanOrEqual,
Property = "Date",
Value = "2017/04/10",
Type = InputTypeEnum.String
}
};
var nonFilterInput = new Dictionary <string, string>
{
{ "TestName1", "TestValue1" },
{ "TestName2", "TestValue2" },
{ "TestName3", "TestValue3" }
};
var orderInput = new List <OrderInput>
{
new OrderInput
{
Property = "Number",
Order = OrderTypeEnum.Desc
}
};
var paging = new PagingInput()
{
Page = 2,
Size = 2
};
var orderFilterInput = new DynamicQueryNetInput()
{
Order = orderInput,
Filter = filterInput,
NonFilter = nonFilterInput,
Paging = paging
};
var filteredResult = Mock.QueryableItems.Filter(orderFilterInput);
var normalResult = Mock.QueryableItems.Where(p => (string.Compare(p.Date, "2017/04/07") >= 0) &&
(string.Compare(p.Date, "2017/04/10") <= 0))
.OrderByDescending(p => p.Number).Skip(2 * 2).Take(2);
AssertUtil.EnumarableAreEqual(filteredResult, normalResult);
}
/// <summary>
/// 根据条件分页查询服务器信息
/// </summary>
/// <param name="input">查询条件</param>
/// <returns>服务器信息列表</returns>
public async Task <PagingOutput <NpsServerSearchOutput> > SearchAsync(PagingInput <NpsServerSearchInput> input)
{
var servers = await _npsServerRepository
.WhereIf(input.Filter?.ServerIPAddress.IsNotNullOrEmpty() ?? false, x => x.ServerIPAddress == input.Filter.ServerIPAddress)
.OrderByDescending(x => x.CreateTime)
.ToPagingListAsync <NpsServer, NpsServerSearchOutput>(input, out long count);
return(servers.ToPagingOutput(count));
}
public async Task <Response <PagedResultDto <QueryPostDto> > > QueryPost([FromQuery] PagingInput input)
{
var response = new Response <PagedResultDto <QueryPostDto> >
{
Result = await _blogService.QueryPosts(input)
};
return(response);
}
public PagingOutput <SsoSites> GetPageList(PagingInput input)
{
return(AutofacConfig.Resolve <SsoSitesRepository>().GetPageList(
input.PageIndex,
input.PageSize,
null,
a => a.CreateTime,
true
));
}
public PagingOutput <AccountActionRelation> GetPageList(PagingInput input)
{
return(AutofacConfig.Resolve <AccountActionRelationRepository>().GetPageList(
input.PageIndex,
input.PageSize,
null,
a => a.CreateTime,
true
));
}
public DataTuple GetPlist(
ArchiveState archive,
OrderedElementSet selectElements,
List <FilterData> filters,
PagingInput pagingData)
{
return(this.GetPlistInfoItems(
archive.Ontology, this.GetArchiveDb(archive.Ontology, archive),
selectElements,
filters, pagingData));
}
public JsonResult GetPermissionList(int jtStartIndex = 0, int jtPageSize = 1, string jtSorting = null, string keyword = "")
{
PagingInput paging = new PagingInput (){
PageIndex = jtStartIndex/jtPageSize,
PageSize = jtPageSize,
SortDirection = Mobile.Framework.Extensions.SortDirection.Ascending,
SortPropertyName = "PermissionID"
};
var source = AppGlobal.Services.BusinessPermission.GetPermissionPaging(MobileAuthentication.User.CompanyId.Value, keyword, false,paging);
return Json(new { Result = "OK", Records = source.Results, TotalRecordCount = source.RowCount });
}
/// <summary>
/// 按照目录分页获取指定节点、本体的数据
/// <remarks>
/// 如果传入的目录为空则表示获取全部目录的数据
/// </remarks>
/// </summary>
/// <param name="ontology"></param>
/// <param name="selectElements"></param>
/// <param name="filters">过滤器列表</param>
/// <param name="pagingData"></param>
/// <returns></returns>
public DataTuple GetPlist(
OntologyDescriptor ontology,
OrderedElementSet selectElements,
List <FilterData> filters,
PagingInput pagingData)
{
return(this.GetPlistInfoItems(
ontology, this.GetEntityDb(ontology),
selectElements,
filters, pagingData));
}
public ActionResult List(SpeciesQueryInput speciesQueryInput, PagingInput pagingInput)
{
var viewModel = new
{
Species = _speciesViewModelQuery.BuildSpeciesList(speciesQueryInput, pagingInput)
};
return(RestfulResult(
viewModel,
"species",
"list"));
}
public ActionResult GetPlistBatches(GetPlistBatchs input)
{
if (!ModelState.IsValid)
{
return(ModelState.ToJsonResult());
}
Guid?ontologyId = null;
if (string.IsNullOrEmpty(input.OntologyCode))
{
ontologyId = null;
}
else
{
OntologyDescriptor ontology;
if (!AcDomain.NodeHost.Ontologies.TryGetOntology(input.OntologyCode, out ontology))
{
throw new ValidationException("意外的本体码" + input.OntologyCode);
}
ontologyId = ontology.Ontology.Id;
}
var pagingData = new PagingInput(input.PageIndex
, input.PageSize, input.SortField, input.SortOrder);
if (ontologyId != null)
{
input.Filters.Insert(0, FilterData.EQ("OntologyId", ontologyId.Value));
}
var data = GetRequiredService <IBatchQuery>().GetPlist(base.EntityType, () =>
{
RdbDescriptor rdb;
if (!AcDomain.Rdbs.TryDb(base.EntityType.DatabaseId, out rdb))
{
throw new AnycmdException("意外配置的Batch实体类型对象数据库标识" + base.EntityType.DatabaseId);
}
List <DbParameter> ps;
var filterString = new SqlFilterStringBuilder().FilterString(rdb, input.Filters, "a", out ps);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString;
}
return(new SqlFilter(filterString, ps.ToArray()));
}, pagingData);
Debug.Assert(pagingData.Total != null, "pagingData.Total != null");
return(this.JsonResult(new MiniGrid <BatchTr> {
total = pagingData.Total.Value, data = data.Select(a => new BatchTr(a))
}));
}
public List<DicReader> GetPlistGroupAccountTrs(string key, Guid groupId, PagingInput paging)
{
paging.Valid();
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
const string filterString = @" where (a.Name like @key
or a.Code like @key
or a.LoginName like @key) and a.GroupId=@GroupId";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
parameters.Add(CreateParameter("GroupId", groupId, DbType.Guid));
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("GroupAccountTr", filter, paging);
}
/// <summary>
/// 分页查询Commit记录
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task <PagedResultDto <CommitDto> > QueryCommits(PagingInput input)
{
var query = await _commitRepository.GetAllListAsync();
var count = query.Count();
var result = query.Select(x => new CommitDto
{
Sha = x.Sha,
Message = x.Message,
Date = x.Date.ToString("yyyy-MM-dd HH:mm:ss")
}).OrderByDescending(x => x.Date).PageByIndex(input.Page, input.Limit).ToList();
return(new PagedResultDto <CommitDto>(count, result));
}
public ActionResult Activity(ActivitiesQueryInput activityInput, PagingInput pagingInput)
{
if (Request.IsAjaxRequest())
{
return(new JsonNetResult(new
{
Model = new
{
Activities = _activityViewModelQuery.BuildHomeActivityList(_userContext.GetAuthenticatedUserId(), activityInput, pagingInput)
}
}));
}
return(HttpNotFound());
}
public List <DicReader> GetPlistGroupAccountTrs(string key, Guid groupId, PagingInput paging)
{
paging.Valid();
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
const string filterString = @" where (a.Name like @key
or a.Code like @key
or a.LoginName like @key) and a.GroupId=@GroupId" ;
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
parameters.Add(CreateParameter("GroupId", groupId, DbType.Guid));
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("GroupAccountTr", filter, paging));
}
/// <summary>
/// 分页查询所有已开通服务列表
/// </summary>
/// <param name="input">查询服务参数</param>
/// <returns>分页返回查询结果</returns>
public async Task <List <NpsClientOpenedOutput> > SearchAsync(PagingInput <NpsClientSearchInput> input)
{
var outputs = new List <NpsClientOpenedOutput>();
var npsAppSecrets = await _npsAppSecretRepository
.Where(x => x.CreateUserId == CurrentUser.UserId)
.WhereIf(input.Filter?.DeviceUniqueId.IsNotNullOrEmpty() ?? false, x => x.DeviceUniqueId == input.Filter.DeviceUniqueId)
.WhereCascade(x => x.IsDeleted == false)
.Include(x => x.NpsServer)
.Include(x => x.NpsClient)
.ToListAsync();
if (npsAppSecrets.Count == 0)
{
return(outputs);
}
var npsClientIds = npsAppSecrets.Select(x => x.NpsClient?.Id ?? 0).ToList();
var npsChannels = await _npsChannelRepository
.Where(x => x.IsDeleted == false)
.Where(x => npsClientIds.Contains(x.NpsClientId))
.WhereIf(input.Filter?.SearchPorts.IsNotNull() ?? false, x => input.Filter.SearchPorts.Contains(x.DeviceAddress))
.ToListAsync();
if (npsChannels.Count == 0)
{
return(outputs);
}
npsAppSecrets.ForEach(npsAppSecret =>
{
if (npsAppSecret.NpsClient != null)
{
npsAppSecret.NpsClient.NpsChannels = npsChannels.Where(x => x.NpsClientId == npsAppSecret.NpsClient?.Id).ToList();
}
});
npsAppSecrets.ForEach(npsAppSecret =>
{
var npsClientOpenedOutput = Mapper.Map <NpsClientOpenedOutput>(npsAppSecret);
outputs.Add(npsClientOpenedOutput);
});
return(outputs);
}
public List<DicReader> GetPlistAccountTrs(List<FilterData> filters, string catalogCode, bool includeDescendants, PagingInput paging)
{
paging.Valid();
bool byOrgCode = !string.IsNullOrEmpty(catalogCode);
Func<SqlFilter> filter = () =>
{
List<DbParameter> parameters;
var filterString = new SqlFilterStringBuilder().FilterString(base.DbContext.Rdb, filters, "a", out parameters);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where 1=1 and " + filterString;
}
else
{
filterString = " where 1=1 ";
}
if (!includeDescendants)
{
if (byOrgCode)
{
if (!string.IsNullOrEmpty(catalogCode))
{
parameters.Add(this.CreateParameter("CatalogCode", catalogCode, DbType.String));
filterString += "and a.CatalogCode=@CatalogCode ";
}
}
}
else
{
if (byOrgCode)
{
if (!string.IsNullOrEmpty(catalogCode))
{
parameters.Add(CreateParameter("CatalogCode", catalogCode + "%", DbType.String));
filterString += "and a.CatalogCode like @CatalogCode ";
}
}
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("AccountTr", filter, paging);
}
/// <summary>
/// 分页查询好文
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task <PagedResultDto <QueryNiceArticleDto> > QueryNicceArticle(PagingInput input)
{
var count = await _niceArticleRepository.CountAsync();
var result = (from niceArticles in await _niceArticleRepository.GetAllListAsync()
join categories in await _categoryRepository.GetAllListAsync()
on niceArticles.CategoryId equals categories.Id
orderby niceArticles.Time descending
select new QueryNiceArticleDto
{
Title = niceArticles.Title,
Author = niceArticles.Author,
Source = niceArticles.Source,
Url = niceArticles.Url,
Category = categories.CategoryName,
Time = niceArticles.Time.ToString("MMMM dd, yyyy HH:mm:ss", new CultureInfo("en-us")),
}).PageByIndex(input.Page, input.Limit).ToList();
return(new PagedResultDto <QueryNiceArticleDto>(count, result));
}
public async Task NpsClientSearchTestAsync()
{
Thread.CurrentPrincipal = new ClaimsPrincipal
(
new ClaimsIdentity
(
new List <Claim>
{
new Claim(ClaimTypes.NameIdentifier, "117084294703656960")
}
)
);
var input1 = new PagingInput <NpsClientSearchInput>();
var input2 = new PagingInput <NpsClientSearchInput>
{
Filter = new NpsClientSearchInput {
DeviceUniqueId = "AAAAAAAAAA"
}
};
var input3 = new PagingInput <NpsClientSearchInput>
{
Filter = new NpsClientSearchInput {
DeviceUniqueId = "BBBBBBBBBB", SearchPorts = new List <string> {
"1111", "5555", "6666"
}
}
};
var searchResult1 = await _npsClientService.SearchAsync(input1);
var searchResult2 = await _npsClientService.SearchAsync(input2);
var searchResult3 = await _npsClientService.SearchAsync(input3);
Assert.NotNull(searchResult1);
Assert.NotNull(searchResult2);
Assert.NotNull(searchResult3);
}
public List<DicReader> GetPlistVisitingLogTrs(Guid accountId, string loginName, DateTime? leftVisitOn, DateTime? rightVisitOn, PagingInput paging)
{
paging.Valid();
loginName = (loginName ?? string.Empty).ToLower();
Func<SqlFilter> filter = () =>
{
var parameters = new List<DbParameter>();
var filterString = @" where (a.AccountId=@AccountId or Lower(a.LoginName)=@LoginName) ";
parameters.Add(CreateParameter("LoginName", loginName, DbType.String));
parameters.Add(CreateParameter("AccountId", accountId, DbType.Guid));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return new SqlFilter(filterString, parameters.ToArray());
};
return base.GetPlist("VisitingLog", filter, paging);
}
public ActionResult PrivateIndex(ActivitiesQueryInput activityInput, PagingInput pagingInput)
{
var profiler = MiniProfiler.Current;
using (profiler.Step("Check if user is authenticated"))
{
if (!_userContext.IsUserAuthenticated())
{
return(RedirectToAction("PublicIndex"));
}
}
dynamic viewModel = new ExpandoObject();
using (profiler.Step("Build private index view model"))
{
var userResult = _documentSession
.Query <All_Users.Result, All_Users>()
.AsProjection <All_Users.Result>()
.Where(x => x.UserId == _userContext.GetAuthenticatedUserId())
.First();
viewModel.User = _userViewModelQuery.BuildUser(_userContext.GetAuthenticatedUserId());
using (profiler.Step("Build timeline items (ActivityViewModelQuery.BuildHomeActivityList)"))
{
viewModel.Activities = _activityViewModelQuery.BuildHomeActivityList(_userContext.GetAuthenticatedUserId(), activityInput, pagingInput);
}
viewModel.ShowUserWelcome = userResult.User.CallsToAction.Contains("user-welcome");
viewModel.ShowActivities = true;
}
return(RestfulResult(
viewModel,
"home",
"privateindex"));
}
public IList<OperationLog> GetPlistOperationLogs(Guid? targetId,
DateTime? leftCreateOn, DateTime? rightCreateOn
, List<FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService<ISqlFilterStringBuilder>();
RdbDescriptor db = GetOperationLogDb();
List<DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString + "{0}";
}
else
{
filterString = " where 1=1 {0}";
}
if (targetId.HasValue)
{
filterString = string.Format(filterString, " and t.TargetID=@TargetId {0}");
}
if (leftCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn >= @leftCreateOn");
}
if (rightCreateOn.HasValue)
{
filterString = string.Format(filterString, " and t.CreateOn < @rightCreateOn");
}
else
{
filterString = string.Format(filterString, string.Empty);
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from OperationLog as t" + filterString;
var operationLogs = new List<OperationLog>();
if (prams == null)
{
prams = new List<DbParameter>();
}
if (targetId.HasValue)
{
prams.Add(CreateParameter(db, "TargetId", targetId.Value, DbType.Guid));
}
if (leftCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "leftCreateOn", leftCreateOn.Value, DbType.DateTime));
}
if (rightCreateOn.HasValue)
{
prams.Add(CreateParameter(db, "rightCreateOn", rightCreateOn.Value, DbType.DateTime));
}
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "OperationLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
operationLogs.Add(OperationLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast<DbParameter>().ToArray());
reader.Close();
return operationLogs;
}
/// <summary>
/// 根据目录获取给定节点和本体的数据，如果传入的目录为空表示获取本节点的数据
/// <remarks>本节点通常是中心节点</remarks>
/// </summary>
/// <param name="ontology"></param>
/// <param name="db">模型</param>
/// <param name="filters"></param>
/// <param name="selectElements">sql select语句的选择列集合</param>
/// <param name="pagingData"></param>
/// <returns>
/// 数据记录列表，数据记录表现为字典形式，键是数据元素编码值是相应数据元素对应的数据项值
/// </returns>
private DataTuple GetPlistInfoItems(
OntologyDescriptor ontology,
RdbDescriptor db, OrderedElementSet selectElements, List<FilterData> filters,
PagingInput pagingData)
{
if (string.IsNullOrEmpty(pagingData.SortField))
{
pagingData.SortField = "IncrementId";
}
if (string.IsNullOrEmpty(pagingData.SortOrder))
{
pagingData.SortOrder = "asc";
}
var elements = ontology.Elements;
if (filters != null)
{
for (int i = 0; i < filters.Count; i++)
{
var filter = filters[i];
if (elements.ContainsKey(filter.field))
{
// TODO:根据数据属性优化查询，比如对于身份证件号来说如果输入的值长度
// 为20或18的话可以将like替换为等于
filter.type = "string";
var element = elements[filter.field];
if (element.Element.IsEnabled != 1)
{
continue;
}
if (element.Element.InfoDicId.HasValue)
{
filter.comparison = "eq";
}
else
{
filter.comparison = "like";
}
}
else
{
filters.RemoveAt(i);
}
}
}
var tableName = ontology.Ontology.EntityTableName;
var sbSqlPredicate = new StringBuilder();
var l = sbSqlPredicate.Length;
var pQueryList = new List<DbParameter>();
List<DbParameter> pFilters;
var filterString = _filterStringBuilder.FilterString(db, filters, null, out pFilters);
if (!string.IsNullOrEmpty(filterString))
{
foreach (var pFilter in pFilters)
{
object obj = pFilter.Value;
if (obj == null)
{
obj = DBNull.Value;
}
var p = db.CreateParameter();
p.ParameterName = pFilter.ParameterName;
p.Value = obj;
pQueryList.Add(p);
}
if (sbSqlPredicate.Length != l)
{
sbSqlPredicate.Append(" and ");
}
sbSqlPredicate.Append(filterString);
}
string sqlPredicateString = string.Empty;
if (sbSqlPredicate.Length > 0)
{
sqlPredicateString = sbSqlPredicate.ToString();
}
var sqlText = new StringBuilder();
OrderedElementSet elementList;
if (selectElements == null || selectElements.Count == 0)
{
elementList = new OrderedElementSet { ontology.Elements["id"] };
}
else
{
elementList = selectElements;
}
sqlText.Append("SELECT TOP {0} ");
int len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,");
len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM {3} where ");
if (ontology.Ontology.IsLogicalDeletionEntity)
{
sqlText.Append("DeletionStateCode = 0");
}
else
{
sqlText.Append("1 = 1");
}
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlText.Append(" and ").Append(sqlPredicateString);
}
sqlText.Append(") a WHERE a.RowNumber > {4}");
string sqlQuery = string.Format(
sqlText.ToString(),
pagingData.PageSize.ToString(CultureInfo.InvariantCulture),
pagingData.SortField,
pagingData.SortOrder,
tableName,
(pagingData.SkipCount).ToString(CultureInfo.InvariantCulture));
pagingData.Count(() =>
{
string where = ontology.Ontology.IsLogicalDeletionEntity ? "where DeletionStateCode = 0" : "";
string sqlCount = string.Format("select count(1) from {0} {1}", tableName, where);
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlCount = sqlCount + " and " + sqlPredicateString;
}
return (int)db.ExecuteScalar(
sqlCount, pQueryList.Select(p => ((ICloneable)p).Clone()).Cast<DbParameter>().ToArray());
});
var list = new List<object[]>();
var reader = db.ExecuteReader(sqlQuery, pQueryList.ToArray());
while (reader.Read())
{
var values = new object[elementList.Count];
for (int i = 0; i < elementList.Count; i++)
{
values[i] = reader.GetValue(i);
}
list.Add(values);
}
reader.Close();
return new DataTuple(elementList, list.ToArray());
}
public List <DicReader> GetPlistVisitingLogTrs(Guid accountId, string loginName, DateTime?leftVisitOn, DateTime?rightVisitOn, PagingInput paging)
{
paging.Valid();
loginName = (loginName ?? string.Empty).ToLower();
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
var filterString = @" where (a.AccountId=@AccountId or Lower(a.LoginName)=@LoginName) ";
parameters.Add(CreateParameter("LoginName", loginName, DbType.String));
parameters.Add(CreateParameter("AccountId", accountId, DbType.Guid));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("VisitingLog", filter, paging));
}
public List <DicReader> GetPlistVisitingLogTrs(string key, DateTime?leftVisitOn, DateTime?rightVisitOn, PagingInput paging)
{
paging.Valid();
if (key != null)
{
key = key.Trim();
}
Func <SqlFilter> filter = () =>
{
var parameters = new List <DbParameter>();
var filterString = @" where a.LoginName like @key ";
parameters.Add(CreateParameter("key", "%" + key + "%", DbType.String));
if (leftVisitOn.HasValue)
{
parameters.Add(CreateParameter("leftVisitOn", leftVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn>=@leftVisitOn";
}
if (rightVisitOn.HasValue)
{
parameters.Add(CreateParameter("rightVisitOn", rightVisitOn.Value, DbType.DateTime));
filterString += " and a.VisitOn<@rightVisitOn";
}
return(new SqlFilter(filterString, parameters.ToArray()));
};
return(base.GetPlist("VisitingLog", filter, paging));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="query"></param>
/// <param name="paging"></param>
/// <returns></returns>
public static IQueryable <TSource> SetPaging <TSource>(this IQueryable <TSource> query, PagingInput paging)
{
return(query.Skip(paging.SkipCount).Take(paging.PageSize));
}
/// <summary>
/// 分页查询文章列表
/// </summary>
/// <param name="input"></param>
/// <param name="factory"></param>
/// <returns></returns>
public async Task <ServiceResult <PagedList <QueryPostDto> > > QueryPostsAsync(PagingInput input, Func <Task <ServiceResult <PagedList <QueryPostDto> > > > factory)
{
return(await Cache.GetOrAddAsync(KEY_QueryPosts.FormatWith(input.Page, input.Limit), factory, CacheStrategy.ONE_DAY));
}
/// <inheritdoc/>
public Task <PagingOutput <NetworkInterfaceStatus> > GetPageAsync(string networkInterfaceName, PagingInput pagingInput)
{
Expression <Func <Entities.Network.NetworkInterfaceStatus, bool> > where = e => e.NetworkInterfaceName == networkInterfaceName;
return(this.GetPageAsync(pagingInput, where));
}
public IList<ExceptionLog> GetPlistExceptionLogs(List<FilterData> filters, PagingInput paging)
{
paging.Valid();
var filterStringBuilder = _acDomain.RetrieveRequiredService<ISqlFilterStringBuilder>();
RdbDescriptor db = GetExceptionLogDb();
List<DbParameter> prams;
var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString;
}
var sql =
@"select top({0}) *
from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t"
+ filterString + ") a WHERE a.RowNumber > {4}";
var countSql = "select count(*) from ExceptionLog as t" + filterString;
var exceptionLogs = new List<ExceptionLog>();
var reader = db.ExecuteReader(
string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "ExceptionLog", paging.PageSize * paging.PageIndex), prams.ToArray());
while (reader.Read())
{
exceptionLogs.Add(ExceptionLog.Create(reader));
}
paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast<DbParameter>().ToArray());
reader.Close();
return exceptionLogs;
}
/// <summary>
/// 按照目录分页获取指定节点、本体的数据
/// <remarks>
/// 如果传入的目录为空则表示获取全部目录的数据
/// </remarks>
/// </summary>
/// <param name="ontology"></param>
/// <param name="selectElements"></param>
/// <param name="filters">过滤器列表</param>
/// <param name="pagingData"></param>
/// <returns></returns>
public DataTuple GetPlist(
OntologyDescriptor ontology,
OrderedElementSet selectElements,
List<FilterData> filters,
PagingInput pagingData)
{
return this.GetPlistInfoItems(
ontology, this.GetEntityDb(ontology),
selectElements,
filters, pagingData);
}
public DataTuple GetPlist(
ArchiveState archive,
OrderedElementSet selectElements,
List<FilterData> filters,
PagingInput pagingData)
{
return this.GetPlistInfoItems(
archive.Ontology, this.GetArchiveDb(archive.Ontology, archive),
selectElements,
filters, pagingData);
}
/// <summary>
/// 根据目录获取给定节点和本体的数据，如果传入的目录为空表示获取本节点的数据
/// <remarks>本节点通常是中心节点</remarks>
/// </summary>
/// <param name="ontology"></param>
/// <param name="db">模型</param>
/// <param name="filters"></param>
/// <param name="selectElements">sql select语句的选择列集合</param>
/// <param name="pagingData"></param>
/// <returns>
/// 数据记录列表，数据记录表现为字典形式，键是数据元素编码值是相应数据元素对应的数据项值
/// </returns>
private DataTuple GetPlistInfoItems(
OntologyDescriptor ontology,
RdbDescriptor db, OrderedElementSet selectElements, List <FilterData> filters,
PagingInput pagingData)
{
if (string.IsNullOrEmpty(pagingData.SortField))
{
pagingData.SortField = "IncrementId";
}
if (string.IsNullOrEmpty(pagingData.SortOrder))
{
pagingData.SortOrder = "asc";
}
var elements = ontology.Elements;
if (filters != null)
{
for (int i = 0; i < filters.Count; i++)
{
var filter = filters[i];
if (elements.ContainsKey(filter.field))
{
// TODO:根据数据属性优化查询，比如对于身份证件号来说如果输入的值长度
// 为20或18的话可以将like替换为等于
filter.type = "string";
var element = elements[filter.field];
if (element.Element.IsEnabled != 1)
{
continue;
}
if (element.Element.InfoDicId.HasValue)
{
filter.comparison = "eq";
}
else
{
filter.comparison = "like";
}
}
else
{
filters.RemoveAt(i);
}
}
}
var tableName = ontology.Ontology.EntityTableName;
var sbSqlPredicate = new StringBuilder();
var l = sbSqlPredicate.Length;
var pQueryList = new List <DbParameter>();
List <DbParameter> pFilters;
var filterString = _filterStringBuilder.FilterString(db, filters, null, out pFilters);
if (!string.IsNullOrEmpty(filterString))
{
foreach (var pFilter in pFilters)
{
object obj = pFilter.Value;
if (obj == null)
{
obj = DBNull.Value;
}
var p = db.CreateParameter();
p.ParameterName = pFilter.ParameterName;
p.Value = obj;
pQueryList.Add(p);
}
if (sbSqlPredicate.Length != l)
{
sbSqlPredicate.Append(" and ");
}
sbSqlPredicate.Append(filterString);
}
string sqlPredicateString = string.Empty;
if (sbSqlPredicate.Length > 0)
{
sqlPredicateString = sbSqlPredicate.ToString();
}
var sqlText = new StringBuilder();
OrderedElementSet elementList;
if (selectElements == null || selectElements.Count == 0)
{
elementList = new OrderedElementSet {
ontology.Elements["id"]
};
}
else
{
elementList = selectElements;
}
sqlText.Append("SELECT TOP {0} ");
int len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,");
len = sqlText.Length;
foreach (var element in elementList)
{
if (sqlText.Length != len)
{
sqlText.Append(",");
}
sqlText.Append("[").Append(element.Element.FieldCode).Append("]");
}
sqlText.Append(" FROM {3} where ");
if (ontology.Ontology.IsLogicalDeletionEntity)
{
sqlText.Append("DeletionStateCode = 0");
}
else
{
sqlText.Append("1 = 1");
}
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlText.Append(" and ").Append(sqlPredicateString);
}
sqlText.Append(") a WHERE a.RowNumber > {4}");
string sqlQuery = string.Format(
sqlText.ToString(),
pagingData.PageSize.ToString(CultureInfo.InvariantCulture),
pagingData.SortField,
pagingData.SortOrder,
tableName,
(pagingData.SkipCount).ToString(CultureInfo.InvariantCulture));
pagingData.Count(() =>
{
string where = ontology.Ontology.IsLogicalDeletionEntity ? "where DeletionStateCode = 0" : "";
string sqlCount = string.Format("select count(1) from {0} {1}", tableName, where);
if (!string.IsNullOrEmpty(sqlPredicateString))
{
sqlCount = sqlCount + " and " + sqlPredicateString;
}
return((int)db.ExecuteScalar(
sqlCount, pQueryList.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray()));
});
var list = new List <object[]>();
var reader = db.ExecuteReader(sqlQuery, pQueryList.ToArray());
while (reader.Read())
{
var values = new object[elementList.Count];
for (int i = 0; i < elementList.Count; i++)
{
values[i] = reader.GetValue(i);
}
list.Add(values);
}
reader.Close();
return(new DataTuple(elementList, list.ToArray()));
}
public async Task <ServiceResult <PagedList <QueryPostForAdminDto> > > QueryPostsForAdminAsync([FromQuery] PagingInput input)
{
return(await _blogService.QueryPostsForAdminAsync(input));
}
public ActionResult GetPlistBatches(GetPlistBatchs input)
{
if (!ModelState.IsValid)
{
return ModelState.ToJsonResult();
}
Guid? ontologyId = null;
if (string.IsNullOrEmpty(input.OntologyCode))
{
ontologyId = null;
}
else
{
OntologyDescriptor ontology;
if (!AcDomain.NodeHost.Ontologies.TryGetOntology(input.OntologyCode, out ontology))
{
throw new ValidationException("意外的本体码" + input.OntologyCode);
}
ontologyId = ontology.Ontology.Id;
}
var pagingData = new PagingInput(input.PageIndex
, input.PageSize, input.SortField, input.SortOrder);
if (ontologyId != null)
{
input.Filters.Insert(0, FilterData.EQ("OntologyId", ontologyId.Value));
}
var data = GetRequiredService<IBatchQuery>().GetPlist(base.EntityType, () =>
{
RdbDescriptor rdb;
if (!AcDomain.Rdbs.TryDb(base.EntityType.DatabaseId, out rdb))
{
throw new AnycmdException("意外配置的Batch实体类型对象数据库标识" + base.EntityType.DatabaseId);
}
List<DbParameter> ps;
var filterString = new SqlFilterStringBuilder().FilterString(rdb, input.Filters, "a", out ps);
if (!string.IsNullOrEmpty(filterString))
{
filterString = " where " + filterString;
}
return new SqlFilter(filterString, ps.ToArray());
}, pagingData);
Debug.Assert(pagingData.Total != null, "pagingData.Total != null");
return this.JsonResult(new MiniGrid<BatchTr> { total = pagingData.Total.Value, data = data.Select(a => new BatchTr(a)) });
}
