//When the button is clicked the message is sent to the message table on the database and the new message field is updated on the Firends list protected void Button1_Click(object sender, EventArgs e) { if (!String.IsNullOrEmpty(TextBox1.Text.ToString().Trim())) { SqlConnection conn = null; SqlCommand com; string queryString; try { conn = ConnectionSQL.connectDB(); queryString = "INSERT INTO Messages(time,sender_id,receiver_id,text) VALUES(CURRENT_TIMESTAMP,'" + sessionp + "','" + chatp + "',@Text)"; com = new SqlCommand(queryString, conn); com.Parameters.AddWithValue("@Text", TextBox1.Text); com.ExecuteNonQuery(); } catch (SqlException ex) { Response.Write(ex.Message); } finally { if (conn != null) { conn.Close(); } } TextBox1.Text = ""; //Updating the new message value on the Friends table try { conn.Open(); queryString = "UPDATE Friends SET NewMessage=1 WHERE (profile_id = '" + chatp + "' AND friend_id = '" + sessionp + "') "; com = new SqlCommand(queryString, conn); com.ExecuteNonQuery(); } catch (SqlException ex) { Response.Write(ex.Message); } finally { if (conn != null) { conn.Close(); } } UpdateMessages(); } }
protected void Page_Load(object sender, EventArgs e) { if (HttpContext.Current.Session["userid"] == null) { Response.Redirect("Default.aspx"); } //Getting user sessions form the previous page sessionp = HttpContext.Current.Session["userid"].ToString(); chatp = Request.QueryString["id"]; Literal2.Text = "<span id=\"current-user\"><a href=\"Profile.aspx?id=" + sessionp + "\">" + HttpContext.Current.Session["username"] + "</a></span>"; // This connection is to retrieve the name of the person you are chatting with SqlConnection conn = null; SqlCommand com; string queryString; try { conn = ConnectionSQL.connectDB(); queryString = "SELECT * FROM Profile WHERE profile_id ='" + chatp + " '"; com = new SqlCommand(queryString, conn); SqlDataReader dr = com.ExecuteReader(); dr.Read(); Label1.Text = dr["fullname"].ToString(); Page.Title = "Chat with " + dr["fullname"].ToString();; } catch (SqlException ex) { Response.Write(ex.Message); } finally { if (conn != null) { conn.Close(); } } UpdateMessages(); }
/// <summary> /// This method updates the messages, retireves them form the database and populates the message field; /// </summary> public void UpdateMessages() { //Getting messages from database, selecting by the relevant users. ArrayList inbox = new ArrayList(); SqlConnection conn = null; SqlCommand com; string queryString; int x = 0; try { conn = ConnectionSQL.connectDB(); queryString = "SELECT * FROM Messages WHERE (sender_id = '" + sessionp + "' AND receiver_id = '" + chatp + "') OR (sender_id = '" + chatp + "' AND receiver_id = '" + sessionp + "') ORDER BY Time DESC"; com = new SqlCommand(queryString, conn); SqlDataReader dr = com.ExecuteReader(); //Creating message objects to hold values and inputing them in to an arraylist (necessary to be flexible with the number of messages) while (dr.Read() && x <= 50) { Message mes = new Message(); mes.sender = dr["sender_id"].ToString(); mes.receiver = dr["receiver_id"].ToString(); mes.time = dr["Time"].ToString(); mes.message = dr["Text"].ToString(); inbox.Add(mes); x++; } } catch (SqlException ex) { Response.Write(ex.Message); } finally { if (conn != null) { conn.Close(); } } //Literal is used to display messages, the message class creates the div so the page can be updated with messages accordingly Literal1.Text = ""; for (int i = x - 1; i >= 0; i--) { //this is testing code!!! Remove after! //iteral1.Text += "<div class='speech-bubble'> <div class='message'> Hey bro, how are you?</div><div class='time'>" + DateTime.Now + "</div></div>"; //Literal1.Text += "<div class='speech-bubble2'> <div class='message'> What???</div><div class='time'>" + DateTime.Now + "</div></div>"; //Testing code ends here Message mes = (Message)inbox[i]; if (mes.sender.Equals(sessionp)) { Literal1.Text += mes.PostSender(); } else { Literal1.Text += mes.PostReceiver(); } } //Updating new message fields try { conn.Open(); queryString = "UPDATE Friends SET NewMessage=0 WHERE (profile_id = '" + sessionp + "' AND friend_id = '" + chatp + "') "; com = new SqlCommand(queryString, conn); com.ExecuteNonQuery(); } catch (SqlException ex) { Response.Write(ex.Message); } finally { if (conn != null) { conn.Close(); } } }
/// <summary> /// creates an account and check whether the email is already used and then redirects the user to their profile /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_SignUp_Click(object sender, EventArgs e) { try { //creating a sqlConnection object through one of our classes SqlConnection conn = ConnectionSQL.ConnectDB(); SqlCommand com; //query string that will help validate if an email is already taken string validateInfo = "SELECT COUNT(profile_id) FROM Profile WHERE email = @email"; com = new SqlCommand(validateInfo, conn); com.Parameters.AddWithValue("@email", txtEmail.Text); //had to use executeScalar instead of executeNonQuery() since execute returns first column but as a object //so we had to cast it into int. If it 0 thats how we know that email is not taken int x = (int)com.ExecuteScalar(); if (x == 0) { //once everything checks out the user account is created/inserted into the database string queryString = "INSERT INTO Profile(num_of_friends, job, company, school, profile_pic, profile_status, sex, email, password,city, province, age, fullname) VALUES(@num_of_friends, @job, @company, @school,@profile_pic, @profile_status, @gender, @email,@password,@city,@prov, @age,@name)"; com = new SqlCommand(queryString, conn); //arguments were adding to the query com.Parameters.AddWithValue("@num_of_friends", 0); com.Parameters.AddWithValue("@job", txtJob.Text); com.Parameters.AddWithValue("@company", txtComp.Text); com.Parameters.AddWithValue("@school", txtSchool.Text); com.Parameters.AddWithValue("@profile_pic", txtImg.Text); com.Parameters.AddWithValue("@profile_status", txtStatus.Value); com.Parameters.AddWithValue("@gender", GenderDropDown.Text); com.Parameters.AddWithValue("@name", txtName.Text); com.Parameters.AddWithValue("@password", txtPassword.Text); com.Parameters.AddWithValue("@email", txtEmail.Text); com.Parameters.AddWithValue("@city", txtCity.Text); com.Parameters.AddWithValue("@prov", txtProv.Text); com.Parameters.AddWithValue("@age", DropDownAge.Text); int i = com.ExecuteNonQuery(); //if i is greater than 1 than we know that the account has been inserted/created and we then go to the user account //else we prompt the user that issue has occur with their account creation if (i > 0) { try { conn = ConnectionSQL.ConnectDB(); //select the nearly created account using the where clause with email as a condition queryString = "SELECT profile_id, fullname, password FROM Profile WHERE email = @email"; com = new SqlCommand(queryString, conn); com.Parameters.Add("@email", System.Data.SqlDbType.VarChar); com.Parameters["@email"].Value = txtEmail.Text; SqlDataReader dr = com.ExecuteReader(); dr.Read(); //storing profile id and fullname into session and redirecting the user to their profile page HttpContext.Current.Session["userid"] = dr["profile_id"].ToString(); HttpContext.Current.Session["username"] = dr["fullname"].ToString(); Response.Redirect("Profile.aspx"); } //this catches any sqlExceptions that occurs and lets the user know what has happened catch (SqlException ex) { Response.Write(ex.Message); } //closes the database connection finally { conn.Close(); } } //this occurs when the query to create an account returns less than 1. The user is prompted to what has happened else { //label is then put to visible showing the error Label4.Visible = true; Label4.Text = "Unable to create an account try again"; } } //this error occurs when the user enters an email that is already taken else { //label is then put to visible showing the error Label4.Visible = true; Label4.Text = "Account already exist"; } } catch (SqlException ex) { //label is then put to visible showing the error string error = ex.Message; Label4.Visible = true; Label4.Text = error; } }
/// <summary> /// This method represents the button click event for when the user presses the add new contact button /// </summary> /// <author> /// Khoa Tran /// </author> protected void add_contact_btn_Click(object sender, EventArgs e) { //used to store if the contact the user tries to add exists or not Boolean match = false; int friend_id = 0; try { conn = ConnectionSQL.ConnectDB(); queryString = "SELECT profile_id from Profile where email = @email"; com = new SqlCommand(queryString, conn); com.Parameters.Add("@email", System.Data.SqlDbType.VarChar); com.Parameters["@email"].Value = new_contact_txtbox.Text; SqlDataReader dr = com.ExecuteReader(); //if the contact email the user entered matches one in the database //email is a unique field so one one record will be retrieved if (dr.HasRows) { dr.Read(); friend_id = int.Parse(dr["profile_id"].ToString()); match = true; } //the contact email the user provided does not match one in the database else { //alert the user using javascript that a match was not found Literal3.Text = "<script>alert('No match found!');</script>"; } } catch (SqlException ex) { Response.Write(ex.Message); } finally { if (conn != null) { conn.Close(); } } //a valid contact email was provided if (match) { // add the contact as a friend queryString = "INSERT INTO dbo.Friends (profile_id,friend_id) VALUES (@profile_id,@friend_id);"; using (conn = ConnectionSQL.ConnectDB()) { com = new SqlCommand(queryString, conn); com.Parameters.Add("@profile_id", System.Data.SqlDbType.Int); com.Parameters["@profile_id"].Value = int.Parse(HttpContext.Current.Session["userid"].ToString()); com.Parameters.Add("@friend_id", System.Data.SqlDbType.Int); com.Parameters["@friend_id"].Value = friend_id; try { com.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { conn.Close(); } } // add the user as a friend of the contact using (conn = ConnectionSQL.ConnectDB()) { com = new SqlCommand(queryString, conn); com.Parameters.Add("@profile_id", System.Data.SqlDbType.Int); com.Parameters["@profile_id"].Value = friend_id; com.Parameters.Add("@friend_id", System.Data.SqlDbType.Int); com.Parameters["@friend_id"].Value = int.Parse(HttpContext.Current.Session["userid"].ToString()); try { com.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { conn.Close(); } } } }
/// <summary> /// This method is used to retrieve and display all the users contacts /// as well as their latest messages and message information /// ex. message sent time, message status (sent, read, unread) /// </summary> /// <author> /// Khoa Tran /// </author> protected void UpdateContactList() { Literal1.Text = ""; try { conn = ConnectionSQL.ConnectDB(); queryString = "SELECT f.friend_id, f.newMessage, p.fullname, p.profile_pic, c3.sender_id, c3.text, c3.time FROM dbo.Friends f LEFT OUTER JOIN dbo.Profile p ON f.friend_id = p.profile_id LEFT OUTER JOIN (select c2.friend_id, c2.message_id, m.sender_id, m.text, m.time FROM (select c1.friend_id, max(c1.message_id) AS message_id from(select message_id, receiver_id AS friend_id from dbo.Messages where sender_id = " + userId + " UNION select message_id, sender_id AS friend_id from dbo.Messages where receiver_id = " + userId + ") c1 GROUP BY c1.friend_id) c2 INNER JOIN dbo.Messages m ON m.message_id = c2.message_id) c3 ON f.friend_id = c3.friend_id WHERE f.profile_id = " + userId + " ORDER BY time DESC;"; com = new SqlCommand(queryString, conn); SqlDataReader dr = com.ExecuteReader(); String readStatus = ""; String message = ""; String time = ""; DateTime messageTime; DateTime currentDateTime = DateTime.Now; String profilePicture = ""; TimeSpan timeSpan; String messageStatus = ""; while (dr.Read()) { //reset readStatus and messageStatus for when user has multiple contacts readStatus = ""; messageStatus = ""; // if the user is the sender of the message if (!dr.IsDBNull(dr.GetOrdinal("sender_id")) && dr["sender_id"].ToString() == userId) { //show a check mark to show that the message was sent messageStatus = "<i class=\"fas fa-check\"></i>"; //if the message has been read by the recipient if (!dr.IsDBNull(dr.GetOrdinal("newMessage")) && dr["newMessage"].ToString() == "False") { readStatus = " read"; } } //the sender of the message is one of the users contacts else { //if the user has not read the message yet if (!dr.IsDBNull(dr.GetOrdinal("newMessage")) && dr["newMessage"].ToString() == "True") { readStatus = " unread"; } } //check if the user has had a chat conversation with this contact if (!dr.IsDBNull(dr.GetOrdinal("text"))) { //this is used to determine how the time should be shown messageTime = DateTime.Parse(dr["time"].ToString()); timeSpan = currentDateTime.Subtract(messageTime); //if message was sent less than a day ago if (timeSpan.TotalDays < 1) { //only show the time as the hour and min (12:00 AM) time = messageTime.ToString("hh:mm tt"); } //if message was sent less than a week ago else if (timeSpan.TotalDays < 7) { //Show the time as the day of the week (Monday) time = messageTime.ToString("dddd"); } //if message was sent less than a year ago else if (timeSpan.TotalDays < 365) { //Show the time as month and day (June 10) time = messageTime.ToString("MMMM dd"); } //if message was sent more than a year ago else { //Show the full date (10/08/2018) time = messageTime.ToString("dd/MM/yyyy"); } message = dr["text"].ToString(); //if the message is too long to display //cut the string and add an ellipses to show //that the message is continued if (message.Length > 100) { message = message.Substring(0, 100) + "..."; } } //there is no existing chat conversation between this user and the contact else { message = ""; time = ""; } //if the contact has a profile picture if (!dr.IsDBNull(dr.GetOrdinal("profile_pic"))) { profilePicture = dr["profile_pic"].ToString(); } //assign them the default profile picture else { profilePicture = "images/profile/default-profile-pic.png"; } //create the html text that holds all the formatted contact information and add it to the literal tag //to display in on the contact aspx page Literal1.Text += "<div class=\"contact\"><a href=\"FriendProfile.aspx?id=" + dr["friend_id"].ToString() + "\"><div class=\"contact-pic\"><img src =\"" + profilePicture + "\" alt=\"Profile Picture\"/></div></a><div class=\"contact-info" + readStatus + "\"><a href=\"Chat.aspx?id=" + dr["friend_id"].ToString() + "\"><div class=\"contact-upper\"><span class=\"contact-name\">" + dr["fullname"].ToString() + "</span><span class=\"last-message-time\">" + time + "</span></div><div class=\"contact-lower\"><span class=\"last-message\">" + message + "</span><span class=\"message-status\">" + messageStatus + "</span></div></a></div></div>"; } } catch (SqlException ex) { Response.Write(ex.Message); } finally { if (conn != null) { conn.Close(); } } }