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