novelPromote/app/Console/Commands/NovelDataTot...

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2021/6/3
 * Time: 18:17
 */

namespace App\Console\Commands;

use App\Models\Account;
use App\Models\AdsDailyReport;
use App\Models\AdsReport;
use App\Models\conf\AccountConf;
use App\Models\conf\ADQAccountBinding;
use App\Models\conf\NovelConf;
use App\Models\NovelDataTotal;
use App\Models\Order;
use App\Services\WxAccountService;
use Illuminate\Console\Command;

class NovelDataTotalStatistics extends Command
{
    protected $signature = 'NovelDataTotalStatistics';
    protected $description = '小说数据趋势每日离线统计';

    public function handle($date = null)
    {
        self::infoMessage(' 开始执行;');

        \DB::connection()->disableQueryLog();
        set_time_limit(0);
        ini_set("memory_limit",-1);

        $yesterday = date('Y-m-d', strtotime('-1 day'));

        try {
            //从关系表中聚合查询小说id列表
            $novelAccountList = AccountConf::query()
                ->where('enable', 1)
//                ->where('novel_id', 108)
                ->get()
                ->toArray();
            $novelIdList = array_unique(array_column($novelAccountList, 'novel_id'));

            //查询小说配置表
            $novelList = NovelConf::query()
                ->select('id', 'name')
                ->where('enable', 1)
                ->whereIn('id', $novelIdList)
                ->get()
                ->keyBy('id')
                ->toArray();

            self::infoMessage(' 获取小说数据完成:共' . count($novelList) . '条小说数据');

            // 删除昨日离线数据,只保留当天的
            NovelDataTotal::query()
                ->where('created_date', $yesterday)
                ->delete();

            //遍历小说列表(内部循环处理小说对应的公众号数据)
            /*将所有需要累加的数据据都汇总完成*/
            foreach ($novelIdList as $novelId) {
                # 查询小说基础信息为空,说明小说已经删除,直接跳过不处理
                $novelData = isset($novelList[$novelId]) ? $novelList[$novelId] : null;
                if(is_null($novelData)) {
                    self::infoMessage(' 小说基础数据查询为空#novel_id:'.$novelId);
                    continue;
                }
                self::infoMessage(' 开始处理小说:'.$novelData['name'].'的数据');

                #查询小说对应的公众号配置信息
                $accountConfList = AccountConf::query()
                    ->where('novel_id', $novelId)
                    ->where('enable', 1)
                    ->get();
                if(empty($accountConfList)) {
                    self::infoMessage(' 小说对应公众号配置信息为空');
                    continue;
                }
                $accountConfList = $accountConfList->toArray();

                # 数据初始化
                $novel['start_date'] = null;
                $novel['end_date'] = null;
                $novel['id'] = $novelId;
                $novel['name'] = $novelData['name'];
                $novel['first_day_roi'] = 0;
                $novel['new_user_cost'] = 0;
                $novel['new_user_charge'] = 0;
                $novel['day_paid'] = 0;
                $novel['charge_total'] = 0;
                $novel['cost_cover_rate'] = 0;
                $novel['fans_increase'] = 0;
                $novel['new_user_charge_uv'] = 0;
                $novel['new_user_charge_pv'] = 0;
                $novel['charge_data'] = '';
                $novel['charge_user_cost'] = 0;

                $chargeData = [];
                # 循环小说公众号配置
                foreach ($accountConfList as $conf) {

                    # 给小说的起始截止日期赋予默认值
                    if (is_null($novel['start_date']) || ($conf['start_date'] < $novel['start_date'])) {
                        $novel['start_date'] = $conf['start_date'];
                    }
                    if (is_null($novel['end_date']) || ($conf['end_date'] > $novel['end_date'])) {
                        $novel['end_date'] = $conf['end_date'];
                    }

                    # 直接从表中查询消耗总数(避免出现由于当天无充值信息丢失消耗数据的情况)
                    $adsReport = AdsReport::query()
                        ->selectRaw('sum(paid) as paid, sum(follow_uv) as follow_uv')
                        ->where('app_id', $conf['app_id'])
                        ->where('ref_date', '>=', $conf['start_date'])
                        ->where('ref_date', '<=', $conf['end_date'])
                        ->first();
                    # 当天消耗
                    $novel['day_paid'] = isset($adsReport->paid) ? $novel['day_paid'] + $adsReport->paid / 100 : $novel['day_paid'];
                    # 新增粉丝数
                    $novel['fans_increase'] = isset($adsReport->follow_uv) ? $novel['fans_increase'] + $adsReport->follow_uv : $novel['fans_increase'];

                    $adqAdsReport = self::getAdqPaid($conf['app_id'], $conf['start_date'], $conf['end_date']);

                    $novel['day_paid'] = isset($adqAdsReport['day_paid']) ? $novel['day_paid'] + $adqAdsReport['day_paid'] : $novel['day_paid'];
                    $novel['fans_increase'] = isset($adqAdsReport['fans_increase']) ? $novel['fans_increase'] + $adqAdsReport['fans_increase'] : $novel['fans_increase'];

                    # 从趋势列表中获取数据
                    $data = self::paymentTrend($conf['app_id'], $conf['start_date'], $conf['end_date']);

                    if (empty($data)) {
                        self::infoMessage(' 小说:' . $novel['name'] . '在' . $conf['start_date'] . '日的趋势数据为空');
                        $data = [];
                    } else {
                        $data = json_decode(json_encode($data), 1);
                    }

                    # 这部分主要是用来循环累加数据
                    foreach ($data as $item) {
                        # 当日新用户累计充值
                        $novel['charge_total'] = isset($item['charge_total']) ? $novel['charge_total'] + $item['charge_total'] : $novel['charge_total'];
                        # 充值人数
                        $novel['new_user_charge_uv'] = isset($item['new_user_charge_uv']) ? $novel['new_user_charge_uv'] + $item['new_user_charge_uv'] : $novel['new_user_charge_uv'];
                        # 充值次数
                        $novel['new_user_charge_pv'] = isset($item['new_user_charge_pv']) ? $novel['new_user_charge_pv'] + $item['new_user_charge_pv'] : $novel['new_user_charge_pv'];

                        # 开始日志至之后的100天数据
                        for ($i = 0; $i < WxAccountService::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'] : $chargeData[$i]['day_charge'];
                                $chargeData[$i]['charge_total'] = isset($item['chargeData'][$i]['charge_total']) ? $chargeData[$i]['charge_total'] + $item['chargeData'][$i]['charge_total'] : $chargeData[$i]['charge_total'];
                                $chargeData[$i]['day_paid'] = isset($item['chargeData'][$i]['day_paid']) ? $chargeData[$i]['day_paid'] + $item['chargeData'][$i]['day_paid'] : $chargeData[$i]['day_paid'];
                            } 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
                $novel['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;
                # 回本率
                $novel['cost_cover_rate'] = $novel['day_paid'] != 0 ? ($novel['charge_total'] / $novel['day_paid']) : 0;
                # 新增粉丝成本
                $novel['new_user_cost'] = $novel['fans_increase'] != 0 ? ($novel['day_paid'] / $novel['fans_increase']) : 0;
                # 充值用户成本
                $novel['charge_user_cost'] = $novel['new_user_charge_uv'] != 0 ? ($novel['day_paid'] / $novel['new_user_charge_uv']) : 0;

                for ($i = 0; $i < WxAccountService::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'] * $novel['first_day_roi']) != 0 ? ($chargeData[$i]['charge_total'] / ($chargeData[$i]['day_paid'] * $novel['first_day_roi'])) : 0;
                }

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

                self::insertData($novel);
                unset($novel);
                unset($chargeData);
                unset($data);
            }

            # 汇总数据统计

            $this->infoMessage(' 数据整理完成');
        } catch (\Exception $exception) {
            $this->infoMessage(' 异常###file:'.$exception->getFile().';line:'.$exception->getLine().';message:'.$exception->getMessage());
        }
    }

    /*
     *  付费趋势
     * */
    public static function paymentTrend($appId, $startDate, $endDate)
    {
        # 获取对应的公众号AppId
        if(is_array($appId)){
            $appIdList = $appId;
        } else {
            $appIdList = [$appId];
        }

        $dateList = [];
        $date = $startDate;
        while($date <= $endDate) {
            $dateList[] = $date;
            $date = date('Y-m-d', strtotime($date . ' +1 day'));
        }

        if(empty($dateList)) return [[], 0];

        # 查询账号信息
        $accountData = Account::query()->selectRaw("channel_id, platform_id, nickname, app_id")
            ->whereIn('app_id', $appIdList)->get();

        # 获取各日期对应的花费
        $paidList = AdsReport::getPaidData($appIdList, $dateList);

        $adqAccountList = ADQAccountBinding::getAdqAccountList($appId, $startDate, $endDate);
        $adqAccounts = $adqDateList = [];
        foreach($adqAccountList as $item) {
            $adqAccounts = array_merge($adqAccounts, $item);
        }
        $adqAccounts = array_unique($adqAccounts);
        $adqDateList = array_keys($adqAccountList);

        if(!empty($adqAccounts)) {
            $adqPaidList = AdsReport::getAdqPaidData($adqAccounts, $adqDateList);
        } else {
            $adqPaidList = null;
        }

        $channelIdList = $accountData->where('app_id', $appId)->pluck('channel_id')->toArray();
        $fansDataList = self::subscribedFansData($channelIdList, $startDate, $endDate);

        $result = [];
        foreach($dateList as $value) {
            $trend = [];

            $fansData = $fansDataList->where('subscribed_date', $value)->first();
            $fansData = json_decode(json_encode($fansData), 1);
            $trend['fans_total'] = !empty($fansData) ? $fansData['fans_total'] : 0;
            $trend['charge_total'] = !empty($fansData) ? $fansData['charge_total'] / 100 : 0;
            $trend['app_id'] = $appId;

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

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

            $dailyAdqAccount = isset($adqAccountList[$value]) ? $adqAccountList[$value] : [];
            $dailyAdqPaid = !empty($dailyAdqAccount) && $adqPaidList->isNotEmpty() ?
                $adqPaidList->whereIn('account_id', $dailyAdqAccount)->where('ref_date', $value)->sum('paid') : 0;
            $trend['day_paid'] += $dailyAdqPaid/100;

            # 获取当日新用户充值情况
            $newUserCharge = Order::getNewUserChargeSecond($value, $channelIdList);
            $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'] = WxAccountService::chargeAfterSubscribed(
                $channelIdList, $value, WxAccountService::DAYS, $appId, $trend['first_day_roi'], $trend['day_paid']
            );

            $result[] = $trend;
        }

        return $result;
    }

    public static function insertData($novel)
    {
        $insertData['start_date'] = $novel['start_date'];
        $insertData['end_date'] = $novel['end_date'];
        $insertData['novel_name'] = $novel['name'];
        $insertData['novel_id'] = $novel['id'];
        $insertData['first_day_roi'] = $novel['first_day_roi'];
        $insertData['day_paid'] = $novel['day_paid'];
        $insertData['charge_total'] = $novel['charge_total'];
        $insertData['cost_cover_rate'] = $novel['cost_cover_rate'];
        $insertData['fans_increase'] = $novel['fans_increase'];
        $insertData['new_user_cost'] = $novel['new_user_cost'];
        $insertData['new_user_charge_uv'] = $novel['new_user_charge_uv'];
        $insertData['new_user_charge_pv'] = $novel['new_user_charge_pv'];
        $insertData['charge_user_cost'] = $novel['charge_user_cost'];
        $insertData['charge_data'] = $novel['charge_data'];
        $insertData['created_date'] = date('Y-m-d');

        $row = NovelDataTotal::query()
            ->where('novel_id', $insertData['novel_id'])
            ->where('created_date', $insertData['created_date'])
            ->first();
        if($row) {
            # 更新
            $res = NovelDataTotal::query()
                ->where('novel_id', $insertData['novel_id'])
                ->where('created_date', $insertData['created_date'])
                ->update($insertData);
            if($res) {
                # 更新成功
                self::infoMessage(' 处理小说:'.$insertData['novel_name'].'的数据；处理结果：更新成功');
            } else {
                # 未有修改
                self::infoMessage(' 处理小说:'.$insertData['novel_name'].'的数据；处理结果：更新失败');
            }
        } else {
            # 插入
            $res = NovelDataTotal::query()->insert($insertData);
            if($res) {
                # 新增成功
                self::infoMessage(' 处理小说:'.$insertData['novel_name'].'的数据；处理结果：新增成功');
            } else {
                # 新增失败
                self::infoMessage(' 处理小说:'.$insertData['novel_name'].'的数据；处理结果：新增失败');
            }
        }
    }

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

    public static function getAdqPaid($appId, $startDate, $endDate)
    {
        # 查询所有配置
        $confList = ADQAccountBinding::query()
            ->where('enable', 1)
            ->where('status', 1)
            ->where('app_id', $appId)
            ->where('start_date', '<=', $endDate)
            ->where('end_date', '>=', $startDate)
            ->get();

        $data = ['day_paid' => 0, 'fan_increase' => 0];
        if($confList->isNotEmpty()) {
            foreach ($confList->toArray() as $conf) {
                $start  = $conf['start_date'] >= $startDate ? $conf['start_date'] : $startDate;
                $end    = $conf['end_date'] <= $endDate ? $conf['end_date'] : $endDate;

                $paidInfo = AdsDailyReport::query()
                    ->where('account_id', $conf['account_id'])
                    ->where('ref_date', '>=', $start)
                    ->where('ref_date', '<=', $end)
                    ->selectRaw('sum(paid) as paid, sum(follow_uv) as follow_uv')
                    ->first();

                $data['day_paid']       += $paidInfo->paid/100;
                $data['fan_increase']   += $paidInfo->follow_uv;
            }
        }

        return $data;
    }
    /*
     *  获取时间段内新关注粉丝数据
     * */
    public static function subscribedFansData($channelIds, $startDate, $endDate)
    {
        # 获取公众号数据
        $list = Order::query()
            ->selectRaw("DATE_FORMAT(user_created_at, '%Y-%m-%d') as subscribed_date, 
            count(member_openid) as fans_total, channel_id, sum(price) as charge_total")
            ->where('enable', 1)
            ->where(function($query) use($channelIds) {
                if(!empty($channelIds))
                    $query->whereIn('channel_id', $channelIds);
            })
            ->where('user_created_at', '>=', $startDate . ' 00:00:00')
            ->where('user_created_at', '<=', $endDate . ' 23:59:59')
            ->where('status', 1)
            ->groupBy(['subscribed_date'])
            ->get();

        return $list;
    }

}