Beispiel #1
0
 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());
     }
 }
Beispiel #2
0
 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());
     }
 }
Beispiel #3
0
 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)));
     }
 }
Beispiel #4
0
 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)));
     }
 }
Beispiel #5
0
 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());
     }
 }
Beispiel #6
0
 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());
     }
 }
Beispiel #7
0
 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());
     }
 }
Beispiel #8
0
 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)));
     }
 }
Beispiel #9
0
 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());
     }
 }
Beispiel #10
0
 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());
     }
 }
Beispiel #11
0
 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());
     }
 }