public async Task <APIResultDto> ExportSheduleSumExcel(SheduleSumInput input)
{
try
{
var exportData = await GetSumShedule(input);
var result = new APIResultDto();
result.Code = 0;
result.Data = CreateSheduleSumExcel("任务汇总.xlsx", exportData);
return(result);
}
catch (Exception ex)
{
Logger.ErrorFormat("ExportEmployeesExcel errormsg{0} Exception{1}", ex.Message, ex);
return(new APIResultDto()
{
Code = 901, Msg = "网络忙...请待会儿再试！"
});
}
}
/// <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;
}