noride/noridecrm/service/backend/controllers/DashboardController.php

<?php 
namespace backend\controllers;

use backend\models\UserMember;
use backend\models\Withdraw;
use Yii;
use backend\models\Config;
use backend\models\Member;
use backend\models\Mt4Users;
use backend\models\Mt4Trades;
use backend\models\Open;
use backend\models\ModifyLever;
use backend\models\Transfer;

class DashboardController extends BaseController
{
    
    /**
     * ib首页
     */
    public function actionIbData()
    {
        $member_id = Yii::$app->request->post('member_id');
        $member = Member::find()->where(['id' => $member_id])->limit(1)->one();
        $main_login = $this->getMainLogin($member);
        
        $mt4User = new Mt4Users();
        $allUsers = $mt4User->getAllUsers($member_id, ['mu.LOGIN']);
        $logins = array_column($allUsers, 'LOGIN');

        // 配置，一定会有
        $config = Config::find()->asArray()->limit(1)->one();

        // 外汇，贵金属，CFD
        $mt4Trades = new Mt4Trades();
        $fvSum = $mt4Trades->getSumVolume($logins, explode(',', $config['forex_symbols']));
        $mvSum = $mt4Trades->getSumVolume($logins, explode(',', $config['metal_symbols']));
        $cvSum = $mt4Trades->getSumVolume($logins, explode(',', $config['cfd_symbols']));
        $tvSum = $fvSum + $mvSum + $cvSum;
        $depositSumByDay = $mt4Trades->getDepositSumByDayByLogins($main_login);
        
        // 直客数
        $directlyUserCount = UserMember::directlyUserCount($member_id);

        return $this->outJson(1, [
            'fvSum' => $fvSum,
            'mvSum' => $mvSum,
            'cvSum' => $cvSum,
            'tvSum' => $tvSum,
            'endDate' => $config['end_his_trade_time'],
            'directlyUserCount' => $directlyUserCount,
            'depositSumByDay' => $depositSumByDay,
        ]);
    }

    /**
     * 后台首页统计信息
     */
    public function actionAdminData()
    {
        return $this->outJson(1, [
            'xTraderCount' => Member::xTraderCount(),
            'xBrokerCount' => Member::xBokerCount(),
            'mt4UsersCount' => Mt4Users::mt4UsersCount(),
            'openCount' => Open::countByType(0),
            'modifyLeverCount' => ModifyLever::countByType(0),
            'transferCount' => Transfer::countByType(0),
        ]);
    }
    
    /**
     * 名下代理和客户工单申请进度情况，申请出金，修改杠杆，申请转账，申请开户
     * @see \backend\models\Mt4Users::getList()
     */
    public function actionApplyList()
    {
        $post = Yii::$app->request->post();
        $member_id = isset($post['member_id']) ? (int) $post['member_id'] : 0;
        $order = isset($post['order']) ? $post['order'] : '';
        $orderBy = isset($post['orderBy']) ? strtolower($post['orderBy']) : 'desc';
        $search = isset($post['search']) ? trim($post['search']) : '';
        $start = isset($post['start']) ? (int) $post['start'] : 0;
        $length = isset($post['length']) ? (int) $post['length'] : 20;
        $draw = isset($post['draw']) ? $post['draw'] : 1;

        $result = ['code' => 0, 'data' => ['data' => [], 'draw' => $draw, 'recordsFiltered' => 0, 'recordsTotal' => 0], 'message' => ''];
        
        if (!$member_id) {
            $result['message'] = '参数错误';
            return json_encode($result);
        }
        
        // 先去查代理商名下代理商，再找他们的名下MT4用户的login，再通过login找到客户的member_id
        $where = " WHERE 1 ";
        $member = new Member();
        $ibList = $member->findChildrenIncludeSelf($member_id);
        if (!$ibList) {
            $result['message'] = '没有名下代理';
            return json_encode($result);
        }
        
        $ibIdArr = array_column($ibList, 'id');
        $userMemberList = UserMember::find()->alias('cum')
            ->select('cum.login, cum.member_id')
            ->innerJoin('mt4_users mu', 'cum.login = mu.LOGIN')
            ->where(['cum.member_id' => $ibIdArr])
            ->asArray()
            ->all();
        if (!$userMemberList) {
            $result['message'] = '没有数据.';
            return json_encode($result);
        }
        
        $loginArr = array_unique(array_column($userMemberList, 'login'));
        
        // find_in_set
        $customerWhere = '';
        foreach ($loginArr as $k => $v) {
            $customerWhere .= " FIND_IN_SET('{$v}', logins) OR ";
        }
        $customerWhere = trim($customerWhere);
        $customerWhere = substr($customerWhere, 0, -2);
        $customerList = Member::find()->select(['id', 'name', 'logins'])->where($customerWhere)->asArray()->all();

        $customerIdArr = array_column($customerList, 'id');
        
        // 代理和客户都要
        $memberIdArr = array_unique(array_merge($ibIdArr, $customerIdArr));
        
        // 如果是搜某个客户的话
        if ($search) {
            $searchMemberList = Member::find()->select(['id', 'logins'])->where(['or', ['=', 'name', $search], "FIND_IN_SET('{$search}', logins)"])->asArray()->all();
            if (!$searchMemberList) {
                $result['message'] = '没有该客户';
                return json_encode($result);
            }
            
            $searchMemberIdArr = array_unique(array_column($searchMemberList, 'id'));
            foreach ($searchMemberIdArr as $k => $v) {
                if (!in_array($v, $memberIdArr)) {
                    unset($searchMemberIdArr[$k]);
                }
            }
            $memberIdStr = implode(',', $searchMemberIdArr);


            // 处理logins字段
            $searchMemberLoginsArr = array_unique(array_column($searchMemberList, 'logins'));
            $searchLoginArr = [];
            foreach ($searchMemberLoginsArr as $k => $v) {
                $temp = preg_split('/\s*,\s*/', trim($v), -1, PREG_SPLIT_NO_EMPTY);
                $searchLoginArr = array_merge($searchLoginArr, $temp);
            }
            if (!$searchMemberLoginsArr) {
                $result['message'] = '没有该客户.';
                return json_encode($result);
            }
            $searchLoginArr = array_unique($searchLoginArr);
            $searchLoginStr = '';
            $searchIsLogin = false; // 搜索的值是不是login，因为可以通过login和姓名搜索
            foreach ($searchLoginArr as $k => $v) {
                $searchLoginStr .= "'" . $v . "',";
                if ($v == $search) {
                    $searchIsLogin = true;
                }
            }
            $searchLoginStr = rtrim($searchLoginStr, ',');
            
            $where .= " AND member_id IN ({$memberIdStr}) ";
            $where1 = $where2 = $where3 = $where;
            
            // 开户比较特殊，用户申请后member_id和mt4_login都为null
            $ibIdStr = implode(',', $ibIdArr);
            $where4 = "  WHERE 1 AND (member_id IN ({$memberIdStr}) OR rid IN ({$ibIdStr})) ";
            
            if ($searchLoginStr) {
                if ($searchIsLogin) {
                    $where1 .= " AND login = '{$search}' ";
                    $where2 .= " AND login = '{$search}' ";
                    $where3 .= " AND from_login = '{$search}' ";
                    $where4 .= " AND mt4_login = '{$search}' ";
                } else {
                    $where1 .= " AND login IN ($searchLoginStr) ";
                    $where2 .= " AND login IN ($searchLoginStr) ";
                    $where3 .= " AND from_login IN ($searchLoginStr) ";
                    $where4 .= " AND mt4_login IN ($searchLoginStr) ";       
                }
            }
        } else {
            $memberIdStr = implode(',', $memberIdArr);
            $where .= " AND member_id IN ({$memberIdStr}) ";
            $where1 = $where2 = $where3 = $where;

            // 开户比较特殊，用户申请后member_id和mt4_login都为null
            $ibIdStr = implode(',', $ibIdArr);
            $where4 = "  WHERE 1 AND (member_id IN ({$memberIdStr}) OR rid IN ({$ibIdStr})) ";
        }
        
        // 排序
        $allowOrderColumn = ['business', 'in_time', 'type', 'complete_time'];
        if (in_array($order, $allowOrderColumn) && in_array($orderBy, ['asc', 'desc'])) {
            $orderCondition = " ORDER BY {$order} $orderBy ";
        } else {
            $orderCondition = " ORDER BY in_time $orderBy ";
        }
        
        // 条数限制
        $limitCondition = " LIMIT {$start}, {$length} ";
        
        $unionSql = "SELECT '申请出金' AS business, member_id, login, in_time, `type`, out_time AS complete_time, memo FROM " . Withdraw::tableName() . " {$where1}
            UNION ALL
            SELECT '修改杠杆' AS business, member_id, login, in_time, `type`, '-' AS complete_time, memo FROM " . ModifyLever::tableName() . " {$where2}
            UNION ALL 
            SELECT '申请转账' AS business, member_id, from_login AS login, in_time, `type`, '-' AS complete_time, memo FROM " . Transfer::tableName() . " {$where3}
            UNION ALL 
            SELECT '申请开户' AS business, member_id, mt4_login AS login, in_time, `type`, '-' AS complete_time, '-' AS memo FROM " . Open::tableName() . " {$where4} ";
        $countSql = "SELECT COUNT(*) FROM ({$unionSql}) AS temp_table";
        $count = Withdraw::getDb()->createCommand($countSql)->queryScalar();
        
        $list = [];
        if ($count) {
            $listSql = $unionSql . $orderCondition . $limitCondition;
            $list = Withdraw::getDb()->createCommand($listSql)->queryAll();
            
            foreach ($list as $k => $v) {
                $list[$k]['name'] = '-';
                $list[$k]['logins'] = '-';
                $list[$k]['login'] = $v['login'] ? $v['login'] : '-';
                $list[$k]['memo'] = $v['memo'] ? $v['memo'] : '-';
                
                foreach ($ibList as $k2 => $v2) {
                    if ($v['member_id'] == $v2['id']) {
                        $list[$k]['name'] = $v2['name'];
                        $list[$k]['logins'] = $v2['logins'];
                        break;
                    }
                }
                
                // 不是代理的话则在客户里找
                if ($list[$k]['name'] == '-') {
                    foreach ($customerList as $k3 => $v3) {
                        if ($v['member_id'] == $v3['id']) {
                            $list[$k]['name'] = $v3['name'];
                            $list[$k]['logins'] = $v3['logins'];
                            break;
                        }
                    }
                }

                $list[$k]['in_time'] = $v['in_time'] ? date('Y-m-d H:i:s', $v['in_time'] / 1000) : '-';
                $list[$k]['complete_time'] = $v['complete_time'] && $v['complete_time'] != '-' ? date('Y-m-d H:i:s', $v['complete_time'] / 1000) : '-';
                
                if ($v['business'] == '申请出金') {
                    $list[$k]['type'] = isset(Withdraw::$typeTextMap[$v['type']]) ? Withdraw::$typeTextMap[$v['type']] : $v['type'];
                } elseif ($v['business'] == '修改杠杆') {
                    $list[$k]['type'] = isset(ModifyLever::$typeTextMap[$v['type']]) ? ModifyLever::$typeTextMap[$v['type']] : $v['type'];
                }elseif ($v['business'] == '申请转账') {
                    $list[$k]['type'] = isset(Transfer::$typeTextMap[$v['type']]) ? Transfer::$typeTextMap[$v['type']] : $v['type'];
                }elseif ($v['business'] == '申请开户') {
                    $list[$k]['type'] = isset(Open::$typeTextMap[$v['type']]) ? Open::$typeTextMap[$v['type']] : $v['type'];
                }
            }
        }

        $data['data'] = $list;
        $data['draw'] = $draw;
        $data['recordsFiltered'] = $count;
        $data['recordsTotal'] = $count;

        $result['data'] = $data;
        $result['code'] = 1;

        return json_encode($result);
    }
    
}