public HistoryData LoadDataByDateUtc(DateTime date, MSGorillaMetricCtx ctx = null)
{
string sql = "select * from HistoryData where Date >= {0} and Date < {1}";
date = date.ToUniversalTime();
date = DateTime.Parse(string.Format("{0:yyyy-MM-dd}", date));
HistoryData data = null;
if (ctx == null)
{
using (ctx = new MSGorillaMetricCtx())
{
data = ctx.HistoryDatas.
SqlQuery(sql, date, date.AddDays(1)).
FirstOrDefault();
}
}
else
{
data = ctx.HistoryDatas.
SqlQuery(sql, date, date.AddDays(1)).
FirstOrDefault();
}
return(data);
}
public List <string> GetCurrentRobotID()
{
List <string> userid = new List <string>();
using (var _gorillaCtx = new MSGorillaMetricCtx())
{
userid = _gorillaCtx.Database.SqlQuery <string>("select userid from userprofile where isrobot=1").ToList();
}
return(userid);
}
public int GetCurrentTopicCount()
{
int count = 0;
using (var _gorillaCtx = new MSGorillaMetricCtx())
{
count = _gorillaCtx.Database.SqlQuery <int>("select count(*) from topic").First();
}
Logger.Info(string.Format("{0} topics exist.", count));
return(count);
}
public int GetCurrentUserCount()
{
int count = 0;
using (var _gorillaCtx = new MSGorillaMetricCtx())
{
count = _gorillaCtx.Database.SqlQuery <int>("select count(*) from userprofile where isrobot=0").First();
}
Logger.Info(string.Format("{0} common user exist.", count));
return(count);
}
public HistoryData GetLatestData(MSGorillaMetricCtx ctx = null)
{
string sql = "select top 1 * from HistoryData order by Date desc";
if (ctx == null)
{
using (ctx = new MSGorillaMetricCtx())
{
return(ctx.HistoryDatas.SqlQuery(sql).FirstOrDefault());
}
}
else
{
return(ctx.HistoryDatas.SqlQuery(sql).FirstOrDefault());
}
}
public List <HistoryData> LoadDataByDateUtc(DateTime start, DateTime end, MSGorillaMetricCtx ctx = null)
{
start = start.ToUniversalTime();
end = end.ToUniversalTime();
string sql = "select * from HistoryData where Date >= {0} and Date < {1}";
string s = string.Format(sql, start, end);
if (ctx == null)
{
using (ctx = new MSGorillaMetricCtx())
{
List <HistoryData> datas = ctx.HistoryDatas.ToList();
return(ctx.HistoryDatas.SqlQuery(sql, start, end).ToList());
}
}
else
{
return(ctx.HistoryDatas.SqlQuery(sql, start, end).ToList());
}
}
public void UpdateStatusAndSave()
{
using (MSGorillaMetricCtx ctx = new MSGorillaMetricCtx())
{
DateTime current = DateTime.UtcNow;
DateTime today = current;// DateTime.Parse(string.Format("{0:yyyy-MM-ddZ}", DateTime.UtcNow));
DateTime yesterday = today.AddDays(-1);
HistoryData dataOfYesterday = LoadDataByDateUtc(current.AddDays(-1), ctx);
if (dataOfYesterday != null)
{
dataOfYesterday.CountOfMsgPostedByRobot = this.GetTotalRobotMessageCountByDateUtc(yesterday);
ctx.SaveChanges();
}
else
{
HistoryData data = new HistoryData();
data.Date = yesterday;
data.CountOfMsgPostedByRobot = GetTotalRobotMessageCountByDateUtc(yesterday);
ctx.HistoryDatas.Add(data);
ctx.SaveChanges();
}
HistoryData dataOfToday = LoadDataByDateUtc(current, ctx);
if (dataOfToday == null)
{
HistoryData data = new HistoryData();
data.Date = DateTime.UtcNow;
data.UserCount = this.GetCurrentUserCount();
data.RobotCount = this.GetCurrentRobotCount();
data.TopicCount = this.GetCurrentTopicCount();
data.CountOfMsgPostedByRobot = 0;
ctx.HistoryDatas.Add(data);
ctx.SaveChanges();
}
}
}