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);

        $where = $this->getCommonWhereRaw($params);
        if (!empty($params['pay_channel_id'])) {
            $where .= " AND pay_channel_id IN (" . implode(',', $params['pay_channel_id']) . ")";
        }
        $channelIncomeQuery = Db::connect('db_game_log')->table('sdk_order_success');

        $channelIncomeQuery->whereRaw($where);

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

        $channelIncomeQuery->group('pay_channel_id');

        $columns = [
            [
                'title' => '游戏ID',
                'dataIndex' => 'game_id',
                'width' => 80,
                'align' => 'center',
            ],
            [
                'title' => '游戏名称',
                'dataIndex' => 'game_name',
                'width' => 150,
                'align' => 'center',
            ],
            [
                'title' => '负责人',
                'dataIndex' => 'auth_name',
                'width' => 100,
                'align' => 'center',
            ],
            [
                'title' => '合计',
                'dataIndex' => 'money',
                'width' => 100,
                'align' => 'center',
            ],
        ];

        $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' => 140,
                'align' => 'center',
                
            ];
        }

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

        

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

        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'],
                    'auth_id' => $item['auth_id'],
                    'auth_name' => $item['auth_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]);
            }
        }

        return [
            'columns' => $columns,
            'data' => array_values($mergedData),
            'totalRow' => $totalRow
        ];
    }

    /**
     * 广告账号对账
     */
    public function getAdCost($params)
    {
      


        $bmIds = $params['bm_id'];
        
        $advertiserId = $params['advertiser_id'];
        $advertiserName = $params['advertiser_name'];
        $date = $params['date'];
       



        $advertiserIds = [];
        if(!empty($bmIds)){
            $advertiserIds = Db::connect('db_advert')
                ->table('ad_advertiser_list')
                ->where('bmid', 'in', implode(',', $bmIds))
                ->where('status', 1)
                ->select()->toArray();
            $advertiserIds = array_column($advertiserIds, 'advertiser_id');
            $advertiserId = implode(',', $advertiserIds);
        }


        echo  Db::connect('db_advert')
            ->table('media_cost')
            ->field('media_cost.advertiser_id as advertiser_id, SUM(money) as money, SUM(ori_money) as ori_money, ad_advertiser_list.advertiser_name as advertiser_name')
            ->where('media_cost.advertiser_id', 'in', $advertiserId)
            ->whereTime('tdate', 'between', $date)
            ->leftJoin('ad_advertiser_list', 'media_cost.advertiser_id = ad_advertiser_list.advertiser_id')
             ->group('advertiser_name, advertiser_id')->buildSql();


            $query = Db::connect('db_advert')
                ->table('media_cost')
                ->field('media_cost.advertiser_id as advertiser_id, SUM(money) as money, SUM(ori_money) as ori_money, ad_advertiser_list.advertiser_name as advertiser_name')
                ->whereTime('tdate', 'between', $date)
                ->leftJoin('ad_advertiser_list', 'media_cost.advertiser_id = ad_advertiser_list.advertiser_id');
            if(!empty($advertiserId)){
                $query->where('media_cost.advertiser_id', 'in', $advertiserId);
            }
            // 根据账号ID查询消耗
            $adCost = $query->group('advertiser_name, advertiser_id')->select()->toArray();
            $ori_money = 0;
            $money = 0;
            foreach($adCost as &$item){
                $item['fandain'] = getRound($item['ori_money'], $item['money'], 2);
                $ori_money += $item['ori_money'];
                $money += $item['money'];
            }

            $totalRow = [
                'advertiser_name' => '合计',
                'ori_money' => $ori_money,
                'money' => $money,
            ];


        return [
            'data' => $adCost,
            'totalRow' => $totalRow
        ];
    }

    /**
     * 市场绩效
     */
    public function getAdKpi($params)
    {
        $date = $params['date'];
        $params = $this->searchByAuth($params);

        // 消耗支出，auth_id 分组
        $costQuery = Db::connect('db_advert')
            ->table('media_cost')
            ->field('auth_id, SUM(money) as cost')
            ->whereTime('tdate', 'between', $date)
            ->group('auth_id');
        if(!empty($params['auth_id'])){
            $costQuery->where('media_cost.auth_id', 'in', implode(',', $params['auth_id']));
        }
        $cost = $costQuery->select()->toArray();
        $costList = array_column($cost, 'cost', 'auth_id');



     // 充值收入
     $incomeQuery = Db::connect('db_game_log')
            ->table('sdk_order_success')
            ->field('auth_id, SUM(money) as income')
            
            ->whereTime('pay_time', 'between', $date)
            ->group('auth_id');
        if(!empty($params['auth_id'])){
            $incomeQuery->where('sdk_order_success.auth_id', 'in', implode(',', $params['auth_id']));
        }
        $income = $incomeQuery->select()->toArray();
        $incomeList = array_column($income, 'income', 'auth_id');


        // 人员列表map
        $authList = Db::connect('db_system')->table('sa_system_user')
                    ->select()->toArray();
        $authList = array_column($authList, 'username', 'id');

        // 合并数据
        $data = [];
        $imcomeSum = 0;
        $costSum = 0;
        foreach($cost as $item){
            $data[] = [
                'auth_id' => $item['auth_id'],
                'auth_name' => $authList[$item['auth_id']] ?? '官网',
                'cost' => $costList[$item['auth_id']] ?? 0,
                'income' => $incomeList[$item['auth_id']] ?? 0,
            ];
            $imcomeSum += $incomeList[$item['auth_id']] ?? 0;
            $costSum += $costList[$item['auth_id']] ?? 0;
        }

        $totalRow = [
            'auth_name' => '合计',
            'cost' => $costSum,
            'income' => $imcomeSum,
        ];
        return [
            'data' => $data,
            'totalRow' => $totalRow
        ];

    }
}