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

<?php


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

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

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

        $orderBy = $this->orderBy($orderBy);
        $sCon = $this->getDbConnection();
        $handle = $sCon->createCommand("
                    select ew.ew_id,ew.ew_group_id,ew.`name`,c.class_id,c.class_name,is_marking_all_html,marking_status,ew.reset_times,ew.create_time,ew.week_num,ews.topic_no,gu.is_write 
                    from english_writing ew  join class c on ew.class_id = c.class_id  join english_writing_setting ews on ew.ew_group_id = ews.ew_group_id
                    join english_writing_guidance gu on ews.ewg_id=gu.ewg_id
                    {$condition}
                    group by ew.ew_id
                    {$orderBy}
            ")->query();

        $rs = $this->paging($sCon, $handle, $pageSize);
        $markingModel = new SEnglishWritingMarking();
        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['ew_id']);

                $week_id = $v['ew_id'];
                $sql = "select COUNT(student_id) AS totalCount,SUM(CASE WHEN is_week_pdf = 1 THEN 1 ELSE 0 END) AS pdfCount,SUM(CASE WHEN is_card_pdf = 1 THEN 1 ELSE 0 END) AS cardCount from english_writing_student where ew_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]['cardCount'] = isset($countArr['cardCount'])?$countArr['cardCount']:0;
                    $rs['rs'][$k]['markCount'] = $markingModel->getMarkingCount($week_id);
                }
                $sql = "select week_download_time from english_writing_student where ew_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;
    }

    public function getWeekOrder($ewId)
    {
        $ewModel = self::findByPk($ewId);
        if($ewModel) {
            $sql = "select count(*) from english_writing ew left join english_writing_setting ews on ews.ew_group_id=ew.ew_group_id where ew_id < '{$ewId}' and ews.product_type=32 and class_id = '{$ewModel['class_id']}' and year_num={$ewModel['year_num']} and week_num={$ewModel['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);
    }
}