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', '>=', $params['pay_date'][0] . ' 00:00:00')
      ->where('pay_date', '<=', $params['pay_date'][1] . ' 23:59:59');
    }

    if(!empty($params['pay_channel_id'])){
      $channelIncomeQuery->where('pay_channel_id', 'in', is_array($params['pay_channel_id']) ? implode(',', $params['pay_channel_id']) : $params['pay_channel_id']);
    }

    if(!empty($params['game_id'])){
      $channelIncomeQuery->where('game_id', 'in', is_array($params['game_id']) ? implode(',', $params['game_id']) : $params['game_id']);
    }


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

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


    $channelIncomeQuery->group('pay_channel_id');




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

    $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' => 180,
        ];
    }
    

    $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,
    ];
  }
}