private static RescheduleLoanEvent GetReschedulingLoanEvent(OpenCbsReader r)
{
return new RescheduleLoanEvent{
Id = r.GetInt("rle_id"),
Amount = r.GetMoney("rle_amount"),
NbOfMaturity = r.GetInt("rle_maturity"),
DateOffset = r.GetInt("rle_date_offset")
};
}
private Loan _GetLoan(OpenCbsReader r)
{
return new Loan(_user, ApplicationSettings.GetInstance(_user.Md5),
NonWorkingDateSingleton.GetInstance(_user.Md5),
ProvisionTable.GetInstance(_user), ChartOfAccounts.GetInstance(_user))
{
Id = r.GetInt("credit_id"),
ClientType = r.GetChar("client_type_code") == 'I'
? OClientTypes.Person
: r.GetChar("client_type_code") == 'G'
? OClientTypes.Group
: OClientTypes.Corporate,
ContractStatus = (OContractStatus) r.GetSmallInt("status"),
CreditCommiteeDate = r.GetNullDateTime("credit_commitee_date"),
CreditCommiteeComment = r.GetString("credit_commitee_comment"),
CreditCommitteeCode = r.GetString("credit_commitee_code"),
Amount = r.GetMoney("amount"),
InterestRate = r.GetDecimal("interest_rate"),
NbOfInstallments = r.GetInt("nb_of_installment"),
NonRepaymentPenalties = new NonRepaymentPenalties
{
InitialAmount = r.GetDouble("non_repayment_penalties_based_on_initial_amount"),
OLB = r.GetDouble("non_repayment_penalties_based_on_olb"),
OverDueInterest = r.GetDouble("non_repayment_penalties_based_on_overdue_interest"),
OverDuePrincipal = r.GetDouble("non_repayment_penalties_based_on_overdue_principal")
},
AnticipatedTotalRepaymentPenalties = r.GetDouble("anticipated_total_repayment_penalties"),
AnticipatedPartialRepaymentPenalties = r.GetDouble("anticipated_partial_repayment_penalties"),
AnticipatedPartialRepaymentPenaltiesBase = (OAnticipatedRepaymentPenaltiesBases)
r.GetSmallInt("anticipated_partial_repayment_base"),
AnticipatedTotalRepaymentPenaltiesBase =(OAnticipatedRepaymentPenaltiesBases)
r.GetSmallInt("anticipated_total_repayment_base"),
Disbursed = r.GetBool("disbursed"),
GracePeriod = r.GetNullInt("grace_period"),
GracePeriodOfLateFees = r.GetNullInt("grace_period_of_latefees"),
WrittenOff = r.GetBool("written_off"),
Rescheduled = r.GetBool("rescheduled"),
Code = r.GetString("contract_code"),
BranchCode = r.GetString("branch_code"),
CreationDate = r.GetDateTime("creation_date"),
StartDate = r.GetDateTime("start_date"),
AlignDisbursementDate = r.GetDateTime("align_disbursed_date"),
CloseDate = r.GetDateTime("close_date"),
Closed = r.GetBool("closed"),
BadLoan = r.GetBool("bad_loan"),
Synchronize = r.GetBool("synchronize"),
ScheduleChangedManually = r.GetBool("schedule_changed"),
AmountUnderLoc = r.GetMoney("amount_under_loc"),
CompulsorySavingsPercentage = r.GetNullInt("loan_percentage"),
LoanPurpose = r.GetString("loan_purpose"),
Comments = r.GetString("comments"),
AmountMin = r.GetMoney("amount_min"),
AmountMax = r.GetMoney("amount_max"),
InterestRateMin = r.GetNullDecimal("ir_min"),
InterestRateMax = r.GetNullDecimal("ir_max"),
NmbOfInstallmentsMin = r.GetNullInt("nmb_of_inst_min"),
NmbOfInstallmentsMax = r.GetNullInt("nmb_of_inst_max"),
LoanCycle = r.GetNullInt("loan_cycle"),
Insurance = r.GetDecimal("insurance"),
NsgID = r.GetNullInt("nsg_id"),
EconomicActivityId = r.GetInt("activity_id"),
FirstInstallmentDate = r.GetDateTime("preferred_first_installment_date"),
};
}
private static ISavingProduct GetProduct(OpenCbsReader r)
{
ISavingProduct product;
switch (r.GetChar("product_type"))
{
case 'B' : product = new SavingsBookProduct(); break;
default : product = null; break;
}
product.Id = r.GetInt("id");
product.Delete = r.GetBool("deleted");
product.Name = r.GetString("name");
product.Code = r.GetString("code");
product.ClientType = r.GetChar("client_type") == 'C' ? OClientTypes.Corporate
: r.GetChar("client_type") == 'G' ? OClientTypes.Group
: r.GetChar("client_type") == 'I' ? OClientTypes.Person
: OClientTypes.All;
product.InitialAmountMin = r.GetMoney("initial_amount_min");
product.InitialAmountMax = r.GetMoney("initial_amount_max");
product.BalanceMin = r.GetMoney("balance_min");
product.BalanceMax = r.GetMoney("balance_max");
product.DepositMin = r.GetMoney("deposit_min");
product.DepositMax = r.GetMoney("deposit_max");
product.WithdrawingMin = r.GetMoney("withdraw_min");
product.WithdrawingMax = r.GetMoney("withdraw_max");
product.TransferMin = r.GetMoney("transfer_min");
product.TransferMax = r.GetMoney("transfer_max");
product.InterestRate = r.GetNullDouble("interest_rate");
product.InterestRateMin = r.GetNullDouble("interest_rate_min");
product.InterestRateMax = r.GetNullDouble("interest_rate_max");
product.EntryFees = r.GetMoney("entry_fees");
product.EntryFeesMax = r.GetMoney("entry_fees_max");
product.EntryFeesMin = r.GetMoney("entry_fees_min");
if (product is SavingsBookProduct)
{
var savingBookProduct = (SavingsBookProduct) product;
savingBookProduct.InterestBase = (OSavingInterestBase)r.GetSmallInt("interest_base");
savingBookProduct.InterestFrequency = (OSavingInterestFrequency)r.GetSmallInt("interest_frequency");
if (savingBookProduct.InterestBase == OSavingInterestBase.Monthly ||
savingBookProduct.InterestBase == OSavingInterestBase.Weekly)
savingBookProduct.CalculAmountBase = (OSavingCalculAmountBase)r.GetSmallInt("calcul_amount_base");
savingBookProduct.WithdrawFeesType = (OSavingsFeesType)r.GetSmallInt("withdraw_fees_type");
if (savingBookProduct.WithdrawFeesType == OSavingsFeesType.Flat)
{
savingBookProduct.FlatWithdrawFeesMin = r.GetMoney("flat_withdraw_fees_min");
savingBookProduct.FlatWithdrawFeesMax = r.GetMoney("flat_withdraw_fees_max");
savingBookProduct.FlatWithdrawFees = r.GetMoney("flat_withdraw_fees");
}
else
{
savingBookProduct.RateWithdrawFeesMin = r.GetNullDouble("rate_withdraw_fees_min");
savingBookProduct.RateWithdrawFeesMax = r.GetNullDouble("rate_withdraw_fees_max");
savingBookProduct.RateWithdrawFees = r.GetNullDouble("rate_withdraw_fees");
}
savingBookProduct.TransferFeesType = (OSavingsFeesType)r.GetSmallInt("transfer_fees_type");
if (savingBookProduct.TransferFeesType == OSavingsFeesType.Flat)
{
savingBookProduct.FlatTransferFeesMin = r.GetMoney("flat_transfer_fees_min");
savingBookProduct.FlatTransferFeesMax = r.GetMoney("flat_transfer_fees_max");
savingBookProduct.FlatTransferFees = r.GetMoney("flat_transfer_fees");
}
else
{
savingBookProduct.RateTransferFeesMin = r.GetNullDouble("rate_transfer_fees_min");
savingBookProduct.RateTransferFeesMax = r.GetNullDouble("rate_transfer_fees_max");
savingBookProduct.RateTransferFees = r.GetNullDouble("rate_transfer_fees");
}
Fee fee = savingBookProduct.InterBranchTransferFee;
fee.IsFlat = r.GetBool("is_ibt_fee_flat");
fee.Min = r.GetNullDecimal("ibt_fee_min");
fee.Max = r.GetNullDecimal("ibt_fee_max");
fee.Value = r.GetNullDecimal("ibt_fee");
savingBookProduct.DepositFees = r.GetMoney("deposit_fees");
savingBookProduct.DepositFeesMax = r.GetMoney("deposit_fees_max");
((SavingsBookProduct)product).DepositFeesMin = r.GetMoney("deposit_fees_min");
savingBookProduct.ChequeDepositMin = r.GetMoney("cheque_deposit_min");
savingBookProduct.ChequeDepositMax = r.GetMoney("cheque_deposit_max");
savingBookProduct.ChequeDepositFees = r.GetMoney("cheque_deposit_fees");
savingBookProduct.ChequeDepositFeesMin = r.GetMoney("cheque_deposit_fees_min");
savingBookProduct.ChequeDepositFeesMax = r.GetMoney("cheque_deposit_fees_max");
savingBookProduct.CloseFees = r.GetMoney("close_fees");
savingBookProduct.CloseFeesMax = r.GetMoney("close_fees_max");
savingBookProduct.CloseFeesMin = r.GetMoney("close_fees_min");
savingBookProduct.ManagementFees = r.GetMoney("management_fees");
savingBookProduct.ManagementFeesMax = r.GetMoney("management_fees_max");
savingBookProduct.ManagementFeesMin = r.GetMoney("management_fees_min");
savingBookProduct.ManagementFeeFreq = new InstallmentType
{
Id = r.GetInt("mgmt_fee_freq_id"),
Name = r.GetString("mgmt_fee_freq_name"),
NbOfDays = r.GetInt("mgmt_fee_freq_days"),
NbOfMonths = r.GetInt("mgmt_fee_freq_months")
};
savingBookProduct.OverdraftFees = r.GetMoney("overdraft_fees");
savingBookProduct.OverdraftFeesMax = r.GetMoney("overdraft_fees_max");
savingBookProduct.OverdraftFeesMin = r.GetMoney("overdraft_fees_min");
savingBookProduct.AgioFees = r.GetNullDouble("agio_fees");
savingBookProduct.AgioFeesMax = r.GetNullDouble("agio_fees_max");
savingBookProduct.AgioFeesMin = r.GetNullDouble("agio_fees_min");
((SavingsBookProduct)product).AgioFeesFreq = new InstallmentType
{
Id = r.GetInt("agio_fees_freq_id"),
Name = r.GetString("agio_fees_freq_name"),
NbOfDays = r.GetInt("agio_fees_freq_days"),
NbOfMonths = r.GetInt("agio_fees_freq_months")
};
savingBookProduct.ReopenFees = r.GetMoney("reopen_fees");
savingBookProduct.ReopenFeesMin = r.GetMoney("reopen_fees_min");
savingBookProduct.ReopenFeesMax = r.GetMoney("reopen_fees_max");
savingBookProduct.UseTermDeposit = r.GetBool("use_term_deposit");
savingBookProduct.TermDepositPeriodMin = r.GetNullInt("term_deposit_period_min");
savingBookProduct.TermDepositPeriodMax = r.GetNullInt("term_deposit_period_max");
if (savingBookProduct.UseTermDeposit)
{
savingBookProduct.InstallmentTypeId = r.GetNullInt("posting_frequency");
savingBookProduct.Periodicity = new InstallmentType
{
Id = r.GetInt("posting_frequency"),
Name = r.GetString("periodicity_name") ,
NbOfDays = r.GetInt("periodicity_days"),
NbOfMonths = r.GetInt("periodicity_month")
};
}
}
if (r.GetNullInt("currency_id") != null)
{
product.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Code = r.GetString("currency_code"),
Name = r.GetString("currency_name"),
IsPivot = r.GetBool("currency_is_pivot"),
IsSwapped = r.GetBool("currency_is_swapped"),
UseCents = r.GetBool("currency_use_cents")
};
}
return product;
}
private static Booking GetBooking(OpenCbsReader reader)
{
return new Booking
{
Id = reader.GetInt("id"),
Amount = reader.GetMoney("amount"),
IsExported = reader.GetBool("is_exported"),
DebitAccount =
new Account
{
Id = reader.GetInt("debit_account_number_id")
},
CreditAccount =
new Account
{
Id = reader.GetInt("credit_account_number_id")
},
EventId = reader.GetInt("event_id"),
ContractId = reader.GetInt("contract_id"),
Date = reader.GetDateTime("transaction_date"),
Currency =
new Currency
{Id = reader.GetInt("currency_id")},
ExchangeRate = reader.GetDouble("exchange_rate"),
Description = reader.GetString("description"),
Branch = new Branch {Id = reader.GetInt("branch_id")}
};
}
private static void SetSavingsEvent(OpenCbsReader r, SavingEvent e, ISavingProduct pProduct)
{
e.Id = r.GetInt("id");
e.ContracId = r.GetInt("contract_id");
e.Code = r.GetString("code");
e.Amount = r.GetMoney("amount");
e.Description = r.GetString("description");
e.Deleted = r.GetBool("deleted");
e.Date = r.GetDateTime("creation_date");
e.Cancelable = r.GetBool("cancelable");
e.IsFired = r.GetBool("is_fired");
e.CancelDate = r.GetNullDateTime("cancel_date");
if(pProduct != null)
e.ProductType = pProduct.GetType();
if (r.GetNullSmallInt("savings_method").HasValue)
e.SavingsMethod = (OSavingsMethods)r.GetNullSmallInt("savings_method").Value;
e.IsPending = r.GetBool("pending");
e.PendingEventId = r.GetNullInt("pending_event_id");
e.TellerId = r.GetNullInt("teller_id");
e.LoanEventId = r.GetNullInt("loan_event_id");
if (pProduct != null)
{
e.ProductType = pProduct.GetType();
}
if (e is SavingTransferEvent)
{
((SavingTransferEvent)e).RelatedContractCode = r.GetString("related_contract_code");
}
if (e is ISavingsFees)
{
((ISavingsFees) e).Fee = r.GetMoney("fees");
}
e.User = new User
{
Id = r.GetInt("user_id"),
UserName = r.GetString("user_name"),
Password = r.GetString("user_pass"),
LastName = r.GetString("last_name"),
FirstName = r.GetString("first_name")
};
e.User.SetRole(r.GetString("role_code"));
e.ClientType = OClientTypes.All;
switch (r.GetString("client_type_code"))
{
case "I":
e.ClientType = OClientTypes.Person; break;
case "C":
e.ClientType = OClientTypes.Corporate; break;
case "G":
e.ClientType = OClientTypes.Group; break;
case "V":
e.ClientType = OClientTypes.Village; break;
}
e.Branch = new Branch { Id = r.GetInt("branch_id") };
e.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Code = r.GetString("currency_code"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped")
};
e.SavingProduct = new SavingsBookProduct { Id = r.GetInt("product_id") };
}
private static Event GetWriteOffEvent(OpenCbsReader r)
{
return new WriteOffEvent
{
Id = r.GetInt("woe_id"),
OLB = r.GetMoney("woe_olb"),
AccruedInterests = r.GetMoney("woe_accrued_interests"),
AccruedPenalties = r.GetMoney("woe_accrued_penalties"),
PastDueDays = r.GetInt("woe_past_due_days"),
OverduePrincipal = r.GetMoney("woe_overdue_principal")
};
}
private RepaymentEvent GetRepaymentEvent(OpenCbsReader r)
{
RepaymentEvent e = new RepaymentEvent {Id = r.GetInt("rpe_id")};
switch (r.GetString("event_type"))
{
case "RBLE":
{
e = new BadLoanRepaymentEvent {Id = r.GetInt("rpe_id")};
break;
}
case "RRLE":
{
e = new RescheduledLoanRepaymentEvent {Id = r.GetInt("rpe_id")};
break;
}
case "ROWO":
{
e = new RepaymentOverWriteOffEvent { Id = r.GetInt("rpe_id") };
break;
}
case "PRLR":
{
e = new PendingRepaymentEvent(r.GetString("event_type"))
{Id = r.GetInt("rpe_id")};
break;
}
case "PBLR":
{
e = new PendingRepaymentEvent(r.GetString("event_type"))
{Id = r.GetInt("rpe_id")};
break;
}
case "PRWO":
{
e = new PendingRepaymentEvent(r.GetString("event_type"))
{Id = r.GetInt("rpe_id")};
break;
}
case "PERE":
{
e = new PendingRepaymentEvent(r.GetString("event_type"))
{Id = r.GetInt("rpe_id")};
break;
}
}
e.Principal = r.GetMoney("rpe_principal");
e.Interests = r.GetMoney("rpe_interests");
e.Penalties = r.GetMoney("rpe_penalties");
e.Commissions = r.GetMoney("rpe_commissions");
e.PastDueDays = r.GetInt("rpe_past_due_days");
e.InstallmentNumber = r.GetInt("rpe_installment_number");
e.PaymentMethodId = r.GetNullInt("rpe_pm");
e.PaymentMethod = e.PaymentMethodId == null ? null :
_paymentMethodManager.SelectPaymentMethodById(e.PaymentMethodId.Value);
e.CalculatedPenalties = r.GetMoney("rpe_calculated_penalties");
e.WrittenOffPenalties = r.GetMoney("rpe_written_off_penalties");
e.UnpaidPenalties = r.GetMoney("rpe_unpaid_penalties");
e.Code = r.GetString("event_type");
if (e.Code != "RBLE")
e.RepaymentType = OPaymentType.StandardPayment;
// set type of payment
switch (r.GetString("event_type").Trim())
{
case "ATR":
{
e.RepaymentType = OPaymentType.TotalPayment;
break;
}
case "APR":
{
e.RepaymentType = OPaymentType.PartialPayment;
break;
}
case "APTR":
{
e.RepaymentType = OPaymentType.PersonTotalPayment;
break;
}
}
return e;
}
private static TrancheEvent GetTrancheLoanEvent(OpenCbsReader r)
{
return new TrancheEvent{
Id = r.GetInt("tranche_id"),
Amount = r.GetMoney("tranche_amount"),
InterestRate = r.GetMoney("tranche_interest_rate").Value,
Maturity = r.GetInt("tranche_maturity"),
StartDate = r.GetDateTime("tranche_start_date"),
GracePeriod = r.GetInt("tranche_grace_period"),
FirstRepaymentDate = r.GetDateTime("tranche_first_repayment_date"),
};
}
private Person GetPersonFromReader(OpenCbsReader r)
{
Person person;
person = new Person
{
Id = r.GetInt("tiers_id"),
HomePhone = r.GetString("home_phone"),
Email = r.GetString("e_mail"),
Status = (OClientStatus)r.GetSmallInt("status"),
SecondaryEmail = r.GetString("secondary_e_mail"),
HomeType = r.GetString("home_type"),
SecondaryHomeType = r.GetString("secondary_hometype"),
ZipCode = r.GetString("zipCode"),
SecondaryZipCode = r.GetString("secondary_zipCode"),
OtherOrgComment = r.GetString("other_org_comment"),
PersonalPhone = r.GetString("personal_phone"),
SecondaryHomePhone = r.GetString("secondary_home_phone"),
SecondaryPersonalPhone = r.GetString("secondary_personal_phone"),
CashReceiptIn = r.GetNullInt("cash_input_voucher_number"),
CashReceiptOut = r.GetNullInt("cash_output_voucher_number"),
Type = r.GetChar("client_type_code") == 'I'
? OClientTypes.Person
: r.GetChar("client_type_code") == 'G'
? OClientTypes.Group
: OClientTypes.Corporate,
Scoring = r.GetNullDouble("scoring"),
LoanCycle = r.GetInt("loan_cycle"),
Active = r.GetBool("active"),
BadClient = r.GetBool("bad_client"),
OtherOrgName = r.GetString("other_org_name"),
OtherOrgAmount = r.GetMoney("other_org_amount"),
OtherOrgDebts = r.GetMoney("other_org_debts"),
City = r.GetString("city"),
Address = r.GetString("address"),
SecondaryCity = r.GetString("secondary_city"),
SecondaryAddress = r.GetString("secondary_address"),
FirstName = r.GetString("first_name"),
Sex = r.GetChar("sex"),
IdentificationData = r.GetString("identification_data"),
DateOfBirth = r.GetNullDateTime("birth_date"),
LastName = r.GetString("last_name"),
FatherName = r.GetString("father_name"),
Image = r.GetString("image_path"),
BirthPlace = r.GetString("birth_place"),
Nationality = r.GetString("nationality"),
FollowUpComment = r.GetString("follow_up_comment"),
Sponsor1 = r.GetString("sponsor1"),
Sponsor2 = r.GetString("sponsor2"),
Sponsor1Comment = r.GetString("sponsor1_comment"),
Sponsor2Comment = r.GetString("sponsor2_comment"),
FavouriteLoanOfficerId = r.GetNullInt("loan_officer_id"),
Branch = new Branch { Id = r.GetInt("branch_id") }
};
return person;
}
private static RescheduleLoanEvent GetReschedulingLoanEvent(OpenCbsReader r)
{
return new RescheduleLoanEvent{
Id = r.GetInt("rle_id"),
Amount = r.GetMoney("rle_amount"),
NbOfMaturity = r.GetInt("rle_maturity"),
PreferredFirstInstallmentDate = r.GetDateTime("rle_preferred_first_installment_date"),
PreviousInterestRate = r.GetDecimal("rle_previous_interest_rate")
};
}
private TrancheEvent GetTrancheLoanEvent(OpenCbsReader r)
{
return new TrancheEvent{
Id = r.GetInt("tranche_id"),
Amount = r.GetMoney("tranche_amount"),
InterestRate = r.GetMoney("tranche_interest_rate").Value,
Maturity = r.GetInt("tranche_maturity"),
StartDate = r.GetDateTime("tranche_start_date"),
GracePeriod = r.GetInt("tranche_grace_period"),
FirstRepaymentDate = r.GetDateTime("tranche_first_repayment_date"),
PaymentMethodId = r.GetNullInt("tranche_pm"),
PaymentMethod = r.GetNullInt("tranche_pm") == null
? null
: _paymentMethodManager.SelectPaymentMethodById(
r.GetNullInt("tranche_pm").Value)
};
}
private static LoanTransitionEvent GetLoanTransitionEvent(OpenCbsReader r)
{
return new LoanTransitionEvent
{
Id = r.GetInt("glll_id"),
Amount = r.GetMoney("glll_amount"),
};
}
private static LoanPenaltyAccrualEvent GetLoanPenaltyAccrualEvent(OpenCbsReader r)
{
return new LoanPenaltyAccrualEvent
{
Id = r.GetInt("lpae_id"),
Penalty = r.GetMoney("lpae_penalty"),
};
}
private static LoanInterestAccrualEvent GetLoanInterestAccrualEvent(OpenCbsReader r)
{
return new LoanInterestAccrualEvent
{
Id = r.GetInt("aile_id"),
Interest = r.GetMoney("aile_interest"),
};
}
private static Event GetSavingEvent(OpenCbsReader r)
{
SavingEvent e;
switch (r.GetString("code"))
{
case OSavingEvents.Deposit:
e = new SavingDepositEvent();
break;
case OSavingEvents.Withdraw:
e = new SavingWithdrawEvent();
break;
case OSavingEvents.InitialDeposit:
e = new SavingInitialDepositEvent();
break;
case OSavingEvents.CreditTransfer:
e = new SavingCreditTransferEvent();
break;
case OSavingEvents.DebitTransfer:
e = new SavingDebitTransferEvent();
break;
case OSavingEvents.SpecialOperationCredit:
e = new SavingCreditOperationEvent();
break;
case OSavingEvents.SpecialOperationDebit:
e = new SavingDebitOperationEvent();
break;
case OSavingEvents.InterBranchCreditTransfer:
e = new SavingCreditInterBranchTransferEvent();
break;
case OSavingEvents.InterBranchDebitTransfer:
e = new SavingDebitInterBranchTransferEvent();
break;
default:
throw new Exception();
}
e.Amount = r.GetMoney("se_amount");
if (e is ISavingsFees)
((ISavingsFees)e).Fee = r.GetMoney("se_fees");
if (e is SavingTransferEvent)
((SavingTransferEvent)e).RelatedContractCode = r.GetString("se_transfer_code");
return e;
}
private static LoanProduct GetProduct(OpenCbsReader r)
{
LoanProduct package = new LoanProduct();
package.Id = r.GetInt("id");
package.Delete = r.GetBool("deleted");
package.Name = r.GetString("name");
package.Code = r.GetString("code");
package.ClientType = r.GetChar("client_type");
package.LoanType = (OLoanTypes)r.GetSmallInt("loan_type");
package.RoundingType = (ORoundingType)r.GetSmallInt("rounding_type");
package.Amount = r.GetMoney("amount");
package.AmountMin = r.GetMoney("amount_min");
package.AmountMax = r.GetMoney("amount_max");
package.InterestRate = r.GetNullDecimal("interest_rate");
package.InterestRateMin = r.GetNullDecimal("interest_rate_min");
package.InterestRateMax = r.GetNullDecimal("interest_rate_max");
package.GracePeriod = r.GetNullInt("grace_period");
package.GracePeriodMin = r.GetNullInt("grace_period_min");
package.GracePeriodMax = r.GetNullInt("grace_period_max");
package.GracePeriodOfLateFees = r.GetNullInt("grace_period_of_latefees");
package.NbOfInstallments = r.GetNullInt("number_of_installments");
package.NbOfInstallmentsMin = r.GetNullInt("number_of_installments_min");
package.NbOfInstallmentsMax = r.GetNullInt("number_of_installments_max");
package.AnticipatedTotalRepaymentPenalties = r.GetNullDouble("anticipated_total_repayment_penalties");
package.AnticipatedTotalRepaymentPenaltiesMin = r.GetNullDouble("anticipated_total_repayment_penalties_min");
package.AnticipatedTotalRepaymentPenaltiesMax = r.GetNullDouble("anticipated_total_repayment_penalties_max");
package.AnticipatedPartialRepaymentPenalties = r.GetNullDouble("anticipated_partial_repayment_penalties");
package.AnticipatedPartialRepaymentPenaltiesMin = r.GetNullDouble("anticipated_partial_repayment_penalties_min");
package.AnticipatedPartialRepaymentPenaltiesMax = r.GetNullDouble("anticipated_partial_repayment_penalties_max");
package.ChargeInterestWithinGracePeriod = r.GetBool("charge_interest_within_grace_period");
package.KeepExpectedInstallment = r.GetBool("keep_expected_installment");
package.AnticipatedTotalRepaymentPenaltiesBase = (OAnticipatedRepaymentPenaltiesBases)r.GetSmallInt("anticipated_total_repayment_base");
package.AnticipatedPartialRepaymentPenaltiesBase = (OAnticipatedRepaymentPenaltiesBases)r.GetSmallInt("anticipated_partial_repayment_base");
package.NonRepaymentPenalties.InitialAmount = r.GetNullDouble("non_repayment_penalties_based_on_initial_amount");
package.NonRepaymentPenalties.OLB = r.GetNullDouble("non_repayment_penalties_based_on_olb");
package.NonRepaymentPenalties.OverDueInterest = r.GetNullDouble("non_repayment_penalties_based_on_overdue_interest");
package.NonRepaymentPenalties.OverDuePrincipal = r.GetNullDouble("non_repayment_penalties_based_on_overdue_principal");
package.NonRepaymentPenaltiesMin.InitialAmount = r.GetNullDouble("non_repayment_penalties_based_on_initial_amount_min");
package.NonRepaymentPenaltiesMin.OLB = r.GetNullDouble("non_repayment_penalties_based_on_olb_min");
package.NonRepaymentPenaltiesMin.OverDuePrincipal = r.GetNullDouble("non_repayment_penalties_based_on_overdue_principal_min");
package.NonRepaymentPenaltiesMin.OverDueInterest = r.GetNullDouble("non_repayment_penalties_based_on_overdue_interest_min");
package.NonRepaymentPenaltiesMax.InitialAmount = r.GetNullDouble("non_repayment_penalties_based_on_initial_amount_max");
package.NonRepaymentPenaltiesMax.OLB = r.GetNullDouble("non_repayment_penalties_based_on_olb_max");
package.NonRepaymentPenaltiesMax.OverDueInterest = r.GetNullDouble("non_repayment_penalties_based_on_overdue_interest_max");
package.NonRepaymentPenaltiesMax.OverDuePrincipal = r.GetNullDouble("non_repayment_penalties_based_on_overdue_principal_max");
package.AllowFlexibleSchedule = r.GetBool("allow_flexible_schedule");
package.UseGuarantorCollateral = r.GetBool("use_guarantor_collateral");
package.SetSeparateGuarantorCollateral = r.GetBool("set_separate_guarantor_collateral");
package.PercentageTotalGuarantorCollateral = r.GetInt("percentage_total_guarantor_collateral");
package.PercentageSeparateGuarantour = r.GetInt("percentage_separate_guarantor");
package.PercentageSeparateCollateral = r.GetInt("percentage_separate_collateral");
package.UseCompulsorySavings = r.GetBool("use_compulsory_savings");
package.CompulsoryAmount = r.GetNullInt("compulsory_amount");
package.CompulsoryAmountMin = r.GetNullInt("compulsory_amount_min");
package.CompulsoryAmountMax = r.GetNullInt("compulsory_amount_max");
package.UseEntryFeesCycles = r.GetBool("use_entry_fees_cycles");
//if (DatabaseHelper.GetNullAuthorizedInt32("fundingLine_id", pReader).HasValue)
//{
// package.FundingLine = new FundingLine { Id = r.GetNullInt("fundingLine_id").Value };
// package.FundingLine.Name = r.GetString("funding_line_name");
// package.FundingLine.Currency = new Currency { Id = r.GetInt("funding_line_currency_id") };
//}
if (r.GetNullInt("currency_id").HasValue)
{
package.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Code = r.GetString("currency_code"),
Name = r.GetString("currency_name"),
IsPivot = r.GetBool("currency_is_pivot"),
IsSwapped = r.GetBool("currency_is_swapped"),
UseCents = r.GetBool("currency_use_cents")
};
}
/* Line of credit */
package.DrawingsNumber = r.GetNullInt("number_of_drawings_loc");
package.AmountUnderLoc = r.GetMoney("amount_under_loc");
package.AmountUnderLocMin = r.GetMoney("amount_under_loc_min");
package.AmountUnderLocMax = r.GetMoney("amount_under_loc_max");
package.MaturityLoc = r.GetNullInt("maturity_loc");
package.MaturityLocMin = r.GetNullInt("maturity_loc_min");
package.MaturityLocMax = r.GetNullInt("maturity_loc_max");
package.ActivatedLOC = r.GetBool("activated_loc");
package.CycleId = r.GetNullInt("cycle_id");
package.CreditInsuranceMin = r.GetDecimal("insurance_min");
package.CreditInsuranceMax = r.GetDecimal("insurance_max");
package.InterestScheme = (OInterestScheme)r.GetInt("interest_scheme");
return package;
}
private static TrancheEvent GetTrancheLoanEvent(OpenCbsReader r)
{
return new TrancheEvent{
Id = r.GetInt("tranche_id"),
Amount = r.GetMoney("tranche_amount"),
InterestRate = r.GetMoney("tranche_interest_rate").Value,
Maturity = r.GetInt("tranche_maturity"),
StartDate = r.GetDateTime("tranche_start_date")
};
}
private static void SetSavingsEvent(OpenCbsReader r, SavingEvent e, ISavingProduct pProduct)
{
e.Id = r.GetInt("id");
e.ContracId = r.GetInt("contract_id");
e.Code = r.GetString("code");
e.Amount = r.GetMoney("amount");
e.Description = r.GetString("description");
e.Deleted = r.GetBool("deleted");
e.Date = r.GetDateTime("creation_date");
e.Cancelable = r.GetBool("cancelable");
e.IsFired = r.GetBool("is_fired");
e.CancelDate = r.GetNullDateTime("cancel_date");
if (pProduct != null)
{
e.ProductType = pProduct.GetType();
}
if (r.GetNullSmallInt("savings_method").HasValue)
{
e.SavingsMethod = (OSavingsMethods)r.GetNullSmallInt("savings_method").Value;
}
e.IsPending = r.GetBool("pending");
e.PendingEventId = r.GetNullInt("pending_event_id");
e.TellerId = r.GetNullInt("teller_id");
e.LoanEventId = r.GetNullInt("loan_event_id");
if (pProduct != null)
{
e.ProductType = pProduct.GetType();
}
if (e is SavingTransferEvent)
{
((SavingTransferEvent)e).RelatedContractCode = r.GetString("related_contract_code");
}
if (e is ISavingsFees)
{
((ISavingsFees)e).Fee = r.GetMoney("fees");
}
e.User = new User
{
Id = r.GetInt("user_id"),
UserName = r.GetString("user_name"),
Password = r.GetString("user_pass"),
LastName = r.GetString("last_name"),
FirstName = r.GetString("first_name")
};
e.User.SetRole(r.GetString("role_code"));
e.ClientType = OClientTypes.All;
switch (r.GetString("client_type_code"))
{
case "I":
e.ClientType = OClientTypes.Person; break;
case "C":
e.ClientType = OClientTypes.Corporate; break;
case "G":
e.ClientType = OClientTypes.Group; break;
case "V":
e.ClientType = OClientTypes.Village; break;
}
e.Branch = new Branch {
Id = r.GetInt("branch_id")
};
e.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Code = r.GetString("currency_code"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped")
};
e.SavingProduct = new SavingsBookProduct {
Id = r.GetInt("product_id")
};
}
private LoanDisbursmentEvent GetLoanDisbursmentEvent(OpenCbsReader r)
{
return new LoanDisbursmentEvent
{
Id = r.GetInt("lde_id"),
Amount = r.GetMoney("lde_amount"),
Fee = r.GetMoney("lde_fees"),
PaymentMethodId = r.GetNullInt("lde_pm"),
PaymentMethod = r.GetNullInt("lde_pm") == null
? null
: _paymentMethodManager.SelectPaymentMethodById(
r.GetNullInt("lde_pm").Value)
};
}
private static LoanEntryFeeEvent GetEntryFeeEvent(OpenCbsReader r)
{
return new LoanEntryFeeEvent
{
Id = r.GetInt("ef_id"),
Fee = r.GetMoney("ef_fee"),
Cancelable = true,
DisbursementEventId = r.GetInt("disbursement_event_id")
};
}
public List <Installment> SelectInstallment(DateTime pStartDate, DateTime pEndDate)
{
List <Installment> rows = new List <Installment>();
const string sqlText = @"SELECT [credit].[id] AS contract_id
,[expected_date] AS installment_date
,[contract_code]
,[number] AS installment_number
,[interest_repayment] + [capital_repayment] - [paid_capital] - [paid_interest] AS installment_amount
,PersonalBank.[name] AS personal_bank_name
,PersonalBank.[BIC] AS personal_bank_bic
,PersonalBank.[IBAN1] AS personal_bank_iban_1
,PersonalBank.[IBAN2] AS personal_bank_iban_2
,BusinessBank.[name] AS business_bank_name
,BusinessBank.[BIC] AS business_bank_bic
,BusinessBank.[IBAN1] AS business_bank_iban_1
,BusinessBank.[IBAN2] AS business_bank_iban_2
,Packages.code AS product_code
,Packages.Name AS product_name
,[Persons].[id] AS client_id
,[Persons].[first_name] + ' ' + [Persons].[last_name] AS client_name
FROM [Installments] i
INNER JOIN Contracts ON Contracts.id = contract_id
INNER JOIN Credit ON Credit.id = contract_id
INNER JOIN Projects ON Projects.id = project_id
INNER JOIN Persons ON Persons.id = Projects.tiers_id
INNER JOIN Banks AS PersonalBank ON PersonalBank.id = Persons.personalBank_id
INNER JOIN Banks AS BusinessBank ON BusinessBank.id = Persons.businessBank_id
INNER JOIN Packages ON Packages.id = Credit.package_id
WHERE paid_date IS NULL
AND ([interest_repayment] + [capital_repayment] - [paid_capital] - [paid_interest]) != 0
AND Credit.disbursed = 1
AND expected_date BETWEEN @startDate AND @endDate
AND (SELECT COUNT(i2.number)
FROM Installments i2
WHERE i2.contract_id = i.contract_id
AND i2.number < i.number
AND ((i2.paid_date IS NULL AND (i2.interest_repayment + i2.capital_repayment - i2.paid_capital - paid_capital) > 0)
OR (i2.paid_date IS NOT NULL AND i2.pending = 1))) = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@startDate", pStartDate);
cmd.AddParam("@endDate", pEndDate);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(rows);
}
while (reader.Read())
{
Installment row = new Installment
{
InstallmentDate = reader.GetDateTime("installment_date"),
ContractId = reader.GetInt("contract_id"),
ClientId = reader.GetInt("client_id"),
ContractCode = reader.GetString("contract_code"),
InstallmentNumber = reader.GetInt("installment_number"),
InstallmentAmount = reader.GetMoney("installment_amount"),
PersonalBankName = reader.GetString("personal_bank_name"),
PersonalBankBic = reader.GetString("personal_bank_bic"),
PersonalBankIban1 = reader.GetString("personal_bank_iban_1"),
PersonalBankIban2 = reader.GetString("personal_bank_iban_2"),
BusinessBankName = reader.GetString("business_bank_name"),
BusinessBankBic = reader.GetString("business_bank_bic"),
BusinessBankIban1 = reader.GetString("business_bank_iban_1"),
BusinessBankIban2 = reader.GetString("business_bank_iban_2"),
ProductCode = reader.GetString("product_code"),
ProductName = reader.GetString("product_name"),
ClientName = reader.GetString("client_name")
};
rows.Add(row);
}
}
}
return(rows);
}
private static AccruedInterestEvent GetLoanInterestAccruingEvent(OpenCbsReader r)
{
return new AccruedInterestEvent{
Id = r.GetInt("liae_id"),
AccruedInterest = r.GetMoney("liae_accruedInterest"),
Interest = r.GetMoney("liae_interestPrepayment"),
Rescheduled = r.GetBool("liae_rescheduled"),
InstallmentNumber = r.GetInt("liae_installmentNumber")
};
}
private static BookingToView GetBooking(Account pAccount, OpenCbsReader reader)
{
return new BookingToView
{
Date = reader.GetDateTime("date"),
EventCode = reader.GetString("event_code"),
ExchangeRate = reader.GetNullDouble("exchange_rate"),
AmountInternal = reader.GetMoney("amount"),
ContractCode = reader.GetString("contract_code"),
Direction =
(reader.GetString("debit_local_account_number") == pAccount.Number
? OBookingDirections.Debit
: OBookingDirections.Credit),
IsExported = reader.GetBool("is_exported")
};
}
private static OverdueEvent GetOverdueEvent(OpenCbsReader r)
{
return new OverdueEvent{
Id = r.GetInt("ov_id"),
OLB = r.GetMoney("ov_olb"),
OverdueDays = r.GetInt("ov_overdue_days"),
OverduePrincipal = r.GetMoney("ov_overdue_principal")
};
}
private static TrancheEvent GetTransh(OpenCbsReader r)
{
return new TrancheEvent
{
Number = r.GetInt("Number"),
StartDate = r.GetDateTime("start_date"),
Amount = r.GetMoney("amount"),
Maturity = r.GetInt("countOfInstallments"),
ApplyNewInterest = r.GetBool("ApplyNewInterest"),
InterestRate = r.GetDecimal("interest_rate"),
StartedFromInstallment = r.GetInt("started_from_installment"),
Deleted = r.GetBool("is_deleted"),
Id = r.GetInt("event_id")
};
}
private static ProvisionEvent GetProvisionEvent(OpenCbsReader r)
{
return new ProvisionEvent{
Id = r.GetInt("pe_id"),
Amount = r.GetMoney("pe_amount"),
OverdueDays = r.GetInt("pe_overdue_days")
};
}
private SavingBookContract GetSavingFromReader(OpenCbsReader pReader)
{
var savingContract = new SavingBookContract(
ApplicationSettings.GetInstance(_user.Md5),
_user);
savingContract.Product = new SavingsBookProduct
{
Id = pReader.GetInt("product_id")
};
savingContract.Id = pReader.GetInt("id");
savingContract.Code = pReader.GetString("code");
savingContract.Status = (OSavingsStatus)pReader.GetSmallInt("status");
savingContract.CreationDate = pReader.GetDateTime("creation_date");
savingContract.ClosedDate = pReader.GetNullDateTime("closed_date");
savingContract.InterestRate = pReader.GetDouble("interest_rate");
savingContract.SavingsOfficer = new User
{
Id = pReader.GetInt("savings_officer_id")
, FirstName = pReader.GetString("so_first_name")
, LastName = pReader.GetString("so_last_name")
};
savingContract.InitialAmount = pReader.GetMoney("initial_amount");
savingContract.EntryFees = pReader.GetMoney("entry_fees");
savingContract.NsgID = pReader.GetNullInt("nsg_id");
return savingContract;
}
private Person GetPersonFromReader(OpenCbsReader r)
{
Person person;
person = new Person
{
Id = r.GetInt("tiers_id"),
HomePhone = r.GetString("home_phone"),
FirstContact = r.GetNullDateTime("first_contact"),
FirstAppointment = r.GetNullDateTime("first_appointment"),
ProfessionalSituation = r.GetString("professional_situation"),
ProfessionalExperience = r.GetString("professional_experience"),
FamilySituation = r.GetString("family_situation"),
Handicapped = r.GetBool("handicapped"),
Email = r.GetString("e_mail"),
Status = (OClientStatus) r.GetSmallInt("status"),
SecondaryEmail = r.GetString("secondary_e_mail"),
HomeType = r.GetString("home_type"),
SecondaryHomeType = r.GetString("secondary_hometype"),
ZipCode = r.GetString("zipCode"),
SecondaryZipCode = r.GetString("secondary_zipCode"),
OtherOrgComment = r.GetString("other_org_comment"),
PersonalPhone = r.GetString("personal_phone"),
SecondaryHomePhone = r.GetString("secondary_home_phone"),
SecondaryPersonalPhone = r.GetString("secondary_personal_phone"),
CashReceiptIn = r.GetNullInt("cash_input_voucher_number"),
CashReceiptOut = r.GetNullInt("cash_output_voucher_number"),
Type = r.GetChar("client_type_code") == 'I'
? OClientTypes.Person
: r.GetChar("client_type_code") == 'G'
? OClientTypes.Group
: OClientTypes.Corporate,
Scoring = r.GetNullDouble("scoring"),
LoanCycle = r.GetInt("loan_cycle"),
Active = r.GetBool("active"),
BadClient = r.GetBool("bad_client"),
OtherOrgName = r.GetString("other_org_name"),
OtherOrgAmount = r.GetMoney("other_org_amount"),
OtherOrgDebts = r.GetMoney("other_org_debts"),
City = r.GetString("city"),
Address = r.GetString("address"),
SecondaryCity = r.GetString("secondary_city"),
SecondaryAddress = r.GetString("secondary_address"),
FirstName = r.GetString("first_name"),
Sex = r.GetChar("sex"),
IdentificationData = r.GetString("identification_data"),
DateOfBirth = r.GetNullDateTime("birth_date"),
LastName = r.GetString("last_name"),
HouseHoldHead = r.GetBool("household_head"),
NbOfDependents = r.GetNullInt("nb_of_dependents"),
NbOfChildren = r.GetNullInt("nb_of_children"),
ChildrenBasicEducation = r.GetNullInt("children_basic_education"),
LivestockNumber = r.GetNullInt("livestock_number"),
LivestockType = r.GetString("livestock_type"),
LandplotSize = r.GetNullDouble("landplot_size"),
HomeTimeLivingIn = r.GetNullInt("home_time_living_in"),
HomeSize = r.GetNullDouble("home_size"),
CapitalOthersEquipments = r.GetString("capital_other_equipments"),
Experience = r.GetNullInt("experience"),
NbOfPeople = r.GetNullInt("nb_of_people"),
FatherName = r.GetString("father_name"),
MotherName = r.GetString("mother_name"),
Image = r.GetString("image_path"),
StudyLevel = r.GetString("study_level"),
BirthPlace = r.GetString("birth_place"),
Nationality = r.GetString("nationality"),
UnemploymentMonths = r.GetNullInt("unemployment_months"),
SSNumber = r.GetString("SS"),
CAFNumber = r.GetString("CAF"),
HousingSituation = r.GetString("housing_situation"),
FollowUpComment = r.GetString("follow_up_comment"),
Sponsor1 = r.GetString("sponsor1"),
Sponsor2 = r.GetString("sponsor2"),
Sponsor1Comment = r.GetString("sponsor1_comment"),
Sponsor2Comment = r.GetString("sponsor2_comment"),
FavouriteLoanOfficerId = r.GetNullInt("loan_officer_id"),
Branch = new Branch {Id = r.GetInt("branch_id")},
PovertyLevelIndicators =
{
ChildrenEducation = r.GetInt("povertylevel_childreneducation"),
EconomicEducation = r.GetInt("povertylevel_economiceducation"),
HealthSituation = r.GetInt("povertylevel_socialparticipation"),
SocialParticipation = r.GetInt("povertylevel_healthsituation")
}
};
return person;
}