trends/worklife/service/backend/models/Mt4Trades.php

<?php  

namespace backend\models;

use backend\helpers\DateTimeHelper;
use Yii;
use common\helpers;
use yii\db\Expression;
use yii\db\Query;
use yii\di\Instance;
use yii\redis\Connection;
use common\helpers\Utils;

/**
 * This is the model class for table "mt4_trades".
 *
 * @property integer $TICKET
 * @property integer $LOGIN
 * @property string $SYMBOL
 * @property integer $DIGITS
 * @property integer $CMD
 * @property integer $VOLUME
 * @property string $OPEN_TIME
 * @property double $OPEN_PRICE
 * @property double $SL
 * @property double $TP
 * @property string $CLOSE_TIME
 * @property string $EXPIRATION
 * @property integer $REASON
 * @property double $CONV_RATE1
 * @property double $CONV_RATE2
 * @property double $COMMISSION
 * @property double $COMMISSION_AGENT
 * @property double $SWAPS
 * @property double $CLOSE_PRICE
 * @property double $PROFIT
 * @property double $TAXES
 * @property string $COMMENT
 * @property integer $INTERNAL_ID
 * @property double $MARGIN_RATE
 * @property integer $TIMESTAMP
 * @property integer $MAGIC
 * @property integer $GW_VOLUME
 * @property integer $GW_OPEN_PRICE
 * @property integer $GW_CLOSE_PRICE
 * @property string $MODIFY_TIME
 */
class Mt4Trades extends \yii\db\ActiveRecord
{
    /**
     * @var array
     */
    public $valueTextMap = [
        'REASON' => [
            0 => 'Client',
            1 => 'Expert',
            2 => 'Dealer',
            3 => 'Signal',
            4 => 'Gateway',
            5 => 'Mobile',
            6 => 'Web',
            7 => 'API',
        ],
    ];

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'mt4_trades';
    }

    /**
     * @return \yii\db\Connection the database connection used by this AR class.
     */
    public static function getDb()
    {
        return Yii::$app->get('dbXcrm');
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['TICKET', 'LOGIN', 'SYMBOL', 'DIGITS', 'CMD', 'VOLUME', 'OPEN_TIME', 'OPEN_PRICE', 'SL', 'TP', 'CLOSE_TIME', 'EXPIRATION', 'CONV_RATE1', 'CONV_RATE2', 'COMMISSION', 'COMMISSION_AGENT', 'SWAPS', 'CLOSE_PRICE', 'PROFIT', 'TAXES', 'COMMENT', 'INTERNAL_ID', 'MARGIN_RATE', 'TIMESTAMP', 'MODIFY_TIME'], 'required'],
            [['TICKET', 'LOGIN', 'DIGITS', 'CMD', 'VOLUME', 'REASON', 'INTERNAL_ID', 'TIMESTAMP', 'MAGIC', 'GW_VOLUME', 'GW_OPEN_PRICE', 'GW_CLOSE_PRICE'], 'integer'],
            [['OPEN_TIME', 'CLOSE_TIME', 'EXPIRATION', 'MODIFY_TIME'], 'safe'],
            [['OPEN_PRICE', 'SL', 'TP', 'CONV_RATE1', 'CONV_RATE2', 'COMMISSION', 'COMMISSION_AGENT', 'SWAPS', 'CLOSE_PRICE', 'PROFIT', 'TAXES', 'MARGIN_RATE'], 'number'],
            [['SYMBOL'], 'string', 'max' => 16],
            [['COMMENT'], 'string', 'max' => 32],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'TICKET' => 'Ticket',
            'LOGIN' => 'Login',
            'SYMBOL' => 'Symbol',
            'DIGITS' => 'Digits',
            'CMD' => 'Cmd',
            'VOLUME' => 'Volume',
            'OPEN_TIME' => 'Open  Time',
            'OPEN_PRICE' => 'Open  Price',
            'SL' => 'Sl',
            'TP' => 'Tp',
            'CLOSE_TIME' => 'Close  Time',
            'EXPIRATION' => 'Expiration',
            'REASON' => 'Reason',
            'CONV_RATE1' => 'Conv  Rate1',
            'CONV_RATE2' => 'Conv  Rate2',
            'COMMISSION' => 'Commission',
            'COMMISSION_AGENT' => 'Commission  Agent',
            'SWAPS' => 'Swaps',
            'CLOSE_PRICE' => 'Close  Price',
            'PROFIT' => 'Profit',
            'TAXES' => 'Taxes',
            'COMMENT' => 'Comment',
            'INTERNAL_ID' => 'Internal  ID',
            'MARGIN_RATE' => 'Margin  Rate',
            'TIMESTAMP' => 'Timestamp',
            'MAGIC' => 'Magic',
            'GW_VOLUME' => 'Gw  Volume',
            'GW_OPEN_PRICE' => 'Gw  Open  Price',
            'GW_CLOSE_PRICE' => 'Gw  Close  Price',
            'MODIFY_TIME' => 'Modify  Time',
        ];
    }

    /**
     * 获取用户交易次数，包括总交易次数、盈利交易次数、亏损交易次数、亏损交易百分比
     * @param int $login 字段LOGIN
     * @return array
     */
    public function getTradeCount($login)
    {
        // 总次数
        $totalCount = static::countHistoryByLogin($login);

        // 盈利交易次数
        $winCount = static::countWinHistoryByLogin($login);

        // 亏损交易次数
        $lossCount = static::countLossHistoryByLogin($login);

        // 盈利交易百分比
        $winPercent = $totalCount ? number_format(($winCount / $totalCount * 100), 5, '.', '') : 0;

        // 亏损交易百分比
        $lossPercent = $totalCount ? number_format(($lossCount / $totalCount * 100), 5, '.', '')  : 0;

        return [
            'totalCount' => $totalCount,
            'winCount' => $winCount,
            'lossCount' => $lossCount,
            'winPercent' => $winPercent,
            'lossPercent' => $lossPercent,
        ];
    }

    /**
     * 总收益
     * @param int $login 字段LOGIN
     * @return array
     */
    public function getProfitSumByDay($login)
    {
        return $this->getListByDay($login, 'PROFIT', '(`CMD` = 0 OR `CMD` = 1)');
    }

    /**
     * 交易手数
     * @param int $login 字段LOGIN
     * @return array
     */
    public function getVolumeSumByDay($login)
    {
        return $this->getListByDay($login, 'VOLUME', '(`CMD` = 0 OR `CMD` = 1)');
    }

    /**
     * 交易货币分布
     * @param int $login 字段LOGIN
     * @return array
     */
    public function getSymbolCount($login)
    {
        $sql = "SELECT `SYMBOL`, COUNT(*) AS cnt FROM " . self::tableName() . " WHERE (`CMD` = 0 OR `CMD` = 1) AND `CLOSE_TIME` != '1970-01-01' AND `LOGIN` = :login GROUP BY `SYMBOL`";
        $values = [':login' => $login];
        $rows = self::getDb()->createCommand($sql)->bindValues($values)->queryAll();
        return $rows;
    }

    /**
     * 交易来源分布
     * @param int $login 字段LOGIN
     * @return array
     */
    public function getReasonCount($login)
    {
        $sql = "SELECT `REASON`, COUNT(*) AS cnt FROM " . self::tableName() . " WHERE (`CMD` = 0 OR `CMD` = 1) AND `CLOSE_TIME` != '1970-01-01' AND `LOGIN` = :login GROUP BY `REASON`";
        $values = [':login' => $login];
        $rows = self::getDb()->createCommand($sql)->bindValues($values)->queryAll();
        foreach ($rows as $k => $v) {
            $rows[$k]['REASON'] = isset($this->valueTextMap['REASON'][$v['REASON']]) ? $this->valueTextMap['REASON'][$v['REASON']] : '';
        }
        return $rows;
    }

    /**
     * 获取第一条记录
     * @param int $login 字段LOGIN
     * @return array|false
     */
    public function getFirstRecord($login)
    {
        return $this->getOneRecord($login, 'ASC');
    }

    /**
     * 获取最后一条记录
     * @param int $login 字段LOGIN
     * @return array|false
     */
    public function getLastRecord($login)
    {
        return $this->getOneRecord($login, 'DESC');
    }

    /**
     * 获取一条记录
     * @param int $login 字段LOGIN
     * @param string $orderBy
     * @return array|false
     */
    protected function getOneRecord($login, $orderBy)
    {
        $sql = "SELECT * FROM " . self::tableName() . " WHERE `LOGIN` = :login ORDER BY `TICKET` {$orderBy} LIMIT 1";
        $values = [':login' => $login];
        $row = self::getDb()->createCommand($sql)->bindValues($values)->queryOne();
        return $row;
    }

    /**
     * 总笔数
     * @param int $login
     * @return int|string
     */
    public static function countHistoryByLogin($login)
    {
        $query = static::find();
        static::addTypeCondition($query, 'history');
        return $query->andWhere(['LOGIN' => $login])->count();
    }

    /**
     * 盈利笔数
     * @param int $login
     * @return int|string
     */
    public static function countWinHistoryByLogin($login)
    {
        $query = static::find();
        static::addTypeCondition($query, 'history');
        return $query->andWhere(['LOGIN' => $login])->andWhere(['>=', 'PROFIT', 0])->count();
    }

    /**
     * 亏损笔数
     * @param int $login
     * @return int|string
     */
    public static function countLossHistoryByLogin($login)
    {
        $query = static::find();
        static::addTypeCondition($query, 'history');
        return $query->andWhere(['LOGIN' => $login])->andWhere(['<', 'PROFIT', 0])->count();
    }

    /**
     * @param int $login
     * @return array
     */
    public static function sumProfitByDay($login)
    {
        $result = [];
        $firstTrade = static::find()->where(['LOGIN' => $login])->orderBy('TICKET asc')->asArray()->limit(1)->one();
        $lastTrade = static::find()->where(['LOGIN' => $login])->orderBy('TICKET desc')->asArray()->limit(1)->one();
        if ($firstTrade == null || $lastTrade == null) {
            return $result;
        }
        $startData = date('Y-m-d 00:00:00', strtotime($firstTrade['CLOSE_TIME']));
        $endData = date('Y-m-d 00:00:00', strtotime($lastTrade['CLOSE_TIME']) + 86400);
        for ($s = strtotime($startData), $e = strtotime($endData); $s <= $e; $s += 86400) {
            // 非最后一天的数据进行缓存
            $profit = static::getDb()->cache(function ($db) use ($login, $s) {
                $profit = static::find()->select(new Expression('IFNULL(SUM(`PROFIT`), 0)'))->where('CMD=0 or CMD=1')->andWhere(['<>', 'CLOSE_TIME', '1970-01-01'])->andWhere(['LOGIN' => $login])->andWhere(['<', 'CLOSE_TIME', date('Y-m-d 00:00:00', $s)])->scalar();
                $profit = round($profit, 2);
                return $profit;
            }, $s == $e ? 0 : 31536000);
            $result[] = [
                'time' => $s * 1000,
                'val' => $profit
            ];
        }
        return $result;
    }
    
    /**
     * 获取每个MT4账户的外汇交易量
     * @param string|array $logins
     * @param string|array $symbols
     * @param string $startTime
     * @param string $endTime
     * @return array 二维数组，包含了MT4和交易量
     */
    public function getSumVolumeEachLogin($logins, $symbols, $startTime = '', $endTime = '')
    {
        if (empty($logins) || empty($symbols)) {
            return [];
        }
        
        if (is_array($logins)) {
            $logins = helpers\StringHelper::buildInSql($logins);
        }
        
        if (is_array($symbols)) {
            $symbols = helpers\StringHelper::buildInSql($symbols);
        }

        $sql = "SELECT `login`, SUM(`VOLUME`) AS `total_volume`  FROM " . self::tableName() . " WHERE (`CMD` = 0 OR `CMD` = 1) AND `CLOSE_TIME` != '1970-01-01' AND `LOGIN` IN ({$logins}) AND `SYMBOL` IN ({$symbols}) ";
        if ($startTime) {
            $sql .= " AND `CLOSE_TIME` > '{$startTime}' ";
        }
        if ($endTime) {
            $sql .= " AND `CLOSE_TIME` < '{$endTime}' ";
        }
        $sql .= " GROUP BY `LOGIN` ";
        
        $list = self::getDb()->createCommand($sql)->queryAll();
        
        $list2 = [];
        foreach ($list as $k => $v) {
            $list2[$v['login']] = $v;
        }
        
        return $list2;
    }

    /**
     * 获取总的外汇交易量
     * @param string|array $logins
     * @param string|array $symbols
     * @param string $startTime
     * @param string $endTime
     * @return float
     */
    public function getSumVolume($logins, $symbols, $startTime = '', $endTime = '')
    {
        if (empty($logins) || empty($symbols)) {
            return 0;
        }
        
        if (is_array($logins)) {
            $logins = implode(',', $logins);
        }

        if (is_array($symbols)) {
            $symbols2 = '';
            foreach ($symbols as $k => $v) {
                $symbols2 .= "'".trim($v)."',";
            }
            $symbols = rtrim($symbols2, ',');
        }

        $sql = "SELECT SUM(`VOLUME`) AS total_volume  FROM " . self::tableName() . " WHERE (`CMD` = 0 OR `CMD` = 1) AND `CLOSE_TIME` != '1970-01-01'  AND `LOGIN` IN ({$logins}) AND `SYMBOL` IN ({$symbols}) ";
        if ($startTime) {
            $sql .= " AND `CLOSE_TIME` > '{$startTime}' ";
        }
        if ($endTime) {
            $sql .= " AND `CLOSE_TIME` < '{$endTime}' ";
        }
        
        $sum = (float) self::getDb()->createCommand($sql)->queryScalar();
        
        return $sum;
    }

    /**
     * 统计信息 收益 笔数 佣金等
     * @param array $logins
     * @param int $type
     * @param string $sTime
     * @param string $eTime
     * @param string $search
     * @return array
     */
    public static function findSum($logins, $type, $sTime, $eTime, $search)
    {
        $result = [
            'sp' => 0,
            'sv' => 0,
            'ss' => 0,
            'sc' => 0,
        ];
        if (!is_array($logins)) {
            $logins = explode(',', $logins);
        }
        if (empty($logins)) {
            return $result;
        }
        $query = static::find();
        $query->select('sum(PROFIT) as sp,sum(VOLUME) as sv,sum(SWAPS) as ss,sum(COMMISSION) as sc')->andWhere('LOGIN IN (' . helpers\StringHelper::buildInSql($logins) . ')');
        if (!empty($type)) {
            static::addTypeCondition($query, $type);
        }
        $query->andFilterWhere(['>=', 'CLOSE_TIME', $sTime]);
        $query->andFilterWhere(['<=', 'CLOSE_TIME', $eTime]);
        $query->andFilterWhere(['or', ['like', 'LOGIN', $search], ['like', 'TICKET', $search]]);
        $data = $query->asArray()->limit(1)->one();
        isset($data['sp']) && $result['sp'] = $data['sp'];
        isset($data['sv']) && $result['sv'] = $data['sv'];
        isset($data['ss']) && $result['ss'] = $data['ss'];
        isset($data['sc']) && $result['sc'] = $data['sc'];
        return $result;
    }

    /**
     * @param Query $query
     * @param string $type
     */
    public static function addTypeCondition($query, $type)
    {
        if ($type == "deposit") {
            // 入金
            $query->andWhere("CMD=6 and profit>0");
        } else if ($type == "withdraw") {
            // 出金
            $query->andWhere("CMD=6 and profit<0");
        } else if ($type == "position") {
            // 持仓
            $query->andWhere("(CMD=0 or CMD=1) and CLOSE_TIME='1970-01-01'");
        } else if ($type == "history") {
            // 历史
            $query->andWhere("(CMD=0 or CMD=1) and CLOSE_TIME!='1970-01-01'");
        } else if ($type == "pending") {
            // 挂单
            $query->andWhere("(CMD=2 or CMD=3 or CMD=4 or CMD=5) and CLOSE_TIME='1970-01-01'");
        } else if ($type == "credit") {
            // 信用
            $query->andWhere("CMD=7");
        } else if ($type == 'all') {
            // 全部
        } else {
            $query->andWhere("0=1");
        }
    }
    /**
     * 获取总入金
     * @param int $login
     * @return int
     */
    public function getDepositSumByLogin($login)
    {
        $where = [
            'and',
            ['=', 'CMD', 6],
            ['>', 'PROFIT', 0],
            ['=', 'LOGIN', $login],
        ];
        return (float) self::find()->where($where)->sum('PROFIT');
    }

    /**
     * 获取总出金
     * @param int $login
     * @return int
     */
    public function getWithdrawSumByLogin($login)
    {
        $where = [
            'and',
            ['=', 'CMD', 6],
            ['<', 'PROFIT', 0],
            ['=', 'LOGIN', $login],
        ];
        return (float) self::find()->where($where)->sum('PROFIT');
    }

    /**
     * 获取总入金
     * @param array $logins
     * @return int
     */
    public function getDepositSumByLogins($logins)
    {
        $where = [
            'and',
            ['=', 'CMD', 6],
            ['>', 'PROFIT', 0],
            ['in', 'LOGIN', $logins],
        ];
        return (float) self::find()->where($where)->sum('PROFIT');
    }

    /**
     * 获取总出金
     * @param array $logins
     * @return int
     */
    public function getWithdrawSumByLogins($logins)
    {
        $where = [
            'and',
            ['=', 'CMD', 6],
            ['<', 'PROFIT', 0],
            ['in', 'LOGIN', $logins],
        ];
        return (float) self::find()->where($where)->sum('PROFIT');
    }

    /**
     * 获取入金第一条记录
     * @param int $login 字段LOGIN
     * @return array|false
     */
    public function getFirstDepositRecord($login)
    {
        return $this->getOneDepositRecord($login, 'ASC');
    }

    /**
     * 获取入金最后一条记录
     * @param int $login 字段LOGIN
     * @return array|false
     */
    public function getLastDepositRecord($login)
    {
        return $this->getOneDepositRecord($login, 'DESC');
    }

    /**
     * 获取一条记录
     * @param int $login 字段LOGIN
     * @param string $orderBy
     * @return array|false
     */
    protected function getOneDepositRecord($login, $orderBy)
    {
        $sql = "SELECT * FROM " . self::tableName() . " WHERE `LOGIN` = :login AND `CMD` = 6 ORDER BY `TICKET` {$orderBy} LIMIT 1";
        $values = [':login' => $login];
        $row = self::getDb()->createCommand($sql)->bindValues($values)->queryOne();
        return $row;
    }
    
    /**
     * 佣金增长曲线
     * @param array $logins 字段LOGIN
     * @return array
     */
    public function getDepositSumByDayByLogins($logins)
    {
        return $this->getListByDay($logins, 'PROFIT', '`CMD` = 6 AND `PROFIT` > 0');
    }
    
    /**
     * 每日列表数据
     * @param array|int $logins 字段LOGIN，可以是单个或多个
     * @param string $field 需要统计的字段
     * @param string $where 其他条件
     * @return array 返回二位数组，每个数组由毫秒时间戳和统计数据组成
     */
    protected function getListByDay($logins, $field, $where)
    {
        if (is_array($logins)) {
            $logins = implode(',', $logins);
        }
        
        if (empty($logins)) {
            return [];
        }

        $sql = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`CLOSE_TIME`), '%Y-%m-%d') AS `close_date`, SUM(`{$field}`) AS `sum_num` FROM " . self::tableName() . " WHERE {$where} AND `CLOSE_TIME` != '1970-01-01' AND `LOGIN` IN ($logins) GROUP BY `close_date` ";
        $all = self::getDb()->createCommand($sql)->queryAll();
        $list = [];

        if ($all) {
            usort($all, function ($a, $b) {
                if ($a['close_date'] == $b['close_date']) {
                    return 0;
                }
                return $a['close_date'] < $b['close_date'] ? -1 : 1;
            });

            // 找出从有记录的第一天到昨天的日期
            $beginTime = $all[0]['close_date'];
            $endTime = date('Y-m-d', strtotime('-1 days'));
            $dateList = helpers\DateHelper::getDaily($beginTime, $endTime);

            foreach ($dateList as $k => $v) {
                $sum = 0;
                foreach ($all as $k2 => $v2) {
                    if ($v2['close_date'] > $v) {
                        break;
                    }
                    $sum += (float) $v2['sum_num'];
                }
                $sum = number_format($sum, 2, '.', '');

                $timestamp = strtotime($v) * 1000;
                $list[] = [$timestamp, $sum];
            }
        } else {
            // 如果没有数据的话，那么从第一天的记录里拿到日期一直到昨天，这是为了在图像的横坐标显示日期
            $beginTime = static::find()->select(['CLOSE_TIME'])->where(['in', 'LOGIN', $logins])->orderBy('TICKET asc')->asArray()->limit(1)->scalar();
            if (!$beginTime) {
                return [];
            }
            $endTime = date('Y-m-d', strtotime('-1 days'));
            $dateList = helpers\DateHelper::getDaily($beginTime, $endTime);
            foreach ($dateList as $k => $v) {
                $timestamp = strtotime($v) * 1000;
                $list[] = [$timestamp, 0];
            }
        }

        return $list;
    }

    /**
     * 获取每天的出金总额并缓存(计划脚本调用)
     * @param $login
     * @return array
     */
    public function findSumDepositByDay($login)
    {
        $ft = $this->getFirstDepositRecord($login);
        if ($ft == null) {
            return [];
        }

        $fcTime = $ft['CLOSE_TIME'];
        if (!$fcTime) {
            return [];
        }
        $cache = Instance::ensure('redis', Connection::className());
        $lcTime = date('Y-m-d', time());
        $list = [];
        $dateList = helpers\DateHelper::getDailyYesterday($fcTime);
        
        foreach ($dateList as $k => $v) {
            $timestamp = strtotime($v) * 1000;
            $sum = $cache->get("findSumDepositByDay,".$timestamp.",".$login);
            if ($sum == null) {
                $sum = $this->getSumProfit($login, $v);
                if ($sum == null) {
                    $sum = 0;
                }
                $cache->setex("findSumDepositByDay,".$timestamp.",".$login, 31536000, $sum);
            }
            $list[] = [$timestamp, $sum];
            if ($v > $lcTime) {
                break;
            }
        }

        return $list;
    }

    /**
     * 获取每天的出金总额
     * @param $login
     * @param $daytime
     * @return false|null|string
     */
    private function getSumProfit($login, $daytime)
    {
        $sql = "select sum(PROFIT) from mt4_trades where CMD=6 and PROFIT>0 and CLOSE_TIME!='1970-01-01' and login=:login and CLOSE_TIME<=:close_time";
        $sqlParams[':login'] = $login;
        $sqlParams[':close_time'] = $daytime;
        return self::getDb()->createCommand($sql, $sqlParams)->queryScalar();
    }
    
    /**
     * 后台交易管理列表数据
     * @param array $post
     * @return array
     */
    public function getAdminList($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        $type = isset($post['type']) ? $post['type'] : '';
        $order = isset($post['order']) ? $post['order'] : '';
        $orderBy = isset($post['orderBy']) ? strtolower($post['orderBy']) : 'desc';
        $search = isset($post['search']) ? $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;
        
        if (!in_array($type, ['deposit', 'withdraw', 'position', 'history', 'pending', 'credit'])) {
            $result['message'] = '参数错误';
            return $result;
        }

        $where = ['and'];

        // 账户或姓名
        if ($search) {
            $where[] = [
                'or',
                ['like', 'LOGIN', $search],
                ['like', 'TICKET', $search],
            ];
        }

        // 排序
        $allowOrderColumn = ['TICKET', 'LOGIN', 'CMD', 'SYMBOL', 'SL', 'TP', 'VOLUME', 'OPEN_PRICE', 'OPEN_TIME', 'CLOSE_PRICE', 'CLOSE_TIME', 'SWAPS', 'COMMISSION', 'PROFIT'];
        if (in_array($order, $allowOrderColumn) && in_array($orderBy, ['asc', 'desc'])) {
            if ($orderBy == 'asc') {
                $orderCondition = [$order => SORT_ASC];
            } else {
                $orderCondition = [$order => SORT_DESC];
            }
        } else {
            $orderCondition = ['TICKET' => SORT_DESC];
        }

        $query = static::find();
        $query->where($where)
            ->orderBy($orderCondition);
        static::addTypeCondition($query, $type);

        $count = $query->count();
        $query->offset($start)->limit($length);
        $list = $query->asArray()->all();

        if ($count) {
            foreach ($list as $k => $v) {
                $list[$k]['PROFIT'] = Utils::formatFloatOrInt($v['PROFIT']);
            }
        }

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

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

        return $result;
    }

    /*
     * 获取已经平仓的订单
     */
    public static function findAllCloseTrade($login)
    {
        return static::find()->where("CMD=0 OR CMD=1")->andWhere(['!=', 'CLOSE_TIME', '1970-01-01'])->andWhere(['LOGIN' => $login])->asArray()->all();
    }

    /**
     * 获取每个MT4账户的佣金
     * @see \console\controllers\CommissionJobController::actionRun() 交易量除以100，所以在这里乘以100
     * @param int $member_id
     * @param string|array $logins
     * @param int $trade_type
     * @param string $startTime
     * @param string $endTime
     * @param bool $is_wy 是否为外佣
     * @return array 二维数组，包含了MT4和交易量
     */
    public function getSumCommissionEachLogin($member_id, $logins, $trade_type, $startTime = '', $endTime = '', $is_wy = false)
    {
        if (empty($logins)) {
            return [];
        }
        
        if (is_array($logins)) {
            $logins = implode(',', $logins);
        }

        if ($is_wy) {
            $like = '"W%"';
        } else {
            $like = '"N%"';
        }

        $sql = "SELECT cr.`user_login` AS `login`, SUM(cr.`commission`) AS `total_commission`  FROM " . self::tableName() . " AS t INNER JOIN " . CommissionRecord::tableName() . " AS cr ON t.`TICKET` = cr.`trade_ticket` WHERE cr.`user_login` IN ({$logins}) AND cr.`trade_type` = {$trade_type} AND cr.`commission_rule` LIKE {$like} AND cr.`ib_id` = {$member_id} ";
        if ($startTime) {
            $sql .= " AND t.`CLOSE_TIME` >= '{$startTime}' ";
        }
        if ($endTime) {
            $sql .= " AND t.`CLOSE_TIME` <= '{$endTime}' ";
        }
        $sql .= " GROUP BY cr.`user_login` ";

        $list = self::getDb()->createCommand($sql)->queryAll();

        $list2 = [];
        foreach ($list as $k => $v) {
            $v['total_commission'] = $v['total_commission'] * 100;
            $list2[$v['login']] = $v;
        }

        return $list2;
    }
    
}