ngds/common/models/UserRelation.php

<?php

namespace common\models;

use common\components\ActiveRecord;
use common\helpers\Cache;
use common\helpers\NetPoint;
use common\helpers\user\Info;
use Yii;
use yii\data\Pagination;
use yii\helpers\Json;

/**
 * This is the model class for table "{{%USER_RELATION}}".
 *
 * @property string $ID
 * @property string $USER_ID 会员ID
 * @property string $PARENT_UID 相对上级会员ID
 * @property string $TOP_UID 顶端会员ID
 * @property int $TOP_DEEP 距离顶端会员深度
 * @property int $PARENT_UIDS 所有的上级ID
 * @property int $CREATED_AT 创建时间
 * @property int $UPDATED_AT 更新时间
 */
class UserRelation extends \common\components\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{%USER_RELATION_NEW}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['USER_ID', 'PARENT_UID', 'TOP_UID', 'TOP_DEEP', 'CREATED_AT'], 'required'],
            [['TOP_DEEP', 'CREATED_AT', 'UPDATED_AT'], 'integer'],
            [['ID', 'USER_ID', 'PARENT_UID', 'TOP_UID'], 'string', 'max' => 32],
            [['ID'], 'unique'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'ID' => 'ID',
            'USER_ID' => '会员ID',
            'PARENT_UID' => '相对上级会员ID',
            'TOP_UID' => '顶端会员ID',
            'TOP_DEEP' => '距离顶端会员深度',
            'PARENT_UIDS' => '所有的上级ID',
            'CREATED_AT' => '创建时间',
            'UPDATED_AT' => '更新时间',
        ];
    }


    /**
     * 获取一层下级数量
     * @param $userId
     * @return int|string
     */
    public static function firstFloorChildNum($userId){
        return intval(UserInfo::find()->where('REC_UID=:REC_UID', [':REC_UID'=>$userId])->count());
    }

    /**
     * 分页获取上级会员
     * @param $userId
     * @param $offset
     * @param $limit
     * @param string $orderBy
     * @param bool $isSlaves
     * @param string $db
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getParentsWithOffset($userId, $offset, $limit, $orderBy = 'DESC', $isSlaves = false, $db = 'db'){
        $sql = "SELECT t2.USER_ID
        FROM
            (
                SELECT 
                @r AS _id,
                (SELECT @r := PARENT_UID FROM AR_USER_RELATION_NEW WHERE USER_ID = _id) AS PARENT_UID,
                @l := @l + 1 AS lvl
                FROM
                (SELECT @r := '".$userId."', @l := 0) vars, AR_USER_RELATION_NEW AS h
                WHERE @r <> 0 
            ) t1
        JOIN AR_USER_RELATION_NEW t2
        ON t1._id = t2.USER_ID AND t1._id != '".$userId."';";
        $parentUidsArr = \Yii::$app->db->createCommand($sql)->queryAll();
        if (empty($parentUidsArr)) {
            return [];
        }
        $allUserIds = array_column($parentUidsArr, 'USER_ID');
        if ($orderBy === 'ASC') {
            $allUserIds = array_reverse($allUserIds);
        }
        if(empty($allUserIds)) return [];
        $topDeep = count($allUserIds);
        $pageParentUids = array_slice($allUserIds, $offset, $limit);
        unset($allUserIds);
        $parentList = [];
        foreach ($pageParentUids as $parentUid) {
            // $parentRelationInfo = static::find($isSlaves, $db)
            // ->select(['TOP_DEEP'])
            // ->where('USER_ID=:USER_ID', ['USER_ID'=>$parentUid])
            // ->asArray()
            // ->one();
            $parentList[] = [
                'USER_ID' => $userId,
                'TOP_DEEP' => $topDeep,
                'PARENT_UID' => $parentUid,
                //'PARENT_DEEP' => $parentRelationInfo['TOP_DEEP'],
            ];

            unset($parentUid, $parentRelationInfo);
        }
        unset($pageParentUids, $userRelationInfo);

        return $parentList;
    }

    // /**
    //  * 分页获取上级会员
    //  * @param $userId
    //  * @param $offset
    //  * @param $limit
    //  * @param string $orderBy
    //  * @param bool $isSlaves
    //  * @param string $db
    //  * @return array|\yii\db\ActiveRecord[]
    //  */
    // public static function getParentsWithOffset($userId, $offset, $limit, $orderBy = 'DESC', $isSlaves = false, $db = 'db'){
    //     $userRelationInfo = static::find($isSlaves, $db)->select(['TOP_DEEP', 'PARENT_UIDS'])->where('USER_ID=:USER_ID', ['USER_ID'=>$userId])->asArray()->one();
    //     if( !$userRelationInfo ) return [];

    //     $parentUidsStr = $userRelationInfo['PARENT_UIDS'];
    //     if( !$parentUidsStr ) return [];
    //     $parentUidsArr = explode(',', $parentUidsStr);
    //     unset($parentUidsStr);
    //     if( $orderBy === 'DESC' ) {
    //         $parentUidsArr = array_reverse($parentUidsArr);
    //     }
    //     if( !$parentUidsArr ) return [];

    //     $pageParentUids = array_slice($parentUidsArr, $offset, $limit);
    //     unset($parentUidsArr);
    //     $parentList = [];
    //     foreach ($pageParentUids as $parentUid) {
    //         $parentRelationInfo = static::find($isSlaves, $db)->select(['TOP_DEEP'])->where('USER_ID=:USER_ID', ['USER_ID'=>$parentUid])->asArray()->one();
    //         $parentList[] = [
    //             'USER_ID' => $userId,
    //             'TOP_DEEP' => $userRelationInfo['TOP_DEEP'],
    //             'PARENT_UID' => $parentUid,
    //             'PARENT_DEEP' => $parentRelationInfo['TOP_DEEP'],
    //         ];

    //         unset($parentUid, $parentRelationInfo);
    //     }
    //     unset($pageParentUids, $userRelationInfo);

    //     return $parentList;
    // }

    /**
     * 分页获取上级会员结算库
     * @param $userId
     * @param $callbackFunc
     * @param $limit
     * @param int $offset
     * @param string $orderBy
     */
    public static function getParentsWithOffsetFromDbCalc($userId, $callbackFunc, $limit, $offset = 0, $orderBy='DESC'){
        $allData = self::getParentsWithOffset($userId, $offset, $limit, $orderBy, false, 'dbCalc');
        if($allData){
            foreach($allData as $data){
                $callbackFunc($data);
                unset($data);
            }
            unset($allData);
            self::getParentsWithOffsetFromDbCalc($userId, $callbackFunc, $limit, $offset + $limit);
        }
    }

    /**
     * 获取上一级父级的会员信息
     * @param $userId
     * @return array|null
     */
    public static function getFirstParentUserInfo($userId){
        $oneUserInfo = UserInfo::findOneAsArray(['USER_ID'=>$userId]);
        if($oneUserInfo['REC_UID']){
            return UserInfo::findOneAsArray(['USER_ID'=>$oneUserInfo['REC_UID']]);
        } else {
            return null;
        }
    }

    /**
     * 查看所传父级节点会员是不是所传会员的父级
     * @param $userId
     * @param $parentUserId
     * @param $periodNum
     * @return bool
     */
    public static function isParentUser($userId, $parentUserId, $periodNum = null){
        $table = self::getTableNameFromPeriod($periodNum);
        $db = $table['db'];
        $tableName = $table['tableName'];
        $data = $db->createCommand("SELECT PARENT_UIDS FROM {$tableName} WHERE USER_ID=:USER_ID")->bindValues([':USER_ID'=>$userId])->queryOne();
        if( !$data ) {
            unset($table, $db, $tableName, $data);
            return false;
        }
        $parentListArr = explode(',', $data['PARENT_UIDS']);
        unset($table, $db, $tableName, $data);
        $existStatus =  in_array($parentUserId, $parentListArr);
        unset($parentListArr);

        return $existStatus;
    }

    /**
     * 获取相交于父级的深度
     * @param $userId
     * @param $parentUserId
     * @param bool $isSlave
     * @return mixed|null
     */
    public static function deepWithParent($userId, $parentUserId, $isSlave = false){
        $userRelationInfo = static::find($isSlave)->select(['TOP_DEEP'])->where('USER_ID=:USER_ID', [':USER_ID'=>$userId])->asArray()->one();
        if( !$userRelationInfo ) return null;
        $parentRelationInfo = static::find($isSlave)->select(['TOP_DEEP'])->where('PARENT_UID=:PARENT_UID', [':PARENT_UID'=>$parentUserId])->asArray()->one();
        if( !$parentRelationInfo ) return null;


        return $parentRelationInfo['TOP_DEEP'] - $userRelationInfo['TOP_DEEP'];
    }

    /**
     * 获取子会员节点带着总深度和循环的子节点层级关系
     * @param $userId
     * @param $deep
     * @param int $loopedDeep
     * @param null $periodNum
     * @return mixed
     * @throws \yii\base\Exception
     * @throws \yii\db\Exception
     */
    public static function getChildrenWithDeepAndLayer($userId, $deep, $loopedDeep = 1, $periodNum=null){
        $allData = self::getFirstFloorChildrenFromPeriod($userId,'TOP_DEEP ASC', $periodNum);
        if($allData){
            $decLevelConfig = Cache::getDecLevelConfig();
            $empLevelConfig = Cache::getEmpLevelConfig();
            $crownLevelConfig = Cache::getStarCrownLevelConfig();
            foreach($allData as $key=>$data){
                // 获取用户的基本信息
                $baseInfo = Info::baseInfo($data['USER_ID'], $periodNum);
                $allData[$key] = array_merge($data, [
                    'USER_NAME' => $baseInfo['USER_NAME'],
                    'TOP_RELATION_DEEP' => $data['TOP_DEEP'],
                    'REAL_NAME' => $baseInfo['REAL_NAME'],
                    'DEC_LV_NAME' => $decLevelConfig[$baseInfo['DEC_LV']]['LEVEL_NAME'],
                    'EMP_LV_NAME' => isset($empLevelConfig[$baseInfo['EMP_LV']])?$empLevelConfig[$baseInfo['EMP_LV']]['LEVEL_NAME']:'',
                    'CROWN_LV_NAME' => isset($crownLevelConfig[$baseInfo['CROWN_LV']])?$crownLevelConfig[$baseInfo['CROWN_LV']]['LEVEL_NAME']:'',
                    'PERIOD_AT' => $baseInfo['PERIOD_AT'],
                ]);
                // 获取字节点数量
                $childNum = self::firstFloorChildNumFromPeriod($data['USER_ID'], $periodNum);
                if($childNum > 0 && $loopedDeep < $deep){
                    $child = self::getChildrenWithDeepAndLayer($data['USER_ID'], $deep, $loopedDeep + 1, $periodNum);
                    $leaf = false;
                    $icon = 'el-icon-user-solid';
                }
                elseif($childNum > 0){
                    $child = null;
                    $leaf = false;
                    $icon = 'el-icon-user-solid';
                }
                else {
                    $child = null;
                    $leaf = true;
                    $icon = 'el-icon-user';
                }
                $allData[$key]['children'] = $child;
                $allData[$key]['leaf'] = $leaf;
                $allData[$key]['icon'] = $icon;
                $allData[$key]['isExpanded'] = false;
                $allData[$key]['displayNone'] = 'display-none';
            }
        }
        return $allData;
    }

    /**
     * 通过期数获取应该查询哪个表和库
     * @param $periodNum
     * @return array
     */
    public static function getTableNameFromPeriod($periodNum = null){
        $db = self::getDb();
        $tableName = self::tableName();
//        if($periodNum !== null){
//            // 获取当前期数
//            $period = Period::instance();
//            $nowPeriodNum = $period->getNowPeriodNum();
//            if($nowPeriodNum != $periodNum){
//                // 从备份库里找到期数对应的网络
//                if(ActiveRecord::isExistsTable('{{%USER_RELATION_'.$periodNum.'}}', 'dbNetPoint')){
//                    $db = Yii::$app->dbNetPoint;
//                    $tableName = '{{%USER_RELATION_'.$periodNum.'}}';
//                }
//            }
//        }
        return [
            'db' => $db,
            'tableName' => $tableName,
        ];
    }

    /**
     * 获取直推子会员
     * @param $userId
     * @param string $orderBy
     * @param null $periodNum
     * @return mixed
     */
    public static function getFirstFloorChildrenFromPeriod($userId, $orderBy='TOP_DEEP ASC,ID ASC', $periodNum=null){
        $table = self::getTableNameFromPeriod($periodNum);
        $db = $table['db'];
        $tableName = $table['tableName'];
        return $db->createCommand("SELECT USER_ID,PARENT_UID,TOP_DEEP FROM {$tableName} WHERE PARENT_UID=:PARENT_UID ORDER BY {$orderBy}")->bindValues([':PARENT_UID'=>$userId])->queryAll();
    }


    /**
     * 获取指定层数的这一层的会员以分页的方式
     * @param $userId
     * @param $deep
     * @param null $periodNum
     * @return array
     */
    public static function getChildrenInDeepFromPeriodWithPage($userId, $deep, $periodNum=null,$params=[]){
        $pageSize = method_exists(\Yii::$app->request,'get')?\Yii::$app->request->get('pageSize', \Yii::$app->params['pageSize']):\Yii::$app->params['pageSize'];
        if(isset($params['pageSize'])) $pageSize=$params['pageSize'];

        $page = null;
        if( isset($params['page']) ) $page=$params['page'];

        $orderBy = 'TOP_DEEP ASC,ID ASC';
        if( isset($params['orderBy']) ) $orderBy = $params['orderBy'];
        $table = self::getTableNameFromPeriod($periodNum);
        $db = $table['db'];
        $tableName = $table['tableName'];
        $userRecInfo = $db->createCommand("SELECT TOP_DEEP FROM $tableName WHERE USER_ID=:USER_ID")->bindValues([':USER_ID'=>$userId])->queryOne();
        $userDeep = $userRecInfo['TOP_DEEP'];
        $totalCountSql = "SELECT COUNT(ID) AS TOTAL_COUNT FROM $tableName WHERE INSTR(`PARENT_UIDS`,'{$userId}')>0 AND TOP_DEEP<=:TOP_DEEP ORDER BY {$orderBy}";
        $totalCount = $db->createCommand($totalCountSql)->bindValues([ ':TOP_DEEP'=>$userDeep + $deep])->queryOne();
        $count = $totalCount['TOTAL_COUNT'];
        $pagination = new Pagination(['totalCount' => $count]);
        if( $page !== null ) {
            $pagination->setPage($page);
        }
        $pagination->setPageSize($pageSize);
        $offset = $pagination->offset;
        $limit = $pagination->limit;
//        $end = $offset + $limit;
        $listSql = "SELECT * FROM $tableName WHERE INSTR(`PARENT_UIDS`,'{$userId}')>0 AND TOP_DEEP<=:TOP_DEEP ORDER BY {$orderBy} LIMIT {$limit} OFFSET {$offset}";
        $lists = $db->createCommand($listSql)->bindValues([ ':TOP_DEEP'=>$userDeep + $deep])->queryAll();
        return [
            'list' => $lists ? $lists : [],
            'pagination' => $pagination,
            'currentPage'=>$pagination->page,
            'totalPages'=>$pagination->pageCount,
            'totalCount' => $pagination->totalCount,
            'pageSize' => $pagination->pageSize,
        ];
    }

    /**
     * 获取一层下级数量从期数
     * @param $userId
     * @param null $periodNum
     * @return int
     */
    public static function firstFloorChildNumFromPeriod($userId, $periodNum=null){
        $table = self::getTableNameFromPeriod($periodNum);
        $db = $table['db'];
        $tableName = $table['tableName'];
        $count = $db->createCommand("SELECT COUNT(ID) AS ID_COUNT FROM $tableName WHERE PARENT_UID=:PARENT_UID")->bindValues([':PARENT_UID'=>$userId])->queryOne();
        return intval($count['ID_COUNT']);
    }


    /**
     * 从缓存中获取会员的全部父级(主要用于结算时的处理，能够提高效率不去查库)
     * @param $userId
     * @return array|mixed
     */
    public static function getAllParentsFromRedis($userId){
        $key = Cache::USER_RELATION_PARENTS;
        $data = Yii::$app->redis->hget($key, $userId);
        if(!$data){
            $data = [];
            self::getParentsWithOffsetFromDbCalc($userId, function($oneData) use(&$data){
                $data[] = $oneData;
            }, 100);
            $data = Json::encode($data);
            Yii::$app->redis->hset($key, $userId, $data);
        }
        return $data ? Json::decode($data) : [];
    }
}