dms-api/app/v1/logic/tool/advert/GdtCostHourLogic.php

<?php

namespace app\v1\logic\tool\advert;

use app\v1\logic\advert\AgentSiteLogic;
use GuzzleHttp\Client;
use support\think\Db;

class GdtCostHourLogic
{
    protected string $dateTable = "media_cost";

    protected string $hourTable = "media_cost_hour";

    protected string $date;

    protected array $advertiserIds=[];

    protected array $siteMap=[];

    public function run($params=[])
    {
        // 重跑用，传数组
        if(!empty($params['date'])){
            $sDate = is_array($params['date']) ? $params['date'][0] : $params['date'];
            $eDate = !empty($params['date'][1]) ? $params['date'][1] : $params['date'][0];
            // 检查日期格式是否正确
            if(!isValidDate($sDate) || !isValidDate($eDate)){
                return json_encode(["status"=>"error", "msg"=>"日期格式不正确"], 256);
            }
        }else{
            $sDate = date('Y-m-d');
            $eDate = date('Y-m-d');
        }

        // 重跑用，传数组
        if(!empty($params['advertiser_ids'])){
            $this->advertiserIds = $params['advertiser_ids'];
        }

        for ($date = $sDate; $date <= $eDate; $date = date('Y-m-d', strtotime($date . '+1 day'))){
            $this->date = $date;
            try {
                $this->initStart();
            }catch (\Exception $e){
                return json_encode(["status"=>"error", "msg"=>$e->getMessage()], 256);
            }
        }

        $this->reRun();

        return json_encode(["status"=>"success", "msg"=>""], 256);
    }

    // 重跑
    protected function reRun(): void
    {
        if (date('H') == 8 && date('i') < 20) {
            $this->date = date('Y-m-d', strtotime("-1 days"));
            $this->initStart();
        }
    }

    protected function initStart(): void
    {
        $tokenMap = $this->getTokenMap();

        $accountList = $this->getAccountList();

        $this->siteMap = (new AgentSiteLogic())->getSiteAuth();
        // 循环执行
        foreach ($accountList as $account)
        {
            $accessToken = $tokenMap[$account['pmid']] ?? "";
            if(!$accessToken) continue;

            // 获取消耗
            $dataList = $this->getGdtCost($account['advertiser_id'], $accessToken);

            // 整理数据入库
            $this->organizeDataList($account, $dataList);
        }
    }

    // 获取媒体消耗
    protected function getGdtCost($advertiser_id, $access_token): array
    {
        $page = 1;
        $data = [];
        do {
            $nonce = md5(time().rand(00000, 99999));
            $url = 'https://api.e.qq.com/v3.0/hourly_reports/get?access_token='.$access_token.'&timestamp='.time().'&nonce='.$nonce;
            $request_data = [
                'account_id' => $advertiser_id,
                'level'      => 'REPORT_LEVEL_ADGROUP',
                'date_range' => json_encode(['start_date' => $this->date, 'end_date' => $this->date]),
                'group_by'   => json_encode(['hour', 'adgroup_id']),
                'fields'     => json_encode(['hour', 'adgroup_id', 'adgroup_name', 'view_count', 'valid_click_count', 'cost', 'activated_count']),
                'page'       => $page,
                'page_size'  => 100,
            ];
            $url = $url."&".http_build_query($request_data);
            $httpClient = new Client(['timeout' => 10]);
            $res = $httpClient->request('GET', $url);
            $result = json_decode($res->getBody(), true);

            if (empty($result['data']['list'])) {
                break;
            }

            $data = array_merge($data, $result['data']['list']);
            $totalPage = $result['data']['page_info']['total_page'] ?? 1;
            $page++;
        } while ($page <= $totalPage);

        return $data;
    }

    // 整理入库数据列表
    protected function organizeDataList($account, $dataList): void
    {
        if(!$dataList) return;

        $db = Db::connect('db_advert');
        // 返点率
        $fandianRate = ($account['son_fandian']>1) ? (1/$account['son_fandian']) : $account['son_fandian'];

        $adData = [];
        foreach ($dataList as $val) {
            $ad_id   = $val['adgroup_id'];
            $ad_name = $val['adgroup_name'];
            $cost    = $val['cost']/100;
            $show    = $val['view_count'];
            $click   = $val['valid_click_count'];
            $convert = $val['activated_count'];

            // Todo 从广告名称中拆分归因数据
            preg_match("/([\d]*)_([\d]*)_([\d]*)/", $ad_name, $matchs);
            $game_id = $matchs[1] ?? 0;
            $agent_id = $matchs[2] ?? 0;
            $site_id = $matchs[3] ?? 0;

            if(!$game_id || empty($this->siteMap[$site_id])) continue;

            $where = [
                'ad_id'   => $ad_id,
                'game_id' => $game_id,
                'agent_id'=> $agent_id,
                'site_id' => $site_id,
                'tdate'   => $this->date,
                'thour'   => $val['hour'],
            ];

            $data = [
                'advertiser_id' => $account['advertiser_id'],
                'ad_show' => $show,
                'ad_click' => $click,
                'ad_convert' => $convert,
                'ori_money' => $cost,
                'money' => $cost * $fandianRate,
                'media_id' => $this->siteMap[$site_id]['media_id'],
                'auth_id' => $this->siteMap[$site_id]['auth_id'],
            ];

            $hourId= $db->table($this->hourTable)->where($where)->value("id");
            if($hourId) {
                $data['id'] = $hourId;
            }
            // 保存小时数据
            $db->table($this->hourTable)->save(array_merge($data, $where));

            // 计算天的数据
            $adKey = $game_id . "_" . $site_id . "_" . $ad_id;
            $adData[$adKey]['game_id'] = $game_id;
            $adData[$adKey]['agent_id'] = $agent_id;
            $adData[$adKey]['site_id'] = $site_id;
            $adData[$adKey]['ad_id'] = $ad_id;
            $adData[$adKey]['ad_show'] = !empty($adData[$adKey]['ad_show']) ? $adData[$adKey]['ad_show']+$show : $show;
            $adData[$adKey]['ad_click'] = !empty($adData[$adKey]['ad_click']) ? $adData[$adKey]['ad_click']+$click : $click;
            $adData[$adKey]['ad_convert'] = !empty($adData[$adKey]['ad_convert']) ? $adData[$adKey]['ad_convert']+$convert : $convert;
            $adData[$adKey]['cost'] = !empty($adData[$adKey]['cost']) ? $adData[$adKey]['cost']+$cost : $cost;
        }

        foreach($adData as $value){
            $where = [
                'ad_id'   => $value['ad_id'],
                'game_id' => $value['game_id'],
                'agent_id'=> $value['agent_id'],
                'site_id' => $value['site_id'],
                'tdate'   => $this->date,
            ];

            $data = [
                'advertiser_id' => $account['advertiser_id'],
                'ad_show'   => $value['ad_show'],
                'ad_click'  => $value['ad_click'],
                'ad_convert'=> $value['ad_convert'],
                'ori_money' => $value['cost'],
                'money'     => $value['cost']*$fandianRate,
                'media_id' => $this->siteMap[$site_id]['media_id'],
                'auth_id' => $this->siteMap[$site_id]['auth_id'],
            ];

            $dateId= $db->table($this->dateTable)->where($where)->value("id");
            if($dateId) {
                $data['id'] = $dateId;
            }
            // 保存日数据
            $db->table($this->dateTable)->save(array_merge($data, $where));
        }
    }

    // 获取需要拉取消耗的媒体账户
    protected function getAccountList(): array
    {
        $table = "ad_gdt_account_list";
        $where = [
            "status" => 1
        ];

        if($this->advertiserIds){
            $where['advertiser_id'] = $this->advertiserIds;
        }

        return Db::connect('db_advert')
            ->table($table)
            ->where($where)
            ->column("pmid, advertiser_id, advertiser_name, son_fandian");
    }

    protected function getTokenMap(): array
    {
        $table = "ad_gdt_account";
        $where = [
            "status" => 1
        ];
        return Db::connect('db_advert')->table($table)->where($where)->column("access_token", "id");
    }
}