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