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
