public void Test_FindUserClass_ExecuteMethod_Returns_A_Listof_DbUsers()
{
FindUser user = new FindUser();
DbUser findUser = new DbUser();
findUser.TypeId = 1;
Assert.IsInstanceOf(typeof(List<DbUser>), user.Execute(findUser));
}
public void Test_EditPasswordClass_ExecuteMethod_Retruns_True_After_Updating_The_Password()
{
DbUser user = new DbUser();
user.Username = "******";
user.Password = "******";
EditPassword editPass = new EditPassword();
Assert.IsTrue(editPass.Execute(user));
}
public void Test_FindUserClass_ExecuteMethod_Returns_MoreThanOneUser_InThe_Listof_DbUsers()
{
FindUser user = new FindUser();
DbUser findUser = new DbUser();
findUser.TypeId = 1;
findUser.Username = "******";
Assert.AreNotEqual(0, user.Execute(findUser).Count);
}
public void Test_InsertFavClass_GetCommandMethod_Retruns_A_bool()
{
int job_id = 1;
DbUser user = new DbUser();
user.UserId = 1;
List<DbUser> userList = new List<DbUser>();
userList.Add(user);
InserFav fav = new InserFav();
bool result = fav.GetCommand(userList, job_id);
Assert.IsInstanceOf(typeof(bool), result);
}
public void Test_DelFavClass_GetCommandMethod_Returns_True_When_JobId_Exists()
{
int job_id = 1;
DbUser user = new DbUser();
user.UserId = 1;
List<DbUser> userList = new List<DbUser>();
userList.Add(user);
DelFav fav = new DelFav();
bool result = fav.GetCommand(userList, job_id);
Assert.IsTrue(result);
}
public bool Execute(DbUser alteredUser)
{
// update user details
GetStreamID id = new GetStreamID();
string stream_id = id.Read(alteredUser.Stream);
GetTStatusID id2 = new GetTStatusID();
string tstatus_id = id2.Read(alteredUser.TStatus);
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_profile", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Add job
cmd.Parameters.Add(new OleDbParameter("@userLocation", alteredUser.Location));
cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId));
cmd.Parameters.Add(new OleDbParameter("@userStream", stream_id));
cmd.Parameters.Add(new OleDbParameter("@tstatID", tstatus_id));
cmd.Parameters.Add(new OleDbParameter("@userDegree", alteredUser.Degree));
cmd.Parameters.Add(new OleDbParameter("@userModules", alteredUser.Modules));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
public DbUser GetUser(Guid sessionId)
{
DbUser user = new DbUser();
List<DbUser> u = new List<DbUser>();
// Get the user out of the database based on the session id
try
{
string qry = "SELECT FDMUser.user_id, FDMUser.username, FDMUser.password, FDMUser.firstname, FDMUser.lastname, FDMUser.email, FDMUser.type_id, FDMUser.Location FROM FDMUser JOIN SESSIONS ON FDMUser.user_id = Sessions.user_id WHERE session_guid = '" + sessionId.ToString() + "'";
IReadCommand cmd = new ReadCommand();
//List<string> u = new List<string>();
cmd.Execute(qry);
DataTable dt = cmd.Execute(qry);
u = (from DataRow row in dt.Rows
select new DbUser
{
UserId = (int)row["USER_ID"],
Username = row["USERNAME"].ToString(),
Password = row["PASSWORD"].ToString(),
FirstName = row ["FIRSTNAME"].ToString(),
LastName = row["LASTNAME"].ToString(),
Email = row["EMAIL"].ToString(),
Location = row["LOCATION"].ToString(),
TypeId = (int)row["TYPE_ID"]
}).ToList();
//// Translate into User object
user = u[0];
//user.UserId = int.Parse(u[0]);
//user.Username = u[1];
////user.Password = u[2];
//user.FirstName = u[3];
//user.LastName = u[4];
//user.Email = u[5];
//user.TypeId = int.Parse(u[6]);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return user;
}
public bool Execute(DbUser alteredUser)
{
FindUser fuser = new FindUser();
List<DbUser> userList = fuser.Execute(alteredUser.Username);
alteredUser.UserId = userList[0].UserId;
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_password", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Add job
cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId));
cmd.Parameters.Add(new OleDbParameter("@newPass", alteredUser.Password));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
public List <DbUser> FindUser(DbUser user)
{
FindUser userDetails = new FindUser();
return(userDetails.Execute(user));
}
public bool UpdatePassword(DbUser user)
{
EditPassword pswd = new EditPassword();
return(pswd.Execute(user));
}
public List <DbUser> Search(string stream, string status, List <string> skills)
{
List <string> sortedUsers = new List <string>();
List <Tuple <string, int> > unsortedUsers = new List <Tuple <string, int> >();
List <DbUser> finalUsers = new List <DbUser>();
// search on: skills, stream, status
// status = training, available, all available, placed
// get all users with stream and status match
string qry = GetQry(stream, status, skills);
IReadCommand read = new ReadCommand();
DataTable dt = read.Execute(qry);
List <string> users = (from row in dt.AsEnumerable() select Convert.ToString(row["USER_ID"])).ToList();
for (int k = 0; k < users.Count(); k++)
{
sortedUsers.Add(users[k]);
}
// match skills
List <string> users2 = new List <string>();
// check if skills are empty
if (skills.Count > 0)
{
// if not: for each required skill
for (int i = 0; i < skills.Count(); i++)
{
// select all users that have skill
string qry2 = "SELECT FDMUSER.user_id FROM FDMUSER JOIN USERSKILL ON FDMUSER.user_id=USERSKILL.user_id JOIN Skill ON Skill.skill_id = USERSKILL.skill_id WHERE skilltext = '" + skills[i] + "'";
IReadCommand read2 = new ReadCommand();
DataTable dt2 = read.Execute(qry2);
List <string> users3 = (from row in dt.AsEnumerable() select Convert.ToString(row["USER_ID"])).ToList();
for (int j = 0; j < users3.Count(); j++)
{
users2.Add(users3[j]);
}
// return list of users
}
//count how often user appears in skills - how many of the relevant skills they have
var groups = users2.GroupBy(v => v);
foreach (var group in groups)
{
Tuple <string, int> user = new Tuple <string, int>(group.Key, group.Count());
unsortedUsers.Add(user);
}
// sort unique users by skill count
unsortedUsers.Sort((a, b) => a.Item2.CompareTo(b.Item2));
for (int k = 0; k < unsortedUsers.Count(); k++)
{
sortedUsers.Add(unsortedUsers[k].Item1.ToString());
}
IEnumerable <string> relevantUsers = sortedUsers.Intersect(users);
foreach (var user in relevantUsers.ToList())
{
UserProfile profile = new UserProfile();
DbUser userProfile = profile.GetUserDetails(int.Parse(user));
finalUsers.Add(userProfile);
}
}
else
{
for (int i = 0; i < sortedUsers.Count(); i++)
{
UserProfile profile = new UserProfile();
DbUser userProfile = profile.GetUserDetails(int.Parse(sortedUsers[i]));
finalUsers.Add(userProfile);
}
}
return(finalUsers);
}
public bool UpdateProfile(DbUser alteredUser)
{
EditProfile profile = new EditProfile();
bool updated = profile.Execute(alteredUser);
return updated;
}
public bool UpdatePassword(DbUser user)
{
EditPassword pswd = new EditPassword();
return pswd.Execute(user);
}
public List<DbUser> FindUser(DbUser user)
{
FindUser userDetails = new FindUser();
return userDetails.Execute(user);
}
