/// <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; }
/// <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; }
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(string pName, int pProvinceId) { const string q = "INSERT INTO [Districts] ([name],[province_id],[deleted]) VALUES( @name, @province,0) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", pName); c.AddParam("@province", pProvinceId); 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 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 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 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 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(); } } }
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; }
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; } }
/// <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 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 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; }
private void UpdateAllowedMenuItem(int pRoleId, int pMenuId, bool pAllowed) { const string q = @"UPDATE [AllowedRoleMenus] SET allowed = @allowed WHERE [menu_item_id] = @menuId AND role_id = @roleId"; 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 UpdateInstallmentComment(string pComment, int pContractId, int pNumber) { const string q = @"UPDATE Installments SET comment = @comment WHERE contract_id = @contractId AND number = @number"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { //primary key = contractId + number c.AddParam("@contractId", pContractId); c.AddParam("@number", pNumber); c.AddParam("@comment", pComment); c.ExecuteNonQuery(); } }
public FundingLine SelectFundingLineByName(string name) { FundingLine fundingLine = null; string q = @"SELECT FundingLines.[id], FundingLines.[name], FundingLines.[deleted], FundingLines.[amount], FundingLines.[begin_date], FundingLines.[end_date], FundingLines.[purpose], FundingLines.[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 FundingLines.[name]=@name AND [deleted]=0"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", name); using (OpenCbsReader r = c.ExecuteReader()) { if (r != null) { if (!r.Empty) { r.Read(); fundingLine = new FundingLine(); fundingLine.Id = r.GetInt("id"); fundingLine.Name = r.GetString("name"); fundingLine.Deleted = r.GetBool("deleted"); fundingLine.StartDate = r.GetDateTime("begin_date"); fundingLine.Purpose = r.GetString("purpose"); fundingLine.EndDate = r.GetDateTime("end_date"); fundingLine.Amount = r.GetMoney("amount"); 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); return(fundingLine); }
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 List <Teller> SelectAllOfUser(int userId) { var tellers = new List <Teller>(); const string q = @"SELECT id , name , description , account_id , deleted , branch_id , user_id , currency_id , amount_min , amount_max , deposit_amount_min , deposit_amount_max , withdrawal_amount_min , withdrawal_amount_max FROM dbo.Tellers WHERE user_id = @user_id AND deleted = 0"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@user_id", userId); using (OpenCbsReader r = c.ExecuteReader()) { if (r.Empty) { return(tellers); } while (r.Read()) { var teller = new Teller(); teller.Id = r.GetInt("id"); teller.Name = r.GetString("name"); teller.Description = r.GetString("description"); teller.Deleted = r.GetBool("deleted"); teller.Account = accountManager.Select(r.GetInt("account_id")); teller.Branch = branchManager.Select(r.GetInt("branch_id")); int uId = r.GetInt("user_id"); teller.User = uId == 0 ? new User { Id = 0 } : userManager.SelectUser(uId, false); teller.Currency = currencyManager.SelectCurrencyById(r.GetInt("currency_id")); teller.MinAmountTeller = r.GetMoney("amount_min"); teller.MaxAmountTeller = r.GetMoney("amount_max"); teller.MinAmountDeposit = r.GetMoney("deposit_amount_min"); teller.MaxAmountDeposit = r.GetMoney("deposit_amount_max"); teller.MinAmountWithdrawal = r.GetMoney("withdrawal_amount_min"); teller.MaxAmountWithdrawal = r.GetMoney("withdrawal_amount_max"); tellers.Add(teller); } } } return(tellers); }
private static void SetUser(OpenCbsCommand sqlCommand, User pUser) { sqlCommand.AddParam("@username", pUser.UserName); sqlCommand.AddParam("@userpass", pUser.Password); sqlCommand.AddParam("@roleCode", pUser.UserRole.ToString()); sqlCommand.AddParam("@firstname", pUser.FirstName); sqlCommand.AddParam("@lastname", pUser.LastName); sqlCommand.AddParam("@mail", pUser.Mail); sqlCommand.AddParam("@sex", pUser.Sex); sqlCommand.AddParam("@phone", pUser.Phone); }
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 void Update(Branch branch, SqlTransaction t) { const string q = @"UPDATE dbo.Branches SET name = @name , code = @code , description = @description , address = @address WHERE id = @id"; using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t)) { c.AddParam("@id", branch.Id); c.AddParam("@name", branch.Name); c.AddParam("@code", branch.Code); c.AddParam("@description", branch.Description); c.AddParam("@address", branch.Address); c.ExecuteNonQuery(); } }
public void DeleteInstallments(int pLoanId, SqlTransaction pSqlTransac) { const string q = @"DELETE FROM Installments WHERE contract_id = @contractId"; using (var c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { c.AddParam("@contractId", pLoanId); c.ExecuteNonQuery(); } }
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 OCurrency GetTellerBalance(Teller teller) { using (SqlConnection conn = GetConnection()) { using (var c = new OpenCbsCommand("GetTellerBalance", conn).AsStoredProcedure()) { c.AddParam("@teller_id", teller.Id); return(decimal.Parse(c.ExecuteScalar().ToString())); } } }
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 DeletePublicHoliday(DictionaryEntry entry) { const string sqlText = "DELETE FROM [PublicHolidays] WHERE [date] = @date"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@date", Convert.ToDateTime(entry.Key)); cmd.ExecuteNonQuery(); } }
public bool IsDistrictUsed(int districtId) { const string query = "SELECT TOP 1 district_id FROM Tiers WHERE district_id = @districtId OR secondary_district_id = @districtId"; using (var connection = GetConnection()) using (var cmd = new OpenCbsCommand(query, connection)) { cmd.AddParam("districtId", districtId); return(cmd.ExecuteScalar() != null); } }
public void DeleteCityById(int pCityId) { const string q = "UPDATE [City] SET [deleted]=1 WHERE id=@id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", pCityId); c.ExecuteNonQuery(); } }
public void DeleteDistrictById(int districtID) { const string q = "UPDATE [Districts] SET [deleted]=1 WHERE id=@id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", districtID); c.ExecuteNonQuery(); } }
public void DeleteUser(User pUser) { const string sqlText = "UPDATE [Users] SET deleted = 1 WHERE [id] = @userId"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@userId", pUser.Id); sqlCommand.ExecuteNonQuery(); } }
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 bool PorductExists(string productName) { const string sqlText = @"SELECT 1 FROM [CollateralProducts] WHERE [name] = @name"; using (var connection = GetConnection()) using (var cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("name", productName); return(cmd.ExecuteScalar() != null); } }
public void DeleteProvinceById(int pProvinceId) { const string q = "UPDATE [Provinces] SET [deleted]=1 WHERE id=@id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", pProvinceId); RefreshCache(); c.ExecuteNonQuery(); } }
public void DeleteCollateralProduct(int colProductId) { const string sqlText = @"UPDATE CollateralProducts SET deleted = 1 WHERE [id] = @id"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand command = new OpenCbsCommand(sqlText, connection)) { command.AddParam("@id", colProductId); command.ExecuteNonQuery(); } }
/// <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 CollateralProduct SelectCollateralProductByPropertyId(int propertyId) { int productId; const string sqlProductIdText = @"SELECT product_id FROM [CollateralProperties] WHERE id = @id "; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlProductIdText, connection)) { cmd.AddParam("@id", propertyId); using (OpenCbsReader reader = cmd.ExecuteReader()) { if (reader.Empty) { return(null); // nothing is coming... (c) } reader.Read(); productId = reader.GetInt("product_id"); } } const string sqlText = @"SELECT [name] ,[desc] ,[deleted] FROM CollateralProducts WHERE id = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand selectProduct = new OpenCbsCommand(sqlText, conn)) { selectProduct.AddParam("@id", productId); using (OpenCbsReader reader = selectProduct.ExecuteReader()) { if (reader.Empty) { return(null); } reader.Read(); CollateralProduct colProduct = new CollateralProduct { Id = productId, Name = reader.GetString("name"), Description = reader.GetString("desc"), Delete = reader.GetBool("deleted") }; return(colProduct); } } }
public void DeleteCollection(int fieldId) { string sqlListText = @"DELETE FROM [AdvancedFieldsCollections] WHERE [field_id] = @field_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand insertCmd = new OpenCbsCommand(sqlListText, conn)) { insertCmd.AddParam("@field_id", fieldId); insertCmd.ExecuteNonQuery(); } }
public void Delete(int?id) { const string q = @"UPDATE dbo.Tellers SET deleted = 1 WHERE id = @id"; using (SqlConnection conn = GetConnection()) using (var c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", id); c.ExecuteNonQuery(); } }
public void UpdateSentField(bool isSent) { string sql = @"UPDATE [dbo].[Questionnaire] SET [is_sent] = @isSent"; using (SqlConnection conn = GetConnection()) using (var cmd = new OpenCbsCommand(sql, conn)) { cmd.AddParam("@isSent", isSent); cmd.ExecuteNonQuery(); } }
/// <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; }
/// <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); }
public void UpdateInstallment(DateTime date, int id, int number) { // Update installement in database const string q = @"UPDATE Installments SET expected_date = @expectedDate 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", id); c.AddParam("@number", number); c.AddParam("@expectedDate", date); c.ExecuteNonQuery(); c.ResetParams(); c.CommandText = @"UPDATE dbo.Installments SET start_date = @start_date WHERE contract_id = @contractId AND number = @number"; c.AddParam("@contractId", id); c.AddParam("@number", number + 1); c.AddParam("@start_date", date); c.ExecuteNonQuery(); } }
public void SavePicture(byte[] picture, byte[] thumbnail, int person_id, string filename, int subId) { int pictureId; string q = string.Format(@"INSERT INTO [dbo].[Pictures] ([group], [id] ,[subid] ,[picture] ,[thumbnail] ,[name]) VALUES (@group ,@person_id ,@subid ,@picture ,@thumbnail ,@name) SELECT CONVERT(int, SCOPE_IDENTITY())"); using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection)) { c.AddParam("group", "SECOND_PICTURE"); c.AddParam("picture", picture); c.AddParam("subid", subId); c.AddParam("name", filename); c.AddParam("thumbnail", thumbnail); c.AddParam("person_id", person_id); pictureId = int.Parse(c.ExecuteScalar().ToString()); } q = string.Format( @"INSERT INTO PersonsPhotos ([person_id], [picture_id]) VALUES (@person_id, @picture_id)"); using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection)) { c.AddParam("person_id", person_id); c.AddParam("picture_id", pictureId); c.ExecuteNonQuery(); } }
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(); RefreshCache(); } }
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(); } }
/// <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 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; }
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 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 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; }
public EconomicActivity SelectEconomicActivity(string pName) { EconomicActivity doa; const string sqlText = "SELECT id, name, deleted FROM EconomicActivities WHERE name = @name"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand selectById = new OpenCbsCommand(sqlText, connection)) { selectById.AddParam("@name", pName); using (OpenCbsReader reader = selectById.ExecuteReader()) { doa = GetEconomicActivity(reader); } } doa.Childrens = SelectChildren(doa.Id); return doa; }
/// <summary> /// This methods allows us to find a economic activity. /// We use recursive method to find parent /// </summary> /// <param name="id">the id searched</param> /// <returns>DomainOfApplication object</returns> public EconomicActivity SelectEconomicActivity(int pId) { EconomicActivity doa; const string sqlText = @"SELECT [id], [name], [deleted] FROM EconomicActivities WHERE id = @id"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand selectById = new OpenCbsCommand(sqlText, connection)) { selectById.AddParam("@id", pId); using (OpenCbsReader reader = selectById.ExecuteReader()) { doa = GetEconomicActivity(reader); } } doa.Childrens = SelectChildren(doa.Id); return doa; }
public int AddGenericTiersIntoDatabase(string type) { int districtId = AddDistrictIntoDatabase().Id; OpenCbsCommand command = new OpenCbsCommand("INSERT INTO Tiers (creation_date,client_type_code,active,bad_client,district_id,city,loan_cycle) VALUES ('1/1/2007','G',1,0,@districtId,'Tress',1) SELECT SCOPE_IDENTITY()", _connection); command.AddParam("@districtId", districtId); int tiersId = int.Parse(command.ExecuteScalar().ToString()); OpenCbsCommand insert = null; if (type.Equals("group")) insert = new OpenCbsCommand("INSERT INTO Groups (id,name) VALUES (" + tiersId + ",'SCG')", _connection); else if (type.Equals("person")) insert = new OpenCbsCommand("INSERT INTO Persons (id,first_name,sex,identification_data,last_name,household_head) VALUES (" + tiersId + ",'Nicolas','M','123KH','BARON',1)", _connection); if (insert != null) insert.ExecuteNonQuery(); return tiersId; }
public PaymentMethod SelectPaymentMethodById(int paymentMethodId) { string q = @"SELECT pm.[id] ,[name] ,[description] ,[pending] , 0 AS account_id FROM [dbo].[PaymentMethods] pm WHERE pm.id = @id"; PaymentMethod pm = new PaymentMethod(); using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", paymentMethodId); using (OpenCbsReader r = c.ExecuteReader()) { if (r != null && !r.Empty) { r.Read(); pm = GetPaymentMethodFromReader(r); } } } return pm; }
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; }
public int AddGenericCreditContractIntoDatabase() { DeleteCreditContract(); int tiersId = AddGenericTiersIntoDatabase("group"); OpenCbsCommand insertProject = new OpenCbsCommand(@"INSERT INTO Projects([tiers_id], [status], [name], [code], [aim], [begin_date]) VALUES (" + tiersId + " , 2,'NOT SET','NOT SET','NOT SET','10/10/2005') SELECT SCOPE_IDENTITY()", _connection); int projectId = int.Parse(insertProject.ExecuteScalar().ToString()); OpenCbsCommand insertContract = new OpenCbsCommand("INSERT INTO [Contracts]([contract_code], [branch_code], [creation_date], [start_date], [close_date], [closed], [project_id], [activity_id],[preferred_first_installment_date]) VALUES " + "('KH/130','SU','10/10/2004','10/10/2005','10/10/2006',0," + projectId + ", 1,'11/10/2005') SELECT SCOPE_IDENTITY()", _connection); int contractId = int.Parse(insertContract.ExecuteScalar().ToString()); int packageId = AddGenericPackage(); int fundingline_id = AddGenericFundingLine(); string sqlText = string.Format( @"INSERT INTO [Credit] ( [id] ,[package_id] ,[amount] ,[interest_rate] ,[installment_type] ,[nb_of_installment] ,[non_repayment_penalties_based_on_initial_amount] ,[non_repayment_penalties_based_on_olb] ,[non_repayment_penalties_based_on_overdue_interest] ,[non_repayment_penalties_based_on_overdue_principal] ,[anticipated_total_repayment_penalties] ,[disbursed] ,[loanofficer_id] ,[fundingLine_id] ,[grace_period] ,[written_off] ,[rescheduled] ,[bad_loan]) VALUES(@id, @packageId, 1000, 2, 1, 2, 2 ,2 ,2 ,3 ,2 ,0 ,1 ,{0} ,6 ,0 ,0 ,0) ", fundingline_id); OpenCbsCommand insertCredit = new OpenCbsCommand(sqlText, _connection); insertCredit.AddParam("@id", contractId); insertCredit.AddParam("@packageId", packageId); insertCredit.ExecuteNonQuery(); OpenCbsCommand insertInstallment = new OpenCbsCommand(@"INSERT INTO [Installments]([expected_date],[interest_repayment],[capital_repayment],[contract_id], [number],[paid_interest],[paid_capital],[fees_unpaid]) VALUES (01/01/2007,100,200,@contractId,1,0,0,0) INSERT INTO [Installments]([expected_date],[interest_repayment],[capital_repayment],[contract_id], [number],[paid_interest],[paid_capital],[fees_unpaid]) VALUES (01/02/2007,100,200,@contractId,2,0,0,0)", _connection); insertInstallment.AddParam("@contractId", contractId); insertInstallment.ExecuteNonQuery(); return contractId; }