/** * 导入机构和校区记录(从excel文件中), * 注意: 目前$ti_id参数未启用 * * @param int $ti_id = NULL 默认将校区全导入到该机构ID下 */ public function import_titc_excel($ti_id = NULL) { if ($_GET['dl'] == '1') { Func::dumpFile('application/vnd.ms-excel', 'file/import_training_campus_template.xls', '培训机构及校区导入模板.xls'); exit; } $data = array(); while (isset($_FILES['file'])) { $param = $_POST; $err_map = array(UPLOAD_ERR_OK => '没有错误发生,文件上传成功', UPLOAD_ERR_INI_SIZE => '上传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值', UPLOAD_ERR_FORM_SIZE => '上传文件的大小超过了 HTML 表单中 MAX_FILE_SIZE 选项指定的值', UPLOAD_ERR_PARTIAL => '文件只有部分被上传', UPLOAD_ERR_NO_FILE => '没有文件被上传', UPLOAD_ERR_NO_TMP_DIR => '找不到临时文件夹', UPLOAD_ERR_CANT_WRITE => '文件写入失败'); if ($_FILES['file']['error'] !== 0) { $data['error'] = $err_map[$_FILES['file']['error']]; break; } if (strpos($_FILES['file']['type'], 'excel') === false) { $mime = mime_content_type($_FILES['file']['tmp_name']); if (!in_array($mime, array('application/vnd.ms-excel', 'application/vnd.ms-office', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'))) { $data['error'] = "您上传的不是Excel文件({$mime})"; break; } } // 开始处理excel $excel = PHPExcel_IOFactory::load($_FILES['file']['tmp_name']); $sheet = $excel->getSheet(0); $row_num = $sheet->getHighestRow(); $col_num = $sheet->getHighestColumn(); $title = array('机构名称', '机构类型', '机构省份', '机构城市', '机构区县', '机构地址', '优先级', '学员人数/年', '机构网址', '校区名称', '校区省份', '校区城市', '校区区县', '校区地址', '联系人', '联系电话'); $col_num = base_convert($col_num, 36, 10); if ($col_num < count($title)) { $data['error'] = 'Excel列数验证未通过'; break; } $col_num = count($title); $col_char = array(); for ($j = 0; $j < $col_num; $j++) { $col_char[$j] = strtoupper(base_convert(10 + $j, 10, 36)); if ($title[$j] !== trim($sheet->getCell($col_char[$j] . '1')->getValue())) { $data['error'] = $col_char[$j] . '列标题不符'; break; } } if (isset($data['error'])) { break; } $rows = array(); for ($i = 2; $i <= $row_num; $i++) { $rows[$i - 2] = array(); for ($j = 0; $j < $col_num; $j++) { $rows[$i - 2][$j] = trim($sheet->getCell($col_char[$j] . $i)->getValue()); } if ($rows[$i - 2][0] == '') { unset($rows[$i - 2]); break; } } unset($sheet); unset($excel); if (empty($rows)) { $data['error'] = 'Excel文件工作表中没有任何要导入的记录'; break; } $tit_map = array_flip(array_map("trim", TrainingInstitutionModel::trainingInstitutionTypePairs())); $tipt_map = array_flip(array_map("trim", TrainingInstitutionModel::trainingInstitutionPriTypePairs())); $sql = <<<EOT SELECT * FROM rd_region WHERE region_id > 1 ORDER BY region_type EOT; $region_list = Fn::db()->fetchAll($sql); // 以region_id为键以行记录为值,同时加个children,以保存直接子项 $region_map = array(); // 以名字加'_'连接起来为键,以region_id为值 $regionname_map = array(); foreach ($region_list as $v) { $region_map[$v['region_id']] = $v; $region_map[$v['region_id']]['children'] = array(); if ($v['region_type'] == 1) { $regionname_map[trim($v['region_name'])] = $v['region_id']; } else { if ($v['region_type'] == 2) { $region_map[$v['parent_id']]['children'][] = $v['region_id']; $regionname_map[trim($region_map[$v['parent_id']]['region_name']) . '_' . trim($v['region_name'])] = $v['region_id']; } else { if ($v['region_type'] == 3) { $region_map[$v['parent_id']]['children'][] = $v['region_id']; $regionname_map[trim($region_map[$region_map[$v['parent_id']]['parent_id']]['region_name']) . '_' . trim($region_map[$v['parent_id']]['region_name']) . '_' . trim($v['region_name'])] = $v['region_id']; } } } } unset($region_list); $row_num = count($rows); $ti_arr = array(); //ti_arr[ti_name] => ti_list[i] $tc_arr = array(); //tc_arr[ti_name][tc_name] => tc_list[i] $ti_list = array(); $tc_list = array(); foreach ($rows as $k => $row) { // 机构名称 if (mb_strlen($row[0], 'UTF-8') > 60) { $data['error'] = $col_char[0] . ($k + 2) . ' - "机构名称"内容太长了,不可超过60个字符'; break; } $row['ti_name'] = $row[0]; // 机构类型 if ($row[1] == '') { $data['error'] = $col_char[1] . ($k + 2) . ' - "机构类型"内容不可为空'; break; } // 机构类型 if (!isset($tit_map[$row[1]])) { $data['error'] = $col_char[1] . ($k + 2) . ' - "机构类型"不正确'; break; } $row['ti_typeid'] = $tit_map[$row[1]]; if ($row[2] == '') { if ($param['ti_provid_required']) { $data['error'] = $col_char[2] . ($k + 2) . ' - "机构省份"不可为空'; break; } else { $row['ti_provid'] = 0; } } else { if (!isset($regionname_map[$row[2]])) { $data['error'] = $col_char[2] . ($k + 2) . ' - "机构省份"不存在'; break; } $row['ti_provid'] = $regionname_map[$row[2]]; } $row['ti_cityid'] = 0; $row['ti_areaid'] = 0; if (!empty($region_map[$regionname_map[$row[2]]]['children'])) { // 验证市 if ($row[3] == '') { if ($param['ti_cityid_required']) { $data['error'] = $col_char[3] . ($k + 2) . ' - "机构城市"不可为空'; break; } else { $row['ti_cityid'] = 0; } } else { if (!isset($regionname_map[$row[2] . '_' . $row[3]])) { $data['error'] = $col_char[3] . ($k + 2) . ' - "机构城市"不存在'; break; } $row['ti_cityid'] = $regionname_map[$row[2] . '_' . $row[3]]; } // 验证区县 if ($row[4] == '') { if ($param['ti_areaid_required']) { $data['error'] = $col_char[4] . ($k + 2) . ' - "机构区县"不可为空'; break; } else { $row['ti_areaid'] = 0; } } else { if (!isset($regionname_map[$row[2] . '_' . $row[3] . '_' . $row[4]])) { $data['error'] = $col_char[4] . ($k + 2) . ' - "机构区县"不存在'; break; } $row['ti_areaid'] = $regionname_map[$row[2] . '_' . $row[3] . '_' . $row[4]]; } } // 机构地址 if ($row[5] == '') { if ($param['ti_addr_required']) { $data['error'] = $col_char[5] . ($k + 2) . ' - "机构地址"不可为空'; break; } } if (mb_strlen($row[5], 'UTF-8') > 255) { $data['error'] = $col_char[5] . ($k + 2) . ' - "机构地址"内容太长了'; break; } $row['ti_addr'] = $row[5]; // 优先级 if ($row[6] == '') { if ($param['ti_priid_required']) { $data['error'] = $col_char[6] . ($k + 2) . ' - "优先级"不能为空'; break; } else { $row[6] = '一般'; } } if (!isset($tipt_map[$row[6]])) { $data['error'] = $col_char[6] . ($k + 2) . ' - "优先级"不正确'; break; } $row['ti_priid'] = $tipt_map[$row[6]]; if ($row[7] == '') { if ($param['ti_stumax_required']) { $data['error'] = $col_char[6] . ($k + 2) . ' - "学员人数/年"不能为空'; break; } else { $row[7] = '0'; } } // 学员人数/年 if (!Validate::isInt($row[7]) || $row[7] < 0) { $data['error'] = $col_char[7] . ($k + 2) . ' - "学员人数/年"必须为正整数'; break; } $row['ti_stumax'] = $row[7]; // 机构网址 if (mb_strlen($row[8], 'UTF-8') > 512) { $data['error'] = $col_char[8] . ($k + 2) . ' - "机构网址"内容太长了'; break; } $row['ti_url'] = $row[8]; // 校区名称 if ($row[9] == '') { $data['error'] = $col_char[9] . ($k + 2) . ' - "校区名称"不可为空'; break; } if (mb_strlen($row[9], 'UTF-8') > 60) { $data['error'] = $col_char[9] . ($k + 2) . ' - "校区名称"太长了'; break; } $row['tc_name'] = $row[9]; if ($row[10] == '') { if ($param['tc_provid_required']) { $data['error'] = $col_char[10] . ($k + 2) . ' - "校区省份"不可为空'; break; } else { $row['tc_provid'] = 0; } } else { if (!isset($regionname_map[$row[10]])) { $data['error'] = $col_char[10] . ($k + 2) . ' - "校区省份"不存在'; break; } $row['tc_provid'] = $regionname_map[$row[10]]; } $row['tc_cityid'] = 0; $row['tc_areaid'] = 0; if (!empty($region_map[$regionname_map[$row[10]]]['children'])) { // 验证市 if ($row[11] == '') { if ($param['tc_cityid_required']) { $data['error'] = $col_char[11] . ($k + 2) . ' - "校区城市"不可为空'; break; } else { $row['tc_cityid'] = 0; } } else { if (!isset($regionname_map[$row[10] . '_' . $row[11]])) { $data['error'] = $col_char[11] . ($k + 2) . ' - "校区城市"不存在'; break; } $row['tc_cityid'] = $regionname_map[$row[10] . '_' . $row[11]]; } // 验证区县 if ($row[12] == '') { if ($param['tc_areaid_required']) { $data['error'] = $col_char[12] . ($k + 2) . ' - "校区区县"不可为空'; break; } else { $row[12] = 0; } } else { if (!isset($regionname_map[$row[10] . '_' . $row[11] . '_' . $row[12]])) { $data['error'] = $col_char[12] . ($k + 2) . ' - "校区区县"不存在'; break; } $row['tc_areaid'] = $regionname_map[$row[10] . '_' . $row[11] . '_' . $row[12]]; } } // 校区地址 if ($row[13] == '') { if ($param['tc_ctcaddr_required']) { $data['error'] = $col_char[13] . ($k + 2) . ' - "校区地址"不可为空'; break; } } if (mb_strlen($row[13], 'UTF-8') > 255) { $data['error'] = $col_char[13] . ($k + 2) . ' - "校区地址"太长了'; break; } $row['tc_ctcaddr'] = $row[13]; // 联系人 if (mb_strlen($row[14], 'UTF-8') > 60) { $data['error'] = $col_char[14] . ($k + 2) . ' - "联系人"太长了'; break; } $row['tc_ctcperson'] = $row[14]; // 联系电话 if ($row['15'] == '') { if ($param['tc_ctcphone_required']) { $data['error'] = $col_char[15] . ($k + 2) . ' - "联系电话"不能为空'; break; } } if (mb_strlen($row[15], 'UTF-8') > 120) { $data['error'] = $col_char[15] . ($k + 2) . ' - "联系电话"太长了'; break; } $row['tc_ctcphone'] = $row[15]; if (!isset($ti_arr[$row[0]])) { $ti_arr[$row[0]] = count($ti_list); $ti_list[] = array('index' => $k + 2, 'ti_id' => 0, 'ti_name' => $row[0], 'ti_typeid' => $row['ti_typeid'], 'ti_provid' => $row['ti_provid'], 'ti_cityid' => $row['ti_cityid'], 'ti_areaid' => $row['ti_areaid'], 'ti_addr' => $row[5], 'ti_priid' => $row['ti_priid'], 'ti_stumax' => $row[7], 'ti_url' => $row[8]); } if (!isset($tc_arr[$row[0]])) { $tc_arr[$row[0]] = array(); } if (isset($tc_arr[$row[0]][$row[9]])) { $data['error'] = $col_char[9] . ($k + 2) . ' - "校区名称"同一机构内有重复'; break; } $tc_arr[$row[0]][$row[9]] = count($tc_list); $tc_list[] = array('index' => $k + 2, 'ti_name' => $row[0], 'tc_name' => $row[9], 'tc_provid' => $row['tc_provid'], 'tc_cityid' => $row['tc_cityid'], 'tc_areaid' => $row['tc_areaid'], 'tc_ctcaddr' => $row[13], 'tc_ctcperson' => $row[14], 'tc_ctcphone' => $row[15]); } if (isset($data['error'])) { break; } unset($region_map); unset($regionname_map); unset($tit_map); unset($tipt_map); unset($rows); // 这里开始导入 try { $db = Fn::db(); if (!$db->beginTransaction()) { throw new Exception('开始导入事务处理失败'); } $time = time(); $adduid = Fn::sess()->userdata('admin_id'); // 导入机构 $sql1 = <<<EOT SELECT ti_id FROM t_training_institution WHERE ti_name = ? EOT; $sql2 = <<<EOT SELECT tc_id FROM t_training_campus WHERE tc_tiid = ? AND tc_name = ? EOT; $sql3 = <<<EOT UPDATE t_training_institution SET ti_campusnum = ti_campusnum + 1 WHERE ti_id = EOT; $tc_update = 0; $tc_insert = 0; $ti_update = 0; $ti_insert = 0; foreach ($ti_list as $k => $row) { $ti_id = $db->fetchOne($sql1, array($row['ti_name'])); if ($ti_id) { $ti_list[$k]['ti_id'] = $ti_id; // update $row2 = array(); if ($param['same_tiname_update_ti_typeid']) { $row2['ti_typeid'] = $row['ti_typeid']; } if ($param['same_tiname_update_ti_region'] && intval($row['ti_provid']) != 0) { $row2['ti_provid'] = $row['ti_provid']; $row2['ti_cityid'] = $row['ti_provid']; $row2['ti_areaid'] = $row['ti_areaid']; } if ($param['same_tiname_update_ti_addr'] && $row['ti_addr'] != '') { $row2['ti_addr'] = $row['ti_addr']; } if ($param['same_tiname_update_ti_priid']) { $row2['ti_priid'] = $row['ti_priid']; } if ($param['same_tiname_update_ti_stumax'] && intval($row['ti_stumax']) != '0') { $row2['ti_stumax'] = $row['ti_stumax']; } if ($param['same_tiname_update_ti_url'] && $row['ti_url'] != '') { $row2['ti_url'] = $row['ti_url']; } if ($row2) { $db->update('t_training_institution', $row2, 'ti_id = ' . $ti_id); $ti_update++; } } else { // insert unset($row['index']); unset($row['ti_id']); $row['ti_flag'] = $time; if ($row['ti_addr'] == '') { $row['ti_addr'] = NULL; } if ($row['ti_url'] == '') { $row['ti_url'] = NULL; } $row['ti_addtime'] = date('Y-m-d H:i:s', $time); $row['ti_adduid'] = $adduid; $db->insert('t_training_institution', $row); $ti_list[$k]['ti_id'] = $db->lastInsertId('t_training_institution', 'ti_id'); $ti_insert++; } } // 导入校区 foreach ($tc_list as $k => $row) { $ti_id = $ti_list[$ti_arr[$row['ti_name']]]['ti_id']; $tc_list[$k]['ti_id'] = $ti_id; $tc_id = $db->fetchOne($sql2, array($ti_id, $row['tc_name'])); if ($tc_id) { // update $tc_list[$k]['tc_id'] = $tc_id; $row2 = array(); if ($param['same_tcname_update_tc_region'] && intval($row['tc_provid']) != 0) { $row2['tc_provid'] = $row['tc_provid']; $row2['tc_cityid'] = $row['tc_provid']; $row2['tc_areaid'] = $row['tc_areaid']; } if ($param['same_tcname_update_tc_ctcaddr'] && $row['tc_ctcaddr'] != '') { $row2['tc_ctcaddr'] = $row['tc_ctcaddr']; } if ($param['same_tcname_update_tc_ctcperson'] && $row['tc_ctcperson'] != '') { $row2['tc_ctcperson'] = $row['tc_ctcperson']; } if ($param['same_tcname_update_tc_ctcphone'] && $row['tc_ctcphone'] != '') { $row2['tc_ctcphone'] = $row['tc_ctcphone']; } if ($row2) { $db->update('t_training_campus', $row2, 'tc_id = ' . $tc_id); $tc_update++; } } else { // insert unset($row['index']); unset($row['ti_name']); $row['tc_tiid'] = $ti_id; $row['tc_flag'] = $time; $row['tc_environ'] = 3; $row['tc_addtime'] = date('Y-m-d H:i:s', $time); $row['tc_adduid'] = $adduid; if ($row['tc_ctcperson'] == '') { $row['tc_ctcperson'] = NULL; } $db->insert('t_training_campus', $row); $tc_list[$k]['tc_id'] = $db->lastInsertId('t_training_campus', 'tc_id'); $db->exec($sql3 . $ti_id); $tc_insert++; } } if ($db->commit()) { $data['success'] = <<<EOT 导入Excel文件({$_FILES['file']['name']})成功,共更新{$ti_update}条机构记录, 插入{$ti_insert}条机构记录, 更新{$tc_update}条校区记录, 插入{$tc_insert}条校区记录 EOT; admin_log('import', '', "培训机构和校区 " . $data['success']); } else { $err = $db->errorInfo()[2]; $db->rollBack(); throw new Exception($err); } } catch (Exception $e) { $data['error'] = $e->getMessage(); } break; } if (!isset($_FILES['file'])) { $param = array('ti_provid_required' => 1, 'ti_cityid_required' => 2, 'ti_areaid_required' => 3, 'ti_addr_required' => 1, 'ti_priid_required' => 1, 'ti_stumax_required' => 1, 'tc_provid_required' => 1, 'tc_cityid_required' => 2, 'tc_areaid_required' => 3, 'tc_ctcaddr_required' => 1, 'tc_ctcphone_required' => 1, 'same_tiname_update_ti_typeid' => 1, 'same_tiname_update_ti_region' => 1, 'same_tiname_update_ti_addr' => 1, 'same_tiname_update_ti_priid' => 1, 'same_tiname_update_ti_stumax' => 1, 'same_tiname_update_ti_url' => 1, 'same_tcname_update_tc_region' => 1, 'same_tcname_update_tc_ctcaddr' => 1, 'same_tcname_update_tc_ctcperson' => 1, 'same_tcname_update_tc_ctcphone' => 1); // 同名校区更新联系电话 } $data['param'] = $param; $this->load->view('traininginstitution/import_titc_excel', $data); }