<?php
/**
* Created by PhpStorm.
* User: yingcai
* Date: 2017/10/10
* Time: 下午4:00
*/
namespace Common\Model;
class ExamAnswerModel extends \Com\Model
{
const EXAM_PAPER = 1;
// 构造方法
public function __construct()
{
parent::__construct('Answer', 'oa_exam_');
}
/**
* 统计周期内考试人数
*
* @author houyingcai
*
* @param array $conds 查询条件
* @param array $order_option 排序参数
* @param string $fields 返回字段
*
* @return array|bool
*/
public function count_join_users($conds = [], $order_option = [], $fields = '*')
{
$where = ' status < ? AND domain = ? ';
$params[] = $this->get_st_delete();
$params[] = QY_DOMAIN;
if ($conds['start_time'] && $conds['end_time']) {
$where .= ' AND created >= ? AND created <= ?';
$params[] = $conds['start_time'];
$params[] = $conds['end_time'];
} else {
return false;
}
if (!empty($conds['ep_id'])) {
$where .= ' AND ep_id in (?)';
$params[] = $conds['ep_id'];
}
$where .= ' AND my_end_time > 0 AND data_type = ' . self::EXAM_PAPER;
// 排序
$orderby = '';
if (!$this->_order_by($orderby, $order_option)) {
return false;
}
$sql = "SELECT COUNT(*) FROM (SELECT {$fields} FROM __TABLE__ WHERE {$where}{$orderby} GROUP BY `uid`) __TABLE__";
return $this->_m->result($sql, $params);
}
/**
* 获取学霸排行列表
*
* @param array $conds 条件数组
* @param int|array $page_option 分页参数
* @param array $order_option 排序
* @param string $fields 读取字段
*
* @return array|bool
*/
public function super_scholar_rank($conds, $page_option = null, $order_option = [], $fields = '*')
{
$params = [];
// 条件
$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();
// 排序
$orderby = '';
if (!$this->_order_by($orderby, $order_option)) {
return false;
}
// 分页参数
$limit = '';
if (!$this->_limit($limit, $page_option)) {
return false;
}
// 读取记录
return $this->_m->fetch_array("SELECT {$fields} FROM __TABLE__ WHERE " . implode(' AND ',
$wheres) . " GROUP BY uid {$orderby}{$limit}", $params);
}
/**
* 根据条件获取用户答卷记录
*
* @param array $conds 查询条件
* @param string $fields 查询字段
*
* @return array|bool
*/
public function list_answer($conds = [], $fields = '*')
{
$params = [];
// 条件
$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();
$sql = "SELECT {$fields} FROM __TABLE__ WHERE " . implode(' AND ', $wheres) . " GROUP BY ep_id,uid ";
// 读取记录
return $this->_m->fetch_array($sql, $params);
}
/**
* 根据UID统计用户考试次数
*
* @param string $uid 员工UID
*
* @return array|bool
*/
public function count_user_data_exam($uid)
{
$conds = [
'l.`uid`' => $uid,
'l.`my_end_time` > ?' => 0,
];
$params = [];
// 条件
$wheres = [];
if (!$this->_parse_where($wheres, $params, $conds)) {
return false;
}
// 企业标记
$wheres[] = "r.`{$this->prefield}domain`=?";
$params[] = QY_DOMAIN;
// 状态条件
$wheres[] = "r.`{$this->prefield}status`<?";
$params[] = $this->get_st_delete();
$sql = 'SELECT l.* FROM __TABLE__ l RIGHT JOIN `oa_exam_paper` r ON l.ep_id = r.ep_id WHERE ' . implode(' AND ',
$wheres) . ' AND r.exam_type = ' . self::EXAM_PAPER . ' GROUP BY l.ep_id';
return count($this->_m->fetch_array($sql, $params));
}
/**
* 根据UID获取员工考试记录
*
* @param string $uid 员工UID
* @param array $page_option 分页
* @param array $order_option 排序
*
* @return array|bool
*/
public function list_user_data_exam($uid, $page_option = null, $order_option = [])
{
$conds = [
'l.`uid`' => $uid,
'l.`my_end_time` > ?' => 0,
];
$params = [];
// 条件
$wheres = [];
if (!$this->_parse_where($wheres, $params, $conds)) {
return false;
}
// 企业标记
$wheres[] = "r.`{$this->prefield}domain`=?";
$params[] = QY_DOMAIN;
// 状态条件
$wheres[] = "r.`{$this->prefield}status`<?";
$params[] = $this->get_st_delete();
// 排序
$orderby = '';
if (!$this->_order_by($orderby, $order_option)) {
return false;
}
// 分页参数
$limit = '';
if (!$this->_limit($limit, $page_option)) {
return false;
}
$sql = "SELECT a.ea_id,a.ep_id,a.my_score,a.my_begin_time,a.my_end_time,a.my_time,b.total
FROM __TABLE__ a,
(
SELECT l.uid,l.ep_id,max(l.my_score) my_score,count(l.ep_id) as total
FROM __TABLE__ l RIGHT JOIN `oa_exam_paper` r ON l.ep_id = r.ep_id
WHERE " . implode(' AND ', $wheres) . " AND r.exam_type = " . self::EXAM_PAPER . " GROUP BY l.ep_id {$orderby}{$limit}
) b
WHERE a.ep_id = b.ep_id AND a.my_score = b.my_score AND a.uid = b.uid
GROUP BY a.ep_id ORDER BY a.ea_id DESC";
return $this->_m->fetch_array($sql, $params);
}
/**
* 员工考试统计列表总数
*
* @param array $conds 查询条件
*
* @return array|bool
*/
public function count_user_exam_count($conds)
{
$params = [];
// 条件
$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();
$sql = "SELECT uid,ep_id FROM __TABLE__ WHERE " . implode(' AND ', $wheres) . " GROUP BY ep_id,uid";
return $this->_m->fetch_array($sql, $params);
}
/**
* 获取员工考试统计列表
*
* @param array $conds 查询条件
* @param array $page_option 分页
* @param array $order_option 排序
*
* @return array|bool
*/
public function list_user_exam_count($conds, $page_option = null, $order_option = [])
{
$params = [];
// 条件
$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();
// 排序
$orderby = '';
if (!$this->_order_by($orderby, $order_option)) {
return false;
}
// 分页参数
$limit = '';
if (!$this->_limit($limit, $page_option)) {
return false;
}
// 根据试卷ID和用户ID进行分组
$sql = "SELECT ea_id,uid,ep_id,MAX(my_score) my_score,my_is_pass,my_time,my_begin_time,my_end_time FROM (SELECT * FROM __TABLE__ WHERE " . implode(' AND ', $wheres) . " AND answer_status>0 ORDER BY my_score DESC) AS b GROUP BY ep_id,uid {$orderby}{$limit}";
return $this->_m->fetch_array($sql, $params);
}
}