duanju_manage/app/Console/Commands/Statistic/PromotionDayCharge.php

<?php

namespace App\Console\Commands\Statistic;

use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;

class PromotionDayCharge extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'Statistic:PromotionDayCharge {--date= : 统计日期}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '推广充值日统计';

    /**
     * Execute the console command.
     */
    public function handle(): void
    {
        $date = $this->option('date') ?? date('Y-m-d', strtotime('yesterday'));
        $optimizerDayData = [];
        DB::table('orders')
            ->whereBetween('created_at', [
                $date, $date. ' 23:59:59'
            ])->where('promotion_id', '<>', 0)
            ->distinct()
            ->select('promotion_id')
            ->orderBy('promotion_id')
            ->chunk(100, function ($items) use ($date, &$optimizerDayData){
                $promotionData = [];
                $now = date('Y-m-d H:i:s');
                $promotions = DB::table('promotions')
                    ->leftJoin('users', 'users.id', '=' , 'promotions.uid')
                    ->whereIn('promotions.id', $items->pluck('promotion_id'))
                    ->select('promotions.id', 'promotions.uid', 'promotions.miniprogram_id', 'users.pid as puid')
                    ->get()->keyBy('id');
                foreach ($items as $item) {
                    $promotion = $promotions->get($item->promotion_id);
                    if(!$promotion) {
                        myLog('PromotionDayCharge')->error('订单中有的推广id,但是推广信息表中没有', ['promotion_id' => $item->promotion_id]);
                        continue;
                    }
                    $promotionDayCharge = $this->promotionDayCharge($item->promotion_id, $date);
                    $newUserCharge = $this->newUserCharge($item->promotion_id, $date);
                    $chargeInfo = $this->getPromotionData($promotionDayCharge, $newUserCharge);
                    $chargeInfo['new_user_num'] = $this->getNewUserNum($item->promotion_id, $date);
                    $promotionData[] = array_merge( $chargeInfo, [
                        'promotion_id' => $item->promotion_id, 'day_at' => $date,
                        'created_at' => $now, 'updated_at' => $now,
                        'user_id' => $promotion->uid,'puser_id' => $promotion->puid,
                        'miniprogram_id' => $promotion->miniprogram_id,
                    ]);

                    $key = $promotion->uid . '-'. $promotion->miniprogram_id;
                    foreach ($chargeInfo as $k => $v) {
                        $optimizerDayData[$key][$k] = $optimizerDayData[$key][$k] ?? 0 + $v;
                        $optimizerDayData[$key]['user_id'] = $promotion->uid;
                        $optimizerDayData[$key]['puser_id'] = $promotion->puid;
                        $optimizerDayData[$key]['miniprogram_id'] = $promotion->miniprogram_id;
                        $optimizerDayData[$key]['day_at'] = $date;
                        $optimizerDayData[$key]['created_at'] = $now;
                        $optimizerDayData[$key]['updated_at'] = $now;
                    }
                }
                DB::table('tj_promotion_day_charge')
                    ->insert($promotionData);
            });

        foreach (collect($optimizerDayData)->chunk(100) as $items) {
            DB::table('tj_optimizer_day_charge')
                ->insert($items->values()->toArray());
        }


        DB::table('promotions')
            ->leftJoin('users', 'users.id', '=' , 'promotions.uid')
            ->select('promotions.id', 'promotions.uid', 'promotions.miniprogram_id', 'users.pid as puid')
            ->orderBy('promotions.id')
            ->chunk(100, function ($items) use ($date){
                $promotionIds = $items->pluck('id');
                $now = date('Y-m-d H:i:s');
                $alreadyExistsPromotionIds = DB::table('tj_promotion_day_charge')
                    ->where('day_at', $date)
                    ->whereIn('promotion_id', $promotionIds)
                    ->select('promotion_id')
                    ->get()->pluck('promotion_id');
                foreach ($items as $item) {
                    if($alreadyExistsPromotionIds->contains($item->id)) {
                        continue;
                    }
                    DB::table('tj_promotion_day_charge')
                        ->insert([
                            'promotion_id' => $item->id, 'day_at' => $date,
                            'created_at' => $now, 'updated_at' => $now,
                            'user_id' => $item->uid, 'puser_id' => $item->puid,
                            'miniprogram_id' => $item->miniprogram_id
                        ]);
                }
            });

        DB::table('users')
            ->join('user_has_roles', 'users.id', 'user_has_roles.user_id')
            ->where(['user_has_roles.role_id' => 2, 'users.deleted_at' => 0])
            ->distinct()
            ->select('users.id', 'users.pid')
            ->orderBy('users.id')
            ->chunk(50, function ($items) use($date) {
                $now = date('Y-m-d H:i:s');
                foreach ($items as $item) {
                    $miniprogramIds = DB::table('user_has_miniprograms')
                        ->where('uid', $item->id)
                        ->where('is_enabled', 1)
                        ->select('miniprogram_id')
                        ->get()->pluck('miniprogram_id');
                    foreach ($miniprogramIds as $miniprogramId) {
                        if(DB::table('tj_optimizer_day_charge')
                            ->where([
                                'day_at' => $date,
                                'user_id' => $item->id, 'miniprogram_id' => $miniprogramId,
                            ])->exists()) {
                            continue;
                        }
                        DB::table('tj_optimizer_day_charge')
                            ->insert([
                                'day_at' => $date, 'user_id' => $item->id, 'miniprogram_id' => $miniprogramId,
                                'puser_id' => $item->pid, 'created_at' => $now, 'updated_at' => $now
                            ]);
                    }
                }
            });
    }

    /**
     * 某个推广链接在某天的新用户数量
     * @param $promotionId 推广链接id
     * @param $date  日期
     * @return mixed
     */
    private function getNewUserNum($promotionId, $date) {
        return intval(Redis::hget(sprintf('promotion:newUserCount:%s', $date), $promotionId));
    }

    public function getPromotionData($promotionDayCharge, $newUserCharge) {
        return [
            'pay_money' => $promotionDayCharge['pay_money'] ?? 0,
            'pay_count' => $promotionDayCharge['pay_count'] ?? 0,
            'common_pay_money' => $promotionDayCharge['common_pay_money'] ?? 0,
            'common_pay_uv' => $promotionDayCharge['common_pay_uv'] ?? 0,
            'common_pay_count' => $promotionDayCharge['common_pay_count'] ?? 0,
            'common_unpay_count' => $promotionDayCharge['common_unpay_count'] ?? 0,
            'vip_pay_money' => $promotionDayCharge['vip_pay_money'] ?? 0,
            'vip_pay_uv' => $promotionDayCharge['vip_pay_uv'] ?? 0,
            'vip_pay_count' => $promotionDayCharge['vip_pay_count'] ?? 0,
            'vip_unpay_count' => $promotionDayCharge['vip_unpay_count'] ?? 0,
            'new_user_pay_money' => $newUserCharge['new_user_pay_money'] ?? 0,
            'new_user_common_pay_money' => $newUserCharge['new_user_common_pay_money'] ?? 0,
            'new_user_vip_pay_money' => $newUserCharge['new_user_vip_pay_money'] ?? 0,
            'new_user_pay_uv' => $newUserCharge['new_user_pay_uv'] ?? 0,
            'new_user_vip_pay_uv' => $newUserCharge['new_user_vip_pay_uv'] ?? 0,
            'new_user_common_pay_uv' =>$newUserCharge['new_user_common_pay_uv'] ?? 0,
        ];
    }

    public function newUserCharge($promotionId, $date) {
        $info = DB::table('orders')
            ->where('promotion_id', $promotionId)
            ->whereBetween('ranse_created_at', [$date, $date. ' 23:59:59'])
            ->whereBetween('created_at', [$date, $date. ' 23:59:59'])
            ->select(
                // 总支付金额
                DB::raw("sum(if(status <> 'unpaid', price, 0)) as pay_money"),
                // 普通支付金额
                DB::raw("sum(if(status <> 'unpaid' and order_type in ('coin', 'first_coin'), price, 0)) as common_pay_money"),
                // 累计充值人数
                DB::raw("count(distinct if(status <> 'unpaid', concat(uid, ranse_created_at), null)) as pay_uv"),
                // 普通支付人数
                DB::raw("count(distinct if(order_type in ('coin', 'first_coin') and status <> 'unpaid', concat(uid, ranse_created_at), null)) as common_pay_uv"),
                // vip支付人数
                DB::raw("count(distinct if(order_type not in ('coin', 'first_coin') and status <> 'unpaid', concat(uid, ranse_created_at), null)) as vip_pay_uv"),
            )->first();
        if($info) {
            return [
                // 新增用户支付总额
                'new_user_pay_money' => $info->pay_money,
                // 新增用户普通支付总额
                'new_user_common_pay_money' => $info->common_pay_money,
                // 新增用户会员支付总额
                'new_user_vip_pay_money' => $info->pay_money - $info->common_pay_money,
                // 新增用户在{$date}充值人数
                'new_user_pay_uv' => $info->pay_uv,
                // 新增用户在{$date}vip充值支付人数
                'new_user_vip_pay_uv' => $info->vip_pay_uv,
                // 新增用户在{$date}普通充值支付人数
                'new_user_common_pay_uv' => $info->vip_pay_uv,
            ];
        } else {
            return null;
        }
    }

    public function promotionDayCharge($promotionId, $date) {
        $info = DB::table('orders')
            ->where('promotion_id', $promotionId)
            ->whereBetween('created_at', [$date, $date. ' 23:59:59'])
            ->where('ranse_created_at', '>', '2000-01-01')
            ->select(
                // 未支付金额
                DB::raw("sum(if(status = 'unpaid', price, 0)) as unpay_money"),
                // 未支付笔数
                DB::raw("sum(if(status = 'unpaid', 1, 0)) as unpay_count"),
                // 总金额
                DB::raw("sum(price) as total_money"),
                // 总笔数
                DB::raw("count(id) as total_count"),
                // 普通未支付笔数
                DB::raw("sum(if(status='unpaid' and order_type in ('coin', 'first_coin'), 1, 0)) as common_unpay_count"),
                // 普通总笔数
                DB::raw("sum(if(order_type in ('coin', 'first_coin'), 1,  0)) as common_count"),
                // 普通支付金额
                DB::raw("sum(if(order_type in ('coin', 'first_coin') and status <> 'unpaid', price, 0)) as common_pay_money"),
                // NOTE!!!,uid, ranse_created_at 唯一确定一个用户
                // 累计充值人数
                DB::raw("count(distinct if(status <> 'unpaid', concat(uid, ranse_created_at), null)) as pay_uv"),
                // 普通支付人数
                DB::raw("count(distinct if(order_type in ('coin', 'first_coin') and status <> 'unpaid', concat(uid, ranse_created_at), null)) as common_pay_uv"),
                // vip支付人数
                DB::raw("count(distinct if(order_type not in ('coin', 'first_coin') and status <> 'unpaid', concat(uid, ranse_created_at), null)) as vip_pay_uv"),
                // vip未支付笔数
                DB::raw("sum(if(order_type not in ('coin', 'first_coin') and status = 'unpaid', 1,  0)) as vip_unpay_count"),
            )->first();
        if($info) {
            return [
                // 支付金额
                'pay_money' => bcsub($info->total_money, $info->unpay_money, 2),
                // 支付笔数
                'pay_count' => $info->total_count - $info->unpay_count,
                /**
                 * -----普通充值--------
                 */
                // 支付金额
                'common_pay_money' => $info->common_pay_money,
                // 支付人数
                'common_pay_uv' => $info->common_pay_uv,
                // 支付笔数
                'common_pay_count' => $info->common_count - $info->common_unpay_count,
                // 未支付笔数
                'common_unpay_count' => $info->common_unpay_count,
                /**
                 * ----会员充值------
                 */
                // 支付金额
                'vip_pay_money' => bcsub(bcsub($info->total_money, $info->unpay_money, 2), $info->common_pay_money, 2),
                // 支付人数
                'vip_pay_uv' => $info->vip_pay_uv,
                // 支付笔数
                'vip_pay_count' => $info->total_count - $info->unpay_count - ($info->common_count - $info->common_unpay_count),
                // 未支付笔数
                'vip_unpay_count' => $info->vip_unpay_count,
            ];
        } else {
            return null;
        }
    }
}