public IHttpActionResult PostJobOutbox([FromBody] JobRequest response)
{
try
{
string email_address = response.email_address;
string date_last_refreshed = response.date_last_refreshed;
DateTime job_due_dateTime = Convert.ToDateTime(date_last_refreshed);
string MySQLFormatDate = job_due_dateTime.ToString("yyyy-MM-dd HH:mm:ss");
if (stringValidate(email_address))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("SELECT user.first_name, user.last_name, job.email_address_receiver, user.profile_picture, job.job_id, job.job_title, job.job_description, job.job_status, job.job_date_created, job.job_date_modified, job.job_due_date, user.user_report FROM opensouce.job, opensouce.user WHERE job.email_address_receiver=user.email_address AND job.email_address_sender='" + email_address + "' AND job.job_date_modified > '" + MySQLFormatDate + "';");
if (reader.HasRows)
{
JobList jobList = new JobList();
while (reader.Read())
{
Job sendJob = new Job();
sendJob.receiver_first_name = reader.GetString(0);
sendJob.receiver_last_name = reader.GetString(1);
sendJob.email_address_receiver = reader.GetString(2);
sendJob.profile_picture = reader.GetString(3);
sendJob.job_id = reader.GetString(4);
sendJob.job_title = reader.GetString(5);
sendJob.job_description = reader.GetString(6);
sendJob.job_status = reader.GetString(7);
sendJob.job_date_created = reader.GetString(8);
sendJob.job_date_modified = reader.GetString(9);
sendJob.job_due_date = reader.GetString(10);
int user_report = reader.GetInt32(11);
jobList.Jobs.Add(sendJob);
}
dbConnection.closeConnection();
return(Ok(jobList));
}
else
{
dbConnection.closeConnection();
return(Ok(new Job()));
}
}
else
{
return(Ok(new Job()));
}
}
catch (MySqlException ex)
{
return(NotFound());
}
}
public IHttpActionResult PostLogin([FromBody] User user)//remove login message
{
try
{
if (user.Equals(null))
{
return(Ok(new Login(false, "User can not be equal to null")));
}
System.String email_address = user.email_address;
System.String password = user.password;
if (stringValidate(email_address) && stringValidate(password))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("SELECT user.email_address, user.first_name, user.last_name, user.profile_picture, user.user_status, user.user_report FROM opensouce.user where user.email_address='" + email_address + "' AND user.password='******' limit 1;");
reader.Read();
if (reader.HasRows)
{
User sendUser = new User();
sendUser.email_address = reader.GetString(0);
sendUser.first_name = reader.GetString(1);
sendUser.last_name = reader.GetString(2);
sendUser.profile_picture = reader.GetString(3);
sendUser.user_status = reader.GetString(4);
sendUser.user_report = reader.GetInt32(5);
dbConnection.closeConnection();
if (sendUser.user_report >= 5)
{
return(Ok(new Login(false, "Too many reports")));
}
else
{
return(Ok(sendUser));
}
}
else
{
dbConnection.closeConnection();
return(Ok(new Login(false, "Login Details Incorrect")));
}
}
else
{
return(Ok(new Login(false, "No null or empty values")));
}
}
catch (MySqlException ex)
{
return(Ok(new Login(false, ex.Message)));
}
}
public IHttpActionResult PostNewJob([FromBody] Job newjob)
{
try
{
if (newjob.Equals(null))
{
return(Ok(new StringValue("Job can not be equal to null")));
}
string email_address_sender = newjob.email_address_sender;
string email_address_receiver = newjob.email_address_receiver;
string job_title = newjob.job_title;
string job_description = newjob.job_description;
string job_status = "Pending";
string job_due_date = newjob.job_due_date;
DateTime job_due_dateTime = Convert.ToDateTime(job_due_date);
string MySQLFormatDate = job_due_dateTime.ToString("yyyy-MM-dd HH:mm:ss");
if (stringValidate(email_address_sender) && stringValidate(email_address_receiver) && stringValidate(job_title) && stringValidate(job_description))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("INSERT INTO opensouce.job (email_address_sender, email_address_receiver, job_title, job_description, job_status, job_date_modified, job_due_date) VALUES ('" + email_address_sender + "', '" + email_address_receiver + "', '" + job_title + "', '" + job_description + "', '" + job_status + "', now(), '" + MySQLFormatDate + "');");
reader.Read();
StringValue response = new StringValue();
if (reader.RecordsAffected == 1)
{
response.value1 = "done";
dbConnection.closeConnection();
return(Ok(response));
}
else
{
response.value1 = "failed";
dbConnection.closeConnection();
return(Ok(response));
}
}
else
{
return(Ok(new StringValue("Invalid format")));
}
}
catch (MySqlException ex)
{
return(Ok(new StringValue("MySqlException" + ex.Message)));
}
catch (NullReferenceException ex)
{
return(Ok(new StringValue("NullReferenceException" + ex.Message)));
}
}
public IHttpActionResult PostAddFavourite([FromBody] StringValue query)
{
try
{
if (query.Equals(null))
{
return(Ok(new Login(false, "Query can not be equal to null")));
}
string value1 = query.value1;
string value2 = query.value2;
if (stringValidate(value1) && stringValidate(value2))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("INSERT INTO opensouce.favourites (favourites.email_address1, favourites.email_address2) VALUES ('" + value1 + "', '" + value2 + "');");
reader.Read();
StringValue response = new StringValue();
if (reader.RecordsAffected == 1)
{
response.value1 = "done";
dbConnection.closeConnection();
return(Ok(response));
}
else
{
response.value1 = "failed";
dbConnection.closeConnection();
return(Ok(response));
}
}
else
{
return(Ok(new User()));
}
}
catch (MySqlException ex)
{
return(NotFound());
}
catch (NullReferenceException ex)
{
return(NotFound());
}
}
public IHttpActionResult PostJobStatus([FromBody] StringValue query)
{
try
{
string job_id = query.value1;
string job_status = query.value2;
if (stringValidate(job_id) && stringValidate(job_status))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("UPDATE opensouce.job SET job_status='" + job_status + "', job_date_modified=now() WHERE job_id='" + job_id + "';");
reader.Read();
StringValue response = new StringValue();
if (reader.RecordsAffected == 1)
{
response.value1 = job_status;
dbConnection.closeConnection();
return(Ok(response));
}
else
{
response.value1 = "failed";
dbConnection.closeConnection();
return(Ok(response));
}
}
else
{
return(Ok(new User()));
}
}
catch (MySqlException ex)
{
return(NotFound());
}
catch (NullReferenceException ex)
{
return(NotFound());
}
}
public IHttpActionResult PostUserStatus([FromBody] StringValue query)
{
try
{
string user_status = query.value1;
string email_address = query.value2;
if (stringValidate(user_status))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("UPDATE opensouce.user SET user_status='" + user_status + "' WHERE email_address='" + email_address + "';");
reader.Read();
StringValue response = new StringValue();
if (reader.RecordsAffected == 1)
{
response.value1 = user_status;
dbConnection.closeConnection();
return(Ok(response));
}
else
{
response.value1 = "failed";
dbConnection.closeConnection();
return(Ok(response));
}
}
else
{
return(Ok(new User()));
}
}
catch (MySqlException ex)
{
return(NotFound());
}
catch (NullReferenceException ex)
{
return(NotFound());
}
}
public IHttpActionResult PostRegister([FromBody] User user)
{
try
{
if (user.Equals(null))
{
return(Ok(new Login(false, "User can not be equal to null")));
}
string email_address = user.email_address;
string password = user.password;
string first_name = user.first_name;
string last_name = user.last_name;
string course_name = user.course_name;
string year_of_study = user.year_of_study;
string contact_number = user.contact_number;
string profile_picture = user.profile_picture;
string profile_picture_big = user.profile_picture_big;
List <Skill> skills = user.skills;
if (stringValidate(email_address) && stringValidate(password) && stringValidate(first_name) && stringValidate(last_name) && stringValidate(course_name) && stringValidate(year_of_study))
{
MySQLConnection dbConnection1 = new MySQLConnection();
MySqlDataReader reader1 = dbConnection1.getMySqlDataReader("SELECT COUNT(1) FROM opensouce.user where user.email_address='" + email_address + "';");
reader1.Read();
if (reader1.GetString(0) != "1")
{
dbConnection1.closeConnection();
MySQLConnection dbConnection2 = new MySQLConnection();
MySqlDataReader reader2 = dbConnection2.getMySqlDataReader("insert into opensouce.user (user.email_address, user.password, user.first_name, user.last_name, user.course_name, user.year_of_study, user.contact_number, user.profile_picture, user.profile_picture_big) values ('" + email_address + "', '" + password + "', '" + first_name + "', '" + last_name + "', '" + course_name + "', '" + year_of_study + "', '" + contact_number + "', '" + profile_picture + "', '" + profile_picture_big + "');");
int rows = reader2.RecordsAffected;
if (rows == 1)
{
dbConnection2.closeConnection();
int insertCount = 0;
MySqlConnection dbConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString);
dbConnection.Open();
foreach (Skill skill in skills)
{
string skill_string = skill.skill;
MySqlCommand mycmd = new MySqlCommand("insert into opensouce.skill (skill.email_address, skill.skill) values ('" + email_address + "', '" + skill_string + "');", dbConnection);
mycmd.ExecuteNonQuery();
}
if (insertCount == (skills.Count - 1))
{
dbConnection.Close();
return(Ok(new Login(true, "Welcome")));
}
else
{
dbConnection.Close();
return(Ok(new Login(true, "Some skills didn't add")));
}
}
else
{
dbConnection2.closeConnection();
return(Ok(new Login(false, "Failed to register")));
}
}
else
{
dbConnection1.closeConnection();
return(Ok(new Login(false, "Email already exists")));
}
}
else
{
return(Ok(new Login(false, "No null or empty values")));
}
}
catch (MySqlException ex)
{
return(Ok(new Login(false, ex.Message)));
}
catch (Exception ex)
{
return(Ok(new Login(false, ex.Message)));
}
}
public IHttpActionResult PostFavourites([FromBody] User user)
{
try
{
if (user.Equals(null))
{
return(Ok(new Login(false, "User can not be equal to null")));
}
string email_address = user.email_address;
if (stringValidate(email_address))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("SELECT here.email_address, here.first_name, here.last_name, here.profile_picture, here.skill, here.favouriteCount, here.user_status, here.user_report FROM (SELECT user.email_address, user.first_name, user.last_name, user.profile_picture, group_concat(skill.skill SEPARATOR '$') as skill, (SELECT count(*) FROM opensouce.favourites WHERE favourites.email_address2=user.email_address) as favouriteCount, user.user_status, user.user_report FROM opensouce.user left join opensouce.skill on user.email_address=skill.email_address group by user.email_address order by user.first_name) AS here inner join opensouce.favourites on here.email_address=favourites.email_address2 where favourites.email_address1='" + email_address + "' order by here.first_name DESC;");
if (reader.HasRows)
{
UserList userList = new UserList();
while (reader.Read())
{
User sendUser = new User();
sendUser.email_address = reader.GetString(0);
sendUser.first_name = reader.GetString(1);
sendUser.last_name = reader.GetString(2);
sendUser.profile_picture = reader.GetString(3);
string tempSkill = reader.GetString(4);
sendUser.favourite_count = reader.GetString(5);
sendUser.user_status = reader.GetString(6);
sendUser.user_report = reader.GetInt32(7);
char[] delimiterChars = { '$' };
string[] skills = tempSkill.Split(delimiterChars);
List <Skill> templist = new List <Skill>();
foreach (string skill in skills)
{
Skill newSkill = new Skill();
newSkill.skill = skill;
templist.Add(newSkill);
}
sendUser.skills = templist;
if (sendUser.user_report < 5)
{
userList.Users.Add(sendUser);
}
}
dbConnection.closeConnection();
if (userList.Users.Count != 0)
{
return(Ok(userList));
}
else
{
StringValue temp = new StringValue();
temp.value1 = "empty";
return(Ok(temp));
}
}
else
{
StringValue temp = new StringValue();
temp.value1 = "empty";
return(Ok(temp));
}
}
else
{
return(Ok(new User()));
}
}
catch (MySqlException ex)
{
return(NotFound());
}
}
public IHttpActionResult PostSearch([FromBody] StringValue query)
{
try
{
if (query.Equals(null))
{
return(Ok(new Login(false, "Query can not be equal to null")));
}
string value1 = query.value1;
string value2 = query.value2;
if (stringValidate(value1))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("SELECT * FROM (SELECT user.email_address, user.first_name, user.last_name, user.profile_picture, group_concat(skill.skill SEPARATOR '$') as skill, (SELECT count(*) FROM opensouce.favourites WHERE favourites.email_address2=user.email_address) as favouriteCount, user.user_status, user.user_report FROM opensouce.user left join opensouce.skill on user.email_address=skill.email_address WHERE not user.email_address='" + value2 + "' group by user.email_address) AS here WHERE here.email_address like '%" + value1 + "%' or here.first_name like '%" + value1 + "%' or here.last_name like '%" + value1 + "%' or here.skill like '%" + value1 + "%' order by here.favouriteCount DESC;");
if (reader.HasRows)
{
UserList userList = new UserList();
while (reader.Read())
{
User sendUser = new User();
sendUser.email_address = reader.GetString(0);
sendUser.first_name = reader.GetString(1);
sendUser.last_name = reader.GetString(2);
sendUser.profile_picture = reader.GetString(3);
string tempSkill = reader.GetString(4);
sendUser.favourite_count = reader.GetString(5);
sendUser.user_status = reader.GetString(6);
sendUser.user_report = reader.GetInt32(7);
char[] delimiterChars = { '$' };
string[] skills = tempSkill.Split(delimiterChars);
List <Skill> templist = new List <Skill>();
foreach (string skill in skills)
{
Skill newSkill = new Skill();
newSkill.skill = skill;
templist.Add(newSkill);
}
sendUser.skills = templist;
if (sendUser.user_report < 5)
{
userList.Users.Add(sendUser);
}
}
dbConnection.closeConnection();
return(Ok(userList));
}
else
{
dbConnection.closeConnection();
return(Ok(new User()));
}
}
else
{
return(Ok(new User()));
}
}
catch (MySqlException ex)
{
return(NotFound());
}
catch (NullReferenceException ex)
{
return(NotFound());
}
}
public IHttpActionResult PostUserOffline([FromBody] StringValue query)
{
try
{
if (query.Equals(null))
{
return(Ok(new Login(false, "Query can not be equal to null")));
}
string value2 = query.value2;
if (stringValidate(value2))
{
MySQLConnection dbConnection = new MySQLConnection();
MySqlDataReader reader = dbConnection.getMySqlDataReader("SELECT user.email_address, user.first_name, user.last_name, user.course_name, user.year_of_study, user.contact_number, date(user.date_created), (SELECT count(*) FROM opensouce.favourites WHERE favourites.email_address2='" + value2 + "') as favouriteCount, group_concat(skill.skill SEPARATOR '$') as skill, user.user_status, user.user_report FROM opensouce.user left join opensouce.skill on user.email_address=skill.email_address WHERE user.email_address='" + value2 + "';");
reader.Read();
if (reader.HasRows)
{
User sendUser = new User();
sendUser.email_address = reader.GetString(0);
sendUser.first_name = reader.GetString(1);
sendUser.last_name = reader.GetString(2);
sendUser.course_name = reader.GetString(3);
sendUser.year_of_study = reader.GetString(4);
sendUser.contact_number = reader.GetString(5);
sendUser.date_created = reader.GetString(6);
sendUser.favourite_count = reader.GetString(7);
string tempSkill = reader.GetString(8);
sendUser.user_status = reader.GetString(9);
sendUser.user_report = reader.GetInt32(10);
char[] delimiterChars = { '$' };
string[] skills = tempSkill.Split(delimiterChars);
List <Skill> templist = new List <Skill>();
foreach (string skill in skills)
{
Skill newSkill = new Skill();
newSkill.skill = skill;
templist.Add(newSkill);
}
sendUser.skills = templist;
dbConnection.closeConnection();
return(Ok(sendUser));
}
else
{
dbConnection.closeConnection();
return(Ok(new User()));
}
}
else
{
return(Ok(new User()));
}
}
catch (MySqlException ex)
{
return(NotFound());
}
catch (NullReferenceException ex)
{
return(NotFound());
}
}