public async Task <ActionResult> Export(SearchBorrowRegisterRequest request)
{
try
{
var list = await _borrowRegisterService.QueryAllBorrowRegisters(request);
System.IO.MemoryStream output = new System.IO.MemoryStream();
System.IO.StreamWriter writer = new System.IO.StreamWriter(output, System.Text.Encoding.UTF8);
writer.Write("借阅时间,借阅单位,借阅人,工程名称,借阅条目,归还日期,接收人,备注");
writer.WriteLine();
//输出内容
list.ForEach(a => {
writer.Write($"\"{a.CreateTimeStr}\",\"");//第一列
writer.Write($"{a.Department}\",\"");
writer.Write($"{a.Borrower}\",\"");
writer.Write($"{a.ProjectName}\",\"");
writer.Write($"{a.ArchivesStr}\",\"");
writer.Write($"{a.ReturnDateStr}\",\"");
writer.Write($"{a.Receiver}\",\"");
writer.Write($"{a.StatusDesc}\",");
writer.WriteLine();
});
writer.Flush();
output.Position = 0;
return(File(output, "application/ms-excel", "借阅记录.csv"));
}
catch (Exception ex)
{
ApplicationLog.Error("Export Excetpion", ex);
return(Ok(ex.Message));
}
}
public async Task <List <SearchBorrowRegisterResult> > QueryAllBorrowRegisters(SearchBorrowRegisterRequest request)
{
var query = _db.BorrowRegister.AsNoTracking().Where(c => !c.Deleted).Where(c => c.Status != BorrowRegisterStatus.Registered);
if (!string.IsNullOrEmpty(request.Keyword))
{
query = query.Where(c => c.Phone.Contains(request.Keyword.Trim()) || c.Borrower.Contains(request.Keyword.Trim()) || c.Company.Contains(request.Keyword.Trim()) || c.Department.Contains(request.Keyword.Trim()));
}
if (request.StartDate.HasValue)
{
query = query.Where(c => c.CreateTime > request.StartDate.Value);
}
if (request.EndDate.HasValue)
{
query = query.Where(c => c.CreateTime < request.EndDate.Value.AddDays(1));
}
query = query.OrderBy(c => c.CreateTime);
//var query = _db.BorrowRegister.AsNoTracking().Where(c => !c.Deleted);
//if (request.iSortCol_0 == "0") {
// if (request.sSortDir_0 == "asc")
// {
// query = query.OrderBy(c => c.CreateTime);
// }
// else {
// query = query.OrderByDescending(c => c.CreateTime);
// }
//}
//if (request.iSortCol_0 == "5")
//{
// if (request.sSortDir_0 == "asc")
// {
// query = query.OrderBy(c => c.ReturnDate);
// }
// else
// {
// query = query.OrderByDescending(c => c.ReturnDate);
// }
//}
var list = await query.Select(c => new SearchBorrowRegisterResult
{
Id = c.Id,
Borrower = c.Borrower,
Company = c.Company,
Department = c.Department,
Phone = c.Phone,
ReturnDate = c.ReturnDate,
SignPhoto = c.SignPhoto,
Status = c.Status,
CreateTime = c.CreateTime,
CreateTimeStr = c.CreateTime.ToString("yyyy-MM-dd"),
Receiver = c.Receiver,
Remark = c.Remark
}).ToListAsync();
var ids = list.Select(c => c.Id);
var archivesList = await _db.BorrowRegisterDetail.AsNoTracking().Where(brd => ids.Contains(brd.BorrowRegisterId)).ToListAsync();
list.ForEach(c =>
{
var arlist = archivesList.Where(j => j.BorrowRegisterId == c.Id);
//c.ArchivesList = arlist;
c.ArchivesStr = string.Join("，", arlist.Select(j => $"{j.ArchivesNumber}/{j.CategoryNumber}/{j.FileNumber}/{j.OrderNumber}"));
c.ReturnDateStr = c.ReturnDate.ToString("yyyy-MM-dd");
c.ProjectName = string.Join(",", arlist.Select(j => j.ProjectName));
});
return(list);
}
public async Task <CommonSearchResponse <List <SearchBorrowRegisterResult> > > SearchBorrowRegister(SearchBorrowRegisterRequest request)
{
var response = new CommonSearchResponse <List <SearchBorrowRegisterResult> >();
try
{
var query = _db.BorrowRegister.AsNoTracking().Where(c => !c.Deleted);
if (!request.ShowRegistered)
{
query = query.Where(c => c.Status != BorrowRegisterStatus.Registered);
}
if (!string.IsNullOrEmpty(request.Keyword))
{
query = query.Where(c => c.Phone.Contains(request.Keyword.Trim()) || c.Borrower.Contains(request.Keyword.Trim()) || c.Company.Contains(request.Keyword.Trim()) || c.Department.Contains(request.Keyword.Trim()));
}
if (request.StartDate.HasValue)
{
query = query.Where(c => c.CreateTime > request.StartDate.Value);
}
if (request.EndDate.HasValue)
{
query = query.Where(c => c.CreateTime < request.EndDate.Value.AddDays(1));
}
if (request.iSortCol_0 == "0")
{
if (request.sSortDir_0 == "asc")
{
query = query.OrderBy(c => c.CreateTime);
}
else
{
query = query.OrderByDescending(c => c.CreateTime);
}
}
else if (request.iSortCol_0 == "5")
{
if (request.sSortDir_0 == "asc")
{
query = query.OrderBy(c => c.ReturnDate);
}
else
{
query = query.OrderByDescending(c => c.ReturnDate);
}
}
else
{
query = query.OrderByDescending(c => c.CreateTime);
}
var list = await query.Skip(request.PageNumber *request.PageSize)
.Take(request.PageSize)
.Select(c => new SearchBorrowRegisterResult
{
Id = c.Id,
Borrower = c.Borrower,
Company = c.Company,
Department = c.Department,
Phone = c.Phone,
ReturnDate = c.ReturnDate,
SignPhoto = c.SignPhoto,
Status = c.Status,
CreateTime = c.CreateTime,
CreateTimeStr = c.CreateTime.ToString("yyyy-MM-dd"),
Receiver = c.Receiver,
Remark = c.Remark
}).ToListAsync();
var ids = list.Select(c => c.Id);
var archivesList = await _db.BorrowRegisterDetail.AsNoTracking().Where(brd => ids.Contains(brd.BorrowRegisterId)).ToListAsync();
list.ForEach(c =>
{
var arlist = archivesList.Where(j => j.BorrowRegisterId == c.Id);
c.ArchivesStr = string.Join("，", arlist.Select(j => $"{j.ArchivesNumber}/{j.CategoryNumber}/{j.FileNumber}/{j.OrderNumber}"));
c.ReturnDateStr = c.ReturnDate.ToString("yyyy-MM-dd");
c.ProjectName = string.Join("，", arlist.Select(j => j.ProjectName));
});
var total = await query.CountAsync();
response.Data = list;
response.TotalPage = total.GetPages(request.PageSize);
response.TotalCount = total;
response.Success = true;
//response.Data = list;
//response.Success = true;
}
catch (Exception ex)
{
response.Message = "获取借阅列表失败";
ApplicationLog.Error("SearchBorrowRegister", ex);
}
return(response);
}
public async Task <CommonSearchResponse <List <SearchBorrowRegisterResult> > > SearchBorrowRegister(SearchBorrowRegisterRequest request)
{
return(await _borrowRegisterService.SearchBorrowRegister(request));
}
public async Task <CommonSearchResponse <List <SearchBorrowRegisterResult> > > SearchBorrowRegister(SearchBorrowRegisterRequest request)
{
var response = new CommonSearchResponse <List <SearchBorrowRegisterResult> >();
try
{
var query = _db.BorrowRegister.AsNoTracking().Where(c => !c.Deleted);
if (!string.IsNullOrEmpty(request.Keyword))
{
query = query.Where(c => c.Phone.Contains(request.Keyword.Trim()) || c.Borrower.Contains(request.Keyword.Trim()) || c.Company.Contains(request.Keyword.Trim()) || c.Department.Contains(request.Keyword.Trim()));
}
var list = await query.OrderBy(c => c.Status).ThenBy(c => c.Id)
.Skip(request.PageNumber * request.PageSize)
.Take(request.PageSize)
.Select(c => new SearchBorrowRegisterResult
{
Id = c.Id,
Borrower = c.Borrower,
Company = c.Company,
Department = c.Company,
Phone = c.Phone,
ReturnDate = c.ReturnDate,
SignPhoto = c.SignPhoto,
Status = c.Status,
CreateTime = c.CreateTime,
CreateTimeStr = c.CreateTime.ToString("yyyy-MM-dd")
}).ToListAsync();
var ids = list.Select(c => c.Id);
var archivesList = await(from brd in _db.BorrowRegisterDetail.AsNoTracking()
join a in _db.ArchivesInfo.AsNoTracking() on brd.ArchivesId equals a.Id
where ids.Contains(brd.BorrowRegisterId)
select new ArchivesSimple
{
BorrowRegisterId = brd.BorrowRegisterId,
ArchivesNumber = a.ArchivesNumber,
CatalogNumber = a.CatalogNumber,
CategoryId = a.CategoryId,
FileNumber = a.FileNumber,
OrderNumber = a.OrderNumber
}).ToListAsync();
list.ForEach(c =>
{
var arlist = archivesList.Where(j => j.BorrowRegisterId == c.Id).ToList();
c.ArchivesList = arlist;
c.ArchivesStr = string.Join("，", arlist.Select(j => $"{j.ArchivesNumber}/{j.FileNumber}/{j.OrderNumber}"));
c.ReturnDateStr = c.ReturnDate.ToString("yyyy-MM-dd");
});
response.Data = list;
response.Success = true;
}
catch (Exception ex)
{
response.Message = "获取借阅列表失败";
ApplicationLog.Error("SearchBorrowRegister", ex);
}
return(response);
}
