///// <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);
}
}
}
}
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 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"));
}
}
/// <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);
}
}
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);
}
/// <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 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);
}
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 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);
}
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"));
}
}
}
/// <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 MessageTemplate SelectByName(string name)
{
string q =
@"SELECT * FROM dbo.MessageTemplates WHERE Name LIKE '%{0}%' AND Deleted <> 1";
q = string.Format(q, name);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
if (!r.Read())
{
return(null);
}
var messageTemplate = new MessageTemplate
{
Id = r.GetInt("Id"),
Name = r.GetString("Name"),
BccEmailAddresses = r.GetString("BccEmailAddresses"),
Subject = r.GetString("Subject"),
Body = r.GetString("Body"),
EmailBody = r.GetString("EmailBody"),
SendEmail = r.GetNullBool("SendEmail"),
SendSMS = r.GetNullBool("SendSMS"),
IsActive = r.GetBool("IsActive"),
EmailAccountId = r.GetInt("EmailAccountId"),
IsDefault = r.GetBool("IsDefault"),
Deleted = r.GetBool("Deleted"),
};
if (messageTemplate.EmailAccountId > 0)
{
EmailAccountManager _emailAccountManager = new EmailAccountManager(user);
messageTemplate.EmailAccount = _emailAccountManager.SelectById(messageTemplate.EmailAccountId);
}
return(messageTemplate);
}
}
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);
}
public object SelectParameterValue(string key)
{
string sqlText = "SELECT [value] FROM GeneralParameters WHERE [key] = @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
{
c.AddParam("@name", key);
using (OpenCbsReader r = c.ExecuteReader())
{
if (!r.Empty)
{
r.Read();
return(r.GetString("value"));
}
}
}
return(null);
}
public List <MessageTemplate> SelectAll()
{
List <MessageTemplate> messageTemplates = new List <MessageTemplate>();
const string q =
@"SELECT * FROM dbo.MessageTemplates where Deleted <> 1";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(messageTemplates);
}
while (r.Read())
{
var messageTemplate = new MessageTemplate
{
Id = r.GetInt("Id"),
Name = r.GetString("Name"),
BccEmailAddresses = r.GetString("BccEmailAddresses"),
Subject = r.GetString("Subject"),
Body = r.GetString("Body"),
EmailBody = r.GetString("EmailBody"),
SendEmail = r.GetNullBool("SendEmail"),
SendSMS = r.GetNullBool("SendSMS"),
IsActive = r.GetBool("IsActive"),
EmailAccountId = r.GetInt("EmailAccountId"),
IsDefault = r.GetBool("IsDefault"),
Deleted = r.GetBool("Deleted"),
};
if (messageTemplate.EmailAccountId > 0)
{
EmailAccountManager _emailAccountManager = new EmailAccountManager(user);
messageTemplate.EmailAccount = _emailAccountManager.SelectById(messageTemplate.EmailAccountId);
}
messageTemplates.Add(messageTemplate);
}
}
return(messageTemplates);
}
public static List <SqlAccountsDatabase> GetListDatabasesIntoAccounts(SqlConnection pSqlConnection)
{
List <SqlAccountsDatabase> databases = new List <SqlAccountsDatabase>();
const string sqlText = @"SELECT *
FROM [Accounts].[dbo].[SqlAccounts]";
if (pSqlConnection.State == ConnectionState.Closed)
{
pSqlConnection.Open();
}
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(databases);
}
while (reader.Read())
{
databases.Add(new SqlAccountsDatabase
{
Account = reader.GetString("account_name"),
Database = reader.GetString("database_name"),
Login = reader.GetString("user_name"),
Password = reader.GetString("password"),
Active = reader.GetBool("active")
});
}
}
}
foreach (SqlAccountsDatabase db in databases)
{
db.Version = GetDatabaseVersion(db.Database, pSqlConnection);
}
return(databases);
}
public static string GetDatabaseSize(string pDatabase, SqlConnection pSqlConnection)
{
string sql = string.Format("USE [{0}] EXEC sp_spaceused", pDatabase);
using (OpenCbsCommand cmd = new OpenCbsCommand(sql, pSqlConnection))
{
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(string.Empty);
}
if (reader.Read())
{
return(reader.GetString("database_size"));
}
return(string.Empty);
}
}
}
public static string GetObjectCreateScript(string db, string name, SqlConnection conn)
{
string q = string.Format("{0}..sp_helptext", db);
OpenCbsCommand cmd = new OpenCbsCommand(q, conn).AsStoredProcedure();
cmd.AddParam("@objname", name);
var buffer = new StringBuilder(2048);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (null == reader)
{
return(string.Empty);
}
while (reader.Read())
{
buffer.Append(reader.GetString("Text"));
}
}
return(buffer.ToString());
}
//public Currency SelectCurrencyByName(string pName)
//{
// const string q = @"SELECT id, name, code, is_pivot, is_swapped FROM [Currencies] where name = @name";
// using (SqlConnection conn = GetConnection())
// using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
// {
// c.AddParam("@name", pName);
// using (OpenCbsReader r = c.ExecuteReader())
// {
// if (r == null || r.Empty) return null;
// r.Read();
// return new Currency
// {
// Id = r.GetInt("id", reader),
// Code = r.GetString("code", reader),
// Name = r.GetString("name", reader),
// IsPivot = r.GetBool("is_pivot", reader),
// IsSwapped = r.GetBool("is_swapped", reader)
// };
// }
// }
//}
public bool IsThisCurrencyAlreadyExist(string pCode, string pName)
{
const string q = @"SELECT * FROM [Currencies] where name = @name AND code = @code";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@code", pCode);
c.AddParam("@name", pName);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(false);
}
return(true);
}
}
}
public static string GetDatabaseVersion(string pDatabase, SqlConnection pSqlConnection)
{
if (!IsOctopusDatabase(pDatabase, pSqlConnection))
{
return(string.Empty);
}
string sqlText = string.Format(
"USE [{0}] SELECT [value] FROM [TechnicalParameters] WHERE [name]='version'", pDatabase);
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(string.Empty);
}
reader.Read();
return(reader.GetString("value"));
}
}
private FundingLineAccountingRule SelectFundingLineAccountingRule(int pId)
{
const string sqlText = @"SELECT AccountingRules.id,
AccountingRules.debit_account_number_id,
AccountingRules.credit_account_number_id,
AccountingRules.booking_direction,
FundingLineAccountingRules.funding_line_id
FROM AccountingRules
INNER JOIN FundingLineAccountingRules ON AccountingRules.id = FundingLineAccountingRules.id
WHERE AccountingRules.id = @id";
FundingLineAccountingRule rule;
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();
rule = _getFundingLineAccountingRule(reader);
}
}
}
if (rule.FundingLine != null)
{
rule.FundingLine = _fundingLineManager.SelectFundingLineById(rule.FundingLine.Id, false);
}
return(rule);
}
public EmailAccount SelectById(int Id)
{
const string q =
@"SELECT * FROM dbo.EmailAccounts where Id = @Id AND Deleted <> 1";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@Id", Id);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
if (!r.Read())
{
return(null);
}
return(new EmailAccount
{
Id = r.GetInt("Id"),
Email = r.GetString("Email"),
DisplayName = r.GetString("DisplayName"),
Host = r.GetString("Host"),
Port = r.GetInt("Port"),
Username = r.GetString("Username"),
Password = r.GetString("Password"),
EnableSsl = r.GetBool("EnableSsl"),
UseDefaultCredentials = r.GetBool("UseDefaultCredentials"),
IsDefaultEmailAccount = r.GetBool("IsDefaultEmailAccount"),
Deleted = r.GetBool("Deleted"),
});
}
}
}
public static string GetDatabaseBranchCodeFromBranches(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format("USE [{0}] SELECT TOP 1 [code] FROM [Branches] WHERE [deleted] = 0", pDatabaseName);
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(string.Empty);
}
reader.Read();
string code = reader.GetString("code");
if (string.IsNullOrEmpty(code))
{
throw new ApplicationException(
string.Format("Emty or no branch code found for database: {0}.", pDatabaseName));
}
return(code);
}
}
}
public static List <SqlDatabaseSettings> GetOpenCbsDatabases(SqlConnection connection)
{
var result = new List <SqlDatabaseSettings>();
var query = ReadQuery("OpenCBSDatabases.sql");
query = string.Format(query, WindowsIdentity.GetCurrent().Name);
using (var command = new OpenCbsCommand(query, connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(new SqlDatabaseSettings
{
Name = reader.GetString("Name"),
BranchCode = reader.GetString("BranchCode"),
DataFileSize = reader.GetInt("DataFileSize"),
LogFileSize = reader.GetInt("LogFileSize"),
Version = reader.GetString("Version"),
});
}
}
return(result);
}
public List <QueuedSMS> SelectAll()
{
List <QueuedSMS> qeuedSMSs = new List <QueuedSMS>();
const string q =
@"SELECT * FROM dbo.QueuedSMS where Deleted <> 1";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(qeuedSMSs);
}
while (r.Read())
{
var queuedSMS = new QueuedSMS
{
Id = r.GetInt("Id"),
From = r.GetString("From"),
Recipient = r.GetString("Recipient"),
RecipientId = r.GetNullInt("RecipientId"),
ContractId = r.GetNullInt("ContractId"),
Charged = r.GetNullBool("Charged"),
Message = r.GetString("Message"),
CreatedOnUtc = r.GetDateTime("CreatedOnUtc"),
SentOnUtc = r.GetNullDateTime("SentOnUtc"),
SentTries = r.GetInt("SentTries"),
Response = r.GetString("Response"),
Deleted = r.GetBool("Deleted"),
};
qeuedSMSs.Add(queuedSMS);
}
}
return(qeuedSMSs);
}
public List <Account> SelectAllAccountsWithoutTeller(int accountId)
{
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
WHERE id NOT IN (SELECT account_id FROM dbo.Tellers WHERE deleted = 0) OR id = @id
ORDER BY account_number";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", accountId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(list);
}
while (reader.Read())
{
list.Add(GetAccount(reader));
}
}
return(list);
}
}
}
public List <FiscalYear> SelectFiscalYears()
{
const string sqlText = @"SELECT
id,
name,
open_date,
close_date
FROM dbo.FiscalYear";
List <FiscalYear> fiscalYears = new List <FiscalYear>();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty)
{
return(null);
}
while (reader.Read())
{
fiscalYears.Add(new FiscalYear
{
Id = reader.GetInt("id"),
Name = reader.GetString("name"),
CloseDate = reader.GetNullDateTime("close_date"),
OpenDate = reader.GetNullDateTime("open_date")
});
}
}
}
}
return(fiscalYears);
}
public List <EmailAccount> SelectAll()
{
List <EmailAccount> emailAccounts = new List <EmailAccount>();
const string q =
@"SELECT * FROM dbo.EmailAccounts where Deleted <> 1";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(emailAccounts);
}
while (r.Read())
{
var b = new EmailAccount
{
Id = r.GetInt("Id"),
Email = r.GetString("Email"),
DisplayName = r.GetString("DisplayName"),
Host = r.GetString("Host"),
Port = r.GetInt("Port"),
Username = r.GetString("Username"),
Password = r.GetString("Password"),
EnableSsl = r.GetBool("EnableSsl"),
UseDefaultCredentials = r.GetBool("UseDefaultCredentials"),
IsDefaultEmailAccount = r.GetBool("IsDefaultEmailAccount"),
Deleted = r.GetBool("Deleted"),
};
emailAccounts.Add(b);
}
}
return(emailAccounts);
}
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");
}
}
public static string GetDatabaseBranchCodeFromBranches(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format("USE [{0}] SELECT TOP 1 [code] FROM [Branches] WHERE [deleted] = 0", pDatabaseName);
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return string.Empty;
reader.Read();
string code = reader.GetString("code");
if (string.IsNullOrEmpty(code))
throw new ApplicationException(
string.Format("Emty or no branch code found for database: {0}.",pDatabaseName));
return code;
}
}
}
///// <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,
provisioning_interest,
provisioning_penalty
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 void SelectSavingsBookContract(SavingBookContract saving)
{
const string sqlText = @"SELECT
sbc.flat_withdraw_fees,
sbc.rate_withdraw_fees,
sbc.flat_transfer_fees,
sbc.rate_transfer_fees,
sbc.flat_deposit_fees,
sbc.flat_close_fees,
sbc.flat_management_fees,
sbc.flat_overdraft_fees,
sbc.in_overdraft,
sbc.rate_agio_fees,
sbc.cheque_deposit_fees,
sbc.flat_reopen_fees,
sbc.flat_ibt_fee,
sbc.rate_ibt_fee,
sbc.use_term_deposit,
sbc.term_deposit_period,
sbc.term_deposit_period_min,
sbc.term_deposit_period_max,
sbc.transfer_account,
sbc.rollover,
t.branch_id,
sbc.next_maturity
FROM dbo.SavingBookContracts sbc
INNER JOIN dbo.SavingContracts sc on sc.id = sbc.id
INNER JOIN dbo.Tiers t on t.id = sc.tiers_id
WHERE sbc.id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@id", saving.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Read()) return;
GetSavingBookFromReader(saving, reader);
}
//It needs to launch a new reader, that's why next code is here
saving.TransferAccount = Select(saving.TransferAccount.Code);
}
OnSavingSelected(saving);
}
public List<Loan> SelectLoansBySavingsId(int savingsId)
{
List<Loan> listLoans = new List<Loan>();
const string sqlText = @"SELECT loan_id
FROM LoansLinkSavingsBook
WHERE savings_id = @savingsId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@savingsId", savingsId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
listLoans.Add(_loanManager.SelectLoan(reader.GetInt("loan_id"), true, false, false));
}
}
}
}
return listLoans;
}
public List<ISavingsContract> SelectAllActive()
{
List<ISavingsContract> listSaving = new List<ISavingsContract>();
string sqlText = @"SELECT SavingContracts.*,
u2.first_name AS so_first_name,
u2.last_name AS so_last_name,
SavingProducts.product_type,
Users.id AS user_id,
Users.deleted,
Users.user_name,
Users.user_pass,
Users.role_code,
Users.first_name,
Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
INNER JOIN Users ON SavingContracts.user_id = Users.id
WHERE SavingContracts.status != " + ((int) OSavingsStatus.Closed);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
listSaving.Add(GetSavingFromReader(reader));
}
}
}
}
foreach (ISavingsContract saving in listSaving)
{
SelectSavingsBookContract((SavingBookContract)saving);
saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
if (_clientManager != null)
saving.Client = _clientManager.SelectClientBySavingsId(saving.Id);
}
return listSaving;
}
public SavingBookContract Select(int pSavingId)
{
SavingBookContract saving;
const string sqlText = @"SELECT SavingContracts.*,
u2.first_name AS so_first_name,
u2.last_name AS so_last_name,
SavingProducts.product_type,
Users.id AS user_id,
Users.deleted,
Users.user_name,
Users.user_pass,
Users.role_code,
Users.first_name,
Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE SavingContracts.id = @id";
using (SqlConnection conn = GetConnection())
using(var cmd = new OpenCbsCommand(sqlText, conn))
{
cmd.AddParam("@id", pSavingId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty) return null;
reader.Read();
saving = GetSavingFromReader(reader);
}
}
SelectSavingsBookContract(saving);
saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
return saving;
}
public List<Loan> SelectAllLoansOfClient(int pClientId)
{
const string sql = @"SELECT Contracts.id as id
FROM Contracts
INNER JOIN Projects ON Contracts.project_id = Projects.id
WHERE Projects.tiers_id = @id
AND Contracts.nsg_id IS NOT NULL";
List<Loan> list = new List<Loan>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sql, conn))
{
c.AddParam("@id", pClientId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty) return list;
while (r.Read())
list.Add(SelectLoan(r.GetInt("id"), true, true, true));
}
}
return list;
}
public List<Alert_v2> SelectAllAlerts(DateTime date, int userId)
{
List<Alert_v2> alerts = new List<Alert_v2>();
string q = "SELECT * FROM dbo.Alerts(@date, @user_id, @branch_id)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.CommandTimeout = 600;
c.AddParam("@date", date.Date);
c.AddParam("@user_id", userId);
c.AddParam("@branch_id", 1);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty) return alerts;
while (r.Read())
{
Alert_v2 alert = new Alert_v2
{
Address = r.GetString("address")
, Amount = r.GetMoney("amount")
, City = r.GetString("city")
, ClientName = r.GetString("client_name")
, ContractCode = r.GetString("contract_code")
, Date = r.GetDateTime("date")
, Id = r.GetInt("id")
, LateDays = r.GetInt("late_days")
, LoanOfficer = new User {Id = r.GetInt("loan_officer_id")}
, Phone = r.GetString("phone")
, Status = (OContractStatus) r.GetInt("status")
, UseCents = r.GetBool("use_cents")
, Kind = (AlertKind) r.GetInt("kind")
, BranchName = r.GetString("branch_name")
};
alerts.Add(alert);
}
}
return alerts;
}
}
/// <summary>
/// Select the latest active loan for a client
/// </summary>
/// <param name="pClientId"></param>
/// <returns></returns>
public List<Loan> SelectActiveLoans(int pClientId)
{
const string q = @"SELECT Contracts.id as id
FROM Contracts
INNER JOIN Projects ON Contracts.project_id = Projects.id
WHERE Projects.tiers_id = @id
AND Contracts.status IN (@status1, @status2, @status3, @status4)
AND Contracts.nsg_id IS NOT NULL";
List<Loan> list = new List<Loan>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pClientId);
c.AddParam("@status1", (int)OContractStatus.Active);
c.AddParam("@status2", (int)OContractStatus.Validated);
c.AddParam("@status3", (int)OContractStatus.Pending);
c.AddParam("@status4", (int)OContractStatus.Postponed);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty) return list;
while (r.Read())
list.Add(SelectLoan(r.GetInt("id"), true, true, true));
}
}
return list;
}
public List<CreditSearchResult> SearchCreditContractByCriteres(int pageNumber, string pQuery, out int count)
{
int startRow = 20*(pageNumber - 1) + 1;
int endRow = 20*pageNumber;
const string query = @"
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [user_id]) row, COUNT(1) OVER(PARTITION BY [user_id]) row_count, * FROM (
SELECT
Contracts.id,
Contracts.contract_code,
Contracts.status,
Contracts.start_date,
Contracts.align_disbursed_date,
Contracts.close_date,
Persons.identification_data as identification_data,
Credit.amount,
Credit.loanofficer_id,
Tiers.client_type_code,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(ISNULL(ISNULL(Groups.name, Persons.first_name + SPACE(1) + Persons.last_name), Corporates.name),'Error!') AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name,
Credit.[amount_min],
Credit.[amount_max],
Credit.[ir_min],
Credit.[ir_max],
Credit.[nmb_of_inst_min],
Credit.[nmb_of_inst_max],
Credit.[loan_cycle],
@user_id [user_id]
FROM Contracts
INNER JOIN Credit ON Contracts.id = Credit.id
INNER JOIN Projects ON Contracts.project_id = Projects.id
INNER JOIN Tiers ON Projects.tiers_id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id)
LEFT JOIN Users ON Users.id = Credit.loanofficer_id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id
UNION ALL
SELECT ISNULL(Contracts.id,0) AS id, ISNULL(Contracts.contract_code,'No contract') AS contract_code,
ISNULL(Contracts.status,0) AS status, ISNULL(Contracts.start_date,'01-01-1900') AS start_date,
ISNULL(Contracts.align_disbursed_date,'01-01-1900') AS align_disbursed_date,
ISNULL(Contracts.close_date,'01-01-1900') AS close_date,
Persons.identification_data AS identification_data, ISNULL(Credit.amount,0) AS amount,
ISNULL(Credit.loanofficer_id,0) AS loanofficer_id,
CASE (Tiers.client_type_code) WHEN 'I' THEN 'V' ELSE '-' END AS client_type_code,
ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS user_name,
Villages.name AS client_name,
ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS loanofficer_name,
Credit.[amount_min],
Credit.[amount_max],
Credit.[ir_min],
Credit.[ir_max],
Credit.[nmb_of_inst_min],
Credit.[nmb_of_inst_max],
Credit.[loan_cycle],
@user_id [user_id]
FROM Villages
INNER JOIN VillagesPersons ON VillagesPersons.village_id = Villages.id
INNER JOIN Persons ON Persons.id = VillagesPersons.person_id
INNER JOIN Tiers ON Persons.id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id)
INNER JOIN Projects ON Tiers.id = Projects.tiers_id
INNER JOIN Contracts ON Contracts.project_id = Projects.id
INNER JOIN Credit ON Credit.id = Contracts.id
LEFT JOIN Users ON Users.id = Credit.loanofficer_id
) R WHERE (ISNULL(contract_code, '') + ' ' + ISNULL(client_name, '') + ' ' + ISNULL([user_name], '') + ' ' + ISNULL(identification_data, '') + ' ' + ISNULL(loanofficer_name, '')) LIKE @criteria
) T WHERE row BETWEEN @startRow AND @endRow
";
count = 0;
var list = new List<CreditSearchResult>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(query, conn))
{
cmd.AddParam("user_id", User.CurrentUser.Id);
cmd.AddParam("startRow", startRow);
cmd.AddParam("endRow", endRow);
cmd.AddParam("criteria", string.Format("%{0}%", pQuery));
using (OpenCbsReader reader = cmd.ExecuteReader())
while (reader.Read())
{
if (count == 0) count = reader.GetInt("row_count");
var result = new CreditSearchResult
{
Id = reader.GetInt("id"),
ContractCode = reader.GetString("contract_code"),
ClientType = reader.GetString("client_type_code"),
ClientName = reader.GetString("client_name"),
ContractStartDate = reader.GetDateTime("start_date").ToShortDateString(),
ContractEndDate = reader.GetDateTime("close_date").ToShortDateString(),
ContractStatus = ((OContractStatus)reader.GetSmallInt("status")).ToString(),
LoanOfficer = new User { Id = reader.GetInt("loanofficer_id") }
};
list.Add(result);
}
}
return list;
}
public static string GetObjectCreateScript(string db, string name, SqlConnection conn)
{
string q = string.Format("{0}..sp_helptext", db);
OpenCbsCommand cmd = new OpenCbsCommand(q, conn).AsStoredProcedure();
cmd.AddParam("@objname", name);
var buffer = new StringBuilder(2048);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (null == reader) return string.Empty;
while (reader.Read())
{
buffer.Append(reader.GetString("Text"));
}
}
return buffer.ToString();
}
public List<SavingSearchResult> SearchSavingContractByCritere(int pPageNumber, string pQuery, bool all, bool activeContractOnly)
{
List<SavingSearchResult> list = new List<SavingSearchResult>();
string sql = @"SELECT
SavingContracts.id,
SavingContracts.code AS contract_code,
SavingContracts.status AS contract_status,
SavingContracts.creation_date as start_date,
SavingContracts.closed_date as end_date,
SavingProducts.product_type as product_type,
Persons.identification_data as identification_data,
Tiers.client_type_code,
Tiers.id AS client_id,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name,
SavingContracts.user_id AS loan_officer_id,
SavingProducts.currency_id
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id";
sql += " WHERE 1=1 ";
if (activeContractOnly)
sql += " AND SavingContracts.status=1 ";
if (!all)
{
sql += @" AND Tiers.branch_id in (
select branch_id
from dbo.UsersBranches
WHERE user_id = @user_id
)";
}
sql += ") maTable";
const string closeWhere = @" WHERE ( contract_code LIKE @contractCode
OR client_name LIKE @clientName
OR user_name LIKE @UserName
OR identification_data LIKE @numberPassport
OR loanofficer_name LIKE @loanofficerName )) maTable";
QueryEntity q = new QueryEntity(pQuery, sql, closeWhere);
string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pPageNumber - 1) * 20);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(pSqlText, conn))
{
foreach (var item in q.DynamiqParameters())
{
select.AddParam(item.Key, string.Format("%{0}%", item.Value));
}
select.AddParam("@user_id", User.CurrentUser.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
SavingSearchResult result =
new SavingSearchResult
{
Id = reader.GetInt("id"),
ClientId = reader.GetInt("client_id"),
ContractCode = reader.GetString("contract_code"),
Status = (OSavingsStatus)reader.GetSmallInt("contract_status"),
ClientTypeCode = reader.GetString("client_type_code")
};
switch (result.ClientTypeCode)
{
case "I": result.ClientType = OClientTypes.Person; break;
case "G": result.ClientType = OClientTypes.Group; break;
case "V": result.ClientType = OClientTypes.Village; break;
case "C": result.ClientType = OClientTypes.Corporate; break;
default: result.ClientType = OClientTypes.Person; break;
}
result.ClientName = reader.GetString("client_name");
result.ContractStartDate = reader.GetDateTime("start_date");
result.ContractEndDate = reader.GetNullDateTime("end_date");
result.ContractType = reader.GetString("product_type");
result.LoanOfficer = new User { Id = reader.GetInt("loan_officer_id") };
result.CurrencyId = reader.GetInt("currency_id");
list.Add(result);
}
}
}
return list;
}
}
public List<LoanEntryFee> SelectInstalledLoanEntryFees(int loanId)
{
List<LoanEntryFee> loanEntryFees = new List<LoanEntryFee>();
string q =
@"SELECT id
,[entry_fee_id]
,[fee_value]
FROM [dbo].[CreditEntryFees]
WHERE [credit_id]=@credit_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@credit_id", loanId);
using (OpenCbsReader r = c.ExecuteReader())
{
while (r.Read())
{
LoanEntryFee lef = new LoanEntryFee();
lef.Id = r.GetInt("id");
lef.ProductEntryFeeId = r.GetInt("entry_fee_id");
lef.FeeValue = r.GetDecimal("fee_value");
loanEntryFees.Add(lef);
}
}
}
return loanEntryFees;
}
public List<ISavingsContract> SelectAll(string pSavingCode)
{
List<ISavingsContract> listSaving = new List<ISavingsContract>();
const string sqlText = @"SELECT SavingContracts.*,
u2.first_name AS so_first_name,
u2.last_name AS so_last_name,
SavingProducts.product_type,
Users.id AS user_id,
Users.deleted,
Users.user_name,
Users.user_pass,
Users.role_code,
Users.first_name,
Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE SavingContracts.Code LIKE @code";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@code", string.Format("%{0}%", pSavingCode));
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return new List<ISavingsContract>();
while (reader.Read())
{
ISavingsContract saving = GetSavingFromReader(reader);
listSaving.Add(saving);
}
}
}
foreach (ISavingsContract saving in listSaving)
{
SelectSavingsBookContract((SavingBookContract)saving);
saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
}
return listSaving;
}
public List<DateTime> SelectInstallmentDatesForVillageActiveContracts(int villageId)
{
List<DateTime> installmentDates = new List<DateTime>();
string q =
@"SELECT
Installments.expected_date
FROM dbo.VillagesPersons vp
INNER JOIN dbo.Projects pr ON pr.tiers_id = vp.person_id
INNER JOIN dbo.Contracts c ON c.project_id = pr.id
INNER JOIN dbo.Installments ON Installments.contract_id = c.id
WHERE village_id = @village_id
AND c.[status] = 5
GROUP BY Installments.expected_date
ORDER BY Installments.expected_date";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@village_id", villageId);
using (OpenCbsReader r = c.ExecuteReader())
{
while (r.Read())
{
installmentDates.Add(r.GetDateTime("expected_date"));
}
}
}
return installmentDates;
}
public KeyValuePair<int, string>[] SelectClientSavingBookCodes(int tiersId, int? currencyId)
{
const string sqlText = @"SELECT sc.id, sc.code
FROM SavingContracts sc
INNER JOIN SavingProducts sp
ON sc.product_id = sp.id AND (@currency_id IS NULL OR sp.currency_id = @currency_id)
WHERE tiers_id = @tiersId AND closed_date IS NULL";
using (SqlConnection conn = GetConnection())
using (var command = new OpenCbsCommand(sqlText, conn))
{
command.AddParam("tiersId", tiersId);
command.AddParam("currency_id", currencyId);
var result = new List<KeyValuePair<int, string>>();
using (var reader = command.ExecuteReader())
{
while(reader.Read())
{
var savingId = reader.GetInt("id");
var savingCode = reader.GetString("code");
result.Add(
new KeyValuePair<int, string>(savingId, savingCode)
);
}
return result.ToArray();
}
}
}
/// <summary>
/// Returns Loan
/// </summary>
/// <param name="pLoanId">Loan id</param>
/// <param name="pAddGeneralInformation">add product, events, chartOfAccount, and funding line</param>
/// <param name="pAddOptionalInformation"></param>
/// <param name="pAddOptionalEventInformation"></param>
/// <returns></returns>
public Loan SelectLoan(int pLoanId, bool pAddGeneralInformation, bool pAddOptionalInformation, bool pAddOptionalEventInformation)
{
Loan loan;
int productId;
int installmentTypeId;
int loanOfficerId;
int fundingLineId;
const string q = @"SELECT Credit.id AS credit_id,
Credit.package_id,
Credit.amount,
Credit.interest_rate,
Credit.installment_type,
Credit.nb_of_installment,
Credit.non_repayment_penalties_based_on_overdue_principal,
Credit.non_repayment_penalties_based_on_overdue_interest,
Credit.non_repayment_penalties_based_on_olb,
Credit.non_repayment_penalties_based_on_initial_amount,
Credit.anticipated_total_repayment_penalties,
Credit.anticipated_partial_repayment_penalties,
Credit.anticipated_total_repayment_base,
Credit.anticipated_partial_repayment_base,
Credit.disbursed, Credit.loanofficer_id,
Credit.fundingLine_id,
Credit.grace_period,
Credit.written_off,
Credit.rescheduled,
Credit.bad_loan,
Credit.grace_period_of_latefees,
Contracts.contract_code,
Tiers.client_type_code,
Credit.synchronize,
Credit.schedule_changed,
ISNULL(Credit.amount_under_loc, 0) AS amount_under_loc,
Contracts.branch_code,
Contracts.creation_date,
Contracts.start_date,
ISNULL(Contracts.align_disbursed_date, Contracts.start_date) AS align_disbursed_date,
Contracts.close_date,
Contracts.closed,
Contracts.status,
Contracts.credit_commitee_date,
Contracts.credit_commitee_comment,
Contracts.credit_commitee_code,
Contracts.loan_purpose,
Contracts.comments,
Contracts.nsg_id,
Contracts.activity_id,
Contracts.preferred_first_installment_date,
LoansLinkSavingsBook.loan_percentage,
Credit.[amount_min],
Credit.[amount_max],
Credit.[ir_min],
Credit.[ir_max],
Credit.[nmb_of_inst_min],
Credit.[nmb_of_inst_max],
Credit.[loan_cycle],
Credit.insurance
FROM Contracts
INNER JOIN Credit ON Contracts.id = Credit.id
INNER JOIN Projects ON Contracts.project_id = Projects.id
INNER JOIN Tiers ON Projects.tiers_id = Tiers.id
LEFT JOIN LoansLinkSavingsBook ON LoansLinkSavingsBook.loan_id = Contracts.id
WHERE Credit.id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pLoanId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty) return null;
r.Read();
loan = _GetLoan(r);
installmentTypeId = r.GetInt("installment_type");
productId = r.GetInt("package_id");
loanOfficerId = r.GetInt("loanofficer_id");
fundingLineId = r.GetInt("fundingLine_id");
}
}
loan.InstallmentType = _installmentTypeManagement.SelectInstallmentType(installmentTypeId);
loan.InstallmentList = _installmentManagement.SelectInstallments(loan.Id);
loan.EconomicActivity = _economicActivityManager.SelectEconomicActivity(loan.EconomicActivityId);
loan.GivenTranches = SelectTranches(loan.Id);
if (pAddGeneralInformation)
{
loan.Product = _packageManager.Select(productId);
loan.Events = _eventManagement.SelectEvents(loan.Id);
foreach (Event loanEvent in loan.Events)
{
if (loanEvent is LoanDisbursmentEvent)
{
if (((LoanDisbursmentEvent) loanEvent).PaymentMethodId==null) continue;
int paymentMethodId = (int)((LoanDisbursmentEvent) loanEvent).PaymentMethodId;
loanEvent.PaymentMethod = _paymentMethodManager.SelectPaymentMethodById(paymentMethodId);
}
if (loanEvent is RepaymentEvent)
{
if (((RepaymentEvent)loanEvent).PaymentMethodId==null) continue;
int paymentMethodId = (int) ((RepaymentEvent) loanEvent).PaymentMethodId;
loanEvent.PaymentMethod = _paymentMethodManager.SelectPaymentMethodById(paymentMethodId);
}
}
if (_projectManager != null)
loan.Project = _projectManager.SelectProjectByContractId(loan.Id);
foreach (Installment installment in loan.InstallmentList)
{
installment.OLB = loan.CalculateExpectedOlb(installment.Number, loan.Product.KeepExpectedInstallment);
}
}
if (pAddOptionalInformation)
{
loan.FundingLine = _fundingLineManager.SelectFundingLineById(fundingLineId, false);
loan.LoanOfficer = _userManager.SelectUser(loanOfficerId, true);
loan.Guarantors = GetGuarantors(loan.Id);
loan.Collaterals = GetCollaterals(loan.Id);
if (loan.ClientType == OClientTypes.Group)
loan.LoanShares.AddRange(GetLoanShareAmount(pLoanId));
}
return loan;
}
public List<ISavingsContract> SelectSavings(int pClientId)
{
const string sqlText = @"SELECT SavingContracts.[id]
,SavingContracts.[product_id]
,SavingContracts.[user_id]
,SavingContracts.[code]
,SavingContracts.[tiers_id]
,SavingContracts.[creation_date]
,SavingContracts.[interest_rate]
,SavingContracts.[status]
,SavingContracts.[closed_date]
,SavingContracts.[savings_officer_id]
,SavingContracts.[initial_amount]
,SavingContracts.[entry_fees]
,SavingContracts.[nsg_id]
,u2.first_name AS so_first_name
,u2.last_name AS so_last_name
,SavingProducts.product_type
,Users.id AS user_id
,Users.deleted
,Users.user_name
,Users.user_pass
,Users.role_code
,Users.first_name
,Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE SavingContracts.tiers_id = @tiers_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
List<ISavingsContract> savings = new List<ISavingsContract>();
select.AddParam("@tiers_id", pClientId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return new List<ISavingsContract>();
while (reader.Read())
{
savings.Add(GetSavingFromReader(reader));
}
}
foreach (ISavingsContract saving in savings)
{
SelectSavingsBookContract((SavingBookContract)saving);
saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
((SavingBookContract)saving).Loans = SelectLoansBySavingsId(saving.Id);
}
return savings;
}
}
public List<Loan> SelectLoansByClientId(int clientId)
{
List<int> ids = new List<int>();
const string q = @"SELECT Cont.id FROM Contracts AS Cont
INNER JOIN Projects AS Pr ON Cont.project_id = Pr.id
INNER JOIN Tiers AS Tr ON Tr.id = Pr.tiers_id
WHERE Tr.id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", clientId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty) return new List<Loan>();
while (r.Read())
ids.Add(r.GetInt("id"));
}
}
List<Loan> loans = new List<Loan>();
foreach (int id in ids)
loans.Add(SelectLoan(id, true, true, true));
return loans;
}
public SavingBookContract SelectSavingsByLoanId(int pLoanId, bool loadLoans)
{
SavingBookContract saving;
const string sqlText = @"SELECT
SavingContracts.*,
u2.first_name AS so_first_name,
u2.last_name AS so_last_name,
SavingProducts.product_type,
Users.id AS [user_id],
Users.deleted,
Users.user_name,
Users.user_pass,
Users.role_code,
Users.first_name,
Users.last_name
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN LoansLinkSavingsBook ON LoansLinkSavingsBook.savings_id = SavingContracts.id
INNER JOIN Users ON SavingContracts.[user_id] = Users.id
INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
WHERE LoansLinkSavingsBook.loan_id = @loanId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@loanId", pLoanId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return null;
reader.Read();
saving = (SavingBookContract)GetSavingFromReader(reader);
}
}
if (saving != null)
{
saving.Product = (SavingsBookProduct)_savingProductManager.SelectSavingProduct(saving.Product.Id);
saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
if (loadLoans)
saving.Loans = SelectLoansBySavingsId(saving.Id);
}
return saving;
}
public AlertStock SelectLoansByLoanOfficer(int pLoanOfficerId)
{
string addLoanOfficer = "";
if (pLoanOfficerId != 0)
addLoanOfficer = " AND (Credit.loanofficer_id = @loanOfficerId) ";
string sqlTextRepaymentAlert =
string.Format(@"SELECT Credit.id AS contract_id,
Credit.interest_rate,
Contracts.contract_code,
Contracts.creation_date,
Contracts.start_date,
Contracts.align_disbursed_date,
Contracts.close_date,
CASE Contracts.status
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'Validated'
WHEN 3 THEN 'Refused'
WHEN 4 THEN 'Abandoned'
WHEN 5 THEN 'Active'
WHEN 6 THEN 'Closed'
WHEN 7 THEN 'WrittenOff'
ELSE '-'
END AS loan_status,
InstallmentTypes.name AS installment_types,
ISNULL(Groups.name, ISNULL(Persons.first_name + ' ' + Persons.last_name,Corporates.name)) AS client_name,
Districts.name as district_name, Installments.capital_repayment + Installments.interest_repayment
- Installments.paid_capital - Installments.paid_interest AS amount,
Installments.expected_date AS effect_date, ISNULL(( SELECT SUM(principal) FROM contractEvents
INNER JOIN repaymentEvents ON repaymentEvents.id = contractEvents.id WHERE is_deleted = 0
AND contract_id = Contracts.id ), 0) AS olb FROM Credit
INNER JOIN Contracts ON Contracts.id = Credit.id
INNER JOIN Installments ON Installments.contract_id = Credit.id
INNER JOIN InstallmentTypes ON dbo.Credit.installment_type = dbo.InstallmentTypes.id
INNER JOIN Projects ON Contracts.project_id = Projects.id
INNER JOIN Tiers ON Projects.tiers_id = Tiers.id
LEFT OUTER JOIN Corporates ON Tiers.id=Corporates.id
LEFT OUTER JOIN Persons ON dbo.Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON dbo.Tiers.id = Groups.id
LEFT OUTER JOIN Districts ON dbo.Tiers.district_id = Districts.id
WHERE ( Installments.capital_repayment + Installments.interest_repayment - Installments.paid_capital - Installments.paid_interest > 0.02 ) AND Contracts.status = 5
{0}
ORDER BY contract_id, effect_date DESC", addLoanOfficer);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmdSelectRepayment = new OpenCbsCommand(sqlTextRepaymentAlert, conn))
{
if (pLoanOfficerId != 0)
cmdSelectRepayment.AddParam("@loanOfficerId", pLoanOfficerId);
using (OpenCbsReader reader = cmdSelectRepayment.ExecuteReader())
{
if(reader.Empty) return new AlertStock();
AlertStock alertStock = new AlertStock();
while (reader.Read())
{
alertStock.Add(GetAlert(reader, 'R'));
}
return alertStock;
}
}
}
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");
}
}
}
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 static string GetDatabaseSize(string pDatabase, SqlConnection pSqlConnection)
{
string sql = string.Format("USE [{0}] EXEC sp_spaceused", pDatabase);
using (OpenCbsCommand cmd = new OpenCbsCommand(sql, pSqlConnection))
{
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty) return string.Empty;
if (reader.Read())
{
return reader.GetString("database_size");
}
return string.Empty;
}
}
}
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 static List<SqlAccountsDatabase> GetListDatabasesIntoAccounts(SqlConnection pSqlConnection)
{
List<SqlAccountsDatabase> databases = new List<SqlAccountsDatabase>();
const string sqlText = @"SELECT *
FROM [Accounts].[dbo].[SqlAccounts]";
if (pSqlConnection.State == ConnectionState.Closed)
pSqlConnection.Open();
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty) return databases;
while (reader.Read())
{
databases.Add(new SqlAccountsDatabase
{
Account = reader.GetString("account_name"),
Database = reader.GetString("database_name"),
Login = reader.GetString("user_name"),
Password = reader.GetString("password"),
Active = reader.GetBool("active")
});
}
}
}
foreach (SqlAccountsDatabase db in databases)
{
db.Version = GetDatabaseVersion(db.Database, pSqlConnection);
}
return databases;
}
public int GetNumberSavingContract(string pQuery, bool all, bool activeContractsOnly)
{
string sql =@"SELECT TOP 100 percent
SavingContracts.id,
SavingContracts.code AS contract_code,
SavingContracts.creation_date as start_date,
Persons.identification_data as identification_data,
Tiers.client_type_code,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name
FROM SavingContracts
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id";
sql += " WHERE 1=1 ";
if (activeContractsOnly)
sql += " AND SavingContracts.[status]=1 ";
if (!all)
{
sql += @" AND Tiers.branch_id in
(
select
branch_id
from
dbo.UsersBranches
WHERE user_id = @user_id
)";
}
sql += @") maTable";
const string closeWhere = @" WHERE (contract_code LIKE @contractCode
OR client_name LIKE @clientName
OR user_name LIKE @userName
OR identification_data LIKE @numberPassport
OR loanofficer_name LIKE @loanofficerName )) maTable";
QueryEntity q = new QueryEntity(pQuery, sql, closeWhere);
string sqlText = q.ConstructSQLEntityNumberProxy();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
foreach (var item in q.DynamiqParameters())
{
select.AddParam(item.Key, string.Format("%{0}%", item.Value));
}
select.AddParam("@user_id", User.CurrentUser.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return 0;
reader.Read();
return reader.GetInt(0);
}
}
}
public static List<SqlDatabaseSettings> GetOpenCbsDatabases(SqlConnection connection)
{
var result = new List<SqlDatabaseSettings>();
var query = ReadQuery("OpenCBSDatabases.sql");
query = string.Format(query, WindowsIdentity.GetCurrent().Name);
using (var command = new OpenCbsCommand(query, connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(new SqlDatabaseSettings
{
Name = reader.GetString("Name"),
BranchCode = reader.GetString("BranchCode"),
DataFileSize = reader.GetInt("DataFileSize"),
LogFileSize = reader.GetInt("LogFileSize"),
Version = reader.GetString("Version"),
});
}
}
return result;
}
public List<LoanShare> GetLoanShareAmount(int pContractId)
{
const string q = @"SELECT lsa.group_id,
lsa.person_id,
lsa.amount,
p.first_name + ' ' + p.last_name AS person_name
FROM LoanShareAmounts AS lsa
LEFT JOIN Persons AS p ON p.id = lsa.person_id
WHERE contract_id = @contract_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("contract_id", pContractId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty) return new List<LoanShare>();
List<LoanShare> loanShares = new List<LoanShare>();
while (r.Read())
{
loanShares.Add(new LoanShare { PersonId = r.GetInt("person_id"),
PersonName = r.GetString("person_name"),
Amount = r.GetMoney("amount")
});
}
return loanShares;
}
}
}
