//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();
}
}
}
