fix:统计查询优化

zhengyuxuan

2025-04-07 6b9333d8ba03965a221cec5ba8249900d0c3683c

fix:统计查询优化

 LifePayment/LifePayment.Application/LifePay/StatisticsService.cs

@@ -293,33 +293,34 @@

        private async Task<DallyStatistics> TopStatistics(string channleId, DateTime today)
        {
            var lifepayOrderBaseList = await _lifePayOrderRepository.Where(x => x.CreationTime < today)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).ToListAsync();

            /// 累计收款：统计平台账户下订单创建时间在昨天及之前收到的【用户支付成功的金额-退款给用户的金额】；
            var accumulatedReceipts = await _lifePayOrderRepository.Where(x => x.CreationTime < today && x.PayStatus != LifePayStatusEnum.未支付)
            .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).SumAsync(x => x.PayAmount) - 
            await _lifePayOrderRepository.Where(x => x.CreationTime < today && x.LifePayRefundStatus == LifePayRefundStatusEnum.已退款)
            .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).SumAsync(x => (x.RefundPrice ?? 0));
            var accumulatedReceipts = lifepayOrderBaseList.Where(x => x.PayStatus != LifePayStatusEnum.未支付).Sum(x => x.PayAmount)
               - lifepayOrderBaseList.Where(x => x.LifePayRefundStatus == LifePayRefundStatusEnum.已退款).Sum(x => (x.RefundPrice ?? 0));

            /// 昨日收款：统计平台账户下订单创建时间在昨天收到的【用户支付成功的金额-退款给用户的金额】；
            var receiptsYesterday = await _lifePayOrderRepository.Where(x => x.CreationTime >= today.AddDays(-1) && x.CreationTime < today && x.PayStatus == LifePayStatusEnum.已支付)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).SumAsync(x => x.PayAmount) -
                await _lifePayOrderRepository.Where(x => x.CreationTime >= today.AddDays(-1) && x.CreationTime < today && x.LifePayRefundStatus == LifePayRefundStatusEnum.已退款)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).SumAsync(x => (x.RefundPrice ?? 0));
            var receiptsYesterday = lifepayOrderBaseList.Where(x => x.CreationTime >= today.AddDays(-1) && x.PayStatus == LifePayStatusEnum.已支付)
                .Sum(x => x.PayAmount) -
                lifepayOrderBaseList.Where(x => x.CreationTime >= today.AddDays(-1) && x.LifePayRefundStatus == LifePayRefundStatusEnum.已退款)
                .Sum(x => (x.RefundPrice ?? 0));
            /// 累计收入：统计平台账户下订单状态为“已完成”且订单创建时间在昨天及之前收到的【用户实付金额-平台扣款金额-部分退款金额】；
            var accumulatedIncome = await _lifePayOrderRepository.Where(x => x.CreationTime < today && x.LifePayOrderStatus == LifePayOrderStatusEnum.已完成)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).SumAsync(x => x.PayAmount - (x.PlatformDeductionAmount ?? 0) - (x.RefundPrice ?? 0));
            var accumulatedIncome = lifepayOrderBaseList.Where(x => x.LifePayOrderStatus == LifePayOrderStatusEnum.已完成)
              .Sum(x => x.PayAmount - (x.PlatformDeductionAmount ?? 0) - (x.RefundPrice ?? 0));
            /// 昨日收入：统计平台账户下订单状态为“已完成”且订单创建时间在昨天收到的【用户实付金额-平台扣款金额-部分退款金额】；
            var yesterdayIncome = await _lifePayOrderRepository.Where(x => x.CreationTime >= today.AddDays(-1) && x.CreationTime < today && x.LifePayOrderStatus == LifePayOrderStatusEnum.已完成)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).SumAsync(x => x.PayAmount - (x.PlatformDeductionAmount ?? 0) - (x.RefundPrice ?? 0));
            var yesterdayIncome = lifepayOrderBaseList.Where(x => x.CreationTime >= today.AddDays(-1) && x.LifePayOrderStatus == LifePayOrderStatusEnum.已完成)
                .Sum(x => x.PayAmount - (x.PlatformDeductionAmount ?? 0) - (x.RefundPrice ?? 0));
            /// 累计下单：统计平台中订单下单时间在昨天及之前时间的订单记录；
            var accumulatedOrders = await _lifePayOrderRepository.Where(x => x.CreationTime < today).WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).CountAsync();
            var accumulatedOrders = lifepayOrderBaseList.Count();
            /// 昨日下单：统计平台中订单下单时间在昨天的订单记录；
            var ordersNumYesterday = await _lifePayOrderRepository.Where(x => x.CreationTime >= today.AddDays(-1) && x.CreationTime < today)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).CountAsync();
            var ordersNumYesterday = lifepayOrderBaseList.Where(x => x.CreationTime >= today.AddDays(-1)).Count();
            /// 昨日成功：统计平台中订单状态为“已完成/部分充值成功”且订单下单时间在昨天的订单记录；
            var yesterdaySuccess = await _lifePayOrderRepository.Where(x => x.CreationTime >= today.AddDays(-1) && x.CreationTime < today && x.LifePayOrderStatus == LifePayOrderStatusEnum.已完成)
                .WhereIf(string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).CountAsync();
            var yesterdaySuccess = lifepayOrderBaseList.Where(x => x.CreationTime >= today.AddDays(-1) && x.LifePayOrderStatus == LifePayOrderStatusEnum.已完成)
                .Count();
            /// 昨日失败：统计平台中订单状态为“充值失败/已退款”且订单下单时间在昨天的订单记录；
            var yesterdayFail = await _lifePayOrderRepository.Where(x => x.CreationTime >= today.AddDays(-1) && x.CreationTime < today && x.LifePayOrderStatus == LifePayOrderStatusEnum.已退款)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.ChannelId == channleId).CountAsync();
            var yesterdayFail = lifepayOrderBaseList.Where(x => x.CreationTime >= today.AddDays(-1) && x.LifePayOrderStatus == LifePayOrderStatusEnum.已退款)
                .Count();
            /// 累计用户
            var accumulatedUsers = await _lifePayUserRepository.Where(x => x.CreationTime < today)
                .WhereIf(!string.IsNullOrWhiteSpace(channleId), x => x.CreationChannleNum == channleId).CountAsync();