public async Task <ActionResult> InquireContractByCustomAsync(CourseContractQueryViewModel viewModel) { IQueryable <CourseContract> items = await viewModel.InquireContractByCustomAsync(this); if (items == null) { if (!ModelState.IsValid) { return(View(ConsoleHomeController.InputErrorView)); } else { return(View("~/Views/ConsoleHome/Shared/AlertMessage.cshtml", model: ModelState.ErrorMessage())); } } return(View("~/Views/ContractConsole/Module/CustomContractList.cshtml", items)); }
public async Task <ActionResult> GetContractLessonsSummaryAsync(CourseContractQueryViewModel viewModel) { CourseContract item = null; var resultItems = await viewModel.InquireContractByCustomAsync(this); if (resultItems != null) { item = resultItems.FirstOrDefault(); } if (item == null) { return(View("~/Views/Shared/JsAlert.cshtml", model: "資料錯誤!!")); } var items = item.AttendedLessonList(); DataTable getSummary() { DataTable table = new DataTable(); table.Columns.Add(new DataColumn("合約編號", typeof(String))); table.Columns.Add(new DataColumn("姓名", typeof(String))); table.Columns.Add(new DataColumn("月份", typeof(String))); table.Columns.Add(new DataColumn("課程單價", typeof(int))); table.Columns.Add(new DataColumn("上課數", typeof(int))); table.Columns.Add(new DataColumn("上課金額", typeof(int))); foreach (var g in items.GroupBy(l => new { l.ClassTime.Value.Year, l.ClassTime.Value.Month })) { var r = table.NewRow(); r[0] = item.ContractNo(); r[1] = item.ContractLearner(); r[2] = $"{g.Key.Year:0000}{g.Key.Month:00}"; r[3] = item.LessonPriceType.ListPrice; var count = g.Count(); r[4] = count; r[5] = g.Sum(l => l.RegisterLesson.LessonPriceType.ListPrice) * item.CourseContractType.GroupingMemberCount * item.CourseContractType.GroupingLessonDiscount.PercentageOfDiscount / 100; table.Rows.Add(r); } table.TableName = $"截至{DateTime.Today:yyyy-MM-dd}-匯總表"; return(table); } DataTable getDetails() { //上課日期 上課時間 上課地點 上課時間長度 體能顧問姓名 姓名 簽到時間 DataTable table = new DataTable(); table.Columns.Add(new DataColumn("上課日期", typeof(String))); table.Columns.Add(new DataColumn("上課時間", typeof(String))); table.Columns.Add(new DataColumn("上課地點", typeof(String))); table.Columns.Add(new DataColumn("上課時間長度", typeof(int))); table.Columns.Add(new DataColumn("體能顧問姓名", typeof(String))); table.Columns.Add(new DataColumn("姓名", typeof(String))); table.Columns.Add(new DataColumn("簽到時間", typeof(DateTime))); foreach (var lesson in items.OrderBy(l => l.ClassTime)) { var r = table.NewRow(); r[0] = $"{lesson.ClassTime:yyyy/MM/dd}"; r[1] = $"{lesson.ClassTime:HH:mm}~{lesson.ClassTime.Value.AddMinutes(lesson.DurationInMinutes.Value):HH:mm}"; if (lesson.BranchStore?.BranchName != null) { r[2] = lesson.BranchStore.BranchName; } r[3] = lesson.DurationInMinutes; r[4] = lesson.AsAttendingCoach.UserProfile.FullName(); r[5] = String.Join("/", lesson.GroupingLesson.RegisterLesson.Select(g => g.UserProfile).ToArray().Select(u => u.FullName())); if (lesson.LessonPlan.CommitAttendance.HasValue) { r[6] = lesson.LessonPlan.CommitAttendance; } table.Rows.Add(r); } table.TableName = $"截至{DateTime.Today:yyyy-MM-dd}-明細"; return(table); } using (DataSet ds = new DataSet()) { ds.Tables.Add(getSummary()); ds.Tables.Add(getDetails()); await ds.SaveAsExcelAsync(Response, String.Format("attachment;filename={0}({1:yyyy-MM-dd HH-mm-ss}).xlsx", HttpUtility.UrlEncode("LessonsInventoryByContract"), DateTime.Now), viewModel.FileDownloadToken); } return(new EmptyResult()); }