/// <summary>
/// Method to add a TrancheEvent into database. We use the NullableTypes to make the correspondance between
/// nullable int, decimal and double types in database and our own objects
/// </summary>
/// <param name="trancheEvent">TrancheEvent Object</param>
/// <returns>The id of the Tranche Event which has been added</returns>
public int Add(TrancheEvent trancheEvent)
{
const string sqlText = @"
INSERT INTO [TrancheEvents]
( [id]
,[interest_rate]
,[amount]
,[maturity]
,[start_date]
,[applied_new_interest])
VALUES
(@Id,
@InterestRate,
@Amount,
@Maturity,
@StartDate,
@applied_new_interest)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (var cmd = new OpenCbsCommand(sqlText, conn))
{
SetTrancheEvent(cmd, trancheEvent);
return int.Parse(cmd.ExecuteScalar().ToString());
}
}
public void Delete(SqlTransaction pSqlTransaction)
{
const string sqltext = "DELETE FROM LoanScale";
using (OpenCbsCommand delete = new OpenCbsCommand(sqltext, pSqlTransaction.Connection, pSqlTransaction))
{
delete.ExecuteNonQuery();
}
}
public void DeleteAllProvisioningRules(SqlTransaction sqlTransac)
{
const string sqlText = "DELETE FROM ProvisioningRules";
using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
delete.ExecuteNonQuery();
}
}
public void InsertLoanScale(LoanScaleRate pLoanScaleRate, SqlTransaction pSqlTransaction)
{
const string sqlText = @"INSERT INTO LoanScale(id,ScaleMin, ScaleMax) VALUES(@number,@Min, @Max)";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransaction.Connection, pSqlTransaction))
{
SetLoanScale(insert, pLoanScaleRate);
insert.ExecuteNonQuery();
}
}
public void Add(DateTime pDate, double pRate, Currency pCurrency)
{
const string q = "INSERT INTO [ExchangeRates] ([exchange_date], [exchange_rate],[currency_id]) " +
"VALUES(@date,@rate,@currency)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
SetExchangeRate(c, pDate.Date, pRate,pCurrency);
c.ExecuteNonQuery();
}
}
public void DeleteStandardBooking(int id)
{
const string sqlText = @"DELETE FROM StandardBookings WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
{
insertIntoTable.AddParam("@Id", id);
insertIntoTable.ExecuteNonQuery();
}
}
}
public void CreateStandardBooking(Booking booking)
{
const string sqlText = @"INSERT INTO StandardBookings([Name], debit_account_id, credit_account_id)
VALUES (@name, @debit_account_id, @credit_account_id)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
{
insertIntoTable.AddParam("@name", booking.Name);
insertIntoTable.AddParam("@debit_account_id", booking.DebitAccount.Id);
insertIntoTable.AddParam("@credit_account_id", booking.CreditAccount.Id);
insertIntoTable.ExecuteNonQuery();
}
}
public int Add(Currency pCurrency, SqlTransaction t)
{
const string q = @"INSERT INTO [Currencies] ([name], [code], [is_pivot], [is_swapped],use_cents)
VALUES(@name, @code, @is_pivot, @is_swapped,@use_cents) SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
{
c.AddParam("@name", pCurrency.Name);
c.AddParam("@code", pCurrency.Code);
c.AddParam("@is_pivot", pCurrency.IsPivot);
c.AddParam("@is_swapped", pCurrency.IsSwapped);
c.AddParam("@use_cents", pCurrency.UseCents);
return int.Parse(c.ExecuteScalar().ToString());
}
}
public void MakeEventExported(int pSavingEventId, SqlTransaction sqlTransac)
{
const string q = @"UPDATE [SavingEvents]
SET [is_exported] = @is_exported
WHERE [id] = @id";
using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac))
{
c.AddParam("@is_exported", true);
c.AddParam("@id", pSavingEventId);
c.ExecuteNonQuery();
}
}
public static int UpdateAccountActive(string pAccountName, bool pActive, SqlConnection pSqlConnection)
{
const string sqlText = @"UPDATE [Accounts].[dbo].[SqlAccounts]
SET active = @active
WHERE [account_name] = @accountName";
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection))
{
update.AddParam("@active", pActive);
update.AddParam("@accountName", pAccountName);
return(update.ExecuteNonQuery());
}
}
public int Add(SavingEvent pSavingEvent, int pSavingContractId, SqlTransaction sqlTransac)
{
const string q = @"INSERT INTO [SavingEvents](
[user_id],
[contract_id],
[code],
[amount],
[description],
[reference_number],
[deleted],
[creation_date],
[cancelable],
[is_fired],
[related_contract_code],
[fees],
[savings_method],
[pending],
[is_local],
[pending_event_id],
[teller_id],
[loan_event_id])
VALUES(
@user_id,
@contract_id,
@code,
@amount,
@description,
@reference_number,
@deleted,
@creation_date,
@cancelable,
@is_fired,
@related_contract_code,
@fees,
@savings_method,
@pending,
@is_local,
@pending_event_id,
@teller_id,
@loan_event_id)
SELECT CONVERT(int, SCOPE_IDENTITY())" ;
using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac))
{
SetInsertCommandForSavingEvent(c, pSavingEvent, pSavingContractId);
pSavingEvent.Id = Convert.ToInt32(c.ExecuteScalar());
return(pSavingEvent.Id);
}
}
public void DeleteAccountingRule(IAccountingRule pRule)
{
const string sqlText = @"UPDATE [AccountingRules]
SET [deleted] = 1
WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, conn))
{
delete.AddParam("@id", pRule.Id);
delete.ExecuteNonQuery();
}
}
}
public int UpdateSelectedParameter(string pName, int pNewValue)
{
ApplicationSettings.GetInstance(_user.Md5).UpdateParameter(pName, pNewValue);
string sql = "UPDATE GeneralParameters SET [value] = @value WHERE upper([key]) = @key";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sql, conn))
{
c.AddParam("@value", pNewValue.ToString());
c.AddParam("@key", pName);
return(c.ExecuteNonQuery());
}
}
public void UpdateEventDescription(int pSavingEventId, string pDescription)
{
const string q = @"UPDATE [SavingEvents]
SET [description] = @description
WHERE [id] = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@description", pDescription);
c.AddParam("@id", pSavingEventId);
c.ExecuteNonQuery();
}
}
public int GetContractQuantityByCurrencyId(int currencyId)
{
const string q = @"
SELECT COUNT(Credit.id) AS contract_quantity
FROM [dbo].[Currencies] AS Cur
INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id
INNER JOIN Credit ON Credit.package_id=Pack.id
WHERE Cur.id=@currency_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency_id", currencyId);
return (int)c.ExecuteScalar();
}
}
public void ChangePendingEventStatus(int pSavingEventId, bool isPending)
{
const string q = @"UPDATE [SavingEvents]
SET [pending] = @pending
WHERE [id] = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@pending", isPending);
c.AddParam("@id", pSavingEventId);
c.ExecuteNonQuery();
}
}
public double GetNearestRate(DateTime pDate, Currency pCurrency)
{
const string q = @"SELECT TOP 1 exchange_rate
FROM ExchangeRates
WHERE exchange_date <= @date AND currency_id = @currency
ORDER BY exchange_date DESC";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency", pCurrency.Id);
c.AddParam("@date", pDate);
return(Convert.ToDouble(c.ExecuteScalar()));
}
}
public void DeleteTiers()
{
OpenCbsCommand deletePersonGroupBelonging = new OpenCbsCommand("DELETE PersonGroupBelonging", SqlConnection);
deletePersonGroupBelonging.ExecuteNonQuery();
OpenCbsCommand deletePersons = new OpenCbsCommand("DELETE Persons", SqlConnection);
deletePersons.ExecuteNonQuery();
OpenCbsCommand deleteGroups = new OpenCbsCommand("DELETE Groups", SqlConnection);
deleteGroups.ExecuteNonQuery();
OpenCbsCommand deleteTiers = new OpenCbsCommand("DELETE Tiers", SqlConnection);
deleteTiers.ExecuteNonQuery();
}
public int Add(Currency pCurrency, SqlTransaction t)
{
const string q = @"INSERT INTO [Currencies] ([name], [code], [is_pivot], [is_swapped],use_cents)
VALUES(@name, @code, @is_pivot, @is_swapped,@use_cents) SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
{
c.AddParam("@name", pCurrency.Name);
c.AddParam("@code", pCurrency.Code);
c.AddParam("@is_pivot", pCurrency.IsPivot);
c.AddParam("@is_swapped", pCurrency.IsSwapped);
c.AddParam("@use_cents", pCurrency.UseCents);
return(int.Parse(c.ExecuteScalar().ToString()));
}
}
public InstallmentType AddBiWeeklyInstallmentType()
{
InstallmentType biWeekly = new InstallmentType();
biWeekly.Name = "Bi-Weekly";
biWeekly.NbOfDays = 14;
biWeekly.NbOfMonths = 0;
string sqlText = "INSERT INTO InstallmentTypes (name,nb_of_days,nb_of_months) VALUES ('" + biWeekly.Name + "'," + biWeekly.NbOfDays + "," + biWeekly.NbOfMonths + ") SELECT SCOPE_IDENTITY()";
OpenCbsCommand insert = new OpenCbsCommand(sqlText, SqlConnection);
biWeekly.Id = int.Parse(insert.ExecuteScalar().ToString());
return(biWeekly);
}
public static string GetDatabasePath(SqlConnection pSqlConnection)
{
const string sqlText =
@"
SELECT physical_name
FROM [master].sys.master_files
WHERE database_id = 1 AND FILE_ID = 1
";
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
string dbFilePath = (string)select.ExecuteScalar();
return(Path.GetDirectoryName(dbFilePath));
}
}
public double GetMostRecentlyRate(DateTime pDate, Currency pCurrency)
{
const string q = @"SELECT TOP 1 exchange_rate
FROM ExchangeRates
WHERE exchange_date <= @date AND currency_id = @currency
ORDER BY exchange_date DESC";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency",pCurrency.Id);
c.AddParam("@date", pDate);
return Convert.ToDouble(c.ExecuteScalar());
}
}
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 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 void AttachDemoDatabase(SqlConnection connection, string path)
{
var query = String.Format("CREATE DATABASE DemoDB ON " +
"(FILENAME = '{0}\\DemoDB.mdf'), " +
"(FILENAME = '{0}\\DemoDB.ldf') " +
"FOR ATTACH; " +
"CREATE DATABASE DemoDB_attachments ON " +
"(FILENAME = '{0}\\DemoDB_attachments.mdf'), " +
"(FILENAME = '{0}\\DemoDB_attachments.ldf') " +
"FOR ATTACH;", path);
using (var command = new OpenCbsCommand(query, connection))
{
command.ExecuteNonQuery();
}
}
public int GetContractQuantityByCurrencyId(int currencyId)
{
const string q = @"
SELECT COUNT(Credit.id) AS contract_quantity
FROM [dbo].[Currencies] AS Cur
INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id
INNER JOIN Credit ON Credit.package_id=Pack.id
WHERE Cur.id=@currency_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@currency_id", currencyId);
return((int)c.ExecuteScalar());
}
}
public void DeleteEventInDatabase(SavingEvent pSavingEvent)
{
const string q = @"UPDATE [SavingEvents] SET
[deleted] = 1
, is_exported = 0
,[cancel_date] = @cancel_date
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pSavingEvent.Id);
c.AddParam("@cancel_date", pSavingEvent.CancelDate.Value);
c.ExecuteNonQuery();
}
}
public static void AddDatabaseToAccounts(string pAccountName, string pDatabaseName, string pLogin, string pPassword, SqlConnection pSqlConnection)
{
const string sqlText = @"INSERT INTO [Accounts].[dbo].[SqlAccounts]
([account_name],[database_name],[user_name],[password],[active])
VALUES (@accountName, @databaseName, @login, @password,1)";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlConnection))
{
insert.AddParam("@accountName", pAccountName);
insert.AddParam("@databaseName", pDatabaseName);
insert.AddParam("@login", pLogin);
insert.AddParam("@password", pPassword);
insert.ExecuteNonQuery();
}
}
public static void AddDatabaseToAccounts(string pAccountName, string pDatabaseName, string pLogin, string pPassword, SqlConnection pSqlConnection)
{
const string sqlText = @"INSERT INTO [Accounts].[dbo].[SqlAccounts]
([account_name],[database_name],[user_name],[password],[active])
VALUES (@accountName, @databaseName, @login, @password,1)";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlConnection))
{
insert.AddParam("@accountName", pAccountName);
insert.AddParam("@databaseName", pDatabaseName);
insert.AddParam("@login", pLogin);
insert.AddParam("@password", pPassword);
insert.ExecuteNonQuery();
}
}
public int AddFundingLineEvent(FundingLineEvent pFundingLineEvent, SqlTransaction pTransac)
{
const string sqlText =
@"INSERT INTO [FundingLineEvents]
([code],
[amount],
[direction],
[fundingline_id],
[deleted],
[creation_date],
[type],
user_id,
contract_event_id)
VALUES
(@code,
@amount,
@direction,
@fundingLineId,
@deleted,
@creationDate,
@type,
@user_id,
@contract_event_id
)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = pTransac == null
? new OpenCbsCommand(sqlText, conn)
: new OpenCbsCommand(sqlText, pTransac.Connection, pTransac))
{
cmd.AddParam("@code", pFundingLineEvent.Code);
cmd.AddParam("@amount", pFundingLineEvent.Amount);
cmd.AddParam("@direction", (int)pFundingLineEvent.Movement);
cmd.AddParam("@fundingLineId", pFundingLineEvent.FundingLine.Id);
// pFundingLineId);
cmd.AddParam("@deleted", false);
cmd.AddParam("@creationDate", pFundingLineEvent.CreationDate);
cmd.AddParam("@type", (int)pFundingLineEvent.Type);
cmd.AddParam("@user_id", _user == null? (object)null:_user.Id);
cmd.AddParam("contract_event_id", pFundingLineEvent.AttachTo == null ?
(object)null: pFundingLineEvent.AttachTo.Id);
pFundingLineEvent.Id = Convert.ToInt32(cmd.ExecuteScalar());
return(pFundingLineEvent.Id);
}
}
public bool Update(QueuedSMS queuedSMS)
{
bool updateOk = false;
try
{
const string q = @"UPDATE dbo.QueuedSMS
SET [From] = @From,
[Recipient] = @Recipient,
[RecipientId] = @RecipientId,
[ContractId] = @ContractId,
[Charged] = @Charged,
[Message] = @Message,
[CreatedOnUtc] = @CreatedOnUtc,
[SentOnUtc] = @SentOnUtc,
[SentTries] = @SentTries,
[Response] = @Response,
[Deleted] = @Deleted
WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@Id", queuedSMS.Id);
c.AddParam("@From", queuedSMS.From);
c.AddParam("@Recipient", queuedSMS.Recipient);
c.AddParam("@RecipientId", queuedSMS.RecipientId);
c.AddParam("@ContractId", queuedSMS.ContractId);
c.AddParam("@Charged", queuedSMS.Charged);
c.AddParam("@Message", queuedSMS.Message);
c.AddParam("@CreatedOnUtc", queuedSMS.CreatedOnUtc);
c.AddParam("@SentOnUtc", queuedSMS.SentOnUtc);
c.AddParam("@SentTries", queuedSMS.SentTries);
c.AddParam("@Response", queuedSMS.Response);
c.AddParam("@Deleted", false);
c.ExecuteNonQuery();
updateOk = true;
}
}
catch (System.Exception ex)
{
throw ex;
}
return(updateOk);
}
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>
/// Executes a SQL script file.<br/>
/// Each peace of script delimited by "GO" is executed individually.<br/>
/// If any error occurs, the script execution stops and returns an error.<br/>
/// Exept after the /** IGNORE ERRORS **/ section where all errors are ignored.<br/>
/// Warning : Only /*xxx*/ comments on a single line are supported!<br/>
/// </summary>
/// <param name="pScriptFile">Scripy file path</param>
/// <param name="pDatabaseName"></param>
/// <param name="pSqlConnection"></param>
/// <returns>Script exec result status</returns>
public static void ExecuteScript(string pScriptFile, string pDatabaseName, SqlConnection pSqlConnection)
{
List <string> queries = new List <string> {
string.Format("USE [{0}]", pDatabaseName)
};
queries.AddRange(_ParseSqlFile(pScriptFile));
foreach (string query in queries)
{
OpenCbsCommand command = new OpenCbsCommand(query, pSqlConnection)
{
CommandTimeout = 480
};
command.ExecuteNonQuery();
}
}
public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection)
{
const string sqlText = @"SELECT database_name
FROM Accounts.dbo.SqlAccounts
WHERE account_name = @account";
if (pSqlConnection.State == ConnectionState.Closed)
{
pSqlConnection.Open();
}
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
select.AddParam("@account", pAccountName);
return((string)select.ExecuteScalar());
}
}
public void Update(Currency pCurrency, SqlTransaction t)
{
const string q = @"UPDATE [Currencies] set [name] = @name, [code] = @code,
[is_pivot] = @is_pivot, [is_swapped] = @is_swapped, use_cents = @use_cents
WHERE [id] = @currencyID";
using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
{
c.AddParam("@currencyID", pCurrency.Id);
c.AddParam("@name", pCurrency.Name);
c.AddParam("@code", pCurrency.Code);
c.AddParam("@is_pivot", pCurrency.IsPivot);
c.AddParam("@is_swapped", pCurrency.IsSwapped);
c.AddParam("@use_cents", pCurrency.UseCents);
c.ExecuteNonQuery();
}
}
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);
}
/// <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 void AddCreditInsuranceEvent(CreditInsuranceEvent pEvent, SqlTransaction pSqlTransac)
{
const string q = @"
INSERT INTO [dbo].[CreditInsuranceEvents]
([id]
,[commission]
,[principal])
VALUES
(@id
,@commission
,@principal)";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
SetCreditInsuranceEvent(pEvent, c);
c.ExecuteNonQuery();
}
}
public static string GetObjectDropScript(string db, string name, SqlConnection conn)
{
string q = @"SELECT xtype
FROM {0}..sysobjects
WHERE name = @name";
q = string.Format(q, db);
OpenCbsCommand c = new OpenCbsCommand(q, conn);
c.AddParam("@name", name);
string xtype = c.ExecuteScalar().ToString().Trim();
string xtypeObject = string.Empty;
switch (xtype)
{
case "FN":
case "IF":
case "TF":
xtypeObject = "FUNCTION";
break;
case "P":
xtypeObject = "PROCEDURE";
break;
case "V":
xtypeObject = "VIEW";
break;
default:
Debug.Fail("Cannot be here.");
break;
}
const string retval = @"IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.{0}') AND type = N'{1}'
)
DROP {2} [dbo].[{0}]";
return(string.Format(retval, name, xtype, xtypeObject));
}
public int AddAccountingRule(IAccountingRule pRule)
{
const string sqlText = @"INSERT INTO [AccountingRules] (
[debit_account_number_id],
[credit_account_number_id],
[rule_type],
[booking_direction],
[event_type],
[event_attribute_id],
[order],
[description])
VALUES (@debit_account_number_id,
@credit_account_number_id,
@rule_type,
@booking_direction,
@event_type,
@event_attribute_id,
@order,
@description)
SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, conn))
{
insert.AddParam("@debit_account_number_id", pRule.DebitAccount.Id);
insert.AddParam("@credit_account_number_id", pRule.CreditAccount.Id);
insert.AddParam("@rule_type", pRule is ContractAccountingRule ? 'C' : 'F');
insert.AddParam("@booking_direction", (int)pRule.BookingDirection);
insert.AddParam("@event_type", pRule.EventType.EventCode);
insert.AddParam("@event_attribute_id", pRule.EventAttribute.Id);
insert.AddParam("@order", pRule.Order);
insert.AddParam("@description", pRule.Description);
pRule.Id = Convert.ToInt32(insert.ExecuteScalar());
}
}
if (pRule is ContractAccountingRule)
{
AddContractAccountingRule(pRule as ContractAccountingRule);
}
return(pRule.Id);
}
public void Update(TrancheEvent trancheEvent)
{
string sqlText = @"
UPDATE [TrancheEvents] SET
[interest_rate] = @InterestRate,
[amount] = @Amount,
[maturity] = @Maturity,
[start_date] = @StartDate,
[applied_new_interest] = @applied_new_interest
WHERE id = @Id";
using (SqlConnection conn = GetConnection())
using (var cmd = new OpenCbsCommand(sqlText, conn))
{
SetTrancheEvent(cmd, trancheEvent);
cmd.ExecuteNonQuery();
}
}
public void AddParameter(DictionaryEntry entry)
{
ApplicationSettings.GetInstance(_user.Md5).AddParameter(entry.Key.ToString(), entry.Value);
string sqlText = "INSERT INTO [GeneralParameters]([key], [value])" +
" VALUES(@name,@value)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
{
c.AddParam("@name", entry.Key.ToString());
if (entry.Value != null)
c.AddParam("@value", entry.Value.ToString());
else
c.AddParam("@value", null);
c.ExecuteNonQuery();
}
}
public void AddProvisioningRate(ProvisioningRate pR, SqlTransaction sqlTransac)
{
const string sqlText = @"INSERT INTO ProvisioningRules(
id,
number_of_days_min,
number_of_days_max,
provisioning_value)
VALUES(
@number,
@numberOfDaysMin,
@numberOfDaysMax,
@provisioningPercentage)
SELECT SCOPE_IDENTITY()";
using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
{
SetProvisioningRate(insert, pR);
insert.ExecuteScalar();
}
}
public void SelectLoanScales()
{
LoanScaleTable loanscaleTable = LoanScaleTable.GetInstance(_user);
const string sqlText = @"SELECT id, ScaleMin, ScaleMax
FROM LoanScale";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader.Empty) return;
while (reader.Read())
{
loanscaleTable.AddLoanScaleRate(GetLoanScale(reader));
}
}
}
}
}
public static int UpdateAccountActive(string pAccountName, bool pActive, SqlConnection pSqlConnection)
{
const string sqlText = @"UPDATE [Accounts].[dbo].[SqlAccounts]
SET active = @active
WHERE [account_name] = @accountName";
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection))
{
update.AddParam("@active", pActive);
update.AddParam("@accountName", pAccountName);
return update.ExecuteNonQuery();
}
}
public static void ShrinkDatabase(string pDatabaseName, SqlConnection pSqlConnection)
{
string database = TechnicalSettings.DatabaseName;
string sql1 = String.Format("ALTER DATABASE {0} SET RECOVERY SIMPLE", database);
string sql2 = String.Format("ALTER DATABASE {0} SET AUTO_SHRINK ON", database);
try
{
OpenCbsCommand cmd = new OpenCbsCommand(sql1, pSqlConnection);
cmd.ExecuteNonQuery();
cmd = new OpenCbsCommand(sql2, pSqlConnection);
cmd.ExecuteNonQuery();
string sql = String.Format("DBCC SHRINKDATABASE ({0})", database);
cmd = new OpenCbsCommand(sql, pSqlConnection);
cmd.ExecuteNonQuery();
}
catch (Exception) { }
}
public static bool IsOctopusDatabase(string dbName, SqlConnection connection)
{
string query =
string.Format("USE [{0}] SELECT 1 FROM sys.tables WHERE NAME = 'TechnicalParameters'", dbName);
using (OpenCbsCommand command = new OpenCbsCommand(query, connection))
return command.ExecuteScalar() != null;
}
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 static string GetObjectDropScript(string db, string name, SqlConnection conn)
{
string q = @"SELECT xtype
FROM {0}..sysobjects
WHERE name = @name";
q = string.Format(q, db);
OpenCbsCommand c = new OpenCbsCommand(q, conn);
c.AddParam("@name", name);
string xtype = c.ExecuteScalar().ToString().Trim();
string xtypeObject = string.Empty;
switch (xtype)
{
case "FN":
case "IF":
case "TF":
xtypeObject = "FUNCTION";
break;
case "P":
xtypeObject = "PROCEDURE";
break;
case "V":
xtypeObject = "VIEW";
break;
default:
Debug.Fail("Cannot be here.");
break;
}
const string retval = @"IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.{0}') AND type = N'{1}'
)
DROP {2} [dbo].[{0}]";
return string.Format(retval, name, xtype, xtypeObject);
}
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 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 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 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 GetDatabasePath(SqlConnection pSqlConnection)
{
const string sqlText =
@"
SELECT physical_name
FROM [master].sys.master_files
WHERE database_id = 1 AND FILE_ID = 1
";
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
string dbFilePath = (string)select.ExecuteScalar();
return Path.GetDirectoryName(dbFilePath);
}
}
public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection)
{
const string sqlText = @"SELECT database_name
FROM Accounts.dbo.SqlAccounts
WHERE account_name = @account";
if (pSqlConnection.State == ConnectionState.Closed)
pSqlConnection.Open();
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
{
select.AddParam("@account", pAccountName);
return (string)select.ExecuteScalar();
}
}
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 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 static int ChangeAdminPasswordForAccount(string pAccountName, string pPassword, SqlConnection pSqlConnection)
{
const string sqlText = @"DECLARE @dbName NVARCHAR(50),
@query NVARCHAR(200)
SELECT @dbName = database_name
FROM Accounts.dbo.SqlAccounts
WHERE account_name = @account
SET @query = 'UPDATE ' + @dbName + '.dbo.Users SET user_pass = ''' + @password + ''' WHERE [user_name] = ''admin'''
EXEC (@query)";
if (pSqlConnection.State == ConnectionState.Closed)
pSqlConnection.Open();
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, pSqlConnection))
{
update.AddParam("@account", pAccountName);
update.AddParam("@password", pPassword);
return update.ExecuteNonQuery();
}
}
/// <summary>
/// Executes a SQL script file.<br/>
/// Each peace of script delimited by "GO" is executed individually.<br/>
/// If any error occurs, the script execution stops and returns an error.<br/>
/// Exept after the /** IGNORE ERRORS **/ section where all errors are ignored.<br/>
/// Warning : Only /*xxx*/ comments on a single line are supported!<br/>
/// </summary>
/// <param name="script">Scripy file path</param>
/// <param name="database"></param>
/// <param name="connection"></param>
/// <returns>Script exec result status</returns>
public static void ExecuteScript(string script, string database, SqlConnection connection = null, SqlTransaction transaction = null)
{
if (connection == null && transaction == null)
{
throw new ArgumentException("Both connection and transaction cannot be null.");
}
var queries = new List<string> { string.Format("USE [{0}]", database) };
queries.AddRange(_ParseSqlFile(script));
foreach (var query in queries)
{
var conn = connection ?? transaction.Connection;
var command = new OpenCbsCommand(query, conn, transaction) { CommandTimeout = 480 };
command.ExecuteNonQuery();
}
}
