dms-api/app/v1/logic/customer/ReconciliationLogic.php

<?php

namespace app\v1\logic\customer;

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

class ReconciliationLogic extends BaseLogic
{
    // 充值渠道收入
    public function getChannelIncome($where)
    {

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

        $channelIncomeQuery = Db::connect('db_game_log')->table('sdk_order_success');

        if (!empty($params['pay_date'])) {
            $channelIncomeQuery = $channelIncomeQuery
                ->where('pay_date', 'between', [$params['pay_date'][0] . ' 00:00:00', $params['pay_date'][1] . ' 23:59:59']);
        }

        if (!empty($params['pay_channel_id'])) {
            $channelIncomeQuery->where('pay_channel_id', $params['pay_channel_id']);
        }

        if (!empty($params['game_id'])) {
            $channelIncomeQuery->where('game_id', $params['game_id']);
        }

        $channelIncomeQuery->field('game_id,pay_channel_id,sum(money) as money');


        $channelIncomeQuery->group('pay_channel_id');

        $columns = [
            [
                'title' => '游戏ID',
                'dataIndex' => 'game_id',
                'width' => 100,
            ],
            [
                'title' => '游戏名称',
                'dataIndex' => 'game_name',
                'width' => 150,
            ],
            [
                'title' => '合计',
                'dataIndex' => 'money',
                'width' => 100,
            ],
        ];

        $payChannelList = Db::connect('db_center')->table('pay_channel')
            ->where('status', 1);

        if (!empty($params['pay_channel_id'])) {
            $payChannelList = $payChannelList->where('id', 'in', $params['pay_channel_id']);
        }
        $payChannelList = $payChannelList->select()->toArray();

        foreach ($payChannelList as $payChannel) {
            $title = $payChannel['name'];
            $payChannelId = $payChannel['id'];
            $columns[] = [
                'title' => $title,
                'dataIndex' => 'pay_channel_id_' . $payChannelId,
                'width' => 120,
            ];
        }


        $data = $channelIncomeQuery->select()->toArray();


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

        foreach ($data as $key => $value) {

            $data[$key]['pay_channel_id_' . $value['pay_channel_id']] = $value['money'];
        }

        // 按game_id分组合并数据
        $mergedData = [];
        foreach ($data as $item) {
            $gameId = $item['game_id'];

            if (!isset($mergedData[$gameId])) {
                $mergedData[$gameId] = [
                    'game_id' => $gameId,
                    'game_name' => $item['game_name'],
                    'money' => 0
                ];

                // 初始化所有pay_channel_id金额为0
                foreach ($payChannelList as $channel) {
                    $mergedData[$gameId]['pay_channel_id_' . $channel['id']] = 0;
                }
            }

            // 累加合计金额
            $mergedData[$gameId]['money'] += floatval($item['money']);

            // 设置对应渠道金额
            $channelKey = 'pay_channel_id_' . $item['pay_channel_id'];
            $mergedData[$gameId][$channelKey] = $item[$channelKey];
        }
        // 添加合计行
        $totalRow = [
            'game_id' => '合计',
            'game_name' => '',
            'money' => 0
        ];

        // 初始化所有渠道金额为0
        foreach ($payChannelList as $channel) {
            $totalRow['pay_channel_id_' . $channel['id']] = 0;
        }

        // 计算合计数据
        foreach ($mergedData as $row) {
            $totalRow['money'] += floatval($row['money']);

            foreach ($payChannelList as $channel) {
                $channelKey = 'pay_channel_id_' . $channel['id'];
                $totalRow[$channelKey] += floatval($row[$channelKey]);
            }
        }

        // 将合计行插入到数组最前面
        array_unshift($mergedData, $totalRow);
        // 转为数组格式
        $data = array_values($mergedData);
        return [
            'columns' => $columns,
            'data' => $data,
        ];
    }
}