public async Task <List <ResumeExportDto> > GetExportResumesAsync(QueryExportResumeInput input)
{
CancellationToken.ThrowIfCancellationRequested();
if (input == null)
{
throw new ArgumentNullException(nameof(input));
}
var query = from a in _context.Resumes
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 ResumeExportDto
{
Id = a.Id,
Name = a.Name,
JobId = a.JobId,
JobName = c.Title,
PhoneNumber = a.PhoneNumber,
CreationTime = a.CreationTime,
AuditStatus = a.AuditStatus,
PlatformName = a.PlatformName,
PlatformId = a.PlatformId,
CreatorUserId = a.CreatorUserId,
OwnerUserId = a.OwnerUserId,
City = a.City,
Description = a.Description
};
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 (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)
{
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);
}
return(await query.ToListAsync(CancellationToken));
}
public async Task <IActionResult> Export(QueryExportResumeInput input)
{
var resumes = await _resumeQuerier.GetExportResumesAsync(input);
var columnNames = new string[] {
"简历编号",
"职位",
"姓名",
"电话",
"邮箱",
"学历",
"工作年限",
"来源",
"审核情况",
"标签",
"入库时间"
};
using (ExcelPackage package = new ExcelPackage())
{
var worksheet01 = package.Workbook.Worksheets.Add("简历列表");
for (int i = 0; i < columnNames.Length; i++)
{
worksheet01.Cells[1, i + 1].Value = columnNames[i];
}
worksheet01.Cells[1, 1, 1, 11].Style.Font.Bold = true;
for (int i = 0; i < resumes.Count; i++)
{
worksheet01.Cells[i + 2, 1].Value = resumes[i].No;
worksheet01.Cells[i + 2, 2].Value = resumes[i].JobName;
worksheet01.Cells[i + 2, 3].Value = resumes[i].Name;
worksheet01.Cells[i + 2, 4].Value = resumes[i].PhoneNumber;
worksheet01.Cells[i + 2, 5].Value = resumes[i].Email;
worksheet01.Cells[i + 2, 6].Value = resumes[i].Education;
worksheet01.Cells[i + 2, 7].Value = resumes[i].WorkIn;
worksheet01.Cells[i + 2, 8].Value = resumes[i].PlatformName;
worksheet01.Cells[i + 2, 9].Value = resumes[i].AuditStatus.GetDescription();
if (resumes[i].Tags != null)
{
worksheet01.Cells[i + 2, 10].Value = string.Join(' ', resumes[i].Tags);
}
else
{
worksheet01.Cells[i + 2, 10].Value = "";
}
worksheet01.Cells[i + 2, 11].Value = resumes[i].CreationTime.ToString("yyyy-MM-dd");
}
return(File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"简历报告{DateTime.Now:yyyy-MM-dd_HH_mm_ss}.xlsx"));
}
}
public async Task <IActionResult> Export(QueryExportResumeInput input)
{
var resumes = await _resumeQuerier.GetExportResumesAsync(input);
var columnNames = new string[] {
"简历编号",
"来源",
"职位",
"简历",
"审核情况",
"姓名",
"电话",
"求职意向城市",
};
using (ExcelPackage package = new ExcelPackage())
{
var worksheet01 = package.Workbook.Worksheets.Add("简历列表");
for (int i = 0; i < columnNames.Length; i++)
{
worksheet01.Cells[1, i + 1].Value = columnNames[i];
}
worksheet01.Cells[1, 1, 1, 6].Style.Font.Bold = true;
for (int i = 0; i < resumes.Count; i++)
{
worksheet01.Cells[i + 2, 1].Value = resumes[i].PlatformName;
worksheet01.Cells[i + 2, 2].Value = resumes[i].JobName;
if (!string.IsNullOrEmpty(resumes[i].Description))
{
//添加简历记录
var name = !string.IsNullOrEmpty(resumes[i].Name) ? resumes[i].Name : i.ToString();
var resumeWorksheet = package.Workbook.Worksheets.Add($"简历{name}");
AddHtmlToWorksheet(resumes[i].Description, resumeWorksheet, "简历列表!A1");
worksheet01.Cells[i + 2, 4].Style.Font.UnderLine = true;
worksheet01.Cells[i + 2, 4].Style.Font.Color.SetColor(Color.Blue);
worksheet01.Cells[i + 2, 4].Hyperlink = new ExcelHyperLink($"简历{name}!A1", "简历");
}
worksheet01.Cells[i + 2, 5].Value = resumes[i].AuditStatus.GetDescription();
worksheet01.Cells[i + 2, 6].Value = resumes[i].Name;
worksheet01.Cells[i + 2, 7].Value = resumes[i].PhoneNumber;
worksheet01.Cells[i + 2, 8].Value = resumes[i].City;
}
return(File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "简历报告.xlsx"));
}
}
public async Task <List <ResumeExportDto> > GetExportResumesAsync(QueryExportResumeInput 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 ResumeExportDto
{
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,
OwnerUserId = a.OwnerUserId,
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.WorkInYears.HasValue)
{
query = query.Where(w => w.WorkIn <= DateTime.Now.AddYears(-input.WorkInYears.Value));
}
var resumes = await query.ToListAsync(CancellationToken);
foreach (var resume in resumes)
{
var tags = await _context.Tags.Where(w => w.ResumeId == resume.Id).ToListAsync(CancellationToken);
resume.Tags = tags.Select(s => s.Value).ToList();
}
return(resumes);
}
public async Task <List <ResumeExportDto> > GetExportResumesAsync(QueryExportResumeInput input)
{
return(await Task.FromResult <List <ResumeExportDto> >(null));
}
