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