<?php
Yii::import('application.models.*');
Yii::import('application.components.*');

/**
 * 英语外刊宝脚本同步
 */
class EnglishReadingCommand extends CConsoleCommand
{
    public function init()
    {
        parent::init();

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

    /***
     * 脚本可选参数 学校id：schoolId
     *
     *
     * 英语外刊宝脚本同步/usr/local/php5.3/bin/php /home/www/vhosts/zxhx.testing/zsyas2/protected/shell/yiic.php englishreading index  schoolId=3980  --YII_ENV=testing
     *
     *
     */
    public function actionIndex($YII_ENV = 'development')
    {
        echo 'start 英语外刊宝同步脚本.....' . PHP_EOL;

        foreach ($_SERVER['argv'] as $k => $v) {
            if (strpos($v, 'schoolId=') !== FALSE) {
                $schoolId = substr($v, strlen('schoolId='));
            }
        }

        $schools = isset($schoolId) ? array(0=>array('school_id'=> $schoolId)) : $this->getSchools();

        foreach ($schools as $school) {
            echo 'start school_id:' . $school['school_id'] . '...' . PHP_EOL;
            $con = $this->getSchoolDbCon($school['school_id']);
            if (!$con) {
                echo 'school database cannot connect' . PHP_EOL;
                continue;
            }
            try {

                self::syncTpl($con);

                echo 'school_id:'.$school['school_id'] . 'done' . PHP_EOL;
            } catch (\Exception $e) {
                echo $e->getMessage() . PHP_EOL;
                continue;
            }
            sleep(0.1);
        }

        echo 'end 处理完成' . PHP_EOL;
        exit;
    }

    /**
     * 获取所有正常可用的学校
     * @return mixed
     */
    private function getSchools()
    {
        $db = Yii::app()->businessDb;
        $sql = "SELECT school_id,school_name FROM `school` WHERE `status`=0 ";
        $schools = $db->createCommand($sql)->queryAll();
        $db->close();
        return $schools;
    }

    /**
     * 学校库连接
     * @param $schoolId
     * @return bool|CDbConnection
     */
    public function getSchoolDbCon($schoolId)
    {
        $db = BusinessDatabase::model()->find('school_id=:sid', array(':sid' => $schoolId));
        if (empty($db)) {
            return false;
        }

        $myDbDsn = 'mysql:host=' . $db->database_host . ';dbname=' . $db->database_name;
        $my_connection = new CDbConnection($myDbDsn, $db->database_user, $db->database_password);

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

        $myDbDsn = null;

        return $my_connection;
    }

    /**
     * 同步模板数据
     * @param $con object 数据库连接
     */
    private function syncTpl($con)
    {
        //找到当前的学期
        $semesterSql='select * from semester where status=1 order by start_time desc';
        $semester = $con->createCommand($semesterSql)->queryRow();
        if(!$semester){
            echo '没有可用的学期信息'.PHP_EOL;
            return;
        }
        //当前学期的考试
        $examsSql="select * from (
                    SELECT spr.class_id,spr.exam_id,e.create_time,e.subject_id FROM student_paper_relation as spr LEFT JOIN `exam` as e ON spr.exam_id = e.exam_id
                    WHERE  spr.semester_id='{$semester['semester_id']}' AND e.exam_group_id > 0 AND spr.is_feedback=1 AND spr.is_complete=1  AND spr.is_del = 0 AND  e.status = 1  AND e.subject_id=8
                    ORDER BY e.create_time DESC
                    ) t group by t.class_id";
        $paperStudentInfo=array();
        $exam = $con->createCommand($examsSql)->queryAll();
        if($exam) {
            $examIds = array();
            foreach ($exam as $item) {
                $examIds[] = $item['exam_id'];
            }
            $examIds = implode(',', $examIds);
            //考试成绩
            $sql = "select spr.student_id,spr.scoring,p.score,spr.class_id,p.exam_id,p.paper_id from paper p
                    inner join  student_paper_relation spr on p.paper_id=spr.paper_id
                    where p.exam_id in ({$examIds}) and spr.is_feedback=1 and spr.is_complete=1 order by spr.class_id asc ";
            $paperStudentInfo = $con->createCommand($sql)->queryAll();
        }
        if(!$paperStudentInfo){
           //学期数据
           $sql="SELECT semester_id,semester_name,`status`,refer_code,start_time,end_time FROM `semester` ORDER BY  start_time  DESC";
           $semesters=$con->createCommand($sql)->queryAll();
           $isLatelySemester=false;
           $latelySemester=array();//当前学期的上学期
           foreach($semesters as $item){
               if($isLatelySemester){
                   $latelySemester=$item;
               }
               if(!$isLatelySemester && $item['status']){
                   $isLatelySemester=true;
                   continue;
               }
           }
           if($latelySemester){
               //上学期考试
               $examsSql="select * from (
                    SELECT spr.class_id,spr.exam_id,e.create_time,e.subject_id FROM student_paper_relation_{$latelySemester['refer_code']} as spr LEFT JOIN `exam` as e ON spr.exam_id = e.exam_id
                    WHERE  spr.semester_id='{$latelySemester['semester_id']}' AND e.exam_group_id > 0 AND spr.is_feedback=1 AND spr.is_complete=1  AND spr.is_del = 0 AND  e.status = 1  AND e.subject_id=8
                    ORDER BY e.create_time DESC
                    ) t group by t.class_id";
               $exam = $con->createCommand($examsSql)->queryAll();
               if(!$exam){
                   echo '没有可用的考试信息!!'.PHP_EOL;
                   return;
               }
               $examIds=array();
               foreach($exam as $item){
                   $examIds[]=$item['exam_id'];
               }
               $examIds=implode(',',$examIds);
               //考试成绩
               $sql="select spr.student_id,spr.scoring,p.score,spr.class_id,p.exam_id,p.paper_id from paper p
                    inner join  student_paper_relation_{$latelySemester['refer_code']} spr on p.paper_id=spr.paper_id
                    where p.exam_id in ({$examIds}) and spr.is_feedback=1 and spr.is_complete=1 order by spr.class_id asc";
               $paperStudentInfo=$con->createCommand($sql)->queryAll();
           }
       }
       $studentLevel=array();
       foreach($paperStudentInfo as $item){
           if(!$item['score'] || intval($item['score'])<intval($item['scoring'])){
               continue;
           }
           $rate=round(intval($item['scoring'])/intval($item['score']),4)*100;
           $level=2;
           if($rate<=60){
               $level=1;
           }elseif($rate>60 && $rate<=80){
               $level=2;
           }elseif($rate>80){
               $level=3;
           }
           $studentLevel[$level][]=$item['student_id'];
       }
       if($studentLevel){
           foreach($studentLevel as $key=>$studentsIds){
               $studentsIdsStr=implode(',',$studentsIds);
               $sql = "update student_info set reading_level={$key} where student_id in ({$studentsIdsStr})";
               echo $sql.PHP_EOL;
               $con->createCommand($sql)->execute();
           }
       }
       echo 'end'.PHP_EOL;
    }
}