public DataTable notifications(int userId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_notifications", sqlConn2);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
sda.Fill(ds);
dt = ds.Tables[0];
DataTable dt2 = new DataTable();
DataRow dr2 = null;
//define the columns
dt2.Columns.Add(new DataColumn("NotificationId", typeof(string)));
dt2.Columns.Add(new DataColumn("NotificationType", typeof(string)));
dt2.Columns.Add(new DataColumn("Text", typeof(string)));
dt2.Columns.Add(new DataColumn("Unread", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedTime", typeof(string)));
dt2.Columns.Add(new DataColumn("Link", typeof(string)));
Classes.Date d = new Classes.Date();
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr2 = dt2.NewRow();
//add values to each rows
dr2["NotificationId"] = dt.Rows[i]["NotificationId"].ToString();
dr2["NotificationType"] = dt.Rows[i]["NotificationType"].ToString();
dr2["Unread"] = dt.Rows[i]["Unread"].ToString();
dr2["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
//link
dr2["Link"] = notificationLink(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString()));
//text
dr2["Text"] = notificationText(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString()));
//add the row to DataTable
dt2.Rows.Add(dr2);
}
return(dt2);
}
public DataTable notifications(int userId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_notifications", sqlConn2);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
sda.Fill(ds);
dt = ds.Tables[0];
DataTable dt2 = new DataTable();
DataRow dr2 = null;
//define the columns
dt2.Columns.Add(new DataColumn("NotificationId", typeof(string)));
dt2.Columns.Add(new DataColumn("NotificationType", typeof(string)));
dt2.Columns.Add(new DataColumn("Text", typeof(string)));
dt2.Columns.Add(new DataColumn("Unread", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedTime", typeof(string)));
dt2.Columns.Add(new DataColumn("Link", typeof(string)));
Classes.Date d = new Classes.Date();
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr2 = dt2.NewRow();
//add values to each rows
dr2["NotificationId"] = dt.Rows[i]["NotificationId"].ToString();
dr2["NotificationType"] = dt.Rows[i]["NotificationType"].ToString();
dr2["Unread"] = dt.Rows[i]["Unread"].ToString();
dr2["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
//link
dr2["Link"] = notificationLink(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString()));
//text
dr2["Text"] = notificationText(Convert.ToInt32(dt.Rows[i]["NotificationType"].ToString()), Convert.ToInt64(dt.Rows[i]["ItemId"].ToString()));
//add the row to DataTable
dt2.Rows.Add(dr2);
}
return dt2;
}
public DataTable eventRequestsList(int userId, Int64 eventId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_requestsInfo", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId;
try
{
sda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
}
if (dt.Rows.Count == 0)
{
return(dt);
}
else
{
DataTable dt2 = new DataTable();
DataRow dr = null;
string profilePicUrl = "";
//define the columns
dt2.Columns.Add(new DataColumn("RequestId", typeof(string)));
dt2.Columns.Add(new DataColumn("EventId", typeof(string)));
dt2.Columns.Add(new DataColumn("SenderId", typeof(string)));
dt2.Columns.Add(new DataColumn("FullName", typeof(string)));
dt2.Columns.Add(new DataColumn("Message", typeof(string)));
dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedTime", typeof(string)));
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr = dt2.NewRow();
//add values to each rows
dr["RequestId"] = dt.Rows[i]["RequestId"].ToString();
dr["EventId"] = dt.Rows[i]["EventId"].ToString();
dr["SenderId"] = dt.Rows[i]["UserId"].ToString();
dr["FullName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString();
dr["Message"] = dt.Rows[i]["Message"].ToString();
// time
Classes.Date d = new Classes.Date();
dr["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
// Show profile's photo
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg";
}
else
{
profilePicUrl = "~/Images/nophoto.png";
}
dr["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dt2.Rows.Add(dr);
}
return(dt2);
}
}
public Tuple<int, DataTable> requestsList(int userId)
{
//status codes: 0 no event 1 requests 2 no request
int status = 0;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_requestsUserEvents", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
try
{
sda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
}
if (dt.Rows.Count == 0)
{
return new Tuple<int, DataTable>(status, dt);
}
else
{
DataTable dt2 = new DataTable();
DataRow dr = null;
string profilePicUrl = "";
//define the columns
dt2.Columns.Add(new DataColumn("EventId", typeof(string)));
dt2.Columns.Add(new DataColumn("Name", typeof(string)));
dt2.Columns.Add(new DataColumn("TypeId", typeof(string)));
dt2.Columns.Add(new DataColumn("RemainedTime", typeof(string)));
dt2.Columns.Add(new DataColumn("RequestsCount", typeof(string)));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["Status"].ToString() != "3" && dt.Rows[i]["RequestsCount"].ToString() != "0")
{
//create new row
dr = dt2.NewRow();
//add values to each rows
dr["EventId"] = dt.Rows[i]["EventId"].ToString();
dr["Name"] = dt.Rows[i]["Name"].ToString();
dr["TypeId"] = dt.Rows[i]["TypeId"].ToString();
dr["RequestsCount"] = dt.Rows[i]["RequestsCount"].ToString();
// time
Classes.Date d = new Classes.Date();
dr["RemainedTime"] = d.FormatRemainedDate(dt.Rows[i]["Date"].ToString());
//add the row to DataTable
dt2.Rows.Add(dr);
}
}
if (dt2.Rows.Count == 0)
{
status = 2;
return new Tuple<int, DataTable>(status, dt2);
}
else
{
status = 1;
return new Tuple<int, DataTable>(status, dt2);
}
}
}
public DataTable eventRequestsList(int userId, Int64 eventId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_requestsInfo", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId;
try
{
sda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
}
if (dt.Rows.Count == 0)
{
return dt;
}
else
{
DataTable dt2 = new DataTable();
DataRow dr = null;
string profilePicUrl = "";
//define the columns
dt2.Columns.Add(new DataColumn("RequestId", typeof(string)));
dt2.Columns.Add(new DataColumn("EventId", typeof(string)));
dt2.Columns.Add(new DataColumn("SenderId", typeof(string)));
dt2.Columns.Add(new DataColumn("FullName", typeof(string)));
dt2.Columns.Add(new DataColumn("Message", typeof(string)));
dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedTime", typeof(string)));
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr = dt2.NewRow();
//add values to each rows
dr["RequestId"] = dt.Rows[i]["RequestId"].ToString();
dr["EventId"] = dt.Rows[i]["EventId"].ToString();
dr["SenderId"] = dt.Rows[i]["UserId"].ToString();
dr["FullName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString();
dr["Message"] = dt.Rows[i]["Message"].ToString();
// time
Classes.Date d = new Classes.Date();
dr["PassedTime"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
// Show profile's photo
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg";
}
else
{
profilePicUrl = "~/Images/nophoto.png";
}
dr["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dt2.Rows.Add(dr);
}
return dt2;
}
}
public EventObjectAll eventsFollowing(int userId, Int64 eventId)
{
EventObjectAll allObjects = new EventObjectAll();
if (eventId == 0)
{
eventId = 99999999;
}
Classes.UserInfo ui = new Classes.UserInfo();
int locationId = ui.locationIdByUserId(userId);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_followingEvents", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)
{
return(allObjects);
}
else
{
Classes.Date d = new Classes.Date();
Classes.Nearby n = new Classes.Nearby();
EventObject object1 = new EventObject();
EventObject object2 = new EventObject();
EventObject object3 = new EventObject();
EventObject object4 = new EventObject();
EventObject object5 = new EventObject();
for (int i = 0; i < dt.Rows.Count; i++)
{
string Location = "";
Classes.Locations l = new Classes.Locations();
DataTable dtLocation = l.getLocationInfoByCityId(Convert.ToInt32(dt.Rows[i]["LocationId"].ToString()));
if (dtLocation.Rows.Count == 0)
{
Location = "Not Available!";
}
else
{
Location = dtLocation.Rows[0]["CountryName"].ToString() + " - " + dtLocation.Rows[0]["CityName"].ToString();
}
// Show profile's photo
string profilePicUrl;
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "Files/ProfilesPhotos/" + dt.Rows[i]["OwnerId"].ToString() + "-100.jpg";
}
else
{
profilePicUrl = "Images/nophoto.png";
}
// Rate
int rate = 0;
int rateSufficient = Convert.ToInt32(ConfigurationManager.AppSettings["RateSufficient"].ToString());
int rateCount = Convert.ToInt32(dt.Rows[i]["rateCount"].ToString());
int rateScore = Convert.ToInt32(dt.Rows[i]["rateScore"].ToString());
if (rateCount >= rateSufficient)
{
rate = (20 * rateScore) / rateCount;
}
EventObject myEventObject = new EventObject(
Convert.ToInt64(dt.Rows[i]["EventId"].ToString()),
dt.Rows[i]["Name"].ToString(),
d.FormatRemainedDate(dt.Rows[i]["Date"].ToString()),
Convert.ToInt16(dt.Rows[i]["TypeId"].ToString()),
Convert.ToInt16(dt.Rows[i]["CoverId"].ToString()),
Convert.ToInt32(dt.Rows[i]["OwnerId"].ToString()),
Location,
rate,
dt.Rows[i]["OwnerName"].ToString(),
Convert.ToInt32(dt.Rows[i]["Participants"].ToString() + 1),
Convert.ToInt32(dt.Rows[i]["ParticipantsAccepted"].ToString() + 1),
profilePicUrl);
switch (i)
{
case 0:
{
object1 = myEventObject;
break;
}
case 1:
{
object2 = myEventObject;
break;
}
case 2:
{
object3 = myEventObject;
break;
}
case 3:
{
object4 = myEventObject;
break;
}
case 4:
{
object5 = myEventObject;
break;
}
}
allObjects = new EventObjectAll(
object1,
object2,
object3,
object4,
object5);
}
return(allObjects);
}
}
public EventObjectAll eventsCity(int userId, Int64 eventId, int mode)
{
EventObjectAll allObjects = new EventObjectAll();
string spName = "";
int days = 0;
if (eventId == 0)
{
eventId = 99999999;
}
if (mode == 1)
{
spName = "sp_nearbyCityAll";
}
else if (mode == 2)//day
{
spName = "sp_nearbyCityDate";
days = 1;
}
else if (mode == 3)//week
{
spName = "sp_nearbyCityDate";
days = 7;
}
else if (mode == 4)//month
{
spName = "sp_nearbyCityDate";
days = 31;
}
Classes.UserInfo ui = new Classes.UserInfo();
int locationId = ui.locationIdByUserId(userId);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter(spName, sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@LocationId", SqlDbType.Int).Value = locationId;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId;
sda.SelectCommand.Parameters.Add("@Days", SqlDbType.SmallInt).Value = days;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)
{
return allObjects;
}
else
{
Classes.Date d = new Classes.Date();
Classes.Nearby n = new Classes.Nearby();
EventObject object1 = new EventObject();
EventObject object2 = new EventObject();
EventObject object3 = new EventObject();
EventObject object4 = new EventObject();
EventObject object5 = new EventObject();
string Location = "";
Classes.Locations l = new Classes.Locations();
DataTable dtLocation = l.getLocationInfoByCityId(Convert.ToInt32(locationId));
if (dtLocation.Rows.Count == 0)
{
Location = "Not Available!";
}
else
{
Location = dtLocation.Rows[0]["CountryName"].ToString() + " - " + dtLocation.Rows[0]["CityName"].ToString();
}
for (int i = 0; i < dt.Rows.Count; i++)
{
// Show profile's photo
string profilePicUrl;
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "Files/ProfilesPhotos/" + dt.Rows[i]["OwnerId"].ToString() + "-100.jpg";
}
else
{
profilePicUrl = "Images/nophoto.png";
}
// Rate
int rate = 0;
int rateSufficient = Convert.ToInt32(ConfigurationManager.AppSettings["RateSufficient"].ToString());
int rateCount = Convert.ToInt32(dt.Rows[i]["rateCount"].ToString());
int rateScore = Convert.ToInt32(dt.Rows[i]["rateScore"].ToString());
if (rateCount >= rateSufficient)
{
rate = (20 * rateScore) / rateCount;
}
EventObject myEventObject = new EventObject(
Convert.ToInt64(dt.Rows[i]["EventId"].ToString()),
dt.Rows[i]["Name"].ToString(),
d.FormatRemainedDate(dt.Rows[i]["Date"].ToString()),
Convert.ToInt16(dt.Rows[i]["TypeId"].ToString()),
Convert.ToInt16(dt.Rows[i]["CoverId"].ToString()),
Convert.ToInt32(dt.Rows[i]["OwnerId"].ToString()),
Location,
rate,
dt.Rows[i]["OwnerName"].ToString(),
Convert.ToInt32(dt.Rows[i]["Participants"].ToString() + 1),
Convert.ToInt32(dt.Rows[i]["ParticipantsAccepted"].ToString() + 1),
profilePicUrl);
switch (i)
{
case 0:
{
object1 = myEventObject;
break;
}
case 1:
{
object2 = myEventObject;
break;
}
case 2:
{
object3 = myEventObject;
break;
}
case 3:
{
object4 = myEventObject;
break;
}
case 4:
{
object5 = myEventObject;
break;
}
}
}
allObjects = new EventObjectAll(
object1,
object2,
object3,
object4,
object5);
return allObjects;
}
}
public DataTable eventBoardMessages(Int64 eventId, int ownerId)
{
DataTable dt = new DataTable();
DataTable dtCountries = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_eventBoardMessages", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)// no messages
{
return(dt);
}
else
{
DataTable dt2 = new DataTable();
DataRow dr2 = null;
//define the columns
dt2.Columns.Add(new DataColumn("MessageId", typeof(string)));
dt2.Columns.Add(new DataColumn("IsOwner", typeof(string)));
dt2.Columns.Add(new DataColumn("SenderName", typeof(string)));
dt2.Columns.Add(new DataColumn("SenderId", typeof(string)));
dt2.Columns.Add(new DataColumn("Message", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedDate", typeof(string)));
dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
string profilePicUrl;
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr2 = dt2.NewRow();
//add values to each rows
dr2["MessageId"] = dt.Rows[i]["MessageId"].ToString();
dr2["SenderName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString();
dr2["SenderId"] = dt.Rows[i]["UserId"].ToString();
dr2["Message"] = dt.Rows[i]["Message"].ToString();
Classes.Date d = new Classes.Date();
dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
if (ownerId == Convert.ToInt32(dt.Rows[i]["UserId"].ToString()))
{
dr2["IsOwner"] = "true";
}
else
{
dr2["IsOwner"] = "false";
}
// Show profile's photo
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg";
}
else
{
profilePicUrl = "~/Images/nophoto.png";
}
dr2["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dt2.Rows.Add(dr2);
}
return(dt2);
}
}
public DataTable messageLists(int userId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_messageLists", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
try
{
sda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
}
DataTable dt2 = new DataTable();
DataRow dr2 = null;
//define the columns
dt2.Columns.Add(new DataColumn("MessageListId", typeof(string)));
dt2.Columns.Add(new DataColumn("OtherId", typeof(string)));
dt2.Columns.Add(new DataColumn("FirstName", typeof(string)));
dt2.Columns.Add(new DataColumn("LastName", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedDate", typeof(string)));
dt2.Columns.Add(new DataColumn("Brief", typeof(string)));
dt2.Columns.Add(new DataColumn("Unread", typeof(string)));
dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
dt2.Columns.Add(new DataColumn("NewCount", typeof(string)));
string profilePicUrl;
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr2 = dt2.NewRow();
//add values to each rows
dr2["MessageListId"] = dt.Rows[i]["MessageListId"].ToString();
dr2["OtherId"] = dt.Rows[i]["OtherId"].ToString();
dr2["FirstName"] = dt.Rows[i]["FirstName"].ToString();
dr2["LastName"] = dt.Rows[i]["LastName"].ToString();
dr2["Brief"] = dt.Rows[i]["Brief"].ToString();
dr2["Unread"] = dt.Rows[i]["Unread"].ToString();
dr2["NewCount"] = dt.Rows[i]["NewCount"].ToString();
Classes.Date d = new Classes.Date();
dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
// Show profile's photo
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-220.jpg";
}
else
{
profilePicUrl = "~/Images/ProfilesPhotos/nophoto220.png";
}
dr2["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dt2.Rows.Add(dr2);
}
return dt2;
}
public Tuple<int, DataTable, DataTable, DataTable> showMessages(int userId, int otherId)
{
int status = 0;
DataTable dtUserName = new DataTable();
DataTable dtOtherName = new DataTable();
DataTable dtMessages = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_messagesSenderReceiverInfo", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId;
sda.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId;
//try
//{
sda.Fill(ds);
dtUserName = ds.Tables[0];
dtOtherName = ds.Tables[1];
if (dtUserName.Rows.Count == 0 || dtOtherName.Rows.Count == 0)
{
status = -1;
}
//}
//catch (Exception ex)
//{
//}
//finally
//{
// sqlConn.Close();
// sda.Dispose();
// sqlConn.Dispose();
//}
if (status != -1)
{
DataTable dt1 = new DataTable();
DataSet ds1 = new DataSet();
SqlConnection sqlConn3 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn3);
sda1.SelectCommand.CommandType = CommandType.StoredProcedure;
sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId;
sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId;
sda1.Fill(ds1);
dt1 = ds1.Tables[0];
DataRow dr2 = null;
//define the columns
dtMessages.Columns.Add(new DataColumn("MessageId", typeof(string)));
dtMessages.Columns.Add(new DataColumn("Sender", typeof(string)));
dtMessages.Columns.Add(new DataColumn("Message", typeof(string)));
dtMessages.Columns.Add(new DataColumn("PassedDate", typeof(string)));
dtMessages.Columns.Add(new DataColumn("Unread", typeof(string)));
dtMessages.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
string profilePicUrl;
for (int i = 0; i < dt1.Rows.Count; i++)
{
//create new row
dr2 = dtMessages.NewRow();
//add values to each rows
dr2["MessageId"] = dt1.Rows[i]["MessageId"].ToString();
dr2["Sender"] = dt1.Rows[i]["Sender"].ToString();
dr2["Message"] = dt1.Rows[i]["Message"].ToString();
dr2["Unread"] = dt1.Rows[i]["Unread"].ToString();
Classes.Date d = new Classes.Date();
dr2["PassedDate"] = d.FormatPassedDate(dt1.Rows[i]["SubmitDate"].ToString());
// Show profile's photo
//if (Convert.ToBoolean(dt1.Rows[i]["HasPhoto"].ToString()))
//{
// profilePicUrl = "~/Files/ProfilesPhotos/" + dt1.Rows[i]["UserId"].ToString() + "-220.jpg";
//}
//else
//{
profilePicUrl = "~/Images/ProfilesPhotos/nophoto220.png";
//}
dr2["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dtMessages.Rows.Add(dr2);
}
}
return new Tuple<int, DataTable, DataTable, DataTable>(status, dtUserName, dtOtherName, dtMessages);
}
public Tuple <int, DataTable, DataTable, DataTable> showMessages(int userId, int otherId)
{
int status = 0;
DataTable dtUserName = new DataTable();
DataTable dtOtherName = new DataTable();
DataTable dtMessages = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_messagesSenderReceiverInfo", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId;
sda.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId;
//try
//{
sda.Fill(ds);
dtUserName = ds.Tables[0];
dtOtherName = ds.Tables[1];
if (dtUserName.Rows.Count == 0 || dtOtherName.Rows.Count == 0)
{
status = -1;
}
//}
//catch (Exception ex)
//{
//}
//finally
//{
// sqlConn.Close();
// sda.Dispose();
// sqlConn.Dispose();
//}
if (status != -1)
{
DataTable dt1 = new DataTable();
DataSet ds1 = new DataSet();
SqlConnection sqlConn3 = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn3);
sda1.SelectCommand.CommandType = CommandType.StoredProcedure;
sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId;
sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId;
sda1.Fill(ds1);
dt1 = ds1.Tables[0];
DataRow dr2 = null;
//define the columns
dtMessages.Columns.Add(new DataColumn("MessageId", typeof(string)));
dtMessages.Columns.Add(new DataColumn("Sender", typeof(string)));
dtMessages.Columns.Add(new DataColumn("Message", typeof(string)));
dtMessages.Columns.Add(new DataColumn("PassedDate", typeof(string)));
dtMessages.Columns.Add(new DataColumn("Unread", typeof(string)));
dtMessages.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
string profilePicUrl;
for (int i = 0; i < dt1.Rows.Count; i++)
{
//create new row
dr2 = dtMessages.NewRow();
//add values to each rows
dr2["MessageId"] = dt1.Rows[i]["MessageId"].ToString();
dr2["Sender"] = dt1.Rows[i]["Sender"].ToString();
dr2["Message"] = dt1.Rows[i]["Message"].ToString();
dr2["Unread"] = dt1.Rows[i]["Unread"].ToString();
Classes.Date d = new Classes.Date();
dr2["PassedDate"] = d.FormatPassedDate(dt1.Rows[i]["SubmitDate"].ToString());
// Show profile's photo
//if (Convert.ToBoolean(dt1.Rows[i]["HasPhoto"].ToString()))
//{
// profilePicUrl = "~/Files/ProfilesPhotos/" + dt1.Rows[i]["UserId"].ToString() + "-220.jpg";
//}
//else
//{
profilePicUrl = "~/Images/ProfilesPhotos/nophoto220.png";
//}
dr2["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dtMessages.Rows.Add(dr2);
}
}
return(new Tuple <int, DataTable, DataTable, DataTable>(status, dtUserName, dtOtherName, dtMessages));
}
public DataTable eventBoardMessages(Int64 eventId, int ownerId)
{
DataTable dt = new DataTable();
DataTable dtCountries = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_eventBoardMessages", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@EventId", SqlDbType.BigInt).Value = eventId;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)// no messages
{
return dt;
}
else
{
DataTable dt2 = new DataTable();
DataRow dr2 = null;
//define the columns
dt2.Columns.Add(new DataColumn("MessageId", typeof(string)));
dt2.Columns.Add(new DataColumn("IsOwner", typeof(string)));
dt2.Columns.Add(new DataColumn("SenderName", typeof(string)));
dt2.Columns.Add(new DataColumn("SenderId", typeof(string)));
dt2.Columns.Add(new DataColumn("Message", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedDate", typeof(string)));
dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
string profilePicUrl;
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr2 = dt2.NewRow();
//add values to each rows
dr2["MessageId"] = dt.Rows[i]["MessageId"].ToString();
dr2["SenderName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString();
dr2["SenderId"] = dt.Rows[i]["UserId"].ToString();
dr2["Message"] = dt.Rows[i]["Message"].ToString();
Classes.Date d = new Classes.Date();
dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
if (ownerId == Convert.ToInt32(dt.Rows[i]["UserId"].ToString()))
{
dr2["IsOwner"] = "true";
}
else
{
dr2["IsOwner"] = "false";
}
// Show profile's photo
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-100.jpg";
}
else
{
profilePicUrl = "~/Images/nophoto.png";
}
dr2["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dt2.Rows.Add(dr2);
}
return dt2;
}
}
public DataTable eventslist(int userId, string mode)
{
string spName = "";
switch (mode)
{
case "created":
spName = "sp_eventsCreated";
break;
case "accepted":
spName = "sp_eventsAccepted";
break;
case "requested":
spName = "sp_eventsRequested";
break;
case "bookmarked":
spName = "sp_eventsBookmarked";
break;
}
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter(spName, sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sda.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)
{
return dt;
}
else
{
DataTable dtEvents = new DataTable();
DataRow drEvents = null;
//define the columns
dtEvents.Columns.Add(new DataColumn("EventId", typeof(string)));
dtEvents.Columns.Add(new DataColumn("Name", typeof(string)));
dtEvents.Columns.Add(new DataColumn("RemainedTime", typeof(string)));
dtEvents.Columns.Add(new DataColumn("TypeId", typeof(string)));
Classes.Date d = new Classes.Date();
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
drEvents = dtEvents.NewRow();
//add values to each rows
drEvents["EventId"] = dt.Rows[i]["EventId"].ToString();
drEvents["Name"] = dt.Rows[i]["Name"].ToString();
drEvents["RemainedTime"] = d.FormatRemainedDate(dt.Rows[i]["Date"].ToString());
drEvents["TypeId"] = dt.Rows[i]["TypeId"].ToString();
//add the row to DataTable
dtEvents.Rows.Add(drEvents);
}
return dtEvents;
}
}
public MessageObjectAll getMessages(int userId, int otherId, Int64 messageId)
{
MessageObjectAll allObjects = new MessageObjectAll();
if (messageId == 0)
{
messageId = 999999999;
}
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn);
sda1.SelectCommand.CommandType = CommandType.StoredProcedure;
sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId;
sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId;
sda1.SelectCommand.Parameters.Add("@MessageId", SqlDbType.Int).Value = messageId;
//try
//{
sda1.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda1.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)
{
return allObjects;
}
else
{
MessageObject object1 = new MessageObject();
MessageObject object2 = new MessageObject();
MessageObject object3 = new MessageObject();
MessageObject object4 = new MessageObject();
MessageObject object5 = new MessageObject();
MessageObject object6 = new MessageObject();
MessageObject object7 = new MessageObject();
MessageObject object8 = new MessageObject();
MessageObject object9 = new MessageObject();
MessageObject object10 = new MessageObject();
// profile pic url
int userIdString = 0;
Classes.Date d = new Classes.Date();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (Convert.ToBoolean(dt.Rows[i]["Sender"].ToString()))
{
userIdString = Convert.ToInt32(dt.Rows[i]["OwnerId"].ToString());
}
else
{
userIdString = Convert.ToInt32(dt.Rows[i]["OtherId"].ToString());
}
MessageObject myMessageObject = new MessageObject(
Convert.ToInt32(dt.Rows[i]["MessageId"].ToString()),
Convert.ToBoolean(dt.Rows[i]["Sender"].ToString()),
dt.Rows[i]["Message"].ToString(),
d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()),
Convert.ToBoolean(dt.Rows[i]["Unread"].ToString()),
userIdString);
switch (i)
{
case 0:
{
object10 = myMessageObject;
break;
}
case 1:
{
object9 = myMessageObject;
break;
}
case 2:
{
object8 = myMessageObject;
break;
}
case 3:
{
object7 = myMessageObject;
break;
}
case 4:
{
object6 = myMessageObject;
break;
}
case 5:
{
object5 = myMessageObject;
break;
}
case 6:
{
object4 = myMessageObject;
break;
}
case 7:
{
object3 = myMessageObject;
break;
}
case 8:
{
object2 = myMessageObject;
break;
}
case 9:
{
object1 = myMessageObject;
break;
}
}
allObjects = new MessageObjectAll(
object1,
object2,
object3,
object4,
object5,
object6,
object7,
object8,
object9,
object10);
}
return allObjects;
}
}
public Tuple <int, DataTable> requestsList(int userId)
{
//status codes: 0 no event 1 requests 2 no request
int status = 0;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_requestsUserEvents", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
try
{
sda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
}
if (dt.Rows.Count == 0)
{
return(new Tuple <int, DataTable>(status, dt));
}
else
{
DataTable dt2 = new DataTable();
DataRow dr = null;
string profilePicUrl = "";
//define the columns
dt2.Columns.Add(new DataColumn("EventId", typeof(string)));
dt2.Columns.Add(new DataColumn("Name", typeof(string)));
dt2.Columns.Add(new DataColumn("TypeId", typeof(string)));
dt2.Columns.Add(new DataColumn("RemainedTime", typeof(string)));
dt2.Columns.Add(new DataColumn("RequestsCount", typeof(string)));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["Status"].ToString() != "3" && dt.Rows[i]["RequestsCount"].ToString() != "0")
{
//create new row
dr = dt2.NewRow();
//add values to each rows
dr["EventId"] = dt.Rows[i]["EventId"].ToString();
dr["Name"] = dt.Rows[i]["Name"].ToString();
dr["TypeId"] = dt.Rows[i]["TypeId"].ToString();
dr["RequestsCount"] = dt.Rows[i]["RequestsCount"].ToString();
// time
Classes.Date d = new Classes.Date();
dr["RemainedTime"] = d.FormatRemainedDate(dt.Rows[i]["Date"].ToString());
//add the row to DataTable
dt2.Rows.Add(dr);
}
}
if (dt2.Rows.Count == 0)
{
status = 2;
return(new Tuple <int, DataTable>(status, dt2));
}
else
{
status = 1;
return(new Tuple <int, DataTable>(status, dt2));
}
}
}
public DataTable eventslist(int userId, string mode)
{
string spName = "";
switch (mode)
{
case "created":
spName = "sp_eventsCreated";
break;
case "accepted":
spName = "sp_eventsAccepted";
break;
case "requested":
spName = "sp_eventsRequested";
break;
case "bookmarked":
spName = "sp_eventsBookmarked";
break;
}
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter(spName, sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sda.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)
{
return(dt);
}
else
{
DataTable dtEvents = new DataTable();
DataRow drEvents = null;
//define the columns
dtEvents.Columns.Add(new DataColumn("EventId", typeof(string)));
dtEvents.Columns.Add(new DataColumn("Name", typeof(string)));
dtEvents.Columns.Add(new DataColumn("RemainedTime", typeof(string)));
dtEvents.Columns.Add(new DataColumn("TypeId", typeof(string)));
Classes.Date d = new Classes.Date();
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
drEvents = dtEvents.NewRow();
//add values to each rows
drEvents["EventId"] = dt.Rows[i]["EventId"].ToString();
drEvents["Name"] = dt.Rows[i]["Name"].ToString();
drEvents["RemainedTime"] = d.FormatRemainedDate(dt.Rows[i]["Date"].ToString());
drEvents["TypeId"] = dt.Rows[i]["TypeId"].ToString();
//add the row to DataTable
dtEvents.Rows.Add(drEvents);
}
return(dtEvents);
}
}
public DataTable messageLists(int userId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_messageLists", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
//try
//{
sda.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
//}
DataTable dt2 = new DataTable();
DataRow dr2 = null;
//define the columns
dt2.Columns.Add(new DataColumn("MessageListId", typeof(string)));
dt2.Columns.Add(new DataColumn("OtherId", typeof(string)));
dt2.Columns.Add(new DataColumn("FirstName", typeof(string)));
dt2.Columns.Add(new DataColumn("LastName", typeof(string)));
dt2.Columns.Add(new DataColumn("PassedDate", typeof(string)));
dt2.Columns.Add(new DataColumn("Brief", typeof(string)));
dt2.Columns.Add(new DataColumn("Unread", typeof(string)));
dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
dt2.Columns.Add(new DataColumn("NewCount", typeof(string)));
string profilePicUrl;
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr2 = dt2.NewRow();
//add values to each rows
dr2["MessageListId"] = dt.Rows[i]["MessageListId"].ToString();
dr2["OtherId"] = dt.Rows[i]["OtherId"].ToString();
dr2["FirstName"] = dt.Rows[i]["FirstName"].ToString();
dr2["LastName"] = dt.Rows[i]["LastName"].ToString();
dr2["Brief"] = dt.Rows[i]["Brief"].ToString();
dr2["Unread"] = dt.Rows[i]["Unread"].ToString();
dr2["NewCount"] = dt.Rows[i]["NewCount"].ToString();
Classes.Date d = new Classes.Date();
dr2["PassedDate"] = d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString());
// Show profile's photo
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["OtherId"].ToString() + "-100.jpg";
}
else
{
profilePicUrl = "~/Images/nophoto.png";
}
dr2["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dt2.Rows.Add(dr2);
}
return(dt2);
}
public DataTable requestsList(int userId)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter("sp_eventRequests", sqlConn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
try
{
sda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sda.Dispose();
sqlConn.Dispose();
}
if (dt.Rows.Count == 0)
{
return dt;
}
else
{
DataTable dt2 = new DataTable();
DataRow dr = null;
//define the columns
dt2.Columns.Add(new DataColumn("RequestId", typeof(string)));
dt2.Columns.Add(new DataColumn("EventId", typeof(string)));
dt2.Columns.Add(new DataColumn("EventName", typeof(string)));
dt2.Columns.Add(new DataColumn("ParticipantsRemained", typeof(string)));
dt2.Columns.Add(new DataColumn("SenderId", typeof(string)));
dt2.Columns.Add(new DataColumn("FullName", typeof(string)));
dt2.Columns.Add(new DataColumn("Username", typeof(string)));
dt2.Columns.Add(new DataColumn("Rate", typeof(string)));
dt2.Columns.Add(new DataColumn("Distance", typeof(string)));
dt2.Columns.Add(new DataColumn("Message", typeof(string)));
dt2.Columns.Add(new DataColumn("ProfilePicUrl", typeof(string)));
dt2.Columns.Add(new DataColumn("Gender", typeof(string)));
dt2.Columns.Add(new DataColumn("Age", typeof(string)));
dt2.Columns.Add(new DataColumn("City", typeof(string)));
dt2.Columns.Add(new DataColumn("RemainedTime", typeof(string)));
int rateCount, rateScore, popularity;
string rate, profilePicUrl;
int rateSufficient = Convert.ToInt32(ConfigurationManager.AppSettings["RateSufficient"].ToString());
for (int i = 0; i < dt.Rows.Count; i++)
{
//create new row
dr = dt2.NewRow();
//add values to each rows
dr["RequestId"] = dt.Rows[i]["RequestId"].ToString();
dr["EventId"] = dt.Rows[i]["EventId"].ToString();
dr["EventName"] = dt.Rows[i]["Name"].ToString();
dr["ParticipantsRemained"] = dt.Rows[i]["ParticipantsRemained"].ToString();
dr["SenderId"] = dt.Rows[i]["UserId"].ToString();
dr["FullName"] = dt.Rows[i]["FirstName"].ToString() + " " + dt.Rows[i]["LastName"].ToString();
dr["Username"] = dt.Rows[i]["Username"].ToString();
dr["Distance"] = "111"; //////////////////////////////////
dr["Message"] = dt.Rows[i]["Message"].ToString();
dr["Gender"] = dt.Rows[i]["Gender"].ToString();
dr["Age"] = "25"; //////////////////////////////////
dr["City"] = "Helsinki"; //////////////////////////////////
// time
Classes.Date d = new Classes.Date();
dr["RemainedTime"] = d.FormatRemainedDate(dt.Rows[i]["Date"].ToString());
// Rate
rateCount = Convert.ToInt32(dt.Rows[i]["rateCount"].ToString());
rateScore = Convert.ToInt32(dt.Rows[i]["rateScore"].ToString());
if (rateCount >= rateSufficient)
{
popularity = (20 * rateScore) / rateCount;
rate = popularity.ToString();
}
else
{
rate = "NA";
}
dr["Rate"] = rate;
// Show profile's photo
if (Convert.ToBoolean(dt.Rows[i]["HasPhoto"].ToString()))
{
profilePicUrl = "~/Files/ProfilesPhotos/" + dt.Rows[i]["UserId"].ToString() + "-220.jpg";
}
else
{
profilePicUrl = "~/Images/ProfilesPhotos/nophoto220.png";
}
dr["ProfilePicUrl"] = profilePicUrl;
//add the row to DataTable
dt2.Rows.Add(dr);
}
return dt2;
}
}
public MessageObjectAll getMessages(int userId, int otherId, Int64 messageId)
{
MessageObjectAll allObjects = new MessageObjectAll();
if (messageId == 0)
{
messageId = 999999999;
}
DataTable dt = new DataTable();
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString);
SqlDataAdapter sda1 = new SqlDataAdapter("sp_messages", sqlConn);
sda1.SelectCommand.CommandType = CommandType.StoredProcedure;
sda1.SelectCommand.Parameters.Add("@OwnerId", SqlDbType.Int).Value = userId;
sda1.SelectCommand.Parameters.Add("@OtherId", SqlDbType.Int).Value = otherId;
sda1.SelectCommand.Parameters.Add("@MessageId", SqlDbType.Int).Value = messageId;
//try
//{
sda1.Fill(ds);
dt = ds.Tables[0];
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConn.Close();
sda1.Dispose();
sqlConn.Dispose();
//}
if (dt.Rows.Count == 0)
{
return(allObjects);
}
else
{
MessageObject object1 = new MessageObject();
MessageObject object2 = new MessageObject();
MessageObject object3 = new MessageObject();
MessageObject object4 = new MessageObject();
MessageObject object5 = new MessageObject();
MessageObject object6 = new MessageObject();
MessageObject object7 = new MessageObject();
MessageObject object8 = new MessageObject();
MessageObject object9 = new MessageObject();
MessageObject object10 = new MessageObject();
// profile pic url
int userIdString = 0;
Classes.Date d = new Classes.Date();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (Convert.ToBoolean(dt.Rows[i]["Sender"].ToString()))
{
userIdString = Convert.ToInt32(dt.Rows[i]["OwnerId"].ToString());
}
else
{
userIdString = Convert.ToInt32(dt.Rows[i]["OtherId"].ToString());
}
MessageObject myMessageObject = new MessageObject(
Convert.ToInt32(dt.Rows[i]["MessageId"].ToString()),
Convert.ToBoolean(dt.Rows[i]["Sender"].ToString()),
dt.Rows[i]["Message"].ToString(),
d.FormatPassedDate(dt.Rows[i]["SubmitDate"].ToString()),
Convert.ToBoolean(dt.Rows[i]["Unread"].ToString()),
userIdString);
switch (i)
{
case 0:
{
object10 = myMessageObject;
break;
}
case 1:
{
object9 = myMessageObject;
break;
}
case 2:
{
object8 = myMessageObject;
break;
}
case 3:
{
object7 = myMessageObject;
break;
}
case 4:
{
object6 = myMessageObject;
break;
}
case 5:
{
object5 = myMessageObject;
break;
}
case 6:
{
object4 = myMessageObject;
break;
}
case 7:
{
object3 = myMessageObject;
break;
}
case 8:
{
object2 = myMessageObject;
break;
}
case 9:
{
object1 = myMessageObject;
break;
}
}
allObjects = new MessageObjectAll(
object1,
object2,
object3,
object4,
object5,
object6,
object7,
object8,
object9,
object10);
}
return(allObjects);
}
}
