ngds/common/models/UserNetwork.php

<?php

namespace common\models;

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

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

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['USER_ID', 'PARENT_UID', 'LOCATION', 'TOP_UID', 'TOP_DEEP', 'CREATED_AT'], 'required'],
            [['TOP_DEEP', 'PERIOD_NUM', 'RELATIVE_LOCATION', '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',
            'LOCATION_TAG' => '网体标记',
            'RELATIVE_LOCATION' => '相对父级的区位',
            '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('CON_UID=:CON_UID AND DELETED=0', [':CON_UID'=>$userId])->count());
    }

    /**
     * 使用父级parent_uid,分页获取上级会员
     * @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_NETWORK_NEW WHERE USER_ID = _id) AS PARENT_UID,
                @l := @l + 1 AS lvl
                FROM
                (SELECT @r := '".$userId."', @l := 0) vars, AR_USER_NETWORK_NEW AS h
                WHERE @r <> 0 
            ) t1
        JOIN AR_USER_NETWORK_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) {
            try {
                $parentNetInfo = static::find($isSlaves, $db)
                ->select(['TOP_DEEP', 'RELATIVE_LOCATION', 'LOCATION_TAG', 'PARENT_UID'])
                ->where('USER_ID=:USER_ID', ['USER_ID'=>$parentUid])
                ->asArray()
                ->one();
                $location = empty($parentNetInfo['PARENT_UID']) ? $parentNetInfo['LOCATION_TAG'] : $parentNetInfo['RELATIVE_LOCATION'];
                $parentList[] = [
                    'USER_ID' => $userId,
                    'TOP_DEEP' => $topDeep,
                    'PARENT_UID' => $parentUid,
                    'PARENT_DEEP' => $parentNetInfo['TOP_DEEP'],
                    'LOCATION' => $location,
                ];
            } catch (Exception $e) {
                $file_name = date('Y-m-d', time()).'_usernetwork.php_error.log';
                file_put_contents($file_name, var_export([
                    '$parentUid' => $parentUid,
                    '$userId' => $userId,
                    'error' => $e->getMessage()
                ],true), FILE_APPEND);
            }

            unset($parentUid, $parentNetInfo);
        }
        unset($pageParentUids, $userNetInfo);

        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'){
    //     $userNetInfo = static::find($isSlaves, $db)->select(['TOP_DEEP', 'PARENT_UIDS', 'LOCATION_TAG'])->where('USER_ID=:USER_ID', ['USER_ID'=>$userId])->asArray()->one();
    //     if( !$userNetInfo ) return [];

    //     $parentUidsStr = $userNetInfo['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) {
    //         try {
    //             $parentNetInfo = static::find($isSlaves, $db)->select(['TOP_DEEP', 'LOCATION_TAG'])->where('USER_ID=:USER_ID', ['USER_ID'=>$parentUid])->asArray()->one();
    //             $parentList[] = [
    //                 'USER_ID' => $userId,
    //                 'TOP_DEEP' => $userNetInfo['TOP_DEEP'],
    //                 'PARENT_UID' => $parentUid,
    //                 'PARENT_DEEP' => $parentNetInfo['TOP_DEEP'],
    // //                'LOCATION_TAG' => $userNetInfo['LOCATION_TAG'],
    // //                'PARENT_LOCATION_TAG' => $parentNetInfo['LOCATION_TAG'],
    //                 'LOCATION' => substr($userNetInfo['LOCATION_TAG'], strlen($parentNetInfo['LOCATION_TAG']), 1),
    //             ];
    //         } catch (Exception $e) {
    //             $file_name = date('Y-m-d', time()).'_usernetwork.php_error.log';
    //             file_put_contents($file_name, var_export([
    //                 '$parentUid' => $parentUid,
    //                 '$userId' => $userId,
    //                 'error' => $e->getMessage()
    //             ],true), FILE_APPEND);
    //         }

    //         unset($parentUid, $parentNetInfo);
    //     }
    //     unset($pageParentUids, $userNetInfo);

    //     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['CON_UID']){
            return UserInfo::findOneAsArray(['USER_ID'=>$oneUserInfo['CON_UID']]);
        } else {
            return null;
        }
    }

    /**
     * 获取指定深度的子会员
     * @param $userId
     * @param $deep
     * @param string $orderBy
     * @return array
     */
    public static function getChildrenWithDeep($userId, $deep, $orderBy='TOP_DEEP ASC'){
        $userNetInfo = static::find()->select(['LOCATION_TAG', 'TOP_DEEP'])->where('USER_ID=:USER_ID', ['USER_ID'=>$userId])->asArray()->one();

        $childrenNetInfo = static::find()->select(['USER_ID', 'LOCATION_TAG', 'TOP_DEEP'])->where('LOCATION_TAG LIKE :LOCATION_TAG AND TOP_DEEP<=:TOP_DEEP', ['LOCATION_TAG'=>$userNetInfo['LOCATION_TAG'] . '%', 'TOP_DEEP'=>$userNetInfo['TOP_DEEP'] + $deep])->orderBy($orderBy)->asArray()->all();
        foreach ($childrenNetInfo as $key => $childNetInfo) {
            $childrenNetInfo[$key]['LOCATION'] = substr($childNetInfo['LOCATION_TAG'], strlen($userNetInfo['LOCATION_TAG']), 1);
            unset($key, $childNetInfo);
        }

        unset($userNetInfo);

        return $childrenNetInfo;
    }

    /**
     * 获取下一层会员的接点信息
     * @param $userId
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getFirstFloorChildren($userId) {
        $childrenNetInfo = static::find()->select(['USER_ID', 'RELATIVE_LOCATION', 'LOCATION_TAG', 'TOP_DEEP'])->where('PARENT_UID=:PARENT_UID', ['PARENT_UID'=>$userId])->asArray()->all();
        unset($userNetInfo);

        return $childrenNetInfo;
    }

    /**
     * 查看所传父级节点会员是不是所传会员的父级
     * @param $userId
     * @param $parentUserId
     * @param null $periodNum
     * @return bool
     * @throws \yii\db\Exception
     */
    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 $periodNum
     * @return mixed|null
     */
    public static function getLocation($userId, $parentUserId, $periodNum = null){
        $table = self::getTableNameFromPeriod($periodNum);
        $db = $table['db'];
        $tableName = $table['tableName'];
        $userNetInfo = $db->createCommand("SELECT LOCATION_TAG FROM $tableName WHERE USER_ID=:USER_ID")->bindValues([':USER_ID'=>$userId])->queryOne();
        if( !$userNetInfo || !isset($userNetInfo['LOCATION_TAG']) ) return 0;

        $parentNetInfo = $db->createCommand("SELECT LOCATION_TAG FROM $tableName WHERE USER_ID=:USER_ID")->bindValues([':USER_ID'=>$userId])->queryOne();
        if( !$parentNetInfo || !isset($parentNetInfo['LOCATION_TAG']) ) return 0;

        $location = substr($userNetInfo['LOCATION_TAG'], strlen($parentNetInfo['LOCATION_TAG']), 1);

        return $location ? $location : 0;
    }

    /**
     * 获取子会员节点带着总深度和循环的子节点层级关系
     * @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::getChildrenFromPeriod($userId, $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_NETWORK_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']:'',
//                    'MOBILE' => $baseInfo['MOBILE'],
                    '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';
                $allData[$key]['RELATIVE_LOCATION'] = $data['RELATIVE_LOCATION'] == 1 ? 'L' : 'R';
            }
        }
        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_NETWORK_'.$periodNum.'}}', 'dbNetPoint')){
//                    $db = Yii::$app->dbNetPoint;
//                    $tableName = '{{%USER_NETWORK_'.$periodNum.'}}';
//                }
//            }
//        }
        return [
            'db' => $db,
            'tableName' => $tableName,
        ];
    }

    /**
     * 获取指定深度的子会员从指定的期数中
     * @param $userId
     * @param $periodNum
     * @return array|\yii\db\DataReader
     * @throws \yii\db\Exception
     */
    public static function getChildrenFromPeriod($userId, $periodNum=null){
        $table = self::getTableNameFromPeriod($periodNum);
        $db = $table['db'];
        $tableName = $table['tableName'];
        return $db->createCommand("SELECT USER_ID,TOP_DEEP,RELATIVE_LOCATION FROM {$tableName} WHERE PARENT_UID=:PARENT_UID")->bindValues([':PARENT_UID'=>$userId])->queryAll();
    }

    /**
     * 获取指定层数的这一层的会员以分页的方式
     * @param $userId
     * @param $deep
     * @param null $periodNum
     * @param array $params
     * @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'];
        $userNetInfo = $db->createCommand("SELECT LOCATION_TAG,TOP_DEEP FROM {$tableName} WHERE USER_ID=:USER_ID")->bindValues([':USER_ID'=>$userId])->queryOne();
        $userDeep = $userNetInfo['TOP_DEEP'];
        $userLocationTag = $userNetInfo['LOCATION_TAG'];
        $totalCountSql = "SELECT COUNT(ID) AS TOTAL_COUNT FROM {$tableName} WHERE LOCATION_TAG LIKE :LOCATION_TAG AND TOP_DEEP<=:TOP_DEEP AND INSTR(`PARENT_UIDS`,'{$userId}')>0  ORDER BY {$orderBy}";
        $totalCount = $db->createCommand($totalCountSql)->bindValues([':LOCATION_TAG'=>$userLocationTag . '%', ':TOP_DEEP'=>$userDeep + $deep])->queryOne();
        $count = $totalCount['TOTAL_COUNT'];
        $pagination = new Pagination(['totalCount' => $count]);
        $pagination->setPageSize($pageSize);
        if( $page !== null ) {
            $pagination->setPage($page);
        }
        $offset = $pagination->offset;
        $limit = $pagination->limit;
//        $end = $offset + $limit;
        $listSql = "SELECT * FROM $tableName WHERE LOCATION_TAG LIKE :LOCATION_TAG AND TOP_DEEP<=:TOP_DEEP AND INSTR(`PARENT_UIDS`,'{$userId}')>0 ORDER BY {$orderBy} LIMIT {$limit} OFFSET {$offset}";
        $lists = $db->createCommand($listSql)->bindValues([':LOCATION_TAG'=>$userLocationTag . '%', ':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
     * @param null $deep
     * @return array
     */
    public static function getAllParentFromPeriodWithPage($userId, $periodNum=null, $deep=null){
        $pageSize = method_exists(\Yii::$app->request,'get')?\Yii::$app->request->get('pageSize', \Yii::$app->params['pageSize']):\Yii::$app->params['pageSize'];
        $table = self::getTableNameFromPeriod($periodNum);
        $db = $table['db'];
        $tableName = $table['tableName'];
        $sql = "SELECT * FROM {$tableName} WHERE USER_ID=:USER_ID";
        $oneData = $db->createCommand($sql)->bindValues([':USER_ID'=>$userId])->queryOne();
        $userDeep = $oneData['TOP_DEEP'];
        $parentUidsStr = $oneData['PARENT_UIDS'] ?? "";
        if( !$parentUidsStr ) return [];

        $parentUidsArr = explode(',', $parentUidsStr);
        $parentUidsFlip = array_reverse($parentUidsArr);
        if ($deep != null) {
            $parentUidsFlip = array_slice($parentUidsFlip, 0, $deep);
        }
        $count = count($parentUidsFlip);
        $pagination = new Pagination(['totalCount' => $count]);
        $pagination->setPageSize($pageSize);
        $offset = $pagination->offset;
        $limit = $pagination->limit;

        $parentUidList = array_slice($parentUidsFlip, $offset, $limit);
        $lists = [];
        foreach ($parentUidList as $parentUid) {
            $sql = "SELECT * FROM {$tableName} WHERE USER_ID=:USER_ID";
            $data = $db->createCommand($sql)->bindValues([':USER_ID' => $parentUid])->queryOne();
            if( !$data ) continue;

            $data['LOCATION'] = substr($oneData['LOCATION_TAG'], strlen($data['LOCATION_TAG']), 1);
            $lists[] = $data;
        }

        return [
            'list' => $lists ? $lists : [],
            'pagination' => $pagination,
            'currentPage' => $pagination->page,
            'totalPages' => $pagination->pageCount,
            'totalCount' => $pagination->totalCount,
            'pageSize' => $pagination->pageSize,
            'listTopDeep' => $userDeep,
        ];
    }

    /**
     * 获取一层下级数量从期数
     * @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_NETWORK_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) : [];
    }

    /**
     * 判断在某个区位是否存在会员
     * @param $userId
     * @param $location
     * @return bool
     */
    public static function issetUserInLocation($userId, $location){
        $childrenNetList = UserNetwork::find()->select(['LOCATION_TAG'])->where('PARENT_UID=:PARENT_UID', [':PARENT_UID' => $userId])->asArray()->all();
        foreach ($childrenNetList as $everyData) {
            //取最后一位数
            $everyLocation = substr($everyData['LOCATION_TAG'], -1);
            if( $everyLocation == $location ) return true;
        }

        return false;
    }
}