public Dictionary <int, int> SelectUserToRole()
{
const string q = @"SELECT u.id AS user_id, r.id AS role_id
FROM dbo.Users AS u
LEFT JOIN dbo.Roles AS r ON r.code = u.role_code";
Dictionary <int, int> userToRole = new Dictionary <int, int>();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(userToRole);
}
while (r.Read())
{
int userId = r.GetInt("user_id");
int roleId = r.GetInt("role_id");
userToRole.Add(userId, roleId);
}
}
}
}
return(userToRole);
}
public List <City> SelectCityByDistrictId(int pDistrictId)
{
List <City> cities = new List <City>();
const string q = "SELECT name, id FROM City WHERE district_id = @id and deleted = 0 ORDER BY name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pDistrictId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
while (r.Read())
{
City city = new City
{
Name = r.GetString("name"),
Id = r.GetInt("id"),
DistrictId = pDistrictId
};
cities.Add(city);
}
}
}
}
return(cities);
}
public Dictionary <int, List <int> > SelectBranchRel()
{
const string q = @"SELECT user_id, branch_id
FROM dbo.UsersBranches
ORDER BY user_id";
Dictionary <int, List <int> > retval = new Dictionary <int, List <int> >();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(retval);
}
while (r.Read())
{
int userId = r.GetInt("user_id");
if (!retval.ContainsKey(userId))
{
retval.Add(userId, new List <int>());
}
retval[userId].Add(r.GetInt("branch_id"));
}
}
return(retval);
}
public List <User> GetSubordinate(int idUser)
{
var listUsers = new List <User>();
using (var conn = GetConnection())
{
using (var coman = new OpenCbsCommand {
Connection = conn
})
{
coman.CommandText = "select * from dbo.GetSubordinates(@id)";
coman.AddParam("@id", idUser);
var reader = coman.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
listUsers.Add(new User
{
Id = reader.GetInt("id"),
FirstName = reader.GetString("first_name"),
LastName = reader.GetString("last_name"),
IsDeleted = reader.GetBool("deleted"),
UserName = reader.GetString("user_name"),
Password = reader.GetString("user_pass"),
Mail = reader.GetString("mail"),
Sex = reader.GetChar("sex"),
HasContract = reader.GetInt("num_contracts") > 0
});
}
}
}
return(listUsers);
}
/// <summary>
/// This methods allows us to find all domains of application
/// </summary>
/// <returns>hierarchic collection of DomainOfApplication
/// </returns>
public List <EconomicActivity> SelectAllEconomicActivities()
{
List <EconomicActivity> doaList = new List <EconomicActivity>();
const string sqlText = "SELECT id FROM EconomicActivities WHERE parent_id IS NULL AND deleted = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectAll = new OpenCbsCommand(sqlText, connection))
{
using (OpenCbsReader reader = selectAll.ExecuteReader())
{
while (reader.Read())
{
EconomicActivity domain = new EconomicActivity
{
Id = reader.GetInt("id")
};
doaList.Add(domain);
}
}
}
for (int i = 0; i < doaList.Count; i++)
{
doaList[i] = SelectEconomicActivity(doaList[i].Id);
}
return(doaList);
}
public Dictionary <int, List <int> > SelectSubordinateRel()
{
const string q = @"SELECT user_id, subordinate_id
FROM dbo.UsersSubordinates
ORDER BY user_id";
Dictionary <int, List <int> > retval = new Dictionary <int, List <int> >();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(retval);
}
int currentId = 0;
while (r.Read())
{
int userId = r.GetInt("user_id");
if (currentId != userId)
{
currentId = userId;
retval.Add(currentId, new List <int>());
}
retval[currentId].Add(r.GetInt("subordinate_id"));
}
}
return(retval);
}
/// <summary>
/// Returns requested picture.
/// </summary>
/// <param name="pGroup">Picture group</param>
/// <param name="pId">Picture Id</param>
/// <param name="pSubID">Picture sub Id</param>
/// <param name="pThumbnail">Do you want the thumbnail or the actual picture?</param>
/// <returns>Found picture informations</returns>
public PictureInfo GetPicture(string pGroup, int pId, int pSubID, bool pThumbnail)
{
string sql = pThumbnail
? "SELECT thumbnail,name FROM Pictures WHERE [group]=@group AND id=@id AND subid=@subid"
: "SELECT picture,name FROM Pictures WHERE [group]=@group AND id=@id AND subid=@subid";
using (OpenCbsCommand c = new OpenCbsCommand(sql, AttachmentsConnection))
{
c.AddParam("@group", pGroup);
c.AddParam("@id", pId);
c.AddParam("@subid", pSubID);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(null);
}
r.Read();
PictureInfo pi = new PictureInfo
{
Binary = r.GetBytes(0),
Name = r.GetString(1),
Id = pId,
SubId = pSubID,
Group = pGroup
};
return(pi);
}
}
}
public bool ThisActivityAlreadyExist(string pName, int pParentId, bool isLoanPurpose)
{
int id = 0;
if (isLoanPurpose)
{
const string sqlText = @"SELECT id, name, deleted FROM LoanPurpose 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");
}
}
}
}
else
{
ThisActivityAlreadyExist(pName, pParentId);
}
return(id != 0);
}
public bool IsThisActionAllowedForThisRole(int pRoleId, ActionItemObject pAction)
{
string q = @"SELECT allowed FROM AllowedRoleActions
WHERE action_id = @actionId AND role_id = @roleId";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@roleId", pRoleId);
c.AddParam("@actionId", pAction.Id);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
return(r.GetBool("allowed"));
}
}
}
}
}
return(true);
}
public List <Province> SelectAllProvinces()
{
List <Province> provinces = new List <Province>();
const string q = "SELECT id,name FROM Provinces ORDER BY name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
while (r.Read())
{
Province province = new Province
{
Id = r.GetInt("id"),
Name = r.GetString("name")
};
provinces.Add(province);
}
}
}
return(provinces);
}
public int SelectUserForThisRole(string pRoleName)
{
string q = @"SELECT TOP 1 [user_id]
FROM UserRole
INNER JOIN Roles ON UserRole.role_id = Roles.id
INNER JOIN Users ON Users.id = UserRole.[user_id]
WHERE Roles.code = @roleCode AND Users.deleted = 0";
int foundId = 0;
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@roleCode", pRoleName);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
foundId = r.GetInt("user_id");
}
}
}
return(foundId);
}
}
}
/// <summary>
/// Select a role by its name
/// </summary>
/// <param name="pRoleName"></param>
/// <param name="pIncludeDeleted"></param>
/// <returns>selected role or null otherwise</returns>
public Role SelectRole(string pRoleName, bool pIncludeDeleted)
{
string q = @"SELECT [id], [code], [deleted], [description], [role_of_loan], [role_of_saving], [role_of_teller]
FROM [Roles] WHERE [code] = @name ";
q += pIncludeDeleted ? "" : "AND [deleted] = 0";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", pRoleName);
Role role;
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(null);
}
r.Read();
role = GetRole(r);
}
role.SetMenuItems(GetAllowedMenuList(role.Id));
role.SetActionItems(GetAllowedActionList(role.Id));
return(role);
}
}
}
/// <summary>
/// Select a Role by its database id with an Sqltransaction contexte
/// </summary>
/// <param name="pRoleId"></param>
/// <param name="pIncludeDeletedRole"></param>
/// <param name="pSqlTransac"></param>
/// <returns>selected Role or null otherwise</returns>
public Role SelectRole(int pRoleId, bool pIncludeDeletedRole, SqlTransaction pSqlTransac)
{
string q = @"SELECT [Roles].[id], [code], [deleted], [description], [role_of_loan], [role_of_saving], [role_of_teller]
FROM [Roles] WHERE [id] = @id ";
if (!pIncludeDeletedRole)
{
q += " AND [deleted] = 0";
}
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pRoleId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
Role role = GetRole(r);
return(role);
}
}
}
return(null);
}
}
public Branch SelectBranchByName(string name)
{
string query = @"SELECT id
, name
, deleted
, code
, address
, description
FROM dbo.Branches
WHERE name LIKE '%{0}%'";
query = string.Format(query, name);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(query, conn))
using (OpenCbsReader r = cmd.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
if (!r.Read())
{
return(null);
}
return(new Branch
{
Id = r.GetInt("id"),
Code = r.GetString("code"),
Name = r.GetString("name"),
Deleted = r.GetBool("deleted"),
Description = r.GetString("description")
});
}
}
public string GetBranchCodeByClientId(int clientId)
{
const string q = @"SELECT Branches.code FROM Tiers
INNER JOIN Branches ON Branches.id = Tiers.branch_id
WHERE Tiers.id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", clientId);
string code = string.Empty;
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
if (r.Read())
{
code = r.GetString("code");
}
}
return(code);
}
}
public District SelectDistrictByName(string name)
{
District district = null;
const string q = "SELECT Districts.id, Districts.name, Districts.province_id, " +
"Provinces.id AS province_id, Provinces.name AS province_name " +
"FROM Districts INNER JOIN " +
"Provinces ON Districts.province_id = Provinces.id " +
"WHERE Districts.name= @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
district = new District();
district.Province = new Province();
district.Id = r.GetInt("id");
district.Name = r.GetString("name");
district.Province.Id = r.GetInt("province_id");
district.Province.Name = r.GetString("province_name");
}
}
}
}
return(district);
}
public List <District> GetDistricts()
{
List <Province> provinces = GetProvinces();
List <District> districts = new List <District>();
const string q = "SELECT [id], [name], [province_id] FROM [Districts] WHERE [deleted]=0 ORDER BY name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader reader = c.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
District district = new District();
district.Id = reader.GetInt("id");
district.Name = reader.GetString("name");
int province_id = reader.GetInt("province_id");
foreach (Province p in provinces)
{
if (p.Id == province_id)
{
district.Province = p;
}
}
districts.Add(district);
}
}
}
return(districts);
}
public List <District> SelectDistrictsByProvinceId(int pProvinceId)
{
List <District> districts = new List <District>();
const string q = "SELECT Districts.id, Districts.name, Districts.province_id, " +
"Provinces.id AS province_id, Provinces.name AS province_name " +
"FROM Districts INNER JOIN " +
"Provinces ON Districts.province_id = Provinces.id " +
"WHERE Provinces.id= @id AND Districts.deleted = 0 ORDER BY Districts.name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pProvinceId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
while (r.Read())
{
District district = new District();
district.Province = new Province();
district.Id = r.GetInt("id");
district.Name = r.GetString("name");
district.Province.Id = r.GetInt("province_id");
district.Province.Name = r.GetString("province_name");
districts.Add(district);
}
}
}
}
return(districts);
}
private List <EconomicActivity> SelectLPChildren(int pParentId)
{
List <EconomicActivity> doaList = new List <EconomicActivity>();
const string sqlText = "SELECT id, name, deleted FROM LoanPurpose 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 = SelectLPChildren(doaList[i].Id);
}
return(doaList);
}
public int CountActiveLoans(int branchId)
{
const string sqlText = @";with lode(id, event_type) as
(
select
distinct contract_id
, event_type
from dbo.ContractEvents ce
where event_type = 'LODE' and is_deleted = 0
)
, loce(id, event_type) as
(
select
distinct contract_id
, event_type
from dbo.ContractEvents ce
where event_type in('LOCE','WROE') and is_deleted = 0
)
, raw_loans (id, lode, loce, project_id,contract_code) as
(
select
c.id id
, lode.event_type lode
, loce.event_type loce
, c.project_id project_id
, c.contract_code
from lode
inner join Contracts c on lode.id = c.id
left join loce on loce.id = c.id
)
select count(*) count from raw_loans rl
left join Projects pr on pr.id = rl.project_id
left join Tiers t on t.id = pr.tiers_id
where loce is null and t.branch_id = @branchId";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@branchId", branchId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(-1);
}
reader.Read();
var result = reader.GetInt("count");
return(result);
}
}
}
}
public FundingLine SelectFundingLineByNameAndPurpose(FundingLine lookupFundingLine, SqlTransaction sqlTransac,
bool includeAll)
{
FundingLine newFL = new FundingLine();
string q =
@"SELECT FundingLines.[id],
[deleted],
[currency_id],
Currencies.[name] as currency_name,
Currencies.[code] as currency_code,
Currencies.[is_pivot] as currency_pivot,
Currencies.[is_swapped] as currency_is_swapped
FROM [FundingLines]
LEFT JOIN Currencies ON FundingLines.currency_id = Currencies.id
WHERE [purpose] = @purpose AND
FundingLines.[name] = @name";
if (!includeAll)
{
q += "and [deleted]=@deleted";
}
using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac))
{
c.AddParam("@purpose", lookupFundingLine.Purpose);
c.AddParam("@name", lookupFundingLine.Name);
if (!includeAll)
{
c.AddParam("@deleted", lookupFundingLine.Deleted);
}
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
newFL.Id = r.GetInt("id");
newFL.Deleted = r.GetBool("deleted");
newFL.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(newFL);
return(newFL);
}
public List <Teller> SelectAllOfUser(int userId)
{
var tellers = new List <Teller>();
const string q = @"SELECT id
, name
, [desc]
, account_id
, deleted
, branch_id
, user_id
, currency_id
, amount_min
, amount_max
, deposit_amount_min
, deposit_amount_max
, withdrawal_amount_min
, withdrawal_amount_max
FROM dbo.Tellers
WHERE user_id = @user_id AND deleted = 0";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@user_id", userId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(tellers);
}
while (r.Read())
{
var teller = new Teller();
teller.Id = r.GetInt("id");
teller.Name = r.GetString("name");
teller.Description = r.GetString("desc");
teller.Deleted = r.GetBool("deleted");
teller.Account = accountManager.Select(r.GetInt("account_id"));
teller.Branch = branchManager.Select(r.GetInt("branch_id"));
int uId = r.GetInt("user_id");
teller.User = uId == 0 ? new User {
Id = 0
} : userManager.SelectUser(uId, false);
teller.Currency = currencyManager.SelectCurrencyById(r.GetInt("currency_id"));
teller.MinAmountTeller = r.GetMoney("amount_min");
teller.MaxAmountTeller = r.GetMoney("amount_max");
teller.MinAmountDeposit = r.GetMoney("deposit_amount_min");
teller.MaxAmountDeposit = r.GetMoney("deposit_amount_max");
teller.MinAmountWithdrawal = r.GetMoney("withdrawal_amount_min");
teller.MaxAmountWithdrawal = r.GetMoney("withdrawal_amount_max");
tellers.Add(teller);
}
}
}
return(tellers);
}
public FundingLine SelectFundingLineByName(string name)
{
FundingLine fundingLine = null;
string q =
@"SELECT FundingLines.[id],
FundingLines.[name],
FundingLines.[deleted],
FundingLines.[amount],
FundingLines.[begin_date],
FundingLines.[end_date],
FundingLines.[purpose],
FundingLines.[currency_id],
Currencies.[name] as currency_name,
Currencies.[code] as currency_code,
Currencies.[is_pivot] as currency_is_pivot,
Currencies.[is_swapped] as currency_is_swapped
FROM [FundingLines]
LEFT JOIN Currencies ON FundingLines.currency_id = Currencies.id
WHERE FundingLines.[name]=@name AND [deleted]=0";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
fundingLine = new FundingLine();
fundingLine.Id = r.GetInt("id");
fundingLine.Name = r.GetString("name");
fundingLine.Deleted = r.GetBool("deleted");
fundingLine.StartDate = r.GetDateTime("begin_date");
fundingLine.Purpose = r.GetString("purpose");
fundingLine.EndDate = r.GetDateTime("end_date");
fundingLine.Amount = r.GetMoney("amount");
fundingLine.Currency = new Currency
{
Id = r.GetInt("currency_id"),
Name = r.GetString("currency_name"),
Code = r.GetString("currency_code"),
IsPivot = r.GetBool("currency_is_pivot"),
IsSwapped = r.GetBool("currency_is_swapped")
};
}
}
}
}
SetLazyLoader(fundingLine);
return(fundingLine);
}
public CollateralProduct SelectCollateralProductByPropertyId(int propertyId)
{
int productId;
const string sqlProductIdText = @"SELECT product_id
FROM [CollateralProperties]
WHERE id = @id ";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlProductIdText, connection))
{
cmd.AddParam("@id", propertyId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(null); // nothing is coming... (c)
}
reader.Read();
productId = reader.GetInt("product_id");
}
}
const string sqlText = @"SELECT
[name]
,[desc]
,[deleted]
FROM CollateralProducts
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectProduct = new OpenCbsCommand(sqlText, conn))
{
selectProduct.AddParam("@id", productId);
using (OpenCbsReader reader = selectProduct.ExecuteReader())
{
if (reader.Empty)
{
return(null);
}
reader.Read();
CollateralProduct colProduct = new CollateralProduct
{
Id = productId,
Name = reader.GetString("name"),
Description = reader.GetString("desc"),
Delete = reader.GetBool("deleted")
};
return(colProduct);
}
}
}
public 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 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 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);
}
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"));
}
}
}
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 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 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<string> SelectProjectPurposes()
{
List<string> list = new List<string>();
const string sqlText = "SELECT * FROM ProjectPurposes";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
list.Add(reader.GetString("name"));
}
}
}
}
return list;
}
/// <summary>
/// This method allows us to select a package from 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="colProductId">id's of package searched</param>
/// <returns>A package Object if id matches with datas in database, null if not</returns>
public CollateralProduct SelectCollateralProduct(int colProductId)
{
const string sqlText = @"SELECT
[name]
,[desc]
,[deleted]
FROM CollateralProducts
WHERE id = @id";
CollateralProduct colProduct = new CollateralProduct();
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@id", colProductId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty) return null;
reader.Read();
colProduct.Id = colProductId;
colProduct.Name = reader.GetString("name");
colProduct.Description = reader.GetString("desc");
colProduct.Delete = reader.GetBool("deleted");
reader.Dispose();
}
}
List<CollateralProperty> properties = new List<CollateralProperty>();
const string sqlPropertyText = @"SELECT
id
,type_id
,[name]
,[desc]
FROM CollateralProperties
WHERE product_id = @product_id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlPropertyText, connection))
{
cmd.AddParam("@product_id", colProduct.Id);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty) return null;
while (reader.Read())
{
CollateralProperty collateralProperty = new CollateralProperty();
collateralProperty.Id = reader.GetInt("id");
collateralProperty.Type = (OCollateralPropertyTypes)Enum.ToObject(typeof(OCollateralPropertyTypes),
reader.GetInt("type_id"));
collateralProperty.Name = reader.GetString("name");
collateralProperty.Description = reader.GetString("desc");
if (collateralProperty.Type == OCollateralPropertyTypes.Collection)
{
List<string> propertyList = new List<string>();
const string sqlListText = @"SELECT [value]
FROM CollateralPropertyCollections
WHERE property_id = @property_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectList = new OpenCbsCommand(sqlListText, conn))
{
selectList.AddParam("@property_id", collateralProperty.Id);
using (OpenCbsReader listReader = selectList.ExecuteReader())
{
if (listReader.Empty) return null;
while (listReader.Read())
{
propertyList.Add(listReader.GetString("value"));
}
collateralProperty.Collection = propertyList;
}
}
}
properties.Add(collateralProperty);
}
colProduct.Properties = properties;
}
}
return colProduct;
}
public EconomicActivity SelectEconomicActivity(string pName)
{
EconomicActivity doa;
const string sqlText = "SELECT id, name, deleted FROM EconomicActivities WHERE name = @name";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectById = new OpenCbsCommand(sqlText, connection))
{
selectById.AddParam("@name", pName);
using (OpenCbsReader reader = selectById.ExecuteReader())
{
doa = GetEconomicActivity(reader);
}
}
doa.Childrens = SelectChildren(doa.Id);
return doa;
}
/// <summary>
/// This methods allows us to find all domains of application
/// </summary>
/// <returns>hierarchic collection of DomainOfApplication
/// </returns>
public List<EconomicActivity> SelectAllEconomicActivities()
{
List<EconomicActivity> doaList = new List<EconomicActivity>();
const string sqlText = "SELECT id FROM EconomicActivities WHERE parent_id IS NULL AND deleted = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectAll = new OpenCbsCommand(sqlText, connection))
{
using (OpenCbsReader reader = selectAll.ExecuteReader())
{
while (reader.Read())
{
EconomicActivity domain = new EconomicActivity
{Id = reader.GetInt("id")};
doaList.Add(domain);
}
}
}
for (int i = 0; i < doaList.Count; i++)
doaList[i] = SelectEconomicActivity(doaList[i].Id);
return doaList;
}
public PaymentMethod SelectPaymentMethodById(int paymentMethodId)
{
string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
, 0 AS account_id
FROM [dbo].[PaymentMethods] pm
WHERE pm.id = @id";
PaymentMethod pm = new PaymentMethod();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", paymentMethodId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
r.Read();
pm = GetPaymentMethodFromReader(r);
}
}
}
return pm;
}
public List<PaymentMethod> SelectPaymentMethodOfBranch(int branchId)
{
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].[branch_id] = @id AND [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;
}
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 Dictionary<int, List<int>> SelectSubordinateRel()
{
const string q = @"SELECT user_id, subordinate_id
FROM dbo.UsersSubordinates
ORDER BY user_id";
Dictionary<int, List<int>> retval = new Dictionary<int, List<int>>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty) return retval;
int currentId = 0;
while (r.Read())
{
int userId = r.GetInt("user_id");
if (currentId != userId)
{
currentId = userId;
retval.Add(currentId, new List<int>());
}
retval[currentId].Add(r.GetInt("subordinate_id"));
}
}
return retval;
}
public List<int> SelectSubordinateRel(int userId)
{
const string q = @"SELECT user_id, subordinate_id
FROM dbo.UsersSubordinates
WHERE user_id = @id";
var retval = new List<int>();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", userId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty) return retval;
while (r.Read())
{
retval.Add(r.GetInt("subordinate_id"));
}
}
}
}
return retval;
}
public Dictionary<int, List<int>> SelectBranchRel()
{
const string q = @"SELECT user_id, branch_id
FROM dbo.UsersBranches
ORDER BY user_id";
Dictionary<int, List<int>> retval = new Dictionary<int, List<int>>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty) return retval;
while (r.Read())
{
int userId = r.GetInt("user_id");
if (!retval.ContainsKey(userId)) retval.Add(userId, new List<int>());
retval[userId].Add(r.GetInt("branch_id"));
}
}
return retval;
}
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 Dashboard GetDashboard(int branchId, int subordinateId, int loanProductId)
{
var dashboard = new Dashboard();
using (var connection = GetConnection())
using (var command = new OpenCbsCommand("GetDashboard", connection)
.AsStoredProcedure()
.With("@date", TimeProvider.Today)
.With("@userId", User.CurrentUser.Id)
.With("@subordinateId", subordinateId)
.With("@branchId", branchId)
.With("@loanProductId", loanProductId)
.WithTimeout(200))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var portfolioLine = new PortfolioLine
{
Name = reader.GetString("name"),
Amount = reader.GetDecimal("amount"),
Quantity = reader.GetInt("quantity"),
Color = reader.GetString("color")
};
dashboard.PortfolioLines.Add(portfolioLine);
}
reader.NextResult();
while (reader.Read())
{
var stat = new ActionStat
{
Date = reader.GetDateTime("date"),
AmountDisbursed = reader.GetDecimal("amount_disbursed"),
NumberDisbursed = reader.GetInt("number_disbursed"),
AmountRepaid = reader.GetDecimal("amount_repaid"),
NumberRepaid = reader.GetInt("number_repaid"),
};
dashboard.ActionStats.Add(stat);
}
}
return dashboard;
}
public CollateralProperty SelectCollateralProperty(int propertyId)
{
const string sqlPropertyText = @"SELECT
[type_id]
, [name]
, [desc]
FROM CollateralProperties
WHERE id = @id";
CollateralProperty collateralProperty = new CollateralProperty();
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectProperty = new OpenCbsCommand(sqlPropertyText, connection))
{
selectProperty.AddParam("@id", propertyId);
using (OpenCbsReader propertyReader = selectProperty.ExecuteReader())
{
if (propertyReader.Empty) return null; // nothing is coming! (c)
propertyReader.Read();
collateralProperty.Id = propertyId;
collateralProperty.Type = (OCollateralPropertyTypes)Enum.ToObject(typeof(OCollateralPropertyTypes),
propertyReader.GetInt("type_id"));
collateralProperty.Name = propertyReader.GetString("name");
collateralProperty.Description = propertyReader.GetString("desc");
if (collateralProperty.Type == OCollateralPropertyTypes.Collection)
{
List<string> propertyList = new List<string>();
const string sqlListText = @"SELECT [value]
FROM CollateralPropertyCollections
WHERE property_id = @property_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectList = new OpenCbsCommand(sqlListText, conn))
{
selectList.AddParam("@property_id", collateralProperty.Id);
using (OpenCbsReader listReader = selectList.ExecuteReader())
{
if (listReader.Empty) return null;
while (listReader.Read())
{
propertyList.Add(listReader.GetString("value"));
}
collateralProperty.Collection = propertyList;
}
}
}
}
}
return collateralProperty;
}
public CollateralProduct SelectCollateralProductByPropertyId(int propertyId)
{
int productId;
const string sqlProductIdText = @"SELECT product_id
FROM [CollateralProperties]
WHERE id = @id ";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlProductIdText, connection))
{
cmd.AddParam("@id", propertyId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty) return null; // nothing is coming... (c)
reader.Read();
productId = reader.GetInt("product_id");
}
}
const string sqlText = @"SELECT
[name]
,[desc]
,[deleted]
FROM CollateralProducts
WHERE id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectProduct = new OpenCbsCommand(sqlText, conn))
{
selectProduct.AddParam("@id", productId);
using (OpenCbsReader reader = selectProduct.ExecuteReader())
{
if (reader.Empty) return null;
reader.Read();
CollateralProduct colProduct = new CollateralProduct
{
Id = productId,
Name = reader.GetString("name"),
Description = reader.GetString("desc"),
Delete = reader.GetBool("deleted")
};
return colProduct;
}
}
}
public List<Project> SelectProjectsByClientId(int pClientId)
{
var list = new List<Project>();
var listIds = new List<int>();
const string query = "SELECT id FROM Projects WHERE tiers_id = @tiersId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(query, conn))
{
select.AddParam("@tiersId", pClientId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader != null)
if (!reader.Empty)
{
while (reader.Read())
{
listIds.Add(reader.GetInt("id"));
}
}
}
foreach (int id in listIds)
{
list.Add(SelectProject(id));
}
}
return list;
}
private IEnumerable<FollowUp> SelectFollowUps(int pProjectId)
{
List<FollowUp> list = new List<FollowUp>();
const string sqlText = "SELECT * FROM FollowUp WHERE project_id = @pId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@pId", pProjectId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty) return new List<FollowUp>();
while (reader.Read())
{
FollowUp followUp = new FollowUp();
followUp.Id = reader.GetInt("id");
followUp.Year = reader.GetInt("year");
followUp.Jobs1 = reader.GetInt("Jobs1");
followUp.Jobs2 = reader.GetInt("Jobs2");
followUp.CA = reader.GetMoney("CA");
followUp.PersonalSituation = reader.GetString("PersonalSituation");
followUp.Activity = reader.GetString("activity");
followUp.Comment = reader.GetString("comment");
list.Add(followUp);
}
return list;
}
}
}
public List<User> SellectAllWithoutTellerOfBranch(Branch branch, User user)
{
const string q = @"SELECT
u.id,
u.deleted,
u.user_name,
u.first_name,
u.last_name,
u.user_pass,
u.mail,
u.sex,
u.phone,
(SELECT COUNT(*)
FROM dbo.Credit
WHERE loanofficer_id = u.id) AS num_contracts
FROM dbo.Users AS u
INNER JOIN dbo.UsersBranches ub ON ub.user_id = u.id
INNER JOIN UserRole ur ON ur.user_id = u .id
INNER JOIN Roles r ON r.id = ur.role_id
WHERE u.deleted = 0 AND r.role_of_teller = 1
AND (u.id NOT IN (SELECT user_id FROM Tellers WHERE deleted = 0) OR u.id = @user_id)
AND ub.branch_id = @branch_id AND u.id IN (SELECT @boss_id
UNION ALL
SELECT subordinate_id
FROM dbo.UsersSubordinates
WHERE user_id = @boss_id)";
List<User> users = new List<User>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@branch_id", branch.Id);
c.AddParam("@boss_id", User.CurrentUser.Id);
c.AddParam("@user_id", user == null ? 0 : user.Id);
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 PaymentMethod SelectPaymentMethodByName(string name)
{
const string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
,0 AS account_id
FROM [PaymentMethods] pm
WHERE [name] = @name";
PaymentMethod pm = new PaymentMethod();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
r.Read();
pm = GetPaymentMethodFromReader(r);
}
}
}
return pm;
}
private User SelectUser(int? pUserId, string username, string password, bool pIncludeDeletedUser)
{
var selectUser = @"SELECT [Users].[id] as user_id,
[user_name],
[user_pass],
[role_code],
[first_name],
[last_name],
[mail],
[sex],
[phone],
[Users].[deleted],
[Roles].[id] as role_id,
[Roles].[code] AS role_name,
(SELECT COUNT(a.id)
FROM (SELECT Credit.id, loanofficer_id
FROM Credit
GROUP BY Credit.id, loanofficer_id ) a
WHERE a.loanofficer_id = Users.id) AS contract_count
FROM [Users] INNER JOIN UserRole on UserRole.user_id = Users.id
INNER JOIN Roles ON Roles.id = UserRole.role_id
WHERE 1 = 1 ";
if (username != null && password != null)
selectUser += @" AND [Users].[user_name] = @username AND [Users].[user_pass] = @password";
if (pUserId != null)
selectUser += @" AND [Users].[id] = @id";
if (!pIncludeDeletedUser)
selectUser += @" AND [Users].[deleted] = 0";
selectUser += @" GROUP BY [Users].[id],
[Users].[deleted],
[user_name],
[user_pass],
[role_code],
[first_name],
[last_name],
[mail],
[sex],
[phone],
[Roles].id,
[Roles].code
";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand sqlCommand = new OpenCbsCommand(selectUser, conn))
{
if (username != null && password != null)
{
sqlCommand.AddParam("@username", username);
sqlCommand.AddParam("@password", password);
}
if (pUserId != null)
sqlCommand.AddParam("@id", pUserId);
using (OpenCbsReader reader = sqlCommand.ExecuteReader())
{
if (reader != null)
{
if (!reader.Empty)
{
reader.Read();
return _GetUser(reader);
}
}
}
return null;
}
}
public List<PaymentMethod> SelectPaymentMethodsForClosure()
{
string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
,[account_id]
FROM [PaymentMethods] pm
INNER JOIN LinkBranchesPaymentMethods lbpm ON lbpm.payment_method_id = pm.id
ORDER BY pm.[id]";
List<PaymentMethod> paymentMethods = new List<PaymentMethod>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
while (r.Read())
{
paymentMethods.Add(GetPaymentMethodFromReader(r));
}
}
return paymentMethods;
}
public InstallmentType SelectInstallmentTypeByName(string name)
{
const string q = "SELECT * FROM InstallmentTypes WHERE name = @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty) return null;
r.Read();
return GetInstallmentTypeFromReader(r);
}
}
}
public int GetNumberProject(string pQuery)
{
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.ConstructSQLEntityNumberProxy();
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())
{
reader.Read();
return reader.GetInt(0);
}
}
}
public Project SelectProjectByContractId(int pContractId)
{
string sqlText = @"SELECT Projects.*
FROM Projects
INNER JOIN Contracts ON Projects.id = Contracts.project_id
WHERE Contracts.id = @contractId";
Project project = null;
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@contractId", pContractId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
reader.Read();
project = GetProject(reader);
}
}
}
if (_clientManager != null)
project.Client = _clientManager.SelectClientByProjectId(project.Id);
return project;
}
/// <summary>
/// This methods allows us to find a economic activity.
/// We use recursive method to find parent
/// </summary>
/// <param name="id">the id searched</param>
/// <returns>DomainOfApplication object</returns>
public EconomicActivity SelectEconomicActivity(int pId)
{
EconomicActivity doa;
const string sqlText = @"SELECT [id], [name], [deleted]
FROM EconomicActivities
WHERE id = @id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectById = new OpenCbsCommand(sqlText, connection))
{
selectById.AddParam("@id", pId);
using (OpenCbsReader reader = selectById.ExecuteReader())
{
doa = GetEconomicActivity(reader);
}
}
doa.Childrens = SelectChildren(doa.Id);
return doa;
}
public 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 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;
}
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;
}
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;
}
/// <summary>
/// Select all packages in database
/// </summary>
/// <param name="pShowAlsoDeleted"></param>
/// <returns>a list contains all packages</returns>
public List<CollateralProduct> SelectAllCollateralProducts(bool pShowAlsoDeleted)
{
List<CollateralProduct> packagesList = new List<CollateralProduct>();
string sqlText = @"SELECT id
FROM CollateralProducts
WHERE 1 = 1";
if (!pShowAlsoDeleted)
sqlText += " AND deleted = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectPackages = new OpenCbsCommand(sqlText, connection))
{
using (OpenCbsReader reader = selectPackages.ExecuteReader())
{
if (reader.Empty) return new List<CollateralProduct>();
while (reader.Read())
{
CollateralProduct pack = new CollateralProduct { Id = reader.GetInt("id") };
packagesList.Add(pack);
}
}
}
for (int i = 0; i < packagesList.Count; i++)
{
packagesList[i] = SelectCollateralProduct(packagesList[i].Id);
}
return packagesList;
}