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 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 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(); } }
/// <summary> /// Select all events for selected Funding Line /// </summary> /// <param name="fundingLine">funding line </param> /// <returns>list of Funding Line events</returns> public List <FundingLineEvent> SelectFundingLineEvents(FundingLine fundingLine) { List <FundingLineEvent> list = new List <FundingLineEvent>(); const string sqlText = @"SELECT [id], [code], [amount], [direction], [fundingline_id], [deleted], [creation_date], [type] FROM [FundingLineEvents] WHERE fundingline_id = @fundingline_id ORDER BY creation_date DESC, id DESC"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn)) { cmd.AddParam("@fundingline_id", fundingLine.Id); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader == null || reader.Empty) { return(list); } { while (reader.Read()) { FundingLineEvent fundingLineEvent = new FundingLineEvent { Id = reader.GetInt("id"), Code = reader.GetString("code"), Amount = reader.GetMoney("amount"), Movement = ((OBookingDirections) reader.GetSmallInt("direction")), IsDeleted = reader.GetBool("deleted"), CreationDate = reader.GetDateTime("creation_date"), Type = ((OFundingLineEventTypes) reader.GetSmallInt("type")), FundingLine = fundingLine }; list.Add(fundingLineEvent); } } } return(list); } }
public void UpdateStandardBooking(Booking booking) { const string sqlText = @"UPDATE StandardBookings SET [Name] = @name, debit_account_id = @debit_account_id, credit_account_id = @credit_account_id WHERE Id = @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.AddParam("@Id", booking.Id); insertIntoTable.ExecuteNonQuery(); } } }
private static void SetAccount(OpenCbsCommand cmd, Account pAccount) { cmd.AddParam("@number", pAccount.Number); cmd.AddParam("@label", pAccount.Label); cmd.AddParam("@debitPlus", pAccount.DebitPlus); cmd.AddParam("@type", pAccount.Type); cmd.AddParam("@typeCode", pAccount.TypeCode); cmd.AddParam("@account_category_id", (int)pAccount.AccountCategory); if (pAccount.ParentAccountId == 0 || pAccount.ParentAccountId == null) { cmd.AddParam("parentAccountId", null); } else { cmd.AddParam("@parentAccountId", pAccount.ParentAccountId); } cmd.AddParam("@lft", pAccount.Left); cmd.AddParam("@rgt", pAccount.Right); }
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 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 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 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 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 AccountingRuleCollection SelectAllByEventType(string type) { const string sqlText = @"SELECT id, rule_type FROM [AccountingRules] WHERE deleted = 0 AND (event_type = @event_type OR @event_type = '')"; AccountingRuleCollection rules = new AccountingRuleCollection(); using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@event_type", type); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) { return(rules); } while (reader.Read()) { if (reader.GetChar("rule_type") == 'C') { rules.Add(new ContractAccountingRule { Id = reader.GetInt("id") }); } } } } } List <Account> accounts = _accountManager.SelectAllAccounts(); for (int i = 0; i < rules.Count; i++) { if (rules[i] is ContractAccountingRule) { rules[i] = SelectContractAccountingRule(rules[i].Id); } else { rules[i] = SelectFundingLineAccountingRule(rules[i].Id); } rules[i].DebitAccount = accounts.FirstOrDefault(item => item.Id == rules[i].DebitAccount.Id); rules[i].CreditAccount = accounts.FirstOrDefault(item => item.Id == rules[i].CreditAccount.Id); } return(rules); }
public 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 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 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 DeleteSavingsEventByLoanEventId(int loanEventId, SqlTransaction sqlTransaction) { const string q = @"UPDATE [dbo].[SavingEvents] SET [deleted]=1, [is_exported]=0 WHERE loan_event_id = @loan_event_id"; using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransaction.Connection, sqlTransaction)) { c.AddParam("@loan_event_id", loanEventId); c.ExecuteNonQuery(); } }
//public Currency SelectCurrencyByName(string pName) //{ // const string q = @"SELECT id, name, code, is_pivot, is_swapped FROM [Currencies] where name = @name"; // using (SqlConnection conn = GetConnection()) // using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) // { // c.AddParam("@name", pName); // using (OpenCbsReader r = c.ExecuteReader()) // { // if (r == null || r.Empty) return null; // r.Read(); // return new Currency // { // Id = r.GetInt("id", reader), // Code = r.GetString("code", reader), // Name = r.GetString("name", reader), // IsPivot = r.GetBool("is_pivot", reader), // IsSwapped = r.GetBool("is_swapped", reader) // }; // } // } //} public bool IsThisCurrencyAlreadyExist(string pCode, string pName) { const string q = @"SELECT * FROM [Currencies] where name = @name AND code = @code"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@code", pCode); c.AddParam("@name", pName); using (OpenCbsReader r = c.ExecuteReader()) { if (r == null || r.Empty) { return(false); } return(true); } } }
public 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 Delete(MessageTemplate messageTemplate) { const string q = @"UPDATE dbo.MessageTemplates SET Deleted = 1 WHERE Id = @Id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@Id", messageTemplate.Id); c.ExecuteNonQuery(); } }
public int UpdateSelectedParameter(string pName, bool 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)) { if (pNewValue) { c.AddParam("@value", "1"); } else { c.AddParam("@value", "0"); } c.AddParam("@key", pName); return(c.ExecuteNonQuery()); } }
public void DeleteFiscalYear(SqlTransaction sqlTransaction, FiscalYear fiscalYear) { const string sqlText = @"DELETE FROM dbo.FiscalYear WHERE id = @id"; using (OpenCbsCommand command = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction)) { command.AddParam("@id", fiscalYear.Id); command.ExecuteNonQuery(); } }
public void Delete(EmailAccount emailAccount) { const string q = @"UPDATE dbo.EmailAccounts SET Deleted = 1 WHERE Id = @Id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@Id", emailAccount.Id); c.ExecuteNonQuery(); } }
public void Delete(QueuedSMS queuedSMS) { const string q = @"UPDATE dbo.QueuedSMS SET Deleted = 1 WHERE Id = @Id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@Id", queuedSMS.Id); c.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 DeleteAccountCategory(AccountCategory accountCategory) { const string sqlText = @"DELETE FROM AccountsCategory WHERE id = @id"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, conn)) { delete.AddParam("@id", accountCategory.Id); delete.ExecuteNonQuery(); } } }
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 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(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 void InsertFiscalYear(SqlTransaction sqlTransaction, FiscalYear fiscalYear) { const string sqlText = @"INSERT INTO dbo.FiscalYear ( name , open_date , close_date ) VALUES ( @name , @open_date , @close_date )"; using (OpenCbsCommand command = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction)) { command.AddParam("@name", fiscalYear.Name); 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.ExecuteNonQuery(); } }
private static void SetProvisioningRate(OpenCbsCommand octCommand, ProvisioningRate pProvisionningRate) { octCommand.AddParam("@number", pProvisionningRate.Number); octCommand.AddParam("@numberOfDaysMin", pProvisionningRate.NbOfDaysMin); octCommand.AddParam("@numberOfDaysMax", pProvisionningRate.NbOfDaysMax); octCommand.AddParam("@ProvisioningOlb", pProvisionningRate.ProvisioningValue); octCommand.AddParam("@ProvisioningInterest", pProvisionningRate.ProvisioningInterest); octCommand.AddParam("@ProvisioningPenalty", pProvisionningRate.ProvisioningPenalty); }
private static void SetTrancheEvent(OpenCbsCommand cmd, TrancheEvent trancheEvent) { cmd.AddParam("@Id", trancheEvent.Id); cmd.AddParam("@InterestRate", trancheEvent.InterestRate); cmd.AddParam("@Amount", trancheEvent.Amount); cmd.AddParam("@Maturity", trancheEvent.Maturity); cmd.AddParam("@StartDate", trancheEvent.StartDate); cmd.AddParam("@applied_new_interest", trancheEvent.StartDate); }
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 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 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 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 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 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 SelectSavingsBookContract(SavingBookContract saving) { const string sqlText = @"SELECT sbc.flat_withdraw_fees, sbc.rate_withdraw_fees, sbc.flat_transfer_fees, sbc.rate_transfer_fees, sbc.flat_deposit_fees, sbc.flat_close_fees, sbc.flat_management_fees, sbc.flat_overdraft_fees, sbc.in_overdraft, sbc.rate_agio_fees, sbc.cheque_deposit_fees, sbc.flat_reopen_fees, sbc.flat_ibt_fee, sbc.rate_ibt_fee, sbc.use_term_deposit, sbc.term_deposit_period, sbc.term_deposit_period_min, sbc.term_deposit_period_max, sbc.transfer_account, sbc.rollover, t.branch_id, sbc.next_maturity FROM dbo.SavingBookContracts sbc INNER JOIN dbo.SavingContracts sc on sc.id = sbc.id INNER JOIN dbo.Tiers t on t.id = sc.tiers_id WHERE sbc.id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@id", saving.Id); using (OpenCbsReader reader = select.ExecuteReader()) { if (!reader.Read()) return; GetSavingBookFromReader(saving, reader); } //It needs to launch a new reader, that's why next code is here saving.TransferAccount = Select(saving.TransferAccount.Code); } OnSavingSelected(saving); }
public SavingBookContract SelectSavingsByLoanId(int pLoanId, bool loadLoans) { SavingBookContract saving; const string sqlText = @"SELECT SavingContracts.*, u2.first_name AS so_first_name, u2.last_name AS so_last_name, SavingProducts.product_type, Users.id AS [user_id], Users.deleted, Users.user_name, Users.user_pass, Users.role_code, Users.first_name, Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN LoansLinkSavingsBook ON LoansLinkSavingsBook.savings_id = SavingContracts.id INNER JOIN Users ON SavingContracts.[user_id] = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE LoansLinkSavingsBook.loan_id = @loanId"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@loanId", pLoanId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return null; reader.Read(); saving = (SavingBookContract)GetSavingFromReader(reader); } } if (saving != null) { saving.Product = (SavingsBookProduct)_savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); if (loadLoans) saving.Loans = SelectLoansBySavingsId(saving.Id); } return saving; }
public List<Loan> SelectLoansBySavingsId(int savingsId) { List<Loan> listLoans = new List<Loan>(); const string sqlText = @"SELECT loan_id FROM LoansLinkSavingsBook WHERE savings_id = @savingsId"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@savingsId", savingsId); using (OpenCbsReader reader = select.ExecuteReader()) { if (!reader.Empty) { while (reader.Read()) { listLoans.Add(_loanManager.SelectLoan(reader.GetInt("loan_id"), true, false, false)); } } } } return listLoans; }
public List<ISavingsContract> SelectSavings(int pClientId) { const string sqlText = @"SELECT SavingContracts.[id] ,SavingContracts.[product_id] ,SavingContracts.[user_id] ,SavingContracts.[code] ,SavingContracts.[tiers_id] ,SavingContracts.[creation_date] ,SavingContracts.[interest_rate] ,SavingContracts.[status] ,SavingContracts.[closed_date] ,SavingContracts.[savings_officer_id] ,SavingContracts.[initial_amount] ,SavingContracts.[entry_fees] ,SavingContracts.[nsg_id] ,u2.first_name AS so_first_name ,u2.last_name AS so_last_name ,SavingProducts.product_type ,Users.id AS user_id ,Users.deleted ,Users.user_name ,Users.user_pass ,Users.role_code ,Users.first_name ,Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE SavingContracts.tiers_id = @tiers_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { List<ISavingsContract> savings = new List<ISavingsContract>(); select.AddParam("@tiers_id", pClientId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return new List<ISavingsContract>(); while (reader.Read()) { savings.Add(GetSavingFromReader(reader)); } } foreach (ISavingsContract saving in savings) { SelectSavingsBookContract((SavingBookContract)saving); saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); ((SavingBookContract)saving).Loans = SelectLoansBySavingsId(saving.Id); } return savings; } }
public List<ISavingsContract> SelectAll(string pSavingCode) { List<ISavingsContract> listSaving = new List<ISavingsContract>(); const string sqlText = @"SELECT SavingContracts.*, u2.first_name AS so_first_name, u2.last_name AS so_last_name, SavingProducts.product_type, Users.id AS user_id, Users.deleted, Users.user_name, Users.user_pass, Users.role_code, Users.first_name, Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE SavingContracts.Code LIKE @code"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@code", string.Format("%{0}%", pSavingCode)); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return new List<ISavingsContract>(); while (reader.Read()) { ISavingsContract saving = GetSavingFromReader(reader); listSaving.Add(saving); } } } foreach (ISavingsContract saving in listSaving) { SelectSavingsBookContract((SavingBookContract)saving); saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); } return listSaving; }
public KeyValuePair<int, string>[] SelectClientSavingBookCodes(int tiersId, int? currencyId) { const string sqlText = @"SELECT sc.id, sc.code FROM SavingContracts sc INNER JOIN SavingProducts sp ON sc.product_id = sp.id AND (@currency_id IS NULL OR sp.currency_id = @currency_id) WHERE tiers_id = @tiersId AND closed_date IS NULL"; using (SqlConnection conn = GetConnection()) using (var command = new OpenCbsCommand(sqlText, conn)) { command.AddParam("tiersId", tiersId); command.AddParam("currency_id", currencyId); var result = new List<KeyValuePair<int, string>>(); using (var reader = command.ExecuteReader()) { while(reader.Read()) { var savingId = reader.GetInt("id"); var savingCode = reader.GetString("code"); result.Add( new KeyValuePair<int, string>(savingId, savingCode) ); } return result.ToArray(); } } }
public SavingBookContract Select(int pSavingId) { SavingBookContract saving; const string sqlText = @"SELECT SavingContracts.*, u2.first_name AS so_first_name, u2.last_name AS so_last_name, SavingProducts.product_type, Users.id AS user_id, Users.deleted, Users.user_name, Users.user_pass, Users.role_code, Users.first_name, Users.last_name FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id WHERE SavingContracts.id = @id"; using (SqlConnection conn = GetConnection()) using(var cmd = new OpenCbsCommand(sqlText, conn)) { cmd.AddParam("@id", pSavingId); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) return null; reader.Read(); saving = GetSavingFromReader(reader); } } SelectSavingsBookContract(saving); saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id); saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product)); return saving; }
public 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 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(); } }
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 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 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(); } }
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 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(); } }
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 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 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 GetNumberSavingContract(string pQuery, bool all, bool activeContractsOnly) { string sql =@"SELECT TOP 100 percent SavingContracts.id, SavingContracts.code AS contract_code, SavingContracts.creation_date as start_date, Persons.identification_data as identification_data, Tiers.client_type_code, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name, ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name FROM SavingContracts INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id LEFT OUTER JOIN Persons ON Tiers.id = Persons.id LEFT OUTER JOIN Groups ON Tiers.id = Groups.id LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id"; sql += " WHERE 1=1 "; if (activeContractsOnly) sql += " AND SavingContracts.[status]=1 "; if (!all) { sql += @" AND Tiers.branch_id in ( select branch_id from dbo.UsersBranches WHERE user_id = @user_id )"; } sql += @") maTable"; const string closeWhere = @" WHERE (contract_code LIKE @contractCode OR client_name LIKE @clientName OR user_name LIKE @userName OR identification_data LIKE @numberPassport OR loanofficer_name LIKE @loanofficerName )) maTable"; QueryEntity q = new QueryEntity(pQuery, sql, closeWhere); string sqlText = q.ConstructSQLEntityNumberProxy(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { foreach (var item in q.DynamiqParameters()) { select.AddParam(item.Key, string.Format("%{0}%", item.Value)); } select.AddParam("@user_id", User.CurrentUser.Id); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader.Empty) return 0; reader.Read(); return reader.GetInt(0); } } }
public static 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 List<SavingSearchResult> SearchSavingContractByCritere(int pPageNumber, string pQuery, bool all, bool activeContractOnly) { List<SavingSearchResult> list = new List<SavingSearchResult>(); string sql = @"SELECT SavingContracts.id, SavingContracts.code AS contract_code, SavingContracts.status AS contract_status, SavingContracts.creation_date as start_date, SavingContracts.closed_date as end_date, SavingProducts.product_type as product_type, Persons.identification_data as identification_data, Tiers.client_type_code, Tiers.id AS client_id, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name, ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name, ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name, SavingContracts.user_id AS loan_officer_id, SavingProducts.currency_id FROM SavingContracts INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id INNER JOIN Users ON SavingContracts.user_id = Users.id INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.id LEFT OUTER JOIN Persons ON Tiers.id = Persons.id LEFT OUTER JOIN Groups ON Tiers.id = Groups.id LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id"; sql += " WHERE 1=1 "; if (activeContractOnly) sql += " AND SavingContracts.status=1 "; if (!all) { sql += @" AND Tiers.branch_id in ( select branch_id from dbo.UsersBranches WHERE user_id = @user_id )"; } sql += ") maTable"; const string closeWhere = @" WHERE ( contract_code LIKE @contractCode OR client_name LIKE @clientName OR user_name LIKE @UserName OR identification_data LIKE @numberPassport OR loanofficer_name LIKE @loanofficerName )) maTable"; QueryEntity q = new QueryEntity(pQuery, sql, closeWhere); string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pPageNumber - 1) * 20); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(pSqlText, conn)) { foreach (var item in q.DynamiqParameters()) { select.AddParam(item.Key, string.Format("%{0}%", item.Value)); } select.AddParam("@user_id", User.CurrentUser.Id); using (OpenCbsReader reader = select.ExecuteReader()) { if (!reader.Empty) { while (reader.Read()) { SavingSearchResult result = new SavingSearchResult { Id = reader.GetInt("id"), ClientId = reader.GetInt("client_id"), ContractCode = reader.GetString("contract_code"), Status = (OSavingsStatus)reader.GetSmallInt("contract_status"), ClientTypeCode = reader.GetString("client_type_code") }; switch (result.ClientTypeCode) { case "I": result.ClientType = OClientTypes.Person; break; case "G": result.ClientType = OClientTypes.Group; break; case "V": result.ClientType = OClientTypes.Village; break; case "C": result.ClientType = OClientTypes.Corporate; break; default: result.ClientType = OClientTypes.Person; break; } result.ClientName = reader.GetString("client_name"); result.ContractStartDate = reader.GetDateTime("start_date"); result.ContractEndDate = reader.GetNullDateTime("end_date"); result.ContractType = reader.GetString("product_type"); result.LoanOfficer = new User { Id = reader.GetInt("loan_officer_id") }; result.CurrencyId = reader.GetInt("currency_id"); list.Add(result); } } } return list; } }