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 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 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 }));
}
public static void UpdateMonthlyAchievement <TEntity>(this MonthlyIndicator item, ModelSource <TEntity> models, bool?forcedUpdate = null, bool?calcAverage = null)
where TEntity : class, new()
{
AchievementQueryViewModel queryModel = new AchievementQueryViewModel
{
AchievementDateFrom = item.StartDate,
BypassCondition = true,
};
IQueryable <Payment> voidPayment = UpdateVoidShare(models, item.StartDate, item.EndExclusiveDate);
IQueryable <V_Tuition> lessonItems = queryModel.InquireAchievement(models);
IQueryable <LessonTime> STItems = models.GetTable <LessonTime>()
.Where(c => c.ClassTime >= item.StartDate)
.Where(c => c.ClassTime < item.StartDate.AddMonths(1))
.Where(t => t.TrainingBySelf == 2);
if (forcedUpdate == true)
{
lessonItems = lessonItems.Where(l => l.SettlementID.HasValue);
}
else if (item.StartDate == DateTime.Today.FirstDayOfMonth())
{
lessonItems = lessonItems.Where(l => l.ClassTime < DateTime.Today);
}
else
{
lessonItems = lessonItems.Where(l => l.SettlementID.HasValue);
}
IQueryable <V_Tuition> tuitionItems = lessonItems;
PaymentQueryViewModel paymentQuery = new PaymentQueryViewModel
{
PayoffDateFrom = item.StartDate,
PayoffDateTo = item.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 = item.StartDate,
EffectiveDateTo = item.EndExclusiveDate,
};
IQueryable <CourseContract> contractItems = contractQuery.InquireContract(models);
IQueryable <CourseContract> installmentItems = contractItems.Where(c => c.Installment.HasValue);
IQueryable <CourseContract> nonInstallmentItems = contractItems.Where(c => !c.Installment.HasValue);
int lessonAchievement, tuitionAchievement;
void calcHeadquarterAchievement()
{
var voidTuition = voidPayment
.Join(models.GetTable <TuitionAchievement>(), p => p.PaymentID, t => t.InstallmentID, (p, t) => t);
lessonAchievement = tuitionItems.Where(t => SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (tuitionItems.Where(t => SessionScopeForAchievement.Contains(t.ELStatus)).Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100) ?? 0);
tuitionAchievement = achievementItems.Sum(a => a.ShareAmount) ?? 0;
IQueryable <TuitionAchievement> newContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => !t.Renewal.HasValue || t.Renewal != true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models);
IQueryable <TuitionAchievement> renewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => t.Renewal == true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models);
IQueryable <TuitionAchievement> installmentContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => t.Installment.HasValue),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models);
var revenueItem = item.MonthlyRevenueIndicator.Where(r => r.MonthlyRevenueGoal != null)
.Select(r => r.MonthlyRevenueGoal)
.FirstOrDefault();
if (revenueItem != null)
{
revenueItem.ActualCompleteLessonCount = tuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ tuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
revenueItem.ActualLessonAchievement = lessonAchievement;
revenueItem.ActualSharedAchievement = tuitionAchievement;
revenueItem.RenewContractCount = nonInstallmentItems.Where(c => c.Renewal == true).Count();
revenueItem.NewContractCount = nonInstallmentItems.Count() - revenueItem.RenewContractCount;
revenueItem.RenewContractSubtotal = nonInstallmentItems.Where(c => c.Renewal == true).Sum(c => c.TotalCost) ?? 0;
revenueItem.NewContractSubtotal = nonInstallmentItems.Sum(c => c.TotalCost) - revenueItem.RenewContractSubtotal;
revenueItem.InstallmentCount = installmentItems.Count();
revenueItem.InstallmentSubtotal = installmentItems.Sum(c => c.TotalCost) ?? 0;
revenueItem.ActualCompleteTSCount = tuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.體驗課程).Count()
+ tuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.體驗課程).Count();
revenueItem.ActualCompletePICount = tuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.自主訓練).Count()
+ tuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.自主訓練).Count();
revenueItem.NewContractAchievement = newContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.RenewContractAchievement = renewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.InstallmentAchievement = installmentContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.VoidShare = voidTuition.Sum(t => t.VoidShare) ?? 0;
models.SubmitChanges();
}
}
void calcBranchAchievement()
{
foreach (var branchIndicator in item.MonthlyBranchIndicator)
{
var voidTuition = voidPayment
.Join(models.GetTable <PaymentTransaction>().Where(t => t.BranchID == branchIndicator.BranchID), p => p.PaymentID, t => t.PaymentID, (p, t) => p)
.Join(models.GetTable <TuitionAchievement>(), p => p.PaymentID, t => t.InstallmentID, (p, t) => t);
var branchTuitionItems = tuitionItems.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
var branchAchievementItems = achievementItems.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
var branchContractItems = contractItems.Where(c => c.CourseContractExtension.BranchID == branchIndicator.BranchID);
var branchInstallmentItems = installmentItems.Where(c => c.CourseContractExtension.BranchID == branchIndicator.BranchID);
var branchNonInstallmentItems = nonInstallmentItems.Where(c => c.CourseContractExtension.BranchID == branchIndicator.BranchID);
IQueryable <TuitionAchievement> branchNewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => !t.Renewal.HasValue || t.Renewal != true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
IQueryable <TuitionAchievement> branchRenewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => t.Renewal == true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
IQueryable <TuitionAchievement> branchInstallmentContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => t.Installment.HasValue),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
var revenueItem = branchIndicator.MonthlyBranchRevenueIndicator
.Where(v => v.MonthlyBranchRevenueGoal != null)
.FirstOrDefault()?.MonthlyBranchRevenueGoal;
if (revenueItem != null)
{
lessonAchievement = branchTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (branchTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.ELStatus)).Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100) ?? 0);
tuitionAchievement = branchAchievementItems.Sum(a => a.ShareAmount) ?? 0;
revenueItem.ActualCompleteLessonCount = branchTuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ branchTuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
revenueItem.ActualLessonAchievement = lessonAchievement;
revenueItem.ActualSharedAchievement = tuitionAchievement;
revenueItem.RenewContractCount = branchNonInstallmentItems.Where(c => c.Renewal == true).Count();
revenueItem.NewContractCount = branchNonInstallmentItems.Count() - revenueItem.RenewContractCount;
revenueItem.RenewContractSubtotal = branchNonInstallmentItems.Where(c => c.Renewal == true).Sum(c => c.TotalCost) ?? 0;
revenueItem.NewContractSubtotal = branchNonInstallmentItems.Sum(c => c.TotalCost) - revenueItem.RenewContractSubtotal;
revenueItem.InstallmentCount = branchInstallmentItems.Count();
revenueItem.InstallmentSubtotal = branchInstallmentItems.Sum(c => c.TotalCost) ?? 0;
revenueItem.ActualCompleteTSCount = branchTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.體驗課程).Count()
+ branchTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.體驗課程).Count();
revenueItem.ActualCompletePICount = branchTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.自主訓練).Count()
+ branchTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.自主訓練).Count();
revenueItem.NewContractAchievement = branchNewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.RenewContractAchievement = branchRenewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.InstallmentAchievement = branchInstallmentContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.VoidShare = voidTuition.Sum(t => t.VoidShare) ?? 0;
models.SubmitChanges();
}
}
}
void calcCoachAchievement()
{
foreach (var coachIndicator in item.MonthlyCoachRevenueIndicator)
{
var voidTuition = voidPayment
.Join(models.GetTable <TuitionAchievement>()
.Where(t => t.CoachID == coachIndicator.CoachID),
p => p.PaymentID, t => t.InstallmentID, (p, t) => t);
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);
IQueryable <CourseContract> coachInstallmentItems = coachContractItems.Where(c => c.Installment.HasValue);
IQueryable <CourseContract> coachNonInstallmentItems = coachContractItems.Where(c => !c.Installment.HasValue);
var coachSTItems = STItems.Where(t => t.AttendingCoach == coachIndicator.CoachID);
IQueryable <TuitionAchievement> coachNewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => !t.Renewal.HasValue || t.Renewal != true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachID == coachIndicator.CoachID);
IQueryable <TuitionAchievement> coachRenewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => t.Renewal == true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachID == coachIndicator.CoachID);
IQueryable <TuitionAchievement> coachInstallmentContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => t.Installment.HasValue),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachID == coachIndicator.CoachID);
lessonAchievement = coachTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (coachTuitionItems.Where(t => 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 => SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ coachTuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
coachIndicator.ActualLessonAchievement = lessonAchievement;
coachIndicator.ActualRenewContractAchievement = coachRenewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
coachIndicator.ActualNewContractAchievement = coachNewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
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.InstallmentAchievement = coachInstallmentContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
coachIndicator.RenewContractCount = coachNonInstallmentItems.Where(t => t.Renewal == true).Count();
coachIndicator.NewContractCount = coachNonInstallmentItems.Count() - coachIndicator.RenewContractCount;
coachIndicator.RenewContractSubtotal = coachNonInstallmentItems.Where(c => c.Renewal == true).Sum(c => c.TotalCost) ?? 0;
coachIndicator.NewContractSubtotal = coachNonInstallmentItems.Sum(c => c.TotalCost) - coachIndicator.RenewContractSubtotal;
coachIndicator.InstallmentCount = coachInstallmentItems.Count();
coachIndicator.InstallmentSubtotal = coachInstallmentItems.Sum(c => c.TotalCost) ?? 0;
if (calcAverage == true)
{
coachIndicator.AverageLessonPrice = item.CalculateAverageLessonPrice(models, coachIndicator.CoachID);
coachIndicator.LessonTuitionGoal = coachIndicator.CompleteLessonsGoal * coachIndicator.AverageLessonPrice;
}
coachIndicator.STCount = coachSTItems.Count();
coachIndicator.VoidShare = voidTuition.Sum(t => t.VoidShare) ?? 0;
models.SubmitChanges();
}
}
calcHeadquarterAchievement();
calcBranchAchievement();
calcCoachAchievement();
}
public ActionResult CreateContractQueryXlsx(CourseContractQueryViewModel viewModel)
{
IQueryable <CourseContract> items = viewModel.InquireContract(this, out string alertMessage);
if (items.Count() == 0)
{
Response.AppendCookie(new HttpCookie("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() ? "是" : "",
});
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={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())
{
xls.SaveAs(Response.OutputStream);
}
}
return(new EmptyResult());
}
public ActionResult CreateContractServiceQueryXlsx(CourseContractQueryViewModel viewModel)
{
IQueryable <CourseContract> items = viewModel.InquireContract(this, out string alertMessage);
if (items.Count() == 0)
{
Response.AppendCookie(new HttpCookie("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.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={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())
{
xls.SaveAs(Response.OutputStream);
}
}
return(new EmptyResult());
}
