/// <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 Province AddProvinceIntoDatabase() { OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Provinces (name,deleted) VALUES ('Sugh',0) SELECT SCOPE_IDENTITY()", SqlConnection); Province province = new Province(); province.Name = "Sugh"; province.Id = int.Parse(command.ExecuteScalar().ToString()); return(province); }
public void AddGenericEvent(int userId) { int contractId = AddGenericCreditContractIntoDatabase(true); int id = 1; OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [ContractEvents]([id], [event_type], [contract_id], " + "[event_date],[is_deleted], [user_id]) VALUES(" + id + ",'PDLE', " + contractId + ", '10/10/2006',1, " + userId + ") SELECT SCOPE_IDENTITY()", SqlConnection); insert.ExecuteScalar(); }
public int AddGenericEvent() { int contractId = AddGenericCreditContractIntoDatabase(true); int userId = AddUserWithIntermediaryAttributs(); OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [ContractEvents]([event_type], [contract_id], " + "[event_date],[is_deleted], [user_id]) VALUES('PDLE', " + contractId + ", '10/10/2006',0, " + userId + ")", SqlConnection); return(int.Parse(insert.ExecuteScalar().ToString())); }
public int InsertAccountCategory(AccountCategory accountCategory, SqlTransaction pSqlTransac) { const string sqlText = @"INSERT INTO AccountsCategory (name) VALUES (@name) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac)) { insert.AddParam("@name", accountCategory.Name); return(int.Parse(insert.ExecuteScalar().ToString())); } }
public bool NumbersExist(string[] accountNumbers) { string query = string.Format( "IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE account_number in ({0})) SELECT 1 ELSE SELECT 0", string.Join(",", accountNumbers) ); using (SqlConnection connection = GetConnection()) using (OpenCbsCommand command = new OpenCbsCommand(query, connection)) return(Convert.ToInt32(command.ExecuteScalar()) == 1); }
public Branch AddBranchIntoDatabase() { Branch branch = new Branch(); branch.Name = "Default"; OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Branches (name) VALUES ('@name') SELECT SCOPE_IDENTITY()", SqlConnection); command.AddParam("@name", branch.Name); command.ExecuteScalar().ToString(); return(branch); }
public int AddGenericFundingLine2() { OpenCbsCommand insert = new OpenCbsCommand(@"INSERT INTO FundingLines (name,begin_date,end_date,amount,purpose,deleted, currency_id) VALUES ('AFD1302', @startDate, @endDate,1000,'TEST',0, 1) SELECT SCOPE_IDENTITY()", SqlConnection); insert.AddParam("@startDate", DateTime.Now); insert.AddParam("@endDate", DateTime.Now); int id = Convert.ToInt32(insert.ExecuteScalar()); return(id); }
public bool IdsExist(int[] ids) { string[] idStrings = ids.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray(); string query = string.Format( "IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE id in ({0})) SELECT 1 ELSE SELECT 0", string.Join(",", idStrings) ); using (SqlConnection connection = GetConnection()) using (OpenCbsCommand command = new OpenCbsCommand(query, connection)) return(Convert.ToInt32(command.ExecuteScalar()) == 1); }
public District AddDistrictIntoDatabase() { Province province = AddProvinceIntoDatabase(); District district = new District(); district.Province = province; district.Name = "District"; OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Districts (name,province_id,deleted) VALUES ('" + district.Name + "',@provinceId,0) SELECT SCOPE_IDENTITY()", SqlConnection); command.AddParam("@provinceId", province.Id); district.Id = int.Parse(command.ExecuteScalar().ToString()); return(district); }
public static bool DatabaseExists(string name, SqlConnection conn) { string q = @"SELECT CASE WHEN DB_ID('{0}') IS NULL THEN 0 ELSE 1 END"; q = string.Format(q, name); OpenCbsCommand cmd = new OpenCbsCommand { CommandText = q, Connection = conn }; return(Convert.ToBoolean(cmd.ExecuteScalar())); }
public void DeleteFundingLineEvent(FundingLineEvent pFundingLineEvent, SqlTransaction sqlTransac) { const string sqlText = @" UPDATE [FundingLineEvents] SET [deleted]=1 WHERE [id] = @id"; using (OpenCbsCommand c = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { c.AddParam("@id", pFundingLineEvent.Id); c.AddParam("@deleted", true); c.ExecuteScalar(); } }
public int AddGenericCreditContractIntoDatabase(bool disburse) { DeleteCreditContract(); int tiersId = AddGenericTiersIntoDatabase(OClientTypes.Group); int projectId = AddGenericProjectIntoDatabase(tiersId); OpenCbsCommand insertContract = new OpenCbsCommand("INSERT INTO [Contracts]([contract_code], [branch_code], [creation_date], [start_date], [close_date], [closed], [project_id]) VALUES " + "('KH/130','SU','10/10/2004','10/10/2005','10/10/2006',0," + projectId + ") SELECT SCOPE_IDENTITY()", SqlConnection); int contractId = int.Parse(insertContract.ExecuteScalar().ToString()); return(contractId); }
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 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 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 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 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 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 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 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 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 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 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 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 AddGenericFundingLine4() { OpenCbsCommand delete = new OpenCbsCommand(@"DELETE FROM FundingLineEvents where fundingline_id = (SELECT id FROM FundingLines where name = 'AFD_TEST')", SqlConnection); delete.ExecuteNonQuery(); OpenCbsCommand delete2 = new OpenCbsCommand(@"DELETE FROM FundingLines WHERE name = 'AFD_TEST'", SqlConnection); delete2.ExecuteNonQuery(); OpenCbsCommand insert = new OpenCbsCommand(@"INSERT INTO FundingLines (name,begin_date,end_date,amount,purpose,deleted, currency_id) VALUES ('AFD_TEST',@startDate, @endDate,1000,'TEST',0,1) SELECT SCOPE_IDENTITY()", SqlConnection); insert.AddParam("@startDate", DateTime.Now); insert.AddParam("@endDate", DateTime.Now); return(Convert.ToInt32(insert.ExecuteScalar())); }
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 int Add(EmailAccount emailAccount) { const string q = @"INSERT INTO dbo.EmailAccounts (Email, DisplayName, Host, Port, Username, Password, EnableSsl, UseDefaultCredentials, IsDefaultEmailAccount, Deleted) VALUES (@email, @displayName, @host, @port, @userName, @password, @enableSSL, @useDefaultCred, @isDefault, @deleted) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@email", emailAccount.Email); c.AddParam("@displayName", emailAccount.DisplayName); c.AddParam("@host", emailAccount.Host); c.AddParam("@port", emailAccount.Port); c.AddParam("@userName", emailAccount.Username); c.AddParam("@password", emailAccount.Password); c.AddParam("@enableSSL", emailAccount.EnableSsl); c.AddParam("@useDefaultCred", emailAccount.UseDefaultCredentials); c.AddParam("@isDefault", emailAccount.IsDefaultEmailAccount); c.AddParam("@deleted", false); return(int.Parse(c.ExecuteScalar().ToString())); } }
public int Add(QueuedSMS queuedSMS) { const string q = @"INSERT INTO dbo.QueuedSMS ([From], [Recipient], [RecipientId], [ContractId], [Charged], [Message], CreatedOnUtc, SentOnUtc, SentTries, Deleted) VALUES (@From, @Recipient, @RecipientId, @ContractId, @Charged, @Message, @CreatedOnUtc, @SentOnUtc, @SentTries, @Deleted) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { 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("@Deleted", false); return(int.Parse(c.ExecuteScalar().ToString())); } }
public int Add(MessageTemplate messageTemplate) { const string q = @"INSERT INTO dbo.MessageTemplates (Name, BccEmailAddresses, Subject, Body, EmailBody, IsActive, SendEmail, SendSMS, EmailAccountId, IsDefault, Deleted) VALUES (@Name, @BccEmailAddresses, @Subject, @Body, @EmailBody, @IsActive, @SendEmail, @SendSMS, @EmailAccountId, @IsDefault, @Deleted) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@Name", messageTemplate.Name); c.AddParam("@BccEmailAddresses", messageTemplate.BccEmailAddresses); c.AddParam("@Subject", messageTemplate.Subject); c.AddParam("@Body", messageTemplate.Body); c.AddParam("@EmailBody", messageTemplate.EmailBody); c.AddParam("@SendEmail", messageTemplate.SendEmail); c.AddParam("@SendSMS", messageTemplate.SendSMS); c.AddParam("@IsActive", messageTemplate.IsActive); c.AddParam("@EmailAccountId", messageTemplate.EmailAccountId); c.AddParam("@IsDefault", messageTemplate.IsDefault); c.AddParam("@Deleted", false); return(int.Parse(c.ExecuteScalar().ToString())); } }
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(); } } }
private int _AddContract(Loan pContract, int pProjectId, SqlTransaction pSqlTransac) { const string q = @"INSERT INTO [Contracts]([contract_code], [branch_code], [closed], [creation_date], [start_date], [align_disbursed_date], [close_date], [project_id], [status], [credit_commitee_date], [credit_commitee_comment], [credit_commitee_code], [loan_purpose], [comments], [nsg_id], [activity_id], preferred_first_installment_date) VALUES(@code, @branchCode, @closed, @creationDate, @startDate, @align_disbursed_date, @closeDate, @projectId, @status, @creditCommiteeDate, @creditCommiteeComment, @creditCommiteeCode, @loanPurpose, @comments, @NsgID, @activityId, @preferredFirstInstallmentDate) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { c.AddParam("@code", "fake_code"); c.AddParam("@status", (int) pContract.ContractStatus); c.AddParam("@creditCommiteeDate", pContract.CreditCommiteeDate); c.AddParam("@creditCommiteeComment", pContract.CreditCommiteeComment); c.AddParam("@creditCommiteeCode", pContract.CreditCommitteeCode); c.AddParam("@branchCode", pContract.BranchCode); c.AddParam("@closed", pContract.Closed); c.AddParam("@creationDate", pContract.CreationDate); c.AddParam("@startDate", pContract.StartDate); c.AddParam("@align_disbursed_date", pContract.AlignDisbursementDate); c.AddParam("@closeDate", pContract.CloseDate); c.AddParam("@projectId", pProjectId); c.AddParam("@loanPurpose", pContract.LoanPurpose); c.AddParam("@comments", pContract.Comments); c.AddParam("@NsgID", pContract.NsgID); c.AddParam("activityId", pContract.EconomicActivityId); c.AddParam("@preferredFirstInstallmentDate",pContract.FirstInstallmentDate); pContract.Id = Convert.ToInt32(c.ExecuteScalar()); } if (string.IsNullOrEmpty(pContract.Code)) pContract.Code = "-"; if (pContract.Guarantors.Count != 0) foreach (Guarantor guarantor in pContract.Guarantors) _AddGuarantor(guarantor, pContract.Id, pSqlTransac); if (pContract.Collaterals.Count != 0) foreach (ContractCollateral contractCollateral in pContract.Collaterals) AddCollateral(contractCollateral, pContract.Id, pSqlTransac); // Compulsory savings handling //if (pContract.Product.UseCompulsorySavings) if (pContract.CompulsorySavings != null) { const string sqlText = @"INSERT INTO LoansLinkSavingsBook ([loan_id], [savings_id], [loan_percentage]) VALUES (@loanId, @savingsId, @loanPercentage)"; using (OpenCbsCommand c = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac)) { if (pContract.CompulsorySavings != null) c.AddParam("@savingsId", pContract.CompulsorySavings.Id); else c.AddParam("@savingsId", null); c.AddParam("@loanId", pContract.Id); c.AddParam("@loanPercentage", pContract.CompulsorySavingsPercentage); c.ExecuteNonQuery(); } } return pContract.Id; }
public void AddCollateral(ContractCollateral contractCollateral, int contractId, SqlTransaction pSqlTransac) { string q = @"INSERT INTO [CollateralsLinkContracts] ([contract_id]) VALUES (@contract_id) SELECT CONVERT(int, SCOPE_IDENTITY())"; using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { c.AddParam("@contract_id", contractId); contractCollateral.Id = Convert.ToInt32(c.ExecuteScalar()); } foreach (CollateralPropertyValue propertyValue in contractCollateral.PropertyValues) { AddCollateralPropertyValue(contractCollateral, propertyValue, pSqlTransac); } }
public int AddGenericFundingLine() { OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO FundingLines (name,amount, purpose, deleted, currency_id, begin_date, end_date) VALUES ('AFD130',0, 'Not Set', 0, " + AddGenericCurrency() + ", '20080101', '20120101') SELECT SCOPE_IDENTITY()", _connection); return(int.Parse(insert.ExecuteScalar().ToString())); }
public int CreateClosure(int countOfTransactions) { const string sqlText = @"INSERT INTO dbo.AccountingClosure ( user_id , date_of_closure , count_of_transactions ) VALUES (@user_id, @date, @count ) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand command = new OpenCbsCommand(sqlText, conn)) { command.AddParam("@user_id", User.CurrentUser.Id); command.AddParam("@date", TimeProvider.Now); command.AddParam("@count", countOfTransactions); return int.Parse(command.ExecuteScalar().ToString()); } } }
public bool IsLoanDisbursed(int contractId) { const string query = "SELECT [disbursed] FROM Credit WHERE id = @contractId"; using (var connection = GetConnection()) using (var cmd = new OpenCbsCommand(query, connection)) { cmd.AddParam("contractId", contractId); var result = cmd.ExecuteScalar(); return result != null && Convert.ToBoolean(result); } }
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 int Add(SavingEvent pSavingEvent, int pSavingContractId, SqlTransaction sqlTransac) { const string q = @"INSERT INTO [SavingEvents]( [user_id], [contract_id], [code], [amount], [description], [deleted], [creation_date], [cancelable], [is_fired], [related_contract_code], [fees], [savings_method], [pending], [pending_event_id], [teller_id], [loan_event_id]) VALUES( @user_id, @contract_id, @code, @amount, @description, @deleted, @creation_date, @cancelable, @is_fired, @related_contract_code, @fees, @savings_method, @pending, @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 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 int InsertAccountCategory(AccountCategory accountCategory, SqlTransaction pSqlTransac) { const string sqlText = @"INSERT INTO AccountsCategory (name) VALUES (@name) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac)) { insert.AddParam("@name", accountCategory.Name); return int.Parse(insert.ExecuteScalar().ToString()); } }
public bool NumbersExist(string[] accountNumbers) { string query = string.Format( "IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE account_number in ({0})) SELECT 1 ELSE SELECT 0", string.Join(",", accountNumbers) ); using (SqlConnection connection = GetConnection()) using (OpenCbsCommand command = new OpenCbsCommand(query, connection)) return Convert.ToInt32(command.ExecuteScalar()) == 1; }
public void Insert(Account account, SqlTransaction sqlTransac, bool setIdentity) { int rightMostSibling; string sqlText; if (null == account.ParentAccountId || 0 == account.ParentAccountId) { sqlText = @"SELECT CASE WHEN MAX(rgt) IS NULL THEN 1 ELSE MAX(rgt) + 1 END FROM dbo.ChartOfAccounts"; using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { rightMostSibling = Convert.ToInt32(cmd.ExecuteScalar()); } } else { sqlText = @"SELECT rgt FROM dbo.ChartOfAccounts WHERE id = @parent_account_id"; using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { cmd.AddParam("@parent_account_id", account.ParentAccountId); rightMostSibling = Convert.ToInt32(cmd.ExecuteScalar()); } } sqlText = @"UPDATE dbo.ChartOfAccounts SET lft = CASE WHEN lft > @right_most_sibling THEN lft + 2 ELSE lft END , rgt = CASE WHEN rgt >= @right_most_sibling THEN rgt + 2 ELSE rgt END WHERE rgt >= @right_most_sibling"; using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { cmd.AddParam("@right_most_sibling", rightMostSibling); cmd.ExecuteNonQuery(); account.Left = rightMostSibling; account.Right = account.Left + 1; } if (setIdentity) { sqlText = @" SET IDENTITY_INSERT [ChartOFAccounts] ON INSERT INTO ChartOFAccounts ( id, account_number, label, debit_plus, type_code, account_category_id, type, parent_account_id, lft, rgt) VALUES ( @id, @number, @label, @debitPlus, @typeCode, @account_category_id, @type, @parentAccountId, @lft, @rgt) SET IDENTITY_INSERT [ChartOFAccounts] OFF "; using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { cmd.AddParam("@id", account.Id); SetAccount(cmd, account); cmd.ExecuteNonQuery(); } } else { sqlText = @"INSERT INTO ChartOFAccounts ( account_number, label, debit_plus, type_code, account_category_id, type, parent_account_id, lft, rgt) VALUES ( @number, @label, @debitPlus, @typeCode, @account_category_id, @type, @parentAccountId, @lft, @rgt)"; using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { SetAccount(cmd, account); cmd.ExecuteNonQuery(); } } }
public bool IdsExist(int[] ids) { string[] idStrings = ids.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray(); string query = string.Format( "IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE id in ({0})) SELECT 1 ELSE SELECT 0", string.Join(",", idStrings) ); using (SqlConnection connection = GetConnection()) using (OpenCbsCommand command = new OpenCbsCommand(query, connection)) return Convert.ToInt32(command.ExecuteScalar()) == 1; }
public void AddGeneralParameterIntoDatabase() { OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [GeneralParameters]([key], [value]) VALUES('ID_PATTERN', '/d/d/w/w') SELECT SCOPE_IDENTITY()", _connection); insert.ExecuteScalar(); }
public decimal GetGlobalOLBForProvisionning() { const string q = @"SELECT ISNULL(SUM(Installments.capital_repayment - Installments.paid_capital),0) FROM Credit INNER JOIN Installments ON Credit.id = Installments.contract_id WHERE (Credit.disbursed = 1) AND (Credit.written_off = 0) AND (Credit.bad_loan = 0)"; using (SqlConnection conn = GetConnection()) using(OpenCbsCommand c = new OpenCbsCommand(q, conn)) { return Convert.ToDecimal(c.ExecuteScalar()); } }
public int GetNumberOfSavings(int clientId) { const string sqlText = @"SELECT COUNT(id) FROM SavingContracts WHERE SavingContracts.tiers_id = @tiers_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@tiers_id", clientId); return (int)select.ExecuteScalar(); } }
public int GetNbOfLoansForClosure() { const string q = @"SELECT COUNT(Credit.id) FROM Credit WHERE Credit.disbursed = 1 AND Credit.written_off = 0 AND (NOT ((SELECT SUM(interest_repayment) + SUM(capital_repayment) - SUM(paid_interest) - SUM(paid_capital) FROM Installments WHERE contract_id = Credit.id) < 0.02))"; using (SqlConnection conn = GetConnection()) using(OpenCbsCommand c = new OpenCbsCommand(q, conn)) { return Convert.ToInt32(c.ExecuteScalar()); } }
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 int SelectLoanID(string pLoanContractCode) { const string q = @"SELECT ID FROM Contracts WHERE contract_code = @contractCode"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@contractCode", pLoanContractCode); object val = c.ExecuteScalar(); return null == val ? 0 : Convert.ToInt32(val); } }
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 Add(ISavingsContract savings, Client client, SqlTransaction sqlTransac) { const string sqlText = @"INSERT INTO [SavingContracts] ( [product_id], [user_id], [code], [status], [tiers_id], [creation_date], [interest_rate], [closed_date], [savings_officer_id], [initial_amount], [entry_fees], [nsg_id] ) VALUES ( @product_id, @user_id, @code, @status, @tiers_id, @creation_date, @interest_rate, @closedDate, @savings_officer_id, @initial_amount, @entry_fees, @nsg_id ) SELECT CONVERT(int, SCOPE_IDENTITY())"; using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac)) { cmd.AddParam("@product_id", savings.Product.Id); cmd.AddParam("@user_id", savings.User.Id); cmd.AddParam("@code", savings.Code); cmd.AddParam("@status", (int)savings.Status); cmd.AddParam("@tiers_id", client.Id); cmd.AddParam("@creation_date", savings.CreationDate); cmd.AddParam("@interest_rate", savings.InterestRate); cmd.AddParam("@closedDate", savings.ClosedDate); cmd.AddParam("@savings_officer_id", savings.SavingsOfficer.Id); cmd.AddParam("@initial_amount", savings.InitialAmount); cmd.AddParam("@entry_fees", savings.EntryFees); cmd.AddParam("@nsg_id", savings.NsgID); savings.Id = int.Parse(cmd.ExecuteScalar().ToString()); } AddSavingsBookContract((SavingBookContract)savings, sqlTransac); return savings.Id; }
public int AddGenericCurrency() { OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO Currencies (name, code, is_pivot, is_swapped) VALUES ('USD', 'USD', 1, 0) SELECT SCOPE_IDENTITY()", _connection); return(int.Parse(insert.ExecuteScalar().ToString())); }
public static bool DatabaseExists(string name, SqlConnection conn) { string q = @"SELECT CASE WHEN DB_ID('{0}') IS NULL THEN 0 ELSE 1 END"; q = string.Format(q, name); OpenCbsCommand cmd = new OpenCbsCommand {CommandText = q, Connection = conn}; return Convert.ToBoolean(cmd.ExecuteScalar()); }
public void UpdateLoanShare(LoanShare pLoanShare, int pLoanId, int pGroupId, SqlTransaction pSqlTransac) { // Check if share exists const string q = @"SELECT COUNT(*) FROM dbo.LoanShareAmounts WHERE person_id = @person_id AND group_id = @group_id AND contract_id = @contract_id"; bool exists; using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { c.AddParam("@person_id", pLoanShare.PersonId); c.AddParam("@group_id", pGroupId); c.AddParam("@contract_id", pLoanId); c.AddParam("@amount", pLoanShare.Amount); exists = Convert.ToInt32(c.ExecuteScalar()) > 0; } string query; if (exists) { query = @"UPDATE LoanShareAmounts SET amount = @amount WHERE person_id = @person_id AND group_id = @group_id AND contract_id = @contract_id"; } else { query = @"INSERT INTO LoanShareAmounts (person_id, group_id, contract_id, amount) VALUES (@person_id, @group_id, @contract_id, @amount)"; } using(OpenCbsCommand c = new OpenCbsCommand(query, pSqlTransac.Connection, pSqlTransac)) { c.AddParam("@person_id", pLoanShare.PersonId); c.AddParam("@group_id", pGroupId); c.AddParam("@contract_id", pLoanId); c.AddParam("@amount", pLoanShare.Amount); c.ExecuteNonQuery(); } }
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 void UpdateMeetingAttendees(VillageAttendee attendee) { if (attendee.Id == 0) { const string q = @"INSERT INTO dbo.VillagesAttendance (village_id, person_id, [date], attended, comment, loan_id) VALUES (@village_id, @person_id, @attended_date, @attended, @comment, @loan_id) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@attended", attendee.Attended); c.AddParam("@comment", attendee.Comment); c.AddParam("@village_id", attendee.VillageId); c.AddParam("@person_id", attendee.TiersId); c.AddParam("@attended_date", attendee.AttendedDate); c.AddParam("@loan_id", attendee.LoanId); attendee.Id = Convert.ToInt32(c.ExecuteScalar()); } } else { const string q = @"UPDATE dbo.VillagesAttendance SET attended = @attended, comment = @comment WHERE village_id = @village_id AND person_id = @person_id AND [date] = @attended_date"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@attended", attendee.Attended); c.AddParam("@comment", attendee.Comment); c.AddParam("@village_id", attendee.VillageId); c.AddParam("@person_id", attendee.TiersId); c.AddParam("@attended_date", attendee.AttendedDate); c.ExecuteNonQuery(); } } }
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 string GetRepaymentModuleLastStartupDate() { const string sqlText = @"SELECT value FROM dbo.TechnicalParameters WHERE name='repayment_module_last_startup_date'"; using (var conn = GetConnection()) using (var select = new OpenCbsCommand(sqlText, conn)) { var re = select.ExecuteScalar(); return re == null ? String.Empty : re.ToString(); } }