public ActionResult InvokePaymentQuery(PaymentQueryViewModel viewModel)
{
//viewModel.ContractDateFrom = DateTime.Today.FirstDayOfMonth();
//viewModel.ContractDateTo = viewModel.ContractDateFrom.Value.AddMonths(1).AddDays(-1);
ViewBag.ViewModel = viewModel;
return(View("~/Views/PaymentConsole/PaymentModal/PaymentQuery.cshtml"));
}
public ActionResult ShowCurrentMonthPaymentView(PaymentQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
IQueryable <Payment> items = models.PromptIncomePayment();
return(View("~/Views/PaymentConsole/Module/CurrentMonthPaymentView.cshtml", items));
}
// GET: PaymentConsole
public ActionResult ShowPaymentList(PaymentQueryViewModel viewModel)
{
if (viewModel.KeyID != null)
{
viewModel.PaymentID = viewModel.DecryptKeyValue();
}
IQueryable <Payment> items = viewModel.InquirePayment(this, out string alertMessage);
return(View("~/Views/PaymentConsole/Module/PaymentItemsList.cshtml", items));
}
public ActionResult GetMerchandiseList(PaymentQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
var items = models.GetTable <MerchandiseTransaction>()
.Where(t => t.TransactionID == viewModel.TransactionID)
.Select(t => t.MerchandiseWindow)
.Where(p => p.Status == (int)Naming.MerchandiseStatus.OnSale);
return(View("~/Views/PaymentConsole/Module/MerchandiseList.cshtml", items));
}
// GET: PaymentConsole
public async Task <ActionResult> ShowPaymentListAsync(PaymentQueryViewModel viewModel)
{
if (viewModel.KeyID != null)
{
viewModel.PaymentID = viewModel.DecryptKeyValue();
}
IQueryable <Payment> items = await viewModel.InquirePaymentAsync(this);
return(View("~/Views/PaymentConsole/Module/PaymentItemsList.cshtml", items));
}
public ActionResult ApplyCoachAchievementShare(PaymentQueryViewModel viewModel)
{
ViewResult result = (ViewResult)ProcessPayment(viewModel);
Payment item = result.Model as Payment;
if (item == null)
{
return(result);
}
return(View("~/Views/PaymentConsole/PaymentModal/ApplyCoachAchievementShare.cshtml", item));
}
public ActionResult ShowAchievementShareList(PaymentQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
if (viewModel.KeyID != null)
{
viewModel.PaymentID = viewModel.DecryptKeyValue();
}
var items = models.GetTable <TuitionAchievement>().Where(t => t.InstallmentID == viewModel.PaymentID);
return(View("~/Views/PaymentConsole/Module/AchievementShareList.cshtml", items));
}
public ActionResult ShowPaymentDetails(PaymentQueryViewModel viewModel)
{
ViewResult result = (ViewResult)ProcessPayment(viewModel);
Payment item = result.Model as Payment;
if (item == null)
{
return(result);
}
return(View("~/Views/PaymentConsole/PaymentModal/AboutPaymentDetails.cshtml", item));
}
public async Task <ActionResult> InquirePaymentAsync(PaymentQueryViewModel viewModel)
{
if (viewModel.KeyID != null)
{
viewModel.PaymentID = viewModel.DecryptKeyValue();
}
viewModel.CustomQuery = viewModel.CustomQuery.GetEfficientString();
//if (viewModel.CustomQuery != null)
//{
// viewModel.ContractNo = viewModel.UserName = viewModel.InvoiceNo = viewModel.CustomQuery;
//}
if (viewModel.CustomQuery == null)
{
bool hasQuery = false;
if (!viewModel.PayoffDateFrom.HasValue)
{
ModelState.AddModelError("PayoffDateFrom", "請選擇查詢起日");
}
else
{
hasQuery = true;
}
if (!viewModel.PayoffDateTo.HasValue)
{
ModelState.AddModelError("PayoffDateTo", "請選擇查詢迄日");
}
else
{
hasQuery = true;
}
if (!hasQuery)
{
ModelState.AddModelError("CustomQuery", "請輸入學生姓名(暱稱) 或 合約編號 或 發票號碼");
}
}
if (!ModelState.IsValid)
{
ViewBag.ModelState = ModelState;
return(View(ConsoleHomeController.InputErrorView));
}
IQueryable <Payment> items = await viewModel.InquirePaymentAsync(this);
return(View("~/Views/PaymentConsole/Module/CustomPaymentList.cshtml", items));
}
public static async Task <IQueryable <Payment> > InquirePaymentAsync(this PaymentQueryViewModel queryViewModel, SampleController <UserProfile> controller)
{
var ModelState = controller.ModelState;
var ViewBag = controller.ViewBag;
var HttpContext = controller.HttpContext;
var models = controller.DataSource;
ViewBag.ViewModel = queryViewModel;
PaymentQueryViewModel viewModel = (PaymentQueryViewModel)queryViewModel.Duplicate();
var profile = await HttpContext.GetUserAsync();
return(viewModel.InquirePayment(models));
}
public static IQueryable <Payment> InquirePayment <TEntity>(this PaymentQueryViewModel queryViewModel, SampleController <TEntity> controller, out String alertMessage)
where TEntity : class, new()
{
alertMessage = null;
var ModelState = controller.ModelState;
var ViewBag = controller.ViewBag;
var HttpContext = controller.HttpContext;
var models = controller.DataSource;
ViewBag.ViewModel = queryViewModel;
PaymentQueryViewModel viewModel = (PaymentQueryViewModel)queryViewModel.Duplicate();
var profile = HttpContext.GetUser();
return(viewModel.InquirePayment(models));
}
public ActionResult ProcessPayment(PaymentQueryViewModel viewModel)
{
if (viewModel.KeyID != null)
{
viewModel.PaymentID = viewModel.DecryptKeyValue();
}
var item = models.GetTable <Payment>().Where(c => c.PaymentID == viewModel.PaymentID).FirstOrDefault();
if (item == null)
{
return(View("~/Views/ConsoleHome/Shared/JsAlert.cshtml", model: "收款資料錯誤!!"));
}
return(View("~/Views/PaymentConsole/Module/ProcessPayment.cshtml", item));
}
public static IQueryable <LessonTime> GetUnpaidPISession(this PaymentQueryViewModel viewModel, GenericManager <BFDataContext> models)
{
var unpaid = models.FilterByUnpaidLesson();
var items = models.GetTable <LessonTime>()
.Where(r => r.ClassTime < DateTime.Today.AddDays(1))
.Where(r => r.RegisterLesson.LessonPriceType.Status == (int)Naming.DocumentLevelDefinition.自主訓練)
.Where(r => unpaid.Any(l => l.RegisterID == r.RegisterID));
if (viewModel.BranchID.HasValue)
{
items = items.Where(r => r.BranchID == viewModel.BranchID);
}
return(items);
}
public ActionResult DeleteAchievementShare(PaymentQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
if (viewModel.KeyID != null)
{
viewModel.PaymentID = viewModel.DecryptKeyValue();
}
if (models.ExecuteCommand("delete TuitionAchievement where CoachID = {0} and InstallmentID = {1}",
viewModel.CoachID, viewModel.PaymentID) > 0)
{
return(Json(new { result = true }));
}
else
{
return(Json(new { result = false, message = "資料錯誤!!" }));
}
}
public ActionResult ProcessAchievementShare(PaymentQueryViewModel viewModel)
{
ViewBag.ViewModel = viewModel;
if (viewModel.KeyID != null)
{
viewModel.PaymentID = viewModel.DecryptKeyValue();
}
var item = models.GetTable <TuitionAchievement>()
.Where(t => t.InstallmentID == viewModel.PaymentID)
.Where(t => t.CoachID == viewModel.CoachID)
.FirstOrDefault();
if (item == null)
{
return(View("~/Views/ConsoleHome/Shared/JsAlert.cshtml", model: "收款資料錯誤!!"));
}
return(View("~/Views/PaymentConsole/Module/ProcessAchievementShare.cshtml", item));
}
public static IQueryable <LessonTime> GetUnpaidTrialSession(this PaymentQueryViewModel viewModel, GenericManager <BFDataContext> models, UserProfile profile)
{
var registerItems = models.PropmptReceivableTrialLesson();
var unpaid = models.FilterByUnpaidLesson(registerItems);
var items = models.GetTable <LessonTime>()
.Where(r => unpaid.Any(l => l.RegisterID == r.RegisterID));
if (profile.IsAssistant() || profile.IsOfficer())
{
}
else
{
items = items
.Where(l => ((l.BranchStore.Status & (int)BranchStore.StatusDefinition.VirtualClassroom) == (int)BranchStore.StatusDefinition.VirtualClassroom &&
l.AttendingCoach == profile.UID) ||
l.BranchID == viewModel.BranchID);
}
return(items);
}
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 void UpdateMonthlyAchievement <TEntity>(this MonthlyIndicator item, ModelSource <TEntity> models, bool?forcedUpdate = null, bool?calcAverage = null)
where TEntity : class, new()
{
AchievementQueryViewModel queryModel = new AchievementQueryViewModel
{
AchievementDateFrom = item.StartDate,
BypassCondition = true,
};
IQueryable <Payment> voidPayment = UpdateVoidShare(models, item.StartDate, item.EndExclusiveDate);
IQueryable <V_Tuition> lessonItems = queryModel.InquireAchievement(models);
IQueryable <LessonTime> STItems = models.GetTable <LessonTime>()
.Where(c => c.ClassTime >= item.StartDate)
.Where(c => c.ClassTime < item.StartDate.AddMonths(1))
.Where(t => t.TrainingBySelf == 2);
if (forcedUpdate == true)
{
lessonItems = lessonItems.Where(l => l.SettlementID.HasValue);
}
else if (item.StartDate == DateTime.Today.FirstDayOfMonth())
{
lessonItems = lessonItems.Where(l => l.ClassTime < DateTime.Today);
}
else
{
lessonItems = lessonItems.Where(l => l.SettlementID.HasValue);
}
IQueryable <V_Tuition> tuitionItems = lessonItems;
PaymentQueryViewModel paymentQuery = new PaymentQueryViewModel
{
PayoffDateFrom = item.StartDate,
PayoffDateTo = item.EndExclusiveDate.AddDays(-1),
BypassCondition = true,
};
IQueryable <Payment> paymentItems = paymentQuery.InquirePayment(models);
IQueryable <TuitionAchievement> achievementItems = paymentItems.GetPaymentAchievement(models);
CourseContractQueryViewModel contractQuery = new CourseContractQueryViewModel
{
ContractQueryMode = Naming.ContractServiceMode.ContractOnly,
Status = (int)Naming.CourseContractStatus.已生效,
EffectiveDateFrom = item.StartDate,
EffectiveDateTo = item.EndExclusiveDate,
};
IQueryable <CourseContract> contractItems = contractQuery.InquireContract(models);
IQueryable <CourseContract> installmentItems = contractItems.Where(c => c.Installment.HasValue);
IQueryable <CourseContract> nonInstallmentItems = contractItems.Where(c => !c.Installment.HasValue);
int lessonAchievement, tuitionAchievement;
void calcHeadquarterAchievement()
{
var voidTuition = voidPayment
.Join(models.GetTable <TuitionAchievement>(), p => p.PaymentID, t => t.InstallmentID, (p, t) => t);
lessonAchievement = tuitionItems.Where(t => SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (tuitionItems.Where(t => SessionScopeForAchievement.Contains(t.ELStatus)).Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100) ?? 0);
tuitionAchievement = achievementItems.Sum(a => a.ShareAmount) ?? 0;
IQueryable <TuitionAchievement> newContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => !t.Renewal.HasValue || t.Renewal != true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models);
IQueryable <TuitionAchievement> renewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => t.Renewal == true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models);
IQueryable <TuitionAchievement> installmentContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => t.Installment.HasValue),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models);
var revenueItem = item.MonthlyRevenueIndicator.Where(r => r.MonthlyRevenueGoal != null)
.Select(r => r.MonthlyRevenueGoal)
.FirstOrDefault();
if (revenueItem != null)
{
revenueItem.ActualCompleteLessonCount = tuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ tuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
revenueItem.ActualLessonAchievement = lessonAchievement;
revenueItem.ActualSharedAchievement = tuitionAchievement;
revenueItem.RenewContractCount = nonInstallmentItems.Where(c => c.Renewal == true).Count();
revenueItem.NewContractCount = nonInstallmentItems.Count() - revenueItem.RenewContractCount;
revenueItem.RenewContractSubtotal = nonInstallmentItems.Where(c => c.Renewal == true).Sum(c => c.TotalCost) ?? 0;
revenueItem.NewContractSubtotal = nonInstallmentItems.Sum(c => c.TotalCost) - revenueItem.RenewContractSubtotal;
revenueItem.InstallmentCount = installmentItems.Count();
revenueItem.InstallmentSubtotal = installmentItems.Sum(c => c.TotalCost) ?? 0;
revenueItem.ActualCompleteTSCount = tuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.體驗課程).Count()
+ tuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.體驗課程).Count();
revenueItem.ActualCompletePICount = tuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.自主訓練).Count()
+ tuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.自主訓練).Count();
revenueItem.NewContractAchievement = newContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.RenewContractAchievement = renewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.InstallmentAchievement = installmentContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.VoidShare = voidTuition.Sum(t => t.VoidShare) ?? 0;
models.SubmitChanges();
}
}
void calcBranchAchievement()
{
foreach (var branchIndicator in item.MonthlyBranchIndicator)
{
var voidTuition = voidPayment
.Join(models.GetTable <PaymentTransaction>().Where(t => t.BranchID == branchIndicator.BranchID), p => p.PaymentID, t => t.PaymentID, (p, t) => p)
.Join(models.GetTable <TuitionAchievement>(), p => p.PaymentID, t => t.InstallmentID, (p, t) => t);
var branchTuitionItems = tuitionItems.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
var branchAchievementItems = achievementItems.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
var branchContractItems = contractItems.Where(c => c.CourseContractExtension.BranchID == branchIndicator.BranchID);
var branchInstallmentItems = installmentItems.Where(c => c.CourseContractExtension.BranchID == branchIndicator.BranchID);
var branchNonInstallmentItems = nonInstallmentItems.Where(c => c.CourseContractExtension.BranchID == branchIndicator.BranchID);
IQueryable <TuitionAchievement> branchNewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => !t.Renewal.HasValue || t.Renewal != true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
IQueryable <TuitionAchievement> branchRenewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => t.Renewal == true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
IQueryable <TuitionAchievement> branchInstallmentContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => t.Installment.HasValue),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachWorkPlace == branchIndicator.BranchID);
var revenueItem = branchIndicator.MonthlyBranchRevenueIndicator
.Where(v => v.MonthlyBranchRevenueGoal != null)
.FirstOrDefault()?.MonthlyBranchRevenueGoal;
if (revenueItem != null)
{
lessonAchievement = branchTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (branchTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.ELStatus)).Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100) ?? 0);
tuitionAchievement = branchAchievementItems.Sum(a => a.ShareAmount) ?? 0;
revenueItem.ActualCompleteLessonCount = branchTuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ branchTuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
revenueItem.ActualLessonAchievement = lessonAchievement;
revenueItem.ActualSharedAchievement = tuitionAchievement;
revenueItem.RenewContractCount = branchNonInstallmentItems.Where(c => c.Renewal == true).Count();
revenueItem.NewContractCount = branchNonInstallmentItems.Count() - revenueItem.RenewContractCount;
revenueItem.RenewContractSubtotal = branchNonInstallmentItems.Where(c => c.Renewal == true).Sum(c => c.TotalCost) ?? 0;
revenueItem.NewContractSubtotal = branchNonInstallmentItems.Sum(c => c.TotalCost) - revenueItem.RenewContractSubtotal;
revenueItem.InstallmentCount = branchInstallmentItems.Count();
revenueItem.InstallmentSubtotal = branchInstallmentItems.Sum(c => c.TotalCost) ?? 0;
revenueItem.ActualCompleteTSCount = branchTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.體驗課程).Count()
+ branchTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.體驗課程).Count();
revenueItem.ActualCompletePICount = branchTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.自主訓練).Count()
+ branchTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.自主訓練).Count();
revenueItem.NewContractAchievement = branchNewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.RenewContractAchievement = branchRenewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.InstallmentAchievement = branchInstallmentContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
revenueItem.VoidShare = voidTuition.Sum(t => t.VoidShare) ?? 0;
models.SubmitChanges();
}
}
}
void calcCoachAchievement()
{
foreach (var coachIndicator in item.MonthlyCoachRevenueIndicator)
{
var voidTuition = voidPayment
.Join(models.GetTable <TuitionAchievement>()
.Where(t => t.CoachID == coachIndicator.CoachID),
p => p.PaymentID, t => t.InstallmentID, (p, t) => t);
var coachTuitionItems = tuitionItems.Where(t => t.AttendingCoach == coachIndicator.CoachID);
var coachAchievementItems = achievementItems.Where(t => t.CoachID == coachIndicator.CoachID);
var coachContractItems = contractItems.Where(c => c.FitnessConsultant == coachIndicator.CoachID);
IQueryable <CourseContract> coachInstallmentItems = coachContractItems.Where(c => c.Installment.HasValue);
IQueryable <CourseContract> coachNonInstallmentItems = coachContractItems.Where(c => !c.Installment.HasValue);
var coachSTItems = STItems.Where(t => t.AttendingCoach == coachIndicator.CoachID);
IQueryable <TuitionAchievement> coachNewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => !t.Renewal.HasValue || t.Renewal != true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachID == coachIndicator.CoachID);
IQueryable <TuitionAchievement> coachRenewContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => !t.Installment.HasValue)
.Where(t => t.Renewal == true),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachID == coachIndicator.CoachID);
IQueryable <TuitionAchievement> coachInstallmentContractAchievementItems = paymentItems
.Join(models.GetTable <ContractPayment>()
.Join(models.GetTable <CourseContract>()
.Where(t => t.Installment.HasValue),
c => c.ContractID, t => t.ContractID, (c, t) => c),
p => p.PaymentID, c => c.PaymentID, (p, c) => p)
.GetPaymentAchievement(models)
.Where(t => t.CoachID == coachIndicator.CoachID);
lessonAchievement = coachTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (coachTuitionItems.Where(t => SessionScopeForAchievement.Contains(t.ELStatus)).Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100) ?? 0);
tuitionAchievement = coachAchievementItems.Sum(a => a.ShareAmount) ?? 0;
coachIndicator.ActualCompleteLessonCount = coachTuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ coachTuitionItems.Where(t => SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
coachIndicator.ActualLessonAchievement = lessonAchievement;
coachIndicator.ActualRenewContractAchievement = coachRenewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
coachIndicator.ActualNewContractAchievement = coachNewContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
coachIndicator.ActualCompleteTSCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.體驗課程).Count()
+ coachTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.體驗課程).Count();
coachIndicator.ActualCompletePICount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.自主訓練).Count()
+ coachTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.自主訓練).Count();
coachIndicator.InstallmentAchievement = coachInstallmentContractAchievementItems.Sum(t => t.ShareAmount) ?? 0;
coachIndicator.RenewContractCount = coachNonInstallmentItems.Where(t => t.Renewal == true).Count();
coachIndicator.NewContractCount = coachNonInstallmentItems.Count() - coachIndicator.RenewContractCount;
coachIndicator.RenewContractSubtotal = coachNonInstallmentItems.Where(c => c.Renewal == true).Sum(c => c.TotalCost) ?? 0;
coachIndicator.NewContractSubtotal = coachNonInstallmentItems.Sum(c => c.TotalCost) - coachIndicator.RenewContractSubtotal;
coachIndicator.InstallmentCount = coachInstallmentItems.Count();
coachIndicator.InstallmentSubtotal = coachInstallmentItems.Sum(c => c.TotalCost) ?? 0;
if (calcAverage == true)
{
coachIndicator.AverageLessonPrice = item.CalculateAverageLessonPrice(models, coachIndicator.CoachID);
coachIndicator.LessonTuitionGoal = coachIndicator.CompleteLessonsGoal * coachIndicator.AverageLessonPrice;
}
coachIndicator.STCount = coachSTItems.Count();
coachIndicator.VoidShare = voidTuition.Sum(t => t.VoidShare) ?? 0;
models.SubmitChanges();
}
}
calcHeadquarterAchievement();
calcBranchAchievement();
calcCoachAchievement();
}
public ActionResult RestoreCoachRevenueIndicator(MonthlyCoachRevenueIndicatorQueryViewModel viewModel, int[] exclusiveCoach = null)
{
ViewBag.ViewModel = viewModel;
IQueryable <MonthlyIndicator> indicatorItems = models.GetTable <MonthlyIndicator>();
if (viewModel.DateFrom.HasValue)
{
viewModel.DateFrom = viewModel.DateFrom.Value.FirstDayOfMonth();
indicatorItems.Where(i => i.StartDate >= viewModel.DateFrom);
}
if (viewModel.DateTo.HasValue)
{
viewModel.DateTo = viewModel.DateTo.Value.FirstDayOfMonth().AddMonths(1);
indicatorItems.Where(i => i.StartDate < viewModel.DateTo);
}
IQueryable <ServingCoach> coachItems = models.GetTable <ServingCoach>();
if (viewModel.CoachID.HasValue)
{
coachItems = coachItems.Where(c => c.CoachID == viewModel.CoachID);
}
if (exclusiveCoach != null && exclusiveCoach.Length > 0)
{
coachItems = coachItems
.Where(c => !exclusiveCoach.Contains(c.CoachID))
.Where(c => c.CoachWorkplace.Any());
}
var forCoaches = coachItems.ToArray();
foreach (MonthlyIndicator indicator in indicatorItems)
{
AchievementQueryViewModel queryModel = new AchievementQueryViewModel
{
AchievementDateFrom = indicator.StartDate,
BypassCondition = true,
};
IQueryable <V_Tuition> lessonItems = queryModel.InquireAchievement(models);
lessonItems = lessonItems.Where(l => l.SettlementID.HasValue);
IQueryable <V_Tuition> tuitionItems = lessonItems;
PaymentQueryViewModel paymentQuery = new PaymentQueryViewModel
{
PayoffDateFrom = indicator.StartDate,
PayoffDateTo = indicator.EndExclusiveDate.AddDays(-1),
BypassCondition = true,
};
IQueryable <Payment> paymentItems = paymentQuery.InquirePayment(models);
IQueryable <TuitionAchievement> achievementItems = paymentItems.GetPaymentAchievement(models);
CourseContractQueryViewModel contractQuery = new CourseContractQueryViewModel
{
ContractQueryMode = Naming.ContractServiceMode.ContractOnly,
Status = (int)Naming.CourseContractStatus.已生效,
EffectiveDateFrom = indicator.StartDate,
EffectiveDateTo = indicator.EndExclusiveDate,
};
IQueryable <CourseContract> contractItems = contractQuery.InquireContract(models);
void calcCoachAchievement(MonthlyCoachRevenueIndicator coachIndicator)
{
int lessonAchievement, tuitionAchievement;
var coachTuitionItems = tuitionItems.Where(t => t.AttendingCoach == coachIndicator.CoachID);
var coachAchievementItems = achievementItems.Where(t => t.CoachID == coachIndicator.CoachID);
var coachContractItems = contractItems.Where(c => c.FitnessConsultant == coachIndicator.CoachID);
lessonAchievement = coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForAchievement.Contains(t.PriceStatus)).Sum(t => t.ListPrice * t.GroupingMemberCount * t.PercentageOfDiscount / 100) ?? 0;
lessonAchievement += (coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForAchievement.Contains(t.ELStatus)).Sum(l => l.EnterpriseListPrice * l.GroupingMemberCount * l.PercentageOfDiscount / 100) ?? 0);
tuitionAchievement = coachAchievementItems.Sum(a => a.ShareAmount) ?? 0;
coachIndicator.ActualCompleteLessonCount = coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForComleteLessonCount.Contains(t.PriceStatus)).Count()
+ coachTuitionItems.Where(t => BusinessConsoleExtensions.SessionScopeForComleteLessonCount.Contains(t.ELStatus)).Count();
coachIndicator.ActualLessonAchievement = lessonAchievement;
coachIndicator.ActualRenewContractAchievement = coachContractItems.Where(c => c.Renewal == true).Count();
coachIndicator.ActualNewContractAchievement = coachContractItems.Count() - coachIndicator.ActualRenewContractAchievement;
coachIndicator.ActualCompleteTSCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.體驗課程).Count()
+ coachTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.體驗課程).Count();
coachIndicator.ActualCompletePICount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.自主訓練).Count()
+ coachTuitionItems.Where(t => t.ELStatus == (int)Naming.LessonPriceStatus.自主訓練).Count();
coachIndicator.ATCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動防護課程).Count();
coachIndicator.ATAchievement = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動防護課程).Sum(t => t.ListPrice) ?? 0;
coachIndicator.SRCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動恢復課程).Count();
coachIndicator.SRAchievement = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.運動恢復課程).Sum(t => t.ListPrice) ?? 0;
coachIndicator.SDCount = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.營養課程).Count();
coachIndicator.SDAchievement = coachTuitionItems.Where(t => t.PriceStatus == (int)Naming.LessonPriceStatus.營養課程).Sum(t => t.ListPrice) ?? 0;
models.SubmitChanges();
}
foreach (var coach in forCoaches)
{
var item = models.GetTable <MonthlyCoachRevenueIndicator>()
.Where(c => c.PeriodID == indicator.PeriodID && c.CoachID == coach.CoachID).FirstOrDefault();
if (item == null)
{
var attendingLessons = models.GetTable <LessonTime>()
.Where(l => l.AttendingCoach == coach.CoachID)
.Where(l => l.ClassTime >= indicator.StartDate)
.Where(l => l.ClassTime < indicator.EndExclusiveDate);
if (attendingLessons.Any())
{
item = new MonthlyCoachRevenueIndicator
{
PeriodID = indicator.PeriodID,
CoachID = coach.CoachID,
BranchID = coach.CoachWorkplace.First().BranchID,
LevelID = coach.LevelID,
};
models.GetTable <MonthlyCoachRevenueIndicator>().InsertOnSubmit(item);
item.AchievementGoal = 0;
item.CompleteLessonsGoal = 0;
item.AverageLessonPrice = indicator.CalculateAverageLessonPrice(models, coach.CoachID);
item.BRCount = 0;
item.LessonTuitionGoal = 0;
models.SubmitChanges();
calcCoachAchievement(item);
}
}
}
}
return(Json(new { result = true }));
}
public static async Task <IQueryable <Payment> > InquirePaymentByCustomAsync(this PaymentQueryViewModel viewModel, SampleController <UserProfile> controller)
{
var ModelState = controller.ModelState;
var ViewBag = controller.ViewBag;
var HttpContext = controller.HttpContext;
var models = controller.DataSource;
ViewBag.ViewModel = viewModel;
var profile = await HttpContext.GetUserAsync();
bool hasConditon = false;
IQueryable <Payment> items = models.GetTable <Payment>()
.Where(p => p.TransactionType.HasValue)
.Where(p => p.PayoffAmount > 0)
.Where(p => p.Status.HasValue);
IQueryable <VoidPayment> voidItems = models.GetTable <VoidPayment>();
IQueryable <InvoiceItem> invoiceItems = models.GetTable <InvoiceItem>();
Expression <Func <Payment, bool> > queryExpr = c => false;
if (viewModel.PaymentID.HasValue)
{
items = items.Where(p => p.PaymentID == viewModel.PaymentID);
}
if (viewModel.BypassCondition == true)
{
hasConditon = true;
queryExpr = c => true;
}
viewModel.UserName = viewModel.UserName.GetEfficientString();
if (viewModel.UserName != null)
{
hasConditon = true;
queryExpr = queryExpr.Or(c => c.ContractPayment.CourseContract.CourseContractMember.Any(m => m.UserProfile.RealName.Contains(viewModel.UserName) || m.UserProfile.Nickname.Contains(viewModel.UserName)));
queryExpr = queryExpr.Or(c => c.TuitionInstallment.IntuitionCharge.RegisterLesson.UserProfile.RealName.Contains(viewModel.UserName));
queryExpr = queryExpr.Or(c => c.TuitionInstallment.IntuitionCharge.RegisterLesson.UserProfile.Nickname.Contains(viewModel.UserName));
}
if (!hasConditon)
{
viewModel.ContractNo = viewModel.ContractNo.GetEfficientString();
if (viewModel.ContractNo != null)
{
hasConditon = true;
var no = viewModel.ContractNo.Split('-');
if (no.Length > 1)
{
int.TryParse(no[1], out int seqNo);
queryExpr = queryExpr.Or(c => c.ContractPayment.CourseContract.ContractNo.StartsWith(no[0]) &&
c.ContractPayment.CourseContract.SequenceNo == seqNo);
}
else
{
queryExpr = queryExpr.Or(c => c.ContractPayment.CourseContract.ContractNo.StartsWith(viewModel.ContractNo));
}
}
}
if (!hasConditon)
{
if (viewModel.BranchID.HasValue)
{
hasConditon = true;
queryExpr = queryExpr.Or(c => c.PaymentTransaction.BranchID == viewModel.BranchID);
}
}
if (!hasConditon)
{
if (viewModel.HandlerID.HasValue)
{
hasConditon = true;
queryExpr = queryExpr.Or(c => c.HandlerID == viewModel.HandlerID);
voidItems = voidItems.Where(v => v.HandlerID == viewModel.HandlerID);
}
}
if (!hasConditon)
{
if (profile.IsAssistant() || profile.IsAccounting() || profile.IsServitor() || profile.IsOfficer())
{
}
else if (profile.IsManager() || profile.IsViceManager())
{
var branches = models.GetTable <BranchStore>().Where(b => b.ManagerID == profile.UID || b.ViceManagerID == profile.UID);
items = items
.Join(models.GetTable <PaymentTransaction>()
.Join(branches, p => p.BranchID, b => b.BranchID, (p, b) => p),
c => c.PaymentID, h => h.PaymentID, (c, h) => c);
}
else if (profile.IsCoach())
{
items = items.Where(p => p.HandlerID == profile.UID);
voidItems = voidItems.Where(v => v.HandlerID == profile.UID);
}
else
{
items = items.Where(p => false);
}
}
if (hasConditon)
{
items = items.Where(queryExpr);
}
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)
{
invoiceItems = invoiceItems.Where(c => c.InvoiceType == (byte)viewModel.InvoiceType);
}
viewModel.InvoiceNo = viewModel.InvoiceNo.GetEfficientString();
if (viewModel.InvoiceNo != null && Regex.IsMatch(viewModel.InvoiceNo, "[A-Za-z]{2}[0-9]{8}"))
{
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)
{
invoiceItems = invoiceItems.Where(c => c.InvoiceBuyer.ReceiptNo == viewModel.BuyerReceiptNo);
}
Expression <Func <InvoiceItem, bool> > queryInv = f => true;
bool dateQuery = false;
Expression <Func <Payment, bool> > dateItems = p => true;
IQueryable <InvoiceAllowance> allowanceItems = models.GetTable <InvoiceAllowance>();
IQueryable <InvoiceCancellation> cancelledItems = models.GetTable <InvoiceCancellation>();
if (viewModel.PayoffDateFrom.HasValue)
{
dateQuery = true;
dateItems = dateItems.And(p => p.PayoffDate >= viewModel.PayoffDateFrom);
queryInv = queryInv.And(i => i.InvoiceDate >= viewModel.PayoffDateFrom);
cancelledItems = cancelledItems.Where(i => i.CancelDate >= viewModel.PayoffDateFrom);
allowanceItems = allowanceItems.Where(a => a.AllowanceDate >= viewModel.PayoffDateFrom);
voidItems = voidItems.Where(v => v.VoidDate >= viewModel.PayoffDateFrom);
}
if (viewModel.PayoffDateTo.HasValue)
{
dateQuery = true;
dateItems = dateItems.And(i => i.PayoffDate < viewModel.PayoffDateTo.Value.AddDays(1));
queryInv = queryInv.And(i => i.InvoiceDate < viewModel.PayoffDateTo.Value.AddDays(1));
cancelledItems = cancelledItems.Where(i => i.CancelDate < viewModel.PayoffDateTo.Value.AddDays(1));
allowanceItems = allowanceItems.Where(a => a.AllowanceDate < viewModel.PayoffDateTo.Value.AddDays(1));
voidItems = voidItems.Where(v => v.VoidDate < viewModel.PayoffDateTo.Value.AddDays(1));
}
IQueryable <Payment> result = items.Join(invoiceItems, p => p.InvoiceID, i => i.InvoiceID, (p, i) => p);
if (dateQuery)
{
result = result.Join(models.GetTable <Payment>().Where(dateItems), p => p.PaymentID, d => d.PaymentID, (p, d) => p)
.Union(result.Join(models.GetTable <InvoiceItem>().Where(queryInv), p => p.InvoiceID, i => i.InvoiceID, (p, i) => p))
.Union(result.Join(allowanceItems, p => p.AllowanceID, a => a.AllowanceID, (p, a) => p)
.Union(result.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 = items
.Where(p => p.VoidPayment == null /*&& !p.AllowanceID.HasValue*/);
}
if (viewModel.HasAllowance == true)
{
result = result.Where(p => p.AllowanceID.HasValue);
}
return(result);
}
public static IQueryable <Payment> InquirePayment(this PaymentQueryViewModel viewModel, GenericManager <BFDataContext> models)
{
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 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);
}
