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

<?php


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

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

    /**
     * 词汇宝产品列表
     */
    public function getMagicWord($condition = array(), $orderBy = array("er.create_time desc"), $pageSize = 10){
        $condition = $this->condition($condition);

        $orderBy = $this->orderBy($orderBy);
        $sCon = $this->getDbConnection();
        $handle = $sCon->createCommand("
                    select er.mw_id,er.mw_group_id,er.`name`,c.class_id,c.class_name,ers.type,ers.practice_type,ers.scan_type,is_marking_all_html,marking_status,er.reset_times,er.create_time,er.week_num from english_magic_word er  join class c on er.class_id = c.class_id  join english_magic_word_setting ers on er.mw_group_id = ers.mw_group_id
                    {$condition}
                    group by er.mw_id
                    {$orderBy}
            ")->query();

        $rs = $this->paging($sCon, $handle, $pageSize);
        $markingModel = new SEnglishMagicWordMarking();
        if($rs['rs']){
            foreach($rs['rs'] as $k=>$v){
                $rs['rs'][$k]['totalCount'] = 0;
                $rs['rs'][$k]['pdfCount'] = 0;
                $rs['rs'][$k]['isDown'] = 0;
                $rs['rs'][$k]['downTime'] = '';
                $rs['rs'][$k]['type_name'] = '';
                $rs['rs'][$k]['week_no'] = $this->getWeekOrder($v['mw_id']);
                $rs['rs'][$k]['is_card_complete'] = $this->isCardComplete($v['mw_id']);

                $week_id = $v['mw_id'];
                $sql = "select COUNT(student_id) AS totalCount,SUM(CASE WHEN is_week_pdf = 1 THEN 1 ELSE 0 END) AS pdfCount from english_magic_word_student where mw_id = '{$week_id}'";
                $countArr = $sCon->createCommand($sql)->queryRow();
                if($countArr){
                    $rs['rs'][$k]['totalCount'] = isset($countArr['totalCount'])?$countArr['totalCount']:0;
                    $rs['rs'][$k]['pdfCount'] = isset($countArr['pdfCount'])?$countArr['pdfCount']:0;
                    $rs['rs'][$k]['markCount'] = $markingModel->getMarkingCount($week_id);
                }
                $sql = "select week_download_time from english_magic_word_student where mw_id = '{$week_id}' and is_week_download = 1 order by week_download_time desc limit 1";
                $isDownArr = $sCon->createCommand($sql)->queryRow();
                if($isDownArr){
                    $rs['rs'][$k]['isDown'] = 1;
                    $rs['rs'][$k]['downTime'] = isset($isDownArr['week_download_time'])?date("Y-m-d H:i",$isDownArr['week_download_time']):'';
                }
            }
        }

        return $rs;
    }

    /**
     * 批改后pdf是否全部生成
     * @param $mwId
     * @return bool
     */
    public function isCardComplete($mwId){
        $sql = "select SUM(IF(ws.is_card_pdf=1,1,0)) as pdf_count,SUM(IF(s.create_time IS NOT NULL, 1, 0)) as mark_count from english_magic_word_student ws
 left JOIN english_magic_word_score s on s.mw_id=ws.mw_id and s.student_id=ws.student_id
 where  ws.mw_id='{$mwId}'";
        $rs = $this->getDbConnection()->createCommand($sql)->queryRow();
        if($rs && $rs['pdf_count']>0){
            return true;
        }else{
            return false;
        }
    }

    public function getWeekOrder($mwId)
    {
        $mwModel = self::findByPk($mwId);
        if($mwModel) {
            $sql = "select count(*) from english_magic_word em left join english_magic_word_setting ems on ems.mw_group_id=em.mw_group_id where mw_id < '{$mwId}' and ems.product_type=33 and class_id = '{$mwModel['class_id']}' and year_num={$mwModel['year_num']} and week_num={$mwModel['week_num']}";
            $rs = $this->getDbConnection()->createCommand($sql)->queryScalar();
        }

        return isset($rs) ? $rs + 1 : 1;
    }

    //分页获取学生数据
    public function getStudentsByPages(){
        $result = array();
        $sql = "select count(0) as count from student_info";
        $connect = $this->getDbConnection();
        $countArr = $connect->createCommand($sql)->queryRow();
        if($countArr && $countArr['count']){
            $count = $countArr['count'];
            $limit = 10000;
            $page = ceil($count/$limit);

            $_sql = "select * from student_info";
            for($a = 1;$a <= $page;$a++){
                $offset = ($a - 1)*$limit;
                $_sqls = $_sql." limit {$offset},{$limit}";
                $data = $connect->createCommand($_sqls)->queryAll();
                foreach($data as $k=>$v){
                    $result['school_student_info'][$v['student_id']] = $v;
                    $result['school_student_names'][$v['student_id']] = $v['realname'];
                }
                unset($data);
            }
        }
        return $result;
    }

    public function condition($condition = array()){
        return $condition ? " where ".implode(" and ", $condition) : "";
    }

    public function orderBy($orderBy = array()){
        return $orderBy ? " order by ".implode(",", $orderBy) : "";
    }

    public function paging($conn, $handle, $pageSize){
        $pager = new CPagination($handle->rowCount, $pageSize);
        $handle = $conn->createCommand($handle->queryString." limit :offset, :limit");
        $handle->bindValue(":offset", $pager->currentPage * $pager->pageSize);
        $handle->bindValue(":limit", $pager->pageSize);
        $rs = $handle->queryAll();

        return array("rs" => $rs, "pager" => $pager);
    }
}