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; }