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