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