private FundingLineAccountingRule _getFundingLineAccountingRule(OpenCbsReader reader)
{
FundingLineAccountingRule rule = new FundingLineAccountingRule();
rule.Id = reader.GetInt("id");
rule.DebitAccount = new Account {
Id = reader.GetInt("debit_account_number_id")
};
rule.CreditAccount = new Account {
Id = reader.GetInt("credit_account_number_id")
};
rule.BookingDirection = (OBookingDirections)reader.GetSmallInt("booking_direction");
int?fundingLineId = reader.GetInt("funding_line_id");
if (fundingLineId.HasValue)
{
rule.FundingLine = new FundingLine {
Id = fundingLineId.Value
}
}
;
return(rule);
}
}
public Currency GetPivot()
{
Currency pivot = null;
const string q = "SELECT * FROM Currencies where is_pivot = 1";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(pivot);
}
while (r.Read())
{
pivot = new Currency
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
Code = r.GetString("code"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped"),
UseCents = r.GetBool("use_cents")
};
}
return(pivot);
}
}
public bool SelectAccountingRuleByChartOfAccountsId(Account account)
{
const string sqlText = @"SELECT TOP 1 id
FROM AccountingRules
WHERE debit_account_number_id = @id OR credit_account_number_id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", account.Id);
int?inUse;
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(false);
}
reader.Read();
inUse = reader.GetInt("id");
}
return(inUse != null);
}
}
}
public List <Currency> SelectAllCurrencies(SqlTransaction t)
{
List <Currency> currencies = new List <Currency>();
const string q = @"SELECT * FROM Currencies ORDER BY is_pivot DESC";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(currencies);
}
while (r.Read())
{
currencies.Add(new Currency
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
Code = r.GetString("code"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped"),
UseCents = r.GetBool("use_cents")
});
}
}
return(currencies);
}
public Account SelectChartAccount(int pAccountId)
{
const string sqlText = @"SELECT id,
account_number,
label,
debit_plus,
type_code,
account_category_id,
type,
parent_account_id, lft, rgt
FROM ChartOfAccounts
WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", pAccountId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(null);
}
reader.Read();
return(GetAccount(reader));
}
}
}
}
///// <summary>
///// This method Fill the instance of the ProvisioningTable object accessed by singleton
///// </summary>
public List <ProvisioningRate> SelectAllProvisioningRates()
{
List <ProvisioningRate> list = new List <ProvisioningRate>();
const string sqlText = @"SELECT
id,
number_of_days_min,
number_of_days_max,
provisioning_value
FROM ProvisioningRules";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(list);
}
while (reader.Read())
{
list.Add(GetProvisionningRate(reader));
}
return(list);
}
}
}
}
public Booking SelectStandardBookingById(int id)
{
const string sqlText = @"SELECT Id, [Name], debit_account_id, credit_account_id
FROM StandardBookings
WHERE Id = @Id";
Booking standardBooking = new Booking();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@Id", id);
using (OpenCbsReader reader = select.ExecuteReader())
{
while (reader.Read())
{
standardBooking.Id = reader.GetInt("Id");
standardBooking.Name = reader.GetString("Name");
Account account =
_chartOfAccounts.GetAccountById(reader.GetInt("debit_account_id"));
standardBooking.DebitAccount = account;
account =
_chartOfAccounts.GetAccountById(reader.GetInt("credit_account_id"));
standardBooking.CreditAccount = account;
}
}
}
}
return standardBooking;
}
public List <Account> SelectAllAccounts()
{
List <Account> list = new List <Account>();
const string sqlText = @"SELECT id,
account_number,
label,
debit_plus,
type_code,
account_category_id,
type,
parent_account_id, lft, rgt
FROM ChartOfAccounts
ORDER BY account_number";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(list);
}
while (reader.Read())
{
list.Add(GetAccount(reader));
}
}
return(list);
}
}
}
public Currency SelectCurrencyByName(string name)
{
const string q = @"SELECT id, code, is_pivot, is_swapped, use_cents FROM [Currencies] WHERE name = @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(null);
}
r.Read();
return(new Currency
{
Id = r.GetInt("id"),
Code = r.GetString("code"),
Name = name,
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped"),
UseCents = r.GetBool("use_cents")
});
}
}
}
public List <AccountCategory> SelectAccountCategories()
{
List <AccountCategory> list = new List <AccountCategory>();
const string sqlText = @"SELECT id, name FROM AccountsCategory";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(list);
}
while (reader.Read())
{
list.Add(new AccountCategory
{
Id = reader.GetSmallInt("id"),
Name = reader.GetString("name"),
}
);
}
return(list);
}
}
}
}
public static string GetDatabaseVersion(string database, SqlConnection connection)
{
if (connection == null)
{
throw new ArgumentException("Both connection and transaction cannot be null.");
}
if (!IsOctopusDatabase(database, connection))
{
return(string.Empty);
}
string sqlText = string.Format(
"USE [{0}] SELECT [value] FROM [TechnicalParameters] WHERE [name]='version'", database);
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, connection))
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(string.Empty);
}
reader.Read();
return(reader.GetString("value"));
}
}
private static Dictionary <string, char> GetFilelist(string file, SqlConnection connection)
{
//list of files of a backup.
Dictionary <string, char> databaseFiles = new Dictionary <string, char>();
string q = @"RESTORE FILELISTONLY FROM DISK = '{0}'";
q = string.Format(q, file);
OpenCbsCommand cmd = new OpenCbsCommand(q, connection);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (null == reader || reader.Empty)
{
return(null);
}
while (reader.Read())
{
string logicalName = reader.GetString("LogicalName");
char type = reader.GetString("Type").ToCharArray()[0];
databaseFiles.Add(logicalName, type);
}
}
return(databaseFiles);
}
public AccountCategory SelectAccountCategoriesById(int id)
{
const string sqlText = @"SELECT id, name
FROM AccountsCategory
WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(null);
}
reader.Read();
return(new AccountCategory
{
Id = reader.GetSmallInt("id"),
Name = reader.GetString("name"),
});
}
}
}
}
public List<Booking> SelectAllStandardBookings()
{
const string sqlText = @"SELECT Id, [Name], debit_account_id, credit_account_id FROM StandardBookings";
List<Booking> list = new List<Booking>();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return list;
while (reader.Read())
{
Booking standardBooking = new Booking();
standardBooking.Id = reader.GetInt("Id");
standardBooking.Name = reader.GetString("Name");
Account account =
_chartOfAccounts.GetAccountById(reader.GetInt("debit_account_id"));
standardBooking.DebitAccount = account;
account =
_chartOfAccounts.GetAccountById(reader.GetInt("credit_account_id"));
standardBooking.CreditAccount = account;
list.Add(standardBooking);
}
}
}
return list;
}
}
public ExchangeRate Select(DateTime pDate, Currency pCurrency)
{
const string q = @"SELECT exchange_date, exchange_rate, currency_id
FROM ExchangeRates
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, exchange_date)) = @date
AND currency_id = @currency";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@date", pDate.Date);
c.AddParam("@currency", pCurrency.Id);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
r.Read();
return(new ExchangeRate
{
Date = pDate,
Rate = r.GetDouble("exchange_rate"),
Currency = pCurrency
});
}
}
}
private static SavingEvent ReadEvent(OpenCbsReader r, ISavingProduct pProduct)
{
string code = r.GetString("code");
SavingEvent e = GetSavingsEvent(code);
SetSavingsEvent(r, e, pProduct);
return(e);
}
private static LoanScaleRate GetLoanScale(OpenCbsReader pReader)
{
return(new LoanScaleRate
{
Number = pReader.GetInt("id"),
ScaleMin = pReader.GetInt("ScaleMin"),
ScaleMax = pReader.GetInt("ScaleMax")
});
}
/// <summary>
/// Select all events for selected Funding Line
/// </summary>
/// <param name="fundingLine">funding line </param>
/// <returns>list of Funding Line events</returns>
public List <FundingLineEvent> SelectFundingLineEvents(FundingLine fundingLine)
{
List <FundingLineEvent> list = new List <FundingLineEvent>();
const string sqlText =
@"SELECT
[id],
[code],
[amount],
[direction],
[fundingline_id],
[deleted],
[creation_date],
[type]
FROM [FundingLineEvents]
WHERE fundingline_id = @fundingline_id
ORDER BY creation_date DESC, id DESC";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
{
cmd.AddParam("@fundingline_id", fundingLine.Id);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(list);
}
{
while (reader.Read())
{
FundingLineEvent fundingLineEvent = new FundingLineEvent
{
Id = reader.GetInt("id"),
Code = reader.GetString("code"),
Amount = reader.GetMoney("amount"),
Movement =
((OBookingDirections)
reader.GetSmallInt("direction")),
IsDeleted =
reader.GetBool("deleted"),
CreationDate =
reader.GetDateTime("creation_date"),
Type =
((OFundingLineEventTypes)
reader.GetSmallInt("type")),
FundingLine = fundingLine
};
list.Add(fundingLineEvent);
}
}
}
return(list);
}
}
private static ProvisioningRate GetProvisionningRate(OpenCbsReader reader)
{
return(new ProvisioningRate
{
Number = reader.GetInt("id"),
NbOfDaysMin = reader.GetInt("number_of_days_min"),
NbOfDaysMax = reader.GetInt("number_of_days_max"),
Rate = reader.GetDouble("provisioning_value")
});
}
/// <summary>
/// Create at "SetupPath" a OCTOPUS_('version').xml who contains diagram of the current database
/// </summary>
public static void SaveDatabaseDiagramsInXml(bool pDestination, string pDatabaseName, SqlConnection pSqlConnection)
{
string sql = string.Format(@"USE [{0}] SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns WHERE(table_name IN (SELECT table_name FROM Information_Schema.Tables
WHERE Table_Type = 'Base Table')) ORDER BY table_name" , pDatabaseName);
OpenCbsCommand command = new OpenCbsCommand(sql, pSqlConnection);
string path = !pDestination
? Path.Combine(UserSettings.GetUpdatePath, string.Format(SCHEMA_FILE_NAME, TechnicalSettings.SoftwareVersion))
: Path.Combine(UserSettings.GetUpdatePath, string.Format(LOCAL_SCHEMA_FILE_NAME, TechnicalSettings.SoftwareVersion));
XmlTextWriter xml = new XmlTextWriter(path, Encoding.Unicode);
xml.WriteStartDocument();
xml.WriteStartElement("Database");
xml.WriteAttributeString("Version", TechnicalSettings.SoftwareVersion);
xml.WriteAttributeString("SystemDate", DateTime.Today.ToString("dd MM yyyy"));
bool firstTable = true;
using (OpenCbsReader reader = command.ExecuteReader())
{
string tableName = "";
while (reader.Read())
{
string tableNameTemp = reader.GetString("table_name");
if ((tableNameTemp != "sysdiagrams") && (tableNameTemp != "dtproperties") && (!tableNameTemp.StartsWith("Tconso_SP_Octopus_Consolidation_")))
{
if (tableName != tableNameTemp && firstTable)
{
xml.WriteStartElement("table");
tableName = tableNameTemp;
xml.WriteAttributeString("name", tableNameTemp);
firstTable = false;
}
else if (tableName != tableNameTemp)
{
xml.WriteEndElement(); //table
xml.WriteStartElement("table");
tableName = tableNameTemp;
xml.WriteAttributeString("name", tableNameTemp);
}
xml.WriteStartElement("column");
xml.WriteAttributeString("name", reader.GetString("column_name"));
xml.WriteAttributeString("type", reader.GetString("data_type"));
xml.WriteAttributeString("is_nullable", reader.GetString("is_nullable").Trim().ToUpper());
xml.WriteEndElement(); //column
}
}
}
xml.WriteEndElement(); //database
xml.Close();
}
public AccountingRuleCollection SelectAllByEventType(string type)
{
const string sqlText = @"SELECT id, rule_type
FROM [AccountingRules]
WHERE deleted = 0
AND (event_type = @event_type OR @event_type = '')";
AccountingRuleCollection rules = new AccountingRuleCollection();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@event_type", type);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(rules);
}
while (reader.Read())
{
if (reader.GetChar("rule_type") == 'C')
{
rules.Add(new ContractAccountingRule {
Id = reader.GetInt("id")
});
}
}
}
}
}
List <Account> accounts = _accountManager.SelectAllAccounts();
for (int i = 0; i < rules.Count; i++)
{
if (rules[i] is ContractAccountingRule)
{
rules[i] = SelectContractAccountingRule(rules[i].Id);
}
else
{
rules[i] = SelectFundingLineAccountingRule(rules[i].Id);
}
rules[i].DebitAccount = accounts.FirstOrDefault(item => item.Id == rules[i].DebitAccount.Id);
rules[i].CreditAccount = accounts.FirstOrDefault(item => item.Id == rules[i].CreditAccount.Id);
}
return(rules);
}
public List <ExchangeRate> SelectRatesByDate(DateTime beginDate, DateTime endDate)
{
const string q =
@"SELECT
exchange_date,
exchange_rate,
currency_id,
is_pivot,
is_swapped,
name,
code
FROM ExchangeRates
INNER JOIN Currencies ON ExchangeRates.currency_id = Currencies.id
WHERE exchange_date BETWEEN @beginDate AND @endDate";
List <ExchangeRate> rates;
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@beginDate", beginDate.Date);
c.AddParam("@endDate", endDate.Date);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(null);
}
rates = new List <ExchangeRate>();
while (r.Read())
{
ExchangeRate newRate = new ExchangeRate
{
Date = r.GetDateTime("exchange_date"),
Rate = r.GetDouble("exchange_rate"),
Currency = new Currency
{
Id = r.GetInt("currency_id"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped"),
Name = r.GetString("name"),
Code = r.GetString("code")
}
};
rates.Add(newRate);
}
}
}
return(rates);
}
private static string BackupToFile(string fileName, string database, SqlConnection connection)
{
string backupDirectory;
if (TechnicalSettings.UseDemoDatabase)
{
backupDirectory = @"C:\Users\Public";
}
else
{
// To perform a backup we first need to get the appropriate backup folder, which is a bit tricky.
// First, we need to get the service name.
const string query = "SELECT @@SERVICENAME AS name";
OpenCbsCommand cmd = new OpenCbsCommand(query, connection);
string serviceName = string.Empty;
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(null);
}
reader.Read();
serviceName = reader.GetString("name");
}
// Then get the instance name from the registry
const string sqlServerKey = @"SOFTWARE\Microsoft\Microsoft SQL Server";
string key = string.Format(@"{0}\Instance Names\SQL", sqlServerKey);
string instanceName = ExtendedRegistry.GetKeyValue(key, serviceName);
if (string.IsNullOrEmpty(instanceName))
{
return(null);
}
// Finally, get the backup directory
key = string.Format(@"{0}\{1}\MSSQLServer", sqlServerKey, instanceName);
backupDirectory = ExtendedRegistry.GetKeyValue(key, "BackupDirectory");
if (string.IsNullOrEmpty(backupDirectory))
{
return(null);
}
}
string path = Path.Combine(backupDirectory, fileName);
BackupToFileImpl(path, database, connection);
return(path);
}
public List <QueuedEmail> SelectAll()
{
List <QueuedEmail> qeuedEmails = new List <QueuedEmail>();
const string q =
@"SELECT * FROM dbo.QueuedEmails where Deleted <> 1";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(qeuedEmails);
}
while (r.Read())
{
var queuedEmail = new QueuedEmail
{
Id = r.GetInt("Id"),
Priority = r.GetInt("Priority"),
From = r.GetString("From"),
FromName = r.GetString("FromName"),
To = r.GetString("To"),
ToName = r.GetString("ToName"),
ReplyTo = r.GetString("ReplyTo"),
ReplyToName = r.GetString("ReplyToName"),
CC = r.GetString("CC"),
Bcc = r.GetString("Bcc"),
Body = r.GetString("Body"),
CreatedOnUtc = r.GetDateTime("CreatedOnUtc"),
SentOnUtc = r.GetNullDateTime("SentOnUtc"),
EmailAccountId = r.GetInt("EmailAccountId"),
SentTries = r.GetInt("SentTries"),
Subject = r.GetString("Subject"),
Deleted = r.GetBool("Deleted"),
};
if (queuedEmail.EmailAccountId > 0)
{
EmailAccountManager _emailAccountManager = new EmailAccountManager(user);
queuedEmail.EmailAccount = _emailAccountManager.SelectById(queuedEmail.EmailAccountId);
}
qeuedEmails.Add(queuedEmail);
}
}
return(qeuedEmails);
}
private static Account GetAccount(OpenCbsReader pReader)
{
return(new Account
{
Id = pReader.GetInt("id"),
Number = pReader.GetString("account_number"),
Label = pReader.GetString("label"),
DebitPlus = pReader.GetBool("debit_plus"),
TypeCode = pReader.GetString("type_code"),
AccountCategory = ((OAccountCategories)pReader.GetSmallInt("account_category_id")),
Type = pReader.GetBool("type"),
ParentAccountId = pReader.GetNullInt("parent_account_id"),
Left = pReader.GetInt("lft"),
Right = pReader.GetInt("rgt")
});
}
public IAccountingRule Select(int pId)
{
const string sqlText = @"SELECT rule_type
FROM [AccountingRules]
WHERE deleted = 0
AND id = @id";
IAccountingRule rule = new ContractAccountingRule();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", pId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(null);
}
reader.Read();
if (reader.GetChar("rule_type") == 'C')
{
rule = new ContractAccountingRule {
Id = pId
}
}
;
}
}
}
if (rule is ContractAccountingRule)
{
rule = SelectContractAccountingRule(rule.Id);
}
List <Account> accounts = _accountManager.SelectAllAccounts();
rule.DebitAccount = accounts.FirstOrDefault(item => item.Id == rule.DebitAccount.Id);
rule.CreditAccount = accounts.FirstOrDefault(item => item.Id == rule.CreditAccount.Id);
return(rule);
}
/// <summary>
/// Fills General Settings with values from database
/// </summary>
///
public void FillGeneralSettings()
{
ApplicationSettings.GetInstance(_user.Md5).DeleteAllParameters();
string sqlText = "SELECT UPPER([key]) as [key], [value] FROM GeneralParameters";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
while (r.Read())
{
ApplicationSettings.GetInstance(_user.Md5).AddParameter(r.GetString("key"),
r.GetString("value"));
}
}
}
public static string GetDatabaseBranchCode(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format("USE [{0}] SELECT [value] FROM [GeneralParameters] WHERE [key]='BRANCH_CODE'", pDatabaseName);
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(string.Empty);
}
reader.Read();
return(reader.GetString("value"));
}
}
}
public Guid?GetGuid()
{
string query = "SELECT [value] FROM TechnicalParameters WHERE [name] = 'GUID'";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(query, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (!r.Empty)
{
r.Read();
string temp = r.GetString("value");
return(new Guid(temp));
}
}
return(null);
}
public int SelectFundingLineEventId(FundingLineEvent pFundingLineEvent, SqlTransaction sqlTransac,
bool includeDeleted)
{
int id = -1;
string sqlText =
@"SELECT [id]
FROM [FundingLineEvents]
WHERE [code] = @code
AND [amount] = @amount
AND [direction] = @direction
AND [type] = @type
AND [fundingline_id] = @fundinglineid";
if (!includeDeleted)
{
sqlText += " and deleted = @deleted";
}
OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac);
cmd.AddParam("@code", pFundingLineEvent.Code);
cmd.AddParam("@amount", pFundingLineEvent.Amount);
cmd.AddParam("@direction", (int)pFundingLineEvent.Movement);
cmd.AddParam("@type", (int)pFundingLineEvent.Type);
cmd.AddParam("@fundinglineid", pFundingLineEvent.FundingLine.Id);
if (!includeDeleted)
{
cmd.AddParam("@deleted", pFundingLineEvent.IsDeleted);
}
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader != null)
{
if (!reader.Empty)
{
reader.Read();
id = reader.GetInt("id");
}
}
}
return(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 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 EntryFee GetEntryFee(OpenCbsReader r)
{
EntryFee entryFee = new EntryFee
{
Id = r.GetInt("id"),
Name = r.GetString("name_of_fee"),
Min = r.GetNullDecimal("min"),
Max = r.GetNullDecimal("max"),
Value = r.GetNullDecimal("value"),
IsRate = r.GetNullBool("rate") ?? false,
Index = r.GetInt("fee_index"),
CycleId = r.GetNullInt("cycle_id")
};
return entryFee;
}
private static ExoticInstallmentsTable _GetExoticProduct(OpenCbsReader r)
{
return new ExoticInstallmentsTable
{
Id = r.GetInt("id"),
Name = r.GetString("name")
};
}
private static ExoticInstallment _GetExoticInstallment(OpenCbsReader r)
{
return new ExoticInstallment
{
Number = r.GetInt("number"),
PrincipalCoeff = r.GetDouble("principal_coeff"),
InterestCoeff =r.GetNullDouble("interest_coeff")
};
}
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 Alert GetAlert(OpenCbsReader pReader, char pType)
{
int i = 0;
var alert = new Alert();
while (i < pReader.FieldCount)
{
alert.AddParameter(pReader.GetName(i), pReader.GetValue(i));
i++;
}
alert.Type = pType;
return alert;
}
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 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 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 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 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 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 static SavingEvent ReadEvent(OpenCbsReader r, ISavingProduct pProduct)
{
string code = r.GetString("code");
SavingEvent e = GetSavingsEvent(code);
SetSavingsEvent(r, e, pProduct);
return e;
}
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 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 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 Event ReadEvent(OpenCbsReader r)
{
Event e;
if (r.GetNullInt("lde_id").HasValue)
{
e = GetLoanDisbursmentEvent(r);
}
else if (r.GetNullInt("woe_id").HasValue)
{
e = GetWriteOffEvent(r);
}
else if (r.GetNullInt("rle_id").HasValue)
{
e = GetReschedulingLoanEvent(r);
}
else if (r.GetNullInt("rpe_id").HasValue)
{
e = GetRepaymentEvent(r);
}
else if (r.GetNullInt("tranche_id").HasValue)
{
e = GetTrancheLoanEvent(r);
}
else if (r.GetNullInt("liae_id").HasValue)
{
e = GetLoanInterestAccruingEvent(r);
}
else if (r.GetNullInt("ov_id").HasValue)
{
e = GetOverdueEvent(r);
}
else if (r.GetNullInt("pe_id").HasValue)
{
e = GetProvisionEvent(r);
}
else if (r.GetNullInt("ef_id").HasValue)
{
e = GetEntryFeeEvent(r);
}
else if (r.GetNullInt("cie_id").HasValue)
{
e = GetCreditInsuranceEvent(r);
}
else if (r.GetString("code").StartsWith("S"))
{
e = GetSavingEvent(r);
}
else
{
if(r.GetString("code").Equals("LOVE"))
e = new LoanValidationEvent{Id = r.GetInt("event_id")};
else if (r.GetString("code").Equals("LOCE"))
e = new LoanCloseEvent{Id = r.GetInt("event_id")};
else
e = new RegEvent {Id = r.GetInt("event_id")};
}
GetEvent(r, e);
return e;
}
private static Account GetAccount(OpenCbsReader pReader)
{
return new Account
{
Id = pReader.GetInt("id"),
Number = pReader.GetString("account_number"),
Label = pReader.GetString("label"),
DebitPlus = pReader.GetBool("debit_plus"),
TypeCode = pReader.GetString("type_code"),
AccountCategory = ((OAccountCategories)pReader.GetSmallInt("account_category_id")),
Type = pReader.GetBool("type"),
ParentAccountId = pReader.GetNullInt("parent_account_id"),
Left = pReader.GetInt("lft"),
Right = pReader.GetInt("rgt")
};
}
private static ContractAccountingRule GetContractAccountingRule(OpenCbsReader reader)
{
ContractAccountingRule rule = new ContractAccountingRule();
rule.Id = reader.GetInt("id");
rule.EventType = new EventType
{
EventCode = reader.GetString("event_type"),
Description = reader.GetString("event_description")
};
rule.EventAttribute = new EventAttribute
{
Id = reader.GetInt("event_attribute_id"),
Name = reader.GetString("attribute_name")
};
rule.DebitAccount = new Account { Id = reader.GetInt("debit_account_number_id") };
rule.CreditAccount = new Account { Id = reader.GetInt("credit_account_number_id") };
rule.BookingDirection = (OBookingDirections)reader.GetSmallInt("booking_direction");
rule.Order = reader.GetInt("order");
rule.Description = reader.GetString("rule_description");
rule.ProductType = (OProductTypes)reader.GetSmallInt("product_type");
int? loanProductId = reader.GetNullInt("loan_product_id");
if (loanProductId.HasValue)
rule.LoanProduct = new LoanProduct { Id = loanProductId.Value };
int? currencyId = reader.GetNullInt("currency_id");
if (currencyId.HasValue)
rule.Currency = new Currency { Id = currencyId.Value };
int? savingsProductId = reader.GetNullInt("savings_product_id");
if (savingsProductId.HasValue)
rule.SavingProduct = new SavingsBookProduct { Id = savingsProductId.Value };
rule.ClientType = reader.GetChar("client_type").ConvertToClientType();
int? activityId = reader.GetNullInt("activity_id");
if (activityId.HasValue)
rule.EconomicActivity = new EconomicActivity { Id = activityId.Value };
return rule;
}
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 GetCreditInsuranceEvent(OpenCbsReader r)
{
CreditInsuranceEvent cie = new CreditInsuranceEvent();
cie.Id = r.GetInt("cie_id");
cie.Commission = r.GetDecimal("cie_commission");
cie.Principal = r.GetDecimal("cie_principal");
return cie;
}
private FundingLineAccountingRule _getFundingLineAccountingRule(OpenCbsReader reader)
{
FundingLineAccountingRule rule = new FundingLineAccountingRule();
rule.Id = reader.GetInt("id");
rule.DebitAccount = new Account { Id = reader.GetInt("debit_account_number_id") };
rule.CreditAccount = new Account { Id = reader.GetInt("credit_account_number_id") };
rule.BookingDirection = (OBookingDirections)reader.GetSmallInt("booking_direction");
int? fundingLineId = reader.GetInt("funding_line_id");
if (fundingLineId.HasValue)
rule.FundingLine = new FundingLine { Id = fundingLineId.Value };
return rule;
}
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")
};
}
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 void GetEvent(OpenCbsReader r, Event pEvent)
{
//abstract class Event attributes
string eventType = r.GetString("event_type");
pEvent.Code = eventType;
pEvent.ContracId = r.GetInt("contract_id");
pEvent.Date = r.GetDateTime("event_date");
pEvent.EntryDate = r.GetDateTime("entry_date");
pEvent.Deleted = r.GetBool("event_deleted");
pEvent.IsFired = true;
pEvent.Cancelable = true;
pEvent.ExportedDate = DateTime.MinValue;
pEvent.Comment = r.GetString("comment");
pEvent.TellerId = r.GetNullInt("teller_id");
pEvent.ParentId = r.GetNullInt("parent_id");
pEvent.CancelDate = r.GetNullDateTime("cancel_date");
pEvent.ClientType = OClientTypes.All;
switch (r.GetString("client_type_code"))
{
case "I":
pEvent.ClientType = OClientTypes.Person;
break;
case "C":
pEvent.ClientType = OClientTypes.Corporate;
break;
case "G":
pEvent.ClientType = OClientTypes.Group;
break;
case "V":
pEvent.ClientType = OClientTypes.Village;
break;
}
//User associated to the event
pEvent.User = new User
{
Id = r.GetInt("user_id"),
UserName = r.GetString("user_username"),
Password = r.GetString("user_password"),
LastName = r.GetString("user_lastname"),
FirstName = r.GetString("user_firstname")
};
pEvent.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Code = r.GetString("currency_code"),
IsPivot = r.GetBool("is_pivot"),
IsSwapped = r.GetBool("is_swapped")
};
pEvent.Branch = new Branch { Id = r.GetInt("branch_id") };
pEvent.LoanProduct = new LoanProduct { Id = r.GetInt("product_id") };
pEvent.User.SetRole(r.GetString("user_role"));
if (
eventType.Equals("ULIE") ||
eventType.Equals("ULOE")
)
return;
if (r.HasColumn("contract_code"))
pEvent.Description = r.GetString("contract_code");
}
private void GetSavingBookFromReader(SavingBookContract saving, OpenCbsReader reader)
{
saving.FlatWithdrawFees = reader.GetNullDecimal("flat_withdraw_fees");
saving.RateWithdrawFees = reader.GetNullDouble("rate_withdraw_fees");
saving.FlatTransferFees = reader.GetNullDecimal("flat_transfer_fees");
saving.RateTransferFees = reader.GetNullDouble("rate_transfer_fees");
saving.DepositFees = reader.GetNullDecimal("flat_deposit_fees");
saving.ChequeDepositFees = reader.GetNullDecimal("cheque_deposit_fees");
saving.CloseFees = reader.GetNullDecimal("flat_close_fees");
saving.ManagementFees = reader.GetNullDecimal("flat_management_fees");
saving.OverdraftFees = reader.GetNullDecimal("flat_overdraft_fees");
saving.InOverdraft = reader.GetBool("in_overdraft");
saving.AgioFees = reader.GetNullDouble("rate_agio_fees");
saving.ReopenFees = reader.GetNullDecimal("flat_reopen_fees");
saving.FlatInterBranchTransferFee = reader.GetNullDecimal("flat_ibt_fee");
saving.RateInterBranchTransferFee =reader.GetNullDouble("rate_ibt_fee");
saving.UseTermDeposit = reader.GetBool("use_term_deposit");
saving.NumberOfPeriods = reader.GetInt("term_deposit_period");
saving.TermDepositPeriodMin = reader.GetNullInt("term_deposit_period_min");
saving.TermDepositPeriodMax = reader.GetNullInt("term_deposit_period_max");
saving.TransferAccount = new SavingBookContract(ApplicationSettings.GetInstance(_user.Md5), _user)
{ Code = reader.GetString("transfer_account") };
saving.NextMaturity = reader.GetNullDateTime("next_maturity");
if (saving.UseTermDeposit)
saving.Rollover = (OSavingsRollover) reader.GetInt("rollover");
// This is the bozo's way of fetching branch information.
// Ideally it should be available through the saving's client object.
// But that object is initialized in many places so that modifying
// it does not look feasible.
// Instead, we constract a new Branch object *partially* and then
// through the OnSavingSelected below call back into the Service layer
// to finalize the construction.
saving.Branch = new Branch {Id = reader.GetInt("branch_id")};
}
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 ProvisioningRate GetProvisionningRate(OpenCbsReader reader)
{
return new ProvisioningRate
{
Number = reader.GetInt("id"),
NbOfDaysMin = reader.GetInt("number_of_days_min"),
NbOfDaysMax = reader.GetInt("number_of_days_max"),
ProvisioningValue = reader.GetDouble("provisioning_value"),
ProvisioningInterest = reader.GetDouble("provisioning_interest"),
ProvisioningPenalty = reader.GetDouble("provisioning_penalty")
};
}
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")
};
}
