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