private dynamic UpdatePageViewSummary(dynamic arg)
{
//lastPageViewId have to be saved on setting (but we will re-count everytime for now)
var lastPageViewId = 0;
var results = this.SiteDatabase.Query
(string.Format("SELECT TableName, ContentId, COUNT(Id) as Hit, Request FROM PageView WHERE Id > {0} GROUP BY TableName, ContentId", lastPageViewId),
new
{
TableName = "test",
ContentId = 0,
Hit = 0,
Request = ""
}).ToList();
// update lastPageViewId back and save to setting (but we will re-count everytime for now)
//lastPageViewId = this.SiteDatabase.Query<PageView>().LastOrDefault().Id;
foreach (dynamic pageView in results)
{
string tableName = pageView.TableName;
int contentId = pageView.ContentId;
var summary = this.SiteDatabase.Query <PageViewSummary>()
.Where(rec => rec.TableName == tableName && rec.ContentId == contentId).FirstOrDefault();
if (summary == null)
{
var request = JObject.Parse(pageView.Request);
summary = new PageViewSummary()
{
ContentId = contentId,
TableName = tableName,
PageViews = pageView.Hit,
Url = Uri.UnescapeDataString(request.Value <string>("Path"))
};
}
else
{
// if exist should be just update the pageview (but we will re-count everytime for now)
//summary.PageViews += pageView.Hit;
summary.PageViews = pageView.Hit;
}
this.SiteDatabase.UpsertRecord(summary);
}
return("OK");
}
/// <summary>
/// Get Page View Count for given item in table
/// </summary>
/// <param name="db"></param>
/// <param name="table"></param>
/// <param name="id"></param>
/// <returns></returns>
public static long GetPageViewCount(NancyBlackDatabase db, dynamic siteSettings, string table, int id)
{
var key = "PageViewCount-" + table + id;
object cached = MemoryCache.Default.Get(key);
if (cached != null)
{
return((long)cached);
}
if (siteSettings.analytics == null)
{
dynamic result = db.Query
(string.Format("SELECT COUNT(Id) as Hit FROM PageView WHERE ContentId = {0} AND TableName = '{1}'", id, table),
new
{
Hit = 0
}).FirstOrDefault();
if (result == null)
{
result = 0;
}
MemoryCache.Default.Add(key, result, DateTimeOffset.Now.AddMinutes(10));
return(result);
}
else
{
var content = db.QueryAsDynamic(table, "Id eq " + id).FirstOrDefault();
if (content == null)
{
MemoryCache.Default.Add(key, 0, DateTimeOffset.Now.AddMinutes(10));
return(0); // wrong content
}
string url = content.Url;
if (url == null)
{
MemoryCache.Default.Add(key, 0, DateTimeOffset.Now.AddMinutes(10));
return(0); // cannot get Url
}
if (url.Contains("/archive/"))
{
url = url.Replace("/archive/", "/");
}
url = url.Replace('/', '-');
long pageViews = 0;
lock (BaseModule.GetLockObject("PageViewSummary-" + url)) // ensure only one thread is working on calculation
{
// if multiple threads is locked - they will arrive here when lock is released
// so check the cache again
cached = MemoryCache.Default.Get(key);
if (cached != null)
{
return((int)cached);
}
CloudTable summaryTable = ContentModule.GetSummaryTable(siteSettings);
var queryString = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, url),
TableOperators.And,
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, url));
var query = new TableQuery <PageViewSummary>();
var result = summaryTable.ExecuteQuery <PageViewSummary>(query.Where(queryString)).FirstOrDefault();
if (result == null)
{
result = new PageViewSummary();
result.PageViews = 0;
result.Path = url;
result.PrepareForAzure();
result.Timestamp = DateTimeOffset.MinValue;
summaryTable.Execute(TableOperation.InsertOrReplace(result));
}
// find all pageview since the pageview summary timestamp
var pvQueryString = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, url),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.GreaterThanOrEqual, result.Timestamp));
CloudTable rawTable = ContentModule.GetPageViewTable(siteSettings);
var pageviewQuery = new TableQuery <PageView>();
var count = rawTable.ExecuteQuery <PageView>(pageviewQuery.Where(pvQueryString)).Count();
result.PageViews = result.PageViews + count;
summaryTable.Execute(TableOperation.InsertOrReplace(result));
pageViews = result.PageViews;
MemoryCache.Default.Add(key, pageViews, DateTimeOffset.Now.AddMinutes(10));
return(pageViews);
}
}
}
/// <summary>
/// Get Page View Count
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
private dynamic GetPageViewCount(dynamic arg)
{
var table = (string)arg.table;
var id = (int)arg.id;
// invalid data type, also prevent SQL Injection attack when we replace string
if (this.SiteDatabase.DataType.FromName(table) == null)
{
return(400);
}
var key = table + id;
dynamic cached = MemoryCache.Default.Get(key);
if (cached != null)
{
return((string)cached);
}
if (this.CurrentSite.analytics == null)
{
dynamic result = this.SiteDatabase.Query
(string.Format("SELECT COUNT(Id) as Hit FROM PageView WHERE ContentId = {0} AND TableName = '{1}'", id, table),
new
{
Hit = 0
}).FirstOrDefault();
if (result == null)
{
return("0");
}
MemoryCache.Default.Add(key, result.Hit.ToString("0,0"), DateTimeOffset.Now.AddMinutes(10));
return(result.Hit.ToString("0,0"));
}
else
{
var content = this.SiteDatabase.QueryAsDynamic(table, "Id eq " + id).FirstOrDefault();
if (content == null)
{
MemoryCache.Default.Add(key, "0", DateTimeOffset.Now.AddMinutes(10));
return("0"); // wrong content
}
string url = content.Url;
if (url == null)
{
MemoryCache.Default.Add(key, "0", DateTimeOffset.Now.AddMinutes(10));
return("0"); // cannot get Url
}
url = url.Replace('/', '-');
long pageViews = 0;
lock (BaseModule.GetLockObject("PageViewSummary-" + url)) // ensure only one thread is working on calculation
{
// if multiple threads is locked - they will arrive here when lock is released
// so check the cache again
cached = MemoryCache.Default.Get(key);
if (cached != null)
{
return((string)cached);
}
var summaryTable = this.GetSummaryTable();
var queryString = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, url),
TableOperators.And,
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, url));
var query = new TableQuery <PageViewSummary>();
var result = summaryTable.ExecuteQuery <PageViewSummary>(query.Where(queryString)).FirstOrDefault();
if (result == null)
{
result = new PageViewSummary();
result.PageViews = 0;
result.Path = url;
result.PrepareForAzure();
result.Timestamp = DateTimeOffset.MinValue;
summaryTable.Execute(TableOperation.InsertOrReplace(result));
}
// find all pageview since the pageview summary timestamp
var pvQueryString = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, url),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.GreaterThanOrEqual, result.Timestamp));
var rawTable = this.GetPageViewTable();
var pageviewQuery = new TableQuery <PageView>();
var count = rawTable.ExecuteQuery <PageView>(pageviewQuery.Where(pvQueryString)).Count();
result.PageViews = result.PageViews + count;
summaryTable.Execute(TableOperation.InsertOrReplace(result));
pageViews = result.PageViews;
MemoryCache.Default.Add(key, pageViews.ToString("0,0"), DateTimeOffset.Now.AddMinutes(10));
return(pageViews.ToString("0,0"));
}
}
}
