public List<HickGroupUsers> FetchUsersByGroupId(string groupID)
{
try
{
string constr = ConfigurationManager.ConnectionStrings["HickConnectionString"].ConnectionString.ToString();
List<HickGroupUsers> objlst = new List<HickGroupUsers>();
HickGroupUsers objGroupUsers = null;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = constr;
conn.Open();
StringBuilder sb = new StringBuilder();
//sb.AppendLine("select * from hick_group_users where group_id=" + groupID);
//sb.AppendLine("select gu.user_id,u.Firstname,u.Lastname from hick_group_users as gu inner join Hick_Users as u on u.ID=gu.user_id where gu.group_id=" + groupID);
using (SqlCommand command = new SqlCommand("sp_hick_FetchUsersByGroupId", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@GroupId", groupID);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
objGroupUsers = new HickGroupUsers();
objGroupUsers.UserId = Convert.ToInt32(reader["user_id"]);
objGroupUsers.FullName = Convert.ToString(ecd.DecryptData((reader["Firstname"].ToString()), ecd.GetEncryptType())) + " " + Convert.ToString(ecd.DecryptData((reader["Lastname"]).ToString(), ecd.GetEncryptType()));
objlst.Add(objGroupUsers);
}
}
}
}
return objlst;
}
catch (Exception)
{
throw;
}
}
public List<HickGroups> GetGroups(long currentuserid)
{
try
{
List<HickGroups> objgroupColl = new List<HickGroups>();
string constr = ConfigurationManager.ConnectionStrings["HickConnectionString"].ConnectionString.ToString();
StringBuilder sb = new StringBuilder();
//sb.Append(@"select * from hick_groups where id in (select Distinct id from hick_groups where created_by={0}) ");
//sb.Append(@"OR id in (select Distinct group_id from hick_group_users where user_id={0}) ; ");
//string query = sb.ToString();
//query = string.Format(query, currentuserid);
using (SqlConnection conn = new SqlConnection(constr))
{
conn.ConnectionString = constr;
conn.Open();
using (SqlCommand command = new SqlCommand("sp_hick_FetchGroupsForCurUsr", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@UsrId", currentuserid);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
HickGroups objgrp = new HickGroups();
objgrp.Id = Convert.ToInt32(reader["id"]);
objgroupColl.Add(objgrp);
}
}
}
}
if (objgroupColl.Count > 0)
{
for (int i = 0; i < objgroupColl.Count; i++)
{
long groupid = objgroupColl[i].Id;
//sb = new StringBuilder();
//sb.AppendLine("SELECT * FROM hick_group_users AS GU");
//sb.AppendLine("INNER JOIN Hick_Users AS U ON GU.user_id=U.ID");
//sb.AppendLine("WHERE GU.group_id=" + objgroupColl[i].Id + "");
using (SqlCommand command = new SqlCommand("sp_hick_FetchUsersOfGroup", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@GroupId", objgroupColl[i].Id);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
objgroupColl[i].GroupUsersColl = new List<HickGroupUsers>();
while (reader.Read())
{
HickGroupUsers objgrpuser = new HickGroupUsers();
objgrpuser.GroupId = Convert.ToInt32(reader["group_id"]);
objgrpuser.UserId = Convert.ToInt32(reader["user_id"]);
objgrpuser.FullName = Convert.ToString(ecd.DecryptData((reader["Firstname"]).ToString(), ecd.GetEncryptType())) + " " + Convert.ToString(ecd.DecryptData((reader["Lastname"]).ToString(), ecd.GetEncryptType()));
if (objgrpuser.UserId == currentuserid && reader["message_status"] != DBNull.Value && Convert.ToInt32(reader["message_status"]) == (int)ReadStatus.UnRead)
{
objgroupColl[i].IsUnreadMessage = true;
}
objgroupColl[i].GroupUsersColl.Add(objgrpuser);
}
}
}
}
sb = null;
//sb = new StringBuilder();
//sb.AppendLine("select * from Hick_user_Conversation UC");
//sb.AppendLine("INNER JOIN Hick_VideoConversation_Log CL on UC.ID=CL.ConversationId");
//sb.AppendLine("where UC.group_id=" + groupid + " and UC.ConversationDate=CONVERT(date, @date)");
//sb.AppendLine("and CL.Status=" + (int)ReadStatus.CallInitiated + "");
using (SqlCommand command = new SqlCommand("sp_hick_FetchVideoCallStatus", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@groupId", groupid);
command.Parameters.AddWithValue("@status", (int)ReadStatus.CallInitiated);
command.Parameters.AddWithValue("@date", DateTime.UtcNow);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (currentuserid != Convert.ToInt32(reader["PeerId"]))
{
objgroupColl[i].IncomingCall = true;
}
}
}
}
}
}
}
}
return objgroupColl;
}
catch (Exception)
{
throw;
}
}
