noride/noridecrm/service/backend/models/Mt4Users.php

<?php

namespace backend\models;

use Yii;
use yii\db\ActiveQuery;

/**
 * This is the model class for table "mt4_users".
 *
 * @property integer $LOGIN
 * @property string $GROUP
 * @property integer $ENABLE
 * @property integer $ENABLE_CHANGE_PASS
 * @property integer $ENABLE_READONLY
 * @property integer $ENABLE_OTP
 * @property string $PASSWORD_PHONE
 * @property string $NAME
 * @property string $COUNTRY
 * @property string $CITY
 * @property string $STATE
 * @property string $ZIPCODE
 * @property string $ADDRESS
 * @property string $LEAD_SOURCE
 * @property string $PHONE
 * @property string $EMAIL
 * @property string $COMMENT
 * @property string $ID
 * @property string $STATUS
 * @property string $REGDATE
 * @property string $LASTDATE
 * @property integer $LEVERAGE
 * @property integer $AGENT_ACCOUNT
 * @property integer $TIMESTAMP
 * @property double $BALANCE
 * @property double $PREVMONTHBALANCE
 * @property double $PREVBALANCE
 * @property double $CREDIT
 * @property double $INTERESTRATE
 * @property double $TAXES
 * @property integer $SEND_REPORTS
 * @property integer $MQID
 * @property integer $USER_COLOR
 * @property double $EQUITY
 * @property double $MARGIN
 * @property double $MARGIN_LEVEL
 * @property double $MARGIN_FREE
 * @property string $CURRENCY
 * @property resource $API_DATA
 * @property string $MODIFY_TIME
 */
class Mt4Users extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'mt4_users';
    }

    /**
     * @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 [
            [['LOGIN', 'GROUP', 'ENABLE', 'ENABLE_CHANGE_PASS', 'ENABLE_READONLY', 'PASSWORD_PHONE', 'NAME', 'COUNTRY', 'CITY', 'STATE', 'ZIPCODE', 'ADDRESS', 'PHONE', 'EMAIL', 'COMMENT', 'ID', 'STATUS', 'REGDATE', 'LASTDATE', 'LEVERAGE', 'AGENT_ACCOUNT', 'TIMESTAMP', 'BALANCE', 'PREVMONTHBALANCE', 'PREVBALANCE', 'CREDIT', 'INTERESTRATE', 'TAXES', 'SEND_REPORTS', 'USER_COLOR', 'MODIFY_TIME'], 'required'],
            [['LOGIN', 'ENABLE', 'ENABLE_CHANGE_PASS', 'ENABLE_READONLY', 'ENABLE_OTP', 'LEVERAGE', 'AGENT_ACCOUNT', 'TIMESTAMP', 'SEND_REPORTS', 'MQID', 'USER_COLOR'], 'integer'],
            [['REGDATE', 'LASTDATE', 'MODIFY_TIME'], 'safe'],
            [['BALANCE', 'PREVMONTHBALANCE', 'PREVBALANCE', 'CREDIT', 'INTERESTRATE', 'TAXES', 'EQUITY', 'MARGIN', 'MARGIN_LEVEL', 'MARGIN_FREE'], 'number'],
            [['API_DATA'], 'string'],
            [['GROUP', 'ZIPCODE', 'STATUS', 'CURRENCY'], 'string', 'max' => 16],
            [['PASSWORD_PHONE', 'COUNTRY', 'CITY', 'STATE', 'LEAD_SOURCE', 'PHONE', 'ID'], 'string', 'max' => 32],
            [['NAME', 'ADDRESS'], 'string', 'max' => 128],
            [['EMAIL'], 'string', 'max' => 48],
            [['COMMENT'], 'string', 'max' => 64],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'LOGIN' => 'Login',
            'GROUP' => 'Group',
            'ENABLE' => 'Enable',
            'ENABLE_CHANGE_PASS' => 'Enable  Change  Pass',
            'ENABLE_READONLY' => 'Enable  Readonly',
            'ENABLE_OTP' => 'Enable  Otp',
            'PASSWORD_PHONE' => 'Password  Phone',
            'NAME' => 'Name',
            'COUNTRY' => 'Country',
            'CITY' => 'City',
            'STATE' => 'State',
            'ZIPCODE' => 'Zipcode',
            'ADDRESS' => 'Address',
            'LEAD_SOURCE' => 'Lead  Source',
            'PHONE' => 'Phone',
            'EMAIL' => 'Email',
            'COMMENT' => 'Comment',
            'ID' => 'ID',
            'STATUS' => 'Status',
            'REGDATE' => 'Regdate',
            'LASTDATE' => 'Lastdate',
            'LEVERAGE' => 'Leverage',
            'AGENT_ACCOUNT' => 'Agent  Account',
            'TIMESTAMP' => 'Timestamp',
            'BALANCE' => 'Balance',
            'PREVMONTHBALANCE' => 'Prevmonthbalance',
            'PREVBALANCE' => 'Prevbalance',
            'CREDIT' => 'Credit',
            'INTERESTRATE' => 'Interestrate',
            'TAXES' => 'Taxes',
            'SEND_REPORTS' => 'Send  Reports',
            'MQID' => 'Mqid',
            'USER_COLOR' => 'User  Color',
            'EQUITY' => 'Equity',
            'MARGIN' => 'Margin',
            'MARGIN_LEVEL' => 'Margin  Level',
            'MARGIN_FREE' => 'Margin  Free',
            'CURRENCY' => 'Currency',
            'API_DATA' => 'Api  Data',
            'MODIFY_TIME' => 'Modify  Time',
        ];
    }

    public function findByLogin($login, $asArray = false)
    {
        $query = self::find()->where(['login' => $login])->limit(1);
        if ($asArray) {
            $query->asArray();
        }
        $user = $query->one();
        if ($user) {
            $user['MARGIN'] = doubleval(round($user['MARGIN'] * 100) / 100.0);
            $user['MARGIN_FREE'] = doubleval(round($user['MARGIN_FREE'] * 100) / 100.0);
            $user['MARGIN_LEVEL'] = doubleval(round($user['MARGIN_LEVEL'] * 100) / 100.0);
            $user['EQUITY'] = doubleval(round($user['EQUITY'] * 100) / 100.0);
        }
        return $user;
    }

    /**
     * 名下用户
     * @param array $post
     * @param bool $addTotalToList 是否把总数加到列表的最后一行
     * @return array
     */
    public function getList($post, $addTotalToList = true)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        $member_id = $post['member_id'];
        $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;
        $ibId = isset($post['ibId']) ? $post['ibId'] : 0;
        $draw = isset($post['draw']) ? $post['draw'] : 1;

        $where = ['and'];

        // 代理商，没有搜索条件就查当前用户
        $member = new Member();
        $ibs = $member->findChildrenIncludeSelf($member_id);
        $id_arr = array_column($ibs, 'id');
        if ($ibId && in_array($ibId, $id_arr)) {
            $ibs = $member->findChildrenIncludeSelf($ibId);
            $id_arr = array_column($ibs, 'id');
        }
        if ($id_arr) {
            $where[] = ['in', 'cum.member_id', $id_arr];    
        }
        

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

        // 排序
        $allowOrderColumn = ['name', 'LOGIN', 'NAME', 'LEVERAGE', 'BALANCE', 'MARGIN', 'MARGIN_FREE', 'MARGIN_LEVEL', 'EQUITY', 'MODIFY_TIME', 'GROUP'];
        if (in_array($order, $allowOrderColumn) && in_array($orderBy, ['asc', 'desc'])) {
            if ($order === 'name') {
                $order = 'cum.' . $order;
            } elseif ($order === 'NAME') {
                $order = 'mu.' . $order;
            }
            if ($order === 'LOGIN') {
                $order = 'mu.LOGIN';
            }
            if ($orderBy == 'asc') {
                $orderCondition = [$order => SORT_ASC];
            } else {
                $orderCondition = [$order => SORT_DESC];
            }
        } else {
            $orderCondition = ['mu.LOGIN' => SORT_DESC];
        }

        $query = UserMember::find();
        $query->alias('cum')
            ->select('cum.*, mu.*')
            ->innerJoin('mt4_users mu', 'cum.login = mu.LOGIN')
            ->where($where)
            ->orderBy($orderCondition);

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

        if ($count) {
            foreach ($list as $k => $v) {
                $list[$k]['in_time'] = (int) $v['in_time'];
                $list[$k]['MODIFY_TIME'] = date('m-d H:i', strtotime($v['MODIFY_TIME']));
                $list[$k]['MARGIN'] = round($v['MARGIN'], 2);
                $list[$k]['MARGIN_FREE'] = round($v['MARGIN_FREE'], 2);
                $list[$k]['MARGIN_LEVEL'] = round($v['MARGIN_LEVEL'], 2);
                $list[$k]['EQUITY'] = round($v['EQUITY'], 2);
            }

            // 计算总数，放到最后一行
            if ($addTotalToList) {
                $query = UserMember::find();
                $sum = $query->alias('cum')
                    ->select(['SUM(mu.BALANCE) AS balance', 'SUM(mu.MARGIN) AS margin', 'SUM(mu.MARGIN_FREE) AS margin_free', 'SUM(mu.EQUITY) AS equity'])
                    ->innerJoin('mt4_users mu', 'cum.login = mu.LOGIN')
                    ->where($where)
                    ->asArray()
                    ->limit(1)
                    ->one();
                $lastRow = [
                    'BALANCE' => round($sum['balance'], 2),
                    'EQUITY' => round($sum['equity'], 2),
                    'LEVERAGE' => '',
                    'LOGIN' => '',
                    'MARGIN' => round($sum['margin'], 2),
                    'MARGIN_FREE' => round($sum['margin_free'], 2),
                    'MARGIN_LEVEL' => '',
                    'MODIFY_TIME' => '',
                    'NAME' => '',
                    'name' => '',
                ];
                array_push($list, $lastRow);
            }

        } else {
            // 没有数据则输出默认值
            if ($addTotalToList) {
                $list = [
                    [
                        'BALANCE' => 0,
                        'EQUITY' => 0,
                        'LEVERAGE' => '',
                        'LOGIN' => '',
                        'MARGIN' => 0,
                        'MARGIN_FREE' => 0,
                        'MARGIN_LEVEL' => '',
                        'MODIFY_TIME' => '',
                        'NAME' => '',
                        'name' => '',
                    ],
                ];
            }
        }

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

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

        return $result;
    }
    
    /**
     * 名下所有账户
     * @param int $member_id  当前登录用户的id
     * @param array|string $select
     * @return array
     */
    public function getAllUsers($member_id, $select = 'cum.*, mu.*')
    {
        // 代理商，没有搜索条件就查当前用户
        $member = new Member();
        $ibs = $member->findChildrenIncludeSelf($member_id);
        $id_arr = array_column($ibs, 'id');
        if (!$id_arr) {
            return [];
        }
        
        $query = UserMember::find();
        $list = $query->alias('cum')
            ->select($select)
            ->innerJoin('mt4_users mu', 'cum.login = mu.LOGIN')
            ->where(['in', 'cum.member_id', $id_arr])
            ->asArray()
            ->all();
        
        return $list;
    }
    
     /**
      * MT4用户数
      */
     public static function mt4UsersCount()
    {
        return static::find()->where(['!=', 'GROUP', 'demoforex-usd'])->count();   
    }

    /**
     * 获取代理商用户信息
     * @return ActiveQuery
     */
    public function getIbMember()
    {
        return $this->hasOne(Member::className(), ['id' => 'member_id'])->viaTable(UserMember::tableName(), ['login' => 'LOGIN']);
    }
    /**
     * 获取代理商名下代理商的名下MT4用户
     * @param array|int member_ids
     * @return array
     */
    public static function findChildLogins($member_ids)
    {
        if (is_numeric($member_ids)) {
            $member_ids = [$member_ids];    
        }
        
        $list = UserMember::find()->alias('cum')
            ->select('cum.*, mu.*')
            ->innerJoin('mt4_users mu', 'cum.login = mu.LOGIN')
            ->where(['in', 'cum.member_id', $member_ids])
            ->orderBy(['mu.LOGIN' => SORT_DESC])
            ->asArray()
            ->all();
        return $list;
    }
    
}