// ---------------- // Grouped by feature // ---------------- public async Task <AggregatedResult <string> > SelectGroupedByFeatureAsync(DateTimeOffset start, DateTimeOffset end) { // Sql query const string sql = @" SELECT f.ModuleId AS [Aggregate] , COUNT(m.Id) AS Count FROM {prefix}_Metrics m INNER JOIN {prefix}_ShellFeatures f ON f.Id = m.FeatureId WHERE m.CreatedDate >= '{start}' AND m.CreatedDate <= '{end}' GROUP BY f.ModuleId "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern() }; // Execute and return results return(await _dbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <string>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <string>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
public void MaxRating() { ICollection <TournamentResult> results = new[] { new TournamentResult() { Username = Username, Rating = 1002 }, new TournamentResult() { Username = Username, Rating = 900 }, new TournamentResult() { Username = Username, Rating = 1300 } }; AggregatedResult aggregatedResult = new AggregatedResult(results.GroupBy(r => r.Username).Single()); Assert.Equal(results.Max(r => r.Rating), aggregatedResult.MaxRating); }
public static string SerializeCounts <T>(this AggregatedResult <T> result) { var output = new List <int>(); if (result.Data != null) { foreach (var item in result.Data) { output.Add(item.Count); } } return(output.Serialize <int>()); }
public static AggregatedResult <T> MergeIntoRange <T>(this AggregatedResult <T> result, T start, T end) { // Final output var output = new AggregatedResult <T>(); // Pattern match dates var startDate = start as DateTimeOffset?; var endDate = end as DateTimeOffset?; // Are we working with dates if (startDate != null && endDate != null) { // Normalize dates var normalizedStartDate = NormalizeDateTimeOffset(startDate.Value); var normalizedEndDate = NormalizeDateTimeOffset(endDate.Value); // Create dummy range var delta = normalizedStartDate.DayDifference(normalizedEndDate); for (var i = 0; i <= delta; i++) { var aggregate = normalizedStartDate.AddDays(i); var count = 0; // Add real count for dummy aggregate if (result.Data != null) { foreach (var data in result.Data) { var comparer = ((DateTimeOffset)Convert.ChangeType(data.Aggregate, typeof(DateTimeOffset))); if (aggregate.Equals(NormalizeDateTimeOffset(comparer))) { count = data.Count; } } } // Add data to output.Data.Add(new AggregatedCount <T>() { Aggregate = (T)Convert.ChangeType(aggregate, typeof(T)), Count = count }); } } return(output); }
// ---------------- // Grouped by reputation name // ---------------- public async Task <AggregatedResult <DateTimeOffset> > SelectGroupedByNameAsync( string reputationName, string groupBy, DateTimeOffset start, DateTimeOffset end) { // Sql query const string sql = @" SELECT COUNT(Id) AS [Count], MAX({groupBy}) AS [Aggregate] FROM {prefix}_UserReputations WHERE [Name] = '{reputationName}' AND {groupBy} >= '{start}' AND {groupBy} <= '{end}' GROUP BY YEAR({groupBy}), MONTH({groupBy}), DAY({groupBy}) "; // Sql replacements // Note user supplied input should never be passed into this method var replacements = new Dictionary <string, string>() { ["{reputationName}"] = reputationName.Replace("'", "''"), ["{groupBy}"] = groupBy.Replace("'", "''"), ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern() }; // Execute and return results return(await _dbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <DateTimeOffset>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <DateTimeOffset>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
public async Task <AggregatedResult <int> > SelectSummedByIntAsync( string groupBy, DateTimeOffset start, DateTimeOffset end, int featureId) { // Sql query const string sql = @" SELECT MAX(ur.{groupBy}) AS [Aggregate], SUM(ur.Points) AS [Count] FROM {prefix}_UserReputations ur INNER JOIN {prefix}_Users u ON ur.CreatedUserId = u.Id WHERE ( (ur.CreatedDate >= '{start}' AND ur.CreatedDate <= '{end}') AND (ur.FeatureId = {featureId}) ) GROUP BY ur.{groupBy} ORDER BY [Count] DESC "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{groupBy}"] = groupBy, ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern(), ["{featureId}"] = featureId.ToString() }; // Execute and return results return(await _dbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <int>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <int>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
// ---------------- // Grouped by string Title, Url etc // ---------------- public async Task <AggregatedResult <string> > SelectGroupedByStringAsync( string groupBy, DateTimeOffset start, DateTimeOffset end, int limit = 10) { // Sql query const string sql = @" SELECT TOP {limit} m.{groupBy} AS [Aggregate] , COUNT(m.Id) AS [Count] FROM {prefix}_Metrics m WHERE m.CreatedDate >= '{start}' AND m.CreatedDate <= '{end}' GROUP BY m.{groupBy} ORDER BY [Count] DESC "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{groupBy}"] = groupBy, ["{limit}"] = limit.ToString(), ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern() }; // Execute and return results return(await _dbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <string>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <string>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
public async Task <AggregatedResult <DateTimeOffset> > SelectGroupedByDateAsync(string groupBy, DateTimeOffset start, DateTimeOffset end, int featureId) { // Sql query const string sql = @" SELECT COUNT(em.Id) AS [Count], MAX(em.{groupBy}) AS [Aggregate] FROM {prefix}_EntityMetrics em INNER JOIN {prefix}_Entities e ON e.Id = em.EntityId WHERE ( (em.{groupBy} >= '{start}' AND em.{groupBy} <= '{end}') AND (e.FeatureId = {featureId}) ) GROUP BY YEAR(em.{groupBy}), MONTH(em.{groupBy}), DAY(em.{groupBy}) "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{groupBy}"] = groupBy, ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern(), ["{featureId}"] = featureId.ToString() }; // Execute and return results return(await _dbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <DateTimeOffset>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <DateTimeOffset>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
// ---------------- // Grouped by date // ---------------- public async Task <AggregatedResult <DateTimeOffset> > SelectGroupedByDateAsync( string groupBy, DateTimeOffset start, DateTimeOffset end) { // Sql query const string sql = @" SELECT COUNT(Id) AS [Count], MAX({groupBy}) AS [Aggregate] FROM {prefix}_EntityReplies WHERE {groupBy} >= '{start}' AND {groupBy} <= '{end}' GROUP BY YEAR({groupBy}), MONTH({groupBy}), DAY({groupBy}) "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{groupBy}"] = groupBy, ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern() }; // Execute and return results return(await DbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <DateTimeOffset>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <DateTimeOffset>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
public async Task <AggregatedResult <int> > SelectGroupedByIntAsync(string groupBy, DateTimeOffset start, DateTimeOffset end) { // Sql query const string sql = @" SELECT COUNT(em.Id) AS [Count], MAX(em.{groupBy}) AS [Aggregate] FROM {prefix}_EntityMetrics em INNER JOIN {prefix}_Entities e ON em.EntityId = e.Id WHERE em.CreatedDate >= '{start}' AND em.CreatedDate <= '{end}' GROUP BY em.{groupBy} ORDER BY [Count] DESC "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{groupBy}"] = groupBy, ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern() }; // Execute and return results return(await DbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <int>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <int>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
public static string SerializeLabels <T>(this AggregatedResult <T> result) { var output = new List <string>(); if (result.Data != null) { foreach (var item in result.Data) { string value = null; if (item.Aggregate is DateTimeOffset) { value = ((DateTimeOffset)Convert.ChangeType(item.Aggregate, typeof(DateTimeOffset))).ToPrettyDate(); } if (value == null) { value = item.Aggregate.ToString();; } output.Add(value); } } return(output.Serialize <string>()); }
// ---------------- // Grouped by role // ---------------- public async Task <AggregatedResult <string> > SelectGroupedByRoleAsync(DateTimeOffset start, DateTimeOffset end) { // Sql query const string sql = @" DECLARE @temp TABLE ( [Aggregate] nvarchar(255) NOT NULL, [Count] int NOT NULL ); INSERT INTO @temp SELECT r.[Name] AS [Aggregate], COUNT(m.Id) AS Count FROM {prefix}_Metrics m RIGHT OUTER JOIN {prefix}_UserRoles ur ON ur.UserId = m.CreatedUserId RIGHT OUTER JOIN {prefix}_Roles r ON r.Id = ur.RoleId WHERE m.CreatedDate >= '{start}' AND m.CreatedDate <= '{end}' GROUP BY r.[Name] -- Get anonymous count DECLARE @anonymousCount int; SET @anonymousCount = ( SELECT COUNT(m.Id) AS Count FROM {prefix}_Metrics m WHERE m.CreatedDate >= '{start}' AND m.CreatedDate <= '{end}' AND m.CreatedUserId = 0 ); UPDATE @temp SET [Count] = (@anonymousCount) WHERE [Aggregate] = '{anonymousName}' SELECT [Aggregate] AS Aggregate, [Count] AS Count FROM @temp "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern(), ["{anonymousName}"] = DefaultRoles.Anonymous }; // Execute and return results return(await _dbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <string>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <string>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }
public async Task ProcessEvent(AggregatedResult result, Image <Bgr, Byte> image) { if (DateTime.Now < _nextPicture) { Log.Logger.Debug($"Picture blocked for {(_nextPicture - DateTime.Now).TotalSeconds}"); return; } if (DateTime.Now < _waitUntil) { Log.Logger.Debug($"- CountDown Waiting : {(_waitUntil - DateTime.Now).TotalSeconds}"); return; } switch (State) { case SelfieState.Ready: if (result.Label != MirrorStateMachine.SELFIE) { return; } else { State = SelfieState.CountDown; } return; case SelfieState.CountDown: if (_countDown <= 3) { var step = _countDownMessages[_countDown++]; await ShowMessage(step.Message, step.Wait); return; } _countDown = 0; State = SelfieState.Click; return; case SelfieState.Click: await ShowMessage("Click!"); await Task.Delay(ClickDelay); var success = await TakeSelfie(image); if (success) { await ShowMessage(GetImageUrl(), 0, 500, "image"); await Task.Delay(ShowPictureDelay); await ShowMessage("Tweeted! Veja a foto no @espelhotdc"); } else { await ShowMessage("Droga! Alguma coisa deu errado tirando a foto :("); } await Task.Delay(FinalMessageDelay); State = SelfieState.Ready; _nextPicture = DateTime.Now.AddMilliseconds(CoolDownDelay); await ShowMessage("Olá!"); return; } }
public async Task <AggregatedResult <string> > SelectUserMetricsAsync(DateTimeOffset start, DateTimeOffset end) { // Sql query const string sql = @" DECLARE @totalUsers int; DECLARE @returningUsers int; DECLARE @newUsers int; DECLARE @totalBadges int; DECLARE @newBadges int; DECLARE @totalReputations int; DECLARE @newReputations int; SET @totalUsers = (SELECT COUNT(Id) FROM {prefix}_Users); SET @newUsers = ( SELECT COUNT(Id) FROM {prefix}_Users WHERE CreatedDate >= '{start}' AND CreatedDate <= '{end}' ); SET @returningUsers = ( SELECT COUNT(Id) FROM {prefix}_Users WHERE CreatedDate <= '{start}' AND Id IN ( SELECT DISTINCT CreatedUserId FROM {prefix}_UserReputations WHERE ([Name] = 'Visit') AND (CreatedDate >= '{start}' AND CreatedDate <= '{end}') ) ); SET @totalBadges = ( SELECT COUNT(Id) FROM {prefix}_UserBadges ); SET @newBadges = ( SELECT COUNT(Id) FROM {prefix}_UserBadges WHERE CreatedDate >= '{start}' AND CreatedDate <= '{end}' ); SET @totalReputations = ( SELECT COUNT(Id) FROM {prefix}_UserReputations ); SET @newReputations = ( SELECT COUNT(Id) FROM {prefix}_UserReputations WHERE CreatedDate >= '{start}' AND CreatedDate <= '{end}' ); DECLARE @temp TABLE ( [Aggregate] nvarchar(100) NOT NULL, [Count] int NOT NULL ); INSERT INTO @temp SELECT 'TotalUsers', @totalUsers; INSERT INTO @temp SELECT 'NewUsers', @newUsers; INSERT INTO @temp SELECT 'ReturningUsers', @returningUsers; INSERT INTO @temp SELECT 'TotalBadges', @totalBadges; INSERT INTO @temp SELECT 'NewBadges', @newBadges; INSERT INTO @temp SELECT 'TotalReputations', @totalReputations; INSERT INTO @temp SELECT 'NewReputations', @newReputations; SELECT * FROM @temp; "; // Sql replacements var replacements = new Dictionary <string, string>() { ["{start}"] = start.ToSortableDateTimePattern(), ["{end}"] = end.ToSortableDateTimePattern() }; // Execute and return results return(await _dbHelper.ExecuteReaderAsync(sql, replacements, async reader => { var output = new AggregatedResult <string>(); while (await reader.ReadAsync()) { var aggregatedCount = new AggregatedCount <string>(); aggregatedCount.PopulateModel(reader); output.Data.Add(aggregatedCount); } return output; })); }