xiaoben-as2-php/protected/commands/CorrectDataHandleCommand.php

<?php
class CorrectDataHandleCommand extends CConsoleCommand {
    public $conn;
    public $sConn;
    public function init() {
        parent::init();

        @ini_set('memory_limit', '1024M');
        set_time_limit(0);

    }
    public $authUsername = '';

    public function actionIndex($YII_ENV='production'){

        $needHandleData=$this->getNeedHandleData();

        if (!$needHandleData) {
            $this->showMsg(1, '没有需要处理的考试');
            return '';
        }

        $schoolIds = array_keys($needHandleData);
        $this->showMsg(1, '共有' . count($schoolIds) . '个学校需要处理 .....');

        //查询学校数据库连接地址
        $schoolDatabases = $this->getSchoolDatabases($schoolIds);
        foreach ($needHandleData as $schoolId=>$datum){
            $this->showMsg(1, '当前学校ID：' . $schoolId . ' .......');
            $this->databaseInfo = isset($schoolDatabases[$schoolId]) ? $schoolDatabases[$schoolId] : array();
            if (!$this->databaseInfo) {
                $this->showMsg(1, '学校ID：' . $schoolId . '没有数据库连接信息');
                continue;
            }
            //连接学校数据库
            $this->schConnObj = null;
            $this->schConnObj = $this->getSchConnObj();
            if (!$this->schConnObj) {
                $this->showMsg(1, '学校ID：' . $schoolId . '数据库连接失败！！！！！');
                continue;
            }

            MyActiveRecord::$schoolId = $schoolId;
            foreach ($datum as $examClass){
                $examGroupIds=explode(",", $examClass["exam_group_ids"]);
                $classIds = explode(",", $examClass["class_ids"]);
                if(!$examGroupIds || !$classIds){
                    $this->showMsg(1, '学校ID：' . $schoolId . '考试数据，班级数据设置异常');
                    continue;
                }
                $noExamClass=array();   //未参加任何考试的班级
                $haveExamClass=array(); //参加考试的班级
                $examRelationStudent=$this->getAllStudentByExamId($examGroupIds);
                foreach ($classIds as $classId){
                    $classJoinExam=array();
                    //读取班级学生id
                    $classStudent=$this->getStudentIdsByClassId($classId);
                    //查询学生参与的考试id
                    foreach ($examRelationStudent as $examId => $studentIds){
                        if(array_intersect($classStudent,$studentIds)){
                            $classJoinExam[]=$examId;
                        }
                    }
                    if(!$classJoinExam){
                        $noExamClass[]=$classId;
                    }else{
                        $haveExamClass[(string)$classId]=$classJoinExam;
                    }
                }


            }
        }

    }


    /**
     * 需要处理的数据
     * @return array|CDbDataReader
     */
    protected function getNeedHandleData()
    {
        $schoolRelExam = array();
        $time = time();
        //获取要生成教师讲案的数据
        $sql = "select id,school_id,gp_group_id,class_ids,exam_group_ids from xb_data_handle where  status = 1 and error_msg = '' ";
        $result = $this->getBusConn()->createCommand($sql)->queryAll();

        if ($result) {
            foreach ($result as $value) {
                $schoolId = $value['school_id'];
                if (!isset($schoolRelExam[$schoolId])) {
                    $schoolRelExam[$schoolId] = array();
                }
                $schoolRelExam[$schoolId][] = array(
                    'class_ids' => $value['class_ids'],
                    'gp_group_id' => $value['gp_group_id'],
                    'exam_group_id' => $value['exam_group_id'],
                    'id' => $value['id'],
                );
            }
        }

        return $schoolRelExam;
    }

    /**
     * 业务数据库对象
     * @return CDbConnection|null
     */
    protected function getBusConn()
    {
        $time = time();
        if ($this->busConnObj) {
            if ($time - $this->busDbTime > $this->dbTimeout) {
                echo '关闭超时业务数据库重新连接' . PHP_EOL;
            }else{
                return $this->busConnObj;
            }
        }

        $dbParams = Yii::app()->params["default_server"];
        $busConnObj = $this->getDbConnection($dbParams['addr'], Yii::app()->params["default_db"]['name'], $dbParams['username'], $dbParams['password']);
        $this->busConnObj = $busConnObj;
        $this->busDbTime = $time;

        return $busConnObj;
    }


    /**
     * 显示信息
     * @param $status
     * @param $msg
     */
    protected function showMsg($status,$msg)
    {
        echo $msg . PHP_EOL;
    }

    /**
     * 获取学校数据库
     * @param $schoolIds
     * @return array
     */
    protected function getSchoolDatabases($schoolIds)
    {
        $schoolDatabases = array();

        $sql = "select school_id,database_host,database_user,database_password,database_name,group_id from `database` where school_id in (" . implode(',', $schoolIds) . ")";

        $rs = $this->getBusConn()->createCommand($sql)->queryAll();

        if ($rs) {
            foreach ($rs as $value) {
                $schoolDatabases[$value['school_id']] = $value;
            }
        }

        return $schoolDatabases;
    }

    /**
     * 学校数据库对象
     * @return mixed
     */
    public function getSchConnObj()
    {
        $time = time();
        if ($this->schConnObj) {
            if ($time - $this->schDbTime > $this->dbTimeout) {
                echo '关闭超时学校数据库重新连接' . PHP_EOL;
            }else{
                return $this->schConnObj;
            }
        }

        $databaseInfo = $this->databaseInfo;
        $schConnObj = $this->getDbConnection($databaseInfo['database_host'], $databaseInfo['database_name'], $databaseInfo['database_user'], $databaseInfo['database_password']);
        $this->schConnObj = $schConnObj;
        $this->schDbTime = $time;

        return $schConnObj;
    }
    public function getDbConnection($databaseHost,$databaseName,$databaseUser,$databasePassword)
    {
        if($databaseHost && $databaseName && $databaseUser && $databasePassword){
            $myDbDsn = 'mysql:host=' . $databaseHost . ';dbname=' . $databaseName;
            $myConnection = new CDbConnection($myDbDsn, $databaseUser, $databasePassword);

            $myConnection->emulatePrepare = true;
            $myConnection->enableProfiling = true;
            $myConnection->enableParamLogging = true;

            $myDbDsn = null;

            return $myConnection;
        }else{
            return null;
        }
    }
    //读取考试id
    private function getExamIds($examGroupId){
        $sql="select exam_id from exam where exam_group_id='{$examGroupId}'";
        $result = $this->getSchConnObj()->createCommand($sql)->queryAll();
        $examIds=array();
        if($result){
            foreach ($result as $item){
                $examIds[]=$item['exam_id'];
            }
        }
        return $examIds;
    }
    //读取班级学生
    private function getStudentIdsByClassId($classId){
        $sql="select student_id from student_class_relation where class_id='{$classId}' and status=0";
        $result = $this->getSchConnObj()->createCommand($sql)->queryAll();
        $studentIds=array();
        if($result){
            foreach ($result as $item){
                $studentIds[]=$item['student_id'];
            }
        }
        return $studentIds;
    }


    //查询所有参加学生
    private function getAllStudentByExamId($examGroupIds){
        $studentRelationExamId=array();
        foreach ($examGroupIds as $examGroupId){
            $examIds=$this->getExamIds($examGroupId);
            $sql="select student_id,exam_id from  student_paper_relation where exam_id in(".implode(',',$examIds).") and is_del=0 and is_feedback=1";
            $result = $this->getSchConnObj()->createCommand($sql)->queryAll();
            if($result){
                foreach ($result as $item){
                    $studentRelationExamId[(string)$item['exam_id']][]=$item['student_id'];
                }
            }

        }
        return $studentRelationExamId;
    }
}