tplDirectory = "merchant/views/product/";
$this->customerAuth = new Zend_Session_Namespace("customerAuth");
}
public function productUploadRecordInputAction()
{
echo Ec::renderTpl($this->tplDirectory . 'product-upload-record-import.tpl', 'noleftlayout');
}
/**
* @author $val
* @todo 产品备案模板下载
*/
public function productRecordTemplateDownloadAction()
{
$result = array();
$result['status'] = 0;
header("Content-type: text/html; charset=utf-8");
$hsCodes = trim($this->_request->getParam('hs_code', ''));
$hsCodes = Service_Hscodes::splitHscodes($hsCodes, ',');
//var_dump($hsCodes);exit();
if (empty($hsCodes)) {
header("Contetn-type: text/html; charset=utf-8");
exit('请输入海关编码!');
}
foreach ($hsCodes as $key => $hsCode) {
$aHsCode = Service_Hscodes::getByCondition(array('hs_code' => $hsCode, 'hs_code_status' => '1'));
//$aHsCode = Service_Hscodes::getByField($hsCode, 'hs_code');
if (empty($aHsCode)) {
$result['info'] = ('海关编码[' . $hsCode . ']不存在');
break;
}
$aHsUom = $aHsCode[0];
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hsCode, 'he_status' => '1'));
if (empty($hsElementList)) {
header("Content-type: text/html; charset=utf-8");
$result['info'] = ('异常：海关编码[' . $hsCode . ']没有申报要素');
break;
}
$aHsUom = Service_HsUom::getByField($hsCode, 'hs_code');
if (empty($aHsUom)) {
header("Content-type: text/html; charset=utf-8");
$result['info'] = ('异常：海关编码[' . $hsCode . ']没有法定单位');
break;
}
$oExcel = new PHPExcel();
$oExcel->setActiveSheetIndex(0);
$oActiveSheet = $oExcel->getActiveSheet();
$oExcel->getDefaultStyle()->getFont()->setName('宋体');
$oExcel->getDefaultStyle()->getFont()->setSize(12);
$oActiveSheet->setCellValue('A1', '产品SKU');
$oActiveSheet->setCellValue('B1', '产品名称');
$oActiveSheet->setCellValue('C1', '英文品名');
$oActiveSheet->setCellValue('D1', '单位');
$oActiveSheet->setCellValue('D2', '个', PHPExcel_Cell_DataType::TYPE_STRING);
$oActiveSheet->setCellValue('E1', '条码类型');
$oActiveSheet->setCellValue('F1', '产品条码');
$oActiveSheet->setCellValue('G1', '申报币种');
$oActiveSheet->setCellValue('H1', '申报价值(USD)');
$oActiveSheet->setCellValue('I1', '重量(KG)');
$oActiveSheet->setCellValue('J1', '有无发票');
$oActiveSheet->setCellValue('K1', '海关品名');
$index = 10;
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '供应商代码');
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '产品型号');
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '是否带电池');
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '电池类型');
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '电池类型补充');
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '图片URL1');
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '图片RUL2');
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '海关编码');
$oActiveSheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($index) . '2', $hsCode, PHPExcel_Cell_DataType::TYPE_STRING);
//申报要素 下载输出
foreach ($hsElementList as $hsElementRow) {
++$index;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', $hsElementRow['he_name']);
}
//法定单位
$kvUom = Service_ProductUom::getKeyVal();
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '法定单位(' . $kvUom[$aHsUom['pu_code_law']] . ')');
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '第二单位(' . $kvUom[$aHsUom['pu_code_second']] . ')');
}
//是否零件类
$index += 1;
$oActiveSheet->setCellValue(PHPExcel_Cell::stringFromColumnIndex($index) . '1', '是否零件类');
$oWriter = PHPExcel_IOFactory::createWriter($oExcel, 'Excel5');
if (strpos($_SERVER['HTTP_USER_AGENT'], "MSIE")) {//IE
$filename = urlencode('海关编码[' . $hsCode . ']的商品申报模板.xls');
} else {
$filename = '海关编码[' . $hsCode . ']商品申报模板.xls';
}
$fullPath = dirname(APPLICATION_PATH) . "/data/file/$filename";
$oWriter->save($fullPath);
$result['status'] = 1;
$result['file'][$key]['filename'] = $filename;
$result['file'][$key]['fullPath'] = $fullPath;
$result['info'] = '生成Excel成功';
}
if ($result['status'] == 0) {
$status = false;
$errorMsg .= $result['info'] . '
';
} else {
//$data .= $result['info'].'
';
$status = true;
$excelArr = $result['file'];
}
if ($status === false) {
die($errorMsg);
}
if (count($excelArr) == 1) {
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/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename="' . iconv("UTF-8", "GBK", $excelArr[0]['filename']) . '"');
header("Content-Transfer-Encoding:binary");
echo file_get_contents($excelArr[0]['fullPath']);
@unlink($tmp);
exit();
}
//生成文件
$zipFileName = date('ymdhi') . '海关编码商品申报模板.zip';
$zip = new ZipArchive();
if ($zip->open($zipFileName, ZipArchive::CREATE) !== TRUE) {
exit('无法打开文件，或者文件创建失败');
}
foreach ($excelArr as $v) {
$v['fullPath'] = $v['fullPath'];
$v['filename'] = $v['filename'];
$zip->addFile($v['fullPath'], iconv("UTF-8", "GBK", $v['filename']));
}
$zip->close();
// 删除Excel
foreach ($excelArr as $val) {
//@unlink($val['fullPath']);
}
//下面是输出下载;
header("Cache-Control: max-age=0");
header("Content-Description: File Transfer");
header('Content-disposition: attachment; filename=' . iconv("UTF-8", "GBK", basename($zipFileName))); //文件名
header("Content-Type: application/zip");
header("Content-Transfer-Encoding: binary");
header('Content-Length: ' . filesize($zipFileName));
@readfile($zipFileName);//输出文件;
@unlink($zipFileName);
die();
}
/**
* @author $val
* @todo 查看产品备案批量导入
*/
/*public function previewAction()
{
set_time_limit(300);
ini_set('memory_limit','512M');
$productUploadRecord = new Zend_Session_Namespace('productUploadRecord');
$field = 'XMLForInput';
$xlsData = self::getXLSData($_FILES, $field);
if (isset($xlsData['ask']) && $xlsData['ask']=='0') {
$this->view->uploadinfo = $xlsData;
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl",'noleftlayout');
exit;
} else {
$orderRows = $this->_getDataArr($xlsData);
$productUpload->uploadData = $orderRows;
$this->view->uploadData=$orderRows;
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-preview.tpl",'noleftlayout');
}
}*/
/**
* @todo 上传excel文件
*/
public function uploadAction()
{
ini_set('memory_limit', '2048M');
$uploadSession = new Zend_Session_Namespace('productUploadRecord');
$uploadSession->productExcel = '';
$path = APPLICATION_PATH . '/../data/upload/' . $this->_customerAuth['id'];
if (!file_exists($path)) {
if (!mkdir($path, 0777, true)) {
$this->view->message = '上传失败：内部错误';
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
}
$config = array();
$config['upload_path'] = $path;
$config['allowed_types'] = 'xls|xlsx|zip';
$config['max_size'] = '2048000';
$config['encrypt_name'] = true;
$upload = new Common_UploadFile($config);
$upload->set_upload_path($path);
if (!$upload->do_upload($_FILES, 'XMLForInput')) {
$this->view->message = '上传文件失败：' . $upload->display_errors();
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
$uploadData = $upload->data();
if (empty($uploadData) || empty($uploadData['full_path'])) {
$this->view->message = '上传失败：内部错误';
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
//处理ZIP内excel
if ($uploadData['file_ext'] == '.zip') {
$zip = new ZipArchive;
$zip->open($uploadData['full_path']);
$excelArr = $filenameRelation = array();
if ($zip->numFiles > 50) {
$zip->close();
@unlink($uploadData['full_path']);
$this->view->message = '压缩包内海关编码不能超过50个';
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
if ($zip->numFiles <= 0) {
$zip->close();
@unlink($uploadData['full_path']);
$this->view->message = '压缩包内无数据，请填充数据后再上传';
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
$dirName = date("YmdHis");
$zipPath = APPLICATION_PATH . '/../data/upload/' . $this->_customerAuth['id'] . "/$dirName";
if (!file_exists($zipPath)) {
if (!mkdir($zipPath, 0777, true)) {
$zip->close();
@unlink($uploadData['full_path']);
$this->view->message = '上传失败：内部错误';
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
}
for ($i = 0; $i < $zip->numFiles; $i++) {
$filename = $zip->getNameIndex($i);
$arr = array();
$arr = explode('.', $filename);
if (trim($arr[1]) != 'xls' && trim($arr[1]) != 'xlsx') {
$zip->close();
@unlink($uploadData['full_path']);
$filename = iconv("UTF-8", "GBK//ignore", $filename);
$this->view->message = '[' . $filename . ']文件类型错误,压缩包内请上传Excel文件';
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
if (count($arr) > 2) {
$zip->close();
@unlink($uploadData['full_path']);
$this->view->message = '[' . $filename . ']错误的命名方式';
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
//不判断压缩包内excel文件命名
$filename = $zip->getNameIndex($i);
$content = $zip->getFromName($filename);
//$newFileName = "hsCode_$i".'.xls';
$myfile = fopen($zipPath . "/$filename", "w") or die("fail!");
fwrite($myfile, $content);
fclose($myfile);
$match = array();
preg_match('/\d{10}/', $filename, $match);
$filenameRelation[$filename] = $match[0];
$excelArr[] = $filename;
//$filenameRelation[$newFileName] = $match[0];
//$excelArr[] = $newFileName;
}
// $zip->extractTo($zipPath);
$zip->close();
@unlink($uploadData['full_path']);
$uploadSession->zipPath = "";
$uploadSession->excelArr = array();
$uploadSession->zipPath = $zipPath;
$uploadSession->excelArr = $excelArr;
$this->handleZip($zipPath, $excelArr, $filenameRelation);//处理解压后的excel
die;
}
$excelData = Common_Upload::readEXCEL($uploadData['full_path']);
$uploadSession->uploadData = $excelData;
if (!is_array($excelData) || empty($excelData) || count($excelData) < 2) {
$this->view->message = '文件数据为空，请填充数据后再上传';
@unlink($uploadData['full_path']);
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
exit;
}
$result = $this->validateData($excelData);
//echo "";
//print_r($result);
if ($result['ask'] == 0) {
$this->view->message = implode('
', $result['error']);
@unlink($uploadData['full_path']);
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
} else {
$this->view->excelHead = $result['header'];
$this->view->excelData = $result['body'];
$uploadSession->productExcel = $result['body'];
$uploadSession->productHeader = $result['header'];
$uploadSession->tmpFile = $uploadData['full_path'];
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-preview.tpl", 'noleftlayout');
}
}
/**
* @param unknown $excelHeader
* @param string $hsCode
* @return multitype:string
*/
private function validateExcelHeader($excelHeader, $hsCode = '')
{
$header = array(
'产品SKU', '产品名称', '英文品名', '单位', '条码类型', '产品条码', '申报币种', '申报价值(USD)',
'重量(KG)', '有无发票', '海关品名', '供应商代码', '产品型号', '是否带电池', '电池类型', '电池类型补充',
'图片URL1', '图片RUL2', '海关编码',
);
// APPEND HS ELEMENT
$hsElement = Service_HsElement::getByCondition(array('hs_code' => $hsCode, 'he_status' => 1));
$hsUom = Service_HsUom::getByField($hsCode, 'hs_code');
$uomMap = Service_ProductUom::getKeyVal();
$error = array();
if ($hsElement) {
foreach ($hsElement as $item) {
$header[] = $item['he_name'];
}
} else {
$error[] = '找不到与海关编码[' . $hsCode . ']相匹配的申报要素';
}
if ($hsUom) {
if ('' !== trim($hsUom['pu_code_law'])) {
$header[] = '法定单位(' . $uomMap[trim($hsUom['pu_code_law'])] . ')';
}
if ('' !== trim($hsUom['pu_code_second'])) {
$header[] = '第二单位(' . $uomMap[trim($hsUom['pu_code_second'])] . ')';
}
} else {
$error[] = '找不到与海关编码[' . $hsCode . ']相匹配的申报单位';
}
$header[] = '是否零件类';
if (empty($error)) {
foreach ($excelHeader as $key => $value) {
if (!isset($header[$key])) {
break;
}
if ($header[$key] !== trim($value)) {
$error[] = '表头错误：' . PHPExcel_Cell::stringFromColumnIndex($key) . '1单元应该为' . $header[$key];
}
}
}
return $error;
}
/**
*
* @param unknown $excelData
* @return multitype:number Ambigous |multitype:number string mixed boolean Ambigous
*/
private function validateData(&$excelData)
{
$result = array('ask' => 0);
$customer_defined_barcode_array = array();
$aError = array();
//表头
$excelHead = array_shift($excelData);
$hsCode = trim($excelData[0][18]);
$aError = $this->validateExcelHeader($excelHead, $hsCode);
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hsCode, 'he_status' => 1));
$aHsUom = Service_HsUom::getByField($hsCode, 'hs_code');
//如果表头有错，返回错误
if (!empty($aError)) {
$result['error'] = $aError;
return $result;
}
//表体
//检测标题数据量大小
if (count($excelData) >= 5000) {
$result['error'] = array('excel文件数据超过5000条！');
return $result;
}
$line = 1;
$aSku = array();
foreach ($excelData as $excelRowIndex => $excelRow) {
//把空格都去掉
Common_Common::trim_blank($excelRow);
$excelData[$excelRowIndex] = $excelRow;
++$line;
$isValid = TRUE;
$productSku = trim($excelRow[0]);
$productBarcode = $this->_customerAuth['code'] . '-' . $productSku;
$productTitle = $excelRow[1];//产品名称
$productTitleEn = $excelRow[2];//产品英文名
$puName = trim($excelRow[3]);
$productBarcodeType_name = $excelRow[4];//条码类型
$barCode = trim($excelRow[5]);
$currencyCode = trim($excelRow[6]);
$productDeclaredValue = trim($excelRow[7]);
$productWeight = sprintf("%.4f", trim($excelRow[8]));
$hasInvoiceDesc = trim($excelRow[9]);
$hsGoodsName = trim($excelRow[10]);
$distributorId = 0; // 供应商ID
$distributorValid = TRUE; // 供应商代码为空或供应商代码有效时为TRUE，否则为FALSE
$distributorCode = trim($excelRow[11]);
$withBattery = trim($excelRow[13]);
$batteryType = trim($excelRow[14]);
$batterySupplement = trim($excelRow[15]);
$customerId = $this->_customerAuth['id'];
$customerCode = $this->_customerAuth['code'];
$skuArray["{$productSku}"] = $productSku;
if (!in_array(trim($productBarcodeType_name), array('自定义类型', '默认类型', '序列号类型'))) {
$aError[] = '第' . $line . '行：条码类型必须指定，且只能为：（自定义类型，默认类型，序列号类型）这三种类型';
$isValid = FALSE;
}
switch ($productBarcodeType_name) {
case '默认类型':
$product_barcode_type = 0;
break;
case '自定义类型':
$product_barcode_type = 1;
break;
case '序列号类型':
$product_barcode_type = 2;
break;
default:
$product_barcode_type = 0;
}
$excelData[$excelRowIndex]['product_barcode_type'] = $product_barcode_type;
$skuArray["{$productSku}"] = $productSku;
if (empty($productSku)) {
$aError[] = '第' . $line . '行：产品SKU不能为空';
$isValid = FALSE;
}
if("" == $productSku){
$aError[] = '第' . $line . '行：产品SKU'.Ec_Lang::getInstance()->getTranslate('ProductSKU').Ec_Lang::getInstance()->getTranslate('require');//"产品sku必填";
$isValid = FALSE;
}elseif (!preg_match('/^[0-9a-zA-Z\-]{1,25}$/', $productSku)){
// 自定义条码不收此限制
$aError[] = '第' . $line . '行：产品SKU'.Ec_Lang::getInstance()->getTranslate('ProductSkuRequiredMustBeAlphanumericCharactersAndUnderscores');//"产品sku必填必须是字母数字下划线";
$isValid = FALSE;
}
if (in_array($productSku, $aSku)) {
$aError[] = '第' . $line . '行：产品SKU[' . $productSku . ']跟前面行有重复';
$isValid = FALSE;
} else $aSku[] = $productSku;
$where = array('customer_id' => $this->_customerAuth['id'], 'product_sku' => $productSku);
$aProduct = Service_Product::getByWhere($where);
if (($aProduct)) {
$aError[] = '第' . $line . '行：产品SKU[' . $productSku . ']已存在';
$isValid = FALSE;
continue;
} else {
if (1 !== $product_barcode_type && !preg_match('/^[0-9a-zA-Z\-]{1,25}$/', $productSku)) {
// 自定义类型条码不受此限制
$aError[] = '第' . $line . '行："产品SKU[' . $productSku . ']"，非自定义类型的产品条码,产品SKU必须是字母、数字和横杠,最大长度为25位.';
$isValid = FALSE;
}
}
if (empty($productTitle)) {
$aError[] = '第' . $line . '行：产品名称不能为空';
$isValid = FALSE;
}
if (empty($productTitleEn)) {
$aError[] = '第' . $line . '行：产品英文名称不能为空';
$isValid = FALSE;
}
$uom = Service_ProductUom::getByField($puName, "pu_name");
$pu_code = $uom['pu_code'];
$excelData[$excelRowIndex]['pu_code'] = $pu_code;
$productUomArr = Service_Product::getProductUom();
if (!in_array($pu_code, $productUomArr)) {
$aError[] = '产品单位只能是[台,座,辆,艘,架,套,个,只,头,张,,件,支,枝,根,条,把,块,卷,副,片,组,份,幅,双,对,棵,株,井,盘,筒,盆,箱,批,罐,桶,扎,包,箩,打,筐,罗,匹,册,本,发,枚,捆,袋,粒,盒,合,瓶,部]';
$isValid = FALSE;
}
if ($product_barcode_type == 0 || $product_barcode_type == 2) {
if (!empty($barCode)) {
$aError[] = '第' . $line . '行：产品条码为默认类型或序列号类型，不需要填写产品条码！';
$isValid = FALSE;
}
}
if ($product_barcode_type == 1) {
if (empty($barCode)) {
$aError[] = '第' . $line . '行：自定义条码类型必须提供条码';
$isValid = FALSE;
} else {
$product_barcode = $barCode;
if (!preg_match('/^[0-9a-zA-Z\-]{1,18}$/', $barCode)) {
// 自定义类型条码不受此限制
$aError[] = '第'.$line.'自定义条码仅限能输入字母、数字和横杠,最大长度为18位.';
} else {
$aProduct = Service_Product::getByCondition(array('product_barcode' => $product_barcode));
if ($aProduct) {
$aError[] = '第' . $line . '行：产品条码已存在.';
$isValid = FALSE;
}
if (in_array($barCode, $customer_defined_barcode_array)) {
$aError[] = '第' . $line . '行：产品条码重复.';
$isValid = FALSE;
} else {
$customer_defined_barcode_array[] = $barCode;
}
}
}
}
$excelData[$excelRowIndex]['product_barcode'] = (1 == $product_barcode_type) ? $barCode : ($customerCode . '-' . $productSku);
//申报币种
if (empty($currencyCode)) {
$aError[] = '第' . $line . '行：申报币种必须填写.';
$isValid = FALSE;
} else {
if ($currencyCode != 'USD') {
$aError[] = '第' . $line . '行：申报币种必须为美元(USD).';
$isValid = FALSE;
}
}
$excelData[$excelRowIndex]['currency_code'] = 'USD';
if (!(is_numeric($productDeclaredValue) && $productDeclaredValue > 0)) {
$aError[] = '第' . $line . '行：产品申报价值必须为正的金额';
$isValid = FALSE;
}
//重量
if (!(is_numeric($productWeight) && $productWeight > 0)) {
$aError[] = '第' . $line . '行：产品重量必填，且必须为大于0的数';
$isValid = FALSE;
}
//有无发票
$kvHasInvoice = array('有' => 1, '无' => 2);
if (empty($hasInvoiceDesc)) {
$aError[] = '第' . $line . '行：有无发票必须填写.';
$isValid = FALSE;
} else if (!array_key_exists($hasInvoiceDesc, $kvHasInvoice)) {
$aError[] = '第' . $line . '行：有无发票必须填写有或者无.';
$isValid = FALSE;
} else {
$has_invoice = $kvHasInvoice[$hasInvoiceDesc];
}
$excelData[$excelRowIndex]['has_invoice'] = $has_invoice;
if (empty($hsGoodsName)) {
$aError[] = '第' . $line . '行：海关品名不能为空.';
$isValid = FALSE;
}
// 对供应商代码有效性作判断
if (!empty($distributorCode)) {
$distributor = Service_Distributor::getByCode($distributorCode, $this->_customerAuth['code']);
if ($distributor) {
$distributorId = $distributor['distributor_id'];
} else {
$distributorValid = FALSE;
$aError[] = '第' . $line . '行：无效的供应商代码【' . $distributorCode . '】';
$isValid = FALSE;
}
}
$excelData[$excelRowIndex]['distributor_id'] = $distributorId;
if ($withBattery == "是" && $batteryType == "") {
$aError[] = '第' . $line . '行：电池类型必须填.';
$isValid = FALSE;
}
$batteryTypes = '';
if ($withBattery == "是") {
if (!in_array($batteryType, array('配置电池', '内置电池', '纽扣电池', '锂金属电池'))) {
$aError[] = '第' . $line . '行：电池类型只能填写这四种:配置电池,内置电池,纽扣电池,锂金属电池';
$isValid = FALSE;
}
switch ($batteryType) {
case '配置电池':
$batteryTypes = 1;
break;
case '内置电池':
$batteryTypes = 2;
break;
case '纽扣电池':
$batteryTypes = 3;
break;
case '锂金属电池':
$batteryTypes = 4;
break;
}
}
$excelData[$excelRowIndex]['battery_type'] = $batteryTypes;
$excelData[$excelRowIndex]['battery_detail'] = $batterySupplement;
//海关编码验证
$lineHsCode = trim($excelRow[18]);
$hs_condition = array(
'hs_code' => $hsCode,
'hs_code_status' => 1
);
$aHsCodes = Service_Hscodes::getByCondition($hs_condition);
if (empty($aHsCodes)) {
$aError[] = '海关编码[' . $hsCode . ']非法';
$isValid = FALSE;
}
if (empty($lineHsCode)) {
$aError[] = '第' . $line . '行：海关编码不能为空';
$isValid = FALSE;
}
if ($lineHsCode != $hsCode) {
$aError[] = '第' . $line . '行：海关编码[' . $lineHsCode . ']与[' . $hsCode . ']不一致';
$isValid = FALSE;
}
$excelData[$excelRowIndex]['hs_code'] = $lineHsCode;
$i = 18;
$elementValueLength = 0;
// UNICODE正则表达式：去除\u200b空白字符
$unicodeSpacePattern = '/\x{200b}/u';
//申报要素
$j = 17;
foreach ($hsElementList as $hsk => $hsElementRow) {
++$j;
$element = preg_replace($unicodeSpacePattern, '', trim($excelRow[$j]));
$element = preg_replace('/\x{a0}/u', ' ', $element);
$element = strtoupper(trim($element));
if ($element !== "") {
$checkElement[$hsElementRow['he_id']] = $element;
}
}
if ($this->_customerAuth['code'] == "E0728") {
$existProduct = Service_Product::getByCondition(array('hs_code' => $hsCode, 'hs_goods_name' => $hsGoodsName, 'customer_id' => $this->_customerAuth['id']), "*");
if (!empty($existProduct)) {
foreach ($existProduct as $ep) {
//if($ep['product_id']==$aProduct['product_id']) continue;
$he = Service_HsElementMap::getByCondition(array('product_id' => $ep['product_id'], 'not_empty_hem_detail' => 1), "*");
$pass = 1;
if (count($checkElement) != count($he)) continue;
if (!empty($he)) {
foreach ($he as $e) {
if (!isset($checkElement[$e['he_id']]) || $checkElement[$e['he_id']] != $e['hem_detail']) {
$pass = 0;
continue;
}
}
}
if ($pass) {
$aError[] = '第' . $line . '行：' . "上传申报要素与" . $ep['product_sku'] . "的一样,申报要素上传失败";
break;
}
}
}
}
foreach ($hsElementList as $key => $hsElementRow) {
++$i;
$element = preg_replace($unicodeSpacePattern, '', trim($excelRow[$i])); //trim($excelRow[$i]);
$excelData[$excelRowIndex][$i] = $element;
if ($element === '') {
if (!in_array($excelHead[$i], array('其他', '其它', 'GTIN', 'CAS'))) {
//throw new Exception('申报要素【'.$excelHead[$i].'】不能为空');
// if ($element == '') $aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
$isValid = FALSE;
}
}
//汉字按2字符算
$elementValueLength += strlen(mb_convert_encoding($element, 'GB2312', 'UTF-8'));
//申报要素不允许包含的特殊字符
$specialCharsPattern = '/[\\\'\":;?~`!@#$^&=)({}\[\]]/';
$specialCharsCheck = preg_match($specialCharsPattern, $excelRow[$i]);
if ($specialCharsCheck) {
$aError[] = '第' . $line . '行：' . $excelRow[$i] . ' 包含特殊字符';
$isValid = FALSE;
}
//中文特殊字符
$specialCharsPattern_cn = array(
"！",
"￥",
"（",
"）",
"【",
"】",
"、",
"；",
"“",
"”",
"‘",
"’",
"？",
"《",
"》",
"。",
"…",
);
if (!empty($excelRow[$i])) {
foreach ($specialCharsPattern_cn as $cn_val) {
$specialCharsCheck_cn = mb_strstr($excelRow[$i], $cn_val);
if ($specialCharsCheck_cn) {
$aError[] = '第' . $line . '行： 海关申报要素：' . $excelRow[$i] . ' 包含特殊字符';
$isValid = FALSE;
}
}
$checkgb = Service_GB18030::isValid($excelRow[$i]);
//var_dump($checkgb);
if (!$checkgb)
{
$aError[] = '第' . $line .'行：' . '海关申报要素【' . $excelHead[$i] . '】含有非GB18030字符集字符';
$isValid = FALSE;
}
}
}
if ($elementValueLength > 200) {
$aError[] = '第' . $line . '行：各申报元素的内容总长度不超过200个字符。';
$isValid = FALSE;
}
//法定单位
$i += 1;
$code_law = trim($excelRow[$i]);
if (empty($code_law)) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
$isValid = FALSE;
}
if (!is_numeric($code_law)) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '只能是数字';
$isValid = FALSE;
}
//检测法定单位是否强制整数
$productUomRow = Service_ProductUom::getByField($aHsUom['pu_code_law'], 'pu_code', array('pu_int_request'));
if ((1 == $productUomRow['pu_int_request']) && !ctype_digit(strval($code_law))) {
// $i += 1;
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '必须是大于0的正整数，您的不正确输入为：' . $code_law;
$isValid = FALSE;
}
//法定单位不能小于0.001
if ($code_law) {
$code_law = floor($code_law * 1000) / 1000;
if ($code_law <= 0) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '，第一法定单位必须大于0！请不要输入小于0.001的小数！';
$isValid = FALSE;
}
}
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$i += 1;
$code_second = trim($excelRow[$i]);
if (empty($code_second)) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
$isValid = FALSE;
}
if (!is_numeric($code_second)) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '只能是数字';
$isValid = FALSE;
}
//检测法定单位是否强制整数
$productUomRow = Service_ProductUom::getByField($aHsUom['pu_code_second'], 'pu_code', array('pu_int_request'));
if ((1 == $productUomRow['pu_int_request']) && !ctype_digit(strval($code_second))) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '必须是大于0的正整数，您的不正确输入为：' . $code_second;
$isValid = FALSE;
}
if ($code_second) {
$code_second = floor($code_second * 1000) / 1000;
if ($code_second <= 0) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '，第二法定单位必须大于0！请不要输入小于0.001的小数！';
$isValid = FALSE;
}
}
}
//是否零件类
$i += 1;
$is_accessories = trim($excelRow[$i]);
if ($is_accessories && !in_array($is_accessories, array('是', '否'))) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '只能填“是”或者“否”';
$isValid = FALSE;
}
$excelData[$excelRowIndex]['is_valid'] = $isValid;
}
//返回结果
if (!empty($aError)) {
$result['error'] = $aError;
} else {
$result['header'] = $excelHead;
$result['body'] = $excelData;
//echo '',print_r($excelData),'
';exit();
$result['ask'] = 1;
}
return $result;
}
/**
* @todo 产品备案批量上传数据写入
*/
public function insertAction($isZip = false)
{
if (!$isZip) {
$uploadSession = new Zend_Session_Namespace('productUploadRecord');
$productExcel = $uploadSession->productExcel;
if (!$productExcel) {
exit(json_encode(array('ask' => 0, 'message' => '您还没有上传文件')));
}
$result = array('ask' => 0, 'message' => '');
//$excelHead = array_shift($excelData);
$sessionData = $uploadSession->productExcel;
$productExcel = array();
if (empty($sessionData)) {
$this->_redirect('/merchant/product-upload-record/product-upload-record-input');
}
$ids = $this->_request->getParam('select');
if (empty($ids)) {
$this->_redirect('/merchant/product-upload-record/product-upload-record-input');
}
$productSet = array();
$productAttrSet = array();
$hsElementSet = array();
$hsUomSet = array();
foreach ($ids as $id) {
if ($sessionData[$id]) {
$tmpProduct = $sessionData[$id];
$productSet[] = array(
'product_sku' => $tmpProduct[0],
'product_barcode' => $tmpProduct['product_barcode'],
'product_title' => $tmpProduct[1],
'product_title_en' => $tmpProduct[2],
'customer_code' => $this->_customerAuth['code'],
'customer_id' => $this->_customerAuth['id'],
'pu_code' => $tmpProduct['pu_code'],
'currency_code' => $tmpProduct['currency_code'],
'product_barcode_type' => $tmpProduct['product_barcode_type'],
'product_declared_value' => $tmpProduct[7],
'product_weight' => $tmpProduct[8],
'model_serial' => $tmpProduct[12],
'hs_code' => $tmpProduct['hs_code'],
'product_status' => 2,
'product_type' => 0,
'has_invoice' => $tmpProduct['has_invoice'],
'hs_goods_name' => strtoupper($tmpProduct[10]),
'distributor_id' => $tmpProduct['distributor_id'], // 供应商ID
'with_battery' => $tmpProduct[13] == "是" ? "1" : "0",
'battery_type' => $tmpProduct['battery_type'],
'battery_detail' => $tmpProduct['battery_detail'],
);
$tmpUrl = array();
if ('' !== $tmpProduct[16]) {
$tmpUrl['url1'] = $tmpProduct[16];
}
if ('' !== $tmpProduct[17]) {
$tmpUrl['url2'] = $tmpProduct[17];
}
$productAttrSet[] = $tmpUrl;
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $tmpProduct['hs_code'], 'he_status' => '1'));
$hsUom = Service_HsUom::getByField($tmpProduct['hs_code'], 'hs_code');
$heIndex = 19;
$tmpHsElem = array();
foreach ($hsElementList as $elem) {
$tmpHsElem[] = array(
'he_id' => $elem['he_id'],
'hem_detail' => $tmpProduct[$heIndex],
);
++$heIndex;
}
$hsElementSet[] = $tmpHsElem;
$tmpHsUom = array();
if ('' != $hsUom['pu_code_law']) {
$tmpHsUom['hu_id'] = $hsUom['hu_id'];
$tmpHsUom['hum_quantity_law'] = $tmpProduct[$heIndex];
++$heIndex;
}
if ('' != $hsUom['pu_code_second']) {
$tmpHsUom['hum_quantity_second'] = $tmpProduct[$heIndex];
++$heIndex;
}
$hsUomSet[] = $tmpHsUom;
}
}
//echo '', print_r($productSet, TRUE), '
';
//echo '', print_r($productAttrSet, TRUE), '
';
//echo '', print_r($hsElementSet, TRUE), '
';
//echo '', print_r($hsUomSet, TRUE), '
';
//exit();
} else {//压缩包文件处理后整理数据
$uploadSession = new Zend_Session_Namespace('productUploadRecord');
$elementExcel = $uploadSession->elementExcel;
if (!$elementExcel) {
exit(json_encode(array('ask' => 0, 'message' => '您还没有上传文件')));
}
$excelData = Common_Upload::readEXCEL($elementExcel);
if (!is_array($excelData) || empty($excelData) || count($excelData) < 2) {
exit(json_encode(array('ask' => 0, 'message' => '内部错误')));
}
$result = array('ask' => 0, 'message' => '');
//$this->validateData($excelData); //确认非必填数据自动填写
$excelHead = array_shift($excelData);
//var_dump($excelData);
$productSet = array();
$productAttrSet = array();
$hsElementSet = array();
$hsUomSet = array();
foreach ($excelData as $excelRowIndex => $excelRow) {
$barCode = trim($excelRow[5]);
$puName = trim($excelRow[3]);
$uom = Service_ProductUom::getByField($puName, "pu_name");
$pu_code = $uom['pu_code'];
$currencyCode = trim($excelRow[6]);
$productBarcodeType_name = $excelRow[4];//条码类型
switch ($productBarcodeType_name) {
case '默认类型':
$product_barcode_type = 0;
break;
case '自定义类型':
$product_barcode_type = 1;
break;
case '序列号类型':
$product_barcode_type = 2;
break;
default:
$product_barcode_type = 0;
}
$productBarcode = (1 == $product_barcode_type) ? $barCode : ($this->_customerAuth['code'] . '-' . $excelRow[0]);
$hasInvoiceDesc = trim($excelRow[9]);
$kvHasInvoice = array('有' => 1, '无' => 2);
$has_invoice = $kvHasInvoice[$hasInvoiceDesc];
$distributorId = 0; // 供应商ID
$distributorCode = trim($excelRow[11]);
// 对供应商代码有效性作判断
if (!empty($distributorCode)) {
$distributor = Service_Distributor::getByCode($distributorCode, $this->_customerAuth['code']);
$distributorId = $distributor['distributor_id'];
}
$batteryType = trim($excelRow[14]);
switch ($batteryType) {
case '配置电池':
$batteryTypes = 1;
break;
case '内置电池':
$batteryTypes = 2;
break;
case '纽扣电池':
$batteryTypes = 3;
break;
case '锂金属电池':
$batteryTypes = 4;
break;
}
$productSet[] = array(
'product_sku' => $excelRow[0],
'product_barcode' => $productBarcode,
'product_title' => $excelRow[1],
'product_title_en' => $excelRow[2],
'customer_code' => $this->_customerAuth['code'],
'customer_id' => $this->_customerAuth['id'],
'pu_code' => $pu_code,
'currency_code' => $currencyCode,
'product_barcode_type' => $product_barcode_type,
'product_declared_value' => $excelRow[7],
'product_weight' => $excelRow[8],
'model_serial' => $excelRow[12],
'hs_code' => trim($excelRow[18]),
'product_status' => 2,
'product_type' => 0,
'has_invoice' => $has_invoice,
'hs_goods_name' => strtoupper($excelRow[10]),
'distributor_id' => $distributorId, // 供应商ID
'with_battery' => $excelRow[13] == "是" ? "1" : "0",
'battery_type' => $batteryTypes,
'battery_detail' => trim($excelRow[15]),
);
$tmpUrl = array();
if ('' !== $excelRow[16]) {
$tmpUrl['url1'] = $excelRow[16];
}
if ('' !== $excelRow[17]) {
$tmpUrl['url2'] = $excelRow[17];
}
$productAttrSet[] = $tmpUrl;
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => trim($excelRow[18]), 'he_status' => '1'));
$hsUom = Service_HsUom::getByField(trim($excelRow[18]), 'hs_code');
$heIndex = 19;
$tmpHsElem = array();
foreach ($hsElementList as $elem) {
$tmpHsElem[] = array(
'he_id' => $elem['he_id'],
'hem_detail' => (empty($excelRow[$heIndex]) && $excelHead[$heIndex] == '其他') ? '' : $excelRow[$heIndex],
);
++$heIndex;
}
$hsElementSet[] = $tmpHsElem;
$tmpHsUom = array();
if ('' != $hsUom['pu_code_law']) {
$tmpHsUom['hu_id'] = $hsUom['hu_id'];
$tmpHsUom['hum_quantity_law'] = $excelRow[$heIndex];
++$heIndex;
}
if ('' != $hsUom['pu_code_second']) {
$tmpHsUom['hum_quantity_second'] = $excelRow[$heIndex];
++$heIndex;
}
$hsUomSet[] = $tmpHsUom;
}
//print_r($hsElementSet);
//exit();
}
if (!$isZip) {
//开始事务
$db = Common_Common::getAdapter();
}
$successArr = array();
$failureArr = array();
foreach ($productSet as $key => $pr) {
if (!$isZip) {
$db->beginTransaction();
}
try {
$insertRow = $pr;
$productID = Service_Product::add($insertRow);
Service_ProductLog::add(array(
'product_id' => $productID,
'pl_type' => 0,
'customer_id' => $insertRow['customer_id'],
'pl_add_time' => date('Y-m-d H:i:s'),
'pl_statu_pre' => 2,
'pl_statu_now' => 2,
'pl_note' => 'ProductSku:[' . $insertRow['product_sku'] . '] Create',
'pl_ip' => Common_Common::getIP(),
'user_id' => -1,
'account_id' => Service_Login::getUserId()
));
if ($productID) {
// 产品图片插入
if ($productAttrSet[$key]) {
foreach ($productAttrSet[$key] as $link) {
$paRow = array(
'product_id' => $productID,
'pa_file_type' => 'link',
'pa_path' => $link,
);
Service_ProductAttached::add($paRow);
}
}
// 申报要素插入
$checkElement = array();
foreach ($hsElementSet[$key] as $hsElement) {
$hsElemRow = $hsElement;
$hsElemRow['product_id'] = $productID;
$hsElemRow['hem_add_time'] = date('Y-m-d H:i:s');
if ($hsElement['hem_detail'] === '' || is_null($hsElement['hem_detail'])) {
$eleRow = Service_HsElement::getByField($hsElement['he_id'], 'he_id');
if (!in_array($eleRow['he_name'], array('其他', '其它', 'GTIN', 'CAS'))) {
throw new Exception('申报要素【' . $eleRow['he_name'] . '】不能为空');
// if ($element == '') $aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
}
/*if($eleRow['he_code'] == '其他') {
continue;
} else {
throw new Exception('申报要素【'.$eleRow['he_code'].'】不能为空');
}*/
}
Service_HsElementMap::add($hsElemRow);
if ($hsElement['hem_detail'] != "") {
$checkElement[$hsElement['he_id']] = $hsElement['hem_detail'];
}
}
if ($insertRow['customer_code'] == "E0728") {
$existProduct = Service_Product::getByCondition(array('hs_code' => $insertRow['hs_code'], 'hs_goods_name' => $insertRow['hs_goods_name'], 'customer_id' => $insertRow['customer_id']), "*");
if (!empty($existProduct)) {
foreach ($existProduct as $ep) {
if ($ep['product_id'] == $productID) continue;
$he = Service_HsElementMap::getByCondition(array('product_id' => $ep['product_id'], 'not_empty_hem_detail' => 1), "*");
$pass = 1;
if (count($checkElement) != count($he)) continue;
if (!empty($he)) {
foreach ($he as $e) {
if (!isset($checkElement[$e['he_id']]) || $checkElement[$e['he_id']] != $e['hem_detail']) {
$pass = 0;
continue;
}
}
}
if ($pass) throw new Exception("上传申报要素与" . $ep['product_sku'] . "的一样,申报要素上传失败");
}
}
}
// 申报单位插入
$uomRow = $hsUomSet[$key];
$uomRow['product_id'] = $productID;
$uomRow['hum_add_time'] = date('Y-m-d H:i:s');
Service_HsUomMap::add($uomRow);
//处理备案
Service_Product::handleRecord($productID);
// 产品备案上传日志写入
Service_ProductLog::add(array(
'product_id' => $productID,
'pl_type' => 0,
'customer_id' => $insertRow['customer_id'],
'pl_add_time' => date('Y-m-d H:i:s'),
'pl_statu_pre' => 2,
'pl_statu_now' => 2,
'pl_note' => 'ProductSku:[' . $insertRow['product_sku'] . '] Create',
'pl_ip' => Common_Common::getIP(),
'user_id' => -1,
'account_id' => Service_Login::getUserId()
));
}
if (!$isZip) {
$db->commit();
}
$result['ask'] = 1;
//$db->rollback();
$successArr[] = $insertRow['product_sku'];
} catch (Exception $e) {
if (!$isZip) {
$db->rollback();
}
$failureArr[] = $insertRow['product_sku'] . "上传失败:" . $e->getMessage();
}
}
if (!$isZip) {
$result['message'] = '共上传备案' . (count($successArr) + count($failureArr)) + '个产品，成功' . count($successArr) . '个，失败' . count($failureArr) . '个';
if ($failureArr) {
$result['message'] .= '，失败SKU：' . implode('、', $failureArr);
}
@unlink($uploadSession->tmpFile);
$uploadSession->tmpFile = '';
$uploadSession->unsetAll();
exit(json_encode($result));
} else {
$result['message'] = $failureArr['0'];
return $result;
}
}
public function handleZip($zipPath, $files, $filenameRelation)
{
$allContent = array();
$allMessage = '';
foreach ($files as $value) {
$zipFullPath = $zipPath . "/$value";
$excelData = Common_Upload::readEXCEL($zipFullPath);
$hsCode = "";
if (empty($filenameRelation[$value])) {
$hsCode = $result['hs_code'];
} else {
$hsCode = $filenameRelation[$value];
}
if (!is_array($excelData) || empty($excelData) || count($excelData) < 2) {
//删除目录
//$this->deleteDir($zipPath,$files);
$allMessage .= '海关编码[' . $hsCode . ']Excel的数据为空，请填充数据后再上传
';
//echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl",'noleftlayout');
//exit;
}
$result = $this->validateData($excelData);
if ($result['ask'] == 0) {
//$this->deleteDir($zipPath,$files);
$allMessage .= '海关编码[' . $hsCode . ']Excel的异常：
' . implode('；
', $result['error']) . '
';
//echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl",'noleftlayout');
//exit;
} else {
$allContent[] = $result;
}
}
if (!empty($allMessage)) {
//删除目录
$this->deleteDir($zipPath, $files);
$this->view->message = $allMessage;
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-import.tpl", 'noleftlayout');
} else {
$this->view->allContent = $allContent;
echo Ec::renderTpl($this->tplDirectory . "product-upload-record-zip-preview.tpl", 'noleftlayout');
}
}
public function deleteDir($path, $files)
{
foreach ($files as $val) {
@unlink($path . "/$val");
}
@rmdir($path);
}
public function batchConfirmAction()
{
ini_set('memory_limit', '2048M');
$uploadSession = new Zend_Session_Namespace('productUploadRecord');
$zipPath = $uploadSession->zipPath;
$excelArr = $uploadSession->excelArr;
$uploadSession->elementExcel = '';
$msg = array('ask' => 0, 'message' => '');
if (empty($excelArr)) {
$msg['message'] = '请重新上传文件';
exit(json_encode($msg));
}
$status = true;
//开始事务
$db = Common_Common::getAdapter();
$db->beginTransaction();
foreach ($excelArr as $value) {
$uploadSession->elementExcel = $zipPath . "/$value";
$result = $this->insertAction(true);
if ($result['ask'] == 0) {
$status = false;
$msg['message'] = $value . ' ' . $result['message'];
break;
}
}
if ($status === false) {
$this->deleteDir($zipPath, $excelArr);
$msg['ask'] = 0;
$db->rollback();
} else {
$msg['ask'] = 1;
$db->commit();
}
$uploadSession->zipPath = "";
$uploadSession->excelArr = array();
exit(json_encode($msg));
}
}