dms-api/app/process/CreateTables.php

<?php

namespace app\process;
use support\think\Db;
use Workerman\Crontab\Crontab;

class CreateTables
{
    public function onWorkerStart(): void
    {
        // 每天的0点10执行，注意这里省略了秒位
        new Crontab('10 0 * * *', function(){
            $this->initStart();
        });
    }

    protected function initStart()
    {
        echo "开始创建表\n";

        $createTablesSqlList = [
            $this->base_total_day,
            $this->basic_login_total,
            $this->base_total_hour,
            $this->basic_login_total_game,
            $this->basic_login_total_server,
            $this->basic_reg_total,
            $this->game_active_hour,
            $this->server_total_day,
            $this->server_total_hour,
        ];

        foreach ($createTablesSqlList as $sql){
            try {
                $year = date('Y', strtotime('+1 year'));
                $month = date('Ym', strtotime('+1 month'));
                $sql = str_replace('{{YEAR}}', $year, $sql);
                $sql = str_replace('{{MONTH}}', $month, $sql);
                // echo "创建表：".$sql."\n";
                Db::connect("report_data")->execute($sql);
            }catch (\Exception $e){
                echo $e->getMessage();
            }
        }
    }

    protected string $base_total_day = "CREATE TABLE IF NOT EXISTS `base_total_day_{{YEAR}}` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `tdate` date NOT NULL COMMENT '日期',
          `game_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
          `media_id` int(11) NOT NULL DEFAULT '0' COMMENT '媒体ID',
          `agent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '渠道ID',
          `site_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告位ID',
          `auth_id` int(10) NOT NULL DEFAULT '0' COMMENT '负责人ID',
          `ad_show_pv` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告展示PV',
          `ad_show_ip` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告展示IP',
          `ad_click_pv` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告安装PV',
          `ad_click_ip` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告安装IP',
          `download` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '下载数',
          `install` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '安装数',
          `install_ip` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '安装IP',
          `reg_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册数',
          `reg_login_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当天注册当天登陆用户数',
          `reg_dev` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册设备',
          `login_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登陆总数',
          `login_reg_game` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登陆数（注册游戏相同）',
          `old_login_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '老用户登陆',
          `role_create_user` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建角色用户数',
          `reg_pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册付费数',
          `reg_pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册付费金额',
          `reg_pay_amount` float unsigned NOT NULL DEFAULT '0' COMMENT '注册付费分成金额',
          `pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总用户数',
          `pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总金额',
          `pay_amount` float unsigned NOT NULL DEFAULT '0' COMMENT '付费分成总金额',
          `reg_pay_num_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册付费数（剔除跨游戏）',
          `reg_pay_total_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册付费金额（剔除跨游戏）',
          `reg_pay_amount_rg` float unsigned NOT NULL DEFAULT '0' COMMENT '注册付费分成金额（剔除跨游戏）',
          `pay_num_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总用户数（剔除跨游戏）',
          `pay_total_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总金额（剔除跨游戏）',
          `pay_amount_rg` float unsigned NOT NULL DEFAULT '0' COMMENT '付费分成总金额（剔除跨游戏）',
          PRIMARY KEY (`id`) USING BTREE,
          UNIQUE KEY `select_index` (`tdate`,`agent_id`,`site_id`,`game_id`) USING BTREE,
          KEY `agent_id` (`agent_id`) USING BTREE,
          KEY `site_id` (`site_id`) USING BTREE,
          KEY `game_id` (`game_id`) USING BTREE,
          KEY `auth_id` (`auth_id`) USING BTREE,
          KEY `media_id` (`media_id`) USING BTREE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    protected string $basic_login_total = "CREATE TABLE IF NOT EXISTS `basic_login_total_{{YEAR}}` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `tdate` date NOT NULL COMMENT '登录时间',
      `agent_id` int(11) NOT NULL COMMENT '渠道ID',
      `site_id` int(11) NOT NULL DEFAULT '0' COMMENT '广告位ID',
      `cplaceid` varchar(50) DEFAULT NULL COMMENT '子ID',
      `adid` varchar(20) DEFAULT NULL COMMENT '创意ID',
      `turn` int(10) unsigned NOT NULL COMMENT '轮数ID',
      `login_count` int(11) NOT NULL COMMENT '账号数',
      `active` int(10) unsigned NOT NULL COMMENT '活跃天数(0:当天)',
      `plat_id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '平台ID',
      PRIMARY KEY (`id`) USING BTREE,
      KEY `tdate` (`tdate`) USING BTREE,
      KEY `agent_id` (`agent_id`) USING BTREE,
      KEY `site_id` (`site_id`) USING BTREE,
      KEY `plat_id` (`plat_id`) USING BTREE,
      KEY `active` (`active`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    protected string $base_total_hour = "CREATE TABLE IF NOT EXISTS `base_total_hour_{{MONTH}}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tdate` date NOT NULL COMMENT '日期',
  `thour` tinyint(3) unsigned NOT NULL COMMENT '小时',
  `game_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `media_id` int(11) NOT NULL DEFAULT '0' COMMENT '媒体ID',
  `agent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '渠道ID',
  `site_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告位ID',
  `auth_id` int(10) NOT NULL DEFAULT '0' COMMENT '负责人ID',
  `ad_show_pv` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告展示PV',
  `ad_show_ip` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告展示IP',
  `ad_click_pv` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告安装PV',
  `ad_click_ip` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告安装IP',
  `cost` decimal(14,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '消耗金额',
  `download` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '下载数',
  `install` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '安装数',
  `install_ip` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '安装IP',
  `reg_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册数',
  `reg_login_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册登陆用户',
  `reg_dev` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册设备',
  `login_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登陆总数',
  `login_reg_game` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登陆数（注册游戏相同）',
  `old_login_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '老用户登陆',
  `role_create_user` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建用户数',
  `reg_pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '小时注册当天付费数',
  `reg_pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '小时注册当天付费金额',
  `reg_pay_amount` float unsigned NOT NULL DEFAULT '0' COMMENT '小时注册当天分成金额',
  `reg_pay_num_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册累计付费数',
  `reg_pay_total_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册累计付费金额',
  `reg_pay_amount_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册累计分成金额',
  `pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总用户数',
  `pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总金额',
  `pay_amount` float unsigned NOT NULL DEFAULT '0' COMMENT '付费分成总金额',
  `pay_num_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总用户数（剔除跨游戏）',
  `pay_total_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费总金额（剔除跨游戏）',
  `pay_amount_rg` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '付费分成总金额（剔除跨游戏）',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `select_index` (`tdate`,`thour`,`agent_id`,`site_id`,`game_id`) USING BTREE,
  KEY `tdate` (`tdate`) USING BTREE,
  KEY `thour` (`thour`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `media_id` (`media_id`) USING BTREE,
  KEY `auth_id` (`auth_id`) USING BTREE,
  KEY `site_id` (`site_id`) USING BTREE,
  KEY `game_id` (`game_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    protected string $basic_login_total_game = "CREATE TABLE IF NOT EXISTS `basic_login_total_game_{{YEAR}}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tdate` date NOT NULL COMMENT '登录时间',
  `agent_id` int(11) NOT NULL COMMENT '渠道ID',
  `site_id` int(11) NOT NULL DEFAULT '0' COMMENT '广告位ID',
  `cplaceid` varchar(50) DEFAULT NULL COMMENT '子ID',
  `adid` varchar(20) DEFAULT NULL COMMENT '创意ID',
  `game_id` int(10) unsigned NOT NULL COMMENT '游戏ID',
  `turn` int(10) unsigned NOT NULL COMMENT '轮数ID',
  `login_count` int(11) NOT NULL COMMENT '账号数',
  `active` int(10) unsigned NOT NULL COMMENT '活跃天数(0:当天)',
  `plat_id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '平台ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `tdate` (`tdate`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `site_id` (`site_id`) USING BTREE,
  KEY `game_id` (`game_id`) USING BTREE,
  KEY `plat_id` (`plat_id`) USING BTREE,
  KEY `active` (`active`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;";
    protected string $basic_login_total_server = "CREATE TABLE IF NOT EXISTS `basic_login_total_server_{{YEAR}}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tdate` date NOT NULL COMMENT '登录时间',
  `agent_id` int(11) NOT NULL COMMENT '渠道ID',
  `site_id` int(11) NOT NULL DEFAULT '0' COMMENT '广告位ID',
  `cplaceid` varchar(50) DEFAULT NULL COMMENT '子ID',
  `adid` varchar(20) DEFAULT NULL COMMENT '创意ID',
  `game_id` int(10) unsigned NOT NULL COMMENT '游戏ID',
  `server_id` int(11) NOT NULL DEFAULT '0' COMMENT '服务器ID',
  `turn` int(10) unsigned NOT NULL COMMENT '轮数ID',
  `login_count` int(11) NOT NULL COMMENT '账号数',
  `active` int(10) unsigned NOT NULL COMMENT '活跃天数(0:当天)',
  `plat_id` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '平台ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `tdate` (`tdate`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `site_id` (`site_id`) USING BTREE,
  KEY `game_id` (`game_id`) USING BTREE,
  KEY `server_id` (`server_id`) USING BTREE,
  KEY `plat_id` (`plat_id`) USING BTREE,
  KEY `active` (`active`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;";
    protected string $basic_reg_total = "CREATE TABLE IF NOT EXISTS `basic_reg_total_{{YEAR}}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tdate` date NOT NULL COMMENT '日期',
  `thour` tinyint(4) NOT NULL COMMENT '小时',
  `agent_id` int(11) NOT NULL COMMENT '渠道ID',
  `site_id` int(11) NOT NULL COMMENT '广告位ID',
  `adid` varchar(100) DEFAULT '' COMMENT '创意ID',
  `turn` int(11) DEFAULT '0' COMMENT '轮数',
  `cplaceid` varchar(100) DEFAULT '' COMMENT '子ID',
  `reg_count` int(11) NOT NULL DEFAULT '0' COMMENT '注册数',
  `login_count` int(11) NOT NULL DEFAULT '0' COMMENT '登录数',
  `game_id` int(11) NOT NULL COMMENT '游戏ID',
  `server_id` int(11) NOT NULL DEFAULT '0' COMMENT '服务器ID',
  `plat_id` tinyint(4) NOT NULL DEFAULT '0' COMMENT '平台ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `tdate` (`tdate`) USING BTREE,
  KEY `thour` (`thour`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `site_id` (`site_id`) USING BTREE,
  KEY `game_id` (`game_id`) USING BTREE,
  KEY `server_id` (`server_id`) USING BTREE,
  KEY `adid` (`adid`) USING BTREE,
  KEY `cplaceid` (`cplaceid`) USING BTREE,
  KEY `plat_id` (`plat_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='注册日志基础统计表';";
    protected string $game_active_hour = "CREATE TABLE IF NOT EXISTS `game_active_hour_{{MONTH}}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `media_id` int(11) NOT NULL DEFAULT '0' COMMENT '媒体ID',
  `agent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '渠道ID',
  `site_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告位ID',
  `auth_id` int(10) NOT NULL DEFAULT '0' COMMENT '负责人ID',
  `reg_date` date NOT NULL COMMENT '日期',
  `thour` tinyint(3) unsigned NOT NULL COMMENT '小时',
  `days` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '活跃天数（0当天登陆）',
  `active_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '活跃数',
  `active_reg_game` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '活跃数（注册游戏相同）',
  `pay_total` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `select_index` (`reg_date`,`thour`,`days`,`agent_id`,`site_id`,`game_id`) USING BTREE,
  KEY `reg_date` (`reg_date`) USING BTREE,
  KEY `thour` (`thour`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `site_id` (`site_id`) USING BTREE,
  KEY `media_id` (`media_id`) USING BTREE,
  KEY `auth_id` (`auth_id`) USING BTREE,
  KEY `game_id` (`game_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='注册活跃表(按小时统计)';";
    protected string $server_total_day = "CREATE TABLE IF NOT EXISTS `server_total_day_{{YEAR}}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tdate` date NOT NULL COMMENT '日期',
  `game_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `media_id` int(11) NOT NULL DEFAULT '0' COMMENT '媒体ID',
  `agent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '渠道ID',
  `site_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告位ID',
  `auth_id` int(10) NOT NULL DEFAULT '0' COMMENT '负责人ID',
  `server_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '区服ID',
  `user_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登陆用户数(数据上报)',
  `new_user_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '新登陆用户（进入游戏）',
  `reg_user_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册登陆数(数据上报)',
  `role_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏角色登陆数',
  `role_create_user` int(10) unsigned DEFAULT '0' COMMENT '创建角色用户数',
  `role_create_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏创角数',
  `pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '充值人数',
  `pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '充值金额',
  `reg_pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册付费人数',
  `reg_pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册付费金额',
  `gf_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '衮服登陆用户数',
  `gf_pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '衮服付费数',
  `gf_pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '衮服付费金额',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `select_index` (`tdate`,`agent_id`,`site_id`,`game_id`,`server_id`) USING BTREE,
  KEY `tdate` (`tdate`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `site_id` (`site_id`) USING BTREE,
  KEY `game_id` (`game_id`) USING BTREE,
  KEY `media_id` (`media_id`) USING BTREE,
  KEY `auth_id` (`auth_id`) USING BTREE,
  KEY `server_id` (`server_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='按区服统计总表';";
    protected string $server_total_hour = "CREATE TABLE IF NOT EXISTS `server_total_hour_{{MONTH}}` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tdate` date NOT NULL COMMENT '日期',
  `thour` tinyint(4) NOT NULL COMMENT '小时',
  `game_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
  `media_id` int(11) NOT NULL DEFAULT '0' COMMENT '媒体ID',
  `agent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '渠道ID',
  `site_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告位ID',
  `auth_id` int(10) NOT NULL DEFAULT '0' COMMENT '负责人ID',
  `server_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '区服ID',
  `user_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登陆用户数(数据上报)',
  `new_user_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '新登陆用户（进入游戏）',
  `reg_user_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册登陆数(数据上报)',
  `role_login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏角色登陆数',
  `role_create_user` int(10) unsigned DEFAULT '0' COMMENT '创建角色用户数',
  `role_create_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游戏创角数',
  `pay_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '充值人数',
  `pay_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '充值金额',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `select_index` (`tdate`,`thour`,`agent_id`,`site_id`,`game_id`,`server_id`) USING BTREE,
  KEY `tdate` (`tdate`) USING BTREE,
  KEY `thour` (`thour`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `site_id` (`site_id`) USING BTREE,
  KEY `game_id` (`game_id`) USING BTREE,
  KEY `media_id` (`media_id`) USING BTREE,
  KEY `auth_id` (`auth_id`) USING BTREE,
  KEY `server_id` (`server_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='按区服统计小时总表';";

}