<?php
/**
 * Created by PhpStorm.
 * User: boyer
 * Date: 2019/12/31
 * Time: 15:18
 */
require_once ('../config.php');
ini_set("memory_limit",'4G');
require_once("PHPExcel.php");
require_once("PHPExcel/Reader/Excel2007.php");
require_once("PHPExcel/Reader/Excel5.php");
function getUpdate($row){
    $str = '';
    foreach ($row as $key =>$v){
        $str .=$key .'= \''.$v.'\',';
    }
    return rtrim($str,',');
}
$fileName ='海关编码.xls';
// $fileName = mb_convert_encoding($fileName,'gbk');
$title = array(
    'he_id'=>'he_id',
    'p_name'=>'p_name',
    '新的海关品名'=>'new_pname',
    'hs_code'=>'hs_code',
    'is_positive'=>'is_positive',
    '正面清单'=>'new_is_positive',
    '新海关编码是否存在'=>'is_exit',
    'uom1是否一样'=>'uom1_differ',
    'uom2是否一样'=>'uom2_differ',
    '新uom1'=>'uom1',
    '新uom2'=>'uom2',
    '消费税'=>'consumption_tax_new',
    '增值税'=>'added_value_tax_new',
);
$data = Common_Upload::readXlsFromTitle($fileName,$title);
foreach ($data as $hs){
    echo $hs['hs_code'];
    echo PHP_EOL;
    $sql = '';
    if($hs['is_exit'] == 0){
        $sql = "delete from hs_codes where hs_code = '".$hs['hs_code']."';".PHP_EOL;
        $sql .= "delete from hs_uom where hs_code = '".$hs['hs_code']."';".PHP_EOL;
    }else{
        $update = array();
        $uomUpdate = array();
        if($hs['is_positive'] ==0  && $hs['new_is_positive']!='#N/A'){
            $update['is_positive'] = 1;
        }elseif ($hs['is_positive'] ==1  && $hs['new_is_positive']=='#N/A'){
            $update['is_positive'] = 0;
        }
        if($hs['p_name']!=$hs['new_pname'] && $hs['new_pname']!='#N/A'){
            $update['p_name'] = $hs['new_pname'];
        }
        if($hs['uom1_differ'] ==1){
            $uomUpdate['pu_code_law'] = $hs['uom1'];
        }
        if($hs['uom2_differ'] == 1){
            $uomUpdate['pu_code_second'] = $hs['uom2'];
        }
        if($hs['consumption_tax_new']){
            $update['consumption_tax'] = $hs['consumption_tax_new'];
        }
        if($hs['added_value_tax_new']){
            $update['added_value_tax'] = $hs['added_value_tax_new'];
        }
        if(!empty($update)){
            $sql .= "update hs_codes set ".getUpdate($update).' where hs_code =\''.$hs['hs_code'].'\';'.PHP_EOL;

        }
        if(!empty($uomUpdate)){
            $sql .= "update hs_uom set ".getUpdate($uomUpdate).' where hs_code =\''.$hs['hs_code'].'\';'.PHP_EOL;
        }
    }
    if($sql) {
        file_put_contents('update.sql', $sql, FILE_APPEND);
    }
}