public IHttpActionResult PostSmallPicture([FromBody] StringValue input) { try { string email_address = input.value1; if (stringValidate(email_address)) { MySQLConnection dbConnection = new MySQLConnection(); MySqlDataReader reader = dbConnection.getMySqlDataReader("SELECT user.profile_picture FROM opensouce.user WHERE user.email_address='" + email_address + "';"); reader.Read(); if (reader.HasRows) { StringValue output = new StringValue(); output.value1 = reader.GetString(0); dbConnection.closeConnection(); return(Ok(output)); } else { dbConnection.closeConnection(); return(Ok(new StringValue())); } } else { return(Ok(new StringValue())); } } catch (MySqlException ex) { return(NotFound()); } }
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()); } }