public Guid VerifyDetails(string username, string password, string type)
{
// Get the type_id of the application
int type_id = int.Parse(GetAppType(type));
// Get userid from username and password
string qry = "SELECT user_id FROM FDMUser WHERE username = '******' AND password = '******'";
IReadOneCommand cmd = new ReadOneCommand();
string u = cmd.Execute(qry);
Console.WriteLine(u.Count());
if (u != "")
{
string qry2 = "SELECT type_id FROM FDMUser WHERE user_id = " + u;
IReadOneCommand cmd2 = new ReadOneCommand();
string t = cmd.Execute(qry2);
if (int.Parse(t) == type_id)
{
ISessionControlCommand sessionControl = new SessionControlCommand();
return(sessionControl.SessionStart(int.Parse(u), int.Parse(t)));
}
}
return(Guid.Empty);
}
public void GetDates(DbJob job)
{
// Get dateposted
string qry = "select to_char(max(DatePosted), 'DD/MM/YYYY') from JOBPOST WHERE job_id = " + job.JobId;
IReadOneCommand cmd = new ReadOneCommand();
job.DatePosted = DateTime.Parse(cmd.Execute(qry));
// Get deadline
string qry2 = "select to_char(max(Deadline), 'DD/MM/YYYY') from JOBPOST WHERE job_id = " + job.JobId;
IReadOneCommand cmd2 = new ReadOneCommand();
job.Deadline = DateTime.Parse(cmd.Execute(qry2));
}
public string Read(string qry)
{
string cmdString = "SELECT skill_id FROM Skill WHERE SkillText = '" + qry + "'";
IReadOneCommand cmd = new ReadOneCommand();
string result = cmd.Execute(cmdString).ToString();
return result;
}
public string Read(int streamId)
{
string cmdString = "SELECT StreamText FROM Stream WHERE stream_id = '" + streamId + "'";
IReadOneCommand cmd = new ReadOneCommand();
return(cmd.Execute(cmdString).ToString());
}
public string Read(string tstatus)
{
string cmdString = "SELECT tstatus_id FROM TRAINEE_STATUS WHERE TSTATUSTEXT = '" + tstatus + "'";
IReadOneCommand cmd = new ReadOneCommand();
return(cmd.Execute(cmdString).ToString());
}
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 string GetUserStream(int user_id)
{
string qry = "SELECT stream.streamtext FROM stream JOIN PROFILE ON Profile.stream_id = stream.stream_id JOIN FDMUSER ON profile.user_id = fdmuser.user_id WHERE FDMUSER.user_id = " + user_id;
IReadOneCommand cmd = new ReadOneCommand();
return(cmd.Execute(qry));
}
public string Read(string stream)
{
string cmdString = "SELECT stream_id FROM Stream WHERE StreamText = '" + stream + "'";
IReadOneCommand cmd = new ReadOneCommand();
return(cmd.Execute(cmdString).ToString());
}
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 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 + "'";
IReadOneCommand cmd = new ReadOneCommand();
string u = cmd.Execute(qry);
// 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), int.Parse(type_id));
}
}
}
return Guid.Empty;
}
public string Read(int userId)
{
string cmdString = "SELECT SUBSTR(firstname||' '||lastname,0,30) FROM FDMUser WHERE user_id = '" + userId + "'";
IReadOneCommand cmd = new ReadOneCommand();
return(cmd.Execute(cmdString).ToString());
}
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 + "'";
IReadOneCommand cmd = new ReadOneCommand();
string u = cmd.Execute(qry);
// 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), int.Parse(type_id)));
}
}
}
return(Guid.Empty);
}
public int GetUserIdByCommentId(int commentId)
{
string cmdString = "select user_id FROM COMMENTS WHERE comment_id = '" + commentId + "'";
IReadOneCommand readCmd = new ReadOneCommand();
int userId = int.Parse(readCmd.Execute(cmdString));
return userId;
}
public int GetUserID(string username, string password)
{
string qry = "SELECT user_id FROM FDMUser WHERE username = "******" and password = " + password;
IReadOneCommand cmd = new ReadOneCommand();
int u = int.Parse(cmd.Execute(qry)[0].ToString());
return u;
}
public int GetUserID(string username, string password)
{
string qry = "SELECT user_id FROM FDMUser WHERE username = "******" and password = " + password;
IReadOneCommand cmd = new ReadOneCommand();
int u = int.Parse(cmd.Execute(qry)[0].ToString());
return(u);
}
public string GetAppType(string type)
{
// A method to get the type id from the database from the type of application
string qry = "SELECT type_id FROM Type WHERE type_text = '" + type + "'";
IReadOneCommand cmd = new ReadOneCommand();
string type_id = cmd.Execute(qry);
return type_id;
}
public string Read(string qry)
{
string cmdString = "SELECT skill_id FROM Skill WHERE SkillText = '" + qry + "'";
IReadOneCommand cmd = new ReadOneCommand();
string result = cmd.Execute(cmdString).ToString();
return(result);
}
public int GetUserIdByCommentId(int commentId)
{
string cmdString = "select user_id FROM COMMENTS WHERE comment_id = '" + commentId + "'";
IReadOneCommand readCmd = new ReadOneCommand();
int userId = int.Parse(readCmd.Execute(cmdString));
return(userId);
}
public string GetUsernameBySessionId(Guid sessionId)
{
string cmdString = "select FDMUser.Username FROM FDMUSER JOIN SESSIONS ON SESSIONS.User_id = FDMUser.User_id WHERE SESSIONS.Session_Guid = '" + sessionId + "'";
IReadOneCommand readCmd = new ReadOneCommand();
string username = readCmd.Execute(cmdString);
return(username);
}
public string GetAppType(string type)
{
// A method to get the id from the database from the type of application
string qry = "SELECT type_id FROM Type WHERE type_text = " + type;
IReadOneCommand cmd = new ReadOneCommand();
string type_id = cmd.Execute(qry);
return(type_id);
}
public string GetUserIDBySessionId(Guid sessionId)
{
// Get user id
string qry = "SELECT FDMUSER.user_id FROM FDMUSER JOIN Sessions ON FDMUSER.user_id = Sessions.user_id WHERE Sessions.session_guid = '" + sessionId + "'";
IReadOneCommand cmd = new ReadOneCommand();
string id = cmd.Execute(qry);
return(id);
}
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 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 bool DetermineIfExists(int recommender_id, int recommended_id, int job_id)
{
string cmdString = "SELECT recommendation_id FROM RECOMMENDATIONS WHERE RECOMMENDATIONS.recomender_id= " + recommender_id + "AND RECOMMENDATIONS.Job_id =" + job_id + " AND RECOMMENDATIONS.recomendee_id= " + recommended_id;
IReadOneCommand cmd = new ReadOneCommand();
string id = cmd.Execute(cmdString);
if (id == "0")
{
// does not exist
return false;
}
return true;
}
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 DetermineIfExists(string skill)
{
string cmdString = "SELECT skill_id FROM Skill WHERE upper(SkillText) = '" + skill.ToUpper() + "'";
IReadOneCommand cmd = new ReadOneCommand();
string id = cmd.Execute(cmdString);
if (id == "0")
{
// does not exist
return false;
}
return true;
}
// Uses SMTP directly
public bool Execute(Guid sessionID, string subject, string body, string recipientEmail, List <string> cc, string file)
{
// Sender
// Get userEmail
string qry = "SELECT email FROM FDMUSER JOIN SESSIONS ON FDMUSER.user_id = SESSIONS.user_id WHERE session_guid = '" + sessionID.ToString() + "'";
ReadOneCommand cmd = new ReadOneCommand();
string userEmail = cmd.Execute(qry);
// Create message
MailMessage message = new MailMessage();
message.From = new MailAddress(userEmail);
// Recipient
message.To.Add(new MailAddress(recipientEmail));
// CC
if (cc.Count() > 0)
{
for (int i = 0; i < cc.Count(); i++)
{
message.CC.Add(new MailAddress(cc[i]));
}
}
// Subject
message.Subject = subject;
// Attachment
if (file != "")
{
Attachment attachment = new Attachment(file, "my attachment");
message.Attachments.Add(attachment);
}
// Body
message.Body = body;
try
{
SmtpClient client = new SmtpClient("localhost");
client.SendAsync(message, message);
//client.Send(message);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
}
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 DetermineIfExists(int recommender_id, int recommended_id, int job_id)
{
string cmdString = "SELECT recommendation_id FROM RECOMMENDATIONS WHERE RECOMMENDATIONS.recomender_id= " + recommender_id + "AND RECOMMENDATIONS.Job_id =" + job_id + " AND RECOMMENDATIONS.recomendee_id= " + recommended_id;
IReadOneCommand cmd = new ReadOneCommand();
string id = cmd.Execute(cmdString);
if (id == "0")
{
// does not exist
return(false);
}
return(true);
}
public bool DetermineIfExists(string skill)
{
string cmdString = "SELECT skill_id FROM Skill WHERE upper(SkillText) = '" + skill.ToUpper() + "'";
IReadOneCommand cmd = new ReadOneCommand();
string id = cmd.Execute(cmdString);
if (id == "0")
{
// does not exist
return(false);
}
return(true);
}
// Uses SMTP directly
public bool Execute(Guid sessionID, string subject, string body, string recipientEmail, List<string> cc, string file)
{
// Sender
// Get userEmail
string qry = "SELECT email FROM FDMUSER JOIN SESSIONS ON FDMUSER.user_id = SESSIONS.user_id WHERE session_guid = '" + sessionID.ToString() + "'";
ReadOneCommand cmd = new ReadOneCommand();
string userEmail = cmd.Execute(qry);
// Create message
MailMessage message = new MailMessage();
message.From = new MailAddress(userEmail);
// Recipient
message.To.Add(new MailAddress(recipientEmail));
// CC
if (cc.Count() > 0)
{
for (int i = 0; i < cc.Count(); i++)
{
message.CC.Add(new MailAddress(cc[i]));
}
}
// Subject
message.Subject = subject;
// Attachment
if (file != "")
{
Attachment attachment = new Attachment(file, "my attachment");
message.Attachments.Add(attachment);
}
// Body
message.Body = body;
try
{
SmtpClient client = new SmtpClient("localhost");
client.SendAsync(message, message);
//client.Send(message);
return true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
}
public bool DetermineIfExists(int user_id, int job_id)
{
string cmdString = "SELECT fav_id FROM FAVOURITE WHERE FAVOURITE.user_id= " + user_id +
"AND FAVOURITE.Job_id =" + job_id;
IReadOneCommand cmd = new ReadOneCommand();
string id = cmd.Execute(cmdString);
if (id == "0")
{
// does not exist
return(false);
}
return(true);
}
public Guid VerifyDetails(string username, string password, string type)
{
// Get the type_id of the application
int type_id = int.Parse(GetAppType(type));
// Get userid from username and password
string qry = "SELECT user_id FROM FDMUser WHERE username = '******' AND password = '******'";
IReadOneCommand cmd = new ReadOneCommand();
string u = cmd.Execute(qry);
Console.WriteLine(u.Count());
if (u != "")
{
string qry2 = "SELECT type_id FROM FDMUser WHERE user_id = " + u;
IReadOneCommand cmd2 = new ReadOneCommand();
string t = cmd.Execute(qry2);
if (int.Parse(t) == type_id)
{
ISessionControlCommand sessionControl = new SessionControlCommand();
return sessionControl.SessionStart(int.Parse(u), int.Parse(t));
}
}
return Guid.Empty;
}
public bool Execute(Guid sessionID, string recipientEmail)
{
try
{
// Get userEmail
string qry = "SELECT email FROM FDMUSER JOIN SESSIONS ON FDMUSER.user_id = SESSIONS.user_id WHERE session_guid = '" + sessionID.ToString() + "'";
ReadOneCommand cmd = new ReadOneCommand();
string userEmail = cmd.Execute(qry);
DbEmail email = new DbEmail();
email.Sender = userEmail;
email.Recipient = recipientEmail;
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
}
public bool Execute(Guid sessionID, string recipientEmail)
{
try
{
// Get userEmail
string qry = "SELECT email FROM FDMUSER JOIN SESSIONS ON FDMUSER.user_id = SESSIONS.user_id WHERE session_guid = '" + sessionID.ToString() + "'";
ReadOneCommand cmd = new ReadOneCommand();
string userEmail = cmd.Execute(qry);
DbEmail email = new DbEmail();
email.Sender = userEmail;
email.Recipient = recipientEmail;
return true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
}
public string Read(string tstatus)
{
string cmdString = "SELECT tstatus_id FROM TRAINEE_STATUS WHERE TSTATUSTEXT = '" + tstatus + "'";
IReadOneCommand cmd = new ReadOneCommand();
return cmd.Execute(cmdString).ToString();
}
public string Read(string status)
{
string cmdString = "SELECT status_id FROM Status WHERE StatusText = '" + status + "'";
IReadOneCommand cmd = new ReadOneCommand();
return cmd.Execute(cmdString).ToString();
}
public string Read(int statusId)
{
string cmdString = "SELECT StatusText FROM Status WHERE status_id = '" + statusId + "'";
IReadOneCommand cmd = new ReadOneCommand();
return cmd.Execute(cmdString).ToString();
}
public string Read(int userId)
{
string cmdString = "SELECT SUBSTR(firstname||' '||lastname,0,30) FROM FDMUser WHERE user_id = '" + userId + "'";
IReadOneCommand cmd = new ReadOneCommand();
return cmd.Execute(cmdString).ToString();
}