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 ActionResult CommitCoachRevenueIndicator(MonthlyCoachRevenueIndicatorQueryViewModel viewModel)
{
ViewResult result = (ViewResult)LoadMonthlyIndicator(viewModel);
MonthlyIndicator indicator = result.Model as MonthlyIndicator;
if (indicator == null)
{
return(result);
}
var coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == viewModel.CoachID).FirstOrDefault();
if (coach == null)
{
return(View("~/Views/ConsoleHome/Shared/JsAlert.cshtml", model: "請選擇教練!!"));
}
if (!viewModel.BranchID.HasValue)
{
return(View("~/Views/ConsoleHome/Shared/JsAlert.cshtml", model: "請選擇分店!!"));
}
if (!viewModel.AchievementGoal.HasValue)
{
ModelState.AddModelError("AchievementGoal", "請輸收款業績");
}
if (viewModel.DataOperation != Naming.DataOperationMode.Create)
{
if (!viewModel.AverageLessonPrice.HasValue)
{
ModelState.AddModelError("AverageLessonPrice", "請輸平均單價");
}
}
if (!viewModel.CompleteLessonsGoal.HasValue)
{
ModelState.AddModelError("CompleteLessonsGoal", "請輸入課數");
}
if (!viewModel.BRCount.HasValue)
{
ModelState.AddModelError("BRCount", "請輸BR堂數");
}
if (!ModelState.IsValid)
{
ViewBag.ModelState = ModelState;
return(View(ConsoleHomeController.InputErrorView));
}
var item = models.GetTable <MonthlyCoachRevenueIndicator>()
.Where(c => c.PeriodID == viewModel.PeriodID && c.CoachID == viewModel.CoachID).FirstOrDefault();
if (item == null)
{
item = new MonthlyCoachRevenueIndicator
{
PeriodID = indicator.PeriodID,
CoachID = viewModel.CoachID.Value,
BranchID = viewModel.BranchID,
LevelID = coach.LevelID,
};
models.GetTable <MonthlyCoachRevenueIndicator>().InsertOnSubmit(item);
}
item.AchievementGoal = viewModel.AchievementGoal;
item.CompleteLessonsGoal = viewModel.CompleteLessonsGoal;
if (viewModel.DataOperation == Naming.DataOperationMode.Create)
{
item.AverageLessonPrice = indicator.CalculateAverageLessonPrice(models, viewModel.CoachID);;
}
else
{
item.AverageLessonPrice = viewModel.AverageLessonPrice;
}
item.BRCount = viewModel.BRCount;
item.LessonTuitionGoal = item.AverageLessonPrice * item.CompleteLessonsGoal;
models.SubmitChanges();
indicator.UpdateMonthlyAchievementGoal(models);
return(Json(new { result = true }, JsonRequestBehavior.AllowGet));
}
public static MonthlyIndicator InitializeMonthlyIndicator <TEntity>(this ModelSource <TEntity> models, int year, int month, bool?forcedPrepare = null)
where TEntity : class, new()
{
DateTime startDate = new DateTime(year, month, 1);
DateTime lastPeriod = startDate.AddMonths(-1);
var sampleItem = models.GetTable <MonthlyIndicator>()
.Where(i => i.StartDate == lastPeriod).FirstOrDefault();
if (sampleItem == null)
{
sampleItem = models.GetTable <MonthlyIndicator>()
.OrderBy(m => m.PeriodID).FirstOrDefault();
}
if (sampleItem == null)
{
return(null);
}
MonthlyIndicator item = models.GetTable <MonthlyIndicator>().Where(i => i.Year == year && i.Month == month).FirstOrDefault();
if (item == null)
{
item = new MonthlyIndicator
{
Year = year,
Month = month,
StartDate = startDate,
EndExclusiveDate = startDate.AddMonths(1),
};
models.GetTable <MonthlyIndicator>().InsertOnSubmit(item);
}
foreach (var r in sampleItem.MonthlyRevenueIndicator)
{
MonthlyRevenueIndicator newItem = item.MonthlyRevenueIndicator.Where(i => i.GradeID == r.GradeID).FirstOrDefault();
if (newItem != null)
{
if (newItem.MonthlyRevenueGoal != null)
{
continue;
}
}
else
{
newItem = new MonthlyRevenueIndicator
{
MonthlyIndicator = item,
RevenueGoal = r.RevenueGoal,
GradeID = r.GradeID,
};
}
if (r.MonthlyRevenueGoal != null)
{
newItem.MonthlyRevenueGoal = new MonthlyRevenueGoal
{
CustomIndicatorPercentage = r.MonthlyRevenueGoal.CustomIndicatorPercentage,
CustomRevenueGoal = r.MonthlyRevenueGoal.CustomRevenueGoal,
NewContractCount = r.MonthlyRevenueGoal.NewContractCount,
RenewContractCount = r.MonthlyRevenueGoal.RenewContractCount,
NewContractSubtotal = r.MonthlyRevenueGoal.NewContractSubtotal,
RenewContractSubtotal = r.MonthlyRevenueGoal.RenewContractSubtotal,
InstallmentCount = r.MonthlyRevenueGoal.InstallmentCount,
InstallmentSubtotal = r.MonthlyRevenueGoal.InstallmentSubtotal,
};
}
}
foreach (var b in sampleItem.MonthlyBranchIndicator)
{
MonthlyBranchIndicator newBranchItem = item.MonthlyBranchIndicator.Where(i => i.BranchID == b.BranchID).FirstOrDefault();
if (newBranchItem == null)
{
newBranchItem = new MonthlyBranchIndicator
{
MonthlyIndicator = item,
BranchID = b.BranchID,
};
}
foreach (var r in b.MonthlyBranchRevenueIndicator)
{
MonthlyBranchRevenueIndicator newItem = newBranchItem.MonthlyBranchRevenueIndicator.Where(i => i.GradeID == r.GradeID).FirstOrDefault();
if (newItem != null)
{
if (newItem.MonthlyBranchRevenueGoal != null)
{
continue;
}
}
else
{
newItem = new MonthlyBranchRevenueIndicator
{
MonthlyBranchIndicator = newBranchItem,
GradeID = r.GradeID,
RevenueGoal = r.RevenueGoal,
};
}
var branchGoal = r.MonthlyBranchRevenueGoal;
if (branchGoal != null)
{
newItem.MonthlyBranchRevenueGoal = new MonthlyBranchRevenueGoal
{
LessonTuitionGoal = branchGoal.LessonTuitionGoal,
CompleteLessonsGoal = branchGoal.CompleteLessonsGoal,
AchievementGoal = branchGoal.AchievementGoal,
CustomRevenueGoal = branchGoal.CustomRevenueGoal,
CustomIndicatorPercentage = branchGoal.CustomIndicatorPercentage,
NewContractCount = branchGoal.NewContractCount,
RenewContractCount = branchGoal.RenewContractCount,
NewContractSubtotal = branchGoal.NewContractSubtotal,
RenewContractSubtotal = branchGoal.RenewContractSubtotal,
InstallmentCount = branchGoal.InstallmentCount,
InstallmentSubtotal = branchGoal.InstallmentSubtotal,
};
}
}
}
if (forcedPrepare == true)
{
foreach (var c in sampleItem.MonthlyCoachRevenueIndicator)
{
MonthlyCoachRevenueIndicator newItem = item.MonthlyCoachRevenueIndicator.Where(i => i.CoachID == c.CoachID).FirstOrDefault();
if (newItem != null)
{
continue;
}
int actualCount = c.ActualCompleteLessonCount ?? 0;
newItem = new MonthlyCoachRevenueIndicator
{
MonthlyIndicator = item,
CoachID = c.CoachID,
BranchID = c.BranchID,
AchievementGoal = c.AchievementGoal,
CompleteLessonsGoal = c.CompleteLessonsGoal,
LessonTuitionGoal = c.LessonTuitionGoal,
BRCount = c.BRCount,
LevelID = c.ServingCoach.LevelID,
//AverageLessonPrice = actualCount > 0
// ? (c.ActualLessonAchievement + c.ActualCompleteLessonCount - 1) / actualCount
// : 0,
AverageLessonPrice = sampleItem.CalculateAverageLessonPrice(models, c.CoachID),
};
newItem.LessonTuitionGoal = newItem.CompleteLessonsGoal * newItem.AverageLessonPrice;
}
}
models.SubmitChanges();
return(item);
}
