novelPromote/app/Console/Commands/PlayletDataS...

<?php

namespace App\Console\Commands;

use App\Log;
use App\Models\AccountPromoteConfig;
use App\Models\AdsReport;
use App\Models\PlayletConfig;
use App\Models\PlayletStatisticsData;
use App\Models\VpOrder;
use App\Services\PlayletService;
use Illuminate\Console\Command;

class PlayletDataStatistics extends Command
{
    protected $signature = 'PlayletDataStatistics';

    protected $description = '推手推广短剧离线数据统计';

    public function handle()
    {
        # 查询待处理的账号配置信息
        $accountConf = AccountPromoteConfig::where('enable', 1)->get();
        if(empty($accountConf)) {
            $this->infoMessage('暂无短剧推手配置信息');
            return false;
        }

        # 查询短剧信息
        $playletList = PlayletConfig::where('enable', 1)->get();
        if(empty($playletList)) {
            $this->infoMessage('暂无待处理的短剧信息');
            return false;
        }

        try {
            // 删除昨日离线数据,只保留当天的
            $yesterday = date('Y-m-d', strtotime('-1 day'));
            PlayletStatisticsData::where('created_date', $yesterday)->delete();

            $playletData = [];
            foreach ($accountConf as $conf) {
                $value = [];
                $date = date('Y-m-d');
                $value['start_date'] = $conf->start_date;
                $value['end_date'] = $conf->end_date > $date ? $date : $conf->end_date;

                $i = $value['start_date'];
                while($i<=$value['end_date']) {
                    $key = $conf->playlet_id.'###'.$i;
                    if(isset($playletData[$key])) {
                        $playletData[$key]['app_list'][] = $conf->app_id;
                    } else {
                        $playletData[$key]['playlet_id'] = $conf->playlet_id;
                        $playletData[$key]['subscribed_date'] = $i;
                        $playletData[$key]['app_list'][] = $conf->app_id;
                    }

                    $i = date('Y-m-d', strtotime($i . ' +1 day'));
                }
            }

            if(empty($playletData)) {
                $this->infoMessage('短剧整理数据结果为空');
                return false;
            }

            # 获取推手-短剧对应日期的相关数据
            foreach ($playletData as $v) {
                # 获取本次待处理的短剧信息
                $playletId = isset($v['playlet_id']) ? $v['playlet_id'] : null;
                if(empty($playletId))
                    continue;

                $playletInfo = $playletList->where('id', $playletId)->first();
                if(empty($playletInfo)) {
                    continue;
                }
                $this->info('短剧编号【'.$playletId.'】');
                $this->info('短剧名称【'.$playletInfo->name.'】');
                $v['playlet_name'] = $playletInfo->name;
                $v['start_date'] = $v['end_date'] = $v['subscribed_date'];
                $v['first_day_roi'] = 0;
                $v['day_paid'] = 0;
                $v['charge_total'] = 0;
                $v['cost_cover_rate'] = 0;
                $v['fans_increase'] = 0;
                $v['new_user_cost'] = 0;
                $v['new_user_charge_uv'] = 0;
                $v['new_user_charge_pv'] = 0;
                $v['charge_user_cost'] = 0;
                $v['charge_data'] = '';

                $chargeData = [];

                $this->infoMessage('开始处理短剧:'.$playletInfo->name.'在'.$v['start_date'].'日的数据;');

                $res = $this->paymentTrendByDay($v['app_list'], $v['subscribed_date']);

                if(empty($res)) {
                    $this->infoMessage(' 短剧:'.$playletInfo->name.'在'.$v['start_date'].'日的趋势数据为空');
                }

                if(empty($v['app_list'])) {
                    $this->infoMessage(' 短剧:'.$playletInfo->name.'在'.$v['start_date'].'日的app_list数据为空');
                    # 当天消耗
                    $v['day_paid'] = 0;
                    # 新增粉丝数
                    $v['fans_increase'] = 0;
                } else {
                    # 数据计算处理以及赋予默认值
                    $adsReport = AdsReport::selectRaw('sum(paid) as paid, sum(scan_follow_count) as follow_uv')->whereIn('app_id', $v['app_list'])
                        ->where('ref_date', $v['subscribed_date'])->first();
                    # 当天消耗
                    $v['day_paid'] = isset($adsReport->paid) ? $v['day_paid'] + $adsReport->paid / 100 : $v['day_paid'];
                    # 新增粉丝数
                    $v['fans_increase'] = isset($adsReport->follow_uv) ? $v['fans_increase'] + $adsReport->follow_uv : $v['fans_increase'];
                }

                foreach($res as $item) {
                    # 当日新用户累计充值
                    $v['charge_total'] = isset($item['charge_total']) ? $v['charge_total'] + $item['charge_total'] : $v['charge_total'];
                    # 充值人数
                    $v['new_user_charge_uv'] = isset($item['new_user_charge_uv']) ? $v['new_user_charge_uv'] + $item['new_user_charge_uv'] : $v['new_user_charge_uv'];
                    # 充值次数
                    $v['new_user_charge_pv'] = isset($item['new_user_charge_pv']) ? $v['new_user_charge_pv'] + $item['new_user_charge_pv'] : $v['new_user_charge_pv'];

                    # 开始日志至之后的100天数据
                    for ($i = 0; $i < PlayletService::DAYS; $i++) {
                        if (isset($chargeData[$i])) {
                            //已存在,累加
                            $chargeData[$i]['day_charge'] = isset($item['chargeData'][$i]['day_charge']) ? $chargeData[$i]['day_charge'] + $item['chargeData'][$i]['day_charge'] : 0;
                            $chargeData[$i]['charge_total'] = isset($item['chargeData'][$i]['charge_total']) ? $chargeData[$i]['charge_total'] + $item['chargeData'][$i]['charge_total'] : 0;
                            $chargeData[$i]['day_paid'] = isset($item['chargeData'][$i]['day_paid']) ? $chargeData[$i]['day_paid'] + $item['chargeData'][$i]['day_paid'] : 0;
                        } else {
                            //不存在,赋值
                            $chargeData[$i]['day_charge'] = isset($item['chargeData'][$i]['day_charge']) ? $item['chargeData'][$i]['day_charge'] : 0;
                            $chargeData[$i]['charge_total'] = isset($item['chargeData'][$i]['charge_total']) ? $item['chargeData'][$i]['charge_total'] : 0;
                            $chargeData[$i]['day_paid'] = isset($item['chargeData'][$i]['day_paid']) ? $item['chargeData'][$i]['day_paid'] : 0;
                        }
                    }
                }

                /*上面两项不在循环中累加的原因在于,循环中重点统计有新增粉丝的数据,循环中累加会丢失部分没有新粉数据日期的消耗*/

                # 首日ROI
                $v['first_day_roi'] = isset($chargeData[0]['day_paid']) && isset($chargeData[0]['day_charge']) && $chargeData[0]['day_paid'] != 0 ? ($chargeData[0]['day_charge'] / $chargeData[0]['day_paid']) : 0;
                # 回本率
                $v['cost_cover_rate'] = $v['day_paid'] != 0 ? ($v['charge_total'] / $v['day_paid']) : 0;
                # 新增粉丝成本
                $v['new_user_cost'] = $v['fans_increase'] != 0 ? ($v['day_paid'] / $v['fans_increase']) : 0;
                # 充值用户成本
                $v['charge_user_cost'] = $v['new_user_charge_uv'] != 0 ? ($v['day_paid'] / $v['new_user_charge_uv']) : 0;

                for ($i = 0; $i < PlayletService::DAYS; $i++) {
                    $chargeData[$i]['day_charge'] = isset($chargeData[$i]['day_charge']) ? $chargeData[$i]['day_charge'] : 0;
                    $chargeData[$i]['charge_total'] = isset($chargeData[$i]['charge_total']) ? $chargeData[$i]['charge_total'] : 0;
                    $chargeData[$i]['day_paid'] = isset($chargeData[$i]['day_paid']) ? $chargeData[$i]['day_paid'] : 0;
                    $chargeData[$i]['day_add'] = (isset($chargeData[$i]['day_paid']) && $chargeData[$i]['day_paid'] != 0) ? ($chargeData[$i]['day_charge'] / $chargeData[$i]['day_paid']) : 0;
                    $chargeData[$i]['day_cover'] = (isset($chargeData[$i]['day_paid']) && $chargeData[$i]['day_paid'] != 0) ? ($chargeData[$i]['charge_total'] / $chargeData[$i]['day_paid']) : 0;
                    $chargeData[$i]['day_times'] = isset($chargeData[$i]['day_paid']) && ($chargeData[$i]['day_paid'] * $v['first_day_roi']) != 0 ? ($chargeData[$i]['charge_total'] / ($chargeData[$i]['day_paid'] * $v['first_day_roi'])) : 0;
                }
//            }

                $v['charge_data'] = json_encode($chargeData);

                $this->insertData($v);
                unset($v);
                unset($chargeData);
                unset($res);
            }
        } catch (\Exception $e) {
            $this->infoMessage(' 异常###file:'.$e->getFile().';line:'.$e->getLine().';message:'.$e->getMessage());
            return false;
        }
    }

    /**
     * 账号在某天对应的付费趋势数据
     * */
    public static function paymentTrendByDay($appId, $date)
    {
        # 获取对应的公众号AppId
        if(is_array($appId)){
            $appIdList = $appId;
        } else {
            $appIdList = [$appId];
        }
        # 获取各日期对应的花费
        $paidList = AdsReport::getPaidData($appIdList, [$date]);

        $result = [];
        foreach($appIdList as $app_id){
            $trend = [];
            $fansData = self::subscribedFansData($app_id, $date, $date);
            $trend['fans_total'] = isset($fansData->fans_total) ? $fansData->fans_total : 0;
            $trend['charge_total'] = isset($fansData->charge_total) ? $fansData->charge_total / 10000 : 0;

            $trend['app_id'] = $app_id;

            # 当日消耗
            $paidInfo = $paidList->where('app_id', $app_id)
                ->where('ref_date', $date)
                ->first();

            $trend['day_paid'] = isset($paidInfo->paid) ? ($paidInfo->paid / 100) : 0;

            # 获取当日新用户充值情况
            $newUserCharge = VpOrder::getNewUserChargeFirstDay($date, $app_id);
            $trend['new_user_charge'] = isset($newUserCharge->new_user_charge) ? $newUserCharge->new_user_charge : 0;
            $trend['new_user_charge_uv'] = isset($newUserCharge->new_user_charge_uv) ? $newUserCharge->new_user_charge_uv : 0;
            $trend['new_user_charge_pv'] = isset($newUserCharge->new_user_charge_pv) ? $newUserCharge->new_user_charge_pv : 0;
            $trend['first_day_roi'] = $trend['day_paid'] != 0 ? (($trend['new_user_charge'] / 100) / $trend['day_paid']) : 0;

            // 充增回倍
            $trend['chargeData'] = PlayletService::chargeAfterSubscribed(
                $date, PlayletService::DAYS, $app_id, $trend['first_day_roi'], $trend['day_paid']
            );

            $result[] = $trend;
        }

        return $result;
    }

    private function insertData($playlet)
    {
        $insertData['start_date'] = $playlet['start_date'];
        $insertData['end_date'] = $playlet['end_date'];
        $insertData['playlet_name'] = $playlet['playlet_name'];
        $insertData['playlet_id'] = $playlet['playlet_id'];
        $insertData['first_day_roi'] = $playlet['first_day_roi'];
        $insertData['day_paid'] = $playlet['day_paid'];
        $insertData['charge_total'] = $playlet['charge_total'];
        $insertData['cost_cover_rate'] = $playlet['cost_cover_rate'];
        $insertData['fans_increase'] = $playlet['fans_increase'];
        $insertData['new_user_cost'] = $playlet['new_user_cost'];
        $insertData['new_user_charge_uv'] = $playlet['new_user_charge_uv'];
        $insertData['new_user_charge_pv'] = $playlet['new_user_charge_pv'];
        $insertData['charge_user_cost'] = $playlet['charge_user_cost'];
        $insertData['charge_data'] = $playlet['charge_data'];
        $insertData['created_date'] = date('Y-m-d');

        $row = PlayletStatisticsData::where('playlet_id', $insertData['playlet_id'])->where('created_date', $insertData['created_date'])
            ->where('start_date', $insertData['start_date'])->first();
        if($row) {
            # 更新
            $res = PlayletStatisticsData::where('playlet_id', $insertData['playlet_id'])->where('created_date', $insertData['created_date'])
                ->where('start_date', $insertData['start_date'])->update($insertData);
            if($res) {
                # 更新成功
                $this->infoMessage(' 处理短剧:'.$insertData['playlet_id'].'在'.$insertData['start_date'].'日的数据完成；处理结果：更新成功');
            } else {
                # 未有修改
                $this->infoMessage(' 处理短剧:'.$insertData['playlet_id'].'在'.$insertData['start_date'].'日的数据完成；处理结果：更新失败');
            }
        } else {
            # 插入
            $res = PlayletStatisticsData::insert($insertData);
            if($res) {
                # 新增成功
                $this->infoMessage(' 处理短剧:'.$insertData['playlet_id'].'在'.$insertData['start_date'].'日的数据完成；处理结果：更新成功');
            } else {
                # 新增失败
                $this->infoMessage(' 处理短剧:'.$insertData['playlet_id'].'在'.$insertData['start_date'].'日的数据完成；处理结果：更新失败');
            }
        }
    }

    /*
     * 获取账号指定日期新关注粉丝数据
     * */
    public static function subscribedFansData($appId, $startDate, $endDate)
    {
        # 获取公众号数据
        $data = VpOrder::selectRaw("DATE_FORMAT(mp_user_register_time, '%Y-%m-%d') as subscribed_date, count(openid) as fans_total, app_id, sum(pay_money) as charge_total")->where('enable', 1)
            ->where('app_id', $appId)
            ->where('mp_user_register_time', '>=', $startDate . ' 00:00:00')
            ->where('mp_user_register_time', '<=', $endDate . ' 23:59:59')
            ->where('pay_status', 1)
            ->first();

        return $data;
    }

    private function infoMessage($message)
    {
        $this->info(date('Y-m-d H:i:s') . ' ' .$message .' 内存占用:'.round(memory_get_usage()/1024/1024, 2).'MB'. "\r\n");
    }
}