public async Task <PaginationOutput <ResumeDto> > GetListAsync(QueryResumeInput input)
{
using (var connection = await _provider.GetDbConnectionAsync())
{
string columnSql = "a.Id,a.Name,a.JobId,c.Title,a.PhoneNumber," +
"a.CreationTime,a.CreatorUserId,CONCAT(d.Surname,d.Name) CreatorUserName ,a.OwnerUserId,CONCAT(e.Surname,e.Name) OwnerUserName," +
"b.Id as InvestigationId,a.AuditStatus,a.PlatformName,a.PlatformId," +
"a.Enable,a.EnableReason";
var fromSql = new StringBuilder();
fromSql.Append("from resumes a ");
fromSql.Append("left join investigations b on a.Id = b.ResumeId ");
fromSql.Append("inner join jobs c on a.JobId = c.Id ");
fromSql.Append("inner join users d on a.CreatorUserId = d.Id ");
fromSql.Append("inner join users e on a.OwnerUserId = e.id ");
fromSql.Append(" where 1 = 1");
if (!string.IsNullOrEmpty(input.Keyword))
{
fromSql.Append(" and (a.Name like '%@Keyword%' or a.PhoneNumber like '%@Keyword%' or a.PlatformId like '%@Keyword%') ");
}
if (input.JobId.HasValue)
{
fromSql.Append(" and a.JobId = @JobId ");
}
if (input.CreatorUserId.HasValue)
{
fromSql.Append(" and a.CreatorUserId = @CreatorUserId ");
}
if (input.OwnerUserId.HasValue && input.OwnerUserId != Guid.Empty)
{
fromSql.Append(" and a.OwnerUserId = @OwnerUserId ");
}
if (input.StartTime.HasValue && input.EndTime.HasValue)
{
fromSql.Append(" and a.CreationTime >= @StartTime and a.CreationTime<= @EndTime ");
}
if (input.AuditStatus.HasValue)
{
fromSql.Append(" and a.AuditStatus = @AuditStatus ");
}
return(await connection.GetPaginationAsync <ResumeDto>(columnSql, fromSql.ToString(), input, input.PageSize, input.PageIndex));
}
}
public async Task <IActionResult> List(QueryResumeInput input)
{
if (!input.OwnerUserId.HasValue)
{
if (CustomSetting.DefaultOnlySeeMyselfData)
{
input.OwnerUserId = UserIdentifier.UserId;
}
else
{
input.OwnerUserId = Guid.Empty;
}
}
var output = await _resumeQuerier.GetListAsync(input);
var model = new QueryResumeViewModel();
model.Output = new PaginationModel <ResumeDto>(output, input);
return(await BuildListDisplayAsync(model));
}
public async Task <PaginationOutput <ResumeDto> > GetListAsync(QueryResumeInput input)
{
CancellationToken.ThrowIfCancellationRequested();
if (input == null)
{
throw new ArgumentNullException(nameof(input));
}
var query = from a in _context.Resumes
join b in _context.Investigations on a.Id equals b.ResumeId into bb
from bbb in bb.DefaultIfEmpty()
join c in _context.Jobs on a.JobId equals c.Id
join d in _context.Users on a.CreatorUserId equals d.Id
join e in _context.Users on a.OwnerUserId equals e.Id
select new ResumeDto
{
Id = a.Id,
Name = a.Name,
JobId = a.JobId,
JobName = c.Title,
PhoneNumber = a.PhoneNumber,
CreationTime = a.CreationTime,
CreatorUserId = a.CreatorUserId,
CreatorUserName = d.FullName,
OwnerUserId = a.OwnerUserId,
OwnerUserName = e.FullName,
InvestigationId = bbb.Id,
AuditStatus = a.AuditStatus,
PlatformName = a.PlatformName,
PlatformId = a.PlatformId
};
if (!string.IsNullOrEmpty(input.Keyword))
{
query = query.Where(w => w.Name.Contains(input.Keyword) || w.PhoneNumber.Contains(input.Keyword) || w.PlatformId.Contains(input.Keyword));
if (Guid.TryParse(input.Keyword, out var id))
{
query = query.Where(w => w.Id == id);
}
}
if (input.JobId.HasValue)
{
query = query.Where(w => w.JobId == input.JobId.Value);
}
if (input.CreatorUserId.HasValue)
{
query = query.Where(w => w.CreatorUserId == input.CreatorUserId.Value);
}
if (input.OwnerUserId.HasValue && input.OwnerUserId != Guid.Empty)
{
query = query.Where(w => w.OwnerUserId == input.OwnerUserId.Value);
}
if (input.StartTime.HasValue && input.EndTime.HasValue)
{
query = query.Where(w => w.CreationTime >= input.StartTime.Value && w.CreationTime <= input.EndTime.Value);
}
if (input.AuditStatus.HasValue)
{
query = query.Where(w => w.AuditStatus == (AuditStatus)input.AuditStatus.Value);
}
var totalCount = await query.CountAsync(CancellationToken);
var totalSize = (int)Math.Ceiling(totalCount / (decimal)input.PageSize);
var resumes = await query.OrderByDescending(o => o.CreationTime)
.Skip((input.PageIndex - 1) * input.PageSize)
.Take(input.PageSize)
.ToListAsync(CancellationToken);
return(new PaginationOutput <ResumeDto>(totalSize, resumes));
}
public async Task <PaginationOutput <ResumeDto> > GetListAsync(QueryResumeInput input)
{
CancellationToken.ThrowIfCancellationRequested();
if (input == null)
{
throw new ArgumentNullException(nameof(input));
}
var query = from a in _context.Resumes
join b in _context.Investigations on a.Id equals b.ResumeId into bb
from bbb in bb.DefaultIfEmpty()
join c in _context.Jobs on a.JobId equals c.Id into cc
from ccc in cc.DefaultIfEmpty()
join d in _context.Users on a.CreatorUserId equals d.Id
join e in _context.Users on a.OwnerUserId equals e.Id
select new ResumeDto
{
Id = a.Id,
No = a.No,
Name = a.Name,
JobId = ccc == null ? Guid.Empty : ccc.Id,
JobName = ccc == null ? "" : ccc.Title,
PhoneNumber = a.PhoneNumber,
CreationTime = a.CreationTime,
CreatorUserId = a.CreatorUserId,
CreatorUserName = d.FullName,
OwnerUserId = a.OwnerUserId,
OwnerUserName = e.FullName,
InvestigationId = bbb.Id,
AuditStatus = a.AuditStatus,
PlatformName = a.PlatformName,
PlatformId = a.PlatformId,
Education = a.Education,
WorkIn = a.WorkIn
};
if (!string.IsNullOrEmpty(input.Keyword))
{
query = query.Where(w => w.Name.Contains(input.Keyword) ||
w.PhoneNumber.Contains(input.Keyword) ||
w.PlatformId.Contains(input.Keyword) ||
w.No.Contains(input.Keyword));
if (Guid.TryParse(input.Keyword, out var id))
{
query = query.Where(w => w.Id == id);
}
}
if (input.JobId.HasValue)
{
query = query.Where(w => w.JobId == input.JobId.Value);
}
if (input.CreatorUserId.HasValue)
{
query = query.Where(w => w.CreatorUserId == input.CreatorUserId.Value);
}
if (input.OwnerUserId.HasValue && input.OwnerUserId != Guid.Empty)
{
query = query.Where(w => w.OwnerUserId == input.OwnerUserId.Value);
}
if (input.StartTime.HasValue && input.EndTime.HasValue)
{
query = query.Where(w => w.CreationTime >= input.StartTime.Value && w.CreationTime <= input.EndTime.Value);
}
if (input.AuditStatus.HasValue)
{
query = query.Where(w => w.AuditStatus == (AuditStatus)input.AuditStatus.Value);
}
if (!string.IsNullOrEmpty(input.Tag))
{
var tagQuery = from a in _context.Tags where a.Value == input.Tag select a;
query = from a in query
join b in tagQuery on a.Id equals b.ResumeId
select a;
}
if (!string.IsNullOrEmpty(input.Education))
{
query = query.Where(w => w.Education == input.Education);
}
if (input.WorkIn.HasValue)
{
query = query.Where(w => w.WorkIn >= input.WorkIn.Value);
}
if (!string.IsNullOrEmpty(input.PlatformName))
{
query = query.Where(w => w.PlatformName == input.PlatformName);
}
var totalCount = await query.CountAsync(CancellationToken);
var totalSize = (int)Math.Ceiling(totalCount / (decimal)input.PageSize);
var resumes = await query.OrderByDescending(o => o.CreationTime)
.Skip((input.PageIndex - 1) * input.PageSize)
.Take(input.PageSize)
.ToListAsync(CancellationToken);
foreach (var resume in resumes)
{
var tags = await _context.Tags.Where(w => w.ResumeId == resume.Id).ToListAsync(CancellationToken);
if (tags != null)
{
resume.Tags = tags.Select(s => s.Value).ToList();
}
var attachment = await _context.Attachments.Where(w => w.ResumeId == resume.Id).OrderBy(o => o.CreationTime).FirstOrDefaultAsync();
if (attachment != null)
{
resume.FilePath = attachment.FilePath;
}
}
return(new PaginationOutput <ResumeDto>(totalSize, resumes));
}