/// <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> /// 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 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 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 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); } }
private void SaveUsersRole(int pUserId, int pRoleId, SqlTransaction transaction) { const string sqlText = @"INSERT INTO [UserRole]([role_id], [user_id]) VALUES(@role_id, @user_id)"; using (var sqlCommand = new OpenCbsCommand(sqlText, transaction.Connection, transaction)) { sqlCommand.AddParam("@role_id", pRoleId); sqlCommand.AddParam("@user_id", pUserId); sqlCommand.ExecuteScalar(); } }
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 DeleteFundingLine(FundingLine fund) { string q = "UPDATE [FundingLines] SET [deleted] = @deleted WHERE [id] = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", fund.Id); c.AddParam("@deleted", true); c.ExecuteScalar(); } }
public int AddProvince(string pName) { const string q = "INSERT INTO [Provinces] ([name], [deleted]) VALUES (@name,0) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@name", pName); c.AddParam("@deleted", false); 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 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 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 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())); } }
private void _UpdateUsersRole(int pUserId, int pRoleId, SqlTransaction transaction) { const string sqlText = @"UPDATE [UserRole] SET [role_id] = @role_id WHERE [user_id] = @user_id"; using (var sqlCommand = new OpenCbsCommand(sqlText, transaction.Connection, transaction)) { sqlCommand.AddParam("@role_id", pRoleId); sqlCommand.AddParam("@user_id", pUserId); sqlCommand.ExecuteScalar(); } }
private void SaveUsersRole(int pUserId, int pRoleId) { const string sqlText = @"INSERT INTO [UserRole]([role_id], [user_id]) VALUES(@role_id, @user_id)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@role_id", pRoleId); sqlCommand.AddParam("@user_id", pUserId); sqlCommand.ExecuteScalar(); } }
public Project SelectProjectByCode(string code) { const string q = @"SELECT id FROM dbo.Projects WHERE code = @code"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@code", code); object val = c.ExecuteScalar(); return(null == val ? null : SelectProject(Convert.ToInt32(val))); } }
public bool IsCityUsed(int cityId) { const string query = "SELECT c.id FROM City c " + "INNER JOIN Tiers t " + " ON c.[name] = t.city OR c.[name] = t.secondary_city " + "WHERE c.id = @cityId"; using (var connection = GetConnection()) using (var cmd = new OpenCbsCommand(query, connection)) { cmd.AddParam("cityId", cityId); return(cmd.ExecuteScalar() != null); } }
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()); } }
private void _UpdateUsersRole(int pUserId, int pRoleId) { const string sqlText = @"UPDATE [UserRole] SET [role_id] = @role_id WHERE [user_id] = @user_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@role_id", pRoleId); sqlCommand.AddParam("@user_id", pUserId); sqlCommand.ExecuteScalar(); } }
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 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); var id = c.ExecuteScalar().ToString(); RefreshCache(); return(int.Parse(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 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 _AddFollowUp(FollowUp pUp, int pId, SqlTransaction pTransac) { const string q = @"INSERT INTO [FollowUp] ([project_id],[year],[CA],[Jobs1],[Jobs2],[PersonalSituation],[activity] ,[comment]) VALUES(@projectId,@year,@CA,@jobs1,@jobs2,@personalSituation,@activity,@comment) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, pTransac.Connection, pTransac)) { c.AddParam("@projectId", pId); 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); pUp.Id = Convert.ToInt32(c.ExecuteScalar()); } }
private void AddCustomizableField(CustomizableField field) { string sqlText = @"INSERT INTO [AdvancedFields] ([entity_id] , [type_id] , [name] , [desc] , [is_mandatory] , [is_unique]) VALUES (@entity_id , @type_id , @f_name , @f_desc , @is_mandatory , @is_unique) SELECT CONVERT(int, SCOPE_IDENTITY())"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand insertCmd = new OpenCbsCommand(sqlText, conn)) { insertCmd.AddParam("@entity_id", (int)field.Entity); insertCmd.AddParam("@type_id", (int)field.Type); insertCmd.AddParam("@f_name", field.Name); insertCmd.AddParam("@f_desc", field.Description); insertCmd.AddParam("@is_mandatory", field.IsMandatory); insertCmd.AddParam("@is_unique", field.IsUnique); field.Id = int.Parse(insertCmd.ExecuteScalar().ToString()); if (field.Type == OCustomizableFieldTypes.Collection) { foreach (string colItem in field.Collection) { if (colItem.Trim().Length > 0) { AddCollectionItem(field.Id, colItem); } } } } }
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 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; }
/// <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())); } }
/// <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]) VALUES(@deleted, @code, @description) 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); pRole.Id = int.Parse(c.ExecuteScalar().ToString()); SaveRoleMenu(pRole); SaveAllowedActionsForRole(pRole); } } return(pRole.Id); }
public int AddCollateralProperty(int collateralProductId, CollateralProperty collateralProperty) { string sqlText = @"INSERT INTO [CollateralProperties] ( [product_id] ,[type_id] ,[name] ,[desc] ) VALUES ( @product_id ,@type_id ,@prop_name ,@prop_desc ) SELECT CONVERT(int, SCOPE_IDENTITY())"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@product_id", collateralProductId); cmd.AddParam("@prop_name", collateralProperty.Name); cmd.AddParam("@prop_desc", collateralProperty.Description); cmd.AddParam("@type_id", (int)Enum.Parse(typeof(OCollateralPropertyTypes), collateralProperty.Type.ToString())); collateralProperty.Id = int.Parse(cmd.ExecuteScalar().ToString()); if (collateralProperty.Type == OCollateralPropertyTypes.Collection) { foreach (string colItem in collateralProperty.Collection) { AddCollateralPropertyCollectionItem(collateralProperty.Id, colItem); } } } return(collateralProperty.Id); }
public int NumberOfLinksToInstallmentType(InstallmentType installmentType) { const string q = @"SELECT COUNT(installment_type) FROM ( SELECT installment_type FROM Credit UNION ALL SELECT installment_type FROM dbo.Packages UNION ALL SELECT installment_types_id AS [installment_type] FROM dbo.TermDepositProducts UNION ALL SELECT agio_fees_freq AS [installment_type] FROM dbo.SavingBookProducts UNION ALL SELECT management_fees_freq AS [installment_type] FROM dbo.SavingBookProducts ) AS T WHERE T.installment_type = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", installmentType.Id); return(int.Parse(c.ExecuteScalar().ToString())); } }
/// <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 OCurrency GetLatestTellerOpenBalance(Teller teller) { const string sql = @"SELECT TOP 1 [amount] FROM [TellerEvents] WHERE [teller_id] = @teller_id AND [event_code] = 'ODAE' ORDER BY id DESC"; using (SqlConnection connection = GetConnection()) { using (OpenCbsCommand cmd = new OpenCbsCommand(sql, connection)) { cmd.AddParam("@teller_id", teller.Id); object balance = cmd.ExecuteScalar(); if (balance == null) { return(0); } return(decimal.Parse(balance.ToString())); } } }
public int Add(Project pProject, int pTiersId, SqlTransaction pSqlTransac) { const string q = @"INSERT INTO Projects([tiers_id],[status],[name],[code],[aim],[begin_date], [abilities],[experience],[market],[concurrence],[purpose],[corporate_name],[corporate_juridicStatus] ,[corporate_FiscalStatus],[corporate_siret],[corporate_CA],[corporate_nbOfJobs],[corporate_financialPlanType] ,[corporateFinancialPlanAmount],[corporate_financialPlanTotalAmount],[address],[city],[zipCode],[district_id] ,[home_phone],[personalPhone],[Email],[hometype],[corporate_registre]) VALUES(@tiersId,@status,@name,@code,@aim,@beginDate,@abilities,@experience,@market,@concurrence,@purpose, @corporateName,@corporateJuridicStatus,@corporateFiscalStatus,@corporateSiret,@corporateCA,@corporateNbOfJobs, @corporateFinancialPlanType, @corporateFinancialPlanAmount, @corporateFinancialPlanTotalAmount, @address,@city, @zipCode, @districtId, @homePhone, @personalPhone,@Email,@hometype,@corporateRegistre) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { c.AddParam("@tiersId", pTiersId); c.AddParam("@status", (int) pProject.ProjectStatus); c.AddParam("@name", pProject.Name); c.AddParam("@code", pProject.Code); c.AddParam("@aim", pProject.Aim); c.AddParam("@beginDate", pProject.BeginDate); c.AddParam("@abilities", pProject.Abilities); c.AddParam("@experience", pProject.Experience); c.AddParam("@market", pProject.Market); c.AddParam("@concurrence", pProject.Concurrence); c.AddParam("@purpose", pProject.Purpose); c.AddParam("@corporateName", pProject.CorporateName); c.AddParam("@corporateJuridicStatus", pProject.CorporateJuridicStatus); c.AddParam("@corporateFiscalStatus", pProject.CorporateFiscalStatus); c.AddParam("@corporateRegistre", pProject.CorporateRegistre); c.AddParam("@corporateSiret", pProject.CorporateSIRET); c.AddParam("@corporateCA", pProject.CorporateCA); c.AddParam("@corporateNbOfJobs", pProject.CorporateNbOfJobs); c.AddParam("@corporateFinancialPlanType", pProject.CorporateFinancialPlanType); c.AddParam("@corporateFinancialPlanAmount", pProject.CorporateFinancialPlanAmount); c.AddParam("@corporateFinancialPlanTotalAmount", pProject.CorporateFinancialPlanTotalAmount); c.AddParam("@address", pProject.Address); c.AddParam("@city", pProject.City); c.AddParam("@zipCode", pProject.ZipCode); if (pProject.District != null) c.AddParam("@districtId", pProject.District.Id); else c.AddParam("@districtId", null); c.AddParam("@homePhone", pProject.HomePhone); c.AddParam("@personalPhone", pProject.PersonalPhone); c.AddParam("@Email", pProject.Email); c.AddParam("@hometype", pProject.HomeType); pProject.Id = Convert.ToInt32(c.ExecuteScalar()); } foreach (FollowUp followUp in pProject.FollowUps) { _AddFollowUp(followUp, pProject.Id, pSqlTransac); } foreach (Loan credit in pProject.Credits) { _creditManager.Add(credit, pProject.Id, pSqlTransac); } return pProject.Id; }
private void _AddFollowUp(FollowUp pUp, int pId, SqlTransaction pTransac) { const string q = @"INSERT INTO [FollowUp] ([project_id],[year],[CA],[Jobs1],[Jobs2],[PersonalSituation],[activity] ,[comment]) VALUES(@projectId,@year,@CA,@jobs1,@jobs2,@personalSituation,@activity,@comment) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, pTransac.Connection, pTransac)) { c.AddParam("@projectId", pId); 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); pUp.Id = Convert.ToInt32(c.ExecuteScalar()); } }
public Project SelectProjectByCode(string code) { const string q = @"SELECT id FROM dbo.Projects WHERE code = @code"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@code", code); object val = c.ExecuteScalar(); return null == val ? null : SelectProject(Convert.ToInt32(val)); } }
public int AddFundingLine(FundingLine pFund, SqlTransaction sqlTransac) { string q = @"INSERT INTO [FundingLines]([name],[begin_date],[end_date],[amount],[purpose], [deleted], [currency_id]) VALUES(@name,@beginDate,@endDate,@amount,@purpose,@deleted, @currency_id ) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac)) { c.AddParam("@name", pFund.Name); c.AddParam("@beginDate", pFund.StartDate); c.AddParam("@endDate", pFund.EndDate); c.AddParam("@amount", pFund.Amount); c.AddParam("@purpose", pFund.Purpose); c.AddParam("@deleted", pFund.Deleted); c.AddParam("@currency_id", pFund.Currency.Id); pFund.Id = int.Parse(c.ExecuteScalar().ToString()); foreach (FundingLineEvent eventFL in pFund.Events) { if (eventFL.Id == 0) _eventFundingLineManager.AddFundingLineEvent(eventFL, sqlTransac); //new event else _eventFundingLineManager.UpdateFundingLineEvent(eventFL, sqlTransac); // delete event } } SetLazyLoader(pFund); return pFund.Id; }
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 int AddUser(User pUser) { string sqlText = @"INSERT INTO [Users] ( [deleted], [role_code], [user_name], [user_pass], [user_sq], [user_sqa], [first_name], [last_name], [mail], [sex], [phone], [last_updated], [timed_out], [is_expired], [is_reset], [login_attempt], [frapid_login_id] ) VALUES( @deleted, @roleCode, @username, @userpass, @usersq, @usersqa, @firstname, @lastname, @mail, @sex, @phone, @now, @timedout, @isexpired, @isreset, @loginattempt, @frapidloginid ) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@deleted", false); /*sqlCommand.AddParam("@username", pUser.UserName); * sqlCommand.AddParam("@userpass", pUser.Password); * sqlCommand.AddParam("@usersq", pUser.Secret.Question); * sqlCommand.AddParam("@usersqa", Hasher.Get(pUser.Secret.Answer)); * 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); * sqlCommand.AddParam("@timedout", pUser.TimedOut); * sqlCommand.AddParam("@isexpired", pUser.IsExpired); * sqlCommand.AddParam("@isreset", pUser.IsReset); * sqlCommand.AddParam("@loginattempt", pUser.LoginAttempt); * sqlCommand.AddParam("@frapidloginid", pUser.FrapidLoginId); */ SetUser(sqlCommand, pUser, true); pUser.Id = int.Parse(sqlCommand.ExecuteScalar().ToString()); SaveUsersRole(pUser.Id, pUser.UserRole.Id); } return(pUser.Id); }
public int AddGenericFundingLine() { OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO FundingLines (name,amount, purpose, deleted, currency_id, begin_date, end_date) VALUES ('AFD130',0, 'Not Set', 0, " + AddGenericCurrency() + ", '20080101', '20120101') SELECT SCOPE_IDENTITY()", _connection); return int.Parse(insert.ExecuteScalar().ToString()); }
public int 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; }
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 OCurrency GetLatestTellerOpenBalance(Teller teller) { const string sql = @"SELECT TOP 1 [amount] FROM [TellerEvents] WHERE [teller_id] = @teller_id AND [event_code] = 'ODAE' ORDER BY id DESC"; using (SqlConnection connection = GetConnection()) { using (OpenCbsCommand cmd = new OpenCbsCommand(sql, connection)) { cmd.AddParam("@teller_id", teller.Id); object balance = cmd.ExecuteScalar(); if (balance == null) return 0; return decimal.Parse(balance.ToString()); } } }
public int AddEconomicActivity(EconomicActivity loanPurpose, bool isLoanPurpose) { string sqlText = ""; if (isLoanPurpose) { sqlText = @"INSERT INTO LoanPurpose ([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", loanPurpose.Name); insert.AddParam("@deleted", loanPurpose.Deleted); if (loanPurpose.Parent != null) insert.AddParam("@parentId", loanPurpose.Parent.Id); else insert.AddParam("@parentId", null); return int.Parse(insert.ExecuteScalar().ToString()); } }
public int Add(Project pProject, int pTiersId, SqlTransaction pSqlTransac) { const string q = @"INSERT INTO Projects([tiers_id],[status],[name],[code],[aim],[begin_date], [abilities],[experience],[market],[concurrence],[purpose],[corporate_name],[corporate_juridicStatus] ,[corporate_FiscalStatus],[corporate_siret],[corporate_CA],[corporate_nbOfJobs],[corporate_financialPlanType] ,[corporateFinancialPlanAmount],[corporate_financialPlanTotalAmount],[address],[city],[zipCode],[district_id] ,[home_phone],[personalPhone],[Email],[hometype],[corporate_registre]) VALUES(@tiersId,@status,@name,@code,@aim,@beginDate,@abilities,@experience,@market,@concurrence,@purpose, @corporateName,@corporateJuridicStatus,@corporateFiscalStatus,@corporateSiret,@corporateCA,@corporateNbOfJobs, @corporateFinancialPlanType, @corporateFinancialPlanAmount, @corporateFinancialPlanTotalAmount, @address,@city, @zipCode, @districtId, @homePhone, @personalPhone,@Email,@hometype,@corporateRegistre) SELECT SCOPE_IDENTITY()"; using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac)) { c.AddParam("@tiersId", pTiersId); c.AddParam("@status", (int)pProject.ProjectStatus); c.AddParam("@name", pProject.Name); c.AddParam("@code", pProject.Code); c.AddParam("@aim", pProject.Aim); c.AddParam("@beginDate", pProject.BeginDate); c.AddParam("@abilities", pProject.Abilities); c.AddParam("@experience", pProject.Experience); c.AddParam("@market", pProject.Market); c.AddParam("@concurrence", pProject.Concurrence); c.AddParam("@purpose", pProject.Purpose); c.AddParam("@corporateName", pProject.CorporateName); c.AddParam("@corporateJuridicStatus", pProject.CorporateJuridicStatus); c.AddParam("@corporateFiscalStatus", pProject.CorporateFiscalStatus); c.AddParam("@corporateRegistre", pProject.CorporateRegistre); c.AddParam("@corporateSiret", pProject.CorporateSIRET); c.AddParam("@corporateCA", pProject.CorporateCA); c.AddParam("@corporateNbOfJobs", pProject.CorporateNbOfJobs); c.AddParam("@corporateFinancialPlanType", pProject.CorporateFinancialPlanType); c.AddParam("@corporateFinancialPlanAmount", pProject.CorporateFinancialPlanAmount); c.AddParam("@corporateFinancialPlanTotalAmount", pProject.CorporateFinancialPlanTotalAmount); c.AddParam("@address", pProject.Address); c.AddParam("@city", pProject.City); c.AddParam("@zipCode", pProject.ZipCode); if (pProject.District != null) { c.AddParam("@districtId", pProject.District.Id); } else { c.AddParam("@districtId", null); } c.AddParam("@homePhone", pProject.HomePhone); c.AddParam("@personalPhone", pProject.PersonalPhone); c.AddParam("@Email", pProject.Email); c.AddParam("@hometype", pProject.HomeType); pProject.Id = Convert.ToInt32(c.ExecuteScalar()); } foreach (FollowUp followUp in pProject.FollowUps) { _AddFollowUp(followUp, pProject.Id, pSqlTransac); } foreach (Loan credit in pProject.Credits) { _creditManager.Add(credit, pProject.Id, pSqlTransac); } return(pProject.Id); }
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 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 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 int NumberOfLinksToInstallmentType(InstallmentType installmentType) { const string q = @"SELECT COUNT(installment_type) FROM ( SELECT installment_type FROM Credit UNION ALL SELECT installment_type FROM dbo.Packages UNION ALL SELECT installment_types_id AS [installment_type] FROM dbo.TermDepositProducts UNION ALL SELECT agio_fees_freq AS [installment_type] FROM dbo.SavingBookProducts UNION ALL SELECT management_fees_freq AS [installment_type] FROM dbo.SavingBookProducts ) AS T WHERE T.installment_type = @id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@id", installmentType.Id); return int.Parse(c.ExecuteScalar().ToString()); } }
public void AddGeneralParameterIntoDatabase() { OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [GeneralParameters]([key], [value]) VALUES('ID_PATTERN', '/d/d/w/w') SELECT SCOPE_IDENTITY()", _connection); insert.ExecuteScalar(); }
public int AddCollateralProperty(int collateralProductId, CollateralProperty collateralProperty) { string sqlText = @"INSERT INTO [CollateralProperties] ( [product_id] ,[type_id] ,[name] ,[desc] ) VALUES ( @product_id ,@type_id ,@prop_name ,@prop_desc ) SELECT CONVERT(int, SCOPE_IDENTITY())"; using (SqlConnection connection = GetConnection()) using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection)) { cmd.AddParam("@product_id", collateralProductId); cmd.AddParam("@prop_name", collateralProperty.Name); cmd.AddParam("@prop_desc", collateralProperty.Description); cmd.AddParam("@type_id", (int)Enum.Parse(typeof(OCollateralPropertyTypes), collateralProperty.Type.ToString())); collateralProperty.Id = int.Parse(cmd.ExecuteScalar().ToString()); if (collateralProperty.Type == OCollateralPropertyTypes.Collection) foreach (string colItem in collateralProperty.Collection) AddCollateralPropertyCollectionItem(collateralProperty.Id, colItem); } return collateralProperty.Id; }
public int AddGenericCurrency() { OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO Currencies (name, code, is_pivot, is_swapped) VALUES ('USD', 'USD', 1, 0) SELECT SCOPE_IDENTITY()", _connection); return int.Parse(insert.ExecuteScalar().ToString()); }
private void SaveUsersRole(int pUserId, int pRoleId) { const string sqlText = @"INSERT INTO [UserRole]([role_id], [user_id]) VALUES(@role_id, @user_id)"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@role_id", pRoleId); sqlCommand.AddParam("@user_id", pUserId); sqlCommand.ExecuteScalar(); } }
public int AddGenericPackage() { OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO Packages (currency_id, deleted,name,client_type,installment_type,loan_type,charge_interest_within_grace_period,keep_expected_installment,anticipated_total_repayment_base) VALUES (" + AddGenericCurrency() + ", 0,'Package','G',1,1,0,0,1) SELECT SCOPE_IDENTITY()", _connection); return int.Parse(insert.ExecuteScalar().ToString()); }
private void _UpdateUsersRole(int pUserId, int pRoleId) { const string sqlText = @"UPDATE [UserRole] SET [role_id] = @role_id WHERE [user_id] = @user_id"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn)) { sqlCommand.AddParam("@role_id", pRoleId); sqlCommand.AddParam("@user_id", pUserId); sqlCommand.ExecuteScalar(); } }
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 void SaveCustomizableFieldValues(List <CustomizableFieldValue> fieldValues, int linkId, char linkType) { int groupId; string sqlText = @"SELECT id FROM dbo.AdvancedFieldsLinkEntities WHERE link_id = @link_id AND link_type = @link_type "; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand selectCmd = new OpenCbsCommand(sqlText, conn)) { selectCmd.AddParam("@link_id", linkId); selectCmd.AddParam("@link_type", linkType); using (OpenCbsReader reader = selectCmd.ExecuteReader()) { if (reader.Empty) { groupId = 0; } else { reader.Read(); groupId = reader.GetInt("id"); } } } if (groupId == 0) // add { sqlText = @" INSERT INTO [AdvancedFieldsLinkEntities] ([link_id] ,[link_type]) VALUES (@link_id, @link_type) SELECT CONVERT(int, SCOPE_IDENTITY())"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand insertCmd = new OpenCbsCommand(sqlText, conn)) { insertCmd.AddParam("@link_id", linkId); insertCmd.AddParam("@link_type", linkType); groupId = int.Parse(insertCmd.ExecuteScalar().ToString()); } foreach (CustomizableFieldValue fieldValue in fieldValues) { SaveCustomizableFieldValue(fieldValue, groupId, true); } } else // update { foreach (CustomizableFieldValue fieldValue in fieldValues) { int fieldId; string sqlTextSel = @" SELECT id FROM dbo.AdvancedFieldsValues WHERE entity_field_id = @entity_field_id AND field_id = @field_id "; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand selCmd = new OpenCbsCommand(sqlTextSel, conn)) { selCmd.AddParam("@entity_field_id", groupId); selCmd.AddParam("@field_id", fieldValue.Field.Id); using (OpenCbsReader reader = selCmd.ExecuteReader()) { if (reader == null || reader.Empty) { fieldId = 0; } else { reader.Read(); fieldId = reader.GetInt("id"); } } } SaveCustomizableFieldValue(fieldValue, groupId, (fieldId == 0)); } } }