private static User _GetUser(OpenCbsReader pReader)
{
User user = new User
{
Id = pReader.GetInt("user_id"),
UserName = pReader.GetString("user_name"),
FirstName = pReader.GetString("first_name"),
LastName = pReader.GetString("last_name"),
Mail = pReader.GetString("mail"),
IsDeleted = pReader.GetBool("deleted"),
HasContract = (pReader.GetInt("contract_count") != 0),
Sex = pReader.GetChar("sex"),
Phone = pReader.GetString("phone")
};
user.SetRole(pReader.GetString("role_code"));
user.UserRole = new Role
{
RoleName = pReader.GetString("role_name"),
Id = pReader.GetInt("role_id"),
IsRoleForLoan = pReader.GetBool("role_of_loan"),
IsRoleForSaving = pReader.GetBool("role_of_saving"),
IsRoleForTeller = pReader.GetBool("role_of_teller")
};
return(user);
}
private List <District> GetDistrictsCache()
{
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 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 <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 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 Teller SelectVault(int branchId)
{
var teller = new Teller();
const string q = @"SELECT id
, name
, description
, account_id
, deleted
, branch_id
, currency_id
FROM dbo.Tellers
WHERE branch_id = @branch_id AND deleted = 0 AND user_id = 0";
using (var conn = GetConnection())
using (var c = new OpenCbsCommand(q, conn))
{
c.AddParam("@branch_id", branchId);
using (OpenCbsReader r = c.ExecuteReader())
{
r.Read();
if (r.Empty)
{
return(null);
}
teller.Id = r.GetInt("id");
teller.Name = r.GetString("name");
teller.Description = r.GetString("description");
teller.Deleted = r.GetBool("deleted");
teller.Account = accountManager.Select(r.GetInt("account_id"));
teller.Branch = branchManager.Select(r.GetInt("branch_id"));
teller.Currency = currencyManager.SelectCurrencyById(r.GetInt("currency_id"));
}
}
return(teller);
}
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 <PaymentMethod> GetPaymentMethodsWithoutBranch()
{
string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
,0 AS [account_id]
FROM [PaymentMethods] pm
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())
{
PaymentMethod paymentMethod = new PaymentMethod
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
Description = r.GetString("description"),
IsPending = r.GetBool("pending"),
Account = _accountManager.Select(r.GetInt("account_id"))
};
paymentMethods.Add(paymentMethod);
}
}
}
return(paymentMethods);
}
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 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 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);
}
private static InstallmentType GetInstallmentTypeFromReader(OpenCbsReader r)
{
return(new InstallmentType
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
NbOfDays = r.GetInt("nb_of_days"),
NbOfMonths = r.GetInt("nb_of_months")
});
}
private PaymentMethod GetPaymentMethodFromReader(OpenCbsReader r)
{
//Do not change this calling of constructor by Object initializer
PaymentMethod pm = new PaymentMethod(
r.GetInt("id"),
r.GetString("name"),
r.GetString("description"),
r.GetBool("pending")
);
pm.Account = _accountManager.Select(r.GetInt("account_id"));
return(pm);
}
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);
}
/// <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 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 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);
}
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 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));
}
}
}
private List <EconomicActivity> GetCasheEconomicActivities()
{
List <EconomicActivity> doaList = new List <EconomicActivity>();
const string sqlText =
"SELECT id,name,deleted,parent_id FROM EconomicActivities WHERE 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"),
Name = reader.GetString("name"),
ParentId = reader.GetNullInt("parent_id")
};
doaList.Add(domain);
}
}
}
foreach (var economicActivity in doaList)
{
economicActivity.Parent = doaList.FirstOrDefault(val => val.Id == economicActivity.ParentId);
economicActivity.Childrens = doaList.Where(val => economicActivity.Id == val.ParentId).ToList();
}
return(doaList);
}
private static Installment GetInstallment(OpenCbsReader r)
{
var installment = new Installment
{
Number = r.GetInt("number"),
ExpectedDate = r.GetDateTime("expected_date"),
InterestsRepayment = r.GetMoney("interest_repayment"),
CapitalRepayment = r.GetMoney("capital_repayment"),
PaidDate = r.GetNullDateTime("paid_date"),
PaidCapital = r.GetMoney("paid_capital"),
FeesUnpaid = r.GetMoney("fees_unpaid"),
PaidInterests = r.GetMoney("paid_interest"),
PaidFees = r.GetMoney("paid_fees"),
Comment = r.GetString("comment"),
IsPending = r.GetBool("pending"),
StartDate = r.GetDateTime("start_date"),
OLB = r.GetMoney("olb"),
Commission = r.GetMoney("commission"),
PaidCommissions = r.GetMoney("paid_commission"),
LastInterestAccrualDate = r.GetDateTime("last_interest_accrual_date"),
ExtraAmount1 = r.GetMoney("extra_amount_1"),
ExtraAmount2 = r.GetMoney("extra_amount_2")
};
return(installment);
}
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);
}
}
}
public List <MenuObject> GetMenuList(OSecurityObjectTypes[] securityObjectTypes)
{
string q = @"SELECT [id], [component_name] FROM [MenuItems]";
if (securityObjectTypes.Any())
{
string[] types = securityObjectTypes.Select(t => Convert.ToString((int)t)).ToArray();
string condition = string.Format(" WHERE [type] in ({0})", string.Join(",", types));
q += condition;
}
List <MenuObject> menus = new List <MenuObject>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
while (r.Read())
{
menus.Add(new MenuObject
{
Id = r.GetInt("id"),
Name = r.GetString("component_name").Trim(),
NotSavedInDBYet = false
});
}
}
}
}
return(menus);
}
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 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 bool FieldValuesExistForFieldId(int fieldId)
{
string sqlText = @"SELECT COUNT(*)
AS [number]
FROM dbo.AdvancedFieldsValues
WHERE field_id = @field_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectCmd = new OpenCbsCommand(sqlText, conn))
{
selectCmd.AddParam("@field_id", fieldId);
using (OpenCbsReader reader = selectCmd.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(false);
}
reader.Read();
if (reader.GetInt("number") > 0)
{
return(true);
}
}
}
return(false);
}
public bool CustomizableFieldsExistFor(OCustomizableFieldEntities entity)
{
string sqlText = @"SELECT COUNT(*) AS [number]
FROM dbo.AdvancedFields
WHERE [entity_id] = @entity_id ";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectCmd = new OpenCbsCommand(sqlText, conn))
{
selectCmd.AddParam("@entity_id",
(int)Enum.Parse(typeof(OCustomizableFieldEntities), entity.ToString()));
using (OpenCbsReader reader = selectCmd.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(false);
}
reader.Read();
if (reader.GetInt("number") > 0)
{
return(true);
}
}
}
return(false);
}
private List <EconomicActivity> 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);
}
private Project GetProject(OpenCbsReader reader)
{
Project project = new Project();
project.Id = reader.GetInt("id");
project.ProjectStatus = (OProjectStatus)reader.GetSmallInt("status");
project.Code = reader.GetString("code");
project.Name = reader.GetString("name");
project.Aim = reader.GetString("aim");
project.BeginDate = reader.GetDateTime("begin_date");
project.Abilities = reader.GetString("abilities");
project.Experience = reader.GetString("experience");
project.Market = reader.GetString("market");
project.Concurrence = reader.GetString("concurrence");
project.Purpose = reader.GetString("purpose");
project.CorporateName = reader.GetString("corporate_name");
project.CorporateJuridicStatus = reader.GetString("corporate_juridicStatus");
project.CorporateFiscalStatus = reader.GetString("corporate_FiscalStatus");
project.CorporateSIRET = reader.GetString("corporate_siret");
project.CorporateRegistre = reader.GetString("corporate_registre");
project.CorporateCA = reader.GetMoney("corporate_CA");
project.CorporateNbOfJobs = reader.GetNullInt("corporate_nbOfJobs");
project.CorporateFinancialPlanType = reader.GetString("corporate_financialPlanType");
project.CorporateFinancialPlanAmount = reader.GetMoney("corporateFinancialPlanAmount");
project.CorporateFinancialPlanTotalAmount = reader.GetMoney("corporate_financialPlanTotalAmount");
project.Address = reader.GetString("address");
project.City = reader.GetString("city");
project.ZipCode = reader.GetString("zipCode");
int? districtId = reader.GetNullInt("district_id");
if (districtId.HasValue)
project.District = new District { Id = districtId.Value };
project.HomePhone = reader.GetString("home_phone");
project.PersonalPhone = reader.GetString("personalPhone");
project.Email = reader.GetString("Email");
project.HomeType = reader.GetString("hometype");
return project;
}
private static Role GetRole(OpenCbsReader r)
{
return new Role
{
Id = r.GetInt("id"),
RoleName = r.GetString("code"),
IsDeleted = r.GetBool("deleted"),
Description = r.GetString("description")
};;
}
private static User _GetUser(OpenCbsReader pReader)
{
User user = new User
{
Id = pReader.GetInt("user_id"),
UserName = pReader.GetString("user_name"),
FirstName = pReader.GetString("first_name"),
LastName = pReader.GetString("last_name"),
Mail = pReader.GetString("mail"),
IsDeleted = pReader.GetBool("deleted"),
HasContract = (pReader.GetInt("contract_count") != 0),
Sex = pReader.GetChar("sex"),
Phone = pReader.GetString("phone")
};
user.SetRole(pReader.GetString("role_code"));
user.UserRole = new Role
{
RoleName = pReader.GetString("role_name"),
Id = pReader.GetInt("role_id")
};
return user;
}
private static Role GetRoleForFrmRoles(OpenCbsReader r)
{
return new Role
{
Id = r.GetInt("id"),
RoleName = r.GetString("code"),
IsDeleted = r.GetBool("deleted"),
Description = r.GetString("description"),
DefaultStartPage = (OStartPages.StartPages)Enum.Parse(typeof(OStartPages.StartPages), (r.GetString("default_start_view")), true)
}; ;
}
private static Role GetRole(OpenCbsReader r)
{
return new Role
{
Id = r.GetInt("id"),
RoleName = r.GetString("code"),
IsDeleted = r.GetBool("deleted"),
Description = r.GetString("description"),
IsRoleForLoan = r.GetBool("role_of_loan"),
IsRoleForSaving = r.GetBool("role_of_saving"),
IsRoleForTeller = r.GetBool("role_of_teller")
};;
}
private PaymentMethod GetPaymentMethodFromReader(OpenCbsReader r)
{
//Do not change this calling of constructor by Object initializer
PaymentMethod pm = new PaymentMethod(
r.GetInt("id"),
r.GetString("name"),
r.GetString("description"),
r.GetBool("pending")
);
pm.Account = _accountManager.Select(r.GetInt("account_id"));
return pm;
}
private static InstallmentType GetInstallmentTypeFromReader(OpenCbsReader r)
{
return new InstallmentType
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
NbOfDays = r.GetInt("nb_of_days"),
NbOfMonths = r.GetInt("nb_of_months")
};
}
private static EconomicActivity GetEconomicActivity(OpenCbsReader pReader)
{
EconomicActivity doa = new EconomicActivity();
if (pReader != null)
{
if (!pReader.Empty)
{
pReader.Read();
doa.Id = pReader.GetInt("id");
doa.Name = pReader.GetString("name");
doa.Deleted = pReader.GetBool("deleted");
}
}
return doa;
}
private static Installment GetInstallmentHistoryFromReader(OpenCbsReader r)
{
var i = new Installment
{
Number = r.GetInt("number"),
ExpectedDate = r.GetDateTime("expected_date"),
StartDate = r.GetDateTime("start_date"),
CapitalRepayment = r.GetMoney("capital_repayment"),
InterestsRepayment = r.GetMoney("interest_repayment"),
PaidInterests = r.GetMoney("paid_interest"),
PaidCapital = r.GetMoney("paid_capital"),
PaidFees = r.GetMoney("paid_fees"),
FeesUnpaid = r.GetMoney("fees_unpaid"),
PaidDate = r.GetNullDateTime("paid_date"),
Comment = r.GetString("comment"),
OLB = r.GetMoney("olb"),
IsPending = r.GetBool("pending")
};
return i;
}