/// <inheritdoc/> public IList <Person> Query(PersonQueryDto dto) { var sqlWhere = new StringBuilder(); if (!string.IsNullOrEmpty(dto.Query)) { sqlWhere.AppendLine(" AND ( "); sqlWhere.AppendLine(" PersonCode LIKE @Query OR "); sqlWhere.AppendLine(" LoginId LIKE @Query OR "); sqlWhere.AppendLine(" Name LIKE @Query OR "); sqlWhere.AppendLine(" Title LIKE @Query OR "); sqlWhere.AppendLine(" PersonId IN ( "); sqlWhere.AppendLine(" SELECT TargetId "); sqlWhere.AppendLine(" FROM Tag AS TG1 WITH (NOLOCK) "); sqlWhere.AppendLine(" WHERE TG1.TargetTable = 'Person' "); sqlWhere.AppendLine(" AND TG1.Value = @TagQuery "); sqlWhere.AppendLine(" ) "); sqlWhere.AppendLine(" ) "); } if (!string.IsNullOrEmpty(dto.Status)) { sqlWhere.AppendLine(" AND Status = @Status "); } if (!string.IsNullOrEmpty(dto.GroupCode)) { sqlWhere.AppendLine(" AND EXISTS ("); sqlWhere.AppendLine(" SELECT PersonId "); sqlWhere.AppendLine(" FROM [Group] AS GR1 WITH (NOLOCK) INNER JOIN "); sqlWhere.AppendLine(" GroupPerson AS GP1 WITH (NOLOCK) ON GR1.GroupId = GP1.GroupID "); sqlWhere.AppendLine(" WHERE GR1.GroupCode = @GroupCode "); sqlWhere.AppendLine(" ) "); } if (!string.IsNullOrEmpty(dto.ProjectCode)) { sqlWhere.AppendLine(" AND EXISTS ("); sqlWhere.AppendLine(" SELECT PersonId "); sqlWhere.AppendLine(" FROM Project AS PR1 WITH (NOLOCK) INNER JOIN "); sqlWhere.AppendLine(" ProjectPerson AS PP1 WITH (NOLOCK) ON PR1.ProjectId = PP1.ProjectID "); sqlWhere.AppendLine(" WHERE PR1.ProjectCode = @ProjectCode "); sqlWhere.AppendLine(" ) "); } var sqlSort = new StringBuilder(); if (dto.Sort.Count == 0) { sqlSort.AppendLine(" ORDER BY {0}.SortNo ASC, {0}.PersonCode ASC "); } else { sqlSort.AppendLine(" ORDER BY "); foreach (var item in dto.Sort) { if (item.Field.Equals("PersonCode", StringComparison.OrdinalIgnoreCase)) { sqlSort.Append(" {0}.PersonCode " + item.SortType.ToString() + ","); } else if (item.Field.Equals("SortNo", StringComparison.OrdinalIgnoreCase)) { sqlSort.Append(" {0}.SortNo " + item.SortType.ToString() + ","); } else if (item.Field.Equals("Name", StringComparison.OrdinalIgnoreCase)) { sqlSort.Append(" {0}.Name " + item.SortType.ToString() + ","); } else if (item.Field.Equals("CreateTime", StringComparison.OrdinalIgnoreCase)) { sqlSort.Append(" {0}.CreateTime " + item.SortType.ToString() + ","); } } sqlSort.Length -= 1; sqlSort.AppendLine(); } var param = new { Status = dto.Status, GroupCode = dto.GroupCode, ProjectCode = dto.ProjectCode, Query = DataUtil.EscapeLike(dto.Query, LikeMatchType.PrefixSearch), TagQuery = dto.Query, Page = dto.Page, PageSize = dto.PageSize, }; var sqlCount = new StringBuilder(); sqlCount.AppendLine(" SELECT COUNT(*) FROM Person WHERE 1 = 1 "); sqlCount.AppendLine(sqlWhere.ToString()); dto.TotalCount = (int)Connection.ExecuteScalar( sqlCount.ToString(), param, commandTimeout: DBSettings.CommandTimeout); var sql = new StringBuilder(); sql.AppendLine(" SELECT PS1.* "); sql.AppendLine(" , (SELECT TG1.Value "); sql.AppendLine(" FROM Tag AS TG1 WITH (NOLOCK) "); sql.AppendLine(" WHERE TG1.TargetId = PS1.PersonId "); sql.AppendLine(" ORDER BY TG1.Value "); sql.AppendLine(" FOR JSON PATH "); sql.AppendLine(" ) AS PersonTags "); sql.AppendLine(" FROM Person AS PS1 "); sql.AppendLine(" WHERE 1 = 1 "); sql.AppendLine(sqlWhere.ToString()); sql.AppendLine(string.Format(sqlSort.ToString(), "PS1")); sql.AppendLine(" OFFSET (@Page - 1) * @PageSize ROWS "); sql.AppendLine(" FETCH FIRST @PageSize ROWS ONLY "); return(Connection.Query <Person, string, Person>( sql.ToString(), (person, personTags) => { person.Tags = JsonUtil.Deserialize <List <string> >(JsonUtil.ToRawJsonArray(personTags, "Value")); return person; }, param, splitOn: "PersonTags", commandTimeout: DBSettings.CommandTimeout).ToList()); }
/// <inheritdoc/> public IList <Person> QueryPerson(PersonQueryDto dto) { return(_personQueryService.Query(dto)); }