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;

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 优化师
     * @param  int $minId  小程序id
     * date 2023/06/20 10:20
     * @return array
     */
    public static function getTodayData($accountId, $type = 1,$minId=0)
    {
        $date = date("Y-m-d");
        $date = "2023-06-25";
        $start = $date. " 00:00:00";
        $end = $date . " 23:59:59";

        $where  = [];
        if ($minId > 0){
            $where['miniprogram_id'] = $minId;
        }

        if ($type == 2){
            $where['puser_id']  = $accountId;
        }else if($type ==3){
            $where['user_id'] = $accountId;
        }
        $new_user_num = DB::table('user_ranse_record_all')->where($where)->where('date', $date)->count(); // 新增用户人数
        $info = DB::table('orders')->where('status', "<>", 'UNPAID')
            ->where($where)
            ->whereBetween("created_at", [$start, $end])
            ->whereBetween('ranse_created_at', [$start, $end])
            ->select(
                DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
            )->first();
        $new_user_recharge_total = $info->recharge_total ?: 0; // 新增用户充值总额
        $new_user_recharge_num = $info->recharge_num ?: 0; // 新增用户充值人数
        $info = DB::table('orders')->where('status', "<>", 'UNPAID')
            ->where($where)
            ->whereBetween("created_at", [$start, $end])
            ->whereBetween('ranse_created_at', [$start, $end])
            ->whereIn('order_type', ["COIN", "FIRST_COIN"])
            ->select(
                DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
            )->first(); // 普通充值金额
        $recharge_coin_total = $info->recharge_total ?: 0; // 新增用户普通充值总额
        $recharge_coin_num = $info->recharge_num ?: 0; // 新增用户普通充值总额
        $info = DB::table('orders')->where('status', "<>", 'UNPAID')
            ->where($where)
            ->whereBetween("created_at", [$start, $end])
            ->whereBetween('ranse_created_at', [$start, $end])
            ->whereNotIn('order_type', ["COIN", "FIRST_COIN"])
            ->select(
                DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
            )->first(); // 会员充值金额
        $recharge_vip_total = $info->recharge_total ?: 0; // 新增用户会员充值总额
        $recharge_vip_num = $info->recharge_num ?: 0; // 新增用户会员充值总额

        $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_total' => $recharge_coin_total ?: 0,
            'recharge_coin_num' => $recharge_coin_num ?: 0,
            'recharge_vip_total' => $recharge_vip_total ?: 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);
    }

    /**
     *
     * name: getTodayData
     * @param int $accountId 账号id
     * @param int $type 账号类型 1 其他 2 投放公司 3 优化师
     * date 2023/06/20 10:20
     * @return array
     */
    public static function getTodayHomeData(mixed $accountId, int $type)
     {
        $date = date("Y-m-d");
        $start = date("Y-m-d") . " 00:00:00";
        $end = date("Y-m-d") . " 23:59:59";
        if ($type == 1) {
            $new_user_num = DB::table('user_ranse_record_all')->where('date', $date)->count(); // 新增用户人数
            $info = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                    DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
                )->first();
            $new_user_recharge_total = $info->recharge_total ?: 0; // 新增用户充值总额
            $new_user_recharge_num = $info->recharge_num ?: 0; // 新增用户充值人数
            $info = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->whereIn('order_type', ["COIN", "FIRST_COIN"])
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                    DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
                )->first(); // 普通充值金额
            $new_user_recharge_coin_total = $info->recharge_total ?: 0; // 新增用户普通充值总额
            $new_user_recharge_coin_num = $info->recharge_num ?: 0; // 新增用户普通充值总额
            $info = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->whereNotIn('order_type', ["COIN", "FIRST_COIN"])
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                    DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
                )->first(); // 会员充值金额
            $new_user_recharge_vip_total = $info->recharge_total ?: 0; // 新增用户会员充值总额
            $new_user_recharge_vip_num = $info->recharge_num ?: 0; // 新增用户会员充值总额
            $info = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as  recharge_total"), // 单日新增用户充值金额
                    DB::raw("count(DISTINCT ranse_created_at,uid) as  recharge_num")  // 当日新增用户充值人数
                )->first();
            $recharge_total = $info->recharge_total ?: 0; // 充值金额
            // 充值笔数
           $recharge_num =  DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])->count();
            $recharge_coin_total = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->whereIn('order_type', ["COIN", "FIRST_COIN"])->sum('price'); // 普通充值金额
            $recharge_vip_total = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->whereNotIn('order_type', ["COIN", "FIRST_COIN"])->sum('price'); // 会员充值金额


        } else {
            $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)); // 当日新增用户人数
            if ($type == 2) {
                $where = ['puser_id' => $accountId];
            } else {
                $where = ['user_id' => $accountId];
            }
            $info = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where($where)
                ->whereBetween("created_at", [$start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->whereIn('order_type', ["COIN", "FIRST_COIN"])->select(
                DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
            )->first(); // 普通充值金额
            $new_user_recharge_coin_total = $info->recharge_total ?: 0; // 新增用户普通充值总额
            $new_user_recharge_coin_num = $info->recharge_num ?: 0; // 新增用户普通充值总额
            $info = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where($where)
                ->whereBetween("created_at", [$start, $end])
                ->whereBetween('ranse_created_at', [$start, $end])
                ->whereNotIn('order_type', ["COIN", "FIRST_COIN"])
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as recharge_total"), // 单日新增用户充值金额
                    DB::raw("count(DISTINCT ranse_created_at,uid) as recharge_num")  // 当日新增用户充值人数
                )->first(); // 会员充值金额
            $new_user_recharge_vip_total = $info->recharge_total ?: 0; // 新增用户会员充值总额
            $new_user_recharge_vip_num = $info->recharge_num ?: 0; // 新增用户会员充值总额
            $info = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->where($where)
                ->select(
                    DB::raw("sum(if(status = 'unpaid', 0, price)) as  recharge_total"), // 单日新增用户充值金额
                    DB::raw("count(DISTINCT ranse_created_at,uid) as  recharge_num")  // 当日新增用户充值人数
                )->first();
            $recharge_total = $info->recharge_total ?: 0; // 充值人数
            $recharge_num =  DB::table('orders')->where('status', "<>", 'UNPAID')
                ->whereBetween("created_at", [$start, $end])
                ->where($where)->count();
            $recharge_coin_total = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where($where)
                ->whereBetween("created_at", [$start, $end])
                ->whereIn('order_type', ["COIN", "FIRST_COIN"])->sum('price'); // 普通充值金额
            $recharge_vip_total = DB::table('orders')->where('status', "<>", 'UNPAID')
                ->where($where)
                ->whereBetween("created_at", [$start, $end])
                ->whereNotIn('order_type', ["COIN", "FIRST_COIN"])->sum('price'); // 会员充值金额

        }

        $data =  [
            'new_user_num' => $new_user_num, // 新增用户数
            'new_user_recharge_num' =>  $new_user_recharge_num, // 新增用户充值人数
            'new_user_recharge_coin_num' => $new_user_recharge_coin_num, // 新增用户普通充值人数
            'new_user_recharge_vip_num' => $new_user_recharge_vip_num, // 新增用户vip充值人数
            'new_user_recharge_total' =>  $new_user_recharge_total, // 新增用户充值金额
            'new_user_recharge_vip_total' => $new_user_recharge_vip_total, // 新增用户普通充值金额
            'new_user_recharge_coin_total' => $new_user_recharge_coin_total, // 新增用户vip充值金额
            'recharge_total' => $recharge_total, // 充值金额
            'recharge_coin_totall' => $recharge_coin_total, // 普通充值金额
            'recharge_vip_total' => $recharge_vip_total, // VIP 充值金额
            'recharge_num' => $recharge_num, // 充值笔数

        ];

        // 新增用户充值率
        $data['new_user_recharge_rate'] = $data['new_user_num'] >  0 && $data['new_user_recharge_num'] >  0 ? sprintf("%.2f%s",$data['new_user_recharge_num']/$data['new_user_num'],"%") : 0;
       // 新增用户人均充值
        $data['new_user_mean'] = $data['new_user_num'] >  0 && $data['new_user_recharge_total'] >  0 ? sprintf("%.2f",$data['new_user_recharge_total']/$data['new_user_num']) : 0;
        $data['recharge_mean'] = $data['recharge_total'] >  0 && $data['recharge_num'] >  0 ? sprintf("%.2f",$data['recharge_total']/$data['recharge_num'],"%") : 0; // 新增用户人均充值
        return $data;

    }


    /**
     * 保存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: getTodayData
     * @param int $accountId 账号id
     * @param mixed $type 账号类型 1 其他 2 投放公司 3 优化师
     * @param mixed $miniProgramId 小程序id
     */
    public static function historyStats(int $accountId, $type,$miniProgramId)
    {
        $month = self::getStaticsData($accountId, date('Y-m-01'), date('Y-m-d', strtotime('yesterday')), $type,$miniProgramId);
        $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,$miniProgramId);
        $history = self::getHistoryData($accountId, $type,$miniProgramId);
        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(new_user_total) as new_user_total"), // 总充金额
            DB::raw("sum(recharge_user_total) as recharge_user_total"), // 累计充值人数
        )->first();

        return [
            'recharge_amount_total' => $info->recharge_amount_total ?: 0,
            'new_user_total' => $info->new_user_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;
    }


}