dms-api/app/v1/logic/dataReport/UserLogLogic.php

<?php

// 玩家日志逻

namespace app\v1\logic\dataReport;

use plugin\saiadmin\basic\BaseLogic;
use plugin\saiadmin\service\OpenSpoutWriter;
use support\think\Db;

class UserLogLogic extends BaseLogic
{

    // 注册日志
    public function getRegLogList($where): mixed
    {
        $params = $this->searchByAuth($where);

        $regTime = $params['reg_time'];
        $tableName = 'sdk_reg_log_' . date('Ym', strtotime($regTime));
        $params['reg_time'] = strtotime($regTime . " 00:00:00") . "<=" . strtotime($regTime . " 23:59:59");
        $orderBy = request()->input('orderBy', 'reg_time');
        $orderType = request()->input('orderType', 'desc');
        $page = request()->input('page', 1);
        $limit = request()->input('limit', 10);


        // 获取这一天的注册日志
        $regLogQuery = Db::connect('db_game_log')
            ->table($tableName)
            ->where('reg_time', '>=', strtotime($regTime . ' 00:00:00'))
            ->where('reg_time', '<=', strtotime($regTime . ' 23:59:59'));

        // 添加其他查询条件
        if (!empty($params['game_id'])) {
            $regLogQuery = $regLogQuery->where('game_id', 'in', is_array($params['game_id']) ? implode(',', $params['game_id']) : $params['game_id']);
        }
        if (!empty($params['user_name'])) {
            $regLogQuery = $regLogQuery->where('user_name', $params['user_name']);
        }
        if (!empty($params['media_id'])) {
            $regLogQuery = $regLogQuery->where('media_id', $params['media_id']);
        }
        if (!empty($params['site_id'])) {
            $regLogQuery = $regLogQuery->where('site_id', $params['site_id']);
        }
        
        if (!empty($params['agent_id'])) {
            $regLogQuery = $regLogQuery->where('agent_id', $params['agent_id']);
        }
        if (!empty($params['auth_id'])){
            $regLogQuery = $regLogQuery->where('auth_id', 'in', is_array($params['auth_id']) ? implode(',', $params['auth_id']) : $params['auth_id']);
        }
        if (!empty($params['vt'])) {
            $regLogQuery = $regLogQuery->where('vt', $params['vt']);
        }

        // 自然量ID, auth_id=0为自然量
        $whereRaw = [];
        if(!empty($params['nomal_game_id'])){
            for($i=0;$i<count($params['nomal_game_id']);$i++){
                $whereRaw[] = "(game_id = {$params['nomal_game_id'][$i]} AND auth_id=0)";
            }
        }
        $whereRaw = implode(' OR ', $whereRaw);
        $regLogQuery = $regLogQuery->whereRaw($whereRaw);

        echo $regLogQuery->buildSql();

        // 分页
        $data = $regLogQuery->paginate($limit)->toArray();

        return $data;
    }

    // 登录日志
    public function getLoginLogList($where): mixed
    {
        $orderBy = request()->input('orderBy', 'login_time');
        $orderType = request()->input('orderType', 'desc');
        $page = request()->input('page', 1);
        $limit = request()->input('limit', 10);

        $params = $this->searchByAuth($where);
        $loginTime = $params['login_time'];
        $tableName = 'sdk_login_log_' . date('Ym', strtotime($loginTime));


        $loginLogQuery = Db::connect('db_game_log')
            ->table($tableName)
            ->where('login_time', '>=', strtotime($params['login_time'] . ' 00:00:00'))
            ->where('login_time', '<=', strtotime($params['login_time'] . ' 23:59:59'));

        if (!empty($params['game_id'])) {
            $loginLogQuery = $loginLogQuery->where('game_id', 'in', is_array($params['game_id']) ? implode(',', $params['game_id']) : $params['game_id']);
        }
        if (!empty($params['user_name'])) {
            $loginLogQuery = $loginLogQuery->where('user_name', $params['user_name']);
        }
        if (!empty($params['media_id'])) {
            $loginLogQuery = $loginLogQuery->where('media_id', $params['media_id']);
        }
        if (!empty($params['site_id'])) {
            $loginLogQuery = $loginLogQuery->where('site_id', $params['site_id']);
        }
        if (!empty($params['auth_id'])) {
            $loginLogQuery = $loginLogQuery->where('auth_id', 'in', is_array($params['auth_id']) ? implode(',', $params['auth_id']) : $params['auth_id']);
        }
        if (!empty($params['agent_id'])) {
            $loginLogQuery = $loginLogQuery->where('agent_id', $params['agent_id']);
        }
        if (!empty($params['reg_time'])) {
            $loginLogQuery = $loginLogQuery
                ->where('reg_time', '>=', strtotime($params['reg_time'] . ' 00:00:00'))
                ->where('reg_time', '<=', strtotime($params['reg_time'] . ' 23:59:59'));
        }
        if (!empty($params['login_time'])) {
            $loginLogQuery = $loginLogQuery
                ->where('login_time', '>=', strtotime($params['login_time'] . ' 00:00:00'))
                ->where('login_time', '<=', strtotime($params['login_time'] . ' 23:59:59'));
        }

        // 自然量ID, auth_id=0为自然量
        $whereRaw = [];
        if(!empty($params['nomal_game_id'])){
            for($i=0;$i<count($params['nomal_game_id']);$i++){
                $whereRaw[] = "(game_id = {$params['nomal_game_id'][$i]} AND auth_id=0)";
            }
        }
        $whereRaw = implode(' OR ', $whereRaw);
        $loginLogQuery = $loginLogQuery->whereRaw($whereRaw);

        // 执行查询并排序
        $loginLogQuery->order($orderBy, $orderType);



        $data = $loginLogQuery->paginate($limit)->toArray();
        return $data;
    }

    // 充值明细
    public function exportRechargeDetailList($where): mixed
    {
        $data = $this->getRechargeDetailList($where, 'all');

        $data = $this->trandformListColumn($data, ['game', 'ip', 'agent', 'auth', 'pay_channel']);
        $data = array_map(function ($item) {
            return [
                'order_id' => $item['order_id'],
                'user_name' => $item['user_name'],
                'agent_id' => $item['agent_id'],
                'site_id' => $item['site_id'],
                'game_name' => $item['game_name'],
                'server_id' => $item['server_id'],
                'server_name' => $item['server_name'],
                'pay_channel_name' => $item['pay_channel_name'],
                'money' => $item['money'],
                'pay_date' => $item['pay_date'],
                'reg_date' => $item['reg_date'],
                'first_payment' => $item['first_payment'],
                'agent_name' => $item['agent_name'],
                'auth_name' => $item['auth_name'],
            ];
        }, $data);

        // 是否首次付费
        $filter = [
            'first_payment' => [
                ['value' => 1, 'label' => '是'],
                ['value' => 0, 'label' => '否']
            ]
        ];
        $file_name = '充值明细_' . date('YmdHis') . '.xlsx';
        $header = ['订单号', '用户名', '渠道ID', '广告位ID', '游戏名', '服务器ID', '服务器名', '支付方式', '充值金额', '充值时间', '注册时间', '是否首次付费', '渠道名', '负责人'];

        $writer = new OpenSpoutWriter($file_name);
        $writer->setWidth([15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15]);
        $writer->setHeader($header);
        // $writer->setData($data, null);
        $writer->setData($data, null, $filter);
        $file_path = $writer->returnFile();
        return response()->download($file_path, urlencode($file_name));
    }

    // 充值明细导出

    public function getRechargeDetailList($where, $type = "list"): mixed
    {
        $orderBy = request()->input('orderBy', 'pay_date');
        $orderType = request()->input('orderType', 'desc');
        $page = request()->input('page', 1);
        $limit = request()->input('limit', 10);

        $params = $this->searchByAuth($where);

        $tableName = 'sdk_order_success';

        $rechargeDetailQuery = Db::connect('db_game_log')
            ->table($tableName)
            ->where('pay_date', '>=', $params['pay_date'][0] . ' 00:00:00')
            ->where('pay_date', '<=', $params['pay_date'][1] . ' 23:59:59');


        if (!empty($params['game_id'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('game_id', 'in', is_array($params['game_id']) ? implode(',', $params['game_id']) : $params['game_id']);
        }
        if (!empty($params['user_name'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('user_name', $params['user_name']);
        }
        if (!empty($params['media_id'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('media_id', $params['media_id']);
        }
        if (!empty($params['auth_id'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('auth_id', 'in', is_array($params['auth_id']) ? implode(',', $params['auth_id']) : $params['auth_id']);
        }
        if (!empty($params['agent_id'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('agent_id', $params['agent_id']);
        }
        if (!empty($params['site_id'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('site_id', $params['site_id']);
        }
        if (!empty($params['server_id'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('server_id', $params['server_id']);
        }
        if (!empty($params['server_name'])) {
            $rechargeDetailQuery = $rechargeDetailQuery->where('server_name', $params['server_name']);
        }
        if (!empty($params['reg_date'])) {
            $rechargeDetailQuery = $rechargeDetailQuery
                ->where('reg_date', '>=', $params['reg_date'][0] . ' 00:00:00')
                ->where('reg_date', '<=', $params['reg_date'][1] . ' 23:59:59');
        }
        // 自然量ID, auth_id=0为自然量
        $whereRaw = [];
        if(!empty($params['nomal_game_id'])){
            for($i=0;$i<count($params['nomal_game_id']);$i++){
                $whereRaw[] = "(game_id = {$params['nomal_game_id'][$i]} AND auth_id=0)";
            }
        }
        $whereRaw = implode(' OR ', $whereRaw);
        $rechargeDetailQuery = $rechargeDetailQuery->whereRaw($whereRaw);

        $rechargeDetailQuery->order($orderBy, $orderType);

        if ($type == 'all') {
            $data = $rechargeDetailQuery->select()->toArray();
        } else {
            $data = $rechargeDetailQuery->paginate($limit)->toArray();
        }

        return $data;
    }

    // 充值排行
    public function getRechargeRankList($where): mixed
    {
        $orderBy = request()->input('orderBy', 'searchTotalMoney');
        $orderType = request()->input('orderType', 'desc');
        $page = request()->input('page', 1);
        $limit = request()->input('limit', 10);

        $params = $this->searchByAuth($where);

        $tableName = 'sdk_order_success';

        $rechargeRankQuery = Db::connect('db_game_log')
            ->table($tableName)
            ->where('pay_date', '>=', $params['pay_date'][0] . ' 00:00:00')
            ->where('pay_date', '<=', $params['pay_date'][1] . ' 23:59:59');

        if (!empty($params['game_id'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('game_id', 'in', is_array($params['game_id']) ? implode(',', $params['game_id']) : $params['game_id']);
        }
        if (!empty($params['user_name'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('user_name', $params['user_name']);
        }
        if (!empty($params['media_id'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('media_id', $params['media_id']);
        }
        if (!empty($params['auth_id'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('auth_id', 'in', is_array($params['auth_id']) ? implode(',', $params['auth_id']) : $params['auth_id']);
        }
        if (!empty($params['agent_id'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('agent_id', $params['agent_id']);
        }
        if (!empty($params['site_id'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('site_id', $params['site_id']);
        }
        if (!empty($params['server_id'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('server_id', $params['server_id']);
        }
        if (!empty($params['server_name'])) {
            $rechargeRankQuery = $rechargeRankQuery->where('server_name', $params['server_name']);
        }
        if (!empty($params['reg_date'])) {
            $rechargeRankQuery = $rechargeRankQuery
                ->where('reg_date', '>=', $params['reg_date'][0] . ' 00:00:00')
                ->where('reg_date', '<=', $params['reg_date'][1] . ' 23:59:59');
        }
        // 自然量ID, auth_id=0为自然量
        $whereRaw = [];
        if(!empty($params['nomal_game_id'])){
            for($i=0;$i<count($params['nomal_game_id']);$i++){
                $whereRaw[] = "(game_id = {$params['nomal_game_id'][$i]} AND auth_id=0)";
            }
        }
        $whereRaw = implode(' OR ', $whereRaw);
        $rechargeRankQuery = $rechargeRankQuery->whereRaw($whereRaw);

        // 先选择字段，包括计算字段
        $rechargeRankQuery->field('user_name,sum(money) as searchTotalMoney,
            order_id,
            game_id,
            media_id,
            agent_id,
            site_id,
            uid,
            pay_channel_id,
            auth_id,
            server_id,
            server_name,
            pay_date,
            reg_date,
            role_name,
            role_id');

        $rechargeRankQuery->group('uid, game_id,site_id,server_id,media_id,auth_id,agent_id,server_id,auth_id,role_id');

        $rechargeRankQuery->order($orderBy, $orderType);


        $data = $rechargeRankQuery->paginate($limit)->toArray();


        $data['data'] = $this->trandformListColumn($data['data'], ['game', 'auth', 'agent', 'pay_channel']);


        // // 告警提示，查询最近登录时间，查询最近充值时间，和现在时间对比，如果超过3天，则告警，并提示

        foreach ($data['data'] as &$item) {
            // 最后登录时间
            $item['login_time'] = Db::connect('db_origin')->table('user_' . $item['uid'] % 10)->where('uid', $item['uid'])->value('login_time');
            // 最近注册时间
            $item['pay_time'] = Db::connect('db_origin')->table('user_' . $item['uid'] % 10)->where('uid', $item['uid'])->value('pay_time');
            // 现在时间
            $item['now_time'] = time();

            // 最近登录时间与现在时间对比，如果超过3天，则告警，并提示
            if ($item['login_time'] < $item['now_time'] - 3 * 24 * 60 * 60) {
                $item['login_alert'] = 1;
            } else {
                $item['login_alert'] = 0;
            }
            // 最近充值时间与现在时间对比，如果超过3天，则告警，并提示
            if ($item['pay_time'] < $item['now_time'] - 3 * 24 * 60 * 60) {
                $item['pay_alert'] = 1;
            } else {
                $item['pay_alert'] = 0;
            }
        }


        $totalWhere = [];
        if (!empty($params['game_id'])) {
            $totalWhere[] = ['game_id', 'in', is_array($params['game_id']) ? implode(',', $params['game_id']) : $params['game_id']];
        }
        if (!empty($where['auth_id'])) {
            $totalWhere[] = ['auth_id', 'in', is_array($params['auth_id']) ? implode(',', $params['auth_id']) : $params['auth_id']];
        }
        if (!empty($where['agent_id'])) {
            $totalWhere['agent_id'] = $params['agent_id'];
        }
        if (!empty($where['site_id'])) {
            $totalWhere['site_id'] = $params['site_id'];
        }
        if (!empty($where['server_id'])) {
            $totalWhere['server_id'] = $params['server_id'];
        }
        if (!empty($where['user_name'])) {
            $totalWhere['user_name'] = $params['user_name'];
        }
        if (!empty($where['server_name'])) {
            $totalWhere['server_name'] = $params['server_name'];
        }
        if (!empty($where['reg_date'])) {
            $totalWhere['reg_date'] = $params['reg_date'];
        }
        // 自然量ID, auth_id=0为自然量
        $whereRaw = [];
        if(!empty($params['nomal_game_id'])){
            for($i=0;$i<count($params['nomal_game_id']);$i++){
                $whereRaw[] = "(game_id = {$params['nomal_game_id'][$i]} AND auth_id=0)";
            }
        }
        $whereRaw = implode(' OR ', $whereRaw);
     


        $totalList = Db::connect('db_game_log')->table('sdk_order_success')
            ->where($totalWhere)
            ->whereRaw($whereRaw)
            ->field('game_id,uid,sum(money) as totalMoney')
            ->group('uid,game_id,site_id,server_id,media_id,auth_id,agent_id,server_id,auth_id,role_id')
            ->select()->toArray();


        // 查询累计充值金额（对每一行数据单独查询历史充值金额）
        $totalMoneyMap = [];
        if (!empty($data['data']) && !empty($totalList)) {
            foreach ($totalList as $totalItem) {
                $key = $totalItem['uid'] . '_' . $totalItem['game_id'];
                $totalMoneyMap[$key] = $totalItem['totalMoney'] ?? 0;
            }
        }

        // 为搜索结果添加累计充值金额
        foreach ($data['data'] as &$item) {
            $key = $item['uid'] . '_' . $item['game_id'];
            $item['totalMoney'] = $totalMoneyMap[$key] ?? 0;
        }

        return $data;
    }

    // 角色数据

    public function getRoleDataList($where): mixed
    {
        $orderBy = request()->input('orderBy', 'reg_time');
        $orderType = request()->input('orderType', 'desc');
        $page = request()->input('page', 1);
        $limit = request()->input('limit', 10);

        $params = $this->searchByAuth($where);
        $regTime = $params['reg_time'];


        $egTableName = 'sdk_reg_log_' . date('Ym', strtotime($regTime));


        $userData = Db::connect('db_game_log')->table($egTableName)->where('reg_time', '>=', strtotime($regTime . ' 00:00:00'))->where('reg_time', '<=', strtotime($regTime . ' 23:59:59'))->select()->toArray();

        $where_sql = $this->generateWhereSql($params);
        


        $uids = array_column($userData, 'uid');

        if (empty($userData)) {
            return [];
        }

        $where_sql = " AND uid in (" . implode(',', $uids) . ") {$where_sql}";


      
        $sql_parts = [];
        $sql_parts[] = "SELECT * FROM role_data_and WHERE 1=1 {$where_sql}";
        $sql_parts[] = "SELECT * FROM role_data_ios WHERE 1=1 {$where_sql}";


        $unionSql = implode(" UNION ALL ", $sql_parts);

        // 分页

        $offset = ($page - 1) * $limit;
        $unionSql .= " LIMIT {$offset}, {$limit}";
        $roleData = Db::connect('db_game_log')->query($unionSql);

        // 合并相同uid的数据，将角色信息放到数组中
        $mergedData = [];
        foreach ($roleData as $item) {
            $uid = $item['uid'];

            if (!isset($mergedData[$uid])) {
                // 创建用户基础信息，排除角色相关字段
                $baseInfo = array_diff_key($item, array_flip(['role_id', 'role_name', 'role_level']));
                $mergedData[$uid] = $baseInfo + ['roles' => []];
            }

            // 添加角色信息到数组
            $mergedData[$uid]['roles'][] = [
                'server_name' => $item['server_name'],
                'role_name' => $item['role_name'],
                'role_level' => $item['role_level'],
            ];
        }

        // 转换回数组格式
        $roleData = array_values($mergedData);
        $roleData = $this->trandformListColumn($roleData, ['game', 'ip', 'auth', 'agent']);
        $count = count($roleData);

        return [
            'data' => $roleData,
            'current_page' => $page,
            'per_page' => $limit,
            'last_page' => ceil($count / $limit),
            'has_more' => $page < ceil($count / $limit),
            'total' => $count
        ];

    }

}