public void Test_GetFavClass_ExecuteMethod_Retruns_A_List_of_DbJob()
{
DbJob job = new DbJob();
job.JobId = 1;
List<DbJob> jobList = new List<DbJob>();
jobList.Add(job);
GetFav getFav = new GetFav();
getFav.setSessionId ( Guid.Parse("023bf663-97ea-421d-89fc-611edea5f79d"));
List<DbJob> jobs = getFav.Execute(job);
Assert.IsInstanceOf(typeof(List<DbJob>), jobs);
}
public void Test_GetFavClass_ExecuteMethod_Retruns_A_True_When_User_Has_Favr()
{
DbJob job = new DbJob();
job.JobId = 1;
List<DbJob> jobList = new List<DbJob>();
jobList.Add(job);
GetFav getFav = new GetFav();
getFav.setSessionId(Guid.Parse("023bf663-97ea-421d-89fc-611edea5f79d"));
List<DbJob> jobs = getFav.Execute(job);
Assert.AreEqual(3, jobs.Count);
}
public DbJob SetupJob()
{
DbJob job = new DbJob();
job.Location = "Manchester";
job.Status = "Interviewing";
job.Stream = "Testing";
job.Title = "Testing insert job";
job.Description = "A series of tests to test that a job is added to the database along with related skills.";
job.UserId = 9;
job.Deadline = DateTime.Now;
job.DatePosted = DateTime.Now;
job.Company = "Barclays";
return job;
}
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 void Test_UpdateJobClass_GetCommandMethod_Returns_A_bool()
{
DbJob job = new DbJob();
job.JobId = 6;
job.UserId = 6;
job.Stream = "Testing";
job.Status = "Interviewing";
job.Company = "MyCompany";
job.Location = "Manchester";
job.Title = "updating From Tests";
job.Description = "I commit this change via NUnit";
job.Deadline = DateTime.Now;
List<DbJob> jobList = new List<DbJob>();
jobList.Add(job);
UpdateJob upjob = new UpdateJob();
bool result = upjob.GetCommand(jobList);
Assert.IsInstanceOf(typeof(bool), result);
}
public void Test_UpdateClass_GetCommandMethod_Returns_A_bool()
{
DbJob job = new DbJob();
job.JobId = 5;
List<DbJob> jobs = new List<DbJob>();
jobs.Add(job);
List<string> deleteskills = new List<string>();
deleteskills.Add(".Net");
List<string> Addedskills = new List<string>();
Addedskills.Add("SQL");
Addedskills.Add("Java");
Addedskills.Add("VMware");
UpdateJob upjob = new UpdateJob();
bool result = upjob.GetCommand(jobs, deleteskills);
Assert.IsInstanceOf(typeof(bool), result);
}
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 bool InsertJob(DbJob job, List <string> skills, Guid sessionid)
{
InsertJob insJob = new InsertJob();
return(insJob.GetCommand(job, skills, sessionid));
}
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);
}
public bool InsertJob(DbJob job, List<string> skills, Guid sessionid)
{
InsertJob insJob = new InsertJob();
return insJob.GetCommand(job, skills, sessionid);
}
public List<DbJob> GetListOfFavJobs(DbJob job, Guid session_id)
{
GetFav getFav = new GetFav();
getFav.setSessionId(session_id);
return getFav.Execute(job);
}
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);
}
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;
}
