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;
}
