public void RunSQL(string sqlText, SqlTransaction tran) { using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, tran.Connection, tran)) { sqlCommand.ExecuteNonQuery(); } }
public int AddUser(User pUser) { const string sqlText = @"INSERT INTO [Users] ( [deleted], [role_code], [user_name], [user_pass], [first_name], [last_name], [mail], [sex], [phone]) VALUES( @deleted, @roleCode, @username, @userpass, @firstname, @lastname, @mail, @sex, @phone) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@deleted", false); SetUser(sqlCommand, pUser); pUser.Id = int.Parse(sqlCommand.ExecuteScalar().ToString()); SaveUsersRole(pUser.Id, pUser.UserRole.Id); } return pUser.Id; }
/// <summary> /// Add Role in database /// </summary> /// <param name="pRole"></param> /// <returns>Role id</returns> public int AddRole(Role pRole) { const string q = @"INSERT INTO [Roles] ([deleted], [code], [description], [role_of_loan], [role_of_saving], [role_of_teller]) VALUES(@deleted, @code, @description, @role_of_loan, @role_of_saving, @role_of_teller) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@deleted", false); c.AddParam("@code", pRole.RoleName); c.AddParam("@description", pRole.Description); c.AddParam("@role_of_loan", pRole.IsRoleForLoan); c.AddParam("@role_of_saving", pRole.IsRoleForSaving); c.AddParam("@role_of_teller", pRole.IsRoleForTeller); pRole.Id = int.Parse(c.ExecuteScalar().ToString()); SaveRoleMenu(pRole); SaveAllowedActionsForRole(pRole); } } return pRole.Id; }
protected void DeleteDatasFromTable(string tableName, SqlTransaction transaction) { const string deleteSql = "DELETE FROM {0}"; string query = string.Format(deleteSql, tableName); using (OpenCbsCommand command = new OpenCbsCommand(query, transaction.Connection, transaction)) command.ExecuteNonQuery(); }
/// <summary> /// Method to add a package 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="colProduct">Package Object</param> /// <returns>The id of the package which has been added</returns> public int AddCollateralProduct(CollateralProduct colProduct) { string sqlText = @"INSERT INTO [CollateralProducts] ( [name] ,[desc] ,[deleted] ) VALUES ( @name ,@desc ,@deleted ) SELECT CONVERT(int, SCOPE_IDENTITY())"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@name", colProduct.Name); cmd.AddParam("@desc", colProduct.Description); cmd.AddParam("@deleted", colProduct.Delete); colProduct.Id = int.Parse(cmd.ExecuteScalar().ToString()); } foreach (CollateralProperty collateralProperty in colProduct.Properties) AddCollateralProperty(colProduct.Id, collateralProperty); return colProduct.Id; }
/// <summary> /// Add a new picture in the database.<br/> /// If pName is null or empty, an automatic name will be generated. /// </summary> /// <param name="pGroup">Picture group</param> /// <param name="pId">Picture Id</param> /// <param name="pictureSubId">Picture subId</param> /// <param name="pPicture">PNG picture binary data</param> /// <param name="pThumbnail">PNG thumbnail picture binary data</param> /// <param name="pName">Picture name</param> /// <returns></returns> public int AddPicture(string pGroup, int pId, int pictureSubId, byte[] pPicture, byte[] pThumbnail, string pName) { // Find the first available subid List<int> subIds = GetPictureSubIds(pGroup, pId); int foundPlace = subIds.Count; for (int i = 0; i < subIds.Count; i++) { if (subIds[i] != i) { foundPlace = i; break; } } // Add row string q = @"INSERT INTO Pictures ([group], [id] ,[subid] ,[picture] ,[thumbnail] ,[name]) VALUES (@group ,@id ,@subid ,@picture ,@thumbnail ,@name)"; using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection)) { c.AddParam("group", pGroup); c.AddParam("id", pId); c.AddParam("subid", pictureSubId); c.AddParam("picture", pPicture); c.AddParam("thumbnail", pThumbnail); if (pName.Length < 50) c.AddParam("name", pName); else c.AddParam("name", pName.Substring(0, 49)); c.ExecuteNonQuery(); } return foundPlace; }
public void DeleteInstallmentType(InstallmentType installmentType) { const string q = @"DELETE FROM [InstallmentTypes] WHERE id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", installmentType.Id); c.ExecuteNonQuery(); } }
public void AddPublicHoliday(DictionaryEntry entry) { const string sqlText = "INSERT INTO [PublicHolidays]([date], [name]) VALUES(@date, @name)"; using(SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@date",Convert.ToDateTime(entry.Key)); cmd.AddParam("@name", entry.Value.ToString()); cmd.ExecuteNonQuery(); } }
public int AddDistrict(District pDistrict) { const string q = "INSERT INTO [Districts]([name], [province_id],[deleted]) VALUES(@name,@provinceId,0) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", pDistrict.Name); c.AddParam("@provinceId", pDistrict.Province.Id); c.AddParam("@deleted", false); return int.Parse(c.ExecuteScalar().ToString()); } }
public int AddCity(City pCity) { const string q = "INSERT INTO [City] ([name], [district_id],[deleted]) VALUES (@name,@district_id,0) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", pCity.Name); c.AddParam("@district_id", pCity.DistrictId); c.AddParam("@deleted", pCity.Deleted); return int.Parse(c.ExecuteScalar().ToString()); } }
public int AddInstallmentType(InstallmentType installmentType) { const string q = @"INSERT INTO [InstallmentTypes]([name], [nb_of_days], [nb_of_months]) VALUES(@name, @days, @months) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { SetInstallmentType(c, installmentType); return int.Parse(c.ExecuteScalar().ToString()); } }
public void AddCollectionItem(int fieldId, string colItem) { string sqlListText = @"INSERT INTO [AdvancedFieldsCollections] ([field_id], [value]) VALUES (@field_id, @col_item)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand insertCmd = new OpenCbsCommand(sqlListText, conn)) { insertCmd.AddParam("@field_id", fieldId); insertCmd.AddParam("@col_item", colItem); insertCmd.ExecuteNonQuery(); } }
public void EditInstallmentType(InstallmentType installmentType) { const string q = @"UPDATE [InstallmentTypes] SET [name] = @name, [nb_of_days] = @days, [nb_of_months] = @months WHERE id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { SetInstallmentType(c, installmentType); c.AddParam("@id", installmentType.Id); c.ExecuteNonQuery(); } }
public List <FundingLine> SelectFundingLines() { List <FundingLine> list = new List <FundingLine>(); string q = @"SELECT FundingLines.[id], FundingLines.[name], [begin_date], [end_date], [amount], [purpose], [deleted], [currency_id], Currencies.[name] as currency_name, Currencies.[code] as currency_code, Currencies.[is_pivot] as currency_is_pivot, Currencies.[is_swapped] as currency_is_swapped FROM [FundingLines] LEFT JOIN Currencies ON FundingLines.currency_id = Currencies.id WHERE [deleted] = 0"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { while (r.Read()) { FundingLine fundingLine = new FundingLine(); fundingLine.Id = r.GetInt("id"); fundingLine.Name = r.GetString("name"); fundingLine.StartDate = r.GetDateTime("begin_date"); fundingLine.EndDate = r.GetDateTime("end_date"); fundingLine.Amount = r.GetMoney("amount"); fundingLine.Amount = r.GetMoney("amount"); fundingLine.Purpose = r.GetString("purpose"); fundingLine.Deleted = r.GetBool("deleted"); fundingLine.Currency = new Currency { Id = r.GetInt("currency_id"), Name = r.GetString("currency_name"), Code = r.GetString("currency_code"), IsPivot = r.GetBool("currency_is_pivot"), IsSwapped = r.GetBool("currency_is_swapped") }; SetLazyLoader(fundingLine); list.Add(fundingLine); } } return(list); }
public void AddPaymentMethodToBranch(PaymentMethod paymentMethod) { const string q = @"INSERT INTO LinkBranchesPaymentMethods (branch_id, payment_method_id, account_id) VALUES (@branch_id, @payment_method_id, @account_id)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@branch_id", paymentMethod.Branch.Id); c.AddParam("@payment_method_id", paymentMethod.Id); c.AddParam("@account_id", paymentMethod.Account.Id); c.ExecuteNonQuery(); } }
public bool CodeExists(int id, string code) { const string q = @"select count(*) from dbo.Branches where code = @code and id <> @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", id); c.AddParam("@code", code); return Convert.ToBoolean(c.ExecuteScalar()); } }
public void UpdateUser(User pUser, bool saveSecret = false) { string sqlText = @"UPDATE [Users] SET [user_name] = @username, [role_code] = @roleCode, [first_name] = @firstname, [last_name] = @lastname, [last_updated] = @now, [timed_out] = @timedout, [is_expired] = @isexpired, [is_reset] = @isreset, [login_attempt] = @loginattempt, [mail] = @mail, [sex] = @sex, [phone] = @phone, [frapid_login_id] = @frapidloginid WHERE [id] = @userId"; if (saveSecret) { sqlText = @"UPDATE [Users] SET [user_name] = @username, [user_pass] = @userpass, [user_sq] = @usersq, [user_sqa] = @usersqa, [role_code] = @roleCode, [first_name] = @firstname, [last_name] = @lastname, [last_updated] = @now, [timed_out] = @timedout, [is_expired] = @isexpired, [is_reset] = @isreset, [login_attempt] = @loginattempt, [mail] = @mail, [sex] = @sex, [phone] = @phone, [frapid_login_id] = @frapidloginid WHERE [id] = @userId"; } using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@userId", pUser.Id); SetUser(sqlCommand, pUser, saveSecret); sqlCommand.ExecuteNonQuery(); _UpdateUsersRole(pUser.Id, pUser.UserRole.Id); } }
public void UpdatePaymentMethodFromBranch(PaymentMethod paymentMethod) { const string q = @"UPDATE LinkBranchesPaymentMethods SET account_id = @account_id, payment_method_id = @payment_method_id WHERE id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@account_id", paymentMethod.Account.Id); c.AddParam("@payment_method_id", paymentMethod.Id); c.AddParam("@id", paymentMethod.LinkId); c.ExecuteNonQuery(); } }
public int AddCity(City pCity) { const string q = "INSERT INTO [City] ([name], [district_id],[deleted]) VALUES (@name,@district_id,0) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", pCity.Name); c.AddParam("@district_id", pCity.DistrictId); c.AddParam("@deleted", pCity.Deleted); var id = c.ExecuteScalar().ToString(); RefreshCache(); return(int.Parse(id)); } }
public int AddDistrict(District pDistrict) { const string q = "INSERT INTO [Districts]([name], [province_id],[deleted]) VALUES(@name,@provinceId,0) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", pDistrict.Name); c.AddParam("@provinceId", pDistrict.Province.Id); c.AddParam("@deleted", false); var id = c.ExecuteScalar().ToString(); RefreshCache(); return(int.Parse(id)); } }
public bool CodeExists(int id, string code) { const string q = @"select count(*) from dbo.Branches where code = @code and id <> @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", id); c.AddParam("@code", code); return(Convert.ToBoolean(c.ExecuteScalar())); } }
public List <KeyValuePair <int, Installment> > SelectInstalments() { const string q = @"SELECT contract_id, expected_date, interest_repayment, capital_repayment, number, paid_interest, paid_capital, fees_unpaid, paid_date, paid_fees, comment, pending, start_date, olb, commission, paid_commission, last_interest_accrual_date, extra_amount_1, extra_amount_2 FROM Installments WHERE paid_capital = 0 AND paid_interest = 0"; //select only those Installments that have not had any repayments using (var conn = GetConnection()) using (var c = new OpenCbsCommand(q, conn)) { using (var r = c.ExecuteReader()) { if (r == null || r.Empty) { return(new List <KeyValuePair <int, Installment> >()); } var installmentList = new List <KeyValuePair <int, Installment> >(); while (r.Read()) { var result = new KeyValuePair <int, Installment>( r.GetInt("contract_id"), GetInstallment(r)); installmentList.Add(result); } return(installmentList); } } }
public void UpdateDeletedEconomicActivityLoanHistory(int contractId, int personId, int economicActivityId, SqlTransaction sqlTransaction, bool deleted) { const string sqlText = @"UPDATE EconomicActivityLoanHistory SET deleted = @deleted, economic_activity_id = @economic_activity_id WHERE contract_id = @contract_id AND person_id = @person_id"; using (OpenCbsCommand update = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction)) { update.AddParam("@contract_id", contractId); update.AddParam("@person_id", personId); update.AddParam("@economic_activity_id", economicActivityId); update.AddParam("@deleted", deleted); update.ExecuteNonQuery(); } }
public Branch Add(Branch branch, SqlTransaction t) { const string q = @"INSERT INTO dbo.Branches (name, code, address, description) VALUES (@name, @code, @address, @description) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t)) { c.AddParam("@name", branch.Name); c.AddParam("@code", branch.Code); c.AddParam("@address", branch.Address); c.AddParam("@description", branch.Description); branch.Id = Convert.ToInt32(c.ExecuteScalar()); return branch; } }
public void AddAllowedItem(int pRoleId, int pActionId, bool pAllowed) { const string q = @"INSERT INTO AllowedRoleActions (action_item_id, role_id, allowed) VALUES (@actionId, @roleId, @allowed)"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@allowed", pAllowed); c.AddParam("@actionId", pActionId); c.AddParam("@roleId", pRoleId); c.ExecuteNonQuery(); } } }
private void AddAllowedMenuItem(int pRoleId, int pMenuId, bool pAllowed) { const string q = @"INSERT INTO AllowedRoleMenus (menu_item_id, role_id, allowed) VALUES (@menuId, @roleId, @allowed)"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@allowed", pAllowed); c.AddParam("@menuId", pMenuId); c.AddParam("@roleId", pRoleId); c.ExecuteNonQuery(); } } }
public void UpdateCollateralProduct(int productId, string name, string description) { const string sqlText = @"UPDATE [CollateralProducts] SET [name] = @name, [desc] = @desc WHERE id = @product_id"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand updateProduct = new OpenCbsCommand(sqlText, connection)) { updateProduct.AddParam("@product_id", productId); updateProduct.AddParam("@name", name); updateProduct.AddParam("@desc", description); updateProduct.ExecuteNonQuery(); } }
public void AddInstallments(List <Installment> pInstallments, int pLoanId, SqlTransaction pSqlTransac) { const string q = @"INSERT INTO Installments( expected_date, interest_repayment, capital_repayment, contract_id, number, paid_interest, paid_capital, fees_unpaid, paid_date, paid_fees, comment, pending, start_date, olb, commission, paid_commission) VALUES ( @expectedDate, @interestsRepayment, @capitalRepayment, @contractId, @number, @paidInterests, @paidCapital, @feesUnpaid, @paidDate, @paid_fees, @comment, @pending, @start_date, @olb, @commission, @paidCommission)"; using (var c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { foreach (var installment in pInstallments) { SetInstallment(installment, pLoanId, c); c.ExecuteNonQuery(); c.ResetParams(); } } }
public List <User> SelectAll() { const string q = @"SELECT id, deleted, user_name, first_name, last_name, user_pass, mail, sex, phone, (SELECT COUNT(*) FROM dbo.Credit WHERE loanofficer_id = u.id) AS num_contracts FROM dbo.Users AS u"; List <User> users = new List <User>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(users); } while (r.Read()) { User u = new User { Id = r.GetInt("id"), FirstName = r.GetString("first_name"), LastName = r.GetString("last_name"), IsDeleted = r.GetBool("deleted"), UserName = r.GetString("user_name"), Password = r.GetString("user_pass"), Mail = r.GetString("mail"), Sex = r.GetChar("sex"), HasContract = r.GetInt("num_contracts") > 0 }; users.Add(u); } } return(users); }
public Branch Add(Branch branch, SqlTransaction t) { const string q = @"INSERT INTO dbo.Branches (name, code, address, description) VALUES (@name, @code, @address, @description) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t)) { c.AddParam("@name", branch.Name); c.AddParam("@code", branch.Code); c.AddParam("@address", branch.Address); c.AddParam("@description", branch.Description); branch.Id = Convert.ToInt32(c.ExecuteScalar()); return(branch); } }
private void SelectAllPublicHolidays() { const string sqlText = @"SELECT name, date FROM PublicHolidays ORDER BY date"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, connection)) using (OpenCbsReader reader = select.ExecuteReader()) { while (reader.Read()) { nonWorkingDateHelper.PublicHolidays.Add(reader.GetDateTime("date"), reader.GetString("name")); } } }
/// <summary> /// Add an economic activity in database /// </summary> /// <param name="pEconomicActivity">the economic activity object to add</param> /// <returns>the id of the economic activity added</returns> public int AddEconomicActivity(EconomicActivity pEconomicActivity) { const string sqlText = @"INSERT INTO EconomicActivities ([name] , [parent_id] , [deleted]) VALUES (@name,@parentId,@deleted) SELECT SCOPE_IDENTITY()"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, connection)) { insert.AddParam("@name", pEconomicActivity.Name); insert.AddParam("@deleted", pEconomicActivity.Deleted); if (pEconomicActivity.Parent != null) insert.AddParam("@parentId", pEconomicActivity.Parent.Id); else insert.AddParam("@parentId", null); return int.Parse(insert.ExecuteScalar().ToString()); } }
public void AddCollateralPropertyCollectionItem(int collateralPropertyId, string colItem) { string sqlListText = @"INSERT INTO [CollateralPropertyCollections] ([property_id] ,[value]) VALUES (@property_id , @col_item)"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlListText, connection)) { cmd.AddParam("@property_id", collateralPropertyId); cmd.AddParam("@col_item", colItem); cmd.ExecuteNonQuery(); } }
public List <ProjetSearchResult> SelectProjectByCriteres(int pageNumber, string pQuery) { List <ProjetSearchResult> list = new List <ProjetSearchResult>(); string SELECT_FROM_PROJET_ = @" SELECT DISTINCT pro.id,pro.code,pro.name as name_project,pro.aim,pers.first_name, pers.last_name,tie.client_type_code,tie.id as tiers_id,corp.name as companyName FROM (Projects as pro INNER JOIN Tiers tie on pro.tiers_id=tie.id ) LEFT JOIN Corporates corp on corp.id=tie.id LEFT JOIN Persons pers on pers.id=tie.id ) maTable" ; string CloseWhere = @" WHERE ( companyName LIKE @companyName OR code LIKE @code OR name_project LIKE @nameProject OR aim LIKE @aim OR last_name LIKE @lastName OR first_name LIKE @firtName )) maTable"; QueryEntity q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere); string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pageNumber - 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)); } using (OpenCbsReader reader = select.ExecuteReader()) { while (reader.Read()) { ProjetSearchResult resultat = new ProjetSearchResult(); resultat.Id = reader.GetInt("id"); resultat.Code = reader.GetString("code"); resultat.ProjectName = reader.GetString("name_project"); resultat.CompanyName = reader.GetString("companyName"); resultat.Aim = reader.GetString("aim"); resultat.TiersId = reader.GetInt("tiers_id"); resultat.Status = reader.GetChar("client_type_code"); resultat.LastName = reader.GetString("last_name"); resultat.FirstName = reader.GetString("first_name"); //resultat.ContractCode = reader.GetString("contract_code"); list.Add(resultat); } } } return(list); }
public Teller Add(Teller teller, SqlTransaction t) { const string sqlText = @"INSERT INTO dbo.Tellers (name, [desc], account_id, branch_id, user_id, currency_id, amount_min, amount_max, deposit_amount_min, deposit_amount_max, withdrawal_amount_min, withdrawal_amount_max) VALUES (@name, @desc, @account_id, @branch_id, @user_id, @currency_id, @amount_min, @amount_max, @deposit_amount_min, @deposit_amount_max, @withdrawal_amount_min, @withdrawal_amount_max) SELECT SCOPE_IDENTITY()"; using (var c = new OpenCbsCommand(sqlText, t.Connection, t)) { c.AddParam("@name", teller.Name); c.AddParam("@desc", teller.Description); if (teller.Branch != null) c.AddParam("@branch_id", teller.Branch.Id); else c.AddParam("@branch_id", null); if (teller.Account != null) c.AddParam("@account_id", teller.Account.Id); else c.AddParam("@account_id", null); if (teller.User != null) c.AddParam("@user_id", teller.User.Id); else c.AddParam("@user_id", null); if (teller.Currency != null) c.AddParam("@currency_id", teller.Currency.Id); else c.AddParam("@currency_id", null); c.AddParam("@amount_min", teller.MinAmountTeller); c.AddParam("@amount_max", teller.MaxAmountTeller); c.AddParam("@deposit_amount_min", teller.MinAmountDeposit); c.AddParam("@deposit_amount_max", teller.MaxAmountDeposit); c.AddParam("@withdrawal_amount_min", teller.MinAmountWithdrawal); c.AddParam("@withdrawal_amount_max", teller.MaxAmountWithdrawal); teller.Id = Convert.ToInt32(c.ExecuteScalar()); } return teller; }
public Dictionary <ActionItemObject, bool> GetAllowedActionList(int pRoleId) { //select maintained allowed flags for actions or true if nothing was specified string q = @"SELECT ActionItems.[id], ActionItems.[class_name], ActionItems.[method_name], ISNULL((SELECT allowed FROM AllowedRoleActions WHERE AllowedRoleActions.action_item_id = ActionItems.id AND AllowedRoleActions.role_id = @roleId), 1) as allowed FROM ActionItems"; Dictionary <ActionItemObject, bool> Actions = new Dictionary <ActionItemObject, bool>(); using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@roleId", pRoleId); using (OpenCbsReader r = c.ExecuteReader()) { if (r != null) { if (!r.Empty) { while (r.Read()) { Actions.Add(new ActionItemObject { Id = r.GetInt("id"), ClassName = r.GetString("class_name").Trim(), MethodName = r.GetString("method_name").Trim() }, r.GetBool("allowed")); } } } } } } return(Actions); }
public List <PaymentMethod> GetPaymentMethodOfBranch() { string q = @"SELECT [lbpm].[payment_method_id], [lbpm].[id], [pm].[name], [pm].[description], [pm].[pending], [lbpm].[branch_id], [lbpm].[date], [lbpm].[account_id] FROM PaymentMethods pm INNER JOIN LinkBranchesPaymentMethods lbpm ON lbpm.payment_method_id = pm.id WHERE [lbpm].[deleted] = 0"; List <PaymentMethod> paymentMethods = new List <PaymentMethod>(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { //c.AddParam("@id", branchId); using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(paymentMethods); } while (r.Read()) { PaymentMethod paymentMethod = new PaymentMethod { Id = r.GetInt("payment_method_id"), Name = r.GetString("name"), Description = r.GetString("description"), IsPending = r.GetBool("pending"), LinkId = r.GetInt("id"), Branch = _branchManager.Select(r.GetInt("branch_id")), Date = r.GetDateTime("date"), Account = _accountManager.Select(r.GetInt("account_id")) }; paymentMethods.Add(paymentMethod); } } } return(paymentMethods); }
public MyInformation GetQuestionnaire() { const string sqlText = @"SELECT Name , Country , Email , NumberOfClients , GrossPortfolio , PositionInCompony , PersonName , Phone , Skype , PurposeOfUsage , OtherMessages , is_sent FROM Questionnaire"; MyInformation myInformation = new MyInformation(); myInformation.MfiName = null; using (SqlConnection conn = GetConnection()) using (var cmd = new OpenCbsCommand(sqlText, conn)) { using (OpenCbsReader reader = cmd.ExecuteReader()) { if (!reader.Empty) { reader.Read(); myInformation.MfiName = reader.GetString("Name"); myInformation.Country = reader.GetString("Country"); myInformation.Email = reader.GetString("Email"); myInformation.NumberOfClients = reader.GetString("NumberOfClients"); myInformation.GrossPortfolio = reader.GetString("GrossPortfolio"); myInformation.PositionInCompany = reader.GetString("PositionInCompony"); myInformation.PersonName = reader.GetString("PersonName"); myInformation.Phone = reader.GetString("Phone"); myInformation.Skype = reader.GetString("Skype"); myInformation.PurposeOfUsage = reader.GetString("PurposeOfUsage"); myInformation.Comment = reader.GetString("OtherMessages"); myInformation.IsSent = reader.GetBool("is_sent"); return(myInformation); } return(null); } } }
public void UpdateAllowedItem(int pRoleId, int pActionId, bool pAllowed) { const string q = @"UPDATE [AllowedRoleActions] SET allowed = @allowed WHERE [action_item_id] = @actionId AND role_id = @roleId"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@allowed", pAllowed); c.AddParam("@actionId", pActionId); c.AddParam("@roleId", pRoleId); c.ExecuteNonQuery(); } } }
private void _UpdateFollowUp(FollowUp pUp, SqlTransaction pTransaction) { const string q = @"UPDATE [FollowUp] SET [year] = @year,[CA] = @CA,[Jobs1] = @Jobs1 ,[Jobs2] = @Jobs2 ,[PersonalSituation] = @PersonalSituation ,[activity] = @activity ,[comment] = @comment WHERE id = @id"; using (OpenCbsCommand c = new OpenCbsCommand(q, pTransaction.Connection, pTransaction)) { c.AddParam("@id", pUp.Id); c.AddParam("@year", pUp.Year); c.AddParam("@CA", pUp.CA); c.AddParam("@jobs1", pUp.Jobs1); c.AddParam("@jobs2", pUp.Jobs2); c.AddParam("@personalSituation", pUp.PersonalSituation); c.AddParam("@activity", pUp.Activity); c.AddParam("@comment", pUp.Comment); c.ExecuteNonQuery(); } }
public bool CreateMFI(MFI pMFI) { if (SelectMFI().Login == null) { string sqlText = "INSERT INTO [MFI] ([name], [login], [password]) VALUES(@name,@login,@password)"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@name", pMFI.Name); cmd.AddParam("@login", pMFI.Login); cmd.AddParam("@password", pMFI.Password); cmd.ExecuteNonQuery(); return true; } } return false; }
public void UpdateInstallmentComment(string pComment, int pContractId, int pNumber) { const string q = @"UPDATE Installments SET comment = @comment WHERE contract_id = @contractId AND number = @number"; using (var conn = GetConnection()) using (var c = new OpenCbsCommand(q, conn)) { //primary key = contractId + number c.AddParam("@contractId", pContractId); c.AddParam("@number", pNumber); c.AddParam("@comment", pComment); c.ExecuteNonQuery(); } }
public bool CreateMFI(MFI pMFI) { if (SelectMFI().Login == null) { string sqlText = "INSERT INTO [MFI] ([name], [login], [password]) VALUES(@name,@login,@password)"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@name", pMFI.Name); cmd.AddParam("@login", pMFI.Login); cmd.AddParam("@password", pMFI.Password); cmd.ExecuteNonQuery(); return(true); } } return(false); }
public OStartPages.StartPages GetRolesDefaultStartPageByRoleId(int roleId) { string q = @"SELECT [default_start_view] FROM [Roles] WHERE [id] = @id "; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", roleId); using (OpenCbsReader r = c.ExecuteReader()) { r.Read(); OStartPages.StartPages startPage = (OStartPages.StartPages)Enum.Parse(typeof(OStartPages.StartPages), r.GetString("default_start_view")); return(startPage); } } }
private Project SelectProject(int pId) { Project project = null; string sqlText = "SELECT * FROM Projects WHERE id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn)) { select.AddParam("@id", pId); using (OpenCbsReader reader = select.ExecuteReader()) { if (reader != null) { if (!reader.Empty) { reader.Read(); project = GetProject(reader); } else { return(project); } } } if (project.District != null) { project.District = _locations.SelectDistrictById(project.District.Id); } if (project.Id != 0) { project.FollowUps.AddRange(SelectFollowUps(project.Id)); } if (project.Id != 0) { project.AddCredits(_creditManager.SelectLoansByProject(project.Id)); } } return(project); }
public MyInformation GetQuestionnaire() { const string sqlText = @"SELECT Name , Country , Email , NumberOfClients , GrossPortfolio , PositionInCompony , PersonName , Phone , Skype , PurposeOfUsage , OtherMessages , is_sent FROM Questionnaire"; MyInformation myInformation = new MyInformation(); myInformation.MfiName = null; using (SqlConnection conn = GetConnection()) using (var cmd = new OpenCbsCommand(sqlText, conn)) { using(OpenCbsReader reader = cmd.ExecuteReader()) { if (!reader.Empty) { reader.Read(); myInformation.MfiName = reader.GetString("Name"); myInformation.Country = reader.GetString("Country"); myInformation.Email = reader.GetString("Email"); myInformation.NumberOfClients = reader.GetString("NumberOfClients"); myInformation.GrossPortfolio = reader.GetString("GrossPortfolio"); myInformation.PositionInCompany = reader.GetString("PositionInCompony"); myInformation.PersonName = reader.GetString("PersonName"); myInformation.Phone = reader.GetString("Phone"); myInformation.Skype = reader.GetString("Skype"); myInformation.PurposeOfUsage = reader.GetString("PurposeOfUsage"); myInformation.Comment = reader.GetString("OtherMessages"); myInformation.IsSent = reader.GetBool("is_sent"); return myInformation; } return null; } } }
public List <Installment> GetArchivedInstallments(int eventId, SqlTransaction t) { const string query = @"SELECT number, expected_date, capital_repayment, interest_repayment, paid_interest, paid_capital, paid_fees, fees_unpaid, paid_date, comment, pending, start_date, olb, commission, paid_commission, last_interest_accrual_date, extra_amount_1, extra_amount_2 FROM InstallmentHistory WHERE event_id = @event_id AND delete_date IS NULL"; using (var c = new OpenCbsCommand(query, t.Connection, t)) { c.AddParam("@event_id", eventId); var retval = new List <Installment>(); using (var r = c.ExecuteReader()) { if (null == r || r.Empty) { return(retval); } while (r.Read()) { var i = GetInstallmentHistoryFromReader(r); retval.Add(i); } } return(retval); } }
public byte[] GetPicture(string clientType, int clientId, bool thumbnail, int photoSubId) { string q; if (thumbnail) { q = string.Format( @"SELECT TOP(1) [Pictures].[thumbnail] FROM [Pictures] WHERE [Pictures].[group]=@client_type AND [Pictures].[id]=@client_id AND [Pictures].[subid]=@photo_sub_id "); } else { q = string.Format( @"SELECT TOP(1) Pictures.picture FROM Pictures WHERE [Pictures].[group]=@client_type AND [Pictures].[id]=@client_id AND [Pictures].[subid]=@photo_sub_id "); } using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection)) { c.AddParam("client_type", clientType); c.AddParam("client_id", clientId); c.AddParam("photo_sub_id", photoSubId); using (OpenCbsReader r = c.ExecuteReader()) { if (r.Read()) { return(r.GetBytes(0)); } else { return(null); } } } }
public bool UpdateMFI(MFI pMFI) { if (SelectMFI().Login != null) { string sqlText = @"UPDATE [MFI] SET [name]=@name, [login]=@login, [password]=@password"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@name", pMFI.Name); cmd.AddParam("@login", pMFI.Login); cmd.AddParam("@password", pMFI.Password); cmd.ExecuteNonQuery(); } return(true); } return(false); }
public void AddEconomicActivityLoanHistory(EconomicActivityLoanHistory activityLoanHistory, SqlTransaction sqlTransaction) { const string sqlText = @"INSERT INTO EconomicActivityLoanHistory ([contract_id],[person_id],[group_id],[economic_activity_id],[deleted]) VALUES (@contract_id, @person_id, @group_id, @economic_activity_id, @deleted)"; using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction)) { insert.AddParam("@contract_id", activityLoanHistory.Contract.Id); insert.AddParam("@person_id", activityLoanHistory.Person.Id); if (activityLoanHistory.Group != null) insert.AddParam("@group_id", activityLoanHistory.Group.Id); else insert.AddParam("@group_id", null); insert.AddParam("@economic_activity_id", activityLoanHistory.EconomicActivity.Id); insert.AddParam("@deleted", activityLoanHistory.Deleted); insert.ExecuteNonQuery(); } }
public MenuObject AddNewMenu(string name) { MenuObject newMenu = new MenuObject(); const string q = @"INSERT INTO [MenuItems]([component_name]) VALUES (@menu) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@menu", name.Trim()); newMenu.Id = int.Parse(c.ExecuteScalar().ToString()); newMenu.NotSavedInDBYet = false; newMenu.Name = name; } } return newMenu; }
public bool CustomizableFieldsExistFor(OCustomizableFieldEntities entity) { string sqlText = @"SELECT COUNT(*) AS [number] FROM dbo.AdvancedFields WHERE [entity_id] = @entity_id "; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand selectCmd = new OpenCbsCommand(sqlText, conn)) { selectCmd.AddParam("@entity_id", (int) Enum.Parse(typeof (OCustomizableFieldEntities), entity.ToString())); using (OpenCbsReader reader = selectCmd.ExecuteReader()) { if (reader == null || reader.Empty) return false; reader.Read(); if (reader.GetInt("number") > 0) return true; } } return false; }
/// <summary> /// Add Role in database /// </summary> /// <param name="pRole"></param> /// <returns>Role id</returns> public int AddRole(Role pRole) { const string q = @"INSERT INTO [Roles] ([deleted], [code], [description], [default_start_view]) VALUES(@deleted, @code, @description, @start_view) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) { using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@deleted", false); c.AddParam("@code", pRole.RoleName); c.AddParam("@description", pRole.Description); c.AddParam("@start_view", pRole.DefaultStartPage.ToString()); pRole.Id = int.Parse(c.ExecuteScalar().ToString()); SaveRoleMenu(pRole); SaveAllowedActionsForRole(pRole); } } return pRole.Id; }
public bool EconomicActivityLoanHistoryDeleted(int contractId, int personId, SqlTransaction sqlTransaction) { int id = 0; const string sqlText = @"SELECT contract_id, person_id, group_id, economic_activity_id, deleted FROM EconomicActivityLoanHistory WHERE contract_id = @contract_id AND person_id = @person_id AND deleted = 1"; using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, sqlTransaction.Connection, sqlTransaction)) { sqlCommand.AddParam("@contract_id", contractId); sqlCommand.AddParam("@person_id", personId); using (OpenCbsReader reader = sqlCommand.ExecuteReader()) { if (!reader.Empty) { reader.Read(); id = reader.GetInt("contract_id"); } } } return id != 0; }
private List<EconomicActivity> SelectChildren(int pParentId) { List<EconomicActivity> doaList = new List<EconomicActivity>(); const string sqlText = "SELECT id, name, deleted FROM EconomicActivities WHERE parent_id = @id AND deleted = 0"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, connection)) { sqlCommand.AddParam("@id", pParentId); using (OpenCbsReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { EconomicActivity domain = new EconomicActivity { Id = reader.GetInt("id"), Name = reader.GetString("name"), Deleted = reader.GetBool("deleted") }; doaList.Add(domain); } } } for (int i = 0; i < doaList.Count; i++) doaList[i].Childrens = SelectChildren(doaList[i].Id); return doaList; }
/// <summary> /// Update economic activity name and delete /// </summary> /// <param name="pEconomicActivity">EconomicActivity object</param> public void UpdateEconomicActivity(EconomicActivity pEconomicActivity) { const string sqlText = "UPDATE EconomicActivities SET name = @name,deleted = @wasDeleted WHERE id = @id"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand update = new OpenCbsCommand(sqlText, connection)) { update.AddParam("@id", pEconomicActivity.Id); update.AddParam("@name", pEconomicActivity.Name); update.AddParam("@wasDeleted", pEconomicActivity.Deleted); update.ExecuteNonQuery(); } }
public bool ThisActivityAlreadyExist(string pName, int pParentId) { int id = 0; const string sqlText = @"SELECT id, name, deleted FROM EconomicActivities WHERE parent_id = @id AND name = @name AND deleted = 0"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, connection)) { sqlCommand.AddParam("@name", pName); sqlCommand.AddParam("@id", pParentId); using (OpenCbsReader reader = sqlCommand.ExecuteReader()) { if (!reader.Empty) { reader.Read(); id = reader.GetInt("id"); } } } return id != 0; }