playlet/app/Console/Commands/ActiveFansSt...

<?php

namespace App\Console\Commands;

use App\Log;
use App\Models\ActiveFansData;
use App\Models\DjOrder;
use App\Models\OfficialAccount;
use App\Models\TencentAdDailyReport;
use Illuminate\Console\Command;

class ActiveFansStat extends Command
{
    protected $signature = 'ActiveFansStat  {type?} {ref_date?}';   // type 1:按年抓取（date示例：2021） 2:按指定日期（date示例：2021-01-01）3:抓取昨日数据
    protected $description = '用户激活数据统计';

    protected $type;
    protected $dateTime;
    protected $activeDate = false;
    protected $limit = 50;
    protected $startId = 0;
    protected $appIdList = [];

    public function handle()
    {
        \DB::connection()->disableQueryLog();

        $this->info(date('m-d H:i:s') . ' 开始整理');
        $this->type = $this->argument('type') ? $this->argument('type') : 2;
        $this->dateTime = $this->argument('ref_date') ? $this->argument('ref_date') : null;
        # 获取需要统计的公众账号列表
        do{
            $this->info('起始ID：'.$this->startId);

            $this->appIdList = [];
            $accountList = OfficialAccount::selectRaw('id, mp_app_id as app_id')->where('enable', 1)->where('id', '>', $this->startId)
                ->limit($this->limit)->orderBy('id')
                ->get();
            if(empty($accountList))
                return false;

            $count = $accountList->count();
            $this->appIdList = $accountList->pluck('app_id');
            $this->startId = $accountList->max('id');
            $this->info('本次获取到的公众账号个数：'.$count);

            $this->getActiveDate();
        } while ($count == $this->limit);

        $this->info(date('m-d H:i:s') . ' 整理结束');
        return true;
    }

    /**
     * 获取粉丝激活时间
     * */
    private function getActiveDate()
    {
        try {
            if($this->type==1) {
                for ($month = 1; $month <= 12; $month++) {
                    $days = getDaysByMonth($this->dateTime, $month);
                    foreach ($days as $day) {
                        if($day > date("Y-m-d"))
                            continue 2;
                        $this->activeDate = $day;
                        $this->stat();
                    }
                }
            } elseif ($this->type==2) {
                if($this->activeDate === false) {
                    if(is_null($this->dateTime)) {
                        $this->activeDate = date('Y-m-d');
                    } else {
                        $this->activeDate = $this->dateTime;
                    }
                }
                $this->stat();
            } elseif($this->type==3) {
                $this->activeDate = date('Y-m-d', strtotime('-1 days'));
                $this->stat();
            }
        } catch(\Exception $e) {
            Log::logError('统计日期处理发生异常', [
                'msg' => $e->getMessage(),
                'line' => $e->getLine()
            ], 'ActiveFansStat');
        }

        return true;
    }

    /**
     * 废弃
     * */
    private function stat()
    {
        $days = ActiveFansData::ACTIVATE_FANS_RECORD_DAYS;
        $this->info('统计日期：'.$this->activeDate);
        foreach ($this->appIdList as $appId) {
            # 获取该账号在近{$days}天的投放金额
            $paidData = TencentAdDailyReport::select(['ref_date', 'paid', 'app_id'])->where('app_id', $appId)
                ->where('ref_date', '>=', date("Y-m-d", strtotime('-'.$days. ' days', strtotime($this->activeDate))))
                ->where('ref_date', '<=', $this->activeDate)
                ->where('enable', 1)
                ->get();

            for($i=0; $i<$days; $i++) {
                $expenseDate = date("Y-m-d", strtotime('-'.$i. ' days', strtotime($this->activeDate)));
                $dayPaidInfo = $paidData->where('ref_date', $expenseDate)->first();
                $dayPaid = isset($dayPaidInfo->paid) ? $dayPaidInfo->paid : 0;
//                $this->info('投放日期：'.$expenseDate);
                $chargeData = DjOrder::getActiveFansData($appId, $this->activeDate, $expenseDate);

                $activeFans = $chargeData->where('paid_at', '>=', strtotime($this->activeDate . ' 00:00:00') * 1000)
                    ->where('paid_at', '<=', strtotime($this->activeDate . ' 23:59:59') * 1000)
                    ->count();

                # 获取统计日期对应的新用户充值金额  充值次数
                $payInfo = DjOrder::where('bind_app_id', $appId)
                    ->where('order_pay_time', '>=', strtotime($this->activeDate.' 00:00:00') * 1000)
                    ->where('order_pay_time', '<=', strtotime($this->activeDate.' 23:59:59') * 1000)
                    ->where('mp_user_register_time', '>=', strtotime($expenseDate.' 00:00:00') * 1000)
                    ->where('mp_user_register_time', '<=', strtotime($expenseDate.' 23:59:59') * 1000)
                    ->where('pay_status', 1)
                    ->where('is_ad_user', 1)
                    ->selectRaw('sum(pay_money) as pay_money, count(1) as pay_count')
                    ->first();

                $payMoney = $payInfo->pay_money ?? 0;
                $payCount = $payInfo->pay_count ?? 0;

                $payMoneyTotal = DjOrder::where('bind_app_id', $appId)
                    ->where('order_pay_time', '>=', strtotime($expenseDate.' 00:00:00') * 1000)
                    ->where('order_pay_time', '<=', strtotime($this->activeDate.' 23:59:59') * 1000)
                    ->where('mp_user_register_time', '>=', strtotime($expenseDate.' 00:00:00') * 1000)
                    ->where('mp_user_register_time', '<=', strtotime($expenseDate.' 23:59:59') * 1000)
                    ->where('pay_status', 1)
                    ->where('is_ad_user', 1)
                    ->sum('pay_money');

                $insertData = [
                    'paid' => $dayPaid,
                    'active_fans' => $activeFans,
                    'pay_money' => intval($payMoney / 100),
                    'pay_money_total' => intval($payMoneyTotal / 100),
                    'pay_count' => $payCount
                ];

                ActiveFansData::updateOrCreate([
                    'app_id' => $appId, 'expense_date' => $expenseDate, 'ref_date' => $this->activeDate
                ], $insertData);
            }
        }
    }
}