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