public List <DbRecommendation> Execute(Guid sessionID)
{
DbRecommendation rec = new DbRecommendation();
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionID).ToString());
string qry = "SELECT FDMUSER.firstname ||' '|| FDMUSER.lastname AS Name FROM FDMUSER JOIN RECOMMENDATIONS ON RECOMMENDATIONS.recomender_id = FDMUSER.user_id WHERE RECOMMENDATIONS.recomender_id = " + userId;
IReadOneCommand cmd = new ReadOneCommand();
rec.Recommender = cmd.Execute(qry);
string qry2 = "SELECT FDMUSER.firstname ||' '|| FDMUSER.lastname AS Name, JOBPOST.title, JOBPOST.job_id, recommendations.reason FROM FDMUSER JOIN RECOMMENDATIONS ON RECOMMENDATIONS.recomendee_id = FDMUSER.user_id JOIN JOBPOST ON RECOMMENDATIONS.job_id = JOBPOST.job_id WHERE RECOMMENDATIONS.recomender_id = " + userId;
IReadCommand cmd2 = new ReadCommand();
DataTable dt = cmd2.Execute(qry2);
List <DbRecommendation> recPeople = new List <DbRecommendation>(dt.Rows.Count);
recPeople = (from DataRow row in dt.Rows
select new DbRecommendation
{
Recommended = row["NAME"].ToString(),
JobTitle = row["TITLE"].ToString(),
JobID = int.Parse(row["JOB_ID"].ToString()),
Reason = row["REASON"].ToString()
}).ToList();
return(recPeople);
}
public bool GetCommand(int jobId, Guid sessionId, string commentText)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_comment", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
//Get user id
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionId).ToString());
cmd.Parameters.Add(new OleDbParameter("@user_id", userId));
cmd.Parameters.Add(new OleDbParameter("@job_id", jobId));
cmd.Parameters.Add(new OleDbParameter("@comText", commentText));
cmd.ExecuteNonQuery();
return(true);
}
catch (OleDbException e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public bool GetCommand(List <DbUser> user, int stream_id)
{
FindUser tempUser = new FindUser();
IWriteCommand cmd = new WriteCommand();
DbUser deluser = new DbUser();
bool result = false;
try
{
string username = user[0].Username;
deluser = tempUser.GetUserByUsername(username);
if (deluser.UserId == 0)
{
return(result);
}
else
{
string cmdString = "sp_del_user_and_constraints(" + deluser.UserId + ")";
cmd.SetCommand(cmdString);
if (cmd.Execute())
{
Console.WriteLine(cmdString);
result = true;
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(result);
}
public DbUser DisplayProfile(Guid sessionid)
{
UserProfile profile = new UserProfile();
FindUser find = new FindUser();
int id = int.Parse(find.GetUserIDBySessionId(sessionid));
DbUser user = profile.GetUserDetails(id);
return(user);
}
private string Read(string username)
{
FindUser user = new FindUser();
string password = "";
if (user.GetUserByUsername(username).Username == username)
{
string cmdString = "SELECT password FROM FDMUser WHERE FDMUSER.Username = '******'";
IReadOneCommand cmd = new ReadOneCommand();
password = cmd.Execute(cmdString).ToString();
}
return(password);
}
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 <DbJob> Execute(DbJob job)
{
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionID).ToString());
string qry = "SELECT JOBPOST.job_id, Stream_id, Status_id, Description, Title, DatePosted, Deadline, Company, Location FROM JobPost JOIN FAVOURITE on JOBPOST.Job_id = FAVOURITE.Job_id WHERE FAVOURITE.User_id = " +
userId + "ORDER BY DatePosted";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
List <DbJob> favJobs = new List <DbJob>(dt.Rows.Count);
//Get text for stream and status
GetOneStatus status = new GetOneStatus();
GetOneStream stream = new GetOneStream();
favJobs = (from DataRow row in dt.Rows
select new DbJob
{
JobId = int.Parse(row["JOB_ID"].ToString()),
Stream = stream.Read(int.Parse(row["STREAM_ID"].ToString())),
Status = status.Read(int.Parse(row["STATUS_ID"].ToString())),
Description = row["DESCRIPTION"].ToString(),
Title = row["TITLE"].ToString(),
DatePosted = DateTime.Parse(row["DATEPOSTED"].ToString()),
Deadline = DateTime.Parse(row["DEADLINE"].ToString()),
Company = row["COMPANY"].ToString(),
Location = row["LOCATION"].ToString()
}).ToList();
for (int i = 0; i < favJobs.Count; i++)
{
// favJobs[i].JobId = job.JobId;
favJobs[i].UserId = userId;
}
return(favJobs);
}
public List <DbJob> CheckJobExists(string username)
{
FindUser tempUser = new FindUser();
DbUser deluser = new DbUser();
DbJob job = new DbJob();
MyJobs jobs = new MyJobs();
List <DbJob> jobcount = new List <DbJob>();
try
{
deluser = tempUser.GetUserByUsername(username);
if (deluser.TypeId == 1)
{
job.UserId = deluser.UserId;
jobcount = jobs.Execute(job);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(jobcount);
}
public string FindUsernameBySessionId(Guid sessionId)
{
FindUser findUser = new FindUser();
return(findUser.GetUsernameBySessionId(sessionId));
}
public List <DbUser> GetUserByUserName(string username)
{
FindUser user = new FindUser();
return(user.Execute(username));
}
public List <DbUser> FindUser(DbUser user)
{
FindUser userDetails = new FindUser();
return(userDetails.Execute(user));
}
public int GetNumOfUsers(string firstName, string lastName)
{
FindUser user = new FindUser();
return(user.NumOfUsers(firstName, lastName));
}
public List <DbUser> GetAvailableConsultants(string stream)
{
FindUser find = new FindUser();
return(find.GetConsultants(stream));
}
public List <string> GetUserNamesForAutoFill()
{
FindUser usernames = new FindUser();
return(usernames.Execute());
}
public bool GetCommand(DbJob job, List <string> skills, Guid sessionID)
{
//string cmdString = "sp_add_job(" + job[0].UserId + ",'" + job[0].StreamId + "','" +
// job[0].StatusId + "','" + job[0].Description + "','" +
// job[0].Title + "','" + job[0].Deadline + "','" +
// job[0].Company + "'," + job[0].Location + ")";
//IWriteCommand cmd = new WriteCommand();
//cmd.SetCommand(cmdString);
//if (cmd.Execute())
//{
// return true;
//}
//return false;
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_job", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Get IDs
GetStatusID statusid = new GetStatusID();
int status_ID = int.Parse(statusid.Read(job.Status));
GetStreamID streamid = new GetStreamID();
int stream_ID = int.Parse(streamid.Read(job.Stream));
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionID).ToString());
// Add job
cmd.Parameters.Add(new OleDbParameter("@user_id", userId));
cmd.Parameters.Add(new OleDbParameter("@stream_id", stream_ID));
cmd.Parameters.Add(new OleDbParameter("@status_id", status_ID));
cmd.Parameters.Add(new OleDbParameter("@description", job.Description));
cmd.Parameters.Add(new OleDbParameter("@title", job.Title));
cmd.Parameters.Add(new OleDbParameter("@deadline", job.Deadline));
cmd.Parameters.Add(new OleDbParameter("@company", job.Company));
cmd.Parameters.Add(new OleDbParameter("@location", job.Location));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
// Add jobskills
for (int i = 0; i < skills.Count(); i++)
{
IDbCommand cmd2 = new OleDbCommand("sp_add_jobSkill", (OleDbConnection)cn);
cmd2.Transaction = tran;
cmd2.CommandType = CommandType.StoredProcedure;
string formattedSkill = FormatSkill(skills[i]);
GetSkillId id = new GetSkillId();
int skillid = int.Parse(id.Read(formattedSkill));
cmd2.Parameters.Add(new OleDbParameter("@skill_id", int.Parse(skillid.ToString())));
affectedRows = affectedRows + cmd2.ExecuteNonQuery();
}
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
