private static User _GetUser(OpenCbsReader pReader)
{
User user = new User
{
Id = pReader.GetInt("user_id"),
UserName = pReader.GetString("user_name"),
Password = pReader.GetString("user_pass"),
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"),
TimedOut = pReader.GetNullBool("timed_out"),
IsExpired = pReader.GetNullBool("is_expired"),
IsReset = pReader.GetNullBool("is_reset"),
LastUpdated = pReader.GetNullDateTime("last_updated"),
LoginAttempt = pReader.GetNullInt("login_attempt"),
FrapidLoginId = pReader.GetNullInt("frapid_login_id"),
};
user.Secret.Question = pReader.GetString("user_sq");
user.SetRole(pReader.GetString("role_code"));
user.UserRole = new Role
{
RoleName = pReader.GetString("role_name"),
Id = pReader.GetInt("role_id")
};
return(user);
}
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 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);
}
public List <EconomicActivity> GetCacheLoanPurposes()
{
List <EconomicActivity> list = new List <EconomicActivity>();
const string sql = @"
select
id,
name,
deleted,
parent_id
from
dbo.LoanPurpose
where
deleted = 0
";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectAll = new OpenCbsCommand(sql, connection))
{
using (OpenCbsReader reader = selectAll.ExecuteReader())
{
while (reader.Read())
{
EconomicActivity domain = new EconomicActivity
{
Id = reader.GetInt("id"),
Name = reader.GetString("name"),
Deleted = reader.GetBool("deleted"),
ParentId = reader.GetNullInt("parent_id")
};
list.Add(domain);
}
}
}
foreach (var economicActivity in list)
{
economicActivity.Parent = list.FirstOrDefault(val => val.Id == economicActivity.ParentId);
economicActivity.Childrens = list.Where(val => economicActivity.Id == val.ParentId).ToList();
}
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)";
*/
const string q = @"SELECT
u.id,
u.deleted,
u.last_updated,
u.timed_out,
u.is_expired,
u.is_reset,
u.login_attempt,
u.user_name,
u.first_name,
u.last_name,
u.user_pass,
u.user_sq,
u.user_sqa,
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 (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"),
TimedOut = r.GetNullBool("timed_out"),
IsExpired = r.GetNullBool("is_expired"),
LoginAttempt = r.GetNullInt("login_attempt"),
LastUpdated = r.GetNullDateTime("last_updated"),
UserName = r.GetString("user_name"),
Password = r.GetString("user_pass"),
Mail = r.GetString("mail"),
Sex = r.GetChar("sex"),
HasContract = r.GetInt("num_contracts") > 0
};
u.Secret.Question = r.GetString("user_sq");
u.Secret.Answer = r.GetString("user_sqa");
users.Add(u);
}
}
}
return(users);
}
public List <User> SelectAll()
{
const string q = @"SELECT
id,
deleted,
last_updated,
timed_out,
is_expired,
is_reset,
login_attempt,
frapid_login_id,
user_name,
first_name,
last_name,
user_pass,
user_sq,
user_sqa,
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"),
TimedOut = r.GetNullBool("timed_out"),
IsExpired = r.GetNullBool("is_expired"),
IsReset = r.GetNullBool("is_reset"),
LastUpdated = r.GetNullDateTime("last_updated"),
LoginAttempt = r.GetNullInt("login_attempt"),
FrapidLoginId = r.GetNullInt("frapid_login_id"),
UserName = r.GetString("user_name"),
Password = r.GetString("user_pass"),
Mail = r.GetString("mail"),
Sex = r.GetChar("sex"),
HasContract = r.GetInt("num_contracts") > 0
};
u.Secret.Question = r.GetString("user_sq");
u.Secret.Answer = r.GetString("user_sqa");
users.Add(u);
}
}
return(users);
}
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;
}