/// <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; }