duanju_manage/modules/Common/Services/Statistic/OptimizerDayUser.php

<?php
/**
 *
 * @file:PromotionDayUser.php
 * @Date: 2023/6/25
 * @Time: 16:58
 */


namespace Modules\Common\Services\Statistic;

use Illuminate\Support\Facades\DB;
use Modules\Common\Models\TjOptimizerDayNewUser;
use Modules\Common\Services\BaseService;

class OptimizerDayUser extends BaseService
{
    protected static $miniPrograms; // 小程序
    protected static $newUserStatisc; // 优化师
    protected static $orderStatisc; // 充值统计
    protected static $orderVipStatisc; // 充值会员统计
    protected static $orderCoinStatisc; // 充值会员统计

    /**
     * 优化师用户统计
     * name:optimizerDayUserStatistic
     * @param $date
     * date 2023/06/25 17:01
     */
    public static function optimizerDayUserStatistic($date)
    {

        self::getMiniProgram(); // 获取小程序
        self::getStatisticOrders($date); // 进行订单统计
        self::getStatisticNewUser($date);

        //  从优化师维度出发统计
        DB::table('users', 'u')
            ->leftJoin('user_has_roles as  ur', 'ur.user_id', '=', 'u.id')
            ->leftJoin('roles as r', 'r.id', '=', 'ur.role_id')
            ->where('r.identify', 'optimizer')
            ->select("u.id as  user_id", 'u.pid as  puser_id')->orderBy('u.id')
            ->chunk(10, function ($items) use ($date) {
                foreach ($items as $v) {
                    self::getOptimizerDayData($v, $date);
                }
            });
    }

    /**
     *  优化师统计
     * name: getOptimizerDayData
     * @param  $date 日期
     * @param $data 账号
     *  $data = [
     *          'user_id' => 10, // 优化师id
     *          'puser_id' => 2, // 公司账号id
     *        ]
     *
     * @return array|int[]
     * date 2023/06/26 17:24
     */
    private static function getOptimizerDayData($data, $date)
    {
        $userId = getProp($data, 'user_id');
        $puserId = getProp($data, 'puser_id');
        $userHasMin = DB::table('user_has_miniprograms')->where('uid', $userId)->get();
        $userHasMin = $userHasMin->pluck('miniprogram_id')->unique()->toArray();
        if ($userHasMin) {
            foreach ($userHasMin as $value) {
                $userData = self::$newUserStatisc->where('user_id', $userId)->where('miniprogram_id', $value)->first();
                $order = self::$orderStatisc->where('user_id', $userId)->where('miniprogram_id', $value)->first();
                $vip = self::$orderVipStatisc->where('user_id', $userId)->where('miniprogram_id', $value)->first();
                $coin = self::$orderCoinStatisc->where('user_id', $userId)->where('miniprogram_id', $value)->first();
                $min = self::$miniPrograms->where('id', $value)->first();
                $data = [
                    "user_id" => $userId,
                    'puser_id' => $puserId,
                    'miniprogram_id' => $value,
                    'date' => $date,
                    'new_user_num' => getProp($userData, 'new_user_num', 0),
                    'new_user_recharge_num' => getProp($order, 'new_user_recharge_num', 0),
                    'new_user_recharge_total' => getProp($order, 'new_user_recharge_total', 0),
                    'new_user_recharge_vip_num' => getProp($vip, 'new_user_recharge_vip_num', 0),
                    'new_user_recharge_coin_num' => getProp($coin, 'new_user_recharge_coin_num', 0),
                    'miniprogram_name' => getProp($min, 'name', ''),
                    'miniprogram_play_name' => getProp($min, 'play_name', ''),
                    'miniprogram_type' => getProp($min, 'type', ''),
                ];
                $last = TjOptimizerDayNewUser::where('user_id', $userId)->where('miniprogram_id',$value)->where('puser_id', $puserId)->where('date', "<", $date)->orderBy('date', "desc")->first();
                if (!is_empty($last)) {
                    $data['recharge_user_total'] = $last->recharge_user_total + $data['new_user_recharge_num'];
                    $data['recharge_amount_total'] = $last->recharge_amount_total + $data['new_user_recharge_total'];
                    $data['new_user_total'] = $last->new_user_total + $data['new_user_num'];
                } else {
                    $data['recharge_user_total'] = $data['new_user_recharge_num'];
                    $data['new_user_total'] = $data['new_user_num'];
                    $data['recharge_amount_total'] =  $data['new_user_recharge_total'];
                }
                TjOptimizerDayNewUser::updateOrCreate(['user_id' => $data['user_id'], 'puser_id' => $data['puser_id'], 'date' => $data['date'],'miniprogram_id' => $data['miniprogram_id']], $data);
            }
        }
    }

    /**
     *   获取系统所有小程序
     * name: getMiniProgram
     * date 2023/06/26 17:21
     */
    private static function getMiniProgram()
    {
        self::$miniPrograms = DB::table('miniprogram')->get();
    }

    /**
     *  统计当日订单
     * name: getStatisticOrders
     * @param $date
     * date 2023/06/26 17:21
     */
    private static function getStatisticOrders($date)
    {
        $start = $date . " 00:00:00";
        $end = $date . " 23:59:59";
        self::$orderStatisc = DB::table('orders')->where('status', "<>", 'UNPAID')
            ->whereBetween("created_at", [$start, $end])
            ->whereBetween('ranse_created_at', [$start, $end])
            ->select(
                "user_id",
                'puser_id',
                "miniprogram_id",
                DB::raw("sum(if(status = 'unpaid', 0, price)) as  new_user_recharge_total"), // 单日新增用户充值金额
                DB::raw("count(DISTINCT ranse_created_at,uid) as new_user_recharge_num")  // 当日新增用户充值人数
            )->groupBy('user_id', 'miniprogram_id')->get();
        self::$orderVipStatisc = DB::table('orders')->where('status', "<>", 'UNPAID')
            ->whereBetween("created_at", [$start, $end])
            ->whereBetween('ranse_created_at', [$start, $end])->whereNotIn("order_type", ["COIN", "FIRST_COIN"])
            ->select(
                "user_id",
                'puser_id',
                "miniprogram_id",
                DB::raw("COUNT(DISTINCT ranse_end_at,uid) as new_user_recharge_vip_num") // 会员充值人数
            )->groupBy('user_id', 'miniprogram_id')->get();
        self::$orderCoinStatisc = DB::table('orders')->where('status', "<>", 'UNPAID')
            ->whereBetween("created_at", [$start, $end])
            ->whereBetween('ranse_created_at', [$start, $end])->whereIn("order_type", ["COIN", "FIRST_COIN"])
            ->select(
                "user_id",
                'puser_id',
                "miniprogram_id",
                DB::raw("COUNT(DISTINCT ranse_end_at,uid) as new_user_recharge_coin_num") // 普通充值人数
            )->groupBy('user_id', 'miniprogram_id')->get();
    }

    /**
     *  当日新用户统计
     * name: getStatisticNewUser
     * @param $date
     * date 2023/06/26 17:21
     */
    private static function getStatisticNewUser($date)
    {
        self::$newUserStatisc = DB::table('user_ranse_record_all')->where('date', "=", $date)
            ->select(
                "user_id", 'puser_id', 'miniprogram_id', 'miniprogram_type', 'date',
                DB::raw('count(user_id) as new_user_num')
            )->orderBy('user_id')
            ->groupBy('user_id', 'miniprogram_id')->get();
    }


}