/// <summary>
/// 创建任务汇总的Excel
/// </summary>
/// <param name="fileName"></param>
/// <param name="data"></param>
/// <returns></returns>
public string CreateSheduleSumExcel(string fileName, SheduleSumStatisDto data)
{
var fullPath = ExcelHelper.GetSavePath(_hostingEnvironment.WebRootPath) + fileName;
using (var fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("SheduleSum");
var rowIndex = 0;
IRow titleRow = sheet.CreateRow(rowIndex);
string[] titles = { "区域", "计划名", "计划时间", "任务名", "任务类型", "计划数", "完成数", "逾期数", "完成率" };
var fontTitle = workbook.CreateFont();
fontTitle.IsBold = true;
for (int i = 0; i < titles.Length; i++)
{
var cell = titleRow.CreateCell(i);
cell.CellStyle.SetFont(fontTitle);
cell.SetCellValue(titles[i]);
//ExcelHelper.SetCell(titleRow.CreateCell(i), fontTitle, titles[i]);
}
var font = workbook.CreateFont();
foreach (var item in data.sheduleSumDtos)
{
rowIndex++;
IRow row = sheet.CreateRow(rowIndex);
ExcelHelper.SetCell(row.CreateCell(0), font, item.AreaName);
ExcelHelper.SetCell(row.CreateCell(1), font, item.SheduleName);
ExcelHelper.SetCell(row.CreateCell(2), font, item.Time);
ExcelHelper.SetCell(row.CreateCell(3), font, item.TaskName);
ExcelHelper.SetCell(row.CreateCell(4), font, item.TaskTypeName);
ExcelHelper.SetCell(row.CreateCell(5), font, item.Total.ToString());
ExcelHelper.SetCell(row.CreateCell(6), font, item.Complete.ToString());
ExcelHelper.SetCell(row.CreateCell(7), font, item.Expired.ToString());
ExcelHelper.SetCell(row.CreateCell(8), font, item.CompleteRate);
}
var completeRateT = "0%";
if (data.CompleteSum != 0 && data.TotalSum != 0)
{
completeRateT = (Math.Round((double)data.CompleteSum / data.TotalSum, 2) * 100).ToString() + "%";
}
rowIndex++;
IRow rowEnd = sheet.CreateRow(rowIndex);
ExcelHelper.SetCell(rowEnd.CreateCell(0), font, "总计：");
ExcelHelper.SetCell(rowEnd.CreateCell(5), font, data.TotalSum.ToString());
ExcelHelper.SetCell(rowEnd.CreateCell(6), font, data.CompleteSum.ToString());
ExcelHelper.SetCell(rowEnd.CreateCell(7), font, data.ExpiredSum.ToString());
ExcelHelper.SetCell(rowEnd.CreateCell(8), font, completeRateT);
workbook.Write(fs);
}
return("/files/downloadtemp/" + fileName);
}
/// <summary>
/// 获取任务汇总数据(按区域、任务类型、任务名)
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public async Task <SheduleSumStatisDto> GetSumShedule(SheduleSumInput input)
{
var areaCode = await GetCurrentUserAreaCodeAsync();
var areaCodeE = areaCode.HasValue ? areaCode : input.AreaCode;
var timeNow = DateTime.Today;
//input.StartTime = input.StartTime.HasValue ? input.StartTime : timeNow.AddDays(1 - timeNow.Day);
//input.EndTime = input.EndTime.HasValue ? input.EndTime : timeNow.AddDays(1 - timeNow.Day).AddMonths(1).AddDays(-1);
var query = from sd in _scheduledetailRepository.GetAll()
join t in _visittaskRepository.GetAll()
.WhereIf(input.TaskId.HasValue, t => t.Id == input.TaskId)
on sd.TaskId equals t.Id
join s in _scheduleRepository.GetAll()
.WhereIf(input.StartTime.HasValue, s => s.EndTime >= input.StartTime)
.WhereIf(input.EndTime.HasValue, s => s.EndTime <= input.EndTime)
.WhereIf(!string.IsNullOrEmpty(input.SheduleName), s => s.Desc.Contains(input.SheduleName))
.Where(s => s.Status == ScheduleMasterStatusEnum.已发布)
on sd.ScheduleId equals s.Id
join g in _growerRepository.GetAll()
.WhereIf(areaCodeE.HasValue, g => g.AreaCode == areaCodeE)
//.WhereIf(input.AreaCode.HasValue, g => g.AreaCode == input.AreaCode)
on sd.GrowerId equals g.Id
select new
{
g.AreaCode,
t.Type,
t.Name,
sd.VisitNum,
sd.CompleteNum,
sd.Status,
s.Id,
s.Desc,
s.BeginTime,
s.EndTime
};
var equery = from q in query
group new
{
q.AreaCode,
q.Type,
q.Name,
q.VisitNum,
q.CompleteNum,
q.Status
} by new { q.AreaCode, q.Type, q.Name, q.Id, q.Desc, q.BeginTime, q.EndTime } into gq
select new SheduleSumDto()
{
AreaCode = gq.Key.AreaCode,
TaskType = gq.Key.Type,
TaskName = gq.Key.Name,
Total = gq.Sum(g => g.VisitNum),
Complete = gq.Sum(g => g.CompleteNum),
Expired = gq.Where(m => m.Status == ScheduleStatusEnum.已逾期).Sum(s => s.VisitNum - s.CompleteNum),
Time = gq.Key.BeginTime.Value.ToString("yyyy-MM-dd") + "至" + gq.Key.EndTime.Value.ToString("yyyy-MM-dd"),
SheduleName = gq.Key.Desc
};
var result = new SheduleSumStatisDto();
var dataList = (await equery.OrderBy(s => s.AreaCode).ToListAsync()).MapTo <List <SheduleSumDto> >();
result.sheduleSumDtos = dataList;
var total = dataList.Sum(s => s.Total);
result.TotalSum = total.HasValue ? total.Value : 0;
var complete = dataList.Sum(s => s.Complete);
result.CompleteSum = complete.HasValue ? complete.Value : 0;
var expireds = dataList.Sum(s => s.Expired);
result.ExpiredSum = expireds.HasValue ? expireds.Value : 0;
return(result);
//var query = (from sd in _scheduledetailRepository.GetAll()
// join s in _scheduleRepository.GetAll().Where(s => s.BeginTime >= input.StartTime && s.BeginTime <= input.EndTime) on sd.ScheduleId equals s.Id
// join t in _visittaskRepository.GetAll().WhereIf(!string.IsNullOrEmpty(input.TaskName), t => t.Name.Contains(input.TaskName)) on sd.TaskId equals t.Id
// join g in _growerRepository.GetAll().WhereIf(input.Area.HasValue, g => g.CountyCode == input.Area) on sd.GrowerId equals g.Id into gs
// from sdg in gs.DefaultIfEmpty()
// select new
// {
// sdg.CountyCode,
// t.Name,
// t.Type,
// sd.VisitNum,
// sd.CompleteNum,
// sd.Status
// }).ToList();
//var list = query.Select(s => new SheduleSumDto
//{
// Area = s.CountyCode,
// TaskName = s.Name,
// TaskType = s.Type,
// Total = s.VisitNum.HasValue ? s.VisitNum.Value : 0,
// Complete = s.CompleteNum.HasValue ? s.VisitNum.Value : 0,
// Expired = s.VisitNum.HasValue ? s.VisitNum.Value : 0,
//})
//var list =await _scheduledetailRepository.GetSheduleSum(input.Area, input.StartTime, input.EndTime, input.TaskName);
//return list;
}
