function index()
{
$objPHPExcel = new PHPExcel();
$inputFileType = 'HTML';
$inputFileName = 'assets/file/sample4.html';
$outputFileType = 'Excel2007';
$outputFileName = 'assets/file/sample4.xls';
$filename = "DownloadReport.xls";
$objPHPExcelReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcelReader->load($inputFileName);
$objget = $objPHPExcel->getActiveSheet();
$objget->getColumnDimension('A')->setWidth(5);
$objget->getColumnDimension('B')->setWidth(5);
$objget->getColumnDimension('C')->setWidth(5);
$objget->getColumnDimension('D')->setWidth(20);
$objget->getColumnDimension('E')->setWidth(50);
$objget->getColumnDimension('F')->setWidth(50);
$objget->getColumnDimension('G')->setWidth(20);
ini_set('zlib.output_compression', 'Off');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
//the folowing two lines make sure it is saved as a xls file
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=' . $filename);
//simpan dalam file sample.xls
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
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 insertXls()
{
$inputFileType = 'Excel5';
$inputFileName = 'nameoffile_2.xls';
/** Create a new Reader of the type defined in $inputFileType **/
$objReader = IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
echo json_encode($sheetData);
}
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 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');
}
}
public function importInfo()
{
//导入对应的phpexcel库
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
//上传对应的Excel文件
//文件上传
//基本配置
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'xls|xlsx|xl';
$config['max_size'] = '20000';
$config['file_name'] = time() . mt_rand(1000, 9999);
//载入上传类
$this->load->library('upload', $config);
//执行上传
$status = $this->upload->do_upload('Excel');
$wrong = $this->upload->display_errors();
if ($wrong) {
error($wrong);
}
//返回信息
$info = $this->upload->data();
// p($info);
//再进行解析对应的Excel文件
//找到对应的文件
$targetfile = './uploads/' . $info['file_name'];
//生成对应的格式
//需要注意的是,这里的excel文件要对应好不同的格式
$objReader = IOFactory::createReader('Excel2007');
//读取整个execl文件生成excel的object
$objPHPExcel = $objReader->load($targetfile);
//获取总的sheet数
$size = $objPHPExcel->getSheetCount();
//读取第一行sheet
$sheet = $objPHPExcel->getSheet(0);
//获取总行数
$hightRow = $sheet->getHighestRow();
// 取得总列数
$hightColum = $sheet->getHighestColumn();
//全部的excel对象数组
p($objPHPExcel->getActiveSheet()->toArray());
p($hightRow);
p($hightColum);
//再进行插入数据库进行保存
}
public function inportExcel()
{
//上传对应的Excel文件
//文件上传
//基本配置
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'xls|xlsx|xl';
$config['max_size'] = '20000';
$config['file_name'] = time() . mt_rand(1000, 9999);
//载入上传类
$this->load->library('upload', $config);
//执行上传
$status = $this->upload->do_upload('Excel');
$wrong = $this->upload->display_errors();
if ($wrong) {
error($wrong);
}
//返回信息
$info = $this->upload->data();
// p($info);
//再进行解析对应的Excel文件
//找到对应的文件
$targetfile = './uploads/' . $info['file_name'];
//生成对应的格式
//需要注意的是,这里的excel文件要对应好不同的格式
$objReader = IOFactory::createReader('Excel2007');
//读取整个execl文件生成excel的object
$objPHPExcel = $objReader->load($targetfile);
//获取总的sheet数
$size = $objPHPExcel->getSheetCount();
//全部数据保存在的数组
$alldata = array();
for ($i = 0; $i < $size; $i++) {
//读取第一行sheet
$sheet = $objPHPExcel->getSheet($i);
//获取总行数
$hightRow = $sheet->getHighestRow();
// 取得总列数
$hightColum = $sheet->getHighestColumn();
//全部的excel对象数组
// p($objPHPExcel->getActiveSheet()->toArray());
// p($hightColum);
// p($hightRow);
// echo "<hr>";
// 获取excel对象的全部数据
$importdata = $objPHPExcel->getActiveSheet()->toArray();
$z = 0;
// 存放字段值数组
$firstname = array();
foreach ($importdata as $key => $value) {
if ($z == 0) {
$firstname = $this->toNameforArray($value);
} else {
$newinfoarr = $this->newarraydata($firstname, $value);
//生成新的数组
array_push($alldata, $newinfoarr);
}
$z++;
}
}
// p($alldata);
//批量导入问题
$this->register->insertAllbatch($alldata);
success('demo/login/getDataIndex', '批量导入' . count($alldata) . '条数据成功....');
}
/**
* Create non-existing reader
*
* @expectedException \PhpOffice\PhpWord\Exception\Exception
*/
public function testNonexistentReaderCanNotBeCreated()
{
IOFactory::createReader('Word2006');
}
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');
}
/**
* 导入用户
*
*/
public function import_user()
{
if (!empty($_FILES) && isset($_FILES['userfile']) && $_FILES['userfile']['error'] == 0) {
$config = array();
$config = array('upload_path' => realpath(dirname(SELF)) . '/resources/', 'allowed_types' => array('xls', 'xlsx'), 'max_size' => '2048', 'file_name' => 'user_' . time());
$this->load->library('upload', $config);
if (!$this->upload->do_upload()) {
$this->to_api_message(0, $this->upload->display_errors());
} else {
$upload_data = $this->upload->data();
//文件的一些信息
$excel_path = $upload_data['full_path'];
@chmod($excel_path, 0644);
require_once APPPATH . '/libraries/PHPExcel.php';
require_once APPPATH . '/libraries/PHPExcel/IOFactory.php';
$objReader = IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($excel_path);
$sheet = $objPHPExcel->getSheet(0);
// 读取第一工作表
$highestRow = $sheet->getHighestRow();
// 取得总行数
$highestColumm = $sheet->getHighestColumn();
// 取得总列数
$this->load->model('webModel/User_group', 'user_group');
$this->load->model('webModel/Setting_model', 'setting');
$setting = $this->setting->get();
$identity_name = '';
if ($setting['service_type'] == 1) {
$app_info = array();
$app_info = array('app_id' => $setting['app_id'], 'app_key' => $setting['app_key'], 'use_private' => FALSE);
$this->load->library('Secken', $app_info);
}
$error = array();
$success_row = $error_row = 0;
for ($row = 1; $row <= $highestRow; $row++) {
//行数是以第1行开始
if ($row == 1) {
continue;
}
$user_name = $sheet->getCell('A' . $row)->getValue();
$true_name = $sheet->getCell('B' . $row)->getValue();
$phone = $sheet->getCell('C' . $row)->getValue();
if (is_null($user_name) && is_null($true_name) && is_null($phone)) {
continue;
}
if (is_null($user_name)) {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('username_not_allow_empty'));
continue;
}
//检查用户名
$check = $this->user->check_user_name($user_name);
if ($check) {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('username_is_exists'));
continue;
}
if (is_null($true_name)) {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('truename_not_allow_empty'));
continue;
}
//检查手机号
$this->load->helper('phone');
$valid_phone = valid_phone($phone);
if ($valid_phone === FALSE) {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('phone_invalid'));
continue;
}
$check = $this->user->check_phone($phone);
if ($check) {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('phone_is_exists'));
continue;
}
if ($this->secken) {
settype($phone, "string");
$result = $this->secken->exchangeUid($phone);
$respone_code = $this->secken->getCode();
$identity_name = '';
if ($respone_code == 200) {
$identity_name = $result['uids'][0];
if (empty($identity_name)) {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('please_login_on_yangcong'));
continue;
}
} else {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('secken_config_error'));
break;
}
} else {
$error_row++;
$error[] = array('row' => $row, 'user_name' => $user_name, 'true_name' => $true_name, 'phone' => $phone, 'error' => $this->lang->line('secken_config_error'));
break;
}
$insertData = array();
$insertData = array('user_name' => $user_name, 'phone' => $phone, 'true_name' => $true_name, 'yangcong_uid' => $identity_name, 'create_time' => date('Y-m-d H:i:s'), 'update_time' => date('Y-m-d H:i:s'));
$op_description = sprintf("导入了新用户:%s", $user_name);
$insert_id = $this->user->insert($insertData);
if ($insert_id) {
$insertData = array();
$insertData = array('user_id' => $insert_id, 'gid' => 1);
$insert_id = $this->user_group->insert($insertData);
if ($insert_id) {
$success_row++;
$this->add_op_log($op_description, 1);
} else {
$error_row++;
$this->add_op_log($op_description, 0);
}
} else {
$error_row++;
$this->add_op_log($op_description, 0);
}
}
$data = array();
$data = array('success_row' => $success_row, 'error_row' => $error_row, 'error' => $error);
$this->to_api_message(1, 'import_user', $data);
}
}
}
/**
* 导入处理
*
* @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';
}
}
public function inputXLS()
{
$FID = $this->input->post('uploadSchemeId');
$config['upload_path'] = './fileFolder/';
$config['allowed_types'] = 'gif|jpg|png|txt|xls|doc';
$config['max_size'] = 100;
$config['max_width'] = 1024;
$config['max_height'] = 768;
$name = $_FILES["file"]["name"];
$config['file_name'] = iconv("UTF-8", "gb2312", $name);
$this->load->library('upload', $config);
if (!$this->upload->do_upload('file')) {
$error = array('error' => $this->upload->display_errors());
//$this->load->view('upload_form', $error);
} else {
$data = array('upload_data' => $this->upload->data());
$filePath = './fileFolder/' . $data['upload_data']['file_name'];
$insertdata['EnclosurePath'] = iconv("gb2312", "UTF-8", $filePath);
$inputFileType = 'Excel5';
$inputFileName = $filePath;
$objReader = IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$row = 0;
$result = "";
foreach ($sheetData as $key => $value) {
if ($row == 0) {
$row++;
continue;
}
$insertArr = array();
$insertArr['FBONUSDATE'] = $value['G'];
$insertArr['FBONUSAMOUNT'] = $value['H'];
$count = $this->BonusRecord_model->getBonusCountWithTime($value['A'], $value['F']);
$tableName = 'T_BONUSRECORD';
$this->load->model('Tools');
if (intval($count) > 0) {
$where = 'FSUBSCRIBECONFIGRMRECORDID=' . $value['A'] . ' AND FBONUSTIMES=' . $value['F'];
$result = $this->Tools->updateData($insertArr, $tableName, $where);
} else {
$insertArr['FSUBSCRIBECONFIGRMRECORDID'] = $value['A'];
$insertArr['FBONUSTIMES'] = $value['F'];
$result = $this->Tools->addData($insertArr, $tableName);
}
}
if ($result) {
echo json_decode($result);
}
}
}
/**
* 根据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);
}
}
//echo "<table>";
//
//foreach ($sheet_array as $row) {
// echo "<tr>";
// foreach ($row as $column)
// echo "<td>$column.'BOS</td>";
// echo "</tr>";
//}
//
//echo "</table>";
//or
//echo first cell of excel file
$file = $path;
Yii::import('application.vendors.phpexcel.PHPExcel', true);
//DEARDSADS
$objReader = IOFactory::createReader('Excel2007');
//$objReader->setReadDataOnly('true');
$objPHPExcel = $objReader->load($file);
//$file --> your filepath and filename
//$objWorksheet = $objPHPExcel->getActiveSheet();
//$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
//$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
//$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5
//echo '<table>' . "\n";
//for ($row = 1; $row <= $highestRow; ++$row) {
// echo '<tr>' . "\n";
// for ($col = 0; $col <= $highestColumnIndex; ++$col) {
// echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
// }
// echo '</tr>' . "\n";
//}
function cetak()
{
$type = $this->uri->segment(4);
$qs = urldecode($_SERVER['QUERY_STRING']);
parse_str($qs, $qs_data);
$params = array();
// foreach ($qs_data as $key => $val)
// $params[$key] = $val;
$kondisi = '';
$status = $this->input->get('kondisi');
if ($status != '') {
$kondisi = "AND cd.customer_status_id={$status}";
}
$rpt = $this->input->get('rpt');
switch ($rpt) {
case 'daf_induk_wp':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'kondisi' => $kondisi);
break;
case 'daf_induk_wp_perjenis':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'usahaid' => (int) $this->input->get('usahaid'), 'kondisi' => $kondisi);
break;
case 'daf_induk_wp_perkec':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'kecid' => (int) $this->input->get('kecid'), 'kondisi' => $kondisi);
break;
case 'daf_wp_baru':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'daf_wp_baru_jenis':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'usahaid' => (int) $this->input->get('usahaid'), 'kondisi' => $kondisi);
break;
case 'daf_wp_baru_kec':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kecid' => (int) $this->input->get('kecid'), 'kondisi' => $kondisi);
break;
case 'daf_wp_jenis_perkec':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'usahaid' => (int) $this->input->get('usahaid'), 'kondisi' => $kondisi);
break;
case 'daf_wp_tutup':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))));
break;
case 'daf_wp_ijin_berakhir':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))));
break;
case 'daf_rekap_kec':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))));
break;
}
$tambahan = array("daerah" => pad_pemda_daerah(), "dinas" => pad_pemda_nama());
$params = array_merge($params, $tambahan);
$rpt = 'pendaftaran/' . $rpt;
// var_dump($params);
// die;
$jasper = $this->load->library('Jasper');
//IF PDF
if ($type == 'pdf') {
echo $jasper->cetak($rpt, $params, $type, false);
} else {
if ($type == 'html') {
$assetpath = 'assets/file';
$tmp = $assetpath . '/tmp/report' . sipkd_user_id() . '.html';
if (is_file($tmp)) {
unlink($tmp);
}
ob_start();
echo $jasper->cetak($rpt, $params, $type, false);
echo '1';
file_put_contents($tmp, ob_get_contents());
$objPHPExcel = new PHPExcel();
$inputFileType = 'HTML';
$inputFileName = $tmp;
$outputFileType = 'Excel2007';
$outputFileName = $assetpath . '/Report.xls';
$filename = $rpt . date("d-m-Y") . ".xls";
$objPHPExcelReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcelReader->load($inputFileName);
ini_set('zlib.output_compression', 'Off');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
//the folowing two lines make sure it is saved as a xls file
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=' . $filename);
//simpan dalam file sample.xls
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
}
}
function cetak()
{
$type = $this->uri->segment(4);
$qs = urldecode($_SERVER['QUERY_STRING']);
parse_str($qs, $qs_data);
$params = array();
// foreach ($qs_data as $key => $val)
// $params[$key] = $val;
$kondisi = $this->input->get('kondisi');
if (!empty($kondisi)) {
$kondisi = 'and s.type_id=' . $this->input->get('kondisi');
}
$rpt = $this->input->get('rpt');
$tglawal = date('Y-m-d', strtotime($this->input->get('tglawal')));
$tglakhir = date('Y-m-d', strtotime($this->input->get('tglakhir')));
$tglawalbayar = date('Y-m-d', strtotime($this->input->get('tglawalbayar')));
$tglakhirbayar = date('Y-m-d', strtotime($this->input->get('tglakhirbayar')));
$tglcetak = date('Y-m-d', strtotime($this->input->get('tglcetak')));
$ignore_html_pg = TRUE;
switch ($rpt) {
case 'tap_register':
case 'tap_register_airtanah':
$type_id = intval($this->input->get('type_id'));
if ($type_id > 0) {
$kondisi = 'and s.type_id=' . $type_id;
} else {
$kondisi = '';
}
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_kendali':
case 'tap_kendali_blm_bayar':
$type_id = intval($this->input->get('type_id'));
if ($type_id > 0) {
$kondisi = 'and s.type_id=' . $type_id;
} else {
$kondisi = '';
}
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_kendali_self':
case 'tap_kendali_self_sdh_bayar':
// $kondisi = ' and s.type_id='.pad_dok_self_id();
$kondisi = ' and cd.usaha_id not in(' . pad_reklame_id() . ',' . pad_air_tanah_id() . ') ';
$kondisi .= " and date(s.terimatgl) between '{$tglawal}' and '{$tglakhir}' ";
$kondisi_bayar = " and date(ss.sspdtgl) between '{$tglawalbayar}' and '{$tglakhirbayar}' ";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi, 'kondisi_bayar' => $kondisi_bayar);
break;
case 'tap_kendali_blm_bayar_self':
// $kondisi = 'and s.type_id='.pad_dok_self_id();
$kondisi = ' and cd.usaha_id not in(' . pad_reklame_id() . ',' . pad_air_tanah_id() . ') ';
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_kendali_airtanah':
case 'tap_kendali_airtanah_sdh_bayar':
$type_id = intval($this->input->get('type_id'));
if ($type_id > 0) {
$kondisi = 'and s.type_id=' . $type_id;
} else {
$kondisi = '';
}
$kondisi .= " and date(k.kohirtgl) between '{$tglawal}' and '{$tglakhir}' ";
$kondisi_bayar = " and date(ss.sspdtgl) between '{$tglawalbayar}' and '{$tglakhirbayar}' ";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi, 'kondisi_bayar' => $kondisi_bayar);
break;
case 'tap_kendali_airtanah_blm_bayar':
$type_id = intval($this->input->get('type_id'));
if ($type_id > 0) {
$kondisi = 'and s.type_id=' . $type_id;
} else {
$kondisi = '';
}
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_register_reklame':
$type_id = intval($this->input->get('type_id'));
if ($type_id > 0) {
$kondisi = 'and s.type_id=' . $type_id;
} else {
$kondisi = '';
}
$naskah = trim($this->input->get('naskah'));
$kondisi .= $naskah != "" ? " and s.r_judul ilike '%{$naskah}%' " : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_register_reklame_jthtempo':
$type_id = intval($this->input->get('type_id'));
if ($type_id > 0) {
$kondisi = 'and s.type_id=' . $type_id;
} else {
$kondisi = '';
}
$naskah = trim($this->input->get('naskah'));
$kondisi .= $naskah != "" ? " and s.r_judul ilike '%{$naskah}%' " : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_kendali_reklame':
case 'tap_kendali_reklame_sdh_bayar':
$insidentil = intval($this->input->get('insidentil'));
$type_id = intval($this->input->get('type_id'));
$kondisi = '';
if ($insidentil > -1) {
$kondisi .= ' and r.insidentil=' . $insidentil;
}
if ($type_id > 0) {
$kondisi .= ' and s.type_id=' . $type_id;
}
$naskah = trim($this->input->get('naskah'));
$kondisi .= $naskah != "" ? " and s.r_judul ilike '%{$naskah}%' " : "";
$kondisi .= " and date(k.kohirtgl) between '{$tglawal}' and '{$tglakhir}' ";
$kondisi_bayar = " and date(ss.sspdtgl) between '{$tglawalbayar}' and '{$tglakhirbayar}' ";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi, 'kondisi_bayar' => $kondisi_bayar);
// print_r($kondisi_bayar);exit;
break;
case 'tap_kendali_reklame_blm_bayar':
$insidentil = intval($this->input->get('insidentil'));
$type_id = intval($this->input->get('type_id'));
$kondisi = '';
if ($insidentil > -1) {
$kondisi .= ' and r.insidentil=' . $insidentil;
}
if ($type_id > 0) {
$kondisi .= ' and s.type_id=' . $type_id;
}
$naskah = trim($this->input->get('naskah'));
$kondisi .= $naskah != "" ? " and s.r_judul ilike '%{$naskah}%' " : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_kendali_reklame_jthtempo_tgl':
$naskah = trim($this->input->get('naskah'));
$kondisi = $naskah != "" ? " and s.r_judul ilike '%{$naskah}%' " : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_kendali_reklame_jthtempo':
$naskah = trim($this->input->get('naskah'));
$kondisi = $naskah != "" ? " and s.r_judul ilike '%{$naskah}%' " : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'tap_kendali_reklame_jthtempo_rek':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'rekeningid' => (int) $this->input->get('rekeningid'));
break;
case 'tap_kendali_airtanah':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'bulan' => (int) $this->input->get('bulan'), 'tahun' => (int) $this->input->get('tahun'));
break;
case 'tap_kendali_airtanah_skpd':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))));
break;
case 'tap_kendali_airtanah_tgl':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))));
break;
case 'tap_kendali_airtanah_masa':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))));
break;
case 'tap_catat_vol_airtanah':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tahun' => (int) $this->input->get('tahun'));
break;
}
$tambahan = array("daerah" => pad_pemda_daerah());
$params = array_merge($params, $tambahan);
$ignore_html_pg = false;
//paging aja semua
$rpt = 'penetapan/' . $rpt;
// var_dump($params);
// die;
$jasper = $this->load->library('Jasper');
if ($type == 'pdf') {
echo $jasper->cetak($rpt, $params, $type, $ignore_html_pg);
} else {
if ($type == 'html') {
$assetpath = 'assets/file';
$tmp = $assetpath . '/tmp/report' . sipkd_user_id() . '.html';
if (is_file($tmp)) {
unlink($tmp);
}
ob_start();
echo $jasper->cetak($rpt, $params, $type, $ignore_html_pg);
echo '1';
file_put_contents($tmp, ob_get_contents());
$objPHPExcel = new PHPExcel();
$inputFileType = 'HTML';
$inputFileName = $tmp;
$outputFileType = 'Excel2007';
$outputFileName = $assetpath . '/Report.xls';
$filename = $rpt . date("d-m-Y") . ".xls";
$objPHPExcelReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcelReader->load($inputFileName);
ini_set('zlib.output_compression', 'Off');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
//the folowing two lines make sure it is saved as a xls file
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=' . $filename);
//simpan dalam file sample.xls
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
}
// echo $jasper->query_debug($rpt, $params);
}
/**
* 评估报告下载处理
*
* @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 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);
}
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);
}
function cetak()
{
$type = $this->uri->segment(4);
$qs = urldecode($_SERVER['QUERY_STRING']);
parse_str($qs, $qs_data);
$params = array();
// foreach ($qs_data as $key => $val)
// $params[$key] = $val;
$rpt = $this->input->get('rpt');
$ignore_html_pg = TRUE;
switch ($rpt) {
case 'dat_sptpd_masuk':
$usahaid = trim($this->input->get('usahaid'));
$kondisi = intval($usahaid) > 0 ? " and cd.usaha_id={$usahaid}" : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))), 'kondisi' => $kondisi);
break;
case 'dat_sptpd_masuk_masa':
$usahaid = trim($this->input->get('usahaid'));
$kondisi = intval($usahaid) > 0 ? " and cd.usaha_id={$usahaid}" : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'masabulan' => (int) $this->input->get('bulan'), 'masatahun' => (int) $this->input->get('tahun'), 'tahun' => (int) pad_tahun_anggaran(), 'kondisi' => $kondisi);
break;
case 'dat_sptpd_masuk_tgl_all':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'tglawal' => date('Y-m-d', strtotime($this->input->get('tglawal'))), 'tglakhir' => date('Y-m-d', strtotime($this->input->get('tglakhir'))));
break;
case 'dat_sptpd_masuk_masa_all':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'bulan' => (int) $this->input->get('bulan'), 'tahun' => (int) $this->input->get('tahun'));
break;
case 'dat_sptpd_blm_masuk_masa':
$usahaid = trim($this->input->get('usahaid'));
$kondisi = intval($usahaid) > 0 ? " and cu.usaha_id={$usahaid}" : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'masabulan' => (int) $this->input->get('bulan'), 'masatahun' => (int) $this->input->get('tahun'), 'tahun' => (int) pad_tahun_anggaran(), 'kondisi' => $kondisi);
break;
case 'dat_sptpd_blm_masuk_masa_all':
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'bulan' => (int) $this->input->get('bulan'), 'tahun' => (int) $this->input->get('tahun'));
break;
case 'dat_srt_pemberitahuan':
$usahaid = trim($this->input->get('usahaid'));
$kondisi = intval($usahaid) > 0 ? " and cu.usaha_id={$usahaid}" : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'masabulan' => (int) $this->input->get('bulan'), 'masatahun' => (int) $this->input->get('tahun'), 'tahun' => (int) pad_tahun_anggaran(), 'kondisi' => $kondisi);
break;
case 'dat_srt_teguran':
$usahaid = trim($this->input->get('usahaid'));
$kondisi = intval($usahaid) > 0 ? " and cu.usaha_id={$usahaid}" : "";
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'masabulan' => (int) $this->input->get('bulan'), 'masatahun' => (int) $this->input->get('tahun'), 'tahun' => (int) pad_tahun_anggaran(), 'kondisi' => $kondisi);
break;
case 'dat_srt_teguran_global':
$ignore_html_pg = FALSE;
$params = array('tglcetak' => date('Y-m-d', strtotime($this->input->get('tglcetak'))), 'usahaid' => (int) $this->input->get('usahaid'));
break;
}
$tambahan = array("daerah" => pad_pemda_daerah(), "dinas" => pad_pemda_nama(), "ttd" => base_url('assets/img/ttd.gif'));
$params = array_merge($params, $tambahan);
$ignore_html_pg = FALSE;
//paging aja semua
$rpt = 'pendataan/' . $rpt;
$jasper = $this->load->library('Jasper');
if ($type == 'pdf') {
echo $jasper->cetak($rpt, $params, $type, $ignore_html_pg);
} else {
if ($type == 'html') {
$assetpath = 'assets/file';
$tmp = $assetpath . '/tmp/report' . sipkd_user_id() . '.html';
if (is_file($tmp)) {
unlink($tmp);
}
ob_start();
echo $jasper->cetak($rpt, $params, $type, $ignore_html_pg);
echo '1';
file_put_contents($tmp, ob_get_contents());
$objPHPExcel = new PHPExcel();
$inputFileType = 'HTML';
$inputFileName = $tmp;
$outputFileType = 'Excel2007';
$outputFileName = $assetpath . '/Report.xls';
$filename = $rpt . date("d-m-Y") . ".xls";
$objPHPExcelReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcelReader->load($inputFileName);
ini_set('zlib.output_compression', 'Off');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
//the folowing two lines make sure it is saved as a xls file
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=' . $filename);
//simpan dalam file sample.xls
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
}
}
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);
}
/**
* 处理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;
}
