game-sdk/simplewind/huomp/logic/data/HourArchiveLogic.php

<?php
/**
 * HourArchiveLogic.php UTF-8
 *  每小时数据逻辑 #13831
 *
 * @date    : 2020/12/16 14:00
 *
 * @license 这不是一个自由软件，未经授权不许任何使用和传播。
 * @author  : chenbingling <cbl@huosdk.com>
 * @version : HUOSDK-Mp
 */

namespace huomp\logic\data;

use huo\model\common\CommonModel;
use huo\model\log\MemLoginLogModel;
use huo\model\member\MemberModel;
use huo\model\member\MemGameModel;
use huo\model\order\OrderModel;
use huolib\constant\CommonConst;
use huolib\constant\OrderConst;
use huolib\tool\Time;
use huomp\model\hour\DayHourLogModel;
use think\Exception;
use think\Log;

class HourArchiveLogic extends CommonModel {
    protected $date           = '';
    protected $hour           = 0;
    protected $dw_conf        = '';
    protected $mem_model      = '';
    protected $mem_game_model = '';

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

    /**
     * 计算每个小时的数据
     *
     * @param string $date 日期 2020-01-01
     * @param int    $hour 小时 0~23
     *
     * @return bool
     */
    public function archiveHour($date, $hour) {
        $_date = $date;
        $_hour = $hour;
        if (empty($_date)) {
            $_date = date('Y-m-d');
        }
        /* 数据库处理 */
        try {
            $this->date = $_date;
            $this->hour = $_hour;
            $_config = $this->dw_conf;
            $_ts = time();
            $_log_date = date('Y-m-d H:i:s', $_ts);
            Log::write("ETL archiveHour at $_log_date and date = $_date and hour= $_hour", 'etl', true);
            $this->mem_model = new MemberModel();
            $this->mem_game_model = new MemGameModel();
            $_start_time = strtotime($_date) + $_hour * CommonConst::CONST_HOUR_SECONDS;  /* 开始时间 */
            $_end_time = $_start_time + CommonConst::CONST_HOUR_SECONDS;  /* 结束时间 */
            {
                /* 从登陆记录获取每日玩家登陆信息 */
                $_dh_data = $this->getLoginLog($_start_time, $_end_time);
                /* 从订单表获取充值数据 */
                $_dh_data = $this->calculateOrderData($_dh_data, $_start_time, $_end_time);
            }
            /* 写入设备每时表 */
            $_dh_model = new DayHourLogModel();
            $_dh_model->checkTable($_start_time); /* 检查表 */
            if (!empty($_dh_data)) {
                $_dh_model->computeTable($this->date)->insertAll($_dh_data, true); /* 插入所有数据 */
            }
            /* 存储过程计算每小时数据 */
            db('', $_config, true)->query("call runhourdata('$_date',$_hour)");
        } catch (Exception $_e) {
            Log::write($_e->getTraceAsString(), 'etl', true);
            Log::write("Error:code:".$_e->getCode().";msg:".$_e->getMessage(), 'etl', true);
        }

        return true;
    }

    /**
     * 计算充值订单数据
     *
     * @param array $data       原始数据
     * @param int   $start_time 开始时间
     * @param int   $end_time   结束时间
     *
     * @return array
     */
    public function calculateOrderData($data, $start_time, $end_time) {
        if (empty($data)) {
            return $data;
        }
        $_model = new OrderModel();
        $_map = [
            'create_time' => ['between', [$start_time, $end_time]],
            'status'      => OrderConst::PAY_STATUS_SUC,
        ];
        $_field = [
            'mem_id'                       => 'mem_id',
            'agent_id'                     => 'agent_id',
            'mg_mem_id'                    => 'mg_mem_id',
            'app_id'                       => 'app_id',
            "IFNULL(SUM(`amount`),0)"      => 'sum_money',
            "IFNULL(SUM(`real_amount`),0)" => 'sum_real_money',
            "COUNT(`id`)"                  => 'order_cnt',
        ];
        $_group = 'mem_id,app_id';
        $_order_list = $_model->where($_map)
                              ->field($_field)
                              ->group($_group)
                              ->select();
        if (is_object($_order_list)) {
            $_order_list = $_order_list->toArray();
        }
        foreach ($_order_list as $_k => $_v) {
            $_key = $_v['mem_id'].'_'.$_v['app_id'];
            if (!isset($data[$_key])) {
                $data[$_key] = $this->getDefaultData($_v['mem_id'], $_v['mg_mem_id']);
            }
            $data[$_key]['sum_money'] = $_v['sum_money'];
            $data[$_key]['sum_real_money'] = $_v['sum_real_money'];
            $data[$_key]['order_cnt'] = $_v['order_cnt'];
            unset($data[$_key]['mg_mem_id']);
        }

        return $data;
    }

    /**
     * 获取设备打开记录log
     *
     * @param int $start_time 开始时间
     * @param int $end_time   结束时间
     *
     * @return array
     */
    public function getLoginLog($start_time, $end_time) {
        $_model = new MemLoginLogModel();
        $_map = [
            'create_time' => ['between', [$start_time, $end_time]]
        ];
        $_field = [
            'date'                              => 'date',
            "FROM_UNIXTIME( create_time, '%H')" => 'hour_key',
            'mem_id'                            => 'mem_id',
            'agent_id'                          => 'agent_id',
            'app_id'                            => 'app_id',
            'mg_mem_id'                         => 'mg_mem_id',
            "COUNT(`id`)"                       => 'login_cnt',
        ];
        $_group = 'mem_id,app_id';
        $_list = $_model->computeTable(date('Y-m-d', $start_time))
                        ->where($_map)
                        ->field($_field)
                        ->group($_group)
                        ->select();
        if (is_object($_list)) {
            $_list = $_list->toArray();
        }
        if (empty($_list)) {
            return [];
        }
        $_rdata = [];
        foreach ($_list as $_k => $_v) {
            $_default_data = $this->getDefaultData($_v['mem_id'], $_v['mg_mem_id']);
            $_data = array_merge($_default_data, $_v);
            $_key = $_v['mem_id'].'_'.$_v['app_id'];
            $_rdata[$_key] = $_data;
            unset($_rdata[$_key]['mg_mem_id']);
        }

        return $_rdata;
    }

    /**
     * 获取初始数据
     *
     * @param $mem_id
     * @param $mg_mem_id
     *
     * @return array
     */
    protected function getDefaultData($mem_id, $mg_mem_id) {
        if (empty($this->date)) {
            $_time = time();
        } else {
            $_time = strtotime($this->date);
        }
        $_data = [
            'date'              => $this->date,
            'hour_key'          => $this->hour,
            'mem_id'            => $mem_id,
            'agent_id'          => 0,
            'app_id'            => 0,
            'reg_time'          => 0,
            'reg_days'          => 0,
            'reg_hour_key'      => 0,
            'game_reg_time'     => 0,
            'game_reg_days'     => 0,
            'game_reg_hour_key' => 0,
            'login_cnt'         => 0,
            'sum_money'         => 0,
            'sum_real_money'    => 0,
            'order_cnt'         => 0,
        ];
        $_mem_reg_time = $this->mem_model->getCreateTimeById($mem_id);
        $_data['reg_time'] = $_mem_reg_time;
        $_data['reg_days'] = Time::timeDateDiff($_mem_reg_time, $_time);
        $_data['reg_hour_key'] = date('H', $_mem_reg_time);
        if (!empty($mg_mem_id)) {
            $_mg_info = $this->mem_game_model->getInfoById($mg_mem_id);
            $_data['app_id'] = get_val($_mg_info, 'app_id', 0);
            $_data['game_reg_time'] = get_val($_mg_info, 'create_time', 0);
            $_data['game_reg_days'] = Time::timeDateDiff($_data['game_reg_time'], $_time);
            $_data['game_reg_hour_key'] = date('H', $_data['game_reg_time']);
        }

        return $_data;
    }
}