<?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();
}
}