コード例 #1
0
        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,
                    });
                }
            }
        }
コード例 #2
0
        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,
                    });
                }
            }
        }
コード例 #3
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);
                }
            }
        }
コード例 #4
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,
                    });
                }
            }
        }