private object LoadValue(HttpApiContext context, ModelSource source, string key, Type type)
{
object v = null;
switch (source)
{
case ModelSource.Header:
return context.Request.Headers[key];
case ModelSource.FormOrQuery:
v = context.Request.Form[key];
if (v == null)
{
v = context.Request.QueryString[key];
}
return v;
case ModelSource.FormJson:
return JsonConvert.DeserializeObject(context.Request.TextContent, type);
case ModelSource.QueryString:
return context.Request.QueryString[key];
case ModelSource.QueryStringJson:
v = context.Request.QueryString[key];
if (v != null)
{
v = JsonConvert.DeserializeObject((string)v, type);
}
return v;
case ModelSource.FormModelJson:
break;
default:
break;
}
return null;
}
public static IQueryable <CourseContract> RemainedLessonCount <TEntity>(this UserProfile profile, ModelSource <TEntity> models, out int remainedCount, out IQueryable <RegisterLesson> remainedItems, bool onlyAttended = false)
where TEntity : class, new()
{
var items = models.GetTable <RegisterLesson>()
.Where(l => l.LessonPriceType.Status != (int)Naming.DocumentLevelDefinition.自主訓練)
.Where(r => r.UID == profile.UID)
.OrderByDescending(r => r.RegisterID);
var currentLessons = items.Where(i => i.Attended != (int)Naming.LessonStatus.課程結束);
remainedItems = currentLessons;
var contractItems = currentLessons.Join(models.GetTable <RegisterLessonContract>(), r => r.RegisterID, c => c.RegisterID, (r, c) => c)
.Join(models.GetTable <CourseContract>(), c => c.ContractID, n => n.ContractID, (c, n) => n);
var familyLessons = contractItems.Where(c => c.CourseContractType.ContractCode == "CFA")
.Join(models.GetTable <RegisterLessonContract>(), c => c.ContractID, r => r.ContractID, (c, r) => r)
.Join(models.GetTable <RegisterLesson>(), c => c.RegisterID, r => r.RegisterID, (c, r) => r);
int totalLessons = currentLessons.Sum(c => (int?)c.Lessons) ?? 0;
int attendedLessons = currentLessons.Sum(c => (int?)c.AttendedLessons) ?? 0;
int attendance;
if (onlyAttended)
{
attendance = currentLessons.Sum(c => (int?)c.GroupingLesson.LessonTime.Count(l => l.LessonAttendance != null)) ?? 0;
}
else
{
attendance = currentLessons.Sum(c => (int?)c.GroupingLesson.LessonTime.Count()) ?? 0;
}
if (familyLessons.Count() > 0)
{
var exceptFamily = currentLessons.Where(r => r.RegisterLessonContract == null || r.RegisterLessonContract.CourseContract.CourseContractType.ContractCode != "CFA");
if (onlyAttended)
{
remainedCount = totalLessons
- (exceptFamily.Sum(c => c.AttendedLessons) ?? 0)
- (exceptFamily.Where(c => c.RegisterGroupID.HasValue).Sum(c => (int?)c.GroupingLesson.LessonTime.Count(l => l.LessonAttendance != null)) ?? 0)
- (familyLessons.Sum(c => c.AttendedLessons) ?? 0)
- (familyLessons.Where(c => c.RegisterGroupID.HasValue).Sum(c => (int?)c.GroupingLesson.LessonTime.Count(l => l.LessonAttendance != null)) ?? 0);
}
else
{
remainedCount = totalLessons
- (exceptFamily.Sum(c => c.AttendedLessons) ?? 0)
- (exceptFamily.Where(c => c.RegisterGroupID.HasValue).Sum(c => (int?)c.GroupingLesson.LessonTime.Count()) ?? 0)
- (familyLessons.Sum(c => c.AttendedLessons) ?? 0)
- (familyLessons.Where(c => c.RegisterGroupID.HasValue).Sum(c => (int?)c.GroupingLesson.LessonTime.Count()) ?? 0);
}
}
else
{
remainedCount = totalLessons - attendedLessons - attendance;
}
return(contractItems);
}
public static IQueryable <LessonTime> InquireLesson <TEntity>(this LessonOverviewQueryViewModel viewModel, ModelSource <TEntity> models, bool exclusiveCoachOrBranch = false)
where TEntity : class, new()
{
IQueryable <LessonTime> items = models.GetTable <LessonTime>();
if (viewModel.Year.HasValue && viewModel.Month.HasValue)
{
viewModel.DateFrom = new DateTime(viewModel.Year.Value, viewModel.Month.Value, 1);
viewModel.DateTo = viewModel.DateFrom.Value.AddMonths(1);
}
if (viewModel.LessonID.HasValue)
{
items = items.Where(t => t.LessonID == viewModel.LessonID);
}
if (viewModel.CoachID.HasValue)
{
items = items.Where(c => c.AttendingCoach == viewModel.CoachID);
}
if (exclusiveCoachOrBranch == false || !viewModel.CoachID.HasValue)
{
if (viewModel.BranchID.HasValue)
{
if (viewModel.ByManager == true)
{
var allCoach = models.GetTable <CoachWorkplace>().Where(w => w.BranchID == viewModel.BranchID)
.Where(w => !models.GetTable <CoachWorkplace>().Where(c => c.CoachID == w.CoachID)
.Any(c => c.BranchID != viewModel.BranchID))
.Select(w => w.CoachID);
items = items.Where(c => allCoach.Any(w => w == c.AttendingCoach));
}
else
{
items = items.Where(c => c.BranchID == viewModel.BranchID);
}
}
}
if (viewModel.DateFrom.HasValue)
{
items = items.Where(c => c.ClassTime >= viewModel.DateFrom);
}
if (viewModel.DateTo.HasValue)
{
items = items.Where(c => c.ClassTime < viewModel.DateTo);
}
if (viewModel.CoachAttended == true)
{
items = items.Where(l => l.LessonAttendance != null);
}
else if (viewModel.CoachAttended == false)
{
items = items.Where(t => t.LessonAttendance == null);
}
if (viewModel.LearnerCommitted == true)
{
items = items.Where(t => t.LessonPlan.CommitAttendance.HasValue);
}
else if (viewModel.LearnerCommitted == false)
{
items = items.Where(t => !t.LessonPlan.CommitAttendance.HasValue);
}
if (viewModel.LessonType.HasValue)
{
items = items.ByLessonQueryType(viewModel.LessonType);
}
return(items);
}
/// <summary>
/// Save any modifications to the EditableObject. If the mesh is a scene mesh or imported mesh, it
/// will be saved to a new asset. If the mesh was originally an asset mesh, the asset is overwritten.
/// </summary>
/// <param name="mesh">mesh to save</param>
/// <param name="meshFilter">will update the mesh filter with the new mesh if not null</param>
/// <param name="skinnedMeshRenderer">will update the skinned mesh renderer with the new mesh if not null</param>
/// <returns>return true if save was successful, false if user-canceled or otherwise failed.</returns>
internal static bool SaveMeshAsset(Mesh mesh, MeshFilter meshFilter = null, SkinnedMeshRenderer skinnedMeshRenderer = null, int overridenDialogResult = -1, string overridenPath = "")
{
if (mesh == null)
{
return(false);
}
string save_path = !string.IsNullOrEmpty(overridenPath) ? overridenPath : DO_NOT_SAVE;
ModelSource source = GetMeshGUID(mesh);
switch (source)
{
case ModelSource.Asset:
int saveChanges = overridenDialogResult != -1 ? overridenDialogResult :
EditorUtility.DisplayDialogComplex(
"Save Changes",
"Save changes to edited mesh?",
"Save", // DIALOG_OK
"Cancel", // DIALOG_CANCEL
"Save As"); // DIALOG_ALT
if (saveChanges == DIALOG_OK)
{
save_path = AssetDatabase.GetAssetPath(mesh);
}
else if (saveChanges == DIALOG_ALT)
{
save_path = EditorUtility.SaveFilePanelInProject("Save Mesh As", mesh.name + ".asset", "asset", "Save edited mesh to");
}
else
{
return(false);
}
break;
case ModelSource.Imported:
case ModelSource.Scene:
default:
save_path = EditorUtility.SaveFilePanelInProject("Save Mesh As", mesh.name + ".asset", "asset", "Save edited mesh to");
break;
}
if (!save_path.Equals(DO_NOT_SAVE) && !string.IsNullOrEmpty(save_path))
{
Object existing = AssetDatabase.LoadMainAssetAtPath(save_path);
if (existing != null && existing is Mesh)
{
//if the mesh that we want to create is the same than the mesh found at this path, do nothing
if (existing.GetInstanceID() == mesh.GetInstanceID())
{
//nothing to do here
}
// save over an existing mesh asset
else
{
PolyMeshUtility.Copy((Mesh)existing, mesh);
Object.DestroyImmediate(mesh, true);
}
}
else
{
AssetDatabase.CreateAsset(mesh, save_path);
}
AssetDatabase.Refresh();
if (meshFilter != null)
{
meshFilter.sharedMesh = (Mesh)AssetDatabase.LoadAssetAtPath(save_path, typeof(Mesh));
}
else if (skinnedMeshRenderer != null)
{
skinnedMeshRenderer.sharedMesh = (Mesh)AssetDatabase.LoadAssetAtPath(save_path, typeof(Mesh));
}
return(true);
}
// Save was canceled
return(false);
}
internal ViewModelVisitor(ModelSource model)
{
modelSource = model;
BindingMyText = "Test!";
}
public LineMessageHandler(Event lineEvent, ModelSource <UserProfile> models)
{
this.lineEvent = lineEvent;
this.models = models;
}
public IObservable <IOperationState <object> > Write(ModelIdentifierBase id, UpdateContainer update, ModelSource target, CancellationToken ct = default)
{
var replayStates = new ReplaySubject <IOperationState <object> >();
WriteImplementation(id, update, target, replayStates, ct);
return(replayStates);
}
public static IQueryable <CoachMonthlySalary> InquireMonthlySalary <TEntity>(this AchievementQueryViewModel viewModel, ModelSource <TEntity> models)
where TEntity : class, new()
{
IQueryable <CoachMonthlySalary> items = models.GetTable <Settlement>()
.Where(s => s.SettlementDate >= viewModel.AchievementDateFrom.Value.AddMonths(1) &&
s.SettlementDate < viewModel.AchievementDateTo.Value.AddMonths(1))
.Join(models.GetTable <CoachMonthlySalary>(),
s => s.SettlementID, c => c.SettlementID, (s, c) => c);
return(items);
}
public static IQueryable <V_Tuition> InquireAchievementTuition <TEntity>(this AchievementQueryViewModel viewModel, ModelSource <TEntity> models)
where TEntity : class, new()
{
if (!viewModel.AchievementDateFrom.HasValue)
{
if (!String.IsNullOrEmpty(viewModel.AchievementYearMonthFrom))
{
viewModel.AchievementDateFrom = DateTime.ParseExact(viewModel.AchievementYearMonthFrom, "yyyy/MM", System.Globalization.CultureInfo.CurrentCulture);
}
else
{
viewModel.AchievementDateFrom = DateTime.Today.FirstDayOfMonth();
}
}
if (!viewModel.AchievementDateTo.HasValue)
{
if (!String.IsNullOrEmpty(viewModel.AchievementYearMonthTo))
{
viewModel.AchievementDateTo = DateTime.ParseExact(viewModel.AchievementYearMonthTo, "yyyy/MM", System.Globalization.CultureInfo.CurrentCulture).AddMonths(1);
}
else
{
viewModel.AchievementDateTo = viewModel.AchievementDateFrom.Value.AddMonths(1);
}
}
IQueryable <V_Tuition> items = models.GetTable <V_Tuition>();
if (viewModel.CoachID.HasValue)
{
items = items.Where(c => c.AttendingCoach == viewModel.CoachID);
}
if (viewModel.ByCoachID != null && viewModel.ByCoachID.Length > 0)
{
items = items.Where(c => viewModel.ByCoachID.Contains(c.AttendingCoach));
}
if (viewModel.BranchID.HasValue)
{
items = items.Where(c => c.BranchID == viewModel.BranchID);
}
if (viewModel.AchievementDateFrom.HasValue)
{
items = items.Where(c => c.ClassTime >= viewModel.AchievementDateFrom);
}
if (viewModel.AchievementDateTo.HasValue)
{
items = items.Where(c => c.ClassTime < viewModel.AchievementDateTo);
}
return(items);
}
public static DataTable BuildContractPaymentReport <TEntity>(this IEnumerable <PaymentMonthlyReportItem> details, ModelSource <TEntity> models)
where TEntity : class, new()
{
List <DataItem> results = new List <DataItem>();
DataItem subtotal = new DataItem
{
A = "總計"
};
foreach (var branch in models.GetTable <BranchStore>())
{
var items = details.Where(d => d.分店 == branch.BranchName);
var i = new DataItem
{
A = branch.BranchName,
B = (items.Where(d => d.摘要.StartsWith("課程顧問費用")).Sum(d => d.收款金額_含稅) ?? 0)
- (items.Where(d => d.摘要.Contains("課程顧問費用")).Where(d => d.摘要.Contains("作廢") || d.摘要.Contains("折讓")).Sum(d => d.退款金額_含稅) ?? 0),
C = items.Where(d => d.摘要.Contains("終止退款")).Sum(d => d.退款金額_含稅) ?? 0,
D = items.Where(d => d.摘要.Contains("終止轉收")).Sum(d => d.退款金額_含稅) ?? 0,
E = items.Where(d => d.摘要.StartsWith("課程顧問費用")).Sum(d => d.貸方金額) ?? 0,
F = items.Where(d => d.摘要.Contains("終止退款")).Sum(d => d.借方金額) ?? 0,
G = items.Where(d => d.摘要.Contains("終止轉收")).Sum(d => d.借方金額) ?? 0,
};
subtotal.B += i.B;
subtotal.C += i.C;
subtotal.D += i.D;
subtotal.E += i.E;
subtotal.F += i.F;
subtotal.G += i.G;
results.Add(i);
}
results.Add(subtotal);
DataTable table = results.ToDataTable();
table.Columns[0].ColumnName = "分店/全部";
table.Columns[1].ColumnName = "新增(含稅)";
table.Columns[2].ColumnName = "終止退款(含稅)";
table.Columns[3].ColumnName = "終止轉收(含稅)";
table.Columns[4].ColumnName = "新增(未稅)";
table.Columns[5].ColumnName = "終止退款(未稅)";
table.Columns[6].ColumnName = "終止轉收(未稅)";
return(table);
}
public static void ExecuteLessonPerformanceSettlement <TEntity>(this ModelSource <TEntity> models, DateTime startDate, DateTime endExclusiveDate)
where TEntity : class, new()
{
var settlement = models.GetTable <Settlement>().Where(s => startDate <= s.SettlementDate && endExclusiveDate > s.SettlementDate).FirstOrDefault();
if (settlement == null)
{
return;
}
LessonTimeAchievementHelper <TEntity> helper = new LessonTimeAchievementHelper <TEntity>(models)
{
LessonItems = models.GetTable <V_Tuition>().Where(l => l.ClassTime >= settlement.StartDate && l.ClassTime < settlement.EndExclusiveDate)
};
foreach (var item in helper.SettlementFullAchievement)
{
models.ExecuteCommand("update LessonTimeSettlement set SettlementStatus = {0},SettlementID = {1} where LessonID={2}", (int)Naming.LessonSettlementStatus.FullAchievement, settlement.SettlementID, item.LessonID);
}
foreach (var item in helper.SettlementHalfAchievement)
{
models.ExecuteCommand("update LessonTimeSettlement set SettlementStatus = {0},SettlementID = {1} where LessonID={2}", (int)Naming.LessonSettlementStatus.HalfAchievement, settlement.SettlementID, item.LessonID);
}
foreach (var item in helper.SettlementVainAchievement)
{
models.ExecuteCommand("update LessonTimeSettlement set SettlementID = {0} where LessonID={1}", settlement.SettlementID, item.LessonID);
}
var coachItems = models.PromptEffectiveCoach();
var salaryTable = models.GetTable <CoachMonthlySalary>();
var countableItems = helper.PerformanceCountableLesson;
var paymentItems = models.GetTable <Payment>().Where(p => p.PayoffDate >= settlement.StartDate && p.PayoffDate < settlement.EndExclusiveDate);
//.FilterByEffective();
IQueryable <TuitionAchievement> achievementItems = paymentItems.GetPaymentAchievement(models);
foreach (var coach in coachItems)
{
var lessonItem = countableItems.Where(l => l.AttendingCoach == coach.CoachID).FirstOrDefault();
var salary = salaryTable.Where(s => s.CoachID == coach.CoachID && s.SettlementID == settlement.SettlementID).FirstOrDefault();
if (salary == null)
{
salary = new CoachMonthlySalary
{
CoachID = coach.CoachID,
SettlementID = settlement.SettlementID,
};
salaryTable.InsertOnSubmit(salary);
}
if (coach.CoachWorkplace.Count == 1)
{
salary.WorkPlace = coach.CoachWorkplace.First().BranchID;
}
if (lessonItem != null)
{
salary.LevelID = lessonItem.ProfessionalLevelID;
salary.GradeIndex = lessonItem.MarkedGradeIndex ?? 0;
}
else
{
salary.LevelID = coach.LevelID ?? 0;
salary.GradeIndex = coach.ProfessionalLevel?.GradeIndex ?? 0;
}
var performanceItems = achievementItems.Where(t => t.CoachID == coach.CoachID);
salary.PerformanceAchievement = performanceItems.Sum(t => t.ShareAmount) ?? 0;
models.SubmitChanges();
void calcCoachAchievement()
{
var attendingLessons = countableItems.Where(l => l.AttendingCoach == coach.CoachID);
foreach (var g in attendingLessons.GroupBy(l => l.BranchID))
{
var branchBonus = salary.CoachBranchMonthlyBonus.Where(b => b.BranchID == g.Key).FirstOrDefault();
if (branchBonus == null)
{
branchBonus = new CoachBranchMonthlyBonus
{
CoachMonthlySalary = salary,
BranchID = g.Key.Value
};
salary.CoachBranchMonthlyBonus.Add(branchBonus);
}
LessonTimeAchievementHelper <TEntity> branchHelper = new LessonTimeAchievementHelper <TEntity>(models)
{
LessonItems = attendingLessons.Where(l => l.BranchID == g.Key)
};
branchBonus.AchievementAttendanceCount = branchHelper.LessonItems.Count()
- branchHelper.SettlementPILesson.Count() / 2m;
branchBonus.Tuition = branchHelper.LessonItems.CalcTuition(models);
branchBonus.AttendanceBonus = branchHelper.LessonItems.CalcTuitionShare(models);
models.SubmitChanges();
}
var attendanceCount = salary.CoachBranchMonthlyBonus.Sum(b => b.AchievementAttendanceCount);
decimal shareRatio = 3.5m;
for (int i = 0; i < PerformanceAchievementIndex.Length; i++)
{
if (salary.PerformanceAchievement >= PerformanceAchievementIndex[i])
{
shareRatio += ShareRatioIncrementForPerformance[i];
break;
}
}
for (int i = 0; i < AttendingLessonIndex.Length; i++)
{
if (attendanceCount >= AttendingLessonIndex[i])
{
shareRatio += ShareRatioIncrementForAttendance[i];
break;
}
}
salary.AchievementShareRatio = shareRatio;
models.SubmitChanges();
};
BranchStore branch;
if (coach.UserProfile.IsOfficer())
{
foreach (var g in countableItems.GroupBy(l => l.BranchID))
{
var branchBonus = salary.CoachBranchMonthlyBonus.Where(b => b.BranchID == g.Key).FirstOrDefault();
if (branchBonus == null)
{
branchBonus = new CoachBranchMonthlyBonus
{
CoachMonthlySalary = salary,
BranchID = g.Key.Value
};
salary.CoachBranchMonthlyBonus.Add(branchBonus);
}
branchBonus.BranchTotalAttendanceCount = g.Count();
branchBonus.BranchTotalTuition = g.CalcTuition(models);
models.SubmitChanges();
}
calcCoachAchievement();
}
else if ((branch = models.GetTable <BranchStore>().Where(b => b.ManagerID == coach.CoachID || b.ViceManagerID == coach.CoachID).FirstOrDefault()) != null)
{
var branchBonus = salary.CoachBranchMonthlyBonus.Where(b => b.BranchID == branch.BranchID).FirstOrDefault();
if (branchBonus == null)
{
branchBonus = new CoachBranchMonthlyBonus
{
CoachMonthlySalary = salary,
BranchID = branch.BranchID
};
salary.CoachBranchMonthlyBonus.Add(branchBonus);
}
var branchItems = countableItems.Where(w => w.WorkPlace == branch.BranchID);
branchBonus.BranchTotalAttendanceCount = branchItems.Count();
branchBonus.BranchTotalTuition = branchItems.CalcTuition(models);
models.SubmitChanges();
calcCoachAchievement();
}
else
{
calcCoachAchievement();
}
}
}
public static IEnumerable <PaymentMonthlyReportItem> CreateMonthlyPaymentReportForSale <TEntity>(this PaymentQueryViewModel viewModel, ModelSource <TEntity> models)
where TEntity : class, new()
{
IQueryable <Payment> items = models.GetTable <Payment>()
.Join(models.GetTable <PaymentTransaction>().Where(t => t.PaymentOrder.Any()),
p => p.PaymentID, t => t.PaymentID, (p, t) => p);
IEnumerable <PaymentMonthlyReportItem> details = items
.Where(p => p.PayoffDate >= viewModel.PayoffDateFrom && p.PayoffDate < viewModel.PayoffDateTo)
.ToArray()
.Select(i => new PaymentMonthlyReportItem
{
日期 = $"{i.PayoffDate:yyyyMMdd}",
發票號碼 = i.InvoiceID.HasValue ? i.InvoiceItem.TrackCode + i.InvoiceItem.No : null,
分店 = i.PaymentTransaction.BranchStore.BranchName,
買受人統編 = i.InvoiceID.HasValue
? i.InvoiceItem.InvoiceBuyer.IsB2C() ? "--" : i.InvoiceItem.InvoiceBuyer.ReceiptNo
: "--",
//姓名 = null,
//合約編號 = null,
信託 = null,
摘要 = $"其他營業收入-{String.Join("/", i.PaymentTransaction.PaymentOrder.Select(o => o.MerchandiseWindow.ProductName))}({i.PaymentType})",
退款金額_含稅 = null,
收款金額_含稅 = i.PayoffAmount,
借方金額 = null,
貸方金額 = (int?)Math.Round(i.PayoffAmount.Value / 1.05m, MidpointRounding.AwayFromZero),
});
//作廢或折讓
details = details.Concat(
items.Join(models.GetTable <VoidPayment>()
.Where(v => v.VoidDate >= viewModel.PayoffDateFrom && v.VoidDate < viewModel.PayoffDateTo),
p => p.PaymentID, v => v.VoidID, (p, v) => p)
.ToArray()
.Select(i => new PaymentMonthlyReportItem
{
日期 = $"{i.VoidPayment.VoidDate:yyyyMMdd}",
發票號碼 = i.InvoiceID.HasValue ? i.InvoiceItem.TrackCode + i.InvoiceItem.No : null,
分店 = i.PaymentTransaction.BranchStore.BranchName,
買受人統編 = i.InvoiceID.HasValue
? i.InvoiceItem.InvoiceBuyer.IsB2C() ? "--" : i.InvoiceItem.InvoiceBuyer.ReceiptNo
: "--",
//姓名 = null,
//合約編號 = null,
信託 = null,
摘要 = i.InvoiceItem.InvoiceCancellation != null
? $"(沖:{i.PayoffDate:yyyyMMdd}-作廢)其他營業收入-{(Naming.PaymentTransactionType?)i.TransactionType}-{String.Join("/", i.PaymentTransaction.PaymentOrder.Select(o => o.MerchandiseWindow.ProductName))}"
//(沖:20190104-作廢)課程顧問費用-CFA201810091870-00-林妍君
: $"(沖:{i.PayoffDate:yyyyMMdd}-折讓)其他營業收入-{(Naming.PaymentTransactionType?)i.TransactionType}-{String.Join("/", i.PaymentTransaction.PaymentOrder.Select(o => o.MerchandiseWindow.ProductName))}",
退款金額_含稅 = i.AllowanceID.HasValue
? (int?)(i.InvoiceAllowance.TotalAmount + i.InvoiceAllowance.TaxAmount)
: i.PayoffAmount,
收款金額_含稅 = null,
借方金額 = i.AllowanceID.HasValue
? (int?)(i.InvoiceAllowance.TotalAmount)
: (int?)Math.Round(i.PayoffAmount.Value / 1.05m, MidpointRounding.AwayFromZero),
貸方金額 = null,
}
));
return(details);
}
public static IEnumerable <PaymentMonthlyReportItem> CreateMonthlyPaymentReportForPISession <TEntity>(this PaymentQueryViewModel viewModel, ModelSource <TEntity> models)
where TEntity : class, new()
{
//IQueryable<RegisterLesson> lessons = models.GetTable<RegisterLesson>()
// .Join(models.GetTable<LessonTime>().FilterByReceivableTrainingSession(), r => r.RegisterID, l => l.RegisterID, (r, l) => r);
//IQueryable<Payment> items = models.GetTable<Payment>().Join(models.GetTable<TuitionInstallment>()
// .Join(lessons,
// t => t.RegisterID, r => r.RegisterID, (t, r) => t),
// p => p.PaymentID, t => t.InstallmentID, (p, t) => p);
IQueryable <Payment> items = models.GetTable <Payment>()
.Where(p => p.TransactionType == (int)Naming.PaymentTransactionType.自主訓練);
IEnumerable <PaymentMonthlyReportItem> details = items
.Where(p => p.PayoffDate >= viewModel.PayoffDateFrom && p.PayoffDate < viewModel.PayoffDateTo)
.ToArray()
.Select(i => new PaymentMonthlyReportItem
{
日期 = $"{i.PayoffDate:yyyyMMdd}",
發票號碼 = i.InvoiceID.HasValue ? i.InvoiceItem.TrackCode + i.InvoiceItem.No : null,
分店 = i.PaymentTransaction.BranchStore.BranchName,
買受人統編 = i.InvoiceID.HasValue
? i.InvoiceItem.InvoiceBuyer.IsB2C() ? "--" : i.InvoiceItem.InvoiceBuyer.ReceiptNo
: "--",
//姓名 = i.TuitionInstallment.IntuitionCharge.RegisterLesson.UserProfile.FullName(),
//合約編號 = null,
信託 = null,
摘要 = $"銷貨收入-{i.PaymentFor}{i.PayoffDate:yyyyMMdd}-{i.TuitionInstallment?.IntuitionCharge.RegisterLesson.UserProfile.RealName}({i.PaymentType})",
退款金額_含稅 = null,
收款金額_含稅 = i.PayoffAmount,
借方金額 = null,
貸方金額 = (int?)Math.Round(i.PayoffAmount.Value / 1.05m, MidpointRounding.AwayFromZero),
});
//作廢或折讓
details = details.Concat(
items.Join(models.GetTable <VoidPayment>()
.Where(v => v.VoidDate >= viewModel.PayoffDateFrom && v.VoidDate < viewModel.PayoffDateTo),
p => p.PaymentID, v => v.VoidID, (p, v) => p)
.ToArray()
.Select(i => new PaymentMonthlyReportItem
{
日期 = $"{i.VoidPayment.VoidDate:yyyyMMdd}",
發票號碼 = i.InvoiceID.HasValue ? i.InvoiceItem.TrackCode + i.InvoiceItem.No : null,
分店 = i.PaymentTransaction.BranchStore.BranchName,
買受人統編 = i.InvoiceID.HasValue
? i.InvoiceItem.InvoiceBuyer.IsB2C() ? "--" : i.InvoiceItem.InvoiceBuyer.ReceiptNo
: "--",
//姓名 = i.TuitionInstallment.IntuitionCharge.RegisterLesson.UserProfile.FullName(),
//合約編號 = null,
信託 = null,
摘要 = i.InvoiceItem.InvoiceCancellation != null
? $"(沖:{i.PayoffDate:yyyyMMdd}-作廢)銷貨收入-{i.PaymentFor}-{i.TuitionInstallment?.IntuitionCharge.RegisterLesson.UserProfile.RealName}"
//(沖:20190104-作廢)課程顧問費用-CFA201810091870-00-林妍君
: $"(沖:{i.PayoffDate:yyyyMMdd}-折讓)銷貨收入-{i.PaymentFor}-{i.TuitionInstallment?.IntuitionCharge.RegisterLesson.UserProfile.RealName}",
退款金額_含稅 = i.AllowanceID.HasValue
? (int?)(i.InvoiceAllowance.TotalAmount + i.InvoiceAllowance.TaxAmount)
: i.PayoffAmount,
收款金額_含稅 = null,
借方金額 = i.AllowanceID.HasValue
? (int?)(i.InvoiceAllowance.TotalAmount)
: (int?)Math.Round(i.PayoffAmount.Value / 1.05m, MidpointRounding.AwayFromZero),
貸方金額 = null,
}
));
return(details);
}
public static DataTable CreateLessonAchievementDetails <TEntity>(this ModelSource <TEntity> models, IQueryable <LessonTime> items)
where TEntity : class, new()
{
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(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(int)));
table.Columns.Add(new DataColumn("是否信託", typeof(String)));
table.Columns.Add(new DataColumn("課程代碼", typeof(int)));
table.Columns.Add(new DataColumn("體能顧問所屬分店", typeof(String)));
var details = items.Where(t => t.RegisterLesson.RegisterLessonContract != null)
.GroupBy(t => new
{
t.AttendingCoach,
t.RegisterLesson.RegisterLessonContract.ContractID,
t.BranchID
});
foreach (var item in details)
{
CourseContract contract = models.GetTable <CourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo();
r[1] = coach.UserProfile.FullName();
r[2] = contract.CourseContractExtension.BranchStore.BranchName;
if (contract.CourseContractType.IsGroup == true)
{
r[3] = String.Join("/", contract.CourseContractMember.Select(m => m.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = contract.ContractOwner.FullName();
}
r[4] = contract.CourseContractType.TypeName
+ " (" + contract.LessonPriceType.DurationInMinutes + " 分鐘)";
r[5] = contract.LessonPriceType.ListPrice;
var count = item.Count();
var halfCount = item.Count(t => t.LessonAttendance == null || !t.LessonPlan.CommitAttendance.HasValue);
r[6] = count - halfCount;
r[7] = halfCount;
r[8] = branch.BranchName;
var discount = contract.CourseContractType.GroupingLessonDiscount;
r[9] = item.Sum(l => l.RegisterLesson.LessonPriceType.ListPrice) * discount.GroupingMemberCount * discount.PercentageOfDiscount / 100;
r[10] = contract.Entrusted == true
? "是"
: contract.Entrusted == false
? "否"
: "";
if (contract.LessonPriceType.Status.HasValue)
{
r[11] = contract.LessonPriceType.Status.Value;
}
r[12] = coach.WorkPlace();
table.Rows.Add(r);
}
var enterprise = items.Where(t => t.RegisterLesson.RegisterLessonEnterprise != null)
.GroupBy(t => new
{
t.AttendingCoach,
t.RegisterLesson.RegisterLessonEnterprise.ContractID,
t.RegisterID,
t.BranchID
});
foreach (var item in enterprise)
{
EnterpriseCourseContract contract = models.GetTable <EnterpriseCourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
RegisterLesson lesson = models.GetTable <RegisterLesson>().Where(g => g.RegisterID == item.Key.RegisterID).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo;
r[1] = coach.UserProfile.FullName();
r[2] = contract.BranchStore.BranchName;
if (lesson.GroupingMemberCount > 1)
{
r[3] = String.Join("/", lesson.GroupingLesson.RegisterLesson.Select(s => s.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = lesson.UserProfile.FullName();
}
r[4] = contract.Subject;
r[5] = contract.EnterpriseCourseContent.OrderByDescending(c => c.ListPrice).First().ListPrice;
var count = item.Count();
var halfCount = item.Count(t => t.LessonAttendance == null || !t.LessonPlan.CommitAttendance.HasValue);
r[6] = count - halfCount;
r[7] = halfCount;
r[8] = branch.BranchName;
r[9] = count * lesson.RegisterLessonEnterprise.EnterpriseCourseContent.ListPrice
* lesson.GroupingLessonDiscount.PercentageOfDiscount / 100;
r[11] = (int)Naming.LessonPriceStatus.企業合作方案;
r[12] = coach.WorkPlace();
table.Rows.Add(r);
}
var others = items.Where(t => t.RegisterLesson.RegisterLessonContract == null && t.RegisterLesson.RegisterLessonEnterprise == null);
foreach (var item in others)
{
var coach = item.AsAttendingCoach;
var r = table.NewRow();
r[0] = "--";
r[1] = coach.UserProfile.FullName();
if (item.BranchID.HasValue)
{
r[2] = item.BranchStore.BranchName;
}
r[3] = item.RegisterLesson.UserProfile.FullName();
r[4] = item.RegisterLesson.LessonPriceType.Description
+ " (" + item.RegisterLesson.LessonPriceType.DurationInMinutes + " 分鐘)";
r[9] = r[5] = item.RegisterLesson.LessonPriceType.ListPrice;
var halfCount = item.LessonAttendance == null || item.LessonPlan.CommitAttendance.HasValue ? 1 : 0;
r[6] = 1 - halfCount;
r[7] = halfCount;
if (item.BranchID.HasValue)
{
r[8] = item.BranchStore.BranchName;
}
if (item.RegisterLesson.LessonPriceType.Status.HasValue)
{
r[11] = item.RegisterLesson.LessonPriceType.Status.Value;
}
r[12] = coach.WorkPlace();
table.Rows.Add(r);
}
table.TableName = "上課統計表-人員明細";
return(table);
}
public ModelSourceAttribute(ModelSource source)
{
ModelSource = source;
}
static Func <ModelType, ConditionType> CreateError(string property, string compareSource = null, CompareOperator compareOperator = CompareOperator.Equal, object compareValue = null, params ConditionTypeSet[] sets)
{
return((ModelType rootType) =>
{
var label = GetLabel(rootType, property);
// Determine the appropriate error message
// If compareSource is null then a ModelExpression has been specified to handle the validation
if (compareSource == null)
{
return new Error(GetErrorCode(rootType.Name, property, "RequiredIf"), "required", typeof(RequiredIfRule),
(s) => s
.Replace("{property}", label), sets);
}
else
{
string message;
if (compareValue == null)
{
message = compareOperator == CompareOperator.Equal ? "required-if-not-exists" : "required-if-exists";
}
else
{
bool isDate = compareValue is DateTime;
switch (compareOperator)
{
case CompareOperator.Equal:
message = "required-if-equal";
break;
case CompareOperator.NotEqual:
message = "required-if-not-equal";
break;
case CompareOperator.GreaterThan:
message = isDate ? "required-if-after" : "required-if-greater-than";
break;
case CompareOperator.GreaterThanEqual:
message = isDate ? "required-if-on-or-after" : "required-if-greater-than-or-equal";
break;
case CompareOperator.LessThan:
message = isDate ? "required-if-before" : "required-if-less-than";
break;
case CompareOperator.LessThanEqual:
message = isDate ? "required-if-on-or-before" : "required-if-less-than-or-equal";
break;
default:
throw new ArgumentException("Invalid comparison operator for required if rule");
}
}
// Get the comparison source
ModelSource source;
ModelProperty sourceProperty;
ModelSource.TryGetSource(rootType, compareSource, out source, out sourceProperty);
// Create and return the error
var compareValueFormatted = compareValue == null ? "" : ((ModelValueProperty)sourceProperty).FormatValue(compareValue);
return new Error(GetErrorCode(rootType.Name, property, "RequiredIf"), message, typeof(RequiredIfRule),
(s) => s
.Replace("{property}", label)
.Replace("{compareSource}", GetSourceLabel(rootType, compareSource))
.Replace("{compareValue}", compareValueFormatted), sets);
}
});
}
public static DataTable CreateLessonAchievementDetails <TEntity>(this ModelSource <TEntity> models, IQueryable <V_Tuition> items)
where TEntity : class, new()
{
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(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(int)));
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(int)));
table.Columns.Add(new DataColumn("SettlementID", typeof(int)));
table.Columns.Add(new DataColumn("簽約體能顧問", typeof(String)));
var details = items.Where(t => t.ContractID.HasValue)
.GroupBy(t => new
{
t.AttendingCoach,
t.ContractID,
t.BranchID,
t.SettlementID,
});
var branchItems = models.GetTable <BranchStore>().ToArray();
foreach (var item in details)
{
CourseContract contract = models.GetTable <CourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
var branch = branchItems.Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo();
r[1] = coach.UserProfile.FullName();
r[2] = contract.CourseContractExtension.BranchStore.BranchName;
if (contract.CourseContractType.IsGroup == true)
{
r[3] = String.Join("/", contract.CourseContractMember.Select(m => m.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = contract.ContractOwner.FullName();
}
r[4] = contract.CourseContractType.TypeName
+ " (" + contract.LessonPriceType.DurationInMinutes + " 分鐘)";
r[5] = contract.LessonPriceType.ListPrice;
r[6] = item.Where(l => l.CoachAttendance.HasValue).Count(); //item.Where(l => l.AchievementIndex == 1m).Count();
r[7] = item.Join(models.GetTable <Settlement>(), l => l.SettlementID, s => s.SettlementID, (l, s) => new { l.CommitAttendance, s.SettlementDate })
.Where(l => l.CommitAttendance <= l.SettlementDate).Count(); //item.Where(l => l.AchievementIndex == 0.5m).Count();
r[8] = branch.BranchName;
r[9] = item.Sum(l => l.ListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100);
r[10] = contract.Entrusted == true
? "是"
: contract.Entrusted == false
? "否"
: "";
if (contract.LessonPriceType.Status.HasValue)
{
r[11] = contract.LessonPriceType.Status.Value;
}
var sample = item.First();
r[12] = branchItems.Where(b => b.BranchID == sample.CoachWorkPlace)
.Select(b => b.BranchName).FirstOrDefault() ?? "其他";
r[13] = item.Count();
if (item.Key.SettlementID.HasValue)
{
r[14] = item.Key.SettlementID;
}
r[15] = contract.ServingCoach.UserProfile.FullName();
table.Rows.Add(r);
}
var enterprise = items.Where(t => t.EnterpriseContractID.HasValue)
.GroupBy(t => new
{
t.AttendingCoach,
ContractID = t.EnterpriseContractID,
t.RegisterID,
t.BranchID,
t.SettlementID,
});
foreach (var item in enterprise)
{
EnterpriseCourseContract contract = models.GetTable <EnterpriseCourseContract>().Where(u => u.ContractID == item.Key.ContractID).First();
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.Key.AttendingCoach).First();
RegisterLesson lesson = models.GetTable <RegisterLesson>().Where(g => g.RegisterID == item.Key.RegisterID).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.Key.BranchID).First();
var r = table.NewRow();
r[0] = contract.ContractNo;
r[1] = coach.UserProfile.FullName();
r[2] = contract.BranchStore.BranchName;
if (lesson.GroupingMemberCount > 1)
{
r[3] = String.Join("/", lesson.GroupingLesson.RegisterLesson.Select(s => s.UserProfile).ToArray().Select(m => m.FullName()));
}
else
{
r[3] = lesson.UserProfile.FullName();
}
r[4] = contract.Subject;
r[5] = contract.EnterpriseCourseContent.OrderByDescending(c => c.ListPrice).First().ListPrice;
r[6] = item.Where(l => l.CoachAttendance.HasValue).Count(); //item.Where(l => l.AchievementIndex == 1m).Count();
r[7] = item.Join(models.GetTable <Settlement>(), l => l.SettlementID, s => s.SettlementID, (l, s) => new { l.CommitAttendance, s.SettlementDate })
.Where(l => l.CommitAttendance <= l.SettlementDate).Count(); //item.Where(l => l.AchievementIndex == 0.5m).Count();
r[8] = branch.BranchName;
r[9] = item.Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount
* l.PercentageOfDiscount / 100);
r[11] = item.FirstOrDefault()?.ELStatus; //(int)Naming.LessonPriceStatus.企業合作方案;
var sample = item.First();
r[12] = branchItems.Where(b => b.BranchID == sample.CoachWorkPlace)
.Select(b => b.BranchName).FirstOrDefault() ?? "其他";
r[13] = item.Count();
if (item.Key.SettlementID.HasValue)
{
r[14] = item.Key.SettlementID;
}
table.Rows.Add(r);
}
var others = items.Where(t => !t.ContractID.HasValue && !t.EnterpriseRegisterID.HasValue);
foreach (var item in others)
{
ServingCoach coach = models.GetTable <ServingCoach>().Where(c => c.CoachID == item.AttendingCoach).First();
var branch = models.GetTable <BranchStore>().Where(b => b.BranchID == item.BranchID).FirstOrDefault();
var lesson = models.GetTable <RegisterLesson>().Where(g => g.RegisterID == item.RegisterID).First();
var r = table.NewRow();
r[0] = "--";
r[1] = coach.UserProfile.FullName();
if (item.BranchID.HasValue)
{
r[2] = branch.BranchName;
}
r[3] = lesson.UserProfile.FullName();
r[4] = lesson.LessonPriceType.Description
+ " (" + lesson.LessonPriceType.DurationInMinutes + " 分鐘)";
r[9] = r[5] = item.ListPrice;
r[6] = item.CoachAttendance.HasValue ? 1 : 0; //item.AchievementIndex == 1m ? 1 : 0;
var settlement = models.GetTable <Settlement>().Where(s => s.SettlementID == item.SettlementID).FirstOrDefault();
r[7] = item.CommitAttendance.HasValue && item.CommitAttendance <= settlement?.SettlementDate ? 1 : 0; //item.AchievementIndex == 0.5m ? 1 : 0;
if (branch != null)
{
r[8] = branch.BranchName;
}
r[11] = item.PriceStatus;
r[12] = branchItems.Where(b => b.BranchID == item.CoachWorkPlace)
.Select(b => b.BranchName).FirstOrDefault() ?? "其他";
r[13] = 1;
if (item.SettlementID.HasValue)
{
r[14] = item.SettlementID;
}
table.Rows.Add(r);
}
table.TableName = "上課統計表-人員明細";
return(table);
}
protected override void WriteImplementation(ModelIdentifierBase id, UpdateContainer update, ModelSource target, IObserver <IOperationState <object> > operation, CancellationToken ct = default)
{
if (id is BbcArticleIdentifier articleId)
{
var original = (NewsArticle)update.Original;
var updated = (NewsArticle)update.Updated;
if (updated.Title != original.Title)
{
Debug.WriteLine("Title updated from '" + original.Title + "' to '" + updated.Title);
}
operation.OnCompleteResult(id, id, 100);
}
}
/// <summary>
/// Save any modifications to the EditableObject. If the mesh is a scene mesh or imported mesh, it
/// will be saved to a new asset. If the mesh was originally an asset mesh, the asset is overwritten.
/// </summary>
/// <param name="mesh">mesh to save</param>
/// <param name="meshFilter">will update the mesh filter with the new mesh if not null</param>
/// <param name="skinnedMeshRenderer">will update the skinned mesh renderer with the new mesh if not null</param>
/// <returns>return true if save was successful, false if user-canceled or otherwise failed.</returns>
internal static bool SaveMeshAsset(Mesh mesh, MeshFilter meshFilter = null, SkinnedMeshRenderer skinnedMeshRenderer = null, int overridenDialogResult = -1, string overridenPath = "")
{
if (mesh == null)
{
return(false);
}
string save_path = !string.IsNullOrEmpty(overridenPath) ? overridenPath : DO_NOT_SAVE;
ModelSource source = GetMeshGUID(mesh);
switch (source)
{
case ModelSource.Asset:
int saveChanges = overridenDialogResult != -1
? overridenDialogResult
: EditorUtility.DisplayDialogComplex(
"Save Changes",
"Save changes to edited mesh?",
"Save", // DIALOG_OK
"Cancel", // DIALOG_CANCEL
"Save As"); // DIALOG_ALT
if (saveChanges == DIALOG_OK)
{
save_path = AssetDatabase.GetAssetPath(mesh);
}
else if (saveChanges == DIALOG_ALT)
{
save_path = EditorUtility.SaveFilePanelInProject("Save Mesh As", mesh.name + ".asset", "asset", "Save edited mesh to");
}
else
{
return(false);
}
break;
case ModelSource.Imported:
case ModelSource.Scene:
default:
save_path = EditorUtility.SaveFilePanelInProject("Save Mesh As", mesh.name + ".asset", "asset", "Save edited mesh to");
break;
}
if (!save_path.Equals(DO_NOT_SAVE) && !string.IsNullOrEmpty(save_path))
{
Mesh existing = AssetDatabase.LoadAssetAtPath <Mesh>(save_path);
bool overwrite = existing != null;
Mesh dst = overwrite ? existing : new Mesh();
PolyMeshUtility.Copy(mesh, dst);
if (!overwrite)
{
AssetDatabase.CreateAsset(dst, save_path);
}
AssetDatabase.Refresh();
return(true);
}
// Save was canceled
return(false);
}
public static MonthlyIndicator AssertMonthlyIndicator <TEntity>(this MonthlyIndicatorQueryViewModel viewModel, ModelSource <TEntity> models)
where TEntity : class, new()
{
var item = models.GetTable <MonthlyIndicator>().Where(i => i.PeriodID == viewModel.PeriodID).FirstOrDefault();
if (item != null)
{
return(item);
}
item = models.GetTable <MonthlyIndicator>().Where(i => (i.Year == viewModel.Year && i.Month == viewModel.Month)).FirstOrDefault();
if (item != null)
{
return(item);
}
item = models.InitializeMonthlyIndicator(viewModel.Year.Value, viewModel.Month.Value);
return(item);
}
protected abstract void WriteImplementation(ModelIdentifierBase id, UpdateContainer update, ModelSource target, IObserver <IOperationState <object> > operation, CancellationToken ct = default);
public static MonthlyIndicator GetAlmostMonthlyIndicator <TEntity>(this MonthlyIndicatorQueryViewModel viewModel, ModelSource <TEntity> models, bool?exact = null)
where TEntity : class, new()
{
var item = models.GetTable <MonthlyIndicator>().Where(i => i.PeriodID == viewModel.PeriodID).FirstOrDefault();
if (item != null)
{
return(item);
}
item = models.GetTable <MonthlyIndicator>().Where(i => (i.Year == viewModel.Year && i.Month == viewModel.Month)).FirstOrDefault();
if (item != null)
{
return(item);
}
if (exact == true)
{
return(null);
}
DateTime period = viewModel.Year.HasValue && viewModel.Month.HasValue
? new DateTime(viewModel.Year.Value, viewModel.Month.Value, 1)
: DateTime.Today;
item = models.GetTable <MonthlyIndicator>().Where(i => i.StartDate <= period)
.OrderByDescending(i => i.StartDate).FirstOrDefault();
if (item != null)
{
return(item);
}
item = models.GetTable <MonthlyIndicator>().Where(i => i.StartDate > period)
.OrderBy(i => i.StartDate).FirstOrDefault();
return(item);
}
public virtual void InitializeCollections(IMongoDatabase database)
{
Database = database;
ModelSource.GetModel(this);
}
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);
}
public static IQueryable <V_Tuition> InquireAchievement <TEntity>(this AchievementQueryViewModel viewModel, ModelSource <TEntity> models, UserProfile profile = null)
where TEntity : class, new()
{
IQueryable <V_Tuition> items = models.GetTable <V_Tuition>()
.Where(v => v.PriceStatus != (int)Naming.DocumentLevelDefinition.教練PI);
if (viewModel.IgnoreAttendance == true)
{
}
else
{
items = items.FilterByCompleteLesson();
}
bool hasConditon = false;
if (viewModel.BypassCondition == true)
{
hasConditon = true;
}
if (viewModel.CoachID.HasValue)
{
hasConditon = true;
items = items.Where(c => c.AttendingCoach == viewModel.CoachID);
}
if (viewModel.ByCoachID != null && viewModel.ByCoachID.Length > 0)
{
items = items.Where(c => viewModel.ByCoachID.Contains(c.AttendingCoach));
}
//if (!hasConditon)
//{
if (viewModel.BranchID.HasValue)
{
hasConditon = true;
items = items.Where(c => c.BranchID == viewModel.BranchID);
}
//}
if (!hasConditon)
{
if (profile == null)
{
items = items.Where(l => false);
}
else if (profile.IsAssistant() || profile.IsAccounting() || profile.IsOfficer())
{
}
else if (profile.IsManager() || profile.IsViceManager())
{
var coaches = profile.GetServingCoachInSameStore(models);
items = items.Join(coaches, c => c.AttendingCoach, h => h.CoachID, (c, h) => c);
}
else if (profile.IsCoach())
{
items = items.Where(c => c.AttendingCoach == profile.UID);
}
}
if (viewModel.AchievementDateFrom.HasValue)
{
items = items.Where(c => c.ClassTime >= viewModel.AchievementDateFrom);
if (!viewModel.AchievementDateTo.HasValue)
{
viewModel.AchievementDateTo = viewModel.AchievementDateFrom;
}
}
if (viewModel.AchievementDateTo.HasValue)
{
items = items.Where(c => c.ClassTime < viewModel.AchievementDateTo.Value.AddMonths(1));
}
return(items);
}
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 static IQueryable <CourseContract> InquireContract <TEntity>(this CourseContractQueryViewModel viewModel, ModelSource <TEntity> models)
where TEntity : class, new()
{
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 static int CalculateAverageLessonPrice <TEntity>(this MonthlyIndicator item, ModelSource <TEntity> models, int?coachID)
where TEntity : class, new()
{
AchievementQueryViewModel queryModel = new AchievementQueryViewModel
{
AchievementDateFrom = item.StartDate.AddMonths(-1),
BypassCondition = true,
};
IQueryable <V_Tuition> lessonItems = queryModel.InquireAchievement(models);
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;
int lessonAchievement;
var coachTuitionItems = tuitionItems.Where(t => t.AttendingCoach == 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);
var completeLessonCount = Math.Max(coachTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.PriceStatus)).Count()
+ coachTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.ELStatus)).Count(), 1);
return((lessonAchievement + completeLessonCount - 1) / completeLessonCount);
}
public static IQueryable <Payment> InquirePayment <TEntity>(this PaymentQueryViewModel viewModel, ModelSource <TEntity> models)
where TEntity : class, new()
{
IQueryable <Payment> items = models.PromptIncomePayment();
IQueryable <VoidPayment> voidItems = models.GetTable <VoidPayment>();
IQueryable <InvoiceItem> invoiceItems = models.GetTable <InvoiceItem>();
bool hasInvoiceQuery = false;
if (viewModel.PaymentID.HasValue)
{
items = items.Where(p => p.PaymentID == viewModel.PaymentID);
}
IQueryable <Payment> buildQueryByUserName(IQueryable <Payment> queryItems, String userName)
{
IQueryable <UserProfile> users = models.GetTable <UserProfile>().Where(c => c.RealName.Contains(userName) ||
c.Nickname.Contains(userName));
IQueryable <ContractPayment> cp = users.Join(models.GetTable <CourseContractMember>(), u => u.UID, m => m.UID, (u, m) => m)
.Join(models.GetTable <CourseContract>(), m => m.ContractID, c => c.ContractID, (m, c) => c)
.Join(models.GetTable <ContractPayment>(), c => c.ContractID, p => p.ContractID, (m, p) => p);
IQueryable <TuitionInstallment> ti = users.Join(models.GetTable <RegisterLesson>(), u => u.UID, r => r.UID, (u, r) => r)
.Join(models.GetTable <TuitionInstallment>(), r => r.RegisterID, t => t.RegisterID, (r, t) => t);
queryItems = queryItems.Join(cp, p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.Union(queryItems.Join(ti, p => p.PaymentID, c => c.InstallmentID, (p, c) => p));
return(queryItems);
}
viewModel.UserName = viewModel.UserName.GetEfficientString();
if (viewModel.UserName != null)
{
items = buildQueryByUserName(items, viewModel.UserName);
}
IQueryable <Payment> buildQueryByContractNo(IQueryable <Payment> queryItems, String contractNo)
{
IQueryable <CourseContract> ci = models.GetTable <CourseContract>();
var no = contractNo.Split('-');
if (no.Length > 1)
{
int.TryParse(no[1], out int seqNo);
ci = ci.Where(c => c.ContractNo.StartsWith(no[0]) &&
c.SequenceNo == seqNo);
}
else
{
ci = ci.Where(c => c.ContractNo.StartsWith(contractNo));
}
IQueryable <ContractPayment> cp = ci.Join(models.GetTable <ContractPayment>(), c => c.ContractID, p => p.ContractID, (m, p) => p);
queryItems = queryItems.Join(cp, p => p.PaymentID, c => c.PaymentID, (p, c) => p);
return(queryItems);
}
viewModel.ContractNo = viewModel.ContractNo.GetEfficientString();
if (viewModel.ContractNo != null)
{
items = buildQueryByContractNo(items, viewModel.ContractNo);
}
if (viewModel.BranchID.HasValue)
{
items = items.Where(c => c.PaymentTransaction.BranchID == viewModel.BranchID);
}
if (viewModel.HandlerID.HasValue)
{
items = items.Where(c => c.HandlerID == viewModel.HandlerID);
voidItems = voidItems.Where(v => v.HandlerID == viewModel.HandlerID);
}
if (viewModel.TransactionType.HasValue)
{
items = items.Where(c => c.TransactionType == viewModel.TransactionType);
}
if (viewModel.CompoundType != null)
{
viewModel.CompoundType = viewModel.CompoundType.Where(t => t.HasValue).ToArray();
if (viewModel.CompoundType.Length > 0)
{
items = items.Where(c => viewModel.CompoundType.Contains((Naming.PaymentTransactionType?)c.TransactionType));
}
}
if (viewModel.Status.HasValue)
{
//items = items.Where(c => c.Status == viewModel.Status);
//items = items.Where(c => c.VoidPayment.Status == viewModel.Status);
items = items.Where(c => c.Status == viewModel.Status ||
c.VoidPayment.Status == viewModel.Status);
}
if (viewModel.InvoiceType.HasValue)
{
hasInvoiceQuery = true;
invoiceItems = invoiceItems.Where(c => c.InvoiceType == (byte)viewModel.InvoiceType);
}
if (viewModel.HasInvoicePrinted.HasValue)
{
hasInvoiceQuery = true;
if (viewModel.HasInvoicePrinted == true)
{
invoiceItems = invoiceItems
.Where(i => i.Document.DocumentPrintLog.Any());
}
else if (viewModel.HasInvoicePrinted == false)
{
invoiceItems = invoiceItems
.Where(i => i.InvoiceCancellation == null)
.Where(i => i.PrintMark == "Y")
.Where(i => !i.Document.DocumentPrintLog.Any());
}
}
IQueryable <Payment> buildQueryByInvoiceNo(IQueryable <Payment> queryItems, String invoiceNo)
{
if (Regex.IsMatch(invoiceNo, "[A-Za-z]{2}[0-9]{8}"))
{
String trackCode = invoiceNo.Substring(0, 2).ToUpper();
String no = invoiceNo.Substring(2);
queryItems = queryItems.Join(models.GetTable <InvoiceItem>()
.Where(c => c.TrackCode == trackCode && c.No == no),
p => p.InvoiceID, i => i.InvoiceID, (p, i) => p);
return(queryItems);
}
else
{
return(models.GetTable <Payment>().Where(p => false));
}
}
viewModel.InvoiceNo = viewModel.InvoiceNo.GetEfficientString();
if (viewModel.InvoiceNo != null && Regex.IsMatch(viewModel.InvoiceNo, "[A-Za-z]{2}[0-9]{8}"))
{
hasInvoiceQuery = true;
String trackCode = viewModel.InvoiceNo.Substring(0, 2).ToUpper();
String no = viewModel.InvoiceNo.Substring(2);
invoiceItems = invoiceItems.Where(c => c.TrackCode == trackCode &&
c.No == no);
//allowanceItems = allowanceItems.Where(a => a.AllowanceNumber == viewModel.InvoiceNo);
//voidItems = voidItems
// .Join(models.GetTable<Payment>()
// .Join(models.GetTable<InvoiceItem>().Where(i => i.TrackCode == trackCode && i.No == no),
// p => p.InvoiceID, i => i.InvoiceID, (p, i) => p),
// v => v.VoidID, p => p.PaymentID, (v, p) => v);
}
viewModel.BuyerReceiptNo = viewModel.BuyerReceiptNo.GetEfficientString();
if (viewModel.BuyerReceiptNo != null)
{
hasInvoiceQuery = true;
invoiceItems = invoiceItems.Where(c => c.InvoiceBuyer.ReceiptNo == viewModel.BuyerReceiptNo);
}
IQueryable <InvoiceAllowance> allowanceItems = models.GetTable <InvoiceAllowance>();
IQueryable <InvoiceCancellation> cancelledItems = models.GetTable <InvoiceCancellation>();
IQueryable <Payment> result = items;
if (viewModel.PayoffDateFrom.HasValue)
{
result = result.Where(p => p.PayoffDate >= viewModel.PayoffDateFrom);
}
if (viewModel.PayoffDateTo.HasValue)
{
result = result.Where(i => i.PayoffDate < viewModel.PayoffDateTo.Value.AddDays(1));
}
//result = items.Join(invoiceItems, p => p.InvoiceID, i => i.InvoiceID, (p, i) => p);
if (viewModel.ShareFor.HasValue)
{
result = result.Where(p => p.VoidPayment == null)
.Where(p => p.TuitionAchievement.Any(t => t.CoachID == viewModel.ShareFor));
}
else if (viewModel.IncomeOnly == true)
{
}
//else if (dateQuery)
//{
// result = result
// .Union(items.Join(invoiceItems, p => p.InvoiceID, i => i.InvoiceID, (p, i) => p))
// .Union(items.Join(allowanceItems, p => p.AllowanceID, a => a.AllowanceID, (p, a) => p)
// .Union(items.Join(voidItems, p => p.PaymentID, c => c.VoidID, (p, c) => p)));
//}
if (viewModel.IsCancelled == true)
{
result = result
.Where(p => p.VoidPayment != null /*|| p.AllowanceID.HasValue*/);
}
else if (viewModel.IsCancelled == false)
{
result = result
.Where(p => p.VoidPayment == null /*&& !p.AllowanceID.HasValue*/);
}
if (viewModel.AllowanceDateFrom.HasValue)
{
viewModel.HasAllowance = true;
allowanceItems = allowanceItems.Where(a => a.AllowanceDate >= viewModel.AllowanceDateFrom);
}
if (viewModel.AllowanceDateTo.HasValue)
{
viewModel.HasAllowance = true;
allowanceItems = allowanceItems.Where(a => a.AllowanceDate < viewModel.AllowanceDateTo.Value.AddDays(1));
}
if (viewModel.HasAllowance == true)
{
result = result.Join(allowanceItems, p => p.AllowanceID, a => a.AllowanceID, (p, a) => p);
}
if (viewModel.CancelDateFrom.HasValue)
{
viewModel.HasCancellation = true;
cancelledItems = cancelledItems.Where(i => i.CancelDate >= viewModel.CancelDateFrom);
}
if (viewModel.CancelDateTo.HasValue)
{
viewModel.HasCancellation = true;
cancelledItems = cancelledItems.Where(i => i.CancelDate < viewModel.CancelDateTo.Value.AddDays(1));
}
if (viewModel.HasCancellation == true)
{
hasInvoiceQuery = true;
invoiceItems = invoiceItems.Join(cancelledItems, i => i.InvoiceID, c => c.InvoiceID, (i, c) => i);
}
if (hasInvoiceQuery)
{
result = result.Join(invoiceItems, p => p.InvoiceID, i => i.InvoiceID, (p, i) => p);
}
if (viewModel.CustomQuery != null)
{
result = buildQueryByUserName(result, viewModel.CustomQuery)
.Union(buildQueryByContractNo(result, viewModel.CustomQuery))
.Union(buildQueryByInvoiceNo(result, viewModel.CustomQuery));
}
if (viewModel.HasShare.HasValue)
{
IQueryable <TuitionAchievement> shareItems = models.GetTable <TuitionAchievement>();
if (viewModel.HasShare == true)
{
result = result.Where(t => shareItems.Any(s => s.InstallmentID == t.PaymentID));
}
else
{
//result = result.Where(t => !shareItems.Any(s => s.InstallmentID == t.PaymentID));
result = result.Where(t => !t.TuitionAchievement.Any() || t.TuitionAchievement.Sum(a => a.ShareAmount) < t.PayoffAmount);
}
}
if (viewModel.RelatedID.HasValue)
{
result = result.Where(p => p.HandlerID == viewModel.RelatedID)
.Union(result.Where(p => p.ContractPayment.CourseContract.FitnessConsultant == viewModel.RelatedID))
.Union(result.Where(p => p.TuitionInstallment.IntuitionCharge.RegisterLesson.LessonTime.Any(l => l.AttendingCoach == viewModel.RelatedID)));
}
return(result);
}
public static void UpdateMonthlyAchievementGoal <TEntity>(this MonthlyIndicator item, ModelSource <TEntity> models)
where TEntity : class, new()
{
IQueryable <MonthlyCoachRevenueIndicator> coachItems = models.GetTable <MonthlyCoachRevenueIndicator>().Where(r => r.PeriodID == item.PeriodID);
List <MonthlyBranchRevenueGoal> branchGoalItems = new List <MonthlyBranchRevenueGoal>();
foreach (var branchIndicator in item.MonthlyBranchIndicator)
{
var revenueGoal = branchIndicator.MonthlyBranchRevenueIndicator.Where(r => r.MonthlyBranchRevenueGoal != null)
.Select(r => r.MonthlyBranchRevenueGoal).FirstOrDefault();
if (revenueGoal == null)
{
continue;
}
var branchItems = coachItems.Where(c => c.BranchID == branchIndicator.BranchID);
revenueGoal.CustomRevenueGoal = branchItems.Sum(b => b.LessonTuitionGoal + b.AchievementGoal);
var baseIndicator = branchIndicator.MonthlyBranchRevenueIndicator.OrderBy(m => m.GradeID).First();
revenueGoal.CustomIndicatorPercentage = Math.Round(((decimal?)revenueGoal.CustomRevenueGoal * (decimal?)baseIndicator.MonthlyRevenueGrade.IndicatorPercentage / (decimal?)baseIndicator.RevenueGoal) ?? 0m, 2);
revenueGoal.AchievementGoal = branchItems.Sum(b => b.AchievementGoal);
revenueGoal.LessonTuitionGoal = branchItems.Sum(b => b.LessonTuitionGoal);
revenueGoal.CompleteLessonsGoal = branchItems.Sum(b => b.CompleteLessonsGoal);
models.SubmitChanges();
branchGoalItems.Add(revenueGoal);
}
var headquarterRevenueGoal = item.MonthlyRevenueIndicator.Where(r => r.MonthlyRevenueGoal != null)
.Select(r => r.MonthlyRevenueGoal).FirstOrDefault();
if (headquarterRevenueGoal != null)
{
headquarterRevenueGoal.CustomRevenueGoal = branchGoalItems.Sum(b => b.CustomRevenueGoal);
var baseIndicator = item.MonthlyRevenueIndicator.OrderBy(r => r.GradeID).First();
headquarterRevenueGoal.CustomIndicatorPercentage = Math.Round((decimal)headquarterRevenueGoal.CustomRevenueGoal * (decimal)baseIndicator.MonthlyRevenueGrade.IndicatorPercentage / (decimal)baseIndicator.RevenueGoal, 2);
headquarterRevenueGoal.AchievementGoal = branchGoalItems.Sum(b => b.AchievementGoal);
headquarterRevenueGoal.LessonTuitionGoal = branchGoalItems.Sum(b => b.LessonTuitionGoal);
headquarterRevenueGoal.CompleteLessonsGoal = branchGoalItems.Sum(b => b.CompleteLessonsGoal);
models.SubmitChanges();
}
}
public ModelSourceAttribute(ModelSource source, string name)
{
ModelSource = source;
Name = name;
}
public LineMessageHandler(Event lineEvent, ModelSource <UserProfile> models, HttpContext context)
{
this.lineEvent = lineEvent;
this.models = models;
this.currentContext = context;
}
public void OpenModels(ModelSource source, string path)
{
var s3d = S3D.Load(path);
var wld = s3d.Files.FirstOrDefault(x => x.Name.Contains(".wld"));
if (wld != null)
{
using (var ms = new MemoryStream(wld.Bytes))
{
var pwld = WLD.Load(ms);
pwld.TexturingFormat = WLD.TextureFormat.Original;
pwld.Files = s3d;
_modelDisplay3d.AddWld(pwld);
System.Threading.ThreadPool.QueueUserWorkItem(x =>
{
pwld.ResolveMeshNames();
});
if (Models.Count == 0)
{
Models.Add(new DefaultLights());
Models.Add(new CoordinateSystemVisual3D());
Models.Add(new ModelVisual3D()
{
Content = _modelDisplay3d.Model
});
}
}
}
}
protected SampleController() : base()
{
models = new ModelSource <T, TEntity>();
}
