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