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

<?php


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

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


    public function getMarkingCount($mrId)
    {
        $sql = "select count(distinct student_id) as count from english_magic_word_marking where mw_id={$mrId};";
        $connect = $this->getDbConnection();
        $rs = $connect->createCommand($sql)->queryRow();
        $markCount= isset($rs['count']) ? $rs['count'] : 0;

        $scoreSql = "select count(distinct student_id) as count from english_magic_word_score where mw_id={$mrId};";
        $scoreRs = $connect->createCommand($scoreSql)->queryRow();
        $scoreCount = isset($scoreRs['count']) ? $scoreRs['count'] : 0;

        return min($scoreCount, $markCount);
    }

    public function getStuMarkingStatus($studentIds, $mrId)
    {
        $result = array();
        if ($studentIds) {
            $sql = "select distinct m.student_id from english_magic_word_marking m inner join english_magic_word_score s on m.mw_id=s.mw_id and s.student_id=m.student_id where m.mw_id={$mrId} and m.student_id IN (" . implode(',', $studentIds) . ")";
            $connect = $this->getDbConnection();
            $rs = $connect->createCommand($sql)->queryAll();
            if ($rs) {
                foreach ($rs as $item) {
                    $result[$item['student_id']] = 1;
                }
            }
        }


        return $result;
    }

    /**
     * 获取单词统计表格数据
     * @param $mwId
     * @return array
     * @throws CException
     */
    public function getWordList($mwId)
    {
        $result = array();
        $sql = "select m.student_id,m.is_right,m.right_answer,m.ocr_text,m.mw_sort,m.single_word_id,s.realname 
from english_magic_word_marking m  join student_info s on m.student_id=s.student_id where mw_id='{$mwId}';";
        $connect = $this->getDbConnection();
        $rs = $connect->createCommand($sql)->queryAll();
        //有派生的单词序号
        $wordNo = array();
        if ($rs) {

            $sql = "SELECT c.mw_group_id,c.word_list FROM `english_magic_word_card` c inner join english_magic_word w
           on c.mw_group_id=w.mw_group_id where w.mw_id='{$mwId}'";

            $connect = $this->getDbConnection();
            $cardData = $connect->createCommand($sql)->queryRow();
            $cardData = isset($cardData['word_list']) && json_decode($cardData['word_list'], true) ? json_decode($cardData['word_list'], true) : array();
            $wordIndex =array();
            $index = $wordKey=$derivativeIndex=1;
            //单词序号
            foreach ($cardData as $item) {
                if (isset($item['is_derivative']) && !$item['is_derivative']) {
                    $wordIndex[$item['single_word_id']] = $index;
                    $index++;
                }
            }
            //单词序号(含派生)
            if($wordIndex){
                foreach($cardData as $item){
                    if(isset($wordIndex[$item['single_word_id']])){
                        $no=$wordIndex[$item['single_word_id']];
                        if($item['is_derivative']){
                            $no.="(派生{$derivativeIndex})";
                            $derivativeIndex++;
                        }else{
                            $derivativeIndex=1;
                        }
                        $wordNo[$wordKey]=$no;
                    }
                    $wordKey++;
                }
            }

            foreach ($rs as $item) {
                $mwSort =$item['mw_sort'];
                $result[$mwSort]['right_rate'] = '';

                if(!isset($result[$mwSort]['right_count'])){
                    $result[$mwSort]['right_count'] = 0;
                }
                if($item['is_right']) {
                    $result[$mwSort]['right_count'] += 1;
                }

                $result[$mwSort]['no'] = $item['mw_sort'];
                $result[$mwSort]['mw_sort'] = $item['mw_sort'];
                $result[$mwSort]['single_word_id'] = $item['single_word_id'];
                $result[$mwSort]['right_answer'] = str_replace('&nbsp;',' ',$item['right_answer']);;
                if(!isset($result[$mwSort]['wrong_list'])){
                    $result[$mwSort]['wrong_list'] = '';
                }

                if (!$item['is_right']) {
                    $result[$mwSort]['wrong_list'] .= sprintf('%s(%s) ', $item['realname'], $item['ocr_text']).'，';
                }
                if (isset($result[$mwSort]['total_count'])) {
                    $result[$mwSort]['total_count'] += 1;
                } else {
                    $result[$mwSort]['total_count'] = 1;
                }
            }
        }
        $list = array_values($result);

        $rate = array();
        foreach ($list as $key => &$item) {
            $item['right_rate'] = $item['total_count'] ? round($item['right_count'] / $item['total_count'], 2) * 100 . '%' : 0;
            $item['rate'] = $item['total_count'] ? round($item['right_count'] / $item['total_count'], 2)  : 0;
            //去掉末尾的逗号
            $item['wrong_list']=trim($item['wrong_list'],'，');
            array_push($rate, $item['rate']);
            unset($list[$key]['total_count']);
        }

        if($list && $rate) {
            array_multisort($rate, SORT_DESC, $list);
        }

        if($wordNo){
            foreach($list as &$item){
                if(isset($wordNo[$item['mw_sort']]) ){
                    $item['no']=$wordNo[$item['mw_sort']];
                }else{
                    $item['no']='';
                }
            }
        }

        return $list;
    }

    /**
     * 获取班级学生词汇宝统计表格数据
     * @param $mwId
     * @param $classId
     * @return array
     * @throws CException
     */
    public function getStudentRateList($mwId, $classId)
    {
        $result = array();
        $sql = "select m.score,m.score_rate,m.words_count,s.realname,s.student_id,scr.serial_number,m.create_time,m.single_words_count
from english_magic_word_score m inner join student_info s on m.student_id=s.student_id 
join student_class_relation as scr on scr.student_id = m.student_id and scr.class_id = '{$classId}' where mw_id='{$mwId}';";
        $connect = $this->getDbConnection();
        $rs = $connect->createCommand($sql)->queryAll();
        if ($rs) {
            foreach ($rs as $item) {
                $result[$item['student_id']]['right_rate'] = $item['score_rate'] . '%';
                $result[$item['student_id']]['student_name'] = $item['realname'];
                $result[$item['student_id']]['no'] = $item['serial_number'];
                //$result[$item['student_id']]['wrong_count'] = round((1 - $item['score_rate'] / 100) * $item['words_count']);
                $result[$item['student_id']]['wrong_count'] = $item['words_count'] - $item['score'] >= 0 ? $item['words_count'] - $item['score'] : 0;
                $result[$item['student_id']]['rate_change'] = '+0%';
                $result[$item['student_id']]['rate'] = $item['score_rate'];
                $result[$item['student_id']]['create_time'] = $item['create_time'];
            }
        }

        $oldestSql = "select m.student_id,m.score_rate,m.create_time from english_magic_word_score m inner join english_magic_word w
on m.mw_id=w.mw_id where  w.class_id={$classId} order by m.create_time asc";
        $oldest = $connect->createCommand($oldestSql)->queryAll();
        if ($oldest) {
            foreach ($oldest as $item) {
                if (isset($result[$item['student_id']])) {
                    if (isset($result[$item['student_id']]['rate']) && isset($result[$item['student_id']]['create_time']) && $item['create_time']<$result[$item['student_id']]['create_time']) {
                        $result[$item['student_id']]['rate_change'] = $item['score_rate'] > $result[$item['student_id']]['rate'] ? '-' . ($item['score_rate'] - $result[$item['student_id']]['rate']) . '%' : '+' . ($result[$item['student_id']]['rate'] - $item['score_rate']) . '%';
                    }
                }
            }
        }

        $result =  array_values($result);
        if($result) {
            $rate = _array_column($result, 'rate');
            array_multisort($rate, SORT_DESC, $result);
        }
        return $result;
    }
}