public void TestFixtureSetUp()
{
_eventStock = new EventStock();
_eventStock.Add(new RepaymentEvent());
_eventStock.Add(new BadLoanRepaymentEvent());
_eventStock.Add(new StatisticalProvisionningEvent());
_eventStock.Add(new LoanDisbursmentEvent());
_eventStock.Add(new RescheduleLoanEvent());
_eventStock.Add(new WriteOffEvent());
}
public void PostEvents(List<Loan> loans, EventStock eventStock)
{
using (SqlConnection conn = _savingEventManager.GetConnection())
using (SqlTransaction sqlTransaction = conn.BeginTransaction())
{
try
{
foreach (Event e in eventStock)
{
if (e is SavingEvent) continue;
Loan loan = new Loan();
foreach (Loan l in loans)
{
if (l.Id == e.ContracId)
{
loan = l;
}
}
if (loan.Id > 0)
_ePs.FireEvent(e, loan, sqlTransaction);
}
sqlTransaction.Commit();
}
catch (Exception ex)
{
if (sqlTransaction != null)
sqlTransaction.Rollback();
throw ex;
}
}
}
public void Add(EventStock eventStock)
{
_list.AddRange(eventStock._list);
SortEventsById();
}
public Loan SaveInstallmentsAndRepaymentEvents(Loan loan, IList<Installment> newInstallments, EventStock eventStock)
{
var repayEvent = eventStock.GetRepaymentEvents().First(i => !i.IsFired).Copy();
var amount =
eventStock.GetRepaymentEvents()
.Where(i => !i.IsFired)
.Sum(i => i.Commissions.Value + i.Penalties.Value + i.Interests.Value + i.Principal.Value);
using (var sqlTransaction = _loanManager.GetConnection().BeginTransaction())
{
try
{
if (ApplicationSettings.GetInstance(User.CurrentUser.Md5).UseMandatorySavingAccount)
{
var saving =
(from item in loan.Project.Client.Savings where item.Product.Code == "default" select item)
.FirstOrDefault();
if (saving == null)
{
MessageBox.Show("Make sure that client has default saving account");
return loan;
}
saving = ServicesProvider.GetInstance().GetSavingServices().GetSaving(saving.Id);
var balance = saving.GetBalance(repayEvent.Date);
if (amount > balance)
{
MessageBox.Show("Balance is not enough to repay");
return loan;
}
if (amount <= 0)
return loan;
ServicesProvider.GetInstance()
.GetSavingServices()
.Withdraw(saving, repayEvent.Date, amount,
"Withdraw for loan repayment " + loan.Code, User.CurrentUser,
new Teller(),
sqlTransaction);
eventStock.GetRepaymentEvents().First(i => !i.IsFired).Comment =
saving.Events.Last().Id.ToString(CultureInfo.InvariantCulture);
}
loan.Events = eventStock;
_ePs.FireEvent(repayEvent, loan, sqlTransaction);
ArchiveInstallments(loan, repayEvent, sqlTransaction);
loan.InstallmentList = newInstallments.ToList();
foreach (var installment in newInstallments)
_instalmentManager.UpdateInstallment(installment, loan.Id, repayEvent.Id, sqlTransaction);
if (newInstallments.All(installment => installment.IsRepaid))
{
_ePs.FireEvent(loan.GetCloseEvent(TimeProvider.Now), loan, sqlTransaction);
loan.Closed = true;
loan.ContractStatus = OContractStatus.Closed;
_loanManager.UpdateLoan(loan, sqlTransaction);
}
var repaymentEvents = (from item in loan.Events.GetRepaymentEvents()
where item.ParentId == repayEvent.Id || item.Id == repayEvent.Id
select item).ToList();
var listOfRble = (from item in repaymentEvents where item.Code == "RBLE" select item).ToList();
var listOfRgle = repaymentEvents.Except(listOfRble).ToList();
if (repayEvent.Code == "RBLE")
CallInterceptor(new Dictionary<string, object>
{
{"Loan", loan},
{
"Event", new BadLoanRepaymentEvent
{
Code = "RBLE",
Principal = listOfRble.Sum(item => item.Principal.Value),
Interests = listOfRble.Sum(item => item.Interests.Value),
Commissions = listOfRble.Sum(item => item.Commissions.Value),
Penalties = listOfRble.Sum(item => item.Fees.Value),
Id = repayEvent.Id,
Date = repayEvent.Date
}
},
{"SqlTransaction", sqlTransaction}
});
CallInterceptor(new Dictionary<string, object>
{
{"Loan", loan},
{
"Event", new RepaymentEvent
{
Code = "RGLE",
Principal = listOfRgle.Sum(item => item.Principal.Value),
Interests = listOfRgle.Sum(item => item.Interests.Value),
Commissions = listOfRgle.Sum(item => item.Commissions.Value),
Penalties = listOfRgle.Sum(item => item.Fees.Value),
Id = repayEvent.Id,
Date = repayEvent.Date
}
},
{"SqlTransaction", sqlTransaction}
});
sqlTransaction.Commit();
}
catch (Exception)
{
sqlTransaction.Rollback();
throw;
}
}
if(loan.Closed)
SetClientStatus(loan, loan.Project.Client);
return loan;
}
private EventStock GenerateRepaymentEvents(CreditContractRepayment cCr,
DateTime payDate,
OCurrency penaltiesEvent,
OCurrency commissionsEvent,
OCurrency interestEvent,
OCurrency interestPrepayment,
OCurrency principalEvent,
int pastDueDays,
OPaymentType paymentType,
bool isPending,
int instNumber,
PaymentMethod paymentMethod,
bool overridePenalty,
OCurrency manualPenalty,
OCurrency manualInterestEvent,
ref RepaymentEvent totalEvent)
{
RepaymentEvent rpEvent;
totalEvent.Penalties = 0;
totalEvent.Commissions = 0;
EventStock listOfLoanEvents = new EventStock();
#region Event identification
//////////////////////////////////////////////////////////////////////////////////////////////////////////
if (cCr.PaidIstallments.Count > 0)
{
List<Installment> lateInstallments = cCr.PaidIstallments.FindAll(i => i.CalculatedPenalty > 0);
Installment lastLate = 0 == lateInstallments.Count ? null : lateInstallments.OrderBy(i => i.Number).Last();
foreach (Installment paidInstallment in cCr.PaidIstallments)
{
int number = paidInstallment.Number;
OCurrency rpeCapital = 0;
OCurrency rpeInterests = 0;
OCurrency rpeCommission = 0;
OCurrency rpePenalty = 0;
int overdueDays = (payDate.Date - paidInstallment.ExpectedDate).Days > 0
? (payDate.Date - paidInstallment.ExpectedDate).Days
: 0;
foreach (RepaymentEvent rpe in Events.GetLoanRepaymentEvents())
{
if (rpe.InstallmentNumber == paidInstallment.Number && !rpe.Deleted)
{
if (rpe.RepaymentType != OPaymentType.PersonTotalPayment)
{
if ((rpe.RepaymentType != OPaymentType.PartialPayment)
&& (rpe.RepaymentType != OPaymentType.ProportionalPayment))
{
rpeCapital += rpe.Principal;
rpeInterests += rpe.Interests;
}
if (rpe.RepaymentType == OPaymentType.PartialPayment && paymentType == OPaymentType.TotalPayment)
{
rpeCapital += rpe.Principal;
rpeInterests += rpe.Interests;
}
rpePenalty += rpe.Penalties;
}
rpeCommission += rpe.Commissions;
if (rpe.RepaymentType == OPaymentType.PersonTotalPayment && cCr.LoanOptions.ManualCommissionAmount != 0)
rpeCommission -= rpe.Commissions;
}
}
if (rpeCapital > paidInstallment.PaidCapital)
rpeCapital = 0;
if (rpeInterests < 0)
rpeInterests = 0;
if (rpeInterests > paidInstallment.InterestsRepayment)
{
rpeInterests = 0;
}
OCurrency principalAmount = paidInstallment.CapitalRepayment -
(paidInstallment.CapitalRepayment - (paidInstallment.PaidCapital - rpeCapital));
OCurrency interestAmount = paidInstallment.InterestsRepayment -
(paidInstallment.InterestsRepayment - (paidInstallment.PaidInterests - rpeInterests));
if (interestAmount < 0)
interestAmount = 0;
principalEvent -= principalAmount;
interestEvent -= interestAmount;
if (principalAmount == 0
&& interestAmount == 0
&& paidInstallment.PaidCapital == 0 && manualInterestEvent == 0)
{
principalAmount = principalEvent;
interestAmount = interestEvent;
}
if (interestAmount < 0)
interestAmount = 0;
OCurrency commissionAmount = paidInstallment.CommissionsUnpaid -
(paidInstallment.CommissionsUnpaid - (paidInstallment.PaidCommissions - rpeCommission));
OCurrency penaltyAmount;
if (overridePenalty)
{
if (lastLate != null && paidInstallment.CalculatedPenalty <= manualPenalty && paidInstallment.Number < lastLate.Number)
{
penaltyAmount = paidInstallment.CalculatedPenalty;
manualPenalty -= penaltyAmount;
}
else
{
penaltyAmount = manualPenalty;
manualPenalty = 0m;
}
}
else
{
penaltyAmount = paidInstallment.FeesUnpaid -
(paidInstallment.FeesUnpaid - (paidInstallment.PaidFees - rpePenalty));
}
//just to be sure that we do not have negative in the base
penaltyAmount = penaltyAmount < 0 ? 0 : penaltyAmount;
commissionAmount = commissionAmount < 0 ? 0 : commissionAmount;
if (overridePenalty && penaltyAmount > 0)
{
penaltyAmount = (-1.0) * penaltyAmount;
}
rpEvent = CreateRepaymentEvent(number,
payDate,
penaltyAmount,
commissionAmount,
interestAmount,
interestAmount,
principalAmount,
overdueDays,
_clientType == OClientTypes.Group,
paymentType,
isPending);
if (overridePenalty && penaltyAmount < 0)
penaltyAmount = (-1.0)*penaltyAmount;
rpEvent.PaymentMethod = paymentMethod;
rpEvent.CalculatedPenalties = paidInstallment.CalculatedPenalty;
if (rpEvent.CalculatedPenalties > penaltyAmount)
{
if (overridePenalty)
{
rpEvent.WrittenOffPenalties = rpEvent.CalculatedPenalties - penaltyAmount;
}
else
{
rpEvent.UnpaidPenalties = rpEvent.CalculatedPenalties - penaltyAmount;
}
}
totalEvent.Penalties += rpEvent.Penalties;
totalEvent.Commissions += rpEvent.Commissions;
if (rpEvent.InterestPrepayment != 0
|| rpEvent.Principal != 0
|| rpEvent.Penalties != 0
|| rpEvent.Commissions != 0)
listOfLoanEvents.Add(rpEvent);
}
}
else
{
rpEvent = CreateRepaymentEvent(instNumber,
payDate,
penaltiesEvent,
commissionsEvent,
interestEvent,
interestPrepayment,
principalEvent,
pastDueDays,
_clientType == OClientTypes.Group,
paymentType,
isPending);
rpEvent.PaymentMethod = paymentMethod;
// Calculate penalties
Installment installment = GetInstallment(instNumber - 1);
rpEvent.CalculatedPenalties = installment.CalculatedPenalty;
if (rpEvent.CalculatedPenalties > penaltiesEvent)
{
if (overridePenalty)
{
rpEvent.WrittenOffPenalties = rpEvent.CalculatedPenalties - penaltiesEvent;
}
else
{
rpEvent.UnpaidPenalties = rpEvent.CalculatedPenalties - penaltiesEvent;
}
}
totalEvent.Penalties += rpEvent.Penalties;
totalEvent.Commissions += rpEvent.Commissions;
listOfLoanEvents.Add(rpEvent);
}
#endregion
return listOfLoanEvents;
}
public Loan(LoanProduct pAckage, OCurrency pAmount, decimal pInterestRate, int pNbOfInstallments, int pGracePeriod,
DateTime pStartDate, DayOfWeek? meetingDay, User pUser, ApplicationSettings pGeneralSettings,
NonWorkingDateSingleton pNwds, ProvisionTable pPt, ChartOfAccounts pChartOfAccounts)
{
_user = pUser;
_generalSettings = pGeneralSettings;
_nwdS = pNwds;
Product = pAckage;
NonRepaymentPenalties = new NonRepaymentPenalties();
_events = new EventStock();
_guarantors = new List<Guarantor>();
_collaterals = new List<ContractCollateral>();
_installmentType = pAckage.InstallmentType;
_amount = pAmount;
_interestRate = pInterestRate;
_nbOfInstallments = pNbOfInstallments;
GracePeriod = pGracePeriod;
CreationDate = pStartDate;
_startDate = pStartDate;
_firstInstallmentDate = CalculateInstallmentDate(pStartDate, 1);
if (meetingDay.HasValue)
_firstInstallmentDate = GetMeetingDate(_firstInstallmentDate, meetingDay);
_alignAlignDisbursementDate = CalculateAlignDisbursementDate(_firstInstallmentDate);
//with this constructor, installment are directly calculated when a new CreditContract is instanciated
_installmentList = CalculateInstallments(true);
CalculateStartDates();
}
/// <summary>
/// Instancy a new CreditContract. Use it if installments need'nt calculate
/// </summary>
/// <param name="pUser"></param>
/// <param name="pGeneralSettings"></param>
/// <param name="pNwds"></param>
/// <param name="pPt"></param>
/// <param name="pChartOfAccounts"></param>
public Loan(User pUser, ApplicationSettings pGeneralSettings, NonWorkingDateSingleton pNwds, ProvisionTable pPt, ChartOfAccounts pChartOfAccounts)
{
_user = pUser;
_generalSettings = pGeneralSettings;
_nwdS = pNwds;
NonRepaymentPenalties = new NonRepaymentPenalties();
_installmentList = new List<Installment>();
_guarantors = new List<Guarantor>();
_collaterals = new List<ContractCollateral>();
_events = new EventStock();
}
public void TestClosureTellerEvents()
{
EventStock eventStock = new EventStock
{
new TellerCashInEvent
{
Id = 1,
Amount = 1000,
Currency = new Currency {Id = 1},
Date = new DateTime(2000, 1, 1),
TellerId = 2
}
};
eventStock.Add(new EventStock
{
new TellerCashOutEvent
{
Id = 2,
Amount = 267,
Currency = new Currency {Id = 1},
Date = new DateTime(2000, 1, 1),
TellerId = 3
}
});
List<FiscalYear> fYears = new List<FiscalYear> { new FiscalYear { OpenDate = new DateTime(1900, 1, 1) } };
AccountingClosure closure = new AccountingClosure();
List<Booking> bookings = closure.GetBookings(_rules, eventStock, _tellers, null, null, fYears);
Assert.AreEqual(bookings[0].Amount, 1000);
Assert.AreEqual(bookings[0].CreditAccount.Number, "1991");
Assert.AreEqual(bookings[0].DebitAccount.Number, "1999");
Assert.AreEqual(bookings[1].Amount, 267);
Assert.AreEqual(bookings[1].CreditAccount.Number, "1999");
Assert.AreEqual(bookings[1].DebitAccountNumber, "1992");
}
public EventStock SelectEvents(int pContractId)
{
const string q = @"SELECT
ContractEvents.id AS event_id,
ContractEvents.contract_id,
ContractEvents.event_date,
ContractEvents.event_type,
ContractEvents.event_type AS code,
ContractEvents.is_deleted AS event_deleted,
ContractEvents.entry_date AS entry_date,
ContractEvents.comment,
ContractEvents.teller_id,
ContractEvents.parent_id,
ContractEvents.cancel_date,
LoanDisbursmentEvents.id AS lde_id,
LoanDisbursmentEvents.amount AS lde_amount,
LoanDisbursmentEvents.fees AS lde_fees,
LoanDisbursmentEvents.payment_method_id AS lde_pm,
LoanEntryFeeEvents.id AS ef_id,
LoanEntryFeeEvents.fee AS ef_fee,
LoanEntryFeeEvents.disbursement_event_id,
CreditInsuranceEvents.id AS cie_id,
CreditInsuranceEvents.commission AS cie_commission,
CreditInsuranceEvents.principal AS cie_principal,
WriteOffEvents.id AS woe_id,
WriteOffEvents.olb AS woe_olb,
WriteOffEvents.accrued_interests AS woe_accrued_interests,
WriteOffEvents.accrued_penalties AS woe_accrued_penalties,
WriteOffEvents.past_due_days AS woe_past_due_days,
WriteOffEvents.overdue_principal AS woe_overdue_principal,
ReschedulingOfALoanEvents.id AS rle_id,
ReschedulingOfALoanEvents.amount AS rle_amount,
ReschedulingOfALoanEvents.nb_of_maturity AS rle_maturity,
ReschedulingOfALoanEvents.date_offset AS rle_date_offset,
RepaymentEvents.id AS rpe_id,
RepaymentEvents.principal AS rpe_principal,
RepaymentEvents.interests AS rpe_interests,
RepaymentEvents.penalties AS rpe_penalties,
RepaymentEvents.commissions AS rpe_commissions,
RepaymentEvents.past_due_days AS rpe_past_due_days,
RepaymentEvents.installment_number As rpe_installment_number,
RepaymentEvents.payment_method_id AS rpe_pm,
RepaymentEvents.calculated_penalties rpe_calculated_penalties,
RepaymentEvents.written_off_penalties rpe_written_off_penalties,
RepaymentEvents.unpaid_penalties rpe_unpaid_penalties,
LoanInterestAccruingEvents.id AS liae_id,
LoanInterestAccruingEvents.interest_prepayment AS liae_interestPrepayment,
LoanInterestAccruingEvents.accrued_interest AS liae_accruedInterest,
LoanInterestAccruingEvents.rescheduled AS liae_rescheduled,
LoanInterestAccruingEvents.installment_number AS liae_installmentNumber,
TrancheEvents.amount AS tranche_amount,
TrancheEvents.interest_rate AS tranche_interest_rate,
TrancheEvents.maturity AS tranche_maturity,
TrancheEvents.start_date AS tranche_start_date,
TrancheEvents.id AS tranche_id,
OverdueEvents.id AS ov_id,
OverdueEvents.olb AS ov_olb,
OverdueEvents.overdue_days AS ov_overdue_days,
OverdueEvents.overdue_principal AS ov_overdue_principal,
ProvisionEvents.id AS pe_id,
ProvisionEvents.amount AS pe_amount,
ProvisionEvents.overdue_days AS pe_overdue_days,
Users.id AS user_id,
Users.deleted AS user_deleted,
Users.user_name AS user_username,
Users.user_pass AS user_password,
Users.role_code AS user_role,
Users.first_name AS user_firstname,
Users.last_name AS user_lastname,
0 AS currency_id,
'' AS client_type_code,
0 AS branch_id,
'' AS contract_code,
CAST(0 AS bit) AS is_pivot,
CAST(0 AS bit) AS is_swapped,
'' AS currency_code,
0 AS product_id
FROM ContractEvents
INNER JOIN Users ON ContractEvents.user_id = Users.id
LEFT OUTER JOIN LoanDisbursmentEvents ON ContractEvents.id = LoanDisbursmentEvents.id
LEFT OUTER JOIN LoanEntryFeeEvents ON ContractEvents.id = LoanEntryFeeEvents.id
LEFT OUTER JOIN CreditInsuranceEvents ON ContractEvents.id = CreditInsuranceEvents.id
LEFT OUTER JOIN LoanInterestAccruingEvents ON ContractEvents.id = LoanInterestAccruingEvents.id
LEFT OUTER JOIN RepaymentEvents ON ContractEvents.id = RepaymentEvents.id
LEFT OUTER JOIN ReschedulingOfALoanEvents ON ContractEvents.id = ReschedulingOfALoanEvents.id
LEFT OUTER JOIN WriteOffEvents ON ContractEvents.id = WriteOffEvents.id
LEFT OUTER JOIN TrancheEvents ON ContractEvents.id = TrancheEvents.id
LEFT OUTER JOIN OverdueEvents ON ContractEvents.id = OverdueEvents.id
LEFT OUTER JOIN ProvisionEvents ON ContractEvents.id = ProvisionEvents.id
WHERE (ContractEvents.contract_id = @id)
ORDER BY ContractEvents.id";
using (SqlConnection conn = GetConnection())
using(OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pContractId);
using (OpenCbsReader r = c.ExecuteReader())
{
if(r == null || r.Empty) return new EventStock();
EventStock list = new EventStock();
while (r.Read())
{
list.Add(ReadEvent(r));
}
return list;
}
}
}
public EventStock SelectEvents(string eventType, int userId, DateTime beginDate, DateTime endDate)
{
EventStock list = new EventStock();
string q =
string.Format(
@"SELECT
ISNULL(Contracts.contract_code, SavingContracts.code) as contract_code,
ContractEvents.contract_id,
ContractEvents.parent_id,
union_events.id AS event_id,
union_events.event_type AS code,
union_events.entry_date,
union_events.event_date AS event_date,
union_events.event_type AS event_type,
union_events.is_deleted AS event_deleted,
union_events.cancel_date,
LoanDisbursmentEvents.id AS lde_id,
LoanDisbursmentEvents.amount AS lde_amount,
LoanDisbursmentEvents.fees AS lde_fees,
LoanDisbursmentEvents.payment_method_id AS lde_pm,
LoanEntryFeeEvents.id AS ef_id,
LoanEntryFeeEvents.fee AS ef_fee,
LoanEntryFeeEvents.disbursement_event_id,
WriteOffEvents.id AS woe_id,
WriteOffEvents.olb AS woe_olb,
WriteOffEvents.accrued_interests AS woe_accrued_interests,
WriteOffEvents.accrued_penalties AS woe_accrued_penalties,
WriteOffEvents.past_due_days AS woe_past_due_days,
WriteOffEvents.overdue_principal AS woe_overdue_principal,
ReschedulingOfALoanEvents.id AS rle_id,
ReschedulingOfALoanEvents.amount AS rle_amount,
ReschedulingOfALoanEvents.nb_of_maturity AS rle_maturity,
ReschedulingOfALoanEvents.date_offset AS rle_date_offset,
RepaymentEvents.id AS rpe_id,
RepaymentEvents.principal AS rpe_principal,
RepaymentEvents.interests AS rpe_interests,
RepaymentEvents.penalties AS rpe_penalties,
RepaymentEvents.commissions AS rpe_commissions,
RepaymentEvents.past_due_days AS rpe_past_due_days,
RepaymentEvents.installment_number AS rpe_installment_number,
RepaymentEvents.payment_method_id AS rpe_pm,
RepaymentEvents.calculated_penalties rpe_calculated_penalties,
RepaymentEvents.written_off_penalties rpe_written_off_penalties,
RepaymentEvents.unpaid_penalties rpe_unpaid_penalties,
LoanInterestAccruingEvents.id AS liae_id,
LoanInterestAccruingEvents.interest_prepayment AS liae_interestPrepayment,
LoanInterestAccruingEvents.accrued_interest AS liae_accruedInterest,
LoanInterestAccruingEvents.rescheduled AS liae_rescheduled,
LoanInterestAccruingEvents.installment_number AS liae_installmentNumber,
TrancheEvents.amount AS tranche_amount,
TrancheEvents.interest_rate AS tranche_interest_rate,
TrancheEvents.maturity AS tranche_maturity,
TrancheEvents.start_date AS tranche_start_date,
TrancheEvents.id AS tranche_id,
OverdueEvents.id AS ov_id,
OverdueEvents.olb AS ov_olb,
OverdueEvents.overdue_days AS ov_overdue_days,
OverdueEvents.overdue_principal AS ov_overdue_principal,
ProvisionEvents.id AS pe_id,
ProvisionEvents.amount AS pe_amount,
ProvisionEvents.overdue_days AS pe_overdue_days,
SavingEvents.amount AS se_amount,
SavingEvents.fees AS se_fees,
SavingEvents.related_contract_code AS se_transfer_code,
Users.id AS user_id,
Users.deleted AS user_deleted,
Users.user_name AS user_username,
Users.user_pass AS user_password,
Users.role_code AS user_role,
Users.first_name AS user_firstname,
Users.last_name AS user_lastname,
0 AS currency_id,
'' AS client_type_code,
0 AS branch_id,
'' AS contract_code
FROM (SELECT id,
event_type,
entry_date,
event_date,
is_deleted,
user_id,
cancel_date
FROM ContractEvents
UNION ALL
SELECT id,
code AS event_type,
creation_date AS entry_date,
creation_date AS event_date,
deleted AS is_deleted,
user_id,
cancel_date
FROM SavingEvents WHERE code NOT IN('SIAE', 'SIPE')
)
AS union_events
LEFT JOIN ContractEvents ON union_events.ID = ContractEvents.id
LEFT JOIN SavingEvents ON union_events.ID = SavingEvents.id
LEFT JOIN Contracts ON Contracts.id = ContractEvents.contract_id
LEFT JOIN SavingContracts ON SavingContracts.id = SavingEvents.contract_id
LEFT OUTER JOIN Users ON union_events.user_id = Users.id
LEFT OUTER JOIN LoanDisbursmentEvents ON ContractEvents.id = LoanDisbursmentEvents.id
LEFT OUTER JOIN LoanEntryFeeEvents ON ContractEvents.id = LoanEntryFeeEvents.id
LEFT OUTER JOIN LoanInterestAccruingEvents ON ContractEvents.id = LoanInterestAccruingEvents.id
LEFT OUTER JOIN RepaymentEvents ON ContractEvents.id = RepaymentEvents.id
LEFT OUTER JOIN ReschedulingOfALoanEvents ON ContractEvents.id = ReschedulingOfALoanEvents.id
LEFT OUTER JOIN WriteOffEvents ON ContractEvents.id = WriteOffEvents.id
LEFT OUTER JOIN TrancheEvents ON ContractEvents.id = TrancheEvents.id
LEFT OUTER JOIN OverdueEvents ON ContractEvents.id = OverdueEvents.id
LEFT OUTER JOIN ProvisionEvents ON ContractEvents.id = ProvisionEvents.id
WHERE union_events.event_date BETWEEN @beginDate AND @endDate
AND union_events.event_type LIKE @eventType {0}",
userId > 0 ? "AND ContractEvents.user_id = @userId ORDER BY union_events.event_date" : "ORDER BY union_events.event_date");
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@beginDate", beginDate);
c.AddParam("@endDate", endDate);
c.AddParam("@userId", userId);
c.AddParam("@eventType", string.Format("%{0}%", eventType) );
using (OpenCbsReader r = c.ExecuteReader())
{
if (!(r == null || r.Empty))
while (r.Read())
{
list.Add(ReadEvent(r));
}
}
}
return list;
}
private void GetBookings(ClosureOptions options)
{
List<Booking> bookings = new List<Booking>();
EventStock eventStock = new EventStock();
AccountingRuleCollection rules = ServicesProvider.GetInstance().GetAccountingRuleServices().SelectAll();
rules.SortByOrder();
if (options.DoLoanClosure)
{
UpdateStatus("LoanClosureProcessing", "");
eventStock =
ServicesProvider.GetInstance().GetEventProcessorServices().SelectEventsForClosure(_beginDate, _endDate, _branch);
UpdateStatus("LoanClosureProcessing", eventStock.GetEvents().Count.ToString());
//add generated events for processing
}
if (options.DoSavingClosure)
{
UpdateStatus("SavingsClosureProcessing", "");
eventStock.AddRange(
ServicesProvider.GetInstance().GetSavingServices().SelectEventsForClosure(
_beginDate, _endDate, _branch));
UpdateStatus("SavingsClosureProcessing", eventStock.GetEvents().Count.ToString());
}
if (options.DoTellerManagementClosure)
{
UpdateStatus("TellerManagementProcessing", "");
eventStock.AddRange(ServicesProvider.GetInstance().GetEventProcessorServices().GetTellerEventsForClosure(
_beginDate, _endDate));
UpdateStatus("EventClosureProcessing", eventStock.GetEvents().Count.ToString());
}
//important to have sorted list
eventStock.SortEventsById();
//closure procesing
timerClosure.Start();
timerClosure.Enabled = true;
//set ex rate
List<ExchangeRate> rates =
ServicesProvider.GetInstance().GetExchangeRateServices().SelectRatesByDate(_beginDate, _endDate);
List<CoreDomain.Accounting.FiscalYear> fiscalYears =
ServicesProvider.GetInstance().GetChartOfAccountsServices().SelectFiscalYears();
bookings.AddRange(
_accountingClosure.GetBookings(
rules,
eventStock,
ServicesProvider.GetInstance().GetTellerServices().FindAllTellers(),
ServicesProvider.GetInstance().GetPaymentMethodServices().GetAllPaymentMethodsForClosure(),
rates,
fiscalYears));
timerClosure.Stop();
timerClosure.Enabled = false;
//manual transactions
if (options.DoManualEntries)
{
bookings.AddRange(ServicesProvider.GetInstance().GetAccountingServices().SelectMovements(false, rates,
fiscalYears));
}
#region Reversal
if (options.DoReversalTransactions)
{
UpdateStatus("ReversalTransactionProcessing", "");
bookings.AddRange(ServicesProvider.GetInstance().GetAccountingServices().SelectMovementsForReverse(
rates,
fiscalYears));
}
#endregion
//add reversal provision booking
if (options.DoLoanClosure)
{
bookings.AddRange(
_generatedEvents.GetProvisionEvents().Select(
provisionEvent =>
ServicesProvider.GetInstance().GetAccountingServices().SelectProvisionMovments(
provisionEvent.ContracId, rates,
fiscalYears)).Where(b => b != null));
}
SortBookingsByDate(bookings);
FillListView(bookings);
}
public List<Booking> GetBookings(AccountingRuleCollection rules,
EventStock eventStock,
List<Teller> tellers,
List<PaymentMethod> paymentMethods,
List<ExchangeRate> rates,
List<FiscalYear> fiscalYears)
{
if (eventStock == null)
return new List<Booking>();
rules.SortByOrder();
eventStock.SortEventsById();
bool isExported = false;
var bookings = new List<Booking>();
foreach (Event eventItem in eventStock)
{
ClosureStatus = "Closure";
ClosureStatusInfo = " ->" + eventItem.Code + "-" + eventItem.Id;
var attributes = new List<EventAttribute>();
List<ContractAccountingRule> rulesToApply =
rules.GetContractAccountingRules().Where(item => item.EventType.EventCode == eventItem.Code).ToList();
rules.SortByOrder();
List<ContractAccountingRule> orders = rulesToApply.GroupBy(a => a.Order).Select(g => g.Last()).ToList();
foreach (ContractAccountingRule orderRule in orders)
{
foreach (ContractAccountingRule rule in rulesToApply.Where(r => r.Order == orderRule.Order).ToList())
{
List<EventAttribute> evtAttributes = (from eventAtt in attributes
where eventAtt.Name == rule.EventAttribute.Name
select eventAtt).ToList();
if (rule.EventType.EventCode == eventItem.Code
&& evtAttributes.Count <= rulesToApply.Count(r => r.Order == orderRule.Order) - 1)
{
ContractAccountingRule tempRule = rule.Copy();
if (paymentMethods != null && eventItem.PaymentMethod != null)
tempRule = GetParentPaymentAccount(eventItem.PaymentMethod.Id, tempRule, paymentMethods);
// teller must be last
if (tellers != null && eventItem.TellerId != null)
{
//that copy is very important because the rule might be over written by payment method
tempRule = rule.Copy();
tempRule = GetParentTellerAccount(eventItem.TellerId, tempRule, tellers);
}
Booking b = GetBooking(tempRule, eventItem);
if (b != null && b.Amount > 0)
{
//setting fiscal year
if (fiscalYears != null)
b.FiscalYear =
fiscalYears
.First(
f =>
f.OpenDate <= b.Date.Date &&
(f.CloseDate == null || f.CloseDate >= b.Date.Date)
);
//setting xrate
ExchangeRate rate = null;
if (rates != null)
rate = rates.FirstOrDefault(r => r.Date.Date == b.Date.Date);
b.ExchangeRate = b.Currency.IsPivot ? 1 : rate == null ? 0 : rate.Rate;
isExported = true;
attributes.Add(tempRule.EventAttribute);
bookings.Add(b);
}
}
}
}
if(eventItem is TellerCashInEvent || eventItem is TellerCashOutEvent)
{
bookings.Add(GetTellerBooking((TellerEvent) eventItem, tellers, fiscalYears));
}
eventItem.IsFired = false;
eventItem.IsFired = isExported;
}
return bookings;
}
public void Add(EventStock eventStock)
{
_list.AddRange(eventStock._list);
SortEventsById();
}
public void TestSimpleClosure()
{
EventStock eventStock = new EventStock
{
new LoanDisbursmentEvent
{
Id = 1,
Date = new DateTime(2000,1,1),
Amount = 100,
PaymentMethod = new PaymentMethod {Id = 0},
ClientType = OClientTypes.Person,
EconomicActivity =
new EconomicActivity(1, "Agriculture", null, false),
Currency = new Currency(){Id = 0}
},
new RepaymentEvent
{
Id = 2,
Principal = 100,
Interests = 5,
Penalties = 1,
Commissions = 0,
ClientType = OClientTypes.All,
Date = new DateTime(2000,1,1),
Currency = new Currency(){Id = 0}
}
};
List<FiscalYear> fYears = new List<FiscalYear> { new FiscalYear { OpenDate = new DateTime(1900, 1, 1) } };
AccountingClosure closure = new AccountingClosure();
List<Booking> bookings = closure.GetBookings(_rules, eventStock, null, null, null, fYears);
Assert.AreEqual(bookings[0].Amount, 100);
Assert.AreEqual(bookings[0].DebitAccount.Number, "1052");
Assert.AreEqual(bookings[0].CreditAccount.Number, "1020");
}
private void btCancelLastSavingEvent_Click(object sender, EventArgs e)
{
try
{
if (!_saving.HasCancelableEvents()) return;
EventStock es = new EventStock();
es.AddRange(_saving.Events);
Event foundEvent = es.GetLastSavingNonDeletedEvent;
var coaServices = ServicesProvider.GetInstance().GetChartOfAccountsServices();
var fiscalYear =
coaServices.SelectFiscalYears().Find(
y => y.OpenDate <= foundEvent.Date && (y.CloseDate >= foundEvent.Date || y.CloseDate == null));
if (null == fiscalYear || !fiscalYear.Open)
{
throw new OpenCbsContractSaveException(
OpenCbsContractSaveExceptionEnum.OperationOutsideCurrentFiscalYear);
}
string message = GetString("ConfirmCancelLastEvent");
string caption = GetString("Confirm");
DialogResult res = MessageBox.Show(message, caption, MessageBoxButtons.YesNo);
if (res != DialogResult.Yes) return;
FrmDeleteEventComment frm = new FrmDeleteEventComment();
DialogResult result = frm.ShowDialog();
if (result == DialogResult.OK)
{
try
{
if (_saving is SavingBookContract)
((SavingBookContract)_saving).Loans = SavingServices.SelectLoansBySavingsId(_saving.Id);
SavingEvent sEvent = SavingServices.CancelLastEvent(_saving, User.CurrentUser, frm.Comment);
for (int i = 0; i <= _saving.Events.Count - 1; i++)
if (_saving.Events[i].Id == sEvent.Id)
{
SavingEvent temp = _saving.Events[i];
temp.Deleted = true;
_saving.Events[i] = temp;
}
}
catch (Exception ex)
{
new frmShowError(CustomExceptionHandler.ShowExceptionText(ex)).ShowDialog();
}
((LotrasmicMainWindowForm)_mdiParent).ReloadAlertsSync();
DisplaySavingEvent(_saving);
if (_person != null) DisplaySavings(_person.Savings);
if (_client != null) DisplaySavings(_client.Savings);
}
}
catch (Exception ex)
{
new frmShowError(CustomExceptionHandler.ShowExceptionText(ex)).ShowDialog();
}
}
public EventStock SelectEventsForClosure(DateTime beginDate, DateTime endDate, Branch branch)
{
const string q = @"SELECT
ContractEvents.id AS event_id,
ContractEvents.contract_id,
ContractEvents.event_date,
ContractEvents.event_type,
ContractEvents.event_type AS code,
ContractEvents.is_deleted AS event_deleted,
ContractEvents.entry_date AS entry_date,
ContractEvents.comment,
ContractEvents.teller_id,
ContractEvents.parent_id,
ContractEvents.cancel_date,
LoanDisbursmentEvents.id AS lde_id,
LoanDisbursmentEvents.amount AS lde_amount,
LoanDisbursmentEvents.fees AS lde_fees,
LoanDisbursmentEvents.payment_method_id AS lde_pm,
LoanEntryFeeEvents.id AS ef_id,
LoanEntryFeeEvents.fee AS ef_fee,
LoanEntryFeeEvents.disbursement_event_id,
CreditInsuranceEvents.id AS cie_id,
CreditInsuranceEvents.commission AS cie_commission,
CreditInsuranceEvents.principal AS cie_principal,
WriteOffEvents.id AS woe_id,
WriteOffEvents.olb AS woe_olb,
WriteOffEvents.accrued_interests AS woe_accrued_interests,
WriteOffEvents.accrued_penalties AS woe_accrued_penalties,
WriteOffEvents.past_due_days AS woe_past_due_days,
WriteOffEvents.overdue_principal AS woe_overdue_principal,
ReschedulingOfALoanEvents.id AS rle_id,
ReschedulingOfALoanEvents.amount AS rle_amount,
ReschedulingOfALoanEvents.nb_of_maturity AS rle_maturity,
ReschedulingOfALoanEvents.date_offset AS rle_date_offset,
RepaymentEvents.id AS rpe_id,
RepaymentEvents.principal AS rpe_principal,
RepaymentEvents.interests AS rpe_interests,
RepaymentEvents.penalties AS rpe_penalties,
RepaymentEvents.commissions AS rpe_commissions,
RepaymentEvents.past_due_days AS rpe_past_due_days,
RepaymentEvents.installment_number As rpe_installment_number,
RepaymentEvents.payment_method_id AS rpe_pm,
RepaymentEvents.calculated_penalties rpe_calculated_penalties,
RepaymentEvents.written_off_penalties rpe_written_off_penalties,
RepaymentEvents.unpaid_penalties rpe_unpaid_penalties,
LoanInterestAccruingEvents.id AS liae_id,
LoanInterestAccruingEvents.interest_prepayment AS liae_interestPrepayment,
LoanInterestAccruingEvents.accrued_interest AS liae_accruedInterest,
LoanInterestAccruingEvents.rescheduled AS liae_rescheduled,
LoanInterestAccruingEvents.installment_number AS liae_installmentNumber,
TrancheEvents.amount AS tranche_amount,
TrancheEvents.interest_rate AS tranche_interest_rate,
TrancheEvents.maturity AS tranche_maturity,
TrancheEvents.start_date AS tranche_start_date,
TrancheEvents.id AS tranche_id,
OverdueEvents.id AS ov_id,
OverdueEvents.olb AS ov_olb,
OverdueEvents.overdue_days AS ov_overdue_days,
OverdueEvents.overdue_principal AS ov_overdue_principal,
ProvisionEvents.id AS pe_id,
ProvisionEvents.amount AS pe_amount,
ProvisionEvents.overdue_days AS pe_overdue_days,
Users.id AS user_id,
Users.deleted AS user_deleted,
Users.user_name AS user_username,
Users.user_pass AS user_password,
Users.role_code AS user_role,
Users.first_name AS user_firstname,
Users.last_name AS user_lastname,
Packages.currency_id,
t.client_type_code,
t.branch_id,
con.contract_code,
Currencies.is_pivot,
Currencies.is_swapped,
Currencies.code AS currency_code,
c.package_id AS product_id
FROM ContractEvents
INNER JOIN Users ON ContractEvents.user_id = Users.id
INNER JOIN dbo.Credit c ON contract_id = c.id
INNER JOIN dbo.Packages ON c.package_id = dbo.Packages.id
INNER JOIN Currencies ON Packages.currency_id = Currencies.id
INNER JOIN Contracts con ON con.id = c.id
INNER JOIN Projects pr ON pr.id = con.project_id
INNER JOIN Tiers t ON t.id = pr.tiers_id
LEFT OUTER JOIN LoanDisbursmentEvents ON ContractEvents.id = LoanDisbursmentEvents.id
LEFT OUTER JOIN LoanEntryFeeEvents ON ContractEvents.id = LoanEntryFeeEvents.id
LEFT OUTER JOIN LoanInterestAccruingEvents ON ContractEvents.id = LoanInterestAccruingEvents.id
LEFT OUTER JOIN RepaymentEvents ON ContractEvents.id = RepaymentEvents.id
LEFT OUTER JOIN ReschedulingOfALoanEvents ON ContractEvents.id = ReschedulingOfALoanEvents.id
LEFT OUTER JOIN WriteOffEvents ON ContractEvents.id = WriteOffEvents.id
LEFT OUTER JOIN TrancheEvents ON ContractEvents.id = TrancheEvents.id
LEFT OUTER JOIN OverdueEvents ON ContractEvents.id = OverdueEvents.id
LEFT OUTER JOIN ProvisionEvents ON ContractEvents.id = ProvisionEvents.id
LEFT OUTER JOIN CreditInsuranceEvents ON ContractEvents.id = CreditInsuranceEvents.id
WHERE
ContractEvents.is_exported = 0
AND ContractEvents.is_deleted = 0
AND EXISTS(SELECT event_type
FROM dbo.AccountingRules
GROUP BY event_type)
AND ContractEvents.event_date BETWEEN @beginDate AND @endDate
AND (t.branch_id = @branch_id OR @branch_id = 0)
AND (LoanDisbursmentEvents.id IS NOT NULL
OR RepaymentEvents.id IS NOT NULL
OR ReschedulingOfALoanEvents.id IS NOT NULL
OR WriteOffEvents.id IS NOT NULL
OR OverdueEvents.id IS NOT NULL
OR ProvisionEvents.id IS NOT NULL
OR TrancheEvents.id IS NOT NULL
OR LoanInterestAccruingEvents.id IS NOT NULL
OR LoanEntryFeeEvents.id IS NOT NULL
OR CreditInsuranceEvents.id IS NOT NULL)
ORDER BY ContractEvents.id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@beginDate", beginDate);
c.AddParam("@endDate", endDate);
c.AddParam("@branch_id", branch.Id);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty) return new EventStock();
EventStock list = new EventStock();
while (r.Read())
{
list.Add(ReadEvent(r));
}
return list;
}
}
}
}
public void TestClosureLodeEvent()
{
EventStock eventStock = new EventStock
{
new LoanDisbursmentEvent
{
Id = 1,
Amount = 100,
Date = new DateTime(2000,1,1),
EconomicActivity =
new EconomicActivity(1, "Agriculture", null, false),
ClientType = OClientTypes.Person,
PaymentMethod = new PaymentMethod {Id = 0},
LoanProduct =
new LoanProduct
{
Id = 1,
Code = "EDE34",
Name = "EDEN 34",
Currency = new Currency {Id = 1}
},
Currency = new Currency {Id = 0}
}
};
List<FiscalYear> fYears = new List<FiscalYear> { new FiscalYear { OpenDate = new DateTime(1900, 1, 1) } };
AccountingClosure closure = new AccountingClosure();
List<Booking> bookings = closure.GetBookings(_rules, eventStock, null, null, null, fYears);
Assert.AreEqual(bookings[0].Amount, 100);
Assert.AreEqual(bookings[0].DebitAccount.Number, "1052");
Assert.AreEqual(bookings[0].CreditAccount.Number, "1020");
}
