xiaoben-as2-php/protected/models/SStudentPaperTopicRs.php

<?php
/**
 * @author: CeeFee
 * @description: 班级
 */

class SStudentPaperTopicRs extends MyActiveRecord
{
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'student_paper_topic_rs';
    }

    public function getRelationsByPaperId_StudentIds($paperId, $studentIds)
    {
        $result = array();

        if ($paperId AND is_numeric($paperId) AND $studentIds AND is_array($studentIds)) {
            $criteria = new CDbCriteria();
            $criteria->addCondition("paper_id = '" . $paperId . "' AND student_id IN (" . implode(',', $studentIds) . ")");
            $criteria->order = 'id ASC';

            $result = getAttributes($this->findAll($criteria));
        }

        return $result;
    }

    // 获取答错题的学生记录
    public function getWrongByStudentIds($paperId, $studentIds)
    {
        $result = array();

        if ($paperId AND is_numeric($paperId) AND $studentIds AND is_array($studentIds)) {

            $criteria = new CDbCriteria();
            $criteria->addCondition("paper_id = '" . $paperId . "' AND student_id IN (" . implode(',', $studentIds) . ") and is_right!=1");
            $criteria->order = 'student_id ASC,paper_id ASC,type ASC';

            $result = getAttributes($this->findAll($criteria));
        }

        return $result;
    }

    public function l_getWrongByStudentIds($paperId, $studentIds)
    {
        $result = array();

        if ($paperId AND is_numeric($paperId) AND $studentIds AND is_array($studentIds)) {

            $criteria = new CDbCriteria();
            $criteria->addCondition("paper_id = '" . $paperId . "' AND student_id IN (" . implode(',', $studentIds) . ")");
            $criteria->order = 'student_id ASC,paper_id ASC,type ASC';

            $result = getAttributes($this->findAll($criteria));
        }

        return $result;
    }

    // 刘红伟
    public function ll_getWrongByStudentIds($paperId, $studentIds)
    {
        $result = array();

        if ($paperId AND is_numeric($paperId) AND $studentIds AND is_array($studentIds)) {

            $criteria = new CDbCriteria();
            $criteria->addCondition("paper_id = '" . $paperId . "' AND student_id IN (" . implode(',', $studentIds) . ")");
            $criteria->order = 'student_id ASC,paper_id ASC,type ASC';
            $pager = new CPagination($this->count($criteria));
            $pager->pageSize = 10;
            $pager->applyLimit($criteria);

            return array('result' => $this->findAll($criteria), 'pager' => $pager);


        }

        return $result;
    }

    // 根据试卷id 学生id 试题id获取记录
    public function getStudentWrongRecords($paperId, $studentIds, $topicIds)
    {
        $result = array();

        if ($paperId AND is_numeric($paperId) AND $studentIds AND is_array($studentIds)) {
            $criteria = new CDbCriteria();
            $criteria->addCondition("paper_id = '" . $paperId . "' and student_id in (" . implode(',', $studentIds) . ") and topic_id in (" . implode(',', $topicIds) . ") and is_right!=1");
            $criteria->order = 'paper_id ASC,student_id ASC,topic_id ASC';

            $result = getAttributes($this->findAll($criteria));
        }

        return $result;
    }

    /**
     * 获取试卷学生做题记录
     * @param $paperId
     * @return array|CDbDataReader
     */
    public function getStudentRsOnePaper($paperId)
    {
        $newInfo = array();
        $sql = "select student_id,paper_id,`topic_id`,`type`,is_right,handlerIndex,answer,answer_url,scoring from " . $this->tableName() . " where paper_id = {$paperId} and handlerIndex = 1 order by `id` ";

        $info = $this->getCommandBuilder()->createSqlCommand($sql)->queryAll();

        foreach ($info as $value) {
            $stu_id = (string)$value['student_id'];
            $temp['paper_id'] = $value['paper_id'];
            $temp['topic_id'] = $value['topic_id'];
            $temp['type'] = $value['type'];
            $temp['is_right'] = $value['is_right'];
            $temp['handlerIndex'] = $value['handlerIndex'];
            if ($value['type'] == 1 || $value['type'] == 11) {
                $temp['answer'] = $value['answer'];
            } else {
                $temp['answer'] = $value['answer_url'];
            }
            $temp['scoring'] = $value['scoring'];

            if (!isset($newInfo[$stu_id])) {
                $newInfo[$stu_id] = array();
            }
            $newInfo[$stu_id][$value['topic_id']] = $temp;
        }
        unset($info);

        return $newInfo;
    }

    /**
     * 获取多个试卷学生做题记录
     * @param $paperIds
     * @return array
     */
    public function getStudentRs($paperIds)
    {
        $newInfo = array();
        $testStuIds = array();

        $_countSql = "select count(0) AS count from " . $this->tableName() . " where paper_id in (" . implode(',', $paperIds) . ") and handlerIndex = 1";
        $counTinfo = $this->getCommandBuilder()->createSqlCommand($_countSql)->queryRow();

        $count = 0;
        if (isset($counTinfo['count'])) {
            $count = $counTinfo['count'];
        } else {
            return $newInfo;
        }

        $limit = 20000;
        $page = ceil($count / $limit);

        $__sql = "select student_id,paper_id,`topic_id`,`type`,is_right,handlerIndex,answer,answer_url,scoring from " . $this->tableName() . " where paper_id in (" . implode(',', $paperIds) . ") and handlerIndex = 1 order by `paper_id` ";
        for ($i = 0; $i < $page; $i++) {
            $offset = $i * $limit;

            $sql = $__sql . " limit {$offset},{$limit}";
            $info = $this->getCommandBuilder()->createSqlCommand($sql)->queryAll();

            if ($info) {
                foreach ($info as $value) {
                    $stu_id = (string)$value['student_id'];
                    $temp['paper_id'] = $value['paper_id'];
                    $temp['topic_id'] = $value['topic_id'];
                    $temp['type'] = $value['type'];
                    $temp['is_right'] = $value['is_right'];
                    $temp['handlerIndex'] = $value['handlerIndex'];
//                    if($value['type'] == 1 || $value['type'] == 11){
//                        $temp['answer'] = $value['answer'];
//                    }else{
//                        $temp['answer'] = $value['answer_url'];
//                    }

                    if (trim($value['answer_url'])) {
                        $temp['answer'] = $value['answer_url'];
                    } else {
                        $temp['answer'] = $value['answer'];
                    }
                    $temp['scoring'] = $value['scoring'];

                    if (!isset($newInfo[$stu_id])) {
                        $newInfo[$stu_id] = array();
                    }
                    $newInfo[$stu_id][$value['topic_id']] = $temp;
                    $testStuIds[] = $stu_id;
                }
            }
            unset($info);
        }


        return $newInfo;
    }

    /**
     * 获取上传成绩的学生
     * @param $paperIds
     * @return array|CDbDataReader
     */
    public function getHasScoreStu($examIds)
    {
        $studentIds = array();
        $sql = "select student_id,exam_id  from " . $this->tableName() . "  as sptr join paper as p on sptr.paper_id = p.paper_id where p.exam_id in (" . implode(',', $examIds) . ") and handlerIndex = 1";
        $info = $this->getCommandBuilder()->createSqlCommand($sql)->queryAll();
        if ($info) {
            foreach ($info as $value) {
                if (!isset($studentIds[$value['exam_id']])) {
                    $studentIds[$value['exam_id']] = array();
                }
                $studentIds[$value['exam_id']][$value['student_id']] = $value['student_id'];
            }
            foreach ($studentIds as $eId => $stuIds) {
                $studentIds[$eId] = array_keys($stuIds);
            }
            unset($info);
        }

        return $studentIds;
    }

    /**
     * 做题id获取信息
     * @param $ids
     * @return array
     */
    public function getStuRsByIds($ids)
    {
        $res = array();
        if ($ids) {
            $sql = "select id,student_id,topic_id,`answer_url`,scoring,simplify_answer_url,answer_trace from student_paper_topic_rs where id in (" . implode(',', $ids) . ") and handlerIndex = 1";

            $res = $this->getCommandBuilder()->createSqlCommand($sql)->queryAll();
        }

        return $res;
    }

    /** 同学优解
     * @param $exam
     * @param $codeNow
     * @param $staticUrl
     * @param $conn
     * @param $isQxkPage是否全学科考试1是2不是
     * @return array
     * @throws CDbException
     * @throws CException
     */
    public function bestAnswer($exam, $codeNow, $staticUrl, $conn, $isQxkPage = 0)
    {
        $tableName = empty($codeNow) ? "student_paper_topic_rs" : "student_paper_topic_rs_" . $codeNow;

        $fine_topic_score = array();
        $topicIds = array();
        $studentIds = array();
        $excellent_solution = array();
        if ($isQxkPage == 1) {//如果是全学科的题目
            $typeArr = array(8, 9);
        } else {
            $typeArr = array(7, 17, 27);
        }
        //获取考试试题对应优解分数
        $topic_sql = "select topic_id,score from paper_topic_relation where paper_id = '{$exam['paper_id']}' and type in (" . join(",", $typeArr) . ")";
        $topic_sql_rs = $conn->createCommand($topic_sql)->queryAll();
        foreach ($topic_sql_rs as $item) {
            $fine_topic_score[$item['topic_id']] = 0.8 * $item['score'];
            $topicIds[$item['topic_id']] = $item['topic_id'];
        }

        // FIXME 1:优先取本班标记得分最高的优解
        $sql = "select m.paper_id,m.student_id,m.sptr_id,m.topic_id,sptr.answer_url from marking_special_answer m inner join {$tableName} sptr on m.sptr_id=sptr.id where m.paper_id = '{$exam['paper_id']}' and m.type = 2 group by m.topic_id  order by sptr.scoring desc";
        $special_data = $conn->createCommand($sql)->queryAll();
        foreach ($special_data as $item) {
            $excellent_solution[(string)$item['topic_id']] = $item;
            array_push($studentIds, $item['student_id']);
            unset($topicIds[$item['topic_id']]);
        }

        // FIXME 2:本班得分高于80%的最高分
        if ($topicIds) {
            $sql = "select paper_id,student_id,topic_id,answer_url from {$tableName} where paper_id = '{$exam['paper_id']}' and type = 2 group by topic_id order by scoring desc";
            $class_data = $conn->createCommand($sql)->queryAll();
            foreach ($class_data as $item) {
                if (in_array($item['topic_id'], $topicIds) && $item['topic_id'] >= $fine_topic_score[$item['topic_id']]) {
                    array_push($studentIds, $item['student_id']);
                    $excellent_solution[(string)$item['topic_id']] = $item;
                    unset($topicIds[$item['topic_id']]);
                }
            }
        }

        // FIXME 3:年级标记的得分最高的优解
        if ($topicIds) {
            $sql = <<<SQL
SELECT
	r.student_id,
	r.topic_id,
	r.scoring,
	r.answer_url
FROM
	marking_special_answer m
INNER JOIN student_paper_topic_rs r ON m.student_id = r.student_id
INNER JOIN exam e ON e.exam_id = m.exam_id
AND m.topic_id = r.topic_id AND m.paper_id=r.paper_id 
INNER JOIN exam_group g ON e.exam_group_id=g.exam_group_id 
WHERE
	m.exam_group_id = '{$exam['exam_group_id']}'
AND m.type = 2
AND e.status = 1 
AND IF(g.mark_type=0 OR g.import_score_type=3, e.is_backlog=1,TRUE)
ORDER BY r.scoring DESC;
SQL;
            $grade_special_data = $conn->createCommand($sql)->queryAll();
            foreach ($grade_special_data as $item) {
                if (in_array($item['topic_id'], $topicIds)) {
                    array_push($studentIds, $item['student_id']);
                    $excellent_solution[(string)$item['topic_id']] = $item;
                    unset($topicIds[$item['topic_id']]);
                }
            }
        }

        //FIXME 4:年级得分高于80%的最高分
        $paper_ids_sql = "SELECT p.paper_id FROM paper p INNER JOIN exam e ON p.exam_id=e.exam_id INNER JOIN exam_group g ON e.exam_group_id=g.exam_group_id WHERE e.exam_group_id='{$exam['exam_group_id']}' and e.`status`=1 AND IF(g.mark_type=0 OR g.import_score_type=3, e.is_backlog=1,TRUE);";
        $paper_ids_res = $conn->createCommand($paper_ids_sql)->queryAll();
        $paper_ids = array();
        foreach ($paper_ids_res as $item) {
            $paper_ids[] = $item['paper_id'];
        }
        if (empty($paper_ids)) {
            $paper_ids = array($exam['paper_id']);
        }

        $paper_ids_str = implode(',', $paper_ids);
        if ($topicIds) {
            $topic_rs_sql = "select student_id,topic_id,scoring,answer_url from (select * from student_paper_topic_rs where type in (" . join(",", $typeArr) . ") and paper_id in ({$paper_ids_str}) order by scoring desc) as a group by a.topic_id;";
            $topic_rs = $conn->createCommand($topic_rs_sql)->queryAll();;

            foreach ($topic_rs as $item) {
                if (in_array($item['topic_id'], $topicIds) && $item['topic_id'] >= $fine_topic_score[$item['topic_id']]) {
                    array_push($studentIds, $item['student_id']);
                    $excellent_solution[(string)$item['topic_id']] = $item;
                }
            }
        }

        //FIXME 优解学生信息
        $stuInfos = array();
        $stu_id_str = trim(implode(',', array_unique($studentIds)), ',');
        $besetAnswer = array();
        if ($stu_id_str) {
            $stuSql = "SELECT s.student_id,s.realname,c.class_name FROM student_paper_relation p LEFT JOIN student_info s ON s.student_id = p.student_id LEFT JOIN class c ON p.class_id = c.class_id WHERE p.student_id IN ({$stu_id_str}) AND p.paper_id IN ({$paper_ids_str});";
            $stuRs = $conn->createCommand($stuSql)->queryAll();
            foreach ($stuRs as $stu) {
                $stuInfos[$stu['student_id']] = $stu;
            }

            foreach ($excellent_solution as $item) {
                $besetAnswer[(string)$item['topic_id']]['class_name'] = isset($stuInfos[$item['student_id']]) ? $stuInfos[$item['student_id']]['class_name'] : '';
                $besetAnswer[(string)$item['topic_id']]['student_name'] = isset($stuInfos[$item['student_id']]) ? $stuInfos[$item['student_id']]['realname'] : '';
                if (strpos($item['answer_url'], 'http') !== false) {
                    $besetAnswer[(string)$item['topic_id']]['answer_url'] = $item['answer_url'];
                } else {
                    $besetAnswer[(string)$item['topic_id']]['answer_url'] = $staticUrl . $item['answer_url'];
                }
            }
        }

        return $besetAnswer;
    }


}