/**
* 导入
*/
public function import($exam_pid = 0)
{
if ($_GET['dl'] == '1') {
Func::dumpFile('application/vnd.ms-excel', 'file/import_teacher_stundent_template.xlsx', '师生关联模板.xlsx');
exit;
}
if (!$exam_pid) {
message('参数错误');
}
$data = array();
while (isset($_FILES['file'])) {
$param = $_POST;
$col_char = array();
$rows = Excel::readSimpleUploadFile2($_FILES['file']);
if (!is_array($rows)) {
$data['error'] = $rows;
break;
}
$subject_map = array_flip(C('subject'));
$db = Fn::db();
$exam_ticket_maprule = ExamModel::get_exam($exam_pid, 'exam_ticket_maprule');
$sql = "SELECT subject_id, exam_id FROM rd_exam\n WHERE exam_pid = {$exam_pid}";
$subject_exam = $db->fetchPairs($sql);
if (!is_array($subject_exam)) {
$data['error'] = '考试期次没有考试学科';
break;
}
$exam_subjectid = array_keys($subject_exam);
$list = array();
$subject_key = array();
foreach ($rows as $k => $row) {
if ($k == 0) {
for ($i = 2; $i <= count($row); $i++) {
$subject_id = $subject_map[str_replace("'", "", trim($row[$i]))];
if ($subject_id && in_array($subject_id, $exam_subjectid)) {
$subject_key[$i] = $subject_id;
}
}
} else {
$student = array();
for ($i = 1; $i <= count($row); $i++) {
if ($i == 1) {
$exam_ticket = trim($row[$i]);
if (!$exam_ticket) {
break;
}
$exam_ticket = exam_ticket_maprule_encode($exam_ticket, $exam_ticket_maprule);
$sql = "SELECT uid, school_id FROM rd_student\n WHERE exam_ticket = '{$exam_ticket}'";
$student = $db->fetchRow($sql);
if (!$student) {
break;
}
$list[$student['uid']]['uid'] = $student['uid'];
} else {
$ct_name = str_replace("'", "", trim($row[$i]));
if (!$ct_name) {
continue;
}
$sql = "SELECT ct_id FROM t_cteacher \n LEFT JOIN t_cteacher_school ON scht_ctid = ct_id\n WHERE scht_schid = {$student['school_id']} \n AND ct_name = '{$ct_name}'";
$ct_id = $db->fetchOne($sql);
if (!$ct_id) {
continue;
}
$list[$student['uid']]['teacher'][$subject_key[$i]] = $ct_id;
}
}
}
}
try {
if (!$db->beginTransaction()) {
throw new Exception('开始导入事务处理失败');
}
$insert = 0;
// 导入教师
foreach ($list as $uid => $row) {
foreach ($row['teacher'] as $subject_id => $ct_id) {
$bind = array('tstu_ctid' => $ct_id, 'tstu_stuid' => $uid, 'tstu_exampid' => $exam_pid, 'tstu_examid' => $subject_exam[$subject_id], 'tstu_subjectid' => $subject_id);
TeacherStudentModel::addTeacherStudent($bind);
$insert++;
}
}
if ($db->commit()) {
$data['success'] = <<<EOT
导入Excel文件({$_FILES['file']['name']})成功,共插入{$insert}条师生记录
EOT;
admin_log('import', 'teacher_student', $data['success']);
} else {
$err = $db->errorInfo()[2];
$db->rollBack();
throw new Exception($err);
}
} catch (Exception $e) {
$data['error'] = $e->getMessage();
}
break;
}
$data['exam_pid'] = $exam_pid;
$data['param'] = $param;
$this->load->view('teacher_student/import', $data);
}
/**
* 根据excel导入学生并将导入的学生加入考场中
*/
public function import_student_save()
{
set_time_limit(0);
$place_id = intval($this->input->post('place_id'));
if (!$place_id) {
message('考场不存在');
}
if ($this->db->get_where('exam_place', array('start_time <=' => time(), 'place_id' => $place_id))->row_array()) {
message('该考场正在考试或已结束，无法做此操作', '/admin/place_student/index/' . $place_id);
}
$message = array();
$school_id = intval($this->input->post('school_id'));
if (!$school_id) {
$message[] = '考场地址有错误';
}
$start_line = intval($this->input->post('start_line'));
if ($start_line < 1) {
$message[] = '请输入学生信息在Excel文件开始的行';
}
$fullname_column = intval($this->input->post('fullname_column'));
if ($fullname_column < 1) {
$message[] = '请输入姓名在Excel文件的列';
}
$exam_ticket_column = intval($this->input->post('exam_ticket_column'));
if ($exam_ticket_column < 1) {
$message[] = '请输入准考证号在Excel文件的列';
}
if ($fullname_column && $exam_ticket_column && $fullname_column == $exam_ticket_column) {
$message[] = '姓名和准考证号在Excel文件中不能为同一列';
}
if (!$_FILES['file']) {
$message[] = '请选择导入的Excel文件';
}
$grade_id = intval($this->input->post('grade_id'));
$mobile_column = intval($this->input->post('mobile_column'));
$school_column = intval($this->input->post('school_column'));
$auto_set_paper = intval($this->input->post('auto_set_paper'));
$import_tables = array_filter(explode(',', $this->input->post('import_table')));
$schools = array();
$school_names = $this->input->post('school_key');
if ($school_names) {
$school_ids = $this->input->post('school_ids');
foreach ($school_names as $key => $name) {
$name = str_replace(' ', '', $name);
$sch_id = isset($school_ids[$key]) ? intval($school_ids[$key]) : 0;
if ($sch_id > 0) {
$schools[$name] = $sch_id;
} else {
$message[] = $name . "对应的学校ID不能为空";
}
}
}
if ($message) {
message(implode('<br>', $message));
}
/**
* 上传文件
*/
$upload_path = '../../cache/excel/';
$file_name = microtime(true) . '.' . end(explode('.', $_FILES['file']['name']));
$upload_file = $upload_path . $file_name;
if (!is_dir($upload_path)) {
mkdir($upload_path, '0777', true);
}
if (!@move_uploaded_file($_FILES['file']['tmp_name'], $upload_file)) {
message('导入文件失败，请重新导入！');
} else {
$exam = $this->db->from('rd_exam e')->join('rd_exam_place ep', "e.exam_id=ep.exam_pid", 'left')->where('place_id', $place_id)->get()->row_array();
$grade_id = $grade_id ? $grade_id : $exam['grade_id'];
if (!$school_column) {
$school = $this->db->get_where('school', array('school_id' => $school_id))->row_array();
}
$place_student = $this->db->get_where('rd_exam_place_student', array('place_id' => $place_id))->result_array();
$place_uids = array();
foreach ($place_student as $val) {
$place_uids[] = $val['uid'];
}
$uids = array();
//未加入考场的学生
//导入结果信息统计
$stat = array('total' => 0, 'success' => 0, 'fail' => 0, 'exist' => 0);
/**
* 读取excel
*/
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$inputFileType = IOFactory::identify($upload_file);
$objReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($upload_file);
$sheetcount = $objPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetcount; $i++) {
if ($import_tables && !in_array($i + 1, $import_tables)) {
continue;
}
$list = array_filter($objPHPExcel->getSheet($i)->toArray());
if (!empty($list)) {
$line_count = count($list);
for ($j = $start_line - 1; $j < $line_count; $j++) {
$list[$j] = array_filter($list[$j]);
if (empty($list[$j])) {
continue;
}
$student_name = str_replace(' ', '', $list[$j][$fullname_column - 1]);
$external_exam_ticket = trim($list[$j][$exam_ticket_column - 1]);
if (!$student_name || !$external_exam_ticket) {
continue;
}
$stat['total']++;
if (empty($student_name)) {
$message['fail']['student_name'][] = $external_exam_ticket;
$stat['fail']++;
continue;
}
if (empty($external_exam_ticket)) {
$message['fail']['exam_ticket'][] = $student_name;
$stat['fail']++;
continue;
}
$exam_ticket = exam_ticket_maprule_encode($external_exam_ticket, $exam['exam_ticket_maprule']);
if (!is_numeric($exam_ticket)) {
$message['fail']['exam_ticket_error'][] = $student_name . "-" . $external_exam_ticket;
$stat['fail']++;
continue;
}
//判断准考证号是否已注册
if ($tmp_student = $this->db->select('uid')->from('student')->where('exam_ticket', $exam_ticket)->get()->row_array()) {
$message['exist'][] = $student_name . "-" . $external_exam_ticket;
$stat['exist']++;
if (!in_array($tmp_student['uid'], $place_uids)) {
$this->db->replace('exam_place_student', array('place_id' => $place_id, 'uid' => $tmp_student['uid']));
$uids[] = $tmp_student['uid'];
}
} else {
$mobile = '';
if ($mobile_column && is_phone($list[$j][$mobile_column - 1])) {
$mobile = $list[$j][$mobile_column - 1];
}
if ($school_column && $schools) {
$sch_name = str_replace(' ', '', $list[$j][$school_column - 1]);
$school_id = intval($schools[$sch_name]);
if (!isset($school_info[$school_id])) {
$school_info[$school_id] = $this->db->get_where('school', array('school_id' => $school_id))->row_array();
}
$school = $school_info[$school_id];
if (!$school) {
message('学校“' . $sch_name . '”信息不存在，请设置学校对应的学校ID！');
}
}
$insert_data = array('email' => $exam_ticket . "@mail.exam.new-steps.com", 'first_name' => mb_substr($student_name, 1, strlen($student_name), 'utf-8'), 'last_name' => mb_substr($student_name, 0, 1, 'utf-8'), 'exam_ticket' => $exam_ticket, 'external_account' => $external_exam_ticket, 'maprule' => $exam['exam_ticket_maprule'], 'password' => my_md5($exam['exam_ticket_maprule'] ? $external_exam_ticket : '123456'), 'mobile' => $mobile, 'grade_id' => $grade_id, 'province' => $school['province'], 'city' => $school['city'], 'area' => $school['area'], 'school_id' => $school_id, 'source_from' => '2', 'addtime' => time());
$this->db->insert('student', $insert_data);
$uid = $this->db->insert_id();
if ($uid) {
$stat['success']++;
$this->db->replace('exam_place_student', array('place_id' => $place_id, 'uid' => $uid));
$uids[] = $uid;
} else {
$stat['fail']++;
$message['fail']['insert_fail'][] = $student_name . "-" . $external_exam_ticket;
// . '(' . $this->db->last_query() . ')';
}
}
}
}
}
//新加入考场的学生加入分配试卷计划任务中
if ($auto_set_paper && $uids) {
$insert_data = array();
$insert_data['place_id'] = $place_id;
$insert_data['uid_data'] = json_encode($uids);
$insert_data['status'] = 0;
$insert_data['c_time'] = time();
$insert_data['u_time'] = time();
$this->db->insert('cron_task_place_student_paper', $insert_data);
}
@unlink($upload_file);
$data = array();
$data['place_id'] = $place_id;
$data['message'] = $message;
$data['stat'] = $stat;
$this->load->view('place_student/import_student_result', $data);
}
}
