private static ReportResponse ExecuteQuery(Guid segmentId, DateTime startDate, DateTime endDate)
{
var parameters = new Dictionary <string, object>()
{
{ "@SegmentId", segmentId },
{ "@StartDate", startDate },
{ "@EndDate", endDate }
};
var query = new ReportDataQuery(REPORT_QUERY, parameters);
var cachingPolicy = new CachingPolicy {
ExpirationPeriod = TimeSpan.Zero
};
return(ReportDataService.ExecuteQuery(query, cachingPolicy));
}
/// <summary>
/// Retrieves a list of the most visited items by recency.
/// </summary>
/// <param name="maxItems">Maximum # of items to return. Default: 100.</param>
/// <param name="days">How many days to go back and search for visits. Default: 60.</param>
/// <param name="path">When using a template id: search for withing an specific path.</param>
/// <param name="templateId">Limit results to a subset of items based on the template id.</param>
/// <returns></returns>
public Dictionary <Guid, int> GetMostVisitedItems(int maxItems = 100, int days = 60, string path = null, Guid templateId = new Guid(), Database database = null)
{
var query = string.Empty;
if (templateId != Guid.Empty)
{
var itemIds = GetAllGuidsByTemplateId(new ID(templateId), path, database: database);
if (itemIds != null && itemIds.Any())
{
var stringIds = itemIds.Aggregate(string.Empty, (current, id) => current + (current + "'" + id + "',"));
stringIds = stringIds.Substring(0, stringIds.LastIndexOf(",", StringComparison.Ordinal));
query =
$@"SELECT TOP {maxItems} ItemId, count(*) as cnt FROM Fact_PageViews
WHERE Date > DATEADD(DAY, -{days}, GETDATE())
AND ItemId IN({stringIds}) GROUP BY ItemId ORDER BY cnt DESC";
}
}
else
{
query =
$@"SELECT TOP {maxItems} ItemId, count(*) as cnt FROM Fact_PageViews
WHERE Date > DATEADD(DAY, -{days}, GETDATE()) GROUP BY ItemId ORDER BY cnt DESC";
}
if (!string.IsNullOrEmpty(query))
{
var dataQuery = new ReportDataQuery(query);
var provider = (ReportDataProviderBase)Factory.CreateObject("reporting/dataProvider", true);
var response = provider.GetData("reporting", dataQuery, CachingPolicy.WithCacheDisabled);
var items = response.GetDataTable().Rows;
var results = new Dictionary <Guid, int>();
for (var i = 0; i < items.Count; i++)
{
results.Add(Guid.Parse(System.Convert.ToString(items[i]["ItemId"])), System.Convert.ToInt32(items[i]["cnt"]));
}
return(results);
}
return(null);
}
public IHttpActionResult Get()
{
// Use ReportDataProvider
ReportDataProviderBase reportingDataProvider = ApiContainer.Configuration.GetReportingDataProvider();
var cachingPolicy = new CachingPolicy
{
ExpirationPeriod = Config.CacheExpiration
};
// Load data from a Custom template in core containing a SQL query field. This gets the SQL we need to query the Fact table
Item dataSourceItem = Database.GetDatabase("core").GetItem(new ID("{97502B91-E580-4DAD-A2F5-8E06F4D0554A}"));
var reportSqlQuery = dataSourceItem.Fields["SQL"].Value;
// Use ReportDataQuery to load data from reporting database Fact table
var query = new ReportDataQuery(reportSqlQuery);
var table = reportingDataProvider.GetData("reporting", query, cachingPolicy).GetDataTable();
// Create two dictionaries to store data
Dictionary<string, string> returnVisitData = new Dictionary<string, string>();
Dictionary<string, string> newVisitData = new Dictionary<string, string>();
// Add two values to each dictionary
returnVisitData.Add("VisitType", "Return Visitors");
returnVisitData.Add("Visits", table.Rows[0]["VisitData"].ToString());
newVisitData.Add("VisitType", "New Visitors");
newVisitData.Add("Visits", table.Rows[1]["VisitData"].ToString());
// Data must conform to format:
//{"data":{"dataset":[{"data":[
// So using Report Data class to aid with serialization to correct format
var reportdata = new ReportData();
reportdata.AddRow(returnVisitData);
reportdata.AddRow(newVisitData);
var response = new ReportResponse();
response.Data.Dataset.Add(reportdata);
//output to Json
return new JsonResult<ReportResponse>(response,
new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() },
Encoding.UTF8, this);
}
public IHttpActionResult Get(string keys = null, string dateFrom = null, string dateTo = null)
{
DateTime startDate;
if (!DateTime.TryParseExact(dateFrom, "dd-MM-yyyy", new CultureInfo("en-US"), DateTimeStyles.None, out startDate))
{
startDate = DateTime.MinValue;
}
DateTime endDate;
if (!DateTime.TryParseExact(dateTo, "dd-MM-yyyy", new CultureInfo("en-US"), DateTimeStyles.None, out endDate))
{
endDate = DateTime.MaxValue;
}
else
{
endDate = endDate.Date + new TimeSpan(23, 59, 59);
}
var parameters = new Dictionary <string, object>()
{
{ "@DimensionKeyId", keys },
{ "@StartDate", startDate },
{ "@EndDate", endDate }
};
var query = new ReportDataQuery(REPORT_QUERY, parameters);
var cachingPolicy = new CachingPolicy {
ExpirationPeriod = TimeSpan.Zero
};
var response = ReportDataService.ExecuteQuery(query, cachingPolicy, "[Url]");
var serializerSettings = new JsonSerializerSettings {
ContractResolver = new CamelCasePropertyNamesContractResolver()
};
return(new JsonResult <ReportResponse>(response, serializerSettings, Encoding.UTF8, this));
}
private DataTable GetPopularNewsReportingData(string newsRoot, int daysToAdd)
{
try
{
Log.Info($"NewsRepository - GetPopularNewsReportingData method Parameters - newsRoot = " + newsRoot + " | daysToAdd=" + daysToAdd, this);
// Create ReportDataProviderBase factory using connection info from Sitecore.Analytics.Reporting.config
var provider = (ReportDataProviderBase)Factory.CreateObject("reporting/dataProvider", false);
// Load query from Sitecore ReportQuery item
Item dataSourceItem = Sitecore.Context.Database.Items[Templates.NewsArticleReportQueryItem.ID];
var dataSQLQuery = dataSourceItem.Fields["Query"].Value;
Log.Info($"NewsRepository - GetPopularNewsReportingData method dataSQLQuery = " + dataSQLQuery, this);
//set the parameter to query to filter records based on current context language, URL and start date.
dataSQLQuery = dataSQLQuery.Replace("@Language", "'" + Sitecore.Context.Language.ToString() + "'");
dataSQLQuery = dataSQLQuery.Replace("@LikeURL", newsRoot);
dataSQLQuery = dataSQLQuery.Replace("@StartDate", daysToAdd.ToString());
var query = new ReportDataQuery(dataSQLQuery);
Log.Info($"NewsRepository - GetPopularNewsReportingData method query = " + query.Query, this);
//Get data from reporting datasource in which query will be executed to get records.
var response = provider.GetData("reporting", query, CachingPolicy.WithCacheDisabled);
Log.Info($"NewsRepository - GetPopularNewsReportingData method Response Count = " + response.GetDataTable().Rows.Count, this);
return(response.GetDataTable());
}
catch (Exception ex)
{
Log.Error($"NewsRepository - GetPopularNewsReportingData method has error - {ex.Message}", ex, this);
}
return(null);
}
public IHttpActionResult Get(string datasource, string siteName)
{
ReportDataProviderBase reportingDataProvider = ApiContainer.Configuration.GetReportingDataProvider();
var cachingPolicy = new CachingPolicy
{
ExpirationPeriod = TimeSpan.FromHours(1)
//must find cache expiration node
};
Item dataSourceItem = Database.GetDatabase("core").GetItem(new ID(datasource));
var reportSQLQuery = dataSourceItem.Fields["{0AA8B742-BBDF-4405-AB8D-6FAC7E79433B}"].Value;
NameValueCollection parameters = HttpUtility.ParseQueryString(this.Request.RequestUri.Query);
var from = DateTime.ParseExact(parameters["dateFrom"], "dd-MM-yyyy", new DateTimeFormatInfo());
var to = DateTime.ParseExact(parameters["dateTo"], "dd-MM-yyyy", new DateTimeFormatInfo());
string dateFrom = from.ToString("yyyy-MM-dd");
string dateTo = to.ToString("yyyy-MM-dd");
if (from.Equals(to) && parameters["dateTo"].Length <= 10)
{
dateFrom = from.ToString("yyyy-MM-dd 00:00:00");
dateTo = to.ToString("yyyy-MM-dd 23:59:59");
}
reportSQLQuery = reportSQLQuery.Replace("@StartDate", "'" + dateFrom + "'");
reportSQLQuery = reportSQLQuery.Replace("@EndDate", "'" + dateTo + "'");
string hashedSiteName = "0";
if (siteName != "all")
{
var encoder = new Hash32Encoder();
hashedSiteName = encoder.Encode(siteName);
reportSQLQuery = reportSQLQuery.Replace("@SiteNameIdOperator", "=");
}
else
{
reportSQLQuery = reportSQLQuery.Replace("@SiteNameIdOperator", "!=");
}
reportSQLQuery = reportSQLQuery.Replace("@SiteNameId", hashedSiteName);
var query = new ReportDataQuery(reportSQLQuery);
DataTableReader reader = reportingDataProvider.GetData("reporting", query, cachingPolicy).GetDataTable().CreateDataReader();
var data = new ReportData();
int counter = 0;
while (reader.Read())
{
var row = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
row.Add(reader.GetName(i), reader[i].ToString());
}
data.AddRow(row);
counter++;
}
var responce = new ReportResponse
{
data = data,
TotalRecordCount = counter
};
return(new JsonResult <ReportResponse>(responce, new JsonSerializerSettings {
ContractResolver = new CamelCasePropertyNamesContractResolver()
}, Encoding.UTF8, this));
}
public static ReportResponse ExecuteQuery(ReportDataQuery queryData, CachingPolicy cachingPolicy, string keyTranslationField = null, string keyTranslationDefault = null)
{
var reportResponse = new ReportResponse();
var totalRecordCount = 0;
var translations = new Dictionary <string, string>();
var reportingDataProvider = ApiContainer.Configuration.GetReportingDataProvider();
var reportRows = new List <ReportRow>();
var dataTable = reportingDataProvider.GetData("reporting", queryData, cachingPolicy).GetDataTable();
foreach (DataRow dataRow in dataTable.Rows)
{
var key = GetStringValue(dataRow, "[Key]");
var dimensionKeyId = GetInt64Value(dataRow, "[DimensionKeyId]");
var bounces = GetIntValue(dataRow, "[Bounces]");
var conversions = GetIntValue(dataRow, "[Conversions]");
var timeOnSite = GetInt64Value(dataRow, "[TimeOnSite]");
var value = GetIntValue(dataRow, "[Value]");
var pageViews = GetIntValue(dataRow, "[PageViews]");
var visits = GetIntValue(dataRow, "[Visits]");
var date = GetDateValue(dataRow, "[Date]");
var count = GetIntValue(dataRow, "[Count]");
var reportRow = new ReportRow
{
Key = key,
DimensionKeyId = dimensionKeyId,
Bounces = bounces,
Conversions = conversions,
TimeOnSite = timeOnSite,
PageViews = pageViews,
Visits = visits,
Value = value,
Date = date,
Count = count,
ValuePerVisit = Math.Round((double)value / visits, 2),
BounceRate = Math.Round((double)bounces / visits, 4),
AvgVisitDuration = Math.Round((double)timeOnSite / visits, 2),
ConversionRate = Math.Round((double)conversions / visits, 2),
AvgVisitPageViews = Math.Round((double)pageViews / visits, 2),
AvgVisitCount = Math.Round((double)count / visits, 2),
AvgPageCount = Math.Round((double)count / pageViews, 2),
AvgCountValue = (count == 0) ? 0 : Math.Round((double)value / count, 2)
};
reportResponse.Data.AddRow(reportRow);
if (keyTranslationField != null)
{
var translation = GetStringValue(dataRow, keyTranslationField) ?? keyTranslationDefault;
translations.Add(reportRow.Key, translation);
}
totalRecordCount++;
}
if (keyTranslationField != null)
{
reportResponse.Data.Localization.AddField("key", translations);
}
reportResponse.TotalRecordCount = totalRecordCount;
reportResponse.TimeResolution = "c"; // w
return(reportResponse);
}
private bool Blah(ReportDataQuery a)
{
var b = 0;
return(false);
}
private bool Blah(ReportDataQuery a)
{
var b = 0;
return false;
}
