dms-api/app/v1/logic/tool/GameTotalMonthLogic.php

<?php

namespace app\v1\logic\tool;


use support\think\Db;

class GameTotalMonthLogic extends BaseLogic
{
    protected string $table = "game_total_month";

    protected string $group = 'game_id, agent_id, site_id, tmonth';

    protected array $data;

    protected string $month;
    public function run($params=[])
    {
        if(!empty($params['date'])){
            $sDate = is_array($params['date']) ? $params['date'][0] : $params['date'];
            $eDate = is_array($params['date']) ? $params['date'][1] : $params['date'];
        }else{
            $sDate = date('Y-m');
            $eDate = date('Y-m');
        }

        for ($date = $sDate; $date <= $eDate; $date = date('Y-m', strtotime($date . '+1 month'))){
            $this->month = $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
    {
        $day = date('d');
        $hour = date('H');
        $i = date('i');

        // 重跑上个整月
        if(($day==1 || $day==15) && $hour==4 && $i<10){
            $this->month = date('Y-m', strtotime("-1 month"));
            $this->initStart();
        }
    }

    protected function initStart(): void
    {
        // 重置数据
        $this->data = [];

        $this->regMonth();

        $this->loginMonth();

        $this->regPay();

        $this->payTotal();

        $this->roleCreate();

        $this->mediaCost();

        $this->replaceData(['tmonth'=>$this->month]);
    }

    protected function regMonth()
    {
        $sTime = strtotime("{$this->month}-01 00:00:00");
        $eTime = strtotime(date('Y-m-t 23:59:59', strtotime($sTime)));

        $regTotalTb = "sdk_reg_log_" . date('Ym', strtotime($this->month));
        $filed = "game_id, agent_id, site_id, '{$this->month}' as tmonth, count(distinct uid) as reg_total, count(distinct imei) as reg_dev";

        $where = [
            ['reg_time', 'between', [$sTime, $eTime]],
        ];
        $result = Db::connect('db_game_log')->table($regTotalTb)->field($filed)->where($where)->group($this->group)->select();

        $this->pushData($result);
    }


    protected function loginMonth()
    {
        $tb = "sdk_active_log_" . date('Ym', strtotime($this->month)); // 按天去重
        $filed = "game_id, agent_id, site_id, '{$this->month}' as tmonth, count(distinct uid) as login_total";

        $result = Db::connect('db_game_log')->table($tb)->field($filed)->group($this->group)->select();

        $this->pushData($result);
    }

    protected function regPay()
    {
        $tb = "sdk_order_success";

        $sDate = "{$this->month}-01 00:00:00";
        $eDate = date('Y-m-t 23:59:59', strtotime($this->month));

        $filed = "game_id, agent_id, site_id, '{$this->month}' as tmonth, sum(money) as reg_pay_total, sum(paid_amount) as reg_pay_amount, count(distinct uid) as reg_pay_num";

        $where = [
            ['reg_date', 'between', [$sDate, $eDate]],
            ['pay_date', 'between', [$sDate, $eDate]],
        ];

        $result = Db::connect('db_game_log')->table($tb)->field($filed)->where($where)->group($this->group)->select()->toArray();

        $this->pushData($result);
    }

    protected function payTotal()
    {
        $tb = "sdk_order_success";

        $sDate = "{$this->month}-01 00:00:00";
        $eDate = date('Y-m-t 23:59:59', strtotime($this->month));

        $filed = "game_id, agent_id, site_id, '{$this->month}' as tmonth, sum(money) as pay_total, sum(paid_amount) as pay_amount, count(distinct uid) as pay_num";

        $where = [
            ['pay_date', 'between', [$sDate, $eDate]],
        ];

        $result = Db::connect('db_game_log')->table($tb)->field($filed)->where($where)->group($this->group)->select()->toArray();

        $this->pushData($result);
    }

    protected function roleCreate(): void
    {
        $sDate = "{$this->month}-01 00:00:00";
        $eDate = date('Y-m-t 23:59:59', strtotime($this->month));

        $filed = "game_id, agent_id, site_id, '{$this->month}' as tmonth, COUNT(DISTINCT uid) as role_create_user";
        $where = [
            ['create_time', 'between', [$sDate, $eDate]],
        ];

        // 安卓
        $result = Db::connect('db_game_log')->table("role_data_and")->field($filed)->where($where)->group($this->group)->select();
        $this->pushData($result);

        // iOS
        $result = Db::connect('db_game_log')->table("role_data_ios")->field($filed)->where($where)->group($this->group)->select();
        $this->pushData($result);
    }

    protected function mediaCost(): void
    {
        $sDate = "{$this->month}-01";
        $eDate = date('Y-m-t', strtotime($this->month));

        $filed = "game_id, agent_id, site_id, '{$this->month}' as tmonth, SUM(money) as cost";
        $where = [
            ['tdate', 'between', [$sDate, $eDate]],
        ];

        $result = Db::connect('db_advert')->table("media_cost")->field($filed)->where($where)->group($this->group)->select();

        $this->pushData($result);
    }
}