tplDirectory = "merchant/views/product/";
$this->customerAuth = new Zend_Session_Namespace("customerAuth");
}
public function indexAction()
{
$searchCode = trim($this->_request->getParam('hsCode'));
$hs_code = trim($this->_request->getParam('hs_code'));
if (!empty($hs_code)) {
$result = array();
$result['error'] = 1;
$hs_code = str_replace('，', ',', $hs_code);
$hs_code_arr = array();
$hs_code_arr = explode(',', $hs_code);
//一次查询多少个海关编码
if (count($hs_code_arr) > 1) {
if (count($hs_code_arr) > 20) {
header("Content-type: text/html; charset=utf-8");
$result['message'] = chop('查询海关编码不能超过20个
');
exit(json_encode($result));
}
$status = true;
$errorMsg = '';
$data = '';
foreach ($hs_code_arr as $value) {
$ret = $this->checkHsCode($value);
if ($ret['status'] == 0) {
$status = false;
$errorMsg .= $ret['message'] . '
';
} else {
$data .= $ret['data'] . '
';
}
}
if ($status === false) {
header("Content-type: text/html; charset=utf-8");
$result['message'] = chop($errorMsg, '
');
exit(json_encode($result));
}
$result['error'] = 0;
$result['data'] = $data;
exit(json_encode($result));
} else {
$aHsCodes = Service_Hscodes::getByField($hs_code, 'hs_code');
if (empty($aHsCodes)) {
header("Content-type: text/html; charset=utf-8");
$result['message'] = '海关编码[' . $hs_code . ']不存在';
exit(json_encode($result));
}
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hs_code, 'he_status' => '1'), "*", 0, 0, 'he_sort asc');
if (empty($hsElementList)) {
header("Content-type: text/html; charset=utf-8");
$result['message'] = '异常：海关编码[' . $hs_code . ']没有申报要素';
exit(json_encode($result));
}
$aHsUom = Service_HsUom::getByField($hs_code, 'hs_code');
if (empty($aHsUom)) {
header("Content-type: text/html; charset=utf-8");
$result['message'] = '异常：海关编码[' . $hs_code . ']没有法定单位';
exit(json_encode($result));
}
$result['error'] = 0;
$result['data'] = "海关编码[{$hs_code}]申报要素：";
foreach ($hsElementList as $hsElementRow) {
$result['data'] .= "
{$hsElementRow['he_name']}";
}
$kvUom = Service_ProductUom::getKeyVal();
$result['data'] .= '
法定单位(' . $kvUom[$aHsUom['pu_code_law']] . ')';
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$result['data'] .= '
第二单位(' . $kvUom[$aHsUom['pu_code_second']] . ')';
}
//是否零件类
$result['data'] .= '
是否零件类';
exit(json_encode($result));
}
}
$this->view->searchCode = $searchCode;
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
}
public function checkHsCode($hs_code)
{
$result = array();
$result['status'] = 0;
$aHsCodes = Service_Hscodes::getByField($hs_code, 'hs_code');
if (empty($aHsCodes)) {
$result['message'] = '海关编码[' . $hs_code . ']不存在';
return $result;
}
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hs_code, 'he_status' => '1'), "*", 0, 0, "he_sort asc");
if (empty($hsElementList)) {
$result['message'] = '异常：海关编码[' . $hs_code . ']没有申报要素';
return $result;
}
$aHsUom = Service_HsUom::getByField($hs_code, 'hs_code');
if (empty($aHsUom)) {
$result['message'] = '异常：海关编码[' . $hs_code . ']没有法定单位';
return $result;
}
$result['data'] = "海关编码[{$hs_code}]申报要素：";
foreach ($hsElementList as $hsElementRow) {
$result['data'] .= "
{$hsElementRow['he_name']}";
}
$kvUom = Service_ProductUom::getKeyVal();
$result['data'] .= '
法定单位(' . $kvUom[$aHsUom['pu_code_law']] . ')';
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$result['data'] .= '
第二单位(' . $kvUom[$aHsUom['pu_code_second']] . ')';
}
//是否零件类
$result['data'] .= '
是否零件类
';
$result['status'] = 1;
$result['message'] = '';
return $result;
}
/**
* @author solar
* @todo 下载申报要素模板
*/
public function templateAction()
{
$hs_code = trim($this->_request->getParam('hs_code'));
$hs_code = str_replace('，', ',', $hs_code);
$hs_code_arr = array();
$hs_code_arr = explode(',', $hs_code);
if (count($hs_code_arr) > 1) {
$status = true;
$errorMsg = '';
$data = '';
$excelArr = array();
foreach ($hs_code_arr as $value) {
if (empty($value)) {
continue;
}
$ret = $this->createExcel($value);
if ($ret['status'] == 0) {
$status = false;
$errorMsg .= $ret['info'] . '
';
} else {
$data .= $ret['info'] . '
';
$excelArr[] = $ret['file'];
}
}
if ($status === false) {
die($errorMsg);
}
$filename = date('YmdHi') . "海关申报要素.zip";
$filename = iconv("UTF-8", "GBK", $filename);
// 生成文件
$zip = new ZipArchive();
if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) {
exit('无法打开文件，或者文件创建失败');
}
foreach ($excelArr as $v) {
$v['fullPath'] = iconv("UTF-8", "GBK", $v['fullPath']);
$zip->addFile($v['fullPath'], $v['filename']);
}
$zip->close();
// 删除Excel
foreach ($excelArr as $val) {
$encode = mb_detect_encoding($val['fullPath'], array('ASCII', 'GB2312', 'GBK', 'UTF-8'));
if ($encode == "UTF-8") {
$val['fullPath'] = iconv("UTF-8", "GBK", $val['fullPath']);
}
@unlink($val['fullPath']);
}
//下面是输出下载;
header("Cache-Control: max-age=0");
header("Content-Description: File Transfer");
header('Content-disposition: attachment; filename=' . basename($filename)); //文件名
header("Content-Type: application/zip");
header("Content-Transfer-Encoding: binary");
header('Content-Length: ' . filesize($filename));
@readfile($filename);//输出文件;
die($data);
}
header("Content-type: text/html; charset=utf-8");
if (empty($hs_code)) {
header("Content-type: text/html; charset=utf-8");
exit('请输入海关编码');
}
$aHsCodes = Service_Hscodes::getByField($hs_code, 'hs_code');
if (empty($aHsCodes)) {
header("Content-type: text/html; charset=utf-8");
exit('海关编码[' . $hs_code . ']不存在');
}
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hs_code, 'he_status' => '1'), "*", 0, 0, 'he_sort asc');
if (empty($hsElementList)) {
header("Content-type: text/html; charset=utf-8");
exit('异常：海关编码[' . $hs_code . ']没有申报要素');
}
$aHsUom = Service_HsUom::getByField($hs_code, 'hs_code');
if (empty($aHsUom)) {
header("Content-type: text/html; charset=utf-8");
exit('异常：海关编码[' . $hs_code . ']没有法定单位');
}
$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', '海关编码');
//审核要素
$ascii = ord('B');
foreach ($hsElementList as $hsElementRow) {
$oActiveSheet->setCellValue(chr(++$ascii) . '1', $hsElementRow['he_name']);
}
//法定单位
$kvUom = Service_ProductUom::getKeyVal();
$oActiveSheet->setCellValue(chr(++$ascii) . '1', '法定单位(' . $kvUom[$aHsUom['pu_code_law']] . ')');
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$oActiveSheet->setCellValue(chr(++$ascii) . '1', '第二单位(' . $kvUom[$aHsUom['pu_code_second']] . ')');
}
//是否零件类
$oActiveSheet->setCellValue(chr(++$ascii) . '1', '是否零件类');
//下载输出
$oWriter = PHPExcel_IOFactory::createWriter($oExcel, 'Excel5');
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");
if (strpos($_SERVER['HTTP_USER_AGENT'], "MSIE") || strpos($_SERVER['HTTP_USER_AGENT'], "Edge")) {//IE
$filename = urlencode('海关编码[' . $hs_code . ']的申报要素模板.xls');
} else {
$filename = '海关编码[' . $hs_code . ']的申报要素模板.xls';
}
$filename = iconv("UTF-8", "GBK", $filename);
header('Content-Disposition:attachment;filename="' . $filename . '"');
//header('Content-Disposition:attachment;filename="海关编码['.$hs_code.']的申报要素模板.xls"');
header("Content-Transfer-Encoding:binary");
$oWriter->save('php://output');
}
public function createExcel($hs_code)
{
$result = array();
$result['status'] = 0;
if (empty($hs_code)) {
$result['info'] = '请输入海关编码';
return $result;
}
$aHsCodes = Service_Hscodes::getByField($hs_code, 'hs_code');
if (empty($aHsCodes)) {
$result['info'] = '海关编码[' . $hs_code . ']不存在';
return $result;
}
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hs_code, 'he_status' => '1'), "*", 0, 0, 'he_sort asc');
if (empty($hsElementList)) {
$result['info'] = '异常：海关编码[' . $hs_code . ']没有申报要素';
return $result;
}
$aHsUom = Service_HsUom::getByField($hs_code, 'hs_code');
if (empty($aHsUom)) {
$result['info'] = '异常：海关编码[' . $hs_code . ']没有法定单位';
return $result;
}
$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', '海关编码');
//审核要素
$ascii = ord('B');
foreach ($hsElementList as $hsElementRow) {
$oActiveSheet->setCellValue(chr(++$ascii) . '1', $hsElementRow['he_name']);
}
//法定单位
$kvUom = Service_ProductUom::getKeyVal();
$oActiveSheet->setCellValue(chr(++$ascii) . '1', '法定单位(' . $kvUom[$aHsUom['pu_code_law']] . ')');
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$oActiveSheet->setCellValue(chr(++$ascii) . '1', '第二单位(' . $kvUom[$aHsUom['pu_code_second']] . ')');
}
//是否零件类
$oActiveSheet->setCellValue(chr(++$ascii) . '1', '是否零件类');
//下载输出
$oWriter = PHPExcel_IOFactory::createWriter($oExcel, 'Excel5');
if (strpos($_SERVER['HTTP_USER_AGENT'], "MSIE")) {//IE
$filename = urlencode('海关编码[' . $hs_code . ']的申报要素模板.xls');
} else {
$filename = '海关编码[' . $hs_code . ']的申报要素模板.xls';
}
$new_filename = iconv("utf-8", "gbk", $filename);
$fullPath = APPLICATION_PATH . "/../data/file/$new_filename";
$oWriter->save($fullPath);
$result['status'] = 1;
$result['file']['filename'] = $filename;
$result['file']['fullPath'] = APPLICATION_PATH . "/../data/file/$filename";
$result['info'] = '生成Excel成功';
return $result;
}
/**
*
* 验证压缩包内的数据
*/
public function handleZip($zipPath, $files, $filenameRelation)
{
$allContent = array();
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);
$this->view->message = '海关编码[' . $hsCode . ']Excel的数据为空，请填充数据后再上传';
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
exit;
}
$result = $this->validateData($excelData);
if ($result['ask'] == 0) {
$this->deleteDir($zipPath, $files);
$this->view->message = '海关编码[' . $hsCode . ']Excel的异常：
' . implode('；
', $result['error']);
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
exit;
} else {
$allContent[] = $result;
}
}
$this->view->allContent = $allContent;
echo Ec::renderTpl($this->tplDirectory . "element-zip-preview.tpl", 'noleftlayout');
}
/**
*
* 删除出现问题的目录和文件
*/
public function deleteDir($path, $files)
{
foreach ($files as $val) {
@unlink($path . "/$val");
}
@rmdir($path);
}
/**
*
* 确认上传压缩包内的Excel
*/
public function batchConfirmAction()
{
$uploadSession = new Zend_Session_Namespace('upload');
$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->confirmZipAction();
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));
}
/**
*
* 确认上传压缩包内的Excel
*/
public function confirmZipAction()
{
$uploadSession = new Zend_Session_Namespace('upload');
$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' => '');
$excelHead = array_shift($excelData);
//开始事务
// $db = Common_Common::getAdapter();
// $db->beginTransaction();
try {
$hs_code = trim($excelData[0][1]);
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hs_code, 'he_status' => '1'), "*", 0, 0, 'he_sort asc');
$aHsUom = Service_HsUom::getByField($hs_code, 'hs_code');
foreach ($excelData as $excelRowIndex => $excelRow) {
$where = array('customer_id' => $this->_customerAuth['id'], 'product_sku' => trim($excelRow[0]));
$aProduct = Service_Product::getByWhere($where, 'product_id');
$hemRow = Service_HsElementMap::getByField($aProduct['product_id'], 'product_id');
if ($hemRow) continue;
$i = 0;
// UNICODE正则表达式：去除\u200b空白字符
$unicodeSpacePattern = '/\x{200b}/u';
//申报要素
foreach ($hsElementList as $key => $hsElementRow) {
$i = $key + 2;
$elementMap = array();
$elementMap['he_id'] = $hsElementRow['he_id'];
$elementMap['product_id'] = $aProduct['product_id'];
$element = preg_replace($unicodeSpacePattern, '', trim($excelRow[$i]));
$element = preg_replace('/\x{a0}/u', ' ', $element);
$tmpEle = strtoupper(trim($element));
if ($tmpEle === '' || is_null($tmpEle)) {
if ($hsElementRow['he_code'] == '其他') {
continue;
} else {
throw new Exception('申报要素【' . $hsElementRow['he_code'] . '】不能为空');
}
}
$elementMap['hem_detail'] = $tmpEle;//strtoupper(trim($element, chr(194).chr(160)));
$elementMap['hem_add_time'] = date('Y-m-d H:i:s');
Service_HsElementMap::add($elementMap);
}
$i = 0;
$elementValueLength = 0;
foreach ($hsElementList as $key => $hsElementRow) {
$i = $key + 2;
$element = preg_replace($unicodeSpacePattern, '', trim($excelRow[$i]));
$elementValueLength += mb_strlen($element, "utf-8");
}
if ($elementValueLength > 200) {
throw new Exception("各申报元素的内容总长度不超过200个字符。");
}
//法定单位
$i += 1;
$uomsMap = array();
$uomsMap['hu_id'] = $aHsUom['hu_id'];
$uomsMap['product_id'] = $aProduct['product_id'];
$hum_quantity_law = trim($excelRow[$i]);
//检测法定单位是否强制整数
$productUomRow = Service_ProductUom::getByField($aHsUom['pu_code_law'], 'pu_code', array('pu_int_request'));
if ((1 == $productUomRow['pu_int_request']) && !ctype_digit(strval($hum_quantity_law))) {
throw new Exception("法定单位必须是大于0的正整数，您的不正确输入为：" . $hum_quantity_law);
}
if ($hum_quantity_law) {
$hum_quantity_law = floor($hum_quantity_law * 1000) / 1000;
if ($hum_quantity_law <= 0) {
throw new Exception('第一法定单位必须大于0！请不要输入小于0.001的小数！');
}
}
$uomsMap['hum_quantity_law'] = $hum_quantity_law;
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$i += 1;
$hum_quantity_second = trim($excelRow[$i]);
if ($hum_quantity_second) {
$hum_quantity_second = floor($hum_quantity_second * 1000) / 1000;
if ($hum_quantity_second <= 0) {
throw new Exception('第二法定单位必须大于0！请不要输入小于0.001的小数！');
}
//检测法定单位是否强制整数
$productUomRow = Service_ProductUom::getByField($aHsUom['pu_code_second'], 'pu_code', array('pu_int_request'));
if ((1 == $productUomRow['pu_int_request']) && !ctype_digit(strval($hum_quantity_second))) {
throw new Exception("法定单位必须是大于0的正整数，您的不正确输入为：" . $hum_quantity_second);
}
}
$uomsMap['hum_quantity_second'] = $hum_quantity_second;
}
$uomsMap['hum_add_time'] = date('Y-m-d H:i:s');
Service_HsUomMap::add($uomsMap);
//是否零件类
$i += 1;
$is_accessories_cn = trim($excelRow[$i]);
if ($is_accessories_cn) {
$is_accessories = $is_accessories_cn == '是' ? 1 : 0;
Service_Product::update(array('is_accessories' => $is_accessories), $aProduct['product_id']);
}
//处理备案
Service_Product::handleRecord($aProduct['product_id']);
}
// $db->commit();
$result['ask'] = 1;
} catch (Exception $e) {
// $db->rollback();
$result['message'] = '上传异常：' . $e->getMessage() . '，请重新上传';
}
// @unlink($uploadSession->elementExcel);
// $uploadSession->elementExcel = '';
// exit(json_encode($result));
return $result;
}
/**
* @todo 上传Excel文件
* @author solar
*/
public function uploadAction()
{
$uploadSession = new Zend_Session_Namespace('upload');
$uploadSession->elementExcel = '';
$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 . "element-template.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 . "element-template.tpl", 'noleftlayout');
exit;
}
$uploadData = $upload->data();
if (empty($uploadData) || empty($uploadData['full_path'])) {
$this->view->message = '上传失败：内部错误';
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
exit;
}
//处理ZIP内excel
if ($uploadData['file_ext'] == '.zip') {
$zip = new ZipArchive;
$zip->open($uploadData['full_path']);
$excelArr = $filenameRelation = array();
if ($zip->numFiles > 20) {
$zip->close();
@unlink($uploadData['full_path']);
$this->view->message = '压缩包内申报要素不能超过20个';
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
exit;
}
if ($zip->numFiles <= 0) {
$zip->close();
@unlink($uploadData['full_path']);
$this->view->message = '压缩包内无数据，请填充数据后再上传';
echo Ec::renderTpl($this->tplDirectory . "element-template.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 . "element-template.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']);
$this->view->message = '[' . $filename . ']文件类型错误,请上传Excel文件';
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
exit;
}
if (count($arr) > 2) {
$zip->close();
@unlink($uploadData['full_path']);
$this->view->message = '[' . $filename . ']错误的命名方式';
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
exit;
}
$filename = $zip->getNameIndex($i);
$content = $zip->getFromName($filename);
$newFileName = "hsCode_$i" . '.xls';
$myfile = fopen($zipPath . "/$newFileName", "w") or die("fail!");
fwrite($myfile, $content);
fclose($myfile);
$match = array();
preg_match('/\d{10}/', $filename, $match);
$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']);
if (!is_array($excelData) || empty($excelData) || count($excelData) < 2) {
$this->view->message = '文件数据为空，请填充数据后再上传';
@unlink($uploadData['full_path']);
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
exit;
}
$result = $this->validateData($excelData);
if ($result['ask'] == 0) {
$this->view->message = implode('
', $result['error']);
@unlink($uploadData['full_path']);
echo Ec::renderTpl($this->tplDirectory . "element-template.tpl", 'noleftlayout');
} else {
$this->view->excelHead = $result['header'];
$this->view->excelData = $result['body'];
$uploadSession->elementExcel = $uploadData['full_path'];
echo Ec::renderTpl($this->tplDirectory . "element-preview.tpl", 'noleftlayout');
}
}
private function validateData(&$excelData)
{
$result = array('ask' => 0);
$aError = array();
//表头
$excelHead = array_shift($excelData);
if (trim($excelHead[0]) != '产品SKU') $aError[] = '表头错误：A1单元格应该为"产品SKU"';
if (trim($excelHead[1]) != '海关编码') $aError[] = '表头错误：B1单元格应该为"海关编码"';
$hs_code = trim($excelData[0][1]);
//$aHsCodes = Service_Hscodes::getByField($hs_code, 'hs_code');
$hs_condition = array(
'hs_code' => $hs_code,
'hs_code_status' => 1
);
$result['hs_code'] = $hs_code;
$aHsCodes = Service_Hscodes::getByCondition($hs_condition, "*", 0, 0, 'he_sort asc');
if (empty($aHsCodes)) $aError[] = '海关编码[' . $hs_code . ']非法';
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hs_code, 'he_status' => 1), "*", 0, 0, 'he_sort asc');
if (empty($hsElementList)) {
$aError[] = '异常：海关编码[' . $hs_code . ']没有申报要素';
}
$i = 0;
$ascii = ord('A');
foreach ($hsElementList as $key => $hsElementRow) {
$i = $key + 2;
if (trim($excelHead[$i]) != $hsElementRow['he_name']) {
$aError[] = '表头错误：' . chr($ascii + $i) . '1单元格应该为"' . $hsElementRow['he_name'] . '"';
}
}
$aHsUom = Service_HsUom::getByField($hs_code, 'hs_code');
if (empty($aHsUom)) {
$aError[] = '异常：海关编码[' . $hs_code . ']没有法定单位';
}
//法定单位
$i += 1;
$kvUom = Service_ProductUom::getKeyVal();
$puCodeLaw = '法定单位(' . $kvUom[$aHsUom['pu_code_law']] . ')';
if (trim($excelHead[$i]) != $puCodeLaw) $aError[] = '表头错误：' . chr($ascii + $i) . '1单元格应该为"' . $puCodeLaw . '"';
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$i += 1;
$puCodeSecond = '第二单位(' . $kvUom[$aHsUom['pu_code_second']] . ')';
if (trim($excelHead[$i]) != $puCodeSecond) $aError[] = '表头错误：' . chr($ascii + $i) . '1单元格应该为"' . $puCodeSecond . '"';
}
//如果表头有错，返回错误
if (!empty($aError)) {
$result['error'] = $aError;
return $result;
}
//表体
$line = 1;
$aSku = array();
/*echo "";
print_r($excelData);exit('sssfff');*/
//0 无品牌”、“1 境内自主品牌”、“2 境内收购品牌”、“3 境外品牌（贴牌生产）”、“4 境外品牌（其它）
$ppvalue = array(
'0',
'1',
'2',
'3',
'4'
);
//出口享惠情况（“0 出口货物在最终目的国（地区）不享受优惠关税”、“1 出口货物在最终目的国（地区）享受优惠关税”、“2 出口货物不能确定在最终目的国（地区）享受优惠关税”）
$ckxhvalue = array(
'0',
'1',
'2',
'3',
);
foreach ($excelData as $excelRowIndex => $excelRow) {
++$line;
$productSku = trim($excelRow[0]);
if (empty($productSku)) $aError[] = '第' . $line . '行：产品SKU不能为空';
if (in_array($productSku, $aSku)) $aError[] = '第' . $line . '行：产品SKU[' . $productSku . ']跟前面行有重复';
else $aSku[] = $productSku;
$where = array('customer_id' => $this->_customerAuth['id'], 'product_sku' => $productSku);
$aProduct = Service_Product::getByWhere($where);
if (empty($aProduct)) {
$aError[] = '第' . $line . '行：产品SKU[' . $productSku . ']不存在';
continue;
}
if ($aProduct['product_status'] != 2) {
$aError[] = '第' . $line . '行：产品SKU[' . $productSku . ']不是草稿状态';
}
if ($aProduct['hs_code'] != $hs_code) {
$aError[] = '第' . $line . '行：产品SKU[' . $productSku . ']的海关编码是[' . $aProduct['hs_code'] . ']';
}
$lineHsCode = trim($excelRow[1]);
if (empty($lineHsCode)) $aError[] = '第' . $line . '行：海关编码不能为空';
if ($lineHsCode != $hs_code) $aError[] = '第' . $line . '行：海关编码[' . $lineHsCode . ']与[' . $hs_code . ']不一致';
$i = 0;
$elementValueLength = 0;
// UNICODE正则表达式：去除\u200b空白字符
$unicodeSpacePattern = '/\x{200b}/u';
//申报要素
foreach ($hsElementList as $key => $hsElementRow) {
$i = $key + 2;
$element = preg_replace($unicodeSpacePattern, '', trim($excelRow[$i])); //trim($excelRow[$i]);
$element = preg_replace('/\x{a0}/u', ' ', $element);
$excelData[$excelRowIndex][$i] = $element;
if ($i != 2 && $i != 3) {
if (!in_array($hsElementRow['he_name'],array('其他','其它','GTIN','CAS')) ) {
if ($element == '') $aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
}
//if (empty($element) && $excelHead[$i] != "其他") $aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
} else {
if ($i == 2) {
//品牌验证
if (!in_array($element, $ppvalue)) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '必须是0,1,2,3,4其中的一个';
}
}
if ($i == 3) {
if (!in_array($element, $ckxhvalue)) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '必须是0,1,2,3其中的一个';
}
}
}
//$elementValueLength+= mb_strlen($element,"utf-8");
//招保IT说汉字按2字符算
$elementValueLength += strlen(mb_convert_encoding($element, 'GB2312', 'UTF-8'));
//申报要素不允许包含的特殊字符
$specialCharsPattern = "/[\\\'\":;?~`!@#$^&=)({}\[\]]/";
$specialCharsCheck = preg_match($specialCharsPattern, $excelRow[$i]);
if ($specialCharsCheck) {
$aError[] = '第' . $line . '行：' . $excelRow[$i] . ' 包含特殊字符';
}
//中文特殊字符
$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[] = '海关申报要素：' . $excelRow[$i] . ' 包含特殊字符';
}
}
if (!Service_GB18030::isValid($excelRow[$i])) {
$aError[] = '第' . $line . '行：' . '海关申报要素【' . $excelHead[$i] . '】含有非GB18030字符集字符';
}
}
}
/*if($elementValueLength>200){
$aError[] = '第'.$line.'行,各申报元素的内容总长度不超过200个字符。';
}*/
//法定单位
$i += 1;
$code_law = trim($excelRow[$i]);
if (empty($code_law)) $aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
if (!is_numeric($code_law)) $aError[] = '第' . $line . '行：' . $excelHead[$i] . '只能是数字';
//检测法定单位是否强制整数
$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))) {
$aError[] = '第' . $line . '行：' . $puCodeLaw . '必须是大于0的正整数，您的不正确输入为：' . $code_law;
}
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$i += 1;
$code_second = trim($excelRow[$i]);
if (empty($code_second)) $aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
if (!is_numeric($code_second)) $aError[] = '第' . $line . '行：' . $excelHead[$i] . '只能是数字';
//检测法定单位是否强制整数
$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 . '行：' . $puCodeSecond . '必须是大于0的正整数，您的不正确输入为：' . $code_second;
}
}
//是否零件类
$i += 1;
$is_accessories = trim($excelRow[$i]);
if ($is_accessories && !in_array($is_accessories, array('是', '否'))) {
$aError[] = '第' . $line . '行：' . $excelHead[$i] . '只能填“是”或者“否”';
}
}
//返回结果
if (!empty($aError)) {
$result['error'] = $aError;
} else {
$result['header'] = $excelHead;
$result['body'] = $excelData;
$result['ask'] = 1;
}
return $result;
}
/**
* @author solar
* @todo 确认上传Excel
*/
public function confirmAction()
{
$uploadSession = new Zend_Session_Namespace('upload');
$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' => '');
$excelHead = array_shift($excelData);
//开始事务
$db = Common_Common::getAdapter();
$db->beginTransaction();
try {
$hs_code = trim($excelData[0][1]);
$hsElementList = Service_HsElement::getByCondition(array('hs_code' => $hs_code, 'he_status' => '1'), "*", 0, 0, "he_sort asc");
$aHsUom = Service_HsUom::getByField($hs_code, 'hs_code');
foreach ($excelData as $excelRowIndex => $excelRow) {
$where = array('customer_id' => $this->_customerAuth['id'], 'product_sku' => trim($excelRow[0]));
$aProduct = Service_Product::getByWhere($where, '*');
$hemRow = Service_HsElementMap::getByField($aProduct['product_id'], 'product_id');
if ($hemRow) continue;
$i = 0;
// UNICODE正则表达式：去除\u200b空白字符
$unicodeSpacePattern = '/\x{200b}/u';
$checkElement = array();
$j = 0;
foreach ($hsElementList as $hsk => $hsElementRow) {
$j = $hsk + 2;
$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 ($aProduct['customer_code'] == "E0728") {
$existProduct = Service_Product::getByCondition(array('hs_code' => $aProduct['hs_code'], 'hs_goods_name' => $aProduct['hs_goods_name'], 'customer_id' => $aProduct['customer_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) throw new Exception($aProduct['product_sku'] . "上传申报要素与" . $ep['product_sku'] . "的一样,申报要素上传失败");
}
}
}
//申报要素
foreach ($hsElementList as $key => $hsElementRow) {
$i = $key + 2;
$elementMap = array();
$elementMap['he_id'] = $hsElementRow['he_id'];
$elementMap['product_id'] = $aProduct['product_id'];
$element = preg_replace($unicodeSpacePattern, '', trim($excelRow[$i]));
$element = preg_replace('/\x{a0}/u', ' ', $element);
$tmpEle = strtoupper(trim($element));
if ($tmpEle === '' || is_null($tmpEle)) {
if (in_array($hsElementRow['he_name'],array('其他','其它','GTIN','CAS')) ) {
//if ($element == '') $aError[] = '第' . $line . '行：' . $excelHead[$i] . '不能为空';
continue;
} else {
throw new Exception('申报要素【' . $hsElementRow['he_code'] . '】不能为空');
}
}
$elementMap['hem_detail'] = $tmpEle;//strtoupper(trim($element, chr(194).chr(160)));
$elementMap['hem_add_time'] = date('Y-m-d H:i:s');
Service_HsElementMap::add($elementMap);
}
$i = 0;
$elementValueLength = 0;
foreach ($hsElementList as $key => $hsElementRow) {
$i = $key + 2;
$element = preg_replace($unicodeSpacePattern, '', trim($excelRow[$i]));
$elementValueLength += mb_strlen($element, "utf-8");
}
if ($elementValueLength > 200) {
throw new Exception("各申报元素的内容总长度不超过200个字符。");
}
//法定单位
$i += 1;
$uomsMap = array();
$uomsMap['hu_id'] = $aHsUom['hu_id'];
$uomsMap['product_id'] = $aProduct['product_id'];
$hum_quantity_law = trim($excelRow[$i]);
//检测法定单位是否强制整数
$productUomRow = Service_ProductUom::getByField($aHsUom['pu_code_law'], 'pu_code', array('pu_int_request'));
if ((1 == $productUomRow['pu_int_request']) && !ctype_digit(strval($hum_quantity_law))) {
throw new Exception("法定单位必须是大于0的正整数，您的不正确输入为：" . $hum_quantity_law);
}
if ($hum_quantity_law) {
$hum_quantity_law = floor($hum_quantity_law * 1000) / 1000;
if ($hum_quantity_law <= 0) {
throw new Exception('第一法定单位必须大于0！请不要输入小于0.001的小数！');
}
}
$uomsMap['hum_quantity_law'] = $hum_quantity_law;
//第二单位
if ($aHsUom['pu_code_second'] != '') {
$i += 1;
$hum_quantity_second = trim($excelRow[$i]);
if ($hum_quantity_second) {
$hum_quantity_second = floor($hum_quantity_second * 1000) / 1000;
if ($hum_quantity_second <= 0) {
throw new Exception('第二法定单位必须大于0！请不要输入小于0.001的小数！');
}
//检测法定单位是否强制整数
$productUomRow = Service_ProductUom::getByField($aHsUom['pu_code_second'], 'pu_code', array('pu_int_request'));
if ((1 == $productUomRow['pu_int_request']) && !ctype_digit(strval($hum_quantity_second))) {
throw new Exception("法定单位必须是大于0的正整数，您的不正确输入为：" . $hum_quantity_second);
}
}
$uomsMap['hum_quantity_second'] = $hum_quantity_second;
}
$uomsMap['hum_add_time'] = date('Y-m-d H:i:s');
Service_HsUomMap::add($uomsMap);
//是否零件类
$i += 1;
$is_accessories_cn = trim($excelRow[$i]);
if ($is_accessories_cn) {
$is_accessories = $is_accessories_cn == '是' ? 1 : 0;
Service_Product::update(array('is_accessories' => $is_accessories), $aProduct['product_id']);
}
//处理备案
Service_Product::handleRecord($aProduct['product_id']);
}
$db->commit();
$result['ask'] = 1;
} catch (Exception $e) {
$db->rollback();
$result['message'] = '上传异常：' . $e->getMessage() . '，请重新上传';
}
@unlink($uploadSession->elementExcel);
$uploadSession->elementExcel = '';
exit(json_encode($result));
}
}