public async Task <ActionResult> ShowCauseForEndingDonutChartAsync(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
var profile = await HttpContext.GetUserAsync();
return(View("~/Views/ContractConsole/Module/ShowCauseForEndingDonutChart.cshtml", profile));
}
public ActionResult ShowCauseForEndingDonutChart(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
var profile = HttpContext.GetUser();
return(View("~/Views/ContractConsole/Module/ShowCauseForEndingDonutChart.cshtml", profile));
}
public ActionResult CalculateTotalCost(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
var item = models.GetTable <LessonPriceType>().Where(p => p.PriceID == viewModel.PriceID).FirstOrDefault();
if (item != null)
{
if (item.IsPackagePrice)
{
viewModel.TotalCost = item.ListPrice;
}
else
{
viewModel.TotalCost = item.ListPrice * viewModel.Lessons;
var typeItem = models.GetTable <CourseContractType>().Where(t => t.TypeID == (int?)viewModel.ContractType).FirstOrDefault();
if (typeItem != null)
{
viewModel.TotalCost = viewModel.TotalCost * typeItem.GroupingMemberCount * typeItem.GroupingLessonDiscount.PercentageOfDiscount / 100;
}
}
}
return(View("~/Views/ContractConsole/Editing/TotalCostSummary.cshtml"));
}
public ActionResult InvokeContractQuery(CourseContractQueryViewModel viewModel)
{
//viewModel.ContractDateFrom = DateTime.Today.FirstDayOfMonth();
//viewModel.ContractDateTo = viewModel.ContractDateFrom.Value.AddMonths(1).AddDays(-1);
viewModel.ByCustom = true;
ViewBag.ViewModel = viewModel;
return(View("~/Views/ContractConsole/ContractModal/ContractQuery.cshtml"));
}
public ActionResult CreateContractQueryXlsx(CourseContractQueryViewModel viewModel)
{
ViewResult result = (ViewResult)InquireContract(viewModel);
IQueryable <CourseContract> items = (IQueryable <CourseContract>)result.Model;
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(String)));
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)));
table.Columns.Add(new DataColumn("單堂原價", typeof(int)));
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(String)));
table.Columns.Add(new DataColumn("未完成堂數", typeof(int)));
table.Columns.Add(new DataColumn("已收金額", typeof(int)));
table.Columns.Add(new DataColumn("合約完成日", typeof(String)));
table.Columns.Add(new DataColumn("已發生折讓總額", typeof(decimal)));
foreach (var item in items)
{
assignDataRow(table, item);
foreach (var r in item.RevisionList)
{
assignDataRow(table, r.CourseContract);
}
}
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Cache-control", "max-age=1");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", String.Format("attachment;filename=({1:yyyy-MM-dd HH-mm-ss}){0}", HttpUtility.UrlEncode("ContractDetails.xlsx"), DateTime.Now));
using (DataSet ds = new DataSet())
{
table.TableName = "合約列表";
ds.Tables.Add(table);
using (var xls = ds.ConvertToExcel())
{
xls.SaveAs(Response.OutputStream);
}
}
return(new EmptyResult());
}
public ActionResult ListContractMember(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
if (viewModel.UID != null && viewModel.UID.Length > 0)
{
viewModel.UID = viewModel.UID.Distinct().ToArray();
}
return(View("~/Views/ContractConsole/Editing/ListContractMember.cshtml"));
}
public ActionResult ProcessContractService(CourseContractQueryViewModel viewModel)
{
ViewResult result = (ViewResult)ProcessContract(viewModel);
CourseContract item = result.Model as CourseContract;
if (item != null)
{
result.ViewName = "~/Views/ContractConsole/ContractModal/ProcessContractService.cshtml";
}
return(result);
}
public ActionResult LoadContract(CourseContractQueryViewModel viewModel)
{
ViewResult result = (ViewResult)InquireContract(viewModel);
IQueryable <CourseContract> items = (IQueryable <CourseContract>)result.Model;
return(Json(new
{
result = true,
data = items.ToArray()
.Select(c => new { ContractNo = c.ContractNo(), c.TotalCost, c.Installment })
}, JsonRequestBehavior.AllowGet));
}
public ActionResult ShowRemainedLessonList(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
var profile = models.GetTable <UserProfile>().Where(u => u.UID == viewModel.MemberID)
.FirstOrDefault() ?? new UserProfile {
UID = -1
};
_ = profile.RemainedLessonCount(models, out int remainedCount, out IQueryable <RegisterLesson> remainedLessons);
return(View("~/Views/ConsoleHome/CourseContract/RemainedLessonListModal.cshtml", remainedLessons));
}
public async Task <ActionResult> InquireContractAsync(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
if (viewModel.ByCustom == true)
{
return(await InquireContractByCustomAsync(viewModel));
}
IQueryable <CourseContract> items = viewModel.InquireContract(models);
return(View("~/Views/ContractConsole/Module/ContractList.cshtml", items));
}
public async Task <ActionResult> LoadContractAsync(CourseContractQueryViewModel viewModel)
{
ViewResult result = (ViewResult) await InquireContractAsync(viewModel);
IQueryable <CourseContract> items = (IQueryable <CourseContract>)result.Model;
return(Json(new
{
result = true,
data = items.ToArray()
.Select(c => new { ContractNo = c.ContractNo(), c.TotalCost, c.Installment })
}));
}
public ActionResult ShowContractDetails(CourseContractQueryViewModel viewModel)
{
ViewResult result = (ViewResult)ProcessContract(viewModel);
CourseContract item = result.Model as CourseContract;
if (item == null)
{
return(result);
}
return(View("~/Views/ContractConsole/ContractModal/AboutContractDetails.cshtml", item));
}
public static async Task <IQueryable <CourseContract> > InquireContractAsync(this CourseContractQueryViewModel viewModel, SampleController <UserProfile> controller)
{
var ModelState = controller.ModelState;
var ViewBag = controller.ViewBag;
var HttpContext = controller.HttpContext;
var models = controller.DataSource;
var profile = await HttpContext.GetUserAsync();
ViewBag.ViewModel = viewModel;
return(viewModel.InquireContract(models));
}
public static IQueryable <CourseContract> InquireContract <TEntity>(this CourseContractQueryViewModel viewModel, SampleController <TEntity> controller, out String alertMessage)
where TEntity : class, new()
{
alertMessage = null;
var ModelState = controller.ModelState;
var ViewBag = controller.ViewBag;
var HttpContext = controller.HttpContext;
var models = controller.DataSource;
var profile = HttpContext.GetUser();
ViewBag.ViewModel = viewModel;
return(viewModel.InquireContract(models));
}
public ActionResult ProcessContract(CourseContractQueryViewModel viewModel)
{
if (viewModel.KeyID != null)
{
viewModel.ContractID = viewModel.DecryptKeyValue();
}
var item = models.GetTable <CourseContract>().Where(c => c.ContractID == viewModel.ContractID).FirstOrDefault();
if (item == null)
{
return(View("~/Views/ConsoleHome/Shared/AlertMessage.cshtml", model: "合約資料錯誤!!"));
}
return(View("~/Views/ContractConsole/ContractModal/ProcessContract.cshtml", item));
}
public async Task <ActionResult> CreateContractQueryXlsxAsync(CourseContractQueryViewModel viewModel)
{
ViewResult result = (ViewResult) await InquireContractAsync(viewModel);
IQueryable <CourseContract> items = (IQueryable <CourseContract>)result.Model;
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(String)));
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)));
table.Columns.Add(new DataColumn("單堂原價", typeof(int)));
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(String)));
table.Columns.Add(new DataColumn("未完成堂數", typeof(int)));
table.Columns.Add(new DataColumn("已收金額", typeof(int)));
table.Columns.Add(new DataColumn("合約完成日", typeof(String)));
table.Columns.Add(new DataColumn("已發生折讓總額", typeof(decimal)));
foreach (var item in items)
{
assignDataRow(table, item);
foreach (var r in item.RevisionList)
{
assignDataRow(table, r.CourseContract);
}
}
using (DataSet ds = new DataSet())
{
table.TableName = "合約列表";
ds.Tables.Add(table);
await ds.SaveAsExcelAsync(Response, String.Format("attachment;filename={0}({1:yyyy-MM-dd HH-mm-ss}).xlsx", HttpUtility.UrlEncode("ContractDetails"), DateTime.Now), viewModel.FileDownloadToken);
}
return(new EmptyResult());
}
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 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 ActionResult InquireContractForAmendment(CourseContractQueryViewModel viewModel)
{
IQueryable <CourseContract> items = models.GetTable <CourseContract>()
.Where(c => c.Status == (int)Naming.CourseContractStatus.已生效 ||
c.Status == (int)Naming.CourseContractStatus.已過期)
.Where(c => c.SequenceNo == 0);
bool hasCondition = false;
viewModel.RealName = viewModel.RealName.GetEfficientString();
if (viewModel.RealName != null)
{
items = items.Where(c => c.CourseContractMember.Any(m => m.UserProfile.RealName.Contains(viewModel.RealName) || m.UserProfile.Nickname.Contains(viewModel.RealName)));
hasCondition = true;
}
viewModel.ContractNo = viewModel.ContractNo.GetEfficientString();
if (viewModel.ContractNo != null)
{
hasCondition = true;
var no = viewModel.ContractNo.Split('-');
int seqNo;
if (no.Length > 1)
{
int.TryParse(no[1], out seqNo);
items = items.Where(c => c.ContractNo.StartsWith(no[0]) &&
c.SequenceNo == seqNo);
}
else
{
items = items.Where(c => c.ContractNo.StartsWith(viewModel.ContractNo));
}
}
if (!hasCondition)
{
return(View("~/Views/ConsoleHome/Shared/JsAlert.cshtml", model: "請輸入查詢條件!!"));
}
return(View("~/Views/CourseContract/Module/ContractQueryAmendmentList.ascx", items));
}
public ActionResult ToSignCourseContract(CourseContractQueryViewModel viewModel, String encUID)
{
int?uid = null;
if (encUID != null)
{
uid = encUID.DecryptKeyValue();
}
var item = models.GetTable <UserProfile>().Where(u => u.UID == uid).FirstOrDefault();
if (item != null && item.LevelID == (int)Naming.MemberStatusDefinition.Checked)
{
HttpContext.SignOn(item);
return(Redirect(Url.Action("SignCourseContract", "ConsoleHome", viewModel)));
}
else
{
return(RedirectToAction("Login", "Account"));
}
}
// GET: ContractConsole
public async Task <ActionResult> ShowContractListAsync(CourseContractQueryViewModel viewModel, bool?popupModal)
{
if (viewModel.KeyID != null)
{
viewModel.ContractID = viewModel.DecryptKeyValue();
}
ViewResult result = (ViewResult) await InquireContractAsync(viewModel);
ViewBag.Contracts = result.Model;
var profile = await HttpContext.GetUserAsync();
if (popupModal == true)
{
return(View("~/Views/ConsoleHome/CourseContract/ContractListModal.cshtml", profile.LoadInstance(models)));
}
else
{
return(View("~/Views/ConsoleHome/ContractIndex.cshtml", profile.LoadInstance(models)));
}
}
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());
}
public static async Task <IQueryable <CourseContract> > InquireContractByCustomAsync(this CourseContractQueryViewModel viewModel, SampleController <UserProfile> controller)
{
var ModelState = controller.ModelState;
var ViewBag = controller.ViewBag;
var HttpContext = controller.HttpContext;
var models = controller.DataSource;
ViewBag.ViewModel = viewModel;
var profile = await HttpContext.GetUserAsync();
bool hasConditon = false;
IQueryable <CourseContract> items;
if (viewModel.ContractQueryMode == Naming.ContractServiceMode.ContractOnly)
{
items = models.PromptContract();
}
else if (viewModel.ContractQueryMode == Naming.ContractServiceMode.ServiceOnly)
{
items = models.PromptContractService();
}
else
{
items = models.GetTable <CourseContract>();
}
if (viewModel.KeyID != null)
{
viewModel.ContractID = viewModel.DecryptKeyValue();
}
if (viewModel.ContractID.HasValue)
{
items = items.Where(c => c.ContractID == viewModel.ContractID);
}
if (viewModel.ContractDateFrom.HasValue)
{
hasConditon = true;
items = items.Where(c => c.ContractDate >= viewModel.ContractDateFrom);
}
if (viewModel.ContractDateTo.HasValue)
{
hasConditon = true;
items = items.Where(c => c.ContractDate < viewModel.ContractDateTo.Value);
}
if (viewModel.EffectiveDateFrom.HasValue)
{
hasConditon = true;
items = items.Where(c => c.EffectiveDate >= viewModel.EffectiveDateFrom);
}
if (viewModel.EffectiveDateTo.HasValue)
{
hasConditon = true;
items = items.Where(c => c.EffectiveDate < viewModel.EffectiveDateTo);
}
Expression <Func <CourseContract, bool> > queryExpr = c => false;
bool subCondition = false;
viewModel.CustomQuery = viewModel.CustomQuery.GetEfficientString();
viewModel.ContractNo = viewModel.ContractNo.GetEfficientString();
viewModel.RealName = viewModel.RealName.GetEfficientString();
if (viewModel.CustomQuery != null)
{
if (viewModel.ContractNo == null)
{
viewModel.ContractNo = viewModel.CustomQuery;
}
if (viewModel.RealName == null)
{
viewModel.RealName = viewModel.CustomQuery;
}
}
if (viewModel.ContractNo != null)
{
subCondition = true;
var no = viewModel.ContractNo.Split('-');
if (no.Length > 1)
{
int.TryParse(no[1], out int seqNo);
queryExpr = queryExpr.Or(c => c.ContractNo.StartsWith(no[0]) &&
c.SequenceNo == seqNo);
}
else
{
queryExpr = queryExpr.Or(c => c.ContractNo.StartsWith(viewModel.ContractNo));
}
}
if (viewModel.RealName != null)
{
subCondition = true;
queryExpr = queryExpr.Or(c => c.CourseContractMember.Any(m => m.UserProfile.RealName.Contains(viewModel.RealName) || m.UserProfile.Nickname.Contains(viewModel.RealName)));
}
if (hasConditon)
{
if (subCondition)
{
items = items.Where(queryExpr);
}
else
{
if (viewModel.FitnessConsultant.HasValue)
{
hasConditon = true;
items = items.Where(c => c.FitnessConsultant == viewModel.FitnessConsultant);
}
if (viewModel.ManagerID.HasValue)
{
hasConditon = true;
items = items.FilterByBranchStoreManager(models, viewModel.ManagerID);
}
if (viewModel.OfficerID.HasValue)
{
hasConditon = true;
}
}
}
else
{
if (subCondition)
{
items = items.Where(queryExpr);
}
else
{
//items = items.Where(c => false);
//return Json(new { result = false, message = "請設定查詢條件!!" });
ModelState.AddModelError("RealName", "請輸入查詢學生姓名(暱稱)!!");
ModelState.AddModelError("ContractNo", "請輸入查詢合約編號!!");
ModelState.AddModelError("ContractDateFrom", "請輸入查詢合約起日!!");
ModelState.AddModelError("ContractDateTo", "請輸入查詢合約迄日!!");
ViewBag.ModelState = ModelState;
return(null);
}
}
return(items);
}
public static IQueryable <CourseContract> InquireContract(this CourseContractQueryViewModel viewModel, GenericManager <BFDataContext> models)
{
bool hasConditon = false;
IQueryable <CourseContract> items;
if (viewModel.ContractQueryMode == Naming.ContractServiceMode.ContractOnly)
{
if (viewModel.Status >= (int)Naming.CourseContractStatus.已生效)
{
items = models.PromptOriginalContract();
}
else if (viewModel.PayoffMode.HasValue)
{
items = models.PromptAccountingContract();
}
else
{
items = models.PromptContract();
}
}
else if (viewModel.ContractQueryMode == Naming.ContractServiceMode.ServiceOnly)
{
items = models.PromptContractService();
}
else
{
items = models.GetTable <CourseContract>();
}
if (viewModel.ContractID.HasValue)
{
hasConditon = true;
items = items.Where(c => c.ContractID == viewModel.ContractID);
}
if (viewModel.PayoffMode == Naming.ContractPayoffMode.Unpaid)
{
hasConditon = true;
items = items
.FilterByToPay(models);
}
else if (viewModel.PayoffMode == Naming.ContractPayoffMode.Paid)
{
hasConditon = true;
items = items.Where(c => c.ContractPayment.Any());
}
if (viewModel.IncludeTotalUnpaid == true)
{
Expression <Func <CourseContract, bool> > expr = c => true;
Expression <Func <CourseContract, bool> > defaultExpr = expr;
if (viewModel.PayoffDueFrom.HasValue)
{
hasConditon = true;
expr = expr.And(c => c.PayoffDue >= viewModel.PayoffDueFrom);
}
if (viewModel.PayoffDueTo.HasValue)
{
hasConditon = true;
expr = expr.And(c => c.PayoffDue < viewModel.PayoffDueTo);
}
if (defaultExpr != expr)
{
expr = expr.Or(c => !c.PayoffDue.HasValue);
items = items.Where(expr);
}
}
else
{
if (viewModel.PayoffDueFrom.HasValue)
{
hasConditon = true;
items = items.Where(c => c.PayoffDue >= viewModel.PayoffDueFrom);
}
if (viewModel.PayoffDueTo.HasValue)
{
hasConditon = true;
items = items.Where(c => c.PayoffDue < viewModel.PayoffDueTo);
}
}
if (viewModel.Status.HasValue)
{
hasConditon = true;
items = items.Where(c => c.Status == viewModel.Status);
}
if (viewModel.FitnessConsultant.HasValue)
{
hasConditon = true;
items = items.Where(c => c.FitnessConsultant == viewModel.FitnessConsultant);
}
if (viewModel.ManagerID.HasValue)
{
hasConditon = true;
items = items.FilterByBranchStoreManager(models, viewModel.ManagerID);
}
else if (viewModel.ViceManagerID.HasValue)
{
hasConditon = true;
items = items.FilterByBranchStoreManager(models, viewModel.ManagerID)
.Where(c => c.AgentID != viewModel.ViceManagerID);
}
if (viewModel.OfficerID.HasValue)
{
hasConditon = true;
}
if (viewModel.IsExpired == true)
{
hasConditon = true;
items = items.FilterByExpired(models);
}
else if (viewModel.IsExpired == false)
{
hasConditon = true;
items = items.Where(c => c.Expiration >= DateTime.Today);
}
if (viewModel.EffectiveDateFrom.HasValue)
{
hasConditon = true;
items = items.Where(c => c.EffectiveDate >= viewModel.EffectiveDateFrom);
}
if (viewModel.EffectiveDateTo.HasValue)
{
hasConditon = true;
items = items.Where(c => c.EffectiveDate < viewModel.EffectiveDateTo);
}
if (viewModel.ExpirationFrom.HasValue)
{
hasConditon = true;
items = items.Where(c => c.Expiration >= viewModel.ExpirationFrom);
}
if (viewModel.ExpirationTo.HasValue)
{
hasConditon = true;
items = items.Where(c => c.Expiration < viewModel.ExpirationTo.Value);
}
if (viewModel.KeyID != null)
{
viewModel.ContractID = viewModel.DecryptKeyValue();
}
if (viewModel.ContractID.HasValue)
{
hasConditon = true;
items = items.Where(c => c.ContractID == viewModel.ContractID);
}
if (viewModel.AlarmCount.HasValue)
{
hasConditon = true;
items = items.FilterByAlarmedContract(models, viewModel.AlarmCount.Value);
}
if (viewModel.InstallmentID.HasValue)
{
hasConditon = true;
items = items.Where(c => c.InstallmentID == viewModel.InstallmentID);
}
if (viewModel.UnpaidExpiring == true)
{
hasConditon = true;
items = items.FilterByUnpaidContract(models)
.Where(p => p.PayoffDue < DateTime.Today);
}
if (viewModel.Unpaid == true)
{
hasConditon = true;
items = items.FilterByUnpaidContract(models);
}
if (viewModel.MemberID.HasValue)
{
hasConditon = true;
items = items.Where(c => c.CourseContractMember.Any(m => m.UID == viewModel.MemberID));
}
viewModel.Subject = viewModel.Subject.GetEfficientString();
if (viewModel.Subject != null)
{
items = items.Where(c => c.Subject == viewModel.Subject);
}
if (hasConditon)
{
}
else
{
items = items.Where(c => false);
}
//if (viewModel.ContractType.HasValue)
// items = items.Where(c => c.ContractType == viewModel.ContractType);
return(items);
}
public async Task <ActionResult> InquireContractAsync(CourseContractQueryViewModel viewModel)
{
IQueryable <CourseContract> items = models.GetTable <CourseContract>();
//.Where(c => c.SequenceNo == 0);
var profile = await HttpContext.GetUserAsync();
Expression <Func <CourseContract, bool> > queryExpr = c => false;
bool hasConditon = false;
viewModel.RealName = viewModel.RealName.GetEfficientString();
if (viewModel.RealName != null)
{
hasConditon = true;
queryExpr = queryExpr.Or(c => c.CourseContractMember.Any(m => m.UserProfile.RealName.Contains(viewModel.RealName) || m.UserProfile.Nickname.Contains(viewModel.RealName)));
}
if (!hasConditon)
{
viewModel.ContractNo = viewModel.ContractNo.GetEfficientString();
if (viewModel.ContractNo != null)
{
hasConditon = true;
var no = viewModel.ContractNo.Split('-');
int seqNo;
if (no.Length > 1)
{
int.TryParse(no[1], out seqNo);
queryExpr = queryExpr.Or(c => c.ContractNo.StartsWith(no[0]) &&
c.SequenceNo == seqNo);
}
else
{
queryExpr = queryExpr.Or(c => c.ContractNo.StartsWith(viewModel.ContractNo));
}
}
}
if (!hasConditon)
{
if (viewModel.FitnessConsultant.HasValue)
{
hasConditon = true;
queryExpr = queryExpr.Or(c => c.FitnessConsultant == viewModel.FitnessConsultant);
}
}
if (!hasConditon)
{
if (viewModel.BranchID.HasValue)
{
hasConditon = true;
queryExpr = queryExpr.Or(c => c.CourseContractExtension.BranchID == viewModel.BranchID);
}
}
if (!hasConditon)
{
if (profile.IsAssistant() || profile.IsOfficer())
{
}
else if (profile.IsManager() || profile.IsViceManager())
{
var coaches = profile.GetServingCoachInSameStore(models);
items = items.Join(coaches, c => c.FitnessConsultant, h => h.CoachID, (c, h) => c);
}
else if (profile.IsCoach())
{
items = items.Where(c => c.FitnessConsultant == profile.UID);
}
}
if (hasConditon)
{
items = items.Where(queryExpr);
}
if (viewModel.Status.HasValue)
{
items = items.Where(c => c.Status == viewModel.Status);
}
if (viewModel.ContractType.HasValue)
{
items = items.Where(c => c.ContractType == (int)viewModel.ContractType);
}
if (viewModel.ContractDateFrom.HasValue)
{
items = items.Where(c => c.ContractDate >= viewModel.ContractDateFrom);
}
if (viewModel.ContractDateTo.HasValue)
{
items = items.Where(c => c.ContractDate < viewModel.ContractDateTo.Value.AddDays(1));
}
return(View("~/Views/CourseContract/Module/ContractQueryList.ascx", items));
}
public ActionResult ApplyAmendment(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
return(View());
}
// GET: CourseContract
public ActionResult ContractIndex(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
return(View());
}
public ActionResult LoadInstallmentPlan(CourseContractQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
return(View("~/Views/CourseContract/Module/InstallmentPlan.cshtml"));
}
public ActionResult RestoreCoachRevenueIndicator(MonthlyCoachRevenueIndicatorQueryViewModel viewModel, int[] exclusiveCoach = null)
{
ViewBag.ViewModel = viewModel;
IQueryable <MonthlyIndicator> indicatorItems = models.GetTable <MonthlyIndicator>();
if (viewModel.DateFrom.HasValue)
{
viewModel.DateFrom = viewModel.DateFrom.Value.FirstDayOfMonth();
indicatorItems.Where(i => i.StartDate >= viewModel.DateFrom);
}
if (viewModel.DateTo.HasValue)
{
viewModel.DateTo = viewModel.DateTo.Value.FirstDayOfMonth().AddMonths(1);
indicatorItems.Where(i => i.StartDate < viewModel.DateTo);
}
IQueryable <ServingCoach> coachItems = models.GetTable <ServingCoach>();
if (viewModel.CoachID.HasValue)
{
coachItems = coachItems.Where(c => c.CoachID == viewModel.CoachID);
}
if (exclusiveCoach != null && exclusiveCoach.Length > 0)
{
coachItems = coachItems
.Where(c => !exclusiveCoach.Contains(c.CoachID))
.Where(c => c.CoachWorkplace.Any());
}
var forCoaches = coachItems.ToArray();
foreach (MonthlyIndicator indicator in indicatorItems)
{
AchievementQueryViewModel queryModel = new AchievementQueryViewModel
{
AchievementDateFrom = indicator.StartDate,
BypassCondition = true,
};
IQueryable <V_Tuition> lessonItems = queryModel.InquireAchievement(models);
lessonItems = lessonItems.Where(l => l.SettlementID.HasValue);
IQueryable <V_Tuition> tuitionItems = lessonItems;
PaymentQueryViewModel paymentQuery = new PaymentQueryViewModel
{
PayoffDateFrom = indicator.StartDate,
PayoffDateTo = indicator.EndExclusiveDate.AddDays(-1),
BypassCondition = true,
};
IQueryable <Payment> paymentItems = paymentQuery.InquirePayment(models);
IQueryable <TuitionAchievement> achievementItems = paymentItems.GetPaymentAchievement(models);
CourseContractQueryViewModel contractQuery = new CourseContractQueryViewModel
{
ContractQueryMode = Naming.ContractServiceMode.ContractOnly,
Status = (int)Naming.CourseContractStatus.已生效,
EffectiveDateFrom = indicator.StartDate,
EffectiveDateTo = indicator.EndExclusiveDate,
};
IQueryable <CourseContract> contractItems = contractQuery.InquireContract(models);
void calcCoachAchievement(MonthlyCoachRevenueIndicator coachIndicator)
{
int lessonAchievement, tuitionAchievement;
var coachTuitionItems = tuitionItems.Where(t => t.AttendingCoach == coachIndicator.CoachID);
var coachAchievementItems = achievementItems.Where(t => t.CoachID == coachIndicator.CoachID);
var coachContractItems = contractItems.Where(c => c.FitnessConsultant == coachIndicator.CoachID);
lessonAchievement = coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForAchievement.Contains(t.ELStatus)).Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100) ?? 0);
tuitionAchievement = coachAchievementItems.Sum(a => a.ShareAmount) ?? 0;
coachIndicator.ActualCompleteLessonCount = coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
coachIndicator.ActualLessonAchievement = lessonAchievement;
coachIndicator.ActualRenewContractAchievement = coachContractItems.Where(c => c.Renewal == true).Count();
coachIndicator.ActualNewContractAchievement = coachContractItems.Count() - coachIndicator.ActualRenewContractAchievement;
coachIndicator.ActualCompleteTSCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.體驗課程).Count()
+ coachTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.體驗課程).Count();
coachIndicator.ActualCompletePICount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.自主訓練).Count()
+ coachTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.自主訓練).Count();
coachIndicator.ATCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動防護課程).Count();
coachIndicator.ATAchievement = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動防護課程).Sum(t => t.ListPrice) ?? 0;
coachIndicator.SRCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動恢復課程).Count();
coachIndicator.SRAchievement = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動恢復課程).Sum(t => t.ListPrice) ?? 0;
coachIndicator.SDCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.營養課程).Count();
coachIndicator.SDAchievement = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.營養課程).Sum(t => t.ListPrice) ?? 0;
models.SubmitChanges();
}
foreach (var coach in forCoaches)
{
var item = models.GetTable <MonthlyCoachRevenueIndicator>()
.Where(c => c.PeriodID == indicator.PeriodID && c.CoachID == coach.CoachID).FirstOrDefault();
if (item == null)
{
var attendingLessons = models.GetTable <LessonTime>()
.Where(l => l.AttendingCoach == coach.CoachID)
.Where(l => l.ClassTime >= indicator.StartDate)
.Where(l => l.ClassTime < indicator.EndExclusiveDate);
if (attendingLessons.Any())
{
item = new MonthlyCoachRevenueIndicator
{
PeriodID = indicator.PeriodID,
CoachID = coach.CoachID,
BranchID = coach.CoachWorkplace.First().BranchID,
LevelID = coach.LevelID,
};
models.GetTable <MonthlyCoachRevenueIndicator>().InsertOnSubmit(item);
item.AchievementGoal = 0;
item.CompleteLessonsGoal = 0;
item.AverageLessonPrice = indicator.CalculateAverageLessonPrice(models, coach.CoachID);
item.BRCount = 0;
item.LessonTuitionGoal = 0;
models.SubmitChanges();
calcCoachAchievement(item);
}
}
}
}
return(Json(new { result = true }));
}