/// <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));
}
}
}