crm/app/command/Fish.php

<?php
namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use app\model\Company;
use think\facade\Log;
use app\model\FishCampaign;
use app\model\FishCampaignList;
use clue\Fish as clueFish;
use clue\Tencent;

class Fish extends Command
{
    protected function configure()
    {
        $this->setName('fish')
            ->setDescription('Automatically update flying fish clues');
    }

    protected function execute(Input $input, Output $output)
    {
        Log::close(NULL);
        $list = Company::where("channel_setting <> 'null'")->field('root_id,channel_setting,channel_setting_wh')->select()->toArray();
        if (empty($list)) return $output->writeln("没有要执行的任务");
        $fish = new clueFish(config('app.fish_clue_appid'), config('app.fish_clue_secret'));
        $tencent = new Tencent(config('app.tx_clue_appid'), config('app.tx_clue_secret'));
        $fish_wh = new clueFish(config('app.fish_clue_appid_wh'), config('app.fish_clue_secret_wh'));
        foreach ($list as $val) {
            if(isset($val['channel_setting']) && $val['channel_setting']){
                $root_id = $val['root_id'];
                $tmp_data = json_decode($val['channel_setting'], true);
                $tmp_data = $this->refreshToken($fish, $tmp_data, $root_id);
                $start_date = date('Y-m-d', strtotime('-1 day'));
                //飞鱼数据
                $advertiser_id = explode(',', $tmp_data['fish_adv']);
                foreach ($advertiser_id as $va) {
                    $new_data = $fish->getCampaignListV2($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => 1]);
                    if ($new_data['code'] == 0) {
                        $page = $new_data['data']['page_info']['total_page'];
                        $this->update_fish_campaign_data_new($new_data['data']['rows'], $fish, $tmp_data, $root_id, $start_date, $va, 1);
                        if ($page > 1) {
                            for ($i = 1; $i < $page; $i++) {
                                $page_data = $fish->getCampaignListV2($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => $i + 1]);
                                if ($page_data['code'] == 0) {
                                    $this->update_fish_campaign_data_new($page_data['data']['rows'], $fish, $tmp_data, $root_id, $start_date, $va, 1);
                                }
                            }
                        }
                    }
                    $data = $fish->getCampaignList($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => 1]);
                    if ($data['code'] == 0) {
                        //如果广告组数据存在多页
                        $page = $data['data']['page_info']['total_page'];
                        $this->update_fish_campaign_data($data['data']['list'], $fish, $tmp_data, $root_id, $start_date, $va, 1);
                        if ($page > 1) {
                            for ($i = 1; $i < $page; $i++) {
                                $page_data = $fish->getCampaignList($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => $i + 1]);
                                if ($page_data['code'] == 0) {
                                    $this->update_fish_campaign_data($page_data['data']['list'], $fish, $tmp_data, $root_id, $start_date, $va, 1);
                                }
                            }
                        }
                    }
                }
                //腾讯数据
                $advertiser_id = explode(',', $tmp_data['tx_adv']);
                foreach ($advertiser_id as $va) {
                    $data = $tencent->getCampaignList($tmp_data['tx_access_token'], $va, $start_date);
                    if ($data['code'] == 0) {
                        $this->update_tx_campaign_data($data['data']['list'], $tencent, $tmp_data, $root_id, $start_date, $va);
                    }
                }
            }
            if(isset($val['channel_setting_wh']) && $val['channel_setting_wh']){
                $root_id = $val['root_id'];
                $tmp_data = json_decode($val['channel_setting_wh'], true);
                $tmp_data = $this->refreshTokenV2($fish_wh, $tmp_data, $root_id);
                $start_date = date('Y-m-d', strtotime('-1 day'));
                $advertiser_id = explode(',', $tmp_data['fish_adv']);
                foreach ($advertiser_id as $va) {
                    $new_data = $fish->getCampaignListV2($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => 1]);
                    if ($new_data['code'] == 0) {
                        $page = $new_data['data']['page_info']['total_page'];
                        $this->update_fish_campaign_data_new($new_data['data']['rows'], $fish, $tmp_data, $root_id, $start_date, $va, 4);
                        if ($page > 1) {
                            for ($i = 1; $i < $page; $i++) {
                                $page_data = $fish->getCampaignListV2($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => $i + 1]);
                                if ($page_data['code'] == 0) {
                                    $this->update_fish_campaign_data_new($page_data['data']['rows'], $fish, $tmp_data, $root_id, $start_date, $va, 4);
                                }
                            }
                        }
                    }
                    $data = $fish_wh->getCampaignList($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => 1]);
                    if ($data['code'] == 0) {
                        //如果广告组数据存在多页
                        $page = $data['data']['page_info']['total_page'];
                        $this->update_fish_campaign_data($data['data']['list'], $fish_wh, $tmp_data, $root_id, $start_date, $va, 4);
                        if ($page > 1) {
                            for ($i = 1; $i < $page; $i++) {
                                $page_data = $fish_wh->getCampaignList($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $start_date, 'end_date' => $start_date, 'page' => $i + 1]);
                                if ($page_data['code'] == 0) {
                                    $this->update_fish_campaign_data($page_data['data']['list'], $fish_wh, $tmp_data, $root_id, $start_date, $va, 4);
                                }
                            }
                        }
                    }
                }
            }
        }
        $output->writeln("同步完成");
    }

    /**
     * 更新腾讯广告组
     */
    public function update_tx_campaign_data($list, $tencent, $tmp_data, $root_id, $start_date, $advertiser_id)
    {
        $tmp = [];
        FishCampaign::where(['root_id' => $root_id, 'data_time' => $start_date, 'advertiser_id' => $advertiser_id, 'type' => 2])->delete();
        foreach ($list as $v) {
            $c_data['type'] = 2;
            $c_data['root_id'] = $root_id;
            $c_data['data_time'] = $start_date;
            $c_data['advertiser_id'] = $v['account_id'];
            $c_data['campaign_id'] = $v['adgroup_id'];
            $c_data['campaign_name'] = $v['adgroup_name'];
            $c_data['stat_datetime'] = '';
            $c_data['inventory'] = '';
            $c_data['cost'] = $v['cost'] > 0 ? round($v['cost'] / 100, 2) : 0;
            $c_data['show'] = $v['view_count'];
            $c_data['avg_show_cost'] = $v['thousand_display_price'];
            $c_data['click'] = $v['valid_click_count'];
            $c_data['avg_click_cost'] = $v['cpc'] > 0 ? round($v['cpc'] / 100, 2) : 0;
            $c_data['ctr'] = $v['ctr'] * 100;
            $c_data['convert'] = $v['conversions_count'];
            $c_data['convert_cost'] = $v['effective_cost'] > 0 ? round($v['effective_cost'] / 100, 2) : 0;
            $c_data['convert_rate'] = $v['view_count'] > 0 ? round($v['conversions_count'] / $v['view_count']) : 0;
            $c_data['deep_convert'] = 0;
            $c_data['deep_convert_cost'] = 0;
            $c_data['deep_convert_rate'] = 0;
            $tmp[] = $c_data;
            FishCampaignList::where(['root_id' => $root_id, 'data_time' => $start_date, 'advertiser_id' => $advertiser_id, 'campaign_id' => $v['adgroup_id'], 'type' => 2])->delete();
            $data = $tencent->getCampaignAdList($tmp_data['tx_access_token'], $advertiser_id, $start_date, $v['adgroup_id']);
            if ($data['code'] == 0) {
                $this->update_tx_adv_data($data['data']['list'], $root_id, $start_date, $advertiser_id);
            }
        }
        FishCampaign::insertAll($tmp);
    }


    /**
     * 更新腾讯广告组下的计划
     */
    public function update_tx_adv_data($data, $root_id, $data_time, $advertiser_id)
    {
        $tmp_adv_list = [];
        foreach ($data as $val) {
            $c_data['advertiser_id'] = $advertiser_id;
            $c_data['campaign_id'] = $val['adgroup_id'];
            $c_data['campaign_name'] = $val['ad_name'] ?? '';
            $c_data['ad_id'] = $val['ad_id'];
            $c_data['ad_name'] = $val['ad_name'] ?? '';
            $c_data['stat_datetime'] = $val['date'];
            $c_data['cost'] = $val['cost'] > 0 ? round($val['cost'] / 100, 2) : 0;
            $c_data['show'] = $val['view_count'];
            $c_data['click'] = $val['valid_click_count'];
            $c_data['avg_click_cost'] = $val['cpc'] > 0 ? round($val['cpc'] / 100, 2) : 0;
            $c_data['ctr'] = $val['ctr'] * 100;
            $c_data['convert'] = $val['conversions_count'];
            $c_data['convert_cost'] = $val['effective_cost'] > 0 ? round($val['effective_cost'] / 100, 2) : 0;
            $c_data['convert_rate'] = $val['view_count'] > 0 ? round($val['conversions_count'] / $val['view_count']) : 0;
            $c_data['root_id'] = $root_id;
            $c_data['data_time'] = $data_time;
            $c_data['type'] = 2;
            $tmp_adv_list[] = $c_data;
        }
        FishCampaignList::insertAll($tmp_adv_list);
    }

    /**
     * 更新数据-新版
     */
    public function update_fish_campaign_data_new($list, $fish, $tmp_data, $root_id, $data_time, $advertiser_id, $type)
    {
        $data = [];
        foreach ($list as $val) {
            //先删除昨日数据后插入数据
            FishCampaign::where(['root_id' => $root_id, 'data_time' => $data_time, 'advertiser_id' => $advertiser_id, 'campaign_id' => $val['dimensions']['cdp_project_id'], 'type' => $type])->delete();
            FishCampaignList::where(['root_id' => $root_id, 'data_time' => $data_time, 'advertiser_id' => $advertiser_id, 'campaign_id' => $val['dimensions']['cdp_project_id'], 'type' => $type])->delete();
            $tmp_val['advertiser_id'] = $advertiser_id;
            $tmp_val['data_time'] = $data_time;
            $tmp_val['root_id'] = $root_id;
            $tmp_val['type'] = $type;
            $tmp_val['campaign_id'] = $val['dimensions']['cdp_project_id'];
            $tmp_val['campaign_name'] = $val['dimensions']['cdp_project_name'];
            $tmp_val['cost'] = $val['metrics']['stat_cost'];
            $tmp_val['show'] = $val['metrics']['show_cnt'];
            $tmp_val['avg_show_cost'] = $val['metrics']['cpm_platform'];
            $tmp_val['click'] = $val['metrics']['click_cnt'];
            $tmp_val['avg_click_cost'] = $val['metrics']['cpc_platform'];
            $tmp_val['ctr'] = $val['metrics']['ctr'];
            $tmp_val['convert'] = $val['metrics']['convert_cnt'];
            $tmp_val['convert_cost'] = $val['metrics']['conversion_cost'];
            $tmp_val['convert_rate'] = $val['metrics']['conversion_rate'];
            $tmp_val['deep_convert'] = $val['metrics']['deep_convert_cnt'];
            $tmp_val['deep_convert_cost'] = $val['metrics']['deep_convert_cost'];
            $tmp_val['deep_convert_rate'] = $val['metrics']['deep_convert_rate'];
            $data[] = $tmp_val;
            //循环广告组获取广告组下的计划花费
            $adv_data = $fish->getAdListV2($tmp_data['fish_access_token'], ['advertiser_id' => $advertiser_id, 'campaign_ids' => $val['dimensions']['cdp_project_id'], 'start_date' => $data_time, 'end_date' => $data_time, 'page' => 1]);
            if ($adv_data['code'] == 0) {
                $page = $adv_data['data']['page_info']['total_page'];
                $this->update_fish_adv_data_new($adv_data['data']['rows'], $root_id, $data_time, $advertiser_id,$val['dimensions']['cdp_project_id'],$val['dimensions']['cdp_project_name'], $type);
                //如果广告计划存在多页
                if ($page > 1) {
                    for ($i = 1; $i < $page; $i++) {
                        $tmp_adv_data = $fish->getAdListV2($tmp_data['fish_access_token'], ['advertiser_id' => $advertiser_id, 'campaign_ids' => $val['dimensions']['cdp_project_id'], 'start_date' => $data_time, 'end_date' => $data_time, 'page' => $i + 1]);
                        if ($tmp_adv_data['code'] == 0) {
                            $this->update_fish_adv_data_new($tmp_adv_data['data']['rows'], $root_id, $data_time, $advertiser_id,$val['dimensions']['cdp_project_id'],$val['dimensions']['cdp_project_name'], $type);
                        }
                    }
                }
            }
        }
        FishCampaign::insertAll($data);
    }

    /**
     * 更新数据
     */
    public function update_fish_campaign_data($list, $fish, $tmp_data, $root_id, $data_time, $advertiser_id, $type)
    {
        $data = [];
        foreach ($list as $val) {
            //先删除昨日数据后插入数据
            FishCampaign::where(['root_id' => $root_id, 'data_time' => $data_time, 'advertiser_id' => $advertiser_id, 'campaign_id' => $val['campaign_id'],'type'=>$type])->delete();
            FishCampaignList::where(['root_id' => $root_id, 'data_time' => $data_time, 'advertiser_id' => $advertiser_id, 'campaign_id' => $val['campaign_id'],'type'=>$type])->delete();
            $val['advertiser_id'] = $advertiser_id;
            $val['data_time'] = $data_time;
            $val['root_id'] = $root_id;
            $val['type'] = $type;
            unset($val['id']);
            $data[] = $val;
            //循环广告组获取广告组下的计划花费
            $adv_data = $fish->getAdList($tmp_data['fish_access_token'], ['advertiser_id' => $advertiser_id, 'campaign_ids' => $val['campaign_id'], 'start_date' => $data_time, 'end_date' => $data_time, 'page' => 1]);
            if ($adv_data['code'] == 0) {
                $page = $adv_data['data']['page_info']['total_page'];
                $this->update_fish_adv_data($adv_data['data']['list'], $root_id, $data_time, $advertiser_id, $type);
                //如果广告计划存在多页
                if ($page > 1) {
                    for ($i = 1; $i < $page; $i++) {
                        $tmp_adv_data = $fish->getAdList($tmp_data['fish_access_token'], ['advertiser_id' => $advertiser_id, 'campaign_ids' => $val['campaign_id'], 'start_date' => $data_time, 'end_date' => $data_time, 'page' => $i + 1]);
                        if ($tmp_adv_data['code'] == 0) {
                            $this->update_fish_adv_data($tmp_adv_data['data']['list'], $root_id, $data_time, $advertiser_id, $type);
                        }
                    }
                }
            }
        }
        FishCampaign::insertAll($data);
    }

    /**
     * 更新广告组下的计划
     */
    public function update_fish_adv_data($data, $root_id, $data_time, $advertiser_id, $type)
    {
        $tmp_adv_list = [];
        foreach ($data as $val) {
            unset($val['id']);
            unset($val['avg_show_cost']);
            $val['root_id'] = $root_id;
            $val['advertiser_id'] = $advertiser_id;
            $val['data_time'] = $data_time;
            $val['type'] = $type;
            $tmp_adv_list[] = $val;
        }
        FishCampaignList::insertAll($tmp_adv_list);
    }

    /**
     * 更新广告组下的计划-新版
     */
    public function update_fish_adv_data_new($data, $root_id, $data_time, $advertiser_id, $campaign_id, $campaign_name , $type)
    {
        $tmp_adv_list = [];
        foreach ($data as $val) {
            $tmp_val['ad_id'] = $val['dimensions']['cdp_promotion_id'];
            $tmp_val['ad_name'] = $val['dimensions']['cdp_promotion_name'];
            $tmp_val['cost'] = $val['metrics']['stat_cost'];
            $tmp_val['show'] = $val['metrics']['show_cnt'];
            $tmp_val['avg_click_cost'] = $val['metrics']['cpm_platform'];
            $tmp_val['click'] = $val['metrics']['click_cnt'];
            $tmp_val['ctr'] = $val['metrics']['ctr'];
            $tmp_val['convert'] = $val['metrics']['convert_cnt'];
            $tmp_val['convert_cost'] = $val['metrics']['conversion_cost'];
            $tmp_val['convert_rate'] = $val['metrics']['conversion_rate'];
            $tmp_val['campaign_id'] = $campaign_id;
            $tmp_val['campaign_name'] = $campaign_name;
            $tmp_val['root_id'] = $root_id;
            $tmp_val['advertiser_id'] = $advertiser_id;
            $tmp_val['data_time'] = $data_time;
            $tmp_val['type'] = $type;
            $tmp_adv_list[] = $tmp_val;
        }
        FishCampaignList::insertAll($tmp_adv_list);
    }

    /**
     * 刷新token
     */
    public function refreshToken($fish, $tmp_data, $root_id)
    {
        $fish_refresh_time = $tmp_data['fish_refresh_time']; //上次token更新时间
        if ($fish_refresh_time + 80000 < time()) {
            $token = $fish->getRefreshToken($tmp_data['fish_refresh_token']);
            if ($token['code'] == 0) {
                $tmp_data['fish_access_token'] = $token['data']['access_token'];
                $tmp_data['fish_refresh_token'] = $token['data']['refresh_token'];
                $tmp_data['fish_refresh_time'] = time();
                Company::where(['root_id' => $root_id])->update(['channel_setting' => json_encode($tmp_data)]);
            } else {
                $tmp_data['fish_access_token'] = '';
                $tmp_data['fish_refresh_token'] = '';
                Company::where(['root_id' => $root_id])->update(['channel_setting' => json_encode($tmp_data)]);
            }
        }
        return $tmp_data;
    }

    /**
     * 刷新token
     */
    public function refreshTokenV2($fish, $tmp_data, $root_id)
    {
        $fish_refresh_time = $tmp_data['fish_refresh_time']; //上次token更新时间
        if ($fish_refresh_time + 80000 < time()) {
            $token = $fish->getRefreshToken($tmp_data['fish_refresh_token']);
            if ($token['code'] == 0) {
                $tmp_data['fish_access_token'] = $token['data']['access_token'];
                $tmp_data['fish_refresh_token'] = $token['data']['refresh_token'];
                $tmp_data['fish_refresh_time'] = time();
                Company::where(['root_id' => $root_id])->update(['channel_setting_wh' => json_encode($tmp_data)]);
            } else {
                $tmp_data['fish_access_token'] = '';
                $tmp_data['fish_refresh_token'] = '';
                Company::where(['root_id' => $root_id])->update(['channel_setting_wh' => json_encode($tmp_data)]);
            }
        }
        return $tmp_data;
    }
}