<?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]; } }