private async Task Insert(SpfHistoryEntityState state)
{
await MySqlHelper.ExecuteNonQueryAsync(ConnectionString,
@"INSERT INTO `spf_entity_history`(`id`,`state`)VALUES(@domain,@state)",
new MySqlParameter("domain", state.Id),
new MySqlParameter("state", JsonConvert.SerializeObject(state)));
}
public async Task <Dictionary <string, List <TlsEntityState> > > GetDomains(string hostname)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
Dictionary <string, List <TlsEntityState> > results = new Dictionary <string, List <TlsEntityState> >();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connection,
TlsEntityDaoResources.GetDomainMxHosts, new MySqlParameter("hostname", ReverseUrl(hostname))))
{
while (await reader.ReadAsync())
{
string domain = ReverseUrl(reader.GetString("domain"));
TlsEntityState state = JsonConvert.DeserializeObject <TlsEntityState>(reader.GetString("state"));
if (results.ContainsKey(domain))
{
results[domain].Add(state);
}
else
{
results[domain] = new List <TlsEntityState> {
state
};
}
}
}
connection.Close();
}
return(results);
}
public async Task <List <SpfDomainStats> > GetSpfDomainStats(string domain, string provider, string ip,
DateTime startDate, DateTime endDate)
{
string connectionString = await _connectionInfoAsync.GetConnectionStringAsync();
string commandText = AggregateReportDaoV2.SelectSpfDomainStats;
MySqlParameter[] parameters =
{
new MySqlParameter("domain", domain),
new MySqlParameter("provider", provider),
new MySqlParameter("ip", ip),
new MySqlParameter("startDate", startDate.ToString("yyyy-MM-dd")),
new MySqlParameter("endDate", endDate.ToString("yyyy-MM-dd"))
};
List <SpfDomainStats> spfDomainStats = new List <SpfDomainStats>();
using (var reader = await MySqlHelper.ExecuteReaderAsync(connectionString, commandText, parameters))
{
while (await reader.ReadAsync())
{
spfDomainStats.Add(new SpfDomainStats(
reader.GetString("domain"),
reader.GetString("provider"),
reader.GetString("ip"),
reader.GetString("spf_domain"),
reader.GetInt64("spf_pass"),
reader.GetInt64("spf_fail")
));
}
}
return(spfDomainStats);
}
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 <List <AggregateReportExportStats> > GetAggregateReportExport(string domain, string provider, string ip, DateTime startDate,
DateTime endDate, bool includeSubdomains)
{
string reverseDomain = DomainNameUtils.ReverseDomainName(domain);
string connectionString = await _connectionInfoAsync.GetConnectionStringAsync();
string commandText = includeSubdomains
? AggregateReportDaoV2.SelectAggregateExportDataWithSubdomains
: AggregateReportDaoV2.SelectAggregateExportData;
MySqlParameter[] parameters =
{
new MySqlParameter("reverseDomain", reverseDomain),
new MySqlParameter("provider", provider),
new MySqlParameter("ip", ip),
new MySqlParameter("startDate", startDate.ToString("yyyy-MM-dd")),
new MySqlParameter("endDate", endDate.ToString("yyyy-MM-dd"))
};
List <AggregateReportExportStats> aggregateReportExportStats = new List <AggregateReportExportStats>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connectionString, commandText, parameters))
{
while (await reader.ReadAsync())
{
aggregateReportExportStats.Add(_aggregateReportExportStatsFactory.Create(reader));
}
}
return(aggregateReportExportStats);
}
public async Task <List <string> > GetDomainsFromHost(string hostname)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <string> results = new List <string>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connection,
TlsEntityDaoResources.GetDomainsFromMxHost, new MySqlParameter("hostname", ReverseUrl(hostname))))
{
while (await reader.ReadAsync())
{
string domain = ReverseUrl(reader.GetString("domain"));
results.Add(domain);
}
}
connection.Close();
}
return(results);
}
public async Task <List <SpfInfoResponse> > GetSpfForDomains(List <string> domain)
{
string query = string.Format(SpfApiDaoResources.SelectSpfStates,
string.Join(',', domain.Select((_, i) => $"@domain{i}")));
MySqlParameter[] parameters = domain
.Select((_, i) => new MySqlParameter($"domain{i}", _))
.ToArray();
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connectionString, query, parameters))
{
List <SpfInfoResponse> states = new List <SpfInfoResponse>();
while (await reader.ReadAsync())
{
if (!reader.IsDbNull("state"))
{
states.Add(JsonConvert.DeserializeObject <SpfInfoResponse>(reader.GetString("state")));
}
}
return(states);
}
}
public async Task <MxEntityState> Get(string domain)
{
MxEntityState result = null;
using (var connection = await CreateAndOpenConnection())
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connection,
MxStateDaoResources.GetMxRecord, new MySqlParameter("domain", ReverseUrl(domain))))
{
while (await reader.ReadAsync())
{
result = result ?? new MxEntityState(domain)
{
Error = JsonConvert.DeserializeObject <Message>(reader.GetString("error")),
LastUpdated = reader.GetDateTimeNullable("lastUpdated"),
MxState = (MxState)reader.GetInt32("mxState"),
HostMxRecords = new List <HostMxRecord>()
};
string hostMxRecord = reader.GetString("hostMxRecord");
if (!string.IsNullOrEmpty(hostMxRecord))
{
result.HostMxRecords.Add(JsonConvert.DeserializeObject <HostMxRecord>(hostMxRecord));
}
}
}
return(result);
}
private static async Task SetState(string connectionString, SpfEntityState state)
{
await MySqlHelper.ExecuteNonQueryAsync(connectionString,
"INSERT INTO `spf_entity`(`id`,`version`,`state`) VALUES (@domain, @version, @state)",
new MySqlParameter("domain", state.Id),
new MySqlParameter("version", state.Version),
new MySqlParameter("state", JsonConvert.SerializeObject(state)));
}
private async Task Insert(TlsRptEntityState state)
{
await MySqlHelper.ExecuteNonQueryAsync(ConnectionString,
@"INSERT INTO `tls_rpt_entity`(`id`,`version`,`state`)VALUES(@domain,@version,@state)",
new MySqlParameter("domain", state.Id),
new MySqlParameter("version", state.Version),
new MySqlParameter("state", JsonConvert.SerializeObject(state)));
}
public async Task UpdateLastChecked(List <TlsRptSchedulerState> entitiesToUpdate)
{
string query = string.Format(TlsRptPeriodicSchedulerDaoResources.UpdateTlsRptRecordsLastChecked,
string.Join(',', entitiesToUpdate.Select((_, i) => $"@domainName{i}")));
MySqlParameter[] parameters = entitiesToUpdate
.Select((_, i) => new MySqlParameter($"domainName{i}", _.Id.ToLower()))
.ToArray();
string connectionString = await _connectionInfo.GetConnectionStringAsync();
await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameters);
}
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)));
}
private async Task <List <DkimSchedulerState> > GetValues()
{
List <DkimSchedulerState> results = new List <DkimSchedulerState>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(ConnectionString, "SELECT id FROM dkim_scheduled_records"))
{
while (await reader.ReadAsync())
{
results.Add(new DkimSchedulerState(reader.GetString("id")));
}
}
return(results);
}
public async Task <List <MxEntityState> > GetMxEntityStates(List <string> domains)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <(string, HostMxRecord)> hostMxRecords = new List <(string, HostMxRecord)>();
Dictionary <string, MxEntityState> mxEntityStates = new Dictionary <string, MxEntityState>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string query = string.Format(MxApiDaoResources.GetMxRecords,
string.Join(',', domains.Select((_, i) => $"@domain{i}")));
MySqlParameter[] parameters = domains
.Select((domain, i) => new MySqlParameter($"domain{i}", ReverseUrl(domain)))
.ToArray();
await connection.OpenAsync();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connection, query, parameters))
{
while (await reader.ReadAsync())
{
string domain = ReverseUrl(reader.GetString("domain"));
if (!mxEntityStates.ContainsKey(domain))
{
mxEntityStates.Add(domain, new MxEntityState(domain)
{
MxState = (MxState)reader.GetInt32("mxState"),
LastUpdated = reader.GetDateTime("lastUpdated"),
Error = JsonConvert.DeserializeObject <Message>(reader.GetString("error")),
HostMxRecords = new List <HostMxRecord>()
});
}
hostMxRecords.Add((domain,
JsonConvert.DeserializeObject <HostMxRecord>(reader.GetString("hostMxRecord").ToLower())));
}
}
connection.Close();
}
foreach ((string, HostMxRecord)tuple in hostMxRecords)
{
mxEntityStates[tuple.Item1].HostMxRecords.Add(tuple.Item2);
}
return(mxEntityStates.Values.ToList());
}
public async Task <List <string> > GetDomains()
{
List <string> domains = new List <string>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(_connectionInfo.ConnectionString,
"SELECT distinct(entity_id) as domain FROM dkim_entity_history_old"))
{
while (await reader.ReadAsync())
{
domains.Add(reader.GetString("domain"));
}
}
return(domains);
}
private static async Task <List <SpfEntityState> > GetStates(string connectionString)
{
List <SpfEntityState> results = new List <SpfEntityState>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connectionString,
"SELECT state FROM spf_entity ORDER BY version DESC;"))
{
while (await reader.ReadAsync())
{
results.Add(CreateSpfEntityState(reader));
}
}
return(results);
}
public async Task <List <string> > GetDomainsWithoutHistory()
{
List <string> domains = new List <string>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(_connectionInfo.ConnectionString,
"SELECT e.id FROM dkim_entity e LEFT JOIN dkim_entity_history eh ON e.id = eh.id WHERE eh.id IS NULL"))
{
while (await reader.ReadAsync())
{
domains.Add(reader.GetString("id"));
}
}
return(domains);
}
public async Task Delete(string hostname)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
await MySqlHelper.ExecuteNonQueryAsync(connection,
TlsEntityDaoResources.DeleteTlsEntity,
new MySqlParameter("hostname", ReverseUrl(hostname)));
connection.Close();
}
}
public async Task <List <DomainStats> > GetDomainStats(string domain, DateTime startDate, DateTime endDate,
bool rollup = false, string categoryFilter = null)
{
string connectionString = await _connectionInfoAsync.GetConnectionStringAsync();
string commandTemplate = rollup
? AggregateReportDaoV2.SelectDominStatsRollup
: AggregateReportDaoV2.SelectDomainStats;
string commandText = new SqlBuilder()
.AddAggregateReportDefaults()
.AddCategoryFilter(categoryFilter)
.Build(commandTemplate);
MySqlParameter[] parameters =
{
new MySqlParameter("domain", domain),
new MySqlParameter("startDate", startDate.ToString("yyyy-MM-dd")),
new MySqlParameter("endDate", endDate.ToString("yyyy-MM-dd"))
};
List <DomainStats> domainStats = new List <DomainStats>();
using (var reader = await MySqlHelper.ExecuteReaderAsync(connectionString, commandText, parameters))
{
while (await reader.ReadAsync())
{
domainStats.Add(new DomainStats(
reader.GetString("domain"),
reader.GetDateTime("date"),
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")
));
}
}
return(domainStats);
}
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 AddHistoryForDomain(List <DkimHistoryEntityState> states)
{
string individualInsertStatement = string.Join(",", states.Select((v, i) => $"(@id{i},@state{i})"));
string insert = $"INSERT INTO `dkim_entity_history`(`id`,`state`) VALUES {individualInsertStatement};";
MySqlParameter[] parameters = states.Select((v, i) => new MySqlParameter($"id{i}", v.Id))
.Concat(states.Select((v, i) =>
{
JsonSerializerSettings jsonSerializerSettings = new JsonSerializerSettings
{
ContractResolver = new CamelCasePropertyNamesContractResolver()
};
return(new MySqlParameter($"state{i}", JsonConvert.SerializeObject(v, jsonSerializerSettings)));
})).ToArray();
await MySqlHelper.ExecuteNonQueryAsync(_connectionInfo.ConnectionString, insert, parameters);
}
public async Task <List <TlsRptSchedulerState> > GetExpiredTlsRptRecords()
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <TlsRptSchedulerState> results = new List <TlsRptSchedulerState>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connectionString,
TlsRptPeriodicSchedulerDaoResources.SelectTlsRptRecordsToSchedule,
new MySqlParameter("refreshIntervalSeconds", _config.RefreshIntervalSeconds),
new MySqlParameter("limit", _config.DomainBatchSize)))
{
while (await reader.ReadAsync())
{
results.Add(CreateTlsRptSchedulerState(reader));
}
}
return(results);
}
private async Task <List <(DateTime, ReverseDnsResult)> > GetExistingReverseDnsResults(Stopwatch stopwatch)
{
Console.WriteLine($"Querying all reverse dns results");
List <(DateTime, ReverseDnsResult)> results = new List <(DateTime, ReverseDnsResult)>();
double tally = 0;
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(Environment.GetEnvironmentVariable("ReverseDnsConnectionString"), "SELECT * FROM reverse_lookup_results"))
{
while (await reader.ReadAsync())
{
ReverseDnsResponse data = JsonConvert.DeserializeObject <List <ReverseDnsResponse> >(reader.GetString("data")).FirstOrDefault();
List <ReverseDnsResponse> reverseDnsResponses = null;
if (data != null)
{
List <string> ipAddresses = data.IpAddresses;
string host = data.Host;
string organisationalDomain = data.OrganisationalDomain;
reverseDnsResponses = new List <ReverseDnsResponse>
{
new ReverseDnsResponse(host, ipAddresses, organisationalDomain)
};
}
DateTime date = reader.GetDateTime("date");
string originalIpAddress = reader.GetString("ip_address");
ReverseDnsResult reverseDnsResult = new ReverseDnsResult(originalIpAddress, reverseDnsResponses);
results.Add((date, reverseDnsResult));
tally++;
double percentDone = tally / 4500000d;
Console.Write($"\rLoaded {tally} = {percentDone:P2}% after {stopwatch.Elapsed.Minutes} mins {stopwatch.Elapsed.Seconds} secs");
}
}
Console.WriteLine($"\nFinished querying all results");
return(results);
}
public async Task <StatsSummary> GetProviderStatsSummary(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.SelectProviderRollupStatsSummary
: AggregateReportDaoV2.SelectProviderStatsSummary;
string commandText = new SqlBuilder()
.AddAggregateReportDefaults()
.AddCategoryFilter(categoryFilter)
.AddProviderFilter(providerFilter)
.Build(commandTemplate);
MySqlParameter[] parameters =
{
new MySqlParameter("domain", domain),
new MySqlParameter("providerFilter", FormatProvider(providerFilter)),
new MySqlParameter("startDate", startDate.ToString("yyyy-MM-dd")),
new MySqlParameter("endDate", endDate.ToString("yyyy-MM-dd"))
};
StatsSummary ipStatsSummary = null;
using (var reader = await MySqlHelper.ExecuteReaderAsync(connectionString, commandText, parameters))
{
while (await reader.ReadAsync())
{
ipStatsSummary = new StatsSummary(
reader.GetInt32("TotalEmails"),
reader.GetInt32("TotalFullyTrusted"),
reader.GetInt32("TotalPartiallyTrusted"),
reader.GetInt32("TotalQuarantined"),
reader.GetInt32("TotalRejected"),
reader.GetInt32("TotalUntrusted"));
}
}
return(ipStatsSummary);
}
public async Task <List <HistoryItem> > GetHistory()
{
string command =
"SELECT name as \'entity_id\', GROUP_CONCAT(record) as records, start_date\nFROM domain d\nLEFT JOIN dns_record_spf spf ON spf.domain_id = d.id\nWHERE (d.monitor = b\'1\' OR d.publish = b\'1\')\nAND spf.end_date IS NOT NULL\nGROUP BY name, start_date\nORDER BY name, start_date;";
List <HistoryItem> historyItems = new List <HistoryItem>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(_connectionInfo.ConnectionString, command))
{
while (await reader.ReadAsync())
{
historyItems.Add(new HistoryItem(
reader.GetString("entity_id"),
reader.GetString("records")?.Split(",", StringSplitOptions.RemoveEmptyEntries).ToList(),
reader.GetDateTime("start_date")));
}
}
return(historyItems);
}
private async Task <List <TlsRptEntityState> > SelectAll(string id)
{
List <TlsRptEntityState> list = new List <TlsRptEntityState>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(ConnectionString,
@"SELECT state FROM tls_rpt_entity WHERE id = @domain ORDER BY version;",
new MySqlParameter("domain", id)))
{
while (reader.Read())
{
string state = reader.GetString("state");
if (!string.IsNullOrWhiteSpace(state))
{
list.Add(JsonConvert.DeserializeObject <TlsRptEntityState>(state));
}
}
}
return(list);
}
public async Task <List <DkimEntityState> > GetHistoryForDomains(List <string> domains)
{
string domainsString = string.Join(",", domains.Select((v, i) => $"@domain{i}"));
string selectHistoryForDomains = $"SELECT state FROM dkim_entity_history_old where entity_id in ({domainsString});";
MySqlParameter[] parameters = domains.Select((v, i) => new MySqlParameter($"domain{i}", v)).ToArray();
List <DkimEntityState> states = new List <DkimEntityState>();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(_connectionInfo.ConnectionString,
selectHistoryForDomains, parameters))
{
while (await reader.ReadAsync())
{
states.Add(JsonConvert.DeserializeObject <DkimEntityState>(reader.GetString("state")));
}
}
return(states);
}
public async Task <List <string> > GetHostsUniqueToDomain(string domain)
{
List <string> hostnames = new List <string>();
if (string.IsNullOrEmpty(domain))
{
return(hostnames);
}
using (var connection = await CreateAndOpenConnection())
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connection,
MxStateDaoResources.GetHostsUniqueToDomain, new MySqlParameter("domain", ReverseUrl(domain))))
{
while (await reader.ReadAsync())
{
hostnames.Add(ReverseUrl(reader.GetString("hostname")));
}
}
return(hostnames);
}
public async Task <Dictionary <string, TlsEntityState> > GetTlsEntityStates(List <string> hostnames)
{
if (hostnames == null || hostnames.Count == 0)
{
return(null);
}
string connectionString = await _connectionInfo.GetConnectionStringAsync();
Dictionary <string, TlsEntityState> results = new Dictionary <string, TlsEntityState>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string query = string.Format(MxApiDaoResources.GetTlsEntityStates,
string.Join(',', hostnames.Select((_, i) => $"@hostname{i}")));
MySqlParameter[] parameters = hostnames
.Select((hostname, i) => new MySqlParameter($"hostname{i}", ReverseUrl(hostname)))
.ToArray();
await connection.OpenAsync();
using (DbDataReader reader = await MySqlHelper.ExecuteReaderAsync(connection, query, parameters))
{
while (await reader.ReadAsync())
{
string hostname = ReverseUrl(reader.GetString("hostname"));
if (!results.ContainsKey(hostname))
{
results.Add(hostname,
JsonConvert.DeserializeObject <TlsEntityState>(reader.GetString("state")));
}
}
}
connection.Close();
}
return(results);
}
public async Task <StatsSummary> GetIpStatsSummary(string domain, DateTime startDate, DateTime endDate,
string provider, string categoryFilter = null, string hostFilter = null)
{
string connectionString = await _connectionInfoAsync.GetConnectionStringAsync();
var sqlBuilder = new SqlBuilder()
.AddAggregateReportDefaults()
.AddCategoryFilter(categoryFilter);
string commandText = sqlBuilder.Build(AggregateReportDaoV2.SelectIpStatsSummary);
MySqlParameter[] parameters =
{
new MySqlParameter("domain", domain),
new MySqlParameter("provider", provider),
new MySqlParameter("hostFilter", string.IsNullOrWhiteSpace(hostFilter) ? null : hostFilter + "%"),
new MySqlParameter("startDate", startDate.ToString("yyyy-MM-dd")),
new MySqlParameter("endDate", endDate.ToString("yyyy-MM-dd"))
};
StatsSummary ipStatsSummary = null;
using (var reader = await MySqlHelper.ExecuteReaderAsync(connectionString, commandText, parameters))
{
while (await reader.ReadAsync())
{
ipStatsSummary = new StatsSummary(
reader.GetInt32("TotalEmails"),
reader.GetInt32("TotalFullyTrusted"),
reader.GetInt32("TotalPartiallyTrusted"),
reader.GetInt32("TotalQuarantined"),
reader.GetInt32("TotalRejected"),
reader.GetInt32("TotalUntrusted"));
}
}
return(ipStatsSummary);
}
