public void LoanPenaltyAccrual()
{
var launchDate = DateTime.Today;
var listOfLoans = _loanManager.GetListOfLoansToAccruePenalty(launchDate);
var em = new EventManager(User.CurrentUser);
using (var connection=_loanManager.GetConnection())
foreach (var item in listOfLoans)
{
var date = item.Value;
while (date < launchDate)
{
date = date.AddDays(1);
var penaltyEvent = new LoanPenaltyAccrualEvent
{
Penalty = _loanManager.GetDailyPenaltyForLoan(item.Key, date),
Date = date,
User = User.CurrentUser,
ContracId = item.Key
};
using (var transaction = connection.BeginTransaction())
try
{
em.AddLoanEvent(penaltyEvent, penaltyEvent.ContracId, transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
//throw;
}
}
}
}
public void GenerateLoanTransitionEvent()
{
var em = new EventManager(User.CurrentUser);
var date = _loanManager.LastLoanTransitionEventDate();
while (date.Date < DateTime.Now.Date)
{
date = date.AddDays(1);
using (var connection = _loanManager.GetConnection())
using (var transaction = connection.BeginTransaction())
try
{
var glll = _loanManager.GetListOfTransitionToLateLoan(date);
var transitionEventList = glll.Select(item => new LoanTransitionEvent
{
Code = "GLLL",
Date = date,
User = User.CurrentUser,
Amount = item.Value,
ContracId = item.Key
}).ToList();
//var llgl = _loanManager.GetListOfTransitionToGoodLoan(date);
//transitionEventList.AddRange(llgl.Select(item => new LoanTransitionEvent
// {
// Code = "LLGL",
// Date = date,
// User = User.CurrentUser,
// Amount = item.Value,
// ContracId = item.Key
// }));
foreach (var transitionEvent in transitionEventList)
em.AddLoanEvent(transitionEvent, transitionEvent.ContracId, transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public void LoanPenaltyAccrual()
{
const string q = @"SELECT al.id AS id,
al.amount * cr.non_repayment_penalties_based_on_initial_amount +
al.olb * cr.non_repayment_penalties_based_on_olb +
al.principal_due * cr.non_repayment_penalties_based_on_overdue_principal +
al.interest_due * cr.non_repayment_penalties_based_on_overdue_interest AS penalty,
al.late_days
FROM dbo.ActiveLoans(@date, 0) AS al
LEFT JOIN dbo.Credit AS cr ON cr.id=al.id
WHERE al.late_days > 0";
var user = ServicesProvider.GetInstance().GetUserServices().Find(1);
var em = new EventManager(user);
var date = LastPenaltyAccrualEventDate();
while (date.Date < DateTime.Now.Date)
{
date = date.AddDays(1);
using (var connection = _loanManager.GetConnection())
using (var transaction = connection.BeginTransaction())
try
{
var penaltyEventList = new List<LoanPenaltyAccrualEvent>();
using (var c = new OpenCbsCommand(q, connection, transaction))
{
c.AddParam("@date", date);
using (var r = c.ExecuteReader())
{
while (r.Read())
{
var loanId = r.GetInt("id");
var penalty = r.GetDouble("penalty");
var penaltyEvent = new LoanPenaltyAccrualEvent
{
Date = date,
User = user,
Penalty = Convert.ToDecimal(penalty),
ContracId = loanId
};
penaltyEventList.Add(penaltyEvent);
}
}
foreach (var penaltyEvent in penaltyEventList)
em.AddLoanEvent(penaltyEvent, penaltyEvent.ContracId, transaction);
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public void GenerateLoanTransitionEvent()
{
const string glll = @"SELECT id, olb, late_days
FROM dbo.ActiveLoans(@date, 0)
WHERE late_days = 1";
const string llgl = @"SELECT a1.id,a1.olb,a1.late_days
FROM dbo.ActiveLoans(@date, 0) a1
INNER JOIN dbo.ActiveLoans(DATEADD(dd,-1,@date), 0) a2 ON a2.id = a1.id
WHERE a1.late_days = 0 and a2.late_days>0";
var user = ServicesProvider.GetInstance().GetUserServices().Find(1);
var em = new EventManager(user);
var date = LastLoanTransitionEventDate();
while (date.Date < DateTime.Now.Date)
{
date = date.AddDays(1);
using (var connection = _loanManager.GetConnection())
using (var transaction = connection.BeginTransaction())
try
{
var transitionEventList = new List<LoanTransitionEvent>();
using (var c = new OpenCbsCommand(glll, connection, transaction))
{
c.AddParam("@date", date);
using (var r = c.ExecuteReader())
{
while (r.Read())
{
var loanId = r.GetInt("id");
var amount = r.GetDecimal("olb");
var transitionEvent = new LoanTransitionEvent
{
Code = "GLLL",
Date = date,
User = user,
Amount = amount,
ContracId = loanId
};
transitionEventList.Add(transitionEvent);
}
}
}
using (var c = new OpenCbsCommand(llgl, connection, transaction))
{
c.AddParam("@date", date);
using (var r = c.ExecuteReader())
{
while (r.Read())
{
var loanId = r.GetInt("id");
var amount = r.GetDecimal("olb");
var transitionEvent = new LoanTransitionEvent
{
Code = "LLGL",
Date = date,
User = user,
Amount = amount,
ContracId = loanId
};
transitionEventList.Add(transitionEvent);
}
}
}
foreach (var transitionEvent in transitionEventList)
em.AddLoanEvent(transitionEvent, transitionEvent.ContracId, transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public void LoanInterestAccrual()
{
const string q = @"WITH summary AS (SELECT al.id
,inst.interest
,inst.expected_date
,ROW_NUMBER() OVER(PARTITION BY al.id
ORDER BY al.id) AS rk
FROM Activeloans(@date,0) AS al
LEFT JOIN InstallmentSnapshot(@date) AS inst ON inst.contract_id=al.id
WHERE @date < inst.expected_date)
SELECT s.id, s.interest, s.expected_date
FROM summary s
WHERE s.rk = 1";
const string q1 = @"SELECT TOP 1 date FROM (
SELECT MAX(inst.expected_date) AS date
FROM Activeloans(@date, 0) AS al
LEFT JOIN InstallmentSnapshot(@date) AS inst ON inst.contract_id=al.id
WHERE @date >= inst.expected_date AND al.id=@contractId
group by al.id
UNION ALL
SELECT start_date
FROM Contracts
WHERE id=@contractId) t ORDER by date desc";
var user = ServicesProvider.GetInstance().GetUserServices().Find(1);
var em = new EventManager(user);
var date = LastInterestAccrualEventDate();
while (date.Date < DateTime.Now.Date)
{
date = date.AddDays(1);
using (var connection = _loanManager.GetConnection())
using (var transaction = connection.BeginTransaction())
try
{
var interestEventList = new List<LoanInterestAccrualEvent>();
using (var c = new OpenCbsCommand(q, connection, transaction))
{
c.AddParam("@date", date);
using (var r = c.ExecuteReader())
{
while (r.Read())
{
var loanId = r.GetInt("id");
var interest = r.GetDecimal("interest");
var expectedDate = r.GetDateTime("expected_date");
var interestEvent = new LoanInterestAccrualEvent
{
Date = date,
User = user,
Interest = interest,
InstallmentExpectedDate = expectedDate,
ContracId = loanId
};
interestEventList.Add(interestEvent);
}
}
}
foreach (var interestEvent in interestEventList)
{
var previousInstallmentDate = new DateTime();
using (var c = new OpenCbsCommand(q1, connection, transaction))
{
c.AddParam("@date", date);
c.AddParam("@contractId", interestEvent.ContracId);
using (var r = c.ExecuteReader())
{
r.Read();
previousInstallmentDate = r.GetDateTime("date");
}
}
var passedPeriod = interestEvent.Date - previousInstallmentDate;
if (passedPeriod.Days > 30)
interestEvent.Interest = 0;
else if (interestEvent.Date == interestEvent.InstallmentExpectedDate)
interestEvent.Interest -= GetSumOfAccruedInterests(interestEvent.Date,
previousInstallmentDate,
interestEvent.ContracId);
else
interestEvent.Interest = interestEvent.Interest.Value/30;
em.AddLoanEvent(interestEvent, interestEvent.ContracId, transaction);
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
