public async Task <string> GetSpfHistory(string domain)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
return((string)await MySqlHelper.ExecuteScalarAsync(connectionString,
SpfApiDaoResources.SelectSpfHistoryStates, new MySqlParameter("domain", domain)));
}
public async Task <SubdomainStatsResult> GetSubdomainStats(string domain, string provider, DateTime startDate,
DateTime endDate, int page, int pageSize, string categoryFilter = null)
{
var sqlBuilder = new SqlBuilder()
.AddAggregateReportDefaults()
.AddCategoryFilter(categoryFilter);
string connectionString = await _connectionInfoAsync.GetConnectionStringAsync();
MySqlParameter[] parameters =
{
new MySqlParameter("domain", domain),
new MySqlParameter("provider", provider),
new MySqlParameter("startDate", startDate.ToString("yyyy-MM-dd")),
new MySqlParameter("endDate", endDate.ToString("yyyy-MM-dd")),
new MySqlParameter("offset", (page - 1) * pageSize),
new MySqlParameter("pageSize", pageSize),
};
SubdomainStats domainStats = null;
string selectSingleProviderStatsCommandText = sqlBuilder.Build(AggregateReportDaoV2.SelectSingleProviderStats);
using (var reader = await MySqlHelper.ExecuteReaderAsync(connectionString,
selectSingleProviderStatsCommandText, parameters))
{
while (await reader.ReadAsync())
{
domainStats = CreateSubdomainStats(reader);
break;
}
}
List <SubdomainStats> subdomainStats = new List <SubdomainStats>();
string selectSubdomainStatsCommandText = sqlBuilder.Build(AggregateReportDaoV2.SelectSubdomainStats);
using (var reader = await MySqlHelper.ExecuteReaderAsync(connectionString, selectSubdomainStatsCommandText, parameters))
{
while (await reader.ReadAsync())
{
subdomainStats.Add(CreateSubdomainStats(reader));
}
}
string selectSubdomainCountCommandText = sqlBuilder.Build(AggregateReportDaoV2.SelectSubdomainCount);
object subdomainCount = await MySqlHelper.ExecuteScalarAsync(connectionString, selectSubdomainCountCommandText, parameters);
return(new SubdomainStatsResult(domainStats, subdomainStats, ParseInt(subdomainCount)));
}
public async Task Save(TlsEntityState state)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
await MySqlHelper.ExecuteScalarAsync(connection,
TlsEntityDaoResources.SaveTlsEntity,
new MySqlParameter("hostname", ReverseUrl(state.Id)),
new MySqlParameter("state", JsonConvert.SerializeObject(state)));
connection.Close();
}
}
public async Task <ProviderStatsResult> GetProviderStats(string domain, DateTime startDate, DateTime endDate,
int page, int pageSize, bool rollup = false, string categoryFilter = null, string providerFilter = null)
{
string connectionString = await _connectionInfoAsync.GetConnectionStringAsync();
string commandTemplate = rollup
? AggregateReportDaoV2.SelectProviderStatsRollup
: AggregateReportDaoV2.SelectProviderStats;
var sqlBuilder = new SqlBuilder()
.AddAggregateReportDefaults()
.AddCategoryFilter(categoryFilter)
.AddProviderFilter(providerFilter);
var commandText = sqlBuilder
.Build(commandTemplate);
MySqlParameter[] parameters =
{
new MySqlParameter("domain", domain),
new MySqlParameter("startDate", startDate.ToString("yyyy-MM-dd")),
new MySqlParameter("endDate", endDate.ToString("yyyy-MM-dd")),
new MySqlParameter("providerFilter", FormatProvider(providerFilter)),
new MySqlParameter("offset", (page - 1) * pageSize),
new MySqlParameter("pageSize", pageSize),
};
List <ProviderStats> providerStats = new List <ProviderStats>();
using (var reader = await MySqlHelper.ExecuteReaderAsync(connectionString, commandText, parameters))
{
while (await reader.ReadAsync())
{
providerStats.Add(new ProviderStats(
reader.GetString("domain"),
reader.GetString("provider"),
reader.GetInt64("spf_pass_dkim_pass_none"),
reader.GetInt64("spf_pass_dkim_fail_none"),
reader.GetInt64("spf_fail_dkim_pass_none"),
reader.GetInt64("spf_fail_dkim_fail_none"),
reader.GetInt64("spf_pass_dkim_pass_quarantine"),
reader.GetInt64("spf_pass_dkim_fail_quarantine"),
reader.GetInt64("spf_fail_dkim_pass_quarantine"),
reader.GetInt64("spf_fail_dkim_fail_quarantine"),
reader.GetInt64("spf_pass_dkim_pass_reject"),
reader.GetInt64("spf_pass_dkim_fail_reject"),
reader.GetInt64("spf_fail_dkim_pass_reject"),
reader.GetInt64("spf_fail_dkim_fail_reject"),
reader.GetInt64("fully_trusted"),
reader.GetInt64("partially_trusted"),
reader.GetInt64("untrusted"),
reader.GetInt64("quarantined"),
reader.GetInt64("rejected"),
reader.GetInt64("total_emails"),
reader.GetInt64("fail_spf_total"),
reader.GetInt64("fail_dkim_total")
));
}
}
string providersCountCommandFormatString = rollup
? AggregateReportDaoV2.SelectProviderStatsRollupCount
: AggregateReportDaoV2.SelectProviderStatsCount;
string providersCountCommandText = sqlBuilder.Build(providersCountCommandFormatString);
Task <object> providersCount = MySqlHelper.ExecuteScalarAsync(connectionString, providersCountCommandText, parameters);
string allProvidersTotalEmailCountCommandFormatString = rollup
? AggregateReportDaoV2.SelectAllProviderRollupCount
: AggregateReportDaoV2.SelectAllProviderCount;
string allProvidersTotalEmailCountCommandText = sqlBuilder.Build(allProvidersTotalEmailCountCommandFormatString);
Task <object> allProviderCount = MySqlHelper.ExecuteScalarAsync(connectionString, allProvidersTotalEmailCountCommandText, parameters);
await Task.WhenAll(providersCount, allProviderCount);
return(new ProviderStatsResult(providerStats, ParseInt(providersCount.Result),
ParseInt(allProviderCount.Result)));
}