ngds/console/controllers/ImportController.php

<?php
/**
 * Created by PhpStorm.
 * User: xiaohuitu
 * Date: 2020/2/18
 * Time: 6:36 PM
 */
namespace console\controllers;


use common\helpers\Cache;
use common\helpers\Excel;

use common\helpers\snowflake\SnowFake;
use common\helpers\user\Info;
use common\models\Declaration;
use common\models\DeclarationLevel;
use common\models\DecOrder;
use common\models\ImportScore;
use common\models\Order;
use common\models\OrderShop;
use common\models\PerfMonth;
use common\models\PerfPeriod;
use common\models\TmpAreaNetwork;
use common\models\TmpDeleteUser;
use common\models\TmpFreezeUser;
use common\models\TmpOldNetwork;
use common\models\TmpOldNetworkAll;
use common\models\TmpOldNetworkError;
use common\models\TmpOldNetworkReplenish;
use common\models\TmpOldUser;
use common\models\User;
use common\models\UserInfo;
use common\models\UserMove;
use common\models\UserNetwork;
use common\models\UserReg;
use common\models\UserRelation;
use console\models\ImportForm;
use yii\base\BaseObject;


class ImportController extends BaseController
{
    const LIMIT = 1000;

    CONST EXCEL_TABLE_USER_FIELD = [
        '序号' => 'SORT',
        '会员编号' => 'USER_NAME',
        '会员姓名' => 'REAL_NAME',
        '推荐人编号' => 'REC_USER_NAME',
        '推荐人姓名' => 'REC_REAL_NAME',
        '安置人编号' => 'NET_USER_NAME',
        '安置人姓名' => 'NET_REAL_NAME',
    ];

    CONST EXCEL_TABLE_NETWORK_FIELD = [
        '会员编号' => 'USER_NAME',
        '会员名称' => 'REAL_NAME',
        '推荐人编号' => 'PARENT_USER_NAME',
        '推荐人名称' => 'PARENT_REAL_NAME',
        '安置人编号' => 'NET_USER_NAME',
        '安置人名称' => 'NET_REAL_NAME',
    ];

    CONST EXCEL_TABLE_POINTS_FIELD = [
        '会员编号' => 'USER_NAME',
        '会员姓名' => 'REAL_NAME',
        '会员级别' => 'LEVEL_NAME',
        '会员荣衔' => 'EMP_LEVEL_NAME',
        '总奖金' => 'BONUS_TOTAL',
        '原总奖金' => 'ORI_BONUS_TOTAL',
        '基础积分' => 'BASE_SCORE',
        '级别积分' => 'LEVEL_SCORE',
        '升级积分' => 'UPGRADE_SCORE',
        '总积分' => 'TOTAL_SCORE',
    ];

    const TOP_USER_NAME = 'Q94839185';
    const TOP_REAL_NAME = '齐长青';

    const IMPORT_CACHE_SORT_KEY = 'console:importCacheSortKey';

    /**
     * @param string $fileName
     * @param int $rowCount
     * @param int $startRow
     * @throws \yii\base\Exception
     */
    public function actionOldUser($fileName, $rowCount=1000, $startRow=1)
    {
        $excel = new Excel();
        $result = $excel->pageImportDataByExcelFile($fileName, $rowCount, $startRow, self::LIMIT);

        $insertData = [];
        foreach ($result as $everyData) {
            if( !$everyData ) continue;

            $oneInsertData = [];
            foreach ($everyData as $key => $value) {
                if( !isset(self::EXCEL_TABLE_USER_FIELD[$key]) ) continue;

                $oneInsertData[self::EXCEL_TABLE_USER_FIELD[$key]] = $value;
            }

            $insertData[] = $oneInsertData;
            unset($oneInsertData, $everyData);
        }
//        var_dump($insertData);die;
        TmpOldUser::batchInsert($insertData);
        unset($insertData);

        echo sprintf("【%s】导入成功".PHP_EOL, $fileName);
    }

    /**
     * @param string $fileName
     * @param int $rowCount
     * @param int $startRow
     * @throws \yii\base\Exception
     */
    public function actionOldNetwork($fileName, $rowCount=1000, $startRow=1)
    {
        ini_set('memory_limit','512M');
        $pageCount = ceil($rowCount/self::LIMIT);
        $excel = new Excel();
        for($page=1;$page<=$pageCount;$page++) {
            $newStartRow = $startRow + self::LIMIT * ($page - 1);
            $result = $excel->pageImportDataByExcelFile($fileName, $rowCount, $newStartRow, self::LIMIT);

            $insertData = [];
            foreach ($result as $sort => $everyData) {
                if( !$everyData ) continue;

                $oneInsertData = [];
                $oneInsertData['SORT'] = $newStartRow + $sort;
                foreach ($everyData as $key => $value) {
                    if( !isset(self::EXCEL_TABLE_NETWORK_FIELD[$key]) ) continue;

                    $oneInsertData[self::EXCEL_TABLE_NETWORK_FIELD[$key]] = $value;
                }


                //判断用户表中是否已经存在该会员
                $one = UserInfo::find()->select(["ID", "USER_NAME"])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$oneInsertData['USER_NAME']])->asArray()->one();
                if( $one ){
                    echo '会员编号'.$oneInsertData['USER_NAME'].'已存在'.PHP_EOL;
                    continue;
                }


                if( $oneInsertData['NET_USER_NAME'] === self::TOP_USER_NAME ) {
                    $oneInsertData['NET_DEEP'] = 1;
                    $oneInsertData['REC_DEEP'] = 1;
                }else {
                    $oneInsertData['NET_DEEP'] = 0;
                    $oneInsertData['REC_DEEP'] = 0;
                }

                if($oneInsertData['NET_USER_NAME']==NULL) {
                    $oneInsertData['NET_USER_NAME'] = '';
                    $oneInsertData['NET_REAL_NAME'] = $oneInsertData['NET_REAL_NAME'] ?? '';
                    echo '用户编号'.$oneInsertData['USER_NAME'].'的接点人为空'.PHP_EOL;
                }
                if($oneInsertData['PARENT_USER_NAME']==NULL) {
                    $oneInsertData['PARENT_USER_NAME'] = '';
                    $oneInsertData['PARENT_REAL_NAME'] = $oneInsertData['PARENT_REAL_NAME'] ?? '';
                    echo '用户编号'.$oneInsertData['USER_NAME'].'的推荐人为空'.PHP_EOL;
                }

                //更正tmp_old_network表中的错误数据
//                $importOne = TmpOldNetwork::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$oneInsertData['USER_NAME']])->asArray()->one();
//                if( $importOne ) {
//                    echo $oneInsertData['USER_NAME'] . PHP_EOL;
//                    TmpOldNetwork::updateAll([
//                        'PARENT_USER_NAME' => $oneInsertData['PARENT_USER_NAME'],
//                        'PARENT_REAL_NAME' => $oneInsertData['PARENT_REAL_NAME'],
//                        'NET_USER_NAME' => $oneInsertData['NET_USER_NAME'],
//                        'NET_REAL_NAME' => $oneInsertData['NET_REAL_NAME']
//                    ], 'ID=:ID', ['ID'=>$importOne['ID']]);
//                }else {
//                    $insertData[] = $oneInsertData;
//                }
//
//                unset($importOne);

                $insertData[] = $oneInsertData;
                unset($oneInsertData, $everyData);
            }

            if( $insertData ) TmpOldNetwork::batchInsert($insertData);
            unset($newStartRow, $insertData);

            echo sprintf("文件：【%s】页数：【%s】导入成功".PHP_EOL, $fileName, $page);
        }
    }

    //计算导入会员中顶点的开拓深度
    public function actionUpdateRecTopDeep($startPage=1) {
        $numCount = TmpOldNetwork::find()->where("1=1")->orderBy("SORT ASC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {

            $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("SORT ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {

                $one = TmpOldNetwork::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['PARENT_USER_NAME']])->asArray()->one();
                if( !$one ) {
                    //推荐人不在导入会员中 获取深度
                    $userInfoOne = UserInfo::find()->select(['USER_NAME', 'RELATION_DEEP'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['PARENT_USER_NAME']])->asArray()->one();
                    if( !$userInfoOne ) {
                        echo sprintf("在系统中未找到推荐人,会员编号: 【%s】,推荐人编号：【%s】" . PHP_EOL, $pageData['USER_NAME'], $pageData['PARENT_USER_NAME']);
                        unset($one, $userInfoOne);
                        continue;
                    }
                    TmpOldNetwork::updateAll(['REC_DEEP'=>$userInfoOne['RELATION_DEEP']+1], 'ID=:ID', ['ID'=>$pageData['ID']]);
                    unset($userInfoOne);
                }
                unset($pageData,$one);
            }
            unset($pageList);
        }
        unset($numCount, $pageCount);
    }

    public function actionUpdateNetTopDeep($startPage=1) {
        $numCount = TmpOldNetwork::find()->where("1=1")->orderBy("SORT ASC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {

            $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("SORT ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {

                $one = TmpOldNetwork::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['NET_USER_NAME']])->asArray()->one();
                if( !$one ) {
                    //安置人不在导入会员中 获取深度
                    $userInfoOne = UserInfo::find()->select(['USER_NAME', 'NETWORK_DEEP'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['NET_USER_NAME']])->asArray()->one();
                    if( !$userInfoOne ) {
                        echo sprintf("在系统中未找到安置人,会员编号: 【%s】,安置人编号：【%s】" . PHP_EOL, $pageData['USER_NAME'], $pageData['NET_USER_NAME']);
                        unset($one, $userInfoOne);
                        continue;
                    }
                    //更新深度
                    TmpOldNetwork::updateAll(['NET_DEEP'=>$userInfoOne['NETWORK_DEEP']+1], 'ID=:ID', ['ID'=>$pageData['ID']]);
                    unset($userInfoOne);
                }
                unset($pageData,$one);
            }
            unset($pageList);
        }
        unset($numCount, $pageCount);
    }

    /**
     * 更新安置深度
     * @param int $deep
     * @return int
     */
    public function actionUpdateNetDeep($deep=1) {
        echo sprintf("更新安置深度开始，当前安置深度为【%s】".PHP_EOL, $deep);
        $list = TmpOldNetwork::find()->where('NET_DEEP=:NET_DEEP', ['NET_DEEP'=>$deep])->asArray()->all();
        if( !$list ) return $deep;
        foreach ($list as $data) {
//            echo $data['USER_NAME'] . PHP_EOL;
            TmpOldNetwork::updateAll([
                'NET_DEEP' => $deep + 1,
            ], 'NET_USER_NAME=:NET_USER_NAME', ['NET_USER_NAME'=>$data['USER_NAME']]);

            unset($data);
        }

        unset($list);
        return $this->actionUpdateNetDeep($deep+1);
    }

    /**
     * 更新安置深度
     * @param int $startDeep
     * @param int $endDeep
     * @return bool
     */
    public function actionUpdateNetDeepTwo($startDeep=1, $endDeep=10) {
        for( $deep=$startDeep;$deep<=$endDeep;$deep++ ) {
            $deep = $this->actionUpdateNetDeep($deep);
        }

        return true;
    }

    /**
     * 更新推荐深度
     * @param int $deep
     * @return int
     */
    public function actionUpdateRecDeep($deep=1) {
        echo sprintf("更新推荐深度开始，当前推荐深度为【%s】".PHP_EOL, $deep);
        $list = TmpOldNetwork::find()->where('REC_DEEP=:REC_DEEP', ['REC_DEEP'=>$deep])->asArray()->all();
        if( !$list ) return $deep;
        foreach ($list as $data) {

            TmpOldNetwork::updateAll([
                'REC_DEEP' => $deep + 1,
            ], 'PARENT_USER_NAME=:PARENT_USER_NAME', ['PARENT_USER_NAME'=>$data['USER_NAME']]);

            unset($data);
        }

        unset($list);
        return $this->actionUpdateRecDeep($deep+1);
    }


    /**
     * 更新推荐深度
     * @param int $startDeep
     * @param int $endDeep
     * @return bool
     */
    public function actionUpdateRecDeepTwo($startDeep=1, $endDeep=10) {
        for( $deep=$startDeep;$deep<=$endDeep;$deep++ ) {
            $deep = $this->actionUpdateRecDeep($deep);
        }

        return true;
    }


    /**
     * 清空缓存
     */
    public function actionClearCache() {
        $importForm = new ImportForm();
        $importForm->clearInfoCache();

        echo "清空完成" . PHP_EOL;
    }



    public function actionOnlineUser($startPage=1) {
        //加入顶点信息
        $importForm = new ImportForm();

        //从old_network中获取所有的会员信息
        $numCount = TmpOldNetwork::find()->where("1=1")->orderBy("NET_DEEP ASC, REC_DEEP ASC, SORT ASC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("NET_DEEP ASC, REC_DEEP ASC, SORT ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();

            foreach ($pageList as $pageData) {
                $importForm->addOnlineUser($pageData);
                unset($pageData,$userData);
            }
            unset($pageList);
            echo sprintf("导入【用户】数据，总页数【%s】，当前页数：【%s】导入成功".PHP_EOL, $pageCount, $page);
        }
        unset($db, $numCount, $pageCount);
    }

    /**
     * 写入用户缓存数据
     * @param int $startPage
     */
    public function actionAddUserCache($startPage=1) {
        $importForm = new ImportForm();
        $importForm->clearCache();

        $numCount = User::find()->where("1=1")->orderBy("ID DESC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = User::find()->select(['ID', 'USER_NAME'])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                $importForm->cacheUserId($pageData['USER_NAME'], $pageData['ID']);
                unset($pageData);
            }
            unset($pageList);

            echo sprintf("写入用户缓存数据，总页数【%s】，当前页数：【%s】写入成功".PHP_EOL, $pageCount, $page);
        }
    }

    //写userinfo及关系表
    public function actionOnlineInfo($startPage=1 ,$startStatus=true,$startUserName=NULL) {
        //从old_network中获取所有的会员信息
        $numCount = TmpOldNetwork::find()->where("1=1")->orderBy("NET_DEEP ASC, REC_DEEP ASC, SORT ASC")->count("ID");

//        $db = \Yii::$app->db;
        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("NET_DEEP ASC, REC_DEEP ASC, SORT ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
//            $transaction = $db->beginTransaction();
            $importForm = new ImportForm();
//            try {
            foreach ($pageList as $pageData) {
                if( !$startStatus && $pageData["USER_NAME"] === $startUserName )
                {
                    $startStatus = true;
                }
                if( !$startStatus ) continue;

                $importForm->addOnlineInfo($pageData);
            }

//                $transaction->commit();
//            }catch (\Exception $e) {
//                $transaction->rollBack();
//
//                echo $e->getTraceAsString();die;
//            }

            unset($pageList);

            echo sprintf("导入【关系】数据，总页数【%s】，页数：【%s】导入成功".PHP_EOL, $pageCount, $page);
        }

        unset($db, $numCount, $pageCount);

    }


    //写推荐关系表
    public function actionOnlineRelation($startPage=1 ,$startStatus=true,$startUserName=NULL) {
        //从old_network中获取所有的会员信息
        $numCount = TmpOldNetwork::find()->where("1=1")->orderBy("REC_DEEP ASC, SORT ASC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("REC_DEEP ASC, SORT ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();

            $transaction = \Yii::$app->db->beginTransaction();
            try {

                foreach ($pageList as $pageData) {
                    if( !$startStatus && $pageData["USER_NAME"] === $startUserName )
                    {
                        $startStatus = true;
                    }
                    if( !$startStatus ) continue;

                    //会员ID
                    $userId = Info::getUserIdByUserName($pageData['USER_NAME']);
                    //推荐人ID
                    $userInfoRec = UserInfo::find()->select(['USER_ID'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['PARENT_USER_NAME']])->asArray()->one();
                    $recUid = $userInfoRec['USER_ID'];

                    //推荐关系
                    $conRelationInfo = UserRelation::findOneAsArray(['USER_ID'=>$recUid]);
                    if( !$conRelationInfo ) {
                        throw new Exception('推荐人'.$pageData['PARENT_USER_NAME'].'不存在');
                    }
                    //修改深度
                    $deep = $conRelationInfo['TOP_DEEP'] + 1;
                    UserInfo::updateAll([
                        "RELATION_DEEP" => $deep,
                    ], "USER_ID='{$userId}'");

                    // 加入推荐网络关系
                    $insertData = [
                        'ID' => SnowFake::instance()->generateId(),
                        "USER_ID"  => $userId,
                        "PARENT_UID"  => $recUid,
                        "TOP_UID"  => $conRelationInfo['TOP_UID'],
                        "TOP_DEEP"  => $deep,
                        "PARENT_UIDS"  => $conRelationInfo['PARENT_UIDS'] . ',' . $recUid,
                        "CREATED_AT"  => time(),
                    ];
                    UserRelation::insertOne($insertData);

                    if(!UserInfo::updateAllCounters(['REC_NUM'=>1], "USER_ID=:USER_ID", [':USER_ID'=>$recUid])){
                        throw new Exception('会员开拓关系下级节点数量更新失败');
                    }
                    unset($userId,$userInfoRec,$recUid,$conRelationInfo,$deep,$insertData);
                }

                $transaction->commit();
            }catch (\Exception $e) {
                $transaction->rollBack();
                echo $e->getMessage();die;
            }

            unset($pageList);

            echo sprintf("导入【关系】数据，总页数【%s】，页数：【%s】导入成功".PHP_EOL, $pageCount, $page);
        }

        unset($db, $numCount, $pageCount);

    }


    /**
     * 检测排序
     * @param int $startPage
     */
    public function actionCheckSort($startPage=1){
        \Yii::$app->redis->del(self::IMPORT_CACHE_SORT_KEY);
        $numCount = User::find()->where("1=1")->orderBy("ID DESC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = User::find()->select(['ID', 'USER_NAME'])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                \Yii::$app->redis->hSet(self::IMPORT_CACHE_SORT_KEY, $pageData['USER_NAME'], 1);
                unset($pageData);
            }
            unset($pageList);

//            echo sprintf("写入用户缓存数据，总页数【%s】，当前页数：【%s】写入成功".PHP_EOL, $pageCount, $page);
        }
        unset($numCount);

        $numCount = TmpOldNetwork::find()->where("1=1")->orderBy("NET_DEEP ASC, REC_DEEP ASC, SORT ASC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("NET_DEEP ASC, REC_DEEP ASC, SORT ASC")->offset(($page - 1) * self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                $recStatus = \Yii::$app->redis->hGet(self::IMPORT_CACHE_SORT_KEY, $pageData['PARENT_USER_NAME']);
                $netStatus = \Yii::$app->redis->hGet(self::IMPORT_CACHE_SORT_KEY, $pageData['NET_USER_NAME']);
                if( !$recStatus || !$netStatus ) {
                    echo sprintf("用户编号: %s,用户姓名：%s,推荐人编号：%s,推荐人姓名：%s,安置人编号：%s,安置人姓名：%s" . PHP_EOL, $pageData['USER_NAME'], $pageData['REAL_NAME'], $pageData['PARENT_USER_NAME'], $pageData['PARENT_REAL_NAME'], $pageData['NET_USER_NAME'], $pageData['NET_REAL_NAME']);
                    TmpOldNetworkError::insertOne($pageData);
                    TmpOldNetwork::deleteAll('ID=:ID', ['ID'=>$pageData['ID']]);
                }
                unset($recStatus, $netStatus);
                \Yii::$app->redis->hSet(self::IMPORT_CACHE_SORT_KEY, $pageData['USER_NAME'], 1);

                unset($pageData);
            }
            unset($pageList);

//            echo sprintf("写入导入缓存数据，总页数【%s】，当前页数：【%s】写入成功".PHP_EOL, $pageCount, $page);
        }
    }


    public function actionCheckErrorSort() {
        $numCount = TmpOldNetwork::find()->where("1=1")->orderBy("SORT ASC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=1;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("SORT ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                //查询这个人的推荐人和接点人是否在当前表中
                $one = TmpOldNetwork::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['PARENT_USER_NAME']])->asArray()->one();
                if( !$one ) {
                    $userInfoOne = UserInfo::find()->select(['USER_NAME', 'RELATION_DEEP', 'NETWORK_DEEP'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['PARENT_USER_NAME']])->asArray()->one();
                    if( !$userInfoOne ) {
                        TmpOldNetworkError::insertOne($pageData);
                        TmpOldNetwork::deleteAll('ID=:ID', ['ID'=>$pageData['ID']]);
                        echo 'REC ERROR' . $pageData['USER_NAME'] . PHP_EOL;
                        unset($one, $userInfoOne);
                        continue;
                    }
                    unset($userInfoOne);
                }
                unset($one);

                if( $pageData['PARENT_USER_NAME'] !== $pageData['NET_USER_NAME'] ) {
                    //接点人
                    $one = TmpOldNetwork::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['NET_USER_NAME']])->asArray()->one();
                    if( !$one ) {
                        $userInfoOne = UserInfo::find()->select(['USER_NAME', 'RELATION_DEEP', 'NETWORK_DEEP'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['NET_USER_NAME']])->asArray()->one();
                        if( !$userInfoOne ) {
                            TmpOldNetworkError::insertOne($pageData);
                            TmpOldNetwork::deleteAll('ID=:ID', ['ID'=>$pageData['ID']]);
                            echo 'NET ERROR' . $pageData['USER_NAME'] . PHP_EOL;
                            unset($one, $userInfoOne);
                            continue;
                        }
                        unset($userInfoOne);
                    }

                    unset($one);
                }

                unset($pageData);
            }

            unset($pageList);
        }
    }


    /**
     * 为上级增加数量
     * @param int $startPage
     */
    public function actionUpdateParentNum($startPage=1){

        $numCount = UserInfo::find()->select('USER_ID,CON_UID,REC_UID')->where("1=1")->orderBy("ID DESC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {

            $pageList = UserInfo::find()->select('USER_ID,CON_UID,REC_UID')->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                if($pageData['CON_UID']){
                    UserInfo::updateAllCounters(['CON_NUM'=> 1], "USER_ID=:USER_ID", [':USER_ID'=>$pageData['CON_UID']]);
                }
                if($pageData['REC_UID']){
                    UserInfo::updateAllCounters(['REC_NUM'=> 1], "USER_ID=:USER_ID", [':USER_ID'=>$pageData['REC_UID']]);
                }
                unset($pageData);
            }
            unset($pageList);

            echo sprintf("导入【关系】数据，总页数【%s】，页数：【%s】导入成功".PHP_EOL, $pageCount, $page);
        }
        unset($numCount, $pageCount);
    }

    //删除未写入完整的用户信息
    public function actionDeleteUserInfo($userName) {
        //从User中获取会员ID
        $userData = User::find()->select(['ID'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$userName])->asArray()->one();
        if( !$userData ) {
            return true;
        }
        UserInfo::deleteAll("USER_ID='{$userData['ID']}'");
        $netInfo = UserNetwork::find()->where('USER_ID=:USER_ID', ['USER_ID'=>$userData['ID']])->asArray()->one();
        if($netInfo){
            UserNetwork::deleteAll("USER_ID='{$userData['ID']}'");
        }
        $recInfo = UserNetwork::find()->where('USER_ID=:USER_ID', ['USER_ID'=>$userData['ID']])->asArray()->one();
        if($recInfo){
            UserRelation::deleteAll("USER_ID='{$userData['ID']}'");
        }
        echo sprintf("删除用户【%s】，成功".PHP_EOL, $userName);

        unset($userData,$netInfo,$recInfo);
    }


    /**
     * 按深度更新期数
     * @param int $startDeep
     */
    public function actionUpdatePeriod($startDeep=1) {
        for( $deep=$startDeep;$deep<=515;$deep++ ) {
            UserNetwork::updateAll(['PERIOD_NUM'=>103], 'TOP_DEEP='.$deep);
            UserRelation::updateAll(['PERIOD_NUM'=>103], 'TOP_DEEP='.$deep);
            echo sprintf("更新【期数】数据，当前深度为【%s】成功".PHP_EOL, $deep);
        }
    }

    /**
     * 写入用户信息缓存数据
     * @param int $startPage
     */
    public function actionAddUserInfoCache($startPage=1) {
        $importForm = new ImportForm();
        $importForm->clearInfoCache();

        $numCount = UserInfo::find()->where("1=1")->orderBy("ID DESC")->count("ID");

        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = UserInfo::find()->select(['USER_ID', 'USER_NAME', 'CON_UID', 'REC_UID'])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                $importForm->cacheUserInfo($pageData['USER_ID'], $pageData);
                unset($pageData);
            }
            unset($pageList);

            echo sprintf("写入用户缓存数据，总页数【%s】，当前页数：【%s】写入成功".PHP_EOL, $pageCount, $page);
        }
    }



    /**
     * 获取最终父顶点
     * @param string $userName
     * @return bool
     */
    public function actionFindParent($userName='H15678858') {
        echo $userName . PHP_EOL;
//        $circleData = [
//            'H19546931',
//            'H52702897',
//            'H71350536',
//            'H15116023',
//        ];
//        if( in_array($userName, $circleData) ) {
//            return true;
//        }
//        unset($circleData);
        $data = TmpOldNetwork::find()->select(['NET_USER_NAME'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$userName])->asArray()->one();
        if( !$data ) {
            return true;
        }

        unset($userName);
        $this->actionFindParent($data['NET_USER_NAME']);

    }

    public function actionFindRecParent($userName='A14998649') {
        echo $userName . PHP_EOL;

        $data = TmpOldNetworkAll::find()->select(['PARENT_USER_NAME'])->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$userName])->asArray()->one();
        if( !$data ) {
            return true;
        }

        unset($userName);
        $this->actionFindRecParent($data['PARENT_USER_NAME']);

    }

    public function actionGetNextUser($page=1) {
        $hitUserName = 'D17173496';
        $pageList = TmpOldNetwork::find()->where("1=1")->orderBy("NET_DEEP ASC, REC_DEEP ASC, SORT ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
        $key = 0;
        $hit = false;
        foreach ($pageList as $key => $pageData) {
            if( $pageData['USER_NAME'] === $hitUserName ) {
                $hit = true;
                break;
            }
        }

        if( $hit ) {
            echo 'hit' . PHP_EOL;
            $hitKey = $key+1;
            if( isset($pageList[$hitKey]) ) {
                echo sprintf("hit user is %s" . PHP_EOL, $pageList[$hitKey]['USER_NAME']);
            }else {
                echo sprintf("hit user is %s first one" . PHP_EOL, $page+1);
            }
        }else {
            echo 'no hit' . PHP_EOL;
        }

    }

    //超出点位验证
    public function actionFindNetPoint($startPage=1) {
        $numCount = TmpOldNetwork::find()->select(["NET_USER_NAME"])->where("1=1")->orderBy("ID DESC")->count("ID");
        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->select(['NET_USER_NAME'])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                $location = TmpOldNetwork::find()->where('NET_USER_NAME=:NET_USER_NAME', ['NET_USER_NAME'=>$pageData['NET_USER_NAME']])->count();

                $userId = Info::getUserIdByUserName($pageData['NET_USER_NAME']);
                if($userId){
                    $location2 = UserInfo::find()->where('CON_UID=:CON_UID', ['CON_UID'=>$userId])->count();
                }else{
                    $location2=0;
                }
                $points = $location + $location2;
                if($points>3){
                    echo sprintf("安置人编号【%s】，点位数：【%s】超出".PHP_EOL, $pageData['NET_USER_NAME'], $points);
                }
                unset($pageData,$location,$location2,$points);
            }
            unset($pageList);

            echo sprintf("查询安置人点位，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }
        unset($numCount, $pageCount);
    }

    //接点人是否存在
    public function actionFindNetUser($startPage=1) {
        $numCount = TmpOldNetwork::find()->select(["NET_USER_NAME"])->where("1=1")->orderBy("ID DESC")->count("ID");
        $pageCount = ceil($numCount/self::LIMIT);
        $i=0;
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->select(['NET_USER_NAME'])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {

                $jiedian2 = TmpOldNetwork::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['NET_USER_NAME']])->count();
                if(!$jiedian2){
                   $one = User::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['NET_USER_NAME']])->asArray()->one();
                    $i++;
//                    $deep = $one['NETWORK_DEEP'];
//                    TmpOldNetwork::updateAll([
//                        'NET_DEEP' => $deep+1
//                    ], 'USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['USER_NAME']]);
                    //echo sprintf("安置人编号【%s】，导入数据中存在".PHP_EOL, $pageData['NET_USER_NAME']);
                    if(!$one) {
                        echo sprintf("安置人编号【%s】，系统与导入数据中都不存在" . PHP_EOL, $pageData['NET_USER_NAME'], $one['NETWORK_DEEP']);
                    }
                }

                unset($pageData,$location,$location2,$points);
            }
            unset($pageList);

            echo sprintf("查询安置人是否存在，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }
        echo $i;
        unset($numCount, $pageCount);
    }
    //推荐人是否存在
    public function actionFindRecUser($startPage=1) {
        $numCount = TmpOldNetwork::find()->select(["PARENT_USER_NAME"])->where("1=1")->orderBy("ID DESC")->count("ID");
        $pageCount = ceil($numCount/self::LIMIT);
        $i=0;
        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = TmpOldNetwork::find()->select(['PARENT_USER_NAME'])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {

                $jiedian2 = TmpOldNetwork::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['PARENT_USER_NAME']])->count();
                if(!$jiedian2){
                    $one = User::find()->where('USER_NAME=:USER_NAME', ['USER_NAME'=>$pageData['PARENT_USER_NAME']])->asArray()->one();
                    $i++;
                    if(!$one) {
                        echo sprintf("推荐人编号【%s】，系统与导入数据中都不存在" . PHP_EOL, $pageData['PARENT_USER_NAME']);
                    }
                }

                unset($pageData,$location,$location2,$points);
            }
            unset($pageList);

            echo sprintf("查询推荐人是否存在，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }
        echo $i;
        unset($numCount, $pageCount);
    }


    //导出会员及推荐人、级别信息
    public function actionGetAllUser($startPage=1) {
        $numCount = User::find()->where("1=1")->orderBy("ID DESC")->count("ID");
        $pageCount = ceil($numCount/self::LIMIT);
        echo sprintf("会员编号,会员姓名,推荐人,手机号,级别,密文密码".PHP_EOL);
        for ($page=$startPage;$page<=$pageCount;$page++) {

            $pageList = User::find()->select(["ID","USER_NAME","PASSWORD_HASH","REAL_NAME","MOBILE","DEC_LV"])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                //报单级别
                $decLevel = DeclarationLevel::find()->where('ID=:ID', ['ID'=>$pageData['DEC_LV']])->asArray()->one();
                $decLvName = $decLevel['LEVEL_NAME']??'';
                //推荐人
                $recUser = UserInfo::find()->select(["REC_UID"])->where('USER_ID=:USER_ID', ['USER_ID'=>$pageData['ID']])->asArray()->one();
                $recName = Info::getUserNameByUserId($recUser['REC_UID']);
                //输出
                echo sprintf("【%s】,%s,%s,%s,%s,【%s】".PHP_EOL, $pageData['USER_NAME'], $pageData['REAL_NAME'], $recName, $pageData['MOBILE'], $decLvName, $pageData['PASSWORD_HASH']);

                unset($pageData,$decLevel,$decLvName,$recUser,$recName);
            }
            unset($pageList);

            echo sprintf("导出会员信息，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }
        unset($numCount, $pageCount);
    }


    public function actionPerfMonth($startPage=1) {
        $numCount = PerfMonth::find()->where("CALC_MONTH='202007'")->orderBy("ID ASC")->count("ID");
        $pageCount = ceil($numCount/self::LIMIT);
        echo sprintf("会员编号,个人业绩,月新增团队业绩,月累计团队业绩".PHP_EOL);

        for ($page=$startPage;$page<=$pageCount;$page++) {
            $pageList = PerfMonth::find()->select('USER_ID,PV_PCS,PV_PSS,PV_PSS_TOTAL')->where("CALC_MONTH='202007'")->orderBy("ID ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();

            foreach ($pageList as $pageData) {
                $userName = Info::getUserNameByUserId($pageData['USER_ID']);
                echo sprintf("%s,%s,%s,%s" . PHP_EOL, $userName, $pageData['PV_PCS'], $pageData['PV_PSS'], $pageData['PV_PSS_TOTAL']);
                unset($pageData,$userName);
            }
            unset($pageList);
            echo sprintf("导出会员业绩信息，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }
        unset($numCount, $pageCount);
    }

    //根据条件判断会员激活状态
    public function actionEditUserStatus($startPage=1) {
        $numCount = User::find()->where("1=1")->orderBy("ID DESC")->count("ID");
        $pageCount = ceil($numCount/self::LIMIT);

        for ($page=$startPage;$page<=$pageCount;$page++) {

            $pageList = User::find()->select(["ID","STATUS","USER_NAME"])->where("1=1")->orderBy("ID DESC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                // 下单金额/推荐人数
                $orderAmount = Order::find()->where('USER_ID=:USER_ID', [':USER_ID' => $pageData['ID']])->SUM('ORDER_AMOUNT');
                $orderShopAmount = OrderShop::find()->where('USER_ID=:USER_ID', [':USER_ID' => $pageData['ID']])->SUM('ORDER_AMOUNT');
                $amount = $orderAmount + $orderShopAmount;

                $recNum = intval(DecOrder::find()->where('REC_USER_ID=:REC_USER_ID', [':REC_USER_ID' => $pageData['ID']])->count());

                if ($amount < 300 && $recNum==0) {
                    //修改为冻结状态
                    User::updateAll([
                        'STATUS' => 0
                    ], 'ID=:ID', ['ID' => $pageData['ID']]);
                    echo sprintf("修改会员编号【%s】状态冻结" . PHP_EOL, $pageData['USER_NAME']);
                }

                unset($pageData,$orderAmount,$orderShopAmount,$amount,$recNum);
            }
            unset($pageList);

            echo sprintf("修改会员状态，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }

        unset($numCount, $pageCount);
    }

    public function actionUsernameUpper($startPage=1) {
        $numCount = User::find()->where("1=1")->orderBy("ID ASC")->count("ID");

        $i = 0;
        $j = 0;
        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {

            $pageList = User::find()->where("1=1")->orderBy("ID ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                //含中文会员编号跳过
                if (preg_match("/([\x81-\xfe][\x40-\xfe])/", $pageData['USER_NAME'], $match)) {
                    continue;
                }
                //会员编号字母转大写
                $username = strtoupper($pageData['USER_NAME']);
                if($pageData['USER_NAME']===$username) continue;

                $one = User::find()->where("USER_NAME='".$username."' and ID!='".$pageData['ID']."'")->asArray()->one();
                if( !$one ) {
                    User::updateAll(['USER_NAME'=> $username], 'ID=:ID', ['ID'=>$pageData['ID']]);
                    $j++;
                    echo sprintf("修改会员编号成功,会员编号: 【%s】,会员ID：【%s】" . PHP_EOL, $username, $pageData['ID']);
                }else{
                    $i++;
                    echo sprintf("会员编号重复,会员编号: 【%s】,会员ID：【%s】" . PHP_EOL, $username, $pageData['ID']);
                }
                unset($pageData,$one);
            }
            unset($pageList);
            echo sprintf("修改会员编号，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }
        echo $i.'=='.$j;
        unset($numCount, $pageCount);
    }

    public function actionUserinfoUpper($startPage=1) {
        $numCount = UserInfo::find()->where("1=1")->orderBy("ID ASC")->count("ID");

        $i = 0;
        $j = 0;
        $pageCount = ceil($numCount/self::LIMIT);
        for ($page=$startPage;$page<=$pageCount;$page++) {

            $pageList = UserInfo::find()->where("1=1")->orderBy("ID ASC")->offset(($page-1)*self::LIMIT)->limit(self::LIMIT)->asArray()->all();
            foreach ($pageList as $pageData) {
                //含中文会员编号跳过
                if (preg_match("/([\x81-\xfe][\x40-\xfe])/", $pageData['USER_NAME'], $match)) {
                    continue;
                }
                //会员编号字母转大写
                $username = strtoupper($pageData['USER_NAME']);
                if($pageData['USER_NAME']===$username) continue;

                $one = UserInfo::find()->where("USER_NAME='".$username."' and ID!='".$pageData['ID']."'")->asArray()->one();
                if( !$one ) {
                    UserInfo::updateAll(['USER_NAME'=> $username], 'ID=:ID', ['ID'=>$pageData['ID']]);
                    $j++;
                    echo sprintf("修改会员编号成功,会员编号: 【%s】,会员ID：【%s】" . PHP_EOL, $username, $pageData['USER_ID']);
                }else{
                    $i++;
                    echo sprintf("会员编号重复,会员编号: 【%s】,会员ID：【%s】" . PHP_EOL, $username, $pageData['USER_ID']);
                }
                unset($pageData,$one);
            }
            unset($pageList);
            echo sprintf("修改会员编号，总页数【%s】，当前页数：【%s】完成".PHP_EOL, $pageCount, $page);
        }
        echo $i.'=='.$j;
        unset($numCount, $pageCount);
    }


    /**
     * 导入积分数据
     * @param $fileName
     * @param int $rowCount
     * @param int $startRow
     * @throws \Exception
     */
    public function actionPointsData($fileName, $rowCount=1000, $startRow=1)
    {
        $excel = new Excel();
        $result = $excel->pageImportDataByExcelFile($fileName, $rowCount, $startRow, self::LIMIT);

        $insertData = [];
        foreach ($result as $everyData) {
            if( !$everyData ) continue;

            $oneInsertData = [];
            foreach ($everyData as $key => $value) {
                if( !isset(self::EXCEL_TABLE_POINTS_FIELD[$key]) ) continue;
                
                $oneInsertData[self::EXCEL_TABLE_POINTS_FIELD[$key]] = $value;
            }

            $user = Info::getBaseUserByUserName($oneInsertData['USER_NAME']);
            if ( !$user ) {
                throw new \Exception(sprintf("用户【%s】不存在", $oneInsertData['USER_NAME']));
            }
            $oneInsertData['USER_ID'] = $user['ID'];

            $insertData[] = $oneInsertData;
            unset($oneInsertData, $everyData);
        }
//        var_dump($insertData);die;
        ImportScore::batchInsert($insertData);
        unset($insertData);

        echo sprintf("【%s】导入成功".PHP_EOL, $fileName);
    }
}