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