public List <Models.PrivateMessageModel> GetPrivateMessage(String user)
{
Models.PrivateMessageModel model = null;
List <Models.PrivateMessageModel> privatemessage = new List <Models.PrivateMessageModel>();
String _sql;
using (SqlConnection cn = new SqlConnection(connectionString))
{
_sql = "SELECT * FROM [dbo].[PrivateMessage_Table] WHERE Sender_Username = '******' AND Receiver_Username = '******' OR Sender_Username = '******' AND Receiver_Username = '******' ORDER BY Datetime ASC";
SqlCommand cmd = new SqlCommand(_sql, cn);
cn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
model = new Models.PrivateMessageModel();
model.Id = Int32.Parse(Convert.ToString(rd.GetSqlValue(0)));
model.Sender = Convert.ToString(rd.GetSqlValue(1));
model.Receiver = Convert.ToString(rd.GetSqlValue(2));
model.Datetime = Convert.ToDateTime(Convert.ToString(rd.GetSqlValue(3)));
model.Message = Convert.ToString(rd.GetSqlValue(4));
privatemessage.Add(model);
}
}
return(privatemessage);
}
public bool SavePM(Models.PrivateMessageModel model)
{
String _sql = "";
using (SqlConnection cn = new SqlConnection(connectionString))
{
model.Sender = ViewBag.Username;
model.Datetime = DateTime.Now;
int?id = model.Id;
model.Message = model.Message.Replace("\r\n", "<br />");
if (id == 0)
{
_sql = "INSERT INTO [dbo].[PrivateMessage_Table] VALUES ('"
+ model.Sender + "', '" + model.Receiver + "', '"
+ model.Datetime + "', '" + model.Message + "')";
}
SqlCommand cmd = new SqlCommand(_sql, cn);
cn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
return(false);
}
return(true);
}
}
public bool SaveOm(Models.PrivateMessageModel model)
{
String _sql = "";
using (SqlConnection cn = new SqlConnection(connectionString))
{
model.Datetime = DateTime.Now;
_sql = "INSERT INTO [dbo].[PrivateMessage_Table] VALUES ('"
+ ViewBag.Username + "', '" + model.Receiver + "', '"
+ model.Datetime + "', '" + model.Message + "')";
SqlCommand cmd = new SqlCommand(_sql, cn);
cn.Open();
cmd.ExecuteNonQuery();
}
return(true);
}
public Boolean SavePM(Models.PrivateMessageModel model)
{
return(true);
}
public List <Models.PrivateMessageModel> GetNewPrivateMessage()
{
List <Models.PrivateMessageModel> list = new List <Models.PrivateMessageModel>();
Models.PrivateMessageModel model = null;
String _sql;
int i = 0, n = 0;
using (SqlConnection cn = new SqlConnection(connectionString))
{
_sql = "SELECT * FROM [dbo].[PrivateMessage_Table] WHERE Id IN (SELECT max(Id) FROM [dbo].[PrivateMessage_Table] WHERE Sender_Username = '******' OR Receiver_Username ='******' GROUP BY Sender_Username, Receiver_Username) ORDER BY Id DESC";
/*_sql = "SELECT 'User' = CASE WHEN Sender_Username = '******' THEN Receiver_Username ELSE Sender_Username END FROM [dbo].[PrivateMessage_Table] WHERE Sender_Username = '******' OR Receiver_Username = '******'";*/
SqlCommand cmd = new SqlCommand(_sql, cn);
cn.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
model = new Models.PrivateMessageModel();
model.Id = Int32.Parse(Convert.ToString(rd.GetSqlValue(0)));
model.Sender = Convert.ToString(rd.GetSqlValue(1));
model.Receiver = Convert.ToString(rd.GetSqlValue(2));
model.Datetime = Convert.ToDateTime(Convert.ToString(rd.GetSqlValue(3)));
model.Message = Convert.ToString(rd.GetSqlValue(4));
list.Add(model);
}
for (i = 0; i < list.Count; i++)
{
for (n = i + 1; n < list.Count; n++)
{
if (list[i].Receiver == list[n].Sender && list[i].Sender == list[n].Receiver)
{
if (list[i].Id > list[i + 1].Id)
{
list.RemoveAt(i + 1);
break;
}
else
{
list.RemoveAt(i);
break;
}
}
}
}
for (i = 0; i < list.Count; i++)
{
String temp;
if (list[i].Sender == ViewBag.Username)
{
temp = list[i].Sender;
list[i].Sender = list[i].Receiver;
list[i].Receiver = temp;
}
}
}
return(list);
}
public ActionResult PrivateMessageFormView(Models.PrivateMessageModel send)
{
send.Message = send.Message.Replace("\r\n", "<br />");
SaveOm(send);
return(RedirectToAction("NewPrivateMessageView"));
}
public ActionResult PrivateMessageFormView()
{
Models.PrivateMessageModel userlist = new Models.PrivateMessageModel();
userlist.ReceiverList = GetUser();
return(View(userlist));
}
