public async Task <ActionResult> CreateContractQueryXlsxAsync(CourseContractQueryViewModel viewModel)
{
IQueryable <CourseContract> items = await viewModel.InquireContractAsync(this);
if (items.Count() == 0)
{
Response.Cookies.Append("fileDownloadToken", viewModel.FileDownloadToken ?? "");
return(View("~/Views/ConsoleHome/Shared/JsAlert.cshtml", model: "資料不存在!!"));
}
var details = items
.ToList()
.Select(i => new
{
合約編號 = i.ContractNo(),
合約名稱 = i.ContractName(),
學生 = i.ContractLearnerName("/"),
合約生效起日 = $"{i.EffectiveDate:yyyyMMdd}",
合約生效迄日 = $"{i.Expiration:yyyyMMdd}",
合約結束日 = $"{i.ValidTo:yyyyMMdd}",
合約總價金 = i.TotalCost,
專業顧問服務總費用 = (i.TotalCost * 8 + 5) / 10,
教練課程費 = (i.TotalCost * 2 + 5) / 10,
課程單價 = i.LessonPriceType.ListPrice,
單堂原價 = i.LessonPriceType.SeriesSingleLessonPrice(),
剩餘上課數 = i.RemainedLessonCount(false),
購買上課數 = i.Lessons,
其他更多說明 = i.Remark,
合約體能顧問 = i.ServingCoach.UserProfile.FullName(false),
簽約場所 = i.CourseContractExtension.BranchStore.BranchName,
狀態 = i.ContractCurrentStatus(),
應收款期限 = $"{i.PayoffDue:yyyyMMdd}",
累計收款金額 = i.TotalPaidAmount(),
累計收款次數 = i.TotalPayoffCount(),
遠距 = i.LessonPriceType.BranchStore?.IsVirtualClassroom() == true ? "是" : "",
});
Response.Cookies.Append("fileDownloadToken", viewModel.FileDownloadToken ?? "");
Response.Headers.Add("Cache-control", "max-age=1");
Response.ContentType = "application/vnd.ms-excel";
Response.Headers.Add("Content-Disposition", String.Format("attachment;filename={0}({1:yyyy-MM-dd HH-mm-ss}).xlsx", HttpUtility.UrlEncode("ContractDetails"), DateTime.Now));
using (DataSet ds = new DataSet())
{
DataTable table = details.ToDataTable();
table.TableName = $"{viewModel.EffectiveDateFrom:yyyyMMdd}~{viewModel.EffectiveDateTo.Value.AddDays(-1):yyyyMMdd}";
ds.Tables.Add(table);
using (var xls = ds.ConvertToExcel())
{
String tmpPath = Path.Combine(FileLogger.Logger.LogDailyPath, $"{DateTime.Now.Ticks}.tmp");
using (FileStream tmp = System.IO.File.Create(tmpPath))
{
xls.SaveAs(tmp);
tmp.Flush();
tmp.Position = 0;
await tmp.CopyToAsync(Response.Body);
}
await Response.Body.FlushAsync();
System.IO.File.Delete(tmpPath);
}
}
return(new EmptyResult());
}
public async Task <ActionResult> CreateContractServiceQueryXlsxAsync(CourseContractQueryViewModel viewModel)
{
IQueryable <CourseContract> items = await viewModel.InquireContractAsync(this);
if (items.Count() == 0)
{
Response.Cookies.Append("fileDownloadToken", viewModel.FileDownloadToken ?? "");
return(View("~/Views/ConsoleHome/Shared/JsAlert.cshtml", model: "資料不存在!!"));
}
void buildDetails(DataSet ds, string reason)
{
var details = items.Join(models.GetTable <CourseContractRevision>().Where(r => r.Reason == reason),
c => c.ContractID, r => r.RevisionID, (c, r) => c)
.ToArray()
.Select(i => new
{
合約編號 = i.ContractNo(),
合約名稱 = i.ContractName(),
合約體能顧問 = i.ServingCoach.UserProfile.FullName(false),
簽約場所 = i.CourseContractExtension.BranchStore.BranchName,
學生 = i.ContractLearnerName("/"),
合約生效起日 = $"{i.CourseContractRevision.SourceContract.EffectiveDate:yyyyMMdd}",
合約生效迄日 = $"{i.CourseContractRevision.SourceContract.Expiration:yyyyMMdd}",
合約結束日 = $"{i.CourseContractRevision.SourceContract.ValidTo:yyyyMMdd}",
合約總價金 = i.TotalCost,
專業顧問服務總費用 = (i.TotalCost * 8 + 5) / 10,
教練課程費 = (i.TotalCost * 2 + 5) / 10,
課程單價 = i.LessonPriceType.ListPrice,
單堂原價 = i.LessonPriceType.SeriesSingleLessonPrice(),
剩餘堂數 = i.RemainedLessonCount(),
購買上課數 = i.Lessons,
編輯日期 = $"{i.ContractDate:yyyyMMdd}",
審核日期 = reason == "轉換體能顧問" || i.CourseContractRevision.OriginalContract == (int)Naming.OperationMode.快速終止
? $"{i.EffectiveDate:yyyyMMdd}"
: $"{i.CourseContractLevel.Where(l => l.LevelID == (int)Naming.ContractServiceStatus.待簽名).Select(l => l.LevelDate).FirstOrDefault():yyyyMMdd}",
簽約日期 = reason == "轉換體能顧問" || i.CourseContractRevision.OriginalContract == (int)Naming.OperationMode.快速終止
? null
: $"{i.CourseContractLevel.Where(l => l.LevelID == (int)Naming.ContractServiceStatus.已生效).Select(l => l.LevelDate).FirstOrDefault():yyyyMMdd}",
狀態 = i.ContractCurrentStatus(),
其他更多說明 = i.Remark,
});
DataTable table = details.ToDataTable();
table.TableName = $"{reason} {viewModel.EffectiveDateFrom:yyyyMMdd}~{viewModel.EffectiveDateTo.Value.AddDays(-1):yyyyMMdd}";
ds.Tables.Add(table);
}
void buildTerminationDetails(DataSet ds, string reason)
{
var details = items.Join(models.GetTable <CourseContractRevision>().Where(r => r.Reason == reason),
c => c.ContractID, r => r.RevisionID, (c, r) => c)
.ToArray()
.Select(i => new
{
合約編號 = i.ContractNo(),
合約名稱 = i.ContractName(),
合約體能顧問 = i.ServingCoach.UserProfile.FullName(false),
簽約場所 = i.CourseContractExtension.BranchStore.BranchName,
學生 = i.ContractLearnerName("/"),
合約生效起日 = $"{i.CourseContractRevision.SourceContract.EffectiveDate:yyyyMMdd}",
合約生效迄日 = $"{i.CourseContractRevision.SourceContract.Expiration:yyyyMMdd}",
合約結束日 = $"{i.CourseContractRevision.SourceContract.ValidTo:yyyyMMdd}",
合約總價金 = i.TotalCost,
專業顧問服務總費用 = (i.TotalCost * 8 + 5) / 10,
教練課程費 = (i.TotalCost * 2 + 5) / 10,
課程單價 = i.LessonPriceType.ListPrice,
單堂原價 = i.LessonPriceType.SeriesSingleLessonPrice(),
剩餘堂數 = i.RemainedLessonCount(),
購買上課數 = i.Lessons,
編輯日期 = $"{i.ContractDate:yyyyMMdd}",
審核日期 = reason == "轉換體能顧問" || i.CourseContractRevision.OperationMode == (int)Naming.OperationMode.快速終止
? $"{i.EffectiveDate:yyyyMMdd}"
: $"{i.CourseContractLevel.Where(l => l.LevelID == (int)Naming.ContractServiceStatus.待簽名).Select(l => l.LevelDate).FirstOrDefault():yyyyMMdd}",
簽約日期 = reason == "轉換體能顧問" || i.CourseContractRevision.OperationMode == (int)Naming.OperationMode.快速終止
? null
: $"{i.CourseContractLevel.Where(l => l.LevelID == (int)Naming.ContractServiceStatus.已生效).Select(l => l.LevelDate).FirstOrDefault():yyyyMMdd}",
終止類別 = $"{(Naming.OperationMode?)i.CourseContractRevision.OperationMode}",
狀態 = i.ContractCurrentStatus(),
其他更多說明 = i.Remark,
});
DataTable table = details.ToDataTable();
table.TableName = $"{reason} {viewModel.EffectiveDateFrom:yyyyMMdd}~{viewModel.EffectiveDateTo.Value.AddDays(-1):yyyyMMdd}";
ds.Tables.Add(table);
}
void buildConsultantAssignmentDetails(DataSet ds, string reason)
{
var details = items.Join(models.GetTable <CourseContractRevision>().Where(r => r.Reason == reason),
c => c.ContractID, r => r.RevisionID, (c, r) => c)
.ToArray()
.Select(i => new
{
合約編號 = i.ContractNo(),
合約名稱 = i.ContractName(),
原合約體能顧問 = i.CourseContractRevision.CourseContractRevisionItem?.ServingCoach.UserProfile.FullName(false),
合約體能顧問 = i.ServingCoach.UserProfile.FullName(false),
簽約場所 = i.CourseContractExtension.BranchStore.BranchName,
學生 = i.ContractLearnerName("/"),
合約生效起日 = $"{i.CourseContractRevision.SourceContract.EffectiveDate:yyyyMMdd}",
合約生效迄日 = $"{i.CourseContractRevision.SourceContract.Expiration:yyyyMMdd}",
合約結束日 = $"{i.CourseContractRevision.SourceContract.ValidTo:yyyyMMdd}",
合約總價金 = i.TotalCost,
專業顧問服務總費用 = (i.TotalCost * 8 + 5) / 10,
教練課程費 = (i.TotalCost * 2 + 5) / 10,
課程單價 = i.LessonPriceType.ListPrice,
單堂原價 = i.LessonPriceType.SeriesSingleLessonPrice(),
購買上課數 = i.Lessons,
編輯日期 = $"{i.ContractDate:yyyyMMdd}",
//簽約日期 = reason == "轉換體能顧問" || i.CourseContractRevision.OriginalContract == (int)Naming.OperationMode.快速終止
// ? null
// : $"{i.CourseContractLevel.Where(l => l.LevelID == (int)Naming.ContractServiceStatus.已生效).Select(l => l.LevelDate).FirstOrDefault():yyyyMMdd}",
審核日期 = reason == "轉換體能顧問" || i.CourseContractRevision.OriginalContract == (int)Naming.OperationMode.快速終止
? $"{i.EffectiveDate:yyyyMMdd}"
: $"{i.CourseContractLevel.Where(l => l.LevelID == (int)Naming.ContractServiceStatus.待簽名).Select(l => l.LevelDate).FirstOrDefault():yyyyMMdd}",
狀態 = i.ContractCurrentStatus(),
其他更多說明 = i.Remark,
});
DataTable table = details.ToDataTable();
table.TableName = $"{reason} {viewModel.EffectiveDateFrom:yyyyMMdd}~{viewModel.EffectiveDateTo.Value.AddDays(-1):yyyyMMdd}";
ds.Tables.Add(table);
}
Response.Cookies.Append("fileDownloadToken", viewModel.FileDownloadToken ?? "");
Response.Headers.Add("Cache-control", "max-age=1");
Response.ContentType = "application/vnd.ms-excel";
Response.Headers.Add("Content-Disposition", String.Format("attachment;filename={0}({1:yyyy-MM-dd HH-mm-ss}).xlsx", HttpUtility.UrlEncode("ContractServiceDetails"), DateTime.Now));
using (DataSet ds = new DataSet())
{
buildDetails(ds, "展延");
buildTerminationDetails(ds, "終止");
//buildDetails(ds, "轉讓");
buildConsultantAssignmentDetails(ds, "轉換體能顧問");
using (var xls = ds.ConvertToExcel())
{
String tmpPath = Path.Combine(FileLogger.Logger.LogDailyPath, $"{DateTime.Now.Ticks}.tmp");
using (FileStream tmp = System.IO.File.Create(tmpPath))
{
xls.SaveAs(tmp);
tmp.Flush();
tmp.Position = 0;
await tmp.CopyToAsync(Response.Body);
}
await Response.Body.FlushAsync();
System.IO.File.Delete(tmpPath);
}
}
return(new EmptyResult());
}
