/**
* 获取考试排名
*/
public function get_exam_rank()
{
$rule_id = $this->input->get('rule_id');
$exam_pid = $this->input->get('exam_pid');
$subject_id = $this->input->get('subject_id');
$sql = "SELECT place_id FROM {pre}evaluate_rule\n\t WHERE id='{$rule_id}'";
$res = $this->db->query($sql)->row_array();
$place_idd = $res['place_id'];
if (!$this->input->post()) {
$data = array();
if ($place_idd == 0) {
$sql = "SELECT place_id,place_name FROM {pre}exam_place \n WHERE exam_pid='{$exam_pid}'";
} else {
$sql = "SELECT place_id,place_name FROM {pre}exam_place \n WHERE place_id='{$place_idd}'";
}
$data['places'] = $this->db->query($sql)->result_array();
$data['exam_pid'] = $exam_pid;
$data['rule_id'] = $rule_id;
$data['subject_id'] = $subject_id;
$this->load->view('evaluate_rule/exam_rank', $data);
} else {
$place_id = $this->input->post('place_id');
if ($place_id == 0) {
$where_sql = '';
if ($place_idd > 0) {
$where_sql = "AND etp.place_id={$place_idd}";
}
$place_name = "所有考场";
} else {
$where_sql = "AND etp.place_id={$place_id}";
$sql = "SELECT place_name FROM {pre}exam_place \n\t\t\t\t WHERE place_id='{$place_id}'";
$res = $this->db->query($sql)->row_array();
$place_name = $res['place_name'];
}
$exam = $this->db->select('exam_name')->get_where('exam', array('exam_id' => $exam_pid))->row_array();
if (empty($exam)) {
message('考试期次不存在');
}
$exam_name = $exam['exam_name'];
if ($subject_id > 0) {
$where_sql .= " AND etp.subject_id = {$subject_id}";
$sql = "select distinct(etp.uid) as uid, last_name, first_name, exam_ticket, exam_ticket_maprule, etp.test_score\n\t\t from {pre}exam_test_paper etp\n\t\t LEFT JOIN rd_student s ON s.uid = etp.uid\n\t\t LEFT JOIN rd_exam e ON e.exam_id = etp.exam_pid\n\t\t where etp.exam_pid={$exam_pid} AND etp_flag = 2 {$where_sql}\n\t\t ORDER BY etp.test_score DESC";
$result = $this->db->query($sql)->result_array();
$data = array();
$subject = C('subject');
foreach ($result as $item) {
$data[$item['uid']]['姓名'] = $item['last_name'] . $item['first_name'];
$data[$item['uid']]['准考证号'] = exam_ticket_maprule_decode($item['exam_ticket'], $item['exam_ticket_maprule']);
$data[$item['uid']]['总分'] = $item['test_score'];
}
$list = array();
foreach ($data as $uid => $item) {
$list[$item['总分']][] = $item;
}
$rank = 1;
foreach ($list as $key => &$item) {
$list[$key] = array_values($this->arraySort($item, '总分', 'desc'));
$pre_total_score = 0;
foreach ($item as $index => &$val) {
if ($val['总分'] == 0) {
$val = array_merge(array('排名' => ''), $val);
continue;
}
if ($val['总分'] == $pre_total_score) {
$val = array_merge(array('排名' => $item[$index - 1]['排名']), $val);
} else {
$val = array_merge(array('排名' => $rank), $val);
}
$pre_total_score = $val['总分'];
$rank++;
}
}
// 作弊的人员
$sql = "select distinct(etp.uid) as uid, last_name, first_name,exam_ticket,exam_ticket_maprule\n \t\t from {pre}exam_test_paper etp\n \t\t LEFT JOIN rd_student s ON s.uid = etp.uid\n \t\t LEFT JOIN rd_exam e ON e.exam_id = etp.exam_pid\n \t\t where etp.exam_pid={$exam_pid} and etp_flag = -1 {$where_sql}";
$result = $this->db->query($sql)->result_array();
foreach ($result as $info) {
$data = array();
$data['排名'] = '';
$data['姓名'] = $info['last_name'] . $info['first_name'];
$data['准考证号'] = exam_ticket_maprule_decode($info['exam_ticket'], $info['exam_ticket_maprule']);
$data['说明'] = '考试结果作废';
$list[0][] = $data;
}
$exam_name .= '-' . $subject[$subject_id];
} else {
$this->load->model('cron/report/subject_report/complex_model', 'c_model');
$sql = "select distinct(etp.uid) as uid, last_name, first_name,exam_ticket,exam_ticket_maprule\n \t\tfrom {pre}exam_test_paper etp\n \t\tLEFT JOIN rd_student s ON s.uid = etp.uid\n \t\tLEFT JOIN rd_exam e ON e.exam_id = etp.exam_pid\n \t\twhere etp.exam_pid={$exam_pid} AND etp_flag = 2 {$where_sql}";
$result = $this->db->query($sql)->result_array();
$no_result = array();
$data = array();
$subject = C('subject');
foreach ($result as $item) {
$t_data = $this->c_model->module_match_percent($rule_id, $exam_pid, $item['uid']);
if (!empty($t_data['data'])) {
$exam_subject = $t_data['data'][0];
array_shift($exam_subject);
$subject_score = $t_data['data'][4];
array_shift($subject_score);
$total_percent_score = array_pop($t_data['data'][5]);
$data[$item['uid']]['姓名'] = $item['last_name'] . $item['first_name'];
$data[$item['uid']]['准考证号'] = exam_ticket_maprule_decode($item['exam_ticket'], $item['exam_ticket_maprule']);
$data[$item['uid']]['权重总分'] = $total_percent_score;
$data[$item['uid']]['总分'] = array_pop($subject_score);
foreach ($subject as $sub) {
if (in_array($sub, $exam_subject)) {
$key = array_search($sub, $exam_subject);
$data[$item['uid']][$sub] = $subject_score[$key];
}
}
} else {
message('抱歉，没有生成考试成绩，无法获取考试排名');
}
}
$data = $this->arraySort($data, '权重总分', 'desc');
$list = array();
foreach ($data as $uid => $item) {
$list[$item['权重总分']][] = $item;
}
$rank = 1;
foreach ($list as $key => &$item) {
$list[$key] = array_values($this->arraySort($item, '总分', 'desc'));
$pre_total_score = 0;
foreach ($item as $index => &$val) {
if ($val['总分'] == 0) {
$val = array_merge(array('排名' => ''), $val);
continue;
}
if ($val['总分'] == $pre_total_score) {
$val = array_merge(array('排名' => $item[$index - 1]['排名']), $val);
} else {
$val = array_merge(array('排名' => $rank), $val);
}
$pre_total_score = $val['总分'];
$rank++;
}
}
// pr($list,1);
if ($no_result) {
$list[0][] = $no_result;
}
// 作弊的人员
$sql = "select distinct(etp.uid) as uid, last_name, first_name,exam_ticket,exam_ticket_maprule\n \t\t\t\t\tfrom {pre}exam_test_paper etp\n \t\t\t\t\tLEFT JOIN rd_student s ON s.uid = etp.uid\n \t\t\t\t\tLEFT JOIN rd_exam e ON e.exam_id = etp.exam_pid\n \t\t\t\t\twhere etp.exam_pid={$exam_pid} and etp_flag = -1 {$where_sql}";
$result = $this->db->query($sql)->result_array();
foreach ($result as $info) {
$data = array();
$data['排名'] = '';
$data['姓名'] = $info['last_name'] . $info['first_name'];
$data['准考证号'] = exam_ticket_maprule_decode($info['exam_ticket'], $info['exam_ticket_maprule']);
$data['说明'] = '考试结果作废';
$list[0][] = $data;
}
}
$i = 0;
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename={$exam_name}排名.xls");
echo "\t" . "\t" . "\t" . mb_convert_encoding("期次:" . $exam_name, "GBK", "UTF-8") . "\n";
echo "\t" . "\t" . "\t" . mb_convert_encoding("场次:" . $place_name, "GBK", "UTF-8") . "\n";
echo "\n";
foreach ($list as $value) {
foreach ($value as $items) {
if ($i == 0) {
$title = array_keys($items);
foreach ($title as $info) {
echo mb_convert_encoding($info, "GBK", "UTF-8") . "\t";
}
$i++;
}
if (!$items) {
continue;
}
echo "\n";
foreach ($items as $vals) {
if (is_numeric($vals)) {
echo $vals . "\t";
} else {
echo mb_convert_encoding($vals, "GBK", "UTF-8") . "\t";
}
}
}
}
}
}
/**
* 期次考试成绩下载
*/
public function down_examresult()
{
$db = Fn::db();
$exam_pid_arr = array();
$exam_pid_arr = $this->input->post('exam_ids');
if (!$exam_pid_arr) {
message('未选择考试现在期次', 'admin/exam/down_exam');
}
$exam_pid = implode(',', $exam_pid_arr);
$sql = <<<EOT
SELECT distinct exam_id,subject_id
FROM rd_exam_place_subject
WHERE exam_pid IN ({$exam_pid})
EOT;
$exam_subject = $db->fetchPairs($sql);
$subject_arr = array_unique(array_values($exam_subject));
sort($subject_arr);
$sql = <<<EOT
SELECT exam_name
FROM rd_exam
WHERE exam_id IN ({$exam_pid})
EOT;
$exam_name_arr = $db->fetchCol($sql);
$exam_name_str = implode('、', $exam_name_arr);
$sql = <<<EOT
SELECT etp.uid,last_name, first_name,exam_ticket, exam_ticket_maprule, etp.test_score,etp.exam_id,ep.place_name,etp.subject_id,etp.place_id
FROM rd_exam_test_paper etp
LEFT JOIN rd_student s ON s.uid = etp.uid
LEFT JOIN rd_exam e ON e.exam_id = etp.exam_pid
LEFT JOIN rd_exam_place ep ON ep.place_id=etp.place_id
WHERE etp.exam_pid IN ({$exam_pid}) AND etp_flag=2
order by etp.place_id ,etp.subject_id ASC
EOT;
$result = $db->fetchAll($sql);
$sql = <<<EOT
SELECT distinct uid
FROM rd_exam_test_paper
WHERE exam_pid IN ({$exam_pid}) AND etp_flag=2
EOT;
$uid_arr = $db->fetchCol($sql);
$uids = implode(',', $uid_arr);
$sql = <<<EOT
select s.uid,sch.school_name
from rd_student s
LEFT JOIN rd_school sch ON sch.school_id = s.school_id
WHERE s.uid in ({$uids})
EOT;
$uid_school = $db->fetchPairs($sql);
$sql = <<<EOT
SELECT ep.place_id , sc.schcls_name
FROM rd_exam_place ep
LEFT JOIN t_school_class sc ON ep.place_schclsid = sc.schcls_id
EOT;
$place_schcls = $db->fetchPairs($sql);
$data = array();
$subject = C('subject');
foreach ($subject_arr as $val) {
$subject_name[] = $subject[$val];
}
$subject_name_str = implode('、', $subject_name);
foreach ($result as $item) {
$data[$item['uid']]['姓名'] = $data[$item['uid']]['姓名'] ? $data[$item['uid']]['姓名'] : $item['last_name'] . $item['first_name'];
$data[$item['uid']]['准考证号'] = $data[$item['uid']]['准考证号'] ? $data[$item['uid']]['准考证号'] : exam_ticket_maprule_decode($item['exam_ticket'], $item['exam_ticket_maprule']);
$data[$item['uid']]['学校'] = $data[$item['uid']]['学校'] ? $data[$item['uid']]['学校'] : $uid_school[$item['uid']];
if ($place_schcls[$item['place_id']]) {
$data[$item['uid']]['班级'] = $data[$item['uid']]['班级'] ? $data[$item['uid']]['班级'] : $place_schcls[$item['place_id']];
} else {
$data[$item['uid']]['班级'] = $data[$item['uid']]['班级'] ? $data[$item['uid']]['班级'] : $item['place_name'];
}
foreach ($subject_arr as $val) {
if (!$data[$item['uid']][$subject[$val]]) {
$data[$item['uid']][$subject[$val]] = 0;
}
}
$data[$item['uid']][$subject[$item['subject_id']]] = $item['test_score'];
}
$list = array();
foreach ($data as $uid => $item) {
$list[$item['总分']][] = $item;
}
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename={$exam_pid}期次成绩表.xls");
echo "\t" . "\t" . "\t" . mb_convert_encoding("期次:" . $exam_name_str, "GBK", "UTF-8") . "\n";
echo "\t" . "\t" . "\t" . mb_convert_encoding("科目:" . $subject_name_str, "GBK", "UTF-8") . "\n";
echo "\n";
foreach ($list as $value) {
foreach ($value as $items) {
if ($i == 0) {
$title = array_keys($items);
foreach ($title as $info) {
echo mb_convert_encoding($info, "GBK", "UTF-8") . "\t";
}
$i++;
}
if (!$items) {
continue;
}
echo "\n";
foreach ($items as $vals) {
if (is_numeric($vals)) {
echo $vals . "\t";
} else {
echo mb_convert_encoding($vals, "GBK", "UTF-8") . "\t";
}
}
}
}
}
