public static void ExportChat(string conversationid)
{
try
{
string constr = Utility.DBConnectionString;
List<ConversationLog> objlogColl = new List<ConversationLog>();
ConversationLog objlog = null;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = constr;
conn.Open();
StringBuilder sb = new StringBuilder();
sb.AppendLine("select *,peeruser=(select top 1 Username from Hick_Users where ID=l.peerid) from Hick_Conversation_log as l");
sb.AppendLine("where l.Conversation_Id=" + conversationid + "");
sb.AppendLine("and (l.Message_Type=" + (int)MessageTypes.Text + " OR l.Message_Type=" + (int)MessageTypes.File + ")");
sb.AppendLine("order by l.Id asc");
using (SqlCommand command = new SqlCommand(Convert.ToString(sb), conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
objlog = new ConversationLog();
objlog.Id = Convert.ToInt32(reader["Id"]);
objlog.ConversationId = Convert.ToInt32(reader["Conversation_Id"]);
objlog.Conversation = Convert.ToString(reader["Conversation_log"]);
objlog.ConversationDate = Convert.ToString(reader["Conversation_date"]);
objlog.PeerName = Convert.ToString(reader["peeruser"]);
objlog.MessageType = Convert.ToInt32(reader["Message_Type"]);
objlog.ReadStatus = Convert.ToInt32(reader["Read_Status"]);
DateTime dt = Convert.ToDateTime(reader["Conversation_date"]);
objlog.Time = dt.ToString("hh:mm tt");
objlog.PeerID = Convert.ToInt32(reader["peerid"]);
objlogColl.Add(objlog);
}
}
}
if (objlogColl.Count > 0)
{
JavaScriptSerializer jss = new JavaScriptSerializer();
string output = jss.Serialize(objlogColl);
byte[] buffer = System.Text.Encoding.ASCII.GetBytes(output);
string _filnam = "Chat(" + DateTime.Now.ToShortDateString() + ").txt";
//context.Response.ContentType = "text/plain";
//context.Response.AddHeader("Content-Disposition", "attachment;filename=" + _filnam + "");
//context.Response.OutputStream.Write(buffer, 0, buffer.Length);
//HttpContext.Current.ApplicationInstance.CompleteRequest();
var callingPage = HttpContext.Current;
callingPage.Response.AddHeader("Content-Type", "text/plain");
callingPage.Response.Charset = "";
callingPage.Response.AppendHeader("Content-disposition", "attachment; filename=" + _filnam);
callingPage.Response.Write(output);
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
}
}
catch (Exception)
{
throw;
}
}
public static List<ConversationLog> GetMessages(string conversationid)
{
try
{
string constr = Utility.DBConnectionString;
List<ConversationLog> objlogColl = new List<ConversationLog>();
ConversationLog objlog = null;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = constr;
conn.Open();
using (SqlCommand command = new SqlCommand("select *,peeruser=(select Username from Hick_Users where ID=l.peerid) from Hick_Conversation_log as l where Conversation_date=CONVERT(date,GETDATE())", conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
objlog = new ConversationLog();
objlog.Id = Convert.ToInt32(reader["Id"]);
objlog.ConversationId = Convert.ToInt32(reader["Conversation_Id"]);
objlog.Conversation = Convert.ToString(reader["Conversation_log"]);
//objlog.ConversationDate = Convert.ToInt64(reader["Conversation_date"]);
objlogColl.Add(objlog);
}
return objlogColl;
}
}
}
}
catch (Exception)
{
throw;
}
}
public List<ConversationLog> GetMessages(string conversationid, long currentuserid, string timezone, long peeruserid, long groupid)
{
try
{
string constr = ConfigurationManager.ConnectionStrings["HickConnectionString"].ConnectionString.ToString();
List<ConversationLog> objlogColl = new List<ConversationLog>();
ConversationLog objlog = null;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = constr;
conn.Open();
using (SqlCommand command = new SqlCommand("sp_hick_GetMessages", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ConvId", conversationid);
command.Parameters.AddWithValue("@CurUsrId", currentuserid);
command.Parameters.AddWithValue("@PeerId", peeruserid);
command.Parameters.AddWithValue("@GroupId", groupid);
command.Parameters.AddWithValue("@MsgTypeTxt", (int)MessageTypes.Text);
command.Parameters.AddWithValue("@MagTypeFile", (int)MessageTypes.File);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
objlog = new ConversationLog();
objlog.Id = Convert.ToInt32(reader["Id"]);
objlog.ConversationId = Convert.ToInt32(reader["Conversation_Id"]);
objlog.Conversation = Convert.ToString(reader["Conversation_log"]);
objlog.PeerName = Convert.ToString(ecd.DecryptData(reader["peeruserF"].ToString(), ecd.GetEncryptType()));
objlog.PeerName= objlog.PeerName+" "+ Convert.ToString(ecd.DecryptData(reader["peeruserL"].ToString(), ecd.GetEncryptType()));
objlog.PeerUserName = Convert.ToString(ecd.DecryptData(reader["peerusername"].ToString(),ecd.GetEncryptType()));
objlog.MessageType = Convert.ToInt32(reader["Message_Type"]);
if (reader["Message_To"] != System.DBNull.Value)
{
objlog.MessageTo = Convert.ToInt32(reader["Message_To"]);
}
objlog.ReadStatus = Convert.ToInt32(reader["Read_Status"]);
objlog.TotalChatDuration = Convert.ToString(reader["totaltextchat"]);
DateTime dtraw = Convert.ToDateTime(reader["Conversation_date"]);
objlog.ConversationDate = Convert.ToString(Utility.ConvertDateToLocal(timezone, dtraw).ToShortDateString());
DateTime dt = Utility.ConvertDateToLocal(timezone, dtraw);
objlog.Time = objlog.ConversationDate != DateTime.Now.ToShortDateString() ? dt.ToString("MMM dd") + ", " + dt.ToString("hh:mm tt") : dt.ToString("hh:mm tt");
objlog.PeerID = Convert.ToInt32(reader["peerid"]);
/* creating the path for the received file */
if (objlog.MessageType == (int)MessageTypes.File && objlog.ReadStatus == (int)ReadStatus.Received)
{
string filname = objlog.Conversation;
var imageExtensions = Convert.ToString(ConfigurationManager.AppSettings["SendPictureAllowedExtensions"]).Split(',');
bool fileIsImage = false;
foreach (string extension in imageExtensions)
{
if (filname.ToLower().EndsWith("." + extension.ToLower()))
{
fileIsImage = true;
break;
}
}
string sourcefilepath = string.Empty;
if (fileIsImage)
{
sourcefilepath = "UserFiles/" + objlog.PeerUserName.ToLower() + "/pictures";
}
else
{
sourcefilepath = "UserFiles/" + objlog.PeerUserName.ToLower() + "/documents";
}
string filename = filname;
string isssl = HttpContext.Current.Request.ServerVariables["HTTPS"];
string fileUrl = String.Format("{0}{1}{2}", isssl == "off" ? "http://" : "https://", HttpContext.Current.Request.Url.Host, VirtualPathUtility.ToAbsolute(String.Format("~/{0}", System.IO.Path.Combine(sourcefilepath, filename))));
objlog.ReceivedImagePath = fileUrl;
}
objlogColl.Add(objlog);
}
}
}
if (objlogColl.Count > 0 && groupid == 0)
{
//var cmd = "UPDATE Hick_Conversation_log SET Read_Status=@readstatus WHERE Conversation_Id=@convestnid AND Message_To=@currentuser AND Message_Type=@mesgtyp";
//using (SqlCommand command = new SqlCommand(cmd, conn))
//{
// command.Parameters.AddWithValue("@convestnid", conversationid);
// command.Parameters.AddWithValue("@currentuser", currentuserid);
// command.Parameters.AddWithValue("@readstatus", (int)ReadStatus.Read);
// command.Parameters.AddWithValue("@mesgtyp", (int)MessageTypes.Text);
// command.ExecuteNonQuery();
//}
if (conversationid.Contains(","))
{
for (int i = 0; i < conversationid.Split(',').Length; i++)
{
UpdateReadStatus(long.Parse(conversationid.Split(',')[i]), currentuserid, (int)ReadStatus.Read, (int)MessageTypes.Text);
}
}
else
{
UpdateReadStatus(long.Parse(conversationid), currentuserid, (int)ReadStatus.Read, (int)MessageTypes.Text);
}
}
else if (objlogColl.Count > 0 && groupid != 0)
{
UpdateGroupMessageState(groupid, currentuserid);
}
}
return objlogColl;
}
catch (Exception)
{
throw;
}
}
public List<ConversationLog> GetChatLog(long currentid, long peerid, string logdate, string timezone, long groupid)
{
try
{
List<ConversationLog> objcoll = new List<ConversationLog>();
if (currentid != 0)
{
string constr = ConfigurationManager.ConnectionStrings["HickConnectionString"].ConnectionString.ToString();
ConversationLog objlog = null;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = constr;
conn.Open();
bool isexist = false;
//string selcmd = string.Empty;
//if (groupid == 0)
//{
// selcmd = "select * from Hick_user_Conversation where ((Initiator=" + currentid + " and Answerer=" + peerid + ") or (Initiator=" + peerid + " and Answerer=" + currentid + ")) and ConversationDate='" + Utility.ConvertDateToUTC(timezone, Convert.ToDateTime(logdate)) + "'";
//}
//else
//{
// selcmd = "select * from Hick_user_Conversation where group_id=" + groupid + " and ConversationDate='" + Utility.ConvertDateToUTC(timezone, Convert.ToDateTime(logdate)) + "'";
//}
using (SqlCommand command = new SqlCommand("sp_hick_GetChatLog", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CurrentId", currentid);
command.Parameters.AddWithValue("@PeerId", peerid);
command.Parameters.AddWithValue("@ConvDate", logdate);
if (groupid != 0)
{
command.Parameters.AddWithValue("@GroupId", groupid);
}
else
{
command.Parameters.AddWithValue("@GroupId", string.Empty);
}
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
isexist = true;
while (reader.Read())
{
objlog = new ConversationLog();
objlog.Id = Convert.ToInt32(reader["ID"]);
//objcoll.Add(objlog);
}
}
}
}
if (isexist == true)
{
//StringBuilder cmd = new StringBuilder();
//if (groupid == 0)
//{
// cmd.AppendLine("select *,peeruser=(select Username from Hick_Users where ID=l.peerid) from Hick_Conversation_log as l where Conversation_Id=" + objlog.Id + " order by l.Id asc");
//}
//else
//{
// cmd.AppendLine("select *,peeruser=(select Username from Hick_Users where ID=l.peerid) from Hick_Conversation_log as l");
// cmd.AppendLine("where (l.Conversation_Id=" + objlog.Id + " OR l.Conversation_Id=(select top 1 conversation_id from hick_previous_log where group_id=" + groupid + " and l.Conversation_date<=created_date and Convert(date,created_date)=Convert(date,'" + Utility.ConvertDateToUTC(timezone, Convert.ToDateTime(logdate)) + "')))");
// cmd.AppendLine("order by l.Id asc");
//}
using (SqlCommand command = new SqlCommand("sp_hick_FetchConvLog", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ConverId", objlog.Id);
if (groupid != 0)
{
command.Parameters.AddWithValue("@GroupId", groupid);
}
else
{
command.Parameters.AddWithValue("@GroupId", string.Empty);
}
command.Parameters.AddWithValue("@Date", logdate);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
objlog = new ConversationLog();
objlog.Id = Convert.ToInt32(reader["Id"]);
objlog.ConversationId = Convert.ToInt32(reader["Conversation_Id"]);
objlog.Conversation = Convert.ToString(reader["Conversation_log"]);
objlog.PeerName = ecd.DecryptData((reader["peeruser"]).ToString(), ecd.GetEncryptType());
objlog.MessageType = Convert.ToInt32(reader["Message_Type"]);
objlog.ReadStatus = reader["Read_Status"] != DBNull.Value ? Convert.ToInt32(reader["Read_Status"]) : 0;
DateTime dtraw = Convert.ToDateTime(reader["Conversation_date"]);
objlog.ConversationDate = Convert.ToString(Utility.ConvertDateToLocal(timezone, dtraw));
DateTime dt = Utility.ConvertDateToLocal(timezone, dtraw);
objlog.Time = dt.ToString("MMM dd") + ", " + dt.ToString("hh:mm tt");
objlog.PeerID = Convert.ToInt32(reader["peerid"]);
objcoll.Add(objlog);
}
//return objcoll;
}
}
}
}
}
return objcoll;
}
catch (Exception)
{
throw;
}
}
public List<VideoChatLog> GetVideoChatLog(long currentid, long peerid, string logdate, string timezone, long groupid)
{
try
{
List<VideoChatLog> objvideologcoll = new List<VideoChatLog>();
List<VideoChatLog> objvideologcollnew = new List<VideoChatLog>();
if (currentid != 0)
{
string constr = ConfigurationManager.ConnectionStrings["HickConnectionString"].ConnectionString.ToString();
ConversationLog objlog = null;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = constr;
conn.Open();
bool isexist = false;
//string selcmd = string.Empty;
//if (groupid == 0)
//{
// selcmd = "select * from Hick_user_Conversation where ((Initiator=" + currentid + " and Answerer=" + peerid + ") or (Initiator=" + peerid + " and Answerer=" + currentid + ")) and ConversationDate='" + Utility.ConvertDateToUTC(timezone, Convert.ToDateTime(logdate)) + "'";
//}
//else
//{
// selcmd = "select * from Hick_user_Conversation where group_id=" + groupid + " and ConversationDate='" + Utility.ConvertDateToUTC(timezone, Convert.ToDateTime(logdate)) + "'";
//}
using (SqlCommand command = new SqlCommand("sp_hick_FetchUserConversationForToday", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CurrentUserId", currentid);
command.Parameters.AddWithValue("@PeerId", peerid);
command.Parameters.AddWithValue("@Date",logdate);
command.Parameters.AddWithValue("@GroupId", groupid);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
isexist = true;
while (reader.Read())
{
objlog = new ConversationLog();
objlog.Id = Convert.ToInt32(reader["ID"]);
//objcoll.Add(objlog);
}
}
}
}
if (isexist == true)
{
/* fetching video log history*/
//using (SqlCommand command = new SqlCommand("select *,peeruser=(select Username from Hick_Users where ID=l.peerid) from Hick_VideoConversation_Log as l where ConversationId=" + objlog.Id + " order by l.ConversationDate asc", conn))
using (SqlCommand command = new SqlCommand("sp_hick_GetVideoChatLog", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ConvId", objlog.Id);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
VideoChatLog objvideolog = new VideoChatLog();
objvideolog.ConversationId = Convert.ToInt32(reader["ConversationId"]);
objvideolog.VideoId = Convert.ToString(reader["VideoId"]);
objvideolog.ParentVideoId = Convert.ToString(reader["ParentVideoId"]);
// objvideolog.ConversationDate = Convert.ToString(reader["ConversationDate"]);
objvideolog.PeerName = ecd.DecryptData(reader["peeruser"].ToString(),ecd.GetEncryptType());
objvideolog.MessageType = Convert.ToInt32(reader["MessageType"]);
objvideolog.Status = Convert.ToInt32(reader["Status"]);
objvideolog.PeerID = Convert.ToInt32(reader["PeerId"]);
//DateTime dtraw = DateTime.ParseExact(reader["ConversationDate"].ToString(), "MM/dd/yyyy hh:mm tt", CultureInfo.InvariantCulture);
//objvideolog.ConversationDate = Convert.ToString(Utility.ConvertDateToLocal(timezone, dtraw));
DateTime dtraw = Convert.ToDateTime(reader["ConversationDate"]);
objvideolog.ConversationDate = Convert.ToString(Utility.ConvertDateToLocal(timezone, dtraw));
objvideolog.ConversationEndTime = reader["ConversationEndTime"] != DBNull.Value ? Convert.ToString(Utility.ConvertDateToLocal(timezone, DateTime.ParseExact(reader["ConversationEndTime"].ToString(), "MM/dd/yyyy hh:mm tt", CultureInfo.InvariantCulture))) : string.Empty;
DateTime dt = Utility.ConvertDateToLocal(timezone, dtraw);
objvideolog.Time = dt.ToString("MMM dd") + ", " + dt.ToString("hh:mm tt");
objvideologcoll.Add(objvideolog);
}
}
//return objcoll;
}
}
}
}
if (objvideologcoll.Count > 0)
{
for (int i = 0; i < objvideologcoll.Count; i++)
{
var isexist = objvideologcollnew.Where(m => m.ParentVideoId == objvideologcoll[i].VideoId).FirstOrDefault();
if (isexist == null)
{
if (String.IsNullOrEmpty(objvideologcoll[i].ParentVideoId))
{
objvideologcoll[i].Duration = "00:00:00";
}
else
{
DateTime dt1 = Convert.ToDateTime(objvideologcoll[i].ConversationDate).ToLocalTime();
var objprnt = objvideologcoll.Where(m => m.VideoId == objvideologcoll[i].ParentVideoId).FirstOrDefault();
if (objprnt != null)
{
//DateTime dt2 = Convert.ToDateTime(objprnt.ConversationDate).ToLocalTime();
if (!String.IsNullOrEmpty(objprnt.ConversationEndTime))
{
DateTime dt2 = Convert.ToDateTime(objprnt.ConversationEndTime).ToLocalTime();
TimeSpan duration = new TimeSpan(dt2.Ticks - dt1.Ticks);
objvideologcoll[i].Duration = Convert.ToString(duration).Replace("-", "");
}
else
{
objvideologcoll[i].Duration = "00:00:00";
}
}
}
objvideologcollnew.Add(objvideologcoll[i]);
}
}
}
}
return objvideologcollnew;
}
catch (Exception)
{
throw;
}
}