public IEnumerable <UserDetails> GetUserDetails()
{
const string query = @"SELECT UD.FirstName, UD.Phone, UD.Email, UD.Resume
FROM UserDetails AS UD LEFT JOIN UserSkills AS US ON UD.UserId=US.UserId";
return(EntityHelper.ConvertDataTable <UserDetails>(_sqlutil.GetObject(query)));
}
public List <Customer> GetCustomerByPhoneNumber(string locationCode, string phoneNumber, string phoneExtension)
{
List <Customer> customers = new List <Customer>();
DataTable customerDetails = CustomerDataAccess.GetCustomerByPhoneNumber(locationCode, phoneNumber, phoneExtension);
customers.AddRange(EntityHelper.ConvertDataTable <Customer>(customerDetails));
return(customers);
}
public IEnumerable <UserSkills> GetUserSkillsById(int UserId)
{
const string query = @"SELECT UserSkillId, UserId, ShortName, LongName, SkillLevel
FROM UserSkills
WHERE UserId = @UserId";
_param = new List <SqlParameter>
{
new SqlParameter("UserID", UserId),
};
return(EntityHelper.ConvertDataTable <UserSkills> (_sqlutil.GetObjectWithParam(query, _param)));
}
public IEnumerable <Users> GetAllUsers(int adminId)
{
const string query = @"SELECT U.Username,U.Email,U.Phone,U.Password
FROM Users AS U
JOIN AppAdmin AS AA ON U.UserID = AA.UserId
WHERE AA.AdminID = @AdminId";
_param = new List <SqlParameter>
{
new SqlParameter("AdminId", adminId)
};
return(EntityHelper.ConvertDataTable <Users>(_sqlutil.GetObjectWithParam(query, _param)));
}
public IEnumerable <UserDetails> GetUserDetailsById(int UserId)
{
const string query = @"SELECT U.UserID, U.Username, UD.FirstName, UD.Phone, UD.Email, UD.Resume,
CAST(CASE WHEN EXISTS(SELECT 1 FROM AppAdmin AA WHERE AA.UserId = U.UserID)
THEN 'True' ELSE 'False' END AS BIT) AS Admin, US.ShortName, US.SkillLevel, US.UserSkillId
FROM Users U
INNER JOIN UserDetails UD ON U.UserID = UD.UserId
INNER JOIN UserSkills US on U.UserID = US.UserId
WHERE U.UserID = @UserID";
_param = new List <SqlParameter>
{
new SqlParameter("UserID", UserId)
};
return(EntityHelper.ConvertDataTable <UserDetails> (_sqlutil.GetObjectWithParam(query, _param)));
}
public IEnumerable <Users> GetUsers()
{
const string query = @"SELECT * FROM Users";
return(EntityHelper.ConvertDataTable <Users>(_sqlutil.GetObject(query)));
}