/** * 获取考试排名 */ 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"; } } } } }