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\Models\TjOptimizerDayNewUser;
use Modules\Common\Services\BaseService;
use Modules\Common\Services\Statistic\CompanyDayUser;
use Modules\Common\Services\Statistic\OptimizerDayUser;

class UserStatisticsService extends BaseService
{

    // key 日期:
    protected const PROMOTION_STATISTIC_RECORD_REDIS_KEY = 'statistic:miniprogram:users_recode:%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"; // 当日新增户充值金额
    protected const RANSE_NEW_USER_COUNT = "promotion:newUserCount:%s"; // 推广链接新用户统计

    /**
     *
     * name: getTodayData
     * @param int $accountId 账号id
     * @param int $type 账号类型 1 其他 2 投放公司 3 优化师
     * date 2023/06/20 10:20
     * @return array
     */
    public static function getTodayData($accountId, $type = 1)
    {
        $date = date("Y-m-d");
        $key = sprintf(self::PROMOTION_STATISTIC_RECORD_REDIS_KEY, $date);
        $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, $type);
        $data = [
            '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%s", ($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%s', ($new_user_recharge_total / $new_user_recharge_num), "%");
        } else {
            $data['recharge_mean'] = 0;
        }

        return $data;
    }

    /**
     * 获取redis值
     * name: getValue
     * @param $key
     * @param $field
     * @return mixed
     * date 2023/06/25 15:57
     */
    protected static function getValue($key, $field)
    {
        return Redis::hget($key, $field);
    }



    /**
     * 保存redis的值
     * name: setValue
     * @param $key
     * @param $field
     * @param $value
     * date 2023/06/25 15:57
     */
    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, 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('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('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('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('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];
    }

    /**
     *
     * name: getTodayData
     * @param int $accountId 账号id
     * @param mixed $type 账号类型 1 其他 2 投放公司 3 优化师
     */
    public static function historyStats(int $accountId, $type)
    {
        print_sql();
        $month = self::getStaticsData($accountId,date('Y-m-01'), date('Y-m-d', strtotime('yesterday')), $type);
        $lastMonth = self::getStaticsData($accountId,date('Y-m-01',strtotime(date('Y-m-01'))-10), date('Y-m-d',strtotime(date('Y-m-01'))-10), $type);
        $history = self::getHistoryData($accountId,$type);
        return ['month' => $month,'lastMonth' => $lastMonth,'history' => $history];
    }

    /**
     *   历史汇总
     * name: getMonthData
     * @param int $accountId 账号
     * @param mixed $type 角色类型 账号类型 1 其他 2 投放公司 3 优化师
     * @param  mixed $minId 小程序id
     * date 2023/06/27 11:19
     */
    protected static function getHistoryData($accountId, $type, $minId = 0)
    {
        $date = date('Y-m-d', strtotime('yesterday'));
        $sql = TjOptimizerDayNewUser::where('date',$date);
        if ($type == 2) {
            $sql->where('puser_id', $accountId);
        } elseif ($type == 3) {
            $sql->where('puser_id', $accountId);
        }
        if ($minId){
            $sql->where('miniprogram_id',$minId);
        }
        $info = $sql->select(
            DB::raw("sum(recharge_amount_total) as recharge_amount_total"), // 总充金额
            DB::raw("sum(recharge_user_total) as recharge_user_total"), // 累计充值人数
        )->first();

        return [
            'recharge_amount_total' => $info->recharge_amount_total?:0,
            'recharge_user_total' => $info->recharge_user_total?: 0,
        ];
    }

    /**
     *    日期范围的统计
     * name: getMonthData
     * @param int $accountId 账号
     * @param mixed $type 角色类型 账号类型 1 其他 2 投放公司 3 优化师
     * @param  mixed $minId 小程序id
     * date 2023/06/27 11:19
     */
    private static function getStaticsData($accountId, $start, $end,$type,$minId = 0)
    {
        $sql = TjOptimizerDayNewUser::where('date', '>=', $start)->where("date", "<=", $end);
        if ($type == 2) {
            $sql->where('puser_id', $accountId);
        } elseif ($type == 3) {
            $sql->where('puser_id', $accountId);
        }
        if ($minId){
            $sql->where('miniprogram_id',$minId);
        }
        return self::handleStaticData($sql);

    }

    /**
     *   统计数据处理
     * name: handelStaticData
     * @param $sql
     * @return array
     * date 2023/06/27 14:51
     */
    protected  static function handleStaticData($sql): array
    {
        $info =  $sql->select(
            DB::raw("sum(new_user_recharge_total) as new_user_recharge_total"), // 充值人数
            DB::raw("sum(new_user_num) as new_user_num"), // 新增用户人数
            DB::raw("sum(new_user_recharge_num) as new_user_recharge_num"), // 充值人数
            DB::raw("sum(new_user_recharge_vip_num) as new_user_recharge_vip_num"), // VIP充值人数
            DB::raw("sum(new_user_recharge_coin_num) as new_user_recharge_coin_num") // 普通充值人数
        )->first();
        return [
            'new_user_recharge_total' => $info->new_user_recharge_total?:0,
            'new_user_num' => $info->new_user_num?: 0,
            'new_user_recharge_num' => $info->new_user_recharge_num?:0,
            'new_user_recharge_vip_num' => $info->new_user_recharge_vip_num?: 0,
            'new_user_recharge_coin_num' => $info->new_user_recharge_coin_num ?:0,
        ];
    }


    /**
     * 统计列表
     */
    public static function list($param)
    {
        $sql = self::getQuerySql($param)->select("tj_optimizer_day_new_users.*", 'users.username')->orderBy('date', 'desc')->orderBy('id', 'desc');
        $isAll = getProp($param, 'is_all');
        if ($isAll) {
            $list = $sql->get();
        } else {
            $list = $sql->paginate(getProp($param, 'limit', 15));
        }
        if ($list->isEmpty()) {
            return $list;
        }
        $companyIds = $list->pluck('puser_id')->unique()->toArray();

        $companys = DB::table('users')->whereIn('id', $companyIds)->get();

        foreach ($list as $value) {
            // 充值率
            $value->recharge_rate = $value->new_user_recharge_num > 0 && $value->new_user_num > 0 ? sprintf("%.2f%s", $value->new_user_recharge_num / $value->new_user_num, "%") : 0;
            // 人均充值
            $value->recharge_mean = $value->new_user_recharge_num > 0 && $value->new_user_recharge_total > 0 ? sprintf("%.2f%s", $value->new_user_recharge_total / $value->new_user_recharge_num, "%") : 0;
            $value->company_name = $companys->where('id', $value->puser_id)->value('username');
        }
        return $list;
    }

    // 列表数据统计
    public static function listStatistics($param): array
    {
        $sql = self::getQuerySql($param);
        return self::handleStaticData($sql);
    }

    private static function getQuerySql(array $param)
    {
        $sql = TjOptimizerDayNewUser::query()->leftJoin('users', 'users.id', "=", 'tj_optimizer_day_new_users.user_id');
        if (getProp($param, 'start_at')) {
            $sql->where('tj_optimizer_day_new_users.date', ">=", $param['start_at']);
        }
        if (getProp($param, 'end_at')) {
            $sql->where('tj_optimizer_day_new_users.date', "<=", $param['end_at']);
        }
        if (getProp($param, 'user_id')) {
            $sql->where('tj_optimizer_day_new_users.user_id', $param['user_id']);
        }
        if (getProp($param, 'puser_id')) {
            $sql->where('tj_optimizer_day_new_users.puser_id', $param['puser_id']);
        }
        if (getProp($param, 'miniprogram_id')) {
            $sql->where('tj_optimizer_day_new_users.miniprogram_id', $param['miniprogram_id']);
        }
        return $sql;
    }


}