/** * 导入商品基本信息 * edit: bbs.it-home.org*/ public function importproductbasicinfo($data){ include_once 'phpexcel.php'; include_once 'phpexcel/iofactory.php'; include_once 'phpexcel/reader/excel5.php'; // 定义一个错误集合. $error = array(); $resultinfo = null; $neednext = true; //上传文件到服务器指定位置 $filename = $_files[productinfo]['name']; $filepath = cbase_common_uploadpicture::uploadfile($data[productinfo], 'product'); //如果上传文件成功，就执行导入excel操作 if($filepath == 1) { $error[1] = 上传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值; }else if($filepath == 4){ $error[4] = 没有文件被上传; }else{ $objreader = phpexcel_iofactory::createreader('excel5'); $objreader->setreaddataonly(true); $objphpexcel = $objreader->load($filepath); $objworksheet = $objphpexcel->getactivesheet(); $highestrow = $objworksheet->gethighestrow(); $highestcolumn = $objworksheet->gethighestcolumn(); $highestcolumnindex = phpexcel_cell::columnindexfromstring($highestcolumn); $colums = array(); $data = array(); $excelallid = array(); $excelidrow = array(); $execlallshoplinkedid = array(); for($i=0;$i $cvalue = trim($objworksheet->getcellbycolumnandrow($i,1)->getvalue()); switch ($cvalue) { case self::product_sap_code : $colums[$i] = sap_code; break; case self::product_name : $colums[$i] = pname; break; case self::product_group : $colums[$i] = product_group; break; case self::product_brand : $colums[$i] = 'product_brand'; break; case self::product_proxy_flag : $colums[$i] = product_proxy_flag; break; case self::product_binning : $colums[$i] = product_binning; break; case self::product_sell_pick : $colums[$i] = product_sell_pick; break; case self::product_attribute : $colums[$i] = product_attribute; break; case self::product_supplier_code : $colums[$i] = vendor_code; break; case self::product_supply_address : $colums[$i] = zzwerk_code; break; case self::product_batch : $colums[$i] = zzlgort_code; break; default : $error[3][] = $cvalue; break; } } //检测excel中的基本信息是否存在 $datacount = $highestrow - 1; if(count($colums) == 0) { $error[5] = 没有表头; } else if(!in_array('sap_code',$colums)){ $error[2] = 表头中商品sap编码不存在; } else if($datacount $error[6] = excel文件中没有数据; } else if(count($error)==0){ for ($i=2;$i $colkey = array_search('sap_code'); $shoplinkedidvalue = trim($objworksheet->getcellbycolumnandrow($colkey,$i)->getvalue()); if(!$shoplinkedidvalue) { continue; } if(in_array($shoplinkedidvalue,$execlallshoplinkedid)){ $error[7][$shoplinkedidvalue]['duplicate'] = true; $error[7][$shoplinkedidvalue]['excelrow'][] = $i; $execlallshoplinkedid[$i] = $shoplinkedidvalue; $error[7][$shoplinkedidvalue]['noid'] = true; }else { $excelidrow[$shoplinkedidvalue] = $i; $execlallshoplinkedid[$i] = $shoplinkedidvalue; } } $dealmultiple = ceil($datacount / 1000); $allproduct = array(); for($i=0;$i $offset = $i*1000+2; $max = ($i+1)*1000+1; $max = ($max > $datacount) ? $highestrow : $max; $allshoplinkedid = array(); for($j=$offset;$j if($execlallshoplinkedid[$j]){ $allshoplinkedid[] = $execlallshoplinkedid[$j]; } } // 根据sap商品编码查询在库中的记录数. $dbshopproducts = $this->getshoplinkedbyids($allshoplinkedid);
for($j=$offset;$j $product = array();
for($k=0;$k $tempv = trim($objworksheet->getcellbycolumnandrow($k,$j)->getvalue()); if($tempv && $tempv != '') { $product[$colums[$k]] = $tempv; } } //获取文件中的sap编码 $id = $product['sap_code']; if(!$id){ continue; } //检测商品sap编码是否已经存在 if(!in_array($id,$dbshopproducts)){ $allproduct[$id] = $product; }else{ $error[7][$id]['hasid'] = true; } //商品名是否为空 if(!isset($product['pname'])){ $error[7][$id]['emptyname'] = true; } //商品类目（商品组）是否为空 if(!isset($product['product_group'])){ $error[7][$id]['emptyproductgroup'] = true; } //产品层次（品牌）是否为空 if(!isset($product['product_brand'])){ $error[7][$id]['emptyproductbrand'] = true; } //经代销标志是否为空 if(!isset($product['product_proxy_flag'])){ $error[7][$id]['emptyproductproxyflag'] = true; } //装箱清单是否为空 if(!isset($product['product_binning'])){ $error[7][$id]['emptyproductbinning'] = true; } //先销后采标识是否为空 if(!isset($product['product_sell_pick'])){ $error[7][$id]['emptyproductsellpick'] = true; } //商品属性是否为空 if(!isset($product['product_attribute'])){ $error[7][$id]['emptyproductattribute'] = true; } //供应商编码是否为空 if(!isset($product['vendor_code'])){ $error[7][$id]['emptyvendorcode'] = true; } //供应地点是否为空 if(!isset($product['zzwerk_code'])){ $error[7][$id]['emptyzzwerkcode'] = true; } //库区是否为空 if(!isset($product['zzlgort_code'])){ $error[7][$id]['emptyzzlgortcode'] = true; } if(isset($error[7][$id])){ $error[7][$id]['excelrow'] = $j; } } } } } $resultinfo['filename'] = $filename; //返回错误信息 if(count($error)>0){ if(isset($error[1])){ $resultinfo['type'] = 1; $resultinfo['msg'] = $error[1]; }else if(isset($error[2])){ $resultinfo['type'] = 2; $resultinfo['msg'] = $error[2]; }else if(isset($error[3])){ $resultinfo['type'] = 3; $resultinfo['msg'] = '表头【'.implode(',',$error[3]).'】不存在'; }else if(isset($error[4])){ $resultinfo['type'] = 4; $resultinfo['msg'] = $error[4]; }else if(isset($error[6])){ $resultinfo['type'] = 6; $resultinfo['msg'] = $error[6]; }else if(isset($error[7])){ $excelname = null; $objphpwriteexcel = new phpexcel(); $objphpwriteexcel->getproperties()->setcreator(yuer) ->setlastmodifiedby(yuer)->settitle()->setsubject() ->setdescription()->setkeywords()->setcategory(); $prefix = substr($filename,0,strrpos($filename,'.')); $suffix = substr($filename,strrpos($filename,'.')); $excelname = date(y_m_d_h_i_s).'_'.mt_rand(1,99).'_'.$prefix.'errorreport'.$suffix; $excelname = base_tool_pinyin::getpinyin($excelname); $objphpwriteexcel->setactivesheetindex(0); $activesheet = $objphpwriteexcel->getactivesheet(); $activesheet->settitle('错误报告'); $activesheet->setcellvaluebycolumnandrow(0,1,self::product_sap_code); $activesheet->setcellvaluebycolumnandrow(1,1,'原excel行号'); $activesheet->setcellvaluebycolumnandrow(2,1,'第几行编码存在重复'); $activesheet->setcellvaluebycolumnandrow(3,1,self::product_name); $activesheet->setcellvaluebycolumnandrow(4,1,self::product_group); $activesheet->setcellvaluebycolumnandrow(5,1,self::product_brand); $activesheet->setcellvaluebycolumnandrow(6,1,self::product_proxy_flag); $activesheet->setcellvaluebycolumnandrow(7,1,self::product_binning); $activesheet->setcellvaluebycolumnandrow(8,1,self::product_sell_pick); $activesheet->setcellvaluebycolumnandrow(9,1,self::product_attribute); $activesheet->setcellvaluebycolumnandrow(10,1,self::product_supplier_code); $activesheet->setcellvaluebycolumnandrow(11,1,self::product_supply_address); $activesheet->setcellvaluebycolumnandrow(12,1,self::product_batch); $activesheet->setcellvaluebycolumnandrow(13,1,'其他原因'); $activesheet->getcolumndimensionbycolumn(0)->setwidth(15); $activesheet->getcolumndimensionbycolumn(1)->setwidth(20); $activesheet->getcolumndimensionbycolumn(2)->setwidth(20); $activesheet->getcolumndimensionbycolumn(3)->setwidth(20); $activesheet->getcolumndimensionbycolumn(4)->setwidth(20); $activesheet->getcolumndimensionbycolumn(5)->setwidth(20); $activesheet->getcolumndimensionbycolumn(6)->setwidth(20); $activesheet->getcolumndimensionbycolumn(7)->setwidth(20); $activesheet->getcolumndimensionbycolumn(8)->setwidth(20); $activesheet->getcolumndimensionbycolumn(9)->setwidth(20); $activesheet->getcolumndimensionbycolumn(10)->setwidth(20); $activesheet->getcolumndimensionbycolumn(11)->setwidth(20); $activesheet->getcolumndimensionbycolumn(12)->setwidth(20); $activesheet->getcolumndimensionbycolumn(13)->setwidth(20); $writeexcelindex = 2; foreach ($error[7] as $pid=>$pinfo){ if(isset($pinfo['hasid'])){ $activesheet->setcellvaluebycolumnandrow(0,$writeexcelindex,$pid.'-此供应商编码已经存在'); } else { $activesheet->setcellvaluebycolumnandrow(0,$writeexcelindex,$pid); } $activesheet->setcellvaluebycolumnandrow(1,$writeexcelindex,$pinfo['excelrow']); if(isset($pinfo['duplicate'])){ $activesheet->setcellvaluebycolumnandrow(2,$writeexcelindex,$excelidrow[$pid]); } if(isset($pinfo['emptyname'])){ $activesheet->setcellvaluebycolumnandrow(3,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyproductgroup'])){ $activesheet->setcellvaluebycolumnandrow(4,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyproductbrand'])){ $activesheet->setcellvaluebycolumnandrow(5,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyproductproxyflag'])){ $activesheet->setcellvaluebycolumnandrow(6,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyproductbinning'])){ $activesheet->setcellvaluebycolumnandrow(7,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyproductsellpick'])){ $activesheet->setcellvaluebycolumnandrow(8,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyproductattribute'])){ $activesheet->setcellvaluebycolumnandrow(9,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyvendorcode'])){ $activesheet->setcellvaluebycolumnandrow(10,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyzzwerkcode'])){ $activesheet->setcellvaluebycolumnandrow(11,$writeexcelindex,'-为空'); } if(isset($pinfo['emptyzzlgortcode'])){ $activesheet->setcellvaluebycolumnandrow(12,$writeexcelindex,'-为空'); } if(isset($pinfo['other'])){
$activesheet->setcellvaluebycolumnandrow(13,$writeexcelindex,$pinfp['other']); } $writeexcelindex++; } $objwriter = phpexcel_iofactory::createwriter($objphpwriteexcel, 'excel5'); $excelpath = file_path.ds.'feedback'.ds.$excelname; $objwriter->save($excelpath); $resultinfo['type'] = 7; $resultinfo['msg'] = $filename.文件中存在错误; $resultinfo['errorreport'] = $excelname; // 日志操作，暂时空着 } }else{ //导入数据 $logids = ''; $i = 0; foreach ($allproduct as $pid => $pinfo){ $updateproductsql = 'insert into yr_product set '; if(isset($pinfo['pname']) && trim($pinfo['pname'])){ $updateproductsql = $updateproductsql.'pname=\''.str_replace('\'','\'\'',$pinfo['pname']).'\','; } //如果sap编码不足18位，则用0从左开始补全 if(isset($pinfo['sap_code'])){ if(strlen($pinfo['sap_code']) $pinfo['sap_code'] = str_pad($pinfo['sap_code'], 18, 0, str_pad_left); $updateproductsql = $updateproductsql.'sap_code=\''.str_replace('\'','\'\'',$pinfo['sap_code']).'\','; } } if(isset($pinfo['product_group'])){ $updateproductsql = $updateproductsql.'product_group=\''.$pinfo['product_group'].'\','; } if(isset($pinfo['product_brand'])){ $updateproductsql = $updateproductsql.'product_brand=\''.$pinfo['product_brand'].'\','; } if(isset($pinfo['product_proxy_flag'])){ $updateproductsql = $updateproductsql.'product_proxy_flag=\''.$pinfo['product_proxy_flag'].'\','; } if(isset($pinfo['product_binning'])){ $updateproductsql = $updateproductsql.'product_binning=\''.$pinfo['product_binning'].'\','; } if(isset($pinfo['product_sell_pick'])){ $updateproductsql = $updateproductsql.'product_sell_pick=\''.$pinfo['product_sell_pick'].'\','; } if(isset($pinfo['product_attribute'])){ $updateproductsql = $updateproductsql.'product_attribute=\''.$pinfo['product_attribute'].'\','; } if(isset($pinfo['vendor_code'])){ $updateproductsql = $updateproductsql.'vendor_code=\''.$pinfo['vendor_code'].'\','; } if(isset($pinfo['zzwerk_code'])){ $updateproductsql = $updateproductsql.'zzwerk_code=\''.$pinfo['zzwerk_code'].'\','; } if(isset($pinfo['zzlgort_code'])){ $updateproductsql = $updateproductsql.'zzlgort_code=\''.$pinfo['zzlgort_code'].'\''; } //最终的sql语句 $result = $this->excutemultiinsertsql($updateproductsql); } $resultinfo['type'] = 8; $resultinfo['msg'] = 导入商品基本信息成功; /*
* // 日志操作. * $content = '批量新建商品导入操作成功：导入的供应商品编码有->'; * $logdata['content'] = $content.$logids; */ } return $resultinfo; }
复制代码