colleague / study-project · Files

<?php
/**
 * 试卷表
 * @author: houyingcai
 * @email:     594609175@qq.com
 * @date :  2017-05-19 18:06:08
 * @version $Id$
 */

namespace Common\Model;

use Common\Model\RightModel;

class PaperModel extends AbstractModel
{

    // 总数
    const QUERY_SQL_TYPE_COUNT = 0;
    // 列表
    const QUERY_SQL_TYPE_LIST = 1;

    /**
     * 构造方法
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * 根据条件查询试卷总数
     *
     * @author: 蔡建华
     *
     * @param $data array 查询条件
     *
     * @return int|mixed
     */
    public function count_by_paper($data = [])
    {
        list($where, $params) = $this->get_where_paper($data);
        $sql = 'SELECT COUNT(*) FROM __TABLE__ WHERE ' . $where;

        return $this->_m->result($sql, $params);
    }

    /**
     * 根据条件查询试卷列表
     *
     * @author: 蔡建华
     *
     * @param $data array 查询条件
     * @param null $page_option 分页参数
     * @param array $order_option 排序参数
     * @param string $fields 查询的字段
     *
     * @return array|bool
     */
    public function list_by_paper($data, $page_option = null, $order_option = [], $fields = '*')
    {
        list($where, $params) = $this->get_where_paper($data);
        // 排序
        $orderby = '';
        if (!$this->_order_by($orderby, $order_option)) {
            return false;
        }
        // 分页参数
        $limit = '';
        if (!$this->_limit($limit, $page_option)) {
            return false;
        }
        $sql = "SELECT {$fields} FROM __TABLE__ WHERE " . $where . " {$orderby}{$limit} ";

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 拼接Sql语句
     *
     * @author: 蔡建华
     *
     * @param array $data 查询条件
     *
     * @return array
     */
    public function get_where_paper($data = [])
    {
        // 组装查询语句
        $where = "status <? AND domain=?";
        // 操作状态和域名
        $params[] = PaperModel::ST_DELETE;
        $params[] = QY_DOMAIN;

        $where .= " and cate_status = ? ";
        $params[] = self::EC_OPEN_STATES;
        if ($data['ec_id'] > 0) {
            $where .= " and ec_id = ? ";
            $params[] = $data['ec_id'];
        }
        // 考试状态
        $where .= " and exam_status > ? ";
        $params[] = PaperModel::STATUS_DRAFT;

        // 权限判断
        $right = $data['right'];
        if (!empty($right)) {

            // 根据权限获取试卷ID列表
            $ep_list = $this->get_ep_ids_by_right($right);

            if (!empty($ep_list)) {
                // 获取有权限的试卷ID的集合
                $ep_arr = array_unique(array_filter(array_column($ep_list, 'epc_id')));

                $where .= " and  (is_all=?  or ep_id in(?))";
                $params[] = PaperModel::EXAM_COMPANY_ALL;
                $params[] = $ep_arr;
            } else {

                $where .= " and  is_all=? ";
                $params[] = PaperModel::EXAM_COMPANY_ALL;
            }
        }

        return [$where, $params];
    }


    /**
     * 根据权限数据获取有权限的试卷ID集合
     *
     * @param array $right 权限数据
     *
     * @return array
     */
    public function get_ep_ids_by_right($right = [])
    {
        $where_right = "";
        $params[] = PaperModel::ST_DELETE;
        $params[] = QY_DOMAIN;
        $params[] = self::PAPER;
        if (!empty($right['memID'])) {

            $where_right .= empty($where_right) ? " uid =? " : " OR uid =? ";
            $params[] = $right['memID'];
        }
        //部门
        if (!empty($right['dpIds'])) {

            $where_right .= empty($where_right) ? " `cd_id` IN (?) " : " OR `cd_id` IN (?) ";
            $params[] = $right['dpIds'];
        }
        // 岗位
        if (!empty($right['jobIds'])) {

            $where_right .= empty($where_right) ? " `job_id` IN (?) " : " OR `job_id` IN (?) ";
            $params[] = $right['jobIds'];
        }
        // 角色
        if (!empty($right['roleIds'])) {

            $where_right .= empty($where_right) ? " `role_id` IN (?) " : " OR `role_id` IN (?) ";
            $params[] = $right['roleIds'];
        }
        // 标签
        if (!empty($right['tagIds'])) {
            $where_right .= empty($where_right) ? " `tag_id` IN (?) " : " OR `tag_id` IN (?) ";
            $params[] = $right['tagIds'];
        }

        // 权限判断
        $rightModel = new RightModel();
        $table = $rightModel->get_tname();

        if (!empty($where_right)) {
            $sql = "select epc_id from " . $table . " where  status <? AND domain= ? AND `er_type`=?  AND (" . $where_right . ")";
        } else {
            $sql = "select epc_id from " . $table . " where  status <? AND domain= ? AND `er_type`=? ";
        }

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 拼装 试卷管理列表|考试统计列表 where语句
     *
     * @author: 候英才
     *
     * @param array $params 查询条件参数
     * @param int $type 查询列表类型：0=试卷管理列表，1=考试统计列表
     *
     * @return string
     */
    public function get_search_where($params = [], $type = 0)
    {
        // 组装查询语句
        if (!$type) {
            $where = ' status<' . self::ST_DELETE . " AND domain= '" . QY_DOMAIN . "' AND exam_status > 0 ";
        } else {
            $where = ' status<' . self::ST_DELETE . " AND domain= '" . QY_DOMAIN . "' AND exam_status > 1 AND begin_time < " . MILLI_TIME;
        }

        // 如果传递了考试类型，则查询考试类型
        if (!empty($params['exam_type'])) {

            $where .= " AND exam_type= " . $params['exam_type'] . " ";
        }

        // 如果标题不为空
        if (!empty($params['ep_name'])) {
            // 查询%化需要转义
            $params['ep_name'] = str_replace("%", '\%', $params['ep_name']);
            $where .= " AND ep_name like '%" . trim($params['ep_name']) . "%' ";
        }

        // 考试时间范围
        if (!empty($params['begin_time']) && $params['begin_time'] != 'NaN' && $params['end_time'] != 'NaN') {

            $where .= ' AND ((end_time > ' . $params['begin_time'] . '&& begin_time < ' . $params['end_time'] . ') OR (begin_time<' . $params['end_time'] . '&& end_time = 0 ))';

        }

        // 分类不为空
        if (!empty($params['ec_id'])) {

            $where .= ' AND ec_id=' . $params['ec_id'];
        }

        // 试卷使用类型
        if (!empty($params['paper_type'])) {

            if (self::EVALUATION_STATUS_TYPE == $params['paper_type']) {

                $where .= ' AND paper_type=' . self::EVALUATION_PAPER_TYPE;
            }

            if (self::SIMULATION_STATUS_TYPE == $params['paper_type']) {

                $where .= ' AND paper_type=' . self::SIMULATION_PAPER_TYPE;
            }
        }

        // 试卷使用类型
        if (!empty($params['ep_type'])) {

            $where .= ' AND ep_type=' . $params['ep_type'];
        }

        // 考试状态不为空
        if (!empty($params['ep_status'])) {

            switch ($params['ep_status']) {

                // 草稿
                case self::STATUS_DRAFT:
                    $where .= ' AND exam_status =' . self::PAPER_DRAFT;
                    break;
                // 未开始
                case self::STATUS_NOT_START:
                    $where .= ' AND begin_time >' . MILLI_TIME . ' AND exam_status =' . self::PAPER_PUBLISH;
                    break;
                // 已开始
                case self::STATUS_ING:
                    $where .= ' AND exam_status =' . self::PAPER_PUBLISH . ' AND begin_time<' . MILLI_TIME . ' AND (end_time>=' . MILLI_TIME . ' OR end_time=0)';
                    break;
                //  已结束和已终止
                case self::STATUS_END:
                    $where .= ' AND ((exam_status =' . self::PAPER_PUBLISH . ' AND end_time> 0 AND  end_time<' . MILLI_TIME . ') OR exam_status =' . self::PAPER_STOP . ')';
                    break;

                default:
            }
        }

        return $where;
    }

    /**
     * 统计考试统计列表总数|获取试卷管理列表总数
     *
     * @author: 候英才
     *
     * @param array $params 查询条件参数
     *
     * @return int|mixed
     */
    public function count_search_where($params = [])
    {

        $search_type = 0;
        if (isset($params['search_type']) && $params['search_type']) {

            $search_type = $params['search_type'];
        }

        $where = $this->get_search_where($params, $search_type);

        $sql = 'SELECT COUNT(*) FROM __TABLE__ WHERE ' . $where;

        return $this->_m->result($sql, []);
    }

    /**
     * 查询考试统计列表|试卷管理列表
     *
     * @author: 候英才
     *
     * @param array $params 查询条件参数
     * @param null $page_option 分页参数
     * @param array $order_option 排序参数
     * @param string $fields 查询的字段
     *
     * @return array|bool
     */
    public function list_search_where($params = [], $page_option = null, $order_option = [], $fields = '*')
    {
        $search_type = 0;
        if (isset($params['search_type']) && $params['search_type']) {

            $search_type = $params['search_type'];
        }

        $where = $this->get_search_where($params, $search_type);
        // 排序
        $orderby = '';
        if (!$this->_order_by($orderby, $order_option)) {
            return false;
        }
        // 分页参数
        $limit = '';
        if (!$this->_limit($limit, $page_option)) {
            return false;
        }

        $sql = "SELECT {$fields} FROM __TABLE__ WHERE " . $where . " {$orderby}{$limit}";

        // 读取记录
        return $this->_m->fetch_array($sql, []);

    }


    /**
     * 根据条件更新数据
     *
     * @param array $conds 条件数组
     * @param array $data 数据数组
     *
     * @return array|bool
     */
    public function update_by_paper($conds = [], $data = [])
    {
        $params = [];
        // 更新时 SET 数据
        $sets = [];
        if (!$this->_parse_set($sets, $params, $data)) {
            return false;
        }

        // 更新条件
        $wheres = [];
        if (!$this->_parse_where($wheres, $params, $conds)) {
            return false;
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;
        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        return $this->_m->execsql("UPDATE __TABLE__ SET " . implode(',', $sets) . " WHERE " . implode(' AND ', $wheres),
            $params);
    }

    /**
     * 手机端试卷列表
     *
     * @param array $conds 查询条件
     * @param null $page_option 分页
     * @param array $order_option 排序
     * @param string $fields 字段
     *
     * @return array|bool 试卷列表
     */
    public function paper_api_list($conds = [], $page_option = null, $order_option = [], $fields = '*')
    {

        // 排序
        $order_by = '';
        if (!$this->_order_by($order_by, $order_option)) {

            return false;
        }

        // 分页参数
        $limit = '';
        if (!$this->_limit($limit, $page_option)) {

            return false;
        }

        $params = [];

        if ($conds['join_type']) {

            $ep_ids = implode(',', $conds['ep_id']);
            $where = ' p.status<' . self::ST_DELETE . " AND p.domain='" . QY_DOMAIN . "' and p.ep_id in(" . $ep_ids . ") and p.cate_status=" . $conds['cate_status']." and p.exam_type=".$conds['exam_type'];

            if ($conds['ec_id']) {
                $where .= ' AND p.ec_id=' . $conds['ec_id'];
            }

            // 已参与,需要联查最高分
            $sql = 'SELECT * FROM  __TABLE__ p LEFT JOIN (SELECT ea_id,ep_id, my_score,my_is_pass,answer_status FROM (select * from oa_exam_answer where status<' . self::ST_DELETE . " and domain='" . QY_DOMAIN . "' and uid='" . $conds['uid'] . "' order by my_score DESC ) as c group by ep_id )  AS a ON a.ep_id = p.ep_id  WHERE " . $where . " ORDER BY last_time DESC " . $limit;

        } else {

            // 组装查询语句
            $where = " status <? AND domain=?";
            // 操作状态和域名
            $params[] = PaperModel::ST_DELETE;
            $params[] = QY_DOMAIN;

            $where .= " AND ep_id in (?)";
            $params[] = $conds['ep_id'];

            $where .= " AND exam_type =?";
            $params[] = $conds['exam_type'];

            $where .= " AND cate_status =?";
            $params[] = $conds['cate_status'];

            $where .= " AND exam_status IN(?)";
            $params[] = $conds['exam_status'];

            if ($conds['ec_id']) {

                $where .= " AND ec_id=?";
                $params[] = $conds['ec_id'];
            }

            // 未参与
            $sql = 'SELECT ' . $fields . ' FROM  __TABLE__ WHERE ' . $where . $order_by . $limit;

        }


        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 组装查询已参与试卷列表sql
     *
     * @param array $conditions 查询条件
     * @param int $sql_type 查询类型（0:总数，1:列表）
     * @param string $order_by 排序
     * @param string $limit 分页
     *
     * @return array sql语句、参数
     */
    protected function _get_join_paper_sql($conditions = [], $sql_type = self::QUERY_SQL_TYPE_COUNT, $order_by = '', $limit = '')
    {

        // 分类开启
        $params[] = self::EC_OPEN_STATES;

        // 分类id
        $ec_id = 0;
        if (isset($conditions['ec_id']) && $conditions['ec_id']) {

            $ec_id = $conditions['ec_id'];
        }

        // 初始化分类查询条件
        $where_ec_id = '';
        // 分类id不为空，拼接查询条件，追加参数
        if ($ec_id) {

            $where_ec_id = 'AND `ep`.`ec_id`=? ';
            $params[] = $ec_id;
        }

        $params[] = self::STATUS_DRAFT;
        $params[] = QY_DOMAIN;
        $params[] = self::ST_DELETE;
        $params[] = $conditions['uid'];
        $params[] = QY_DOMAIN;
        $params[] = self::ST_DELETE;

        // 排序和分页
        $order_limit = '';
        // 查询字段
        $fields = '`ep`.ep_id,`ep`.paper_type,`ep`.ep_name,`ep`.total_score,`ep`.exam_status,`ep`.begin_time,`ep`.end_time,
            `ep`.join_count,`ep`.last_time,`a`.my_is_pass,`a`.answer_status,max(`a`.my_score) as my_score';

        // 列表
        if ($sql_type) {

            $order_limit = $order_by . $limit;
        }

        $sql = 'SELECT ' . $fields . ' FROM `oa_exam_paper` AS `ep` 
                LEFT JOIN `oa_exam_answer` AS `a` 
                ON `ep`.`ep_id`=`a`.ep_id 
                WHERE `ep`.`cate_status`=? 
                    ' . $where_ec_id . '
                    AND `ep`.`exam_type`=1 
                    AND `ep`.`exam_status`>? 
                    AND `ep`.`domain`=? 
                    AND `ep`.`status`<? 
                    AND `a`.`uid`=? 
                    AND `a`.`my_time`!=0 
                    AND `a`.`domain`=? 
                    AND `a`.`status`<? GROUP BY `a`.ep_id' . $order_limit;

        return [$sql, $params];
    }

    /**
     * 组装可见范围不为全公司的试卷－权限sql
     *
     * @param array $conditions 查询条件
     *
     * @return array sql语句、参数
     */
    protected function _get_right_paper_sql($conditions = [])
    {

        // 权限
        $right = $conditions['right'];
        // 权限sql条件
        $where_right = '';
        // 权限参数
        $right_params = [];

        // 用户
        if (!empty($right['memID'])) {

            $where_right .= empty($where_right) ? ' uid =? ' : ' OR uid =? ';
            $right_params[] = $right['memID'];
        }

        // 部门
        if (!empty($right['dpIds'])) {

            $where_right .= empty($where_right) ? ' `cd_id` IN (?) ' : ' OR `cd_id` IN (?) ';
            $right_params[] = $right['dpIds'];
        }

        // 岗位
        if (!empty($right['jobIds'])) {

            $where_right .= empty($where_right) ? ' `job_id` IN (?) ' : ' OR `job_id` IN (?) ';
            $right_params[] = $right['jobIds'];
        }

        // 角色
        if (!empty($right['roleIds'])) {

            $where_right .= empty($where_right) ? ' `role_id` IN (?) ' : ' OR `role_id` IN (?) ';
            $right_params[] = $right['roleIds'];
        }

        // 分类开启
        $params[] = self::EC_OPEN_STATES;

        // 分类id
        $ec_id = $conditions['ec_id'];
        // 初始化分类查询条件
        $where_ec_id = '';
        // 分类id不为空，拼接查询条件，追加参数
        if ($ec_id) {

            $where_ec_id = 'AND `ep`.`ec_id`=? ';
            $params[] = $ec_id;
        }

        $params[] = self::STATUS_DRAFT;
        $params[] = QY_DOMAIN;
        $params[] = self::ST_DELETE;
        $params[] = self::PAPER;

        if ($where_right) {

            $sql = 'SELECT `ep`.* FROM `oa_exam_paper` AS `ep` 
                LEFT JOIN `oa_exam_right` AS `r` 
                ON `ep`.`ep_id`=`r`.epc_id 
                WHERE `ep`.`cate_status`=? 
                    ' . $where_ec_id . '
                    AND `ep`.`exam_type`=1 
                    AND `ep`.`exam_status`>? 
                    AND `ep`.`domain`=? 
                    AND `ep`.`status`<? 
                    AND `r`.`er_type`=? 
                    AND (' . $where_right . ') 
                    AND `r`.`domain`=? 
                    AND `r`.`status`<?';
            // 合并权限where参数
            $params = array_merge($params, $right_params);
        } else {

            $sql = 'SELECT `ep`.* FROM `oa_exam_paper` AS `ep` 
                LEFT JOIN `oa_exam_right` AS `r` 
                ON `ep`.`ep_id`=`r`.epc_id 
                WHERE `ep`.`cate_status`=? 
                    ' . $where_ec_id . '
                    AND `ep`.`exam_type`=1 
                    AND `ep`.`exam_status`>? 
                    AND `ep`.`domain`=? 
                    AND `ep`.`status`<? 
                    AND `r`.`er_type`=? 
                    AND `r`.`domain`=? 
                    AND `r`.`status`<?';
        }

        $params[] = QY_DOMAIN;
        $params[] = self::ST_DELETE;

        return [$sql, $params];
    }

    /**
     * 查询可见范围为全公司的试卷sql
     *
     * @param array $conditions 查询条件
     *
     * @return array sql语句、参数
     */
    protected function _get_is_all_paper_sql($conditions = [])
    {

        // 全公司
        $params[] = self::EXAM_COMPANY_ALL;
        // 分类开启
        $params[] = self::EC_OPEN_STATES;

        // 分类id
        $ec_id = $conditions['ec_id'];
        // 初始化分类查询条件
        $where_ec_id = '';
        // 分类id不为空，拼接查询条件，追加参数
        if ($ec_id) {

            $where_ec_id = 'AND `ep2`.`ec_id`=? ';
            $params[] = $ec_id;
        }

        $params[] = self::STATUS_DRAFT;
        $params[] = QY_DOMAIN;
        $params[] = self::ST_DELETE;

        $sql = 'SELECT `ep2`.* FROM `oa_exam_paper` as `ep2` 
                WHERE `ep2`.`is_all`=? 
                AND `ep2`.`cate_status`=? 
                ' . $where_ec_id . '
                AND `ep2`.`exam_type`=1  
                AND `ep2`.`exam_status`>? 
                AND `ep2`.`domain`=? 
                AND `ep2`.`status`<?';

        return [$sql, $params];
    }
}