public List <DbUser> GetConsultants(string stream)
{
string qry;
if (stream != "All")
{
qry = "SELECT FDMUSER.User_id, FDMUSER.username, FDMUSER.FirstName, FDMUSER.LastName, FDMUSER.Location, FDMUSER.Email FROM FDMUSER JOIN TYPE ON FDMUSER.type_id = TYPE.type_id JOIN PROFILE ON PROFILE.user_id = FDMUSER.user_id JOIN STREAM ON STREAM.stream_id = PROFILE.stream_id WHERE type.type_text = 'Consultant' AND STREAM.streamtext = '" + stream + "' ORDER BY fdmuser.lastname, fdmUser.firstname";
}
else
{
qry = "SELECT FDMUSER.User_id, FDMUSER.username, FDMUSER.FirstName, FDMUSER.LastName, FDMUSER.Location, FDMUSER.Email FROM FDMUSER JOIN TYPE ON FDMUSER.type_id = TYPE.type_id WHERE type_text = 'Consultant' ORDER BY fdmuser.lastname";
}
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
users = (from DataRow row in dt.Rows
select new DbUser
{
UserId = int.Parse(row["USER_ID"].ToString()),
Username = row["USERNAME"].ToString(),
FirstName = row["FIRSTNAME"].ToString(),
LastName = row["LASTNAME"].ToString(),
Location = row["LOCATION"].ToString(),
Email = row["EMAIL"].ToString()
}).ToList();
return(users);
}
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 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 List <DbJob> Execute(DbJob job)
{
string cmdString = "select JOBPOST.job_id, JOBPOST.stream_id,JOBPOST.status_id,JOBPOST.Location, JOBPOST.Company, JOBPOST.DatePosted, JOBPOST.Deadline, JOBPOST.Title, JOBPOST.description from JOBPOST WHERE JOBPOST.User_id =" + job.UserId;
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
GetOneStatus status = new GetOneStatus();
GetOneStream stream = new GetOneStream();
List <DbJob> jobs = (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 < jobs.Count; i++)
{
jobs[i].UserId = job.UserId;
}
return(jobs);
}
public List <DbUser> Execute(string username)
{
string cmdString = "select FDMUSER.User_id, FDMUser.FirstName, FDMUser.LastName, FDMUser.Location, FDMUser.Email from FDMUser WHERE lower(FDMUser.Username) =lower('" +
username + "')";
List <DbUser> users2 = new List <DbUser>();
try
{
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
Console.WriteLine(dt.Rows.Count);
users2 = (from DataRow row in dt.Rows
select new DbUser
{
UserId = int.Parse(row["USER_ID"].ToString()),
FirstName = row["FIRSTNAME"].ToString(),
LastName = row["LASTNAME"].ToString(),
Email = row["EMAIL"].ToString(),
Location = row["LOCATION"].ToString()
}).ToList();
users2[0].Username = username;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(users2);
}
public DbJob GetJob(int jobId)
{
string qry = "SELECT Job_id, User_id, Stream_id, Status_id, Description, Title, DatePosted, Deadline, Company, Location FROM JobPost WHERE job_id = " + jobId;
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
//Get text for stream and status
GetOneStatus status = new GetOneStatus();
GetOneStream stream = new GetOneStream();
List <DbJob> recentJobs = (from DataRow row in dt.Rows
select new DbJob
{
JobId = int.Parse(row["JOB_ID"].ToString()),
UserId = int.Parse(row["USER_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();
return(recentJobs[0]);
}
//public List<DbUser> Execute(DbUser user)
//{
// string cmdString = "select FDMUser.username, FDMUser.Location, FDM.Email from FDMUser WHERE FDMUser.FirstName ='" +
// user.FirstName + "' AND FDMUser.LastName = '" + user.LastName + "'";
// IReadCommand cmd = new ReadCommand();
// DataTable dt = cmd.Execute(cmdString);
// users = (from DataRow row in dt.Rows
// select new DbUser
// {
// Email = row["EMAIL"].ToString(),
// Location = row["LOCATION"].ToString(),
// Username = row["USERNAME"].ToString()
// }).ToList();
// return users;
//}
public List <string> Execute()
{
string cmdString = "select FDMUser.username from FDMUser";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
usernames = (from row in dt.AsEnumerable() select Convert.ToString(row["USERNAME"])).ToList();
return(usernames);
}
public List <string> Execute()
{
string qry = "select TYPE.type_text from TYPE";
IReadCommand cmd = new ReadCommand();
List <string> usertypes = new List <string>();
DataTable dt = cmd.Execute(qry);
usertypes = (from row in dt.AsEnumerable() select Convert.ToString(row["TYPE_TEXT"])).ToList();
return(usertypes);
}
public List <string> GetStreams()
{
string cmdString = "select streamtext from Stream ORDER BY StreamText";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
List <string> stream = new List <string>();
stream = (from row in dt.AsEnumerable() select Convert.ToString(row["STREAMTEXT"])).ToList();
return(stream);
}
public List <string> Execute()
{
string qry = "select STREAM.streamtext from STREAM";
IReadCommand cmd = new ReadCommand();
List <string> streams = new List <string>();
DataTable dt = cmd.Execute(qry);
streams = (from row in dt.AsEnumerable() select Convert.ToString(row["STREAMTEXT"])).ToList();
return(streams);
}
public List<string> GetTStatuses()
{
string cmdString = "select tStatusText from trainee_Status ORDER BY tStatusText";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
List<string> status = new List<string>();
status = (from row in dt.AsEnumerable() select Convert.ToString(row["TSTATUSTEXT"])).ToList();
return status;
}
public List<string> GetLocations()
{
string qry = "select DISTINCT JOBPOST.location from JOBPOST";
IReadCommand cmd = new ReadCommand();
List<string> locations = new List<string>();
DataTable dt = cmd.Execute(qry);
locations = (from row in dt.AsEnumerable() select Convert.ToString(row["LOCATION"])).ToList();
return locations;
}
public List<string> GetStreams()
{
string cmdString = "select streamtext from Stream ORDER BY StreamText";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
List<string> stream = new List<string>();
stream = (from row in dt.AsEnumerable() select Convert.ToString(row["STREAMTEXT"])).ToList();
return stream;
}
public List <string> GetLocations()
{
string qry = "select DISTINCT JOBPOST.location from JOBPOST";
IReadCommand cmd = new ReadCommand();
List <string> locations = new List <string>();
DataTable dt = cmd.Execute(qry);
locations = (from row in dt.AsEnumerable() select Convert.ToString(row["LOCATION"])).ToList();
return(locations);
}
public List<string> GetCompanies()
{
string qry = "select DISTINCT JOBPOST.company from JOBPOST";
IReadCommand cmd = new ReadCommand();
List<string> companies = new List<string>();
DataTable dt = cmd.Execute(qry);
companies = (from row in dt.AsEnumerable() select Convert.ToString(row["COMPANY"])).ToList();
return companies;
}
public List <string> GetTStatuses()
{
string cmdString = "select tStatusText from trainee_Status ORDER BY tStatusText";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
List <string> status = new List <string>();
status = (from row in dt.AsEnumerable() select Convert.ToString(row["TSTATUSTEXT"])).ToList();
return(status);
}
public List <string> GetCompanies()
{
string qry = "select DISTINCT JOBPOST.company from JOBPOST";
IReadCommand cmd = new ReadCommand();
List <string> companies = new List <string>();
DataTable dt = cmd.Execute(qry);
companies = (from row in dt.AsEnumerable() select Convert.ToString(row["COMPANY"])).ToList();
return(companies);
}
public List <string> Execute()
{
string cmdString = "select Status.StatusText from Status";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
List <string> status = new List <string>();
status = (from row in dt.AsEnumerable() select Convert.ToString(row["STATUSTEXT"])).ToList();
return(status);
}
public List <string> Execute()
{
string cmdString = "Select distinct COMPANY from JOBPOST";
IReadCommand cmd = new ReadCommand();
List <string> company = new List <string>();
DataTable dt = cmd.Execute(cmdString);
company = (from row in dt.AsEnumerable() select Convert.ToString(row["COMPANY"])).ToList();
return(company);
}
public List <string> Execute()
{
string cmdString = "Select distinct Location from JOBPOST";
IReadCommand cmd = new ReadCommand();
List <string> location = new List <string>();
DataTable dt = cmd.Execute(cmdString);
location = (from row in dt.AsEnumerable() select Convert.ToString(row["LOCATION"])).ToList();
return(location);
}
public List<string> Execute(int jobId)
{
string cmdString = "SELECT skilltext FROM skill s JOIN ( SELECT skill_id FROM jobskill WHERE job_id = '" + jobId + "')js ON s.skill_id = js.skill_id";
IReadCommand cmd = new ReadCommand();
List<string> skills = new List<string>();
DataTable dt = cmd.Execute(cmdString);
skills = (from row in dt.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return skills;
}
public List<string> GetUserSkills(int userId)
{
string qry2 = "SELECT skilltext FROM skill s JOIN ( SELECT skill_id FROM userskill WHERE user_id = '" + userId + "')us ON s.skill_id = us.skill_id";
IReadCommand cmd2 = new ReadCommand();
DataTable dt = cmd2.Execute(qry2);
List<string> skills = new List<string>();
skills = (from row in dt.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return skills;
}
public int NumOfUsers(string firstName, string lastName)
{
string cmdString = "select FDMUser.username FROM FDMUser WHERE lower(FDMUser.FirstName) = lower('" +
firstName.ToUpper() + "') AND lower(FDMUser.LastName) = lower('" +
lastName.ToUpper() + "')";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
usernames = (from row in dt.AsEnumerable() select Convert.ToString(row["USERNAME"])).ToList();
return(usernames.Count);
}
public List <string> GetUserSkills(int userId)
{
string qry2 = "SELECT skilltext FROM skill s JOIN ( SELECT skill_id FROM userskill WHERE user_id = '" + userId + "')us ON s.skill_id = us.skill_id";
IReadCommand cmd2 = new ReadCommand();
DataTable dt = cmd2.Execute(qry2);
List <string> skills = new List <string>();
skills = (from row in dt.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return(skills);
}
public List<string> Execute()
{
string cmdString = "SELECT skilltext FROM Skill ORDER BY skilltext";
IReadCommand cmd = new ReadCommand();
List<string> skills = new List<string>();
DataTable dt = cmd.Execute(cmdString);
skills = (from row in dt.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return skills;
}
public List<string> Execute()
{
string cmdString = "Select distinct COMPANY from JOBPOST";
IReadCommand cmd = new ReadCommand();
List<string> company = new List<string>();
DataTable dt = cmd.Execute(cmdString);
company = (from row in dt.AsEnumerable() select Convert.ToString(row["COMPANY"])).ToList();
return company;
}
public List<string> Execute()
{
string cmdString = "Select distinct Location from JOBPOST";
IReadCommand cmd = new ReadCommand();
List<string> location = new List<string>();
DataTable dt = cmd.Execute(cmdString);
location = (from row in dt.AsEnumerable() select Convert.ToString(row["LOCATION"])).ToList();
return location;
}
public List <string> Execute()
{
string cmdString = "SELECT skilltext FROM Skill ORDER BY skilltext";
IReadCommand cmd = new ReadCommand();
List <string> skills = new List <string>();
DataTable dt = cmd.Execute(cmdString);
skills = (from row in dt.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return(skills);
}
public List <string> Execute(int jobId)
{
string cmdString = "SELECT skilltext FROM skill s JOIN ( SELECT skill_id FROM jobskill WHERE job_id = '" + jobId + "')js ON s.skill_id = js.skill_id";
IReadCommand cmd = new ReadCommand();
List <string> skills = new List <string>();
DataTable dt = cmd.Execute(cmdString);
skills = (from row in dt.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return(skills);
}
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 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 List <DbJob> Execute()
{
string qry = "SELECT Job_id, User_id, Stream_id, Status_id, Description, Title, DatePosted, Deadline, Company, Location FROM JobPost ORDER BY DatePosted";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
List <DbJob> recentJobs = new List <DbJob>(dt.Rows.Count);
recentJobs = (from DataRow row in dt.Rows
select new DbJob
{
//JobId = row["JOBID"].ToString(),
}).ToList();
return(recentJobs);
}
public DbUser GetUserByUsername(string username)
{
string cmdString = "select FDMUSER.user_id, FDMUSER.type_id FROM FDMUser WHERE lower(FDMUser.Username) = lower('" +
username + "')";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
users = (from DataRow row in dt.Rows
select new DbUser
{
UserId = int.Parse(row["USER_ID"].ToString()),
TypeId = int.Parse(row["TYPE_ID"].ToString())
}).ToList();
users[0].Username = username;
return(users[0]);
}
public List <DbJob> Execute()
{
string qry;
if (streamText != "All")
{
qry = "SELECT JobPost.Job_id, JobPost.User_id, JobPost.Stream_id, JobPost.Status_id, JobPost.Description, JobPost.Title, JobPost.DatePosted, JobPost.Deadline, JobPost.Company, JobPost.Location FROM JobPost JOIN stream ON stream.stream_id = jobpost.stream_id WHERE streamtext = '" + this.streamText + "' ORDER BY JOBPOST.DatePosted DESC";
}
else
{
qry = "SELECT JobPost.Job_id, JobPost.User_id, JobPost.Stream_id, JobPost.Status_id, JobPost.Description, JobPost.Title, JobPost.DatePosted, JobPost.Deadline, JobPost.Company, JobPost.Location FROM JobPost JOIN stream ON stream.stream_id = jobpost.stream_id WHERE jobpost.dateposted >= trunc(sysdate)-7 OR jobpost.deadline BETWEEN trunc(sysdate) AND trunc(sysdate)+14 ORDER BY JOBPOST.DatePosted DESC";
}
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
List <DbJob> recentJobs = new List <DbJob>(dt.Rows.Count);
//Get text for stream and status
GetOneStatus status = new GetOneStatus();
GetOneStream stream = new GetOneStream();
recentJobs = (from DataRow row in dt.Rows
select new DbJob
{
JobId = int.Parse(row["JOB_ID"].ToString()),
UserId = int.Parse(row["USER_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 < recentJobs.Count(); i++)
{
GetDates(recentJobs[i]);
}
return(recentJobs);
}
public DbUser GetUserDetails(int userid)
{
// Get user details
string qry2 = "SELECT FDMUSER.user_id, FDMUSER.firstname, FDMUSER.lastname, FDMUSER.email, FDMUSER.location, Profile.degree, Profile.modules, Profile.tstatus_id, profile.stream_id FROM FDMUSER JOIN Profile ON FDMUSER.user_id = profile.user_id WHERE FDMUSER.user_id = " + userid;
IReadCommand cmd2 = new ReadCommand();
DataTable dt = cmd2.Execute(qry2);
List <DbUser> users = (from DataRow row in dt.Rows
select new DbUser
{
UserId = int.Parse(row["USER_ID"].ToString()),
FirstName = row["FIRSTNAME"].ToString(),
LastName = row["LASTNAME"].ToString(),
Email = row["EMAIL"].ToString(),
Location = row["LOCATION"].ToString(),
Degree = row["DEGREE"].ToString(),
Modules = row["MODULES"].ToString(),
TStatus = row["TSTATUS_ID"].ToString(),
Stream = row["STREAM_ID"].ToString()
}).ToList();
//string tstatusId = (from row in dt.AsEnumerable() select Convert.ToString(row["TSTATUS_ID"])).ToString();
//string streamId = (from row in dt.AsEnumerable() select Convert.ToString(row["STREAM_ID"])).ToString();
// Get stream and tstatus text
string qry3 = "SELECT streamtext FROM Stream WHERE stream_id = " + users[0].Stream;
IReadOneCommand cmd3 = new ReadOneCommand();
users[0].Stream = cmd3.Execute(qry3);
// Get stream and tstatus text
string qry4 = "SELECT tstatustext FROM Trainee_Status WHERE tstatus_id = " + users[0].TStatus;
IReadOneCommand cmd4 = new ReadOneCommand();
users[0].TStatus = cmd3.Execute(qry4);
// Get userskills
users[0].Skills = GetUserSkills(users[0].UserId);
users[0].Name = users[0].FirstName + " " + users[0].LastName;
return(users[0]);
}
public List <DbUser> Execute(DbUser user)
{
string cmdString = "select FDMUser.User_id, FDMUser.username, FDMUser.FirstName, FDMUser.LastName, FDMUser.Location, FDMUser.Email from FDMUser WHERE FDMUser.Type_id =" + user.TypeId;
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(cmdString);
users = (from DataRow row in dt.Rows
select new DbUser
{
UserId = int.Parse(row["USER_ID"].ToString()),
FirstName = row["FIRSTNAME"].ToString(),
LastName = row["LASTNAME"].ToString(),
Email = row["EMAIL"].ToString(),
Location = row["LOCATION"].ToString(),
Username = row["USERNAME"].ToString()
}).ToList();
return(users);
}
public DbJob GetDetails(int jobID)
{
try
{
string qry = "SELECT Job_id, User_id, Stream_id, Status_id, Description, Title, DatePosted, Deadline, Company, Location FROM JobPost WHERE job_id = " + jobID;
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
//Get text for stream and status
GetOneStatus status = new GetOneStatus();
GetOneStream stream = new GetOneStream();
List<DbJob> jobs = (from DataRow row in dt.Rows
select new DbJob
{
JobId = int.Parse(row["JOB_ID"].ToString()),
UserId = int.Parse(row["USER_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();
DbJob job = jobs[0];
string qry2 = "SELECT skill.skilltext FROM SKILL JOIN JOBSKILL ON skill.skill_id = jobskill.skill_id WHERE job_id = " + jobID;
IReadCommand cmd2 = new ReadCommand();
DataTable dt2 = cmd.Execute(qry2);
job.Skills = (from row in dt2.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return job;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return new DbJob();
}
}
public DbJob GetDetails(int jobID)
{
try
{
string qry = "SELECT Job_id, User_id, Stream_id, Status_id, Description, Title, DatePosted, Deadline, Company, Location FROM JobPost WHERE job_id = " + jobID;
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
//Get text for stream and status
GetOneStatus status = new GetOneStatus();
GetOneStream stream = new GetOneStream();
List <DbJob> jobs = (from DataRow row in dt.Rows
select new DbJob
{
JobId = int.Parse(row["JOB_ID"].ToString()),
UserId = int.Parse(row["USER_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();
DbJob job = jobs[0];
string qry2 = "SELECT skill.skilltext FROM SKILL JOIN JOBSKILL ON skill.skill_id = jobskill.skill_id WHERE job_id = " + jobID;
IReadCommand cmd2 = new ReadCommand();
DataTable dt2 = cmd.Execute(qry2);
job.Skills = (from row in dt2.AsEnumerable() select Convert.ToString(row["SKILLTEXT"])).ToList();
return(job);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(new DbJob());
}
}
public Guid VerifyDetails(string username, string password, string consultant, string trainee)
{
// Get the type_id of part of the application
string type_id = GetAppType(consultant);
// Get the type_id of part of the application
string type_id2 = GetAppType(trainee);
// Get list of usernames and passwords of relevant type of user
string qry = "SELECT user_id, username, password FROM FDMUser WHERE type_id = " + type_id + " OR type_id = " + type_id2;
IReadCommand cmd = new ReadCommand();
List<DbUser> u = new List<DbUser>();
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()
}).ToList();
// Compare inputs with usernames and passwords of users
for (int i = 1; i < u.Count(); i += 3)
{
// if the username matches, check the password
if (username == u[i].ToString())
{
// if the password matches as well - success
if (password == u[i + 1].ToString())
{
ISessionControlCommand sessionControl = new SessionControlCommand();
return sessionControl.SessionStart(int.Parse(u[i].UserId.ToString()), int.Parse(type_id));
}
}
}
return Guid.Empty;
}
public Guid VerifyDetails(string username, string password, string consultant, string trainee)
{
// Get the type_id of part of the application
string type_id = GetAppType(consultant);
// Get the type_id of part of the application
string type_id2 = GetAppType(trainee);
// Get list of usernames and passwords of relevant type of user
string qry = "SELECT user_id, username, password FROM FDMUser WHERE type_id = " + type_id + " OR type_id = " + type_id2;
IReadCommand cmd = new ReadCommand();
List <DbUser> u = new List <DbUser>();
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()
}).ToList();
// Compare inputs with usernames and passwords of users
for (int i = 1; i < u.Count(); i += 3)
{
// if the username matches, check the password
if (username == u[i].ToString())
{
// if the password matches as well - success
if (password == u[i + 1].ToString())
{
ISessionControlCommand sessionControl = new SessionControlCommand();
return(sessionControl.SessionStart(int.Parse(u[i].UserId.ToString()), int.Parse(type_id)));
}
}
}
return(Guid.Empty);
}
public DbUser GetUserDetails(int userid)
{
// Get user details
string qry2 = "SELECT FDMUSER.user_id, FDMUSER.firstname, FDMUSER.lastname, FDMUSER.email, FDMUSER.location, Profile.degree, Profile.modules, Profile.tstatus_id, profile.stream_id FROM FDMUSER JOIN Profile ON FDMUSER.user_id = profile.user_id WHERE FDMUSER.user_id = " + userid;
IReadCommand cmd2 = new ReadCommand();
DataTable dt = cmd2.Execute(qry2);
List<DbUser> users = (from DataRow row in dt.Rows
select new DbUser
{
UserId = int.Parse(row["USER_ID"].ToString()),
FirstName = row["FIRSTNAME"].ToString(),
LastName = row["LASTNAME"].ToString(),
Email = row["EMAIL"].ToString(),
Location = row["LOCATION"].ToString(),
Degree = row["DEGREE"].ToString(),
Modules = row["MODULES"].ToString(),
TStatus = row["TSTATUS_ID"].ToString(),
Stream = row["STREAM_ID"].ToString()
}).ToList();
//string tstatusId = (from row in dt.AsEnumerable() select Convert.ToString(row["TSTATUS_ID"])).ToString();
//string streamId = (from row in dt.AsEnumerable() select Convert.ToString(row["STREAM_ID"])).ToString();
// Get stream and tstatus text
string qry3 = "SELECT streamtext FROM Stream WHERE stream_id = " + users[0].Stream;
IReadOneCommand cmd3 = new ReadOneCommand();
users[0].Stream = cmd3.Execute(qry3);
// Get stream and tstatus text
string qry4 = "SELECT tstatustext FROM Trainee_Status WHERE tstatus_id = " + users[0].TStatus;
IReadOneCommand cmd4 = new ReadOneCommand();
users[0].TStatus = cmd3.Execute(qry4);
// Get userskills
users[0].Skills = GetUserSkills(users[0].UserId);
users[0].Name = users[0].FirstName + " " + users[0].LastName;
return users[0];
}
public Guid GetSessionID(Guid currentSessionID)
{
try
{
string qry = "SELECT session_guid FROM Sessions WHERE session_guid = '" + currentSessionID.ToString() + "'";
IReadCommand cmd = new ReadCommand();
List<string> u = new List<string>();
DataTable dt = cmd.Execute(qry);
u = (from row in dt.AsEnumerable() select Convert.ToString(row["SESSION_GUID"])).ToList();
Guid g = Guid.Parse(u[0].ToString());
return g;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return Guid.Empty;
}
}
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 <DbComments> Execute(int jobId)
{
string qry = "SELECT Comment_id, Job_id, User_id, Text, DateAdded FROM Comments WHERE Job_id = " + jobId + " ORDER BY DateAdded ASC";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
List <DbComments> comments = new List <DbComments>(dt.Rows.Count);
GetOneUser user = new GetOneUser();
comments = (from DataRow row in dt.Rows
select new DbComments
{
CommentId = int.Parse(row["COMMENT_ID"].ToString()),
JobId = int.Parse(row["JOB_ID"].ToString()),
Username = user.Read(int.Parse(row["USER_ID"].ToString())),
Text = row["TEXT"].ToString(),
DateAdded = DateTime.Parse(row["DATEADDED"].ToString())
}).ToList();
return(comments);
}
public Guid GetSessionID(Guid currentSessionID)
{
try
{
string qry = "SELECT session_guid FROM Sessions WHERE session_guid = '" + currentSessionID.ToString() + "'";
IReadCommand cmd = new ReadCommand();
List <string> u = new List <string>();
DataTable dt = cmd.Execute(qry);
u = (from row in dt.AsEnumerable() select Convert.ToString(row["SESSION_GUID"])).ToList();
Guid g = Guid.Parse(u[0].ToString());
return(g);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(Guid.Empty);
}
}
public List<DbComments> Execute(int jobId)
{
string qry = "SELECT Comment_id, Job_id, User_id, Text, DateAdded FROM Comments WHERE Job_id = " + jobId + " ORDER BY DateAdded ASC";
IReadCommand cmd = new ReadCommand();
DataTable dt = cmd.Execute(qry);
List<DbComments> comments = new List<DbComments>(dt.Rows.Count);
GetOneUser user = new GetOneUser();
comments = (from DataRow row in dt.Rows
select new DbComments
{
CommentId = int.Parse(row["COMMENT_ID"].ToString()),
JobId = int.Parse(row["JOB_ID"].ToString()),
Username = user.Read(int.Parse(row["USER_ID"].ToString())),
Text = row["TEXT"].ToString(),
DateAdded = DateTime.Parse(row["DATEADDED"].ToString())
}).ToList();
return comments;
}
public List<DbJob> Search(string stream, string status, string location, string company, List<string> skills)
{
List<DbJob> finalJobs = new List<DbJob>();
List<string> sortedJobs = new List<string>();
List<string> sortedJobs2 = new List<string>();
List<string> sortedJobs3 = new List<string>();
List<string> sortedJobs4 = new List<string>();
List<Tuple<string, int>> unsortedJobs = new List<Tuple<string, int>>();
// search based on stream, status, skills, company and location
string qry1 = GetQryStreamStatus(stream, status);
string qry2 = GetQryLocComp(location, company);
IReadCommand read = new ReadCommand();
DataTable dt1 = read.Execute(qry1);
List<string> jobs1 = (from row in dt1.AsEnumerable() select Convert.ToString(row["JOB_ID"])).ToList();
for (int i = 0; i < jobs1.Count(); i++)
{
sortedJobs.Add(jobs1[i]);
}
DataTable dt2 = read.Execute(qry2);
List<string> jobs2 = (from row in dt2.AsEnumerable() select Convert.ToString(row["JOB_ID"])).ToList();
for (int i = 0; i < jobs2.Count(); i++)
{
sortedJobs2.Add(jobs2[i]);
}
//selects matching jobs from both lists
List<string> relevantJobs = sortedJobs.Intersect(sortedJobs2).ToList();
for (int k = 0; k < relevantJobs.Count(); k++)
{
sortedJobs3.Add(relevantJobs[k]);
}
// match skills
List<string> jobs3 = 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++)
{
Console.WriteLine(skills[i]);
// select all jobs that have skill
string qry3 = "SELECT JOBPOST.job_id FROM JOBPOST JOIN JOBSKILL ON JOBPOST.job_id=JOBSKILL.job_id JOIN SKILL ON SKILL.skill_id = JOBSKILL.skill_id WHERE skilltext = '" + skills[i] + "'";
IReadCommand read2 = new ReadCommand();
DataTable dt3 = read2.Execute(qry3);
List<string> jobs4 = (from row in dt3.AsEnumerable() select Convert.ToString(row["JOB_ID"])).ToList();
for (int j = 0; j < jobs4.Count(); j++)
{
jobs3.Add(jobs4[j]);
//Console.WriteLine(jobs4[j]);
}
Console.WriteLine("count " + jobs3.Count().ToString());
// return list of jobs
}
//count how often job appears in skills - how many of the relevant skills it has
var groups = jobs3.GroupBy(v => v);
foreach (var group in groups)
{
Tuple<string, int> job = new Tuple<string, int>(group.Key, group.Count());
unsortedJobs.Add(job);
}
// sort unique jobs by skill count
unsortedJobs.Sort((a, b) => a.Item2.CompareTo(b.Item2));
Console.WriteLine("count unsorted" + unsortedJobs.Count().ToString());
for (int k = 0; k < unsortedJobs.Count(); k++)
{
sortedJobs4.Add(unsortedJobs[k].Item1.ToString());
}
Console.WriteLine("count sorted" + sortedJobs4.Count().ToString());
IEnumerable<string> relevantJobs2 = sortedJobs4.Intersect(relevantJobs);
//IEnumerable<string> relevantJobs2 = relevantJobs.Intersect(sortedJobs3);
foreach (var job in relevantJobs2.ToList())
{
RecentJobs recJobs = new RecentJobs();
DbJob jobDesc = recJobs.GetJob(int.Parse(job));
finalJobs.Add(jobDesc);
}
}
else
{
for (int i = 0; i < sortedJobs4.Count(); i++)
{
RecentJobs recJobs = new RecentJobs();
DbJob jobDesc = recJobs.GetJob(int.Parse(sortedJobs4.ToList()[i]));
finalJobs.Add(jobDesc);
}
}
return finalJobs;
}
private void PopulateTheComboBoxes()
{
IReadCommand cmd = new ReadCommand();
string statusText = "SELECT STATUSTEXT FROM STATUS";
StatusComBox.DataSource = cmd.Execute(statusText);
string streamText = "SELECT STREAMTEXT FROM STREAM";
StreamComBox.DataSource = cmd.Execute(streamText);
}
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;
}
