public function Insert() { if ($this->input->post('Import')) { set_time_limit(3600); //FOR UPLOAD $fileName = $_FILES['fileimport']['name']; //FOR UPLOAD $config['upload_path'] = BASEPATH . '../includes/assets/'; $config['file_name'] = $fileName; $config['allowed_types'] = 'csv'; $config['max_size'] = 1000000; $this->load->library('upload'); $this->upload->initialize($config); if (!$this->upload->do_upload('fileimport')) { $error = $this->upload->display_errors(); echo $error; exit; } //FOR READ $inputFileName = BASEPATH . '../includes/assets/' . $fileName; //READ your excel workbook try { $inputFileType = IOFactory::identify($inputFileName); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } //Get Worksheet dimendions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); //Loop through each row of thw Worksheet in turn for ($row = 1; $row <= $highestRow; $row++) { $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); if (strlen($rowData[0][0]) == 1) { $new_rowData = '000' . $rowData[0][0]; } else { if (strlen($rowData[0][0]) == 2) { $new_rowData = '00' . $rowData[0][0]; } else { $new_rowData = $rowData[0][0]; } } //iNSERT ROW DATA ARRAY INTO YOUR DATABASE OF CHOISE HERE $data_upload = array('id_kekuatan' => $new_rowData, 'kekuatan' => $rowData[0][1]); $this->db->insert(TABLE, $data_upload); } echo 'Compile for insert to table ' . TABLE . ' success'; } else { $view_data['controller'] = 'kekuatan'; $view_data['table'] = TABLE; $view_data['body'] = 'compiler/import'; $this->load->view('compiler/wrapper', $view_data); } }
public function Insert() { if ($this->input->post('Import')) { //FOR UPLOAD $fileName = $_FILES['fileimport']['name']; //FOR UPLOAD $config['upload_path'] = BASEPATH . '../includes/assets/'; $config['file_name'] = $fileName; $config['allowed_types'] = 'csv'; $config['max_size'] = 1000000; $this->load->library('upload'); $this->upload->initialize($config); if (!$this->upload->do_upload('fileimport')) { $error = $this->upload->display_errors(); echo $error; exit; } //FOR READ $inputFileName = BASEPATH . '../includes/assets/' . $fileName; //READ your excel workbook try { $inputFileType = IOFactory::identify($inputFileName); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } //Get Worksheet dimendions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); //Loop through each row of thw Worksheet in turn for ($row = 2; $row <= $highestRow; $row++) { $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); //iNSERT ROW DATA ARRAY INTO YOUR DATABASE OF CHOISE HERE $i = 0; $data_upload = array('id_kelas_terapi' => $rowData[0][0], 'id_sub_kelas_terapi1' => $rowData[0][1], 'id_sub_kelas_terapi2' => $rowData[0][2], 'id_sub_kelas_terapi3' => $rowData[0][3], 'nama_obat' => $rowData[0][4], 'id_atc_obat' => $rowData[0][5], 'id sediaan' => $rowData[0][6], 'id_kekuatan' => $rowData[0][7], 'id_satuan' => $rowData[0][8], 'subkutan' => $rowData[0][9], 'intrakutan' => $rowData[0][10], 'intramuscular' => $rowData[0][11], 'intravena' => $rowData[0][12], 'intravena_bolus' => $rowData[0][13], 'intra_arteri' => $rowData[0][14], 'intralumbal' => $rowData[0][15], 'intraperitoneal' => $rowData[0][16], 'intrapleural' => $rowData[0][17], 'intracardial' => $rowData[0][18], 'anti_artikuler' => $rowData[0][19], 'implantasi_subkutan' => $rowData[0][20], 'rektal' => $rowData[0][21], 'intranasal' => $rowData[0][22], 'intra_okuler' => $rowData[0][23], 'intra_aurikuler' => $rowData[0][24], 'intrapulmonal' => $rowData[0][25], 'intravaginal' => $rowData[0][26], 'infus_drip' => $rowData[0][27], 'injeksi_infiltr' => $rowData[0][28], 'pv' => $rowData[0][29], 'Tk1' => $rowData[0][30], 'Tk2' => $rowData[0][31], 'Tk3' => $rowData[0][32], 'PRB' => $rowData[0][33], 'PP' => $rowData[0][34], 'restriksi_kelas_terapi' => $rowData[0][35], 'restriksi_sub_kelas_terapi1' => $rowData[0][36], 'restriksi_sub_kelas_terapi2' => $rowData[0][37], 'restriksi_sub_kelas_terapi3' => $rowData[0][38], 'restriksi_obat' => $rowData[0][39], 'tambahan_restriksi_obat' => $rowData[0][40], 'tambahan_restriksi_obat2' => $rowData[0][41], 'restriksi_sediaan' => $rowData[0][42], 'restriksi1' => $rowData[0][43], 'restriksi2' => $rowData[0][44], 'restriksi3' => $rowData[0][45], 'restriksi4' => $rowData[0][46], 'restriksi5' => $rowData[0][47]); $i++; $this->db->insert(TABLE, $data_upload); } echo 'Compile for insert to table ' . TABLE . ' success'; } else { $view_data['controller'] = 'fornas'; $view_data['table'] = TABLE; $view_data['body'] = 'compiler/import'; $this->load->view('compiler/wrapper', $view_data); } }
function upload_history_post() { $this->db->trans_begin(); $UploadDirectory = 'assets/excel/'; //Upload Directory, ends with slash & make sure folder exist $NewFileName = ""; //die($UploadDirectory); // replace with your mysql database details if (!@file_exists($UploadDirectory)) { //destination folder does not exist die('No upload directory'); } if (isset($_FILES['import']['name'])) { // $foto = post_safe('gambar'); $FileName = strtolower($_FILES['import']['name']); //uploaded file name $FileTitle = $FileName; $ImageExt = substr($FileName, strrpos($FileName, '.')); //file extension $FileType = $_FILES['import']['type']; //file type //$FileSize = $_FILES['import']["size"]; //file size $RandNumber = ''; //rand(0, 999); //Random number to make each filename unique. //echo $FileType; die; //$uploaded_date = date("Y-m-d H:i:s"); // if ($foto !== '') { // @unlink('assets/images/projects/'.$foto); // } switch (strtolower($FileType)) { //allowed file types // case 'image/png': //png file // case 'image/gif': //gif file // case 'image/jpeg': //jpeg file // case 'application/pdf': //PDF file // case 'application/msword': //ms word file case 'application/vnd.ms-excel': //ms excel file //ms excel file case 'application/octet-stream': //ms excel file //ms excel file case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet': //xlsx file // case 'text/plain': //text file // case 'text/html': //html file break; default: die('Unsupported File!'); //output error } //File Title will be used as new File name $NewFileName = preg_replace(array('/\\s/', '/\\.[\\.]+/', '/[^\\w_\\.\\-]/'), array('_', '.', ''), strtolower($FileTitle)); $NewFileName = $NewFileName . '_' . $RandNumber . $ImageExt; //Rename and save uploded file to destination folder. if (move_uploaded_file($_FILES['import']["tmp_name"], $UploadDirectory . $NewFileName)) { } else { die('error uploading File!'); } } //$media = $this->upload->data('import'); $inputFileName = './assets/excel/' . $NewFileName; // Read your Excel workbook $this->load->library(array('PHPExcel', 'PHPExcel/IOFactory')); try { $inputFileType = IOFactory::identify($inputFileName); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // Loop through each row of the worksheet in turn for ($row = 2; $row <= $highestRow; $row++) { // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); // Insert row data array into your database of choice here if ($rowData[0][1] !== NULL) { $get_data = $this->db->get_where('tb_kategori_cuti', array('id' => post_safe('jenis_pengajuan_cuti')))->row(); $data = array('id_kategori_cuti' => post_safe('jenis_pengajuan_cuti'), 'tanggal' => date2mysql_export($rowData[0][7]), 'nip' => str_replace(' ', '', $rowData[0][2]), 'nama' => $rowData[0][1], 'pangkat' => $rowData[0][3] !== NULL ? $rowData[0][3] : '', 'jabatan' => $rowData[0][4] !== NULL ? $rowData[0][4] : '', 'unit_satuan' => $rowData[0][5] !== NULL ? $rowData[0][5] : '', 'status_pengajuan' => 'Selesai', 'no_ijin' => '', 'jumlah' => $rowData[0][6], 'satuan' => 'Hari', 'dari' => date2mysql_export($rowData[0][7]), 'sampai' => $rowData[0][8] !== NULL ? date2mysql_export($rowData[0][8]) : NULL, 'tanggal_cetak' => date2mysql_export($rowData[0][7]), 'keterangan_disetujui' => $get_data->keterangan_pokok_surat, 'tembusan' => $rowData[0][10] !== NULL ? $rowData[0][10] : '', 'import_excel' => 'Ya'); $this->db->insert('tb_pengajuan_cuti', $data); if ($this->db->trans_status() === FALSE) { $this->db->trans_rollback(); $result['status'] = FALSE; } if ($this->db->trans_status() === FALSE) { $this->db->trans_rollback(); $result['status'] = FALSE; } } } if ($this->db->trans_status() === FALSE) { $this->db->trans_rollback(); $result['status'] = FALSE; } else { $this->db->trans_commit(); $result['status'] = TRUE; } $this->response($result, 200); }
/** * 评估报告下载处理 * * @return void */ public function report_deal() { $post = $this->input->post(); /** 上传文件 */ $config['upload_path'] = '../../cache/excel/'; $config['allowed_types'] = '*'; $config['max_size'] = 1024 * 10; #单位kb $config['overwrite'] = false; $this->load->library('upload', $config); if (!$this->upload->do_upload('file')) { $this->report($this->upload->display_errors()); } else { /* 教师信息 */ $teacher = $this->session->userdata('teacher'); $upload_data = $this->upload->data(); $message = array(); $exam_id = $post['exam_id']; if (empty($exam_id)) { message('未查询到当前考试期次信息!请联系管理员!'); } /* 考试期次信息 */ $sql = "SELECT * FROM {pre}exam WHERE exam_id={$exam_id}"; $exam = $this->db->query($sql)->row_array(); if (empty($exam)) { message('未查询到当前考试期次信息!请联系管理员!'); } /* 当前考试期次下的学科 */ $sql = "SELECT exam_id,subject_id FROM {pre}exam WHERE exam_pid={$exam_id}"; $subjects_exam_tmp = $this->db->query($sql)->result_array(); if (empty($subjects_exam_tmp)) { message('当前考试期次下不存在学科!请联系管理员!'); } $subjects_exam = array(); foreach ($subjects_exam_tmp as $key => $value) { $subjects_exam[$value['subject_id']] = $value; } /* 学科 */ $subjects_tmp = json_decode($teacher['subjects'], true); if (empty($subjects_tmp) || count($subjects_tmp) <= 0) { message('当前帐号暂未选择学科,请联系管理员!'); } $subjects = array(); /* 有报告的学科 */ foreach ($subjects_tmp as $key => $value) { if (isset($subjects_exam[$value]) || is_numeric($value) && $value == 0) { $subjects[$value]['is_exist'] = true; } else { $subjects[$value]['is_exist'] = false; } } /* 考试期次对应学生 考试期次-> 评估规则 -> 考生*/ $sql = "SELECT er.id AS rule_id,es.uids from {pre}evaluate_student AS es LEFT JOIN {pre}evaluate_rule AS er ON es.rule_id=er.id WHERE er.exam_pid={$exam_id}"; $row = $this->db->query($sql)->row_array(); if (empty($row)) { message('获取当前考试期次下考生信息失败,请联系管理员!'); } $rule_id = $row['rule_id']; $uids = explode(',', $row['uids']); if (empty($uids) || count($uids) <= 0) { message('当前考试期次下不存在考生!'); } /** 读取excel */ $this->load->library('PHPExcel'); $this->load->library('PHPExcel/IOFactory'); $inputFileType = IOFactory::identify($upload_data['file_relative_path']); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($upload_data['file_relative_path']); $data = $objPHPExcel->getSheet(0)->toArray(); if (empty($data) || count($data) <= 1) { message('表格数据错误,请下载表格模板从新填写!'); } $students = array(); foreach ($data as $key => $value) { if ($key < 1) { continue; } if (empty($value[0])) { continue; } /* 准考证号转换为uid */ $ticket = $value[0]; /* 准考证类型 */ /* if ($exam['exam_ticket_maprule'] > 0) { $sql = "SELECT uid,student_name AS name FROM {pre}exam_student_list WHERE student_ticket={$ticket}"; } else { */ $sql = "SELECT uid,concat(last_name,first_name) AS name FROM {pre}student WHERE exam_ticket={$ticket}"; /* } */ $row = $this->db->query($sql)->row_array(); $uid = $row['uid']; $students[$key]['ticket'] = $ticket; if (!empty($uid) && $uid > 0 && in_array($uid, $uids)) { $students[$key]['uid'] = $uid; $students[$key]['name'] = $row['name']; } else { $students[$key]['uid'] = false; } } $data = array(); $data['students'] = $students; $data['subjects'] = $subjects; $subject_sys = C('subject'); /* 加入总结报告 */ $subject_sys[0] = '总结'; $data['subject_sys'] = $subject_sys; $data['rule_id'] = $rule_id; $this->load->view('teacher_download/report_list', $data); } }
public function save_import() { set_time_limit(0); if (!$_FILES['profession_file']) { $message[] = '请选择导入的Excel文件'; } /** * 上传文件 */ $upload_path = '../../cache/excel/'; $file_name = microtime(true) . '.' . end(explode('.', $_FILES['profession_file']['name'])); $upload_file = $upload_path . $file_name; if (!is_dir($upload_path)) { mkdir($upload_path, '0777', true); } if (!@move_uploaded_file($_FILES['profession_file']['tmp_name'], $upload_file)) { message('导入文件失败,请重新导入!'); } //导入结果信息统计 $stat = array('total' => 0, 'success' => 0, 'fail' => 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++) { $list = array_filter($objPHPExcel->getSheet($i)->toArray()); if (empty($list)) { continue; } foreach ($list as $k => $v) { if (!$k) { continue; } $stat['total']++; $data = array(); try { if (!Validate::isNotEmpty($v[0])) { $stat['fail']++; continue; } $data['profession_name'] = htmlspecialchars(trim($v[0])); $data['profession_emerging'] = 0; if (trim($v[1]) == '是') { $data['profession_emerging'] = 1; } $data['profession_explain'] = htmlspecialchars(trim($v[2])); if (ProfessionModel::addProfession($data)) { $stat['success']++; } else { $stat['fail']++; } } catch (Exception $e) { $stat['fail']++; } } } @unlink($upload_file); message("本次导入共有{$stat['total']}个职业,成功导入{$stat['success']}个,失败{$stat['fail']}个。", '/admin/profession/index'); }
function import_excel_to_db() { if ($this->input->post('save_excel')) { $fileName = $_FILES['import']['name']; $config['upload_path'] = './assets/files/'; $config['file_name'] = $fileName; $config['allowed_types'] = 'xls|xlsx'; $config['max_size'] = 10000; $this->load->library('upload'); $this->upload->initialize($config); if (!$this->upload->do_upload('import')) { $this->upload->display_errors(); } //$media = $this->upload->data('import'); $inputFileName = './assets/files/' . $fileName; // Read your Excel workbook try { $inputFileType = IOFactory::identify($inputFileName); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // Loop through each row of the worksheet in turn for ($row = 2; $row <= $highestRow; $row++) { // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); // Insert row data array into your database of choice here $dt = array("kode_alat" => $rowData[0][0], "nama_alat" => $rowData[0][1], "satuan_alat" => $rowData[0][2], "harga_alat" => $rowData[0][3], "provinsi" => $rowData[0][4], "tahun" => $rowData[0][5]); $this->db->insert("tblalat", $dt); } //echo "Import Success"; $messageStat = TRUE; $message = "<strong> Import data dari Excel berhasil ditambahkan. </stong>"; $this->session->set_flashdata('message_status', $this->messageStat); $this->session->set_flashdata('flash_message', $message); redirect('alat'); } }
function do_upload() { $thn = base64_decode($_GET['xYz']); $this->db->trans_begin(); if ($this->input->post('save')) { $fileName = $_FILES['import']['name']; $config['upload_path'] = './assets/files/'; $config['file_name'] = $fileName; $config['allowed_types'] = 'xls|xlsx'; $config['max_size'] = 10000; $this->load->library('upload'); $this->upload->initialize($config); if (!$this->upload->do_upload('import')) { $this->upload->display_errors(); } $media = $this->upload->data('import'); $inputFileName = './assets/files/' . $media['file_name']; // Read your Excel workbook try { $inputFileType = IOFactory::identify($inputFileName); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch (Exception $e) { die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // Loop through each row of the worksheet in turn for ($row = 17; $row <= $highestRow; $row++) { // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); // Insert row data array into your database of choice here $jk = $rowData[0][5]; $jen = ""; if ($jk == "L") { $jen = "Pria"; } elseif ($jk == "P") { $jen = "Wanita"; } else { $jen = ""; } $data = array("nis" => '', "id_pendaftaran" => '', "kelas" => '', "name" => $rowData[0][4], "username" => '', "jenis_kelamin" => $jen, "tempat_lahir" => $rowData[0][8], "tanggal_lahir" => $rowData[0][9], "alamat" => $rowData[0][12], "tahun" => $thn, "active" => 0, "id_level" => 4); $query = $this->db->insert("ref_siswa", $data); /*if($query) { $hasil = 1; }else{ $hasil = 0; }*/ } // $this->db->trans_complete(); if ($this->db->trans_status() === FALSE) { $this->db->trans_rollback(); $hasil = 0; } else { $this->db->trans_commit(); $hasil = 1; } } $session = $this->session->userdata('login'); $data['nm_user_last'] = $session['nm_user_last']; $data['id_user'] = $session['id_user']; $data['session_level'] = $session['id_level']; $data['tahun_ajaran'] = $thn; $tahun = $session['tahun']; $status = 3; $data['result'] = $hasil; $this->load->view('upload/index', $data); }
public function read_excel2mysql() { //load libary PHPExcel $this->load->library('Excel', 'excel'); $this->load->library('IOFactory', false); $inputFileType = 'Excel5'; $inputFileName = "C:/xampp/htdocs/truck-transport/assets/upload/transport_pricelist.xls"; $this->excel = IOFactory::identify($inputFileName); $this->excel = IOFactory::createReader(); $this->excel->setReadDataOnly(true); $this->excel->load($inputFileName); $this->excel->setActiveSheetIndex(0); }
/** * 导入处理 * * @return mixed void */ public function save() { ini_set("display_errors", "On"); error_reporting(-1); $post = $this->input->post(); /** 上传文件 */ $config['upload_path'] = '../../cache/excel/'; $config['allowed_types'] = '*'; $config['max_size'] = 1024 * 10; #单位kb $config['overwrite'] = false; $this->load->library('upload', $config); if (!$this->upload->do_upload('file')) { $data = array(); $data['exam_id'] = $post['exam_id']; $data['error'] = $this->upload->display_errors(); $data['exams'] = ExamModel::get_exam_list_all(array('exam_pid' => '0', 'status' => '1'), 'exam_id,exam_name'); $this->load->view('interview/interview_import', $data); } else { /** 实时输出导入结果 */ ob_end_flush(); set_time_limit(0); $start_time = microtime(true); $upload_data = $this->upload->data(); /** 读取excel */ $this->load->library('PHPExcel'); $this->load->library('PHPExcel/IOFactory'); $inputFileType = IOFactory::identify($upload_data['file_relative_path']); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($upload_data['file_relative_path']); $data = $objPHPExcel->getSheet(0)->toArray(); $end_time = microtime(true); $execute_time = $end_time - $start_time; if (!empty($data)) { echo '文件载入成功!执行时间:' . sprintf('%.4f', $execute_time) . 's' . '<hr/>'; flush(); } /** 评分标准信息 */ $start_time = microtime(true); $relation = EvaluationStandardExamModel::get_standard_by_exam($post['exam_id']); $standard = EvaluationStandardModel::get_one($relation['standard_id']); $option_count = count(explode(',', $standard['options'])); $end_time = microtime(true); $execute_time = $end_time - $start_time; if ($standard && !empty($standard) && $option_count >= 1) { echo '获取考试期次对应评分标准信息成功!执行时间:' . sprintf('%.4f', $execute_time) . 's' . '<hr/>'; flush(); } else { echo '获取考试期次对应评分标准信息失败!请检查本期次对应评分标准及评分项!'; flush(); exit; } if ($standard['status'] != 1) { echo '当前评分标准已禁用!停止导入!<hr/>'; flush(); exit; } /** 导入结果 */ $start_time = microtime(true); foreach ($data as $key => $value) { if ($key < 1) { continue; } $student_ticket = trim($value[0]); $sql = "select * from {pre}exam_student_list where student_ticket={$student_ticket}"; $ruidabei_student = $this->db->query($sql)->row_array(); if (!$ruidabei_student['uid'] > 0) { echo "未查询到当前用户数据,在第" . ($key + 1) . "行。姓名:" . $value['1'] . "<br/>"; continue; } if (!$ruidabei_student) { echo "未查询到当前用户数据,在第" . ($key + 1) . "行。姓名:" . $value['1'] . "<br/>"; continue; } if ($ruidabei_student['student_name'] != $value[1]) { echo "当前用户睿达杯准考证号与姓名不符,在第" . ($key + 1) . "行。姓名:" . $value[1] . "<br/>"; continue; } $option_index = 1; /* 获取学科信息 */ $subject_name = "%" . $value[2] . "%"; $subject_id = $this->db->select('subject_id')->get_where('subject', array('subject_name like' => $subject_name))->row_array(); foreach ($value as $k => $v) { if ($k < 3 || $k >= 3 + $option_count) { continue; } /** 判断数据是否已存在数据库中 */ $param = array(); $param['exam_id'] = $post['exam_id']; $param['student_id'] = $ruidabei_student['uid']; $param['subject_id'] = $subject_id['subject_id']; $param['option_index'] = $option_index; $row = array(); $row['exam_id'] = $post['exam_id']; $row['student_id'] = $ruidabei_student['uid']; $row['subject_id'] = $subject_id['subject_id']; $row['option_index'] = $option_index; $row['scroe'] = $v ? $v : 0; $row['create_time'] = time(); $option_index++; $result = InterviewResultModel::get_one($param); if ($result && !empty($result)) { echo '<p style="color:red;">警告!本条数据已存在!将覆盖原有数据!在第' . ($key + 1) . '行:' . $param['student_id'] . '</p><br/>'; $result = InterviewResultModel::update(array('id' => $result['id']), $row); } else { $result = InterviewResultModel::add($row); } if ($result) { echo '<p style="color:green;">第' . ($key + 1) . '行' . '第' . ($k - 2) . '条导入成功!</p><br/>'; flush(); } else { echo '<p style="color:red;">错误!第' . ($key + 1) . '行' . '第' . ($k - 2) . '条导入失败!</p><br/>'; flush(); } } } $end_time = microtime(true); $execute_time = $end_time - $start_time; echo '导入成功!用时:' . $execute_time . 's'; } }
/** * 导入处理 * * @return mixed void */ public function save() { $post = $this->input->post(); /** 上传文件 */ $config['upload_path'] = '../../cache/excel/'; $config['allowed_types'] = '*'; $config['max_size'] = 1024 * 10; #单位kb $config['overwrite'] = false; $this->load->library('upload', $config); if (!$this->upload->do_upload('file')) { $data = array(); $data['exam_id'] = $post['exam_id']; $data['error'] = $this->upload->display_errors(); $data['exams'] = ExamModel::get_exam_list_all(array('exam_pid' => '0', 'status' => '1'), 'exam_id,exam_name'); $this->load->view('ruidabei/import', $data); } else { /** 实时输出导入结果 */ ob_end_flush(); set_time_limit(0); $start_time = microtime(true); $upload_data = $this->upload->data(); /** 读取excel */ $this->load->library('PHPExcel'); $this->load->library('PHPExcel/IOFactory'); $inputFileType = IOFactory::identify($upload_data['file_relative_path']); $objReader = IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($upload_data['file_relative_path']); $data = $objPHPExcel->getSheet(0)->toArray(); $end_time = microtime(true); $execute_time = $end_time - $start_time; if (!empty($data)) { echo '文件载入成功!执行时间:' . sprintf('%.4f', $execute_time) . 's' . '<hr/>'; flush(); } /** 导入结果 */ $start_time = microtime(true); foreach ($data as $key => $value) { if ($key < 1) { continue; } $student_ticket = $value[0]; $sql = "select * from {pre}exam_student_list where student_ticket={$student_ticket}"; $ruidabei_student = $this->db->query($sql)->row_array(); if (!$ruidabei_student) { echo "未查询到当前用户数据,在第" . ($key + 1) . "行:" . $value[1]; continue; } if ($ruidabei_student['student_name'] != $value[1]) { echo "当前用户睿达杯准考证号与姓名不符,在第" . ($key + 1) . "行" . $value[1]; continue; } /** 判断数据是否已存在数据库中 */ $param = array(); $param['exam_id'] = $post['exam_id']; $param['student_id'] = $ruidabei_student['uid']; $param['school_name'] = $value[2]; $param['grade'] = $value[7]; $param['subject'] = $value[6]; $row = array(); $row['exam_id'] = $post['exam_id']; $row['student_id'] = $ruidabei_student['uid']; $row['student_name'] = $value[1]; $row['school_name'] = $value[2]; $row['awards'] = $value[3]; $row['score'] = $value[4]; $row['ranks'] = $value[5]; $row['subject'] = $value[6]; $row['grade'] = $value[7]; $row['create_time'] = time(); $query_result = RuidabeiResultModel::get_one($param); if ($query_result) { echo '<p style="color:red;">警告!本条数据已存在!将覆盖原有数据!在第' . ($key + 1) . '行' . '</p><br/>'; $result = RuidabeiResultModel::update(array('id' => $query_result['id']), $row); } else { $result = RuidabeiResultModel::add($row); } if ($result) { echo '<p style="color:green;">第' . ($key + 1) . '行导入成功!</p><br/>'; flush(); } else { echo '<p style="color:red;">错误!第' . ($key + 1) . '行导入失败!</p><br/>'; flush(); } } $end_time = microtime(true); $execute_time = $end_time - $start_time; echo '导入成功!用时:' . $execute_time . 's'; } }
/** * 根据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); } }
/** * 处理excel数据 * @param string $filename 待处理文件 * @return mixed list<map<string, variant>>类型 */ private function _validate_file_excel($filename) { //加载PHPExcel类 $this->load->library('PHPExcel'); $this->load->library('PHPExcel/IOFactory'); /** Identify the type of $inputFileName **/ $inputFileType = IOFactory::identify($filename); /** Create a new Reader of the type that has been identified **/ $objReader = IOFactory::createReader($inputFileType); /** Load $inputFileName to a PHPExcel Object **/ $objPHPExcel = $objReader->load($filename); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumn = $sheet->getHighestColumn(); // 取得总列数 $code = CODE_SUCCESS; $msg = array(); $data = array(); $cache_data = array(); $empty_data = array(); $invalid_data = array(); $repeat_data = array(); //重复邮箱 $invalidate_subject_data = array(); //无效学科 //判断表头是否正确 if (trim($objPHPExcel->getActiveSheet()->getCell("A1")->getValue()) != '姓名' || trim($objPHPExcel->getActiveSheet()->getCell("B1")->getValue()) != '邮箱' || trim($objPHPExcel->getActiveSheet()->getCell("C1")->getValue()) != '角色') { return array('code' => CODE_ERROR, 'msg' => '表头字段不正确,必须为:姓名 邮箱 角色 (先后顺序不能跌倒)'); } //忽略表头 //$subjects = $this->_get_reversed_subjects(); $roles = Fn::db()->fetchPairs("SELECT role_id, role_name FROM rd_role"); if ($roles) { $roles = array_flip(array_map("trim", $roles)); } else { $roles = array(); } for ($i = 2; $i <= $highestRow; $i++) { $realname = trim($objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue()); //第一列姓名 $email = trim($objPHPExcel->getActiveSheet()->getCell("B" . $i)->getValue()); //第二列邮箱 $role_name = trim($objPHPExcel->getActiveSheet()->getCell("C" . $i)->getValue()); //第三列角色 //跳过全空行 if ($realname == '' && $email == '' && $role_name == '') { continue; } //不能为空验证 if ($realname == '' || $email == '' || $role_name == '') { $empty_data[] = array('line_number' => $i); } //角色验证 $role_id_arr = array(); $role_name_arr = array_unique(array_map("trim", explode(',', $role_name))); $role_name_arr2 = array(); foreach ($role_name_arr as $v) { if ($v == '') { continue; } $role_name_arr2[] = $v; } unset($role_name_arr); if (empty($role_name_arr2)) { $invalidate_subject_data[] = array('email' => $email, 'line_number' => $i, 'role_name' => $role_name); } else { foreach ($role_name_arr2 as $v) { if (!isset($roles[$v])) { $invalidate_subject_data[] = array('email' => $email, 'line_number' => $i, 'role_name' => $role_name); break; } $role_id_arr[] = $roles[$v]; } } //重复邮箱验证 if (isset($cache_data[$email])) { !isset($repeat_data[$email]) && ($repeat_data[$email] = array('current_line' => '', 'count' => '0', 'line_numbers' => '')); $repeat_data[$email]['current_line'] = $cache_data[$email]['line_number']; $repeat_data[$email]['count']++; $repeat_data[$email]['line_numbers'][] = $i; } //邮件合法性验证 if (!is_email($email)) { $invalid_data[] = array('email' => $email, 'line_number' => $i); continue; } $cache_data[$email] = array('line_number' => $i); $data[$email] = array('realname' => $realname, 'email' => $email, 'role_id' => $role_id_arr, 'role_name' => $role_name); } if (count($empty_data) || count($invalid_data) || count($repeat_data) || count($invalidate_subject_data)) { $code = CODE_ERROR; $msg[] = '导入失败,可能原因:<br/>'; if (count($empty_data)) { $msg[] = '<strong>以下行有未填信息,请调整后再试:</strong>'; foreach ($invalid_data as $k => $v) { $msg[] = '--》行号:' . $v['line_number']; } $msg[] = '<hr/>'; } if (count($invalid_data)) { $msg[] = '<strong>以下邮箱格式不正确,请调整后再试:</strong>'; foreach ($invalid_data as $k => $v) { $msg[] = '--》行号:' . $v['line_number'] . ', 邮箱:' . $v['email']; } $msg[] = '<hr/>'; } if (count($repeat_data)) { $msg[] = '<strong>以下邮箱格式有重复,请调整后再试:</strong>'; foreach ($repeat_data as $k => $v) { $msg[] = '--》与当前邮箱重复,当前行号:' . $v['current_line'] . ', 当前邮箱:' . $k . ',共有' . $v['count'] . '个邮箱与之重复,分别在行:' . implode(', ', $v['line_numbers']); } $msg[] = '<hr/>'; } if (count($invalidate_subject_data)) { $msg[] = '<strong>以下帐号角色不正确(不存在以下角色, 请调整后再试:</strong>'; foreach ($invalidate_subject_data as $k => $v) { $msg[] = '--》行号:' . $v['line_number'] . ', 邮箱:' . $v['email'] . ', 角色:' . $v['role_name']; } $msg[] = '<hr/>'; } //删除附件 //$this->_remove_file($filename); } $return_data = array('code' => $code, 'msg' => $msg, 'data' => $data); return $return_data; }