Vendor("PHPExcel.IOFactory");
$objPHPExcel = \PHPExcel_IOFactory::load("./Data/data.xlsx");
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
// 导入excel
public function importExcel($filepath,$start_rows,$end_rows)
{
Vendor("PHPExcel.IOFactory");
set_time_limit(200);
// excel类型
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
// 分割excel数据表
$filterSubset = new \PHPExcel_MyReadFilter($start_rows,$end_rows,range('A','R'));
$objReader->setReadFilter($filterSubset);
$objPHPExcel = $objReader->load($filepath);
// foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {
// foreach ($sheet->getRowIterator() as $row) {
// foreach ($row->getCellIterator() as $cell) {
// $data = $cell->getValue();
// echo $data.' ; ';
// }
// echo "</br>";
// }
// }
// $objPHPExcel = \PHPExcel_IOFactory::load($filepath);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
unset($sheetData[1]);
$x = count($sheetData) + 1;
for ($i = 2; $i <= $x; $i++) {
if (empty(array_filter($sheetData[$i]))) {
unset($sheetData[$i]);
}
}
$sheetData = array_values($sheetData);
// 获取所有渠道ID
$where['status'] = 0;
$soulists = Db::name('source_list')->where($where)->select();
foreach($soulists as $key => $val){
$slists[$val['source_id']] = $val['source_name'];
}
// 获取邀约人
$map['status'] = 1;
$userlist = Db::name('user')->where($map)->select();
foreach($userlist as $ukey => $uval){
$ulist[$uval['id']] = $uval['name'];
}
// 应聘职位(1正在招聘;2急招;3停止)
$deList = Db::query('SELECT job_id,job_name FROM recruitment_jobs');
foreach ($deList as $k => $v) {
$joblist[$v['job_id']] = $v['job_name'];
}
foreach ($sheetData as $key => $value) {
// 邀约人ID
$userl = array_search($value['C'],$ulist);
if($userl){
$sheetData[$key]['user_id'] = $userl;
}else{
$this->error('第' . ($key + 2) . "行C列有错误");
}
//应聘职位id
$mm = array_search($value['D'], $joblist);
if (!$mm) {
$this->error('第' . ($key + 2) . "行D列有错误或没有此职位");
}
$sheetData[$key]['apply_for_job'] = $mm;
//sex
if ($value['F'] == '男') {
$sheetData[$key]['sex'] = 1;
} elseif ($value['F'] == '女') {
$sheetData[$key]['sex'] = 2;
} else {
$this->error('第' . ($key + 2) . "行F列有错误");
}
//edu
switch ($value['K']) {
case '高中':
$sheetData[$key]['edu_level'] = 1;
break;
case '中专':
$sheetData[$key]['edu_level'] = 2;
break;
case '大专':
$sheetData[$key]['edu_level'] = 3;
break;
case '本科':
$sheetData[$key]['edu_level'] = 4;
break;
case '硕士':
$sheetData[$key]['edu_level'] = 5;
break;
case '博士':
$sheetData[$key]['edu_level'] = 6;
break;
case '研究生':
$sheetData[$key]['edu_level'] = 7;
break;
default:
$this->error('第' . ($key + 2) . "行K列有错误");
break;
}
// 资格证书
if ($value['L'] == '有') {
$sheetData[$key]['sex'] = 1;
} elseif ($value['L'] == '无' || $value['L'] == '') {
$sheetData[$key]['sex'] = 0;
} else {
$this->error('第' . ($key + 2) . "行L列有错误");
}
//source判断是否有此渠道
$source_id = array_search($value['M'],$slists);
if($source_id == false)
{
$this->error('第' . ($key + 2) . "行M列有错误");
}else{
$sheetData[$key]['source_id'] = $source_id;
}
if($value['N'] == '投递'){
$sheetData[$key]['type'] = 3;
}elseif($value['N'] == '搜索'){
$sheetData[$key]['type'] = 4;
}elseif(array_search($value['N'],$slists)){
$sheetData[$key]['type'] = array_search($value['N'],$slists);
}
// 沟通记录
$sheetData[$key]['contact_remark'] = $value['P'].';'.$value['Q'].';'.$value['R'];
}
$x = count($sheetData);
for ($i = 0; $i < $x; $i++) {
// $linkManCond['phone_number'] = $sheetData[$i]['H'];
// $linkManCond['link_man_name'] = $sheetData[$i]['E'];
// $linkManExist = Db::table('link_man')->where($linkManCond)->find();
// if ($linkManExist) {
// continue;
// } else {
//not exist can insert
$linkManData['link_man_name'] = $sheetData[$i]['E'];
$linkManData['sex'] = $sheetData[$i]['sex'];
$linkManData['age'] = $sheetData[$i]['G'];
$linkManData['user_id'] = $sheetData[$i]['user_id'];
$linkManData['apply_for_job'] = $sheetData[$i]['apply_for_job'];
$linkManData['phone_number'] = $sheetData[$i]['H'];
// $linkManData['email'] = $sheetData[$i]['F'];
// $linkManData['wechat_number'] = $sheetData[$i]['G'];
$linkManData['create_time'] = time();
$linkManData['source'] = $sheetData[$i]['source_id'];
$linkManData['type'] = $sheetData[$i]['type'];
// $linkManId = Db::name('link_man')->insertGetId($linkManData);
// $eduData['link_man_id'] = $linkManId;
$eduData['school_name'] = $sheetData[$i]['I'];
$eduData['school_major'] = $sheetData[$i]['J'];
$eduData['education_level'] = $sheetData[$i]['edu_level'];
// $eduData['graduation_year_month'] = strtotime($sheetData[$i]['K']);
// $res = Db::name('education')->insert($eduData);
// 电话沟通
$teleData['contact_time'] = strtotime($sheetData[$i]['B']);
// $teleData['link_man_id'] = $linkManId;
$teleData['user_id'] = $sheetData[$i]['user_id'];
$teleData['contact_remark'] = $sheetData[$i]['contact_remark'];
$teleData['phone_res'] = 0;
// $teleres = Db::name('phone_contact')->insert($teleData);
// 面试预约
$interview = $sheetData[$i]['O'];
// 启动事务
Db::startTrans();
try{
$linkManId = Db::name('link_man')->insertGetId($linkManData);
$eduData['link_man_id'] = $linkManId;
$teleData['link_man_id'] = $linkManId;
$interData['link_man_id'] = $linkManId;
$res = Db::name('education')->insert($eduData);
$teleres = Db::name('phone_contact')->insert($teleData);
// 提交事务
Db::commit();
} catch (\Exception $e) {
// 回滚事务
Db::rollback();
}
if($interview != ''){
// $interData['link_man_id'] = $linkManId;
$interData['user_id'] = $sheetData[$i]['user_id'];
$interData['interview_invitation_time'] = strtotime($interview);
$interData['invitation_time'] = strtotime($sheetData[$i]['B']);
$interData['invitation_res'] = 1;
Db::name('interview_invitation')->insert($interData);
}
// }
}
if (empty($res) || empty($linkManId)) {
$this->error('导入失败');
} else {
$start_rows += 1000;
$this->importExcel($filepath,$start_rows);
$this->success('导入成功');
}
}