private void NL_SendMailAndMarkDB(SafeReader sr) { int customerID = sr["CustomerId"]; long loanID = sr["LoanID"]; DateTime plannedDate = sr["PlannedDate"]; int Xdays = sr["Xdays"]; // 2|5 indicate which type of notification to send long loanScheduleId = sr["LoanScheduleID"]; NL_AddLog(LogType.Info, "NL_SendMailAndMarkDB", new object[] { customerID, loanID, Xdays, loanScheduleId, plannedDate, nowTime }, null, null, null); GetLoanState loanState = new GetLoanState(customerID, loanID, nowTime); loanState.Execute(); var nlModel = loanState.Result; NL_LoanSchedules theSchedule = null; nlModel.Loan.Histories.ForEach(h => theSchedule = h.Schedule.FirstOrDefault(s => s.LoanScheduleID == loanScheduleId)); if (theSchedule == null) { Log.Debug("Schedule for loan {0}, old {1} not found", loanID, sr["OldLoanID"]); NL_AddLog(LogType.Warn, "Schedule not found", new object[] { customerID, loanID, Xdays, loanScheduleId, plannedDate, nowTime }, null, null, null); return; } var variables = new Dictionary <string, string> { { "FirstName", sr["FirstName"] }, { "AmountDueScalar", theSchedule.AmountDue.ToString(CultureInfo.InvariantCulture) }, { "Date", FormattingUtils.FormatDateToString(plannedDate) }, { "DebitCard", sr["CreditCardNo"] } }; var templateName = (Xdays == 2) ? "Mandrill - 2 days notice" : "Mandrill - 5 days notice"; XDaysDueMails xDaysDueMails = new XDaysDueMails(customerID, templateName, variables); //DON"T DELETE - in the futere email will be sent from here.!!! //xDaysDueMails.Execute(); var parameterName = (Xdays == 2) ? "TwoDaysDueMailSent" : "FiveDaysDueMailSent"; // new SP var queryParameteres = new QueryParameter[] { new QueryParameter("LoanScheduleID", loanScheduleId), new QueryParameter(parameterName, true) }; NL_AddLog(LogType.Info, "Processing", new object[] { customerID, loanID, Xdays, loanScheduleId, plannedDate, nowTime }, new object[] { theSchedule, parameterName, templateName }, null, null); DB.ExecuteNonQuery("NL_LoanSchedulesUpdate", CommandSpecies.StoredProcedure, queryParameteres); Log.Info("update loanID {2} scheduleID {1} x days due for customer {0}", customerID, loanScheduleId, loanID); }
public void TestScheduleFormat() { var x1 = new NL_LoanSchedules() { AmountDue = 55m, Balance = 22m, Fees = 30.77m, //FeesPaid = 20.99m, Interest = 12m, InterestRate = 2.25m, //InterestPaid = 1.55m, LoanHistoryID = 1, LoanScheduleID = 0, LoanScheduleStatusID = 3, PlannedDate = new DateTime(2014, 10, 19) }; m_oLog.Debug(x1.ToString()); }
/// <summary> /// calculates schedules according to keren shava formula (EqualPrincipal) /// </summary> private void CalculateScheduleEqualPrincipalFormula() { int interestOnlyRepayments = Calculator.currentHistory.InterestOnlyRepaymentCount; Calculator.currentHistory.Schedule.Clear(); RepaymentIntervalTypes intervalType = (RepaymentIntervalTypes)Calculator.currentHistory.RepaymentIntervalTypeID; int principalPayments = Calculator.currentHistory.RepaymentCount - interestOnlyRepayments; decimal iPrincipal = Math.Floor(Calculator.currentHistory.Amount / principalPayments); decimal iFirstPrincipal = Calculator.currentHistory.Amount - iPrincipal * (principalPayments - 1); List <decimal> discounts = (WorkingModel.Offer != null && WorkingModel.Offer.DiscountPlan != null) ? WorkingModel.Offer.DiscountPlan : null; if (discounts != null) { discounts.ForEach(d => Log.Debug(d)); int discountCount = discounts.Count; decimal balance = Calculator.currentHistory.Amount; Calculator.schedule = new List <NL_LoanSchedules>(); // create Schedule for (int i = 1; i <= Calculator.currentHistory.RepaymentCount; i++) { decimal principal = iPrincipal; if (i <= interestOnlyRepayments) { principal = 0; } else if (i == interestOnlyRepayments + 1) { principal = iFirstPrincipal; } decimal r = Calculator.currentHistory.InterestRate; if (i <= discountCount) { r *= (1 + discounts[i - 1]); } DateTime plannedDate = Calculator.AddRepaymentIntervals(i - 1, Calculator.currentHistory.RepaymentDate, intervalType).Date; DateTime prevScheduleDate = Calculator.PreviousScheduleDate(plannedDate, intervalType); NL_LoanSchedules item = new NL_LoanSchedules() { InterestRate = r, PlannedDate = plannedDate.Date, Principal = principal, // intervals' principal LoanScheduleStatusID = (int)NLScheduleStatuses.StillToPay, Position = i, Balance = balance, //local open principal, scheduled Interest = Calculator.InterestBtwnDates(plannedDate, prevScheduleDate, balance) }; balance -= principal; Calculator.schedule.Add(item); Calculator.currentHistory.Schedule.Add(item); } // for } }
public void CreateSchedule() { Scheduleses = new List <NL_LoanSchedules>(); NL_LoanSchedules loanSchedule = new NL_LoanSchedules(); var row = 14; while (!string.IsNullOrEmpty(Worksheet.Cells[row, 1].Text)) { //"Payment Number if (!string.IsNullOrEmpty(Worksheet.Cells[row, 1].Text)) { loanSchedule.Position = Convert.ToInt32(Worksheet.Cells[row, 1].Text); } //Payment Date if (!string.IsNullOrEmpty(Worksheet.Cells[row, 2].Text)) { loanSchedule.PlannedDate = DateTime.ParseExact(Worksheet.Cells[row, 2].Text, new[] { "MM/dd/yyyy", "MM/dd/yy" }, new CultureInfo("en-US"), DateTimeStyles.None); } //Begining Balance if (!string.IsNullOrEmpty(Worksheet.Cells[row, 3].Text)) { var beginingBalance = Worksheet.Cells[row, 3].Text; } //Scheduale Payment if (!string.IsNullOrEmpty(Worksheet.Cells[row, 4].Text)) { var schedualePayment = Worksheet.Cells[row, 4].Text; } //Extra Payment //loanSchedule.FeesPaid = Convert.ToDecimal(Worksheet.Cells[row, 5].Text); //TotalPayment if ((!string.IsNullOrEmpty(Worksheet.Cells[row, 6].Text) && !Worksheet.Cells[row, 6].Text.Contains("$-"))) { loanSchedule.AmountDue = Convert.ToDecimal(Worksheet.Cells[row, 6].Text.Replace("$", String.Empty)); } //Principal if ((!string.IsNullOrEmpty(Worksheet.Cells[row, 7].Text) && !Worksheet.Cells[row, 7].Text.Contains("$-"))) { loanSchedule.PrincipalPaid = Convert.ToDecimal(Worksheet.Cells[row, 7].Text.Replace("$", String.Empty)); } //Interest if ((!string.IsNullOrEmpty(Worksheet.Cells[row, 8].Text) && !Worksheet.Cells[row, 8].Text.Contains("$-"))) { loanSchedule.InterestPaid = Convert.ToDecimal(Worksheet.Cells[row, 8].Text.Replace("$", String.Empty)); } //Ending Balance if ((!string.IsNullOrEmpty(Worksheet.Cells[row, 9].Text) && !Worksheet.Cells[row, 9].Text.Contains("$-"))) { var endingBalance = Worksheet.Cells[row, 9].Text.Replace("$", String.Empty); } //Cumulative Interest if ((!string.IsNullOrEmpty(Worksheet.Cells[row, 10].Text) && !Worksheet.Cells[row, 10].Text.Contains("$-"))) { loanSchedule.InterestOP = Convert.ToDecimal(Worksheet.Cells[row, 10].Text.Replace("$", String.Empty)); } Scheduleses.Add(loanSchedule); row++; } //try { // var calcOptions = new ExcelCalculationOption() { // AllowCirculareReferences = true // }; // Worksheet.Cells[row, 1].Calculate(calcOptions); // Worksheet.Cells[row, 2].Calculate(calcOptions); // Worksheet.Cells[row, 3].Calculate(calcOptions); // Worksheet.Cells[row, 4].Calculate(calcOptions); // Worksheet.Cells[row, 5].Calculate(calcOptions); // Worksheet.Cells[row, 6].Calculate(calcOptions); // Worksheet.Cells[row, 7].Calculate(calcOptions); // Worksheet.Cells[row, 8].Calculate(calcOptions); // Worksheet.Cells[row, 9].Calculate(calcOptions); // Worksheet.Cells[row, 10].Calculate(calcOptions); //} //catch (Exception) { //} }
} // constructor /// <exception cref="OverflowException">Condition. </exception> public virtual void Execute() { // not accepted rollover if (Calculator.acceptedRollover.Rollover == null) { Log.Alert("RolloverRescheduling: no accepted rollover"); return; } // not accepted rollover if (!Calculator.acceptedRollover.Rollover.IsAccepted || !Calculator.acceptedRollover.Rollover.CustomerActionTime.HasValue) { Log.Alert("RolloverRescheduling: rollover not accepted (paid). {0}", Calculator.acceptedRollover.Rollover); return; } // rollover proceeseed if (Calculator.acceptedRollover.Rollover.CustomerActionTime.Value.Date == Calculator.currentHistory.EventTime.Date || Calculator.acceptedRolloverProcessed) { Log.Debug("RolloverRescheduling: History ({0}) for rollover {1:d}, rolloverID {2} already exists", Calculator.currentHistory.LoanHistoryID, Calculator.acceptedRollover.Rollover.CustomerActionTime.Value, Calculator.acceptedRollover.Rollover.LoanRolloverID); return; } var rolloverPayment = Calculator.events.FirstOrDefault(p => p.Payment != null && p.Payment.PaymentTime.Date.Equals(Calculator.acceptedRollover.Rollover.CustomerActionTime.Value.Date) && p.Payment.PaymentDestination.Equals(NLPaymentDestinations.Rollover.ToString())); // rollover not paid if (rolloverPayment == null) { Log.Alert("RolloverRescheduling: rollover payment not found. {0}", Calculator.acceptedRollover.Rollover); return; } DateTime acceptionTime = Calculator.acceptedRollover.Rollover.CustomerActionTime.Value.Date; NL_LoanHistory lastHistory = WorkingModel.Loan.LastHistory(); // 1. create new history NL_LoanHistory newHistory = new NL_LoanHistory { LoanID = lastHistory.LoanID, LoanLegalID = lastHistory.LoanLegalID, AgreementModel = lastHistory.AgreementModel, Agreements = lastHistory.Agreements, InterestRate = lastHistory.InterestRate, RepaymentIntervalTypeID = lastHistory.RepaymentIntervalTypeID, UserID = WorkingModel.CustomerID, Description = "accept rollover", Amount = Calculator.currentOpenPrincipal, EventTime = acceptionTime }; RepaymentIntervalTypes intervalType = (RepaymentIntervalTypes)lastHistory.RepaymentIntervalTypeID; int removedItems = 0; newHistory.RepaymentDate = DateTime.MinValue; // 2. mark removed schedules + add new schedules foreach (NL_LoanSchedules s in Calculator.schedule.Where(s => s.PlannedDate >= acceptionTime)) { s.SetStatusOnRescheduling(); s.ClosedTime = acceptionTime; removedItems++; DateTime plannedDate = Calculator.AddRepaymentIntervals(1, s.PlannedDate, intervalType); if (newHistory.RepaymentDate.Equals(DateTime.MinValue)) { newHistory.RepaymentDate = plannedDate; } // add new schedule instead of removed NL_LoanSchedules newSchedule = new NL_LoanSchedules() { LoanScheduleID = 0, LoanScheduleStatusID = (int)NLScheduleStatuses.StillToPay, Position = s.Position, PlannedDate = plannedDate, Principal = s.Principal, // (s.Principal - s.PrincipalPaid), InterestRate = s.InterestRate, TwoDaysDueMailSent = false, //s.TwoDaysDueMailSent, FiveDaysDueMailSent = false, //s.FiveDaysDueMailSent }; Log.Debug("schedule {0} replaced by {1}", s, newSchedule); newHistory.Schedule.Add(newSchedule); } newHistory.RepaymentCount = removedItems; WorkingModel.Loan.Histories.Add(newHistory); //List<NL_LoanFees> replacedDistributedFees = new List<NL_LoanFees>(); bool distributedFees = false; // 3. mark removed distributed fees + add new distributed fees foreach (NL_LoanFees fee in Calculator.distributedFeesList.Where(f => f.AssignTime.Date >= acceptionTime)) { fee.DisabledTime = acceptionTime; fee.Notes = "disabled on rollover"; fee.DeletedByUserID = WorkingModel.UserID ?? 1; distributedFees = true; //NL_LoanFees newFee = new NL_LoanFees() { // LoanFeeID = 0, // Amount = fee.Amount, // AssignTime = Calculator.AddRepaymentIntervals(1, fee.AssignTime, intervalType), // LoanID = WorkingModel.Loan.LoanID, // LoanFeeTypeID = fee.LoanFeeTypeID, // AssignedByUserID = fee.AssignedByUserID, // CreatedTime = acceptionTime, // Notes = fee.Notes //}; //Log.Debug("fee {0} replaced by {1}", fee, newFee); //replacedDistributedFees.Add(newFee); } //Calculator.distributedFeesList.AddRange(replacedDistributedFees); //WorkingModel.Loan.Fees.AddRange(replacedDistributedFees); if (distributedFees) { // offer-fees NL_OfferFees offerFees = WorkingModel.Offer.OfferFees.FirstOrDefault(); if (offerFees != null && offerFees.DistributedPartPercent != null && (decimal)offerFees.DistributedPartPercent == 1) { var feeCalculator = new SetupFeeCalculator(offerFees.Percent, null); decimal servicingFeeAmount = feeCalculator.Calculate(newHistory.Amount).Total; decimal servicingFeePaidAmount = WorkingModel.Loan.Fees.Where(f => f.LoanFeeTypeID == (int)NLFeeTypes.ServicingFee).Sum(f => f.PaidAmount); Log.Debug("servicingFeeAmount: {0}, servicingFeePaidAmount: {1}", servicingFeeAmount, servicingFeePaidAmount); // new "spreaded" amount Calculator.AttachDistributedFeesToLoanBySchedule(WorkingModel, (servicingFeeAmount - servicingFeePaidAmount), acceptionTime); } } // TODO could be reseted at all?????????????? // reset paid amount for deleted/closed schedules and disabled distributed fees foreach (NL_Payments p in WorkingModel.Loan.Payments) { foreach (NL_LoanSchedulePayments sp in p.SchedulePayments) { foreach (NL_LoanSchedules s in Calculator.schedule.Where(s => s.IsDeleted())) { if (s.LoanScheduleID == sp.LoanScheduleID) { sp.ResetInterestPaid = sp.PrincipalPaid; sp.ResetPrincipalPaid = sp.PrincipalPaid; sp.PrincipalPaid = 0; sp.InterestPaid = 0; } } } foreach (NL_LoanFeePayments fp in p.FeePayments) { foreach (NL_LoanFees f in Calculator.distributedFeesList.Where(f => f.DisabledTime.Equals(acceptionTime))) { if (f.LoanFeeID == fp.LoanFeeID) { fp.ResetAmount = fp.Amount; fp.Amount = 0; f.PaidAmount -= (decimal)fp.ResetAmount; } } } } Calculator.acceptedRolloverProcessed = true; }
// installment processed at the end of day public LoanEvent(DateTime date, NL_LoanSchedules scheduleItem, int priority = 0) : this(new DateTime(date.Year, date.Month, date.Day, 23, 59, 59), priority) { ScheduleItem = scheduleItem; }
} //Execute private void NLMarkLoanAsLate(NLLateLoansJobModel model) { Log.Debug("NLMarkLoanAsLate: {0}", model.ToString()); NL_AddLog(LogType.Info, "NLMarkLoanAsLate", model, null, null, null); if (model.LoanStatus != NLLoanStatuses.Late) { // DON'T REMOVE!!!!!!!!!! SHOULD BE UNCOMMENT AFTER "old" job cancellation //DB.ExecuteNonQuery( // "UpdateCustomer", CommandSpecies.StoredProcedure, // new QueryParameter("CustomerId", customerId), // new QueryParameter("LoanStatus", "Late"), // new QueryParameter("IsWasLate", true) // ); DB.ExecuteNonQuery("NL_LoanUpdate", CommandSpecies.StoredProcedure, new QueryParameter("LoanID", model.LoanID), new QueryParameter("LoanStatusID", (int)NLLoanStatuses.Late)); Log.Debug("Updating nlloan {0} to late", model.LoanID); NL_AddLog(LogType.Info, "updating loan to late", model, model.LoanID, null, null); } if (model.ScheduleStatus != NLScheduleStatuses.Late) { DB.ExecuteNonQuery("NL_LoanSchedulesUpdate", CommandSpecies.StoredProcedure, new QueryParameter("LoanScheduleID", model.LoanScheduleID), new QueryParameter("LoanScheduleStatusID", (int)NLScheduleStatuses.Late)); Log.Debug("Updating schedule {0} to late", model.LoanScheduleID); NL_AddLog(LogType.Info, "updating schedule to late", model, model.LoanScheduleID, null, null); } GetLoanState loanState = new GetLoanState(model.CustomerID, model.LoanID, now); loanState.Execute(); decimal interest = loanState.Result.Interest; // TODO check: real unpaid interest for this date here if (!LateFeesAllowed(loanState.Result.Loan.LoanOptions, model.LoanID)) { Log.Debug("late fees for loan {0} not allowed", model.LoanID); NL_AddLog(LogType.Info, "Late fees not allowed", model, loanState.Result.Loan.LoanOptions, null, null); return; } int daysLate = (int)(now - model.PlannedDate).TotalDays; int feeAmount; NLFeeTypes feeType; NL_Model.CalculateFee(daysLate, interest, out feeAmount, out feeType); Log.Debug("calculated feeAmount={0}, FeeType={1} daysLate={2} schedule={3} loan={4}", feeAmount, (int)feeType, daysLate, model.LoanScheduleID, model.LoanID); NL_AddLog(LogType.Info, "calculated fee data", model, new object[] { daysLate, interest, feeAmount, feeType }, null, null); if (feeType != NLFeeTypes.None) { // check if this fee type for this schedule already assigned // get next schedule date NL_LoanSchedules nextSchedule = null; loanState.Result.Loan.Histories.ForEach(h => nextSchedule = h.Schedule.FirstOrDefault(s => s.PlannedDate > model.PlannedDate)); DateTime dateTo = nextSchedule == null ? model.PlannedDate : nextSchedule.PlannedDate; // between this and nect schedules same fee alread assigned if (loanState.Result.Loan.Fees.FirstOrDefault(f => f.LoanFeeTypeID == (int)feeType && f.AssignTime.Date <= dateTo.Date && f.AssignTime.Date >= model.PlannedDate) != null) { Log.Debug("NL: Tried to apply already assigned late charge for customer {0} loan {1}: feetype: {2}", model.CustomerID, model.LoanID, feeType); NL_AddLog(LogType.Info, "LatefeeExists", model, feeType, null, null); return; } NL_LoanFees lateFee = new NL_LoanFees() { AssignedByUserID = 1, LoanID = model.LoanID, Amount = feeAmount, AssignTime = now.Date, CreatedTime = now, LoanFeeTypeID = (int)feeType, Notes = daysLate + " days late;schedule " + model.LoanScheduleID, DeletedByUserID = null, DisabledTime = null }; var nlfList = new List <NL_LoanFees>(); nlfList.Add(lateFee); try { DB.ExecuteNonQuery("NL_LoanFeesSave", CommandSpecies.StoredProcedure, DB.CreateTableParameter <NL_LoanFees>("Tbl", nlfList)); Log.Debug("NL: Applied late charge for customer {0} loan {1}: data: {2}", model.CustomerID, model.LoanID, lateFee); NL_AddLog(LogType.Info, "Latefee", model, lateFee, null, null); // ReSharper disable once CatchAllClause } catch (Exception ex) { Log.Alert("NL: Failed to add late fee for customer {0} loan {1}: data: {2}", model.CustomerID, model.LoanID, lateFee); NL_AddLog(LogType.Error, "Failed to add late fee", model, lateFee, ex.ToString(), ex.StackTrace); } } } //NL_MarkLoanAsLate