/**
* 批量导入
*/
public function import()
{
$file = $this->request->request('file');
if (!$file) {
$this->error(__('Parameter %s can not be empty', 'file'));
}
$filePath = ROOT_PATH . DS . 'public' . DS . $file;
if (!is_file($filePath)) {
$this->error(__('No results were found'));
}
//实例化reader
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
$this->error(__('Unknown data format'));
}
if ($ext === 'csv') {
$file = fopen($filePath, 'r');
$filePath = tempnam(sys_get_temp_dir(), 'import_csv');
$fp = fopen($filePath, "w");
$n = 0;
while ($line = fgets($file)) {
$line = rtrim($line, "\n\r\0");
$encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
if ($encoding != 'utf-8') {
$line = mb_convert_encoding($line, 'utf-8', $encoding);
}
if ($n == 0 || preg_match('/^".*"$/', $line)) {
fwrite($fp, $line . "\n");
} else {
fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
}
$n++;
}
fclose($file) || fclose($fp);
$reader = new Csv();
} elseif ($ext === 'xls') {
$reader = new Xls();
} else {
$reader = new Xlsx();
}
//导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
//$importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';
//模板文件列名
$listName = ['红人名称', '联系邮箱', '性别', '年龄区间', '特征标签', '国家地区', '语言', '站点', '社交平台', '平台链接', '平台粉丝', '状态等级', '合作提出方', '是否合作', '创建时间', '创建人', '首次合作时间', '带货/素材', '合作类型', '洽谈合作费', '总销售额', '专属折扣', '注册邮箱'];
try {
if (!$PHPExcel = $reader->load($filePath)) {
$this->error(__('Unknown data format'));
}
$currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表
$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
$maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
$fields = [];
for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$fields[] = $val;
}
}
//模板文件不正确
if ($listName !== $fields) {
throw new Exception("模板文件不正确！！");
}
$data = [];
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$cell = $currentSheet->getCellByColumnAndRow($currentColumn,$currentRow);
if($cell->getDataType()==DataType::TYPE_NUMERIC){
$cellstyleformat = $cell->getStyle($cell->getCoordinate())
->getNumberFormat();
$formatcode = $cellstyleformat->getFormatCode();
if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $formatcode)) {
$val = DateChange::excelToTimestamp($val);
$num = 8*60*60;
$val = $val-$num;
$val = date('Y-m-d H:i:s',$val);
}
}
$data[$currentRow - 2][$currentColumn - 1] = is_null($val) ? '' : $val;
}
}
} catch (Exception $exception) {
$this->error($exception->getMessage());
}
if (!$data) {
$this->error('未导入任何数据！！');
}
$email_is_only=$this->model->where('is_del',1)->column('email');
$email_is_only = collection($email_is_only)->toArray();
$reg_email_is_only=$this->model->where('is_del',1)->column('reg_email');
$reg_email_is_only = collection($reg_email_is_only)->toArray();
$discount_is_only=$this->model->where('is_del',1)->column('discount');
$discount_is_only = collection($discount_is_only)->toArray();
$old_data=$this->model->where('is_del',1)->field('celebrity_name,reg_email,site_id')->select();
$old_data = collection($old_data)->toArray();
$diff_data = [];
foreach ($old_data as $vv){
$diff_data[] = implode(',',$vv);
}
//批量添加产品
foreach ($data as $k => $v) {
if (empty($v[0])) {
$this->error('导入失败！！,红人名称不能为空');
}
if (empty($v[1])) {
$this->error('导入失败！！,联系邮箱不能为空');
}
if (empty($v[13])) {
$this->error('导入失败！！,是否合作不能为空');
}
if (empty($v[15])) {
$this->error('导入失败！！,创建人不能为空');
}
if (!isset($v[19])) {
$this->error('导入失败！！,洽谈合作费不能为空');
}
if (!isset($v[22])) {
$this->error('导入失败！！,注册邮箱不能为空');
}
$params[$k]['celebrity_name'] = $v[0];//红人名称
$params[$k]['email'] = $v[1];//联系邮箱
$params[$k]['sex'] = '男'==$v[2]?1:2;//性别
// $params[$k]['age'] = $v[3];//年龄区间
// $params[$k]['celebrity_tag'] = $v[4];//特征标签
// $params[$k]['country_id'] = $v[5];//国家地区
// $params[$k]['language'] = $v[6];//语言
// $params[$k]['site_id'] = $v[7];//站点
// $params[$k]['platform_id'] = $v[8];//社交平台
// $params[$k]['link'] = $v[9];//平台链接
// $params[$k]['fens'] = $v[10];//平台粉丝
// $params[$k]['intention_level'] = $v[11];//状态等级
$params[$k]['co_sponsor'] = '红人'==$v[12]?2:1;//合作提出方
$params[$k]['is_cooperation'] = '是'==$v[13]?1:0;//是否合作
$params[$k]['create_time'] = $v[14];//创建时间
$params[$k]['create_person'] = $v[15];//创建人
$params[$k]['cooperation_time'] = $v[16];//首次合作时间
// $params[$k]['cooperation_goods'] = $v[17];//带货/素材素材
// $params[$k]['cooperation_type'] = $v[18];//合作类型
$params[$k]['cooperation_fee'] = $v[19];//洽谈合作费
$params[$k]['total_sales'] = $v[20];//总销售额
$params[$k]['discount'] = $v[21];//专属折扣
$params[$k]['reg_email'] = $v[22];//注册邮箱
if (in_array($params[$k]['email'],$email_is_only)){
$num=$k+2;
$this->error("联系邮箱:[".$params[$k]['email']."]已存在,请检查,第".$num.'行');
}
if (in_array($params[$k]['discount'],$discount_is_only)){
$num=$k+2;
$this->error("专属折扣:[".$params[$k]['discount']."]已存在,请检查,第".$num.'行');
}
if (in_array($params[$k]['reg_email'],$reg_email_is_only)){
$num=$k+2;
$this->error("注册邮箱:[".$params[$k]['reg_email']."]已存在,请检查,第".$num.'行');
}
switch (strtolower($v[7])) {
case 'zeelool':
$params[$k]['site_id'] = 1;
break;
case 'voogueme':
$params[$k]['site_id'] = 2;
break;
case 'nihao':
$params[$k]['site_id'] = 3;
break;
case 'meeloog':
$params[$k]['site_id'] = 4;
break;
case 'wesee':
$params[$k]['site_id'] = 5;
break;
case 'amazon':
$params[$k]['site_id'] = 6;
break;
case 'zeelool_es':
$params[$k]['site_id'] = 7;
break;
case 'zeelool_jp':
$params[$k]['site_id'] = 8;
break;
case 'zeelool_de':
$params[$k]['site_id'] = 9;
break;
default:
$this->error('导入失败！！,站点来源有误');
break;
}
//判断是否存在相同数据
$in_data = $params[$k]['celebrity_name'].','.$params[$k]['reg_email'].','.$params[$k]['site_id'];
if (in_array($in_data,$diff_data)){
$num=$k+2;
$this->error($v[7]."站的红人：".$params[$k]['celebrity_name']."注册邮箱:[".$params[$k]['reg_email']."]已存在,请检查,第".$num.'行');
}
switch (trim($v[3])) {
case '25以下':
$params[$k]['age'] = 1;
break;
case '25-35':
$params[$k]['age'] = 2;
break;
case '35-45':
$params[$k]['age'] = 3;
break;
case '45-55':
$params[$k]['age'] = 4;
break;
case '55+':
$params[$k]['age'] = 5;
break;
default:
$this->error('导入失败！！,年龄区间不符合');
break;
}
$tags = explode(',',trim($v[4]));
$tmp_tag = [];
switch ($tags) {
case in_array('美妆', $tags):
$tmp_tag[] = "1";
break;
case in_array('发型', $tags):
$tmp_tag[] = "2";
break;
case in_array('美甲', $tags):
$tmp_tag[] = "3";
break;
case in_array('生活', $tags):
$tmp_tag[] = "4";
break;
case in_array('娱乐', $tags):
$tmp_tag[] = "5";
break;
case in_array('假发', $tags):
$tmp_tag[] = "6";
break;
case in_array('其他', $tags):
$tmp_tag[] = "7";
break;
default:
$tmp_tag[] = "";
break;
}
$params[$k]['celebrity_tag'] = json_encode($tmp_tag);
switch (trim($v[5])) {
case '美国':
$params[$k]['country_id'] = 1;
break;
case '英国':
$params[$k]['country_id'] = 2;
break;
case '意大利':
$params[$k]['country_id'] = 3;
break;
case '德国':
$params[$k]['country_id'] = 4;
break;
case '西班牙':
$params[$k]['country_id'] = 5;
break;
case '日本':
$params[$k]['country_id'] = 6;
break;
case '其他':
$params[$k]['country_id'] = 7;
break;
default:
$this->error('导入失败！！,国家地区不符合');
break;
}
switch (trim($v[6])) {
case '英语':
$params[$k]['language'] = 1;
break;
case '西语':
$params[$k]['language'] = 2;
break;
case '德语':
$params[$k]['language'] = 3;
break;
case '日语':
$params[$k]['language'] = 4;
break;
case '中文':
$params[$k]['language'] = 5;
break;
case '意大利语':
$params[$k]['language'] = 6;
break;
case '法语':
$params[$k]['language'] = 7;
break;
case '葡萄牙语':
$params[$k]['language'] = 8;
break;
case '韩语':
$params[$k]['language'] = 9;
break;
case '俄语':
$params[$k]['language'] = 10;
break;
case '其他':
$params[$k]['language'] = 11;
break;
default:
$this->error('导入失败！！,语言不符合');
break;
}
$plats = explode(',',trim($v[8]));
$tmp_plat = [];
switch ($plats) {
case in_array('YTB', $plats):
$tmp_plat[] = 1;
break;
case in_array('IG', $plats):
$tmp_plat[] = 2;
break;
case in_array('FB', $plats):
$tmp_plat[] = 3;
break;
case in_array('TT', $plats):
$tmp_plat[] = 4;
break;
case in_array('其他', $plats):
$tmp_plat[] = 5;
break;
default:
$tmp_plat[] = "";
break;
}
$params[$k]['platform_id'] = json_encode($tmp_plat);
$params[$k]['link'] = json_encode(explode(',',trim($v[9])));
$params[$k]['fens'] = json_encode(explode(',',trim($v[10])));
switch ($v[11]) {
case '意向':
$params[$k]['intention_level'] = 1;
break;
case '沟通':
$params[$k]['intention_level'] = 2;
break;
case '合作':
$params[$k]['intention_level'] = 3;
break;
case '流失':
$params[$k]['intention_level'] = 4;
break;
default:
$this->error('导入失败！！,意向等级为:高,中,低');
break;
}
//cooperation_goods
$goodss = explode(',',trim($v[17]));
$tmp_goods = [];
switch ($goodss) {
case in_array('带货', $goodss):
$tmp_goods[] = "1";
break;
case in_array('素材', $goodss):
$tmp_goods[] = "2";
break;
default:
$tmp_goods[] = "";
break;
}
$params[$k]['cooperation_goods'] = json_encode($tmp_goods);
$co_types = explode(',',trim($v[18]));
$tmp_co_type = [];
switch ($co_types) {
case in_array('图片', $co_types):
$tmp_co_type[] = "1";
break;
case in_array('长视频', $co_types):
$tmp_co_type[] = "2";
break;
case in_array('短视频', $co_types):
$tmp_co_type[] = "3";
break;
case in_array('其他', $co_types):
$tmp_co_type[] = "4";
break;
default:
$tmp_co_type[] = "";
break;
}
$params[$k]['cooperation_type'] = json_encode($tmp_co_type);
// $params[$k]['create_user_id'] = session('admin.id');
// $params[$k]['create_time'] = date('Y-m-d H:i:s', time());
}
$result = $this->model->allowField(true)->saveAll($params);
if ($result) {
$this->success('导入成功！！');
} else {
$this->error('导入失败！！');
}
}
1906
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
