duanju_manage/modules/Statistic/Services/UserStatisticsService.php

<?php
/**
 *
 * @file:UserStatisticsService.php
 * @Date: 2023/6/20
 * @Time: 09:31
 */


namespace Modules\Statistic\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;
use Modules\Common\Services\BaseService;

class UserStatisticsService extends BaseService
{

    // 日期:小程序id
    protected const PROMOTION_STATISTIC_RECORD_REDIS_KEY = 'statistic:miniprogram:users_recode:%s:%s';
    protected const NEW_USER_NUM = 'new_user_num_%s'; // 当日新增用户数
    protected const NEW_USER_RECHARGE_NUM = "new_user_recharge_num_%s"; // 当日新增户充值人数
    protected const NEW_USER_RECHARGE_TOTAL = "new_user_recharge_total_%s"; // 当日新增户充值金额

    /**
     *
     * name: getTodayData
     * @param int $accountId 账号id
     * @param int $miniProgramId 小程序id
     * @param int $type 账号类型 1 其他 2 投放公司 3 优化师
     * date 2023/06/20 10:20
     */
    public static function getTodayData($accountId, $miniProgramId, $type = 1)
    {
        $date = date("Y-m-d");
        $key = sprintf(self::PROMOTION_STATISTIC_RECORD_REDIS_KEY, $date, $miniProgramId);
        $new_user_recharge_total = self::getValue($key, sprintf(self::NEW_USER_RECHARGE_TOTAL, $accountId)); // 当日新增用户充值总额
        $new_user_recharge_num = self::getValue($key, sprintf(self::NEW_USER_RECHARGE_NUM, $accountId)); // 当日新增用户充值人数
        $new_user_num = self::getValue($key, sprintf(self::NEW_USER_NUM, $accountId)); // 当日新增用户人数
        [$recharge_coin_num, $recharge_vip_num] = self::getRechargeUserNum($accountId, $miniProgramId, $type);
        $data = [
            'key' => $key,
            'account_id' =>  $accountId,
            'date' => $date,
            'new_user_recharge_total' => $new_user_recharge_total ?: 0,
            'new_user_recharge_num' => $new_user_recharge_num ?: 0,
            'new_user_num' => $new_user_num ?: 0,
            'recharge_coin_num' => $recharge_coin_num ?: 0,
            'recharge_vip_num' => $recharge_vip_num ?: 0,
        ];

        if ($new_user_num > 0 && $new_user_recharge_num > 0) {
            $data['recharge_rate'] = sprintf('%.2f%', ($new_user_recharge_num / $new_user_num) * 100);
        } else {
            $data['recharge_rate'] = 0;
            $data['recharge_mean'] = 0;
        }
        if ($new_user_recharge_total > 0 || $new_user_recharge_num > 0) {
            $data['recharge_mean'] = sprintf('%.2f', ($new_user_recharge_total / $new_user_recharge_num));
        } else {
            $data['recharge_mean'] = 0;
        }

        return $data;
    }

    // 获取redis值
    protected static function getValue($key, $field)
    {
        return Redis::hget($key, $field);
    }

    // 保存redis的值
    protected static function setValue($key, $field, $value)
    {
        Redis::hset($key, $field, $value);
    }

    /**
     *  充值人数查询
     * name: getRechargeUserNum
     * @param $accountId 账号id
     * @param  $miniProgramId 小程序id
     * @param mixed $type 账号类型 2 投放公司 3 优化师
     * date 2023/06/20 10:18
     */
    private static function getRechargeUserNum($accountId, $miniProgramId, mixed $type): array
    {
        $start = date("Y-m-d") . " 00:00:00";
        $end = date("Y-m-d") . " 23:59:59";
        if ($type == 2) {
            // 投放公司
            $recharge_coin_num = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where('miniprogram_id', $miniProgramId)
                ->where('puser_id', $accountId)->whereBetween("created_at", [$start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->where('order_type', 'COIN')->groupBy('uid', 'ranse_created_at')->count();
            $recharge_vip_num = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where('miniprogram_id', $miniProgramId)
                ->where('puser_id', $accountId)->whereBetween("created_at",[ $start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->where('order_type', '<>', 'COIN')->groupBy('uid', 'ranse_created_at')->count();
        } elseif ($type == 3) {
            // 优化师
            $recharge_coin_num = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where('miniprogram_id', $miniProgramId)
                ->where('user_id', $accountId)->whereBetween("created_at", [$start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->where('order_type', 'COIN')->groupBy('uid', 'ranse_created_at')->count();
            $recharge_vip_num = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where('miniprogram_id', $miniProgramId)
                ->where('user_id', $accountId)->whereBetween("created_at",[$start, $end])
                ->whereBetween('ranse_created_at',[ $start, $end])
                ->where('order_type', '<>', 'COIN')->groupBy('uid', 'ranse_created_at')->count();
        } else {
            // 其他暂不统计
            $recharge_coin_num = 0;
            $recharge_vip_num = 0;
        }
        return [$recharge_coin_num, $recharge_vip_num];
    }
}