game-sdk/simplewind/huo/logic/data/GenDataSwitchLogic.php

<?php
/**
 * GenDataSwitchLogic.php UTF-8
 *
 *
 * @date    : 2019/11/21 10:40
 *
 * @license 这不是一个自由软件，未经授权不许任何使用和传播。
 * @author  : ouzhongfu <ozf@huosdk.com>
 * @version : HUOSDK_NOVEL 1.0
 */

namespace huo\logic\data;

use huo\model\agent\AgentCpaLogSwitchModel;
use huo\model\common\CommonModel;
use huo\model\log\DayMemLogSwitchModel;
use huo\model\member\MemberModel;
use huo\model\member\MemGameModel;
use huo\model\member\MemoauthModel;
use huo\model\order\OrderModel;
use huolib\constant\CommonConst;
use huolib\constant\OrderConst;
use think\Exception;
use think\Log;

class GenDataSwitchLogic extends CommonModel {
    private $log_db_conf = [];
    private $db_conf     = [];
    private $dw_conf     = [];

    public function __construct($name = null) {
        parent::__construct($name);
        if (file_exists(GLOBAL_CONF_PATH.'database_log.php')) {
            $this->log_db_conf = include GLOBAL_CONF_PATH.'database_log.php';
        }
        if (file_exists(GLOBAL_CONF_PATH.'database.php')) {
            $this->db_conf = include GLOBAL_CONF_PATH.'database.php';
        }
        if (file_exists(GLOBAL_CONF_PATH.'database_dw.php')) {
            $this->dw_conf = include GLOBAL_CONF_PATH.'database_dw.php';
        }
    }

    /**
     * 执行每日数据
     *
     * @param string $date 日期
     *
     * @return void
     */
    function archiveDaily($date = '') {
        set_time_limit(0);
        $_ts = time();
        if (empty($date)) {
            $_date = date('Y-m-d', strtotime("-1 day"));
        } else {
            $_date = $date;
        }
        $_log_date = date('Y-m-d H:i:s', $_ts);
        $_config = $this->getDbConfig();
        /* 数据库处理 */
        try {
            $_start_time = time();
            Log::write("ETL archiveDailySwitch at $_log_date and date = $_date", 'etl', true);
            $this->genLogMemByLoginData($_date);
            $this->genLogMem($_date);
            $this->genLogMemGame($_date);
            $this->genLogMemCpa($_date);
            db('', $_config, true)->query("call rundailyswitch('$_date')");
            $_end_time = time();
            $_diff = $_end_time - $_start_time;
            Log::write("ETL archiveDailySwitch end use time $_diff s", 'etl', true);
        } catch (Exception $_e) {
            Log::write(
                "Error:code:".$_e->getCode().";msg:".$_e->getMessage().'；qqq.'.$_e->getTraceAsString(), 'etl', true
            );
        }

        return;
    }

    /**
     * @param $date
     */
    public function genLogMemByLoginData($date) {
        $_date = $date;
        $_start_time = strtotime($_date);
        $_end_time = strtotime($_date);
        $_log_database = $this->log_db_conf['database'];
        $_database = $this->db_conf['database'];
        $_day_mem_switch_model = new DayMemLogSwitchModel();
        while ($_start_time <= $_end_time) {
            $_day_mem_switch_model->checkTable($_start_time);
            $_day_end_time = $_start_time + 86400;
            $_date_key = date('Ym', $_start_time);
            $_date_month_key = date('Ym', $_start_time);
            $_date = date('Y-m-d', $_start_time);
            $_delete_sql = "DELETE FROM ".$_log_database.".log_log_day_mem_switch_".$_date_key." WHERE date='".$_date
                           ."' ";
            db()->execute($_delete_sql);
            $_sql = "REPLACE INTO ".$_log_database.".log_log_day_mem_switch_".$_date_key."
            (
                `date`,
                `mem_id`,
                `app_id`,
                `agent_id`,
                `reg_time`,
                `reg_days`,
                `device_id`,
                `login_cnt`,
                `last_login_ip`,
                `reg_app_id`
            )
            SELECT
                '".$_date."',
                `ll`.`mem_id` AS `mem_id`,
                `ll`.`app_id` AS `app_id`,
                IFNULL(`m`.`agent_id` , '0') AS `agent_id`,
                IFNULL(`m`.`create_time` , '0') AS `reg_time`,
                (TO_DAYS(FROM_UNIXTIME(`ll`.`create_time`)) - TO_DAYS(FROM_UNIXTIME(IFNULL(`m`.`create_time`,0)))) AS `reg_days`,
                `ll`.`device_id` AS `device_id`,
                COUNT(`ll`.`id`) AS `login_cnt`,
                inet_aton(`ll`.`ip`) AS `last_login_ip`,
                `ll`.`reg_app_id` AS `reg_app_id`
            FROM
                (".$_database.".`h_log_mem_login_".$_date_month_key."` `ll`)
                LEFT JOIN  ".$_database.".`h_member` m ON ll.mem_id = m.id
            WHERE
                (FROM_UNIXTIME(`ll`.`create_time`, '%Y-%m-%d') = '".$_date."')
                AND m.is_switch = 1
            GROUP BY `ll`.`mem_id` , `ll`.`app_id`;";
            db()->execute($_sql);
            $_start_time = $_day_end_time;
        }
    }

    public function genLogMem($date) {
        $_date = $date;
        $_start_time = strtotime($_date);
        $_end_time = strtotime($_date);
        $_order_model = new OrderModel();
        $_day_mem_switch_model = new DayMemLogSwitchModel();
        $_field = [
            'count(`id`)'        => 'order_suc_cnt',
            'app_id'             => 'app_id',
            'agent_id'           => 'agent_id',
            'mem_id'             => 'mem_id',
            'sum(`amount`)'      => 'sum_money',
            'sum(`real_amount`)' => 'sum_real_money',
            'create_time'        => 'last_pay_time',
            'amount'             => 'last_money',
        ];
        while ($_start_time <= $_end_time) {
            $_day_end_time = $_start_time + 86400;
            $_map = [
                'status'      => 2,
                'create_time' => ['between', [$_start_time, $_day_end_time]],
                'is_switch'   => OrderConst::PAY_SWITCH_YES,
            ];
            $_group = 'mem_id,app_id';
            $_datas = $_order_model->where($_map)->field($_field)->group($_group)->select();
            if (is_object($_datas)) {
                $_datas = $_datas->toArray();
            }
            foreach ($_datas as $_data) {
                $_data['date'] = date('Y-m-d', $_start_time);
                $_data['order_cnt'] = $_data['order_suc_cnt'];
                unset($_data['id']);
                $_rs = $_day_mem_switch_model->updateData($_data, 0);
                if (false === $_rs) {
                    var_dump($_data);
                    exit;
                }
            }
            $_start_time = $_day_end_time;
        }
    }

    public function genLogMemCpa($date) {
        $_date = $date;
        $_start_time = strtotime($_date);
        $_end_time = strtotime($_date);
        $_mem_model = new MemberModel();
        $_day_mem_switch_model = new DayMemLogSwitchModel();
        $_acl_switch_model = new AgentCpaLogSwitchModel();
        $_ma_model = new MemoauthModel();
        $_field = [
            'id'       => 'mem_id',
            'app_id'   => 'app_id',
            'agent_id' => 'agent_id'
        ];
        while ($_start_time <= $_end_time) {
            $_day_end_time = $_start_time + 86400;
            $_map = [
                'create_time' => ['between', [$_start_time, $_day_end_time]],
                'is_switch'   => OrderConst::PAY_SWITCH_YES,
            ];
            $_datas = $_mem_model->where($_map)->field($_field)->select();
            if (is_object($_datas)) {
                $_datas = $_datas->toArray();
            }
            foreach ($_datas as $_data) {
                $_data['date'] = date('Y-m-d', $_start_time);
                $_id = $_acl_switch_model->getIdByMemId($_data['mem_id']);
                if (!empty($_id)) {
                    $_data['is_cpa'] = 2;
                }
                $_openid = $_ma_model->getOpenidByMemId('', $_data['mem_id']);
                if (!empty($_openid)) {
                    $_data['is_auth'] = 2;
                }
                $_rs = $_day_mem_switch_model->updateData($_data, 0);
                if (false === $_rs) {
                    var_dump($_data);
                    exit;
                }
            }
            $_start_time = $_day_end_time;
        }
    }

    /**
     * 查询玩家游戏是否新创
     *
     * @param $date
     *
     * @return bool
     */
    public function genLogMemGame($date) {
        $_date = $date;
        $_start_time = strtotime($_date);
        $_end_time = $_start_time + CommonConst::CONST_DAY_SECONDS;
        $_map = [
            'mem_game_model.create_time' => ['between', [$_start_time, $_end_time]]
        ];
        $_field = [
            'mem_game_model.id'     => 'id',
            'mem_game_model.mem_id' => 'mem_id',
            'mem_game_model.app_id' => 'app_id',
        ];
        $_mg_model = new MemGameModel();
        $_datas = $_mg_model->with(
            ['leftmem' => function ($query) {
                $query->where(['is_switch' => OrderConst::PAY_SWITCH_YES]);
            }]
        )->where($_map)->column($_field);
        $_day_mem_switch_model = new DayMemLogSwitchModel();
        foreach ($_datas as $_mem) {
            $_data = [];
            $_data['date'] = $_date;
            $_data['app_id'] = $_mem['app_id'];
            $_data['mem_id'] = $_mem['mem_id'];
            $_data['is_new_app'] = CommonConst::STATUS_YES;
            $_rs = $_day_mem_switch_model->updateData($_data, 0);
            if (false === $_rs) {
                var_dump($_data);
                exit;
            }
        }

        return true;
    }

    /**
     * 获取数据仓库配置
     */
    public function getDbConfig() {
        $_db_dw = [    // 数据库类型
                       'type'            => '',
                       // 服务器地址
                       'hostname'        => '',
                       // 数据库名
                       'database'        => '',
                       // 用户名
                       'username'        => '',
                       // 密码
                       'password'        => '',
                       // 端口
                       'hostport'        => '',
                       // 数据库编码默认采用utf8
                       'charset'         => 'utf8mb4',
                       // 数据库表前缀
                       'prefix'          => 'dw_',
                       // 连接dsn
                       'dsn'             => '',
                       // 数据库连接参数
                       'params'          => [],
                       // 数据库调试模式
                       'debug'           => true,
                       // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
                       'deploy'          => 0,
                       // 数据库读写是否分离 主从式有效
                       'rw_separate'     => false,
                       // 读写分离后 主服务器数量
                       'master_num'      => 1,
                       // 指定从服务器序号
                       'slave_no'        => '',
                       // 是否严格检查字段是否存在
                       'fields_strict'   => true,
                       // 数据集返回类型
                       'resultset_type'  => 'collection',
                       // 自动写入时间戳字段
                       'auto_timestamp'  => false,
                       // 时间字段取出后的默认时间格式
                       'datetime_format' => false,
                       // 是否需要进行SQL性能分析
                       'sql_explain'     => false,];
        if (file_exists(CMF_ROOT.'data/conf/database_dw.php')) {
            $_db_dw = include CMF_ROOT.'data/conf/database_dw.php';
        }

        return $_db_dw;
    }
}