public void InitializesConnection(string provider)
{
var test = new DbContextOptionsBuilder();
SqlConnectionBuilder.Build(provider, "test", test);
Assert.True(test.IsConfigured);
}
public async Task <List <RecruitmentMessage> > GetRecruitmentMessages()
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
var calls = await connection.QueryAsync <RecruitmentMessage>(Sql.GetRecruitmentMessages.Value);
return(calls.ToList());
}
}
/// <summary>
/// Runs the specified services.
/// </summary>
/// <param name="services">The services.</param>
/// <param name="configuration">The configuration.</param>
public void Run(IServiceCollection services, IConfigurationRoot configuration)
{
var settings = configuration.GetSection(Section).Get <MonitorSettings>();
services.AddDbContext <DatastoreContext>(options => SqlConnectionBuilder.Build(settings.Provider, settings.ConnectionString, options));
services.AddScoped <IMonitorService, MonitorService>();
services.AddScoped <IDatastoreRepository, DatastoreRepository>();
services.AddSingleton <IClient, Client>();
}
public async Task <List <Transaction> > GetTransactions()
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
var calls = await connection.QueryAsync <Transaction>(Sql.GetTransactions.Value);
return(calls.ToList());
}
}
public async Task <List <Call> > GetCalls()
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
var sql = $@"{Sql.GetCalls.Value}";
var calls = await connection.QueryAsync <Call>(sql);
return(calls.ToList());
}
}
public async Task <RecruitmentMessageMetrics> GetOverviewRecruitmentMessageMetrics(
DateTime?startDate,
DateTime endDate,
DateTime?previousPeriodStartDate,
DateTime?previousPeriodEndDate)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
if (!startDate.HasValue)
{
// The only case for this is the All Time selection
// Dapper seems to have some optimizations that break and can potentially time out if you pass in parameters
// and then change those parameters in the sql that is run, so this is being run separately
using (var firstDataPoint = connection.QueryFirstAsync <DateTime?>(GetFirstDataPoint.Value))
{
startDate = previousPeriodEndDate = previousPeriodStartDate = await firstDataPoint;
}
}
var sql = $@"{GetCalendarDatesInRange.Value(previousPeriodStartDate)}
{GetTotal.Value}
{GetTotalsByDayOfWeek.Value}
{GetTotalsByPeriod.Value}
{GetTotalsByWeek.Value}";
using (var multi = await connection.QueryMultipleAsync(sql, new
{
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
}))
{
var days = await multi.ReadAsync <DateTime>();
var totalDto = await multi.ReadSingleAsync <TotalEntity>();
var messagesByDayOfWeekDtos = await multi.ReadAsync <PerDayOfWeekEntity>();
var messagePerformanceDtos = await multi.ReadAsync <PerformanceEntity>();
var messageWeekOverWeek = await multi.ReadAsync <WeekOverWeekEntity>();
return(new RecruitmentMessageMetrics()
{
MessagesByDayOfWeek = _entityMapper.BuildByDayOfWeek <RecruitmentMessageByDayOfWeek>(messagesByDayOfWeekDtos.ToList()),
MessagePerformance = _entityMapper.BuildByPeriod(days.ToList(), messagePerformanceDtos.ToList(), previousPeriodEndDate),
WeekOverWeek = _mapper.Map <List <ByWeek> >(messageWeekOverWeek),
TotalMessages = totalDto.Total,
UniqueContacts = totalDto.UniqueContacts,
});
}
}
}
public async Task InsertAttachment(Attachment attachment, string messageId)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
var sql = $@"{MergeAttachment.Value}";
await connection.ExecuteAsync(sql, new
{
attachment.AttachmentId,
MessageId = messageId,
attachment.Base64Data,
attachment.FileName,
});
}
}
public void Run(IServiceCollection services, IConfigurationRoot configuration)
{
RegisterOptions(services, configuration);
var databaseSettings = configuration.GetSection("Authentication").Get <AuthenticationConfiguration>();
services.AddDbContext <ApplicationDbContext>(options => SqlConnectionBuilder.Build(databaseSettings.Provider, databaseSettings.ConnectionString, options));
services
.AddIdentity <ApplicationUser, IdentityRole>(options =>
{
options.Password.RequireNonAlphanumeric = false;
})
.AddEntityFrameworkStores <ApplicationDbContext>()
.AddDefaultTokenProviders();
var jwtOptions = services.BuildServiceProvider().GetService <IOptionsSnapshot <JwtOptions> >().Value;
var tokenValidationParameters = new TokenValidationParameters
{
ValidateIssuerSigningKey = true,
IssuerSigningKey = jwtOptions.SigningKey,
ValidateIssuer = true,
ValidIssuer = jwtOptions.Issuer,
ValidateAudience = false,
ValidAudience = jwtOptions.Audience,
ValidateLifetime = true,
ClockSkew = TimeSpan.Zero,
RoleClaimType = ClaimTypes.Role
};
services.AddAuthentication(options =>
{
options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
})
.AddJwtBearer(options =>
{
options.TokenValidationParameters = tokenValidationParameters;
});
// Update token settings when JwtOptions change
services.BuildServiceProvider()
.GetService <IOptionsMonitor <JwtOptions> >()
.OnChange(x => tokenValidationParameters.IssuerSigningKey = x.SigningKey);
}
public async Task <int> InsertCall(Call call)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
var sql = $@"{MergeCall.Value}";
await connection.ExecuteAsync(sql, new
{
call.CallId,
call.PhoneNumber,
call.Name,
call.OccurredDate,
call.IsIncoming,
call.Duration
});
}
return(1);
}
public async Task <int> InsertMessage(Message message)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
await connection.ExecuteAsync(MergeMessage.Value, new
{
message.MessageId,
message.PhoneNumber,
message.Name,
message.OccurredDate,
message.IsIncoming,
message.IsMedia,
message.Text,
message.TextLength,
message.ThreadId
});
}
return(1);
}
public async Task <int> InsertRecruitmentMessage(RecruitmentMessage message)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
await connection.ExecuteAsync(MergeRecruitmentMessage.Value, new
{
message.RecruitmentMessageId,
message.RecruiterId,
message.MessageSource,
message.RecruiterName,
message.RecruiterCompany,
message.Subject,
message.Body,
message.IsIncoming,
message.OccurredDate,
});
}
return(1);
}
public async Task <int> InsertChatMessage(ChatMessage message)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
await connection.ExecuteAsync(MergeChatMessages.Value, new
{
message.ChatMessageId,
message.GroupId,
message.GroupName,
message.SenderName,
message.SenderId,
message.OccurredDate,
message.IsIncoming,
message.Text,
message.IsMedia,
message.TextLength,
});
}
return(1);
}
public async Task <int> InsertTransaction(Transaction transaction)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
await connection.ExecuteAsync(MergeTransaction.Value, new
{
transaction.TransactionId,
transaction.AccountId,
transaction.AccountName,
transaction.MerchantId,
transaction.Amount,
transaction.Description,
transaction.IsCashIn,
transaction.IsCashOut,
transaction.CategoryId,
transaction.Labels,
transaction.OccurredDate,
});
}
return(1);
}
public async Task <int> InsertRide(Ride ride)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
await connection.ExecuteAsync(MergeRide.Value, new
{
ride.RideId,
ride.RideType,
ride.OriginLat,
ride.OriginLong,
ride.DestinationLat,
ride.DestinationLong,
ride.RequestDate,
ride.PickupDate,
ride.DropoffDate,
ride.Price,
ride.Distance,
});
}
return(1);
}
public void Throws_Exception_When_Provider_Is_Not_Recognized()
{
var result = Assert.Throws <Exception>(() => SqlConnectionBuilder.Build("DONTEXIST", string.Empty, null));
Assert.True(result.Message == "No provider found for DONTEXIST");
}
public async Task <MessageMetrics> GetOverviewMessageMetrics(
DateTime?startDate,
DateTime endDate,
DateTime?previousPeriodStartDate,
DateTime?previousPeriodEndDate)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
if (!startDate.HasValue)
{
// The only case for this is the All Time selection
// Dapper seems to have some optimizations that break and can potentially time out if you pass in parameters
// and then change those parameters in the sql that is run, so this is being run separately
using (var firstDatePoint = connection.QuerySingleAsync <DateTime?>(GetFirstDataPoint.Value))
{
startDate = previousPeriodEndDate = previousPeriodStartDate = await firstDatePoint;
}
}
var sql =
$@"{GetCalendarDatesInRange.Value(previousPeriodStartDate)}
{GetTotalsByGender.Value}
{GetTotal.Value}
{GetTotalsByDayOfWeek.Value}
{GetTotalsByHour.Value}
{GetTotalIncomingOutgoing.Value}
{GetTotalsByPeriod.Value}
{GetTotalsByWeek.Value}";
using (var multi = connection.QueryMultiple(sql,
new
{
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
}))
{
var days = await multi.ReadAsync <DateTime>();
var messageByGenderDtos = await multi.ReadAsync <MessageByGenderEntity>();
var totalDto = await multi.ReadSingleAsync <TotalEntity>();
var messagesByDayOfWeekDtos = await multi.ReadAsync <PerDayOfWeekEntity>();
var messagesByHourDtos = await multi.ReadAsync <PerHourEntity>();
var messagesInVsOutDtos = await multi.ReadAsync <InVsOutDto>();
var messagePerformanceDtos = await multi.ReadAsync <PerformanceEntity>();
var messageWeekOverWeek = await multi.ReadAsync <WeekOverWeekEntity>();
var metrics = new MessageMetrics
{
AverageOutgoingTextLengthFemale = _entityMapper.GetAverageTextLengthByGender(messageByGenderDtos.ToList(), "F"),
AverageOutgoingTextLengthMale = _entityMapper.GetAverageTextLengthByGender(messageByGenderDtos.ToList(), "M"),
TotalMessagesFemale = _entityMapper.GetTotalMessagesByGender(messageByGenderDtos.ToList(), "F"),
TotalMessagesMale = _entityMapper.GetTotalMessagesByGender(messageByGenderDtos.ToList(), "M"),
MessagesByDayOfWeek = _entityMapper.BuildByDayOfWeek <MessageByDayOfWeek>(messagesByDayOfWeekDtos.ToList()),
MessagesByHour = _entityMapper.BuildByHour <MessageByHour>(messagesByHourDtos.ToList()),
MessagePerformance = _entityMapper.BuildByPeriod(days.ToList(), messagePerformanceDtos.ToList(), previousPeriodEndDate),
WeekOverWeek = _mapper.Map <List <ByWeek> >(messageWeekOverWeek),
TotalMessages = totalDto.Total,
UniqueContacts = totalDto.UniqueContacts,
TotalMessagesIncoming = messagesInVsOutDtos.FirstOrDefault(d => d.IsIncoming)?.Total,
TotalMessagesOutgoing = messagesInVsOutDtos.FirstOrDefault(d => !d.IsIncoming)?.Total
};
return(metrics);
}
}
}
public async Task <CallMetrics> GetOverviewCallMetrics(
DateTime?startDate,
DateTime endDate,
DateTime?previousPeriodStartDate,
DateTime?previousPeriodEndDate)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
if (!startDate.HasValue)
{
// The only case for this is the All Time selection
// Dapper seems to have some optimizations that break and can potentially time out if you pass in parameters
// and then change those parameters in the sql that is run, so this is being run separately
using (var firstDatePoint = connection.QuerySingleAsync <DateTime?>(GetFirstDataPoint.Value))
{
startDate = previousPeriodEndDate = previousPeriodStartDate = await firstDatePoint;
}
}
var sql =
$@"{GetCalendarDatesInRange.Value(previousPeriodStartDate)}
{GetTotal.Value}
{GetTotalDuration.Value}
{GetTotalKnownFemaleDuration.Value}
{GetTotalKnownMaleDuration.Value}
{GetTotalsByDayOfWeek.Value}
{GetTotalsByHour.Value}
{GetTotalIncomingOutgoing.Value}
{GetTotalsByPeriod.Value}
{GetTotalsByWeek.Value}";
using (var multi = connection.QueryMultiple(sql, new
{
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
}))
{
var days = await multi.ReadAsync <DateTime>();
var totalCalls = await multi.ReadSingleAsync <int>();
var totalDuration = await multi.ReadSingleAsync <int>();
var totalKnownFemaleDuration = await multi.ReadSingleAsync <int>();
var totalKnownMaleDuration = await multi.ReadSingleAsync <int>();
var callsByDayOfWeekDto = await multi.ReadAsync <PerDayOfWeekEntity>();
var callsByHourDto = await multi.ReadAsync <PerHourEntity>();
var callsInVsOutDto = await multi.ReadAsync <InVsOutDto>();
var callsPerformance = await multi.ReadAsync <PerformanceEntity>();
var weekOverWeekDtos = await multi.ReadAsync <WeekOverWeekEntity>();
return(new CallMetrics
{
CallsByDayOfWeek = _entityMapper.BuildByDayOfWeek <CallByDayOfWeek>(callsByDayOfWeekDto.ToList()),
CallsByHour = _entityMapper.BuildByHour <CallByHour>(callsByHourDto.ToList()),
CallPerformance = _entityMapper.BuildByPeriod(days.ToList(), callsPerformance.ToList(), previousPeriodEndDate),
WeekOverWeek = _mapper.Map <List <ByWeek> >(weekOverWeekDtos),
TotalCalls = totalCalls,
TotalKnownFemaleDurationSeconds = totalKnownFemaleDuration,
TotalKnownMaleDurationSeconds = totalKnownMaleDuration,
TotalKnownDurationSeconds = totalKnownMaleDuration + totalKnownFemaleDuration,
TotalDurationSeconds = totalDuration,
TotalCallsIncoming = callsInVsOutDto.FirstOrDefault(d => d.IsIncoming)?.Total ?? 0,
TotalCallsOutgoing = callsInVsOutDto.FirstOrDefault(d => !d.IsIncoming)?.Total ?? 0,
});
}
}
}
public async Task <RideMetrics> GetOverviewRideMetrics(
DateTime?startDate,
DateTime endDate,
DateTime?previousPeriodStartDate,
DateTime?previousPeriodEndDate)
{
using (var connection = SqlConnectionBuilder.Build(_configuration.Value.DB_CONNECTION_STRING))
{
if (!startDate.HasValue)
{
// The only case for this is the All Time selection
// Dapper seems to have some optimizations that break and can potentially time out if you pass in parameters
// and then change those parameters in the sql that is run, so this is being run separately
using (var firstDatePoint = connection.QuerySingleAsync <DateTime?>(GetFirstDataPoint.Value))
{
startDate = previousPeriodEndDate = previousPeriodStartDate = await firstDatePoint;
}
}
var sql =
$@"{GetCalendarDatesInRange.Value(previousPeriodStartDate)}
{GetTotals.Value}
{GetTotalsByDayOfWeek.Value}
{GetTotalsByHour.Value}
{GetTotalsByPeriod.Value}
{GetTotalsByWeek.Value}";
using (var multi = connection.QueryMultiple(sql,
new
{
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
}))
{
var days = await multi.ReadAsync <DateTime>();
var totalDto = await multi.ReadSingleAsync <RideTotalEntity>();
var ridesByDayOfWeekDtos = await multi.ReadAsync <PerDayOfWeekEntity>();
var ridesByHourDtos = await multi.ReadAsync <PerHourEntity>();
var ridesPerformanceDtos = await multi.ReadAsync <PerformanceEntity>();
var ridesWeekOverWeek = await multi.ReadAsync <WeekOverWeekEntity>();
return(new RideMetrics()
{
RidesByDayOfWeek = _entityMapper.BuildByDayOfWeek <RideByDayOfWeek>(ridesByDayOfWeekDtos.ToList()),
RidesByHour = _entityMapper.BuildByHour <RideByHour>(ridesByHourDtos.ToList()),
RidePerformance = _entityMapper.BuildByPeriod(days.ToList(), ridesPerformanceDtos.ToList(), previousPeriodEndDate),
WeekOverWeek = _mapper.Map <List <ByWeek> >(ridesWeekOverWeek),
TotalRides = totalDto.Total,
AverageSecondsWaiting = totalDto.AverageSecondsWaiting,
TotalSecondsWaiting = totalDto.TotalSecondsWaiting,
AverageSecondsDriving = totalDto.AverageSecondsDriving,
TotalSecondsDriving = totalDto.TotalSecondsDriving,
ShortestRide = totalDto.ShortestRide,
LongestRide = totalDto.LongestRide,
AverageDistance = totalDto.AverageDistance,
TotalDistance = totalDto.TotalDistance,
TotalPrice = totalDto.TotalPrice,
AveragePrice = totalDto.AveragePrice,
MostExpensivePrice = totalDto.MostExpensivePrice,
FarthestDistance = totalDto.FarthestDistance,
});
}
}
}