private void GetTwoWeekAgo()
{
SqlCommand cmd;
using (var conn = new SqlConnection((string)Application["connectionString"]))
{
conn.Open();
cmd = new SqlCommand("", conn) {CommandType = CommandType.Text};
switch (Request["type"].ToLower())
{
case "achieved":
cmd.CommandText = "select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,(SELECT count(1) "
+ " FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID "
+ " WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=1 "
+ " ) as MemberCount , (SELECT count(1) FROM TrackedEvents T "
+ " JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid "
+ " AND U.Deleted = 0 and T.showontimeline=0 ) as FollowerCount "
+ " from events inner join users on users.userid=events.userid "
+
" where ( events.EventAchievedDate >= dateadd(day,datediff(day,0,getdate())-14,0) "
+ " and events.EventAchievedDate <= dateadd(day,datediff(day,0,getdate())-7,0) ) "
+ " and events.deleted=0 "
+ " and events.EventAchieved=1 "
+ " and events.PrivateEvent=0 "
+ " ORDER BY events.EventAchievedDate DESC ";
break;
case "latest":
cmd.CommandText = "select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,(SELECT count(1) "
+ " FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID "
+ " WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=1 "
+ " ) as MemberCount , (SELECT count(1) FROM TrackedEvents T "
+ " JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid "
+ " AND U.Deleted = 0 and T.showontimeline=0 ) as FollowerCount "
+ " from events inner join users on users.userid=events.userid "
+ " where ( events.CreatedDate >= dateadd(day,datediff(day,0,getdate())-14,0) "
+ " and events.CreatedDate <= dateadd(day,datediff(day,0,getdate())-7,0) ) "
+ " and events.deleted=0 "
+ " and events.EventAchieved=0 "
+ " and events.PrivateEvent=0 "
+ " ORDER BY events.CreatedDate DESC ";
break;
case "updated":
cmd.CommandText = "select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,(SELECT count(1) "
+ " FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID "
+ " WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=1 "
+ " ) as MemberCount , (SELECT count(1) FROM TrackedEvents T "
+ " JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid "
+ " AND U.Deleted = 0 and T.showontimeline=0 ) as FollowerCount "
+ " from events inner join users on users.userid=events.userid "
+
" where ( events.LastUpdatedDate >= dateadd(day,datediff(day,0,getdate())-14,0) "
+ " and events.LastUpdatedDate <= dateadd(day,datediff(day,0,getdate())-7,0) ) "
+ " and events.deleted=0 "
+ " and events.EventAchieved=0 "
+ " and events.PrivateEvent=0 "
+ " and events.CreatedDate <> events.LastUpdatedDate "
+ " ORDER BY events.LastUpdatedDate DESC ";
break;
case "similar":
using (var sedogoDbEntities = new SedogoDbEntities())
{
var eventId = Convert.ToInt32(Request["EID"]);
var searchWord = sedogoDbEntities.Events.First(x => x.EventID == eventId).EventName;
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=1
) as MemberCount,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=0
) as FollowerCount
from events
inner join users on users.userid=events.userid
where
(
events.LastUpdatedDate >= dateadd(day,datediff(day,0,getdate())-14,0)
and events.LastUpdatedDate <= dateadd(day,datediff(day,0,getdate())-7,0)
) and events.deleted=0 and events.EventAchieved=0 and events.PrivateEvent=0
and FREETEXT(EventName, '" +
searchWord + @"')
ORDER BY events.CreatedDate DESC ";
}
break;
case "other":
var eId = Convert.ToInt32(Request["EID"]);
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=1
) as MemberCount,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=0
) as FollowerCount
from events
inner join users on users.userid=events.userid
where
(
events.LastUpdatedDate >= dateadd(day,datediff(day,0,getdate())-14,0)
and events.LastUpdatedDate <= dateadd(day,datediff(day,0,getdate())-7,0)
) and events.deleted=0 and events.EventAchieved=0 and events.PrivateEvent=0
and events.UserId IN (SELECT UserID FROM Events WHERE EventId = " + eId + @")
ORDER BY events.CreatedDate DESC ";
break;
}
DbDataReader rdrTwoWeekAgo = cmd.ExecuteReader();
if (rdrTwoWeekAgo == null)
{
return;
}
while (rdrTwoWeekAgo.Read())
{
var eventID = int.Parse(rdrTwoWeekAgo["EventID"].ToString());
var eventName = (string) rdrTwoWeekAgo["EventName"];
var fname = (string) rdrTwoWeekAgo["FirstName"];
var lname = (string) rdrTwoWeekAgo["LastName"];
var memCount = rdrTwoWeekAgo["MemberCount"].ToString();
var folCount = rdrTwoWeekAgo["FollowerCount"].ToString();
var eventHyperlink = new HyperLink
{
Text =
GetSubString(eventName, 100) + " <span style=color:grey>" + fname + " " +
lname + "</span>",
NavigateUrl = "~/viewEvent.aspx?EID=" + eventID
};
eventHyperlink.Attributes.Add("class", "event");
PlaceHolderTwoWeekAgo.Controls.Add(eventHyperlink);
var ltEvent6 = new Literal();
var memberFollowerString = " - ";
if (memCount != "0")
{
memberFollowerString += memCount == "1" ? memCount + " member " : memCount + " members ";
}
if (folCount != "0")
{
memberFollowerString += memCount == "1" ? folCount + " follower " : folCount + " followers ";
}
ltEvent6.Text = "<span style=color:#cccccc>" + memberFollowerString + "</span>";
PlaceHolderTwoWeekAgo.Controls.Add(ltEvent6);
PlaceHolderTwoWeekAgo.Controls.Add(new LiteralControl("<br/>"));
}
rdrTwoWeekAgo.Close();
}
}
/// <summary>
/// Creates the similar events.
/// </summary>
private void CreateSimilarEvents()
{
similarPanel.Visible = IsSimilarVisible;
if (!IsSimilarVisible)
{
return;
}
using (var sedogoDbEntities = new SedogoDbEntities())
{
var _event = sedogoDbEntities.Events.FirstOrDefault(x => x.EventID == EventId);
if (_event != null)
{
var searchWord = _event.EventName;
var events = sedogoDbEntities.SelectSimilarEvents(searchWord, EventId);
if (events == null)
{
return;
}
foreach (var @event in events)
{
{
var eventID = int.Parse(@event.EventID.ToString());
var eventName = @event.EventName;
var eventHyperlink = new HyperLink
{
Text = eventName,
NavigateUrl = "~/viewEvent.aspx?EID=" + eventID
};
goalsSimilarPlaceHolder.Controls.Add(eventHyperlink);
goalsSimilarPlaceHolder.Controls.Add(new LiteralControl("<br/>"));
}
}
}
}
var nSimilarHyperlink = new HyperLink
{
Text = "<b>More ></b>",
NavigateUrl = userID > 0
? "~/MoreDetail.aspx?type=similar&EID=" + EventId
: "~/HomeMoreDetail.aspx?type=similar&EID=" + EventId
};
goalsSimilarPlaceHolder.Controls.Add(nSimilarHyperlink);
goalsSimilarPlaceHolder.Controls.Add(new LiteralControl("<br/>"));
}
private void GetToday()
{
var conn = new SqlConnection((string)Application["connectionString"]);
conn.Open();
var cmd = new SqlCommand("", conn) { CommandType = CommandType.Text };
switch (Request["type"].ToLower())
{
case "achieved":
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=1
) as MemberCount,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=0
) as FollowerCount
FROM events
INNER JOIN users on users.userid=events.userid
WHERE convert(datetime,convert(varchar(11),events.EventAchievedDate,102)) = convert(datetime,convert(varchar(11),getdate(),102))
and events.deleted=0 and events.EventAchieved=1 and events.PrivateEvent=0
ORDER BY events.EventAchievedDate DESC ";
break;
case "latest":
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,(SELECT count(1) FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=1 ) as MemberCount , (SELECT count(1) FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=0 ) as FollowerCount from events inner join users on users.userid=events.userid where convert(datetime,convert(varchar(11),events.CreatedDate,102)) = convert(datetime,convert(varchar(11),getdate(),102)) and events.deleted=0 and events.EventAchieved=0 and events.PrivateEvent=0 ORDER BY events.CreatedDate DESC ";
break;
case "updated":
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,(SELECT count(1) FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=1 ) as MemberCount , (SELECT count(1) FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=0 ) as FollowerCount from events inner join users on users.userid=events.userid where convert(datetime,convert(varchar(11),events.LastUpdatedDate,102)) = convert(datetime,convert(varchar(11),getdate(),102)) and events.deleted=0 and events.EventAchieved=0 and events.PrivateEvent=0 and events.CreatedDate <> events.LastUpdatedDate ORDER BY events.LastUpdatedDate DESC ";
break;
case "now":
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,(SELECT count(1) FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=1 ) as MemberCount , (SELECT count(1) FROM TrackedEvents T JOIN Users U ON T.UserID = U.UserID WHERE T.EventID = events.eventid AND U.Deleted = 0 and T.showontimeline=0 ) as FollowerCount from events inner join users on users.userid=events.userid where ( ( RangeStartDate <= getdate() and RangeEndDate >= getdate() ) or convert(varchar(11),StartDate,103) = convert(varchar(11),getdate(),103) ) and events.deleted=0 and events.EventAchieved=0 and events.PrivateEvent=0 ORDER BY events.CreatedDate DESC ";
break;
case "similar":
using (var sedogoDbEntities = new SedogoDbEntities())
{
var eventId = Convert.ToInt32(Request["EID"]);
var searchWord = sedogoDbEntities.Events.First(x => x.EventID == eventId).EventName;
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=1
) as MemberCount,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=0
) as FollowerCount
from events
inner join users on users.userid=events.userid
where
(
(
RangeStartDate <= getdate() and RangeEndDate >= getdate()
)
or convert(varchar(11),StartDate,103) = convert(varchar(11),getdate(),103)
) and events.deleted=0 and events.EventAchieved=0 and events.PrivateEvent=0
and FREETEXT(EventName, '" +
searchWord + @"')
ORDER BY events.CreatedDate DESC ";
}
break;
case "other":
var eId = Convert.ToInt32(Request["EID"]);
cmd.CommandText =
@"select top 20 events.eventid,events.eventname,users.FirstName,users.LastName,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=1
) as MemberCount,
(
SELECT count(1)
FROM TrackedEvents T
JOIN Users U ON T.UserID = U.UserID
WHERE T.EventID = events.eventid
AND U.Deleted = 0
and T.showontimeline=0
) as FollowerCount
from events
inner join users on users.userid=events.userid
where
(
(
RangeStartDate <= getdate() and RangeEndDate >= getdate()
)
or convert(varchar(11),StartDate,103) = convert(varchar(11),getdate(),103)
) and events.deleted=0 and events.EventAchieved=0 and events.PrivateEvent=0
and events.UserId IN (SELECT UserID FROM Events WHERE EventId = " + eId + @")
ORDER BY events.CreatedDate DESC ";
break;
}
DbDataReader rdrToday = cmd.ExecuteReader();
if (rdrToday != null)
{
while (rdrToday.Read())
{
var eventID = int.Parse(rdrToday["EventID"].ToString());
var eventName = (string)rdrToday["EventName"];
var fname = (string)rdrToday["FirstName"];
var lname = (string)rdrToday["LastName"];
var memCount = rdrToday["MemberCount"].ToString();
var folCount = rdrToday["FollowerCount"].ToString();
CreateGoalLink(eventName, fname, lname, eventID, memCount, folCount);
}
rdrToday.Close();
}
}
/// <summary>
/// Creates the other events.
/// </summary>
private void CreateOtherEvents()
{
otherPanel.Visible = IsOtherVisible;
if (!IsOtherVisible)
{
return;
}
int uid;
if (EventId > 0 && IsSimilarVisible && Session["loggedInUserID"] != null && int.TryParse(Session["loggedInUserID"].ToString(), out uid) && uid > 0)
{
var currentEvent = new SedogoEvent(Session["loggedInUserFullName"].ToString(), EventId);
otherPanel.Visible = IsSimilarVisible && userID != currentEvent.userID;
}
else
{
otherPanel.Visible = IsSimilarVisible;
}
if (!IsSimilarVisible)
{
return;
}
using (var sedogoDbEntities = new SedogoDbEntities())
{
var events = sedogoDbEntities.SelectOtherEvents(EventId);
if (events == null)
{
return;
}
bool flag = true;
foreach (var @event in events)
{
if (flag)
{
var user = new SedogoUser("", @event.UserID);
otherGoalsTitle.Text = user.firstName + "'s goals";
flag = false;
}
var eventID = int.Parse(@event.EventID.ToString());
var eventName = @event.EventName;
var eventHyperlink = new HyperLink
{
Text = eventName,
NavigateUrl = "~/viewEvent.aspx?EID=" + eventID
};
goalsOtherPlaceHolder.Controls.Add(eventHyperlink);
goalsOtherPlaceHolder.Controls.Add(new LiteralControl("<br/>"));
}
}
var nSimilarHyperlink = new HyperLink
{
Text = "<b>More ></b>",
NavigateUrl = userID > 0
? "~/MoreDetail.aspx?type=other&EID=" + EventId
: "~/HomeMoreDetail.aspx?type=other&EID=" + EventId
};
goalsOtherPlaceHolder.Controls.Add(nSimilarHyperlink);
goalsOtherPlaceHolder.Controls.Add(new LiteralControl("<br/>"));
}