public void DeleteAccount(Account account)
{
using (SqlConnection conn = GetConnection())
{
OpenCbsCommand cmd = new OpenCbsCommand
{
Connection = conn,
CommandText =
@"DELETE FROM dbo.ChartOfAccounts
WHERE lft >= @lft AND rgt <= @rgt"
};
cmd.AddParam("@lft", account.Left);
cmd.AddParam("@rgt", account.Right);
cmd.ExecuteNonQuery();
cmd.ResetParams();
cmd.CommandText =
@"UPDATE dbo.ChartOfAccounts
SET lft = lft - @diff, rgt = rgt - @diff
WHERE lft > @lft";
cmd.AddParam("@diff", account.Right - account.Left + 1);
cmd.AddParam("@lft", account.Left);
cmd.ExecuteNonQuery();
}
}
public void UpdateAccountingRule(IAccountingRule rule)
{
const string sqlText = @"UPDATE [AccountingRules]
SET [debit_account_number_id] = @debit_account_number_id,
[credit_account_number_id] = @credit_account_number_id,
[booking_direction] = @booking_direction,
[event_type] = @event_type,
[event_attribute_id] = @event_attribute_id,
[order] = @order,
[description] = @description
WHERE id = @id";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
{
cmd.AddParam("@id", rule.Id);
cmd.AddParam("@debit_account_number_id", rule.DebitAccount.Id);
cmd.AddParam("@credit_account_number_id", rule.CreditAccount.Id);
cmd.AddParam("@event_type", rule.EventType.EventCode);
cmd.AddParam("@event_attribute_id", rule.EventAttribute.Id);
cmd.AddParam("@booking_direction", (int)rule.BookingDirection);
cmd.AddParam("@order", rule.Order);
cmd.AddParam("@description", rule.Description);
cmd.ExecuteNonQuery();
}
}
if (rule is ContractAccountingRule)
{
UpdateContractAccountingRule(rule as ContractAccountingRule);
}
}
private void AddContractAccountingRule(ContractAccountingRule rule)
{
const string sqlText = @"INSERT INTO
[ContractAccountingRules]
([id],
[product_type],
[loan_product_id],
[savings_product_id],
[client_type],
[activity_id],
[currency_id])
VALUES
(@id,
@productType,
@loanProductId,
@savingsProductId,
@clientType,
@activityId,
@currency_id)";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
{
SetAccountingRule(cmd, rule);
cmd.ExecuteNonQuery();
}
}
}
public static void CreateDatabase(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = "CREATE DATABASE " + pDatabaseName;
OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection);
cmd.ExecuteNonQuery();
}
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());
}
}
public void DeleteEvents()
{
OpenCbsCommand deleteLIAE = new OpenCbsCommand("DELETE LoanInterestAccruingEvents", SqlConnection);
deleteLIAE.ExecuteNonQuery();
OpenCbsCommand deleteROLE = new OpenCbsCommand("DELETE ReschedulingOfALoanEvents", SqlConnection);
deleteROLE.ExecuteNonQuery();
OpenCbsCommand deleteWROE = new OpenCbsCommand("DELETE WriteOffEvents", SqlConnection);
deleteWROE.ExecuteNonQuery();
OpenCbsCommand deleteLODE = new OpenCbsCommand("DELETE LoanDisbursmentEvents", SqlConnection);
deleteLODE.ExecuteNonQuery();
OpenCbsCommand deleteRPE = new OpenCbsCommand("DELETE RepaymentEvents", SqlConnection);
deleteRPE.ExecuteNonQuery();
OpenCbsCommand delete = new OpenCbsCommand("DELETE ContractEvents", SqlConnection);
delete.ExecuteNonQuery();
OpenCbsCommand deleteSVE = new OpenCbsCommand("DELETE SavingEvents", SqlConnection);
deleteSVE.ExecuteNonQuery();
OpenCbsCommand deleteEVS = new OpenCbsCommand("DELETE Events", SqlConnection);
deleteEVS.ExecuteNonQuery();
DeleteCreditContract();
}
public void DeleteCreditContract()
{
OpenCbsCommand ContractAssignHistory = new OpenCbsCommand("DELETE ContractAssignHistory", SqlConnection);
ContractAssignHistory.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor3 = new OpenCbsCommand("DELETE LoanDisbursmentEvents", SqlConnection);
deleteLinkGuarantor3.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor2 = new OpenCbsCommand("DELETE ContractEvents", SqlConnection);
deleteLinkGuarantor2.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor = new OpenCbsCommand("DELETE LinkGuarantorCredit", SqlConnection);
deleteLinkGuarantor.ExecuteNonQuery();
OpenCbsCommand deleteCredit = new OpenCbsCommand("DELETE Credit", SqlConnection);
deleteCredit.ExecuteNonQuery();
DeletePackage();
DeleteFundingLine();
OpenCbsCommand deleteContract = new OpenCbsCommand("DELETE Contracts", SqlConnection);
deleteContract.ExecuteNonQuery();
DeleteSaving();
DeletedProject();
DeleteTiers();
DeleteDistrict();
DeleteProvince();
}
public void DeleteTiers()
{
OpenCbsCommand deleteClientBranchHistory = new OpenCbsCommand("DELETE ClientBranchHistory", _connection);
deleteClientBranchHistory.ExecuteNonQuery();
OpenCbsCommand deletePersonGroupBelonging = new OpenCbsCommand("DELETE PersonGroupBelonging", _connection);
deletePersonGroupBelonging.ExecuteNonQuery();
OpenCbsCommand deletePersons = new OpenCbsCommand("DELETE Persons", _connection);
deletePersons.ExecuteNonQuery();
OpenCbsCommand deleteGroups = new OpenCbsCommand("DELETE Groups", _connection);
deleteGroups.ExecuteNonQuery();
OpenCbsCommand deleteProject = new OpenCbsCommand("DELETE Projects", _connection);
deleteProject.ExecuteNonQuery();
OpenCbsCommand deleteSavingEvents = new OpenCbsCommand("DELETE SavingEvents", _connection);
deleteSavingEvents.ExecuteNonQuery();
OpenCbsCommand deleteSavingBookContracts = new OpenCbsCommand("DELETE FROM SavingBookContracts", _connection);
deleteSavingBookContracts.ExecuteNonQuery();
OpenCbsCommand deleteSavingDepositContracts = new OpenCbsCommand("DELETE FROM SavingDepositContracts", _connection);
deleteSavingDepositContracts.ExecuteNonQuery();
OpenCbsCommand deleteSavingContracts = new OpenCbsCommand("DELETE SavingContracts", _connection);
deleteSavingContracts.ExecuteNonQuery();
OpenCbsCommand deleteTiers = new OpenCbsCommand("DELETE Tiers", _connection);
deleteTiers.ExecuteNonQuery();
}
public void UpdateFiscalYear(SqlTransaction sqlTransaction, FiscalYear fiscalYear)
{
const string sqlText =
@"UPDATE FiscalYear
SET close_date = @close_date,
open_date = @open_date,
name = @name
WHERE id = @id";
using (OpenCbsCommand command = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction))
{
if (fiscalYear.OpenDate != null)
{
command.AddParam("@open_date", ((DateTime)fiscalYear.OpenDate).Date);
}
else
{
command.AddParam("@open_date", null);
}
if (fiscalYear.CloseDate != null)
{
command.AddParam("@close_date", ((DateTime)fiscalYear.CloseDate).Date);
}
else
{
command.AddParam("@close_date", null);
}
command.AddParam("@name", fiscalYear.Name);
command.AddParam("@id", fiscalYear.Id);
command.ExecuteNonQuery();
}
}
public void DeleteCreditContract()
{
OpenCbsCommand deleteLinkSavings = new OpenCbsCommand("DELETE FROM [LoansLinkSavingsBook]", _connection);
deleteLinkSavings.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor3 = new OpenCbsCommand("DELETE LoanDisbursmentEvents", _connection);
deleteLinkGuarantor3.ExecuteNonQuery();
OpenCbsCommand deleteInstallmentHistory = new OpenCbsCommand("DELETE InstallmentHistory", _connection);
deleteInstallmentHistory.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor2 = new OpenCbsCommand("DELETE ContractEvents", _connection);
deleteLinkGuarantor2.ExecuteNonQuery();
OpenCbsCommand deleteLinkGuarantor = new OpenCbsCommand("DELETE LinkGuarantorCredit", _connection);
deleteLinkGuarantor.ExecuteNonQuery();
OpenCbsCommand deleteCredit = new OpenCbsCommand("DELETE Credit", _connection);
deleteCredit.ExecuteNonQuery();
DeletePackage();
DeleteFundingLine();
OpenCbsCommand deleteContract = new OpenCbsCommand("DELETE Contracts", _connection);
deleteContract.ExecuteNonQuery();
DeleteTiers();
DeleteCity();
DeleteDistrict();
DeleteProvince();
}
public int AddGenericCreditContractIntoDatabase()
{
DeleteCreditContract();
int tiersId = AddGenericTiersIntoDatabase("group");
OpenCbsCommand insertProject = new OpenCbsCommand(@"INSERT INTO Projects([tiers_id], [status], [name], [code], [aim], [begin_date]) VALUES
(" + tiersId + " , 2,'NOT SET','NOT SET','NOT SET','10/10/2005') SELECT SCOPE_IDENTITY()", _connection);
int projectId = int.Parse(insertProject.ExecuteScalar().ToString());
OpenCbsCommand insertContract = new OpenCbsCommand("INSERT INTO [Contracts]([contract_code], [branch_code], [creation_date], [start_date], [close_date], [closed], [project_id], [activity_id],[preferred_first_installment_date]) VALUES " +
"('KH/130','SU','10/10/2004','10/10/2005','10/10/2006',0," + projectId + ", 1,'11/10/2005') SELECT SCOPE_IDENTITY()", _connection);
int contractId = int.Parse(insertContract.ExecuteScalar().ToString());
int packageId = AddGenericPackage();
int fundingline_id = AddGenericFundingLine();
string sqlText =
string.Format(
@"INSERT INTO [Credit]
( [id]
,[package_id]
,[amount]
,[interest_rate]
,[installment_type]
,[nb_of_installment]
,[non_repayment_penalties_based_on_initial_amount]
,[non_repayment_penalties_based_on_olb]
,[non_repayment_penalties_based_on_overdue_interest]
,[non_repayment_penalties_based_on_overdue_principal]
,[anticipated_total_repayment_penalties]
,[disbursed]
,[loanofficer_id]
,[fundingLine_id]
,[grace_period]
,[written_off]
,[rescheduled]
,[bad_loan]
,[effective_interest_rate])
VALUES(@id, @packageId, 1000, 2, 1, 2, 2 ,2 ,2 ,3 ,2 ,0 ,1 ,{0} ,6 ,0 ,0 ,0,0)
", fundingline_id);
OpenCbsCommand insertCredit = new OpenCbsCommand(sqlText, _connection);
insertCredit.AddParam("@id", contractId);
insertCredit.AddParam("@packageId", packageId);
insertCredit.ExecuteNonQuery();
OpenCbsCommand insertInstallment = new OpenCbsCommand(@"INSERT INTO [Installments]([expected_date],[interest_repayment],[capital_repayment],[contract_id],
[number],[paid_interest],[paid_capital],[fees_unpaid])
VALUES (01/01/2007,100,200,@contractId,1,0,0,0)
INSERT INTO [Installments]([expected_date],[interest_repayment],[capital_repayment],[contract_id],
[number],[paid_interest],[paid_capital],[fees_unpaid])
VALUES (01/02/2007,100,200,@contractId,2,0,0,0)", _connection);
insertInstallment.AddParam("@contractId", contractId);
insertInstallment.ExecuteNonQuery();
return(contractId);
}
private void InitScript(string sql)
{
ConnectionManager connectionManager = ConnectionManager.GetInstance();
OpenCbsCommand insert = new OpenCbsCommand(sql, connectionManager.SqlConnection);
insert.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 DeleteDistrict()
{
OpenCbsCommand delete = new OpenCbsCommand("DELETE City", SqlConnection);
delete.ExecuteNonQuery();
delete = new OpenCbsCommand("DELETE Districts", SqlConnection);
delete.ExecuteNonQuery();
}
public void Delete(SqlTransaction pSqlTransaction)
{
const string sqltext = "DELETE FROM LoanScale";
using (OpenCbsCommand delete = new OpenCbsCommand(sqltext, pSqlTransaction.Connection, pSqlTransaction))
{
delete.ExecuteNonQuery();
}
}
public void DeleteCycles()
{
OpenCbsCommand deletePersonGroupBelonging = new OpenCbsCommand("DELETE AmountCycles", SqlConnection);
deletePersonGroupBelonging.ExecuteNonQuery();
OpenCbsCommand deletePersons = new OpenCbsCommand("DELETE Cycles", SqlConnection);
deletePersons.ExecuteNonQuery();
}
public void DeleteAllUser()
{
DeleteAllBranches();
DeleteAllUserRoles();
DeleteAllRoles();
OpenCbsCommand delete = new OpenCbsCommand("DELETE Users WHERE id != 1", SqlConnection);
delete.ExecuteNonQuery();
}
private static void SwitchOnSigleMode(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format(@"ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", pDatabaseName);
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection))
{
cmd.ExecuteNonQuery();
}
}
public void DeleteExotics()
{
OpenCbsCommand deleteExoInstallments = new OpenCbsCommand("DELETE ExoticInstallments", SqlConnection);
deleteExoInstallments.ExecuteNonQuery();
OpenCbsCommand deleteExotics = new OpenCbsCommand("DELETE Exotics", SqlConnection);
deleteExotics.ExecuteNonQuery();
}
private static void SwitchOffSigleMode(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format(@"ALTER DATABASE {0} SET MULTI_USER", pDatabaseName);
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection))
{
cmd.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 DeleteInstallments()
{
OpenCbsCommand deleteHistoric = new OpenCbsCommand("DELETE InstallmentHistory", SqlConnection);
deleteHistoric.ExecuteNonQuery();
OpenCbsCommand delete = new OpenCbsCommand("DELETE Installments", SqlConnection);
delete.ExecuteNonQuery();
}
public void Delete(SqlTransaction pSqlTransaction)
{
const string sqltext = "DELETE FROM LoanScale";
using (OpenCbsCommand delete = new OpenCbsCommand(sqltext, pSqlTransaction.Connection, pSqlTransaction))
{
delete.ExecuteNonQuery();
}
}
public void DeleteFundingLine()
{
OpenCbsCommand delete22 = new OpenCbsCommand("DELETE FundingLineEvents", SqlConnection);
delete22.ExecuteNonQuery();
OpenCbsCommand delete = new OpenCbsCommand("DELETE FundingLines", SqlConnection);
delete.ExecuteNonQuery();
}
public bool Update(QueuedEmail queuedEmail)
{
bool updateOk = false;
try
{
const string q = @"UPDATE dbo.QueuedEmails
SET [Priority] = @Priority,
[From] = @From,
[FromName] = @FromName,
[To] = @To,
[ToName] = @ToName,
[ReplyTo] = @ReplyTo,
[ReplyToName] = @ReplyToName,
[CC] = @CC,
[Bcc] = @Bcc,
[Body] = @Body,
[CreatedOnUtc] = @CreatedOnUtc,
[SentOnUtc] = @SentOnUtc,
[SentTries] = @SentTries,
[EmailAccountId] = @EmailAccountId,
[Subject] = @Subject,
[Deleted] = @Deleted
WHERE Id = @Id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@Id", queuedEmail.Id);
c.AddParam("@Priority", queuedEmail.Priority);
c.AddParam("@From", queuedEmail.From);
c.AddParam("@FromName", queuedEmail.FromName);
c.AddParam("@To", queuedEmail.To);
c.AddParam("@ToName", queuedEmail.ToName);
c.AddParam("@ReplyTo", queuedEmail.ReplyTo);
c.AddParam("@ReplyToName", queuedEmail.ReplyToName);
c.AddParam("@CC", queuedEmail.CC);
c.AddParam("@Bcc", queuedEmail.Bcc);
c.AddParam("@Body", queuedEmail.Body);
c.AddParam("@CreatedOnUtc", queuedEmail.CreatedOnUtc);
c.AddParam("@SentOnUtc", queuedEmail.SentOnUtc);
c.AddParam("@EmailAccountId", queuedEmail.EmailAccountId);
c.AddParam("@SentTries", queuedEmail.SentTries);
c.AddParam("@Subject", queuedEmail.Subject);
c.AddParam("@Deleted", false);
c.ExecuteNonQuery();
updateOk = true;
}
}
catch (System.Exception ex)
{
throw ex;
}
return(updateOk);
}
public void AddGenericFundingLine(string code, bool deleted)
{
OpenCbsCommand insert = new OpenCbsCommand(@"INSERT INTO FundingLines (name,begin_date,end_date,amount,purpose,deleted)
VALUES (@code,'11/11/2006','11/11/2006',1000,'TEST',@deleted) SELECT SCOPE_IDENTITY()", SqlConnection);
insert.AddParam("@code", code);
insert.AddParam("@deleted", deleted);
insert.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 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 DeleteAllBranches()
{
OpenCbsCommand delete = new OpenCbsCommand("DELETE FROM Branches", SqlConnection);
delete.ExecuteNonQuery();
OpenCbsCommand reset = new OpenCbsCommand("DBCC CHECKIDENT (Branches, RESEED, 0)", SqlConnection);
reset.ExecuteNonQuery();
}
public void DeleteSelectedParameter(string key)
{
string sqlText = "DELETE FROM GeneralParameters WHERE upper([key]) = @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
{
c.AddParam("@name", key);
c.ExecuteNonQuery();
}
}
public void SetGuid(Guid guid)
{
string query = "INSERT INTO [TechnicalParameters] ([name], [value]) VALUES ('GUID', @value)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(query, conn))
{
c.AddParam("@value", guid.ToString());
c.ExecuteNonQuery();
}
}
public static void DeleteDatabase(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", pDatabaseName);
OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection);
cmd.ExecuteNonQuery();
sqlText = "DROP DATABASE " + pDatabaseName;
cmd = new OpenCbsCommand(sqlText, pSqlConnection);
cmd.ExecuteNonQuery();
}
public void SetBuildNumber(string buildNum)
{
string query = "UPDATE [TechnicalParameters] SET [value]=@value WHERE [name]='BuildNumber'";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(query, conn))
{
c.AddParam("@value", buildNum);
c.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 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 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 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 UpdateInitialData(int pSavingId, OCurrency initialAmount, OCurrency entryFees)
{
const string sqlText = @"UPDATE SavingContracts SET initial_amount = @initial_amount, entry_fees = @entry_fees WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
{
update.AddParam("@id", pSavingId);
update.AddParam("@initial_amount", initialAmount);
update.AddParam("@entry_fees", entryFees);
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 void DeleteDatabase(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", pDatabaseName);
OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection);
cmd.ExecuteNonQuery();
sqlText = "DROP DATABASE " + pDatabaseName;
cmd = new OpenCbsCommand(sqlText, pSqlConnection);
cmd.ExecuteNonQuery();
}
public void UpdateSavingContractCode(int savingsId, string code, SqlTransaction pSqlTransac)
{
const string sqlText = @"UPDATE SavingContracts SET code = @code WHERE id = @id";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
cmd.AddParam("@code", code);
cmd.AddParam("@id", savingsId);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Inserts entry fees for the specified loan into database
/// </summary>
/// <param name="loanEntryFees">List of loan entry fees</param>
/// <param name="loanId">Loan (credit) id</param>
/// <param name="transaction">Transaction for action</param>
public void InsertLoanEntryFees(List<LoanEntryFee> loanEntryFees, int loanId, SqlTransaction transaction)
{
const string q = @"INSERT INTO [dbo].[CreditEntryFees]
(credit_id, entry_fee_id, fee_value)
VALUES (@credit_id, @entry_fee_id, @fee_value)";
foreach (LoanEntryFee entryFee in loanEntryFees)
{
using (var c = new OpenCbsCommand(q, transaction.Connection, transaction))
{
c.AddParam("@credit_id", loanId);
c.AddParam("@entry_fee_id", entryFee.ProductEntryFee.Id);
c.AddParam("@fee_value", entryFee.FeeValue);
c.ExecuteNonQuery();
}
}
}
public void DeleteLoanShareAmountWhereNotDisbursed(int groupId)
{
const string q = @"
DELETE FROM [dbo].[LoanShareAmounts]
WHERE group_id=@group_id
AND
( contract_id NOT IN
(SELECT id
FROM [dbo].[Credit]
WHERE disbursed=1)
)";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@group_id", groupId);
c.ExecuteNonQuery();
}
}
public void AddCollateralPropertyValue(ContractCollateral contractCollateral, CollateralPropertyValue propertyValue, SqlTransaction pSqlTransac)
{
string q = @"INSERT INTO [CollateralPropertyValues] ([contract_collateral_id], [property_id], [value])
VALUES (@contract_collateral_id, @property_id, @value)";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@contract_collateral_id", contractCollateral.Id);
c.AddParam("@property_id", propertyValue.Property.Id);
c.AddParam("@value", propertyValue.Value);
c.ExecuteNonQuery();
}
}
private void _SetLoanShareAmount(Loan pLoan, SqlTransaction pSqlTransac)
{
// Get group id
int group_id;
const string q = @"SELECT p.tiers_id
FROM dbo.Projects AS p
LEFT JOIN dbo.Contracts AS c ON c.project_id = p.id
WHERE c.id = @contract_id";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@contract_id", pLoan.Id);
group_id = Convert.ToInt32(c.ExecuteScalar());
}
const string sqlText = @"INSERT INTO LoanShareAmounts (person_id, group_id, contract_id, amount)
VALUES (@person_id, @group_id, @contract_id, @amount)";
using(OpenCbsCommand c = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
foreach (LoanShare ls in pLoan.LoanShares)
{
c.ResetParams();
c.AddParam("@person_id", ls.PersonId);
c.AddParam("@group_id", group_id);
c.AddParam("@contract_id", pLoan.Id);
c.AddParam("@amount", ls.Amount.Value);
c.ExecuteNonQuery();
}
}
}
public void UpdateNextMaturityForSavingBook(int savingId, DateTime? nextMaturity)
{
const string sqlText =
@"UPDATE SavingBookContracts
SET next_maturity = @nextMaturity
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
{
cmd.AddParam("@nextMaturity", nextMaturity);
cmd.AddParam("@id", savingId);
cmd.ExecuteNonQuery();
}
}
public void UpdateOverdraftStatus(int savingId, bool inOverdraft)
{
const string sqlText = @"UPDATE SavingBookContracts SET in_overdraft = @inOverdraft WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
{
update.AddParam("@id", savingId);
update.AddParam("@inOverdraft", inOverdraft);
update.ExecuteNonQuery();
}
}
public void RemoveCompulsorySavings(int loanId, SqlTransaction pSqlTransac)
{
const string q = @"UPDATE [LoansLinkSavingsBook]
SET savings_id = NULL
WHERE loan_id = @loanId";
using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
{
c.AddParam("@loanId", loanId);
c.ExecuteNonQuery();
}
}
public void UpdateStatus(int savingId, OSavingsStatus status, DateTime? closedDate)
{
const string sqlText = @"UPDATE SavingContracts SET status = @status, closed_date = @closedDate WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
{
update.AddParam("@status", (int)status);
update.AddParam("@closedDate", closedDate);
update.AddParam("@id", savingId);
update.ExecuteNonQuery();
}
}
private static void BackupToFileImpl(string pTempSavingPath, string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlRecoveryMode1 = String.Format("ALTER DATABASE {0} SET RECOVERY SIMPLE", pDatabaseName);
string sqlAutoShrink2 = String.Format("ALTER DATABASE {0} SET AUTO_SHRINK ON", pDatabaseName);
string sqlBackupFile3 = String.Format(@"
DECLARE
@DataBaseName varchar(255),
@BackupFile varchar(255)
SET @DataBaseName = '{0}'
SET @BackupFile = N'{1}'
BACKUP DATABASE @DataBaseName TO DISK= @BackupFile
WITH FORMAT", pDatabaseName, pTempSavingPath);
string sqlTruncLogFile4 = String.Format(@"
DECLARE
@DestDataBaseName varchar(255)
SET @DestDataBaseName = '{0}'
BACKUP LOG @DestDataBaseName WITH TRUNCATE_ONLY", pDatabaseName);
string sqlTruncLogFile5 = String.Format(@"
DECLARE
@DestDataBaseName varchar(255),
@LogName varchar(255)
SET @DestDataBaseName = '{0}'
SET @LogName = (SELECT name from sysfiles where groupid = 0)
DBCC SHRINKFILE(@Logname)", pDatabaseName);
const string sqlSqlServerVersion
= @"SELECT CONVERT(INTEGER, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))";
// Ensure recovery mode is FULL
var cmd = new OpenCbsCommand(sqlRecoveryMode1, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
//Ensure auto shrink is on
cmd = new OpenCbsCommand(sqlAutoShrink2, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
// Backup data int file
cmd = new OpenCbsCommand(sqlBackupFile3, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
// Trunc transaction log
cmd = new OpenCbsCommand(sqlSqlServerVersion, pSqlConnection);
cmd.CommandTimeout = 300;
var sqlVersion = (int)cmd.ExecuteScalar();
if (sqlVersion < 10) // If SQL Server is 2000 or 2005
{
cmd = new OpenCbsCommand(sqlTruncLogFile4, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
}
else // If SQL Server is 2008 or higher
{
cmd = new OpenCbsCommand(sqlTruncLogFile5, pSqlConnection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
}
}
public static void CreateDatabase(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = "CREATE DATABASE " + pDatabaseName;
OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection);
cmd.ExecuteNonQuery();
}
private static void SwitchOffSigleMode(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format(@"ALTER DATABASE {0} SET MULTI_USER", pDatabaseName);
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection))
{
cmd.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();
}
}
private static void SwitchOnSigleMode(string pDatabaseName, SqlConnection pSqlConnection)
{
string sqlText = string.Format(@"ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", pDatabaseName);
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection))
{
cmd.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 static void RawRestore(string filePath, string dbName, string dataDir, SqlConnection conn)
{
SwitchOnSigleMode(dbName, conn);
string query =
@"DECLARE
@DestDataBaseName varchar(255),
@BackupFile varchar(255)
SET @DestDataBaseName = '{0}'
SET @BackupFile = '{1}'
RESTORE DATABASE @DestDataBaseName FROM DISK = @BackupFile
WITH REPLACE";
query = string.Format(query, dbName, filePath);
Dictionary<string, char> logicalFiles = GetFilelist(filePath, conn);
foreach (KeyValuePair<string, char> logicalFile in logicalFiles)
{
const string moveString = ", MOVE '{0}' TO '{1}'";
switch (logicalFile.Value)
{
case 'D':
query += string.Format(
moveString
, logicalFile.Key
, Path.Combine(dataDir, dbName + ".mdf")
);
break;
case 'L':
query += string.Format(
moveString
, logicalFile.Key
, Path.Combine(dataDir, dbName + ".ldf")
);
break;
}
}
using (OpenCbsCommand command = new OpenCbsCommand(query, conn))
{
command.CommandTimeout = 300;
command.ExecuteNonQuery();
}
SwitchOffSigleMode(dbName, conn);
}
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();
}
}
public void AddSavingsBookContract(SavingBookContract pSaving, SqlTransaction pSqlTransac)
{
const string sqlText = @"INSERT INTO [SavingBookContracts]
(
[id],
[flat_withdraw_fees],
[rate_withdraw_fees],
[flat_transfer_fees],
[rate_transfer_fees],
[flat_deposit_fees],
[flat_close_fees],
[flat_management_fees],
[flat_overdraft_fees],
[in_overdraft],
[rate_agio_fees],
[cheque_deposit_fees],
[flat_reopen_fees],
[flat_ibt_fee],
[rate_ibt_fee],
[use_term_deposit],
[term_deposit_period],
[term_deposit_period_min],
[term_deposit_period_max],
[transfer_account],
[rollover],
[next_maturity]
)
VALUES
(
@id,
@flatWithdrawFees,
@rateWithdrawFees,
@flatTransferFees,
@rateTransferFees,
@flatDepositFees,
@flatCloseFees,
@flatManagementFees,
@flatOverdraftFees,
@inOverdraft,
@rateAgioFees,
@chequeDepositFees,
@flatReopenFees,
@flat_ibt_fee,
@rate_ibt_fee,
@use_term_deposit,
@term_deposit_period,
@term_deposit_period_min,
@term_deposit_period_max,
@transfer_account,
@rollover,
@next_maturity
)";
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
{
cmd.AddParam("@id", pSaving.Id);
cmd.AddParam("@flatWithdrawFees", pSaving.Product.WithdrawFeesType == OSavingsFeesType.Flat ? pSaving.FlatWithdrawFees : null);
cmd.AddParam("@rateWithdrawFees", pSaving.Product.WithdrawFeesType == OSavingsFeesType.Rate ? pSaving.RateWithdrawFees : null);
cmd.AddParam("@flatTransferFees", pSaving.Product.TransferFeesType == OSavingsFeesType.Flat ? pSaving.FlatTransferFees : null);
cmd.AddParam("@rateTransferFees", pSaving.Product.TransferFeesType == OSavingsFeesType.Rate ? pSaving.RateTransferFees : null);
OCurrency flat = null;
double? rate = null;
if (pSaving.Product.InterBranchTransferFee.IsFlat)
{
flat = pSaving.FlatInterBranchTransferFee;
}
else
{
rate = pSaving.RateInterBranchTransferFee;
}
cmd.AddParam("@flat_ibt_fee", flat);
cmd.AddParam("@rate_ibt_fee", rate);
cmd.AddParam("@flatDepositFees", pSaving.DepositFees);
cmd.AddParam("@flatCloseFees", pSaving.CloseFees);
cmd.AddParam("@flatManagementFees", pSaving.ManagementFees);
cmd.AddParam("@flatOverdraftFees", pSaving.OverdraftFees);
cmd.AddParam("@inOverdraft", pSaving.InOverdraft);
cmd.AddParam("@rateAgioFees", pSaving.AgioFees);
cmd.AddParam("@chequeDepositFees", pSaving.ChequeDepositFees);
cmd.AddParam("@flatReopenFees", pSaving.ReopenFees);
cmd.AddParam("@use_term_deposit", pSaving.UseTermDeposit);
cmd.AddParam("@term_deposit_period", pSaving.NumberOfPeriods);
// if (pSaving.UseTermDeposit)
// {
cmd.AddParam("@term_deposit_period_min", pSaving.TermDepositPeriodMin);
cmd.AddParam("@term_deposit_period_max", pSaving.TermDepositPeriodMax);
cmd.AddParam("@transfer_account",
pSaving.TransferAccount != null ? pSaving.TransferAccount.Code : null);
cmd.AddParam("@rollover", (int)pSaving.Rollover);
// }
// else
// {
// cmd.AddParam("@term_deposit_period_min", null);
// cmd.AddParam("@term_deposit_period_max", null);
// cmd.AddParam("@transfer_account", null);
// cmd.AddParam("@rollover", null);
// }
cmd.AddParam("@next_maturity", pSaving.NextMaturity);
cmd.ExecuteNonQuery();
}
}
