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

<?php 
namespace backend\models;

use Yii;
use common\helpers\Idcard;
use backend\helpers\MailHelper;

/**
 * This is the model class for table "crm_member".
 *
 * @property integer $id
 * @property integer $type
 * @property integer $is_enable
 * @property string $username
 * @property string $password
 * @property string $ip
 * @property string $logins
 * @property string $name
 * @property integer $gender
 * @property string $id_no
 * @property string $birthday
 * @property string $address
 * @property string $mobile
 * @property string $main_login
 * @property string $random_code
 * @property integer $random_code_time
 * @property string $avatar
 * @property integer $ref_id
 * @property string $ref_path
 * @property integer $in_time
 * @property string $ib_old_login_name
 */
class Member extends \yii\db\ActiveRecord
{
    const MEMBER_TYPE_USER = 1;
    const MEMBER_TYPE_IB = 2;
    const MEMBER_TYPE_ADMIN = 99;
    
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'crm_member';
    }

    /**
     * @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 [
            [['type', 'username', 'password'], 'required'],
            [['type', 'is_enable', 'gender', 'random_code_time', 'ref_id', 'in_time'], 'integer'],
            [['birthday'], 'safe'],
            [['username', 'password', 'ip', 'logins', 'name', 'id_no', 'address', 'mobile', 'main_login', 'random_code', 'avatar', 'ib_old_login_name'], 'string', 'max' => 255],
            [['ref_path'], 'string', 'max' => 3000],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'type' => 'Type',
            'is_enable' => 'Is Enable',
            'username' => 'Username',
            'password' => 'Password',
            'ip' => 'Ip',
            'logins' => 'MT4 login account',
            'name' => 'Name',
            'gender' => 'Gender',
            'id_no' => 'Id No',
            'birthday' => 'Birthday',
            'address' => 'Address',
            'mobile' => 'Mobile',
            'main_login' => 'Main Login',
            'random_code' => 'Random Code',
            'random_code_time' => 'Random Code Time',
            'avatar' => 'Avatar',
            'ref_id' => 'Ref ID',
            'ref_path' => 'Ref Path',
            'in_time' => 'In Time',
            'ib_old_login_name' => 'Ib Old Login Name',
        ];
    }

    /**
     * @param string $username
     * @param int $type
     * @return static
     */
    public function findByUserName($username, $type)
    {
        if ($type == null) {
            return self::findOne(['username' => strtolower($username)]);
        } else {
            return self::findOne(['username' => strtolower($username), 'type' => $type]);
        }
    }

    /**
     * @param string $username
     * @return static
     */
    public function findByIbOldLoginName($username)
    {
        return self::findOne(['ib_old_login_name' => strtolower($username)]);
    }

    /**
     * @param int $member_id
     * @return array
     */
    public function getLogins($member_id)
    {
        $member = Member::find()->select('logins')->where(['id' => $member_id])->asArray()->limit(1)->one();
        return explode(',', $member['logins']);
    }

    /**
     * @param string $password
     * @return string
     */
    public function hash($password)
    {
        return md5($password);
    }

    /**
     * @param int $id
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function findById($id)
    {
        return static::find()->where(['id'=> $id])->asArray()->limit(1)->one();
    }

    /**
     * @param int $login
     * @param int $type
     * @param string|array $orderBy
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function findByLogin($login, $type = null, $orderBy = null)
    {
        $login = (int) $login;
        $query = static::find()->where("FIND_IN_SET({$login}, logins)")->limit(1);
        if ($type !== null) {
            $query->andWhere(['type' => $type]);
        }
        if ($orderBy !== null) {
            $query->orderBy($orderBy);
        }
        $result = $query->asArray()->one();
        return $result;
    }

    /**
     * 修改密码
     * @param int $id
     * @param int $type
     * @param string $oldPassword
     * @param string $newPassword
     * @return array
     */
    public function changePassword($id, $type, $oldPassword, $newPassword)
    {
        $result = ['code' => 0, 'message' => ''];

        $where = [
            'AND',
            ['=', 'id', $id],
            ['=', 'type', $type],
        ];
        /** @var Member $member */
        $member = self::find()->where($where)->limit(1)->one();

        if (!$member) {
            $result['message'] = '找不到相应的用户';
            return $result;
        }

        // 判断旧密码是否正确
        if ($this->hash($oldPassword) !== $member->password) {
            $result['message'] = '旧密码不正确';
            return $result;
        }

        // 修改成新密码
        $member->password = $this->hash($newPassword);
        if ($member->save()) {
            $result['code'] = 1;
        } else {
            $result['message'] = '修改失败';
        }

        return $result;
    }

    /**
     * 获取下级代理商
     * @param int $id
     * @param bool $includeSelf 是否包含自己，默认否
     * @param bool $hasPassword 字段里是包含password字段，默认否
     * @return array
     */
    public function findChildren($id, $includeSelf = false, $hasPassword = false)
    {
        $member = static::findById($id);
        if (!$member) {
            return [];
        }

        if ($member['ref_path']) {
            $like = $member['ref_path'] . $id . ',';
        } else {
            $like = $id . ',';
        }

        $sql = "SELECT * FROM " . self::tableName() . " WHERE ref_path LIKE '" . $like . "%' ORDER BY id ASC";
        $list = self::getDb()->createCommand($sql)->queryAll();
        if ($includeSelf) {
            $list[] = $member;
        }
        foreach ($list as $k => $v) {
            if (!$hasPassword) {
                unset($list[$k]['password']);
            }
        }
        return $list;
    }

    /**
     * 获取下级代理商，包含自己
     * @param int $id
     * @return array
     */
    public function findChildrenIncludeSelf($id)
    {
        return $this->findChildren($id, true);
    }

    public static function findParents($id)
    {
        $member = static::find()->where(['id' => $id])->asArray()->limit(1)->one();
        if ($member == null) {
            return [];
        }
        $result = [$member];
        $temp = static::findParents($member['ref_id']);
        if (!empty($temp)) {
            $result = array_merge($temp, $result);
        }
        return $result;
    }

    /**
     * @param string $email
     * @return bool
     */
    public static function checkEmailExist($email)
    {
        return static::find()->where(['username' => $email])->exists();
    }

    /**
     * @param string $idNo
     * @return bool
     */
    public static function checkIdNoExist($idNo)
    {
        return static::find()->where(['id_no' => $idNo, 'type' => 2])->exists();
    }

    /**
     * 名下代理
     * @param array $post
     * @return array
     */
    public function getView($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        $member_id = $post['member_id'];
        $id = isset($post['id']) ? $post['id'] : '';

        if (!$id) {
            $result['message'] = '参数错误';
            return $result;
        }

        $ib = static::findById($id);
        if (!$ib) {
            $result['message'] = '参数错误.';
            return $result;
        }

        $ibs = $this->findChildrenIncludeSelf($member_id);
        $id_arr = array_column($ibs, 'id');
        if (!in_array($id, $id_arr)) {
            $result['message'] = '您没有权限查看这个页面';
            return $result;
        }

        $logins = array_map('trim', explode(',', trim($ib['logins'])));
        $mt4Users = Mt4Users::find()->where(['and', ['in', 'LOGIN', $logins]])->asArray()->all();
        foreach ($mt4Users as $k => $v) {
            $mt4Users[$k]['BALANCE'] = round($v['BALANCE'], 2);
        }
        $mt4Users2 = [];
        foreach ($mt4Users as $k => $v) {
            $mt4Users2[$v['LOGIN']] = $v;
        }
        $mt4Users = $mt4Users2;

        // 总入金和总出金
        $mt4Trades = new Mt4Trades();
        $depositSum = $mt4Trades->getDepositSumByLogins($logins);
        $withdrawSum = $mt4Trades->getWithdrawSumByLogins($logins);

        // 直属MT4账户总数
        $directlyUserCount = UserMember::directlyUserCount($ib['id']);

        $depositSumByDay = $mt4Trades->getDepositSumByDayByLogins($logins);

        $equity = 0;
        foreach ($mt4Users as $k => $v) {
            $equity += $v['EQUITY'];
        }

        $data = [
            'ib' => $ib,
            'mt4Users' => $mt4Users,
            'equity' => $equity,
            'directlyUserCount' => $directlyUserCount,
            'depositSum' => round($depositSum, 5),
            'withdrawSum' => round($withdrawSum, 5),
            'depositSumByDay' => $depositSumByDay,
        ];

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

        return $result;
    }

    /**
     * 根据类型统计用户数量
     * @param int $type
     * @return int
     */
    public static function countByType($type)
    {
        $type = (int) $type;
        return static::find()->where(['type' => $type])->count();
    }

    /**
     * 统计XTrader用户数量
     * @return int
     */
    public static function xTraderCount()
    {
        return static::countByType(static::MEMBER_TYPE_USER);
    }

    /**
     * 统计XBroker用户数量
     * @return int
     */
    public static function xBokerCount()
    {
        return static::countByType(static::MEMBER_TYPE_IB);
    }

    /**
     * 统计后台用户数量
     * @return int
     */
    public static function adminCount()
    {
        return static::countByType(static::MEMBER_TYPE_ADMIN);
    }

    /**
     * 后台代理商列表数据
     * @param array $post
     * @return array
     */
    public function getAdminIbList($post)
    {
        $result = $this->getAdminList($post, static::MEMBER_TYPE_IB);
        if ($result['code'] == 0) {
            return $result;
        }

        // 数据处理
        $data = $result['data']['data'];
        if ($data) {
            $refIds = array_column($data, 'ref_id');
            $ibList = static::find()->select(['id', 'name'])->where(['in', 'id', $refIds])->asArray()->all();

            foreach ($data as $k => $v) {
                $data[$k]['IBNAME'] = '';
                foreach ($ibList as $k2 => $v2) {
                    if ($v['ref_id'] == $v2['id']) {
                        $data[$k]['IBNAME'] = $v2['name'];
                        break;
                    }
                }
            }

            $result['data']['data'] = $data;
        }

        return $result;
    }

    /**
     * 后台列表数据
     * @param array $post
     * @param int $type
     * @return array
     */
    public function getAdminList($post, $type)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        if (!in_array($type, [static::MEMBER_TYPE_USER, static::MEMBER_TYPE_IB, static::MEMBER_TYPE_ADMIN])) {
            return $result;
        }

        $id = isset($post['id']) ? (int) $post['id'] : 0;
        $order = isset($post['order']) ? strtolower($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;

        $where = ['and', ['=', 'type', $type]];

        // 名下客户
        if ($id) {
            $ibs = $this->findChildrenIncludeSelf($id);
            $id_arr = array_column($ibs, 'id');
            $where[] = ['in', 'id', $id_arr];
        }

        // 搜索
        if ($search) {
            if (filter_var($search, FILTER_VALIDATE_IP) !== false) {
                $where[] = ['=', 'ip', $search];
            } elseif (is_numeric($search)) {
                // 用户名也可能是数字
                $where[] = [
                    'or',
                    ['like', 'logins', $search],
                    ['like', 'username', $search],
                    ['like', 'name', $search],
                ];
            } else {
                $where[] = [
                    'or',
                    ['like', 'username', $search],
                    ['like', 'name', $search],
                ];
            }
        }

        // 排序
        $allowOrderColumn = ['id', 'ib_old_login_name', 'logins', 'username', 'name', 'mobile', 'is_enable', 'in_time'];
        if (in_array($order, $allowOrderColumn) && in_array($orderBy, ['asc', 'desc'])) {
            if ($orderBy == 'asc') {
                $orderCondition = [$order => SORT_ASC];
            } else {
                $orderCondition = [$order => SORT_DESC];
            }
        } else {
            $orderCondition = ['id' => SORT_DESC];
        }

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

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

        if ($count) {
            foreach ($list as $k => $v) {
                unset($list[$k]['password']);
            }
        }

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

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

        return $result;
    }

    /**
     * 添加代理商
     * @param array $post
     * @return array
     */
    public function addAdminIb($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        // 验证
        $ref = static::find()->where(['id' => $post['ref_id']])->limit(1)->asArray()->one();
        if (!$ref || $ref['type'] != static::MEMBER_TYPE_IB) {
            $result['message'] = '上级代理不存在';
            return $result;
        }
        $ib_old_login_name = static::find()->select(['ib_old_login_name'])->where(['ib_old_login_name' => $post['ib_old_login_name']])->limit(1)->asArray()->scalar();
        if ($ib_old_login_name) {
            $result['message'] = '用户名已存在';
            return $result;
        }
        $username = static::find()->select(['username'])->where(['username' => $post['username']])->limit(1)->asArray()->scalar();
        if ($username) {
            $result['message'] = '电子邮箱已存在';
            return $result;
        }

        $idno = static::find()->select(['id_no'])->where(['id_no' => $post['id_no']])->limit(1)->asArray()->scalar();
        if ($idno) {
            $result['message'] = '身份证已存在';
            return $result;
        }

        // 字段数据处理
        $attributes = $post;
        if (!empty($attributes['password'])) {
            $attributes['password'] = $this->hash($attributes['password']);
        }
        if (!empty($attributes['id_no'])) {
            $idcard = Idcard::getInstance();
            if ($idcard->isChinaIDCard($attributes['id_no'])) {
                $attributes['birthday'] = $idcard->birthday;
                $attributes['gender'] = $idcard->getChinaIDCardSex($attributes['id_no']) === '男' ? 1 : 2;
            } else {
                $attributes['id_no'] = '';
            }
        }
        $attributes['ref_path'] = $ref['ref_path']  . $attributes['ref_id'] . ',';
        $attributes['in_time'] = round(microtime(true) * 1000);
        $attributes['type'] = static::MEMBER_TYPE_IB;

        $this->setAttributes($attributes);
        if ($this->save()) {
            $result['code'] = 1;
            // 发送短信
            if (!empty($post['isSendMail']) && $post['isSendMail'] === 'on') {

            }
        } else {
            $errors = $this->getFirstErrors();
            $error = reset($errors);
            $result['message'] = !empty($error) ? $error : '保存失败';
        }

        return $result;
    }

    /**
     * 添加用户
     * @param array $post
     * @return array
     */
    public function addAdminMember($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        $username = static::find()->select(['username'])->where(['username' => $post['username']])->limit(1)->asArray()->scalar();
        if ($username) {
            $result['message'] = '电子邮箱已存在';
            return $result;
        }

        $idno = static::find()->select(['id_no'])->where(['id_no' => $post['id_no']])->limit(1)->asArray()->scalar();
        if ($idno) {
            $result['message'] = '身份证已存在';
            return $result;
        }

        // 字段数据处理
        $attributes = $post;
        if (!empty($attributes['password'])) {
            $attributes['password'] = $this->hash($attributes['password']);
        }
        if (!empty($attributes['id_no'])) {
            $idcard = Idcard::getInstance();
            if ($idcard->isChinaIDCard($attributes['id_no'])) {
                $attributes['birthday'] = $idcard->birthday;
                $attributes['gender'] = $idcard->getChinaIDCardSex($attributes['id_no']) === '男' ? 1 : 2;
            } else {
                $attributes['id_no'] = '';
            }
        }
        $attributes['in_time'] = round(microtime(true) * 1000);
        $attributes['type'] = static::MEMBER_TYPE_USER;

        $this->setAttributes($attributes);
        if ($this->save()) {
            $result['code'] = 1;
            // 发送短信
            if (!empty($post['isSendMail']) && $post['isSendMail'] === 'on') {

            }
        } else {
            $errors = $this->getFirstErrors();
            $error = reset($errors);
            $result['message'] = !empty($error) ? $error : '保存失败';
        }

        return $result;
    }

    /**
     * 后台代理商详情
     * @param array $post
     * @return array
     */
    public function getAdminIbView($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        $id = isset($post['id']) ? intval($post['id']) : 0;
        if (!$id) {
            $result['message'] = '参数错误';
            return $result;
        }

        $member = static::findById($id);
        if (!$member) {
            $result['message'] = '该代理商不存在';
            return $result;
        }
        $member['birthday'] = date('Y-m-d', strtotime($member['birthday']));
        $member['in_time'] = date('Y-m-d H:i:s', $member['in_time'] / 1000);

        $signins = Signin::find()->where(['member_id' => $id])->orderBy(['id' => SORT_DESC])->limit(10)->asArray()->all();
        foreach ($signins as $k => $v) {
            $signins[$k]['login_time'] = date('Y-m-d H:i:s', $v['in_time'] / 1000);
        }
        
        $bank_info = MemberBankInfo::find()->where(['member_id' => $id])->limit(1)->asArray()->one();

        $data = [
            'member' => $member,
            'signins' => $signins,
            'bank_info' => $bank_info,
        ];

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

        return $result;
    }

    /**
     * 后台管理员列表数据
     * @param array $post
     * @return array
     */
    public function getAdminAdminList($post)
    {
        $result = $this->getAdminList($post, static::MEMBER_TYPE_ADMIN);
        if ($result['code'] == 0) {
            return $result;
        }

        // 数据处理
        $data = $result['data']['data'];
        if ($data) {
            $ids = array_column($data, 'id');
            $signs = Signin::find()->select(['member_id', 'ip'])->where(['in', 'member_id', $ids])->orderBy(['id' => SORT_DESC])->asArray()->all();

            foreach ($data as $k => $v) {
                $data[$k]['last_login_ip'] = '';
                foreach ($signs as $k2 => $v2) {
                    if ($v['id'] == $v2['member_id']) {
                        $data[$k]['last_login_ip'] = $v2['ip'];
                        break;
                    }
                }
            }

            $result['data']['data'] = $data;
        }

        return $result;
    }

    /**
     * 后台添加管理员
     * @param array $post
     * @return array
     */
    public function addAdminAdmin($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        // 验证
        $username = static::find()->select(['username'])->where(['username' => $post['username']])->limit(1)->asArray()->scalar();
        if ($username) {
            $result['message'] = '用户名已存在';
            return $result;
        }
        $mobile = static::find()->select(['mobile'])->where(['mobile' => $post['mobile']])->limit(1)->asArray()->scalar();
        if ($mobile) {
            $result['message'] = '手机号已存在';
            return $result;
        }

        // 字段数据处理
        $attributes = $post;
        if (!empty($attributes['password'])) {
            $attributes['password'] = $this->hash($attributes['password']);
        }
        $attributes['in_time'] = round(microtime(true) * 1000);
        $attributes['type'] = static::MEMBER_TYPE_ADMIN;

        $this->setAttributes($attributes);
        if ($this->save()) {
            $result['code'] = 1;
        } else {
            $errors = $this->getFirstErrors();
            $error = reset($errors);
            $result['message'] = !empty($error) ? $error : '保存失败';
        }

        return $result;
    }

    /**
     * 后台编辑管理员
     * @param array $post
     * @return array
     */
    public function editAdminAdmin($post)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        // 验证
        $memberModel = static::find()->where(['id' => $post['id'], 'type' => static::MEMBER_TYPE_ADMIN])->limit(1)->one();
        if (!$memberModel) {
            $result['message'] = '该管理员不存在';
            return $result;
        }
        $mobile = static::find()->select(['mobile'])->where(['mobile' => $post['mobile']])->limit(1)->asArray()->scalar();
        if ($mobile && $mobile != $memberModel['mobile']) {
            $result['message'] = '手机号已存在';
            return $result;
        }

        // 字段数据处理
        $attributes = $post;
        if (!empty($attributes['password'])) {
            $attributes['password'] = $this->hash($attributes['password']);
        } else {
            // 为空则不修改密码
            unset($attributes['password']);
        }
        $attributes['in_time'] = round(microtime(true) * 1000);

        $memberModel->setAttributes($attributes);
        if ($memberModel->save()) {
            $result['code'] = 1;
        } else {
            $errors = $memberModel->getFirstErrors();
            $error = reset($errors);
            $result['message'] = !empty($error) ? $error : '保存失败';
        }

        return $result;
    }
    
    /**
     * 删除用户
     * @param int $id
     * @return bool
     */
    public static function deleteById($id)
    {
        $result = ['code' => 0, 'data' => [], 'message' => ''];

        $member = static::find()->where(['id' => $id])->one();
        if ($member == null) {
            $result['message'] = '用户不存在';
            return $result;
        }
        if ($member->type == 2) {
            $id = $member->id;
            $agents = static::find()->where(['ref_id' => $id])->asArray()->all();
            if ($agents) {
                $result['message'] = '代理有发展下级， 不能删除';
                return $result;
            }
        }
        $transaction = static::getDb()->beginTransaction();
        try {
            $member->delete();
            Deposit::deleteAll(['member_id' => $member->id]);
            Withdraw::deleteAll(['member_id' => $member->id]);
            ModifyLever::deleteAll(['member_id' => $member->id]);
            NoticeRead::deleteAll(['member_id' => $member->id]);
            Signin::deleteAll(['member_id' => $member->id]);
            Transfer::deleteAll(['member_id' => $member->id]);
            $transaction->commit();
            $result['code'] = 1;
            $result['message'] = '删除成功';
            return $result;
        } catch (\Exception $e) {
            $transaction->rollBack();
        }
        $result['message'] = '删除成功';
        return $result;
    }

    /**
     * @param int $id
     * @return array
     */
    public function findDirectlyChildren($id)
    {
        $list = static::find()->where(['ref_id' => $id])->asArray()->all();
        $result = $list;
        foreach ($list as $k => $v) {
            $sub_result = static::findDirectlyChildren($v['id']);
            if ($sub_result) {
                $result = array_merge($result, $sub_result);    
            }
        }
        return $result;
    }

    /**
     * 是否为同名账户，判断身份证号，只能转入XTrader
     * @param int $fromLogin
     * @param int $toLogin
     * @param int $type
     * @return array
     */
    public static function isSameAccount($fromLogin, $toLogin, $type)
    {
        $result = ['code' => 0, 'data' => [], 'message' => '转出账户和转入账户不是同名账户'];
        
        $fromLogin = (int) $fromLogin;
        $toLogin = (int) $toLogin;
        $type = (int) $type;
        
        if ($fromLogin == $toLogin) {
            $result['message'] = '转出账户和转入账户相同';
            return $result;
        }

        $fromMember = static::findByLogin($fromLogin, $type);
        $toMember = static::findByLogin($toLogin, static::MEMBER_TYPE_USER);

        if (!$fromMember) {
            $result['message'] = '转出账户不存在';
            return $result;
        }
        
        if (!$toMember) {
            $result['message'] = '转入账户不存在';
            return $result;
        }
        
        // 按身份证号判断
        if ($fromMember['id_no'] && $toMember['id_no'] && strtoupper($fromMember['id_no']) == strtoupper($toMember['id_no'])) {
            $result['code']  = 1;
            return $result;
        }

        return $result;
    }

}