crazy_god_API/crontab/crontabAdjustData.php

<?php

const ChannelTrackMaps = [
    "Organic"           => [ "g1b8nag" ],
	"游戏内部跟踪链接"     => [ "yhm47x1", "sij469x", "ynlt7ad", "l67x2mt", "6jq5m5g", "a7krf04", "v4x1byi", "327ogs3", "vt67uin", "cl7yqth", "fqupiw4", "b8q4iua", "x2ao8dk", "tri542t", "c9h1f36", "nua60j4", "o5rfavc" ],
	"社媒KOL"            => [ "ys85rvc", "n3l2809", "d2p6i5s", "oscpr9r" ],
	"各平台主页渠道"       => [ "a9nv7dj", "53ece3e", "1z942q1", "d7wopl7", "j7u6h9x", "to6eamo" ],
	"邮件营销"            => [ "cj6x62s", "pgdmu1z", "4x6dr4w" ],
	"游戏外邀请"          => [ "1jn49bp", "y8phovy" ],
	"Facebook"          => [ "2xgabxp", "k1qjyjr", "cwvjh9o", "oss8d1v" ],
	"Google_ads"        => [ "ussdj6t", "n6mofty" ],
	"苹果商店页搜索广告"    => [ "rs64xuy" ],
	"Unity"             => [ "u69nimr" ],
	"Applovin"          => [ "kkph0xk" ],
	"IronSource"        => [ "vjjyyf2" ],
	"Chartboost"        => [ "14dbbj0" ],
	"Mintegral"         => [ "r2phf78" ],
	"Vungle"            => [ "6j6t1sg" ],
	"Inplayable"        => [ "n4tb7gp" ],
	"Linkme"            => [ "yw8hp34" ],
	"Tecdo"             => [ "vbc3665" ],
	"Virgrilmedia"      => [ "6366s1a" ],
	"TikTok"            => [ "6xxj7ry" ],
	"Prodege"           => [ "ji2y5ap" ],
];
const ViewChannels = [
    "Facebook", "Google_ads", "Mintegral", "苹果商店页搜索广告", "TikTok"
];
if (substr(PHP_SAPI_NAME(), 0, 3) !== 'cli') {
    die("The program runs only in CLI mode!");
}
const API_TOKEN = 'Tha-k75HDFTdG9WE_s58';
const APP_TOKEN = 'rv3fkx1a8em8';
const DB_CONN = 'backend';
const API_URL = 'https://dash.adjust.com/control-center/reports-service/report?';
!defined('TIMESTAMP') && define('TIMESTAMP', time());
$sid = $lid = 1;
include_once dirname(__FILE__) . "/crontab.inc.php";
include_once dirname(__FILE__) . "/../com.php";
set_time_limit(0);
$hi = date('Hi', TIMESTAMP);
if (!empty($argv[1]))$hi = $argv[1];
oo::logs()->debug3( "hi:{$hi}, time:". TIMESTAMP, 'adjustDataCrontab');
switch ($hi){
    case '0000':// 8点00分 拉取周报
    case '0800':// 8点00分 拉取周报
        Adjust::adjustReport('week') ;
        break;
    case '0015':// 8点15分 拉取周报
    case '0815':// 8点15分 拉取周报
        Adjust::adjustReport('day') ;
        break;
    case '0030':// 8点30分 拉取月报
    case '0830':// 8点30分 拉取月报
        Adjust::adjustReport('month') ;
        break;
    case '0045':// 8点45分 拉取日报明细
        Adjust::adjustReportCampaign() ;
        break;
    case '0845':// 8点45分 拉取日报明细
        Adjust::adjustReportCampaign(7) ;
        break;
    case '0010':// 9点 拉取日报明细
        Adjust::adjustReportViews() ;
        break;
    case '0810':// 9点 拉取日报明细
        Adjust::adjustReportViews(7) ;
        break;
    case '0001':// prodege 七日过岛120 人数
        Adjust::prodegeSpend() ;
        break;
    case '0005':// asaction 七日过岛100 人数
        Adjust::adActionSpend() ;
        break;
    case '0740':// Mistplay 花费
        Adjust::mistplaySpend() ;
    case '0745':// adjoe 花费
        Adjust::adjoeSpend() ;
        break;
    case '0035':// digitalturbine 花费
        Adjust::digitalturbineSpend() ;
        break;
}
Class Adjust{
    static $headers = [
        'Authorization:Bearer '.API_TOKEN
    ];

    static $channelDateEvents = [
        'adaction' => [ //adaction 7 天100 岛  20刀
            '2022-12-01' => [
                'passisland100_d7_events_cohort' => 20
            ],
        ],
        'prodege' => [  // Prodege多了一个链接 然后这个月开始事件改了哈  改成了20天内过120岛  给35刀，过200岛 给50刀，也就是说两个20天内 两个事件都过得话 就是85刀
            '2022-12-01' => [
                'passisland120_d20_events_cohort' => 35,
                'passisland200_d20_events_cohort' => 50,
            ],
            '2022-09-01' => [
                'passisland120_d7_events_cohort' => 45,
            ],
            '2022-08-01' => [
                'passisland20_d7_events_cohort' => 45 * 0.537542662,
            ],
        ],
    ];
    private static function getDateList($type, $maxNum = 120){
        $dayTime = strtotime(date('Y-m-d', TIMESTAMP));
        switch ($type){
            case 'day':
                $beginTime = $dayTime - 86400;
                break;
            case 'week':
                $maxNum = 52;
                $weekIdx = date('w', $dayTime);
                $weekIdx = empty($weekIdx) ? 7 : $weekIdx;
                $beginTime = $dayTime - ($weekIdx-1)*86400;
                break;
            case 'month':
                $maxNum = 13;
                $monthIdx = date('d', $dayTime);
                $beginTime = $dayTime - ($monthIdx-1)*86400;
                break;
            default:
                return[];
        }
        $dateList = [];
        for($i = 0; $i<= $maxNum; $i++){
            $stime = strtotime("-{$i} {$type}", $beginTime);
            $etime = strtotime("+1 {$type}", $stime) - 86400;
            $dateList[] = [date('Y-m-d', $stime), date('Y-m-d', $etime)];
        }
        return $dateList;

    }

    private static function getOldData($table, $date){
        $sql = "select * from {$table} where date = '{$date}'";
        $oldDatas = oo::commonOprDb(DB_CONN)->getAll($sql,MYSQLI_ASSOC);
        return $oldDatas;
    }

    # 周报，月报
    static function adjustReport($type = 'month'){
        $typePrefix = substr($type, 0, 1);
        $dateList = self::getDateList($type);
        $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','paying_user_size'];
        $periodDays = self::getPeriodIdxs($type);
        $periodItems = self::getPeriodItems($type);
        $adjustReportTable = otable::adjustReport($type);
        $dayCnt = 0;
        oo::commonOprDb(DB_CONN)->query("set names utf8mb4");
        foreach ($dateList as $dateArray){
            $insertAdjustData = [];
            $updateAdjustData = [];
            $dayCnt++;
            list($start, $end) = $dateArray;
            oo::logs()->debug3(   "index:$dayCnt:{$start} ~ {$end}:" , 'adjustDataCrontab');
            $dateKey = $type == 'day' ? $start : date('Ymd', strtotime($start)) . '~'.date('Ymd', strtotime($end)) ;

            $params['date_period'] = "{$start}:{$end}";
            $params['dimensions'] = "country_code,network,tracker_token,os_name";
            $params['metrics'] = implode(',',$periodItems);
            $params['attribution_type'] = "click";
//            $params['reattributed'] = "false";
            $params['app_token__in'] = APP_TOKEN;
            $url = urldecode(API_URL . http_build_query($params));
            oo::logs()->debug3(  $url , 'adjustDataCrontab');
            $response = funs::curlPost($url, self::$headers, [], 'GET');
            $response = @json_decode($response, true)['rows'] ?? [];
            oo::logs()->debug3(  '记录数：' . count($response) , 'adjustDataCrontab');
            # 查询老数据
            $oldDatas = self::getOldData($adjustReportTable, $dateKey);
            $oldAdjustDataIdMaps = [];
            foreach ($oldDatas as $data){
                $key = "{$data['date']}{$data['countries']}{$data['os_names']}{$data['trackers_channel_token']}";
                $oldAdjustDataIdMaps[$key] = $data['id'];
            }
            $newkips = [0,0,0];
            foreach ($response as $data){
                $key = "{$dateKey}{$data['country_code']}{$data['os_name']}{$data['tracker_token']}";
                $adjustData = [
                    'date' => $dateKey,
                    'countries' => $data['country_code'],
                    'os_names' => $data['os_name'],
                    'trackers_channel_token' => $data['tracker_token'],
                    'trackers_channel_name' => $data['network'],
                    'trackers_group_type' => $data['network'],
                    'kpi_values' => json_encode([$data['daus'], $data['installs'], $data['all_revenue']],32),
                ];
                $periodData = [];
                foreach ($periodDays as $day){
                    $kips = [];
                    foreach ($periodItemsPrefix as $prefix){
                        $kips[] = $data["{$prefix}_{$typePrefix}{$day}"];
                    }
                    $periodData[$day] = $kips;
                }
                $adjustData['period_values'] = json_encode($periodData);
                if(empty($oldAdjustDataIdMaps[$key])) # 新纪录
                    $insertAdjustData[] = $adjustData;
                else
                    $updateAdjustData[$oldAdjustDataIdMaps[$key]] = $adjustData;
            }
            oo::logs()->debug3(  'insertAdjustData:'.count($insertAdjustData), 'adjustDataCrontab');
            oo::logs()->debug3(  'updateAdjustData:'.count($updateAdjustData), 'adjustDataCrontab');
            if(count($insertAdjustData)){
                $insertAdjustDataList = array_chunk($insertAdjustData, 100);
                foreach ($insertAdjustDataList as $insertAdjustData){
                    oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($adjustReportTable, array_keys($insertAdjustData[0]), $insertAdjustData));
                }
            }

            foreach ($updateAdjustData as $id => $updateItem){
                oo::commonOprDb(DB_CONN)->query(funs::db_updateSQL($adjustReportTable, $updateItem,  ' id='.$id));
            }
            oo::logs()->debug3(  '--------------------------------------------------------------' , 'adjustDataCrontab');
        }
        oo::logs()->debug3(  $dayCnt , 'adjustDataCrontab');
    }
    static function adjustReportViews($days = 120){
        ini_set('memory_limit', -1);
        $type = 'day';
        $typePrefix = substr($type, 0, 1);
        $dateList = self::getDateList($type, $days);
        $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','revenue_total'];
        $periodDays = [
            0,
            1,
            2,
            6,
            14,
            29,
            59,
            89,
            119,
        ];
        $periodItems = self::getPeriodItems($type, $periodDays);
        $adjustViewsTable = otable::adjustPeriodViews();
        $dayCnt = 0;
        oo::commonOprDb(DB_CONN)->query("set names utf8mb4");
        $viewTracks = [];
        foreach (ViewChannels as $channel){
            $viewTracks = array_merge($viewTracks, (array)ChannelTrackMaps[$channel]);
        }

        foreach ($dateList as $dateArray){
            $insertAdjustData = [];
            $updateAdjustData = [];
            $dayCnt++;
            list($start, $end) = $dateArray;
            oo::logs()->debug3(   "index:$dayCnt:{$start} ~ {$end}:" , 'adjustDataCrontab');
//            $dateKey = $type == 'day' ? $start : date('Ymd', strtotime($start)) . '~'.date('Ymd', strtotime($end)) ;
            $dateKey = $start;

            $params['date_period'] = "{$start}:{$end}";
            $params['dimensions'] = "channel,os_name,country_code,partner_name,adgroup_id_network,adgroup_network,creative_id_network,creative_network,campaign_id_network,campaign_network,network_token,campaign_token,adgroup_token,creative_token";
            $params['metrics'] = implode(',',$periodItems);
            $params['attribution_type'] = "click";
            $params['app_token__in'] = APP_TOKEN;
            $params['ad_revenue_sources'] = "Applovin+MAX+FB+only%2CAppLovin+Max";//&ad_revenue_sources=Applovin+MAX+FB+only%2CAppLovin+Max
//            $params['reattributed'] = "false";
            $url = urldecode(API_URL . http_build_query($params));
            oo::logs()->debug3(  $url , 'adjustDataCrontab');
            $response = funs::curlPost($url, self::$headers, [], 'GET');
            $response = json_decode($response, true)['rows'] ?? [];
            oo::logs()->debug3(  '记录数：' . count($response) , 'adjustDataCrontab');
            # 查询老数据
            $oldViewsDatas = self::getOldData($adjustViewsTable, $dateKey);
            $oldViewsDataIdMaps = [];
            foreach ($oldViewsDatas as $data){
                $key = "{$data['date']}{$data['countries']}{$data['os_names']}{$data['trackers_channel_token']}{$data['campaigns_token']}{$data['adgroups_token']}{$data['creatives_token']}";
                $oldViewsDataIdMaps[$key] = $data['id'];
            }

            foreach ($response as $data){
                if (!in_array($data['network_token'], $viewTracks))continue;
                if(empty($data['campaign_token']) || empty($data['adgroup_token']) || empty($data['creative_token']) )continue;
                $key = "{$dateKey}{$data['country_code']}{$data['os_name']}{$data['network_token']}{$data['campaign_token']}{$data['adgroup_token']}{$data['creative_token']}";
                $adjustData = [
                    'date' => $dateKey,

                    'trackers_channel_token'    => $data['network_token'],
                    'trackers_channel_name'     => $data['partner_name'],
                    'trackers_group_type'       => $data['channel'],

                    'campaigns_token'           => $data['campaign_token'],
                    'campaigns_name'            => $data['campaign_network'],
                    'campaigns_id'              => $data['campaign_id_network'],

                    'adgroups_token'            => $data['adgroup_token'],
                    'adgroups_name'             => $data['adgroup_network'],
                    'adgroups_id'               => $data['adgroup_id_network'],

                    'creatives_token'           => $data['creative_token'],
                    'creatives_name'            => $data['creative_network'],
                    'creatives_id'              => $data['creative_id_network'],

                    'countries'                 => $data['country_code'] ?? 'none',
                    'os_names'                  => $data['os_name'],
                    'kpi_values'                => json_encode([$data['daus'], $data['installs'], $data['all_revenue'], $data['revenue']],32),
                ];
                foreach ($periodDays as $day){
                    $kips = [];
                    foreach ($periodItemsPrefix as $prefix){
                        $kips[] = $data["{$prefix}_{$typePrefix}{$day}"];
                    }
                    $adjustData["period_{$day}_kpis"] = json_encode($kips);
                }
                if(empty($oldViewsDataIdMaps[$key])) # 新纪录
                    $insertAdjustData[] = $adjustData;
                else
                    $updateAdjustData[$oldViewsDataIdMaps[$key]] = $adjustData;
            }

            oo::logs()->debug3(  'insertAdjustData:'.count($insertAdjustData), 'adjustDataCrontab');
            oo::logs()->debug3(  'updateAdjustData:'.count($updateAdjustData), 'adjustDataCrontab');
            if(count($insertAdjustData)){
                $insertAdjustDataList = array_chunk($insertAdjustData, 500);
                foreach ($insertAdjustDataList as $insertAdjustData){
                    oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($adjustViewsTable, array_keys($insertAdjustData[0]), $insertAdjustData));
                }
            }
            foreach ($updateAdjustData as $id => $updateItem){
                oo::commonOprDb(DB_CONN)->query(funs::db_updateSQL($adjustViewsTable, $updateItem,  ' id='.$id));
            }
            oo::logs()->debug3(  '--------------------------------------------------------------' , 'adjustDataCrontab');
        }
        oo::logs()->debug3(  $dayCnt , 'adjustDataCrontab');
    }
    static function adjustReportCampaign($days = 120){
        $type = 'day';
        $typePrefix = substr($type, 0, 1);
        $dateList = self::getDateList($type, $days);
        $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','revenue_total'];
        $periodDays = [
            0,
            1,
            2,
            6,
            14,
            29,
            59,
            89,
            119,
        ];
        $periodItems = self::getPeriodItems($type, $periodDays);
        $adjustDataTable = otable::adjustPeriodCampaigns();
        $dayCnt = 0;
        oo::commonOprDb(DB_CONN)->query("set names utf8mb4");
        $viewTracks = [];
        foreach (ViewChannels as $channel){
            $viewTracks = array_merge($viewTracks, (array)ChannelTrackMaps[$channel]);
        }
        foreach ($dateList as $dateArray){
            $insertAdjustData = [];
            $updateAdjustData = [];
            $dayCnt++;
            list($start, $end) = $dateArray;
            oo::logs()->debug3(   "index:$dayCnt:{$start} ~ {$end}:" , 'adjustDataCrontab');
//            $dateKey = $type == 'day' ? $start : date('Ymd', strtotime($start)) . '~'.date('Ymd', strtotime($end)) ;
            $dateKey = $start;

            $params['date_period'] = "{$start}:{$end}";
            $params['dimensions'] = "channel,os_name,country_code,partner_name,campaign_id_network,campaign_network,network_token,campaign_token";
            $params['metrics'] = implode(',',$periodItems);
            $params['attribution_type'] = "click";
            $params['app_token__in'] = APP_TOKEN;
            $params['ad_revenue_sources'] = "Applovin+MAX+FB+only%2CAppLovin+Max";//&ad_revenue_sources=Applovin+MAX+FB+only%2CAppLovin+Max
//            $params['reattributed'] = "false";
            $url = urldecode(API_URL . http_build_query($params));
            oo::logs()->debug3(  $url , 'adjustDataCrontab');
            $response = funs::curlPost($url, self::$headers, [], 'GET');
            $response = @json_decode($response, true)['rows'] ?? [];
            oo::logs()->debug3(  '记录数：' . count($response) , 'adjustDataCrontab');
            # 查询老数据
            $oldAdjustData = self::getOldData($adjustDataTable, $dateKey);
            $oldAdjustDataIdMaps = [];
            foreach ($oldAdjustData as $data){
                $key = "{$data['date']}{$data['countries']}{$data['os_names']}{$data['trackers_channel_token']}{$data['campaigns_token']}";
                $oldAdjustDataIdMaps[$key] = $data['id'];
            }

            foreach ($response as $data){
                if (in_array($data['network_token'], $viewTracks))continue;
                $key = "{$dateKey}{$data['country_code']}{$data['os_name']}{$data['network_token']}{$data['campaign_token']}";
                $adjustData = [
                    'date' => $dateKey,
                    'countries' => $data['country_code'] ?? 'none',
                    'os_names' => $data['os_name'],
                    'trackers_channel_token' => $data['network_token'],
                    'trackers_channel_name' => $data['partner_name'],
                    'trackers_group_type' => $data['channel'],
                    'campaigns_token' => $data['campaign_token'],
                    'campaigns_name' => $data['campaign_network'],
                    'campaigns_id' => $data['campaign_id_network'],
                    'kpi_values' => json_encode([$data['daus'], $data['installs'], $data['all_revenue'], $data['revenue']],32),// dau install 全部收入  内购收入
                ];
                foreach ($periodDays as $day){
                    $kips = [];
                    foreach ($periodItemsPrefix as $prefix){
                        $kips[] = $data["{$prefix}_{$typePrefix}{$day}"];
                    }
                    $adjustData["period_{$day}_kpis"] = json_encode($kips);
                }
                if(empty($oldAdjustDataIdMaps[$key])) # 新纪录
                    $insertAdjustData[] = $adjustData;
                else
                    $updateAdjustData[$oldAdjustDataIdMaps[$key]] = $adjustData;
            }
            oo::logs()->debug3(  'insertAdjustData:'.count($insertAdjustData), 'adjustDataCrontab');
            oo::logs()->debug3(  'updateAdjustData:'.count($updateAdjustData), 'adjustDataCrontab');
            if(count($insertAdjustData)){
                $insertAdjustDataList = array_chunk($insertAdjustData, 500);
                foreach ($insertAdjustDataList as $insertAdjustData){
                    oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($adjustDataTable, array_keys($insertAdjustData[0]), $insertAdjustData));
                }
            }

            foreach ($updateAdjustData as $id => $updateItem){
                oo::commonOprDb(DB_CONN)->query(funs::db_updateSQL($adjustDataTable, $updateItem,  ' id='.$id));
            }
            oo::logs()->debug3(  '--------------------------------------------------------------' , 'adjustDataCrontab');
        }
        oo::logs()->debug3(  $dayCnt , 'adjustDataCrontab');
    }
    private static function getPeriodIdxs($type){
        $periodDays = [];
        $max = 0;
        switch ($type){
            case 'day':
                $max = 120;
                break;
            case 'week':
                $max = 52;
                break;
            case 'month':
                $max = 13;
                break;
            default:
                return [];
        }
        for ($i=0;$i<=$max;$i++){
            if($type == 'week'){
                if($i > 1 && $i%4)continue;
            }
            $periodDays[] = $i;
        }
        return $periodDays;
    }
    private static function getPeriodItems($type, $periodDays = null){
        $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','revenue_total','paying_user_size'];
        $typePrefix = substr($type, 0, 1);
        $periodDays = $periodDays ?? self::getPeriodIdxs($type);
        $periodItems = ['daus','installs','cohort_all_revenue','all_revenue','revenue'];
        foreach ($periodItemsPrefix as $item){
            foreach ($periodDays as $day){
                $periodItems[] = "{$item}_{$typePrefix}{$day}";
            }
        }
        return $periodItems;
    }

    static function prodegeSpend(){

        $channel = 'prodege';
        $end = date('Y-m-d', TIMESTAMP - 20 * 86400);
        $start = $end;
        $table = otable::prodegeSpends();

        $events = array_keys(self::getEventsByChannelDate($channel, $start));
        $event = implode(',', $events);
        $query = "full_data=true&attribution_source=first&cost_mode=mixed&reattributed=all&attribution_type=all&readable_names=false&format_dates=false&metrics=installs,{$event}&dimensions=day&sort=day&utc_offset=%2B00:00&cohort_maturity=mature&sandbox=false&chart_1_dimensions=day&chart_1_metrics=installs&chart_2_dimensions=day&chart_2_metrics=installs&date_period={$start}:{$end}&channel_id__in=partner_1582&app_token__in=".APP_TOKEN;
        $url = API_URL . $query;
        oo::logs()->debug3(  $url , 'adjustDataCrontab');
        $response = funs::curlPost($url, self::$headers, [], 'GET');
        $rows = @json_decode($response, true)['rows'] ?? [];
        oo::logs()->debug3(  '记录数：' . count($rows) , 'adjustDataCrontab');
        $insertData = [];
        $deleteDates = [];
        foreach ($rows as $row){
            $deleteDates[] = $row['day'];
            $insertData[] = [
                'date' => $row['day'],
                'installs' => $row['installs'],
                'spends' => self::getSpends($channel, $row),
            ];
        }
        $deleteDatesStr = implode("','", $deleteDates);
        oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
        if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
    }
    
    static function adActionSpend($time = TIMESTAMP){
        $channel = 'adaction';
        $end = date('Y-m-d', $time - 7 * 86400);
        $start = $end;//date('Y-m-d', $time - 14 * 86400);
        $table = otable::adActionSpends();

        $events = array_keys(self::getEventsByChannelDate($channel, $start));
        $event = implode(',', $events);
        $query = "full_data=true&attribution_source=first&cost_mode=mixed&reattributed=all&attribution_type=all&readable_names=false&format_dates=false&metrics=installs,{$event}&dimensions=day&sort=day&utc_offset=%2B00:00&cohort_maturity=mature&sandbox=false&chart_1_dimensions=day&chart_1_metrics=installs&chart_2_dimensions=day&chart_2_metrics=installs&date_period={$start}:{$end}&channel_id__in=partner_234&app_token__in=".APP_TOKEN;
        $url = API_URL . $query;
        oo::logs()->debug3(  $url , 'adjustDataCrontab');
        $response = funs::curlPost($url, self::$headers, [], 'GET');
        $rows = @json_decode($response, true)['rows'] ?? [];
        oo::logs()->debug3(  '记录数：' . count($rows) , 'adjustDataCrontab');
        $insertData = [];
        $deleteDates = [];
        foreach ($rows as $row){
            $deleteDates[] = $row['day'];
            $insertData[] = [
                'date' => $row['day'],
                'installs' => $row['installs'],
                'spends' => self::getSpends($channel, $row) ,
            ];
        }
        $deleteDatesStr = implode("','", $deleteDates);
        oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
        if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
    }

    private static function getSpends($channel, array $row){
        $events = self::getEventsByChannelDate($channel, $row['day']);
        $spends = 0;
        foreach ($events as $event => $cpi){
            $spends += $row[$event] * floatval($cpi);
        }
        return $spends;
    }
    private static function getEventsByChannelDate($channel, $day){
        $channel = strtolower($channel);
        $channelEvents = self::$channelDateEvents[$channel] ?? [];
        if(empty($channelEvents))return [];
        foreach ($channelEvents as $date => $events){
            if($date <= $day){
                return $events;
            }
        }
        return $events;
    }

    static function mistplaySpend(){
        $table = otable::mistplaySpends();
        $end = date('Y-m-d', TIMESTAMP - 86400);
        $start = $end;
        $params = [
            'full_data' => 'true',
            'attribution_source' => 'first',
            'cost_mode' => 'mixed',
            'reattributed' => 'all',
            'attribution_type' => 'all',
            'readable_names' => 'false',
            'format_dates' => 'false',
            'metrics' => 'cost,installs',
            'dimensions' => 'day',
            'sort' => 'day',
            'utc_offset' => urldecode('%2B00:00'),
            'cohort_maturity' => 'mature',
            'sandbox' => 'false',
            'chart_1_dimensions' => 'day',
            'chart_1_metrics' => 'installs',
            'date_period' => "{$start}:{$end}",
            'channel_id__in' => 'partner_1111',
            'app_token__in' => APP_TOKEN,
        ];
        $query = http_build_query($params);
        $url = API_URL . $query;

        oo::logs()->debug3(  'request url:'.$url , 'adjustDataCrontab');
        $response = funs::curlPost($url, self::$headers, [], 'GET');
        $rows = @json_decode($response, true)['rows'] ?? [];
        oo::logs()->debug3(  '记录数：' . count($rows) , 'adjustDataCrontab');
        $insertData = [];
        $deleteDates = [];
        foreach ($rows as $row){
            $deleteDates[] = $row['day'];
            $insertData[] = [
                'date' => $row['day'],
                'installs' => $row['installs'],
                'spends' => $row['cost'],
            ];
        }

        $deleteDatesStr = implode("','", $deleteDates);
        oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
        if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
    }

    static function adjoeSpend(){
        $table = otable::adjoeSpends();
        $end = date('Y-m-d', TIMESTAMP - 86400);
        $start = $end;
        $params = [
            'full_data' => 'true',
            'attribution_source' => 'first',
            'cost_mode' => 'mixed',
            'reattributed' => 'all',
            'attribution_type' => 'all',
            'readable_names' => 'false',
            'format_dates' => 'false',
            'metrics' => 'cost,installs',
            'dimensions' => 'day',
            'sort' => 'day',
            'utc_offset' => urldecode('%2B00:00'),
            'cohort_maturity' => 'mature',
            'sandbox' => 'false',
            'chart_1_dimensions' => 'day',
            'chart_1_metrics' => 'installs',
            'date_period' => "{$start}:{$end}",
            'channel_id__in' => 'partner_1523',
            'app_token__in' => APP_TOKEN,
        ];
        $query = http_build_query($params);
        $url = API_URL . $query;

        oo::logs()->debug3(  'request url:'.$url , 'adjustDataCrontab');
        $response = funs::curlPost($url, self::$headers, [], 'GET');
        $rows = @json_decode($response, true)['rows'] ?? [];
        oo::logs()->debug3(  '记录数：' . count($rows) , 'adjustDataCrontab');
        $insertData = [];
        $deleteDates = [];
        foreach ($rows as $row){
            $deleteDates[] = $row['day'];
            $insertData[] = [
                'date' => $row['day'],
                'installs' => $row['installs'],
                'spends' => $row['cost'],
            ];
        }

        $deleteDatesStr = implode("','", $deleteDates);
        oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
        if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
    }

    static function digitalturbineSpend(){
        $tb = otable::digitalturbineSpends();
        $row = oo::commonOprDb(DB_CONN)->getOne("select * from {$tb} where id>0 limit 1");
        $end = date('Y-m-d', TIMESTAMP - 86400);
        if (empty($row)){
            $start = "2023-08-23";
        }else{
            $start = $end;
        }
        // 获取token
        $token = self::getDigitalturbineAccessToken();

        // 获取报告url
        $csvStr = self::getDigitalturbineReportData($token, $start, $end);
        if(empty($csvStr)){
            oo::logs()->debug3( "获取 digitalturbine  报告为空" , 'adjustDataCrontab');
            return;
        }
        $keysMapping = [
            "date" =>"date",
            "Country" =>"country",
            "Advertiser ARPU" =>"arpu",
            "Advertiser Spend" =>"spends",
            "Offer CTR" =>"ctr",
            "Offer Clicks" =>"clicks",
            "Offer Impressions" =>"impressions",
            "Offer Installs" =>'installs',
            "Campaign ID" =>'campaign_id',
            "Campaign Name" =>'campaign_name',
            "Device OS" =>'os',
        ];

        $csvStr = strtr($csvStr, ["\r\n" => "\n"]);
        $lines = explode("\n", $csvStr);
        $reportData = [];
        $keys = [];
        foreach ($lines as $line){
            $values = str_getcsv( $line);
            if(empty($keys)){
                foreach ($values as $k){
                    $keys[] = $keysMapping[$k];
                }
            }else{
                if (count($keys) != count($values))continue;
                $reportData[] = array_combine($keys, $values);
            }
        }
        $reportData = array_filter($reportData);
        if (count($reportData))oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($tb, $keys, $reportData));

    }

    private static function getDigitalturbineAccessToken(){
        $tokenUrl = 'https://reporting.fyber.com/auth/v1/token';
        $clientId = '0524e6d1d62825487a36ad567e88ff88';
        $clientSecret = '2D_oIp9qNVFyaGFuKxhY5l6wmT1aOBJcd91MXU5BXh26GdkpUYw5VOpkToBjWCQRau6c9ZKNPzk_C6dWDnAn7L0pbIdVg_4vLhFws2IxfS94JNdWgpDfStso1wqq9x_BpDRiT7UAw3SxeFWduH6OVsumkefQUGM-336EuJ242554QuJ0dJa0uo0SFTp7fif6fKL5FvXu2jDyP44pM4AcaMJBnUxfUUlDEdsHCxOWUb3EC8loqczgp_L6wK0viH3KwkqIzxsZ2sYAYgXmILv-B18hwCqL3gqnOhcqeYN0vwGuOCyoM5LF99TGw1yqRGyfRjNp5dD1k0Uptidtv6p5gg';
        $requestBody = [
            "grant_type" => "client_credentials",
            "client_id" => $clientId,
            "client_secret" => $clientSecret,
        ];
        $header = [
            'Content-Type:application/json'
        ];
        $response = funs::curlPost($tokenUrl, $header, json_encode($requestBody), 'POST');
        oo::logs()->debug3( '获取 digitalturbine  token 请求：'.json_encode($requestBody).'  响应：'.$response , 'adjustDataCrontab');
        if(is_string($response))$response = json_decode($response, true);
        if (empty($response['accessToken'])){
            return '';
        }
        return $response['accessToken'];
    }
    private static function getDigitalturbineReportData($accessToken, $start, $end){
        if(empty($accessToken)){
            return [];
        }
        $requestBody = [
            "source" =>"event",
            "dateRange" =>[
                "start" =>$start,
                "end" =>$end
            ],
            "metrics" =>[
                "Advertiser Spend",
                "Advertiser ARPU",
                "Offer Impressions",
                "Offer Clicks",
                "Offer CTR",
                "Offer Installs",
            ],
            "splits" =>[
                "Country",
                "Date",
                "Campaign ID",
                "Campaign Name",
                "Device OS",
            ],
            "filters" =>[
//                [
//                    "dimension" =>"Country",
//                    "values" =>[
//                        "GB",
//                        "US",
//                        "DE"
//                    ]
//                ]
            ]
        ];
        $header = [
            'Content-Type:application/json',
            "Authorization: Bearer {$accessToken}"
        ];
        $url = 'https://reporting.fyber.com/api/v1/report/offerwall?format=csv';
        $response = funs::curlPost($url, $header, json_encode($requestBody), 'POST');
        oo::logs()->debug3( '获取 digitalturbine  报告url 请求：'.json_encode($requestBody).'  响应：'.$response , 'adjustDataCrontab');
        if(is_string($response))$response = json_decode($response, true);
        if (empty($response['url'])){
            return[];
        }
        sleep(6 * 60);  // 等待对方文件生成
        return file_get_contents($response['url']);
    }
}
oo::logs()->debug3( "end:".time() , 'adjustDataCrontab');