public ActionResult InquireContractByCustom(CourseContractQueryViewModel viewModel) { IQueryable <CourseContract> items = viewModel.InquireContractByCustom(this, out String alertMessage); if (items == null) { if (!ModelState.IsValid) { return(View(ConsoleHomeController.InputErrorView)); } else { return(View("~/Views/ConsoleHome/Shared/AlertMessage.cshtml", model: alertMessage)); } } return(View("~/Views/ContractConsole/Module/CustomContractList.cshtml", items)); }
public ActionResult GetContractLessonsSummary(CourseContractQueryViewModel viewModel) { CourseContract item = null; var resultItems = viewModel.InquireContractByCustom(this, out string alertMessage); 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); } Response.Clear(); Response.ClearContent(); Response.ClearHeaders(); Response.AppendCookie(new HttpCookie("fileDownloadToken", viewModel.FileDownloadToken)); Response.AddHeader("Cache-control", "max-age=1"); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", String.Format("attachment;filename=LessonsInventoryByContract({0:yyyy-MM-dd HH-mm-ss}).xlsx", DateTime.Now)); using (DataSet ds = new DataSet()) { ds.Tables.Add(getSummary()); ds.Tables.Add(getDetails()); using (var xls = ds.ConvertToExcel()) { xls.SaveAs(Response.OutputStream); } } return(new EmptyResult()); }