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); }